MySQL中的索引

explain关键字，

MySQL索引特性
索引的概念

MySQL 索引是一种用于提高数据库查询效率的数据结构

• 数据库表中存储的数据都是以记录为单位的，如果在查询数据时直接一条条遍历表中的数据记录，那么查询的时间复杂度将会是 O ( N )。
• 索引的价值在于提高海量数据的检索速度，只要执行了正确的创建索引的操作，查询速度就可能提高成百上千倍。当一张表创建索引后，在数据库底层就会为表中的数据记录构建特定的数据结构，后续在查询表中数据时就能通过查询该数据结构快速定位到目标数据。
• 索引虽然提高了数据的查询速度，但在一定程度上也会降低数据增删改的效率，因为这时在对表中的数据进行增删改操作时，除了需要进行对应的增删改操作之外，可能还需要对底层建立的数据结构进行调整维护。

 

常见的索引分为：

• 主键索引（primary key）。
• 唯一索引（unique）。
• 普通索引（index）。
• 全文索引（fulltext）。ouce 

source xxx.sql 

定位扇区时采用CHS寻址方式：

• 磁头（Heads）： 每个盘面都有一个对应的磁头，因此确定了磁头也就确定了数据在哪一个盘面。
• 柱面（Cylinder）： 所有盘面中半径相同的同心磁道构成柱面，因此在确定了数据在哪一个盘面的基础上，再确定柱面也就确定了数据在该盘面上的哪一个磁道。
• 扇区（Sector）： 每个磁道被划分成若干个扇区，因此在确定了数据在哪一个磁道的基础上，再确定扇区也就确定了数据在该磁道上的哪个扇区。

简单来说，CHS寻址方式就是先通过H确定数据所在的盘面，再通过C确定数据所在的磁道，最后通过S定位到目标扇区。

 

• CHS寻址方式是磁盘定位扇区的方式，但实际CHS寻址方式对磁盘以外的设备来说没什么作用，因此系统软件在定位磁盘上的数据时采用的是LBA（Logical Block Address，逻辑区块地址）。
• LBA是描述计算机存储设备上数据所在区块的通用机制，LBA和CHS之间可以通过计算公式进行相互转换，LBA存在的意义就是对底层逻辑器件进行虚拟化，让系统软件可以不用关心底层硬件具体的寻址方式，而实际底层硬件采用的还是CHS寻址方式。

 

操作系统与磁盘交互的基本单位
操作系统与磁盘进行IO交互的基本单位是4KB，而不是扇区的大小512字节，原因如下：

•  物理内存实际是被划分成一个个4KB大小的页框的，磁盘上的数据也会被划分成一个个4KB大小的页帧，因此操作系统与磁盘以4KB为单位进行IO交互，就能提高数据加载和保存的效率。
• 操作系统与磁盘进行IO交互时，如果直接以扇区的大小作为IO的基本单位，那么这时系统的IO代码和硬件就是强相关的，将来当硬件的扇区大小发生变化时就需要对应修改操作系统的IO代码。
• 此外，以扇区的大小作为IO的基本单位太小了，这就意味着读取同样的数据内容，需要进行更多次的磁盘访问，而磁盘的效率是比较低的，这样IO效率就降低了。

因此操作系统与磁盘以4KB作为IO交互的基本单位，一方面是为了提高IO效率，另一方面是为了实现硬件和系统的解耦。

 

随机访问与连续访问

• 随机访问： 本次IO所给出的扇区地址与上次IO给出的扇区地址不连续，磁头在两次IO操作之间需要做比较大的移动动作才能找到目标扇区进行IO。
• 连续访问： 本次IO所给出的扇区地址与上次IO给出的扇区地址是连续的，磁头很快就能找到目标扇区进行IO。

 

MySQL与磁盘交互的基本单位
MySQL作为一款应用软件，可以想象成是一种特殊的文件系统，它有着更高频的IO场景，因此为了提高基本的IO效率，MySQL与磁盘交互的基本单位是16KB，这个基本数据单元在MySQL这里也叫做Page。
通过show命令查看系统中的全局变量，可以看到InnoDB存储引擎交互的基本单位是16KB。如下

 

• 在MySQL中进行的各种CRUD操作时，都需要先通过计算找到对应的操作位置，只要涉及计算就需要CPU参与，而冯诺依曼体系结构决定了CPU只能和内存打交道，因此为了便于CPU参与，就需要先将数据加载到内存当中。

