[MySQL] MySQL中的索引

🙋‍♂️ 作者：@Ggggggtm 🙋‍♂️

👀 专栏：MySQL 👀

💥 标题：MySQL中的索引💥

** ❣️ 寄语：与其忙着诉苦，不如低头赶路，奋路前行，终将遇到一番好风景 ❣️**

一、初识索引

1、1 索引的概念

在MySQL中，索引是一种数据结构，用于提高查询效率和加速数据检索。创建索引可以加快数据库中表的查询操作，减少查询所需的时间和资源消耗。但是天下没有免费的午餐，查询速度的提高是以插入、更新、删 除的速度为代价的，这些写操作，增加了大量的IO。所以它的价值，在于提高一个海量数据的检索速度。

常见的索引类型有如下几种：

• 主键索引（Primary Key Index）：用于唯一标识表中的每一行。一个表只能有一个主键索引。

• 唯一索引（Unique Index）：确保被索引的列中的值是唯一的。

• 普通索引（Normal Index）：最基本的索引类型，没有唯一性约束。

• 全文索引（Full-Text Index）：用于全文搜索，适用于大段文本的搜索。

  到这里你可能依然对索引的概念是一种模糊的状态，后文我们会详细解释。看会此篇文章再来回头看索引的概念，相信你就会理解了。

1、2 索引案例

怎么来证明索引可以很好的提高查询效率呢？我们个人在平常使用数据库时，并没有感觉到查询的速度有很慢的情况。但是在实际应用中，往往一张表中会包含了大量的数据。此时查询其中一条数据时，速度就会较慢。接下来我们先弄出来了一张数据量较大的表，该表包含八百万条数据。具体的构建过程如下：

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


--构建一个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 ;

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

注意：由于创建表的数据量过大，所以创建的过程中需要一定的时间。当创建完成时，不要查看表中所有的数据，因为数据量过大，且IO效率低。我们来看创建好后的表：

现在我们在此表中以员工工号来查询一个员工的数据，我们看看所用时间：

如上图所示，每条查询语句所用到的时间大概为4.5秒左右，且在查询的过程中明显能够感受到停顿。试想在实际应用中，查询一个用户的信息用了4秒时间，可以接受吗？当然不可以！下面我们创建一个索引，再来查询看看是否又效率提升！构建索引具体如下：

上述我们也提到了，索引本质上就是一种数据结构，当我们在给员工表中的工号建立索引时，实际上数据库底层就会以工号来构建特定的数据结构。由于表中的数据量较大，建立索引时也需要花费较长时间。这时我们再来以工号查询员工信息，具体如下：

这时候再查询时，发现速率大大的提升了。通过上图也能看到，耗费的时间小于0.01秒。那么问题来了：我们一直在说索引本质上是一种数据结构，那到底是什么数据结构呢？在不添加索引的情况下，效率慢在哪里呢？

二、认识磁盘

为了数据持久化，最终mysql中的数据是要存储到磁盘上的。我们前面也了解到了，在创建数据库和表时，就是在对应的目录下创建了文件夹和特定文件。所以我们很有必要来了解一下磁盘。

磁盘是计算机中的一个机械设备，相比于计算机其他电子元件，磁盘效率是比较低的，在加上IO本身的特征，可以知道，如何提交效率，是 MySQL 的一个重要话题。

2、1 磁盘结构

磁盘的整体结构如下图：

重要组成硬件解释：

