【MySQL索引】（重点）

---

---

一、见见索引

导入800w条数据。

表的结构如下：

查询员工编号为998877的员工：

可以看到，查询该数据，花费了4.64s，这对我们客户来说是无法容忍的。

解决方案：

• 为empno创建索引

alter table EMP add index(empno)

再次查询：

查询时间变成了0.01s，这快了n倍！！！

这个就是索引。

二、认识磁盘

硬件方面学不明白，直接看课件即可。

讲一个案例：
假设MySQL要向磁盘获取16kb空间大小的内容。

具体过程如下：

MySQL想要从磁盘中获取16KB大小的文件内容，首先向操作系统申请16KB大小的空间，操作系统不是直接从自己的文件缓冲区拿的，而是将16KB大小转化成4*4KB大小，去磁盘系统中，按单位获取文件大小，再返回给MySQL。
这是实际过程。

但从逻辑上看，就是MySQL向磁盘系统获取16KB大小内容。

三、索引的学习

1.建立共识

• MySQL 中的数据文件，是以page为单位保存在磁盘当中的。
  MySQL 的 CURD 操作，都需要通过计算，找到对应的插入位置，或者找到对应要修改或者查询的数据。
• 而只要涉及计算，就需要CPU参与，而为了便于CPU参与，一定要能够先将数据移动到内存当中。所以在特定时间内，数据一定是磁盘中有，内存中也有。后续操作完内存数据之后，以特定的刷新策略，刷新到磁盘。而这时，就涉及到磁盘和内存的数据交互，也就是IO了。而此时IO的基本单位就是Page。
• 为了更好的进行上面的操作， MySQL 服务器在内存中运行的时候，在服务器内部，就申请了被称为 Buffer Pool 的的大内存空间，来进行各种缓存。其实就是很大的内存空间，来和磁盘数据进行IO交互。
• 为了更高的效率，一定要尽可能的减少系统和磁盘IO的次数

2.重谈page

先建立一个测试表：

create table if not exists user (
id int primary key,
age int not null,
name varchar(16) not null
);

向表中插入多条数据：

注意：插入的数据中，id列是随机的。

但是，当查看user表的内容时，发现id列居然是排好序的。

问题：1.谁做的？为什么要这么做？
2.谈谈page

简单理解的话，page就是一个内存块。
但是不能简单理解。

mysql内部一定需要且存在大量page，这就意味着要将多个page进行管理起来，那就是先描述，再组织。

所以，不能简单地认为page是一个内存块，它是有对应的描述字段的：

struct page
{
	struct page* prev;
	struct page* next;
	char buffer[NUM];
	
}; --- 16KB大小，也就是new page()
随后将所有的page进行类似“链表”形式的管理。

为什么MySQL和操作系统，磁盘交互，要page？

• 1.在不需要page的情况下：

假设要查找id=2的数据，这些数据是放在磁盘中保存的。
MySQL就要先将id=1的数据加载进内存，再将id=2的数据加载进内存，才能查找到id=2的数据，此时就需要两次IO。
如果要查找id=5的数据，就要5次IO了。

• 2.在有page的情况下：
  假设要查找id=2的数据，MySQL首先从磁盘中将page页加载到内存中，而要查找的数据，就在page页中放着，所以加载page到内存后就可以直接进行查找了，此时只需要将page放到内存这1次IO.
  但是如果要查找id=3，id=4，id=5这些数据，就完全不需要和磁盘进行IO了，因为page页已经被加载到内存了，直接在内存中对page进行查找即可。

这就很明显提高了效率。

问题：我怎么保证我要查找的数据就在这个page页中？
答案是无法保证的，但是有很大概率在page页中，因为局部性原理。

IO效率低下的原因往往是因为IO次数过多，而不是因为IO一次的数据量过大。

就像是我要1次把1G的数据放到磁盘，和分100次把1G的数据放到磁盘一样。
一次把1G的数据放到磁盘，只需要一次物理寻址，再将内容写入。
而分100次把1G的内容放到磁盘的话，就需要100次的物理寻址了，效率自然就低。

3.单page和多page同样存在效率低下的问题

单page的缺陷

上面提到的page，对数据的存储如下图：

由于主键的存在，MySQL会根据主键的数据，内部自己进行排序。这也回答了上面的第一个问题：这个排序的过程其实是MySQL自己干的。

那为什么要排序呢？
本质上还是为了提高查询的效率

因为在内部插入数据时，对数据的管理也是链表形式，而链表就是增删快，查询和修改慢，所以进行排序，是有必要的。
如果运气好，就能提前遍历结束，找到数据。

