MySQL--索引（详解）

1.                                                    欢迎大家来到权权的博客~
2.                                欢迎大家对我的博客进行指导，有什么不对的地方，我会及时改进哦~

复制代码

博客主页链接点这里–>：权权的博客主页链接

  
一、本节目标

   了解什么是索引
了清除索引使用的数据布局
把握B+树在索引中的应用
把握索引分类和使用
  二、简介

2.1索引是什么？

概念：MySQL的索引是⼀种数据布局，它可以资助数据库高效地查询、更新数据表中的数据。索引通过⼀定的规则分列数据表中的纪录，使得对表的查询可以通过对索引的搜索来加速速率。

---

MySQL 索引类似于册本的目录，通过指向数据行的位置，可以快速定位和访问表中的数据，比如汉语字典的目录（索引）页，我们可以按笔画、偏旁部首、拼音等排序的目录（索引）快速查找到需要的字。
• 笔画索引

• 偏旁部⾸索引

• 拼⾳索引

---

2.2为什么要使用索引？

   使用索引的目的只有⼀个，就是提拔数据检索的服从，在应用步伐的运行过程中，查
询操作的频率远远⾼于增编削的频率。
  三、索引应该选择哪种数据布局？

3.1 HASH

众所周知，hash是最重要的数据布局木有之一，时间复杂度到达O（1）,但是，索引却不选择它，因为hash不支持范围查找。
3.2 二叉搜索树

⼆叉搜索树的中序遍历是⼀个有序数组，但有几个题目导致它不得当用作索引的数据布局。
二叉树的中序遍历是一个有序序列–>它支持范围查找，但是时间复杂度可能会退化成一个单边树O（N），节点个数过多时，无法保证树高。

---

   由于数据库中的数据是在磁盘上保存的，每一次访问子节点都会发生一次磁盘IO，磁盘IO是制约数据库性能的主要因素。
磁盘IO：计算机系统与硬盘之间的数据输入和输出操作
  3.3 B树

使用B树可以有用解决树高题目时间复杂度：0（logN）.
类似数据量的情况下，N叉树的树高可以得到有用的控制，也就意味着在类似数据量的情况
下可以淘汰IO的次数，从而提拔服从。但是MySQL以为B树做为索引的数据布局不敷好。

---

3.4 B+树

3.4.1 简介

   B+树是⼀种经常用于数据库和⽂件系统等场所的均衡查找树，MySQL索引采用的数据布局，以3阶
B+树为例，如下图所示：
  

---

时间复杂度：O（logN）

---

3.4.2 B+树的特点

   •可以或许保持数据稳定有序，插⼊与修改有较稳定的时间复杂度
• ⾮叶⼦节点仅具有索引作⽤，不存储数据，所有叶⼦节点保真实数据
• 所有叶⼦节点构成⼀个有序链表，可以按照key排序的次序依次遍历全部数据
  

---

3.4.3B+树和B树的对比

   •叶子节点中的数据是连续的，且相互链接，便于区间查找和搜索。
• 非叶子节点的值都包罗在叶子节点中
• 对于B+树而言，在类似树高的情况下，查找任⼀元素的时间复杂度都⼀样，性能均衡。
   索引一样平常使用B+树

---

口试题：索引使用了什么数据布局?

---

四、MySQL的页

4.1 为什么使用页？

   在 .ibd 文件中最重要的布局体就是Page(⻚)，页是内存与磁盘交互的最小单位，默认大小为16KB，每次内存与磁盘的交互⾄少读取一页，以是在磁盘中每个页内部的地址都是连续的，之以是这样做，是因为在使用数据的过程中，根据局部性原理，未来要使用的数据大概率与当前访问的数据在空间上是临近的，以是⼀次从磁盘中读取⼀页的数据放⼊内存中，当下次查询的数据还在这个页中时就可以从内存中直接读取，从而淘汰磁盘I/O进步性能。
  

