【MYSQL】索引操作和聚簇/非聚簇索引（3）

MySQL的索引 - 目录

索引

准备工作

意义

分类

mysql 的工作过程

磁盘

mysql与磁盘交互的基本单位

总结

例子

索引结构

聚簇引擎 和 非聚簇引擎

索引操作

---

索引

准备工作

意义

索引可以提高数据库的性能，不用加内存，不用改程序，不用调sql，只要执行正确的语句 create index，查询速度就可能提升成百上千倍，但是查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量IO，所以索引的价值在于提高一个海量数据的检索速度。

如果我们构建一个海量表，查询一个数据可能会花费很久的时间，但是如果构建索引，查询数据花费的时间就少之又少。

分类

• 主键索引 primary index

• 唯一索引 unique

• 普通索引 index

• 全文索引 fulltext

mysql 的工作过程

mysql 对数据做的CURD操作，根据冯诺依曼体系，mysql 不可能直接去访问磁盘。

实际上数据库对数据做的所有访问，全部都是在内存中进行的。

定期的将数据刷新到磁盘中，也是通过在内存中先通过接口到内核缓冲区，然后调用接口（sync，syncfs）来刷新到磁盘中的数据库文件里。

磁盘

CHS 是一种磁盘数据定位方式，C：cylinder柱面 等价于磁道，H head 磁头，S：sector 扇区，可以通过CHS 方法定位所要访问的山区。不过实际系统软件使用的并非CHS（硬件是），而是LBA，一种线型地址，可以想象成虚拟地址和物理地址，系统会将LBA 地址最后转化成CHS，交给磁盘去进行数据读取。

我们可以在硬件层面定位任何一个基本数据块（扇区），是否可以在系统软件层面按照扇区（512字节，部分扇区4096字节）进行IO交互？不是，

• 因为如果操作系统直接使用硬件提供的数据大小进行交互，那么系统IO 代码和硬件强相关，对应的如果硬件发生变化，系统必须跟着变化。（要解耦）

• 单次IO 512 字节太小。IO单位小就意味着读取同样的数据内容，需要进行多次磁盘访问，会带来效率的降低。

• 文件系统就是在磁盘的基本结构下建立的，文件系统读取的基本单位不是扇区而是数据块。

磁盘的随机访问（random access）和连续访问（sequential access）

随机访问：本次IO 所给出的扇区地址和上次IO 给出扇区地址不连续，这样的话磁头在两次IO 操作之间需要做比较大的移动动作才能重新开始读/写数据。

连续访问：如果当次IO 给出的扇区地址与上次IO 结束的扇区地址是连续的，那么磁头就可以很快的开始这次IO 操作，这样多个IO 操作就叫做连续访问。

尽管相邻的两次IO 操作在同一时刻发出，但如果他们的请求的扇区地址相差很大的话也是随机访问，而并非连续访问。磁盘是通过机械运动进行寻址的，连续访问不用过多定位（因为连续）所以效率较高。

mysql与磁盘交互的基本单位

mysql 作为一款应用软件，可以想象成一种特殊的文件系统，它有更高的IO 场景，所以，为了提高基本IO 效率，mysql 进行的IO 的基本单位是16 kb。（统一使用Innodb 存储引擎）。

16kb 数据从内存中先刷新到内核缓冲区然后再通过IO 到磁盘上。

所以磁盘这个硬件设备的基本单位是512 字节，而MySQL INNODB 引擎使用 16kb 的基本单位来与磁盘进行IO 交互。这个基本的数据单元在MYSQL 中叫做page（不同于系统的page）。

总结

• MYSQL 中的数据文件以page 为单位保存在磁盘中。

• MySQL 的CURD 操作，都需要通过计算，找到对应的插入位置，或者找到对应要修改或者查询的数据。

• 只要涉及到计算，就需要CPU的参与，为了便于CPU的参与，就要把数据先移动到内存当中。

• 但是不会把所有的表都拿到内存中，只拿需要的。

• 在特定时间内，数据一定是磁盘中有，内存中也有。后续操作完内存数据之后，以特定的刷新策略，刷新到磁盘。此时会涉及到磁盘和内存的数据交互，这就是IO，IO的基本单位就是Page。

