mysql中的索引在磁盘中如何存储的 mysql索引存储结构深入理解

1、索引的数据结构

1.1 概述

索引（index）是帮助Mysql高效获取数据的数据结构。

• 索引的本质： 索引是数据结构。简单理解为“排好序的快速查找数据结构”，满足特定查找算法。这些数据结构以某种方式指向数据， 这样就可以在这些数据结构的基础上实现高级查找算法。
• 索引是在存储引擎中实现：因此每种存储引擎的索引不一定完全相同，并且每种存储引擎不一定支持所有索引类型。同时，存储引擎可以定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。有些存储引擎支持更多的索引数和更大的索引长度

优点:

• 提高数据检索的效率，降低数据库的IO成本，这也是创建索引最主要的原因。
• 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
• 在实现数据的参考完整性方面，可以加速表和表之间的连接。换句话说，对于有依赖关系的子表和父表联合查询时，可以提高查询速度。
• 在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低了CPU的消耗。

缺点：

• 创建索引和维护索引要耗费时间，并且随着数据量的增加，所耗费的时间也会增加。
• 索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间存储在磁盘上，如果有大量的索引，索引文件就可能比数据文件更快达到最大文件尺寸。
• 虽然索引大大提高了查询速度，同时却会降低更新表的速度。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

1.2 InnoDB中的索引

要想明白索引怎么存储数据首先，大概明白一个概念就是。索引的数据每一行是存在一个页里面的可以有多个页每个页是使用链表关联的，具体后续在讲。

并且在innodb运用的是b+树。

1.2.1 索引的数据

新建索引

mysql> CREATE TABLE index_demo(
    -> c1 INT,
    -> c2 INT,
    -> c3 CHAR(1),
    -> PRIMARY KEY(c1)
    -> ) ROW_FORMAT = Compact;

新建的 index_demo 表中有2个INT类型的列，1个CHAR(1)类型的列，而且我们规定了c1列为主键，这个表使用 Compact 行格式来实际存储记录的。这里我们简化了index_demo表的行格式示意图：