1. 盘片：磁盘中最重要的组成部分之一。通常，磁盘中会有多个盘片叠放在一起，每个盘片都是由坚固且非磁性的材料制成，如铝合金或玻璃。盘片上覆盖着一个可磁化的薄膜，用来存储数据。
2. 磁头：位于磁盘中的磁头负责读写数据。每个盘片都有两个磁头，一个位于盘片的上表面，另一个位于下表面。这些磁头能够飞行在盘片上方，通过电磁感应读取和写入数据。
3. 主轴：主轴是磁盘驱动器中的一个旋转轴，它用来支撑盘片并使其高速旋转。主轴由电机驱动，通常转速在5400到15000转/分钟之间。
4. 永磁铁： 机械硬盘的存储方式与磁带比较类似，磁体具有记忆的功能，永磁铁是为了保证磁性的稳定。
5. 音圈马达： 硬盘读取数据的关键部位，主要作用是将存储在磁盘上的信息转换为电信号向外传输。
6. 空气滤波片： 过滤空气硬盘透气孔中进入的空气，保证硬盘内部清洁，同时还可以防止硬盘内部的零件氧化，确保硬盘安全使用。
7. 串行接口： 用来连接电脑与硬盘的接口，起到传输的作用。

在上图中我们看到盘片只有一片，实际上是有多片叠放在一起的。且每个盘片的两面都是可以存储数据的。具体如下图：

每个盘片又被划分为多个区域，且每个区域所能够存储容量大小是相同的，我们称该区域为扇区。具体如下图：

从上图可以看出来，在半径方向上，距离圆心越近，扇区越小，距离圆心越远，扇区越大那么，所有扇区都是默认512字节吗？目前是的，我们也这样认为。因为保证一个扇区多大，是由比特位密度决定的。不过最新的磁盘技术，已经慢慢的让扇区大小不同了，不过我们现在暂时不考虑。

从上图中也能看出，盘片表面划分为多个同心圆轨道，每个轨道称为一个磁道。实际上是每个磁道又被细分为扇区，用于存储数据。

数据库文件就是保存在磁盘中的一个个扇区中的，因此找到一个文件本质就是，在磁盘上找到保存该文件的所有扇区。而我们能够定位任何一个扇区，那么便能找到所有扇区，因为查找方式是一样的。那么怎么可以很快的定位到一个扇区呢？

我们先看如下图：

柱面是多盘磁盘，每盘都是双面，大小完全相等。那么同半径的磁道，整体上便构成了一个柱面 。每个盘面都有一个磁头，那么磁头和盘面的对应关系便是1对1的。

那么现在寻址的思路就很明确了：先确定柱面，在确定磁头。这下就确定在那个盘片上了。然后在找对应的扇区，就可以很好的找到对应的存储位置了。我们也称之为CHS寻址（柱面（Cylinder）- 磁头（Heads）- 扇区（Sector）寻址）。 在CHS寻址中，通过指定柱面号、磁头号和扇区号来确定数据的物理位置。通过这三个参数，计算机可以准确地访问磁盘上的数据。

不过实际系统软件使用的并不是 CHS （但是硬件是），而是 LBA ，一种线性地址，可以想象成虚拟地址与物理地址。系统将 LBA 地址最后会转化成为 CHS （这个转换过程由磁盘控制器负责执行，它根据磁盘的物理结构和磁盘参数表来进行适当的转换），交给磁盘去进行数据读取。

2、2 操作系统与磁盘的数据交互

我们在对mysql内部的数据等做操作(CURD)的时候，本质其实就是操作文件内容，那么文件必须先被打开。对文件内容做任何操作，都不是直接在磁盘设备上做操作的！通过冯诺依曼结构我们知道，CPU只与内存进行数据交互。这就意味着任何磁盘数据，在进程中要进行操作，本质都必须在内存中进行！那么在对磁盘数据进行操作时，一定会先将磁盘的数据加载到内存当中，由操作系统进行管理。

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

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

2、3 磁盘随机访问****与连续访问

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

连续访问： 本次IO所给出的扇区地址与上次IO给出的扇区地址是连续的，磁头很快就能找到目标扇区进行IO。

尽管两次IO是在同一时刻发出的，但如果它们请求的扇区地址相差很大，那也只能称为随机访问，因为连续访问中的连续指的是访问的扇区地址的连续，而不是访问时间的连续，由于连续访问不需要过多的定位，因此效率比较高。

