【MySQL】索引的机制、使用

        在学习索引知识之前，我们可以先了解一下什么是索引。实际上，索引就是数据库中一个或多个列存储的结构，能够支持数据库管理系统在不扫描整张表的情况下也能查询到数据行，能够大大提升查询效率。举个例子，我们想要找到一本书的某一章节，一页页翻效率肯定是非常低的，而查目录后直接翻到对应页，查找速度就会大大提高，这就是索引起到的作用。

数据库与磁盘

对磁盘的基本理解

        我们知道，数据库是用于为用户提供存储数据服务的，能够支持用户有效地组织、检索和管理数据。那么为了持久化存储数据，就需要把数据存到磁盘中，而磁盘作为机械设备，效率是远低于计算机的其它电子器件的，所以磁盘IO往往是数据库操作的性能瓶颈，所以提升与磁盘交互的效率就成了MySQL要考虑的一个重要问题。

        所以让我们先对磁盘有个基本的了解：

磁盘通常由多个盘片组成，每个盘片包含两个面，两个面都有读写磁头用以访问磁盘数据。我们根据：柱面（cylinder）、磁头（header）、扇区（sector）对应的编号就能够找到所有的扇区位置，读取任何一个扇区的数据了。

上图显示的是一个盘面，盘面中一圈圈的灰色同心圆就是磁道，从圆心向外作直线就能将磁道划分为若干个弧段，每个磁道上的一个弧段称为一个扇区，扇区的大小通常是512字节，但近年来在迁移到4096字节。尽管最新的磁盘已经慢慢让扇区的大小变得不同了，但我们为了简化方便理解，还是认为每个扇区大小是512个字节。

        而我们的数据库文件本质上就是存储在磁盘的一个个扇区当中的，也就是说我们要想访问文件，就需要根据我们前面提到的CHS就能够定位需要访问的扇区了。

MySQL与磁盘的交互

        前面我们提到了，磁盘的基本单位是扇区，扇区的大小通常是512字节，那么MySQL一次也是从磁盘中读取512字节吗？其实并不是，首先文件系统从磁盘中读取数据的基本单位是4KB，也称为块，而MySQL访问磁盘必须通过文件系统来访问，所以显然不可能只有512字节。

        我们以MySQL的innodb存储引擎为例，它的IO基本单位是16KB（16 * 1024 = 16384），称为一个page：

page设置得比较大，而不是用多少加载多少的原因在于：

空间局部性原理：如果一个数据被访问，那么它周围的数据有较大的可能会在之后再次被访问

所以说如果MySQL在查询时把这个数据附近的数据一起查询了，有较大可能下次要查询的数据也在这次的查询结果中，这样一来就减少了磁盘IO次数，也就提高了查询效率。

        所以说要想通过局部性原理对查询效率进行优化，在数据库存储数据时，也需要把相关性要的数据存在一起，也就是说MySQL是以page为单位来管理数据的。

总结和基本共识

        在理解索引之前，我们先对前面的知识进行总结并建立一些基本共识：

1. MySQL中的数据文件是以page为单位，保存在磁盘中的

2. 对MySQL的CURD（增删查改）操作需要通过计算来找到插入/删除/查询/修改的位置

3. 涉及到计算操作，就意味着数据要被加载到内存当中，交给CPU来计算

4. 所以说在特定时间内，数据在磁盘和内存中都存在，如果数据在内存中被修改了，就要把内存中的数据刷新到磁盘中，这个IO的基本单位是page

5. 由于数据需要被加载到内存中，MySQL服务器分配了一块称为Buffer Pool的大内存空间来进行和磁盘的数据交互

6. 要优化数据库的效率就必须尽可能减少磁盘IO的次数

对索引的理解

索引结构的优势

        为了帮助大家更直观地理解索引对效率的提升，让我们来看一个简单的例子：

比方说我们要对这一张表进行查询，现在没有索引结构，所以就只能遍历整张表，假设一行记录的大小如下：

则在最坏情况下我们要进行的磁盘IO次数为：（8 + 40 + 8 + 72）*  800 / 512 = 200次

如果我们进行一点小优化，用一个索引表存放数据地址，则大小如下：

则此时在最坏情况下我们要进行的磁盘IO次数为：（8 + 8）* 800 / 512 = 25次

确实是减少了不少的IO次数，但如果数据量再大一些呢，比方说有80000条记录，那么最坏情况下我们需要进行2500次磁盘IO，这依然是比较慢的。

如果我们再此基础上再次建立索引：

则最坏情况下需要进行：（800 / 32 * 16 + 32 * 16）/ 512 = 1.78次

如果我们把这个索引结构倒过来，其实就非常像一棵B+树了：

前面提到的只是我们自己模拟的一个简单的索引结构，接下来让我们学习一下innodb是如何实现索引的吧。

Page与B+树结构

        前面提到了，访问数据时会把page加载到内存中，MySQL需要管理许多表文件，那么内存中就会有很多page，则数据库必须管理这些page，进行先描述再组织，然后用处理数据结构的方式就能对page进行增删查改了。

        页间目录结构：页目录是页内部的组件，将几条记录分为一组，这样在页内查找记录时，就可以先二分查找快速定位记录在哪个组中，然后在组内线性查找即可找到需要查询的记录。由于单个Page的大小是固定的，当一个Page不够用时，就需要再创建一个Page来保存新的书就，然后用指针将这些页组织起来。

        页间目录结构：如果我们只用前面的链表结构存储Page，那么要查询某一页时，依然需要线性查找，效率还是不够高，所以还需要引入页间目录结构。

        如下图所示，我们可以用值存放目录的页来作为目录页，目录项存放指向的页的最小的数据即可，这样一来我们就能快速定位数据页的位置。

