[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 磁盘结构

磁盘的整体结构如下图：

重要组成硬件解释：

盘片：磁盘中最重要的组成部分之一。通常，磁盘中会有多个盘片叠放在一起，每个盘片都是由坚固且非磁性的材料制成，如铝合金或玻璃。盘片上覆盖着一个可磁化的薄膜，用来存储数据。

磁头：位于磁盘中的磁头负责读写数据。每个盘片都有两个磁头，一个位于盘片的上表面，另一个位于下表面。这些磁头能够飞行在盘片上方，通过电磁感应读取和写入数据。

主轴：主轴是磁盘驱动器中的一个旋转轴，它用来支撑盘片并使其高速旋转。主轴由电机驱动，通常转速在5400到15000转/分钟之间。

永磁铁：机械硬盘的存储方式与磁带比较类似，磁体具有记忆的功能，永磁铁是为了保证磁性的稳定。
音圈马达：硬盘读取数据的关键部位，主要作用是将存储在磁盘上的信息转换为电信号向外传输。
空气滤波片：过滤空气硬盘透气孔中进入的空气，保证硬盘内部清洁，同时还可以防止硬盘内部的零件氧化，确保硬盘安全使用。
串行接口：用来连接电脑与硬盘的接口，起到传输的作用。

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

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

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

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

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

我们先看如下图：

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

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

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

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

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

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

物理内存实际是被划分成一个个4KB大小的页框的，磁盘上的数据也会被划分成一个个4KB大小的页帧，因此操作系统与磁盘以4KB为单位进行IO交互，就能提高数据加载和保存的效率。
操作系统与磁盘进行IO交互时，如果直接以扇区的大小作为IO的基本单位，那么这时系统的IO代码和硬件就是强相关的，将来当硬件的扇区大小发生变化时就需要对应修改操作系统的IO代码。主要实现了OS与磁盘的解耦。
此外，以扇区的大小作为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程序设计》这本书的时候，如果我们要看<指针章节>，找到该章节有两种做法：

从头逐页的向后翻，直到找到目标内容（遍历，较慢）。
通过书提供的目录，发现指针章节在234页(假设)，那么我们便直接翻到234页。同时，查找目录的方案，可以顺序找，不过因为目录肯定少，所以可以快速提高定位。
本质上，书中的目录，是多花了纸张的，但是却提高了效率。
所以，目录，是一种“空间换时间的做法”

通过上述例子，我们就想到：一个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也带上目录。

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

具体如下图：

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

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

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

索引本质上就是一种数据结构，那么这个数据结构就是指的B+树。
当我们创建一个表时，如果该表中有主键，那么MySQL在底层就会自动将这张表中的的数据以B+树的形式组织起来，保存在Buffer Pool当中，当我们查询数据时就可以通过查询这棵B+树来提高查询效率。
那要是没有主键呢？MySQL会自动形成隐藏主键，同样会构建成B+树将数据存储起来。只不过我们在查询时，并不是按照主键进行查询的，所以本质上还是进行的线性遍历。
我们上述讲述的B+树存储结构中，只有在叶子节点中存储数据。
一个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目录下形成对应的文件。具体如下：

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

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

四、索引操作

4、1 索引的创建

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

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

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

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

主键索引的特点：

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

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

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

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

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

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

4、1、3 创建普通索引

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

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

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

普通索引的特点：

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

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的表结构如下：

查询结果具体如下：

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

Table: 表示索引所属的表名。

Non_unique: 表示索引是否是唯一索引。如果值为0，表示索引是唯一索引；如果值为1，表示索引不是唯一索引。注意，这里的唯一索引指的是在索引列上的值是否允许重复。

Key_name: 表示索引的名称。

Seq_in_index: 表示索引中列的顺序。如果一个索引由多个列构成，在该属性中会显示每个列的顺序号。

Column_name: 表示索引中的列名。

Collation: 表示列排序规则。例如，utf8_general_ci表示不区分大小写的Unicode字符集。

Cardinality: 表示索引中唯一值的数量估计。该值用于优化查询语句，较大的值通常意味着更好的性能。

Sub_part: 表示索引列的部分长度。如果整个列被索引，该值为NULL。

Packed: 表示如何存储索引。如果为NULL，表示没有使用特殊的存储方式。

Null: 表示索引列是否允许NULL值。如果值为YES，表示列允许为NULL；如果值为NO，则表示列不允许为NULL。

Index_type: 表示索引的类型。常见的索引类型包括BTREE（B+树索引）和HASH（哈希索引）等。

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子句中的字段不应该创建索引。

上一篇：Spring过滤器和拦截器的区别

下一篇：PHP Swoole的基本用法