【MySQL】索引原理

一、MySQL 存储与磁盘

1、MySQL 存储的本质

按照特定的数据结构来组织、存储和管理数据的仓库叫做数据库，MySQL是一种关系型数据库。数据库最重要的功能就是存储数据，而数据存储是需要依赖具体的介质的，它就是磁盘。

实际上，我们在 mysql 中创建数据库就是在特定路径下创建一个目录文件，创建表就是创建普通文件，这些文件存储在磁盘中。

2、磁盘的物理结构

磁盘的物理结构如下：磁盘主要由永磁铁、磁头、主轴、盘片以及下面的电路板组成，其中盘片为一摞，每一个盘片都分为上下两面，这两面都可以用来存储数据，且每一面都配有一个磁头。

注意事项：

1、磁盘中每个盘片的每一面都配有一个磁头，且磁头和盘面是没有接触的，二者的距离非常非常低，而一旦有灰尘等杂质落入到磁盘中就可能会导致磁头撞击灰尘从而刮花盘面，所以磁盘拆开后就会损坏。 2、由于磁盘的磁头与盘面距离非常近，为了避免磁盘与盘面接触而刮花盘面导致数据丢失，所以磁盘不能抖动；但是笔记本通常需要进行移动，很可能会发生上述故障，所以现在一般的个人笔记本都是使用固态硬盘 SSD，而不再使用磁盘；同时，SSD 的读写速度也要高于磁盘。 3、但是在企业端，磁盘仍然是存储的主流，因为企业中主机都统一放置在机房中，轻易不会移动；同时，SSD 存在造价贵、读写次数有限等缺点。 4、磁盘是计算机中唯一一个纯机械结构的设备，同时磁盘还是外设，所以磁盘进行数据读写的速度很慢。

3、磁盘的存储结构

磁盘的存储结构如下：

