MySQL索引機制(詳細+原理+解析)

• 2022 年 4 月 1 日
• 筆記
• MySQL, 大二學習的MySQL, 調優

MySQL索引機制

永遠年輕，永遠熱淚盈眶

一.索引的類型與常見的操作

• 前綴索引

  MySQL 前綴索引能有效減小索引文件的大小，提高索引的速度。但是前綴索引也有它的壞處：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前綴索引，也不能把它們用作覆蓋索引(Covering Index)。

• 複合索引

  集一個索引包含多個列（最左前綴匹配原則）

• 唯一索引

  索引列的值必須唯一，但允許有空值

• 全文索引

  在MySQL 5.6版本以前,只有MyISAM存儲引擎支援全文引擎.在5.6版本中,InnoDB加入了對全文索引的支援,但是不支援中文全文索引.在5.7.6版本,MySQL內置了ngram全文解析器,用來支援亞洲語種的分詞.

  全文索引為FUllText，在定義索引的列上支援值的全文查找，允許在這些索引列中插入重複值和空值，全文索引可以在CHAR,VARCHAR,TEXT類型列上創建

• 主鍵索引

  設定主鍵後數據會自動建立索引，InnoDB為聚簇索引

• 單列索引

  即一個索引只包含單個列，一個表可以有多個單列索引

• 覆蓋索引

  覆蓋索引是指一個查詢語句的執行只用從所有就能夠得到，不必從數據表中讀取，覆蓋索引不是索引樹，是一個結果，當一條查詢語句符合覆蓋索引條件時候，MySQL只需要通過索引就可以返回查詢所需要的數據，這樣避免了查到索引後的回表操作，減少了I/O效率

  -- 目前有一個key(name)索引，聚簇索引是key(id)
  -- 使用了覆蓋索引
  select id from stu where key = '天天';
  -- 不使用覆蓋索引，因為查詢的結果無法從普通索引樹中得到
  select * from stu where key = '天天'
  

查看索引

show index from table_name;

列名解析：

刪除索引

drop index index_name on table name;
-- 錯誤刪除primary索引
drop index `PriMary` on temp;
-- >:Incorrect table definition; there can be only one auto column and it must be defined as a key

二.常見的索引詳解與創建

• 主鍵索引

  -- mysql中InnoDB使用主鍵索引作為聚簇索引，主鍵索引無法使用
  -- 創建時候,主鍵自動定義
  create table temppp(id int auto_increment,primary key(id),name varchar(20) not null unique);
  -- 無法刪除primary key索引,需要改變的時候，首先需要刪除主鍵列,刪除後自動選擇一行unique的列作為主鍵索引
  alter table temppp drop COLUMN id;
  

  查看：

刪除前:

刪除後：

• 單列索引

  普通的索引，沒有什麼介紹

  -- 建表時候表級約束建立索引
  create table otest(
  id int(25) PRIMARY key,
  `name` varchar(255),
  -- 這一句就是在建立普通欄位的索引，但是無法設置名字
  key(`name`)
  )
  -- 建表後
  alter table otest add index key(`name`)； 
  

  查看:（注意和前綴索引Sub_part的區別）

  

• 唯一索引

  當索引的列是unique的時候，會生成唯一索引，唯一索引關於null有下列兩種情況

  • SQLSERVER 下的唯一索引的列，允許null值，但最多允許有一個空值

    -- sql server 下實驗程式碼
    create table temp
    (
    id int primary key,
    age varchar(20) unique,
    );
    
    create unique index age on temp(age)
    execute sp_helpindex @objname='temp'
    

    查看:

  

  -- 插入兩條null語句
  insert into temp values(1,null);
  insert into temp values(2,null);
  

  結果:

  

  • MYSQL下的唯一索引的列，允許null值，並且允許多個空值

    -- mysql下實驗程式碼
    create table otest
    (
    id int primary key,
    age varchar(20) unique,
    key(age)
    );
    show index from otest
    

    查看:

    

    會建立兩個索引，一個非聚簇索引，一個是唯一索引

    -- 插入兩條null語句
    ....與上程式碼相似
    

    結果:

    

    可以插入兩個空值（明人不說暗話，我喜歡MySQL）