• 0表示普通记录、1表示目录项记录、2表示最小记录、3`表示最大记录。
• next_record：记录头信息的一项属性，表示下一条地址相对于本条记录的地址偏移量，我们用箭头来表明下一条记录是谁。
• 各个列的值：这里只记录在index_demo表中的三个列，分别是c1、c2和c3。
• 其他信息：除了上述3种信息以外的所有信息，包括其他隐藏列的值以及记录的额外信息。

那么一个页的数据的图为如下：

页内的数据使用链表进行关联，同理多页的数据页也是根据链表进行关联。并且有页号作为地址可以定位到指定的页。

 1.2.2 简单的索引设计

我们在根据某个搜索条件查找一些记录时为什么要遍历所有的数据页呢？因为各个页中的记录并没有规律，我们并不知道我们的搜索条件匹配哪些页中的记录，所以不得不依次遍历所有的数据页。所以如果我们想快速的定位到需要查找的记录在哪些数据页中该咋办？

• 快速定位到指定数据的页内

当我们给数据添加3条数据。

那么这些记录已经按照主键值的大小串联成一个单向链表了，如图所示: 

 假设:每个数据页最多能存放3条记录（实际上一个数据页非常大，可以存放下好多记录)。此时我们再来插入一条记录：

注意，新分配的数据页编号可能并不是连续的并且是拥有其他整数据的。它们只是通过维护着上一个页和下一个页的编号而建立了链表关系。
另外，页10中用户记录最大的主键值是5，而页28中有一条记录的主键值是4，因为5>4，所以这就不符合下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值的要求，
所以在插入主键值为4的记录的时候需要伴随着一次记录移动，也就是把主键值为5的记录移动到页28中，然后再把主键值为4的记录插入到页10中，这个过程的示意图如下:

这个过程表明了在对页中的记录进行增删改操作的过程中，我们必须通过一些诸如记录移动的操作来始终保证这个状态一直成立:下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值，这个过程我们称为页分裂。

1.2.3 页的目录项

因为这些16KB的页在物理存储上是不连续的，所以如果想从这么多页中根据主键值快速定位某些记录所在的页，我们需要给它们做个目录，每个页对应一个目录项，每个目录项包括下边两个部分:

• 页的用户记录中最小的主键值，我们用key来表示不包含其他数据。
• 页号，我们用page_no表示。

以页28 为例，它对应目录项2 ，这个目录项中包含着该页的页号 28 以及该页中用户记录的最小主键值 5 。我们只需要把几个目录项在物理存储器上连续存储（比如：数组），就可以实现根据主键值快速查找某条记录的功能了。比如：查找主键值为 20 的记录，具体查找过程分两步：

先从目录项中根据 二分法 快速确定出主键值为 20 的记录在 目录项3 中（因为 12 < 20 < 209 ），它对应的页是页9 。
再根据前边说的在页中查找记录的方式去页9 中定位具体的记录。至此，针对数据页做的简易目录就搞定了。这个目录有一个别名，称为索引 这就是索引的概念。

那么我们插入若干数据之后便是下图的样子：

从图中可以看出来，我们新分配了一个编号为30的页来专门存储目录项记录。这里再次强调目录项记录和普通的用户记录的

不同点：

• 目录项记录的record_type值是1，而普通用户记录的record_type值是0。
• 目录项记录只有主键值和页的编号两个列，而普通的用户记录的列是用户自己定义的，可能包含很多列，另外还有InnoDB自己添加的隐藏列。
• 了解：记录头信息里还有一个叫min_rec_mask的属性，只有在存储目录项记录的页中的主键值最小的目录项记录的min_rec_mask值为1，其他别的记录的min_rec_mask值都是0。

相同点：

• 两者用的是一样的数据页，都会为主键值生成Page Directory（页目录），从而在按照主键值进行查找时可以使用二分法来加快查询速度。
• 现在以查找主键为20的记录为例，根据某个主键值去查找记录的步骤就可以大致拆分成下边两步：
• 先到存储目录项记录的页，也就是页30中通过二分法快速定位到对应目录项，因为 12 < 20 < 209 ，所以定位到对应的记录所在的页就是页9。
• 再到存储用户记录的页9中根据二分法快速定位到主键值为20的用户记录。

1.2.4 多个目录项纪录的页

根据上面的概念举一反三，纵向目录项继续向上扩张目录项，并且是记录页的目录项。横向页目录项关联页目录项。就得出如下

从图中可以看出，我们插入了一条主键值为320的用户记录之后需要两个新的数据页：

• 为存储该用户记录而新生成了页31 。
• 因为原先存储目录项记录的 页30的容量已满 （我们前边假设只能存储4条目录项记录），所以不得不需要一个新的 页32 来存放 页31 对应的目录项。

现在因为存储目录项记录的页不止一个，所以如果我们想根据主键值查找一条用户记录大致需要3个步骤，以查找主键值为 20 的记录为例：

• 确定目录项记录页
• 我们现在的存储目录项记录的页有两个，即 页30 和 页32 ，又因为页30表示的目录项的主键值的范围是 [1, 320) ，页32表示的目录项的主键值不小于 320 ，所以主键值为 20 的记录对应的目录项记录在 页30 中。
• 通过目录项记录页 确定用户记录真实所在的页 。在一个存储 目录项记录 的页中通过主键值定位一条目录项记录的方式说过了。
• 在真实存储用户记录的页中定位到具体的记录。

这个数据结构，它的名称是 B+树 。

 1.2.5 B+树

层级：

一个B+树的节点其实可以分成好多层，规定最下边的那层，也就是存放我们用户记录的那层为第0层，之后依次往上加。
之前我们做了一个非常极端的假设：存放用户记录的页最多存放3条记录，存放目录项记录的页最多存放4条记录。其实真实环境中一个页存放的记录数量是非常大的，假设所有存放用户记录的叶子节点代表的数据页可以存放100条用户记录，所有存放目录项记录的内节点代表的数据页可以存放1000条目录项记录，那么：

• 如果B+树只有1层，也就是只有1个用于存放用户记录的节点，最多能存放100条记录。
• 如果B+树有2层，最多能存放1000×100=10,0000条记录。
• 如果B+树有3层，最多能存放1000×1000×100=1,0000,0000条记录。
• 如果B+树有4层，最多能存放1000×1000×1000×100=1000,0000,0000条记录。相当多的记录！！！

你的表里能存放100000000000条记录吗？所以一般情况下，我们用到的B+树都不会超过4层，那我们通过主键值去查找某条记录最多只需要做4个页面内的查找（查找3个目录项页和一个用户记录页），又因为在每个页面内有所谓的Page Directory（页目录），所以在页面内也可以通过二分法实现快速定位记录。
结构：

B+树也是一种多路搜索树，基于B树做出了改进，索引结构中数据的存储是页为基本单位进行存储的，B+树中的页类型主要分为两类，一类是页数据，页数据类型中存放的主要是节点数据，在这种类型中由页号，主键以及完整数据构成，另外一类是页目录，页目录中由当前页号、页数据中最小的主键值、主键对应的页号构成，页与页之间是由双向链表相连接，使用双向链表相连接的原因主要是为了范围查询，在页的内部，是按照主键的大小构成的一个有序链表.

 1.2.6 B+树 和 B树

B树其实和B+树差不多。整体的构建思是类似的，上面已经介绍了B+树这里主要介绍B树。

B树也是有层级结构，但是B树的每个层级都是带有数据的。这就说明B树的底层数据会缺少一部分数据（对于B+树的底层数据来说），那么少的数据是说明呢，我们知道除了底层数据外的目录页都会更具大小区间向上做页。那么这个做页的数据就无需存储再底层了。

1.3常见索引概念

索引按照物理实现方式，索引可以分为 2 种：

• 聚簇（聚集）
• 非聚簇（非聚集）索引。

我们也把非聚集索引称为二级索引或者辅助索引。

1.3.1 聚簇索引

• 页内记录按照主键字段 ，从小到大按顺序排列形成一个单向链表。
• 各个存放用户记录的页也是根据页中用户记录的主键字段，从小到大的顺序排成一个双向链表。
• 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。

B+树的叶子节点存储的是完整的用户记录。所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

优点

• 数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
• 聚簇索引对于主键的排序查找和范围查找速度非常快
• 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的io操作。

缺点

• 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增ID列为主键
• 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新
• 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据(回表操作)

1.3.2 非聚簇索引（辅助/二级索引）

 非聚簇索引的B+树结构是相同的，不同的是在底层的叶子节点非聚簇索引只存储部分字段数据，并且非聚簇索引会根据你使用的字段进行大小排列（区分聚簇索引的主键字段）。非聚簇索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个非聚簇索引。

• 问题:为什么我们还需要一次回表操作呢?直接把完整的用户记录放到叶子节点不OK吗?

答：如果把完整的用户记录放到叶子节点是可以不用回表。但是太占空间了，相当于每建立一棵B+树都需要把所有的用户记录再都拷贝一遍，这就有点太浪费存储空间了。

区别：

• 聚簇索引的叶子节点存储的就是我们的数据记录，非聚簇索引的叶子节点存储的是数据位置，非聚簇索引不会影响数据表的物理存储顺序。
• 一个表只能有一个聚簇索引，因为只能有一种排序存储的方式，但可以有多个非聚簇索引，也就是多个索引目录提供数据检索。
• 使用聚簇索引的时候，数据的查询效率高，但如果对数据进行插入，删除，更新等操作，效率会比非聚簇索引低。

1.3.3 联合索引

我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比方说我们想让B+树按照 c2和c3列 的大小进行排序，这个包含两层含义：

• 先把各个记录和页按照c2列进行排序。
• 在记录的c2列相同的情况下，采用c3列进行排序

 注意一点，以c2和c3列的大小为排序规则建立的B+树称为 联合索引 ，本质上也是一个二级索引。它的意思与分别为c2和c3列分别建立索引的表述是不同的，不同点如下：
建立联合索引只会建立如上图一样的1棵B+树。
为c2和c3列分别建立索引会分别以c2和c3列的大小为排序规则建立2棵B+树。

1.4 InnoDB的B+树索引的注意事项

1.4.1、根页面位置万年不动

我们前边介绍B+索引的时候，为了大家理解上的方便，先把存储用户记录的叶子节点都画出来，然后接着画存储目录项记录的内节点，实际上B+树的形成过程是这样的：

1. 每当为某个表创建一个B+树索引（聚簇索引不是人为创建的，默认就有）的时候，都会为这个索引创建一个根节点页面。最开始表中没有数据的时候，每个B+树索引对应的根节点页面中既没有用户记录，也没有目录项记录。
2. 随后向表中插入用户记录时，先把用户记录存储到这个根节点页面中。
3. 当根节点页面中的可用空间用完时继续插入记录，此时会将根节点页面中的所有记录复制到一个新分配的页，比如页a中，然后添加插入记录数据时，对这个新页进行页分裂的操作，得到另一个新页，比如页b。这时新插入的记录根据键值（也就是聚簇索引中的主键值，二级索引中对应的索引列的值）的大小就会被分配到页a或者页b中，而根节点页面便升级为存储目录项记录的页。

这个过程特别注意的是：一个B+树索引的根节点自诞生之日起，便不会再移动。这样只要我们对某个表建立一个索引，那么它的根节点的页号便会被记录到某个地方，然后凡是InnoDB存储引擎需要用到这个索引的时候，都会从那个固定的地方取出根节点的页号，从而来访问这个索引。

1.4.2、内节点中目录项记录的唯一性

我们知道B+树索引的内节点中目录项记录的内容是索引列+页号的搭配，但是这个搭配对于二级索引来说有点不严谨。还拿index_demo表为例，假设这个表中的数据是这样的：

如果二级索引中目录项的内容只是索引号+页号的搭配的话，那么为c2列建立索引后的B+树应该长这样：

如果我们想要新插入一行记录，其中c1、c2、c3的值分别是：9、1、c，那么在修改这个为c2列建立的二级索引对应的B+树时便碰到了个大问题：由于页3中存储的目录项记录是由c2列+页号的值构成的，页3中的两条目录项记录对应的c2列的值都是1，那么我们这条新插入的记录到底应该放在页4中，还是应该放在页5中啊？

为了让新插入记录能找到自己在哪个页里，我们需要 保证在B+树的同一层内节点的目录项记录除页号这个字段以外是唯一的。 所以对于二级索引的内节点的目录项记录的内容实际上是由三个部分构成的：

• 索引列的值
• 主键值
• 页号

也就是我们把主键值也添加到二级索引内节点中的目录项记录了，这样就能保证B+树每一层节点中各条目录项记录除页号这个字段外是唯一的，所以我们为c2列建立二级索引后的示意图实际上应该是这样子的：

 1.5、MyISAM中的索引方案

1.5.1、MyISAM索引的原理

我们知道InnoDB中索引即数据，也就是聚簇索引的那棵B+树的叶子节点中已经把所有完整的用户记录都包含了，而MyISAM的索引方案虽然也使用树形结构，但是却将索引和数据分开存储︰

• 将表中的记录按照记录的插入顺序单独存储在一个文件中，称之为数据文件。这个文件并不划分为若干个数据页，有多少记录就往这个文件中塞多少记录就成了。由于在插入数据的时候并没有刻意按照主键大小排序，所以我们并不能在这些数据上使用二分法进行查找。
• 使用MyISAM存储引擎的表会把索引信息另外仔储到一个新的文件中，而这个文件的构成是主键和数据记录地址的创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户记录，而是主键值＋数据记录地址的组合

这里设表一共有三列，假设我们以Col1为主键，上图是一个MylSAM表的主索引(Primary key)示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主键索引和二级索引 (Secondary key)在结构上没有任何区别，只是主键索引要求key是唯一的，而二级索引的key可以重复。

1.5.2、MyISAM与InnoDB对比

MyISAM的索引方式都是“非聚簇”的，与InnoDB包含1个聚簇索引是不同的。

小结两种引擎中索引的区别：

• 在InnoDB存储引擎中，我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录，而在MyISAM中却需要进行一次回表操作，意味着MyISAM中建立的索引相当于全部都是二级索引。
• InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。
• InnoDB的非聚簇索引data域存储相应记录主键的值，而MyISAM索引记录的是地址。换句话说，InnoDB的所有非聚簇索引都引用主键作为data域。
• MyISAM的回表操作是十分快速的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。
• InnoDB要求表必须有主键（MyISAM可以没有）。如果没有显式指定，则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

1.6 索引的弊端

索引是个好东西，可不能乱建，它在空间和时间上都会有消耗。因为索引本身就是数据。 

1.6.1 空间弊端

每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用 16KB 的存储空间，一棵很大的B+树由许多数据页组成，那就是很大的一片存储空间。

1.6.2 时间弊端

每次对表中的数据进行 增、删、改 操作时，都需要去修改各个B+树索引。而且我们讲过，B+树每层节点都是按照索引列的值 从小到大的顺序排序 而组成了 双向链表 。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些 记录移位 ， 页面分裂 、 页面回收 等操作来维护好节点和记录的排序。如果我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作，会给性能拖后腿。

1.6.3 相关思考问题

思考题：为了减少IO，索引树会一次性加载吗？

答：

1、数据库索引是存储在磁盘上的，如果数据量很大必然导致索引的大小也会很大，超过几个G。
2、当我们利用索引查询时候，是不可能将全部几个G的索引都加载进内存的，我们能做的只能是：逐一加载每一个磁盘页，因为磁盘页对应着索引树的节点。

---

思考题：B+树的存储能力如何？为何说一般查找行记录，最多只需1~3次磁盘IO

答：

1、InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT(占用4个字节)或BIGINT(占用8个字节)，指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值，因为是估算，为了方便计算，这里的K取值为10^3。
也就是说一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿条记录。（这里假定一个数据页也存储10^3条行记录数据了）
2、实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2~4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作
 

---

思考题：为什么说B+树比B-树更适合实际应用中操作系统的文件索引和数据库索引？

答：

1、B+树的查询效率更加稳定，B树的每个节点中都存放了数据，每次查询的速度都不一样，B+树只有叶子节点存放数据，每次查询的速度都一样。
2、B+树的叶子节点形成了一个有序链表，可以轻松地支持范围查询。而B树需要在内部节点进行搜索，范围查询时需要遍历整个B树，效率较低。
3、B+树的磁盘读写代价更低，因为B树的每个节点中都存放了数据，导致每个页中的存储条数变少，要加载更多页，增加了IO的读取次数，B+树中存储的条数更多，IO读取次数较少。

---

思考题：Hash索引与B+树索引的区别

答：
1、Hash索引不能进行范围查询，而B+树可以。这是因为Hash索引指向的数据是无序的，而B+树的叶子节点是个有序的链表。
2、Hash索引不支持联合索引的最左侧原则（即联合索引的部分索引无法使用），而B+树可以。对于联合索引来说，Hash索引在计算Hash值的时候是将索引键合并后再一起计算Hash值，所以不会针对每个索引单独计算Hash值。因此如果用到联合索引的一个或者几个索引时，联合索引无法被利用。
3、Hash索引不支持 ORDER BY 排序，因为Hash索引指向的数据是无序的，因此无法起到排序优化的作用，而B+树索引数据是有序的，可以起到对该字段ORDER BY 排序优化的作用。同理，我们也无法用Hash索引进行模糊查询，而B+树使用LIKE进行模糊查询的时候，LIKE后面后模糊查询（比如%结尾）的话就可以起到优化作用。
4、InnoDB不支持哈希索引
 

---

思考题：Hash 索引与 B+ 树索引是在建索引的时候手动指定的吗？

答：

针对InnoDB和MylSAM存储引擎，都会默认采用B+树索引，无法使用Hash索引。InnoDB提供的自适应Hash是不需要手动指定的。如果是>Memory/Heap和NDB存储引擎，是可以进行选择Hash索引的

2、InnoDB数据存储结构

2.1 数据库的存储结构：页

• 概述

InnoDB将数据划分为若干个页，InnoDB中页的大小默认为16KB。

以页作为磁盘和内存之间交互的基本单位，也就是一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。也就是说，在数据库中，不论读一行，还是读多行，都是将这些行所在的页进行加载。也就是说，数据库管理存储空间的基本单位是（Page），数据库I/O操作的最小单位是页。一个页中可以存储多个行记录。

页a、页b、页c…页n这些页可以不在物理结构上相连，只要通过双向链表相关联即可。每个数据页中的记录会按照主键值从小到大的顺序组成一个单向链表，每个数据页都会为存储在它里边的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应的分组中的记录即可快速找到指定的记录。

• 内部结构

页如果按类型划分的话，常见的有数据页（保存B+树节点)、系统页、Undo页和事务数据页等。数据页是我们最常使用的页。

数据页的16KB大小的存储空间被划分为七个部分，分别是文件头(File Header)、页头(Page Header)、最大最小记录(Infimum+supremum)、用户记录(User Records)、空闲空间(Free Space)、页目录(Page Directory）和文件尾(File Tailer) 。

页结构的示意图如下所示:

 2.2、区、段和碎片区

• 区

B+树的每一层中的页都会形成一个双向链表，如果是以页为单位来分配存储空间的话，双向链表相邻的两个页之间的物理位置可能离得非常远。我们介绍B+树索引的使用场景的时候特别提到范围查询只需要定位到最左边的记录和最右边的记录，然后沿着双向链表一直扫描就可以了，而如果链表中相邻的两个页物理位置离得非常远，指针在磁盘上寻道的时间就会增加，然后读取的速度就会变慢，就是所谓的随机I/O。再一次强调，磁盘的速度和内存的速度差了好几个数量级，随机I/O是非常慢的，所以我们应该尽量让链表中相邻的页的物理位置也相邻，这样进行范围查询的时候才可以使用所谓的顺序I/O。

引入区的概念，一个区就是物理位置上连续的64个页。因为InnoDB中的页的大小默认是16KB，所以一个区的大小是64*16KB=1MB。在表中数据量大的时候，为某个索引分配空间的时候就不再按照页的单位分配了，而是按照区为单位分配，甚至在表中的数据特别多的时候，可以一次性分配多个连续的区。虽然可能造成一点点空间的浪费（数据不足以填充满整个区），但是从性能角度看，可以消除很多的随机I/O，功大于过！

• 段

对于范围查询，其实是对B+树叶子节点中的记录进行顺序扫描，而如果不区分叶子节点和非叶子节点，统统把节点代表的页面放到申请到的区中的话，进行范围扫描的效果就大打折扣了。所以InnoDB对B+树的叶子节点和非叶子节点进行了区别对待，也就是说叶子节点有自己独有的区，非叶子节点也有自己独有的区。存放叶子节点的区的集合就算是一个段（segment），存放非叶子节点的区的集合也算是一个段。也就是说一个索引会生成2个段，一个叶子节点段，一个非叶子节点段。

除了索引的叶子节点段和非叶子节点段之外，InnoDB中还有为存储一些特殊的数据而定义的段，比如回滚段。所以，常见的段有数据段、索引段、回滚段。数据段即为B+树的叶子节点，索引段即为B+树的非叶子节点。

在InnoDB存储引擎中，对段的管理都是由引擎自身所完成，DBA不能也没有必要对其进行控制。这从一定程度上简化了DBA对于段的管理。

段其实不对应表空间中的某一个连续的物理区域，而是一个逻辑上的概念，由若干个零散的页面以及一些完整的区组成。

• 碎片区

默认情况下，一个使用InnoDB存储引擎的表只有一个聚簇索引，一个索引会生成2个段，而段是以区为单位申请存储空间的，一个区默认占用1M（64*16KB=1024KB）存储空间，所以**默认情况下一个只存在几条记录的小表也需要2M的存储空间么？**以后每次添加一个索引都要多申请2M的存储空间么？这对于存储记录比较少的表简直是天大的浪费。这个问题的症结在于到现在为止我们介绍的区都是非常纯粹的，也就是一个区被整个分配给某一个段，或者说区中的所有页面都是为了存储同一个段的数据而存在的，即使段的数据填不满区中所有的页面，那余下的页面也不能挪作他用。

为了考虑以完整的区为单位分配给某个段对于数据量较小的表太浪费存储空间的这种情况，InnoDB提出了一个碎片（fragment）区的概念。在一个碎片区中，并不是所有的页都是为了存储同一个段的数据而存在的，而是碎片区中的页可以用于不同的目的，比如有些页面用于段A，有些页面用于段B，有些页甚至哪个段都不属于。碎片区直属于表空间，并不属于任何一个段。

所以此后为某个段分配存储空间的策略是这样的：

• 在刚开始向表中插入数据的时候，段是从某个碎片区以单个页面为单位来分配存储空间的，
• 当某个段已经占用了32个碎片区页面之后，就会申请以完整的区为单位来分配存储空间。
• 所以现在段不能仅定义为是某些区的集合，更精确的应该是某些零散的页面已经一些完整的区的集合。

如果把表空间比作是一个集团军，段就相当于师，区就相当于团。一般的团都是隶属于某个师的，就像是处于FSEG的区全都隶属于某个段，而处于FREE、FREE_FRAG以及FULL_FRAG这三种状态的区却直接隶属于表空间，就像独立团直接听命于军部一样。
 

3. 索引的应用

3.1、索引的分类

MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。

• 从功能逻辑 上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引。
• 按照 物理实现方式 ，索引可以分为 2 种：聚簇索引和非聚簇索引。
• 按照 作用字段个数 进行划分，分成单列索引和联合索引。

3.1.1、普通索引

在创建普通索引时，不附加任何限制条件，只是用于提高查询效率。这类索引可以创建在任何数据类型中，其值是否唯一和非空，要由字段本身的完整性约束条件决定。建立索引以后，可以通过索引进行查询。例如，在表student的字段name 上建立一个普通索引，查询记录时就可以根据该索引进行查询。

3.1.2、唯一性索引

使用UNIQUE参数可以设置索引为唯一性索引，在创建唯一性索引时，限制该索引的值必须是唯一的但允许有空值。在一张数据表里可以有多个唯一索引。例如，在表student的字段email中创建唯一性索引，那么字段email的值就必须是唯一的。通过唯一性索引,可以更快速地确定某条记录。

3.1.3、主键索引

主键索引就是一种特殊的唯一性索引，在唯一索引的基础上增加了不为空的约束，也就是NOT NULL+UNIQUE，一张表里最多只有一个主键索引。

3.1.4、单列索引

在表中的单个字段上创建索引。单列索引只根据该字段进行索引。单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。一个表可以有多个单列索引。

3.1.5、多列(组合、联合)索引

多列索引是在表的多个字段组合上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询，但是只有查询条件中使用了这些字段中的第一个字段时才会被使用。例如，在表中的字段id，name和，gender上建立一个多列索引idx_id_name_gender，只有在查询条件中使用了字段id时该索引才会被使用。使用组合索引时遵循最左前缀集合。

3.1.6、全文索引

全文索引(也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用【分词技术】等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果

全文索引非常适合大型数据集，对于小的数据集，它的用处比较小。使用参数FULLTEXT可以设置索引为全文索引。在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引只能创建在CHAR、VARCHAR 或TEXT类型及其系列类型的字段上，查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。

例如，表student的字段information是TEXT类型,该字段包含了很多文字信息。在字段information上建立全文索引后，可以提高查询字段information的速度。

全文索引典型的有两种类型:自然语言的全文索引和布尔全文索引。

自然语言搜索引擎将计算每一个文档对象和查询的相关度。这里，相关度是基于匹配的关键词的个数，以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语，匹配时的相关度就越高。相反，非常常见的单词将不会被搜索，如果一个词语的在超过50%的记录中都出现了，那么自然语言的搜索将不会搜索这类词语。
MySQL数据库从3.23.23版开始支持全文索引，但MySQL5.6.4以前只有Myisam支持，5.6.4版本以后innodb才支持，但是官方版本不支持中文分词，需要第三方分词插件。在5.7.6版本，MySQ L内置了ngram全文解析器，用来支持亚洲语种的分词。测试或使用全文索引时，要先看一下自己的MySQL版本、存储引擎和数据类型是否支持全文索引。
随着大数据时代的到来，关系型数据库应对全文索引的需求已力不从心，逐渐被solr、ElasticSearch等专门的搜索引擎所替代。

3.1.7、补充:空间索引

使用参数SPATIAL可以设置索引为空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。MySQL中的空间数据类型包括GEOMETRY、POINT、LINESTRING和POLYGON等。目前只有MyISAM存储引擎支持空间检索，而且索引的字段不能为空值。对于初学者来说，这类索引很少会用到。

3.1.8、小结

不同的存储引擎支持的索引类型也不一样

InnoDB:支持B-tree、Full-text等索引，不支持 Hash 索引;
MyISAM:支持B-tree、Full-text等索引，不支持Hash索引;
Memory :支持B-tree、Hash 等索引，不支持Full-text索引;
NDB:支持Hash索引，不支持B-tree、Full-text等索引;
Archive :不支持B-tree、Hash、Full-text等索引;

3.2、创建索引

• 在建表的时候创建索引。

CREATE TABLE table_name [col_name data_type] 
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC]

• UNIQUE、FULLTEXT和SPATIAL为可选参数，分别表示唯一索引、全文索引和空间索引；
• INDEX与KEY为同义词，两者的作用相同，用来指定创建索引；
• index_name指定索引的名称，为可选参数，如果不指定，那么MySQL默认col_name为索引名；
• col_name为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；
• length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
• ASC或DESC指定升序或者降序的索引值存储。

3.2.1 创建普通索引

CREATE TABLE book( 
    book_id INT , 
    book_name VARCHAR(100), 
    authors VARCHAR(100), 
    info VARCHAR(100) , 
    comment VARCHAR(100), 
    year_publication YEAR, 
    INDEX(year_publication) 
);

3.2.2 创建唯一索引

CREATE TABLE test1( 
    id INT NOT NULL, 
    name varchar(30) NOT NULL, 
    UNIQUE INDEX uk_idx_id(id) 
);

 3.2.3、创建和删除主键索引

CREATE TABLE student ( 
    id INT(10) UNSIGNED AUTO_INCREMENT, 
    student_no VARCHAR(200),
    student_name VARCHAR(200), 
    PRIMARY KEY(id) 
);

# 删除主键索引
ALTER TABLE student drop PRIMARY KEY ;

3.2.4、创建单列索引

CREATE TABLE test2( 
    id INT NOT NULL, 
    name CHAR(50) NULL, 
    INDEX single_idx_name(name(20)) 
);

3.2.5、创建组合索引

CREATE TABLE test3( 
    id INT(11) NOT NULL, 
    name CHAR(30) NOT NULL, 
    age INT(11) NOT NULL, 
    info VARCHAR(255), 
    INDEX multi_idx(id,name,age) 
);

• 在已经存在的表上创建索引

3.2.6 使用ALTER TABLE语句创建索引

ALTER TABLE 
table_name 
ADD 
[UNIQUE|FULLTEXT|SPATIAL] [INDEX | KEY] [index_name] (col_name[length],..) [ASC |DESC]

3.2.7 使用CREATE INDEX创建索引

create  [UNIQUE | FULLTEXT | SPATIAL]
index index_name
on table_name (col_name[length],...) [ASC | DESC]

3.2.8 使用ALTER TABLE删除索引

ALTER TABLE table_name DROP INDEX index_name;

3.2.9 使用DROP INDEX语句删除索引

DROP INDEX index_name ON table_name;

提示 删除表中的列(字段)时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。

3.3 MySQL8.0索引新特性

3.3.1、支持降序索引

CREATE TABLE table_name
(a int,
b int,
index idx_a_b(a,b desc)
);

3.3.2、隐藏索引

在MySQL 5.7版本及之前，只能通过显式的方式删除索引(类似于物理删除)。此时，如果发现删除索引后出现错误，又只能通过显式创建索引的方式将删除的索引创建回来。如果数据表中的数据量非常大，或者数据表本身比较大，这种操作就会消耗系统过多的资源，操作成本非常高。

从MySQL 8.x开始支持隐藏索引（invisible indexes），只需要将待删除的索引设置为隐藏索引（类似于逻辑删除），使查询优化器不再使用这个索引（即使使用force index（强制使用索引），优化器也不会使用该索引），确认将索引设置为隐藏索引后系统不受任何响应，就可以彻底删除索引。这种通过先将索引设置为隐藏索引，再删除索引的方式就是软删除。

3.3.3 创建隐藏索引或者修改成隐藏索引

运用INVISIBLE关键字

• 在建表的时候创建

CREATE TABLE tablename( 
    propname1 type1[CONSTRAINT1], 
    propname2 type2[CONSTRAINT2], 
    ……
    propnamen typen, 
    INDEX [indexname](propname1 [(length)],...) INVISIBLE 
);

• 修改已有的索引

#CREATE 关键字
CREATE INDEX index_name 
ON table_name (propname[(length)]) INVISIBLE;


#ALTER 关键字
ALTER TABLE tablename 
ADD INDEX indexname (propname [(length)]) INVISIBLE;

3.3.4 切换索引可见状态

既然可以隐藏那么就可以显示 ,显示的关键字 VISIBLE

ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; #切换成隐藏索引 
ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #切换成非隐藏索引

注意 当索引被隐藏时，它的内容仍然是和正常索引一样实时更新的。如果一个索引需要长期被隐藏，那么可以将其删除，因为索引的存在会影响插入、更新和删除的性能。

3.4、索引的设计原则（合适设为索引的字段）

为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍。高效的索引对于获得良好的性能非常重要。设计索引时，应该考虑相应准则。那么那些字段适合创建索引呢，下面聊一聊。

3.4.1 字段的数值有唯一性的限制

索引本身可以起到约束的作用，比如唯一索引、主键索引都可以起到唯一性约束的，因此在我们的数据表中，如果某个字段是唯一的，就可以直接创建唯一性索引，或者主键索引。这样可以更快速地通过该索引来确定某条记录。
例如，学生表中学号是具有唯一性的字段，为该字段建立唯一性索引可以很快确定某个学生的信息，如果使用姓名的话，可能存在同名现象，从而降低查询速度。

3.4.2 频繁作为WHERE查询条件的字段

某个字段在SELECT语句的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了。尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。
比如student_info数据表（含100万条数据），假设我们想要查询 student_id=123110 的用户信息。

3.4.3 经常GROUP BY和ORDER BY的列

索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据进行排序的时候，就需要对分组或者排序的字段进行索引。如果待排序的列有多个，那么可以在这些列上建立组合索引。

3.4.4 UPDATE、DELETE的WHERE条件列

对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作，如果对 WHERE 字段创建了索引，就能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。

3.4.5 DISTINCT字段需要创建索引

有时候我们需要对某个字段进行去重，使用 DISTINCT，那么对这个字段创建索引，也会提升查询效率。

3.4.6 多表JOIN连接操作时，创建索引注意事项

首先，连接表的数量尽量不要超过 3 张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。
其次，对 WHERE 条件创建索引，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的。
最后，对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致，因为不同的类型，会进行转换后在进行比较，转换会用到函数，使用函数，索引就会失效。

3.4.7 使用列的类型小的创建索引

我们这里所说的类型大小指的就是该类型表示的数据范围的大小。

数据类型越小，在查询时进行的比较操作越快
数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘I/O带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。
这个建议对于表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键使用更小的数据类型，也就意味着节省更多的存储空间和更高效的I/O。

3.4.8 使用字符串前缀创建索引

假设我们的字符串很长，那存储一个字符串就需要占用很大的存储空间。在我们需要为这个字符串列建立索引时，那就意味着在对应的B+树中有这么两个问题:

B+树索引中的记录需要把该列的完整字符串存储起来，更费时。而且字符串越长，在索引中占用的存储空间越大。
如果B+树索引中索引列存储的字符串很长，那在做字符串比较时会占用更多的时间。
我们可以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引。
这样在查找记录时虽然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串值。既节约空间，又减少了字符串的比较时间，还大体能解决排序的问题。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间，如果只检索字段前面的若干字符，这样可以提高检索速度。
创建一张商户表，因为地址字段比较长，在地址字段上建立前缀索引
 

create table shop ( address varchar ( 120) not null) ;
alter table shop add index ( address( 12));

问题是，截取多少呢?截取得多了，达不到节省索引存储空间的目的;截取得少了，重复内容太多字段的散列度(选择性)会降低。怎么计算不同的长度的选择性呢?

• 这里直接上结论

拓展：Alibaba《Java开发手册》
【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。
说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达 90% 以上。

• 分析过程是通过不同长度去计算，与全表的选择性对比 :
  区分度计算公式：

count(distinct left(列名, 索引长度))/count(*)

3.4.9 区分度高(散列性高)的列适合作为索引

列的基数指的是某一列中不重复数据的个数，比方说某个列包含值2,5,8,2,5,8,2,5,8，虽然有9条记录，但该列的基数却是3(重复字段)。也就是说，**在记录行数一定的情况下，列的基数越大，该列中的值越分散；列的基数越小，该列中的值越集中。**这个列的基数指标非常重要，直接影响我们是否能有效的利用索引。最好为列的基数大的列建立索引，为基数太小的列建立索引效果可能好。

可以使用公式select count(distinct a)/count(*) from t1计算区分度，越接近1越好，一般超过33%就算是比较高效的索引了。

拓展：联合索引把区分度高（散列性高）的列放在前面。

3.4.10 用最频繁的列放到联合索引的左侧

这样也可以较少的建立一些索引。同时，由于"最左前缀原则"，可以增加联合索引的使用率。

因为如果你只是用id的索引查询和使用id+name做索引查询  ，那么你建立id+name的联合索引 的时候。单单查询id可以命中id+name的索引。

3.4.11 限制索引的数目

在多个字段都要创建索引的情况下，联合索引优于单值索引.

在实际工作中，我们也需要注意平衡，索引的数目不是越多越好。我们需要限制每张表上的索引数量，建议单张表索引数量不超过6个。原因：

• 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
• 索引会影响INSERT、DELETE、UPDATE等语句的性能，因为表中的数据更改的同时，索引也会进行调整和更新，会造成负担。
• 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，会增加MySQL优化器生成执行计划时间，降低查询性能。

3.5 不合适设为索引的字段

• 在where中使用不到的字段，不要设置索引

不涉及查询

• 数据量小的表最好不要使用索引

如果表记录太少，比如少于100Q个，那么是不需要创建索引的。表记录太少，是否创建索引对查询效率的影响并不大。甚至说，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。

• 有大量重复数据的列上不要建立索引

在条件表达式中经常用到的不同值较多的列上建立索引，但字段中如果有大量重复数据，也不用创建索引。比如在学生表的“性别"字段上只有“男”与“女”两个不同值，因此无须建立索引。如果建立索引，不但不会提高查询效率，反而会严重降低数据更新速度。

举例1:要在100万行数据中查找其中的50万行（比如性别为男的数据)，一旦创建了索引，你需要先访问50万次索引，然后再访问50万次数据表，这样加起来的开销比不使用索引可能还要大。

• 避免对经常更新的表创建过多的索引

第一层含义︰频繁更新的字段不一定要创建索引。因为更新数据的时候，也需要更新索引，如果索引太多，在更新索引的时候也会造成负担，从而影响效率。
第二层含义:避免对经常更新的表创建过多的索引，并且索引中的列尽可能少。此时，虽然提高了查询速度，同时却会降低更新表的速度。

• 不建议用无序的值作为索引

例如身份证、UUID(在索引比较时需要转为ASCII，并且插入时可能造成页分裂)、MD5、HASH、无序长字符串等。

• 删除不再使用或者很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

• 不要定义冗余或重复的索引

4、性能分析工具的使用以及如何优化

4.1、数据库服务器的优化步骤

整个流程划分成了 观察（Show status） 和 行动（Action） 两个部分。字母 S 的部分代表观察（会使用相应的分析工具），字母 A 代表的部分是行动（对应分析可以采取的行动）。

 

详细解释一下上图：

我们需要观察服务器的状态是否存在周期性的波动。如果存在周期性波动，可能是同期性的原因，比如双十一、促销活动等。这样的话，我们可以通过加缓存，或者更改缓存失效策略。

如果缓存策略没有解决，或者不是周期性波动的原因，我们就需要进一步分析查询延迟和卡顿的原因，接下来我们需要开启慢查询。

慢查询可以帮我们定立执行慢的SQL语句。我们可以通过设置long.query_time参数定义“慢”的阈值，如果SQL执行时间超过了long_query_time，则会认为是慢查询。当收集上来这些慢查询之后，我们就可以通过分析工具对慢查询日志进行分析。

我们就知道了执行慢的SQL，这样就可以针对性地用EXPLAIN查看对应SQL语句的执行计划，或者使用show profile查看SQL中每一个步骤的时间成本。这样我们就可以了解SQL查询慢是因为执行时间长，还是等待时间长。

如果是SQL等待时间长，我们可以调优服务器的参数，比如适当增加数据库缓冲池等。如果是SQL执行时间长，就进入这一步中我们需要考虑是索引设计的问题?还是查询关联的数据表过多?还是因为数据表的字段设计问题导致了这一现象。然后在这些维度上进行对应的调整。

我们需要考虑数据库自身的SQL查询性能是否已经达到了瓶颈，如果确认没有达到性能瓶颈，就需要重新检查，重复以上的步骤。如果已经达到了性能瓶颈，进而，需要考虑增加服务器，采用读写分离的架构，或者考虑对数据库进行分库分表，比如垂直分库、垂直分表和水平分表等。

4.2、查看系统性能参数

在MySQL中，可以使用 SHOW STATUS 语句查询一些MySQL数据库服务器的性能参数 、执行频率 。

 常用参数如下：

• Connections：连接MySQL服务器的次数。
• Uptime：MySQL服务器的上线时间。
• Slow_queries：慢查询的次数。
• Innodb_rows_read：Select查询返回的行数
• Innodb_rows_inserted：执行INSERT操作插入的行数
• Innodb_rows_updated：执行UPDATE操作更新的行数
• Innodb_rows_deleted：执行DELETE操作删除的行数
• Com_select：查询操作的次数。
• Com_insert：插入操作的次数。对于批量插入的 INSERT 操作，只累加一次。
• Com_update：更新操作的次数。
• Com_delete：删除操作的次数。

4.3、统计SQL的查询成本：last_query_cost

一条SQL查询语句在执行前需要确定查询执行计划，如果存在多种执行计划的话，MySQL会计算每个执行计划所需要的成本，从中选择成本最小的一个作为最终执行的执行计划。
如果我们想要查看某SQL语句的查询成本，可以在执行完这条SQL语句之后，通过查看当前会话中的last_query_cost变量值来得到当前查询的成本。它通常也是我们评价一个查询的执行效率的一个常用指标。这个查询成本对应的是SOL语句所需要读取的页的数量。

SHOW STATUS LIKE 'last_query_cost';

使用场景：它对于比较开销是非常有用的，特别是我们有好几种查询方式可选的时候。

SQL 查询是一个动态的过程，从页加载的角度来看，我们可以得到以下两点结论：

• 位置决定效率。如果页就在数据库缓冲池中，那么效率是最高的，否则还需要从内存或者磁盘中进行读取，当然针对单个页的读取来说，如果页存在于内存中，会比在磁盘中读取效率高很多。
• 批量决定效率。如果我们从磁盘中对单一页进行随机读，那么效率是很低的（差不多10ms），而采用顺序读取的方式，批量对页进行读取，平均一页的读取效率就会提升很多，甚至要快于单个页面在内存中的随机读取。

所以说，遇到I/O并不用担心，方法找对了，效率还是很高的。我们首先要考虑数据存放的位置，如果是经常使用的数据就要尽量放到缓冲池中，其次我们可以充分利用磁盘的吞吐能力，一次性批量读取数据，这样单个页的读取效率也就得到了提升。

5、定位执行慢的 SQL：慢查询日志

5.1、开启慢查询日志参数

MySQL的慢查询日志，用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time的值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上（不含10秒）的语句，认为是超出了我们的最大忍耐时间值。

它的主要作用是，帮助我们发现那些执行时间特别长的SQL查询，并且有针对性地进行优化，从而提高系统的整体效率。当我们的数据库服务器发生阻塞、运行变慢的时候，检查一下慢查询日志，找到那些慢查询，对解决问题很有帮助。比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合explain进行全面分析。

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。

慢查询日志支持将日志记录写入文件。

5.1.1、开启slow_query_log

set global slow_query_log='ON';

查看下慢查询日志是否开启，以及慢查询日志文件的位置：

show variables like `%slow_query_log%`;

5.1.2、查看/修改long_query_time阈值 

• 查看

show variables like '%long_query_time%';

• 设置

测试发现：设置global的方式对当前session的long_query_time失效。对新连接的客户端有效。所以可以一并执行下述语句

mysql > set global long_query_time = 1; 
mysql> show global variables like '%long_query_time%'; 

mysql> set long_query_time=1; 
mysql> show variables like '%long_query_time%';

5.1.3、查看慢查询数目 

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

5.1.4、慢查询日志分析工具：mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow 。查看mysqldumpslow的帮助信息

mysqldumpslow --help

mysqldumpslow 命令的具体参数如下：

• -a: 不将数字抽象成N，字符串抽象成S
• -s: 是表示按照何种方式排序：

• c: 访问次数
• l: 锁定时间
• r: 返回记录
• t: 查询时间
• al:平均锁定时间
• ar:平均返回记录数
• at:平均查询时间 （默认方式）
• ac:平均查询次数

• -t: 即为返回前面多少条的数据；
• -g: 后边搭配一个正则匹配模式，大小写不敏感的；

举例：我们想要按照查询时间排序，查看前五条 SQL 语句，这样写即可：

mysqldumpslow -s t -t 5 /var/lib/mysql/atguigu01-slow.log

[root@bogon ~]# mysqldumpslow -s t -t 5 /var/lib/mysql/atguigu01-slow.log 
Reading mysql slow query log from /var/lib/mysql/atguigu01-slow.log 
Count: 1 Time=2.39s (2s) Lock=0.00s (0s) Rows=13.0 (13), root[root]@localhost SELECT * FROM student WHERE name = 'S' 
Count: 1 Time=2.09s (2s) Lock=0.00s (0s) Rows=2.0 (2), root[root]@localhost SELECT * FROM student WHERE stuno = N 
Died at /usr/bin/mysqldumpslow line 162, <> chunk 2.

#得到返回记录集最多的10个SQL 
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log 
#得到访问次数最多的10个SQL 
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log
#得到按照时间排序的前10条里面含有左连接的查询语句 
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log 
#另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况 
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

5.1.5、关闭慢查询日志

• 永久性方式

修改mysql 的配置文件

[mysqld] 
slow_query_log=OFF
#或
[mysqld] 
#slow_query_log =OFF

• 临时性方式

修改配置参数

SET GLOBAL slow_query_log=off;

5.1.5、删除慢查询日志

使用SHOW语句显示慢查询日志信息，具体SQL语句如下。

SHOW VARIABLES LIKE 'slow_query_log% ';

使用命令mysqladmin flush-logs来重新生成查询日志文件，具体命令如下，执行完毕会在数据目录下重新生成慢查询日志文件。

mysqladmin -uroot -p fush-logs slow

5.2、查看SQL执行成本：SHOW PROFILE

show variables like 'profiling';
#开启
set profiling = 'ON';
#查看
show profiles;
show profile cpu,block io for query 2;

show profile的常用查询参数:

• ALL:显示所有的开销信息。
• BLOCK IO:显示块Io开销。
• CONTEXT SWITCHES: 上下文切换开销。
• CPU:显示CPU开销信息。
• IPC:显示发送和接收开销信息。
• MEMORY:显示内存开销信息。
• PAGE FAULTS:显示页面错误开销信息。
• SOURCE:显示和Source_function，Source_file，Source_line相关的开销信息。
• SWAPS:显示交换次数开销信息。

5.3、分析查询语句：EXPLAIN

定位了查询慢的SQL之后，我们就可以使用EXPLAIN或DESCRIBE 工具做针对性的分析查询语句DESCRIBE语句的使用方法与EXPLAIN语句是一样的，并且分析结果也是一样的。

MySQL中有专门负责优化SELECT语句的优化器模块，主要功能:通过计算分析系统中收集到的统计信息，为客户端请求的Query提供它认为最优的执行计划（他认为最优的数据检索方式，但不见得是DBA认为是最优的，这部分最耗费时间)。

这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行查询等等。MysQL为我们提供了EXPLAIN语句来帮助我们查看某个查询语句的具体执行计划，大家看懂EXPLAIN语句的各个输出项，可以有针对性的提升我们查询语句的性能。

5.3.1 概述

EXPLAIN SELECT select_options (you sql)
#或者
DESCRIBE SELECT select_options (you sql)

如果我们想看看某个查询的执行计划的话，可以在具体的查询语句前边加一个 EXPLAIN ，就像这样 ：

mysql> EXPLAIN SELECT 1;

输出的上述信息就是所谓的执行计划。在这个执行计划的辅助下，我们需要知道应该怎样改进自己的查询语句以使查询执行起来更高效。其实除了以SELECT开头的查询语句，其余的DELETE、INSERT、REPLACE以及UPDATE语句等都可以加上 EXPLAIN，用来查看这些语句的执行计划，只是平时我们对SELECT语句更感兴趣。

注意:执行EXPLAIN时并没有真正的执行该后面的语句，因此可以安全的查看执行计划。
EXPLAIN 语句输出的各个列的作用如下：

• id   : 在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
• select_type    :SELECT关键字对应的那个查询的类型
• table   : 表名
• partitions :   匹配的分区信息
• type  :  针对单表的访问方法
• possible_keys :   可能用到的索引
• key   : 实际上使用的索引
• key_len   : 实际使用到的索引长度
• ref   : 当使用索引列等值查询时，与索引列进行等值匹配的对象信息
• rows   : 预估的需要读取的记录条数
• filtered   : 某个表经过搜索条件过滤后剩余记录条数的百分比
• Extra   :一些额外的信息

5.3.2、基本语法

准备一张测试表

CREATE TABLE s1 ( 
id INT AUTO_INCREMENT, 
key1 VARCHAR(100), 
key2 INT, 
key3 VARCHAR(100), 
key_part1 VARCHAR(100), 
key_part2 VARCHAR(100), 
key_part3 VARCHAR(100), 
common_field VARCHAR(100), 
PRIMARY KEY (id), 
INDEX idx_key1 (key1), 
UNIQUE INDEX idx_key2 (key2), 
INDEX idx_key3 (key3), 
INDEX idx_key_part(key_part1, key_part2, key_part3) ) ENGINE=INNODB CHARSET=utf8;

5.3.2.1、table

不论我们的查询语句有多复杂，包含了多少个表 ，到最后也是需要对每个表进行单表访问的，所以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名（有时会出现中间表）。

5.3.2.2、id n

id如果相同，可以认为是一组，从上往下顺序执行
在所有组中，id值越大，优先级越高，越先执行
关注点：id号每个号码，表示一趟独立的查询,一个sql的查询趟数越少越好

5.3.2.3、select_type

一条大的查询语句里边可以包含若干个SELECT关键字，每个SELECT关键字代表着一个小的查询语句而每个SELECT关键字的FROM子句中都可以包含若干张表(这些表用来做连接查询)，每一张表都对应着执行计划输出中的一条记录，对于在同一个SELECT关键字中的表来说，它们的id值是相同的。

MySQL为每一个SELECT关键字代表的小查询都定义了一个称之为select_type的属性，意思是我们只要知道了某个小查询的select_type属性，就知道了这个小查询在整个大查询中扮演了一个什么角色，我们看一下select_type都能取哪些值.

select_type 属性 含义

• SIMPLE : 简单的 select 查询,查询中不包含子查询或者 UNION
• PRIMARY : 查询中若包含任何复杂的子部分，最外层查询则被标记为 Primary
• DERIVED : 在 FROM 列表中包含的子查询被标记为 DERIVED(衍生) MySQL 会递归执行这些子查询, 把结果放在临时表里。
• SUBQUERY : 在SELECT或WHERE列表中包含了子查询
• DEPEDENT SUBQUERY : 在SELECT或WHERE列表中包含了子查询,子查询基于外层
• UNCACHEABLE SUBQUERY : 无法使用缓存的子查询
• UNION : 若第二个SELECT出现在UNION之后，则被标记为UNION； 若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED
• UNION RESULT : 从UNION表获取结果的SELECT

查询语句中不包含UNION或者子查询的查询都算作是SIMPLE类型，连接查询也算是SIMPLE类型

5.3.2.4、partitions

代表分区表中的命中情况，非分区表，该项为NULL 。一般情况下我们的查询语句的执行计划的partitions列的值都是NULL。

5.3.2.5、type ☆

执行计划的一条记录就代表着MySQL对某个表的执行查询时的访问方法，又称“访问类型”，其中的type列就表明了这个访问方法是啥，是较为重要的一个指标。
比如，看到type列的值是ref，表明MySQL即将使用ref访问方法来执行对s1表的杳询。

完整的访问方法如下： system ， const ， eq_ref ， ref ， fulltext ， ref_or_null ， index_merge ， unique_subquery ， index_subquery ， range ， index ， ALL 。

type 是查询的访问类型。是较为重要的一个指标，结果值从最好到最坏依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index >ALL ，一般来说，得保证查询至少达到 range 级别，最好能达到 ref。

• system：当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，那么对该表的访问方法就是system。表只有一行记录（等于系统表），这是 const 类型的特列，平时不会出现，这个也可以忽略不计。
• const：表示通过索引一次就找到了,const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以很快 如将主键置于 where 列表中，MySQL 就能将该查询转换为一个常量
• eq_ref 在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的(如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是eq ref
• ref 当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是ref
• fulltext
• ref_or_null 当对普通二级索引进行等值匹配查询，该索引列的值也可以是NUL值时，那么对该表的访问方法#就可能是ref_or_null
• index_merge 单表访问方法时在某些场景下可以使用Intersection、union .Sort-Union这三种索引合并的方式来执行香询
• unique_subquery 是针对在一些包含IN子查询的查询语句中，如果查询优化器决定将IN子查询，转换为EXISTS子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的type 列的值就是unique_subquery
• index_subquery 利用索引来关联子查询，不再全表扫描
• range 只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的 where 语句中出现 了 between、<、>、in 等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而 结束语另一点，不用扫描全部索引
• index 出现index是sql使用了索引，但是没有通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组。覆盖索引是指不需要回表就能找到数据返回。key_part3 属于联合索引，key_part2 也属于联合索引
• all Full Table Scan，将遍历全表以找到匹配的行

SQL性能优化的目标：至少要达到 range级别，要求是ref级别，最好是consts级别。（阿里巴巴开发手册要求）

5.3.2.6、possible_keys和key

在EXPLAIN语句输出的执行计划中

possible_keys列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些。一般查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。
key列表示实际用到的索引有哪些，如果为NULL，则没有使用索引。

5.3.2.7、key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。 key_len 字段能够帮你检查是否充分的利用上了索引。ken_len 越长，说明索引使用的越充分。主要针对于联合索引，有一定的参考意义。

key_len的长度计算公式：

5.3.2.7、ref

当使用索引列等值查询时，与索引列进行等值匹配的对象信息。

5.3.2.8、rows

rows 列显示 MySQL 认为它执行查询时必须检查的行数。越少越好！

5.3.2.9、 filtered

某个表经过搜索条件过滤后剩余记录条数的百分比，如果使用的是索引执行的单表扫描，那么计算时需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。

5.3.2.10、Extra

顾名思义，Extra列是用来说明一些额外信息的，包含不适合在其他列中显示但十分重要的额外信息。我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。

6. 索引失效的场景

主要介绍mysql下inndb引擎索引失效的案例，不同数据库或者引擎会有所区别。

6.1 全值查询

即select * ,或者查询的字段按照顺序在索引中都可以匹配到

6.2 最佳左前缀法则

在MySQL建立联合索引时会遵守最佳左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。查询字段与索引字段顺序的不同会导致，索引无法充分使用，甚至索引失效！

结论：MySQL可以为多个字段创建索引，一个索引可以包括16个字段。对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。 如果查询条件中没有使用这些字段中第1个字段时，多列（或联合）索引不会被使用。

6.3 计算、函数、类型转换(自动或手动)导致索引失效

不在索引列上做任何操作（计算、函数、(自动 or 手动)类型转换），会导致索引失效而转向全表扫描。

6.4 范围条件右边的列索引失效

应用开发中范围查询，例如：金额查询，日期查询往往都是范围查询。应将查询条件放置where语句最后。（创建的联合索引中，务必把范围涉及到的字段写在最后）

因为假设范围索引列在前进行了索引（找到范围内的值后续的索引边失效了无法定位后续索引列的值）

6.5 不等于(!= 或者<>)索引失效

mysql 在使用不等于(!= 或者<>)时，有时会无法使用索引会导致全表扫描。

6.6 is null可以使用索引，is not null无法使用索引

结论：最好在设计数据表的时候就将字段设置为 NOT NULL 约束，比如你可以将INT类型的字段，默认值设置为0。将字符类型的默认值设置为空字符串(‘’)

拓展：同理，在查询中使用not like也无法使用索引，导致全表扫描

6.7 like以通配符%开头索引失效

拓展：Alibaba《Java开发手册》

【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

6.8 OR前后存在非索引的列，索引失效

在WHERE子句中，如果在OR前的条件列进行了索引，而在OR后的条件列没有进行索引，那么索引会失效。也就是说，OR前后的两个条件中的列都是索引时，查询中才使用索引。  

6.9使用索引的建议

• 对于单列索引，尽量选择针对当前query过滤性更好的索引。
• 在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
• 在选择组合索引的时候，尽量选择能够包含当前query中的where子句中更多字段的索引。
• 在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面。