但是目录页依然需要遍历，不过没关系，我们可以再添加一层上级索引：

而这，就是B+树。假设表建好了B+树索引，表中有100万条记录，当我们查询id=5000的记录时：

1. 查询开始时，先访问B+树根节点，如果根节点不在内存中，就从磁盘加载到内存

2. 自顶向下逐层查找，当查找的节点不在内存中时，从磁盘加载到内存

3. 最终找到对应的叶子节点，包含了指向数据页的指针，将数据从磁盘加载到内存即可

为什么选择B+树

        通过前面的学习我们可以发现B+树确实可以提升数据库的性能，那为什么其它数据结构不行呢？

        首先是链表，在查询时只能够线性查找效率过于低下，所以显然不行；然后是二叉树，在某些情况下可能会退化为线性结构，大大降低效率，不够稳定所以也不行；还有AVL树和红黑树，虽然能够保证树的平衡，但是一方面AVL树和红黑树在数据比较多的情况下，树的高度都会比较高，而B+树的索引结构注定了它是矮胖的树，自顶向下进行查找，层高更低也就意味着磁盘IO次数更少，效率更高；那哈希表呢和B树呢？MySQL确实支持用哈希表作为索引结构，但innodb和myisam不支持。虽然哈希表有时候可以达到O(1)的时间复杂度，但不支持范围查找，B树同样不支持范围查找，相比之下，B+树的叶子节点是以双向链表的形式存储起来的，可以非常方便地支持范围查找。

聚簇索引和非聚簇索引

        innodb和myisam都是MySQL中的存储引擎，它们的索引机制也都是通过B+树实现的，但有一点区别，实际上我们前面讲的让叶子节点直接存放数据是innodb实现索引的方式，被称为聚簇索引。而myisam索引使用的B+树的叶子节点不存放数据，只存放数据记录的地址，也就是将索引page和数据page分离，这种方式我们称为非聚簇索引。

聚簇索引：

非聚簇索引：

        口说无凭，让我们实际地来看看两者之间的差别，我们分别使用innodb和myisam存储引擎建表：

然后在/var/lib/mysql路径下的index_db中可以发现：

.frm文件：存储表的元数据（如定义、结构等），所有存储引擎都会创建该文件

.ibd文件：存储innodb表的实际数据和索引

.myd文件：每个使用myisam的表都有一个.myd文件，存储表中每一行具体内容

.myi文件：每个使用myisam的表都有一个.myi文件，存储表的索引

        我们前面提的索引都是以主键为关键字建立的B+树，其实也可以用其它列来建立索引。假设我们要给表的普通键建立索引，则对于非聚簇索引来说，它的数据页与目录页是分离的，所以再建一棵以唯一键为关键字的B+树花费并不大，即对myisam而言建立主键索引和辅助索引区别不大；而聚簇索引的叶子节点存的就是数据，所以辅助索引的叶子节点不能存数据否则就太浪费了，它的叶子节点存的是主键，通过主键访问主键索引，这样的操作叫做回表查询。

索引的操作

创建索引

主键索引的创建

        第一种方式：

-- 在创建表时，在字段后直接设置主键
create table user1(id int primary key, name varchar(20) not null);

        第二种方式：

-- 在创建表的最后，指定某一列或某几列作为主键
create table user2(id int, name varchar(20) not null, primary key(id));

        第三种方式：

-- 在创建表后，添加某一列或某几列为主键
create table user3(id int, name varchar(20) not null);
alter table user3 add primary key(id);

主键索引的特点：

1. 一个表最多有一个主键索引，效率高

2. 建立主键索引的列的值不能为null，且不能重复

3. 建立主键索引的类型一般为int

唯一索引的创建

        第一种方式：

-- 创建表时，直接设置唯一键
create table user4(id int primary key, name varchar(20) unique key);

        第二种方式：

-- 在创建表的最后设置唯一键
create table user5(id int primary key, name varchar(20) not null, unique key(name));

         第三种方式：

-- 在创建完表后，添加唯一键索引
create table user6(id int primary key, name varchar(20) not null);
alter table user6 add unique key(name);

唯一键索引的特点：

1. 一个表可以有多个唯一键索引，效率高

2. 某一列设置唯一键索引，就要保证这一列数据不重复

3. 如果唯一键索引加上not null，等价于主键索引

普通索引的创建

        第一种方式：

-- 在创建表的后面添加普通索引
create table user7(id int primary key, name varchar(20), index(name));

        第二种方式：

create table user8(id int, name varchar(20));
alter table user8 add index(name); -- 创建表后指定某列为普通索引

        第三种方式：

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

普通索引的特点：
1. 一个表中可以有多个普通索引，在实际开发中用的比较多

2. 某一列需要建立索引，但存在重复值，此时应该使用普通索引

查询索引

        方法一：

        方法二：

        方法三：

删除索引

        方法一（删除主键索引）：

alter table 表名 drop primary key;

        方法二（其他索引）：

alter table 表名 drop index 索引名； 索引名就是show keys
from 表名中的 Key_name 字段

        方法三：

mysql> drop index name on user8

创建索引原则

1.需要频繁查找的列应该设置索引

2.唯一性差的列不适合单独创建索引，即使需要频繁查找

3. 更新得频繁的字段不适合创建索引