2、4 MySQL与磁盘的数据交互

而 MySQL 作为一款应用软件，可以想象成一种特殊的文件系统。它有着更高的IO场景，所以，为了提高基本的IO效率， MySQL 进行IO的基本单位是 16KB。我们通常称这个基本单元为 Page。这里的 Page 与系统的 Page 并不相同，需要区分一下的。 在Linux下，MySQL分为客服端（mysql）与服务端（mysqld）。

• 上文中也提到了，在MySQL中进行的各种CRUD操作时，都需要先通过计算找到对应的操作位置，只要涉及计算就需要CPU参与，而冯诺依曼体系结构决定了CPU只能和内存打交道，因此为了便于CPU参与，就需要先将数据加载到内存当中。
• 那么在特定的时间内，MySQL中的数据一定是同时存在于磁盘和内存中的，当操作完内存数据后，再以特定的刷新策略将内存中的数据刷新到磁盘当中，这时MySQL和磁盘进行数据交互的基本单位就是Page。
• 为了更好的支持上述操作，MySQL服务器在启动的时候会预先申请一块内存空间来进行各种缓存，这块内存空间叫做Buffer Pool，后续磁盘中加载的数据就会保存在Buffer Pool中，刷新数据时也就是将Buffer Pool中的数据刷新到磁盘。
• 由于内核中是有内核缓冲区的，因此MySQL从磁盘读取数据时，需要先将数据从磁盘读取到内核缓冲区，再将数据从内核缓冲区读取到Buffer Pool，MySQL将数据刷新到磁盘时，同样需要先将数据从Buffer Pool刷新到内核缓冲区，再将数据从内核缓冲区刷新到磁盘。

实际上，mysqld并不直接与磁盘打交道，而是利用了中间的内核缓冲区。所谓MySQL 进行IO的基本单位是 16KB，我们可以理解为一次从Buffer Pool向内核缓冲区 读取/写入 的大小为16KB，然后再让内核缓冲区 读取/刷新 到磁盘上。

三、索引的理解

3、1 建立测试表

建表代码如下：

create table if not exists user (
    id int primary key, --一定要添加主键哦，只有这样才会默认生成主键索引
    age int not null,
    name varchar(16) not null
);

具体如下图：

我们在向表中插入一些数据，具体如下图：

发现插入数据时id值是乱序进行插入的，但是我们查询表中的信息发现是有序的！排序的工作是谁做的呢？为什么要进行排序呢？

3、2 *为何MySQL与磁盘IO*交互是 **Page

为何MySQL和磁盘进行IO交互的时候，要采用Page的方案进行交互呢?用多少，加载多少不香吗?

如上面的5条记录，如果MySQL要查找id=2的记录，第一次加载id=1，第二次加载id=2，一次一条记录，那么就需要2次IO。 如果要找id=5，那么就需要5次IO。 但如果这5条(或者更多)都被保存在一个Page中(16KB，能保存很多记录),那么第一次IO查找id=2的时候，整个Page会被加载到MySQL的Buffer Pool中，这里完成了一次IO。但是往后如果在查id=1,3,4,5等，完全不需要进行IO了，而是直接在内存中进行了。所以，就在单Page里面，大大减少了IO的次数。我们知道IO的效率是很慢的。

你怎么保证，用户一定下次找的数据，就在这个Page里面？我们不能严格保证，但是有很大概率，因为有局部性原理。什么是局部性原理？计算机的局部性原理（Locality Principle）是指在程序执行过程中，访问内存的趋势倾向于集中在某些特定的内存区域或数据块上。当一个数据被访问后，其附近的数据也很可能会被访问。比如，当程序访问某个内存地址时，它很可能会在接下来的指令中继续访问相邻的内存地址。这种局部性原理可以通过预取（Prefetching）和预读（Prefetching）等技术来优化内存访问效率。

