header-bg.jpg
记一次奇怪的慢查询
发表于 2019-03-20 00:55
|
分类于 MySQL
|
评论次数 0
|
阅读次数 436

mysql.png
我有一张表 dc_activities, 大约 15 万条记录

这张表有字段 id(主键), title, addtime, is_del, dancer_id 共 5 个字段。

其中 addtime,is_del,dancer_id 上有一个聚集索引。

当我执行如下 SQL 时, 数据库是没有相关记录的, 但是却耗时 5 秒钟之长,SQL 如下:

mysql> select id, title from dc_activities where is_del = 0 and dancer_id = 111 order by addtime desc limit 10;
Empty set

这就很纳闷了,为什么查询结果是空的,却耗时这么久,于是我用 explain 查看查询计划:

mysql> explain select id, title from dc_activities where dancer_id = 111 and is_del = 0 order by addtime desc limit 10;
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------+
| id | select_type | table         | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                            |
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------+
|  1 | SIMPLE      | dc_activities | NULL       | index | NULL          | idx_mix | 8       | NULL |   10 |     1.00 | Using where; Backward index scan |
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------+
1 row in set (0.03 sec)

看完之后的我:

6SN3LDPUMS5K_7Q91T.gif

明明用了idx_mix这条索引了,为啥还能耗时 5 秒左右,真的很不服气,又开启了 profiles 查看 SQL 执行时长:

mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select id, title from dc_activities where dancer_id = 111 and is_del = 0 order by addtime desc limit 10;
Empty set

mysql> select id, title from dc_activities where dancer_id = 111 and is_del = 0 order by addtime desc limit 10;
Empty set

mysql> select id, title from dc_activities where dancer_id = 111 and is_del = 0 order by addtime desc limit 10;
Empty set

查看执行时长:

mysql> show profiles;
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                                          |
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------+
|        1 | 0.16071850 | select id, title from dc_activities where dancer_id = 111 and is_del = 0 order by addtime desc limit 10 |
|        2 | 0.15769150 | select id, title from dc_activities where dancer_id = 111 and is_del = 0 order by addtime desc limit 10 |
|        3 | 0.15984575 | select id, title from dc_activities where dancer_id = 111 and is_del = 0 order by addtime desc limit 10 |
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.02 sec)

可能是执行太多次了,用上了查询缓存,稍微快了那么一丢丢,但是还是无法理解为什么记录是空的能执行这么久?

这就触及到我知识的盲区了,试试用 range 查询能不能解决吧~ 条件为 时间在 1970 年之前,查询计划如下:

mysql> explain select id, title from dc_activities where addtime > '1970-01-01 00:00:00' and dancer_id = 111 and is_del = 0 order by addtime desc limit 10;
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+-------+----------+--------------------------------------------+
| id | select_type | table         | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra                                      |
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+-------+----------+--------------------------------------------+
|  1 | SIMPLE      | dc_activities | NULL       | range | idx_mix       | idx_mix | 5       | NULL | 66586 |     1.00 | Using index condition; Backward index scan |
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+-------+----------+--------------------------------------------+
1 row in set (0.03 sec)

看到这个执行计划, 第一眼感觉不太行, 因为 rows 这列表示预计需要扫描 6 万多行呢!直接执行试试:

mysql> select id, title from dc_activities where addtime > '1970-01-01 00:00:00' and dancer_id = 111 and is_del = 0 order by addtime desc limit 10;
Empty set

mysql> select id, title from dc_activities where addtime > '1970-01-01 00:00:00' and dancer_id = 111 and is_del = 0 order by addtime desc limit 10;
Empty set

mysql> select id, title from dc_activities where addtime > '1970-01-01 00:00:00' and dancer_id = 111 and is_del = 0 order by addtime desc limit 10;
Empty set

咦~ 执行的时候感觉结果立马就出来了,好像快了很多,再看看 profiles:

mysql> show profiles;
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                                                                              |
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|        1 | 0.02929775 | select id, title from dc_activities where addtime > '1970-01-01 00:00:00' and dancer_id = 111 and is_del = 0 order by addtime desc limit 10 |
|        2 | 0.02913775 | select id, title from dc_activities where addtime > '1970-01-01 00:00:00' and dancer_id = 111 and is_del = 0 order by addtime desc limit 10 |
|        3 | 0.02888075 | select id, title from dc_activities where addtime > '1970-01-01 00:00:00' and dancer_id = 111 and is_del = 0 order by addtime desc limit 10 |
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.03 sec)

怎么这么快???

MT2FO040Q9O1E3~69S.gif

真的怀疑人生了,看不懂 MySQL 这几波操作到底是啥意思,愈来愈发觉到自己知识的欠缺…

所以还得比现在更专注于学习才行,虽然目前每天大部分时间都在学习(搬砖)中,但是感觉进步地很慢,偶尔还会控制不住制几划水、逛什么 B 乎、微博…

不理解 MySQL 底层执行原理, 真搞不懂这几个 SQL。

唉,不缩了,学习去了,解决方案暂时先用这个,呜呜呜。

BM~DDQTMOXV8FTSEYI.png

发布评论
还没有评论,快来抢沙发吧!