磁道：磁盘的表面即盘面由一些磁性物质组成，这些磁性物质可以用来记录二进制数据；同时，盘面被划分为一个个同心圆，这些同心圆被称为磁道 (一个同心圆就是一个磁道)，相邻磁道之间是有间隙的，我们的数据就存在磁道上。柱面：磁盘中所有盘面的同一个磁道被称为一个柱面，柱面和磁道其实是等价的，都用来定位磁道。扇区：从圆心向外放射，与磁道围成的一小块区域称为扇区，一个磁道会被划分为许多个扇区，每个扇区就是一个 “磁盘块”，这是磁盘寻址的基本单位，即数据进行 IO 的单位，大小一般为 512 byte。(注：近几年也逐渐出现了更大的大小为4096字节的扇区，被称为4K扇区，但我们这里不考虑）

注意事项：

1、由于每个扇区的大小是固定的，所以从圆心往外，扇区的数据存储密度会随着扇区面积的增大而减小。不过最新的磁盘技术，已经慢慢可以的让扇区大小不同了，不过我们现在也不考虑。 2、由于文件是存储在磁盘的扇区中的，所以找到一个文件的本质上就是找到磁盘上保存这个文件的所有扇区。

磁盘寻址的过程：

磁盘在进行 IO 时，首先需要进行寻址，即找到文件所在的扇区。寻址的过程如下 – 首先定位磁道，即在哪一个柱面 (cylinder)；然后再定位盘面，即定位到寻找哪一个盘面的磁头 (head)，最后再定位在哪一个扇区 (sector)。上述过程在物理上的表现方式如下：启动主轴后，所有的盘片以同样的方式进行高速旋转，同时所有的磁头也共同从圆心到半径左右摆动，当定位到柱面后，磁头停止摆动，盘片继续旋转，当盘片对应扇区旋转到磁头下方后，对应盘面的磁头向扇区中写入/读取数据。所以，在磁盘中定位任意一个/多个扇区，采用的基本硬件定位方式是柱面、磁头、扇区定位法，即 CHS 定位法。

4、磁盘的逻辑结构

我们以磁带的结构来引出磁盘的逻辑结构，如图，磁带盒里面一共有两个齿轮，其中一个齿轮上面缠绕着一圈圈的磁带，当我们把磁带盒插入磁带录音机后，磁带里面的音频数据就会读取然后通过录音机播放出来；当我们把磁带盒拆开后，我们可以发现，磁带扯出来后其结构是线性的，也就是说，磁带里面的数据是按线性方式来读取的。

对比到磁盘，磁盘的盘面由一个个磁道构成的，且这些磁道都是同心圆，和磁带卷起来时一模一样，那么我们也可以将磁盘结构抽象为线性结构，然后使用数组来存储数据：

如上，我们将整个磁盘从逻辑上看作一个 sector arr[n] – 数组的一个元素代表磁盘中的一个扇区，然后由多个扇区组成一个磁道，由多个磁道组成一个盘面，最后在由多个盘面组成整个磁盘。

自此，我们只需要知道数组中的一个下标就可以定位磁盘中的一个扇区，我们对磁盘的管理也转变为了对数组进行管理；在操作系统内部，我们将这种地址称为 LBA (logical block address) 地址。

LBA 地址转 CHS 定位例子：

假设一个磁盘有两个盘片，每个盘片有两个盘面，每个盘面有10个磁道，每个磁道有100个扇区；现在，某个扇区的LBA地址为1234，求该扇区在磁盘上的具体位置：

最后，操作系统为什么要对 CHS 进行逻辑抽象呢？有如下两个原因：

1、数组更便于管理； 2、不让操作系统代码与底层硬件强耦合 – 即使磁盘的存储结构改变，操作系统仍然可以使用 LBA 地址进行定位寻找，只需要改变 LBA 与磁盘扇区的映射关系即可。

5、磁盘的访问方式

磁盘分为随机访问 (Random Access) 与连续访问 (Sequential Access)。

随机访问：本次IO所给出的扇区地址和上次IO给出扇区地址不连续，这样的话磁头在两次IO操作之间需要作比较大的移动动作才能重新开始读/写数据。
连续访问：如果当次IO给出的扇区地址与上次IO结束的扇区地址是连续的，那磁头就能很快的开始这次IO操作，这样的多个IO操作称为连续访问。

因此尽管相邻的两次IO操作在同一时刻发出，但如果它们的请求的扇区地址相差很大的话也只能称为随机访问，而非连续访问。同时，磁盘是通过机械运动进行寻址的，由于随机访问不需要过多的定位，故效率比较高。

6、对 IO 单位的优化

操作系统与磁盘的 IO 单位

在上面我们提到，磁盘的 IO 单位是一个扇区大小，即 512 byte，但这个数据量还是太小了，为了减少 IO 次数，提高 IO 效率，操作系统的文件系统会定制的一次进行多个扇区的读取，如 1KB/2KB/4KB。(文件系统默认采用 4KB 大小为单位 (8个扇区) 进行IO)

这样，不仅内存被划分为了许多个 4KB 大小的空间 (页框)，磁盘中的文件尤其是可执行文件也由多个 4KB 大小的块组成的 (页帧)。

MySQL 与磁盘的 IO 单位

在谈 MySQL 的 IO 单位时，我们首先需要明白，MySQL 作为一款应用级软件，它肯定是不能直接和磁盘这种硬件打交道的。因为操作系统是计算机软硬件资源的管理者，MySQL 只能通过操作系统提供的系统接口对磁盘或者内存中的文件进行操作 (其实就是我们基础 IO 时学的 read/write 等接口)。

同时，MySQL 有着更高的 IO 场景 (MySQL 的主要功能就是存储数据)，所以，为了进一步提高 IO 效率，MySQL 与磁盘进行 IO 的基本单位是 16KB (InnoDB 存储引擎下)。

也就是说，磁盘这个硬件设备的基本单位是 512 字节，操作系统与磁盘进行 IO 的基本单位是 4KB，MySQL 与磁盘行 IO 的基本单位是 16KB。注意，我们这里说 MySQL 与磁盘进行 IO，其实这中间省略了操作系统，完整的过程是 MySQL 将 16KB 基本单位的数据交给操作系统，保存在文件缓冲区中，然后再由操作系统以 4KB 为单位 IO 4 次，最终将 MySQL 数据持久化到磁盘中。

在 MySQL 中，我们将与磁盘进行交互的这个基本数据单元称为 Page，即一个Page的大小位16KB。(注意和系统的page区分)

注意：这里存在一个问题，既然 MySQL 的IO单位是16KB，那么如果我们需要的只是很少的记录，操作系统也必须将整个大小为16KB的Page加载到内存，这样会不会很浪费？其实大多数情况下并不会，因为计算机中存在局部性原理，即该数据被用到那么与它相邻的数据也大概率会被用到。

7、建立共识

在了解磁盘存储数据的原理以及 MySQL 与磁盘的关系后，我们可以建立如下共识：

MySQL 中的数据文件，是以page为单位保存在磁盘当中的。
MySQL 的 CURD 操作，都需要通过计算找到对应的插入位置，或者找到对应要修改或者查询的数据。而只要涉及计算，就需要CPU参与，为了便于CPU参与，一定需要先将数据从磁盘 load 到内存当中，所以在特定时间内，数据一定是磁盘中有，内存中也有。
当操作完数据后，又需要将更新后的数据以特定的刷新策略刷新到磁盘中，这时候就涉及到了磁盘和内存的数据交互，即 IO，并且此时 IO 的基本单位就是 Page (16KB)。
为了更好的进行上面的操作， MySQL 服务器在内存中运行的时候，会在服务器内部预先申请一个名为 缓冲池(Buffer Pool) 的大内存空间来进行各种缓存，它的大小一般为 128M。

二、索引原理

1、引出索引

为了引出索引，我们建立一个 user表，并在表中插入一些无序的数据：

代码语言：javascript复制

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

代码语言：javascript复制

insert into user (id, age, name) values(3, 18, '杨过');
insert into user (id, age, name) 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, '欧阳锋');

