索引優(yōu)化排序
InnoDB和MyISAM的數(shù)據(jù)分布對比
了解存儲的數(shù)據(jù)結(jié)構(gòu)有助于理解索引優(yōu)化排序是否有效的場景
MyISAM 按照數(shù)據(jù)插入的順序存儲在磁盤上秦踪。
二級索引：葉子節(jié)點存儲的是行指針
InnoDB支持聚簇索引
聚簇索引: 葉子節(jié)點存儲主鍵值烟零、剩余其他咧蕉堰、事務(wù)id台谢、用于事務(wù)和MVCC的回滾指針张遭。
二級索引（非聚簇索引）：葉子節(jié)點存儲的是主鍵值莽龟，并以此作為指向行的“指針”蠕嫁。相比于存儲行指針會占用更多空間，好處是在移動行時不需要去更新二級索引中的這個指針轧房。
InnoDB和MyISAM的索引及數(shù)據(jù)存儲結(jié)構(gòu)
使用索引掃描來排序
在MySQL中拌阴，如果where子句和order by子句符合索引排序的規(guī)則，可以有效避免filesort級別排序（成本高）奶镶。
使用索引解決order by問題的情況
-- 假設(shè)對于tableA存在索引index on (key_part1, key_part2)
CREATE TABLE `tableA` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`key_part1` int(11) DEFAULT NULL,
`key_part2` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index` (`key_part1`,`key_part2`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
-- 所有的列排序方向一致時（結(jié)合索引的數(shù)據(jù)結(jié)構(gòu)B-Tree理解）才能使用索引排序
SELECT key_part1, key_part2 FROM tableA
ORDER BY key_part1 DESC, key_part2 DESC;
-- 同為正序
SELECT key_part1, key_part2 FROM tableA
ORDER BY key_part1 ASC, key_part2 ASC;
-- 因為主鍵（一般是id）存在二級索引的葉子節(jié)點中，索引select的字段中默認(rèn)支持包含主鍵
SELECT id, key_part1, key_part2 FROM tableA
ORDER BY key_part1, key_part2;
-- 即使order by中不符合前綴索引規(guī)范陪拘，但是其余前置的索引列在where子句中定義了常量厂镇，也會使用索引排序
SELECT key_part1，key_part2 FROM tableA
WHERE key_part1 = 23
ORDER BY key_part2;
不會使用索引解決排序問題的情況
以下樣例在執(zhí)行計劃中type仍然可能為index左刽，但是可以在extra下發(fā)現(xiàn)using filesort捺信，實際并沒有做到索引優(yōu)化排序
-- 使用兩個索引
SELECT * FROM t1 ORDER BY key1, key2;
-- order by 子句用了不連續(xù)的索引字段(但是如果where子句中能定義有中間列的常數(shù)，也會使用索引排序)
SELECT id, key_part1, key_part2 FROM tableA
ORDER BY key_part1, key_part3;
-- 索引列排序順序不同
SELECT key_part1, key_part2 FROM tableA
ORDER BY key_part1 DESC, key_part2 ASC;
-- 索引列排序順序不同
SELECT key_part1, key_part2 FROM tableA
ORDER BY key_part1 DESC, key_part2 ASC;
-- where條件和order by對應(yīng)的索引不同
SELECT * FROM tableA
WHERE key1 = constant
ORDER BY key2;
-- order by中需要使用完整字段, 下例不可用索引排序
SELECT key_part1, key_part2 FROM tableA
ORDER BY key_part1 , right(key_part2, 3);
-- 不能使用加工過的數(shù)據(jù)
SELECT ABS(key_part1) AS b FROM t1 ORDER BY b;
其它情況：
- 索引不支持存儲數(shù)據(jù)行本身欠痴，例如Memory引擎的hash索引迄靠。
- order by和group by的表達(dá)式不同。
- 如果是多表join的查詢喇辽，order by條件不全是第一個非常數(shù)表的字段掌挚，將無法使用索引排序。
