MySQL数据库索引介绍

一、什么是索引

索引是mysql数据库中的一种数据结构，就是一种数据的组织方式，这种数据结构又称为key

表中的一行行数据按照索引规定的结构组织成了一种树型结构，该树叫B+树

二、为何要用索引

优化查询速度

注意：只能加速索引字段

三、如何正确的看待索引

错误的认知

• 项目启动后运行了一段时间，发现项目是在杆卡上运行。
  
  [En]

  after the project was launched, it was run for a period of time, and it was found that the project was running on a pole card.*

• 想要加索引，最好提前加上，在开发之初，定位到常用的查询，为该字段提前创建索引，
• 在上线之后想加索引，光把问题定位到索引上就需要耗费很长时间，排查成本很高
• 索引越多越好
• 索引使用与加速查询的，降低写效率
• 如果某一张表的ibd文件中创建了很多棵索引树，意味着很小一个updata语句就会导致很多棵索引数都需要发生变化，从而把硬盘io打上去

四、储备知识

1. 索引的根本原理就是把硬盘的io次数降下来
2. 为一张表中的一行行记录创建索引就是为书的一页页内容创建目录
3. 有了目录以后，我们以后的查询都应该通过目录去查询
4. 一次磁盘io带来的影响 7200转/分钟 120转/s 慢在找的过程，读的过程是快的，（统称延迟时间） 一次io的延迟时间=平均寻道时间（大概需要5ms）+平均延迟时间（4ms）—》9ms 9ms对于一个人来说是很慢的，但是对于计算机来说是很长的，比如一台500 -MIPS 的机器每秒可以执行5亿条指令，应为指令是靠电的性质，换句话说就是执行一次io的时间可以执行450万条指令，数据动则十万百万乃至千万条数据，每次9ms的时间显然是一个灾难。
5. 磁盘的预读
6. innodb存储引擎一页16k，即一次io读16k，
7. 当一次io时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓存区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。

五、创建索引的两个步骤

crate index xxx on user(id);

1. 提取索引字段的值当做key，value就是对应本行记录
2. 以key为基础比较大小，生成树型结构

要创建索引，最好使用占用空间小、重复率低的字段创建索引。

六、B+树

innodb存储引擎默认的索引结构为B+ 树，而B+树是由二叉树、平衡二叉树、B树演变而来的。

二叉树——》平衡二叉树——》B 树 ——》B + 树

基本概念：

leaf node：叶子节点

non-leaf node：根节点、树枝节点

二叉树:

二叉树有一个特点，它的左节点的 key 值小于当前节点的 key 值，而它的右节点 key 值大于当前节点 key 值。

创建二叉树索引

取每一条记录的id值作为key值，value为本行完整记录，构建了树型结构后，查找的速度根树的高度有关系。

平衡二叉树：

平衡二叉树又称为AVL树，指的就是左子树的高度与右子树的高度相差不超过1，如下图所示；

与平衡二叉树相比，平衡二叉树的搜索效率更稳定，整体搜索速度更快，但不可能建立基于平衡二叉树的索引。因为每个磁盘块只有一个节点，每个节点只有一组键值对，当数据太大时，二叉树会有很多节点，树的高度会变高，搜索效率会变低！

B 树：

就是构建一个单节点可以存储多个键值对的平衡树，就是B树。

B树相对于平衡二叉树，每个节点存储了更多的键值和数据，每个节点有更多的子节点，子节点的个数称为阶，上图就是一个3阶B树，高度也会很低，这样B树的查找磁盘次数也会很少，这样数据的查找效率就会比平衡二叉树高。

B+ 树：

B+ 树是对B树的进一步优化。

B+ 树和B 树有什么不同

1. B+ 树非叶子节点non-leaf node 上是不存储数据的，仅存储键，而B 树的非叶子节点中不仅存储键，也会存储数据。B + 树之所以这么做的意义在于；树一个节点就是一个页，而数据库中页的大小是固定的，innodb 存储引擎默认一页为16kb，所以在页大小固定的前提下，能往一个页中放入更多的节点，相应的树的阶数就会更大，那么树的高度必然更矮更胖，如此一来我们查找数据进行磁盘io次数又会再次减少，数据查询的效率也会更快。
2. B+ 树的阶数是等于键的数量的，列如我们的B+ 树中每个节点可以存储3个键，3层B+ 树可以存储 3*3*3=9个数据。所以如果我们的B+ 树一个节点可以存储1000个键值，那么3层B+ 树可以存储 1000*1000*1000=10亿个数据。而一般节点是常驻内存的，所以一般我们查找出10亿数据，只需要2次磁盘IO。
3. 因为B+ 树索引的所有数据均存储在叶子节点leaf node ，而且数据是按照顺序排列的。那么B+ 树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。而B 树因为数据分散在各个节点，要实现这一点是很不容易的。 而且B+ 树中各个页之间也是通过双向链表连接的，叶子节点找那个的数据是通过单向链表连接的。其实在B 树中我们也可以多各个节点加上链表。其实这些不是它们之间的区别，是因为在mysql的innodb存储引擎中，索引及时这样存储的。也就是说B+ 树索引就是innodb中 B+ 树索引真正的实现方式，准确的说应该是聚集索引。 在innodb中，我们通过数据页之间通过双向链表连接以及叶子节点中数据之间通过单向链表连接的方式可以找到表中所有的数据。

