查询优化
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
- 当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。
注意：A表与B表的ID字段应建立索引。
- EXISTS
SELECT xxx FROM table WHERE EXISTS(subQuery);
该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果(TRUE或FALSE)来决定主查询的数据结果是否得以保留。
EXISTS(subQuery)只返回布尔值，因此子查询中select * 也可以是select 1或select ‘x’，官方说法是实际执行时会忽略SELECT清单，因此没有区别。
EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如担心效率问题可以自行进行实际检验。
EXISTS子查询往往也可以用条件表达式、其他子查询或JOIN来替代，何种最优需具体问题具体分析。
2. ORDER BY优化
2.1 ORDER BY子句尽量使用Index方式排序
- 建表插数据建索引
CREATE TABLE tbA(
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
age INT,
birth TIMESTAMP NOT NULL
);
INSERT INTO tbA(age,birth) VALUES
(22,NOW()),
(23,NOW()),
(24,NOW());
CREATE INDEX idx_tbA_ageBirth ON tbA(age,birth);
- case1：分析下列sql是否产生了filesort，Y代表产生了，N表示未产生。
EXPLAIN SELECT * FROM tbA WHERE age>20 ORDER BY age; # N
EXPLAIN SELECT * FROM tbA WHERE age>20 ORDER BY age,birth; # N
EXPLAIN SELECT * FROM tbA WHERE age>20 ORDER BY birth; # Y
EXPLAIN SELECT * FROM tbA WHERE age>20 ORDER BY birth,age; # Y
- case2：分析下列sql是否产生了filesort。
EXPLAIN SELECT * FROM tbA ORDER BY birth; # Y
EXPLAIN SELECT * FROM tbA WHERE birth > '2019-09-28 00:00:00' ORDER BY birth;#Y
EXPLAIN SELECT * FROM tbA WHERE birth > '2019-09-28 00:00:00' ORDER BY age; # N
EXPLAIN SELECT * FROM tbA ORDER BY age ASC, birth DESC; # Y,因为默认排序是ASC，手动指定了DESC必定会产生filesort。
- MySQL支持两种方式的排序，FileSort和Index，Index效率高，它指的是MySQL扫描索引本身完成的排序。FileSort效率较低。
- ORDER BY 满足两情况，会使用Index方式排序：
- ORDER BY语句使用索引最左前列(最佳左前缀原则)。
- 使用Where子句与OrderBy子句条件列组合满足索引最左前列。
2.2 尽可能在索引列上完成排序操作
索引建立时已经排好序了，默认是升序(ASC)，所以ORDER BY尽量使用索引的排序，就可以避免产生FileSort。
2.3 FileSort的两种算法
2.3.1 双路排序
MySQL4.1之前是使用双路排序，就是进行两次扫描磁盘，最终得到数据。读取行指针和OrderBy列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。
- 第一次IO：从磁盘读取排序字段，在buffer进行排序。
- 第二次IO：根据排序列表从磁盘中读取对应的数据。
取数据时要对磁盘进行两次扫描，众所周知，IO是很耗时的，所以在MySQL4.1后，出现了第二张改进算法，就是单路排序。
2.3.2 单路排序
从磁盘读取查询所需的所有列，按照OrderBy列在buffer对它们进行排序，然后扫描排序后的列表进行输出。该算法效率更快一些，避免了二次读取数据。并且把随机IO变成了顺序IO；缺点是会使用更多的空间，因为它把每一行都保存在内存中了。
2.3.3 结论和引申出的问题
单路排序总体而言好过双路，但单路有个问题就是：由于单路排序会一次磁盘读取所有列，当取出的数据总大小超过了sort_buffer容量时，会导致每次只取sort_buffer容量大小的数据进行排序(创建tem文件，多路合并)，排完后再次IO取数据，以此类推。故在读取大量数据时，反而导致了大量的IO操作，得不偿失。
2.4 优化策略
2.4.1 增大sort_buffer_size参数的设置
2.4.2 增大max_length_for_sort_data参数的设置
2.4.3 提高Order By的速度
- Order By时select * 是大忌！只Query需要的字段，这点非常重要。
- 当Query的字段大小总和小于max_length_for_sort_data且排序字段不是TEXT|BLOB类型时，会用改进后的单路排序算法，反正大于则使用老算法多路排序。
- 两种算法的数据都可能超过sort_buffer的容量，超过后会创建tem文件进行合并排序，导致多次IO；所以使用单路排序算法的风险更大，因此出现高IO时需提高sort_buffer_size。
- 尝试提高sort_buffer_size
- 不管用哪种算法，提高该参数都会提高效率；当然具体需根据系统能力去提高，一般由DBA操作。
- 尝试提高max_length_for_sort_data
- 提高这个参数，会增加用改进算法的效率；但如果设太高，数据总容量超出sort_buffer_size的概率就增大，明显症状就是高磁盘IO活动和低CPU使用率。
2.5 小结
使用Order by时，注意以下几点
为排序使用索引
- MySQL两种排序方式：文件排序或扫描有序索引排序
- MySQL能为排序与查询使用相同索引，即在索引列上的数据无需再次排序，提高性能。
order by的使用尽量遵循索引最左前缀原则
create index idx_abc on table(a,b,c);
xxx order by a
xxx order by a,b
xxx order by a,b,c
xxx order by a desc,b desc,c desc; /*必须使用同一种排序方式，否则会产生filesort*/
- 如果where使用到了索引的最左前缀定义为常量，则order by能使用到索引
xxx where a = const order by b,c
xxx where a = const and b = const order by c
xxx where a = const and b > const order by b,c
- 不要使用索引进行排序
xxx order by a asc,b desc, c desc /* 排序不一致*/
xxx where g = const order by b,c /* 丢失a索引 */
xxx where a = const order by c /* 丢失b索引 */
xxx where a = const order by a,d /* d非索引列*/
xxx where a in(...) order by b,c /* 范围查询后面索引失效*/
3. GROUP BY 优化
group by优化跟order by优化大致相同
- group by 实质是先排序后进行分组，同样遵循索引的最佳左前缀原则。
- 无法使用索引列时，增大max_length_for_sort_data参数的设置和增加sort_buffer_size参数的设置。
- where高于having，能写在where限定的条件就不要去having限定了。
