请考虑以下架构

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放在了额外的一栏中.

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 |
+----+-------------+----------+------+---------------------+---------------------+---------+-------------+-------+--------------------------+

据我所知,哪里需要额外的磁盘搜寻.为什么不仅仅使用索引？

解决方法:

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

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

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

第二个查询-我的mysql版本未显示“在哪里使用”-我也不会期望-也许是因为我没有记录.

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的其他列信息：

使用索引：这表明MySQL将使用覆盖索引来避免访问表.不要将覆盖索引与索引访问类型混淆.

使用Where：这意味着MySQL服务器将在存储引擎检索行后对行进行后过滤.存储引擎在读取索引时(如果有的话)可以检查涉及索引列的许多WHERE条件,因此并非所有带有WHERE子句的查询都会显示“在哪里使用”.有时,“在哪里使用”的出现提示查询可以从不同的索引中受益.