MySQL重难点（一）索引

目录

一、引子：MySQL与磁盘间的交互基本单元：Page

1、重要问题：为什么 MySQL 每次与磁盘交互，都要以 16KB 为基本单元？为什么不用多少加载多少？

2、有关MySQL的一些共识

3、如何管理 Page

3.1  单个 Page （数据页）内部是如何管理数据的？

3.2  多个 Page数据页之间又是怎么迅速找到数据对应页的呢

二、正式介绍索引

1、B+树 和 B树

2、常见的索引种类

2.1  主键索引

2.2  唯一索引

2.3  普通索引（用的较多）

2.4  全文索引

3、非聚簇索引 VS 聚簇索引

3.1  非聚簇索引：MyISAM存储引擎下与InnoDB 存储引擎非主键索引下的索引方案

3.1.1 MyISAM存储引擎下的非聚簇索引

3.1.2  InnoDB存储引擎下的非主键索引

3.2  聚簇索引：InnoDB存储引擎下的主键索引方案

4、索引的操作

4.1  索引的创建原则

4.2  查看表中的索引信息

4.3  删除索引

5、组合索引（复合索引、联合索引）

5.1  组合索引最左匹配原则

5.2  索引覆盖

索引是一个单独存储在磁盘上的用于提高数据库查询效率的数据库结构，包含着对数据表里所有记录的引用指针。

使用索引可以快速找出在某个 / 多个列中某一特定值行，所有MySQL列类型都可以被索引，对相关列使用索引也是提高查询操作速度的最佳途径。

索引的底层有两种实现方案，B+树和哈希表，大部分MySQL存储引擎都使用B+树，比如 InnoDB 存储引擎。笔者学习 MySQL 时，也学习的是 InnoDB 存储引擎下的MySQL；故本章也就着重介绍InnoDB 存储引擎下的索引机制。

一、引子：MySQL与磁盘间的交互基本单元：Page

MySQL 作为一款应用软件，可以把它视作一个特殊的文件系统，为了提高基本的IO效率，MySQL以 16KB 为基本单位与磁盘进行数据间的交互，这个 16KB 基本单元，被称作 Page。

（真实情况其实是操作系统以 4KB 文件块 为单位进行读取磁盘中的数据，放入自己内存的文件缓冲区内，MySQL 向操作系统申请数据，每单位申请 4 块，也就是 16KB） 

1、重要问题：为什么 MySQL 每次与磁盘交互，都要以 16KB 为基本单元？为什么不用多少加载多少？

假设 MySQL 要查找一个表中 ID=5 的数据，如果用多少加载多少，就需要与磁盘交互五次才能找到，而如果以16KB为单元进行交互，查找这个数据就只需要交互一次，剩下的查找都只在内存中进行，可以大大减少 IO 次数。

造成数据库查询效率低下的主要原因往往不是 IO 交互的单次数据量大小，而是 IO 次数，毕竟是两个计算机硬件间的交互。

2、有关MySQL的一些共识

1、MySQL 中的创建（Create）、更新（Update）、读取（Load）、删除（Delete）（简称CURD）操作，一定是需要通过CPU计算来找到操作位置的。

2、在Linux系统编程专栏中，我们可以知道，CPU只和内存交互，因此磁盘中的数据文件也势必要先加载到内存当中。因此在某段时间内，一定是磁盘和内存同时拥有数据文件，内存中对数据文件进行操作后，再刷新到磁盘上。涉及到磁盘与内存之间的交互，也就是 IO 了。

3、为了更好的进行上述操作，MySQL 服务器在内存中运行的时候，会提前申请一块大的内存空间（Buffer Pool，默认128MB），用于内存和磁盘间的IO交互。因此，我们想要提高数据库查询效率，就一定要尽可能减少 IO 交互次数。

4、MySQL 中的数据文件，都是以 Page 为基本单位存储在磁盘中的；一个 Page 大小为 16KB，MySQL内部一定会存在大量的 Page，这就意味着MySQL必须对 Page 进行管理，不能视作一个个零散的内存块。那么如何管理呢？

3、如何管理 Page

3.1  单个 Page （数据页）内部是如何管理数据的？

我们先给个例子：

create table if not exists user
(
	id int primary key,		-- 添加主键（后文会说，此时数据库会自动帮我们生成主键索引）
	age int not null,
	name VARCHAR(16) not null
);

-- 我们是乱序插入的，并没有按照主键的顺序插入
insert into user(id, age, name) values(3, 18, '杨过');
insert into user values(4, 16, '小龙女');
insert into user(id, age, name) values(2, 26, '黄蓉');
insert into user(id, age, name) values(5, 36, '郭靖');
insert into user(id, age, name) values(1, 56, '欧阳锋');

