前言
你是否有过这样的 “崩溃时刻”？
面对一张百万级甚至千万级数据的表，执行一条简单的查询，却要盯着加载动画等上好几秒；业务高峰时，数据库因大量慢查询拖垮整个系统，用户疯狂吐槽、领导频频追问，你却对着 SQL 和数据表干着急，只知道 “索引能优化”，却根本搞不清怎么用才有效……
如果这些场景让你感同身受，那数据库索引绝对是你必须掌握的 “性能救命稻草”！
但很多人对索引的认知，还停留在 “机械执行建索引操作” 的层面 —— 可你真的懂它吗？
为什么有的索引让查询 “快如闪电”，有的却 “形同虚设”？磁盘的存储、读写逻辑，和索引原理究竟有什么深层关联？支撑索引高效工作的 B + 树，到底是怎样的结构？聚簇索引和非聚簇索引差异在哪？创建索引时，“主键、唯一、普通、全文” 这些类型又该怎么选、怎么用？
别慌，这篇内容会带你从底层到实战，彻底吃透索引：先从 “磁盘如何存储与交互” 讲起（这是理解索引的关键底层逻辑），再层层深入到 “单个 Page、多个 Page 的组织方式”“B + 树的核心设计”，接着对比 “聚簇 / 非聚簇索引” 的本质区别，最后手把手教你各类索引的创建规则与实战操作……
让你不仅知道 “要建索引”，更明白 “索引为什么能优化”“怎么建才真正有用”，从此告别数据库查询的 “性能焦虑”，真正掌握撬动数据效率的核心密码～
一. 索引必要性
先通过一个查找看一看索引的作用：
上述是再数据库中进行查找的结果，第一次查找没有建立索引，查找时间是4秒多，而第二次建立索引之后查找时间基本没有了。
可以现象一下，程序员在MySQL进行数据查找是非常频繁的，而如果每一次进行查找的时候都需要等几秒种，这是无法容忍的。
因此通过上图我们也能够直观的感受到MySQL索引的作用：就是实现对数据的高效查找。
下面我们将详细解释索引是如何做到的，他是怎么让查找的效率得以指数级别提升的。
二. 理解外设——磁盘
关于外设，我已经写过一篇详细的文章，可以跳转操作系统如何管理外设——磁盘去看看，此处对外设仅做仅作部分介绍。
MySQL给用户提供了数据存储的功能，而MySQL只是负责数据管理的，并没有存储的功能，MySQL只不过是将用户的指令作用到磁盘上而已，当用户要新增数据的时候，MySQL就按照特定的格式向磁盘上进行写入，删除修改也是如此。
因此在了解MySQL的底层机制之前，学习以下磁盘的结构是很有必要的。
磁盘是电脑上的唯一机械设备，磁盘是永久性介质，掉电数据不会丢失。
磁盘主要分为六个部分组成：马达，盘面，磁头，磁力臂，永磁铁，控制电路。
上图也可以看到，盘面其实不止一个，有多个盘面，每个盘面上都记录着数据，每个盘面都搭配一个磁头。
磁盘是如何读取，写入数据的
磁盘上的数据都是0，1序列，计算机只能读懂0和1。可以将盘面理解为由上亿个小吸铁石组成的，而因为每个小吸铁石都有南北极之分，就可以用南极表示0，用北极表示1的方式来存储0，1序列；而通过电池脉冲的方式可以将南北逆置，进而实现0，1序列的写入。
表示0和1的方式有很多，磁盘选取的是南北极的方式，其他存储介质就不一定了。
磁盘要如何对数据进行读取呢？？？数据都存储在盘面上，那么怎么从盘面上读取数据呢？？？
盘面的中间部分有马达可以带动盘面进行高速旋转，让磁头可以移动到一整个环的所有位置，磁力臂会带动磁头左右移动，让磁头能移动到盘面的所有位置。磁头并没有与盘面直接接触，但是磁头可以通过一定的方法获取盘面上的南北极信息，进而转换为0，1序列来实现数据的读取。
磁盘的存储构成
- 扇区：磁盘访问时最基本的单位是扇区，扇区的大小一般是512字节（目前也有4kb的），也就是说从磁盘上拿或写时最少要512字节。
- 磁道：以主轴为圆心的同心圆，就是磁道。可以理解为由扇区组成的圆环；
- 柱面：因为盘面有一组，所以对于半径相同的同心圆也有一组，其整体被称为柱面。是由多个半径相同的磁道组成的；
- 盘面：由所有磁道组成的。
因为磁盘访问的最基本的单位是扇区，所以在先磁盘中写入数据的时候，第一件事就是定位扇区，如何定位扇区呢？？？
- 确定柱面Cylinder：通过磁力臂带动磁头左右移动就可以定位要当问哪一个磁道；
- 确定磁头Header：用磁头来确定在哪一个盘面上，因为磁头较少，所以可以对磁头进行编号，这样通过编号与磁头一一对应，就可以确定磁头；
- 确定扇区Sector：通过主轴带动盘面旋转，使得磁头可以移动到磁道的所有位置，使得可以确定具体扇区。
这种确定位置的方式被称为：CHS寻址（Cylinder-Header-Sector，柱面，磁头，扇区）。
因为在进行数据的查找的时候需移动盘面和磁头，所以在进行数据的存储的时候，磁盘的设计者应该有意识的将有关的数据存储的更加集中，来减少数据的查找时间，进而提高效率。
磁盘的逻辑结构
在上面我们谈到了磁盘的存储结构，那操作系统是如何理解磁盘的呢？或者操作系统是以什么角度看待磁盘的呢？？？这就不得不对磁盘的结构进行抽象了。
我们可以将每一个磁道展开或将其拉成直的，如下图：
这不就是一维数组嘛！！！
而每一个同心圆的磁道又可以构成柱面，如下图：
这不就是二维数组嘛！！！
再有多个柱面就可以形成磁盘的所有盘面了，如下图：
最终可以使用三维数组来描述磁盘。
再将上面的逻辑地址转化为线性地址就成了：
现在就成功的将磁盘抽象为了一个以扇区为基本单位的数组。
所以如果现在对每个位置进行编号，不就是的每个扇区都有了自己的地址位置。我们称这种地址叫做LBA地址，该地址是线性地址，是在依次递增的，与CHS地址天差地别。
操作系统通过LBA地址对确定磁盘上的具体空间位置的。
操作系统使用LBA地址，而磁盘上使用的是CHS地址，那么这两种地址之间必定需要进行转换，如何进行转换？？？
数组的基本单位是扇区，而磁盘的基本单位也是扇区，所以：
- 如果知道一个磁盘上又多少个扇区就可以确定LBA地址对应的位置找哪一个扇区了：扇区编号即磁头的编号Header=LBA地址/(一个盘面上扇区的个数)；
- 如果知道一个磁道上的扇区的个数，就能知道在哪一个磁道了：磁道编号=LBA地址%(一个磁盘上扇区的个数)/(一个磁道上扇区的个数)；
- 确定磁道之后，那么剩余的地址就是扇区在磁道上的具体位置：扇区编号=LBA地址%(一个磁盘上的扇区个数)%(一个磁道上扇区的个数)；
通过以上三步就能实现，将LBA地址先CHS地址的转换了。
补充：磁道的长度是不同的，扇区的大小是一样的，那么怎么保证每一个磁道上扇区的个数是一样的？？？
每一个磁道的周长不一样，但是可以通过控制每一个磁道存放0，1序列的密度来实现每一个磁道上扇区个数一样。现在的技术也可以实现对不同大小磁道的定位，只不过使用的算法不同而已。
2.4 MySQL与磁盘进行交互
我们在向磁盘上进行读取的时候，依次依次是读取一个扇区的大小，也就是512字节吗？？？
答案：并不是；
- 如果操作系统按照外设提供的基本单位进行交互，就会使得操作系统与外设强相关，即如果外设改变了操作系统内部也要进行该表才行；
- 但是IO才512字节，太少了。IO单位越小，读取相同数据的时候，进行的IO次数就越多，效率越低；
操作系统对外设进行读取的时候，是以块大小block进行读取的，基本单位是4KB。
操作系统一次性从外设上拿4KB，即使要读取的数据比该单位小也是如此。因为局部性原理，操作系统认为用户要读取该数据，也又可以要读取其周围的数据，所以提前将数据拿上来，下一次用户真要用周围的数据的话，就不用再去磁盘上读取了。
MySQL与磁盘IO的时候也是采用4KB吗？？？
MySQL是一种应用程序，是专门为了进行数据管理的，所以MySQL中会有频繁的数据读取操作，因此MySQL为了进一步提高IO的效率，MySQL进行IO的基本单位是16KB，该基本单位被称为page.
通过select global status like 'innodb_page_size'也可以进行查看：
上图的基本单位是字节，转化过来就是16KB了。
2.5 总结
-
MySQL中的数据文件是以page为单位保存到磁盘上的； -
MySQL在进行CURD操作的时候要将数据从磁盘上读取到内存中，因此降低与磁盘IO的次数是提高MySQL读取效率的重要因素； - 在MySQL服务器运行时，其内部也会申请一块空间
Buffer Poll的大内存用来进行各种数据的缓存。
三. 索引的理解
3.1 引入
下面先通过一个实现来引出索引的本质：
先创建一张成员表：
依次向其中进行插入数据：
可以看到上述打印出来的数据顺序与我们插入的数据是一样的。
下面我们将id添加成主键，再打印一下数据表看看内部的数据：
可以明显的看到，在添加完主键之后，再进行查数据，这些数据就按照id的顺序依次进行排列了。
这是为什么呢？为什么刚刚还在说索引，现在又谈到主键了？？？
下面就开始解释索引到底是如何实现的，通过下面的就是，你一定就知道上面两个问题的答案了。
MySQL内部一定存在着大量的表，这也就意味着一个16KB的page肯定不够用，MySQL中一定有多个page，那么MySQL就需要对这些page进行管理了，如何管理：先描述，再组织。
3.2 理解单个Page
由于 InnoDB 的页是直接映射到磁盘的二进制结构，源码中并未用传统的 C 结构体显式定义所有字段，而是通过宏定义偏移量来描述各部分的位置（避免内存对齐问题）。
所以此处为了方便表示，我们用 C 结构体模拟页的布局（仅示意，实际无此结构体）：
typedef struct {
// 文件头（38字节）
uint32_t fil_page_checksum; /* 对应FIL_PAGE_SPACE_OR_CHKSUM */
uint32_t fil_page_offset; /* 页号 */
uint32_t fil_page_prev; /* 上一页指针 */
uint32_t fil_page_next; /* 下一页指针 */
// ... 省略LSN、类型等字段 ...
// 页头（56字节）
uint16_t page_n_dir_slots; /* 槽位数量 */
uint16_t page_heap_top; /* 空闲空间起始 */
uint16_t page_n_heap; /* 记录总数 */
// ... 省略空闲链表、事务ID等字段 ...
// 用户记录（柔性数组，实际从PAGE_HEADER+56开始）
uint8_t records[]; /* 实际数据记录 */
} page_t;
逻辑示意图如下：
一个Page是16KB，在一个Page中存储着多行数据，这些数据依次排列。
思考：如何在一个Page中快速定位要找的数据位置？？？
我们在阅读书的时候，是如何快速找到我们向看的章节的？？？我们不会一页一页的翻，而是看一下目录就知道我们要看的章节位置，那么在Page中我们是不是也可以采用这种方式呢？？
是的，我们确实可以；但是在编写目录之前是不是需要先对书籍的页码进行排序，在Page中就是依据指定的列进行排序。
因此此时我们就理解了，为什么在添加主键的时候数据会进行排序，实际上主键也是一种索引，通过主键对数据进行排序，形成目录，来实现快速查找。
一个表中有可能存有大量的数据，因此可能存在着大量的Page，那么在如此之多的Page中如何进行定位呢？？？
3.3 理解多个Page
上面的多条Page通过双指针的方式进行相连。
如何实现这些
Page之间的快速查找呢？？？
MySQL中使用的方法是：使用一个目录页，与数据页区分开来，示意图如下：
这种目录页是对每一个普通页进行管理的，其内部不存储用户的数据，而是存储一个key-value的结构：key是普通页中最小的那一个数据编号，即对应上面例子的最小主键值，而value则是对应普通页对象的位置。
通过这种方式就能够实现在普通页之间的快速查找了。
假设在64位机器下，一个地址8字节，一个主键值4字节，则16KB大约可以存储： 16 ∗ 1024 / ( 8 + 4 ) = 1365 16*1024/(8+4)=1365 16∗1024/(8+4)=1365 个普通页，这已经很多人。
- 如果上述的空间依旧是不够，有多个目录页，这些目录页也需要进行管理，我们可以在上面继续添加二级目录页来实现管理。
3.4 B+索引
以上整个结构实际上就是B+树，MySQL选择了B+树来实现其数据的快速查找。
- 通过叶子节点保存数据，非叶子节点保存目录的方式，让叶子节点的数量可以最大化，可以存储更多的叶子，对应的也就是更多的数据；
- 叶子节点全部链表进行连接，使得如果要进行范围查找，就不需要从顶部往下重复遍历了.
通过上述的(1)我们可以肯定，这棵树一定是矮胖的形状，这就意味着它途径的非叶子节点更少，找到目标Page需要更少的非叶子节点Page，将Page从磁盘中读取上来的次数也就越少，IO的效率就更高。
除了B+树还有B树，与B+树相比，B树有很多劣势：
- B树的非叶子节点也存储用户的数据，这就意味着，保存的目录项就变少了，B树要比B+树更高更瘦，所以整体上查找效率不如B+树；
- B树的叶子节点没有使用链表进行相连，其进行范围查找的效率没有B+树高。
在MySQL中也有hash的结构，只不过hash也不能进行快速的范围查找。
-
一般我们在对数据库进行CURD操作的时候，本质就是在对这棵树进行修改。
-
补充：当我们没有设置主键的时时候，
MySQL也会形成默认主键，该默认主键通常是列中的一个唯一键，如果没有MySQL就会以DB_ROW_ID作为其默认主键。
DB_ROW_ID本质上是一个隐藏的自增列，仅在表未显式定义主键（PRIMARY KEY）且无符合条件的非空唯一索引（UNIQUE NOT NULL）时自动生成，用于作为聚簇索引的主键。
因为其一直都是递增的，所以不需要进行额外的操作就是有序的，但是该列并不是我们希望的顺序，因此其通常会导致查找很耗时。
3.5 总结
- Page分为两种：普通页和目录页，目录页用来对普通页进行管理，实现对普通页的快速定位；普通页中也有目录，能够实现在一个普通页中快速定位数据的位置；
- 因为B+树形状是矮胖的，所以只需要从磁盘上加载少量的页到内存中就能够找到目标Page的位置，减少了IO的次数，进而提升了查找的效率。
四. 聚簇索引VS非聚簇索引
- 聚簇索引：将数据的存储与B+树索引进行结合，即数据存储在B+树之中的结构被称为聚簇索引，在上面我们举例的索引结构都属于聚簇索引，
Innodb所采用的方式就是上述的结构，即聚簇索引。 - 非聚簇索引：将B+树的结构与数据的存储进行分离，分开存放，这种结构被称为非聚簇索引，
MyISAM采取的方式就是非聚簇索引。
以下是一个非聚簇索引的示意图：
每个叶子节点存储的key-value结构变为：主键索引-对应列的地址。
通过上述的描述，我们都能够清楚的认识到，其实索引就是数据结构，通过这种数据结构能够实现我们在表中进行快速高效的查找。
- 在
MySQL中也是支持辅助索引的，我们也可以建立除主键索引以外的其他索引；辅助索引本质上就是再先数据库中增加一个B+树结构嘛。 -
Innodb在构建辅助索引的时候，其key-value结构式：辅助索引-主键索引。也就是说Innodb通过辅助索引进行查找的时候分为两步：（1）先通过辅助索引找到主键索引；（2）再通过主键索引找到对应的数据。
五. 索引操作
通过上面的理论知识，我们已经充分理解了索引式如何实现的，以及其高效查找的背后底层逻辑；下面让我们学习一下如何建立索引吧。
5.1 创建主键索引
两种方式：
-
在建表的时候就创建主键:
-
表创建完成后，在指明主键：
5.2 创建唯一键索引
与上面的主键索引的建立方式一样，也是两种：
- 在建表的时候，就创建唯一键；
- 在建表完成后，再创建唯一键。
因为操作与上面一摸一样，就不再进行演示了。
5.3 创建普通索引
两种方式：
-
在建表的时候，就指定普通键：
-
在完成建表之后，再指定：
5.4 索引创建规则
在创建索引的时候，并不是所有数据都适合作为索引的，创建索引的时候也有一些规则：
- 频繁作为查找条件的字段；
- 唯一性比较高的字段，查找的数据尽量不要有太多重复；
- 不会频繁进行更新的字段；
- 经常出现在
where子句中要进行筛选的字段。
5.5 全文索引
当对文章字段或有大量文字的字段进行检索时，会使用到全文索引。MySQL提供全文索引机制，但是有要求，要求表的存储引擎必须是MyISAM，而且默认的全文索引支持英文，不支持中文。如果对中文进行全文检索，可以使用sphinx的中文版(coreseek)。
比如现在有一个数据库，用于存放各个数据属性和内容：
此时先表中插入一些数据：
如果我们希望进行筛选：从body中筛选出那一行中包含mysql这个单词时。如果可以直接使用like进行筛选：
但是当文本很大的时候，在就很耗时间。此时就可以使用全文索引来实现。
添加全文索引：alter table 表名 add fulltext(列名)；
此处我们添加两个全文索引：
这样再对文本大内容进行查找的时候，效率就很快了。