往往IO效率低下的最主要矛盾不是IO单次数据量的大小，而是IO的次数。

3、3 理解Page

在MySQL中，不仅仅是只有一张表。大概率会同时管理着多张表数据。那么而要管理好这些表，就需要 先描述，在组织 。我们目前可以简单理解成一个个独立表是有一个或者多个Page构成的。那么MySQL将内存中的每一个Page都用一个结构体描述起来，然后再将各个结构体以双链表的形式组织起来，因此一个Page结构体内部既包含数据字段，也包含属性字段。我们用上述的测试数据来理解，如下图：

我们也知道，再插入数据时是无序的，为什么查询的时候就有序了呢？也就是为什么要进行排序。我们接着往下看。

3、3、1 页目录

我们知道，当一张表中的数据很多时，查询的速度就会变的很慢。本质上还是因为进行的遍历查询。那么有没有什么办法，能够加快我们的查询速度呢？我们在看《谭浩强C程序设计》这本书的时候，如果我们要看<指针章节>，找到该章节有两种做法：

1. 从头逐页的向后翻，直到找到目标内容（遍历，较慢）。

2. 通过书提供的目录，发现指针章节在234页(假设)，那么我们便直接翻到234页。同时，查找目录的方案，可以顺序找，不过因为目录肯定少，所以可以快速提高定位。

3. 本质上，书中的目录，是多花了纸张的，但是却提高了效率。

4. 所以，目录，是一种“空间换时间的做法”

   通过上述例子，我们就想到：一个Page中能不能也引入目录呢？答案是可以的。MySQL也正是这么做的。具体如下图：

当数据量很大的时候，目录就起到的很好的过滤无用数据的作用。那么当前，在一个Page内部，我们引入了目录。比如，我们要查找id=4记录，之前必须线性遍历4次，才能拿到结

果。现在直接通过目录2[3]，直接进行定位新的起始位置，提高了效率。当我们在添加页内目录时，Page内部能够保存的数据记录变少了，所以
在Page内部引入页内目录本质是一种空间换时间的做法，就像给书添加目录需要花费更多的纸张一样。

但现在或许你就理解了为什么要对插入的数据进行排序了。就是为了能够更好的引入目录，提高查询的速度。因为我们在创建表的时候，设置了id为主键，所以MySQL会以主键为索引进行排序的。

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

那么当我们线性遍历在那个Page当中时，注意访问Page时，就需要将Page从磁盘加载到内存当中，这就意味着需要多次IO，效率也是比较低的。怎么解决多个Page效率低的问题呢？

3、3、2 多个Page

能不能在多个Page之上再次引入目录呢？答案是可以的。解决方案，其实就是我们之前的思路，给Page也带上目录。

1. 使用一个目录项来指向某一页，而这个目录项存放的就是将要指向的页中存放的最小数据的键值。
2. 和页内目录不同的地方在于，这种目录管理的级别是页，而页内目录管理的级别是行。
3. 其中，每个目录项的构成是：键值+指针。

具体如下图：

随着数据量不断增大，Page变得越来越多，这时一个页目录无法管理所有的Page，这时就需要更多个的页目录。这些页目录也是一个个的Page结构体，只不过这些Page结构体中存放的不是数据记录，而是各个Page的目录信息。

那么好了，当数据量更多的时候，存在着很多的用Page存储目录时，我们只需要在该Page之上再次创建Page目录进行管理即可。具体如下图：

我们上图中，最终构建出来的就是一棵B+树。到这里，我们已经给我们的表user构建完了主键索引。我们接下来再来详细的分析和总结一些问题。