这里出现了一个奇怪的现象：我们在插入数据时并没有按照 id 有序的顺序插入，但是查询出来的结果却是按照 id 排好序的，这是为什么呢？

其实这是因为我们给 id 添加了主键约束，所以 mysqld 会自动为主键建立主键索引，而主键索引是有序的。如果我们将 id 的主键约束去掉，那么查询出来的顺序就是我们的插入顺序了。

2、如何理解 Page

理解单个 Page

我们上面提到，MySQL IO 的基本单位的 Page，同时，MySQL 服务会在服务器内部开辟一个缓冲池来缓存数据。由于一个Page的大小只有4KB，那么当 MySQL 的数据文件非常多的时候，buffer poll 中势必就会存在很多的 Page。为了能够有效的管理这些 Page，MySQL 服务就必须先对 Page 的各种属性进行描述，然后再用诸如链表这样的数据结构将一个个的 Page 对象组织起来 (先描述，再组织)：

代码语言：javascript复制

struct page {
    struct page *prev;
    struct page *next;
    char data[NUM];  
    // ...
}; -- 16KB

这样，新增 Page 就变成了从 buffer poll 中 malloc/new 一个 Page 对象，而对 Page 的管理也变成了对链表的增删查改。

因为有主键的问题， MySQL 会默认按照主键给我们的数据进行排序，从上面的Page内数据记录可以看出，数据是有序且彼此关联的。但是为什么数据库在插入数据时要对其进行排序呢？我们按正常顺序插入数据不是也挺好的吗？原因如下：

由于页内部存放数据的模块，实质上是一个链表的结构，而链表的特点是增删快，查询修改慢，所以优化查询的效率是必须的。
而插入数据时排序其实就是为了优化查询的效率 – 因为有序，所以在查找的时候，从头到后都是有效查找，没有任何一个查找是浪费的，并且如果运气好，我们还可以提前结束查找过程。

理解多个 Page

