視圖、索引介紹

1、視圖

1.1、什么是視圖

1.2、創建視圖

1.3、使用視圖

1.4、修改視圖

1.5、刪除視圖

1.6、視圖的優點

2、MySQL存儲結構

2.1、MySQL中的頁

3、索引

3.1、索引的數據結構

3.2、B樹和 B+樹

3.3、B+樹在MySQL索引中的應用

3.4、索引分類

1、視圖

1.1、什么是視圖

視圖是一個虛擬的表，它是基于一個或多個基礎表或基他視圖的查詢的結查集

視圖本身不存儲數據，而是通過執行查詢來動態生成數據

案例：查詢用戶的所有信息和考試成績

所有有這樣開發需求的程序員，都需要寫這么復雜的SQL

把以上SQL定義成一個視圖，用戶就可以像操作普通表一樣使用視圖進行查詢、更新和管理。

1.2、創建視圖

語法：create view?view_name [(column_list)] as?select_statement

1. 不指定列名創建

由于定義視圖時沒有指定列名，這時視圖中的列是由結果集決定的

為重復的列起別名就可以解決列重復的問題

2.?指定列名創建視圖

指定列名之后，視圖會根據指定的列名創建，查詢結果集中是否重名不重要

創建完成后，可以使用指定列名排序

select * from v_student_score_v1 order by id;

1.3、使用視圖

-- 查看視圖
show tables;
-- 查詢視圖

show create view v_student_score;
-- 使用視圖

select * from v_student_score;

使用真實表，在查詢列表中隨時可以加上其他字段，而使用視圖查詢，只能查到創建時指定的字段，不能再添加查詢字段，所以可以使用視圖隱藏不能被展示的字段

視圖和真實表進行表連接查詢：

select * from v_student_total_points v, student s where v.id = s.id;

1.4、修改視圖

通過基本表修改數據，會影響視圖

// 修改唐三藏的JAVA成績為99分

?update score set score = 99 where student_id = 1 and course_id = 1;

// 查詢視圖，發現唐三藏這條記錄已被修改

select * from v_student_socre;

通過視圖修改數據，也會影響基本表

// 修改唐三藏的計算機?絡成績為99分

update v_student_socre set score = 99 where score_id = 3;

// 發現更新失敗，因為創建視圖時使用了order by 語句

//?更新視圖

update v_student_socre_v1 set score = 99 where score_id = 3;

// 查看基礎表數據發現已被修改

select * from score where student_id = 1 and course_id = 5;

結論：不論更新了視圖還是基礎表，相互都會被影響，查詢的數據都是最新結果

以下視圖不可更新：
? 創建視圖時使用聚合函數的視圖
? 創建視圖時使用 DISTINCT
? 創建視圖時使用 GROUP BY 以及 HAVING 子句
? 創建視圖時使用 UNION 或 UNION ALL
? 查詢列表中使用子查詢
? 在FROM子句中引用不可更新視圖

1.5、刪除視圖

語法：drop view 視圖名;

1.6、視圖的優點

1. 簡單性：視圖可以將復雜的查詢封裝成一個簡單的查詢。例如，針對一個復雜的多表連接查詢，可以創建一個視圖，用戶只需查詢視圖而無需了解底層的復雜邏輯。

2. 安全性：通過視圖，可以隱藏表中的敏感數據。例如，一個系統的用戶表中，可以創建一個不包含密碼列視圖，普通用戶只能訪問這個視圖，而不能訪問原始表。

3. 邏輯數據獨立性：視圖提供了一種邏輯數據獨立性，即使底層表結構發生變化，只需修改視圖定義，而無需修改依賴視圖的應用程序。使用到應用程序與數據庫的解耦

4. 重命名列：視圖允許用戶重命名列名，以增強數據可讀性。

2、MySQL存儲結構

2.1、MySQL中的頁

