今日分享开始啦，请大家多多指教~
明天这篇文章给大家介绍一下MySQL的慢查问，全名是慢查问日志，是MySQL提供的一种日志记录，用来记录在MySQL中响应工夫超过阀值的语句。通过这篇文章，心愿你Get到了一些剖析MySQL慢查问的办法和心得。慢查问，在MySQL中始终是绕不开的话题，慢的形式多种多样，如果你想完全避免慢查问？年轻人，我倡议你耗子尾汁~~话不多说，注释开始啦！
一、慢查问配置
1-1、开启慢查问
MySQL反对通过
1、输出命令开启慢查问（长期），在MySQL服务重启后会主动敞开；
2、配置my.cnf（windows是my.ini）系统文件开启，批改配置文件是长久化开启慢查问的形式。
形式一：通过命令开启慢查问
步骤1、查问 slow_query_log 查看是否已开启慢查问日志：
show variables like '%slow_query_log%';
mysql> show variables like '%slow_query_log%'; +---------------------+-----------------------------------+ | Variable_name | Value | +---------------------+-----------------------------------+ | slow_query_log | OFF | | slow_query_log_file | /var/lib/mysql/localhost-slow.log | +---------------------+-----------------------------------+ 2 rows in set (0.01 sec)步骤2、开启慢查问命令：
步骤2、开启慢查问命令：
set global slow_query_log='ON';
步骤3、指定记录慢查问日志SQL执行工夫的阈值（long_query_time 单位：秒，默认10秒）
如下我设置成了1秒，执行工夫超过1秒的SQL将记录到慢查问日志中
set global long_query_time=1;
步骤4、查问 “慢查问日志文件寄存地位”
show variables like '%slow_query_log_file%'; mysql> show variables like '%slow_query_log_file%'; +---------------------+-----------------------------------+ | Variable_name | Value | +---------------------+-----------------------------------+ | slow_query_log_file | /var/lib/mysql/localhost-slow.log | +---------------------+-----------------------------------+ 1 row in set (0.01 sec)
slow_query_log_file 指定慢查问日志的存储门路及文件（默认和数据文件放一起）
步骤5、核查慢查问开启状态
须要退出以后MySQL终端，从新登录即可刷新；
配置了慢查问后，它会记录以下符合条件的SQL：
- 查问语句
- 数据批改语句
- 曾经回滚的SQL
形式二：通过配置my.cnf（windows是my.ini）系统文件开启
（版本：MySQL5.5及以上）
在my.cnf文件的[mysqld]下减少如下配置开启慢查问，如下图
重启数据库后即长久化开启慢查问，查问验证如下：
mysql> show variables like '%_query_%'; +------------------------------+-----------------------------------+ | Variable_name | Value | +------------------------------+-----------------------------------+ | have_query_cache | YES | | long_query_time | 1.000000 | | slow_query_log | ON | | slow_query_log_file | /var/lib/mysql/localhost-slow.log | +------------------------------+-----------------------------------+ 6 rows in set (0.01 sec)
1
![上传中…]()
如上图，是执行工夫超过1秒的SQL语句（测试）
- 第一行：记录时间
- 第二行：用户名 、用户的IP信息、线程ID号
- 第三行：执行破费的工夫【单位：秒】、执行取得锁的工夫、取得的后果行数、扫描的数据行数
- 第四行：这SQL执行的工夫戳
- 第五行：具体的SQL语句
二、Explain剖析慢查问SQL
剖析mysql慢查问日志 ,利用explain关键字能够模仿优化器执行SQL查问语句，来剖析sql慢查问语句，上面咱们的测试表是一张137w数据的app信息表，咱们来举例剖析一下；
SQL示例如下：
-- 1.185s SELECT * from vio_basic_domain_info where app_name like '%欣欣%' ;
这是一条一般的含糊查问语句，查问耗时：1.185s，查到了148条数据； 咱们用Explain剖析后果如下表，依据表信息可知：该SQL没有用到字段app_name上的索引，查问类型是全表扫描，扫描行数137w。
mysql> EXPLAIN SELECT * from vio_basic_domain_info where app_name like '%欣欣%' ; +----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | vio_basic_domain_info | NULL | ALL | NULL | NULL | NULL | NULL | 1377809 | 11.11 | Using where | +----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
当这条SQL应用到索引时，SQL如下：查问耗时：0.156s，查到141条数据
-- 0.156s SELECT * from vio_basic_domain_info where app_name like '欣欣%' ;
Explain剖析后果如下表；依据表信息可知：该SQL用到了idx_app_name索引，查问类型是索引范畴查问，扫描行数141行。因为查问的列不全在索引中（select *），因而回表了一次，取了其余列的数据。
mysql> EXPLAIN SELECT * from vio_basic_domain_info where app_name like '欣欣%' ; +----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | vio_basic_domain_info | NULL | range | idx_app_name | idx_app_name | 515 | NULL | 141 | 100.00 | Using index condition | +----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)
当这条SQL应用到笼罩索引时，SQL如下：查问耗时：0.091s，查到141条数据
-- 0.091s SELECT app_name from vio_basic_domain_info where app_name like '欣欣%' ;
Explain剖析后果如下表；依据表信息可知：和下面的SQL一样应用到了索引，因为查问列就蕴含在索引列中，又省去了0.06s的回表工夫。
mysql> EXPLAIN SELECT app_name from vio_basic_domain_info where app_name like '欣欣%' ; +----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | vio_basic_domain_info | NULL | range | idx_app_name | idx_app_name | 515 | NULL | 141 | 100.00 | Using where; Using index | +----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
那么是如何通过EXPLAIN解析后果剖析SQL的呢？各列属性又代表着什么？一起往下看。
2-1、各列属性的简介：
- id:SELECT的查问序列号，体现执行优先级，如果是子查问，id的序号会递增，id值越大优先级越高，越先被执行
- select_type:示意查问的类型。
- table:输入后果集的表，如设置了别名，也会显示
- partitions:匹配的分区
- type:对表的拜访形式
- possible_keys:示意查问时，可能应用的索引
- key:示意理论应用的索引
- key_len:索引字段的长度
- ref:列与索引的比拟
- rows:扫描出的行数(估算的行数)
- filtered:按表条件过滤的行百分比
- Extra:执行状况的形容和阐明
以上标星的几类是咱们优化慢查问时罕用到的
2-2、慢查问剖析罕用到的属性
1、type：
对表拜访形式，示意MySQL在表中找到所需行的形式，又称“拜访类型”。
存在的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从低到高），介绍三个咱们天天见到的：
ALL：(Full Table Scan) MySQL将遍历全表以找到匹配的行，常说的全表扫描
index: (Full Index Scan) index与ALL区别为index类型只遍历索引树
range:只检索给定范畴的行，应用一个索引来抉择行
2、key
key列显示了SQL理论应用索引，通常是possible_keys列中的索引之一，MySQL优化器个别会通过计算扫描行数来抉择更适宜的索引，如果没有抉择索引，则返回NULL。当然，MySQL优化器存在抉择索引谬误的状况，能够通过批改SQL强制MySQL“应用或漠视某个索引”。
- 强制应用一个索引：FORCE INDEX (index_name)、USE INDEX (index_name)
- 强制疏忽一个索引：IGNORE INDEX (index_name)
3、rows
rows是MySQL预计为了找到所需的行而要读取（扫描）的行数，可能不准确。
4、Extra
这一列显示一些额定信息，很重要。
Using index
查问的列被索引笼罩，并且where筛选条件是索引的是前导列，Extra中为Using index。意味着通过索引查找就能间接找到符合条件的数据，毋庸回表。
注：前导列个别指联结索引中的第一列或“前几列”，以及单列索引的状况；这里为了不便了解我统称为前导列。
Using where
阐明MySQL服务器将在存储引擎检索行后再进行过滤；即没有用到索引，回表查问。
可能的起因：
- 查问的列未被索引笼罩；
- where筛选条件非索引的前导列或无奈正确应用到索引；
Using temporary
这意味着MySQL在对查问后果排序时会应用一个长期表。
Using filesort
阐明MySQL会对后果应用一个内部索引排序，而不是按索引秩序从表里读取行。
Using index condition
查问的列不全在索引中，where条件中是一个前导列的范畴
Using where;Using index
查问的列被索引笼罩，并且where筛选条件是索引列之一，但不是索引的前导列或呈现了其余影响间接应用索引的状况（如存在范畴筛选条件等），Extra中为Using where; Using index，意味着无奈间接通过索引查找来查问到符合条件的数据，影响并不大。
三、一些慢查问优化教训分享
3-1、优化LIMIT分页
在零碎中须要分页的操作通常会应用limit加上偏移量的办法实现，同时加上适合的order by 子句。如果有对应的索引，通常效率会不错，否则MySQL须要做大量的文件排序操作。
一个十分令人头疼问题就是当偏移量十分大的时候，例如可能是limit 1000000,10这样的查问，这是mysql须要查问1000000条而后只返回最初10条，后面的1000000条记录都将被舍弃，这样的代价很高，会造成慢查问。
优化此类查问的一个最简略的办法是尽可能的应用索引笼罩扫描，而不是查问所有的列。而后依据须要做一次关联操作再返回所需的列。对于偏移量很大的时候这样做的效率会失去很大晋升。
对于上面的查问：
-- 执行耗时：1.379s SELECT * from vio_basic_domain_info LIMIT 1000000,10;
Explain剖析后果：
mysql> EXPLAIN SELECT * from vio_basic_domain_info LIMIT 1000000,10; +----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------+ | 1 | SIMPLE | vio_basic_domain_info | NULL | ALL | NULL | NULL | NULL | NULL | 1377809 | 100.00 | NULL | +----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
该语句存在的最大问题在于limit M,N中偏移量M太大，导致每次查问都要先从整个表中找到满足条件 的前M条记录，之后舍弃这M条记录并从第M+1条记录开始再顺次找到N条满足条件的记录。如果表十分大，且筛选字段没有适合的索引，且M特地大那么这样的代价是十分高的。
那么如果咱们下一次的查问能从前一次查问完结后标记的地位开始查找，找到满足条件的10条记录，并记下下一次查问应该开始的地位，以便于下一次查问能间接从该地位 开始，这样就不用每次查问都先从整个表中先找到满足条件的前M条记录，舍弃掉，再从M+1开始再找到10条满足条件的记录了。
解决分页慢查问的形式个别有以下几种
思路一：结构笼罩索引
通过批改SQL，应用上笼罩索引，比方我须要只查问表中的app_name、createTime等大量字段，那么我秩序在app_name、createTime字段设置联结索引，即可实现笼罩索引，无需全表扫描。实用于查问列较少的场景，查问列数过多的不举荐。
耗时：0.390s
mysql> EXPLAIN SELECT app_name,createTime from vio_basic_domain_info LIMIT 1000000,10; +----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+---------+----------+-------------+ | id | select_type | table <b style="color:transparent">来源gao@!dai!ma.com搞$$代^@码网</b> | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | vio_basic_domain_info | NULL | index | NULL | idx_app_name | 515 | NULL | 1377809 | 100.00 | Using index | +----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
思路二：优化offset
无奈用上笼罩索引，那么重点是想方法疾速过滤掉前100w条数据。咱们能够利用自增主键有序的条件，先查问出第1000001条数据的id值，再往后查10行；实用于主键id自增的场景。耗时：0.471s
SELECT * from vio_basic_domain_info where id >=(SELECT id from vio_basic_domain_info ORDER BY id limit 1000000,1) limit 10;
原理：先基于索引查问出第1000001条数据对应的主键id的值，而后间接通过该id的值间接查问该id前面的10条数据。下方EXPLAIN 剖析后果中大家能够看到这条SQL的两步执行流程。
mysql> EXPLAIN SELECT * from vio_basic_domain_info where id >=(SELECT id from vio_basic_domain_info ORDER BY id limit 1000000,1) limit 10; +----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ | 1 | PRIMARY | vio_basic_domain_info | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 10 | 100.00 | Using where | | 2 | SUBQUERY | vio_basic_domain_info | NULL | index | NULL | PRIMARY | 8 | NULL | 1000001 | 100.00 | Using index | +----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ 2 rows in set, 1 warning (0.40 sec)
办法三：“提早关联”
耗时：0.439s提早关联实用于数量级较大的表，SQL如下；
SELECT * from vio_basic_domain_info inner join (select id from vio_basic_domain_info order by id limit 1000000,10) as myNew using(id);
这里咱们利用到了笼罩索引+提早关联查问，相当于先只查问id列，利用笼罩索引疾速查到该页的10条数据id，而后再把返回的10条id拿到表中通过主键索引二次查问。（表数据增速快的状况对该办法影响较小。）
mysql> EXPLAIN SELECT * from vio_basic_domain_info inner join (select id from vio_basic_domain_info order by id limit 1000000,10) as myNew using(id); +----+-------------+-----------------------+------------+--------+---------------+---------+---------+----------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------+------------+--------+---------------+---------+---------+----------+---------+----------+-------------+ | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 1000010 | 100.00 | NULL | | 1 | PRIMARY | vio_basic_domain_info | NULL | eq_ref | PRIMARY | PRIMARY | 8 | myNew.id | 1 | 100.00 | NULL | | 2 | DERIVED | vio_basic_domain_info | NULL | index | NULL | PRIMARY | 8 | NULL | 1000010 | 100.00 | Using index | +----+-------------+-----------------------+------------+--------+---------------+---------+---------+----------+---------+----------+-------------+ 3 rows in set, 1 warning (0.00 sec)
3-2、排查索引没起作用的状况
含糊查问尽量避免用通配符’%’结尾，会导致数据库引擎放弃索引进行全表扫描。如下：
SELECT * FROM t WHERE username LIKE '%欣欣%'
优化形式：尽量在字段前面应用含糊查问。如下：
SELECT * FROM t WHERE username LIKE '欣欣%'
如果需要是要在后面应用含糊查问，
- 应用MySQL内置函数INSTR(str,substr) 来匹配，作用相似于java中的indexOf()，查问字符串呈现的角标地位。
- 应用FullText全文索引，用match against 检索
- 数据量较大的状况，倡议援用ElasticSearch、solr，亿级数据量检索速度秒级
- 当表数据量较少（几千条儿那种），别整花里胡哨的，间接用like ‘%xx%’。
但不得不说，MySQL含糊匹配大字段是硬伤，毕竟保障事务的ACID个性消耗了太多性能，因而，如果理论场景中有相似业务需要，倡议果决更换大数据存储引擎如ElasticSearch、Hbase等。这里和情怀无关~
2.尽量避免应用 not in，会导致引擎走全表扫描。倡议用 not exists 代替，如下：
-- 不走索引
SELECT * FROM t WHERE name not IN ('提莫','队长');
-- 走索引
select * from t as t1 where not exists (select * from t as t2 where name IN ('提莫','队长') and t1.id = t2.id);
3.尽量避免应用 or，会导致数据库引擎放弃索引进行全表扫描。如下：
SELECT * FROM t WHERE id = 1 OR id = 3
优化形式：能够用union代替or。如下：
SELECT * FROM t WHERE id = 1 UNION SELECT * FROM t WHERE id = 3
4.尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描。如下：
SELECT * FROM t WHERE score IS NULL
优化形式：能够给字段增加默认值0，对0值进行判断。如下：
SELECT * FROM t WHERE score = 0
5.尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。
能够将表达式、函数操作挪动到等号右侧。如下：
-- 全表扫描 SELECT * FROM T WHERE score/10 = 9 -- 走索引 SELECT * FROM T WHERE score = 10*9
6.当数据量大时，防止应用where 1=1的条件。通常为了不便拼装查问条件，咱们会默认应用该条件，数据库引擎会放弃索引进行全表扫描。如下：
SELECT username, age, sex FROM T WHERE 1=1
优化形式：用代码拼装sql时进行判断，没 where 条件就去掉 where，有where条件就加 and。
7.查问条件不要用 <> 或者 !=
应用索引列作为条件进行查问时，须要防止应用<>或者!=等判断条件。如的确业务须要，应用到不等于符号，须要在从新评估索引建设，防止在此字段上建设索引，改由查问条件中其余索引字段代替。
8.where条件仅蕴含复合索引非前导列
如：复合（联结）索引蕴含key_part1，key_part2，key_part3三列，但SQL语句没有蕴含索引前置列”key_part1″，依照MySQL联结索引的最左匹配准则，不会走联结索引。
-- 不走索引 select col1 from table where key_part2=1 and key_part3=2 -- 走索引 select col1 from table where key_part1 =1 and key_part2=1 and key_part3=2
9.隐式类型转换造成不应用索引
如下SQL语句因为索引队列类型为varchar，但给定的值为数值，波及隐式类型转换，造成不能正确走索引。
select col1 from table where col_varchar=123;
小结：
咱们须要的是及时发现并解决慢查问，其实很多慢查问是被动呈现的，比方因为某业务数据量猛增数量级变动、因为业务需要变动而改了字段或操作了既有索引等。尽管不是你的错，但这锅可能还得你来背~
今日份分享已完结，请大
转载请注明原文链接：关于java:从天而降一口锅MySQL慢查询的这几个坑你踩过没真是防不胜防
