关于MySQL最左前缀匹配优化

Question

我现在有一个这样的桌子：

> DESC userInfo;
+--------+---------------------+------+-----+---------+----------------+
| Field  | Type                | Null | Key | Default | Extra          |
+--------+---------------------+------+-----+---------+----------------+
| id     | int(11)             | NO   | PRI | NULL    | auto_increment |
| name   | char(32)            | NO   | MUL | NULL    |                |
| age    | tinyint(3) unsigned | NO   |     | NULL    |                |
| gender | tinyint(1)          | NO   |     | 1       |                |
+--------+---------------------+------+-----+---------+----------------+

I使(名称、年龄)成为一个联合的唯一索引：

> SHOW INDEX FROM userInfo;
+----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+--------------------+
| Table    | Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment      |
+----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+--------------------+
| userInfo |          0 | PRIMARY            |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |                    |
| userInfo |          0 | joint_unique_index |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         | 联合唯一索引       |
| userInfo |          0 | joint_unique_index |            2 | age         | A         |           0 |     NULL | NULL   |      | BTREE      |         | 联合唯一索引       |
+----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+--------------------+
3 rows in set (0.00 sec)

现在，当我使用以下查询语句时，它的类型是全部：

> DESC SELECT * FROM userInfo WHERE age = 18;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | userInfo | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+

我可以理解这种行为，因为根据最左边的前缀匹配特性，在查询时，年龄将不被用作索引列。

但是，当我使用以下语句查询时，它的类型是Index：

> DESC SELECT name, age FROM userInfo WHERE age = 18;
+----+-------------+----------+------------+-------+---------------+--------------------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key                | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+--------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | userInfo | NULL       | index | NULL          | joint_unique_index | 132     | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+--------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

我不明白这个结果是怎么产生的。根据示例1，作为查询条件的年龄并不满足最左边的前缀匹配功能，但从结果来看，它的类型实际上是Index！这是MySQL中的优化吗？

当我试图确保使用索引列作为查询条件时，它们的类型始终是ref，如下所示：

> DESC SELECT * FROM userInfo WHERE name = "Jack";
+----+-------------+----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | userInfo | NULL       | ref  | joint_unique_index | joint_unique_index | 128     | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

> DESC SELECT name, age FROM userInfo WHERE name = "Jack";
+----+-------------+----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | userInfo | NULL       | ref  | joint_unique_index | joint_unique_index | 128     | const |    1 |   100.00 | Using index |
+----+-------------+----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

请告诉我为什么当我使用年龄作为查询时，第一个结果是全部，而第二个结果是索引。这是MySQL优化的结果吗？换句话说，在使用SELECT *时，不应用索引列查询，但是当使用SELECT joint_col1、joint_col2 FROM joint_col2时，则使用索引列查询(因为类型是索引)。为什么会出现这种差异呢？

Answer 1

简化一下，索引(name, age)基本上是相同的，就像您有另一个带有这些值副本的表(name, age, id)一样。由于技术原因，(对于InnoDB)包含主键-- MySQL使用它来查找原始表中的整行。

因此，您基本上可以把它看作是有两个表：(id, name, age, gender)和(name, age, id)，它们都有相同的行数。如果提供最左边的列，两者都可以跳到/跳过特定的行。

如果你这样做了

SELECT * FROM userInfo WHERE age = 18;

正如您预期的那样，MySQL必须读取表的每一行，因为找不到年龄为18的行的速度更快--正如您总结的那样，没有以年龄作为最左边列的索引。

如果你这样做了

SELECT name, age FROM userInfo WHERE age = 18;

情况并没有太大变化: MySQL也必须读取每一行，而且仍然不能使用(name, age)上的索引来限制必须读取的行数。

但是MySQL可以使用一个技巧:因为您只需要列name和age，所以它可以从索引中读取所有行--“表”，并且仍然有它需要的所有信息，因为索引是覆盖索引(它覆盖所有所需的列)。

MySQL为什么要这么做？因为它必须读取更少的绝对数据，而不是读取完整的表:索引以更少的字节存储您想要的信息(因为它不包括gender)。读取较少的数据以获取所需的所有信息，比读取更多的数据以获得相同的信息更好/更快。所以MySQL就会这么做。

但是要强调这一点:您的查询仍然必须读取所有行，它基本上仍然是一个完整的表扫描(" all ") --只是在一个列较少的“表”(索引)上，以保存一些字节。虽然您不会注意到一个tinyint列的不同之处，但是如果您的表中有很多或较大的列，那么它实际上是一个相关的加速。

Answer 2

“最左边”规则适用于WHERE子句和INDEX。

INDEX(name, age)对于WHERE name = '...'或WHERE name = '...' AND ((anything else))非常有用，因为name是索引中最左边的。

您拥有的是WHERE age = ... (以及其他任何东西)，因此您需要INDEX(age) (或INDEX(age, ...))。

特别是，SELECT name, age FROM userInfo WHERE age = 18;

INDEX(age) -- good
INDEX(age, name) -- better because it is "covering".

WHERE中列的顺序无关紧要；INDEX中的顺序很重要。