通过上面的分析我们知道，页模式中只有一个功能，那就是在查询某条数据的时候直接将一整页的数据加载到内存中，以减少硬盘IO的次数，从而提高性能。但是，我们也可以看到，现在的页模式内部，实际上是采用了链表的结构，前一条数据指向后一条数据，本质上还是通过数据的逐条比较来取出特定的数据。换句话说，页模式只优化了IO的效率，而并没有优化查找的效率。

这样的话，如果有1千万条数据，一定需要多个Page来保存这些数据，多个Page彼此使用双链表链接起来，而且每个Page内部的数据也是基于链表的。那么，查找特定一条记录，也一定是线性查找，这效率也太低了。

页目录

那么有没有一种方法可以优化页中记录的查找效率呢？我们可以参考现实生活中书的目录：

可以看到，对于数据库原理这本书，如果我们要找 “范式” 这一小节，一共有两种做法：

从头逐页的向后翻，直到找到目标内容。
通过书提供的目录，发现"范式"章节在128，那么我们便直接翻到128页。

可以看到，目录的作用就是可以一次帮我们排除掉许多非目标页，从而帮我们快速定位。但是这种提高是有空间上的代价的，因为我们花费了额外的纸张来存储目录。所以，书的目录本质上是一种以空间换时间的做法。

既然书可以通过目录来提高查找效率，那么数据库是否也能引入目录呢？当然可以，我们分别从单页和多页两种情况来分析。

单页情况

如上，在一个Page内部，我们引入了目录，它可以帮助我们提高查找的效率。比如，们要查找id=4记录，之前必须用4与每条记录的id进行对比，需要线性遍历4次，才能拿到结果。现在直接用4与页目录的 key 值进行对比，如果发现 key 等于目标值或者目标值在当前 key 值和下一个页目录的 key 值之间，那么我们就可以通过 key 后面保存的地址找到新的起始地址进行遍历，从而提高查找效率。(这也回答了为什么 MySQL 会默认对键值进行排序)

注意：这里由于我们的记录太少，所以每个页目录项之间间隔的记录也很少，因此看不出查找的效率提高了，但是如果每条目录项之间间隔100/500条记录，那么以前需要对比100/500次才能过滤掉的记录现在对比一次就能过滤掉了。

多页情况

虽然页目录可以有效解决页内数据查找的效率问题，但是一页的大小只有固定的 16KB，所以随着数据量不断增大， 16KB 不可能存下所有的数据，那么必定会有多个页来存储数据。即在单表数据不断被插入的情况下， MySQL 会在容量不足的时候，自动开辟新的Page来保存新的数据，然后通过指针的方式，将所有的Page组织起来。

这样，我们就可以通过链表指针在多个Page页之间进行遍历，Page内部通过目录来快速定位数据。可是，貌似这样也存在效率问题，因为我们需要从第一个Page开始往后不断遍历，直到在某个Page中找到目标记录，这也就意味着我们依旧需要进行大量的IO，将下一个Page加载到内存，进行线性检测。这样一看我们之前的Page内部的目录优化就显得有点杯水车薪了。

目录页

如何解决呢？其实很简单，和我们之前的解决方案一样，给Page也带上目录就好了 (相当于书的章节)：

使用一个目录项来指向某一页，这个目录项中存放的就是将要指向的页中存放的最小数据的键值。
和页内目录不同的地方在于，这种目录管理的级别是页，而页内目录管理的级别是行，所以我们将保存目录项的页称为目录页。
目录页中，每个目录项由指向的页中存放的最小数据的键值以及指向的页的地址。

如下图：(图中只画了键值，没有画地址)

如上，我们创建了一个/多个目录页来管理页目录，目录页中的数据存放的就是指向的那一页中最小的数据。有数据，就可通过比较和指针找到应该该访问哪个Page。

注：**其实目录页的本质也是页，只是普通页中存的数据是用户数据，而目录页中存的数据则是普通页的最小键值和地址 **。

