索引
索引的出现其实就是为了提高数据查询的效率,就像书的目录一样
索引常见的模型
- 哈希表,这种结构适用于只有等值查询的场景
- 有序数组,等值查询和范围查询场景中的性能就都非常优秀,但更新比较慢。有序数组索引只适用于静态存储引擎
- 树。
树可以有二叉,也可以有多叉。多叉树就是每个节点有多个儿子,儿子之间的大小保证从左到右递增。二叉树是搜索效率最高的,但是实际上大多数的数据库存储却并不使用二叉树。其原因是,索引不止存在内存中,还要写到磁盘上。你可以想象一下一棵100万节点的平衡二叉树,树高20。一次查询可能需要访问20个数据块。在机械硬盘时代,从磁盘随机读一个数据块需要10 ms左右的寻址时间。也就是说,对于一个100万行的表,如果使用二叉树来存储,单独访问一个行可能需要20个10 ms的时间,这个查询可真够慢的。为了让一个查询尽量少地读磁盘,就必须让查询过程访问尽量少的数据块。那么,我们就不应该使用二叉树,而是要使用“N叉”树。这里,“N叉”树中的“N”取决于数据块的大小。
树可以有二叉,也可以有多叉。多叉树就是每个节点有多个儿子,儿子之间的大小保证从左到右递增。二叉树是搜索效率最高的,但是实际上大多数的数据库存储却并不使用二叉树。其原因是,索引不止存在内存中,还要写到磁盘上。你可以想象一下一棵100万节点的平衡二叉树,树高20。一次查询可能需要访问20个数据块。在机械硬盘时代,从磁盘随机读一个数据块需要10 ms左右的寻址时间。也就是说,对于一个100万行的表,如果使用二叉树来存储,单独访问一个行可能需要20个10 ms的时间,这个查询可真够慢的。为了让一个查询尽量少地读磁盘,就必须让查询过程访问尽量少的数据块。那么,我们就不应该使用二叉树,而是要使用“N叉”树。这里,“N叉”树中的“N”取决于数据块的大小。
N叉树由于在读写上的性能优点,以及适配磁盘的访问模式,已经被广泛应用在数据库引擎中了。
Innodb 的索引模型
在InnoDB中,表都是根据主键顺序以索引的形式存放的,这种存储方式的表称为索引组织表。又因为前面我们提到的,InnoDB使用了B+树索引模型,所以数据都是存储在B+树中的。
每一个索引在InnoDB里面对应一棵B+树。
索引类型分为主键索引和非主键索引。
主键索引的叶子节点存的是整行数据。在InnoDB里,主键索引也被称为聚簇索引(clustered index)。
非主键索引的叶子节点内容是主键的值。在InnoDB里,非主键索引也被称为二级索引(secondary index)。
基于非主键索引的查询需要多扫描一棵索引树。因此,我们在应用中应该尽量使用主键查询。
索引维护
- 由于B+树为了维护索引的有序性,在插入新值的时候需要做必要的维护。会涉及到数据的挪动。而且如果当前数据页已经满的话,会新建一个数据页,把部分数据挪过去。这个过程称为页分裂。会对性能造成影响
- 当相邻两个页由于删除了数据,利用率很低之后,会将数据页做合并。
基于上面的案例,一般要求我们在建表的时候要有自增主键.也就是说,每次插入一条新记录,都是追加操作,都不涉及到挪动其他记录,也不会触发叶子节点的分裂。
而有业务逻辑的字段做主键,则往往不容易保证有序插入,这样写数据成本相对较高。
除了考虑性能外,我们还可以从存储空间的角度来看。假设你的表中确实有一个唯一字段,比如字符串类型的身份证号,那应该用身份证号做主键,还是用自增字段做主键呢?
由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键,那么每个二级索引的叶子节点占用约20个字节,而如果用整型做主键,则只要4个字节,如果是长整型(bigint)则是8个字节。
显然,主键长度越小,普通索引的叶子节点就越小,普通索引占用的空间也就越小。
所以,从性能和存储空间方面考量,自增主键往往是更合理的选择。
有没有什么场景适合用业务字段直接做主键的呢?还是有的。比如,有些业务的场景需求是这样的:
- 只有一个索引;
- 该索引必须是唯一索引。
你一定看出来了,这就是典型的KV场景。
由于没有其他索引,所以也就不用考虑其他索引的叶子节点大小的问题。
这时候我们就要优先考虑“尽量使用主键查询”原则,直接将这个索引设置为主键,可以避免每次查询需要搜索两棵树。
数据页的合并
当从InnoDB表删除数据时,相应的数据是先打上删除标签(deleted mark),而后再由purge线程执行清理工作。
清理工作结束后,如果两个相邻的数据页存储填充率低于一定程度,就会尝试合并页,以降低碎片率,提高存储效率。
或者经过多次长度变小的UPDATE操作后(将varchar列长度更新变短),数据页填充率低于一定程度也会尝试合并。
合并完毕之后,空出来的页就会被标记为空闲页,等待再分配。这个工作是InnoDB后台线程自动完成的,无需人为干预、控制。
-
合并数据页,是通过参数
MERGE_THRESHOLD
的值确定的,当InnoDB数据页填充率低于该阈值时,就会尝试进行合并页操作。该参数的默认值是50,最小值是1,在5.6版本之后允许自行指定设置,在5.6之前的版本中则是被硬编码的,无法修改. -
默认的是当发现两个相邻页的填充率都低于50%时,就会尝试进行合并。
-
除了表可以设置外,单个索引也可以设置合并阙值.这个只能在创建索引时一次性指定,不能中途修改
ALTER TABLE t_sk ADD INDEX k1(c1) COMMENT 'MERGE_THRESHOLD=20';
-
页合并的统计情况,可以通过查询
INNODB_METRICS
表获取到。 -
alter table T engine=InnoDB #重建索引
覆盖索引
正常的根据索引进行查询,会有回表的过程。即查询两次。
而覆盖索引可以减少树的搜索次数,显著提提升查询性能,所以使用覆盖所以是一个常用的性能优化。
select * from T where k between 3 and 5 #会回表查询
select ID from T where k between 3 and 5 # 普通索引记录了主键的值,直接返回结果
联合索引
联合索引也属于覆盖索引的一种
比如我们在市民信息表中,建立一个(身份证、姓名)的联合索引。当用户根据身份证来查询他的姓名时,这个索引就起到了覆盖索引的效果。它可以在这个高频请求上用到覆盖索引,不再需要回表查整行记录,减少语句的执行时间。
当然这种索引的维护是有代价的,它是以空间来换时间。
最左前缀原则
通过最左匹配原则,可以减少索引建立的次数.比如(a,b,c) 实际建立了(a,b,c),(a),(a,b)三个索引
注意事项:
- 遇到范围查询(>,<,between,like)会停止
- where中and条件的先后顺序对如何选择索引是无关的。因为优化器会去分析判断选用哪个索引。
- 部分值匹配时,只要条件中有最左索引项就会用到索引
- 条件中没有最左索引,不会用到索引,全表扫描
- like 以%开头,索引无效;当like前缀没有%,后缀有%时,索引有效。
- or语句前后没有同时使用索引。当or左右查询字段只有一个是索引,该索引失效,只有当or左右查询字段均为索引时,才会生效
- 数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型,使索引无效,产生全表扫描。
- 在索引列上使用 IS NULL 或 IS NOT NULL操作。索引是不索引空值的,所以这样的操作不能使用索引。
- 在索引字段上使用not,<>,!=。不等于操作符是永远不会用到索引的,因此对它的处理只会产生全表扫描
- 对索引字段进行计算操作、字段上使用函数
- 当全表扫描速度比索引速度快时,mysql会使用全表扫描,此时索引失效。
索引下推
mysql> select * from tuser where name like '张%' and age=10 and ismale=1;
这个语句,只会使用到name 这个索引
在MySQL 5.6之前,只能开始一个个回表。到主键索引上找出数据行,再对比字段值。
而MySQL 5.6 引入的索引下推优化(index condition pushdown), 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。