MySQL复合索引原理解析

什么是MySQL联合索引

联合索引又叫复合索引，是MySQL的InnoDB引擎中的一个索引方式，如果一个系统频繁地使用相同的几个字段查询结果，就可以考虑建立这几个字段的联合索引来提高查询效率。

如何建立索引

举个例子：

1. create table `table_name`(

2. `id` bigint(20) NOT NULL PRIMARY KEY,

3. `a` int(11),

4. `b` int(11),

5. `c` varchar(22),

6. KEY `key_a_b_c` (`a`,`b`,`c`)

7. )ENGINE=InnoDB DEFAULT CHARSET=utf8;

如上面的代码其中

1. KEY `key_a_b_c` (`a`,`b`,`c`)

语句就是建立了a,b,c字段联合索引的语句。

最左前缀原则

在使用联合索引时要注意有个最左前缀原则，最左前缀原则就是要考虑查询的字段的顺序，只有遵守这个原则才能最大地提高查询的效率，下面我们举个例子说明最左前缀原则。

建立 (a,b,c)的联合索引

1. #完全按创建的顺序，能走到a,b,c3个字段的索引，评级：优化最高

2. SELECT * FROM tz_prod.table_name where a = 1 and b=2 and c = '3';

1. #换了b和c的顺序，MySQL会进行优化，效率和上面的一样，评级：优化最高

2. SELECT * FROM tz_prod.table_name where a = 1 and c = '3' and b=2;

1. #能走到a和b的索引，评级：优化最高

2. SELECT * FROM tz_prod.table_name where a = 1 and b=2 ;

1. #能走到a和b的索引，b的范围查询不影响优化，评级：优化最高

2. SELECT * FROM tz_prod.table_name where a = 1 and b<2 ;

1. #能走到a的索引，评级：优化最高

2. SELECT * FROM tz_prod.table_name where a = 1;

1. #能走到a的索引，评级：优化最高

2. SELECT * FROM tz_prod.table_name order by a ;

1. #只能走到a的索引走不到c的索引，如果c的离散度高则查询效率很低，评级：优化差

2. SELECT * FROM tz_prod.table_name where a = 1 and c = '3';

1. #能走到a和b的索引走不到c的索引，b的范围查询使后面字段无法走索引，评级：优化差

2. SELECT * FROM tz_prod.table_name where a = 1 and b<2 and c = '3';

1. #能走到a的索引，评级：优化最高

2. SELECT * FROM tz_prod.table_name where a > 1 order by a;

1. #能走到a的索引，走不到b索引 评级：优化差

2. SELECT * FROM tz_prod.table_name where a > 1 order by b;

1. #同上，评级：优化差

2. SELECT * FROM tz_prod.table_name where a > 1 order by c;

1. #走不到b和c的索引，最左前缀原则必须以建立索引的第一个字段作为第一个条件，评级：最差

2. SELECT * FROM tz_prod.table_name where b=2 and c = '3';

联合索引提高查询效率的原理

MySQL会为InnoDB的每个表建立聚簇索引，如果表有索引会建立二级索引。聚簇索引以主键建立索引，如果没有主键以表中的唯一键建立，唯一键也没会以隐式的创建一个自增的列来建立。聚簇索引和二级索引都是一个b+树，b+树的特点是数据按一定顺序存在叶子节点且每页数据相连。一般情况下使用索引查询时，先查询二级索引的b+树，查到数据并拿数据中保存的主键回查聚簇索引查到所有数据。下面我们举个例子来重现这个过程。

以下面表举例,假设表中已经存了部分数据：

1. create table `user_info`(

2. `id` bigint(20) NOT NULL PRIMARY KEY,

3. `name` varchar(11),

4. `age` int(11),

5. `phone` varchar(20),

6. KEY `key_name_age` (`name`,`age`)

7. )ENGINE=InnoDB DEFAULT CHARSET=utf8;

InnoDB建立的聚簇索引和二级索引如下图

聚簇索引

二级索引

假如我们想要查找名字为zhaoliu，年龄为30的人的信息。即name='zhaoliu',age=30

• （1）先查二级索引，先用二分法查找发现在wangwu名字的右边
• （2）读取右边的这页的数据到内存，二分法查到数据2个name为zhaoliu人。
• （3）继续二分法比较age查到数据id=31
• （4）id=31回查聚簇索引先用二分法查找发现在31右边
• （5）读取31左边这页数据到内存，二分法查到数据并返回数据

如果你仅仅查找id，name和age数据那么这样就用到了覆盖索引，这样就不用回查聚簇索引，在第（3）步直接返回数据即可。