MySQL的索引

B+Tree

引用文章:https://www.cnblogs.com/dongguacai/p/7241860.html

所有数据都保存在叶子节点上。相较于B树，范围查找效率更高。

即MySQL的InnoDB引擎，表中数据都是根据主键的索引顺序存放。

每一个索引在InnoDB中都对应一个B+树。

主键索引

就是建表时候有primary key的字段，比如说id。又叫聚簇索引。

这个id字段对应的索引结构大概就这样。最下层的子节点上挂着数据。

二级索引

除了主键索引以外的其他索引。

它和主键索引的区别就是，它的最下层挂的不是数据，而是主键id。比如某个普通索引 a。

大概就这样。

回表

如果基于普通索引所在字段进行条件查询，比如select * from t where a = 30;

这个查询分为两个步骤，

步骤1：先去a字段的索引B+树上找到对应的主键索引位置。

步骤2：再去id的索引树种再查询一次，因为主键索引在子节点中挂着数据，拿到对应的30。

这个过程就是回表。

覆盖索引

如果是换一种SQL写发呢，比如 select id from t where a = 30;

二级索引 a 子节点上挂的就是id的值，所以可以直接把这个id的值 3 拿到，返回给客户端，避免了一次回表。

所以在创建索引的时候，也可以考虑业务场景，查询条件字段和查询结果字段，某些情况下可以创建联合索引。

最左前缀原则

适用于联合索引和普通索引。

比如联合索引 (`a`,`b`),那么select * from t where b = 3； 这种是不会走索引的。只有 select * from t where a=3 ;可以

普通索引，如果是一个varchar类型的，比如字段 `name` ,select * from t where `name` LIKE '王%'是可以走name索引。

如果是select * from t where `name` like '%富贵';这就就不会走name索引。

索引下推

区分MySQL版本，5.6及以后的MySQL有这种优化。

比如一张表有一个普通索引 (`name`,`score`) 和一个主键索引(`id`)，其中name字段是varchar类型代表学生姓名，score是int类型代表学生考试分数。

现在要查全校所有姓王的，分数大于60分的学生。

SQL大概是这样：SELECT * FROM `t` WHERE `name` LIKE '王%' AND `score` > 60;

这个SQL的执行过程是什么样呢。

首先查询的是所有字段，那么回表是少不了的了。

先根据索引定位出所有name字段以'王'开头的所有数据，比如有100个，回表100次，到id索引树种找到这些数据，然后比较score字段是不是超过60进行过滤。

索引下推就是对这种查询进行优化。

由于是组合索引，所以在(`name`,`score`)索引上有两列的数据，name和score,就可以在回表之前进行一次过滤。

查询出一批name字段以'王'开头的学生，比如有100个，然后对score字段进行过滤，剩下50个，回表50次，到id索引树种找到这些数据，直接返回客户端。

索引下推总的来说就是减少查询过程中回表的次数。

索引维护

建表建索引时，不光要考虑查询效率，同时也要考虑一下这个字段适不适合作为主键。

如果一张表有一个字段是身份证号，长度18位varchar类型，可以保证唯一性。但是其他的所有索引树的字节点上都要存一份这个身份证号。对于空间无疑是一种浪费。

数据页分裂/合并

比如id的索引的数据页存了 1,2,3,4,6 这5条数据，7,8,9,10,11在下一页。

那么这时如果插入一个12，就会申请一个新的数据页，对原有的索引数据页不会操作。

如果插入的是5，那么就涉及到数据页分裂，需要挪动后边的数据页，这无疑是一种影响效率的操作。

所以一般建主键索引时都会加上自增 AUTO_INCREMENT ，减少页分裂的概率。

页合并，相反于页分裂，就是删除操作时，把某几个数据页的数据合并到一个页上。