• 前綴索引

  1. 一方面，它不會索引所有欄位所有字元，會減小索引樹的大小.
  2. 另外一方面，索引只是為了區別出值，對於某些列，可能前幾位區別很大，我們就可以使用前綴索引。
  3. 一般情況下某個前綴的選擇性也是足夠高的，足以滿足查詢性能。對於BLOB，TEXT，或者很長的VARCHAR類型的列，必須使用前綴索引，因為MySQL不允許索引這些列的完整長度。

  ALTER TABLE table_name ADD KEY(column_name(prefix_length));
  -- 表級創建
  create table temppp (id int auto_increment,primary key(id),
                       name varchar(20) not null unique,
                      key(name(2)));
  -- 表級創建
  alter table temppp add index(name(2))
  

  查看：

前綴索引實例的博文://www.jianshu.com/p/fc80445044cc 很好，推薦

• 複合索引

  -- 建表時候表級約束建立索引
  drop table if exists `otest`;
  create table otest(
  id int(25) PRIMARY key,
  `name` varchar(255),
  age varchar(255),
  -- 這一句就是在建立普通欄位的索引，但是無法設置名字
  key(`name`,age)
  );
  -- 建表後
  alter table otest add key(`name`,age);
  

  查看：

  

  複合索引的最左前綴匹配原則：

  對於複合索引，查詢在一定條件才會使用該索引

  -- 假設一個下列的索引
  alter table otest add index(id,name,age);
  
  -- 只有查詢條件滿足組合索引的前綴匹配才能使用索引，也就是對於查詢的順序為
  -- id id,name id,name,age這三種情況下才能使用組合索引
  
  -- 對於下列這種就無法使用索引
  select * from otest where id=?,age=? -- 缺少了name列
  select * from otest where name=?,age=? -- 缺少了id列
  
  -- 對於下列查詢MySQL會使用優化調整位置
  select * from otest where id=?,age=?,name=? -- 查詢順序是 id,age,name看起來是不能使用索引的，但是MySQL在執行的時候會進行優化，將順序調整為id name age。
  

  複合索引的優點

  減少開銷。建一個聯合索引(col1,col2,col3)，實際相當於建了(col1),(col1,col2),(col1,col2,col3)三個索引。每多一個索引，都會增加寫操作的開銷和磁碟空間的開銷。對於大量數據的表，使用聯合索引會大大的減少開銷！

  覆蓋索引。對聯合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那麼MySQL可以直接通過遍歷索引取得數據，而無需回表，這減少了很多的隨機io操作。減少io操作，特別的隨機io其實是dba主要的優化策略。所以，在真正的實際應用中，覆蓋索引是主要的提升性能的優化手段之一。

  效率高。索引列越多，通過索引篩選出的數據越少。有1000W條數據的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假設假設每個條件可以篩選出10%的數據，如果只有單值索引，那麼通過該索引能篩選出1000W10%=100w條數據，然後再回表從100w條數據中找到符合col2=2 and col3= 3的數據，然後再排序，再分頁；如果是聯合索引，通過索引篩選出1000w10% 10% *10%=1w。

• 全文索引（FULLTEXT）

  在模糊搜索中很有效，搜索全文中的某一個欄位，可以參考這篇博文://zhuanlan.zhihu.com/p/88275060

三.索引的原理

1.通過實驗介紹B+tree

我們先進行下面一個實驗看看InnoDB下的主鍵索引的一個現象。

create table otest(
id int(25) PRIMARY key,
`name` varchar(255),
age varchar(255)
);


insert into otest values(3,'q',1);
insert into otest values(1,'q',1);
insert into otest values(5,'q',1);
insert into otest values(2,'q',1);
insert into otest values(6,'q',1)
-- 查看現象
SELECT * from otest

查看:

我們插入進去的時候，數據的id都是亂序的，為什麼這裡最後select查詢出來的結果都是進行了排序？

這是因為InnoDB索引底層實現的是B+tree，B+tree具有下列的特點：

• 和B-tree一樣是自平衡樹

• m個子樹上層有m個中間節點，但是m個中間節點只保存索引，而不保存數據。

• 所有的葉子結點中包含了全部關鍵字的資訊，及指向含有這些關鍵字記錄的指針，且葉子結點本身依關鍵字的大小自小而大的順序鏈接。