为什么要排序好呢？

为了提高查询效率。因为数据页内部存放的一行行数据，实际上是一个链表结构：

而链表的特点是增删的时候快，但是查询速度比较慢。

为了改善查询速度，我们可以发现，Page 中除了数据记录，还有一个目录——这个目录就和平常书上的目录相似，记录着关键字的名称和其对应的地址（在这里，关键字是主键，记录着的就是主键的值和其对应的地址，也可以是唯一键或者其他的）

目录需要有序，所以要把数据按照关键字进行排序，这样目录就可以使用更加高效的查找算法（比如二分）来迅速定位数据。

3.2  多个 Page数据页之间又是怎么迅速找到数据对应页的呢

单个数据页内部有目录，而多个数据页之间是用链表相连，我们自然可以想到，如果多个数据页之间的查找是线性遍历，那么单个数据页内部的目录就显得杯水车薪了，因此多个数据页相连的链表上层，还有目录页，然后多个目录页之间也是链表结构，层层往上都是类似。

数据页中的存着用户数据，目录页中存着数据页的地址，每一个节点对应一个数据页，存放着这个个数据页最小数据行的关键字和这个数据页的地址。 

如果再层层往上，直至顶层，就会是这样的结构：

这个结构，是B+树结构，也就是 InnoDB 存储引擎下的MySQL索引结构，通过这个结构，就可以大幅提高数据库查询效率

二、正式介绍索引

1、B+树 和 B树

除了B+树，其实还有一种B树：

我们可以发现，B树与B+树最大的区别就是：

B树的每一层的每一个节点都是既存有数据，又存有下一层页的地址，而且叶子结点并不相连；

而B+树只有叶子结点有数据，其它节点不存数据，而且叶子节点之间是相连的。

这样我们就知道为什么索引结构要选B+树了： 

1、B+树的非叶子节点（即索引结构中的目录页）不存储数据，就可以在 16KB 的大小内多存储一些数据页，可以使得树更加“矮胖”，页数更少，减少 IO 次数。

2、叶子结点之间互相连接，亦可以便于范围查找。

2、常见的索引种类

通过前面的介绍我们可以知道，索引实际上是根据我们所取关键字的种类，来构建不同的B+树索引结构的：

2.1  主键索引

以主键为关键字建立的索引，就是主键索引。创建主键索引有三种方式：

-- 1、在创建表的时候，如果指明了主键，就会自动创建主键索引
create table test (id int primary key, age int not null);

-- 2、在创建表的最后，指明某列为主键，自动创建主键索引
create table test (id int, age int not null, primary key(id));

-- 3、在创建表之后，后添加主键
create table test (id int, age int not null);
alter table test add primary key(id);

可以发现，只要表有了主键，MySQL就会自动帮我们创建主键索引，创建主键索引的三种方式其实也就是添加主键的三种方式。

主键索引的特点：

1、因为一个表中只能有一个主键，所以最多也只能有一个主键索引；当然也可以使用复合索引（即以多个列作为关键字的普通索引，需要多个列全部满足，B+树才往下走）

2、因为主键不能重复，所以主键索引效率比较高。

3、主键索引的列通常都是 int 类型。

4、创建主键索引的列，不能为空，数据也不能重复（就是主键的性质） 

2.2  唯一索引

添加唯一键的时候，MySQL也是会自动帮我们创建唯一索引：

-- 1、在创建表时，直接指明唯一键
create table test2(id int, name varchar(30) unique);

-- 2、在创建表的最后，指明一列或多列为唯一键
create table test2(id int, name varchar(30), unique(id), unique(name));

-- 3、在创建表之后，后添加唯一键
create table test2(id int, name varchar(30));
alter table test2 add unique(name);

唯一索引的特点：

1、因为唯一键可以有多个，所以一个表中也可以有多个唯一索引。

2、如果以某一列建立唯一索引，这列数据也不能重复（唯一键的性质）

3、查询效率也比较高；如果唯一键指定了不为空，则等价于主键索引。

2.3  普通索引（用的较多）

因为唯一索引和主键索引只要在指明唯一键和主键之后就会自动创建，供用户操作的空间不大，而普通索引列中数据可以重复，还可以取名字，因此普通索引在实际开发中用的比较多：

-- 1、在创建表的最后，指定某列为普通索引
create test3(id int, name varchar(30), index(name));

-- 2、在创建完表之后，再指定某列为普通索引
create table test3(id int, name varchar(30));
alter table test3 add index(name);

-- 3、直接创建普通索引，普通索引可以起名字
create table test3(id int, name varchar(30));
create index idx_name on test3(name);-- 创建一个名字叫做idx_name的普通索引，关键字是test3表中的name列

普通索引的特点：