1. 在.ibd文件中最重要的結構體就是Page(頁)，頁是內存與磁盤交互的最小單元，默認大小為16KB，每次內存與磁盤的交互至少讀取一頁，所以在磁盤中每個頁內部的地址都是連續的，之所以這樣做，是因為在使用數據的過程中，根據局部性原理，將來要使用的數據大概率與當前訪問的數據在空間上是臨近的，所以一次從磁盤中讀取一頁的數據放入內存中，當下次查詢的數據還在這個頁中時就可以從內存中直接讀取，從而減少磁盤I/O提高性能

.ibd 文件：innodb存儲引擎生成的表空間文件，后綴是.ibd

局部性原理：
時間局部性（Temporal Locality）：如果?個信息項正在被訪問，那么在近期它很可能還會被再次訪問。
空間局部性（Spatial Locality）：將來要?到的信息?概率與正在使?的信息在空間地址上是臨近的。

2.?每一個頁中即使沒有數據也會使用 16KB 的存儲空間，同時與索引的B+樹中的節點對應

查看頁大小：show variables like 'innodb_page_size';

3.?在MySQL中有多種不同類型的頁，最常用的就是用來存儲數據和索引的"索引頁"，也叫做"數據頁"，但不論哪種類型的頁都會包含頁頭(File Header)和頁尾(File Trailer)，頁的主體信息使用數據"行"進行填充，數據頁的基本結構如下圖所示：?

3、索引

MySQL的索引是?種數據結構，它可以幫助數據庫高效地查詢、更新數據表中的數據。索引通過 ?定的規則排列數據表中的記錄，使得對表的查詢可以通過對索引的搜索來加快速度。類似于書籍的目錄，通過指向數據行的位置，可以快速定位和訪問表中的數據

使用索引的目的只有一個，就是提升數據檢索的效率，在應用程序的運行過程中，查詢操作的頻率遠遠高于增刪改的頻率。

3.1、索引的數據結構

hash

時間復雜度是0(1)，查詢速度非常快，但是MySQL并沒有選擇HASH做為索引的默認數據結構主要原因是 HASH 不支持范圍查找

二叉搜索樹

中序遍歷是一個有序序列，所以支持范圍查找，但有幾個問題導致它不適合用作索引的數據結構

1. 最壞情況下時間復雜度為O(N)
2. 節點個數過多無法保證樹高

AVL和紅黑樹，雖然是平衡或者近似平衡，但是畢竟是二叉結構，當節點個數過多時，無法保證樹的高度
在檢索數據時，每次訪問某個節點的子節點時都會發生一次磁盤IO，而在整個數據庫系統中，IO是制約數據庫性能的主要因素，減少IO次數可以有效的提升性能

N叉樹

每個節點可以有超過兩個的子節點，可以解決樹高的問題

時間復雜度：O（logN)
在數據量相同的情況下，可以有效的控制樹高，也就是說可以使用更少的IO次數找到目標節點，從而提高數據庫的效率。但是MySQL認為N叉樹做為索引的數據結構還不夠好

3.2、B樹和 B+樹

B樹和 B+樹都是 N 叉搜索樹

B樹：上述N叉樹的案例就是B樹

B+樹：

B+樹是?種經常用于數據庫和文件系統等場合的平衡查找樹，是MySQL索引采用的數據結構

時間復雜度：O（logN)
可以有效的控制樹高

B+樹的特點：

1. 一個節點，可以存儲N個key，N個key劃分出了N個區間（而不是N+1個區間)
2. 每個節點中的key的值，都會在子節點中也存在（同時該key是子節點的最大值）
3. B+樹的葉子節點，是首尾相連，類似于一個鏈表

4. 非葉子節點只保存索引，不存數據，真實數據都保存在葉子節點中

面試題：

1.?索引使用了什么數據結構?

答：B+樹

2. 介紹一下B+樹

答：B+樹與B樹對比，B+樹的優勢是：
1. 葉子節點之間有一個相互連接的引用，可以通過一個葉子節點找到它相鄰的兄弟節點
MySQL索引在組織葉子節點時使用的是雙向鏈表
2. 非葉子節點的值都包含在葉子節點中
MySQL索引非葉子節點只保存了對子節點的引用，沒有保存真實的數據，所有真實的數據都保存在葉子節點中
3. 對于B+樹而言，在相同樹高的情況下，查找任一元素的時間復雜度都一樣，性能均衡

