MySQL索引的基本知識

資料庫 › MySQL

前言

資料庫索引已經是後端工程師的必備基礎知識，當遇到資料量大的時候，要能夠知道如何優化索引已經是必備技能！

什麼是索引？

索引是一種幫助 MySQL 提高查詢效率的資料結構。

  • 索引的優點：

    1. 大大加快數據查詢速度

  • 索引的缺點：

    1. 維護索引需要耗費數據庫資源
    2. 索引需要占用磁盤空間
    3. 當對表的數據進行增刪改的時候，因為要維護索引，速度會受到影響

索引分類

InnoDB

  • 主鍵索引：設定為主鍵後，資料庫會自動建立索引，InnoDB為聚簇索引（clustered index），索引列值不能為空
  • 普通索引（單例索引 普通索引 單值索引）：即一個索引只包含單個列，一個表可以有多個單列索引
  • 唯一索引：索引列的值必須為一，但是允許有空值（與主鍵索引的區別就是可以為null）
  • 複合索引：即一个索引包含多个列

MyISAM

  • Full Text 全文索引 (MySQL5.7版本之前 只能由于 MYISAM 引擎)
    • 全文索引類型為FULLTEXT，在定義索引的列支援值的全文查找，允許在這些索引列中插入重複值和空值。
    • 全文索引可以在CHAR、VARCHAR、TEXT類型上創建鍵。MYSQL只有 MyISAM 引擎支持全文索引

索引的基本操作

主键索引 Primary Key
創建表的時候會自動建立。

1
2
3
4
--建表 主键會自動創建主键索引
create table t_user(id varchar(20) primary key,name varchar(20));
--查看索引
show index from t_user;

普通索引
這是最基本的索引，它沒有任何限制。

1
2
3
4
5
6
--建表時創建:隨表一起建立的索引索引名同列名一致
create table t_user(id varchar(20) primary key,name varchar(20),key(name));
--建表後創建
create index nameindex on t_user(name);
--删除索引
drop index 索引名 on 表名

唯一索引 Unique index

1
2
3
4
--建表時創建
create table t_user(id varchar(20) primary key,name varchar(20),unique(name));
--建表後創建
create unique index nameindex on t_user(name);

複合索引 Composite Index

1
2
3
4
---建表時創建
 create table t_user(id varchar(20) primary key,name varchar(20),age int,key(name,age));
 --建表後創建
 create index nameageindex on t_user(name,age);

小試身手

如果基於 name, age bir 創建複合索引

1
2
3
4
5
6
7
8
Q: name bir age 能否利用索引
A: 可以
Q: name age bir 能否利用索引
A: 可以
Q: age bir      能否利用索引
A: 不行
Q: bir age name 能否利用索引
A: 可以
  1. 如果要利用複合索引，必須符合左前綴的順序才能利用索引
  2. Mysql 引擎在查詢為了更好利用索引，在查詢過程中會動態調整查詢字段順序以便利用索引
    只要查詢欄位有包含索引，不分順序就可以利用上

索引的底層原理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 建表
create table t_emp(id int primary key,name varchar(20),age int);
-- 1.新增資料
insert into t_emp values(5,'d',22);
insert into t_emp values(6,'d',22);
insert into t_emp values(7,'e',21);
insert into t_emp values(1,'a',23);
insert into t_emp values(2,'b',26);
insert into t_emp values(3,'c',27);
insert into t_emp values(4,'a',32);
insert into t_emp values(8,'f',53);
insert into t_emp values(9,'v',13);
-- 2.查詢結果
select * from t_emp;

為什麼上面指令沒有按順序插入資料，查詢時確是有順序的呢？
原因是：MySQL 底層為主鍵自動創建索引，創建索引會進行排序。也就是 MySQL 底層就是這樣存資料的。

為什麼 MySQL 底層要排序呢？
為了快速查詢。
因為排序之後再查詢就相對比較快。如查詢 id=3 的我只需要按照順序找到3就行了。
如果没有排序只能大海撈針，全靠運氣去搜尋資料

img

為了近一步提高效率 MySQL 索引又進行了優化
基於頁的形式對索引進行管理。
如查询 id=4 的資料，直接先比较頁，先去頁目錄中找，再去資料目錄中找。

如何計算一條列儲存空間？

  • id 為 int = 4 byte
  • name 為 varchar(20) = 20 byte
  • age 為 int = 4 byte
  • 指針為 4~8byte
    故一列為 4+20+4+8 = 36byte。
    一頁page默認可存儲空間為16KB，如果一列為36byte，故1頁可以存放455條數據（16KB*1024/36）

頁目錄默認可存儲空間為16KB，16*1024/(4+8byte) = 1365頁

  • 故如果用兩層結構存儲數據，總共可以455*1365=621226條數據
  • 如果用三層結構存儲數據，總共可以1365 * 1365 * 455 = 8億條左右數據

