MySQL 索引進階指南：深入探秘關鍵知識點
在 MySQL 的浩瀚世界中，索引猶如高效查詢的魔法鑰匙，為數(shù)據(jù)的快速檢索和操作打開便捷之門。當我們對 MySQL 索引有了初步認知后，是時候踏上進階之旅，深入挖掘那些隱藏在背后、更具深度和復雜性的索引知識點。
在這篇文章中，我們將一同突破常規(guī)理解的局限，去探索 MySQL 索引更為精妙和細微之處。從索引的高級特性到復雜場景下的運用策略，從性能優(yōu)化的關鍵要點到可能遇到的疑難問題解析，每一個知識點都將如拼圖般為你構建起更為完整和強大的索引知識體系。無論你是經(jīng)驗豐富的開發(fā)者，還是正在進階道路上努力前行的技術探索者，都將在這里收獲新的啟迪和寶貴的見解。讓我們開啟這場精彩的進階之旅，一同揭開 MySQL 索引的神秘面紗，釋放其更為強大的力量。
一、詳解創(chuàng)建高性能的索引準則
1. 前綴索引的選擇
使用前綴的索引的重要原則就是用盡可能小的前綴獲取最高校的查詢性能，例如我們現(xiàn)在有下面這樣一張表。
CREATE TABLE db1.city (
city varchar(50) NULL
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8
COLLATE=utf8_general_ci;表中會有這樣的數(shù)據(jù)，讀者可以按需下面的腳本創(chuàng)建隨機的幾百萬條:
INSERT INTO db1.city (city) VALUES('London');
INSERT INTO db1.city (city) VALUES('Hiroshima');
INSERT INTO db1.city (city) VALUES('teboksary');
INSERT INTO db1.city (city) VALUES('pak kret');
INSERT INTO db1.city (city) VALUES('yaound');
INSERT INTO db1.city (city) VALUES('tel aviv-jaffa');
INSERT INTO db1.city (city) VALUES('Shimoga');
INSERT INTO db1.city (city) VALUES('Cabuyao');
....```
執(zhí)行上述腳本之后，我們不妨看看表的數(shù)據(jù)分布情況
```sql
select count(*) as c,city from city group by city; 最終輸出比重如下：
66 London
50 Hiroshima
49 teboksary
50 pak kret
50 yaound
48 tel aviv-jaffa
48 Shimoga
46 Cabuyao
46 Callao
46 Bislig由于city字段存在大量的重復，所以我們選擇前綴索引，通過前綴索引的方式實現(xiàn)最盡可能小的長度區(qū)分盡可能多的數(shù)據(jù)，從而做到高效查詢且解決索引維護的開銷。
對此，我們提出了這樣一種做法,首先我們先算出city列的基數(shù)，查看不重復列所占用所有數(shù)據(jù)的比值是多少:
select count(distinct city)/count(*) from city;輸出結果如下，說明完全不重復的city僅僅占用2%，所以我們創(chuàng)建的前綴索引的基數(shù)要盡可能接近這個值，才能做到數(shù)據(jù)區(qū)分最大化:
所以我們截取不同的長度的前綴計算基數(shù)的值:
select
count(distinct left(city,1))/count(*) as sel1,
count(distinct left(city,2))/count(*) as sel2,
count(distinct left(city,3))/count(*) as sel3,
count(distinct left(city,4))/count(*) as sel4,
count(distinct left(city,5))/count(*) as sel5,
count(distinct left(city,6))/count(*) as sel6,
count(distinct left(city,7))/count(*) as sel7
from city;最終我們輸出結果如下，可以看到選擇長度為3的時候，基數(shù)就和完整列的值一樣了，所以我們的前綴索引長度設置為3即可：
CREATE INDEX city_idx ON city (city(3));需要注意的是，我們使用前綴索引進行查詢時，MySQL是無法使用前綴索引進行group by和order by的，所以有涉及這種查詢的讀者需要注意一下使用場景。
2. 索引順序的設計
在不考慮排序和分組的情況下，涉及多列查詢的sql我們建議使用多列索引，而創(chuàng)建多列索引的原則也能很簡單，將選擇性比較大的列放在最前面即可。
為了完成這個實驗，我們可創(chuàng)建下面這張表：
CREATE TABLE `payment` (
`payment_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
`customer_id` SMALLINT UNSIGNED NOT NULL,
`staff_id` TINYINT UNSIGNED NOT NULL,
`rental_id` INT DEFAULT NULL,
`amount` DECIMAL(5,2) NOT NULL,
`payment_date` DATETIME NOT NULL,
`last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`payment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;然后我們使用腳本或者別的方式創(chuàng)建100w條數(shù)據(jù)并插入到數(shù)據(jù)庫中。
因為我們的查詢sql需要用到customer_id和staff_id作為查詢條件，所以我們希望為這兩列字段創(chuàng)建組合索引，所以我們使用了如下sql語句獲取這兩列的基數(shù)。
select count(*) as total,
count(distinct customer_id)/count(*) customer_id,
count(distinct staff_id)/count(*) staff_id
from payment;可以看到，customer_id基數(shù)更大，區(qū)分度更高，所以我們建議customer_id放在前面。
最終我們的創(chuàng)建如下索引，感興趣的讀者可以將兩者位置調換一下，查看百萬級別數(shù)據(jù)性能。
CREATE INDEX idx ON payment ( customer_id, staff_id );以下便是筆者的查詢sql，可以看到執(zhí)行計劃走了索引。
select * from payment where staff_id=1 and customer_id=1;
而且查詢時間為125ms左右：
有的讀者可能不相信筆者的思路，我們不妨將索引順序反過來。
DROP INDEX idx ON payment;
CREATE INDEX idx ON payment ( staff_id , customer_id);我們還是用同樣的sql，可以看到執(zhí)行時間變長了，這還是1w條數(shù)據(jù)的情況，如果達到百萬級別想想更是災難。
3. 巧用索引順序來排序
如果我們查詢的時用的order by和索引順序是一致的，而且查詢時還是索引覆蓋的話，那么我們就可以認為這是一個良好的設計。 使用索引排序同樣遵循最左匹配原則，而且在多表查詢時用到的永遠是第一張表的索引。當然這里也有一些特殊情況，筆者會在后文中詳細闡述。
為了完成實驗，筆者創(chuàng)建了下面這樣一張數(shù)據(jù)表(注意這個下面的唯一索引UNIQUE KEY，筆者后續(xù)的查詢都會基于這個唯一索引完成)，并插入幾條數(shù)據(jù)。
CREATE TABLE rental (
rental_id INT NOT NULL AUTO_INCREMENT,
rental_date DATETIME NOT NULL,
inventory_id MEDIUMINT UNSIGNED NOT NULL,
customer_id SMALLINT UNSIGNED NOT NULL,
return_date DATETIME DEFAULT NULL,
staff_id TINYINT UNSIGNED NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (rental_id),
UNIQUE KEY (rental_date,inventory_id,customer_id),
KEY idx_fk_inventory_id (inventory_id),
KEY idx_fk_customer_id (customer_id),
KEY idx_fk_staff_id (staff_id),
)ENGINE=InnoDB DEFAULT CHARSET=utf8;對應的腳本都在這個倉庫，有需要的讀者可以自取，注意選用MySQL版本的：https://github.com/ivanceras/sakila](https://github.com/ivanceras/sakila
先來看看下面這個語句，這就是我們上文所說的特殊情況，請問這條sql會走索引嗎？
select *
from
rental r
where
rental_date = '2005-5-25'
order by
inventory_id ,
customer_id ;答案是會的，我們用explain可以看到這條語句用到了rental_date，原因也很簡單，我們的唯一索引順序為rental_date,inventory_id,customer_id,所以我們的where條件中帶有rental_date是個常量查詢(這里可以理解為等于號的查詢)，而且order條件方向順序一致，使得where+order符合最左匹配原則，所以最終走了索引，而且extra也沒用出現(xiàn)filesort。
我們上面提到order順序不一致，或者where+order用的列不符合最左匹配原則查詢效率會降低，并且會走文件排序，我們不妨寫個sql印證一下。
先看看排序方向不一致的，如下所示，可以看到一個降序加一個升序，最終執(zhí)行計劃就是用了文件排序。
在看看where+order不符合最左匹配原則的情況，同樣走了文件排序。
了解了特殊情況之后，我們再來看看一些常規(guī)的情況。如下所示，這條sql where+order符合最左匹配原則，所以走了索引。
explain select *
from
rental r
where
rental_date = '2005-5-25'
order by
inventory_id ;輸出結果如下：
id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra|
--+-----------+-----+----------+----+-------------+-----------+-------+-----+----+--------+-----+
1|SIMPLE |r | |ref |rental_date |rental_date|5 |const| 1| 100.0| |當然符合最左匹配原則并不意味著只要列符合最左前綴即可，如下所示，如果第一個列出現(xiàn)范圍查詢則索引就直接失效了。
explain select *
from
rental r
where
rental_date > '2005-5-25'
order by
inventory_id ,customer_id ;輸出結果如下，可以看到直接using where且文件排序，還不走索引
id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows |filtered|Extra |
--+-----------+-----+----------+----+-------------+---+-------+---+-----+--------+---------------------------+
1|SIMPLE |r | |ALL |rental_date | | | |15840| 50.0|Using where; Using filesort|同樣的排序時，如果用到了非索引的列也會使得排序變?yōu)槲募判颍?/p>
最后我們再來看一個聯(lián)結查詢的例子,首先我們建了個表再插入數(shù)據(jù)，腳本都在上方倉庫讀者可以自行獲取，筆者這里為了省事把所有外鍵的定義都刪了。
CREATE TABLE film_actor (
actor_id SMALLINT UNSIGNED NOT NULL,
film_id SMALLINT UNSIGNED NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (actor_id,film_id),
KEY idx_fk_film_id (`film_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE film (
film_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
description TEXT DEFAULT NULL,
release_year YEAR DEFAULT NULL,
language_id TINYINT UNSIGNED NOT NULL,
original_language_id TINYINT UNSIGNED DEFAULT NULL,
rental_duration TINYINT UNSIGNED NOT NULL DEFAULT 3,
rental_rate DECIMAL(4,2) NOT NULL DEFAULT 4.99,
length SMALLINT UNSIGNED DEFAULT NULL,
replacement_cost DECIMAL(5,2) NOT NULL DEFAULT 19.99,
rating ENUM('G','PG','PG-13','R','NC-17') DEFAULT 'G',
special_features SET('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (film_id),
KEY idx_title (title),
KEY idx_fk_language_id (language_id),
KEY idx_fk_original_language_id (original_language_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;如下所示，這句sql理論上是可以走索引的，但MySQL優(yōu)化器將film_actor當作第二張表導致order by無法使用索引。
explain select * from film_actor inner join film using(film_id)order by actor_id ;所以我們看到了這樣的輸出，非常低效。
最后我們總結一下使用排序的原則:
- order by順序方向一致。
- where+order要符合最左匹配原則。
- where條件不要用范圍查詢。
- 多表聯(lián)查是觀察MySQL優(yōu)化器會不會做一些奇奇怪怪的優(yōu)化。
避免創(chuàng)建冗余和重復索引
有時因為開發(fā)人員對于數(shù)據(jù)庫的立即偏差會創(chuàng)建出一些冗余的索引，如下所示:
CREATE table t1(
id int not null primary key,
name varchar(20),
unique(id),
index(id)
)engine=innodb;實際上主鍵、unique、index的關系如下圖所示，由于開發(fā)對于三者關系的不了解，導致了創(chuàng)建了兩個沒有必要的索引，所以我們日常還是需要多留心一下這些問題:
還有一種情況我們也必須要了解一下，有時候我們?yōu)榱颂岣叨嗔胁樵兊男蕰?chuàng)建組合索引。例如我們的sql語句為
select * from t where a=123 and b='aa'這種情況下我們肯定會為了a、b創(chuàng)建索引。
如此一來，按照最左匹配原則，我們就無需單獨為字段a創(chuàng)建一個索引。
select * from t where a=123;如下圖可以看到，單獨使用a作為查詢條件時a也會走我們創(chuàng)建的組合索引。
但是某些場景之下，我們可能又會寫出這樣一條sql，所以我們還是要為(b,a)創(chuàng)建一條索引，請問該索引是冗余索引嗎？
select * from t where b='aa3' and a=12答案不是的，原因很簡單，盡管SQL優(yōu)化器會讓上述SQL走組合索引(a,b)，但是我們單獨以b作為查詢條件時，是走不了組合索引(a,b)的，所以我們創(chuàng)建組合索引(b,a)并不算冗余索引。
二、優(yōu)化特定類型的查詢性能
1. 為什么查詢性能會慢
我們首先了解一下一條sql的生命周期:
- 客戶端向服務端建立連接，并將sql發(fā)送給服務端、
- 服務端進行語法解析，查看語法是否存在問題。
- 生成執(zhí)行計劃。
- 服務端執(zhí)行sql。
- 將sql執(zhí)行結果返回給客戶端。
這其中我們不難發(fā)現(xiàn)，執(zhí)行是最重要的一環(huán)，造成查詢慢的重要部分基本都是執(zhí)行，其原因基本都是訪問的數(shù)據(jù)量太大，或者一次需要篩選出大量的數(shù)據(jù)。
所以，對于這類問題，我們解決方向基本是:
避免檢索沒必要的行。
盡可能避免查詢大量的數(shù)據(jù)，對于某些查詢，我們建議使用分頁查詢的方式。
很多人可能認為某一些查詢慢的問題也出現(xiàn)在客戶端和服務端建立連接和斷開連接這一部分，實際上MySQL的設計很少會出現(xiàn)這些問題，在某些版本的MySQL中對于簡單查詢，它支持1秒10w次，即使是千兆網(wǎng)卡，這一個數(shù)字也基本是2000左右，所以對于現(xiàn)如今的服務器配置，這里的開銷基本可以忽略。
2. 優(yōu)化特定類型的查詢
下面筆者會介紹一些常見的錯誤類型的特定類型查詢，我們先來說一個常見的查詢，count，count常用于統(tǒng)計某列非null的總數(shù)量，所以某些情況下，我們可能常用于統(tǒng)計列的總數(shù)。所以我們統(tǒng)計數(shù)據(jù)庫的列數(shù)時可能會寫出這樣一句sql
--用主鍵統(tǒng)計數(shù)據(jù)庫行數(shù)
select count(rental_id) from rental r ; 實際上，我們使用count()就行了，很多人認為count(*)會擴展出所有的列造成性能問題，實際恰恰相反，count(*)不僅不會擴展所有的列，而且也能統(tǒng)計出當前表中所有的行。所以對于要統(tǒng)計的sql語句我們更簡易使用count(),不僅更能清晰表達意圖還有更不錯的性能表現(xiàn)。
對于count我們也可以用于某列的歸類操作，例如我們希望查詢出顏色為藍色或者紅色的行的數(shù)量。我們的數(shù)據(jù)如下所示，可以看到顏色為3紅4藍1空。
所以我們可能會用到這樣一條sql，但是我們不想為此多寫一列。
select count(*),color from item i group by color ;所以我們用到了這樣一條sql，但是用到了函數(shù)嵌套很不直觀
select sum(if(color='blue',1,0)) as blue ,sum(if(color='red',1,0)) as red from item;其實我們運用count統(tǒng)計非null的特性，就可以寫出這面這樣一條精致的sql
select count(color='blue' or null) as blue,count(color='red' or null) as red from item;可以看到查詢結果也符合預期。
最后我們再來說說union，如果我們能夠保證union的數(shù)據(jù)是不重復，我們還是建議使用union all，如下所示:
explain select rental_id from rental where inventory_id <10000
union
select rental_id from rental where inventory_id >10000使用union因為需要去重的緣故，導致兩個查詢結果進行拼接操作時用到了temporary即外部排序，該操作就會創(chuàng)建臨時表并且還會對臨時表作唯一性檢查，即distinct操作，這就使得這句sql代價非常高。
1 PRIMARY rental range rental_date,idx_fk_inventory_id idx_fk_inventory_id 3 16215 100.0 Using where; Using index
2 UNION rental range rental_date,idx_fk_inventory_id idx_fk_inventory_id 3 1 100.0 Using where; Using index
UNION RESULT <union1,2> ALL Using temporary所以如果我們有辦法或者說查詢結果絕對不重復，我們還是建議使用下面這段sql
explain select rental_id from rental where inventory_id <10000
union all
select rental_id from rental where inventory_id >10000從執(zhí)行計劃我們就可以看出，在拼接操作時，因為無需考慮重復就避免了創(chuàng)建臨時表和distinct去重的操作了。
id|select_type|table |partitions|type |possible_keys |key |key_len|ref|rows |filtered|Extra |
--+-----------+------+----------+-----+-------------------------------+-------------------+-------+---+-----+--------+------------------------+
1|PRIMARY |rental| |range|rental_date,idx_fk_inventory_id|idx_fk_inventory_id|3 | |16215| 100.0|Using where; Using index|
2|UNION |rental| |range|rental_date,idx_fk_inventory_id|idx_fk_inventory_id|3 | | 1| 100.0|Using where; Using index|
