索引
查找一条数据的过程
先看下InnoDB的逻辑存储结构:
表空间:可以看做是InnoDB存储引擎逻辑结构的最高层,所有的数据都存放在表空间中。默认有个共享表空间ibdata1。如果启用innodb_file_per_table参数,需要注意每张表的表空间内存放的只是数据、索引和插入缓冲Bitmap页,其他类的数据,如回滚信息、插入缓冲索引页、系统事务信息、二次写缓冲等还是存放在原来共享表空间中。
段:
表空间是由各个段组成,常见的段有数据段、索引段、回滚段等。数据段即为B+树叶子节点(Leaf node segment),索引段即为B+树非叶子节点(Non-leaf node segment)
区区:是由连续页组成的空间,在任何情况下每个区大小都为1MB。默认情况下,存储引擎页的大小为16KB,即一个区中一共有连续64个连续的页。而为保证页的连续性,InnoDB存储引擎一次从磁盘申请4-5个区。
页:
页(也可以称块),是InnoDB磁盘管理的最小单位。默认每个页大小16KB。1.2x版本后也可以通过参数innodb_page_size设置为4k、8k、16k
如查一条数据:select * from user where id=5;
这里id是主键,我们通过这棵B+树来查找,首先会去找到根页,每张表的根页位置在表空间文件中是固定的;找到根页后通过二分查找法,定位到id=5的数据应该在指针P5指向的页中,那么进一步去page number=5的页中查找,同样通过二分查询法即可找到id=5的记录:
计算一棵B+树可以存放多少行数据
也可以通过命令查看InnoDB每页默认16KB:
先计算非叶子节点, 假设主键ID为bigint类型,长度为8字节,而指针大小在InnoDB源码中设置为6字节,这样一共14字节
而一个页中能存放多少这样的单元,其实就代表有多少指针,即16384/14=1170。
那么可以算出一棵高度为2的B+树,能存放1170*16=18720条这样的数据记录。
根据同样的原理我们可以算出一个高度为3的B+树可以存放:1170117016=21902400条这样的记录。
所以在InnoDB中B+树高度一般为1-3层,它就能满足千万级的数据存储。
索引一些概念
- 聚簇索引(clustered index): 就是将索引和数据放到一起,找到索引也就找到了数据;如下图叶子节点存放一行所有数据。
- 辅助索引(Secondary Index或非聚簇索引): 就是将数据和索引分开,查找时需要先查找到索引,然后通过索引回表找到相应的数据。
回表:先通过数据库索引扫描出数据所在的行,再通过行主键id取出索引中未提供的数据,即基于非主键索引的查询需要多扫描一棵索引树。
如下图,辅助索引查找后,会再回表到聚簇索引,最后找到数据。
- 联合索引:指对表上多个列进行索引。
- 覆盖索引: 即从辅助索引中就可以得到查询的记录,而不需要查询聚簇索引中的记录。
使用覆盖好处:
- 辅助索引不包含整行记录的所有信息,故其大小要远小于聚簇索引,减少大量IO操作。
- 对某些统计(如count(id))并不会通过查询聚簇索引来进行统计,减少IO操作
唯一索引:以唯一列生成的索引,该列不允许有重复值,但允许有空值(NULL)
索引下推:MySQL 5.6引入了索引下推优化,可以在索引遍历过程中,对索引中包含的字段先做判断,过滤掉不符合条件的记录,减少回表字数。
为什么选B+树,而不是B树
B树不管叶子节点还是非叶子节点,都会保存数据,这样导致在非叶子节点中能保存的指针数量变少
指针少的情况下要保存大量数据,只能增加树的高度,导致IO操作变多,查询性能变低;
为什么InnoDB只有一个聚簇索引,而不将所有索引都使用聚簇索引?
因为聚簇索引是将索引和数据都存放在叶子节点中,如果所有的索引都用聚簇索引,则每一个索引都将保存一份数据,会造成数据的冗余,在数据量很大的情况下,这种数据冗余是很消耗资源的。
什么情况下会发生明明创建了索引,但是执行的时候并没有通过索引呢?
查询优化器。
一条SQL语句的查询,可以有不同的执行方案,至于最终选择哪种方案,需要通过优化器进行选择,选择执行成本最低的方案。
优化过程大致如下:
- 1、根据搜索条件,找出所有可能使用的索引
- 2、计算全表扫描的代价
- 3、计算使用不同索引执行查询的代价
- 4、对比各种执行方案的代价,找出成本最低的那一个 。
索引的优缺点
索引的优点如下:
- 1、唯一索引可以保证每一行数据的唯一性
- 2、提高查询速度
- 3、加速表与表的连接
- 4、显著的减少查询中分组和排序的时间
- 5、通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。
索引的缺点如下:
- 创建索引时,需要对表加锁,在锁表的同时,可能会影响到其他的数据操作
- 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行 INSERT、UPDATE 和 DELETE。因为更新表时,MySQL 不仅要保存数据,还要保存索引文件。
- 建立索引会占用磁盘空间的索引文件。一般情况这个问题不算严重,但如果你在一个大表上创建了多种组合索引,且伴随大量数据量插入,索引文件大小也会快速膨胀。
- 如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。
- 对于非常小的表,大部分情况下简单的全表扫描更高效。
使用索引时的注意事项
原则:
不应该
应该
索引失效的场景
其他注意事项:
《MySQL技术内幕》
https://mp.weixin.qq.com/s/6j64s9W6ogs5Y8BbhhkgnA
https://mp.weixin.qq.com/s/KB73550tKpNccW-WKxT7-A
https://mp.weixin.qq.com/s/ovMx9Dv9NCFxSsFM98uYFw