MySQL索引优化（三）排序索引

一、小表驱动大表

简单理解就是外层循环的次数小于内存循环的次数。
小表驱动大表，即小的数据集驱动大的数据集。
MySQL中实现表嵌套的方式主要就是in和exists。
下面的A表和B表中的id字段都创建了索引。

1、当B表的数据集小于A表的数据集时，用in优于exists

select * from A where id in (select id from B)

等价于

for select id from B
for select * from A where A.id = B.id

2、当A表的数据集小于B表的数据集时，用exists优于in

select * from A where exists (select 1 from B where B.id = A.id)

等价于

for select * from A
for select * from B where B.id = A.id

因为exists(subquery)只返回true或者false，因此子查询中的select *也可以是select 1或者select ‘x’，官方说法是实际执行时会忽略select清单，因此没有区别。

二、order by关键字优化

1、尽量使用Index方式，避免使用FileSort方式排序

创建表

create table tblA (
    age int,
    birth timestamp not null
);

insert into tblA(age, birth) values (22, NOW());
insert into tblA(age, birth) values (23, NOW());
insert into tblA(age, birth) values (24, NOW());

创建索引

create index idx_A_ageBirth on tblA(age, birth);

1）order by能使用索引最左前缀

执行

explain select * from tblA where age > 20 order by age;
explain select * from tblA where age > 20 order by age, birth;

结果如下

可以看到Extra中并没有Using FileSort。
ps：这种场景也可以，同升或者同降

order by a desc, b desc, c desc

2）如果where使用索引的最左前缀定义为常量，则order by能使用索引

执行，age = const

explain select * from tblA where age = 20 order by birth, age;

可以看到Extra中并没有Using FileSort。

3）order by不能使用索引

执行

explain select * from tblA where age > 20 order by birth;
explain select * from tblA where age > 20 order by birth, age;

结果如下

可见Extra都产生了Using fileSort，也就是说当order by后面如果缺少带头的索引字段或者字段顺序和创建的索引字段不一致，就会导致Using FileSort。
order by满足两情况，会使用Index方式排序

• order by语句使用索引最左前列
• 使用where子句与order by子句条件列组合满足索引最左前列

总结一句话：尽可能在索引列上完成排序操作（使用被创建索引的字段进行排序），遵循索引创建的最佳左前缀。

2、如果不在索引列上的优化方法

这一块问题比较复杂，就不写了。

1）双路排序

2）单路排序

尝试提高sort_buffer_size或者max_lenght_for_sort_data

三、group by关键字优化

group by是指是先排序后进行分组，遵循索引创建的最佳左前缀。
where高于having，能写在where限定的条件就不要去having限定了。
剩下的同order by