MySQL慢查询的坑

记得那是一条查询SQL，数据量万级时还保持在0.2秒内，随着某一段时间数据猛增，耗时一度达到了2-3秒！没有命中索引，导致全表扫描。explain 中extra显示：Using where; Using temporary; Using filesort，被迫使用了临时表排序，由于是高频查询，并发一起来很快就把DB线程池打满了，导致大量查询请求堆积，DB服务器cpu长时间100%+，大量请求timeout。。最终系统崩溃。老板登场~

对了，那次是十月二日晚上8点半，我在老家枣庄，和哥儿几个正坐在大排档吹着牛B！你猜，我将面临什么尴尬局面？

可见，团队如果对慢查询不引起足够的重视，风险是很大的。经过那次事故我们老板就说了：谁的代码再出现类似事故，开发和部门领导一起走人，吓得一大堆领导心发慌，赶紧招了两位DBA同事🙂🙂🙂。

慢查询，顾名思义，执行很慢的查询。有多慢？超过 long_query_time 参数设定的时间阈值（默认10s），就被认为是慢的，是需要优化的。慢查询被记录在慢查询日志里。

慢查询日志默认是不开启的，如果你需要优化SQL语句，就可以开启这个功能，它可以让你很容易地知道哪些语句是需要优化的（想想一个SQL要10s就可怕）。

墨菲定律：会出错的事情就一定会出错。

这是太真实的事情之一了。为了防患于未然，一起来看看慢查询该怎么处理。本文很干，记得接杯水，没时间看的先收藏哦！

一、慢查询配置

1-1、开启慢查询

MySQL支持通过

1、输入命令开启慢查询（临时），在MySQL服务重启后会自动关闭；
2、配置my.cnf（windows是my.ini）系统文件开启，修改配置文件是持久化开启慢查询的方式。

方式一：通过命令开启慢查询

步骤1、查询 slow_query_log 查看是否已开启慢查询日志：

show variables like '%slow_query_log%';

set global slow_query_log='ON';

如下我设置成了1秒，执行时间超过1秒的SQL将记录到慢查询日志中

set global long_query_time=1;

show variables like '%slow_query_log_file%';

步骤5、核对慢查询开启状态

需要退出当前MySQL终端，重新登录即可刷新；

配置了慢查询后，它会记录以下符合条件的SQL：

查询语句
数据修改语句
已经回滚的SQL

方式二：通过配置my.cnf（windows是my.ini）系统文件开启

（版本：MySQL5.5及以上）

在my.cnf文件的[mysqld]下增加如下配置开启慢查询，如下图

# 开启慢查询功能slow_query_log=ON# 指定记录慢查询日志SQL执行时间得阈值long_query_time=1# 选填，默认数据文件路径# slow_query_log_file=/var/lib/mysql/localhost-slow.log

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秒的SQL语句（测试）

第一行：记录时间
第二行：用户名、用户的IP信息、线程ID号
第三行：执行花费的时间【单位：秒】、执行获得锁的时间、获得的结果行数、扫描的数据行数
第四行：这SQL执行的时间戳
第五行：具体的SQL语句

二、Explain分析慢查询SQL

分析mysql慢查询日志 ,利用explain关键字可以模拟优化器执行SQL查询语句，来分析sql慢查询语句，下面我们的测试表是一张137w数据的app信息表，我们来举例分析一下；

SQL示例如下：

-- 1.185sSELECT * from vio_basic_domain_info where app_name like '%陈哈哈%' ;

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)

-- 0.156sSELECT * from vio_basic_domain_info where app_name like '陈哈哈%' ;

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)

-- 0.091sSELECT app_name from vio_basic_domain_info where app_name like '陈哈哈%' ;

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)

2-1、各列属性的简介：

id:SELECT的查询序列号，体现执行优先级，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
select_type:表示查询的类型。
table:输出结果集的表，如设置了别名，也会显示
partitions:匹配的分区
type:对表的访问方式
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明

一、慢查询配置

1-1、开启慢查询

二、Explain分析慢查询SQL

更多相关文章

随机推荐