联合索引和最左匹配

联合索引和最左前缀原则

为了讲解下面的最左前缀原则，先创建一张表，如下：

CREATE TABLE person_info(
    id INT NOT NULL auto_increment,
    name VARCHAR(100) NOT NULL,
    birthday DATE NOT NULL,
    phone_number CHAR(11) NOT NULL,
    country varchar(100) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_name_birthday_phone_number (name, birthday, phone_number)
);

建立表的时候为 name, birthday, phone_number 创建了一个联合索引，名称为 idx_name_birthday_phone_number。
考虑到这种场景，查询的时候有3个查询条件，姓名，生日和电话号码。每次查询都会带上姓名这个字段，而生日和电话号码可以不加入查询语句。
如果查询的 where 语句中包含了 name 字段，可能就会使用到这个索引。
首先通过 name 索引找到 name 列对应的匹配行，然后再过滤 birthday 和 phone_number。
如果 name 列的值相同，birthday 是内部结构中是升序排列，如果 birthday 列的值也相同，则查询同样是升序排列的 phone_number。

最左前缀原则和模糊查询

先看一条 SQL 语句，如下：

SELECT * FROM person_info WHERE name LIKE '%As%';

假设 name 列上已有索引，但是这条SQL 语句不会使用 name 索引。

在字符串上建立索引，会把列上的每个字符从左到右进行比较，把行记录升序排列。

因为字符串中间有 ‘As’ 的字符串并没有排序，所以只能全表扫描。
如果需要查询名字以 ‘As’ 开头的记录，可以这样写：

SELECT * FROM person_info WHERE name LIKE 'As%';

最左前缀原则和匹配范围值

如果多个列进行范围查询，只有对索引最左边的那个列进行范围查找时才能用到B+树索引。
比如下面这种情况：

SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow' AND birthday > '1980-01-01';

在上面这条SQL语句中，name 列可以使用索引，但 birthday 列不能使用索引。
因为 name 列的范围查找返回了多个 name 值不同的记录，只有在 name 值相同的情况下才能使用 birthday 列的值进行排序。
通过 name 的范围查找返回的记录可能并不是按照 birthday 列进行排序。

联合索引在排序的注意事项

order by 的子句后面的列顺序应与索引列的顺序一致，如果打乱了顺序，可能不会用到这个联合索引。
比如 order by phone_number, name 就不能在排序中用名称为 idx_name_birthday_phone_number 的联合索引。