SQL分析优化

# 查看是否开启慢日志记录show variables like 'slow_query%';#设置开启慢日志set GLOBAL slow_query_log=\"ON\";#设置慢日志记录时间阈值show variables like '%long_query%';#查看有多少慢日志show global status like 'slow_queries';

相关配置及命令

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查询结果参数： Count ：代表这个SQL执行了多少次Time ：代表执行的时间，括号里面是累计时间Lock ：表示锁定的时间，括号是累计Rows ：表示返回的记录数，括号是累计

mysqldumpslow工具

1. 使用预编译提交SQL执行速度2. 当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减少哪些有列名歧义引起的语法错误3. 多条SQL语句压缩到一句SQL中4. 当SQL语句需要union两个查询结果集合时，如果可以判断检索结果中不会有重复的记录时候，应该用union all5. 避免使用HAVING字句，因为HAVING只会在检索出所有记录之后才对结果集进行过滤，而where则是在聚合前刷选记录，如果能通过where字句限制记录的数目，那就能减少这方面的开销6. 考虑使用“临时表”暂存中间结果，可以避免程序中多次扫描主表，也大大减少了程序执行中“共享锁”阻塞“更新锁”，减少了阻塞，提高了并发性能

SQL优化建议

慢sql优化

数据库内部对SQL语句分析后决定的执行路径和执行步骤，通俗的说是数据库服务器在执行sql语句的时候，会准备几套方案，最后选择消耗资源最小的方案

执行计划概念

id：id表示执行顺序，id值越大，越先执行；子查询，id的序号会递增;id值相同的情况，可以理解为一组，执行顺序是从上向下。

select_type：SIMPLE：简单的select语句，不包含子查询或者unionPRIMARY：查询中包含复杂查询（比如子查询），最外层查询被标记为PRIMARYSUBQUERY：当select或where包含了子查询，该子查询被标记为SUBQUERYDERIVED：表示包含在from子句中的子查询的selectUNION：union中第二个或后面的select语句UNION RESULT：代表从union的临时表中读取数据

table：表名，也有可能是别名或者临时表

partitions：当查询的是分区表时，partitions显示分区表命中的分区情况

type:它在 SQL优化中是一个非常重要的指标，以下性能从好到坏依次是：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALLsystem：当表仅有一行记录时(系统表)，数据量很少，往往不需要进行磁盘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可能通过某个索引检索到数据，但不一定最终查询会用到

key:key是查询中实际使用到的索引，若没有使用索引，显示为NULL

key_len:表示查询用到的索引长度（字节数），原则上长度越短越好单列索引，那么需要将整个索引长度算进去；多列索引，不是所有列都能用到，需要计算查询中实际用到的列。注：key_len只计算where条件中用到的索引长度，而排序和分组即便是用到了索引，也不会计算到key_len中。

ref:显示该表的索引字段关联了哪张表的哪个字段

rows：根据表统计信息及选用情况，大致估算出找到所需的记录或所需读取的行数，数值越小越好

filtered:filtered 这个是一个百分比的值，表里符合条件的记录数的百分比。这个字段表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例

Explain各个字段含义

根据MySQL执行计划的输出，分析索引使用情况、扫描的行数可以预估查询效率；进而可以重构SQL语句、调整索引，提升查询效率。

作用总结

explain执行计划

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之后；

简介

Show Profile命令结果

相关命令

①ALL：显示所有的开销信息。②BLOCK IO：显示块IO开销。③CONTEXT SWITCHES：上下文切换开销。④CPU：显示CPU开销信息。⑤IPC：显示发送和接收开销信息。⑥MEMORY：显示内存开销信息。⑦PAGE FAULTS：显示页面错误开销信息。⑧SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息。⑨SWAPS：显示交换次数开销信息。

常用查询参数

①converting  HEAP to MyISAM：查询结果太大，内存不够，数据往磁盘上搬了②Creating tmp table：创建临时表。先拷贝数据到临时表，用完后再删除临时表③Copying to tmp table on disk：把内存中临时表复制到磁盘上，危险！！！④locked

show profile常见诊断需优化结果

1. show profile默认是关闭的，并且开启后只存活于当前会话，也就说每次使用前都需要开启。2. 通过show profiles查看sql语句的耗时时间，然后通过show profile命令对耗时时间长的sql语句进行诊断。3. 注意show profile诊断结果中出现相关字段的含义，判断是否需要优化sql语句。

总结

Show Profile

该功能主要用于测试环境，在生产环境中永远不要开启该功能。

注意事项

1、记录到表中      set global general_log =1    //开启全局日志，开启后会记录所有sql      set global log_output='table'     //记录sql到系统表general_log中      select * from mysql.general_log2、记录到本地日志文件中      set global general_log =1      set global general_log_file=\"C:\\\\Users\\\\pu\\\\Desktop\\\\general.log\"      set global log_output='file'

命令开启

#开启全局查询日志，只能在测试环境下使用，禁止在生产环境下使用general_log=1general_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%\";

查看命令

开启设置

1. 开启全局查询日志之后，你所编写的每一条sql语句都会被记录，仅用作测试时回溯执行SQL的过程记录，配合profile使用2. 比如在需要做系统问题分析时，观察和复现线上问题，可以在测试环境下模拟复现情况，使用全局日志可以用general_log这个表来收集什么时间段发生了什么样的SQL，帮助我们定位收集

全局日志

SQL分析优化