mysql - 使用索引满足 MySQL 条件

Question

请考虑以下架构

CREATE table articles (
    id Int UNSIGNED NOT NULL AUTO_INCREMENT,
    cat_id Int UNSIGNED NOT NULL,
    status Int UNSIGNED  NOT NULL,
    date_added Datetime,
    Primary Key (id)) ENGINE = InnoDB;

CREATE INDEX cat_list_INX ON articles (cat_id, status, date_added);
CREATE INDEX categories_list_INX ON articles (cat_id, status);

我写了以下两个查询，上面两个指标完全可以满足，但是 MySQL 将 where 放在额外的列中。

mysql> EXPLAIN SELECT cat_id FROM articles USE INDEX (cat_list_INX) WHERE cat_id=158 AND status=2 ORDER BY date_added DESC LIMIT 500, 5;
+----+-------------+----------+------+---------------+--------------+---------+-------------+-------+--------------------------+
| id | select_type | table  ref         |   | type | possible_keys | key          | key_len | rows  | Extra                    |
+----+-------------+----------+------+---------------+--------------+---------+-------------+-------+--------------------------+
|  1 | SIMPLE      | articles | ref  | cat_list_INX  | cat_list_INX | 5       | const,const | 50698 | Using where; Using index |
+----+-------------+----------+------+---------------+--------------+---------+-------------+-------+--------------------------+


mysql> EXPLAIN SELECT cat_id FROM articles USE INDEX (categories_list_INX) WHERE cat_id=158 AND status=2;
+----+-------------+----------+------+---------------------+---------------------+---------+-------------+-------+--------------------------+
| id | select_type | tab key                 |le    | type | possible_keys       | key_len | ref         | rows  | Extra                    |
+----+-------------+----------+------+---------------------+---------------------+---------+-------------+-------+--------------------------+
|  1 | SIMPLE      | articles | ref  | categories_list_INX | categories_list_INX | 5       | const,const | 52710 | Using where; Using index |
+----+-------------+----------+------+---------------------+---------------------+---------+-------------+-------+--------------------------+

据我所知哪里需要额外的磁盘搜索。为什么它不只是使用索引？

Answer 1

第一个查询是在存储引擎之外的 mysql 级别过滤记录，因为您的“ORDER BY”子句使用 date_added 字段。

这可以通过像这样在索引中首先移动 date_added 字段来缓解

CREATE INDEX cat_list_INX ON articles (date_added, cat_id, status);

第二个查询 - 我的 mysql 版本没有显示“Using where” - 我也不希望 - 可能是因为我没有记录。

mysql> EXPLAIN SELECT cat_id FROM articles USE INDEX (categories_list_INX) WHERE cat_id=158 AND status=2;
+----+-------------+----------+------+---------------------+---------------------+---------+-------------+------+-------------+
| id | select_type | table    | type | possible_keys       | key                 | key_len | ref         | rows | Extra       |
+----+-------------+----------+------+---------------------+---------------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | articles | ref  | categories_list_INX | categories_list_INX | 8       | const,const |    1 | Using index |
+----+-------------+----------+------+---------------------+---------------------+---------+-------------+------+-------------+
1 row in set (0.00 sec)

来自高性能 MySQL 的额外列信息:

Using Index: 这表明 MySQL 将使用覆盖索引来避免访问表。不要混淆覆盖索引和索引访问类型。

Using Where: 这意味着 MySQL 服务器将在存储引擎检索到行后进行过滤。许多涉及索引中列的 WHERE 条件可以由存储引擎在读取索引时(以及是否)检查，因此并非所有带有 WHERE 子句的查询都会显示“Using where”。有时“Using where”的存在暗示查询可以从不同的索引中获益。