【MySQL之轨迹】MySQL索引与Explain性能分析

---

0. 什么是索引

索引(Index)是帮助MySQL高效获取数据的数据结构

查看表的索引：show index from <表名>

---

1. 索引的数据结构

B+树（B 树的优化版）

B树 与 B+树的比较
B 树

B+树

可以看到，B 树的每一个结点都要存储数据，这就导致了在同样的大小下（InnoDB 每页默认大小为 16 KB）分支点所能存储的数据变少，而B+树的分支点仅存储主键id和指针，能存放更多指针，也就使得叶子数变多，树的高度减少，IO次数随之减少，提高了效率

---

2. 索引分类

① 主键索引
设定为主键后，数据库会自动建立索引，InnoDB 为聚簇索引

② 唯一索引
与主键索引类似，只是主键索引的列值不能为空，而唯一索引的列值可以为空。
但由于唯一，故只能存在一个为空的

创建唯一索引
1) 建表时创建（这种方式无法指定索引名，默认索引名为字段名）
create table t_user(
	id int,
	name varchar(20),
	age int,
	unique(name)	// 为 name 创建唯一索引
);

2) 建表后创建（其中 name_index 为索引的名字，可以随意取名，但一般按照这种格式）
create unique index name_index on t_user(name)

② 单值索引（单列索引，普通索引）
为表中其他字段建立的索引为单值索引。
一个索引只包含单个列，一个表可以有多个单值索引

创建单值索引
1) 建表时创建
create table t_user(
	id int,
	name varchar(20),
	age int,
	key(name)	// 为 name 创建普通索引
);

2) 建表后创建
create index name_index on t_user(name);

作用场景
where name 和 where age 都可以用到该单值索引
但是 where name and age 无法使用该索引

④ 复合索引（联合索引、多列索引）
一个索引包含了多个列，可用于基于多个列的查询，如

创建复合索引
1) 建表时创建
create table t_user(
	id int,
	name varchar(20),
	age int,
	sex int,
	key(name, age, sex)	// 为 name, age, sex  创建联合索引
);

2) 建表后创建
create index name_age_sex_index on t_user(name, age, sex);

作用场景，遵循最左前缀原则，但 mysql 在查询过程中会动态调整查询字段的顺序以更好地利用索引
以下是在 where 后使用查询字段的顺序
基于 name, name age, name age sex 的查询，都能用上该索引
而基于 age, sex, age sex 的查询，都不能用上该索引
但基于 name sex age, sex name age 的查询。可以用上该索引，因字段顺序被动态调整了，符合最左前缀原则

删除索引
drop index <索引名> on <表名>

---

3. 覆盖索引

要查询的字段刚好与索引中的字段 部分相同
则 select 所需数据从索引中就可以直接得到，无需再去获取数据
这也就是不建议使用 select * 的原因

---

4. 聚簇索引与非聚簇索引（二级索引）

① 聚簇索引
数据和索引存储在一起，索引结构的叶子结点保存了行数据，而结点之包含索引

在 InnoDB 中，索引默认使用的是主键，如果没有主键则使用唯一键，没有唯一键则由 InnoDB 隐式生成一个主键充当索引

在 InnoDB 中，聚簇索引上创建的索引称为辅助索引，非聚簇索引都是辅助索引。辅助索引总需要二次查找（回表）。原因是辅助索引叶子结点上存储的是主键id，得到主键id后还需要进行一次聚簇索引获取到行数据

思考：为什么要记录主键id，而不记录数据地址直接找到呢？
答：在增删改时，数据的地址可能发生变化，这就导致了索引树上叶子结点的数据需要修改，而存储主键id则不会出现这种情况

② 非聚簇索引（二级索引）
数据和索引分开存储，索引结构的叶子结点指向了数据对应的位置。

MYISAM 使用的就是非聚簇索引
其主键索引和辅助键索引的结构完全一致，只是结点存储的是主键或者辅助键的区分
叶子结点存储的地址指向真实的表数据，因此索引树是独立的，辅助键索引无序依赖主键索引，故只需要一次查询（避免了回表，提升了查询效率）

