mysql - 在 MySQL 中按日期时间范围选择非常慢

Question

我的表 posts 有超过 650 万条记录。每个帖子都使用固定长度的 name 表示。我使用 MySQL Community 5.7，具有大约 10K-20K IOPS 和 1GB 内存的 SSD 磁盘，key-buffer-size 设置为 512M(顺便说一句，大多数时候我使用默认的 MySQL 配置驱动)。我的资源有限，因此我选择了 MyISAM 作为我的存储引擎。我的基准测试表明，在我的情况下，MyISAM 更快。我也不太关心数据，因为它可以更新。

所以，这是我的方案信息:

+------------+--------+------------+
| TABLE_NAME | ENGINE | row_format |
+------------+--------+------------+
| posts      | MyISAM | Fixed      |
+------------+--------+------------+

+---------------------+---------------------+------+-----+---------+----------------+
| Field               | Type                | Null | Key | Default | Extra          |
+---------------------+---------------------+------+-----+---------+----------------+
| id                  | int(11) unsigned    | NO   | PRI | NULL    | auto_increment |
| name                | char(30)            | NO   | UNI | NULL    |                |
| worker_id           | tinyint(4) unsigned | NO   | MUL | NULL    |                |
| processing_priority | tinyint(4) unsigned | NO   | MUL | 0       |                |
| last_processed_at   | datetime            | YES  | MUL | NULL    |                |
| scraped_at          | datetime            | NO   | MUL | NULL    |                |
+---------------------+---------------------+------+-----+---------+----------------+

+-------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name            | Seq_in_index | Column_name         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| posts |          0 | PRIMARY             |            1 | id                  | A         |     6579588 |     NULL | NULL   |      | BTREE      |         |               |
| posts |          0 | name                |            1 | name                | A         |     6579588 |     NULL | NULL   |      | BTREE      |         |               |
| posts |          1 | last_processed_at   |            1 | last_processed_at   | A         |     6579588 |     NULL | NULL   | YES  | BTREE      |         |               |
| posts |          1 | processing_priority |            1 | processing_priority | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| posts |          1 | worker_id           |            1 | worker_id           | A         |          50 |     NULL | NULL   |      | BTREE      |         |               |
| posts |          1 | scraped_at          |            1 | scraped_at          | A         |      234985 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

我运行的查询:

SELECT COUNT(*) FROM `posts` WHERE `posts`.`worker_id` = 1 AND (last_processed_at >= '2017-11-04 22:20:27.203761')

MySQL 需要 3676.4ms 来执行这个查询。

查询解释:

+----+-------------+-------+------------+------+-----------------------------+-----------+---------+-------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys               | key       | key_len | ref   | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+-----------------------------+-----------+---------+-------+--------+----------+-------------+
|  1 | SIMPLE      | posts | NULL       | ref  | last_processed_at,worker_id | worker_id | 1       | const | 232621 |    37.45 | Using where |
+----+-------------+-------+------------+------+-----------------------------+-----------+---------+-------+--------+----------+-------------+

您对如何优化它有任何想法吗？

Answer 1

您可以使用 worker_id 和 last_processed_at 创建组合键，替换 worker_id 键。