本文主要通過創(chuàng)建一個(gè)abcd 四字段的聯(lián)合索引來分析各個(gè)組合查詢場景下索引的使用情況，同時(shí)之前在做xqueue 項(xiàng)目時(shí)扫倡，在壓測中，發(fā)現(xiàn)因?yàn)閛rder by后的字段沒有使用索引竟纳，使得explain 中 ref 包含using filesort撵溃，性能一直壓不上去，進(jìn)而分析為什么會出現(xiàn)using filesort以及如何避免using filesort問題蚁袭。
準(zhǔn)備
1.創(chuàng)建test表（測試表）征懈。
drop table if exists test;
create table test(
id int primary key auto_increment,
c1 varchar(10),
c2 varchar(10),
c3 varchar(10),
c4 varchar(10),
c5 varchar(10)
) ENGINE=INNODB default CHARSET=utf8;
insert into test(c1,c2,c3,c4,c5) values('a1','a2','a3','a4','a5');
insert into test(c1,c2,c3,c4,c5) values('b1','b2','b3','b4','b5');
insert into test(c1,c2,c3,c4,c5) values('c1','c2','c3','c4','c5');
insert into test(c1,c2,c3,c4,c5) values('d1','d2','d3','d4','d5');
insert into test(c1,c2,c3,c4,c5) values('e1','e2','e3','e4','e5');
2.創(chuàng)建索引。
1.根據(jù)以下Case分析索引的使用情況
Case 1：
分析：
①創(chuàng)建復(fù)合索引的順序?yàn)閏1,c2,c3,c4揩悄。
②上述四組explain執(zhí)行的結(jié)果都一樣：type=ref，key_len=132鬼悠，ref=const,const,const,const删性。
結(jié)論：在執(zhí)行常量等值查詢時(shí)亏娜，改變索引列的順序并不會更改explain的執(zhí)行結(jié)果，因?yàn)閙ysql底層優(yōu)化器會進(jìn)行優(yōu)化蹬挺，但是推薦按照索引順序列編寫sql語句维贺。
Case 2：
分析：
當(dāng)出現(xiàn)范圍的時(shí)候，type=range巴帮，key_len=99溯泣，比不用范圍key_len=66增加了，說明使用上了索引榕茧，但對比Case1中執(zhí)行結(jié)果垃沦，說明c4上索引失效。
結(jié)論：范圍右邊索引列失效用押，但是范圍當(dāng)前位置（c3）的索引是有效的肢簿，從key_len=99可證明。
Case 2.1：
分析：
與上面explain執(zhí)行結(jié)果對比蜻拨，key_len=132說明索引用到了4個(gè)池充，因?yàn)閷Υ藄ql語句mysql底層優(yōu)化器會進(jìn)行優(yōu)化：范圍右邊索引列失效（c4右邊已經(jīng)沒有索引列了），注意索引的順序（c1,c2,c3,c4）缎讼，所以c4右邊不會出現(xiàn)失效的索引列收夸，因此4個(gè)索引全部用上。
結(jié)論：范圍右邊索引列失效血崭，是有順序的：c1,c2,c3,c4咱圆，如果c3有范圍，則c4失效功氨；如果c4有范圍序苏，則沒有失效的索引列，從而會使用全部索引捷凄。
Case 2.2：
分析：
如果在c1處使用范圍忱详，則type=ALL，key=Null跺涤，索引失效匈睁，全表掃描，這里違背了最佳左前綴法則桶错，帶頭大哥已死航唆，因?yàn)閏1主要用于范圍，而不是查詢院刁。
解決方式使用覆蓋索引糯钙。
結(jié)論：在最佳左前綴法則中，如果最左前列（帶頭大哥）的索引失效，則后面的索引都失效任岸。
Case 3：
分析：
利用最佳左前綴法則：中間兄弟不能斷再榄，因此用到了c1和c2索引（查找），從key_len=66享潜，ref=const,const困鸥，c3索引列用在排序過程中。
Case 3.1：
分析：
從explain的執(zhí)行結(jié)果來看：key_len=66剑按，ref=const,const疾就，從而查找只用到c1和c2索引，c3索引用于排序艺蝴。
Case 3.2：
分析：
從explain的執(zhí)行結(jié)果來看：key_len=66猬腰，ref=const,const，查詢使用了c1和c2索引吴趴，由于用了c4進(jìn)行排序漆诽，跳過了c3，出現(xiàn)了Using filesort锣枝。
Case 4：
分析：
查找只用到索引c1厢拭，c2和c3用于排序，無Using filesort撇叁。
Case 4.1：
分析：
和Case 4中explain的執(zhí)行結(jié)果一樣供鸠，但是出現(xiàn)了Using filesort，因?yàn)樗饕膭?chuàng)建順序?yàn)閏1,c2,c3,c4陨闹，但是排序的時(shí)候c2和c3顛倒位置了楞捂。
Case 4.2：
分析：
在查詢時(shí)增加了c5，但是explain的執(zhí)行結(jié)果一樣趋厉，因?yàn)閏5并未創(chuàng)建索引寨闹。
Case 4.3：
分析：
與Case 4.1對比，在Extra中并未出現(xiàn)Using filesort君账，因?yàn)閏2為常量繁堡，在排序中被優(yōu)化，所以索引未顛倒乡数，不會出現(xiàn)Using filesort椭蹄。
Case 5：
分析：
只用到c1上的索引，因?yàn)閏4中間間斷了净赴，根據(jù)最佳左前綴法則绳矩，所以key_len=33，ref=const玖翅，表示只用到一個(gè)索引翼馆。
Case 5.1：
分析：
對比Case 5割以，在group by時(shí)交換了c2和c3的位置，結(jié)果出現(xiàn)Using temporary和Using filesort写妥，極度惡劣拳球。原因：c3和c2與索引創(chuàng)建順序相反审姓。
總結(jié)：
通過以上Case的分析珍特，進(jìn)行如下總結(jié)：
①最佳左前綴法則。
在等值查詢時(shí)魔吐，更改索引列順序扎筒，并不會影響explain的執(zhí)行結(jié)果，因?yàn)閙ysql底層會進(jìn)行優(yōu)化酬姆。
在使用order by時(shí)嗜桌，注意索引順序、常量辞色，以及可能會導(dǎo)致Using filesort的情況骨宠。
②group by容易產(chǎn)生Using temporary。
③通俗理解口訣：
全值匹配我最愛相满，最左前綴要遵守层亿；
帶頭大哥不能死，中間兄弟不能斷立美；
索引列上少計(jì)算匿又，范圍之后全失效；
LIKE百分寫最右建蹄，覆蓋索引不寫星碌更；
不等空值還有or，索引失效要少用洞慎。
using filesort 分析
我們先建一個(gè)user表痛单，其中有自增主鍵、user_id 也建立索引劲腿，create_date暫時(shí)不建索引旭绒，省略其他字段。
CREATE TABLE `user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
`user_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '員工id',
`create_date` datetime NOT NULL DEFAULT '1970-01-01 00:00:00' COMMENT '創(chuàng)建日期',
省略其他字段
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`)
當(dāng)分頁查詢的時(shí)候谆棱，打印執(zhí)行計(jì)劃快压，Extra 一欄出現(xiàn)了 Using filesort。
explain SELECT * FROM user ORDER BY create_date DESC limit 20.40;
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
| 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 22686 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
Using filesort 是什么意思垃瞧？
官方的定義是蔫劣，MySQL must do an extra pass to find out how to retrieve the rows in sorted order. The sort is done by going through all rows according to the join type and storing the sort key and pointer to the row for all rows that match the WHERE clause . The keys then are sorted and the rows are retrieved in sorted order。
MySQL需要額外的一次傳遞个从，以找出如何按排序順序檢索行脉幢。通過根據(jù)聯(lián)接類型瀏覽所有行并為所有匹配WHERE子句的行保存排序關(guān)鍵字和行的指針來完成排序歪沃。然后關(guān)鍵字被排序，并按排序順序檢索行嫌松。標(biāo)紅沪曙，重點(diǎn)。
filesort 有兩種排序方式
- 對需要排序的記錄生成 <sort_key,rowid> 的元數(shù)據(jù)進(jìn)行排序萎羔，該元數(shù)據(jù)僅包含排序字段和rowid液走。排序完成后只有按字段排序的rowid，因此還需要通過rowid進(jìn)行回表操作獲取所需要的列的值贾陷，可能會導(dǎo)致大量的隨機(jī)IO讀消耗缘眶；
- 對需要排序的記錄生成 <sort_key,additional_fields> 的元數(shù)據(jù)，該元數(shù)據(jù)包含排序字段和需要返回的所有列髓废。排序完后不需要回表巷懈，但是元數(shù)據(jù)要比第一種方法長得多，需要更多的空間用于排序慌洪。
優(yōu)化方法
filesort 使用的算法是QuickSort顶燕，即對需要排序的記錄生成元數(shù)據(jù)進(jìn)行分塊排序，然后再使用mergesort方法合并塊冈爹。其中filesort可以使用的內(nèi)存空間大小為參數(shù) sort_buffer_size 的值涌攻，默認(rèn)為2M。當(dāng)排序記錄太多 sort_buffer_size 不夠用時(shí)犯助，mysql會使用臨時(shí)文件來存放各個(gè)分塊癣漆，然后各個(gè)分塊排序后再多次合并分塊最終全局完成排序〖谅颍可以增大 **sort_buffer_size **來解決 filesort 慢問題惠爽，也就是上面的第二種排序。
當(dāng) 排序元組中的extra列的總大小不超過 max_length_for_sort_data 系統(tǒng)變量值的時(shí)候瞬哼，我們?nèi)绾蝺?yōu)化 Using filesort 中的 回表操作 呢婚肆？
文件排序優(yōu)化不僅用于記錄排序關(guān)鍵字和行的位置，并且還記錄查詢需要的列坐慰。這樣可以避免兩次讀取行较性。
我們都知道，Mysql Innodb 下使用的是聚集索引结胀。PRIMARY KEY 的葉子節(jié)點(diǎn)存儲的是數(shù)據(jù)赞咙，其他索引的葉子節(jié)點(diǎn)存儲的是PRIMARY KEY.
當(dāng)我們使用非PRIMARY KEY 查詢的時(shí)候，查詢1會進(jìn)行回表操作糟港，也就是額外的一次查詢攀操，去查詢表中的其他數(shù)據(jù)，而查詢2會直接返回id和user_id秸抚。
查詢1：select * from user where user_id=1;
查詢2：select user_id,id from user where user_id=1;
好速和，下面我們通過例子來理解一下Using filesort 的形成
我們先來看第一條查詢歹垫，根據(jù)create_date 排序，由于create_date 沒有建索引颠放，
explain SELECT create_date,id FROM user ORDER BY create_date DESC;
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
| 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 22686 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
當(dāng)使用沒有索引字段的時(shí)候排惨，會出現(xiàn) Using filesort。那我們建立 create_date 索引之后呢碰凶，看下結(jié)果暮芭。
explain SELECT create_date,id FROM user ORDER BY create_date DESC;
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
| 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 22686 | Using index |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
如果我想把 user_id 也查詢出來呢，看下結(jié)果痒留。聚集索引谴麦，想查詢user_id 還是要進(jìn)行回表操作的蠢沿。
explain SELECT create_date,user_id FROM user ORDER BY create_date DESC;
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
| 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 22686 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
回到開篇伸头，我們?nèi)绾蝺?yōu)化這個(gè)查詢呢。
首先是盡量不要讓sql使用using filesort舷蟀。
select * from (select id from user order by create_date DESC limit 20.40) a left join user b on a.id=b.id;
利用mysql聚集索引的性質(zhì)恤磷，分頁查詢id，避免了Using filesort野宜，這個(gè)查詢是很快的扫步。而在分頁的數(shù)據(jù)量上，再去查詢所有數(shù)據(jù)匈子，性能就很高了河胎。
如果必須使用using filesort，無法避免排序操作時(shí)虎敦，很顯然應(yīng)該盡可能讓 MySQL 選擇使用第二種單路算法來進(jìn)行排序游岳。這樣可以減少大量的隨機(jī)IO操作，很大幅度地提高排序工作的效率其徙。
- 加大 max_length_for_sort_data 參數(shù)的設(shè)置
當(dāng)所有返回字段的最大長度小于這個(gè)參數(shù)值時(shí)胚迫，MySQL 就會選擇改進(jìn)后的單路排序，反之唾那，則選擇老式的雙路排序访锻。所以，如果有充足的內(nèi)存讓MySQL 存放須要返回的非排序字段闹获，就可以加大這個(gè)參數(shù)的值來讓 MySQL 選擇使用改進(jìn)版的排序算法期犬。 - 去掉不必要的返回字段
當(dāng)內(nèi)存不是很充裕時(shí)，不能簡單地通過強(qiáng)行加大上面的參數(shù)來強(qiáng)迫 MySQL 去使用改進(jìn)版的排序算法避诽，否則可能會造成 MySQL 不得不將數(shù)據(jù)分成很多段龟虎，然后進(jìn)行排序，這樣可能會得不償失茎用。此時(shí)就須要去掉不必要的返回字段遣总，讓返回結(jié)果長度適應(yīng) max_length_for_sort_data 參數(shù)的限制睬罗。 - 增大 sort_buffer_size 參數(shù)
設(shè)置增大 sort_buffer_size 并不是為了讓 MySQL選擇改進(jìn)版的排序算法，而是為了讓MySQL盡量減少在排序過程中對需要排序的數(shù)據(jù)進(jìn)行分段旭斥，因?yàn)榉侄螘斐?MySQL 使用臨時(shí)表來進(jìn)行交換排序容达。
參考鏈接：
mysql索引常見面試題
Mysql 查詢優(yōu)化之 Using filesort
ＭySQL 中 Using filesort 問題的優(yōu)化方法
