mysql - 优化 MySQL 索引，查询不到一秒

Question

一个非常简单的 min-max no-join no-nesting SQL 的查询时间超过 2 秒。

表结构:::

> DESCRIBE tbl;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| ...         | ...          | ...  | ... | ...     | ...            |
| created_at  | datetime     | YES  | MUL | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

该表包含 10,000,000 多行

---

表中的索引:::

> SHOW INDEX IN tbl;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tbl   |          0 | PRIMARY    |            1 | id          | A         |    10000545 |     NULL | NULL   |      | BTREE      |         |               |
| tbl   |          1 | created_at |            1 | created_at  | A         |          18 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

---

SQL UNDER CONCERN::: 查找最后 10k 个条目的最小-最大 DateTime

SELECT 
    min(created_at), 
    max(created_at) 
FROM tbl 
ORDER BY id DESC 
LIMIT 10000

---

问题::: 第一个查询总是需要 2 秒以上才能完成。第一次选择后，所有后续查询调用只需不到 0.001 秒即可完成，除非将新行插入表中。

2.06 秒内首次调用:

> SELECT min(created_at), max(created_at) FROM tbl USE INDEX (created_at) ORDER BY id DESC LIMIT 10000;
+---------------------+---------------------+
| min(created_at)     | max(created_at)     |
+---------------------+---------------------+
| 2010-01-01 00:00:00 | 2015-12-28 00:00:00 |
+---------------------+---------------------+
1 row in set (2.06 sec)

0.00 秒后的后续调用:

> SELECT min(created_at), max(created_at) FROM tbl USE INDEX (created_at) ORDER BY id DESC LIMIT 10000;
+---------------------+---------------------+
| min(created_at)     | max(created_at)     |
+---------------------+---------------------+
| 2010-01-01 00:00:00 | 2015-12-28 00:00:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)

一旦将新行添加到表中，它再次需要 2 秒以上才能完成，然后所有后续查询调用只需不到 0.001 秒即可完成。

我知道每次插入新行时索引都会重新排列。所以没关系。但是，我的目标是将首次查询时间缩短到几毫秒以内，因为在频繁更新的系统中每次查询花费 2 秒以上会严重降低性能。

---

查询计划的解释::: 解释语句表明查询几乎遍历了表的所有行。所以我猜我有通过索引改进的空间。但是我应该索引什么？

> EXPLAIN SELECT min(created_at), max(created_at) FROM tbl ORDER BY id DESC LIMIT 10000;
+----+-------------+-------+-------+---------------+------------+---------+------+----------+-------------+
| id | select_type | table | type  | possible_keys | key        | key_len | ref  | rows     | Extra       |
+----+-------------+-------+-------+---------------+------------+---------+------+----------+-------------+
|  1 | SIMPLE      | tbl   | index | NULL          | created_at | 9       | NULL | 10000545 | Using index |
+----+-------------+-------+-------+---------------+------------+---------+------+----------+-------------+
1 row in set (0.00 sec)

Answer 1

您的原始查询不会返回“最近 10000 个条目的最小/最大日期”； LIMIT 在聚合函数处理后应用，因此您要求“给我最大/最小日期，然后将其限制为前 10k”...并且只有一行。

您必须为此使用子查询:

SELECT min(created_at), max(created_at)
FROM (SELECT created_at
      FROM my_table
      ORDER BY id
      LIMIT 10000) subtable;

最好的选择是在 (id, created_at) 上建立索引，因为子查询只需要遍历索引，然后最小/最大查询只需要对超过 10k 的元素进行排序。