七、B+ 树分类

聚集索引、聚集索引、主键索引：

以主键字段值为key构建的B+ 树，改B+ 树的叶子节点放的是主键值与本行完整的记录。

也就是说，表中的数据聚集在叶节点中，因此称为聚集索引。

非聚集索引、非聚集索引、辅助索引、辅助索引：

以非主键字段值为key构成的B+ 树，改B+ 树的叶子节点放的是key与其主键对应的字段值。

补充：一张innodb 存储引擎中有且只能有一张聚集索引，但是可以有多个非聚集索引（辅助索引）

聚集索引负责聚合整个表的所有数据，而辅助索引则专门加速以供使用。

8.覆盖索引和表返回的操作

覆盖索引：在命中索引的基础上，我们想要的数据只在该索引树的叶子节点中找到。

返回表操作：在命中二级索引的基础上，二级索引的叶子节点没有找到想要的数据，需要获取对应的主键字段值对索引进行聚类才能找到。

举例：假设我有一张表，我们这张表是以ID字段为基础创建的主键索引，我是以name字段创建的辅助索引

主键索引——》id字段

辅助索引——》name字段

select name, age, gender from user where name='yang';

这个命中了辅助索引，那么就顺着索引树从根节点一路找下去，找到叶子节点后，叶子节点放的是 'yang' 这个人名以及它的主键字段值

但是我要的是name age gender ，我要的不是id值和主键值，如果我要的是主键值和id值那么我就不需要去其他地方找了——》这就叫覆盖了索引。

因为在您自己的索引树中，您可以找到您想要的数据，所以您不必在其他地方找到它。<details><summary>*<font color='gray'>[En]</font>*</summary>*<font color='gray'>Because in your own index tree, you can find the data you want, so you don't have to find it anywhere else.</font>*</details>
但是这并不是我想要的，那么我要拿着我 'yang'的主键字段值假如是3，就回过头继续去主键索引树根节点继续找到我想要的叶子节点的数据，那里面所有数据都有——》这就叫回表操作

提问：

1、命中了辅助索引的前提下能不能覆盖了索引？

可能是！向这种情况下就是 select name, id from user where name='yang'; '

这就不是 select name, age, gender from user where name='yang';

2、如果命中了主键索引是否覆盖了索引？

一定是！· select name, age, gender from user where id=3;

所以一个SQL语句查询的字段尽量不要想写什么就写什么甚至是 * ，要尽量覆盖了索引。

九、索引管理

MySQL常用的索引分类

聚集索引：即主键索引，primary key
    用途：
    1.加速查找
    2.约束（不为空，不能重复）

辅助索引：
唯一索引：unique
    用途：
    1.加速查找
    2.约束（不能重复）

普通索引：index
    用途：
    1.加速查找

创建聚集索引

alter table 表名 add prmary key 表名(字段名);

alter table 表名 drop primary key;——>删除

创建唯一索引

alter table 表名 add unique key 表名(字段名);  没有写索引名 show create table 表名; 查看

alter table 表名 drop index 索引名;——>删除

创建普通索引

创建表时
create table 表名(
    id int primary key auto_increment,
    class_name varchar(10) unique,
    name varchar(16),
    age int
);

创建表后
create index 索引名 on 表名(字段名);

drop index 索引名 on 表名;——>删除

X.联邦索引的最左前缀匹配原则

create index zz on t1(id, name，age)

例如：有这样的数据
id   name  age   gender   email
1    yang1  18    male    yy@qq.com
2    yang2  28    female  yy@qq.com
3    yang3  38    male    yy@qq.com
4    yang4  48    female  yy@qq.com

我现在对它们建联合索引，那么我每条记录提取的key就是 id，name，value对应的是该条记录，列如：
1，yang1，18 ————>对应  1    yang1  18    male    yy@qq.com
2，yang2, 28 ————>对应  2    yang2  28    female  yy@qq.com

因此，当只有一个领域时，这是一个很好的类比，当只有一个领域。这两个字段的值与大小相比如何？

1，yang1，18
2，yang2，28
请注意，这与字符串的大小相同，从左到右一个接一个。如果第一个场赢了或输了，联盟中没有必要有第二个场。

在查询条件中出现了id name，age字段那么就肯定能命中这个联合索引
但是必须带着id、例如：id name、id age、id age name
核心原则是每次从最左边的第一个位置开始比较大小，然后从最左边缩小范围。这是最左边的前缀匹配原则。

什么时候建立联合索引？

联邦索引只有一棵树，并且需要在没有联邦索引聚集索引和辅助索引的情况下构建三棵树，这浪费了空间。

如果您想一起使用这些字段进行查询，可以考虑建立一个联合索引，只有一个树。

但请注意，最左侧的字段必须包含在查询条件中。

摘要：如果查询条件涉及多个字段值，且多个字段值具有相同的性别，则每个人都会带来某个字段，此时可以使用键连接索引。

Original: https://www.cnblogs.com/XiaoYang-sir/p/15855578.html
Author: Mr-Yang`
Title: MySQL数据库索引介绍