mysql - 改进mysql范围查询

Question

是否有更好的方法来执行以下操作:

SELECT id FROM main_catalog WHERE release_year <= 2010 AND release_year >= 2000

这样做，在一个有大约 500k 条记录的表上大约需要 4 秒，这看起来很糟糕。这是EXPLAIN 语句:

id  select_type  table         type    possible_keys   key             key_len     ref   rows    Extra
1   SIMPLE       main_catalog  range   release_year    release_year    3           NULL  365684  Using index condition; Using where; Using MRR

Answer 1

我在我的 2011 Macbook Pro 上使用 MySQL 5.6.23 复制了您的设置，并且没有出现性能问题。

CREATE TABLE main_catalog (
    id int primary key auto_increment,
    release_year int,
    index(release_year)
);

...inserted a whole bunch of rows...

mysql> select count(*) from main_catalog;
+----------+
| count(*) |
+----------+
|   730000 |
+----------+
1 row in set (0.13 sec)

mysql> SELECT count(id) FROM main_catalog WHERE release_year <= 2010 AND release_year >= 2000;
+-----------+
| count(id) |
+-----------+
|    331100 |
+-----------+
1 row in set (0.11 sec)

mysql> SELECT id FROM main_catalog WHERE release_year <= 2010 AND release_year >= 2000;
...a whole lot of output...
331100 rows in set (0.15 sec)

mysql> explain SELECT id FROM main_catalog WHERE release_year <= 2010 AND release_year >= 2000;
+----+-------------+--------------+-------+---------------+--------------+---------+------+--------+--------------------------+
| id | select_type | table        | type  | possible_keys | key          | key_len | ref  | rows   | Extra                    |
+----+-------------+--------------+-------+---------------+--------------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | main_catalog | range | release_year  | release_year | 5       | NULL | 364596 | Using where; Using index |
+----+-------------+--------------+-------+---------------+--------------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)

一种可能是您的网络连接速度很慢并且在发送数据时遇到问题。检查 SELECT COUNT(id) 是否同样慢。

您也可以尝试WHERE release_year BETWEEN 2000 AND 2010，但我认为这不会产生影响。