上面這種索引結構稱为 B+Tree 資料結構，那為什麼要使用 B+Tree 儲存呢?
img

參考資料：https://www.cnblogs.com/lianzhilei/p/11250589.html

B+Tree是在B-Tree(B樹)基礎上的一種優化，使其更適合實現外存儲索引結構，InnoDB存儲引擎就是用B+Tree實現其索引結構。

從上一節中的B-Tree結構圖中可以看到每個節點中不僅包含數據的key值，還有data值。而每一個頁的存儲空間是有限的，如果data數據較大時將會導致每個節點（即一個頁）能存儲的key的數量很小，當存儲的數據量很大時同樣會導致B-Tree的深度較大，增大查詢時的磁盤I/O次數，進而影響查詢效率。

在B+Tree中，所有數據記錄節點都是按照鍵值大小順序存放在同一層的葉子節點上，而非葉子節點上只存儲key值信息，這樣可以大大加大每個節點存儲的key值數量，降低B+Tree的高度。B+Tree為了加快查詢，最頂層的數據是常駐內存的，如果基於PK查詢最多1～2次，但如果是非PK例如普通索引，最多1~3次硬碟I/O操作，因為它要先找到主鍵索引再根據主鍵索引去查，所以多了1次。

B+Tree相對於B-Tree有幾點不同：

  1. 非葉子節點只會存儲键值訊息（無值）以及指針訊息。
  2. 所有葉子節點之間都有一個鏈指針。
  3. 數據記錄都存放在葉子節點中。（B+Tree只有葉子節點存儲數據，B-Tree非葉子節點也要存儲數據）

故 B+Tree 可以存儲的數據更多一點

InnoDB存儲引擎中頁的大小為16KB，一般表的主鍵類型為INT（占用4個位元組）或BIGINT（占用8個位元組），指針類型也一般為4或8個位元組，也就是說一個頁（B+Tree中的一個節點）中大概存儲16KB/(8B+8B)=1K個鍵值（因為是估值，為方便計算，這里的K取值為〖10〗^3）。也就是說一個深度為3的B+Tree索引可以維護103 * 10^3 * 10^3 = 10億 條記錄。

實際情況中每個節點可能不能填充滿，因此在數據庫中，B+Tree的高度一般都在2-4層。mysql的InnoDB存儲引擎在設計時是將根節點常駐記憶體的，也就是說查找某一鍵值的行記錄時最多只需要1-3次磁盤I/O操作。注意：頂層常駐記憶體

簡單總結 MySQL 索引的底層實現
放入數據進資料庫的時候會基於數據去排序，排序後會以鏈表的形式以一個指針的形式鏈接起來，同時MySQL在底層為了進一步優化，他是基於B+Tree的數據結構進行存儲，對我們的數據進行了一頁一頁的存，默認頁大小為16KB，站在這種B+Tree的數據結構上來講，一個三層的B+Tree的數據結構可以存儲8~10億的數據，一般來說來用的時候兩層足以，那兩層這種B+Tree來講，查詢時如果基於主鍵來查最多動用1次磁盤IO，因為頂層是常駐記憶體。

為什麼 MySQL 主鍵一直用INT的原因：是因為INT可以排序，所以以後主鍵不要再用UUID了，因為UUID比較離散無法排序！

聚簇索引和非聚簇索引
聚簇索引： 將數據存儲與索引放到了一塊，索引結構的葉子節點保存了行數據—-InnoDB
非聚簇索引：將數據與索引分開存儲，索引結構的葉子節點指向了數據對應的位置—-MyISAM

注意：在innodb中，在聚簇索引之上創建的索引稱之為輔助索引，非聚簇索引都是輔助索引，像復合索引、前綴索引、唯一索引。輔助索引葉子節點存儲的不再是行的物理位置，而是主鍵值，輔助索引訪問數據總是需要二次查找。

為什麼是存儲主鍵值，而不是存地址？
Ans：因為增刪改的時候，會導致樹上的地址會發生變化

  • InnoDB

    • InnoDB使用的是聚簇索引，將主鍵組織到一棵B+樹中，而行數據就儲存在葉子節點上，若使用”where id = 14”這樣的條件查找主鍵，則按照B+樹的檢索演算法即可查找到對應的葉節點，之後獲得行數據。
    • 若對Name列進行條件搜索，則需要兩個步驟：第一步在輔助索引B+樹中檢索Name，到達其葉子節點獲取對應的主鍵。第二步使用主鍵在主索引B+樹種再執行一次B+樹檢索操作，最終到達葉子節點即可獲取整行數據。（重點在於通過其他鍵需要建立輔助索引）
    • 聚簇索引默認是主鍵，如果表中沒有定義主鍵，InnoDB 會選擇一個唯一且非空的索引代替。如果沒有這樣的索引，InnoDB 會隱式定義一個主鍵（類似oracle中的RowId）來作為聚簇索引。如果已經設置了主鍵為聚簇索引又希望再單獨設置聚簇索引，必須先刪除主鍵，然後添加我們想要的聚簇索引，最後恢復設置主鍵即可。

  • MYISAM

    • MyISAM使用的是非聚簇索引，非聚簇索引的兩棵B+樹看上去沒什麼不同，節點的結構完全一致只是存儲的內容不同而已，主鍵索引B+樹的節點存儲了主鍵，輔助鍵索引B+樹存儲了輔助鍵。表數據存儲在獨立的地方，這兩顆B+樹的葉子節點都使用一個地址指向真正的表數據，對於表數據來說，這兩個鍵沒有任何差別。由於索引樹是獨立的，通過輔助鍵檢索無需訪問主鍵的索引樹。