1. 索引本质上就是一种数据结构，那么这个数据结构就是指的B+树。
2. 当我们创建一个表时，如果该表中有主键，那么MySQL在底层就会自动将这张表中的的数据以B+树的形式组织起来，保存在Buffer Pool当中，当我们查询数据时就可以通过查询这棵B+树来提高查询效率。
3. 那要是没有主键呢？MySQL会自动形成隐藏主键，同样会构建成B+树将数据存储起来。只不过我们在查询时，并不是按照主键进行查询的，所以本质上还是进行的线性遍历。
4. 我们上述讲述的B+树存储结构中，只有在叶子节点中存储数据。
5. 一个Page的大小为16KB，可存储大量数据。当一个Page中只存储目录项时，可想而知能够管理多大的数据。所以B+树整体是一颗矮胖的多叉树。查找的时候，自定向下找，只需要加载部分目录页到内存（并不需要加载整颗B+树，最开始只需要根节点即可），即可完成算法的整个查找过程，大大减少了IO次数。

相信到这里，你就会对索引有一个很好的了解。那么索引能不能采用其他的数据结构呢？

3、4 索引所能够采用的数据结构

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

• 链表：查找时是线性遍历，效率太低。
• 普通二叉搜索树：可能退化成线性结构，这时查找还是线性遍历。
• AVL树和红黑树：虽然保证了二叉树是绝对或近似平衡的，不会退化成线性结构，但AVL树和红黑树都是二叉树结构，这就意味着树的层高会比较高，而查询数据时都是从根结点开始向下进行查找的，这也就意味着在查询过程中需要遍历更多结点，如果这些结点还没有被加载到Buffer Pool中，这时就需要进行更多次的IO操作，所以最终没有选择其作为索引结构。
• 哈希表：官方的索引实现方式中MySQL是支持HASH的，只不过InnoDB和MyISAM存储引擎并不支持。哈希表的优点就是它的时间复杂度是 O ( 1 ) 的，但哈希表也有一个缺点就是不利于进行数据的范围查找。

3、5 B树与B+树

MySQL的索引采用了B+树，为什么不采用B树呢？

• 普通B树中的所有结点中都同时包括索引信息和数据信息，由于一个Page的大小是固定的，因此非叶子结点中如果包含了数据信息，那么这些结点中能够存储的索引信息一定会变少，这时这棵树形结构一定会变得相对B+树更高更瘦，当查询数据时就可能需要与磁盘进行更多次的IO操作。

• 普通B树中的各个叶子结点之间没有连接起来，这将不利于进行数据的范围查找，需要多次遍历，这就意味着会有更多的IO操作。而B+树的各个叶子结点之间是连接起来的，当我们进行范围查找时，直接先找到第一个数据然后继续向后遍历找到之后的数据即可，因此将各个叶子结点连接起来更有利于进行数据的范围查找。

  下图是B树的结构，可以结合的理解一下：

3、6 聚簇索引与非聚簇索引

聚簇索引（Clustered Index）： 聚簇索引是将表的数据按照索引键的顺序物理地存储在磁盘上。每张表只能有一个聚簇索引，因为它决定了表中数据的物理存储方式。当创建聚簇索引时，表的数据会根据索引键的顺序进行重新排序，并且数据行的物理位置与其在索引键中的顺序一致。

我们上述讲解索引的数据结构B+树时，所解释索引的类型就是聚簇索引。最明显的特点就是数据存储在B+树的叶子节点当中。在前面文章我们也讲到过，再创建一张表时默认的存储引擎是InnoDB。具体如下图：

那我们大概也就知道了，InnoDB存储引擎的索引结构采用的是B+树，同时索引类型为聚簇索引。

非聚簇索引（Non-clustered Index）： 非聚簇索引是在单独的数据结构中存储索引键和对应的行指针，而不是存储实际的数据行。一个表可以有多个非聚簇索引。非聚簇索引通过维护一个索引树，加速数据查询的过程。查询操作首先定位到索引树上的特定值，然后使用该值来获取对应的行指针，最后再通过行指针访问实际的数据行。

