MySQL基础-索引原理

索引

每一张表首先是一棵B+树，树结点的key值就是某一行的主键，value是该行的其他数据。
然后有多少个索引就有多少棵树。
新建索引就是新增一个B+树，查询不走索引就是遍历主B+树。

索引的常见模型

哈希：只能做等值查询，无法做区间查询
有序数组：可以做取件查询，适合存静态数据（不在中间增删）
平衡二叉树：树高太高，每次访问一个节点都相当于第一次硬盘
N叉树：InnoDB 为例，这个 N 差不多是 1200。这棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17 亿了。考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。

主键索引与非主键索引

在 InnoDB 里，主键索引也被称为聚簇索引（clustered index），非主键索引也被称为二级索引（secondary index）。

主键索引的叶子节点存的是整行数据。非主键索引的叶子节点内容是主键的值。
所以如果使用非主键索引进行查询，查出来的只是主键的值，还会再对主键索引进行一次查询，我们要尽量直接使用主键索引查询。

索引维护

页分裂、页合并

B+树为了维护索引的有序性，插入/删除新值时会对树进行必要的维护。

页分裂：一个数据页满了，按照B+Tree算法，新增加一个数据页，叫做页分裂，会导致性能下降，空间利用率降低大概50%。
页合并：当相邻的两个数据页利用率很低的时候会做数据页合并，合并的过程是分裂过程的逆过程。

自增主键的使用场景

1）主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小
2）业务字段做主键场景：1：只有一个索引 2：该索引必须是唯一索引 这是典型的kv场景
由于没有其他索引，故不用考虑其它索引叶子节点大小的问题，故将该值设为主键索引

覆盖索引

mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

对于如上表结构，
执行语句select * from T where k between 3 and 5，
会先在k索引树上查询到合适数据的主键ID，再到主键索引查询所有数据（回到主键索引树搜索的过程，我们称为回表）

执行语句select ID from T where k between 3 and 5，
因为只需要ID的值，查询完k索引树时就得到了结果，故不需要回表可以直接提供结果。
也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

举例：
我们知道，身份证号是市民的唯一标识。也就是说，如果有根据身份证号查询市民信息的需求，我们只要在身份证号字段上建立索引就够了。
而再建立一个（身份证号、姓名）的联合索引，是不是浪费空间？
如果现在有一个高频请求，要根据市民的身份证号查询他的姓名，这个联合索引就有意义了。
它可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间。

最左前缀原则

假设现在有一个（name，age）的联合索引。

由于索引项是按照索引定义里面出现的字段顺序排序的，可以看到所有姓张的人都在一起排着。
如果你要查的是所有名字第一个字是“张”的人，你的 SQL 语句的条件是where name like ‘张 %’。这时，你也能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍历，直到不满足条件为止。

可以看到，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

那么我们该如何安排联合索引的先后顺序呢？

• 第一原则：如果通过调整顺序，可以少维护一个索引，该顺序往往就是需要优先考虑采用
• 第二原则：(a,b)、(b) 还是 (b,a)、(a)？考虑空间，字段长的（如name）只建立一次，短的（如age）建立两次。

索引下推

索引下推：Index Condition Pushdown，简称 ICP。
是Mysql 5.6版本引入的技术优化。
旨在 在“仅能利用最左前缀索的场景”下（而不是能利用全部联合索引），对不在最左前缀索引中的其他联合索引字段加以利用——在遍历索引时，就用这些其他字段进行过滤(where条件里的匹配)。从而减少回表次数，提高性能。
索引下推技术的实现——在遍历索引的那一步，由只传入可以利用到的字段值，改成了多传入下推字段值。

以（name, age）为例，当我们有一个需求是检索出表中“名字第一个字是张，而且年龄是 10 岁的所有男孩”
mysql> select * from tuser where name like '张%' and age=10 and ismale=1;

如图，每一个绿色箭头代表一次回表。
虽然会用到联合索引快速匹配到“张”姓，
但MySQL5.6之前，仍需要对每条记录进行回表，判断年龄。
而MySQL5.6之后，由索引下推，在查询的where阶段就对索引中包含的字段（age）进行判断直接筛掉不合条件的记录，减少了回表次数。