MySQL索引詳細解析

2022-10-10 14:01:50

1. MySQL 索引的最左字首原則
2. 字首索引
3. 索引下推（ICP——Index Condition Pushdown）
4. 檢視 MySQL 語句是否用到索引
5. 為什麼官方建議用自增長主鍵作為索引
6. 如何建立索引
7. 建立索引注意事項
8. 使用索引一定可以提高查詢效能嘛
9. 索引失效

1. MySQL 索引的最左字首原則

左字首原則是聯合索引在使用時要遵循的原則，查詢索引可以使用聯合索引的一部分，但是必須從最左側開始。在建立聯合索引時，要根據業務需求，where子句中將使用最頻繁的一列放在最左邊，mysql會一直向右匹配直到遇到範圍查詢(>、<、between、like)時停止匹配。即範圍列可以用到索引，範圍列後面的列無法用到索引。

比如查詢 a = 1 and b = 2 and c > 3 and d = 4 如果建立 (a，b，d，c) 順序的索引，d是用不到索引的，如果建立 (a，b，d，c) 的索引則都可以用到，a，b，d的順序可以任意調整。可以調整 a，b，c 順序的原因是 MySQL 具有查詢優化器

MySQL 查詢優化器

當按照索引中所有列進行精確匹配（“=” 或 “IN”）時，索引可以被用到，並且 type 為 const。理論上索引對順序是敏感的，但是 MySQL 的查詢優化器會自動調整 where 子句的條件順序以使用適合的索引，所以 MySQL 在進行精確匹配時不存在因 where 子句的順序問題而造成索引失效。

2. 字首索引

定義：對於BLOB、TEXT，或者很長的VARCHAR型別的列，為它們的前幾個字元(具體幾個字元是在建立索引時指定的)建立索引，這樣的索引就叫字首索引。

優點：這樣建立起來的索引更小，所以查詢更快。

缺點：不能在 ORDER BY 或 GROUP BY 中使用字首索引，也不能把字首索參照作覆蓋索引。

建立方法：

alter table table_name add key( column_name( prefix_length));

注：這裡最關鍵的引數就是 prefix_length，這個值需要根據實際表的內容，來得到合適的索引選擇性。

prefix_length 計算方法：

先計算完整列的選擇性 :

select count(distinct col_1)/count(1) from table_1

再計算不同字首長度的選擇性 :

select count(distinct left(col_1,4))/count(1) from table_1

到最優長度之後，建立字首索引 :

create index idx_front on table_1 (col_1(4))

3. 索引下推（ICP——Index Condition Pushdown）

定義：索引下推 Index Condition Pushdown(ICP) 是MySQL使用索引從表中檢索行資料的一種優化方式，從 MySQL5.6 開始支援。5.6 之前，儲存引擎會通過遍歷索引定位基表中的行，然後返回給 Server層,再去為這些資料行進行 WHERE 後的條件的過濾。MySQL5.6之後支援 ICP，如果WHERE條件可以使用索引，MySQL 會把這部分過濾操作放到儲存引擎層，儲存引擎通過索引過濾，把滿足的行從表中讀取出。ICP 能減少引擎層存取基表的次數（回表次數）和 Server層存取儲存引擎的次數。

MySQL通過 optimizer_switch 引數中的 index_condition_pushdown 選項來控制,預設是開啟的。

操作：

檢視是否開啟

show variables like'% optimizer_switch%';

設定 ICP

SET optimizer_switch = ‘index_condition_pushdown=off';

例子：

在 people_table中有一個二級索引(zipcode，lastname，firstname)，查詢是

SELECT * FROM people WHERE zipcode='95054′ AND lastname LIKE ‘%etrunia%' AND address LIKE ‘%Main Street%';

