最左前缀原则（依据官方文档）

站长

2023年09月01日 04:14 · 阅读数 28

前言

一直想了解什么是最左前缀原则，然后在网上找了很多文档，各个文档对于 最左前缀（最左匹配）原则 的解释都是模模糊糊，看完之后让人一脸懵逼，唯一的出来的结论就是最左前缀原则可能和联合索引有关。

既然如此，那么只能够翻官方文档了。

官方文档学习

我们通过搜索 leftmost prefix 关键词找到 8.3.6 Multiple-Column Indexes 文档，其中有一段是这样描述的：

MySQL can create composite indexes (that is, indexes on multiple columns). An index may consist of up to 16 columns. For certain data types, you can index a prefix of the column (see Section 8.3.5, “Column Indexes”).

我们来翻译一下这段内容。

MYSQL 可以创建联合索引（即有多个列的索引）。一个索引最多可以包含 16 列。对于某些数据类型，你可以索引列的前缀。

那么我们如何理解「对于某些数据类型，你可以索引列的前缀。」这句话呢？

我们再看一下 8.3.5 Column Indexes 中关于索引前缀的介绍：

With col_name(N) syntax in an index specification for a string column, you can create an index that uses only the first N characters of the column. Indexing only a prefix of column values in this way can make the index file much smaller. When you index a BLOB or TEXT column, you must specify a prefix length for the index. For example:

CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

即在对字符串列创建索引时，我们可以使用 col_name(N) 语法，创建仅使用该列的前 N 个字符的索引。以这种方式创建的索引文件会小得多。对于 BLOB 或 TEXT 列建立索引时，必须为索引指定前缀长度。

这是关于前缀索引的介绍，我们再回过头来看看最左前缀索引。

MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on. If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table.

MYSQL 的联合索引可以用于包含索引中所有列的查询语句的查询，或者是仅包含第一列，前两列，前三列，等等。如果你在索引定义中以正确的顺序指定列，那么联合索引就可以加快对同一张表的多种不同类型的查询。

示例

我们再来看一下官方文档中给的关于最左前缀的例子。

首先我们有如下一个表：

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);

name 是一个包含了 last_name 和 first_name 列的联合索引。该索引可以用于 last_name 和 first_name 指定的一个范围的查询，也可以只用于只指定了 last_name 列的查询，因为这个列是索引的一个 最左前缀，因此索引 name 可以用于下列的查询语句：

SELECT * FROM test WHERE last_name='Jones';

SELECT * FROM test
  WHERE last_name='Jones' AND first_name='John';

SELECT * FROM test
  WHERE last_name='Jones'
  AND (first_name='John' OR first_name='Jon');

SELECT * FROM test
  WHERE last_name='Jones'
  AND first_name >='M' AND first_name < 'N';

然而，索引 name 不能用于下面的查询：

SELECT * FROM test WHERE first_name='John';

SELECT * FROM test
  WHERE last_name='Jones' OR first_name='John';

如果表拥有一个联合索引，任何一个索引的最左前缀都会被优化器用于查找列。比如，如果你创建了一个包含三列的联合索引 (col1, col2, col3)，你的索引生效于 (col1), (col1, col2), and (col1, col2, col3)。

知识拓展

为什么要使用联合索引

减少开销。建一个联合索引 (col1,col2,col3)，实际相当于建了 (col1)，(col1,col2)，(col1,col2,col3) 三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！
覆盖索引。对联合索引 (col1,col2,col3)，如果有如下的 SQL：select col1,col2,col3 from test where col1=1 and col2=2;。那么 MySQL 可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机 IO 操作。减少 IO 操作，特别的随机 IO 其实是 DBA 主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。
效率高。索引列越多，通过索引筛选出的数据越少。有 1000W 条数据的表，有如下 SQL：select from table where col1=1 and col2=2 and col3=3，假设假设每个条件可以筛选出 10% 的数据，如果只有单值索引，那么通过该索引能筛选出 1000W10%=100w 条数据，然后再回表从 100w 条数据中找到符合 col2=2 and col3=3 的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出 1000w * 10% * 10% * 10% =1w，效率提升可想而知！

走索引问题

在上面官方文档给的例子中，SELECT * FROM test WHERE first_name='John'; 这条 SQL 是不能用 name 索引的，其实这个说法是不太准确的，我们具体来看一下。

我们来看一下下面两条 SQL 语句的执行过程。

mysql> explain select * from test where last_name='Jones';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ref  | name          | name | 90      | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

mysql> explain select * from test where first_name='Jones';
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | test  | NULL       | index | name          | name | 180     | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

从上面我们看到，这两条 SQL 都走了 name 索引，那是说明不只是符合最左前缀原则的索引可以用到该联合索引？

其实不是的，我们可以仔细观察一下上述的两条 SQL 的执行结果。我们会发现他们的 type 是不一样的，一个是 ref，一个是 index。

8.8.2 EXPLAIN Output Format 中关于 ref 的介绍是这样的：

All rows with matching index values are read from this table for each combination of rows from the previous tables. ref is used if the join uses only a leftmost prefix of the key or if the key is not a PRIMARY KEY or UNIQUE index (in other words, if the join cannot select a single row based on the key value). If the key that is used matches only a few rows, this is a good join type.

即：只会读取匹配的行，当查询的列是一个索引的最左前缀或者是一个普通索引（不是主键索引或唯一索引）会使用 ref。即仅仅会查询几行。

关于 ref 的介绍是这样的：

The index join type is the same as ALL, except that the index tree is scanned. This occurs two ways:

If the index is a covering index for the queries and can be used to satisfy all data required from the table, only the index tree is scanned. In this case, the Extra column says Using index. An index-only scan usually is faster than ALL because the size of the index usually is smaller than the table data.

A full table scan is performed using reads from the index to look up data rows in index order. Uses index does not appear in the Extra column.

MySQL can use this join type when the query uses only columns that are part of a single index.

index 连接类型与 all 相同，除了是扫描索引树外。主要有如下两种方式：

如果查询的索引时覆盖索引，并且满足要从该表中查询的数据需求，则仅扫描索引树。在这种情况下，Extra 列为 Using index。索引扫描仅仅会比 ALL 快，因为索引树通常小于整个表。
通过对索引的读取进行全表扫描，以按照索引顺序查找数据行，Using index 不会出现在 Extra 列。

当查询的列属于一个索引的一部分时，MYSQL 会使用该类型。

从上面的对比我们可以看出，ref 只会扫描几行，而 index 会扫描整个索引，虽然都是用了同一个索引，但是执行效率是不同的。

我们对比一下，当只有 id、first_name、last_name 字段的时候，而我们有联合索引 name，我们执行 select * from test where first_name='刘'; 会从索引获取到我们的所有的数据，所以不需要扫描全表，只需要扫描索引树即可。因此 type 为 index。

当我们再插入一个字段，比如 description，这个时候索引树中没有 description 字段，我们扫描索引树是获取不到完整结果的，此时的 type 应该为 all。

我们来验证一下：

mysql> alter table test add column `description` char(30) NOT NULL;


mysql> explain select * from test where first_name='刘';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

总结

在上面我们研究了官方文档中的关于最左前缀相关文档介绍，我们可以得出如下结论：

最左前缀原则：即当你创建了一个联合索引，该索引的任何最左前缀都可以用于查询。比如当你有一个联合索引 (col1, col2, col3)，该索引的所有前缀为 (col1)、(col1, col2)、(col1, col2, col3)，包含这些列的所有查询都会使用该索引进行查询。
联合索引最多包含 16 列。
BLOB 和 TEXT 创建索引必须执行前多少位