MyISAM存储引擎的索引类型就是采用的非聚簇索引。其索引的结构也是B+树。最大的特点就是B+树中不再存储有效数据，而是存储的指针，该指针指向的就是数据所在的地址。下图为MyISAM存储引擎的主键索引结构，其中Col1为主键。如下：

当然， MySQL 除了默认会建立主键索引外，我们用户也有可能建立按照其他列信息建立的索引，一般这种索引可以叫做辅助（普通）索引。

MyISAM存储引擎的普通索引采用的也是B+树结构，与主键索引唯一不同的地方就是普通索引的B+树中的键值可以重复。下图为MyISAM存储引擎的普通索引结构，其中Col2为索引列，和主键索引没有差：

有一个问题：我们知道创建索引时，本质上就是以我们所选中的列创建对用的数据结构，比如主键索引就会以主键为节点的数据，来创建B+树。但是我们也能够创建普通索引，这也就意味着一张表会存在多个索引和多个B+树。有多个B+树的同时，那么会不会每个B+树中都存储了数据信息呢？也就是会不会造成空间的浪费呢？首先，MyISAM存储引擎采用的是非聚簇索引，B+树中存储的是指针，所以并不会造成空间浪费。那InnoDB呢？我们以上表中的 Col3 建立对应的辅助索引如下图：

可以看到，InnoDB 的非主键索引中叶子节点并没有数据，而只有对应记录的key值。

所以通过辅助（普通）索引，找到目标记录，需要两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。这种过程，就叫做回表查询。

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

我们接下来观察一下InnoDB存储引擎和MyISAM存储引擎两者所形成的文件的区别。

我们现在创建一个表时，指定其存储引擎为InnoDB，我们观察一下其形成的文件。建表如下：

我们在 \var\lib\mysql目录下查看其形成的文件。具体如下：

我们再来创建一个表，该表的存储引擎为MyISAM，具体建表如下图：

在 \var\lib\mysql目录下形成对应的文件。具体如下：

通过上述的对比我们来总结一下：

1. 采用 InnoDB 和 MyISAM 存储引擎创建表时都会生成xxx.frm文件，该文件中存储的是表结构相关的信息。
2. 当创建一个存储引擎为InnoDB表时，会生成一个与表名相同的.ibd文件，其中包含了数据页和索引页。这就是聚簇索引，索引和数据是存储在同一个文件中的。
3. InnoDB是MySQL的默认存储引擎，它支持事务处理和行级锁定。InnoDB的聚簇索引是通过B+树数据结构实现的，将主键索引与实际数据存储在一起。
4. InnoDB的聚簇索引是按照主键顺序进行存储的，这样可以提高查询效率。
5. 当创建一个MyISAM表时，会生成与表名相同的.MYI和.MYD文件，分别存储索引和数据。这就是所谓的非聚簇索引，索引和数据是分开存储的。
6. MyISAM是MySQL的另一个存储引擎，不支持事务处理和行级锁定。MyISAM的非聚簇索引也是通过B+树数据结构实现的，但是将索引和实际数据分开存储。

四、索引操作

4、1 索引的创建

4、1、1 主键索引的创建

第一种方式：在创建表的时候，直接在字段名后指定 primary key。具体如下：

第二种方式：在创建表的最后，指定某列或某几列为主键索引。具体如下图：

第三种方式：创建表以后再添加主。具体如下图：

主键索引的特点：

1. 一个表中，最多有一个主键索引，当然可以使复合主键。
2. 主键索引的效率高（主键不可重复）。
3. 创建主键索引的列，它的值不能为null，且不能重复。
4. 主键索引的列基本上是int。

4、1、2 唯一索引的创建

** **第一种方式：在表定义时，在某列后直接指定unique唯一属性。具体如下：

第二种方式：创建表时，在表的后面指定某列或某几列为unique 。如下：

第三种方式：创建表以后再添加唯一键。具体如下：

唯一索引的特点：

