共计 5045 个字符，预计需要花费 13 分钟才能阅读完成。
内容摘要：
- 开启慢查问日志捕捉慢 SQL
- 应用 explain 剖析慢 SQL
- 应用 show profile 查问 SQL 执行细节
- 常见的 SQL 语句优化
一、开启慢查问日志捕捉慢 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 查看
二、应用 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 11、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 操作，在找到第一匹配的元祖后即进行找同样值的操作
三、应用 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：显示 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 语句优化
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
