文章目录
【笔记于学习尚硅谷课程所作】
SQL优化步骤
- 慢查询的开启并捕获
- explain+慢SQL分析
- showprofile查询SQL在Mysq1服务器里面的执行细节和生命周期情况
- SQL数据库服务器的参数调优。
1、查询优化
- 永远让小表驱动大表
- order by关键字优化
- group by关键字优化
1.1 order by优化
ORDER BY子句,尽量使用Index方式排序,避免使用FileSort方式排序
ORDER BY满足两情况,会使用Index方式排序:
-
ORDER BY语句使用索引最左前列
-
使用Where子句与Order BY子句条件列组合满足索引最左前列
如果不在索引列上, flesort有两种算法:双路排序(旧)、单路排序
单路排序的问题:如果要取的数据过大,会导致多次I/O得不偿失
优化:
-
增大sort_buffer_size参数的设置
-
增大max_length_for_sort_data参数的设置
1.2 group by关键字优化
- 大致同order by
- group by实质是先排序后进行分组,遵照索引建的最佳左前缀
- 当无法使用索引列,增大max_length_for_sort_data参数的设置和增大sort_buffer_size参数的设置
- where高于having,能写在where限定的条件就不要去having限定了。
2、慢日志查询
MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_ query_ time值的SQL, 则会被记录到慢查询日志中。
2.1 使用慢日志查询
查看是否开启
SHOW VARIABLES LIKE '%slow_query_log%';
开启
set global slow_query_log=1;
查看超过多少时间的SQL会放入慢日志的参数
SHOW VARIABLES LIKE 'long_query_time%';
设置慢的阙值时间
set global long_query_time=3;
查询当前系统中有多少条慢查询记录
show global status like '%Slow_queries%';
2.2 日志分析工具mysqldumpslow
mysqldumpslow帮助信息:mysqldumpslow --help
3、Show Profile
Show Profile是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量
默认情况下,参数处于关闭状态,并保存最近15次的运行结果
使用步骤
- 是否支持,看看当前的mysq|版本是否支持
- 开启功能,默认是关闭,使用前需要开启
- 运行SQL
- 查看执行过的SQL
- 诊断SQL
查看是否开启
show variables like 'profiling';
开启
set profiling=on;
查看执行过的SQL
show profiles;
查看具体某条SQL细节(10为SQL的序号)
show profile cpu,block io for query 10;
诊断结果的重要参数:
- converting HEAP to MyISAM:查询结果太大,内存都不够用了往磁盘上搬了。
- Creating tmp table:创建临时表
- Copying to tmp table on disk:把内存中临时表复制到磁盘
4、全局查询日志
不能在生产环境开启这个功能
开启后将记录所有使用过的SQL
开启
set global general_log= 1;
以表的形式输出
set global log_output='TABLE';
查看
select * from mysql.general_log;