MySQL 高级（五）排序索引优化 永远小表驱动大表 Order by 关键字的排序优化 Group by 关键字的优化

MySQL 高级（五）排序索引优化

5 排序索引优化

5.1 永远小表驱动大表

优化原则：小表驱动大表，即小的数据集驱动大的数据集

案例：

SELECT * FROM A WHERE id in (select id from B)
--等价于
for select id from B
for select * from A where A.id = B.id
--for 表示循环

​ 当B表的数据集小于A表的数据集时，用in优于exist

select * from A where exists( select 1 from B where A.id = B.id)
--等价于
for select * from A
for select * from B where A.id = B.id
--exists：将主查询的数据，放入到子查询中做条件验证，根据验证结果（TRUE或FALSE），来决定主查询的数据是否保留

​ 当A表的数据集小于B表的数据集时，用exist优于in

结论：当子查询数据集小于主查询时，用in；当主查询数据集小于子查询时，用exists

5.2 Order by 关键字的排序优化

关键点在于：避免产生file sort

优化原则：

• Order by子句尽量使用index索引排序，避免使用filesort方式进行排序。
• 尽可能在索引列上完成排序操作，遵照索引建立时的最佳左前缀
• order by时尽量不要用select * ，会占用很多buffer的大小
• 如果必须使用file sort排序，可以修改MySQL的参数选择使用双路排序或单路排序
  • 双路排序：两次扫描磁盘，通过读取行指针和order by 的列，对他们进行排序，然后根据排序好的列表，获得数据。
  • 单路排序：一次扫描磁盘，读取出需要查询的所有列，然后在buffer中对他们进行排序。总体而言效率优于双路排序
  • 单路引申出的问题：单路排序要比双路排序占用更多的sort_buffer（内存中），若取出的总数据大小大于服务器配置的buffer大小，反而会导致多次取部分数据再排序，导致更多次的IO。（需要通过修改MYSQL服务器参数解决，增大sort_buffer_size和max_size_for_sort_data大小）

测试表结构：

CREATE TABLE tblA(
	age int4,
	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_tblA_AgeBirth on tblA(age,birth);
select * from tblA

测试案例：

结论：

• MySQL支持两种排序方式，file sort和index，index表示MySQL扫描索引本身完成排序，效率较高；
• order by 要使用index排序，需要满足两种情况：Order by 语句使用索引的最左前列原则；不存在ASC和DESC 同时使用的情况

5.3 Group by 关键字的优化

• group by的实质是先排序后分组，遵照索引建的最佳左前缀
• 与order by 一致，当无法使用索引列时，增大sort_buffer_size和max_size_for_sort_data大小
• where高于having，能在where写的限定条件就不要写在having