SQL分析优化
2022-08-30 14:54:27 44 举报AI智能生成
SQL分析优化是通过对数据库查询语句进行分析和修改，以提高其执行效率和性能的过程。这包括了对查询语句的结构、索引的使用、数据的存储等方面进行优化。首先，我们需要对查询语句进行解析，找出可能存在的性能瓶颈。然后，我们可以通过添加、删除或者修改索引，来改善查询语句的执行效率。此外，我们还可以通过调整数据的存储方式，如使用分区表或者汇总表，来减少查询的数据量。最后，我们需要对优化后的查询语句进行测试，以确保其性能的提升。总的来说，SQL分析优化是一个系统的过程，需要对数据库的结构和查询语句有深入的理解。
作者其他创作
大纲/内容
慢sql优化
相关配置及命令
# 查看是否开启慢日志记录
show variables like 'slow_query%';
#设置开启慢日志
set GLOBAL slow_query_log="ON";
#设置慢日志记录时间阈值
show variables like '%long_query%';
#查看有多少慢日志
show global status like 'slow_queries';
show variables like 'slow_query%';
#设置开启慢日志
set GLOBAL slow_query_log="ON";
#设置慢日志记录时间阈值
show variables like '%long_query%';
#查看有多少慢日志
show global status like 'slow_queries';
mysqldumpslow工具
MySQL提供的mysqldumpslow 的工具，在MySQL的bin目录下
mysqldumpslow --help 查看分析工具支持的各种命令，如查询用时最多的20条慢SQL：
mysqldumpslow -s t -t 20 -g 'select' /var/lib/mysql/bab8d9419ec6-slow.log > /data/slow.log
mysqldumpslow -s t -t 20 -g 'select' /var/lib/mysql/bab8d9419ec6-slow.log > /data/slow.log
mysqldumpslow查询结果参数：
Count ：代表这个SQL执行了多少次
Time ：代表执行的时间，括号里面是累计时间
Lock ：表示锁定的时间，括号是累计
Rows ：表示返回的记录数，括号是累计
Count ：代表这个SQL执行了多少次
Time ：代表执行的时间，括号里面是累计时间
Lock ：表示锁定的时间，括号是累计
Rows ：表示返回的记录数，括号是累计
SQL优化建议
1. 使用预编译提交SQL执行速度
2. 当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减少哪些有列名歧义引起的语法错误
3. 多条SQL语句压缩到一句SQL中
4. 当SQL语句需要union两个查询结果集合时，如果可以判断检索结果中不会有重复的记录时候，应该用union all
5. 避免使用HAVING字句，因为HAVING只会在检索出所有记录之后才对结果集进行过滤，而where则是在聚合前刷选记录，如果能通过where字句限制记录的数目，那就能减少这方面的开销
6. 考虑使用“临时表”暂存中间结果，可以避免程序中多次扫描主表，也大大减少了程序执行中“共享锁”阻塞“更新锁”，减少了阻塞，提高了并发性能
2. 当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减少哪些有列名歧义引起的语法错误
3. 多条SQL语句压缩到一句SQL中
4. 当SQL语句需要union两个查询结果集合时，如果可以判断检索结果中不会有重复的记录时候，应该用union all
5. 避免使用HAVING字句，因为HAVING只会在检索出所有记录之后才对结果集进行过滤，而where则是在聚合前刷选记录，如果能通过where字句限制记录的数目，那就能减少这方面的开销
6. 考虑使用“临时表”暂存中间结果，可以避免程序中多次扫描主表，也大大减少了程序执行中“共享锁”阻塞“更新锁”，减少了阻塞，提高了并发性能
explain执行计划
执行计划概念
数据库内部对SQL语句分析后决定的执行路径和执行步骤，通俗的说是数据库服务器在执行sql语句的时候，会准备几套方案，最后选择消耗资源最小的方案
Explain各个字段含义
id：
id表示执行顺序，id值越大，越先执行；
子查询，id的序号会递增;
id值相同的情况，可以理解为一组，执行顺序是从上向下。
id表示执行顺序，id值越大，越先执行；
子查询，id的序号会递增;
id值相同的情况，可以理解为一组，执行顺序是从上向下。
select_type：
SIMPLE：简单的select语句，不包含子查询或者union
PRIMARY：查询中包含复杂查询（比如子查询），最外层查询被标记为PRIMARY
SUBQUERY：当select或where包含了子查询，该子查询被标记为SUBQUERY
DERIVED：表示包含在from子句中的子查询的select
UNION：union中第二个或后面的select语句
UNION RESULT：代表从union的临时表中读取数据
SIMPLE：简单的select语句，不包含子查询或者union
PRIMARY：查询中包含复杂查询（比如子查询），最外层查询被标记为PRIMARY
SUBQUERY：当select或where包含了子查询，该子查询被标记为SUBQUERY
DERIVED：表示包含在from子句中的子查询的select
UNION：union中第二个或后面的select语句
UNION RESULT：代表从union的临时表中读取数据
table：
表名，也有可能是别名或者临时表
表名，也有可能是别名或者临时表
partitions：
当查询的是分区表时，partitions显示分区表命中的分区情况
当查询的是分区表时，partitions显示分区表命中的分区情况
type:
它在 SQL优化中是一个非常重要的指标，以下性能从好到坏依次是：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
system：当表仅有一行记录时(系统表)，数据量很少，往往不需要进行磁盘IO，速度非常快
const：当命中的是主键或者唯一索引且为常量
eq_ref：查询命中的是主键或者唯一索引
ref：非唯一性索引，会找到超过一条符合条件的行
ref_or_null：与ref类似，只是会额外搜索包含null值的行
index_merge：同时使用了两个或两个以上的索引
unique_subquery：子查询返回不重复的集合
index_subquery：区别于unique_subquery，用于非唯一索引，可以返回重复值
range：对有索引的字段，给定范围检索，bettween...and、<、>、<=、in 等
index：遍历索引树读取，而all是从硬盘读取
ALL：将遍历全表以找到匹配的行，性能最差
它在 SQL优化中是一个非常重要的指标，以下性能从好到坏依次是：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
system：当表仅有一行记录时(系统表)，数据量很少，往往不需要进行磁盘IO，速度非常快
const：当命中的是主键或者唯一索引且为常量
eq_ref：查询命中的是主键或者唯一索引
ref：非唯一性索引，会找到超过一条符合条件的行
ref_or_null：与ref类似，只是会额外搜索包含null值的行
index_merge：同时使用了两个或两个以上的索引
unique_subquery：子查询返回不重复的集合
index_subquery：区别于unique_subquery，用于非唯一索引，可以返回重复值
range：对有索引的字段，给定范围检索，bettween...and、<、>、<=、in 等
index：遍历索引树读取，而all是从硬盘读取
ALL：将遍历全表以找到匹配的行，性能最差
possible_keys:
mysql可能通过某个索引检索到数据，但不一定最终查询会用到
mysql可能通过某个索引检索到数据，但不一定最终查询会用到
key:
key是查询中实际使用到的索引，若没有使用索引，显示为NULL
key是查询中实际使用到的索引，若没有使用索引，显示为NULL
key_len:
表示查询用到的索引长度（字节数），原则上长度越短越好
单列索引，那么需要将整个索引长度算进去；
多列索引，不是所有列都能用到，需要计算查询中实际用到的列。
注：key_len只计算where条件中用到的索引长度，而排序和分组即便是用到了索引，也不会计算到key_len中。
表示查询用到的索引长度（字节数），原则上长度越短越好
单列索引，那么需要将整个索引长度算进去；
多列索引，不是所有列都能用到，需要计算查询中实际用到的列。
注：key_len只计算where条件中用到的索引长度，而排序和分组即便是用到了索引，也不会计算到key_len中。
ref:
显示该表的索引字段关联了哪张表的哪个字段
显示该表的索引字段关联了哪张表的哪个字段
rows：
根据表统计信息及选用情况，大致估算出找到所需的记录或所需读取的行数，数值越小越好
根据表统计信息及选用情况，大致估算出找到所需的记录或所需读取的行数，数值越小越好
filtered:
filtered 这个是一个百分比的值，表里符合条件的记录数的百分比。这个字段表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例
filtered 这个是一个百分比的值，表里符合条件的记录数的百分比。这个字段表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例
Extra:
额外的信息会在 Extra 字段显示
1.Using index
查询到的列被索引覆盖，实际上就是覆盖索引的使用
2.Using where
查询未用到可用的索引，通过where条件过滤数据
3.Using where,Using index
通过where条件过滤数据，并且查询用到了覆盖索引
4.Using index condition
查询使用到了索引，但是需要回表查询，5.6及其以上版本才有，之前是Using where
5.Using temporary
查询后结果需要使用临时表来存储，一般在排序或者分组查询时用到
6.Using filesort
无法利用索引完成的排序操作，也就是ORDER BY的字段没有索引
7.Using join buffer
在我们联表查询的时候，如果表的连接条件没有用到索引，需要有一个连接缓冲区来存储中间结果
8.Impossible where
在我们用不太正确的where语句，导致没有符合条件的行
9.No tables used
我们的查询语句中没有FROM子句，或者有FROM DUAL子句
额外的信息会在 Extra 字段显示
1.Using index
查询到的列被索引覆盖，实际上就是覆盖索引的使用
2.Using where
查询未用到可用的索引，通过where条件过滤数据
3.Using where,Using index
通过where条件过滤数据，并且查询用到了覆盖索引
4.Using index condition
查询使用到了索引，但是需要回表查询，5.6及其以上版本才有，之前是Using where
5.Using temporary
查询后结果需要使用临时表来存储，一般在排序或者分组查询时用到
6.Using filesort
无法利用索引完成的排序操作，也就是ORDER BY的字段没有索引
7.Using join buffer
在我们联表查询的时候，如果表的连接条件没有用到索引，需要有一个连接缓冲区来存储中间结果
8.Impossible where
在我们用不太正确的where语句，导致没有符合条件的行
9.No tables used
我们的查询语句中没有FROM子句，或者有FROM DUAL子句
作用总结
根据MySQL执行计划的输出，分析索引使用情况、扫描的行数可以预估查询效率；进而可以重构SQL语句、调整索引，提升查询效率。
Show Profile
简介
1. Show Profile是mysql提供的可以用来分析当前会话中sql语句执行的资源消耗情况的工具，可用于sql调优的测量；
2. 默认情况下处于关闭状态，并保存最近15次的运行结果；
3. 在MySQL数据库中，可以通过配置profiling参数来启用SQL剖析，该参数可以在全局和session级别来设置，对于全局级别则作用于整个MySQL实例，而session级别紧影响当前session；
4. 该参数开启后，后续执行的SQL语句都将记录其资源开销，诸如IO，上下文切换，CPU，Memory等等，根据这些开销进一步分析当前SQL瓶颈从而进行优化与调整；
5. Show profiles是5.0.37之后添加的，要想使用此功能，要确保版本在5.0.37之后；
2. 默认情况下处于关闭状态，并保存最近15次的运行结果；
3. 在MySQL数据库中，可以通过配置profiling参数来启用SQL剖析，该参数可以在全局和session级别来设置，对于全局级别则作用于整个MySQL实例，而session级别紧影响当前session；
4. 该参数开启后，后续执行的SQL语句都将记录其资源开销，诸如IO，上下文切换，CPU，Memory等等，根据这些开销进一步分析当前SQL瓶颈从而进行优化与调整；
5. Show profiles是5.0.37之后添加的，要想使用此功能，要确保版本在5.0.37之后；
相关命令
#查看 profiles 是否打开
show variables like profiles
#设置开启profiles功能
set profiling=1
#profiles帮助指令
help profile
#查看执行记录列表
Show Profile
#使用show profile对sql语句进行诊断
show profile cpu,block io for query Query_ID;
show variables like profiles
#设置开启profiles功能
set profiling=1
#profiles帮助指令
help profile
#查看执行记录列表
Show Profile
#使用show profile对sql语句进行诊断
show profile cpu,block io for query Query_ID;
Show Profile命令结果
show profile cpu,block io for query Query_ID命令结果（Query_ID为Show Profile命令结果中属性）
常用查询参数
①ALL：显示所有的开销信息。
②BLOCK IO：显示块IO开销。
③CONTEXT SWITCHES：上下文切换开销。
④CPU：显示CPU开销信息。
⑤IPC：显示发送和接收开销信息。
⑥MEMORY：显示内存开销信息。
⑦PAGE FAULTS：显示页面错误开销信息。
⑧SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息。
⑨SWAPS：显示交换次数开销信息。
②BLOCK IO：显示块IO开销。
③CONTEXT SWITCHES：上下文切换开销。
④CPU：显示CPU开销信息。
⑤IPC：显示发送和接收开销信息。
⑥MEMORY：显示内存开销信息。
⑦PAGE FAULTS：显示页面错误开销信息。
⑧SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息。
⑨SWAPS：显示交换次数开销信息。
show profile常见诊断需优化结果
①converting HEAP to MyISAM：查询结果太大，内存不够，数据往磁盘上搬了
②Creating tmp table：创建临时表。先拷贝数据到临时表，用完后再删除临时表
③Copying to tmp table on disk：把内存中临时表复制到磁盘上，危险！！！
④locked
②Creating tmp table：创建临时表。先拷贝数据到临时表，用完后再删除临时表
③Copying to tmp table on disk：把内存中临时表复制到磁盘上，危险！！！
④locked
总结
1. show profile默认是关闭的，并且开启后只存活于当前会话，也就说每次使用前都需要开启。
2. 通过show profiles查看sql语句的耗时时间，然后通过show profile命令对耗时时间长的sql语句进行诊断。
3. 注意show profile诊断结果中出现相关字段的含义，判断是否需要优化sql语句。
2. 通过show profiles查看sql语句的耗时时间，然后通过show profile命令对耗时时间长的sql语句进行诊断。
3. 注意show profile诊断结果中出现相关字段的含义，判断是否需要优化sql语句。
全局日志
注意事项
该功能主要用于测试环境，在生产环境中永远不要开启该功能。
开启设置
命令开启
1、记录到表中
set global general_log =1 //开启全局日志，开启后会记录所有sql
set global log_output='table' //记录sql到系统表general_log中
select * from mysql.general_log
2、记录到本地日志文件中
set global general_log =1
set global general_log_file="C:\\Users\\pu\\Desktop\\general.log"
set global log_output='file'
set global general_log =1 //开启全局日志，开启后会记录所有sql
set global log_output='table' //记录sql到系统表general_log中
select * from mysql.general_log
2、记录到本地日志文件中
set global general_log =1
set global general_log_file="C:\\Users\\pu\\Desktop\\general.log"
set global log_output='file'
配置文件开启
#开启全局查询日志，只能在测试环境下使用，禁止在生产环境下使用
general_log=1
general_log_file=C:\\Program Files\\MySQL\MySQL Server 5.7\\log\\mysqlLog.log
# 日志输出格式
log_output=FILE
general_log=1
general_log_file=C:\\Program Files\\MySQL\MySQL Server 5.7\\log\\mysqlLog.log
# 日志输出格式
log_output=FILE
查看命令
1. 查看是否开启全局日志
show variables like 'general_log';
2. 查看全局查询日志的配置
show variables like "log_output%";
3.查看全局查询日志的文件
show variables like "general_log_file%";
show variables like 'general_log';
2. 查看全局查询日志的配置
show variables like "log_output%";
3.查看全局查询日志的文件
show variables like "general_log_file%";
总结
1. 开启全局查询日志之后，你所编写的每一条sql语句都会被记录，仅用作测试时回溯执行SQL的过程记录，配合profile使用
2. 比如在需要做系统问题分析时，观察和复现线上问题，可以在测试环境下模拟复现情况，使用全局日志可以用general_log这个表来收集什么时间段发生了什么样的SQL，帮助我们定位收集
2. 比如在需要做系统问题分析时，观察和复现线上问题，可以在测试环境下模拟复现情况，使用全局日志可以用general_log这个表来收集什么时间段发生了什么样的SQL，帮助我们定位收集
0 条评论
下一页