1. 一个表中，可以有多个唯一索引。
2. 查询效率高。
3. 如果在某一列建立唯一索引，必须保证这列不能有重复数据，可以为NULL。
4. 如果一个唯一索引上指定not null，等价于主键索引。

4、1、3 创建普通索引

第一种方式：在表的定义最后，指定某列为索引。具体如下图：

第二种方式：创建完表以后指定某列为普通索引。如下：

第三种方式：创建一个索引名为 idx_name 的索引，相当于我们可以给索引指定名称（下文会解释）。具体如下：

普通索引的特点：

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

4、1、4 全文索引的创建

** **当对文章字段或有大量文字的字段进行检索时，会使用到全文索引。MySQL提供全文索引机制，但是有要求：你需要确保你的表使用的是支持全文索引的存储引擎，所以可选存储引擎是MyISAM，而且默认的全文索引支持英文，不支持中文。如果对中文进行全文检索，可以使用sphinx的中文版(coreseek)。全文索引的创建如下：

上图是通过fulltext给title和body列创建全文索引。接下来我们再插入一下数据，如下图：

如果要查询哪些文章中包含database关键字，当然我们可以通过模糊匹配进行查找。如下：

但是一旦数据量过大时，最终是需要进行线性遍历的，查询速度会很慢。我们可以使用可以用explain工具看一下，是否使用到索引。如下图：

上图中的 key 为NULL。标示并没有用到任何索引。那么如何使用全文索引呢？使用全文索引进行查询： 一旦创建了全文索引，你可以使用MATCH AGAINST语句来执行全文搜索查询。这个语句将会返回与搜索条件匹配的记录，并根据相关度进行排序。使用模板如下：

SELECT * FROM articles WHERE MATCH(title, content) AGAINST('keyword');

具体使用例子如下：

我们再次使用explain工具看一下，具体如下：

综上我们来总结一下：

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

4、2 查询索引

第一种方法：****show keys from 表名\G。上述在创建普通索引时，我们提到可以给索引命名。那么我们看一下user10表中的索引。user10的表结构如下：

查询结果具体如下：

下面是对查询结果中的属性进行解释：

1. Table: 表示索引所属的表名。
2. Non_unique: 表示索引是否是唯一索引。如果值为0，表示索引是唯一索引；如果值为1，表示索引不是唯一索引。注意，这里的唯一索引指的是在索引列上的值是否允许重复。
3. Key_name: 表示索引的名称。
4. Seq_in_index: 表示索引中列的顺序。如果一个索引由多个列构成，在该属性中会显示每个列的顺序号。
5. Column_name: 表示索引中的列名。
6. Collation: 表示列排序规则。例如，utf8_general_ci表示不区分大小写的Unicode字符集。
7. Cardinality: 表示索引中唯一值的数量估计。该值用于优化查询语句，较大的值通常意味着更好的性能。
8. Sub_part: 表示索引列的部分长度。如果整个列被索引，该值为NULL。
9. Packed: 表示如何存储索引。如果为NULL，表示没有使用特殊的存储方式。
10. Null: 表示索引列是否允许NULL值。如果值为YES，表示列允许为NULL；如果值为NO，则表示列不允许为NULL。
11. Index_type: 表示索引的类型。常见的索引类型包括BTREE（B+树索引）和HASH（哈希索引）等。
12. Comment: 提供关于索引的额外注释信息。

**第二种方法：show index from 表名; **具体如下图：

第三种方式：desc 表名；SQL查询（信息比较简略），比如查询articles表中的索引信息。如下：

4、3 删除索引

第一种方法-删除主键索引： alter table 表名 drop primary key****。具体如下图：

由于主键只有一个，所以我们在删除的时候并不需要指定其列名。

**第二种方法-其他索引的删除： alter table 表名 drop index *索引名。注意：*索引名就是show keys from **表名中的Key_name 字段。具体例子如下：

**第三种方法方法： drop index 索引名 on **表。具体如下图：

4、4 索引创建原则

索引创建的原则如下：

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