MySQL索引优化手段

假设有一张表 user，三个字段 id (主键索引) 、 age(非唯一索引)、name(联合索引idx_name_age)、gender、empid（联合索引idx_gender_empid）。 表中现有数据：

• id: 10, age: 10_v1, name: aaa，gender: M，empid: 10
• id: 11, age: 10_v2, name: bbb，gender: F，empid: 11
• id: 20, age: 20, name: aac，gender: M，empid: 20
• id: 30, age: 30, name: ddd，gender: F，empid: 30

1. 索引覆盖

SELECT
	id, age
FROM
	user
WHERE
	age = 20

age索引的B+树里就包含了id和age，不需要回表

2. 索引下推

SELECT
	*
FROM
	user
WHERE
	name like "aa%"
	and age = 10

在MySQL5.6之前，由于最左前缀索引原则，这里的idx_name_age索引只能用到name，于是需要拿到id = 10、20的两条记录去回表查询，回表的时候再根据age去过滤

MySQL5.6之后引入了索引下推，在idx_name_age索引中拿到两条记录后，不着急回表，先根据age的查询条件去过滤一遍，最终回表的时候只拿着id = 10这一条记录去回表

3. 索引跳跃扫描

SELECT
	*
FROM
	user
WHERE
	empid = 20

正常来说，根据最左前缀原则，由于empid查询条件前面没有gender查询条件，不满足最左前缀原则，应该用不到索引idx_gender_empid；但是MySQL8.0之后引入了索引跳跃扫描，由于gender字段区分度小，优化器会将SQL进行如下优化：

SELECT * FROM user WHERE gender = "M" AND empid = 20
UNION ALL
SELECT * FROM user WHERE gender = "F" AND empid = 20

4. 索引合并

查询优化器同时使用多个互相独立的索引来处理一个查询，将不同索引扫描结果（主键集）通过交集、并集或排序并集方式合并，以减少回表次数，提高复杂查询效率。下面例子中key1和key2分别为两个独立的索引

4.1 交集合并

SELECT * FROM table WHERE key1 = xxx AND key2 = yyy

对key1 = xxx和key2 = yyy得到的两个主键集合做交集，再去回表

4.2 并集合并

SELECT * FROM table WHERE key1 = xxx OR key2 = yyy

对key1 = xxx和key2 = yyy得到的两个主键集合做并集，再去回表

对于复合索引的情况：