数据库索引

数据库索引

1、索引的模型介绍

1.索引的作用：提高数据查询效率，类似于书的目录
2.常见索引模型：哈希表、有序数组、搜索树
3.哈希表：键 - 值(key - value)。
4.哈希思路：把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置
5.哈希冲突的处理办法：链表
6.哈希表适用场景：只有等值查询的场景

• 比如User2 和 User4 根据身份证号算出来的值都是 N，但没关系，后面还跟了一个链表。假设，这时候你要查 ID_card_n2 对应的名字是什么，处理步骤就是：首先，将 ID_card_n2 通过哈希函数算出 N；然后，按顺序遍历，找到 User2。

7.有序数组：按顺序存储。查询用二分查找就可以快速查询，时间复杂度是：O(log(N))
8.有序数组查询效率高，但是对于新增和删除，都要移动元素，故更新效率低
9.有序数组的适用场景：静态存储引擎。比如你要保存的是 2017 年某个城市的所有人口信息，这类不会再修改的数据。

• 按照身份证号大小顺序存储

10.二叉搜索树：每个节点的左儿子小于父节点，父节点又小于右儿子
11.二叉搜索树：查询时间复杂度O(log(N))，更新时间复杂度O(log(N))
12.数据库存储大多不适用二叉树，因为树高过高，会适用N叉树
13.InnoDB中的索引模型：B+Tree，为什么数据库存储使用b+树 而不是二叉树，因为二叉树树高过高，每次查询都需要访问过多节点，即访问数据块过多，而从磁盘随机读取数据块过于耗时。如果按照这个说法，B树也很符合，但是B树的非叶子节点也会存储数据，
14.索引类型：主键索引、非主键索引
主键索引的叶子节点存的是整行的数据(聚簇索引)，非主键索引的叶子节点内容是主键的值(二级索引)

mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;

表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)，两棵树的示例示意图如下。

15.主键索引和普通索引的区别：主键索引只要搜索ID这个B+Tree即可拿到数据。普通索引先搜索索引拿到主键值，再到主键索引树搜索一次(回表)
16.一个数据页满了，按照B+Tree算法，新增加一个数据页，叫做页分裂，会导致性能下降。空间利用率降低大概50%。当相邻的两个数据页利用率很低的时候会做数据页合并，合并的过程是分裂过程的逆过程。
17.从性能和存储空间方面考量，自增主键往往是更合理的选择。

• 主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

---

2、回表

• 回到主键索引树搜索的过程，称为回表

• 在k索引树上找到k=3的记录，取得ID=300
• 在ID主键索引树上找到ID=300的记录R3
• 在k索引树上找到k=5的记录，取得ID=500
• 在ID主键索引树上找到ID=500的记录R4
• 在k索引树上找到k=6的记录，不符合条件，结束

整个过程读取了k索引树三次，但是只回表了两次，所以认为引擎拿到了两条记录，故mysql认为扫描行数是2

在上面这个例子中，由于查询结果所需要的数据只在主键索引上有，所以不得不回表。那么，有没有可能经过索引优化，避免回表过程呢？？就会用到覆盖索引

3、覆盖索引

• 某索引已经覆盖了查询需求，称为覆盖索引，例如：select ID from T where k between 3 and 5
  如果ID的值就在k索引树的节点里面，那么就可以直接返回查询结果，不用再次回表，换句话说，就是索引k已经覆盖了我们的需求，所以成为覆盖索引。由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

4、联合索引

• 如果有一个居民信息表上，将居民身份证和名字建立联合索引有没有必要？其实完全看业务需求，如果我们现在有一个需求，就是根据身份证号查询居民的全部信息，就没有必要建立联合索引，如果我们要根据身份证号查询居民的名字，这个联合索引就十分有意义了，它可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间。
• 如果再来一个根据身份证号查询地址的需求，那岂不是要在建立一个联合索引吗，不用！因为B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。

5、最左前缀原则

• B+Tree这种索引结构，可以利用索引的"最左前缀"来定位记录只要满足最左前缀，就可以利用索引来加速检索。最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符，第一原则是：如果通过调整顺序，可以少维护一个索引，那么这个顺序（索引的复用能力）往往就是需要优先考虑采用的。例如上面的例子，已经有了身份证号与名字的联合索引，我们只要利用这个索引的最左前缀原则就可以找了这个身份证号的主键，进而回表查询地址

• 最左前缀原则可以匹配到左边的属性，那些没有匹配到的怎么呢？比如（name，age）的联合索引，有一个sql语句

  
  mysql> select * from tuser where name like '张%' and age=10 and ismale=1;
  

  

  利用最左前缀原则，可以匹配到ID3的记录，然后呢？在mysql5.6之前，是需要根据ID3回表查数据的，但是5.6后，就有了索引下推的优化

6、索引下推

• 在MySQL5.6之前，只能从根据最左前缀查询到ID开始一个个回表。到主键索引上找出数据行，再对比字段值。MySQL5.6引入的索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。见下图

  

  ​ 无索引下推执行流程

  

  ​ 有索引下推执行流程

• 如果查询顺序和联合索引的顺序不一致，优化器会自动做优化，查询语句的where里面各个判断调换顺序没关系的