使用聚簇索引的優勢
問題: 每次使用輔助索引檢索都要經過兩次B+樹查找，看上去聚簇索引的效率明顯要低於非聚簇索引，這不是多此一舉嗎？聚簇索引的優勢在哪？

  1. 由於行數據和聚簇索引的葉子節點存儲在一起，同一頁中會有多條行數據，訪問同一數據頁不同行記錄時，已經把頁加載到了Buffer中（緩存器），再次訪問時，會在內存中完成訪問，不必訪問磁盤。這樣主鍵和行數據是一起被載入內存的，找到葉子節點就可以立刻將行數據返回了，如果按照主鍵Id來組織數據，獲得數據更快。
  2. 輔助索引的葉子節點，存儲主鍵值，而不是數據的存放地址。好處是當行數據放生變化時，索引樹的節點也需要分裂變化；或者是我們需要查找的數據，在上一次IO讀寫的緩存中沒有，需要發生一次新的IO操作時，可以避免對輔助索引的維護工作，只需要維護聚簇索引樹就好了。另一個好處是，因為輔助索引存放的是主鍵值，減少了輔助索引占用的存儲空間大小。

使用聚簇索引需要注意什麼?

  1. 當使用主鍵為聚簇索引時，主鍵最好不要使用uuid，因為uuid的值太過離散，不適合排序且可能出線新增加記錄的uuid，會插入在索引樹中間的位置，導致索引樹調整復雜度變大，消耗更多的時間和資源。
  2. 建議使用int類型的自增，方便排序並且默認會在索引樹的末尾增加主鍵值，對索引樹的結構影響最小。而且，主鍵值占用的存儲空間越大，輔助索引中保存的主鍵值也會跟著變大，占用存儲空間，也會影響到IO操作讀取到的數據量。

為什麼主鍵通常建議使用自增ID

聚簇索引的數據的物理存放順序與索引順序是一致的，即：只要索引是相鄰的，那麼對應的數據一定也是相鄰地存放在磁盤上的。如果主鍵不是自增id，那麼可以想象，它會乾些什麼，不斷地調整數據的物理地址、分頁，當然也有其他一些措施來減少這些操作，但卻無法徹底避免。但，如果是自增的，那就簡單了，它只需要一頁一頁地寫，索引結構相對緊湊，磁盤碎片少，效率也高。

什麼情況下無法利用索引呢?

  1. 查詢語句中使用LIKE關鍵字
    在查詢語句中使用 LIKE 關鍵字進行查詢時，如果匹配字元串的第一個字元為“%”，索引不會被使用。如果“%”不是在第一個位置，索引就會被使用。（最左匹配）
  2. 查詢語句中使用多列索引
    多列索引是在表的多個欄位上創建一個索引，只有查詢條件中使用了這些欄位中的第一個欄位，索引才會被使用。
  3. 查詢語句中使用OR關鍵字
    查詢語句只有OR關鍵字時，如果OR前後的兩個條件的列都是索引，那麼查詢中將使用索引。如果OR前後有一個條件的列不是索引，那麼查詢中將不使用索引。

如果 name age 各自有建立索引，以下語句可以利用上索引嗎？
name and age 能利用索引
name or age 能利用索引
name or age or bir 不能利用索引

MySQL萬能優化法

  1. 查看優化器的狀態
    1
    show variables like 'optimizer_trace';
  2. session级别臨時開啟 
    1
    set session optimizer_trace="enabled=on",end_markers_in_json=on;
  3. 设置優化器追踪的記憶體大小 
    1
    set OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;
  4. 執行自己的SQL 
    1
    select host,user,plugin from user;
  5. information_schema.optimizer_trace表 
    1
    SELECT trace FROM information_schema.OPTIMIZER_TRACE;
  6. 導入到一個命名為xx.trace的文件，然後用JSON閱讀器來查看（如果沒有權限，也可直接請DBA幫忙把trace文件輸出給你）。 
    1
    SELECT TRACE INTO DUMPFILE "E:\\test.trace" FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

可以重點關注這兩個參數，可以快速定位問題

  • rows_estimation：單表的評估
  • considered_execution_plans：關聯查詢的評估

注意：不設定優化器最大容量的话，可能會導致優化器返回的结果不全。