• 所以在特定的时间内，MySQL中的数据一定是同时存在于磁盘和内存中的，当操作完内存数据后，再以特定的刷新策略将内存中的数据刷新到磁盘当中，这时MySQL和磁盘进行数据交互的基本单位就是Page。
• 为了更好的支持上述操作，MySQL服务器在启动的时候会预先申请一块内存空间来进行各种缓存，这块内存空间叫做Buffer Pool，后续磁盘中加载的数据就会保存在Buffer Pool中，刷新数据时也就是将Buffer Pool中的数据刷新到磁盘。
• 由于内核中是有内核缓冲区的，因此MySQL从磁盘读取数据时，需要先将数据从磁盘读取到内核缓冲区，再将数据从内核缓冲区读取到Buffer Pool，MySQL将数据刷新到磁盘时，同样需要先将数据从Buffer Pool刷新到内核缓冲区，再将数据从内核缓冲区刷新到磁盘。

因此所谓的操作系统和磁盘交互的基本单位是4KB，就是指内核缓冲区与磁盘之间是以4KB为单位进行交互的。而MySQL的Buffer Pool和磁盘实际并不是直接交互的，因此所谓的MySQL与磁盘交互的基本单位是16KB，指的是MySQL的Buffer Pool与内核缓冲区之间是以16KB为单位进行交互的。只不过在说的时候更关注的是MySQL和磁盘之间的关系，所以直接说的是MySQL与磁盘交互的基本单位是16KB，相当于忽略了中间的内核缓冲区

为什么MySQL与磁盘交互的基本单位是Page
MySQL与磁盘进行交互时为什么不是按需交互，而是以Page为基本单位进行交互的？

• 当我们查询表中的某一条记录时，如果MySQL只从磁盘中将这一条记录加载到内存当中，那么当我们继续查询表中的其他记录时，MySQL就一定需要再次与磁盘进行IO交互。
• 而如果当我们查询表中的某一条记录时，MySQL直接将这条记录所在的整个Page都加载到内存当中，那么当我们继续查询表中的其他记录时，MySQL很可能就不再需要与磁盘进行IO交互了，因为这条记录很可能也在被加载进来的Page当中，这时直接在内存中进行查询即可，大大减少了IO的次数。
• 当然，我们不能保证用户下一次要访问的数据一定就在本次加载进来的Page当中，但是根据统计学原理，当一个数据正在被访问时，那么下一次有很大可能会访问其周围的数据（局部性原理），因此我们有较大概率保证用户下一次要访问的数据和本次访问的数据在同一个Page当中，如果局部性原理没有起作用，那就再把对应的Page加载到内存当中即可。

也就是说，MySQL与磁盘进行交互时以Page为基本单位，可以减少与磁盘IO交互的次数，进而提高IO的效率。

 

推导主键索引结构的构建：
单个Page：

• MySQL中要管理很多数据文件，在运行期间一定有大量的Page需要被换入换出，因此MySQL一定需要将内存中大量的Page管理起来。
• MySQL将内存中的每一个Page都用一个结构体描述起来，然后再将各个结构体以双链表的形式组织起来，因此一个Page结构体内部既包含数据字段，也包含属性字段。
• 此外，为了方便后续数据的插入和删除，每个Page结构体内部存储的数据记录会以单链表的形式组织起来，并且各个记录之间会按照主键进行排序。

说明一下：

• 每个Page结构体内部的数据会按照主键进行排序，目的是为了优化数据查询的效率，因为单链表在查找的时候是顺序查找的，有序就意味着在查找的过程中有机会提前结束查询过程。
• 这也就是前面所说的，只要设置了主键，即便向表中插入的数据是乱序的，MySQL底层也会自动按照主键对插入的数据进行排序，因此查询得到的数据是按照主键进行有序排序的。

 

多个Page

• 随着数据量不断增大，单个Page中无法存下所有数据，这时就需要用多个Page来存储数据。
• 这时在查询数据时就需要，先遍历Page双链表确定目标数据在哪一个Page，然后再在该Page内部找到目标数据

 

Page之上创建页目录

• 虽然在单个Page内部能够通过页内目录来快速定位数据，但在遍历Page双链表寻找目标Page时本质进行的还是线性遍历。
• 这时可以给各个Page结构体也建立页目录，页目录中的每个目录项都指向一个Page，而这个目录项存放的就是其指向的Page中存放的最小数据的键值。
• 在给各个Page结构体建立页目录后，在查询数据时就可以先通过遍历页目录找到目标数据所在的Page，然后再在该Page内部找到目标数据。

