explain 使⽤+慢SQL 分析
⼀、开启慢查询⽇志，捕获慢SQL
⼆、explain+慢SQL分析
三、show profile查询SQL语句在服务器中的执⾏细节和⽣命周期
四、SQL数据库服务器参数调优
⼀、开启慢查询⽇志，捕获慢SQL
1
、查看慢查询⽇志是否开启
2
、开启慢查询⽇志
3、查看慢查询⽇志阙值
这个值表⽰超过多长时间的SQL语句会被记录到慢查询⽇志中
4、设置慢查询⽇志阙值[sql]
1. <span >SHOW VARIABLES LIKE  '%slow_query_log%';</span>
[sql]
1. <span >SET  GLOBAL  slow_query_log=1;</span>
[sql]
1. <span >SHOW [GLOBAL ] VARIABLES LIKE  '%long_query_time%';</span>
5、查看多少SQL 语句超过了阙值
6、MySQL 提供的⽇志分析⼯具mysqldumpslow
进⼊MySQL的安装⽬录中的bin⽬录下
执⾏ ./mysqldumpslow --help 查看帮助命令
常⽤参考：
得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 slow.log
得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 slow.log
得到按照时间排序的前10条⾥⾯含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" slow.log
使⽤这些语句时结合| more使⽤
⼆、explain+慢SQL 分析
使⽤EXPLAIN关键字可以模拟优化器执⾏SQL查询语句，从⽽知道MySQL是 如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
使⽤⽅式：Explain+SQL语句
执⾏计划包含的信息：
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
[sql]
1. <span >SET  GLOBAL  long_query_time=3;</span>
[sql]
1. <span >SHOW GLOBAL  STATUS LIKE  '%Slow_queries%';</span>
|  id  |  select_type |  table  | type  | possible_keys | key  | key_len  | ref    | rows  | Extra  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1、id
SELECT查询的序列号，包含⼀组数字，表⽰查询中执⾏SELECT语句或操作表的顺序
包含三种情况：
1.id相同，执⾏顺序由上⾄下
2.id不同，如果是⼦查询，id序号会递增，id值越⼤优先级越⾼，越先被执⾏
3.id既有相同的，⼜有不同的。id如果相同认为是⼀组，执⾏顺序由上⾄下； 在所有组中，id值越⼤优先级越⾼，越先执    ⾏。
2、select_type
SIMPLE：简单SELECT查询，查询中不包含⼦查询或者UNION
SIMPLE
PRIMARY：查询中包含任何复杂的⼦部分，最外层的查询
SUBQUERY：SELECT或WHERE中包含的⼦查询部分
DERIVED：在FROM中包含的⼦查询被标记为DERIVER(衍⽣)， MySQL会递归执⾏这些⼦查询，把结果放到临时表中
UNION
UNION：若第⼆个SELECT出现UNION，则被标记为UNION, 若UNION包含在FROM⼦句的⼦查询中，外层⼦查询将被标记为DERIVED
UNION RESULT：从UNION表获取结果的SELECT
UNION RESULT
3、table
显⽰这⼀⾏数据是关于哪张表的
4、type
type显⽰的是访问类型，是较为重要的⼀个指标，结果值从最好到最坏依次是：
system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL
⼀般来说，得保证查询⾄少达到range级别，最好能达到ref。
system：表只有⼀⾏记录（等于系统表），这是const类型的特例，平时不会出现
system
const：如果通过索引依次就到了，const⽤于⽐较主键索引或者unique索引。 因为只能匹配⼀⾏数据，所以很快。如果将主键置于where列表
中，MySQL就能将该查询转换为⼀个常量
eq_ref：唯⼀性索引扫描，对于每个索引键，表中只有⼀条记录与之匹配。常见于主键或唯⼀索引扫描
eq_ref
ref：⾮唯⼀性索引扫描，返回匹配某个单独值的所有⾏。本质上也是⼀种索引访问，它返回所有匹配
某个单独值的⾏，然⽽它可能会到多个符合条件的⾏，ref
所以它应该属于查和扫描的混合体
range
range：只检索给定范围的⾏，使⽤⼀个索引来选择⾏。key列显⽰使⽤了哪个索引，⼀般就是在你的where语句中出现between、<、>、in等的查询，这种范围扫描索引⽐全表扫描要好，因为只需要开始于缩印的某⼀点，⽽结束于另⼀点，不⽤扫描全部索引
index：Full Index Scan ，index与ALL的区别为index类型只遍历索引树，这通常⽐ALL快，因为索引⽂件通常⽐数据⽂件⼩。 （也就是说虽然ALL和index 都是读全表， 但index是从索引中读取的，⽽ALL是从硬盘读取的）
all：Full Table Scan，遍历全表获得匹配的⾏
all
5、possible_keys
显⽰可能应⽤在这张表中的索引，⼀个或多个。 查询涉及到的字段上若存在索引，则该索引将被列出，但不⼀定被查询实际使⽤
6、key
实际使⽤的索引。如果为NULL，则没有使⽤索引。
查询中若出现了覆盖索引，则该索引仅出现在key列表中。
7、key_len
表⽰索引中使⽤的字节数，可通过该列计算查询中使⽤的索引的长度。在不损失精度的情况下，长度越短越好。
key_len显⽰的值为索引字段的最⼤可能长度，并⾮实际使⽤长度，即key_len是根据表定义计算⽽得，不是通过表内检索出的。
8、ref
显⽰索引的哪⼀列被使⽤了，哪些列或常量被⽤于查索引列上的值。
9、rows
根据表统计信息及索引选⽤情况，⼤致估算出到所需记录多需要读取的⾏数。
10、Extra
包含不适合在其他列中显⽰但⼗分重要的额外信息：
1、Using filesort： 说明MySQL会对数据使⽤⼀个外部的索引排序，⽽不是按照表内的索引顺序进⾏读取。MySQL中⽆法利⽤索引完成的排序操作称为“⽂件排序”
2、Using temporary：  使⽤了临时表保存中间结果，MySQL在对查询结果排序时使⽤临时表。常见于排序order by和分组查询group by
3、Using index： 表⽰相应的SELECT操作中使⽤了覆盖索引（Covering Index），避免访问了表的数据⾏，效率不错。 如果同时出现using where，表明索引被⽤来执⾏索引键值的查； 如果没有同时出现using where，表明索引⽤来读取数据⽽⾮执⾏查动作 覆盖索引（Covering Index）： 理解⽅式1：SELECT的数据列只需要从索引中就能读取到，不需要读取数据⾏，MySQL可以利⽤索引返回SELECT列表中 的字段，⽽不必根据索引再次读取数据⽂件，换
句话说查询列要被所建的索引覆盖 理解⽅式2：索引是⾼效到⾏的⼀个⽅法，但是⼀般数据库也能使⽤索引到⼀个列的数据，因此他不必读取整个⾏。 毕竟索引叶⼦节点存储了他们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取⾏了，⼀个索引 包含了（覆盖）满⾜查询结果的数据就叫做覆盖索引 注意： 如果要使⽤覆盖索引，⼀定要注意SELECT列表中只取出需要的列，不可SELECT *, 因为如果所有字段⼀起做索引会导致索引⽂件过⼤查询性能下降
6、impossible where： WHERE⼦句的值总是false，不能⽤来获取任何元组
7、select tables optimized away： 在没有GROUP BY⼦句的情况下基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作， 不必等到执⾏阶段再进⾏计算，查询执⾏计划⽣成的阶段即完成优化
8、distinct： 优化distinct操作，在到第⼀匹配的元祖后即停⽌同样值的操作
三、show profile查询SQL语句在服务器中的执⾏细节和⽣命周期
Show Profile是MySQL提供可以⽤来分析当前会话中语句执⾏的资源消耗情况，可以⽤于SQL的调优测量
默认关闭，并保存最近15次的运⾏结果
分析步骤
1、查看状态：SHOW VARIABLES LIKE 'profiling';
2、开启：set profiling=on;
3、查看结果：show profiles;
4、诊断SQL：show profile cpu,block io for query 上⼀步SQL数字号码;
sql语句优化方式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
四、SQL数据库服务器参数调优
当order by 和 group by⽆法使⽤索引时，增⼤max_length_for_sort_data参数设置和增⼤sort_buffer_size参数的设置

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。