表结构和数据
CREATE TABLE t1 (
id int(11) NOT NULL AUTO_INCREMENT,
a int(11) DEFAULT NULL,
b int(11) DEFAULT NULL,
c int(11) DEFAULT NULL,
PRIMARY KEY (id),
KEY idx_a_b_c (a,b,c)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
id a b c 1 1 5 3 2 5 3 3 3 4 5 9 4 2 6 1 5 4 3 2 6 5 5 5 7 1 2 1 8 5 5 8 9 5 3 9 10 5 5 1 11 5 7 7
SQL
explain select * from t where a = 5 order by c desc;
-- type:ref
-- key:idx_a_b_c
-- ref:const
-- Extra:Using where; Using index; Using filesort
explain select * from t where a = 5 order by b desc;
-- type:ref
-- key:idx_a_b_c
-- ref:const
-- Extra:Using where; Using index
explain select * from t where a = 5 and c = 5 order by b desc;
-- type:ref
-- key:idx_a_b_c
-- ref:const
-- Extra:Using where; Using index
explain select * from t where a = 5 and b = 5 order by c desc;
-- type:ref
-- key:idx_a_b_c
-- ref:const,const
-- Extra:Using where; Using index
explain select * from t where a = 5 and b >= 5 order by c desc;
-- type:range
-- key:idx_a_b_c
-- ref:NULL
-- Extra:Using where; Using index; Using filesort
索引分析
通过观察联邦索引的数据结构，可以明显看出索引是有序的，而使用索引进行排序利用了这一特性。
[En]
By observing the data structure of federated indexes, it is obvious that indexes are ordered, and sorting using indexes takes advantage of this feature.
我们来观察 a = 5 的这一段索引，很容易就能发现，在 a 确定的情况下， b 是有序的，但 c 是无序的。 a 和 b 命中索引， a 和 c 不命中索引
在 a,b 都确定的情况下， c 是有序的。 a,b,c 命中索引
这就是老生常谈的 最佳左前缀原则 也叫 最左前缀匹配原则
因此，让已排序的项使用索引进行排序
[En]
Therefore, let the sorted items be sorted using the index
第一个条件就是：where条件+排序项符合 最佳左前缀原则
第二个条件：不能使用条件查询
这也可以通过观察联合指数得出结论。
[En]
This can also be concluded by observing the joint index.
a = 5 AND b >= 5 显然是无法保证 c 是有序的
结论
要让 order by 使用索引排序，需要 至少满足以下条件：
- where条件+排序项符合
最佳左前缀原则 - 不能使用条件查询
Original: https://www.cnblogs.com/weirwei/p/15846609.html
Author: weirwei
Title: MySQL 索引排序
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/507856/
转载文章受原作者版权保护。转载请注明原作者出处！