• 最终构建出来的实际就是一棵B+树，这棵B+树就是InnoDB的索引结构，其中每一层Page的作用就是加速它的下一层的查找效率。
• 如果我们创建表时设置了主键，那么MySQL在底层就会自动将这张表中的的数据以B+树的形式组织起来，保存在Buffer Pool当中，当我们查询数据时就可以通过查询这棵B+树来提高查询效率。
• MySQL中可能同时有大量的表正在被处理，因此Buffer Pool中可能会存在多个索引结构，也就是同时存在多个B+树结构，当我们查询表时访问的就是这张表对应的B+树结构

 

B+树中的Page结点是否需要全量加入到Buffer Pool中？

• 当对MySQL中的某张表进行增删查改操作时，不需要将其对应B+树的所有结点全量加入到Buffer Pool中，甚至在刚开始时只需要将B+树的根结点加入到Buffer Pool中。
• 当后续访问表中的数据时，再将该数据对应路径上的结点加入到Buffer Pool中即可，对于其他不需要的结点根本不用加入到Buffer Pool中，这一点和操作系统中的页表是很像的。
• 此外，在刷新数据时也不需要将B+树中所有的结点都进行刷新，在Page结构体中有一个标记位用来标记当前Page是否被修改过，如果被修改过则说明这是一个脏数据，在刷新数据时只有脏数据才需要被刷新到磁盘上。
• 由于B+树中的结点都是16KB大小的Page，因此无论是刷新数据到磁盘函数从磁盘加载数据到Buffer Pool，都是以Page为单位进行的，这也就是所谓的MySQL与磁盘交互的基本单位是Page。

如果把这棵B+树逆时针旋转90度，就会发现这其实就是操作系统中的页表结构，本质操作系统中的页表也是B+树结构。

 

以32位平台为例，页表将一个虚拟地址转换成物理地址的过程如下：

 

• 选择虚拟地址的前10个比特位在页目录当中进行查找，找到对应的页表。
• 再继续选择虚拟地址后续的10个比特位在对应的页表当中进行查找，找到物理内存中对应页框的起始地址。
• 最后选择虚拟地址中剩下的12个比特位作为偏移量，从对应页框的起始地址处向后进行偏移，最终得到的就是转换后的物理地址

 

说明一下：

12个比特位有 2^12种取值，字节对应就是4KB，所以物理内存中一个页框的大小就是4KB，这也就是为什么操作系统与磁盘交互的基本单位是4KB的原因。
此外，页表中的各个B+树结点也不需要全量加入到内存中，而只需要加入访问到的结点即可，所以页表占用的内存大小实际是可控的，这也就是为什么二级页表可行的原因。

 

 

索引结构可以采用哪些数据结构

索引结构可以采用哪些数据结构

除了InnoDB存储引擎所采用的B+树结构，索引结构还可以采用哪些数据结构呢？

 B树 VS B+树

B+树是B树的一种变形结构，那为什么我们没有采用普通的B树作为索引结构呢？

• 首先，普通B树中的所有结点中都同时包括索引信息和数据信息，由于一个Page的大小是固定的，因此非叶子结点中如果包含了数据信息，那么这些结点中能够存储的索引信息一定会变少，这时这棵树形结构一定会变得更高更瘦，当查询数据时就可能需要与磁盘进行更多次的IO操作。
• 其次，普通B树中的各个叶子结点之间没有连接起来，这将不利于进行数据的范围查找，而B+树的各个叶子结点之间是连接起来的，当我们进行范围查找时，直接先找到第一个数据然后继续向后遍历找到之后的数据即可，因此将各个叶子结点连接起来更有利于进行数据的范围查找。

 

聚簇索引 VS 非聚簇索引
MyISAM存储引擎 - 主键索引结构

之前推导的主键索引结构是InnoDB存储引擎的主键索引结构，而MyISAM存储引擎同样采用B+树作为索引的基本数据结构。
与InnoDB存储引擎的B+树不同的是，MyISAM存储引擎的B+树的叶子结点存放的不是数据记录，而是数据记录对应的地址。

• 聚簇索引： 像InnoDB存储引擎这种，将数据记录与索引结构放在一起的索引方案，叫做聚簇索引。
• 非聚簇索引： 像MyISAM存储引擎这种，将数据记录与索引结构分离的索引方案，叫做非聚簇索引。

 

MyISAM存储引擎的主键索引结构

MyISAM存储引擎 - 普通索引结构

