代码之家 › 专栏 › 技术社区 › Dean Xu

为什么MySQL只使用复合索引而不使用单独的索引?

indexing mysql

Dean Xu · 技术社区 · 6 年前

我有下表

CREATE TABLE `test` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL
);

我需要做以下查询

SELECT * FROM `test` ORDER BY a, b LIMIT 1;

如果我加上一个综合指数

ALTER TABLE `t_test` ADD INDEX a_b(`a`, `b`);

它起作用了

> EXPLAIN SELECT * FROM `test` ORDER BY a, b LIMIT 1;
+------+-------------+-------+-------+---------------+------+---------+------+------+-------+
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra |
+------+-------------+-------+-------+---------------+------+---------+------+------+-------+
|    1 | SIMPLE      | test  | index | NULL          | a_b  | 8       | NULL |    1 |       |
+------+-------------+-------+-------+---------------+------+---------+------+------+-------+

但是如果我分别添加两个索引

ALTER TABLE `t_test` ADD INDEX a(`a`), ADD INDEX b(`b`);

它失败了

> EXPLAIN SELECT * FROM `test` ORDER BY a, b LIMIT 1;
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
|    1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using filesort |
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+

连我都补充说 FORCE INDEX ,它不起作用。

据我个人理解,它应该同时使用这两种方法 index a 和 index b 只比综合指数小一点。

即使我错了,它至少应该使用 索引a 首先,然后使用filesort对b进行排序。

这个排序运算符真的不能使用分隔索引吗?如果是,请解释为什么不起作用。如果没有,您有什么解决方案可以让它与单独的索引一起工作吗?提前谢谢。

编辑

例如,我有100行。我可以用 索引a 首先对它们进行分类。然后在每组中使用相同的 a 价值,我可以用 索引b 把它们分类。

为什么这种方法不能在MySQL上工作?

1 回复 | 直到 6 年前

Vatev 6 年前

当索引(复合或非复合)用于“排序”时,MySQL会按顺序读取数据,而不会进行任何排序。对于多个索引,这是不可能的。

在第一个索引上按顺序读取然后进行文件排序是可能的,但速度不太可能更快,所以MySQL不这么做。

如果确实需要,可以使用如下子查询:

    SELECT ...
    FROM (
            SELECT primary_key
            FROM table1
            ORDER BY field1
            LIMIT 15
    ) tmp
    JOIN table1 t ON t.primary_key = tmp.primary_key
    ORDER BY field1, field2

当你有一个 LIMIT 由于某种原因,无法添加综合指数。