局部性原理：
是指步伐在执⾏时呈现出局部性规律，在⼀段时间内，整个步伐的执⾏仅限于步伐中的某部门分。相应地，执行所访问的存储空间也范围于某个内存地区，局部性通常有两种形式：时间局部性和空间局部性。
时间局部性（Temporal Locality）：假如⼀个信息项正在被访问，那么在近期它很可能还会被再次访问。
空间局部性（Spatial Locality）：未来要用到的信息⼤概率与正在使用的信息在空间地址上是临近的。

---

---

• 每⼀个页中即使没有数据也会使⽤ 16KB 的存储空间，同时与索引的B+树中的节点对应。
检察页大小：show variables like 'innodb_page_size;'

---

在MySQL中有多种差别类型的页，存储数据的页叫数据页，存储索引的页叫索引页，但不论哪种类型的页都会包罗页头(File Header)和页尾(File Trailer)，页的主体信息使用数据"行"举行填充，数据页的根本布局如下图所示：

   数据页：
主要用途：用于存储表中的实际数据行。比如在一个存储员工信息的表中，员工的编号、姓名、年岁等详细数据都会存储在数据页中。
布局组成：包罗文件头、页头、最小纪录与最大纪录、用户纪录、空闲空间、页目录和文件尾等部门。这些部门协同工作，以有用地构造和管理数据存储，支持数据的快速查找、插入和删除操作。
索引页：
主要用途：存储索引数据布局，如 B + 树索引。索引页中的每个索引条目都指向一个数据页，从而实现快速的数据查找。例如，在一个根据员工姓名创建索引的表中，索引页中存储着按照姓名排序的索引信息以及对应的指向数据页的指针，通过索引页可以快速定位到包罗目标姓名的员工数据所在的数据页 。
布局组成：同样包罗页头、索引节点和指向下一层节点或数据页的指针等。索引页中的索引节点按照特定的次序构造，通常是根据索引列的值举行排序，以便快速查找和比较。
  

---

4.2 页文件头和页文件尾

页文件头和叶文件尾包罗的内容如图所示：

这里我们只关注，上一页页号和下一页页号，通过这两个属性可以把页与页之间链接起来，形成⼀个
双向链表。

---

4.3 页主体

   页主体部门是保存真实数据的主要地区，每当创建⼀个新页，都会自动分配两个行，⼀个是页内最小行 Infimun ，另⼀个是页内最大行 Supremun ，这两个行并不存储任何真实信息，而是做为数据链表的头和尾，第⼀个数据行有⼀个纪录下一行的地址偏移量的地区 next_record 将页内所有数据行组成了⼀个单向链表，此时新页的布局如下所示：
  

---

   当向⼀个新页插⼊数据时，将 Infimun 连接第⼀个数据行，最后一行真实数据行连接Supremun ，这样数据行就构建成了⼀个单向链表，更多的行数据插⼊后，会按照主键从小到大的次序举行连接，如下图所示：
  

---

4.4页目录

   当按主键或索引查找某条数据时，最直接简朴的方法就是重新行 infimun 开始，沿着链表次序逐个比对查找，但⼀个页有16KB，通常会存在数百行数据，每次都要遍历数百行，无法满足高效查询，为了进步查询服从，InnoDB采用⼆分查找来解决查询服从题目；
• 详细实现⽅式是，在每⼀个页中加⼊⼀个叫做页目录 Page Directory 的布局，将页内包括头行、尾行在内的所有行举行分组，约定头行单独为⼀组，其他每个组最多8条数据，同时把每个组最后一行在页中的地址，按主键从小到大的次序纪录在页目录中在，页目录中的每⼀个位置称为⼀个槽，每个槽都对应了⼀个分组，⼀旦分组中的数据⾏超太过组的上限8个时，就会分裂出⼀个新的分组；
• 后续在查询某行时，就可以通过⼆分查找，先找到对应的槽，然后在槽内最多8个数据行中举行遍历即可，从而⼤幅提⾼了查询服从，这时⼀个页的焦点布局就完成了；
  

