如何分析Mysql慢SQL？

内容择要：

• 开启缓查询日记捕捉缓SQL
• 利用explain阐发缓SQL
• 利用show profile查询SQL施行细节
• 常睹的SQL语句劣化
  
  

1、开启缓查询日记捕捉缓SQL

① 查询mysql能否开启缓日记捕捉：

SHOW VARIABLES LIKE '%slow_query_log%';
赶钙代码

假如借出开启的话，开启：

SET GLOBAL slow_query_log=1;
赶钙代码

②  检察缓查询的工夫阙值：

SHOW GLOBAL VARIABLES LIKE '%long_query_time%';
赶钙代码

能够按照实践状况来调解工夫：

SET GLOBAL long_query_time=2;
赶钙代码

③ 查询几SQL超越了缓查询工夫的�谥担�

SHOW GLOBAL STATUS LIKE '%Slow_queries%';
赶钙代码

④ 利用MySQL供给的日记阐发东西mysqldumpslow，获得好缓SQL

举例：

--获得缓日记中最多的10个SQL

./mysqldumpslow -s r -t 10 /PATH/TO/缓日记文件
赶钙代码

--获得根据工夫排序的前10条内里露有左毗连的查询语句

mysqldumpslow -s t -t 10 -g "left join" 缓日记文件
赶钙代码

更多用法可使用：./mysqldumpslow --help 检察

2、利用explain阐发缓SQL

举例：

EXPLAIN SELECT a.username FROM tb_admin a LEFT JOIN tb_group p ON a.groupId = p.id WHERE a.username = 'xiaophai' LIMIT 1
赶钙代码

1、id

SELECT查询的序列号，包罗一组数字，暗示查询中施行SELECT语句或操纵表的挨次
包罗三种状况：

① id不异，施行挨次由上至下
② id差别，假如是子查询，id序号会递删，id值越年夜劣先级越下，越先被施行
③ 既有不异的，又有差别的。id假如不异以为是一组，施行挨次由上至下；正在一切组中，id值越年夜劣先级越下，越先执 止。

2、select_type

SIMPLE:   简朴SELECT查询，查询中没有包罗子查询大概UNION
     

PRIMARY: 查询终��露任何庞大的子部门，最中层的查询
   

SUBQUERY: SELECT或WHERE终��露的子查询部门
   

DERIVED: 正在FROM终��露的子查询被标识表记标帜为DERIVER(衍死)， MySQL会递回施行那些子查询，把成果放光临时表中
   

UNION: 若第两个SELECT呈现UNION，则被标识表记标帜为UNION, 若UNION包罗正在FROM子句的子查询中，中层子查询将被标识表记标帜为DERIVED
   

UNION RESULT: 从UNION表获得成果的SELECT

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范例的惯例，平常没有会呈现
   

const：假如经由过程索引顺次便找到了，const用于比力主键索引大概unique索引。由于只能婚配一止数据，以是很快。假如将主键置于where列表中，MySQL就可以将该查询转话讵一个常量
   

eq_ref：独一性索引扫描，关于每一个索引键，表中只要一笔记录蹼之婚配。常睹于主键或独一索引扫描
   

ref：非独一性索引扫描，返黄渐配某个零丁值的一切止。素质上也是一种索引会见，它返回一切婚配 某个零丁值的止，但是它能够会找到多个契合前提的止，以是它该当属于查找战扫描的混淆体
   

range：只检索给定范畴的止，利用一个索引去挑选止。key列显现利用了哪一个索引，普通便实邻您的where语句中呈现between、<、>、in等的查询，这类范畴扫描索引比齐表扫描要好，由于只需求开端于缩印的某一面，而完毕于另外一面，不消扫描局部索引
   

index：Full Index Scan ，index取ALL的区分为index范例只遍历索引树，那凡是比ALL快，由于索引文件凡是比数据文件小。（也便是道固然ALL战index皆是读齐表， 但index是从索引中读与的，而ALL是从硬盘读与的）

all：Full Table Scan，遍历齐表得到婚配的止

5、possible_keys
   

显现能够使用正在那张表中的索引，一个或多个。查询触及到的字段上若存正在索引，则该索引将被列出，但纷歧定被查询实践利用

6、key
   

实践利用的索引。假如为NULL，则出有利用索引。
   

查询中若呈现了笼盖索引，则该索引仅呈现正在key列表中。

7、key_len
   

暗示索引中利用的字节数，可经由过程该列计较查询中利用的索引的少队耄正在没有丧失粗度的状况下，少度越短越好。
   

key_len显现的值为索引字段的最年夜能够少度，并不是实践利用少度，即key_len史狴据表界说计较而得，没有是经由过程表内检索出的。

8、ref
   

显现索引的哪一列被利用了，哪些量�虺Ａ勘挥糜诓檎宜饕�列上的值。

9、rows
   

按照表统计疑息及索引选用状况，大抵预算出找到所需记载多需求读与的止数。

10、Extra
   

包罗没有合适正在其他列中显现但非常主要的分外疑息：
   

① Using filesort：阐明MySQL会对数据利用一个内部的索引排序，而没有是根据表内的索引挨次停止读与。MySQL中没法操纵索引完秤弈排序操纵称为“文件排序”
   

