mysql查询优化

发表于 更新于 分类于 阅读次数： 本文字数： 2.8k 阅读时长 ≈ 3 分钟

优化一般性流程

  1. 观察。至少跑一天，看看生产的慢sql
  2. 开启慢查询日志，设置阙值，比如超过5s的就是慢sql，将它竹取出来
  3. explain + 慢sql分析
  4. show profile
  5. 运维经理 or DBA，进行数据库服务器的参数调优

总结

  1. 慢查询的开启并捕获
  2. expain + 慢SQL分析
  3. show profile查询SQL在MySQL服务器里的执行细节和生命周期情况
  4. SQL数据库服务器的参数调优

小表驱动大表

小的数据集驱动大的数据集

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

1
2
3
4
5
SELECT * FRON A WHERE id IN (SELECT id FROM B);

等价于
for select id from
for select * from A where A.id = B.id

当A表的数据集小于B表数据集时，用exist 优于 in。如下：

1
2
3
4
5
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表ID字段应建立索引

提示

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

Order by 优化

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

order by子句，尽量使用index方式排序，避免使用FileSort方式排序

Mysql支持两种方式的排序，FileSort和Index，Index效率高，它指Mysql扫描索引本身完成排序。FileSort方式效率低。

order by满足两种情况，会使用Index方式排序：
①order by 语句使用索引最左前列
②使用where子句与order by子句条件列组合满足索引最左前列。

遵照索引建的最佳左前缀

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

filesort有两种算法

如果不在索引列上，filesort有两种算法:mysql启动双路排序和单路排序。

双路排序

Mysql4.1 之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据。读取行指针和orderby列，对他们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对应的数据输出。

从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。

单路排序

从磁盘读取查询需要的所有列，按照orderby列在buffer对他们进行排序，然后扫描排序后的列表进行输出。他的效率更快一点，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为他把每一行都保存在内存中了。

结论及引申出的问题

  1. 单路算法总体而言好过双路算法
  2. 单路算法中的问题。在sort_buffer中，方法B比方法A要占用更多空间，因为方法B是把所有字段都取出，所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序(创建tmp文件，多路合并)，排完再取sort_buffer容量大小，再排···从而所赐I/O。

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

优化策略

  • 增大sort_buffer_size参数的设置
  • 增大max_length_for_sort_data参数的设置
  • why?

    • order by时select * 是一个大忌，应只查需要的字段，这点非常重要。这里会影响：

      • 当query的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT||BLOB类型时，会用改进后的算法–单路排序，否则使用老算法-多路排序。
      • 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size。

    • 尝试提高sort_buffer_size
      不管使用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的。

    • 尝试提高 max_length_for_sort_data
      提高这个参数，会增加使用改进算法的概率。但是吐过设的太高，数据总量超出sort_buffer_size的概率就增大，明显症状是 高的磁盘I/O活动 和 低的处理器使用率。

小总结

为排序使用索引
Mysql两种排序方式：文件排序或扫描优需索引排序
Mysql能为排序与查询使用相同的索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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 (a,b,c排序都一致可以用索引)


# 如果where使用索引的最左前缀定义为常量，则order by能使用索引
- WHERE a = const ORDER BY b, c
- WHERE a = const AND b=const ORDER BY c
- WHERE a = 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 /*丢失a索引*/
- 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 实质是先排序后进行分组，遵照索引建的最佳左前缀

当无法使用索引列，增大max_length_for_sort_data参数的设置 + 增大 sort_buffer_size参数的设置

where高于having，能写在where限定的条件就不要去having限定了