1 索引
- 索引是什么?分哪几种?
- 为什么使用B+树实现索引?
- 索引的其它知识
1.1 索引是什么?分哪几种?
- 聚集索引
- 辅助索引
索引是查询优化最有效的手段,能够快速定位记录。索引包括普通索引、唯一索引、主键索引、组合索引、全文索引、哈希索引等。由于myisam和innodb两种存储引擎使用到的是聚集索引和辅助索引,所以我们着重介绍聚集索引和辅助索引。
1.1.1 聚集索引
索引树的节点存放了完整的行记录数据,聚集索引有且只有一个。对于innodb引擎来说,主键索引就是聚集索引;对于myisam引擎来说,因为不要求主键,且myisam引擎的数据文件和索引文件分开存储,索引树节点不存放数据,而是存放数据地址,所以myisam是非聚集索引,没有聚集索引。
1.1.2 辅助索引
不论哪种存储引擎,都存在主键索引和辅助索引。对于innodb存储引擎,主键索引就是聚集索引,辅助索引(非主键索引列)存放主键的值而不是数据地址;对于myisam存储引擎,主键索引(它可能也没主键)存放数据地址,辅助索引也同样存放数据地址,所以并没有什么区别。
由于innodb的辅助索引存放主键的值,所以innodb的主键不宜过长,每次非主键查询时,都需要通过辅助索引定位主键,再通过聚集索引定位数据。
1.2 为什么使用B+树实现索引?
- 二叉搜索树、哈希、B树
- 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.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索引解析(联合索引/最左前缀/覆盖索引/索引下推)
结语
本人所有博客仅用于学习记录,不做任何商业用途,如涉及侵权,还请联系删除,感谢阅读,欢迎留言,一起进步~