SQL样例
SELECT a, b, c, d, e, f, g, h FROM TableA WHERE b in ('AT105873A') AND type = '2' GROUP BY if(d is null, e, d) ORDER BY h ASC, g ASC
老规矩explain sql
截图中可以看出来,使用到了文件排序和临时表,在sql没办法优化的情况下该怎么提升响应时间呢
Trace sql的执行过程
SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
SELECT a, b, c, d, e, f, g, h FROM TableA WHERE b in ('AT105873A') AND type = '2' GROUP BY if(d is null, e, d) ORDER BY h ASC, g ASC
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G;
主要查看join_execution片段,里边会展示临时表是创建在磁盘上的还是创建在内存中的,当前是创建在磁盘上的,原因是select语句中有text/blob等可变长度的字段,或者超过512的varchar字段,详情见mysql官方文档:Internal Temporary Table Use in MySQL
优化思路
尽量让临时表创建在内存里
1.将blob/text类型的字段修改为varchar
2.将超过512字节的varchar长度缩短
3.增大tmp_table_size和max_heap_table_size
满足前两个条件的情况下,临时表一般会先在内存中创建,但是受到tmp_table_size,max_heap_table_size的制约,随着并发的上升,创建临时表需要的内存会超过这俩参数,此时就需要调整,判断是否需要调整的依据是Created_tmp_disk_tables/Created_tmp_tables,如果大于10%则需要调整,可以通过show global status like ‘%tmp%’;来查看这俩参数的值
调整内存排序空间的大小
排序主要跟两个mysql系统参数有关max_sort_length,sort_buffer_size,max_sort_length指的是一行数据的大小,sort_buffer_size指的是总的排序空间(内存里)的大小,增大max_sort_length的时候也需要增大sort_buffer_size
判断是否需要调大这两个参数的依据,执行show global status like ‘%sort%’; 看Sort_merge_passes参数,如果这个参数特别大可以适当调整sort_buffer_size来解决
注意事项
- 不要随意调整服务端参数,大部分的设置mysql出厂就已经给设置好了,并且是相对合理的
- 调整内存相关的参数的时候,可能会导致系统的内存回收时间增长,性能不升反降