首页

假设有以下表结构：

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。

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';

这就是索引的最左前缀原则，如果表具有多列索引，例如 (col1, col2, col3)，那么 (col1) 、 (col1, col2) 和 (col1, col2, col3) 的查询都可以用到该索引。相反的，(col2, col3) 或 (col3) 则不会用到，例如：

SELECT * FROM test WHERE first_name='John';

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

第一条很好理解，first_name 显然不在最左边，但是第二条就比较奇怪了，last_name 明明是符合最左前缀原则的，但是官方文档写的是不适用于 name 索引。

这里直接使用 mysql 官方提供的测试数据库 employees，因为官方的示例中数据库字段只有 first_name 和 last_name，但是 employees 数据库里远不止这些字段，所以我这里模拟下实际情况，只查这两个字段：

EXPLAIN SELECT
  first_name,last_name
FROM
  employees 
WHERE
  last_name = 'Cairo' or first_name = 'Wanqing'

可以看到，这里的 type 是 index，说明 mysql 扫描了整个索引树，并且走了索引，这么看来 mysql 文档好像是错的？也不一定，看第二个示例：

SELECT
  *
FROM
  employees 
WHERE
  last_name = 'Cairo' or first_name = 'Wanqing'

在这种情况，explain 的 type 直接是 ALL，并且也没有用索引。

出现这种情况的原因如下：

  1. 在第一种情况是，只查询了 last_name 和 first_name，而这两列都包含在索引当中，所以优化过后直接扫描索引更快
  2. 在第二种情况中，first_name 显然是不能走索引的，且要查询的是所有字段，其次可能又因为 first_name 的选择性不高，mysql 优化器就优化为全表扫描了。

# 参考

  1. MySQL 5.7 Reference Manual - 8.3.5 Multiple-Column Indexes
  2. 掘金 - 最左前缀原则