MyISAM存储引擎的普通索引采用的也是B+树结构，与主键索引唯一不同的地方就是普通索引的B+树中的键值可以重复。
因此一张表可能会同时存在多个B+树结构，但由于MyISAM存储引擎的B+树叶子结点中，存储的是对应的数据记录的地址，因此有效数据只会存储一份。

 

 

InnoDB存储引擎 - 普通索引结构

InnoDB存储引擎的普通索引采用的也是B+树结构，但普通索引的B+树中的键值可以重复，并且B+树的叶子结点中存储的不是数据记录，而是对应数据记录的主键值。
当根据普通索引查询数据时，会先查找普通索引对应的B+树找到目标记录的主键值，然后再查找主键索引对应的B+树找到目标记录，这个过程就叫做回表查询

说明一下：

• InnoDB存储引擎的普通索引的B+树叶子结点中没有保存整条数据记录，是为了节省空间，因为同一张表可能会创建多个普通索引，每个普通索引的B+树中都保存一份数据会造成数据冗余，所以通过回表查询主键索引对应的B+来获取整个数据记录，该做法本质一种以时间换取空间的做法。
• 当根据普通索引查询数据时，其实也不一定需要进行回表查询，因为有可能我们要查询的就是这条记录对应的主键值，因此查询完普通索引对应B+树后即可完成查询。
• 采用InnoDB存储引擎建立的每张表都会有一个主键，就算用户没有设置，InnoDB也会自动帮你创建一个不可见的主键，因为完整数据记录只会存储在主键索引对应的B+树中的，因此采用InnoDB存储引擎建立的表必须有主键

 

当采用InnoDB存储引擎创建表时，在数据库对应的目录下会新增两个文件。如下：

当采用MyISAM存储引擎创建表时，在数据库对应的目录下会新增三个文件。如下：

说明一下：

• 采用InnoDB和MyISAM存储引擎创建表时都会生成xxx.frm文件，该文件中存储的是表结构相关的信息。
• 采用InnoDB存储引擎创建表时会生成一个xxx.ibd文件，该文件中存储的是索引和数据相关的信息，这就是所谓的聚簇索引，索引和数据是存储在同一个文件中的。
• 采用MyISAM存储引擎创建表时会生成一个xxx.MYD文件和一个xxx.MYI文件，其中xxx.MYD文件中存储的是数据相关的信息，而xxx.MYI文件中存储的是索引相关的信息，这就是所谓的非聚簇索引，索引和数据是分开存储的。

 

MySQL创建主键索引的所有方法

1. 在创建表时定义主键

在创建表时，可以直接在列定义后面使用 PRIMARY KEY 关键字来指定某列为主键，也可以在表定义的最后使用 PRIMARY KEY 关键字来指定多列组合为主键。

单字段主键

CREATE TABLE students (student_id INT NOT NULL,name VARCHAR(50),age INT,PRIMARY KEY (student_id)
);

多字段组合主键

CREATE TABLE enrollments (student_id INT NOT NULL,course_id INT NOT NULL,enrollment_date DATE,PRIMARY KEY (student_id, course_id)
);

2. 使用 ALTER TABLE 语句添加主键

如果表已经存在，并且没有主键，可以使用 ALTER TABLE 语句来添加主键。

为单字段添加主键

-- 创建一个没有主键的表
CREATE TABLE employees (employee_id INT NOT NULL,first_name VARCHAR(50),last_name VARCHAR(50)
);-- 使用 ALTER TABLE 语句添加主键
ALTER TABLE employees
ADD PRIMARY KEY (employee_id);

为多字段添加组合主键

-- 创建一个没有主键的表
CREATE TABLE order_items (order_id INT NOT NULL,product_id INT NOT NULL,quantity INT
);-- 使用 ALTER TABLE 语句添加组合主键
ALTER TABLE order_items
ADD PRIMARY KEY (order_id, product_id);

3. 在创建表时使用 AUTO_INCREMENT 属性创建主键

当主键字段的值需要自动递增时，可以使用 AUTO_INCREMENT 属性。通常，该属性与 INT 类型的列一起使用。

CREATE TABLE products (product_id INT AUTO_INCREMENT PRIMARY KEY,product_name VARCHAR(100),price DECIMAL(10, 2)
);

 

唯一索引

unique_key,操作与上类似

 

唯一索引

index,操作与上类似

 

全文索引

fulltext给title和body,操作与上类似

如果要通过全文索引来查询，需要使用match against进行搜索

在这条SQL语句前面加上explain，可以看到key对应的值为title，表示这条SQL在执行过程中用到了索引名为title的索引

 