• 例如要查找主键为6的行，先比对槽中纪录的主键值，定位到最后⼀个槽2，再从最后⼀个槽中的第⼀条纪录遍历，第⼆条纪录就是我们要查询的目标行。
4.5 数据页头

   数据页头纪录了当前页保存的数据的相关信息
  

---

五、B+树在MySQL索引当中的应用

   非叶子节点保存索引数据，叶子节点保存真实的数据，如下图所示：
  

以查找id为5的纪录，完备的检索过程如下：

• 起首判断B+树的根节点中的索引纪录，此时 5 < 7 ，应访问左孩子节点，找到索引页2。
  
• 在索引页2中判断id的大小，找到与5相等的纪录，命中，加载对应的数据页3。
  以上的IO过程，加载索引页1 --> 加载索引页2 --> 加载数据页3.
  

---

提出题目： 计算三层树高的B+树可以存储多少条数据？
    答：假设⼀条用户数据大小为1KB，在忽略数据页中数据页自身属性空间占用的情况下，一页可以存16条数据。
索引页当中存的是主键值和子节点的引用，也就是下一节点的偏移量（地址），假设主键为 bigint：8 byte，下一页地址为4byte,也就是一条索引纪录占8+4=12byte,一个索引页可以存161024/12=1365，也就说理论上应该三层树高的B+树可以存：13651365*16=29811600条纪录，在当前场景下，表中有29811600条的情况下，通过三次IO就可以完成数据的查询。
  

---

六、索引的分类

6.1 主键索引

   当在一个表中定义一个主键 PRIMARY KEY时，会自动创建索引，索引的值是主键列的值，使用innodb作为聚集索引（聚簇索引）。保举为每一个表定义一个主键，假如木有逻辑上唯一且非空的列大概列集可以使用主键，则添加一个自增列（auto_increment）.
  6.2 平凡索引

   最根本的索引，木有唯一性的限制。可能为多列创造的组合索引，称为符合索引大概组全索引。
  6.3 唯一索引

   当在一个表当中定义唯一键Unique时,自动创建唯一索引，与平凡索引类似，区别在于唯一索引的列不允许存在有重复的值
  6.4 全文索引

   基于文本列（char,varchar大概text列）上创建,以加速对这些列中包罗的数据查询和DML操作，仅MyISAM和InnoDB引擎支持。
  6.5 非聚集索引

   • 聚集索引以外的索引称为非聚集索引或⼆级索引。
• ⼆级索引中的每条纪录都包罗该行的主键列，以及⼆级索引指定的列。
• InnoDB使用这个主键值来搜索聚集索引中的完备纪录，这个过程称为回表查询。
  例如：select * from 员工表 where 姓名='张三'；
6.6 索引覆盖

   当⼀个select语句使用了平凡索引且查询列表中的列刚好是创建平凡索引时的所有或部门列，这时就可以直接返回数据，而不消回表查询，这样的现像叫做索引覆盖。
  例如：select 部门 from 员工表 where 姓名='张三';
七、使用索引

7.1 自动创建索引

   当我们为⼀张表加主键约束(Primary key)，外键约束(Foreign Key)，唯⼀约束(Unique)时，MySQL会为对应的的列自动创建⼀个索引.
• 假如表不指定任何约束时，MySQL会自动为每⼀列生成⼀个索引并用 ROW_ID 举行标识.
  7.2 手动创建索引

7.2.1 创建主键索引

1. -- 创建表的时候指定主键
2. create table t_pk1 (
3.   id bigint PRIMARY KEY auto_increment,
4.   name varchar(20)
5. );
6. desc t_pk1;
8. -- 创建表时单独指定主键列
9. create table t_pk2 (
10.   id bigint auto_increment,
11.   name VARCHAR(20),
12.   PRIMARY KEY (id)
13. );
15. show index from t_pk2; --- 查看索引
17. -- 修改表中的列为主键索引
18. create table t_pk3 (
19.   id bigint,
20.   name varchar(20)
21. );
22. show index from t_pk3;
24. -- 修改表中的id列为主键索引
25. ALTER TABLE t_pk3 ADD PRIMARY key (id);
26. ALTER TABLE t_pk3 MODIFY id BIGINT auto_increment;

