MySQL之索引

1 索引

1. 索引是什么？分哪几种？
2. 为什么使用B+树实现索引？
3. 索引的其它知识

1.1 索引是什么？分哪几种？

1. 聚集索引
2. 辅助索引

索引是查询优化最有效的手段，能够快速定位记录。索引包括普通索引、唯一索引、主键索引、组合索引、全文索引、哈希索引等。由于myisam和innodb两种存储引擎使用到的是聚集索引和辅助索引，所以我们着重介绍聚集索引和辅助索引。

1.1.1 聚集索引

索引树的节点存放了完整的行记录数据，聚集索引有且只有一个。对于innodb引擎来说，主键索引就是聚集索引；对于myisam引擎来说，因为不要求主键，且myisam引擎的数据文件和索引文件分开存储，索引树节点不存放数据，而是存放数据地址，所以myisam是非聚集索引，没有聚集索引。

1.1.2 辅助索引

不论哪种存储引擎，都存在主键索引和辅助索引。对于innodb存储引擎，主键索引就是聚集索引，辅助索引(非主键索引列)存放主键的值而不是数据地址；对于myisam存储引擎，主键索引(它可能也没主键)存放数据地址，辅助索引也同样存放数据地址，所以并没有什么区别。
由于innodb的辅助索引存放主键的值，所以innodb的主键不宜过长，每次非主键查询时，都需要通过辅助索引定位主键，再通过聚集索引定位数据。

1.2 为什么使用B+树实现索引？

1. 二叉搜索树、哈希、B树
2. B+树的特性

1.2.1 二叉搜索树、哈希、B树

• 二叉搜索树

每个节点只存储一个记录，当数据量很大的时候，树的深度有很大。

• 哈希

对于单行查询来说，哈希的查询时间复杂度确实比树的更小，但对于排序、分组和比较等查询来说，此时，哈希要比对每一个索引列的值，时间复杂度退化成了O(n)，而树形依旧是O(logn)，由于叶子结点间是有指针互相指向的，所以对于排序查询、比较查询等会快很多。

• B树

B树与B+树的区别在于，B+树的非叶节点不存放数据，假设树的节点可以存放16kb的数据，B树用了10kb的空间存放数据，6kb的空间存放节点，而B+树使用了16kb的空间存放节点，使得树的高度减少了，而树的高度对应了MySQL查询时磁头跳动的次数，一次磁头跳动对应一次I/O操作，而I/O操作是很耗时间的。

上图是索引的B+树数据结构。

1.2.2 B+树的特性

• 由于是m分叉的，高度能够大大降低
• 每个节点可以存储j个记录，如果节点大小设置为页大小，例如4kb，则能够充分利用预读的特性，极大减少磁盘IO。

数据预读：数据库中的数据是存在表空间的，表空间存放段，段里存放区，区里存放页，页里存放行，磁盘读写并不是按需读取的，而是按页预读，一次会读一页的数据，每次加载更多的数据，以便未来减少磁盘IO。

1.3 索引的其它知识

1. 回表查询
2. 索引覆盖
3. 组合索引
4. 最左匹配
5. 索引下推

1.3.1 回表查询

在innodb存储引擎中，先查询辅助索引树定位主键值，再查询一遍聚集索引树定位行记录，涉及两次索引树查询，所以查询速度会变慢，开发过程中应避免回表查询。

1.3.2 索引覆盖

索引覆盖是避免回表查询的一种优化手段。只需要查询辅助索引树就可以返回结果，而不需要查询聚集索引获取完整的行记录，例如

select * from table where name = "ewing";

辅助索引的叶子节点存放的是主键值，而聚集索引的叶子节点存放完整行记录，相同的页空间，辅助节点存放的节点更多，所以可以通过查询辅助索引直接定位到目标值，来优化查询速率。

select id from table where name = "ewing";

上述例子中，id为主键，name列加了索引，如果我们只需要主键值，通过将第一条sql语句改为第二条sql语句，可以加快查询速率。

1.3.3 组合索引

组合索引，是指在多个列上建立索引，此时采用的是B-树(非叶子节点存放数据)，例如索引index(a, b)，是先对a列进行排序，对于相同的a，再对b列进行排序。

1.3.4 最左匹配

创建一个index(a, b, c)索引，相当于创建三个索引index(a)、index(a, b)和index(a, b, c)，向最左边的a看齐。

1.3.5 索引下推

可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表字数。

参考文章

聚集索引 - 辅助索引
原理剖析：InnoDB与MyISAM 聚集索引与非聚集索引
重新学习Mysql数据库2：『浅入浅出』MySQL 和 InnoDB
MySQL索引解析（联合索引/最左前缀/覆盖索引/索引下推）

结语

本人所有博客仅用于学习记录，不做任何商业用途，如涉及侵权，还请联系删除，感谢阅读，欢迎留言，一起进步~