[[407616]]

本文轉(zhuǎn)載自微信公眾號(hào)「微觀技術(shù)」，作者Tom哥 。轉(zhuǎn)載本文請(qǐng)聯(lián)系微觀技術(shù)公眾號(hào)。

大家好，我是Tom哥~

我們都知道，業(yè)務(wù)開(kāi)發(fā)涉及到數(shù)據(jù)庫(kù)的SQL操作時(shí)，一定要 review 是否命中索引。否則，會(huì)走 全表掃描，如果表數(shù)據(jù)量很大時(shí)，會(huì)慢的要死。

假如命中了索引呢?是不是就不會(huì)有慢查詢?

殊不知，我們習(xí)以為常的常識(shí)有時(shí)也會(huì)誤導(dǎo)我們!

人生好難!

聊這個(gè)話題，要有一定技術(shù)基礎(chǔ)，需了解 B+ 樹(shù)的存儲(chǔ)結(jié)構(gòu)

如果不是很清楚的話，先看下之前一篇文章，有詳細(xì)介紹

面試題：mysql 一棵 B+ 樹(shù)可以存多少條數(shù)據(jù)?

1、工作準(zhǔn)備：建表，造數(shù)據(jù)

首先創(chuàng)建一張 user 表，并創(chuàng)建一個(gè) id的主鍵索引，和一個(gè) user_name 的普通索引。

CREATE TABLE `user` ( 
  `id` bigint(20) NOT NULL AUTO_INCREMENT, 
  `user_name` varchar(128) NOT NULL DEFAULT '' COMMENT '用戶名', 
  `age` int(11) NOT NULL  COMMENT '年齡', 
  `address` varchar(128) COMMENT '地址', 
   PRIMARY KEY (`id`), 
   key `idx_user_name` (user_name), 
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COMMENT='用戶表'; 

啟動(dòng)程序，往 user 表中插入 10000 條數(shù)據(jù)。

@GetMapping("/insert_batch") 
public Object insertBatch(@RequestParam("batch") int batch) { 
    for (int j = 1; j <= batch; j++) { 
        List<User> userList = new ArrayList<>(); 
        for (int i = 1; i <= 100; i++) { 
            User user = User.builder().userName("Tom哥-" + ((j - 1) * 100 + i)).age(29).address("上海").build(); 
            userList.add(user); 
        } 
        userMapper.insertBatch(userList); 
    } 
    return "success"; 
} 

2、慢查詢

在分析原因前，我們先來(lái)了解 mysql 慢查詢是什么?如何定義的?

慢查詢定義：

MySQL的慢查詢?nèi)罩臼荕ySQL提供的一種日志記錄，用來(lái)記錄在MySQL中響應(yīng)時(shí)間超過(guò)閥值的語(yǔ)句，具體指運(yùn)行時(shí)間超過(guò)long_query_time值的SQL，則會(huì)被記錄到慢查詢?nèi)罩局小?/p>

慢查詢相關(guān)參數(shù)：

• slow_query_log：是否開(kāi)啟慢查詢?nèi)罩荆?表示開(kāi)啟，0表示關(guān)閉。
• log-slow-queries：舊版(5.6以下版本)MySQL數(shù)據(jù)庫(kù)慢查詢?nèi)罩敬鎯?chǔ)路徑?？梢圆辉O(shè)置該參數(shù)，系統(tǒng)則會(huì)默認(rèn)給一個(gè)缺省的文件host_name-slow.log
• slow-query-log-file：新版(5.6及以上版本)MySQL數(shù)據(jù)庫(kù)慢查詢?nèi)罩敬鎯?chǔ)路徑?？梢圆辉O(shè)置該參數(shù)，系統(tǒng)則會(huì)默認(rèn)給一個(gè)缺省的文件host_name-slow.log
• long_query_time：慢查詢閾值，當(dāng)查詢時(shí)間高于設(shè)定的閾值時(shí)，記錄到日志
• log_queries_not_using_indexes：未使用索引的查詢也被記錄到慢查詢?nèi)罩局?可選項(xiàng))

默認(rèn)情況下slow_query_log的值為OFF，表示慢查詢?nèi)罩臼墙玫?，可以通過(guò)設(shè)置slow_query_log的值來(lái)開(kāi)啟，如下所示：

使用set global slow_query_log=1 開(kāi)啟了慢查詢?nèi)罩局粚?duì)當(dāng)前數(shù)據(jù)庫(kù)生效，如果MySQL重啟后則會(huì)失效。如果要永久生效，必須修改配置文件 my.cnf

long_query_time的默認(rèn)值為10 秒，支持二次修改。線上我們一般會(huì)設(shè)置成1秒，如果業(yè)務(wù)對(duì)延遲敏感的話，我們根據(jù)需要設(shè)置一個(gè)更低的值。

3、開(kāi)始實(shí)驗(yàn)

首先看下以下幾種場(chǎng)景的SQL語(yǔ)句執(zhí)行時(shí)，索引的命中情況。

1、執(zhí)行explain select * from user;，發(fā)現(xiàn) key 這列為NULL，說(shuō)明了沒(méi)有命中索引，走了全表掃描。

2、執(zhí)行 explain select * from user where id=10;，發(fā)現(xiàn) key 這列為 PRIMARY，說(shuō)明使用了主鍵索引。

3、執(zhí)行 explain select user_name from user;，發(fā)現(xiàn) key 這列為 idx_user_name，說(shuō)明使用了二級(jí)普通索引。

