目录
哈希表:适合等值查询,不适合范围查询
有序数组:良好的支持等值和范围查询,但更新操作效率低
搜索树:N叉数,减少读取磁盘的次数
基于主键索引和普通索引的查询有什么区别? 普通索引只是路由到了主键,需要回表查询具体数据,多扫描一棵树
索引维护:非自增键值会带来数据页的分裂与合并,降低效率
自增主键:自增 的性能 和 数据类型带来的空间差异。主键占字节越小,普通索引树越小。
应用覆盖索引提高性能: 通过联合索引来应用覆盖索引,提高性能
索引下推:按照联合索引的顺序依次排序
MySQL实战-04【5525】
一、常见的索引模型:
索引的出现是为了提高查询效率,但是实现索引的方式却有很多种,常见的三种:哈希表,有序数组和搜索树
哈希表:
哈希的思路很简单,把值放在数组里,用一个哈希函数把 key 换算成一个确定的位置,然后把 value 放在数组的这个位置。
哈希索引做区间查询的速度是很慢的。你可以设想下,如果你现在要找身份证号在[ID_card_X, ID_card_Y]这个区间的所有用户,就必须全部扫描一遍了。所以,哈希表这种结构适用于只有等值查询的场景,比如 Memcached 及其他一些 NoSQL 引擎。
键值对存储的数据库 —— 【Redis】
有序数组:
有序数组在等值查询和范围查询场景中的性能就都非常优秀。等值查询和范围查询都可以通过应用二分法。
仅仅看查询效率,有序数组就是最好的数据结构了。但是,在需要更新数据的时候就麻烦了,你往中间插入一个记录就必须得挪动后面所有的记录,成本太高。
所以,有序数组索引只适用于静态存储引擎,比如你要保存的是 2017 年某个城市的所有人口信息,这类不会再修改的数据。
搜索树:
N 叉树由于在读写上的性能优点,以及适配磁盘的访问模式。
二、InnoDB 的索引模型
在 InnoDB 中,表都是根据主键顺序以索引的形式存放的,这种存储方式的表称为索引组织表。数据都是存储在 B+ 树中。每一个索引在 InnoDB 里面对应一棵 B+ 树。B+ 树能够很好地配合磁盘的读写特性,减少单次查询的磁盘访问次数。【索引的存储形式】
从图中不难看出,根据叶子节点的内容,索引类型分为主键索引和非主键索引。
- 主键索引的叶子节点存的是整行数据。在 InnoDB 里,主键索引也被称为聚簇索引(clustered index)。
- 非主键索引的叶子节点内容是主键的值。在 InnoDB 里,非主键索引也被称为二级索引(secondary index)。【主键长度越小,普通索引的叶子节点就越小,普通索引占用的空间也就越小】
基于主键索引和普通索引的查询有什么区别?
- 如果语句是 select * from T where ID=500,即主键查询方式,则只需要搜索 ID 这棵 B+ 树;
- 如果语句是 select * from T where k=5,即普通索引查询方式,则需要先搜索 k 索引树,得到 ID 的值为 500,再到 ID 索引树搜索一次。这个过程称为 回表。
基于非主键索引的查询需要多扫描一棵索引树。
索引维护
B+ 树为了维护索引有序性,在插入新值的时候需要做必要的维护。如果索引值不是自增的,插入删除索引的时候,就会导致数据页的分裂和合并。导致效率下降。
自增主键:
性能:选取业务逻辑字段做主键,则往往不容易保证有序插入,这样写数据成本相对较高。
空间:每个非主键索引的叶子节点上都是主键的值。如果用String类型做主键,那么每个二级索引的叶子节点占用约 20 个字节,而如果用整型做主键,则只要 4 个字节,如果是长整型(bigint)则是 8 个字节。
从性能和存储空间方面考量,自增主键往往是更合理的选择。
问题:对于下面这两个重建索引的作法,是否合理。
-- 重建索引 alter table T drop index k; alter table T add index(k); -- 重建主键索引 alter table T drop primary key; alter table T add primary key(id);
重建索引 k 的做法是合理的,可以达到省空间的目的。索引可能因为删除,或者页分裂等原因,导致数据页有空洞,重建索引的过程会创建一个新的索引,把数据按顺序插入,这样页面的利用率最高,也就是索引更紧凑、更省空间。
重建主键的过程不合理。不论是删除主键还是创建主键,都会将整个表重建。所以连着执行这两个语句的话,第一个语句就白做了。这两个语句,你可以用这个语句代替 : alter table T engine=InnoDB。
三、覆盖索引
ID为表T的主键,k为表T的索引。那么以下两句的执行差异?
select * from T where k between 3 and 5; -- 需要回表
select ID from T where k between 3 and 5; -- 不需要回表
时只需要查 ID 的值,而 ID 的值已经在 k 索引树上了,因此可以直接提供查询结果,不需要回表。也就是说,在这个查询里面,索引 k 已经“覆盖了”我们的查询需求,我们称为覆盖索引。
应用覆盖索引提高性能
由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。
如果有根据身份证号查询市民信息的需求,我们只要在身份证号字段上建立索引就够了。而再建立一个(身份证号、姓名)的联合索引,是不是浪费空间?
如果现在有一个高频请求,要根据市民的身份证号查询他的姓名,这个联合索引就有意义了。它可以在这个高频请求上用到覆盖索引,不再需要回表查整行记录,减少语句的执行时间。
四、最左前缀原则
联合索引的顺序
依据B+Tree索引的最左前缀原则,在建立联合索引的时候,如何安排索引内的字段顺序。
- 第一个原则【索引数量】:如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的。
- 第二个原则【空间占用】:空间考虑,比如市民表的情况,name 字段是比 age 字段大的 ,那我就建议你创建一个(name,age) 的联合索引和一个 (age) 的单字段索引。
索引下推:
MySQL 5.6 引入的索引下推优化(index condition pushdown), 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。
问题:既然主键包含了 a、b 这两个字段,那意味着单独在字段 c 上创建一个索引,就已经包含了三个字段了呀,为什么要创建“ca”“cb”这两个索引?为了这两个查询模式,这两个索引是否都是必须的?为什么呢?
CREATE TABLE `geek` ( `a` int(11) NOT NULL, `b` int(11) NOT NULL, `c` int(11) NOT NULL, `d` int(11) NOT NULL, PRIMARY KEY (`a`,`b`), KEY `c` (`c`), KEY `ca` (`c`,`a`), KEY `cb` (`c`,`b`) ) ENGINE=InnoDB;
select * from geek where c=N order by a limit 1; select * from geek where c=N order by b limit 1;
- 主键 a,b 的聚簇索引组织顺序相当于 order by a,b ,也就是先按 a 排序,再按 b 排序,c 无序。
- 索引c,按照c排序,同时记录主键;
- 索引 ca 的组织是先按 c 排序,再按 a 排序,同时记录主键;
- 索引 cb 的组织是先按 c 排序,在按 b 排序,同时记录主键;
结论:2和3 同效,因此 ca 可以去掉,cb 需要保留。