3.3、B+樹在MySQL索引中的應用

以查找id為5的記錄，完整的檢索過程如下：
1. 首先判斷B+樹的根節點中的索引記錄，此時5<7，應訪問左孩子節點，找到索引頁2
2. 在索引頁2中判斷id的大小，找到與5相等的記錄，命中，加載對應的數據頁

以上的IO過程，加載索引頁1-->加載索引頁2-->加載數據頁3（3次IO）

所有關于頁的操作和訪問都是在內存中進行的

理論上三層樹高的B+樹可以存放多少條記錄：

假設一條用戶數據大小為1KB，在忽略數據頁中數據頁自身屬性空間占用的情況下，一頁可以存16條數據
索引頁一條數據的大小為，主鍵用BIGINT類型占8Byte，下一頁地址6Byte，一共是14Byte,一個索引頁可以保存16*1024/14=1170條索引記錄
如果只有三層樹高的情況，綜合只保存索引的根節點和二級節點的索引頁以及保存真實數據的數據頁，那么一共可以保存1170*1170*16=21,902,400條記錄，也就是說在兩千多萬條數據的表中，可以通過三次IO就完成數據的檢索

3.4、索引分類

主鍵索引

1. 當在一個表上定義一個主鍵PRIMARY KEY時，會自動創建索引，索引的值是主鍵列的值。InnoDB使用它作為聚集索引 / 聚簇索引 / 主鍵索引。

2. 推薦為每個表定義一個主鍵。如果沒有邏輯上唯一且非空的列或列集可以使用主鍵，則添加一個自增列。

3.?如果沒有為表定義PRIMARY KEY，InnoDB使用第一個UNIQUE和NOT NULL的列作為聚集索引。（聚集索引可以標識數據行的唯一性）

普通索引

1.?最基本的索引類型，沒有唯一性的限制。工作中通常為查詢頻繁的列創建索引為了提升查詢效率

2.?可以包含一個列也可以包含多個列，包含多個列時稱為復合索引或組合索引

唯一索引

1.?當在一個表上定義一個唯一鍵 UNQUE 時，自動創建唯一索引。

2.?與普通索引類似，但區別在于唯一索引的列不允許有重復值。

如果表中沒有 PRIMARY KEY 或合適的 UNIQUE索引，InnoDB 會為新插入的行生成一個行號并用 6 字節的 ROW_ID 字段（數據行中的一個隱藏列之一）記錄，ROW_ID 單調遞增，并使用ROW_ID做為索引。這種索引也是聚集索引

非聚集索引

1. 聚集索引以外的索引稱為非聚集索引或二級索引

2.?二級索引中的每條記錄都包含該行的主鍵列，以及二級索引指定的列。

3.?InnoDB使用這個主鍵值來搜索聚集索引中的行，這個過程稱為回表查詢

注意：

1.?創建索引之后都會生成一棵索引樹，創建多少索引生多少棵索引樹

2. 創建索引后，生成的索引樹，也是會占用磁盤空間的創建索引時，要慎重考慮一下需不需要
3. 索引樹越多，對增、刪，改的效率影響越大

非聚集索引的查詢過程：
1. 通過索引查到葉子節點中的索引記錄
2. 通過索引記錄中的主鍵值，去主鍵索引樹中找相應的完整記錄，這個過程稱為回表查詢

select * from student where name = '張三';（回表查詢）

3.?通過索引查詢的列，包含在索引中，不需要回表查詢了，這種的現象叫做索引覆蓋

假設 name 和 sn 這兩列為組合索引：

select sn from student where name = '張三';（索引覆蓋）

^

問題：當前的組合索引中通過學號來查姓名索引生不生效(走不走索引)？

select name from student where sn = '100002';

答：不生效。創建索引時，name列在sn列之前，那么使用的時候也要先使用name再使用sn如果只使用sn列，那么索引就會失效，如果非要使用sn列查，可以為sn單獨創建一個索引。