數(shù)據(jù)庫的查詢性能一直是性能優(yōu)化的重災(zāi)區(qū)。近期看了丁奇老師的分享“為什么SQL語言使用了索引，但卻還是慢查詢？”，收獲頗豐，以此做個記錄
0 準(zhǔn)備
創(chuàng)建一張測試表，設(shè)置主鍵索引(id)和普通索引(a) ，如下：
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
插入兩行測試數(shù)據(jù)
insert into t values(1,1,1),(2,2,2);
1 定義慢查詢
我們來看一下官網(wǎng)對慢查詢的定義
The slow query log consists of SQL statements that take more than
long_query_timeseconds to execute and require at leastmin_examined_row_limitrows to be examined.
mysql的慢查詢與語句的執(zhí)行時間有關(guān)，mysql會將語句的執(zhí)行時間與系統(tǒng)設(shè)置的long_query_time這個參數(shù)做對比，如果執(zhí)行時間time > long_query_time并且掃描的行數(shù)超過min_examined_row_limit， 則會被認為是慢查詢，并將該語句到慢查詢?nèi)罩局小?/p>
查看mysql的long_time_time設(shè)置
mysql > show variables like 'long_query_time%';
## 如下所示，慢查詢設(shè)置的時間為10秒
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
查看慢查詢?nèi)罩镜牡刂?/p>
mysql > show variables like '%slow_query_log%';
## slow_query_log 表示慢查詢?nèi)罩臼欠耖_啟
## slow_query_log_file 表示慢查詢?nèi)罩镜牡刂?+---------------------+-------------------------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /usr/local/mysql/data/ali-186590d5fadf-slow.log |
+---------------------+-------------------------------------------------+
2 定義是否使用索引
對于一個語句，是否使用索引的意思是：這個語句在執(zhí)行過程中是否使用到了索引。具體到表象中，是explain一個語句的時候，輸出結(jié)果里key的值不是NULL
mysql > explain select * from t ;
## 如下key為null，表示未使用索引
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 2 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
mysql> explain select * from t where id =2;
## 如下key為primary，表示使用了主鍵索引
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | t | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
mysql> explain select a from t ;
## 如下key為a，表示使用了a這個普通索引
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t | index | NULL | a | 5 | NULL | 2 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
看上面的結(jié)果可以知道，2和3都使用了索引，但是他們的掃描行數(shù)(rows這一個字段)是不同的，2使用主鍵索引其掃描行數(shù)為1條，3使用了普通索引其掃描的行數(shù)是2條，由于總記錄數(shù)是2條，所以3的方式幾乎是全表掃描了。如果總記錄數(shù)達到100萬條，2的方式還是會很快，而3的方式其掃描的行數(shù)也將達到100萬條，查詢就會很慢。
但是這并不代表2的方式肯定不是慢查詢，因為在極端情況下，如果mysql所在機器的cpu負載很高，2方式查詢的執(zhí)行時間超過了long_query_time，那么它就會被記錄到慢查詢?nèi)罩局小?/p>
3. 慢查詢與索引的關(guān)系
看了上述1，2就明白了，是否是慢查詢與是否使用索引其實并無關(guān)系
慢查詢： 慢查詢與語句的執(zhí)行時間相關(guān)，當(dāng)語句的執(zhí)行時間超過long_query_time，那么它就是慢查詢
索引： 是否使用索引描述了語句的執(zhí)行過程
4. 解析使用索引
在InnoDB中，數(shù)據(jù)都是存儲在主鍵索引上的，如圖所示。一行的所有信息都存儲在主鍵索引上（所以InnoDB在查詢過程中必然會掃描主鍵索引樹），而普通索引上只記錄了普通索引與對應(yīng)主鍵索引的映射關(guān)系
那么在語句執(zhí)行過程中，使用了主鍵索引，就一定不是全表掃描嗎？ 不一定，如下，雖然key為primary了，但是掃描行數(shù)還是全表掃描。優(yōu)化器認為該語句需要使用主鍵索引定位到id>0的行，所以認為該執(zhí)行使用了主鍵索引，
mysql> explain select * from t where id >0;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | t | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
因此，我們需要理清幾個概念：
全表掃描： 我們平時說的使用了全表掃描，指的并不是說語句不使用索引，而是該語句遍歷了整個主鍵索引樹 (select * from t where id > 0)
全索引掃描：指的是語句掃描了整個普通索引樹（select a from t ）
索引掃描： 指的是語句使用了索引的快速搜索功能，并且有效地減少了掃描行數(shù)(select * from t where id =2)
4.1 索引的過濾性
除了全索引掃描，還有哪些使用了索引但執(zhí)行速度不夠快的例子呢？
假設(shè)現(xiàn)在你維護了一張表記錄了全國人的基本信息，需要查找所有年齡在10~15歲之間的姓名和基本信息。該語句為：
select * from t_people where age between 10 and 15
一般我們會認為這樣的語句需要在age上建立索引，否則它就會變成全表掃描。但是最后你會發(fā)現(xiàn)，即使在age上建立了索引，執(zhí)行的速度還是很慢，因為符合條件了記錄數(shù)有上億行。該語句的執(zhí)行過程如下：
- 在age索引上找到age=10的第一個索引
- 根據(jù)索引對應(yīng)的主鍵id，回表到主鍵索引上取整行數(shù)據(jù)
- 從age索引繼續(xù)向下搜索，重復(fù)步驟2，直到碰到age > 15結(jié)束
由于上述的執(zhí)行過程中符合條件的記錄數(shù)超過上億行，因此執(zhí)行的時間會很長，它會被記錄到慢查詢中。而上述select a from t雖然也是全索引掃描，但是還是比這個全索引掃描的要快，因此它只掃描了2行。因此，語句執(zhí)行速度的快慢與掃描的行數(shù)息息相關(guān)，所以我們在討論索引的時候關(guān)心的是掃描行數(shù)，因此索引要有效，就要提高索引的過濾性，減少語句的掃描行數(shù)。上述的age索引過濾性就不夠。
那么過濾性足夠好了，掃描的行數(shù)就一定少呢？
如下有一個(name,age)聯(lián)合索引，如果執(zhí)行語句是 select * from t_people where name ='張三' and age = 8，那么該聯(lián)合索引的過濾性非常不錯，掃描的行數(shù)也會很少，執(zhí)行的效率較高
但是查詢的過濾性和索引的過濾性不一定是一樣的，如果現(xiàn)在的需求是
select * from t_prople where name like '張%' and age =8
在mysql 5.5及之前的版本中，這個語句的執(zhí)行流程如下：
- 在聯(lián)合索引上查找第一個名字為張的聯(lián)合索引
- 取出聯(lián)合索引對應(yīng)的主鍵id，回表到主鍵索引中獲取整行速度并對比age是否為8
- 繼續(xù)在聯(lián)合索引中向下掃描，重復(fù)2，直到碰到第一個名字不為張結(jié)束
可以看到，上述的執(zhí)行過程中，最耗費時間的就是2步驟的回表。假設(shè)全國第一個名字開頭是張的有8000萬，那么這個執(zhí)行過程就需要回表8000萬次。為什么聯(lián)合索引需要回表取判斷age是否為8？ 這是因為在使用聯(lián)合索引的時候，只能使用聯(lián)合索引的最左前綴，這被稱為最左前綴原則。
在mysql 5.6版本引入了一個優(yōu)化方案，該優(yōu)化方案是在聯(lián)合索引中先判斷age是否為8（將判斷下推到索引搜索過程中），如果是才回表取出整行數(shù)據(jù)，假設(shè)名字開頭是張的有8000萬，其中age=8的有1000萬行，那么該查詢只需要回表1000萬次，這會大大減少回表的次數(shù)。
但是該查詢還是要掃描8000萬行的記錄，是否可以繼續(xù)優(yōu)化，減少掃描行數(shù)呢？
我們可以把名字的第一個字和年齡做一個聯(lián)合索引，我們可以使用mysql 5.7引入的新特性：虛擬列，來實現(xiàn)
alter table t_people add name_first varchar(2) generated always as (left(name,1)), add index(name_first,age);
如上，虛擬列name_first永遠自動生成并等于name列的前兩個字節(jié)。并根據(jù)虛擬列生成了一個新的聯(lián)合索引(name_first,age)。有了這個新的聯(lián)合索引，我們就可以通過以下語句來優(yōu)化執(zhí)行語句
select * from t_prople where name_first = '張' and age =8
假設(shè)結(jié)果是1000萬行，那么該語句只需要掃描1000萬行，回表1000萬次。這個優(yōu)化的本質(zhì)是我們創(chuàng)建了一個過濾性更好的索引。
我們使用索引，或者說索引優(yōu)化的過程，實質(zhì)上是減少掃描行數(shù)的過程
5. 問題
5.1 什么是SQL的執(zhí)行時間 ？
sql語句的執(zhí)行時間并不包含其獲取鎖的等待時間，而且慢查詢?nèi)罩静迦氲臅r間是在sql語句釋放所有鎖之后，因此日志順序和執(zhí)行順序很可能是不同的
5.2 我在where中使用了普通索引字段，系統(tǒng)一定會使用全索引掃描嗎？
不一定，系統(tǒng)可能會走全表掃描，這取決與系統(tǒng)的判斷。判斷來源于系統(tǒng)的預(yù)測，也就是說，如果要走 c 字段索引的話，系統(tǒng)會預(yù)測走 c 字段索引大概需要掃描多少行。如果預(yù)測到要掃描的行數(shù)很多，它可能就不走索引而直接掃描全表了。
5.3 如果查詢的字段上有索引，查詢語句一定會使用索引掃描嗎？
不一定，查詢優(yōu)化器在選擇查詢計劃的時候，會對不同查詢計劃進行評估（通過一個cost function），選擇它認為執(zhí)行最快的查詢計劃。
5.4 優(yōu)化器使用的索引不滿意，可以自行指定sql使用的索引嗎？
是可以的，使用use index來指定sql執(zhí)行使用的索引，這是一種優(yōu)化手段，具體可看： https://www.cnblogs.com/edwardlost/archive/2010/12/31/1923105.html
6. 本文摘錄及參考自：
###1. The Slow Query Log - MySQL :: Developer Zone
###2. 為什么我加了索引,SQL執(zhí)行還是這么慢*(一)? - 李佳霖i - 博客園
###3. https://www.zhihu.com/question/49738281?sort=created