• 为了更好的进行上面的操作，MySQL 服务器在内存中运行的时候，在服务器内部，就申请了被称为Buffer Pool 的大内存空间，来进行各种缓存，其实就是很大的内存空间，来和磁盘数据进行IO 交互。

• 为了更高的效率，一定要尽可能的减少系统和磁盘IO 交互的次数。

IO请求 --- 系统中存在着大量的IO 请求，OS必定管理大量的IO 请求。

--> 先描述，再组织。管理在struct request_io 结构体中。

为什么IO 交互是Page 的交互方式呢？

因为如果是连续的一段记录（比如长度为10），我们想要查找中间的记录（比如查找5 的位置），如果我们从前往后加载，一次一条记录的加载，就会需要5次IO 才会找到我们想要的。

但是因为这些记录都被保存在一个Page 中，那么在第一次IO 进行查找过程中，整个Page 会被加载到MySQL 的buffer pool 中，本次IO 结束后，在后续查找Page 内的记录时，完全不用进行IO，而是直接在内存中进行。所以可以大大的减少IO 次数。

如何保证用户下次查找数据，还是在此Page 中？

不能严格保证，但是有很大概率，根据局部性原理。

例子

如果我们现在建表，设定id 为主键，如果乱序插入，发现也会按照id 进行自动排序。

分析：方便快速引入页内目录。

索引结构

在mysql 中，任何一个时刻，都一定存在大量的Page 页存在于mysql 内部，mysql 本身同样要对page 进行管理。先描述在组织，就这样产生了

 struct Page{
 struct Page* next;
 struct Page* prev;
 char buffer[16kb - 其他字段占用的大小];
 };

不同的Page 在MySQL 中，都是16kb，使用prev 和next 构成双向链表。因为有主键的关系，MySQL 会默认按照主键给我们的数据进行排序，从上面的Page 内数据记录可以看出，数据是有序而且彼此关联的。

为什么数据库再插入数据时要对其进行排序呢？

插入时排序是为了优化查询时的效率。

页内部存放数据的模块，实质是链表结构，特点是增删快，修改查询慢，所以优化查询的效率是必须要做的工作。

正是因为插入时排序，查找的时候从头到尾都是有效查找，没有任何一个查找是浪费的，而且如果运气好，可以提前结束查找过程。

为什么有多个Page？

页模式的唯一功能就是在查询某条数据的时候可以直接将一整页的数据加载到内存中，以减少硬盘IO 的次数，从而提高性能。但是我们知道页模式的内部采取的是链表结构，线性查找，本质上还是通过数据的逐条比较来取出特定的数据。

如果数据量非常大，那么一定要多个page 来存储这些数据，多个page 彼此使用双链表连接，而且每个page 内部的数据也是基于链表的，那么查找特定的一条数据也是一定是线性查找，效率非常低。

方法：引入页内目录

如何提高一个页内进行搜索的效率?

书的目录是多花纸张，提高效率。所以目录就是空间换时间的方法。

每一个page 就是一本书，page 里面的每条记录就是书的一页。通过引入页内目录，遍历目录来提高遍历查找数据的速度。

所以设定主键的乱序插入后的自动排序实际上是为了可以快速的引入页内目录。

如何提高页间查找效率？

需要注意的是，如果要保证整体有序，新插入的数据就不一定在新page 上面，这里仅仅做演示。

新建立一个page，保存每个数据页的首个数据的主键（不会重复）和对应指针，用来作为页间目录。

每个目录项的构成就是：键值和指针。

一个page 可以管理2000 多个page。如果增加page 的数量以至于页间目录的数量增加，比如两个，就需要重新建立起更高级的页间目录，来作为这两个页间目录的目录。总之，一定能使得最上层的目录只有一个page 结构。这就是B+ 树。

主键索引：

1. 所有的数据最终可以在磁盘中，也可以在mysql 的buffer pool 内存中

2. 所有的数据都必须以page 为单位进行IO，以page 为单位组织。

3. 在mysql 内部，将热点数据，以B+ 树的形式将所有的page 页，进行组织，形成的数据结构和其配套的查找算法，叫做索引。

总结：

• page 分为目录页和数据页，目录页只放各个下级page 的最小键值。

• 查找的时候自顶部向下查找，只需要将部分目录页加载到内存中，即可完成算法的整个查找过程，大大减少了IO 次数。

