记一次奇怪的慢查询

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

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