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

逃离我推掉我的手 2022-05-31 01:43 254阅读 0赞

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

标签（空格分隔）： MySQL

--------------------

*  MySQL高级开发五查询优化ORDER BY GROUP BY
    
     *  Exists
     *  IN EXISTS对比
     *  order by 优化
     *  group by 优化

在之前的索引优化的总结中，已经将查询的优化进行了比较细致的说明，在查询的优化中，其实还包括其他的优化，一般在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中去限定