關(guān)于MySQL的SQL優(yōu)化之覆蓋索引
前些天，有個(gè)同事跟我說：“我寫了個(gè)SQL，SQL很簡單，但是查詢速度很慢，并且針對查詢條件創(chuàng)建了索引，然而索引卻不起作用，你幫我看看有沒有辦法優(yōu)化?”。
我對他提供的case進(jìn)行了優(yōu)化，并將優(yōu)化過程整理了下來。
我們先來看看優(yōu)化前的表結(jié)構(gòu)、數(shù)據(jù)量、SQL、執(zhí)行計(jì)劃、執(zhí)行時(shí)間等。
1. 表結(jié)構(gòu)：
- CREATE TABLE `t_order` (
- `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
- `order_code` char(12) NOT NULL,
- `order_amount` decimal(12,2) NOT NULL,
- PRIMARY KEY (`id`),
- UNIQUE KEY `uni_order_code` (`order_code`) USING BTREE
- ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
隱藏了部分不相關(guān)字段后，可以看到表足夠簡單， 并且在order_code上創(chuàng)建了唯一性索引uni_order_code。
2. 數(shù)據(jù)量：316977
這個(gè)數(shù)據(jù)量還是比較小的，不過如果SQL足夠差，一樣會查詢很慢。
3. SQL：
- select order_code, order_amount from t_order order by order_code limit 1000;
哇，SQL足夠簡單，不過有時(shí)候越簡單也越難優(yōu)化。
4. 執(zhí)行計(jì)劃：
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | t_order | ALL | NULL | NULL | NULL | NULL | 316350 | Using filesort |
全表掃描、文件排序，注定查詢慢!
那為什么MySQL沒有利用索引(uni_order_code)掃描完成查詢呢?因?yàn)镸ySQL認(rèn)為這個(gè)場景利用索引掃描并非***的結(jié)果。我們先來看下執(zhí)行時(shí)間，然后再來分析為什么沒有利用索引掃描。
5. 執(zhí)行時(shí)間：260ms
的確，執(zhí)行時(shí)間太長了，如果表數(shù)據(jù)量繼續(xù)增長下去，性能會越來越差。
我們來分析下MySQL為什么使用全表掃描、文件排序，而沒有使用索引掃描、利用索引順序：
1. 全表掃描、文件排序：
雖然是全表掃描，但是掃描是順序的(不管機(jī)械硬盤還是SSD順序讀寫性能都是高的)，并且數(shù)據(jù)量不是特別大，所以這部分消耗的時(shí)間應(yīng)該不是特別大，主要的消耗應(yīng)該是在排序上。
2. 利用索引掃描、利用索引順序：
uni_order_code是二級索引，索引上保存了(order_code,id)，每掃描一條索引需要根據(jù)索引上的id定位(隨機(jī)IO)到數(shù)據(jù)行上讀取order_amount，需要1000次隨機(jī)IO才能完成查詢，而機(jī)械硬盤隨機(jī)IO的效率是極低的(機(jī)械硬盤每秒尋址幾百次)。
根據(jù)我們自己的分析選擇全表掃描相對更優(yōu)。如果把limit 1000改成limit 10，則執(zhí)行計(jì)劃會完全不一樣。
既然我們已經(jīng)知道是因?yàn)殡S機(jī)IO導(dǎo)致無法利用索引，那么有沒有辦法消除隨機(jī)IO呢?
有，覆蓋索引。
我們來看看利用覆蓋索引優(yōu)化后的索引、執(zhí)行計(jì)劃、執(zhí)行時(shí)間。
1. 創(chuàng)建索引：
- ALTER TABLE `t_order`
- ADD INDEX `idx_ordercode_orderamount` USING BTREE (`order_code` ASC, `order_amount` ASC);
創(chuàng)建了復(fù)合索引idx_ordercode_orderamount(order_code,order_amount)，將select的列order_amount也放到索引中。
2. 執(zhí)行計(jì)劃：
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | t_order | index | NULL | idx_ordercode_ orderamount |
42 | NULL | 1000 | Using index |
執(zhí)行計(jì)劃顯示查詢會利用覆蓋索引，并且只掃描了1000行數(shù)據(jù)，查詢的性能應(yīng)該是非常好的。
3. 執(zhí)行時(shí)間：13ms
從執(zhí)行時(shí)間來看，SQL的執(zhí)行時(shí)間提升到原來的1/20，已經(jīng)達(dá)到我們的預(yù)期。
總結(jié)：
覆蓋索引是select的數(shù)據(jù)列只用從索引中就能夠取得，不必讀取數(shù)據(jù)行，換句話說查詢列要被所建的索引覆蓋。索引的字段不只包含查詢列，還包含查詢條件、排序等。
要寫出性能很好的SQL不僅需要學(xué)習(xí)SQL，還要能看懂?dāng)?shù)據(jù)庫執(zhí)行計(jì)劃，了解數(shù)據(jù)庫執(zhí)行過程、索引的數(shù)據(jù)結(jié)構(gòu)等。