③ 聚簇索引的优势
虽然使用辅助索引需要经过两次查找降低了效率，但由于索引和数据存储在一次，在读取时已经一起加载到了缓存区，下次再查找时，直接从缓存区取数据就可以了。而非聚簇索引存储的是数据的物理位置，每次取诗句都需要发生一次新的 IO 操作，效率不如聚簇索引的高
同时，非聚簇索引需要维护数据的物理位置，当发生增删操作时，数据的物理位置可能发生改变，这就需要额外维护数据的物理地址降低了效率，而聚簇索引的辅助索引存放的是主键id，没有这个问题

④ 使用聚簇索引的注意点
最好使用 int 类型的自增，由于 int 类型能进行比较方便B+树的形成，而且主键自增使得每次叶子结点都是往后面添加，而不会从中间添加导致树的结构要调整，提高效率

---

5. 索引失效

① 使用 LIKE 关键字： 如果 % 出现在第一个字符时，索引失效
② 使用多列索引： 不符合最左前缀原则则失效（如第一个字段不是复合索引的第一个索引的话）
③ 是同 OR 关键字： 如果前后有一个条件的列不是索引，则索引失效，必须两个列都使用了索引才可以

---

6. 使用 Explain 了解索引性能

Explain 执行计划包含的信息

① id： select 查询的序列号，表示执行 select 子句或操作表的顺序

• id 相同时，执行顺序为由上到下
• id 不同时，id 值越大优先级越高，越先被执行

② select_type： 查询的类型

• SIMPLE，简单查询，不包含子查询或者 UNION
• PRIMARY，有子查询的话，最外层标记为 PRIMARY
• SUBQUERY，在 select 或 where 中包含的子查询
• DERIVED，在 from 中包含的子查询
• UNION，第二个 select 出现在 UNION 之后，标记为 UNION
• UNION RESULT，从 UNION 获取到结果的 select

③ table： 查询的是哪张表

④ type： 上最优，下最劣，依次递减

• system：表只有一行数据，是 const 的特例

• const：表通过一次索引就能找到，常见于用 where 去比较主键或者唯一键的索引

• eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或者唯一索引扫描

• ref：非唯一性索引扫描，返回匹配某个单独值的所有行，常见与使用 where=? 时，有多个行符合这个条件，如：
  

• range：只检索给定范围的行，常见于在 where 钟使用了 between < > in 等查询

• index：只遍历索引树，数据是从索引中读取的

• all：全表扫描，数据是从磁盘中读取的，效率最低。只要没创建索引，通通是全表扫描

⑤ possible_keys： 系统推测可能会用到的索引，但不一定被用到

⑥ key： 实际上用到的索引，若使用了覆盖索引，则改索引只出现在 key 列表中

⑦ key_len： 索引中使用的字节数，长度越长，精确度越高但效率越低。所以再不损失精确度的情况下，长度越短越好（该长度是索引字段的最大可能长度，是计算出来的而并非实际是使用的长度）

⑧ ref： 显示索引的哪一列被使用了，可能是常数 const，也可能是被用于查找的索引列上的值 xx库.xx表.xx列

⑨ rows： 根据表统计信息和索引选用情况，大致估算出找到所需记录所需要读取的行数（当然是越小越好）

⑩ Extra： 不适合在其他列中显示，但十分重要的额外信息

• Using filesort，系统对数据进行了二外的排序，而不是按照索引的排序进行的，不好！！

  常见于 复合索引+排序 中，如有复合索引 a b c，where a order by
  这时候 c 无法使该复合索引自带的排序，而需要自己排序依次
  但如果是 where a order by b, c
  那么复合最左前缀原则，可以利用到该复合索引，不会出现 Using filesort
  

• Using temporary，使用了临时表保存中间结果，系统在对查询结果排序时使用了临时表。常见于排序 order by 于分组查询 group by 中。非常不好！！
• Using index，表名响应的 select 操作中使用了覆盖索引直接拿到数据，没有去表的数据行，好！！
• Using where，表明索引被用来执行索引键值的查找（使用了 where 过滤）
• Using join buffer，使用了连接缓存
• Impossible where，where 子句的值总是 false，没有任何作用

---

倘若迷茫于星河，多看几眼就好啦~（IceClean）