数据库索引

常见的索引数据结构，主键索引和非主键的索引，覆盖索引，最常见的索引原则，更改缓冲区等

一、索引

索引在MySQL中也叫键(Key)

目的：为了提高数据查询的效率，书籍的目录一样，可以快速定位位置

优势：降低 IO 成本，提高效率

势：会生成索引文件，矿石杂质空间

1.各种索引常见数据结构

哈希表、结果、搜索树

哈希表

哈希表是一种以键值（key-value）存储数据的结构，只要输入即搜索的值key，就可以找到对应的值，即时间复杂度为O(1)，但是容易发生冲突，当发生冲突时，常用法、衣服法、再散列法解决

因为值序列存储的，所以排序不是排序时间长于查询的，是很长的查询范围，查询范围时间只能扫描全表的方式，所以哈希这种结构只有等值的情况，和范围查询用于

序序

非常列在等值和范围查询场景中，在等值查询时可以使用等值查询，查询时间复杂度为O((N))；范围时间可以先用二分查询找到只要有一个数据可以查询，必须列出最好的数据结构，然后才能正确地更新所有数据。查询的情况

搜索树

1. 二叉查找树

2. 平衡二叉树

3. N叉树

   最多是让索引的数据库存储不使用。为了让查询尽可能少地读取二块数据，就可以查询到查询过程中不存在的数据。而不是我们应该使用“N叉树”，因为在上叉树的性能优势，以及引擎中替换了二叉树的访问模式，这里已经广泛应用了。 ，“叉叉”树中的“当前没有数据块的”N个DB的大小，以N个时间排列，这个大约是N个排列的N个，这棵树高的位置是034次方个亿值，这已经117了。

4. B+树

   B+树属于N叉树，MySQL中，InnoDB引擎使用了B+树索引模型，所有数据都存储在B+树中的，每个索引都在InnoDB里面一集B+树。

B+树的索引从根结点开始搜索，无需进行全表扫描，而且B+树对索引列是顺序存储的，很适合范围查找，

2.主索引键和非主键索引

现在假设 ID 的表，有作业名称和名称，且主要有一个索引

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)，两棵树的示例值示意图变成

根据节点叶子的内容，索引类型主分为主键索引和非主键索引

1. 主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为簇索引（clustered index），如果语句是 select * from T where ID=500，即主键查询方式，则搜索ID这只有B+树；

2. 非索引的叶子节点内容是主键值的点。在noDB里，非主键索引也被称为保存主键索引（二级索引），复制索引叶子结的不是指向行的物理位置的索引，替代如果索引是 select * from T where k=5，即索引方式，则需要先查询k索引，返回的普通ID值为500，搜索一次，这个ID称为回表语句。基于主非索引的查询需要多扫描一棵树索引树，因此，在应用中应该尽量使用主键查询

3.自增主键

B+为了维护索引页的正常性，在插入新值的时候需要做的维护。根据B+树的算法，当一个新的数据页插入数据后，数据移动后，数据移动，称为删除页的数据，自然会降低，会合并操作，即页切片，即页切片。

解决办法：自增主键

自主增键是自增列上定义的主键，在建表语句中是这样定义的：NOT NULL PRIMARY KEY AUTO_INCREMENT

新记录的时候可以不指定 ID 值，系统会获取当前的 ID 前面加了 1 个 ID 值，符合我们新记录的记录作为其中插入的一个事件。附加操作，不会涉及到移动到其他的情况，也不会触发设备的操作，而有逻辑的现场做主键，则有可能有这样的结果，记录数据关联成本

主键长度越小，普通占用的空间也小，索引，从性能和存储空间方面考量，自增键是更合理的选择

适用业务索引字段直接做主键的情况：只有一个索引，该索引必须是唯一的

4.索引覆盖率

语句select * from T where k between 3 and 5的执行流程：

1. 在k索引树上找到k=3的记录，取得ID=300
2. 再到索引树查到ID=300的R3（第一次回表）
3. 在k索引树一个值k=5，取得ID=500
4. 重新回到索引目录树查到ID=500的R4（第二次回表）
5. 在k索引树一个值k=6，不满足条件，循环结束。

届时，回程表可以省略两次。而通过覆盖索引回表

而select ID from T where k between 3 and 5只需要直接查询这个ID，就可以查询出查询的值，因此，在查询表中提供的值，不需要返回。已经“覆盖了”我们的查询需求，我们称之为覆盖索引

5.最左边的宗旨

创建一个三列的联合索引包含(col1, col2, col3)，索引会生效于(col1)，(col1, col2)以及(col1, col2, col3)