复制代码

---

7.2.2 创建唯一索引

1. # ⽅式⼀，创建表时创建唯⼀键
2. create table t_test_uk (
3. id bigint primary key auto_increment,
4. name varchar(20) unique
5. );
6. # ⽅式⼆，创建表时单独指定唯⼀列
7. create table t_test_uk1 (
8. id bigint primary key auto_increment,
9. name varchar(20),
10. unique (name)
11. );
12. # ⽅式三，修改表中的列为唯⼀索引
13. create table t_test_uk2 (
14. id bigint primary key auto_increment,
15. name varchar(20)
16. );
17. alter table t_test_uk2 add unique(name);

复制代码

---

7.2.3 创建复合索引

1. -- 方式一，创建表时候指定索引
2. create table if not exists animals(
3. id BIGINT primary key auto_increment,
4. name varchar(25),
5. index(id,name)
6. )ENGINE=innodb;
7. -- 方式二，修改表中的列为复合索引
8. create table if not exists animals1(
9. id BIGINT primary key auto_increment,
10. name varchar(25)
11. )ENGINE=innodb;
12. alter table animals1 add index(id,name);
13. -- 方式三，单独创建索引并且指定索引名字
14. create table if not exists animals2(
15. id BIGINT primary key auto_increment,
16. name varchar(25)
17. )ENGINE=innodb;
18. create index index_animals2 on animals2(id,name);
19. show index from animals2;

复制代码

---

7.2.4 创建平凡索引

1. -- 创建表的时候创建普通索引
2. CREATE TABLE t_index1 (
3.   id bigint PRIMARY KEY auto_increment,
4.   name varchar(20) UNIQUE,
5.   sno varchar(20),
6.   index (sno)
7. );
9. -- 修改表中的列为普通索引
10. CREATE TABLE t_index2 (
11.   id bigint PRIMARY KEY auto_increment,
12.   name varchar(20) UNIQUE,
13.   sno varchar(20)
14. );
16. alter table t_index2 add index (sno);
18. -- 单独创建索引并指定索引名
19. CREATE TABLE t_index3 (
20.   id bigint PRIMARY KEY auto_increment,
21.   name varchar(20),
22.   sno varchar(20)
23. );
25. -- 为name 列建立索引，不指定索引名时失败，必须要指定名字
26. create index on t_index3(name);
28. -- 索引名推荐使用 idx_表名_列名[_列名]...
29. create index idx_t_index3_sno on t_index3(sno);
31. alter TABLE t_index3 drop index idx_t_index3_sno;

复制代码

---

7.3 检察索引

1. -- 方法一
2. select index from 表名;
3. -- 方法二
4. select keys from 表名;
5. select keys from 表名\G;
6. # ⽅式三，简要信息：desc 表名;
7. desc 表名

复制代码

---

7.4 删除索引

1. -- 删除主键索引
2. alter table 表名 drop PRIMARY KEY;
3. -- 如果要删除的主键索引是自增列，那么要先把自增列改成非自增，这里假设id被设置成了自增列
4. ALTER TABLE 表名 MODIFY id bigint;
5. # 然后再删除主键索引
6. alter table 表名 drop primary key;
7. -- 删除其他索引
8. alter table 表名 drop index 被设置了的索引列;

复制代码

  通过上面的学习，会产生一个疑问，那我们怎么知道它有没有走索引呢？
  7.5 explain 关键字

   作用：可以去检察本身写的SQL走没有走索引，可以检察执行计划,explain.
  

---

---

我们先给动物表创建一个索引。

然后我们检察这个动物表的执行计划：

我们可以看到它返回的是一个执行计划，并不是一个结果集。

---

   1.不加条件查询所有，这个结果就是explain后面的执行计划。
  

---

欧耶！！！我学会了！！

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。