【深度】MYSQL索引结构详解

MYSQL 是目前企业使用最广泛的数据库之一，索引优化是日常工作中比较常见性能优化工作之一，故本文主要是针对索引相关内容进行梳理和说明。

1 索引是什么

索引是MySQL维护的满足特定查找算法的数据结构，这种数据结构以特定方式指向数据行，便于查询，这种数据结构，叫做索引

2 为什么要建立索引

本质上将任何一个存储系统，在数据量达到一定级别时，都会出现性能问题，尤其是查询性能问题，作为技术人员在日常工作中屡见不鲜，所以建立索引，就是为了提高查询性能

3 索引的优缺点

优点：能有效提高查询性能
缺点：增加了数据库的存储空间，在插入和更新时，速度减慢（原因是索引调整）

4 索引分类

不同的数据库引擎支持不同类型的索引类型，索引根据不同的方式可以分为不同的类别

① 按表列属性：单列索引、联合索引；单列索引和联合索引又包括：普通索引、主键索引、唯一索引、全文索引
单列索引是以表的某一列建立的索引
联合索引则是以表的多列建立的索引，使用场景是索引覆盖时，性能比较高
唯一索引是以表的列定义为unique时建立的索引
全文索引是针对文本格式的列建立索引，不过MySQL本身建立该索引不多，更多的是采用其他方式实现，比如ES

② 按数据结构：B+tree索引、hash索引、T-tree索引、R-tree索引
不同的数据结构适合不同的数据格式，故存在多种结构
B+tree是最常见的索引数据结构
R+tree是空间数据采用的存储结构
hash是MySQL内部在查询过程中可能自己决定是否采用的一种索引结构，是MySQL本身自我优化的一个点，对外不支持hash索引，但内部是支持的

③按存储结构：聚簇索引、辅助索引
这两个索引也是日常被问的最多的，innodb引擎这两种索引都支持，myisam引擎只支持辅助索引（普通索引）
对于innodb而言，一次查询可能至少要做一次索引过滤，即根据主键索引查询，查到索引即查到数据行，通过辅助索引查询，则需要二次索引查询，先根据辅助索引（索引存的是主键），查询到主键，再根据主键索引查询数据行
对于myisam而言，根据辅助索引（存的是数据行地址）查询数据地址，然后再根据数据地址取得对应的数据
聚簇索引：索引、数据存储在一起，索引顺序和磁盘存储顺序一致
辅助索引：索引、数据分开存储，索引顺序和磁盘存储顺序不一致

5 聚簇索引的优缺点

优点：能有效提高查询数据的性能
缺点：索引与数据存储在一起，插入、更新效率低，因为要维护索引结构，磁盘数据页重排

聚集索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的，所以，一般要求数据表有一个业务无关的自增列作为聚集索引，是比较好的实践。否则如果聚集索引比较散列，在插入数据时，需要先找到要插入的数据页，最后数据页数据重排，导致数据插入慢（其实是磁盘数据的排序），如果数据比较散列，那么会导致磁盘存储的**数据页（默认innodb_page_size=16k）**的分裂和合并。

非叶子节点只存储索引列数据，那么为什么主键索引要尽量短，是因为所有的辅助索引都会存储主键索引，如果主键索引很长，那么整个索引数据占用的空间就会大很多

6 B+树及数据存储计算

B即balance，是多路搜索树，和B树对比，有效降低树的高度，增大节点存储数据量
m阶B+树，是指一个节点能拥有的最大子节点数，如果一个节点最多有 n 个key，那么这个节点最多就会有 n+1 个子节点，这棵树就叫做 n+1（m=n+1）阶树

在数据库中，B+Tree的高度一般都在2-4层，MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

如果一个数据页是16k，存储一个int型主键4B或者8B，那么实际情况中每个节点可能不能填充满，一般1/16空闲保留

按MySQL默认一页16k，一条数据1k，那么一个数据页能存储16条数据，假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，共14字节，一个页中能存放16384/14=1170个单元。那么高度为2的B+树，能存放117016=18720条数据记录，高度为3的B+树可以存放：11701170*16=21902400条数据记录，因此B+树的高度一般为2-4层，就可以实现千万级数据存储，一次页的查找代表一次IO，所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据

7 图例索引结构

假设有如下表结构：

id	name	age
1	zs	25
2	ll	20
3	ww	55
4	zl	20
…	…	…

8 存储引擎innodb与myisam索引对比

	innodb	myisam
存储文件	.ibd文件（数据、索引） .frm文件（数据表定义）	.MYD文件（数据） .MYI文件（索引） .frm文件（数据表定义）
索引支持	聚集索引、普通索引	主键索引、普通索引
索引结构	B+ Tree	B+ Tree

9 最左前缀原则

MySQL多列索引需要满足最左前缀原则，否则无法命中索引，进行扫表，例如有index(a, b, c)，那么就相当于建立了index(a), index(a,b)，index(a, b, c)三个索引，这也是在做索引优化的时候，经常使用的规则之一，最左前缀要求区分度最高的、最常使用的字段作为第一列

select id from table where a = 'xxx'; //命中索引，使用的是a列
select id from table where a = 'xxx' and b = 'xx'; //命中索引，使用的是a,b列
select id from table where a = 'xxx' and b = 'xx' and c = 'xxx'; //命中索引， 使用的是a,b,c列
select id from table where a = 'xxx' and c = 'xx'; //命中索引，使用的是a列
//其他情况条件查询无法命中索引

假设有索引index(a, b)

select id from table where a = 'xxx' and b = 'xx'; //命中索引
select id from table where b = 'xxx' and a = 'xx'; //同样命中索引，因为查询优化器会进行优化

另，可参考【MYSQL 索引最左匹配原则】一文，是一个很好的实例分析

10 索引优化方法总结

索引优化是在数据达到一定级别必须处理的一个问题，个人总结的方法有以下几个
1 严格控制索引数量，索引越多，性能越差
2 严格控制建立索引的操作，避免建立冗余索引
3 建立索引，一定要确保索引命中，避免经验主义，explain看一下，但不一定可靠
4 索引命名规范化，例如：idx_col1_col2(col1, col2)
5 force index 有时候需要指定
6 索引失效情况要明确（类型不一致，null问题，函数计算、模糊查询等）

本文就总结这些，欢迎一起交流学习。