以下示例：

SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col1=val1;

SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

SELECT * FROM tbl_name WHERE col1=val1 AND col3=val3;

1. 第一条和第三条与第二条和第三条的查询结果相同

2. 第一条和第五条包含索引的列,，不会用索引去执行查询，因为(col2)和(col2, col3) 不是(c​​ol1, col2, col3)的第四个最左边的目录

3. 第六条查询执行(col1)的查询

对于的第一个索引字段，用时必须是固定值，通符出现在右边，select * from AAA where a like '张%'会索引；而select * from AAA where a like '%三'不会排序索引

6.各种索引类型

创建索引语句差异

alter table `test` add primary key (`id`);                                                          #创建主键索引
alter table `test` add unique (`title`);                                                                #创建唯一索引
alter table `test` add index `title`(`title`);                                                  #创建普通索引

各索引区别

主键索引：它是一种特殊的唯一索引，可选择有空

索引：与“普通唯一索引”类似，不同索引列的值必须唯一允许包含重复值，但允许包含重复值

普通索引：最基本的索引，没有任何限制

7.更改缓冲区

查询过程

在保证不会写入重复的情况下，从性能的角度还是考虑，应该用唯一的索引普通索引呢？

假设执行的语句是select Id from T where k=5

1. 第一个满足条件的记录索引(, 500) 5 之后，还需要查找下一个条件记录，直到索引索引到第一个不满足条件的记录，因为普通的列值不是要求唯一的，所以再多一次判断
2. 对于索引，因为它的唯一列的值必须唯一，所以找到索引(5, 500)后会直接停止检索

这个不同带来的性能差距会有多少呢？

更新过程

为了说明普通索引和唯一索引对更新语句性能的影响，需要先了解更改缓冲区

当需要更新一个数据时，如果直接在内存中的数据页中没有内存，中不影响数据更新的数据更新时，在这些数据更新的时候，在更新的数据页中，如果在OODB正常操作时，如果在此操作时，就不需要这样了从磁盘中读入这个数据页。在下一个查询需要访问这个数据页的时候，将数据页读入内存，然后更改缓冲区中与这个页相关的操作。通过这种方式，保证这个数据逻辑的正确的名字缓冲区，实际上它是可以改变名字的缓冲区，虽然它可以叫作缓冲区。

将更改缓冲区中的操作应用到原数据页的，得到最新结果的过程称为合并。除了访问这个数据页会触发合并外，系统后台线程会定期合并。在正常关闭（shutdown）的过程中，显然，如果将操作先在更改缓冲区更新，减少读取记录，执行语句进入执行的速度会得到明显的提升。而且，数据读取内存是需要占用缓冲池的，所以这种情况还可以提高电容，提高电容占用

使用change buffer的条件

1. 比如说，现在要插入(4,400)这个记录，现在要先判断中是否存在k=4的记录，而这已经必须证明数据如果都读入到内存页直接更新内存会已经有了，那已经需要使用更改缓冲区了
2. 索引，不需要唯一的直接索引相同的先访问内存，所以可以使用change buffer，change buffer因为减少了现在的磁盘访问，所以对更新的普通性能会很明显的提升

更改缓冲区的使用场景

普通索引的所有场景，使用更改缓冲区都可以加速作用吗？

操作前面讲到真正进行数据会触发merge，写merge之前，change buffer里面的越多，就记录更新的时候。同时change buffer更适用于读少的场景，因为读时间数据触发合并，触发会触发操作，即不提升页面性能访问

综合前面所说，唯一和普通索引在查询能力上的索引不明显，主要考虑更新时的性能，其中涉及到更改缓冲区

8.另外的改变缓冲区与重做日志

​​​​​​​​​​​​​​​​​​​​​

假设要在表上执行下面这个插入语句

insert into T(ID, k) values(id1, k1), (id2, k2);

假设k1的数据页已经在内存(InnoDB buffer pool)中，k2的数据页不在内存中

血液更新语句做了如下操作：

1. Page1已经在内存，插件(id1, k1)操作直接就在内存执行
2. Page2没有在内存，所以先在内存的change buffer区域记录“往Page2插件(id2, k2)”的信息
3. 将 1，2 步的操作记录在重做日志中

可以看到这条语句插入了两台内存，写了一次磁盘（按顺序写了一次日志），而且还是通过重做日志

假设现在要执行如下查询语句

select * from t where k in (k1, k2);

语句发生在插入语句后不久，内存中的数据都还在，此时和 ibdata1，redo log(_log_fileX) 就不用了，如果下图没有画出这部分。