---

但是！！！

如果运气不好， 就需要遍历整个page表，直到查询到最后一个数据是想要的为止。
这就是单page下的效率缺陷。

多page的缺陷

知道page这个页的结构之后，那么多个页的保存，就如上图，像链表一样，一个个地连起来。

假如要从1000w条数据中查找，那就不止要导入一个page页到内存了，需要导入多个page页，这些page页之间，有指针互相记录地址，形成一个双链表的结果。

而在1000w中查询一条数据，也同样是从第一个page页中，一条条记录地往下查找，这也是一个线性的过程。

这样找，效率也同样是太慢了！！！

页目录

单page

我们在看《谭浩强C程序设计》这本书的时候，如果我们要看<指针章节>，找到该章节有两种做法：

• 从头逐页的向后翻，直到找到目标内容。
  通过书提供的目录，发现指针章节在234页(假设)，那么我们便直接翻到234页。同时，查找目录的方案，可以顺序找，不过因为目录肯定少，所以可以快速提高定位。
• 本质上，书中的目录，是多花了纸张的，但是却提高了效率。
  所以，目录，是一种“空间换时间的做法”。

多page

上面的单page，是在页内增加一个目录，来指向页内中的某些数据。

而多page情况下，在页内也同样增加目录，来对页进行管理。
只是单page目录管理的是页内的行，而多page目录管理的是页。

而页目录这种保存方法，其实是保存页page的主键大小，根据主键大小决定在B+树放的位置。

目录的指针指向一个页中的最小的数据，另一个指针指向一个特定数据，就构成了一个范围。

比如一级页中，页目录指针指向的是1，和11。
那这个一级目录就把页表分成了：1->10， 11->**的两个层级。
这样层层管理，当来一个数据是5的时候，就会去1->10这个页范围查找，从而排除了11->*这个范围。

这样就不用再进行线性遍历，从而极大提高了效率。

而上面这个结构，不就是一棵典型的B+树吗？

B+树为什么行！

详谈细节

需要注意的点是：

• 1.叶子节点中保存有数据，而路上节点没有，非叶子姐点，不需要数据，所以多出来的空间，就能存储更多的目录项，那就能管理更多的page，所以这棵树就是典型的矮胖树！！！
• 2.路上途径的每个非叶子节点，都有页目录，找到目标page的IO次数就减少，就能极大地提高查找效率！！
• 3.叶子节点的页page，一定是用链表相互连接起来的。

假如一个page大小16KB，全部用来存储页目录，一个指针大小4字节，那么一个page就能管理4096个page了！！！这样惊人的查找效率，谁不爱呢？

上面的结构，就是mysql innode db下的索引结构，一般在建表插入数据的时候，就是在该结构下进行CURD（增删查改）的！！！

如果我创建的表没有主键呢？
如果创建的表没有主键，mysql就会生成一个隐藏的主键，放进B+树结构中，一般这个生成的主键是自增长类型的主键。

对于叶子节点用链表连接起来这个特性，不是mysql这个数据库实现的页page管理结构，而是B+树本身就这样的！！是mysql因为需求，选择了B+树！！

其他数据结构为什么不行?

InnoDB 在建立索引结构来管理数据的时候，其他数据结构为何不行？

• 链表？线性遍历问题，最坏情况就是遍历整个链表。
• 二叉搜索树？退化问题，可能退化成为线性结构，最坏情况也就是链表结构了。
• AVL &&红黑树？虽然是平衡或者近似平衡，但是毕竟是二叉结构，相比较多阶B+，意味着树整体过高，大家都是自顶向下找，层高越低，意味着系统与硬盘更少的IO Page交互。虽然你很秀，但是有更秀的。也就是说，AVL树 && 红黑树，相较于B+树来说，一个是瘦高，一个是矮胖，但是越矮，意味着IO的次数越少，效率越高。
• Hash？官方的索引实现方式中， MySQL 是支持HASH的，不过 InnoDB 和 MyISAM 并不支持.Hash跟进其算法特征，决定了虽然有时候也很快(O(1))，不过在面对范围查找的时候就不行。
  这里具体的情况不是很了解，所以查了GPT：

对于B树来说，B树和B+树的区别如下：

• 一、B树的每一个节点，都会存储一定量的主键，包括路上节点。这也就意味着，有更少的空间用于记录其他的页page，就导致整体的B树结构会更高更瘦，效率问题就出现了。
• 二、B树的所有叶子节点中，没有使用链表连接起来，所以对于范围查找，就不方便，效率就低了。