1、普通索引可以自己取名字，一个表中也可以有多个普通索引

2、如果某一列需要创建索引，但这一列的数据有重复的，那么就应该创建普通索引。 

2.4  全文索引

当对文字段或有大量文字的字段进行检索时，会使用到全文索引。MySQL提供全文索引机制，但是有要求，要求表的存储引擎必须是MyISAM，而且默认的全文索引支持英文，不支持中文。如果对中文进行全文检索，可以使用sphinx的中文版(coreseek)。

有关全文索引，笔者用的也不多，不再多做赘述。

3、非聚簇索引 VS 聚簇索引

3.1  非聚簇索引：MyISAM存储引擎下与InnoDB 存储引擎非主键索引下的索引方案

3.1.1 MyISAM存储引擎下的非聚簇索引

MyISAM存储引擎的索引方案同样是B+树，只不过其的索引结构中，其叶子结点中只有对应数据和关键字和数据的地址，没有数据，也就是说把数据页和数据目录进行了分离，不放在同一个页中了：

像这种索引结构B+树的叶子结点里面没有数据，把具体数据和B+树中的数据页进行了分离的索引结构，叫做非聚簇索引。

在MyISAM存储引擎中，是把数据关键字、地址和具体数据进行分离。

3.1.2  InnoDB存储引擎下的非主键索引

InnoDB存储引擎下的非主键索引，采用的索引结构也是非聚簇索引结构，但是其叶子结点存有的是数据关键字和这个数据的主键键值，又称作辅助索引。

辅助索引的存在是为了节省空间，存放更多的目录项；但是其查询就需要两次，先通过主键找到主键索引，再通过主键索引找到具体的数据地址，这种查找方式叫做回表查询。 

3.2  聚簇索引：InnoDB存储引擎下的主键索引方案

与非聚簇索引相反，聚簇索引，就是B+树索引结构的叶子结点里面既存有数据的关键字、地址，又存有具体的数据，即前文所展示的，一个数据页中，既有目录，又有具体数据；查找都是在一个页里面进行。

4、索引的操作

4.1  索引的创建原则

各类索引如何创建，我们已经在索引种类小结中都介绍了，这里介绍一下在创建普通索引的时候，哪些列适合被创建普通索引，哪些列不适合：
1、频繁被作为查询条件的列，应该创建索引，提高查询效率。

2、但是如果这个列唯一性太差，即便频繁作为查询条件，也不应该被单独作为关键字设置索引，而是应该设置联合索引，提高其唯一性。（唯一性太差，关键字重合的可能性就较高，容易进入想查询的数据页并不在的数据页查询，降低效率，对吗）

3、更新过于频繁的列也不适合建立索引，因为如果列发生更新，其建立的索引结构也要发生更新，维护成本高。

4、极少出现在  where 字句中的字段（即极少作为查询条件的字段），不应该设置索引。

5、总结一下，就是唯一性较好、低频被修改、高频做为查询条件的列，适合充当索引关键字，建立索引。

4.2  查看表中的索引信息

-- 1、show index / keys from 表名; 
-- 二者查询结果是一样的
show index from test3;
show keys from test3;

-- 2、desc 表名；
-- 这种查询结果比较简略隐晦，一般很少使用
desc test3;

4.3  删除索引

-- 1、删除主键索引（必须用这种方法）（其实就是删除主键，主键删除了，主键索引随之删除）
alter table test1 drop primary key;

-- 2、其它索引类型的删除
-- 这里有两种方案，结果是一样的
-- 2.1 alter table 表名 drop index 索引名（即show index from 表名 中的 Key_name字段）
alter table test2 drop index unique;
-- 2.2 drop index 索引名 on 表名
drop index idx_name on test3;

5、组合索引（复合索引、联合索引）

索引结构中的关键字由多个列组成，这样的索引叫做组合索引，组合索引支持局部搜索，也就是说可以提供部分关键字来进行查询。

5.1  组合索引最左匹配原则

所谓组合索引最左匹配原则，就是说在局部查询的时候，在进行局部查询时，必须从组合索引的最左边的列开始，并按照索引中列的顺序进行组合。如果查询条件没有从最左边的列开始，或者跳过了中间的列，组合索引将失效。

 （eg：假设在id、name、age字段上已经成功建立了一个名为MultiIdx的组合索引。索引行中按id、name、age的顺序存放，索引可以搜索id、（id,name）、（id, name, age）字段组合。如果列不构成索引最左面的前缀，那么MySQL不能使用局部索引，如（age）或者（name,age）组合则不能使用该索引查询。）

5.2  索引覆盖

使用组合索引的时候，如果从索引中找到了某一列，不会再进行回表查询，而是直接把索引的值返回。