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

寫不完作業還要玩發表於2022-04-01
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;

列名解析：

列名title 解釋desc 取值value
table 索引對應表的名稱 DB中的表
Non_unique 索引包含value是否為唯一（是否為唯一索引） 0代表是唯一，1代表不是
Key_name 索引的名稱 不命名為建立時列名稱，聯合查詢為Seq_in_index為1的列名稱，重複是使用_+number區分
Seq_in_index 索引中列的序列號，從1開始,表明在聯合查詢中的順序，我們可以根據這個推斷出聯合索引中索引的前後順序（使用最左優化原則） 從1遞增至聯合索引的列數
Column_name 索引的列名 索引的列名
Collation(n.排序方式，校隊) 指排序方式 A表示升序，B表示降序，NULL表示未排序。
Cardinality 基數的意思，表示索引中唯一值的數目的估計值，我們知道某個欄位的重複值越少越適合建立索引，所以我們一般根據Cardinality來判斷索引是否具有高選擇性，如果這個值非常小，就需要評估這個欄位是否適合做索引 最小值為1，表示索引的列欄位值都重複，最大為表中欄位數
Sub_part 當索引是字首索引的時候，sub_part表示字首的字元數 非字首為0，字首索引為字元數
Packed 指示關鍵字如何被壓縮。 如果沒有被壓縮，則為NULL
NUll 如果列含有null，則含有yes null/yes
Index_type 表示索引型別，全文索引是Fulltext，Memory引擎對應Hash，其他大多數是Btree，Rtree沒有見過 FULLTEXT，HASH，BTREE，RTREE
Comment 註釋 ...
Index_comment 註釋 ...

刪除索引

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))

    檢視：

字首索引例項的博文:https://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）

    在模糊搜尋中很有效，搜尋全文中的某一個欄位，可以參考這篇博文:https://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. 對於表中資料操作過多會造成存在許多的頁碎片，關於碎片整理可以看我這篇博文

    https://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是存放表資料和索引的

相關文章