MySQL 优化原则

MySQL的调优大纲

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

EXISTS & IN

  1. --永远记住小表驱动大表
  2. --当 B 表数据集小于 A 表数据集时，使用 in
  3. --当 A 表数据集小于 B 表数据集时，使用 exist
  6. mysql> select * from tbl_emp;
  7. +----+------+--------+
  8. | id | NAME | deptId |
  9. +----+------+--------+
  10. |  1 | z3   |      1 |
  11. |  2 | z4   |      1 |
  12. |  3 | z5   |      1 |
  13. |  4 | w5   |      2 |
  14. |  5 | w6   |      2 |
  15. |  6 | s7   |      3 |
  16. |  7 | s8   |      4 |
  17. |  8 | s9   |     51 |
  18. +----+------+--------+
  19. 8 rows in set (0.00 sec)
  21. mysql> select * from tbl_dept;
  22. +----+----------+--------+
  23. | id | deptName | locAdd |
  24. +----+----------+--------+
  25. |  1 | RD       | 11     |
  26. |  2 | HR       | 12     |
  27. |  3 | MK       | 13     |
  28. |  4 | MIS      | 14     |
  29. |  5 | FD       | 15     |
  30. +----+----------+--------+
  31. 5 rows in set (0.00 sec)
  34. -- in
  35. select * from tbl_emp e where e.deptId in (select id from tbl_dept);
  36.     等价于：
  37.   for select * from tbl_dept
  38.   for select * from tbl_emp e where e.deptId = id
  40. -- exists
  41. select * from tbl_emp e where exists (select 1 from tbl_dept d where e.deptId = d.id);
  42.     等价于：
  43.   for select * from tbl_emp e
  44.   for select * from tbl_dept where id = e.deptId

ORDER BY

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

如果未在索引列上完成排序，mysql 会启动 filesort 的两种算法：双路排序和单路排序

  • 双路排序
    • MySQL4.1之前是使用双路排序，字面意思是两次扫描磁盘，最终得到数据。读取行指针和将要进行orderby操作的列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据传输
    • 从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。

  • 单路排序

    • 取一批数据，要对磁盘进行两次扫描，众所周知，I/O是很耗时的，所以在mysql4.1之后，出现了改进的算法，就是单路排序。
    • 从磁盘读取查询需要的所有列，按照将要进行orderby的列，在sort buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据，并且把随机IO变成顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。

  • 结论及引申出的问题：

    • 由于单路是改进的算法，总体而言好过双路
    • 在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出，所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取取sort_buffer容量大小，再排…… 从而会导致多次I/O。
    • 结论：本来想省一次I/O操作，反而导致了大量的/O操作，反而得不偿失。
  • 更深层次的优化策略：
    • 增大sort_buffer_size参数的设置
    • 增大max_length_for_sort_data参数的设置

遵循如下规则，可提高Order By的速度

  • Order by时select *是一个大忌，只Query需要的字段，这点非常重要。在这里的影响是：
    • 当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活动和低的处理器使用率。

慢查询日志

  1. # 查看慢查询日志
  2. mysql> SHOW VARIABLES LIKE '%slow_query_log%';
  3. +---------------------+-------------------------------+
  4. | Variable_name       | Value                         |
  5. +---------------------+-------------------------------+
  6. | slow_query_log      | ON                            |
  7. | slow_query_log_file | /var/lib/mysql/Heygo-slow.log |
  8. +---------------------+-------------------------------+
  10. # 临时调整
  11. set global slow_query_log = 1;
  13. # 修改my.cnf文件
  14. [mysqld]
  15. slow_query_log =1
  16. slow_query_log_file=/var/lib/mysql/Heygo-slow.log
  18. # 查看慢SQL阈值
  19. SHOW VARIABLES LIKE 'long_query_time%';

日志分析命令

mysqldumpslow

  1. # 得到返回记录集最多的10个SQL
  2. mysqldumpslow -s r -t 10 /var/lib/mysql/Heygo-slow.log
  4. # 得到访问次数最多的10个SQL
  5. mysqldumpslow -s c- t 10/var/lib/mysql/Heygo-slow.log
  7. # 得到按照时间排序的前10条里面含有左连接的查询语句
  8. mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/Heygo-slow.log
  10. # 另外建议在使用这些命令时结合 | 和more使用，否则有可能出现爆屏情况
  11. mysqldumpslow -s r -t 10 /var/lib/mysql/Heygo-slow.log | more

show profile
分析当前会话中语句执行的资源消耗情况

  1. mysql> show profiles;
  2. +----------+------------+----------------------------------------------------------------------+
  3. | Query_ID | Duration   | Query                                                                |
  4. +----------+------------+----------------------------------------------------------------------+
  5. |        1 | 0.00052700 | show variables like 'profiling%'                                     |
  6. |        2 | 0.00030300 | select * from tbl_emp                                                |
  7. |        3 | 0.00010650 | select * from tbl_emp e inner join tbl_dept d on e.'deptId' = d.'id' |
  8. |        4 | 0.00031625 | select * from tbl_emp e inner join tbl_dept d on e.deptId = d.id     |
  9. |        5 | 0.00042100 | select * from tbl_emp e left join tbl_dept d on e.deptId = d.id      |
  10. |        6 | 0.38621875 | select * from emp group by id%20 limit 150000                        |
  11. |        7 | 0.00014900 | select * from emp group by id%20 order by 150000                     |
  12. |        8 | 0.38649000 | select * from emp group by id%20 order by 5                          |
  13. |        9 | 0.06782700 | select COUNT(*) from emp                                             |
  14. |       10 | 0.35434400 | select * from emp group by id%10 limit 150000                        |
  15. +----------+------------+----------------------------------------------------------------------+
  16. 10 rows in set, 1 warning (0.00 sec)