mysql - 如何在两列上使用 WHERE 子句加速 MySQL 查询？

Question

我正在尝试在两列上使用 WHERE 子句来加快对大表的查询，MySQL 仅使用 ALERT_ID 列。

有没有办法使用两个索引重写这个查询？

SHOW_INDEX 和 EXPLAIN 输出如下。

show index from alert_hit;

+-----------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name          | Seq_in_index | Column_name       | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| alert_hit |          0 | PRIMARY           |            1 | id                | A         |    15181402 |     NULL | NULL   |      | BTREE      |         |               |
| alert_hit |          1 | alert_id          |            1 | alert_id          | A         |          20 |     NULL | NULL   | YES  | BTREE      |         |               |
| alert_hit |          1 | timestamp         |            1 | timestamp         | A         |      446511 |     NULL | NULL   | YES  | BTREE      |         |               |
| alert_hit |          1 | data_source_id    |            1 | data_source_id    | A         |          20 |     NULL | NULL   | YES  | BTREE      |         |               |
| alert_hit |          1 | filter_syndicated |            1 | filter_syndicated | A         |          20 |     NULL | NULL   | YES  | BTREE      |         |               |
| alert_hit |          1 | unique_id         |            1 | unique_id         | A         |     5060467 |     NULL | NULL   | YES  | BTREE      |         |               |
| alert_hit |          1 | date_created      |            1 | date_created      | A         |      281137 |     NULL | NULL   |      | BTREE      |         |               |
| alert_hit |          1 | language          |            1 | language          | A         |          20 |     NULL | NULL   | YES  | BTREE      |         |               |
| alert_hit |          1 | region            |            1 | region            | A         |       42406 |     NULL | NULL   | YES  | BTREE      |         |               |
| alert_hit |          1 | market_rank       |            1 | market_rank       | A         |          20 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

explain select count(id) as history FROM alert_hit force index(alert_id, timestamp) where alert_id in (9045,9046,9047,9048,9049,9050,9051,9052,9330,9332)  AND timestamp between DATE_SUB( NOW(), INTERVAL 1*2 day) and DATE_SUB( NOW(), INTERVAL 1 day);
+----+-------------+-----------+-------+--------------------+----------+---------+------+-------+-------------+
| id | select_type | table     | type  | possible_keys      | key      | key_len | ref  | rows  | Extra       |
+----+-------------+-----------+-------+--------------------+----------+---------+------+-------+-------------+
|  1 | SIMPLE      | alert_hit | range | alert_id,timestamp | alert_id | 5       | NULL | 99578 | Using where |
+----+-------------+-----------+-------+--------------------+----------+---------+------+-------+-------------+

Answer 1

你需要在两个字段上都有一个索引

ALTER TABLE alert_hit ADD INDEX `IDX-alert_id-timestamp` (`alert_id`, `timestamp`);

此外，MySQL 将使用多列索引直到 WHERE 子句中有范围条件的第一个字段，因此在这种情况下顺序很重要，timestamp 应该在索引中排在最后。

正如@spencer7593 建议的那样，选择 COUNT(1) 而不是 count(id) 也可能更好。