我们可以大概估算一下，一页的大小是16KB，一个目录页里面只存放 key 值和页指针，同时，key 一般都为整形，我们假设指针的大小是4字节，那么一个目录页最终可以代表的数据大小是 16KB/8B*16KB = 32MB；这个大小其实已经比较大了，但还是不够，即我们还是需要遍历不同的目录页。

所以，我们可以再加一层目录页，如下：

这样就够了吗？我们再算一下：一个二级目录页可以表示的数据是 2048 KB，那么一个以及目录页 (根目录页) 可以表示的数据就是 16KB/8B*32MB = 64GB，这下大部分情况下完全够用了。

可能有的同学已经发现了，上面这种索引结构其实就是 B 树，事实也确实如此，MySQL 中最常用的两种存储引擎 InnoDB 和 MyISAM 底层索引使用的都是 B 树。

需要注意的是：

在B 树，也就是MySQL索引结构中，只有各叶子结点之间是使用双链表连接起来的，这主要是为了支持范围查找，而其他非叶子节点之间是没有关系的。
同时，也只有叶子节点中存储了记录，其他非叶子节点中只存储了页表项，即指向页的最小 key 值和页指针，而并没有真正的存储记录，这也是为了能够腾出更多的空间来存储页表项。
即使创建表时没有指明主键，MySQL 索引也是按照 B 树的结构来组织的，这是因为如果没有主键索引 MySQL 会自动添加一列隐藏列来作为主键。

4、索引结构的选择

那么 InnoDB/MyISAM 在建立索引结构来管理数据的时候，为什么会选择 B 树呢？原因如下：

B 树只有叶子节点中存储了数据，其他非叶子节点中只存储了key值和Page指针，从而使得一个目录页能够存储很多的页表项，最终表现出来的效果就是这棵B 树很胖很矮。这样就能保证在CURD数据时，只需要将少量的 Page (包括目录页和数据页) load 到内存中，即减少了 IO 的次数，也提高了查找的效率。

那么为什么不使用B树呢？原因如下：

B树相较于B 树的特点是目录页中即存储数据，又存储Page指针，从而导致一个目录页只能存储较少的Page，最终表现出来的效果就是这棵B树比较高瘦。这样就导致在CURD数据时会有更多的Page需要被 load 到内存中，从而增加了 IO 的次数。

那为什么不使用其他的数据结构呢？原因如下：

链表：顺序结构，需要线性变量，IO 次数以及查询效率都极差。
二叉搜索树：比较高导致 IO 次数较多，且在极端情况下可能退化成为线性结构。
AVL 树 or 红黑树：虽然是平衡或者近似平衡，但是毕竟是二叉结构，相较于 B 树要高出很多，需要进行更多次的 IO。
Hash：在官方的索引实现方式中， MySQL 是支持HASH的，不过 InnoDB 和 MyISAM 并不支持。另外Hash的算法特征虽然决定它查找很快 (O(1))，不过，在面对范围查找时Hash需要重头查找，除此之外，Hash也还存在一些其他问题。

MySQL 中常见的存储引擎采用的索引结构如下： (BTREE 并不是指B树，而是指B 树)：

5、聚簇索引与非聚簇索引

聚簇索引

聚簇索引是指将索引和数据放在一张 Page 中，InnoDB 就是典型的聚簇索引。

非聚簇索引

而非聚簇索引则是将索引Page和数据Page分离，也就是叶子节点没有数据，只有对应数据的地址。MyISAM 就是典型的非聚簇索引。如下：( Col1 为主键)

其中，我们在使用不同存储引擎创建表时，聚簇索引与非聚簇索引所对应的表文件也会有明显的区别，如下：

代码语言：javascript复制

// 指明engine=InnoDB，使用聚簇索引
create table t1(
id int primary key,
name varchar(20) not null
)engine=InnoDB;

代码语言：javascript复制

// 指明engine=MyISAM，使用非聚簇索引
create table t2(
id int primary key,
name varchar(20) not null
)engine=MyISAM;