但是，實(shí)驗(yàn)發(fā)現(xiàn)，雖然走了二級(jí)索引，但是 rows 掃描行為 9968，說(shuō)明走了全表掃描。性能很差。

本文測(cè)試只造了 1W 條數(shù)據(jù)，如果線上環(huán)境有個(gè)千萬(wàn)級(jí)數(shù)據(jù)量，那估計(jì)要好幾秒才能響應(yīng)結(jié)果。

如果請(qǐng)求并發(fā)量很高，很容易引發(fā)數(shù)據(jù)庫(kù)連接無(wú)法及時(shí)釋放，導(dǎo)致客戶端無(wú)法獲取數(shù)據(jù)庫(kù)連接而報(bào)錯(cuò)。

4、命中索引，依然很慢

我們知道所有的數(shù)據(jù)都是存儲(chǔ)在 B+ 索引樹(shù)上，當(dāng)執(zhí)行 explain select * from user where id>0; 時(shí)，發(fā)現(xiàn)使用了主鍵索引。

mysql 優(yōu)化器根據(jù)主鍵索引找到第一個(gè) id>0 的值，雖然走了索引但其實(shí)還是全表掃描。

沒(méi)命中索引會(huì)走全表掃描，命中了索引也可能走全表掃描。

看來(lái)是否命中索引，并不是評(píng)判 SQL 性能好壞的唯一標(biāo)準(zhǔn)。

其實(shí)，還有一個(gè)重要指標(biāo)，那就是 掃描行數(shù)。

當(dāng)一個(gè)表很大時(shí)，不僅要關(guān)注是否有索引，還要關(guān)注索引的過(guò)濾性是否足夠好。

5、回表優(yōu)化

首先為user表 增加一個(gè) user_name 和 age 的聯(lián)合索引。

ALTER TABLE `user` ADD INDEX idx_user_name_age ( `user_name`,`age` ); 

執(zhí)行 explain select * from user where user_name like 'Tom哥-1%' and age =29;

執(zhí)行流程：

• ① 首先在 idx_user_name_age 索引樹(shù)，查找第一個(gè)以 Tom哥-1 開(kāi)頭的記錄對(duì)應(yīng)的主鍵id
• ② 根據(jù)主鍵id從主鍵索引樹(shù)找到整行記錄，并根據(jù)age做判斷過(guò)濾，等于29則留下，否則丟棄。這個(gè)過(guò)程也稱為回表
• ③ 然后，在 idx_user_name_age 聯(lián)合索引樹(shù)上向右遍歷，找到下一個(gè)主鍵id
• ④ 再執(zhí)行第二步
• ⑤ 后面重復(fù)執(zhí)行第三步、第四步，直到user_name不是以 Tom哥-1 開(kāi)頭，則結(jié)束
• ⑥ 返回所有查詢結(jié)果

分析：

由于按user_name 的前綴匹配，idx_user_name_age二級(jí)索引中的 age 部分并沒(méi)有發(fā)揮作用。導(dǎo)致了大量回表查詢，性能較差。

有什么優(yōu)化策略：

MySQL 5.6 版本引入一個(gè) Index Condition Pushdown Optimization

https://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html

優(yōu)化后，執(zhí)行流程：

• ① 首先在 idx_user_name_age 索引樹(shù)，查找第一個(gè)以 Tom哥-1 開(kāi)頭的索引記錄
• ② 然后，判斷這個(gè)索引記錄中的 age 是否等于 29。如果是，回表 取出整行數(shù)據(jù)，作為后面的結(jié)果返回;如果不是，則丟棄
• ③ 在 idx_user_name_age 聯(lián)合索引樹(shù)上向右遍歷，重復(fù)第二步，直到user_name不是以 Tom哥-1 開(kāi)頭，則結(jié)束
• ④ 返回所有查詢結(jié)果

跟上面的過(guò)程差別，在于判斷 age 是否等于 29 放在了遍歷聯(lián)合索引過(guò)程中進(jìn)行，不需要回表判斷，大大降低了回表的次數(shù)，提升性能。

當(dāng)然這個(gè)優(yōu)化依然沒(méi)有繞開(kāi)最左前綴原則，索引的過(guò)濾性仍然有提升空間。

這時(shí)，我們需要引入一個(gè)叫 虛擬列 的概念。

修改表結(jié)構(gòu)：

ALTER TABLE `user` add user_name_first varchar(12) generated always as  
(left(user_name,6)) , add index(user_name_first,age); 

執(zhí)行 explain select * from user where user_name_first like 'Tom哥-1%' and age =29;

比較發(fā)現(xiàn)，掃描行數(shù) row 變小了，證明優(yōu)化有效果。

6、寫在最后

slow_query_log 收集到的慢 SQL ，結(jié)合 explain 分析是否命中索引，結(jié)合掃描行數(shù)，有針對(duì)性的優(yōu)化慢 SQL。

但是要注意一點(diǎn)，慢 SQL 日志中也可能有正常的 SQL，可能只是當(dāng)時(shí)CPU等系統(tǒng)資源過(guò)載，影響到正常 SQL 的執(zhí)行速度。

簡(jiǎn)單來(lái)講，慢查詢和索引沒(méi)有必然聯(lián)系，一個(gè)SQL語(yǔ)句的執(zhí)行效率最終要看的是掃描行數(shù)。另外可以使用虛擬列和聯(lián)合索引來(lái)提升復(fù)雜查詢的執(zhí)行效率。