我有一张表 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)

看完之后的我:

明明用了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)

怎么这么快？？？

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

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

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

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