使用全文索引进行搜索

MySQL 提供了 MATCH...AGAINST 函数来使用全文索引进行搜索。

SELECT * FROM articles
WHERE MATCH (title, content) AGAINST '搜索的内容';

自然语言搜索模式

自然语言搜索模式是最常用的模式，它会将搜索字符串作为自然语言进行处理。

SELECT * FROM articles
WHERE MATCH (title, content) AGAINST ('MySQL 全文索引' IN NATURAL LANGUAGE MODE);

上述代码会在 articles 表的 title 和 content 列中搜索包含 “MySQL 全文索引” 的记录。

布尔搜索模式

布尔搜索模式允许使用特殊的运算符来构建更复杂的搜索条件。

SELECT * FROM articles
WHERE MATCH (title, content) AGAINST ('+MySQL -数据库' IN BOOLEAN MODE);

这里的 + 表示必须包含该词，- 表示必须不包含该词，所以上述查询会搜索 title 和 content 列中包含 “MySQL” 但不包含 “数据库” 的记录。

查询扩展搜索模式

查询扩展搜索模式会先进行一次初始搜索，然后根据搜索结果中的相关词进行二次搜索，以扩大搜索范围。

SELECT * FROM articles
WHERE MATCH (title, content) AGAINST ('MySQL 全文索引' WITH QUERY EXPANSION);

首先，MySQL 会找到标题和内容中直接包含 “MySQL 全文索引” 的文章。假设这些文章中还经常提到 “索引优化”“全文搜索算法” 等词汇，那么 MySQL 会将这些词汇添加到查询中，再次搜索，可能会找到一些虽然没有直接提及 “MySQL 全文索引”，但与索引优化或全文搜索算法相关的文章，从而扩大了搜索范围，提高了找到相关文档的可能性3. 全文索引的限制和注意事项

• 支持的存储引擎：目前只有 InnoDB 和 MyISAM 存储引擎支持全文索引。
• 最小和最大词长：全文索引会忽略一些长度小于最小词长或大于最大词长的词，这些长度可以通过 ft_min_word_len 和 ft_max_word_len 系统变量进行配置。
• 停用词：全文索引会忽略一些常用的停用词，如 “the”、“and”、“or” 等，你可以通过修改 ft_stopword_file 系统变量来调整停用词列表。
• 更新和维护：当表中的数据发生变化时，全文索引会自动更新，但大量的数据更新可能会影响性能。在进行批量数据更新时，可以考虑先禁用全文索引，更新完成后再重新启用。

说明一下：

• MyISAM存储引擎是支持全文索引的，而InnoDB存储引擎是在5.6以后才开始支持全文索引的。
• 同时使用title和body建立全文索引时，相当于建立了一个复合索引，默认会选择fulltext中的第一个列名作为这个复合索引的索引名，所以这里explain中key对应的索引名为title。
• 由于是title和body共同建立的全文索引，所以如果文章当中没有出现关键字，但文章名称中出现了关键字则也会被筛选出来（当前示例没有体现出来）。

索引创建的原则如下：

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

 

MySQL删除主键索引的所有方法

1. 一般表删除主键索引

当表结构比较常规时，可以直接使用 ALTER TABLE 语句并结合 DROP PRIMARY KEY 子句来删除主键索引。

-- 创建一个带有主键的表
CREATE TABLE students (student_id INT NOT NULL,name VARCHAR(50),age INT,PRIMARY KEY (student_id)
);-- 删除主键索引
ALTER TABLE students
DROP PRIMARY KEY;

在上述示例中，首先创建了一个名为 students 的表，其中 student_id 列被指定为主键。然后使用 ALTER TABLE 语句和 DROP PRIMARY KEY 子句将该表的主键索引删除。

2. 包含自增列的表删除主键索引

如果表中包含自增列（使用了 AUTO_INCREMENT 属性），并且该自增列是主键的一部分，那么在删除主键索引之前，需要先将自增属性移除。

-- 创建一个包含自增列作为主键的表
CREATE TABLE products (product_id INT AUTO_INCREMENT PRIMARY KEY,product_name VARCHAR(100),price DECIMAL(10, 2)
);-- 移除自增属性
ALTER TABLE products
MODIFY product_id INT;-- 删除主键索引
ALTER TABLE products
DROP PRIMARY KEY;

在这个例子中，先创建了一个 products 表，product_id 列是自增的主键。接着使用 MODIFY 子句将 product_id 列的自增属性移除，最后再使用 DROP PRIMARY KEY 子句删除主键索引。