MySql优化之查询优化

小表驱动大表

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

SELECT * FROM A WHERE id IN (SELECT id FROM B)
等价于：
for SELECT id FROM B
for SELECT * FROM A WHERE A.id = B.id

当数据表B的数据集小于A表时，用IN优于EXISTS.

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

当数据表A的数据集小于B表时，用EXISTS优于IN.

SELECT ... FROM table WHERE EXISTS (subquery)该语法可以理解为：将主查询的数据放到子查询中做条件验证，根据验证结果(TRUE或FALSE)来决定主查询的数据结构是否得以保留。

EXISTS(subquery)只返回TRUE或FALSE，因此子查询中的SELECT * 也可以是SELECT 1或SELECT 'X'，实际执行时会忽略SELECT清单，因此没有区别
EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担心效率问题，可进行实际检验以确定是否有效率问题。
EXISTS子查询往往也可以用条件表达式、其他子查询或者JOIN来代替，何种最优需要具体问题具体分析。

ORDER BY优化

尽量使用index方式排序，避免使用filesort方式排序

CREATE TABLE tblA(
    `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    `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);

判断下方SQL语句是否会产生filesort

EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY age;
EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY age,birth;
EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY birth;
EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY birth,age;
EXPLAIN SELECT * FROM tblA ORDER BY birth;
EXPLAIN SELECT * FROM tblA WHERE birth > '2016-01-28' ORDER BY birth;
EXPLAIN SELECT * FROM tblA WHERE birth > '2016-01-28' ORDER BY age;
EXPLAIN SELECT * FROM tblA ORDER BY age ASC,birth DESC;

答案

Using index
Using index
Using filesort
Using filesort
Using filesort
Using filesort
Using index
Using filesort

ORDER BY满足两种情况会使用index方式排序：

ORDER BY语句使用索引最左前列
使用WHERE子句与ORDER BY子句条件列组合满足索引最左前列

尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀。

filesort的两种算法

如果不在索引列上，filesort有两种算法进行排序：双路排序和单路排序。

双路排序：MySql4.1之前使用的是双路排序，两次扫描磁盘最终得到数据。读取行指针和ORDER BY列，对它们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。
单路排序：在双路排序中取一批数据要对磁盘进行两次扫描，这费IO呀！在MySql4.1之后出现了改进的排序算法——单路排序。从磁盘读取查询需要的所有列，按照ORDER BY列在buffer对它们进行排序，然后扫描排序后的列表进行输出，他的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间。因为它把每一行数据都保存在内存中了。

单路的问题：

在sort_buffer中，单路比双路要多占很多空间，因为单路是把所有字段都取出，所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据进行排序（创建tmp文件，多路合并），排完在取sort_buffer容量大小，再排序，循环反复导致多次IO。

本来想省一次IO操作，反而导致了大量的IO操作，得不偿失。

单路优化策略

ORDER BY时SELECT *是一个大忌，只需要query需要的字段，这点非常重要。
- 当query的字段大小综合小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时，会用改进后的算法——单路排序，否则用老算法——多路排序。
- 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次IO，但是用单路排序的风险会更大一些，所以要提高sort_buffer_size。
尝试提高sort_buffer_size：不管使用哪种算法，提高这个参数都会提高效率，当然要根据系统的能力去提高，因为这个参数是针对每个进程的。
尝试提高max_length_for_sort_data：提高这个参数会增加算法的效率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率。