② Using temporary：利用了暂时表保留中心成果，MySQL正在对查询成果排序时利用暂时表。常睹于排序order by战分组查询group by
   

③ Using index：暗示响应的SELECT操纵中利用了笼盖索引（Covering Index），制止会见了表的数据止，服从没有错。假如同时呈现using where，表白索引被雍孟复止索引键值的查赵痘假如出有同时呈现using where，表白索援用去读与数据而非施行查找行动 笼盖索引（Covering Index）：了解方法1：SELECT的数据列只需求从索引中就可以读与到，没有需求读与数据止，MySQL能够操纵索引返回SELECT列表中 的字段，而没必要按照索引再次读与数据文件，换句话道查询列要被所建的索引笼盖 了解方法2：索引史徇效找到止的一个办法，可是普通数据库也能利用索引找迪苹个列的数据，因而他没必要读与全部止。究竟��结果索引叶子节面存储了他们索引的数据；当能经由过程读与索引就能够获得念要的数据，那便没有需求读与止了，一个索引 包罗了（笼盖）满意查询成果的数据便叫做笼盖索引 留意：假如要利用笼盖索引，必然要留意SELECT列表中只掏出需求的列，不成SELECT *, 由于假如一切字段一同做索引会招致索引文件过年夜查询机能降落
   

④ impossible where：WHERE子句的值老是false，不克不及雍么获得任何元组
   

⑤ select tables optimized away：正在出有GROUP BY子句的状况下基于索引劣化MIN/MAX操纵大概关于MyISAM存储引擎劣化COUNT(*)操纵， 没必要比及施行阶段再停止计较，查询施行方案天生的阶段即完成劣化
   

⑥ distinct：劣化distinct操纵，正在找到第一婚配的元祖后即截至找一样值的操纵

3、利用show profile查询SQL施行细节

Show Profile是MySQL供给能够雍么阐发当前会话中语句施行的资本耗损状况，能够用于SQL的调劣丈量

阐发步调

1、检察形态：SHOW VARIABLES LIKE 'profiling';

2、开启：set profiling=on;

3、检察成果：show profiles;

4、诊断SQL：show profile cpu,block io for query 上一步SQL数字号码;

ALL？�允疽磺锌�消疑息

BLOCK IO？�允綢O相干开消

CONTEXT SWITCHES？�允靖叩臀那开幌喔煽�消

CPU？�允綜PU相干开消

IPC？�允臼帐樟焓芟喔煽�消

MEMORY？�允灸诖嫦喔煽�消

PAGE FAULTS？�允疽忱锩�病相干开消

SOURCE？�允菊絊ource_function，Source_file，Source_line相干开消

SWAPS？�允窘涣鞔问�相干开消
     

留意（碰到那几种状况要劣化）
   

converting HEAP to MyISAM：查询成果太年夜，内存不敷用往磁盘上搬

Creating tmp table：创立暂时表

Copying to tmp table on disk：把内存中的暂时表赶钙到磁盘   

locked

4、常睹的SQL语句劣化

1、只管制止正在where字句中利用or去毗连前提，不然将招致抛却利用索引而停止齐表扫描。

select id from user where username='15623695987' or mobile='15623695987';
赶钙代码

能够如许查询:

select id from user where username='15623695987' union all select id from user where mobile='15623695987';
赶钙代码

2、准确利用like查询。

%xx%查询会招致索引利用没有上：

select id from user where username like '%test%'
赶钙代码

准确利用索引:

select id from user where username like 'test%'
赶钙代码

3、只管制止正在where字句中对字段停止表达式操纵

毛病做法：

select id from user where score/2 =100;
赶钙代码

应改成：

select id from user where score =100*2;
赶钙代码

4、假如确认查询成果数目，应尽量减上limit

select id from user where username='test' limit 1;
赶钙代码

5、不消要利用隐式转换

毛病例子：

select id from user where mobile=15689764359 limit 1;
select username from user where id='15' limit 1;
赶钙代码

准确做法：

select id from user where mobile='15689764359' limit 1;
select username from user where id=15 limit 1;
赶钙代码

6、准确利用盖锵索引

举例：user表，index  idx_username(username,group_id)

能利用索引：

select id from user where username='test' and group_id=1;
select id from user where username='test'
select id from user where group_id=1 and username='test'
赶钙代码

不克不及利用索引：

select id from user where group_id=1;
赶钙代码

总结：能否利用索引取idx_username(username,group_id)，那两个字段当比后挨次庸呢

7、假如利用了join，请只管利用小表join年夜表

8、准确利用exists战in

① in

select `user`.id,`user`.username from `user` where `user`.id in (select `order`.user_id from `order`)
赶钙代码

② exists

select `user`.id,`user`.username from `user` where exists (select`order`.user_id from `order` where `user`.id = `order`.user_id)
赶钙代码

使用场疚拷寮：假如子查询得出的成果散记载较少，主查询中的表较年夜且又有索引时该当用in, 废府假如中层的主查询记载较少，子查询中的表年夜，又有索引时利用exists

我是个凑数的。。。

介是神马？！！