MySQL联合索引遵循最左前缀匹配原则

MySQL联合索引采用最左前缀匹配原则，即查询条件从左至右匹配索引中的字段。

深入理解MySQL联合索引的最左前缀匹配原则

在MySQL数据库中，联合索引是一种非常重要的索引类型，可以有效地提高查询性能，很多开发者在创建和使用联合索引时，往往忽视了最左前缀匹配原则，导致索引效果不佳，本文将详细讲解MySQL联合索引的最左前缀匹配原则，并通过实例进行分析，帮助大家更好地理解和应用这一原则。

联合索引简介

联合索引，又称复合索引，是指在一个索引定义中包含多个列的索引，与单一索引相比，联合索引可以覆盖更多的查询场景，提高查询性能。

创建联合索引的语法如下：

CREATE INDEX index_name ON table_name (column1, column2, ...);

index_name表示索引名称，table_name表示表名称，column1, column2, ...表示组成联合索引的列。

最左前缀匹配原则

最左前缀匹配原则是指在使用联合索引进行查询时，遵循从左到右的顺序使用索引列，具体来说，以下几种情况会触发最左前缀匹配：

1、查询条件包含联合索引的所有列。

对于以下联合索引：

CREATE INDEX idx_user_age_name ON user (age, name);

以下查询会触发最左前缀匹配：

SELECT * FROM user WHERE age = 30 AND name = 'zhangsan';

2、查询条件包含联合索引的前缀列。

SELECT * FROM user WHERE age = 30;

3、查询条件包含联合索引的前缀列，并且后续列有范围查询。

SELECT * FROM user WHERE age = 30 AND name LIKE 'zhangsan%';

以下情况不会触发最左前缀匹配：

1、查询条件不包含联合索引的最左列。

SELECT * FROM user WHERE name = 'zhangsan';

2、查询条件包含联合索引的列，但顺序与索引定义不一致。

SELECT * FROM user WHERE name = 'zhangsan' AND age = 30;

实例分析

为了更好地理解最左前缀匹配原则，我们通过以下实例进行分析。

1、创建测试表

CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    age INT,
    name VARCHAR(50),
    address VARCHAR(100)
);

2、创建联合索引

CREATE INDEX idx_user_age_name ON user (age, name);

3、插入测试数据

INSERT INTO user (age, name, address) VALUES (20, 'zhangsan', '北京');
INSERT INTO user (age, name, address) VALUES (30, 'lisi', '上海');
INSERT INTO user (age, name, address) VALUES (30, 'zhaowu', '广州');
INSERT INTO user (age, name, address) VALUES (40, 'zhangsan', '深圳');

4、查询分析

（1）查询条件包含联合索引的所有列

EXPLAIN SELECT * FROM user WHERE age = 30 AND name = 'zhaowu';

查询计划如下：

id  select_type  table  type  possible_keys  key  key_len  ref  rows  filtered  Extra
1   SIMPLE       user   ref   idx_user_age_name  idx_user_age_name  76  const,const  1   100.00  Using index

从查询计划可以看出，MySQL使用了联合索引idx_user_age_name，并且使用了最左前缀匹配。

（2）查询条件包含联合索引的前缀列

EXPLAIN SELECT * FROM user WHERE age = 30;

查询计划如下：

id  select_type  table  type  possible_keys  key  key_len  ref  rows  filtered  Extra
1   SIMPLE       user   ref   idx_user_age_name  idx_user_age_name  5   const  3   33.33  Using index condition

从查询计划可以看出，MySQL使用了联合索引idx_user_age_name的前缀列，即age列。

（3）查询条件包含联合索引的前缀列，并且后续列有范围查询

EXPLAIN SELECT * FROM user WHERE age = 30 AND name LIKE 'zhangsan%';

查询计划如下：

id  select_type  table  type  possible_keys  key  key_len  ref  rows  filtered  Extra
1   SIMPLE       user   range  idx_user_age_name  idx_user_age_name  76  NULL  1   25.00  Using index condition

从查询计划可以看出，MySQL使用了联合索引idx_user_age_name的前缀列，并且对后续列name进行了范围查询。

（4）查询条件不包含联合索引的最左列

EXPLAIN SELECT * FROM user WHERE name = 'zhangsan';

查询计划如下：

id  select_type  table  type  possible_keys  key  key_len  ref  rows  filtered  Extra
1   SIMPLE       user   ALL   NULL  NULL  NULL  NULL  4   25.00  Using where

从查询计划可以看出，MySQL没有使用联合索引idx_user_age_name，而是进行了全表扫描。

本文详细介绍了MySQL联合索引的最左前缀匹配原则，并通过实例进行了分析，在实际开发中，我们需要遵循以下原则：

1、创建联合索引时，应按照查询条件中出现频率和过滤效果的优先级来确定索引列的顺序。

2、使用联合索引进行查询时，应确保查询条件包含联合索引的最左列，以提高查询性能。

3、避免创建不必要的联合索引，以免增加数据库的维护成本。

掌握最左前缀匹配原则，可以帮助我们更好地优化MySQL查询性能，提高数据库的整体效率。