如果沒有使用索引下推技術，則MySQL會通過zipcode=’95054’從儲存引擎中查詢對應的資料，返回到MySQL伺服器端，然後MySQL伺服器端基於lastname LIKE ‘%etrunia%’ and address LIKE ‘%Main Street%’來判斷資料是否符合條件
如果使用了索引下推技術，則MYSQL首先會返回符合zipcode=’95054’的索引，然後根據lastname LIKE ‘%etrunia%’ and address LIKE ‘%Main Street%’來判斷索引是否符合條件。如果符合條件，則根據該索引來定位對應的資料，如果不符合，則直接reject掉。

4. 檢視 MySQL 語句是否用到索引

1. 方法一：通過 show index 檢視表中含那些索引

show index from table_name

2. 方法二：通過 explain 命令檢視 SQL 語句的執行計劃：

例子：

explain select * from t where name = 'name1';

我們可以先從查詢型別type列開始檢視，如果出現all關鍵字，後面的內容就都可以不用看了，代表全表掃描。—— const(主鍵索引或者唯一二級索引進行等值匹配的情況下)，ref(普通的⼆級索引列與常數進⾏等值匹配)，index(掃描全表索引的覆蓋索引)
再看key列，看是否使用了索引， null代表沒有使用索引。
然後看rows列，該列用來表示在SQL執行過程中被掃描的行數，該數值越大，意味著需要掃描的行數越多，相應的耗時就更長。
最後再看extra列,在這列中要觀察是否有Using filesort或者Using temporary這樣的關鍵字出現,這些是很影響資料庫效能的。
MySQL5.7的執行計劃中會預設新增filtered列 (MySQL5.6 使用 explain extended 也會增加此列)，它指返回結果的行佔需要讀到的行(rows 列的值)的百分比。需要注意的是， explain中輸出的rows只是一個估算值。本例中該表進行了全表掃描。

5. 為什麼官方建議用自增長主鍵作為索引

減少分裂和移動的頻率：結合B+Tree的特點，自增主鍵是連續的，在插入過程中能儘量減少頁分裂，即使要進行頁分裂，也只會分裂很少一部分。並且能減少資料的移動，每次插入都是插入到最後。

6. 如何建立索引

1. 在執行 CREATE TABLE 時建立索引

CREATE TABLE user_index2 (
 id INT auto_increment PRIMARY KEY,
 first_name VARCHAR (16),
 last_name VARCHAR (16),
 id_card VARCHAR (18),
 information text,
 KEY name (first_name, last_name),
 FULLTEXT KEY (information),
 UNIQUE KEY (id_card)
);

2. 使用ALTER TABLE命令去增加索引

ALTER TABLE table_name ADD INDEX index_name (column_list);

ALTER TABLE 可用來建立普通索引、UNIQUE索引或PRIMARY KEY索引。

其中 table_name 是要增加索引的表名，column_list 指出對哪些列進行索引，多列時各列之間用逗號分隔。

索引名 index_name 可自己命名，預設時，MySQL將根據第一個索引列賦一個名稱。另外，ALTER TABLE允許在單個語句中更改多個表，因此可以在同時建立多個索引。

3. 使用CREATE INDEX命令建立。

CREATE INDEX index_name ON table_name (column_list);

7. 建立索引注意事項

選擇性低的欄位不要建立索引(例如，性別sex、狀態status)。
很少查詢的列不要建立索引(專案初期就要確定好)。
巨量資料型別欄位不要建立索引。
儘量避免不要使用NULL,應該指定列為NOTNULL(在MySQL中,含有空值的列很難進行查詢優化，它們會使得索引、索引的統計資訊及比較運算更加複雜。可以使用空字串代替空值)。

8. 使用索引一定可以提高查詢效能嘛

通常通過索引查詢資料比全表掃描要快。但是我們也必須注意到它的代價。索引需要空間來儲存，也需要定期維護，每當有記錄在表中增減或索引列被修改時，索引本身也會被修改。這意味著每條記錄的I* NSERT，DELETE，UPDATE將為此多付出4，5 次的磁碟I/O。因為索引需要額外的儲存空間和處理，那些不必要的索引反而會使查詢反應時間變慢。使用索引查詢不一定能提高查詢效能。