如何在两列上使用 WHERE 子句加速 MySQL 查询? [英] How can I speed up a MySQL query with WHERE clauses on two columns?
问题描述
我试图在两列上使用 WHERE 子句加速对大表的查询，尽我所能，MySQL 仅使用 ALERT_ID 列.
I am trying to speed up a query on a large table with WHERE clauses on two columns, as far as I can, MySQL is only using the ALERT_ID column.
有没有办法使用两个索引重写这个查询?
Is there a way to rewrite this query using both indices?
SHOW_INDEX 和 EXPLAIN 输出如下.
SHOW_INDEX and EXPLAIN output is below.
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 |
+----+-------------+-----------+-------+--------------------+----------+---------+------+-------+-------------+
推荐答案
两个字段都需要有一个索引
You need to have one index on both fields
ALTER TABLE alert_hit ADD INDEX `IDX-alert_id-timestamp` (`alert_id`, `timestamp`);
此外，MySQL 将使用多列索引，直到在 WHERE 子句中有范围条件的第一个字段，因此在这种情况下，顺序很重要，timestamp 应该在索引中的最后.
Also MySQL will use the multi column index up to the first field for which there is a range condition in the WHERE clause, so in this case order matters and timestamp should be last in the index.
正如@spencer7593 所建议的，选择 COUNT(1) 而不是 count(id) 也可能更好.
As suggested by @spencer7593 selecting COUNT(1) instead of count(id) might also be better.
这篇关于如何在两列上使用 WHERE 子句加速 MySQL 查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