为什么INNODB可以采用B+树 而不用其他数据结构？

1. 链表，线性遍历

2. 二叉搜索树：可能退化为线性结构

3. AVL&红黑树，虽然平衡或者近似平衡，但是二叉结构相较于多阶B+，树整体过高。自顶向下找层高越低，系统与硬盘的IO 交互就越少。

4. hash：INNODB 和myisam 不支持hash。虽然hash 有时候很快，但是面对范围查找就明显不行。

B 树和B+ 树区别：

1. 分支节点是否存储数据: B树节点既有数据又有page 指针，B+树只有叶子节点有数据，其他目录页只有键值和指针。

2. B+叶子节点全部相连，B没有。

为什么选择B+：

• 节点不存储数据，这样一个节点就可以存储更多key，树变得更矮，IO操作次数更少。

• 叶子节点相连，更便于进行范围查找。

B+ 树在哪里？

在磁盘上有完整的B+ 和数据。

在内存中有局部高频被访问的B+的核心page。

Mysql查找一定会伴随着mysql 进行根据b+ 树进行page 的换入换出。

聚簇引擎 和 非聚簇引擎

MyISAM 的最大特点是 将索引page 和数据page 分离，也就是叶子节点没有数据，只有对应数据的地址。（非聚簇）

InnoDB 是将索引和数据都放在一起。（聚簇）

用非聚簇索引的引擎（MyISAM）创建表，会发现表数据用三个文件存储，其中 .MYD 文件代表data 数据，.MYI 文件代表Index 索引，而聚簇索引的引擎只会创建一个文件。

Mysql 除了默认会创建主键索引，用户也可以按照其他列信息建立索引，一般这种索引叫做辅助索引，或叫普通索引。

对于MyISAM，建立辅助索引和主键索引没有区别（因为索引和数据分离），无非就是主键不能重复，非主键可重复。

InnoDB 也可以创建辅助索引，但是innodb 的非主键索引中叶子节点中没有数据，只有对应记录的主键。

MyISAM 更擅长查找业务，但是不支持事务。InnoDB 支持事务。

索引操作

创建主键索引 (其实就是添加primary key 的操作:

• 创建表的时候，直接在字段名后制定primary key 就已经创建了。

   create table user1(id int primary key, name varchar(30));

• 创建表的最后，指定某列或者某几列作为主键索引。

   create table user2(id int, name varchar(30), primary key(id));

• 创建表之后再添加主键。

   create table user2(id int, name varchar(30));
   ​
   alter table user3 add primary key(id);

创建普通索引(辅助索引)

•  -- 定义表的最后，指定某列为索引
   index(name)

•  -- 创建完表以后指定某列为普通索引
   alter table table_name add index(name);

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

创建唯一索引：

•  -- 定义表时，在某列后直接指定unique
   create table table_name(id int primary key, name varchar(30) unique);

•  -- 创建表时，再表的后面指定某列或者某几列为unique
   create table table_name(id int primary key, name varchar(30), unique(name));

•  alter table table_name add unique(name);

查看索引情况：

•  show keys from table_name;

•  show index from table_name;

•  desc table_name;

删除索引：

•  alter table table_name drop primary key;

•  alter talbe table_name drop index index_name;
   -- index_name 指的是 show keys from table_name 中的key_name 字段

•  drop index index_name on table_name;

我们目前学习的索引都是基于列，当对文章字段或者有大量文字的字段进行检索时，会使用到全文索引。MySQL提供全文索引机制，但是有要求，要求表的存储引擎必须是MyISAM，而且默认的全文索引支持英文而不支持中文。如果对中文进行全文索引，可以使用sphinx 的中文版， coreseek。

• 创建全文索引

   create table articles(
   id int unsigned auto_increment not null primary key,
   title varchar(200),
   body text,
   FULLTEXT(title, body)
   )engine=MyISAM;

• 使用全文索引进行查找

   select * from articles where match (title, body) against ('database');

索引创建的原则

• 比较频繁作为查询条件的字段应该创建索引

• 唯一性太差的字段不适合单组创建索引，即使频繁的作为查找条件。

• 更新非常频繁的字段不适合创建索引

• 不会出现在where 子句中的字段不适合创建索引。