可以看到，聚簇索引 t1 的索引与数据都存放在 t1.ibd 文件中，而非聚簇索引 t2 的数据存放在 t2.MYD 中，索引存放在 t2.MYI 中，是分开存放的。

6、辅助 (普通) 索引

MySQL 除了可以建立主键索引外，我们用户也有可能建立按照其他非主键列的信息建立索引，一般这种索引可以叫做辅助 (普通) 索引。唯一键索引也是普通索引的一种。

对于 MyISAM 这种非聚簇索引存储引擎来说，建立辅助（普通）索引和主键索引没有差别，无非就是主键不能重复，而非主键可重复，因为索引和数据分离，建立主键索引和普通索引的代价是相同的。

下图是基于 MyISAM 的 Col2 建立的索引，和主键索引没有差别：

而对于 InnoDB 这样的聚簇索引存储引擎来说，由于索引和数据保存在同一个Page中，所以不能将普通索引也和数据保存到Page中，这样一份数据就占用了两份内存空间。

所以， InnoDB 的非主键索引中叶子节点并没有数据，而只有对应记录的key值。即我们通过辅助（普通）索引，找到目标记录，需要两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。这个过程就叫做 回表查询。

7、复合索引

复合索引是指由多个属性列来共同构成一个索引，它可以提高多个列查询的性能，这个索引可以是主键索引，也可以是普通索引。

代码语言：javascript复制

create table t3(
id int,
name varchar(20),
primary key(id, name) -- 指定id和name共同作为主键
);

索引最左匹配原则与索引覆盖

索引最左匹配原则是指在使用复合索引时，索引可以有效地支持查询的最左前缀部分。这意味着当查询条件只涉及复合索引的前缀列时，仍然可以利用该索引。

与索引最左匹配原则相呼应的是索引覆盖。索引覆盖是指在查询中，在一个索引中包含了查询所需的所有列。通常，当查询需要回表到数据页来获取完整的数据行时，会增加IO成本和查询延迟。而通过索引覆盖，可以在索引中直接获取查询所需的所有列，而无需回表，从而提高查询的性能。举个例子，假设 (name, addr) 共同构成普通复合索引，那么一旦 name 匹配成功，就直接返回 addr，而不用再根据主键去获取 addr。

通过合理设计复合索引，使其同时满足覆盖查询的需要和最左前缀匹配的原则，可以减少IO成本，降低查询延迟，并提高查询的性能。

三、索引操作

1、创建索引

创建主键索引

创建主键索引一共有两种方式：建表时创建与建表后添加。

建表时创建索引：

代码语言：javascript复制

create table user1(
id int primary key,
name varchar(20) not null
);

代码语言：javascript复制

create table user1(
id int,
name varchar(20) not null,
primay key(id)
);

建表后添加索引：

代码语言：javascript复制

alter table user1 add primary key(id);

逐渐索引的特点：

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

创建唯一键索引

和创建主键索引一样，唯一键索引的创建也是建表时创建与建表后添加。

建表时创建索引：

代码语言：javascript复制

create table user2(
id int primary key,
name varchar(20) not null unique key
);

代码语言：javascript复制

create table user2(
id int primary key,
name varchar(20) not null,
unique key(name)
);

建表后添加索引：

代码语言：javascript复制

alter table user2 add unique key(name);

唯一键索引的特点：

一个表中，可以有多个唯一索引。
查询效率高。
如果在某一列建立唯一索引，必须保证这列不能有重复数据。
唯一键索引是普通索引的一种，可以为空，如果指定 not null 则相当于主键索引。

创建普通索引

同样，唯一键索引也是建表时创建与建表后添加。

建表时创建索引：

代码语言：javascript复制

create table user3(
id int primary key,
name varchar(20) not null,
index(name)
);

建表后添加索引：

代码语言：javascript复制

alter table user3 add index(name);

代码语言：javascript复制

create index myindex on user3(name);

普通索引的特点：

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

创建复合索引

创建复合索引和创建主键索引、普通索引其实一样，只是在创建时指定多列而已，下面我们已创建普通复合索引为例。