• 所有的非終端結點可以看成是索引部分，結點中僅含有其子樹根結點中最大（或最小）關鍵字。

所以上面的排序是為了使用B+tree的結構，B+tree為了範圍搜索，將主鍵按照從小到大排序後，拆分成節點。後續還有新的節點進入的時候，和B-tree相同的操作，會進行分裂。

一般來說，聚簇索引的B+tree都是三層

• ①：每一個底層片稱為一個頁，InnoDB中一個頁的大小默認是16kb，上層的中間結點稱為頁目錄,每個頁目錄都有一個指針指向下層存儲數據的葉結點
• ②：下層每個葉結點之間都使用鏈表連接（ps:這裡是單鏈表還是雙向鏈表我記不清楚了，讀者可以查查）
• ③：這部分是葉結點存儲的數據資訊
• ④：這部分是底層鏈表的指針

2.延伸

1. B-tree是所有結點都要存儲數據，相同的數據更深，查找速度變慢，所以底層沒有使用B-tree。

2. MySQL的InnoDB存儲引擎設計時頂層頁目錄常駐記憶體，對於2-4層B+樹查詢時，聚簇索引IO查詢1-3次，也就是和硬碟交互進行IO讀

3. 計算一個元素的位元組大小：**欄位類型所佔位元組 + 一個指針的位元組數（32位4byte，64位8byte）

4. 實際單表列過多要拆表，這樣主表存數據更多深度也低，查詢也快

5. 對於InnoDB來說主鍵索引就是聚簇索引，而普通索引就是非聚簇索引

6. 對於表中數據操作過多會造成存在許多的頁碎片，關於碎片整理可以看我這篇博文

   //www.cnblogs.com/oldoldcoder/p/16084412.html

四.聚簇索引和非聚簇索引

• 聚簇索引:將數據存儲和索引放到了一塊，索引結構的葉子結點保存了行數據
• 非聚簇索引：將數據與索引分開存儲，索引結構的葉子結點指向主鍵的值,也就是對應的聚簇索引的row id(需要查找兩個B+tree，這個操作過程叫做回表)。

InnoDB中主鍵索引一定是聚簇索引，聚簇索引一定是主鍵索引。

為什麼這裡輔助索引葉子結點不直接存儲數據呢？

• 數據冗餘
• 修改，增加，刪除需要操作的更多，時間線性增加，也就是難以維護
• 佔用磁碟存儲增大

MYISAM只有非聚簇索引，索引最終指向的都是物理地址。

1.使用聚簇索引的優勢

Q:既然有回表的存在，那麼聚簇索引的優勢在哪裡？

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

Q:主鍵索引作為聚簇索引需要注意什麼

1. 當使用主鍵為聚簇索引時，主鍵最好不要使用UUID，因為UUID的值過於離散（可以查看UUID的產生過程），不適合排序，並且可能在兩個已經排序好的結點中會出現新插入的節點，導致索引樹調整複雜度變大。
2. 建議使用int類型的自增，int類型自增主鍵數據量為4億，滿足一般開發要求，並且由於自增，主鍵本身就有序，因此開銷很小，輔助索引中保存的主鍵值也會跟著變化，佔用存儲空間，也會影響到IO操作讀取到的數據量。

2.什麼情況下無法使用索引

• 查詢語句中使用Like關鍵字

  在查詢語句中使用LIke關鍵字進行查詢時，如果匹配字元串的第一個字元為”%”，索引不會使用。如果「%」不是在第一位，索引就會使用

• 查詢語句中使用多列索引

  多列索引是在表的多個欄位上創建的索引，滿足最左前綴匹配原則，索引才會被使用

• 查詢語句中使用OR關鍵字

  查詢語句只有Or關鍵字時候，如果OR前後的兩個條件都是索引，這這次查詢將會使用索引，否則Or前後有一個條件的列不是索引，那麼查詢中將不使用索引

5.關於Explain語句

作者不會，建議查找，這裡列出是作為提醒

永遠年輕，永遠熱淚盈眶

TIPS:MySQL底層存儲文件：

• MyISAM：.frm是存放表結構的文件，.MYD是存放表數據的文件,.MYI是存放表索引的文件
• InnoDB：.frm存放表結構，.Ibd是存放表數據和索引的