而B+树，完美解决了上面的两种B树的缺陷。
还是那句话，虽然你很秀，但还有更秀的。

聚簇索引和非聚簇索引

首先创建一个新的数据库index_db，在该库中创建一个表：test1

查看 默认存放数据库的目录：/var/lib/mysql/index_db目录时，发现有几个文件。

其中，创建一张表，就有几个文件，第一个文件是test1.frm，这个就是传说中的表结构，就存储在该文件中。第二个文件是test1.ibd，后缀名全称是：index block data，也就是索引和数据放在一起了。

对于将索引和数据一起存放的模式，就是聚簇索引！

再次创建一个表：test2，默认的engine改为myisam。

再次查看该数据库下的文件，发现test2表是三个文件。
第一个.frm文件就是表结构，第二个.myd文件，就是data数据，第三个.myi文件，就是index索引，所以，使用myisam存储引擎，就是将索引和数据分开存放。

像这种将索引和数据分开存放的索引，就是非聚簇索引！！！

非聚簇索引大概长这样：
B+树的叶子节点的索引存储的是数据的地址，再通过地址找到表数据，这样就实现了将索引和数据分离存储。

回表查询

MySQL 除了默认会建立主键索引外，我们用户也有可能建立按照其他列信息建立的索引，一般这种索引可以叫做辅助（普通）索引。

对于 MyISAM ,建立辅助（普通）索引和主键索引没有差别，无非就是主键不能重复，而非主键可重复。

对于InnoDB 除了主键索引，用户也会建立辅助（普通）索引。

InnoDB的辅助索引如上图，除了主键索引，在用户建立的辅助索引中，也会构建一颗B+树，这棵B+树的叶子节点，不会存储数据，而是存储主键的key！！

所以用户的查找方式应该如下：首先遍历辅助索引，找到目标的key值，再去主键的B+树中，根据key值找到记录，这个过程就叫做回表查询！

所以建立普通索引时，直接回表查询即可，不需要再在辅助B+树中的叶子节点再存储数据。

四、索引的操作

1.创建主键索引

法1：

– 在创建表的时候，直接在字段名后指定 primary key
create table user1(id int primary key, name varchar(30));

法2：

– 在创建表的最后，指定某列或某几列为主键索引
create table user2(id int, name varchar(30), primary key(id));

法3：

create table user3(id int, name varchar(30));
– 创建表以后再添加主键
alter table user3 add primary key(id);

主键索引的特点：

一个表中，最多有一个主键索引，当然可以使符合主键 主键索引的效率高（主键不可重复） 创建主键索引的列，它的值不能为null，且不能重复
主键索引的列基本上是int

已经创建好一个含有主键索引的表后，查看表格，如下：

2.唯一索引的创建

法1：

– 在表定义时，在某列后直接指定unique唯一属性。
create table user4(id int primary key, name varchar(30) unique);

法2：

– 创建表时，在表的后面指定某列或某几列为unique
create table user5(id int primary key, name varchar(30), unique(name));

法3：

create table user6(id int primary key, name varchar(30)）；
alter table user6 add unique(name);

唯一索引的特点：

一个表中，可以有多个唯一索引
查询效率高
如果在某一列建立唯一索引，必须保证这列不能有重复数据
如果一个唯一索引上指定not null，等价于主键索引

3.普通索引的创建

法1：

create table user8(id int primary key,
name varchar(20),
email varchar(30),
index(name) --在表的定义最后，指定某列为索引
);

法2：

create table user9(id int primary key, name varchar(20), email
varchar(30));
alter table user9 add index(name); --创建完表以后指定某列为普通索引

法3：

create table user10(id int primary key, name varchar(20), email
varchar(30));
– 创建一个索引名为 idx_name 的索引
create index idx_name on user10(name);

普通索引的特点：

一个表中可以有多个普通索引，普通索引在实际开发中用的比较多
如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引

4.索引的删除

法1：

删除主键索引： alter table 表名 drop primary key;

法2：

其他索引的删除： alter table 表名 drop index 索引名； 索引名就是show keys
from 表名中的 Key_name 字段
mysql> alter table user10 drop index idx_name;

法3：

drop index 索引名 on 表名
mysql> drop index name on user8;

5.查询索引

索引创建的原则

比较频繁作为查询条件的字段应该创建索引
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
更新非常频繁的字段不适合作创建索引
不会出现在where子句中的字段不该创建索引