建表时创建索引：

代码语言：javascript复制

create table user4(
id int primary key,
name varchar(20),
addr varchar(32),
index(name, addr)
);

建表后添加索引：

代码语言：javascript复制

alter table user4 add index(name, addr);

代码语言：javascript复制

create index myindex on user4(name, addr);

需要注意的是，在创建普通复合索引时，如果我们不指定索引名称，那么MySQL会自动以第一列的列名为索引名，所以我们可以看到在图一中，存在两个 key_name 为 name 的索引，但它们对应的 column_name 分别为 name 和 addr。从这里也可以看出，name 索引是由 name 和 addr 两个列构成的复合索引。

创建全文索引

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

全文索引的创建方式和普通索引一样，需要注意的是指明存储引擎为 MyISAM：

代码语言：javascript复制

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

我们可以插入一些数据来进行测试：

代码语言：javascript复制

INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');

注意：这里为了方便，每条记录的 body 部分我们只插入了少量的数据，实际上 body 字段中的数据可能很多，比如包含几K到几W个字符的文章等。

现在我们使用一般的where字句搜索body中含有 ‘database’ 的记录：

虽然成功查询出来了，但此次查询其实并没有使用到全文索引，我们可以用 explain 工具查看该 sql 语句的执行过程：

全文索引的正确使用方式其实是这样的：

代码语言：javascript复制

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

索引创建的原则：

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

2、查询索引

第一种方法： show keys from 表名; (注：查询时可以使用 G 选项来格式化显示)

第二种方法: show index from 表名;

第三种方法: desc 表名；(查询出来的信息比较简略)

3、删除索引

删除索引只能是建表后删除，且删除索引的操作和添加索引的操作是对应的：add/create -> drop；

第一种方法 – 删除主键索引： alter table 表名 drop primary key;
第二种方法 – 删除普通索引：alter table 表名 drop index 索引名； (索引名就是show keys from 表名中的 Key_name 字段)
第三种方法 – 删除普通索引：drop index 索引名 on 表名;

四、测试索引的效率

现在我们已经学完了索引，知道了索引最大的作用在于增加数据库表 CURD 操作的效率，但这毕竟只是别人这么说，我认为我们还是需要直观的见到索引带来的效率提升，才能真正的理解并接收它。

为了测试索引的效率，我找了一段存储过程的代码，它的作用是创建一个bit_index数据库，并在其中新建一个 EMP 员工表，然后随机生成800W条记录插入到员工表中，如下：

代码语言：javascript复制

drop database if exists `bit_index`;
create database if not exists `bit_index` default character set utf8;
use `bit_index`;

-- 构建一个8000000条记录的数据
-- 构建的海量表数据需要有差异性，所以使用存储过程来创建， 拷贝下面代码就可以了，暂时不用理解

-- 产生随机字符串
delimiter $$
create function rand_string(n INT)
returns varchar(255)
begin
declare chars_str varchar(100) default
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
set return_str =concat(return_str,substring(chars_str,floor(1 rand()*52),1));
set i = i   1;
end while;
return return_str;
end $$
delimiter ;


-- 产生随机数字
delimiter $$
create function rand_num( )
returns int(5)
begin
declare i int default 0;
set i = floor(10 rand()*500);
return i;
end $$
delimiter ;

-- 创建存储过程，向雇员表添加海量数据
delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i   1;
insert into EMP values ((start i)
,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
until i = max_num
end repeat;
commit;
end $$
delimiter ;

-- 雇员表
CREATE TABLE `EMP` (
  `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',
  `ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '雇员职位',
  `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',
  `hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',
  `comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',
  `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
);


-- 执行存储过程，添加8000000条记录
call insert_emp(100001, 8000000);

大家可以新建一个 index_data.sql 文件，然后将上面的代码 copy 进去，最后在 mysql 中使用 source 语句将其导入到数据库中。(由于插入的数据量非常大，这个过程可能会花费几分钟到十几分钟)