1.查询优化
1.1小表驱动大表
emp有500000条数据,empnos有7条数据,emp是大表,empnos是小表
1.2order by关键字优化
表字段多的时候，避免使用select *
ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序
尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀
使用表test03,简历联合索引idx_test03_c1234(c1,c2,c3,c4)
order by使用索引最左前缀
如果where使用索引的最左前缀定义为常量,则order by能使用索引
![]()
image-20200512182448571
不能使用索引排序
-
排序不一致
image-20200512183207797 -
丢失最左前缀索引
image-20200512183220342 -
丢失中间索引,索引中断
image-20200512183233368 -
使用了非索引字段进行排序
image-20200512183244339 -
使用了in(相当于范围查询)
image-20200512183256565
order引起索引失效的各种情况:
filesort的两种算法:单路排序与双路排序
双路排序:MySQL 4.1 之前使用的双路排序，通过两次扫描磁盘得到数据。读取行指针和 order by 列并对其进行排序，扫描排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。
但是双路排序会扫描两次磁盘，磁盘IO是非常消耗性能的，所以后面被单路排序取代。
单路排序:从磁盘中读取查询需要的所有列，按照 order by 列在 sort_buffer 缓冲区对他们进行排序，然后扫描排序后的列表输出。因为单路排序效率更快，避免了二次读取数据，把随机IO变成了顺序IO，但是会使用更多的空间。
但是单路排序算法可能会导致一个问题：如果数据量过大，一次读取不完，就会导致读取的次数比双路排序多。
因为读取操作是在 sort_buffer 中，如果数据量过大，超出了 sort_buffer 的容量，导致每次只能读取 sort_buffer 容量大小的数据进行排序，排完再取，导致多次IO。
优化策略
- 增大sort_buffer_size参数的设置
- 增大max_length_for_sort_data参数的设置
1.3group by
同order by
group by 实质是先排序后分组,遵照索引键的最佳左前缀
当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置
where高于having,能写在where限定的条件就不要去having限定了。
2.慢查询日志
查看慢查询是否开启及日志存放位置: show variables like '%slow_query_log%';
开启慢查询日志: set global slow_query_log=1;
查看慢查询阈值: show variables like 'long_query_time';
设置阈值为5s: set global long_query_time=5;
测试:
查看日志: cat /www/server/data/mysql-slow.log
3.show profiles
mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优测量
官网：http://dev.mysql.com/doc/refman/5.5/en/show-profile.html
分析步骤：
是否支持，看看当前的SQL版本是否支持
-
开启功能，默认是关闭，使用前需要开启:
查看是否开启: show variables like 'profiling';
image-20200512220411099开启: set profiling=on;
image-20200512220515182 -
运行SQL
image-20200512220928439 -
查看结果，show profiles；
image-20200512220946991 -
诊断SQL，show profile cpu,block io for query 上一步前面的问题SQL 数字号码；
对上面第六句sql进行cpu资源占用,磁盘IO进行具体分析
show profile cpu,block io for query 6;
image-20200512221201424 -
日常开发需要注意的结论
- converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬了。
- Creating tmp table 创建临时表:拷贝数据到临时表,用完再删除
- Copying to tmp table on disk 把内存中临时表复制到磁盘，危险！！！
- locked
4.全局查询日志
只能在测试环境使用,切勿在生产环境使用!!!
查看是否开启及全局日志文件存放位置: show variables like '%general_log%';
开启全局日志: set global general_log=1;
测试:
