mysql高级--查询截取分析

【笔记于学习尚硅谷课程所作】

SQL优化步骤

1. 慢查询的开启并捕获
2. explain+慢SQL分析
3. showprofile查询SQL在Mysq1服务器里面的执行细节和生命周期情况
4. 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次的运行结果

​ 使用步骤

1. 是否支持，看看当前的mysq|版本是否支持
2. 开启功能，默认是关闭，使用前需要开启
3. 运行SQL
4. 查看执行过的SQL
5. 诊断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;