MySQL高级开发（五）--查询优化（ORDER BY & GROUP BY）

MySQL高级开发（五）–查询优化（ORDER BY & GROUP BY）

标签（空格分隔）： MySQL

---

在之前的索引优化的总结中，已经将查询的优化进行了比较细致的说明，在查询的优化中，其实还包括其他的优化，一般在DBA中拿到sql之后，一般通过如下步骤进行sql的优化

1 观察，至少跑1天，看看生产的慢SQL情况
2 开启慢查询日志，设置阀值，比如超过5秒钟的就是慢SQL，并将他抓取出来
3 对sql进行执行计划分析
4 通过show profile解析sql的整体执行细节和生命周期
5 进行SQL数据库服务器的参数调优

优化的原则：小表驱动大表

Exists

Exists语法：SELECT … FROM TABLE WHERE EXISTS (SUBQUERY)
语法说明：主表查询的数据，放在子查询里去验证，返回TRUE 或者 FALSE来判断主查询的数据是否留下

1.EXISTS(subquery)只返回TRUE或FALSE，因此子查询中的SELECT * 也可以是SELECT 1 或者 select ‘x’。优化器会忽略这个查询清单
2.EXISTS子查询在实际执行过程中，可能经过了优化器的优化，而不是我们理解的逐条比对。
3. EXISTS 子查询往往也可以用条件表达式，或者其他子查询，join替代，何种最优，要具体问题具体分析

IN & EXISTS对比

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

select * from A where id in (select aid from b)

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

select * from A where exists (select 1 from b where b.aid = a.id)

当A表的数据集小于B表的数据集是，用exists优于in。

order by 优化

ORDER BY 子句尽量使用INDEX方式排序，避免使用FileSort方式排序。使用Index的方式排序有两种情况：

① ORDER BY 语句使用索引最左前列
② 使用Where子句与Order by 子句条件组合满足索引最左前缀原则

如果不在索引上进行排序，filesort有两种算法：mysql就要启用双路排序 和 单路排序两种算法

双路排序算法：Mysql 4.1之前采用的算法，主要是指两次扫描磁盘，最终得到数据。

步骤：① 读取行指针、orderby 对应的列
② 对列进行排序（Buffer中进行排序）
③ 扫描已经排好序的列表，按照列表中的行指针再次从列表中获取具体的行进行输出

单路排序算法：只扫描一次磁盘（占用了更多内存）

步骤：① 读取所有需要用到的列
② 对ORDER列中的进行排序
③ 将排好序的列进行输出

单路算法和多路算法对比

1、由于单路算法是后出的，在效率上优于多路算法
2、
在缓冲配置：sort_buffer中，单路算法比多路算法占用更多的空间，因为单路算法需要将所有字段都取出后进行排序，但是如果取出的数据总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量的大小数据，进行排序，排完序，再取sort_buffer的容量数据，再排。。。导致多次I/O。反复多次I/O。反而得不偿失。
优化策略：
1、增大sort_buffer_size参数的设置
2、增大max_length_for_sort_data参数的设置

什么情况下会导致单路排序算法：

1. order by时查询列表不能使用select *
   1.1. 当查询的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时，会用改进后的算法—单路排序，否则用老算法 — 多路算法
   1.2 两种算法的数据都有可能超过sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O、但用单路排序算法的风险会更大一些，所以要提高sort_buffer_sort.
2. 尝试提高sort_buffer_size
   不管用哪个算法，提高这个参数都会提高效率，当然要根据系统的能力去提高，因为这个参数是针对每个进程的
3. 尝试提高max_length_for_sort_data
   提高这个参数，会增加用改进算法的概率，如果设置的抬高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率

总结：
Mysql两种排序方式：文件排序或者扫描有序索引排序
索引能为查询和排序提供相同的功效

KEY a_b_c(a,b,c)
#order by 能使用索引最左前缀
ORDER BY a
ORDER BY a,b
ORDER BY a,b,c
ORDER BY a DESC,b DESC,c DESC 
#如果where使用索引的最左前缀定义常量，则order by能使用索引
WHERE a=const ORDER BY b,c
WHERE a=const and b = const ORDER BY c
WHERE a=const and b = const ORDER BY b,c
WHERE a=const AND b > const ORDER BY b,c
#不能索引索引进行排序的情况
ORDER BY a ASC,b DESC,c DESC /*排序不一致*/
where g = const order by b,c/*排序不一致*/
where a = const order by c /*丢失b索引*/
where a = const order by a,d/*d不是索引的一部分*/
where a in (...) order by b,c/*对于排序来说多个相等条件也相当于范围查询*/

group by 优化

group by的优化策略和ORDER BY的优化策略相同。
1. 查询中使用到的GROUP BY 都是先排序后分组。遵照索引建的最佳左前缀法则
2. 当无法使用索引列的时候，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置。
3. where语句高于having，能写在where限定条件就不要在having中去限定