[導讀]我們都知道，業(yè)務開發(fā)涉及到數(shù)據(jù)庫的SQL操作時，一定要review是否命中索引。否則，會走全表掃描，如果表數(shù)據(jù)量很大時，會慢的要死。假如命中了索引呢？是不是就不會有慢查詢？殊不知，我們習以為常的常識有時也會誤導我們！人生好難！聊這個話題，要有一定技術(shù)基礎，需了解B樹的存儲結(jié)構(gòu)如果...
我們都知道，業(yè)務開發(fā)涉及到數(shù)據(jù)庫的SQL操作時，一定要 review 是否命中索引。否則，會走 全表掃描，如果表數(shù)據(jù)量很大時，會慢的要死。
假如命中了索引呢？是不是就不會有慢查詢？
殊不知，我們習以為常的常識有時也會誤導我們！人生好難！聊這個話題，要有一定技術(shù)基礎，需了解 B 樹的存儲結(jié)構(gòu)如果不是很清楚的話，先看下之前一篇文章，有詳細介紹面試題：mysql 一棵 B 樹可以存多少條數(shù)據(jù)？
1、工作準備：建表，造數(shù)據(jù)
首先創(chuàng)建一張 user 表，并創(chuàng)建一個
id的主鍵索引，和一個 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='用戶表';
啟動程序，往
user 表中插入 10000 條數(shù)據(jù)。@GetMapping("/insert_batch")
public?Object?insertBatch(@RequestParam("batch")?int?batch)?{
????for?(int?j?=?1;?j?<=?batch;?j )?{
????????List?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";
}
在分析原因前，我們先來了解 mysql 慢查詢是什么？如何定義的？慢查詢定義：
MySQL的慢查詢?nèi)罩臼荕ySQL提供的一種日志記錄，用來記錄在MySQL中響應時間超過閥值的語句，具體指運行時間超過long_query_time值的SQL，則會被記錄到慢查詢?nèi)罩局小?/p>慢查詢相關(guān)參數(shù)：
- slow_query_log：是否開啟慢查詢?nèi)罩荆?表示開啟，0表示關(guān)閉。
- log-slow-queries：舊版（5.6以下版本）MySQL數(shù)據(jù)庫慢查詢?nèi)罩敬鎯β窂健？梢圆辉O置該參數(shù)，系統(tǒng)則會默認給一個缺省的文件host_name-slow.log
- slow-query-log-file：新版（5.6及以上版本）MySQL數(shù)據(jù)庫慢查詢?nèi)罩敬鎯β窂??？梢圆辉O置該參數(shù)，系統(tǒng)則會默認給一個缺省的文件host_name-slow.log
- long_query_time：慢查詢閾值，當查詢時間高于設定的閾值時，記錄到日志
- log_queries_not_using_indexes：未使用索引的查詢也被記錄到慢查詢?nèi)罩局校蛇x項）
slow_query_log的值為OFF，表示慢查詢?nèi)罩臼墙玫?，可以通過設置slow_query_log的值來開啟，如下所示：使用set global slow_query_log=1 開啟了慢查詢?nèi)罩局粚Ξ斍皵?shù)據(jù)庫生效，如果MySQL重啟后則會失效。如果要永久生效，必須修改配置文件 my.cnf
long_query_time的默認值為10 秒，支持二次修改。線上我們一般會設置成1秒，如果業(yè)務對延遲敏感的話，我們根據(jù)需要設置一個更低的值。3、開始實驗
首先看下以下幾種場景的SQL語句執(zhí)行時，索引的命中情況。
1、執(zhí)行
explain select * from user;，發(fā)現(xiàn) key 這列為NULL，說明了沒有命中索引，走了全表掃描。2、執(zhí)行
explain select * from user where id=10;，發(fā)現(xiàn) key 這列為 PRIMARY，說明使用了主鍵索引。3、執(zhí)行
explain select user_name from user;，發(fā)現(xiàn) key 這列為 idx_user_name，說明使用了二級普通索引。但是，實驗發(fā)現(xiàn)，雖然走了二級索引，但是
rows 掃描行為 9968，說明走了全表掃描。性能很差。本文測試只造了 1W 條數(shù)據(jù)，如果線上環(huán)境有個千萬級數(shù)據(jù)量，那估計要好幾秒才能響應結(jié)果。如果請求并發(fā)量很高，很容易引發(fā)數(shù)據(jù)庫連接無法及時釋放，導致客戶端無法獲取數(shù)據(jù)庫連接而報錯。
4、命中索引，依然很慢
我們知道所有的數(shù)據(jù)都是存儲在 B 索引樹上，當執(zhí)行
explain select * from user where id>0; 時，發(fā)現(xiàn)使用了主鍵索引。mysql 優(yōu)化器根據(jù)主鍵索引找到第一個 id>0 的值，雖然走了索引但其實還是全表掃描。沒命中索引會走全表掃描，命中了索引也可能走全表掃描。
看來是否命中索引，并不是評判 SQL 性能好壞的唯一標準。其實，還有一個重要指標，那就是
掃描行數(shù)。當一個表很大時，不僅要關(guān)注是否有索引，還要關(guān)注索引的過濾性是否足夠好。5、回表優(yōu)化
首先為
user表 增加一個 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索引樹，查找第一個以Tom哥-1開頭的記錄對應的主鍵id - ② 根據(jù)主鍵id從主鍵索引樹找到整行記錄，并根據(jù)
age做判斷過濾，等于29則留下，否則丟棄。這個過程也稱為回表 - ③ 然后，在
idx_user_name_age聯(lián)合索引樹上向右遍歷，找到下一個主鍵id - ④ 再執(zhí)行第二步
- ⑤ 后面重復執(zhí)行第三步、第四步，直到
user_name不是以Tom哥-1開頭，則結(jié)束 - ⑥ 返回所有查詢結(jié)果
user_name 的前綴匹配，idx_user_name_age二級索引中的 age 部分并沒有發(fā)揮作用。導致了大量回表查詢，性能較差。有什么優(yōu)化策略：MySQL 5.6 版本引入一個 Index Condition Pushdown Optimizationhttps://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html優(yōu)化后，執(zhí)行流程：
- ① 首先在
idx_user_name_age索引樹，查找第一個以Tom哥-1開頭的索引記錄 - ② 然后，判斷這個索引記錄中的
age是否等于 29。如果是，回表取出整行數(shù)據(jù)，作為后面的結(jié)果返回；如果不是，則丟棄 - ③ 在
idx_user_name_age聯(lián)合索引樹上向右遍歷，重復第二步，直到user_name不是以Tom哥-1開頭，則結(jié)束 - ④ 返回所有查詢結(jié)果
age 是否等于 29 放在了遍歷聯(lián)合索引過程中進行，不需要回表判斷，大大降低了回表的次數(shù)，提升性能。當然這個優(yōu)化依然沒有繞開最左前綴原則，索引的過濾性仍然有提升空間。這時，我們需要引入一個叫 虛擬列 的概念。修改表結(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ù)，有針對性的優(yōu)化慢 SQL。但是要注意一點，慢 SQL 日志中也可能有正常的 SQL，可能只是當時CPU等系統(tǒng)資源過載，影響到正常 SQL 的執(zhí)行速度。簡單來講，慢查詢和索引沒有必然聯(lián)系，一個SQL語句的執(zhí)行效率最終要看的是掃描行數(shù)。另外可以使用虛擬列和聯(lián)合索引來提升復雜查詢的執(zhí)行效率。
下載文檔
