MySql

MySQL 04_MySQL索引详解

一、索引的简介

1.1 索引的定义及作用

索引（index） 是存储引擎用于快速查找数据记录的一种数据结构，MySQL在进行数据查找时，首先查看查询条件是否命中某条索引，符合则通过索引查找相关数据，如果不符合则需要全表扫描，即需要一条一条地查找记录，直到找到与条件符合的记录。 如上图所示，数据库没有索引的情况下，数据分布在硬盘不同的位置上面，读取数据时，摆臂需要前后摆动查找数据，这样操作非常消耗时间。 如果数据顺序摆放，那么也需要从1到6行按顺序读取，这样就相当于进行了6次I0操作，依旧非常耗时。

如果不借助任何索引结构帮助我们快速定位数据的话，查找Col2=89这条记录，就要逐行去查找、去比较。

从Col 2=34开始，进行比较，发现不是，继续下一行。当前的表只有不到10行数据，但如果表很大的话，有上千万条数据，就意味着要做很多很多次磁盘I/0才能找到。

现在要查找Col 2=89这条记录，CPU必须先去磁盘查找这条记录，找到之后加载到内存，再对数据进行处理，这个过程最耗时间的就是磁盘I/O（涉及到磁盘的旋转时间（速度快）），磁头的寻道时间（速度慢-费时）；

假如给数据使用二叉树这样的数据结构进行存储： 给字段Col2添加了索引，就相当于在硬盘上为Col 2维护了一个索引的数据结构，即这个二叉搜索树。

二叉搜索树的每个结点存储的是（K，V）结构，key是Col2 的值，value是该key所在行的文件指针（地址）。比如: 该二叉搜索树的根节点就是:(34,0x07)，现在对Col2添加了索引，这时再去查找Col2=89这条记录的时候会先去查找该二叉搜索树〈二叉树的遍历查找），读34到内存，89>34；继续读右侧数据，读89到内存，89 == 89;找到数据返回，找到之后就根据当前结点的value快速定位到要查找的记录对应的地址， 可以发现，只需要查找两次就可以定位到记录的地址，查询速度就提高了。

这就是建索引的作用: 减少磁盘I/O的次数，加快查询速率。

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。

索引的本质：是数据结构，可以简单理解为排好序的快速查找数据结构，满足特定查找算法，这些数据结构以某种方式指向数据（页），这样就可以在这些数据结构的基础上实现高级查找算法。

索引是在存储引擎中实现的，因此每种存储引擎的索引不一定完全相同，并且每种存储引擎不一定支持所有索引类型。

  • 存储引擎可以定义每个表的 最大索引数 和 最大索引长度;
  • 所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节;
  • 有些存储引擎支持更多的索引数和更大的索引长度;

1.2 索引的优缺点

1、优点

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

2、缺点 索引在空间和时间上都会有消耗：

  • 每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，一棵很大的B+树由许多数据页组成，那就是很大的一片存储空间；
  • 创建索引和维护索引要耗费时间，并且随着数据量的增加，所耗费的时间也会增加；
  • 虽然索引大大提高了查询速度，同时却会降低更新表的速度，当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护(都需要去修改各个B+树索引)，这样就降低了数据的维护速度；

Tips:

  • B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录，还是内节点中的记录（也就是不论是用户记录还是索引项记录）都是按照索引列的值从小到大的顺序而形成了一个双向链表；而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收等操作来维护好节点和记录的排序；
  • 索引可以提高查询的速度，但是会影响插入记录的速度，再进行大规模的增删改操作的情况下，最好的办法是先删除表中的索引，然后操作完数据后再重新创建索引；

1.3 索引的分类及简介

MySQL的索引从不同维度分为如下三大类多种类型的索引：

  • 从 功能逻辑 上说，索引主要分为：普通索引、唯一索引、主键索引、全文索引、空间索引 和 哈希索引 等类型的索引;
  • 按照 作用字段个数 可划分为：单列索引 和 联合索引 2种类型的索引;
  • 按照 物理实现 方式可分为：聚簇索引 和 非聚簇索引 2种类型的索引;

Tips: 聚簇索引 和 非聚簇索引 将在 1.4 节详细介绍；

1、普通索引

在创建普通索引时，不附加任何限制条件，只是用于提高查询效率。

这类索引可以创建在任何数据类型中，其值是否唯一和非空，要由字段本身的完整性约束条件决定。

建立索引以后，可以通过索引进行查询。例如，在表student的字段name上建立一个普通索引，查询记录时就可以根据该索引进行查询。

2、唯一性索引

使用 UNIQUE 参数 可以设置索引为唯一性索引，在创建唯一性索引时，限制该索引的值必须是唯一的，但允许有空值。

在一张数据表里可以有多个唯一索引。例如，在表student的字段email中创建唯一性索引，那么字段email的值就必须是唯一的。

通过唯一性索引，可以更快速地确定某条记录。

3、主键索引

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

一张表里最多只有一个主键索引，这是由主键索引的物理实现方式决定的，因为数据存储在文件中只能按照一种顺序进行存储。

4、全文索引

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

全文索引非常适合大型数据集，对于小的数据集，它的用处比较小。

使用参数 FULLTEXT 可以设置索引为全文索引，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。

全文索引只能创建在 CHAR、VARCHAR 或 TEXT 类型 及 其系列的文本类型的字段上，查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。例如，表student的字段information是TEXT类型该字段包含了很多文字信息，在字段information上建立全文索引后，可以提高查询字段information的速度。

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

Tips: 自然语言搜索引擎 将计算 每一个文档对象和查询的 相关度（基于匹配的关键词的个数，以及关键词在文档中出现的次数）。在整个索引中出现次数越少的词语，匹配时的相关度就越高。相反，非常常见的单词将不会被搜索，如果一个词语在超过50%的记录中都出现了，那么自然语言的搜索将不会搜索这类词语。

MySQL数据库从3.23.23版开始支持全文索引，但MySQL5.6.4以前只有MyISAM引擎支持，5.6.4版本以后InnoDB才支持，但是 官方版本不支持中文分词，需要第三方分词插件。在5.7.6版本，MySQL内置了ngram全文解析器，用来支持亚洲语种的分词。

测试或使用全文索引时，要先看一下自己的MysQL版本、存储引擎和数据类型是否支持全文索引。

Tips: 随着大数据时代的到来，关系型数据库应对全文索引的需求已力不从心，逐渐被 solr、 ElasticSearch等专门的搜索引擎所替代。

5、空间索引

使用参数 SPATIAL 可以设置索引为空间索引。

空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。

MySQL中的空间数据类型包括 GEOMETRY、POINT、LINESTRING 和 POLYGON等。

目前只有MyISAM存储引擎支持空间检索，而且索引的字段不能为空值。

6、哈希索引

哈希索引是基于哈希表实现的索引类型，以键值对（key-value）的方式存储数据。使用Hash表存储表数据时，Key可以存储为索引列，Value可以存储行记录或者行磁盘地址。

Hash索引在等值查询时效率很高，时间复杂度为O(1)；但是不支持范围快速查找，范围查找时还是只能通过扫描全表方式。

MySQL 中 Memory 引擎中支持哈希索引。

7、单列索引

单列索引 是在表中的单个字段上创建索引，单列索引只根据该字段进行索引。

单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引，只要保证该索引只对应一个字段即可。

一个表可以有多个单列索引。

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

多列索引 是使用表的多个字段组合创建的索引，该索引指向创建时对应的多个字段，可以通过这几个字段进行查询，但是只有 查询条件中使用了这些字段中的第一个字段时才会被使用。

例如，在表中的字段id、name和gender上建立一个多列索引idx_id_name.gender，只有在查询条件中使用了 字段id 时该索引才会被使用。

使用组合索引时遵循最左前缀集合。

小结：不同的存储引擎支持的索引类型也不一样:

  • InnoDB: 支持 B+Tree、Full-text 等 索引，不支持 Hash索引;
  • MylSAM: 支持 B+Tree、Full-text等 索引，不支持 Hash索引;
  • Memory: 支持 B+Tree、Hash等 索引，不支持 Full-text索引;
  • NDB: 支持 Hash 索引，不支持 B+Tree、Full-text等 索引;
  • Archive: 不支持 B+Tree、Hash、Full-text等索引;

1.4索引的数据结构之 B+树原理简介

1、B树：改造二叉树

MySQL的数据是存储在磁盘文件中的，查询处理数据时，需要先把磁盘中的数据加载到内存中，磁盘IO操作非常耗时，所以MySQL优化的重点就是尽量减少磁盘IO操作。

访问二叉树的每个结点就会发生一次IO，如果想要减少IO操作，就需要降低树的高度。

假如二叉树的key为bigint=8字节，每个节点有两个指针，每个指针为4个字节，则二叉树的一个节点占用的空间16个字节（8+4*2=16）。

MySQL的InnoDB存储引擎一次IO会读取一页（默认一页16K）的数据量，而二叉树一次IO有效数据量只有16字节，空间利用率极低。

为了最大化利用一次IO空间，一个简单的想法是在每个节点存储多个元素，在每个节点尽可能多的存储数据。

每个节点可以存储1000个索引（16k/16=1000），这样就将二叉树改造成了多叉树，通过增加树的叉树，将树从高瘦变为矮胖。

现在构建 100W 条数据，树的高度只需要2层(1001个节点)就可以（1000 * 1000 = 100W），也就是说只需要2次磁盘IO就可以查询到数据。磁盘IO次数变少了，查询数据的效率也就提高了。

这种数据结构我们称为B树，如下图： B树是一种多叉平衡查找树，具有以下主要特点：

  • B树的节点中存储着多个元素，每个非叶子节点有多个分叉；
  • 节点中的元素包含键值和数据，节点中的键值从大到小排列，也就是说，在所有的节点都有储存数据；
  • 父节点当中的元素不会出现在子节点中；
  • 所有的叶子结点都位于同一层，叶子节点具有相同的深度，叶节点之间没有指针连接；

在B树中进行等值查询：

示例：如上图所示，假如要查询值等于10的数据，查询路径为：磁盘块1->磁盘块2->磁盘块5，查询流程如下步骤：

  • 第一次磁盘IO：将磁盘块1加载到内存中，在内存中从头遍历比较，10<15，走左路，到磁盘寻址磁盘块2;
  • 第二次磁盘IO：将磁盘块2加载到内存中，在内存中从头遍历比较，7<10 && 10<12，到磁盘中寻址定位到磁盘块5;
  • 第三次磁盘IO：将磁盘块5加载到内存中，在内存中从头遍历比较，找到10=10，取出data，如果data存储的是行记录，取出data，查询结束；如果存储的是磁盘地址，还需要根据磁盘地址到磁盘中取出数据，查询终止；

相比二叉平衡查找树，B树 在整个查找过程中，虽然数据的比较次数并没有明显减少，但是磁盘IO次数会大大减少。同时，由于比较是在内存中进行的，比较的耗时可以忽略不计。B树的高度一般2至3层就能满足大部分的应用场景，所以使用B树构建索引可以很好的提升查询的效率。

但是 B树不支持范围查询的快速查找，如果想查询15-30之间的数据，查到15之后，还要返回根节点重新遍历查找下一个数据，直到全部遍历找到。

如果data存储的是行记录，行的大小随着列数的增多，所占空间会变大。这时，一个页中可存储的数据量就会变少，树相应就会变高，磁盘IO次数就会变大。

2、B+树：改造B树

MySQL在B树的基础上继续改造出了 B+树 来构建索引， B+树 和 B树 最主要的区别在于非叶子节点是否存储数据：

  • B树：非叶子节点和叶子节点都会存储数据，直接导致树的高度增加，影响性能；
  • B+树：只有叶子节点才会存储数据，非叶子节点只存储键值，叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表；

B+树具备以下特征：

  • B+树的节点中可存N个key，N个key划分出N个区间，树的高度是相对矮的；
  • 所有父节点都会重复出现在子节点中，从左到右依次递增；
  • 非叶子结点只起索引作用， 叶子结点包含信息；
  • 非叶子结点可能在内存中缓存；
  • 所有的叶子结点都位于最后一层，叶节点之间首尾连接，所有key值都在子节点中存在，且最大为key；

B+树结构： 在B+树中进行范围查询：

示例：如上图的B+树中，假如想要查找9和26之间的数据，查找路径为：磁盘块1->磁盘块2->磁盘块6->磁盘块7，查找步骤如下所示：

  • 首先查找值等于9的数据，将值等于9的数据缓存到结果集, 这一步和前面等值查询流程一样，发生了三次磁盘IO。
  • 查找到9之后，底层的叶子节点是一个有序列表，我们从磁盘块6，键值9开始向后遍历筛选所有符合筛选条件的数据。
  • 第四次磁盘IO：根据磁盘6后继指针到磁盘中寻址定位到磁盘块7，将磁盘7加载到内存中，在内存中从头遍历比较，9<25<26，9<26<=26，将data缓存到结果集。
  • 主键具备唯一性（后面不会有<=26的数据），不需再向后查找，查询终止。将结果集返回给用户。

MySQL的许多索引都采用了B+树的数据结构。

1.5 聚集索引和非聚集索引 和 InnoDB的索引实现

1、聚集索引

聚簇索引 也称之为 聚集索引，是指索引项的排序方式和表中数据记录排序方式一致的索引，聚簇索引并不是一种单独的索引类型，而是一种数据存储方式，具体细节依赖于其实现方式。

InnoDB的主键索引实现： MySQL数据库的 InnoDB存储引擎中，B+树索引可以分为 聚簇索引（也称聚集索引，clustered index） 和 辅助索引（有时也称 非聚簇索引（non-clustered index） 或 二级索引（secondary index），这两种索引内部都是B+树，聚集索引的叶子节点存放着一整行的数据（一条完整的记录）。

每个InnoDB表都有一个聚簇索引(主键索引) ，使用B+树构建，叶子节点存储的数据是整行记录。一般情况下，聚簇索引等同于主键索引，当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚簇索引。

聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。

所以 聚簇索引 查询效率快，只要找到第一个索引值记录，其余就连续性的记录在物理也一样连续存放。

聚集索引对应的 缺点就是修改慢，因为为了保证表中记录的物理和索引顺序一致，在插入记录的时候，会对数据页重新排序（因为在真实物理存储器的存储顺序只能有一种，而插入新数据必然会导致主键索引树的变化，主键索引树的顺序发生了改变，叶子节点中存储的行数据也要随之进行改变，就会发生大量的数据移动操作，所以效率会慢）。

Tips: 因此，对于InnoDB表，一般都会定义一个自增的ID列为主键；更新主键的代价很高，因为将会导致被更新的行移动，所以一般不更新主键。

因为在物理内存中的顺序只能有一种，所以聚集索引在一个表中只能有一个。

在检索时，InnoDB使用此主键值在聚簇索引中搜索行记录，例如：

1

select * from user_innodb where id = 28;

  • 先在主键树中从根节点开始检索，将根节点加载到内存，比较28<75，走左路；（1次磁盘IO）
  • 将左子树节点加载到内存中，比较16<28<47，向下检索；（1次磁盘IO）
  • 检索到叶节点，将节点加载到内存中遍历，比较16<28，18<28，28=28。查找到值等于28的索引项，直接可以获取整行数据，最后将改记录返回给客户端；（1次磁盘IO）

InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录，这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。 上图是 InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录，这种索引叫做 聚集索引。因为 InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

2、非聚簇索引

在聚簇索引之上创建的索引称之为 辅助索引， 又称作 非聚簇索引，辅助索引访问数据总是需要二次查找，辅助索引叶子节点存储的不再是行的物理位置，而是主键值，通过辅助索引首先找到的是主键值，再通过主键值找到数据行所在的数据页，再通过数据页中的Page Directory找到数据行。

InnoDB的辅助索引（非聚簇索引）实现: Innodb辅助索引的叶子节点并不包含行记录的全部数据，叶子节点除了包含键值外，还包含了相应行数据的聚簇索引键。

非聚簇索引 一般也采用B+树结构，非聚集索引的叶子层并不和实际数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针方式。

辅助索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个辅助索引。在innodb中有时也称辅助索引为二级索引。

InnoDB的所有辅助索引都引用主键作为data域。例如，下图为定义在Col3上的一个辅助索引： InnoDB 表是基于聚簇索引建立的，因此InnoDB 的索引能提供一种非常快速的主键查找性能。它的辅助索引（Secondary Index， 也就是非主键索引）也会包含主键列，所以，如果主键定义的比较大，其他索引也将很大。如果想在表上定义很多索引，则尽量把主键定义得小一些（InnoDB 不会压缩索引）。 辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

示例：

1

select * from t_user_innodb where age=19;

Tips:

  • 为什么不建议使用过长的字段作为主键，因为所有二级索引都引用主键索引，过长的主键索引会令二级索引变得过大; 用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一个B+Tree，非单调的主键会造成在插入新纪录时，数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择;

根据在辅助索引树中获取的主键id，到主键索引树检索数据的过程称为回表查询。

避免回表: 在InnoDB的存储引擎中，使用辅助索引查询的时候，因为辅助索引叶子节点保存的数据不是当前记录的数据而是当前记录的主键索引，索引如果需要获取当前记录完整数据就必然需要根据主键值从主键索引继续查询。这个过程称为回表，回表必然是会消耗性能影响性能。那如何避免呢？

  • 使用索引覆盖，举个例子：现有User表（id(PK),name(key),sex,address,hobby…）
  • 如果在一个场景下，select id,name,sex from user where name =‘zhangsan’;这个语句在业务上频繁使用到，而user表的其他字段使用频率远低于它，在这种情况下，如果在建立 name 字段的索引的时候，不是使用单一索引，而是使用联合索引（name，sex）这样的话再执行这个查询语句时根据辅助索引查询到的结果就可以获取当前语句的完整数据，这样就可以有效地避免了回表再获取sex的数据。

Tips:

  • InnoDB引擎创建的主键索引就是聚簇索引。聚簇索引包含主键id，数据库对应行数据和指针并将这些数据存储在B+Tree的叶子结点上。
  • 其余非主键索引全部都是辅助索引（非聚簇索引），对于InnoDB存储引擎创建的辅助索引（非聚簇索引），索引内容只包含当前字段的内容与主键id，通过查询主键id进行数据的二次查找。

Tips:

  • MySQL数据库目前只有InnoDB数据引擎支持聚簇索引，而MyISAM并不支持聚簇索引。
  • 由于数据物理存储排序方式只有一种，所以每个MySQL的 表只能有一个聚簇索引, 一般情况下就是该表的主键。

1.6 MyISAM引擎的索引（非聚簇索引）实现

MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。 InnoDB中索引即数据，也就是聚簇索引的那颗B+树的叶子节点中已经把所有完整的用户记录都包含了，而MyISAM的索引方案虽然也使用树形结构，但是却将索引和数据分开存储：

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

1、MyISAM引擎的主键索引

MyISAM引擎 也使用B+Tree作为索引结构，B+树叶节点的data域存放的是数据记录的地址，下图是MyISAM主键索引的原理图： 这里假设表一共有三列，以Col1为主键，上图是一个MyISAM表的主索引（Primary key）示意，可以看出MyISAM的索引文件仅仅保存数据记录的地址。

2、MyISAM引擎的辅助索引（Secondary key）

在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果在Col2上建立一个辅助索引，则此索引的结构如下图所示： 同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做 非聚集的，之所以这么称呼是为了与InnoDB的聚集索引区分：

MyISM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致 —— 只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。

由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

1.7 MyISAM 与 InnoDB 的索引对比

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

两种引擎中索引的区别：

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

聚簇索引和非聚簇索引的区别

  • 聚簇索引的叶子节点存放的是主键值和数据行，支持覆盖索引；二级索引的叶子节点存放的是主键值或指向数据行的指针。
  • 由于节子节点(数据页)只能按照一颗B+树排序，故一张表只能有一个聚簇索引。辅助索引的存在不影响聚簇索引中数据的组织，所以一张表可以有多个辅助索引

为了更形象说明这两种索引的区别，假想一个表如下图存储了4行数据，其中Id作为主键索引，Name作为辅助索引，图示清晰的显示了聚簇索引和非聚簇索引的差异：

在Innodb下主键索引是聚集索引，在Myisam下主键索引是非聚集索引。

1.8 联合索引

联合索引 是同时为多个列建立的索引，比方说在index_demo表中想让B+树按照c2和c3列的大小进行排序。

1
2
3
4
5
6
7

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

这个包含两层含义：

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

注意：

  • 每条目录(索引)项记录都由c2、c3、页号三部分组成，各条记录先按照c2列的值进行排序，如果记录的c2列相同，则按照c3列的值进行排序；
  • B+树叶子节点 处的用户记录有c2、c3 和 主键c1列 组成；

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

  • 建立联合索引 只会建立如上图一样的1棵B+树；
  • 为c2 和 c3列分别建立索引 会分别以c2和c3列的大小为排序规则建立2棵B+树；

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

1、根页面位置一经创建物理位置（页号）就不再改变

B+树的形成过程：

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

这个过程特别注意的是：一个B+树索引的根节点自诞生之日起，便不会再移动。

这样只要对某个表建立一个索引，那么它的根节点的页号便会被记录到某个地方，然后凡是InnoDB存储引擎需要用到这个索引的时候，都会从那个固定的地方取出根节点的页号，从而来访问这个索引。

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

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

c1 c2 c3
1 1 ‘u’
3 1 ’d’
5 1 ‘y’
7 1 ‘a’

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

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

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

也就是把主键值也添加到二级索引内节点中的目录项记录了，这样就能保证B+树每一层节点中各条目录项记录除页号这个字段外是唯一的，所以为c2列建立二级索引后的示意图实际上应该是这样子的： 这样再插入记录(9, 1, ‘c’)时，由于页3中存储的目录项记录是由c2列+主键+页号的值构成的，可以先把新记录的c2列的值和页3中各目录项记录的c2列的值作比较，如果c2列的值相同的话，可以接着比较主键值，因为B+树同一层中不同目录项记录的c2列+主键的值肯定是不一样的，所以最后肯定能定位唯一的一条目录项记录，在本例中最后确定新记录应该被插入到页5中。

3、一个页面最少可以存储2条记录

一个B+树只需要很少的层级就可以轻松存储数亿条记录，查询速度相当不错！这是因为B+树本质上就是一个大的多层级目录，每经过一个目录时都会过滤掉许多无效的子目录，直到最后访问的存储真实数据的目录。

那如果一个大的目录中只存放一个子目录是个啥效果呢？那就是目录层级非常非常非常多，而且最后的那个存放真实数据的目录中存放一条记录。费了半天劲只能存放一条真实的用户记录？

所以 InnoDB的一个数据页至少可以存放两条记录。

1.10 Hash结构简介

Hash本身是一个函数，又被称为散列函数，它可以帮助我们大幅提升检索数据的效率。

Hash算法是通过某种确定性的算法（比如MD5、SHA1、SHA2、SHA3）将输入转变为输出，相同的输入永远可以得到相同的输出，假设输入内容有微小偏差，在输出中通常会有不同的结果。

加速查找速度的数据结构，常见的有两类:

  • 树，例如平衡二叉搜索树，查询/插入/修改/删除的平钧时间复杂度都是0(log2N) ;
  • 哈希，例如HashMap，查询/插入/修改/删除的平均时间复杂度都是0( 1); 采用Hash进行检索效率非常高，基本上一次检索就可以找到数据，而B+树需要自顶向下一次查找，多次访问节点才能找到数据，中间需要多次I/O操作，从效率来说 Hash比B+树更快。

在Hash的方式下，一个元素k处于h(k)中，即利用哈希函数h，根据关键字k计算出槽的位置。函数h将关键字域映射到哈希表T[0…m-1]的槽位上。

Tips： Hash函数可能将两个不同的关键字映射（hash）到相同的位置（hash值），这种现象称为： hash碰撞； 发生 hash碰撞 时 一般可以采用 *链接法 将hash冲突的元素放在同一个链表或数组中 来解决冲突；

Hash结构效率高，但也存在一些突出的缺点

  • Hash索引仅能满足（=）和 IN 查询；如果进行范围查询，哈希型的索引，时间复杂度会退化为o(n);而树型的“有序"”特性，依然能够保持o(log2N)的高效率；
  • Hash 索引还有一个缺陷，数据的存储是没有顺序的，在ORDER BY的情况下，使用Hash索引还需要对数据重新排序；
  • 对于联合索引的情况，Hash值是将联合索引键合并后一起来计算的，无法对单独的一个键或者几个索引键进行查询；
  • 对于等值查询来说，通常Hash 索引的效率更高，不过也存在一种情况，就是索引列的重复值如果很多，效率就会降低；这是因为遇到Hash冲突时，需要遍历桶中的行指针来进行比较，找到查询的关键字，非常耗时。所以，Hash索引通常不会用到重复值多的列上，比如列为性别、年龄的情况等；

Hash索引的适用性:

Hash索引存在着很多限制，相比之下在数据库中B+树索引的使用面会更广，不过也有一些场景采用Hash索引I效率更高，比如在键值型（Key-value）数据库中， Redis存储的核心就是 Hash表。

MysQL中的Memory存储引擎支持 Hash存储，如果需要用到查询的临时表时，就可以选择Memory存储引擎，把某个字段设置为Hash索引，比如字符串类型的字段，进行Hash 计算之后长度可以缩短到几个字节。

当字段的重复度低，而且经常需要进行等值查询的时候，用Hash索引是个不错的选择。

InnoDB本身不支持 Hash索引，但是提供 自适应Hash索引(Adaptive Hash lndex)；如果某个数据经常被访问，当满足一定条件的时候，就会将这个数据页的地址存放到Hash表中；这样下次查询的时候，就可以直接找到这个页面的所在位置，这让B+树也具备了Hash索引的优点。 采用自适应Hash索引目的是方便根据SQL的查询条件加速定位到叶子节点，特别是当B+树比较深的时候，通过自适应Hash索引可以明显提高数据的检索效率。

查看是否开启了自适应 Hash:

1

show variables like '%adaptive_hash_index';

二、MySQL索引的创建、查看 与 删除

2.1 索引的创建（声明）

MySQL支持多种方法在单个或多个列上创建索引:

  • 在创建表的定义语句 CREATE TABLE 中指定索引列
  • 使用 CREATE INDEX 语句在已存在的表上添加索引
  • 使用 ALTER TABLE 语句在存在的表上创建/修改索引

1、创建表的时候创建索引

使用 CREATE TABLE 创建表时，除了可以定义列的数据类型外，还可以定义主键约束、外键约束 或者 唯一性约束，不论创建哪种约束，在定义约束的同时相当于在指定列上创建了一个索引（隐式创建）。

隐式创建索引：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

CREATE TABLE dept(
    dept_id INT PRIMARY KEY AUTO_INCREMENT,  # PRIMARY KEY 定义主键，自动（隐式）创建了主键索引
    dept_name VARCHAR(20) 
);

CREATE TABLE emp(
    emp_id INT PRIMARY KEY AUTO_INCREMENT, # PRIMARY 定义主键，自动（隐式）创建了主键索引
    emp_name VARCHAR(20) UNIQUE,
    dept_id INT,
    CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept( dept_id)
);

显式创建表时创建索引的基本语法格式如下:

1
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 指定升序或者降序的索引值存储；

创建普通索引：

1
2
3
4
5
6
7
8
9

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) # 在 year_publication 字段上创建普通索引
);

创建唯一索引：

1
2
3
4
5

CREATE TABLE test1( 
    id INT NOT NULL, 
    name varchar(30) NOT NULL, 
    UNIQUE INDEX uk_idx_id(id) # 使用 UNIQUE 在 id 字段上创建名为 uk_idx_id 的 唯一索引
);

创建主键索引

1
2
3
4
5

CREATE TABLE student ( 
    id INT(10) PRIMARY KEY AUTO_INCREMENT,  # PRIMARY KEY 定义主键，自动（隐式）创建了主键索引
    student_no VARCHAR(200),
    student_name VARCHAR(200)
);

删除主键索引

1

ALTER TABLE student drop PRIMARY KEY;

创建单列索引

1
2
3
4
5

CREATE TABLE test2( 
    id INT NOT NULL, 
    name CHAR(50) NULL, 
    INDEX single_idx_name(name(20))  # 在name字段创建名为single_idx_name的索引， 索引长度为 20 
);

创建组合索引

1
2
3
4
5
6
7

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) # 使用 id、name 和 age 字段创建名为 multi_idx 的组合索引
);

创建全文索引

1
2
3
4
5
6
7

CREATE TABLE `papers` ( 
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
    `title` varchar(200) DEFAULT NULL, 
    `content` text, 
    PRIMARY KEY (`id`), 
    FULLTEXT KEY `title` (`title`,`content`)  # 使用 FULLTEXT 在 title 和 content 字段上创建名为 title 的全文索引
) ENGINE=MyISAM DEFAULT CHARSET=utf8; # MySQL数据库从3.23.23版开始支持全文索引，但MySQL5.6.4以前只有MyISAM支持

全文索引用match+against方式查询：

1

SELECT * FROM papers WHERE MATCH(title,content) AGAINST ('查询字符串');

创建空间索引

1
2
3
4

CREATE TABLE test5( 
    geo GEOMETRY NOT NULL, 
    SPATIAL INDEX spa_idx_geo(geo) # 使用 SPATIAL 在 geo（GEOMETRY类型）自动上创建 名为 spa_idx_geo 的 空间索引， 空间索引的字段不能为空值
) ENGINE=MyISAM; # 只有MyISAM存储引擎支持空间检索

2、 在已经存在的表上创建索引

使用ALTER TABLE语句创建索引

1

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

使用CREATE INDEX创建索引

1

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON table_name (col_name[length],...) [ASC | DESC]

2.2 查看已经创建的索引：

1
2
3

SHOW CREATE TABLE book; # 查看创建表结构的完整SQL语句（包括索引信息）

SHOW INDEX FROM book; # 查看表上创建的索引信息

2.3 索引的删除

使用ALTER TABLE删除索引

1

ALTER TABLE table_name DROP INDEX index_name;

使用DROP INDEX语句删除索引

1

DROP INDEX index_name ON table_name;

Tips:

  • 删除（drop）表时，在表上创建的索引页随之被一并被删除；
  • 删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除；如果组成索引的所有列都被删除，则整个索引将被删除；

三、索引的设计原则

索引的存在和使用是要消耗系统的cpu 和内存的，为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。

索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍。

高效的索引对于获得良好的性能非常重要，所以，在设计索引时应该考虑相应准则。

3.1 哪些情况适合创建索引（索引创建的一般准则）

1、字段的数值有唯一性的限制

索引本身可以起到约束的作用，比如唯一索引、主键索引都可以起到唯一性约束的，因此在数据表中，如果某个字段是唯一的，就可以直接创建 唯一性索引，或者 主键索引,这样可以更快速地通过该索引来确定某条记录。

Tips:

  • 业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引（来源：Alibaba）;
  • 不要以为唯一索引影响了insert速度，这个速度损耗可以忽略，但提高查找速度是明显的;

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

某个字段在SELECT语句的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了。

尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。

3、经常 GROUP BY 和 ORDER BY的列

索引就是让数据按照某种顺序进行存储或检索，因此当使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据进行排序的时候，就需要对分组或者排序的字段进行索引。

如果待排序的列有多个，那么可以在这些列上建立组合索引。

4、UPDATE、DELETE的 WHERE条件列

对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作，如果对 WHERE 字段创建了索引，就能大幅提升效率。

这是因为需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或删除，如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。

5、DISTINCT字段需要创建索引

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

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

首先，连接表的数量尽量不要超过 3 张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。

其次，对 WHERE 条件创建索引，因为 WHERE 才是对数据条件的过滤，如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的（这将导致查找数据量的巨大增长）。

最后，对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致。

7、使用列的类型小的创建索引

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

在定义表结构的时候要显式的指定列的类型，以整数类型为例，有 TINYINT、MEDIUMINT、INT、BIGINT等，它们占用的存储空间依次递增，能表示的整数范围当然也是依次递增。

如果想要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，比如能使用 INT 就不要使用 BIGINT，能使用 MEDIUMINT 就不要使用 INT；这是因为：

  • 数据类型越小，在查询时进行的比较操作越快；
  • 数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘I/O带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率；

Tips: 这个建议对于表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其它所有的二级索引的节点处都会存储一份记录的主键值，如果主键使用更小的数据类型，也就意味着节省更多的存储空间和更高效的I/O。

8、使用字符串前缀创建索引（指定索引长度）

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

  • B+树索引中的记录需要把该列的完整字符串存储起来，更费时，而且字符串越长，在索引中占用的存储空间越大；
  • 如果B+树索引中索引列存储的字符串很长，那在做字符串比较时会占用更多的时间；

可以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引，这样在查找记录时虽然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串值，既节约空间，又减少了字符串的比较时间，还大体能解决排序的问题。

区分度计算公式：

1

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

索引列前缀对排序的影响：使用索引列前缀的方式 无法支持使用索引排序，只能使用文件排序

Tips: 在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。

说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达 90% 以上，可以使用 count(distinct left(列名, 索引长度))/count(*) 来计算区分度。

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

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

最好为列基数大的列建立索引，为基数太小的列建立索引效果可能不好。

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

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

10、使用最频繁的列放到联合索引的左侧

组合索引遵循最左前缀集合。只有查询条件中使用了这些字段中的第一个字段时才会被使用

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

3.2 限制索引的数目

在实际使用索引时，需要注意平衡，索引的数目不是越多越好，需要限制每张表上的索引数量，建议单张表索引数量不超过6个索引，原因：

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

3.3 哪些情况不适合创建索引

  • 在 where 中使用不到的字段，不要设置索引
  • 数据量小的表最好不要使用索引
  • 有大量重复数据的列上不要建立索引
  • 避免对经常更新的表创建过多的索引
    • 第一层含义：频繁更新的字段不一定要创建索引，因为更新数据的时候，也需要更新索引，如果索引太多，在更新索引的时候也会造成负担，从而影响效率；
    • 第二层含义：避免对经常更新的表创建过多的索引，并且索引中的列尽可能少，此时虽然提高了查询速度，同时却会降低更新表的速度；
  • 不建议用无序的值作为索引
    • 例如身份证、UUID(在索引比较时需要转为ASCII，并且插入时可能造成页分裂)、MD5、HASH、无序长字符串等；
  • 及时删除不再使用或者很少使用的索引
    • 表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要，数据库管理员应当定期找出这些索引，将它们删除，从而减少索引多更新操作的影响；
  • 不要定义冗余或重复的索引

四、MySQL8.0索引新特性

4.1 支持降序索引

降序索引以降序存储键值，虽然在语法上，从MysQL 4版本 开始就已经支持降序索引的语法了，但实际上该 DESC 定义是被忽略的，直到 MysQL 8.x版本 才开始真正支持降序索引（仅限于InnoDB存储引擎）。

MySQL在8.0版本之前创建的仍然是升序索引，使用时进行反向扫描，这大大降低了数据库的效率。

在某些场景下，降序索引意义重大。例如，如果一个查询，需要对多个列进行排序，且顺序要求不一致，那么使用降序索引将会避免数据库使用额外的文件排序操作，从而提高性能。

举例: 分别在MySQL 5.7版本和MySQL 8.0版本中创建数据表ts1，结果如下:

1

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

在MySQL 5.7版本中查看数据ts1的结构，如下： 从结果中可以看出，索引仍然是默认的升序。

在MySQL8.0版本中查看数据ts1的结构，如下： 从结果可以看出，索引已经是降序了。

继续测试降序索引在执行计划中的表现, 分别在MySQL 5.7版本和MySQL 8.0版本的数据表ts1中插入800条随机数据，执行语句如下:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

DELIMITER //
CREATE PROCEDURE ts_insert()
BEGIN
	DECLARE i INT DEFAULT 1;
	WHILE i < 800
	DO
		insert into ts1 select rand()*80080, rand ()*80000;
		SET i = i + 1;
	END WHILE;
	commit;
END //
DELIMITER ;

#调用
CALL ts_insert();

在MySQL5.7版本中查看数据表ts1的执行计划：

1

EXPLAIN SELECT * FROM ts1 ORDER BY a,b DESC LIMIT 5;

结果可以看到，执行计划中扫描数为799，而且使用了Using filesort。

Tips:

  • Using filesort是MysQL中一种速度比较慢的外部排序，能避免是最好的。
  • 多数情况下，管理员可以通过优化索引来尽量避免出现Using filesort，从而提高数据库执行速度。

将排序条件修改为order by a desc, b desc后，下面来对比不同版本中执行计划的效果:

分别在MySQL 5.7版本 和 MySQL 8.0版本 中查看数据表ts1的执行计划:

1

EXPLAIN SELECT * FROM ts1 ORDER BY a DESC, b DESC LIMIT 5;

可以看到修改后MySQL 5.7.的执行计划要明显好于MySQL 8.0。

4.2 隐藏索引

1、隐藏索引的简介

在MysQL 5.7版本及之前，只能通过显式的方式删除索引。

此时，如果发现删除索引后出现错误，只能通过显式创建索引的方式将删除的索引创建回来。

如果数据表中的数据量非常大，或者数据表本身比较大，这种操作就会消耗系统过多的资源，操作成本非常高。

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

这种通过先将索引设置为隐藏索引，再删除索引的方式就是 软删除。

Tips: 如果想验证某个索引删除之后的查询性能影响，就可以暂时先隐藏该索引

注意: 主键不能被设置为隐藏索引。当表中没有显式主键时，表中第一个唯一非空索引会成为隐式主键，也不能设置为隐藏索引。

索引默认是可见的，在使用 CREATE TABLE、CREATE INDEX 或 ALTER TABLE 等语句时可以通过 VISIBLE 或 INVISIBLE 关键词设置索引的可见性。

2、创建表时直接创建隐藏索引

1
2
3
4
5
6
7

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

3、在已经存在的表上创建隐藏索引

1

CREATE INDEX indexname ON tablename(propname[(length)]) INVISIBLE;

4、通过ALTER TABLE语句创建

1

ALTER TABLE tablename ADD INDEX indexname (propname [(length)]) INVISIBLE;

5、切换索引可见状态

1
2

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

当索引被隐藏时，它的内容仍然是和正常索引一样实时更新的。

如果一个索引需要长期被隐藏，那么可以将其删除，因为索引的存在会影响插入、更新和删除的性能。

通过设置隐藏索引的可见性可以查看索引对调优的帮助。

4.3 使隐藏索引对查询优化器可见

在MysQL 8.x版本中，为索引提供了一种新的测试方式，可以通过查询优化器的一个开关(use_invisible_indexes)来打开某个设置，使隐藏索引对查询优化器可见。如果 use_invisible_indexes 设置为off(默认)，优化器会忽略隐藏索引；如果设置为on，即使隐藏索引不可见，优化器在生成执行计划时仍会考虑使用隐藏索引。

1、在MySQL命令行执行如下命令查看查询优化器的开关设置。

1

mysql> select @@optimizer_switch \G;

在输出的结果信息中找到如下属性配置:

1

use_invisible_indexes=off

此属性配置值为off，说明隐藏索引默认对查询优化器不可见。

2、使隐藏索引对查询优化器可见 要使隐藏索引对查询优化器可见，需要在MySQL命令行执行如下命令:

1
2

mysql> set session optimizer_switch="use_invisible_indexes=on";
Query oK,0 rows affected (0.80 sec)

SQL语句执行成功，再次查看查询优化器的开关设置。

五、索引优化与查询优化

5.1 数据库调优都有哪些维度

数据库性能调优可以从以下几个纬度进行：

  • 索引失效、没有充分利用到索引 —― 合理建立索引
  • 关联查询太多JOIN(设计缺陷或不得已的需求) —— SQL优化。
  • 服务器调优及各个参数设置（缓冲、线程数等）―― 调整my.cnf
  • 数据过多 ―― 分库分表

5.2 索引失效案例

MySQL中提高性能的一个最有效的方式是对数据表设计合理的 索引。索引提供了高效访问数据的方法，并且加快查询的速度，因此索引对查询的速度有着至关重要的影响。

  • 使用索引可以快速地定位表中的某条记录，从而提高数据库查询的速度，提高数据库的性能；
  • 如果查询时没有使用索引，查询语句就会扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢；

MySQL 大多数情况下都（默认）采用B+树来构建索引。只是空间列类型的索引使用R-树，并且MEMORY表还支持hash索引。

其实，用不用索引，最终都是优化器说了算。优化器是基于什么的优化器？基于cost开销(CostBaseOptimizer)，它不是基于规则(Rule-BasedOptimizer)，也不是基于语义。怎么样开销小就怎么来。另外，SQL语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。

1、全值匹配

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

# 系统中经常出现的sql语句如下：
EXPLAIN SELECT SQL NO CACHE * FROM student WHERE age=30;
EXPLAIN SELECT sQL NO CACHE * FROM student WHERE age=30 AND classId=4;
EXPLAIN SELECT SQL NO CACHE * FROM student WHERE age=30 AND classId=4 AND NAMB='abcd';

# 建立索引前执行时间：  （0.28 sec）
SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME='abcd';

# 建立索引
CREATE INDEX idx_age ON student(age);
CREATE INDEX idx_age_classid ON student(age, classId);
CREATE INDEX idx_age_classid_name ON student (age, classId, NAME);

# 建立索引后执行时间：  （0.01 sec）

2、最佳左前缀法则

在MySQL建立联合索引时会遵守 最佳左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

MySQL可以为多个字段创建索引，一个索引可以包括16个字段。

对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。

如果查询条件中没有使用这些字段中第1个字段时，多列（或联合）索引不会被使用。

3、主键插入顺序 对于一个使用InnoDB存储引擎的表来说，在没有显示的创建索引时，表中的数据实际上都是存储在聚簇索引的叶子节点的。而记录又存储在数据页中的，数据页和记录又是按照记录主键值从小到大的顺序进行排序，所以如果插入的记录的主键值是依次增大的话，那每插满一个数据页就换到下一个数据页继续插，而如果插入的主键值忽小忽大的话，则可能会造成页面分裂和记录移位。

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

1
2

# 索引失效
SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name, 3) = 'abc';

5、类型转换导致索引失效

1
2
3
4

# 索引失效
SELECT SQL_NO_CACHE * FROM student WHERE name= 123;
# 使用到索引
SELECT SQL_NO_CACHE * FROM student WHERE name='123';

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

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

1
2
3
4

CREATE INDEX idx_age_classId_name ON student(age, classId, name);

# classId 范围条件列索引失效
SELECT SQL_NO_CACHE * FROM student WHERE age=30 ADN classId>20 AND name='abc';

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

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

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

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

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

拓展：Alibaba《Java开发手册》 【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

1
2
3
4

# 使用到索引
SELECT SQL_NO_CACHE * FROM student WHERE  name LIKE 'abc%';
# 索引失效
SELECT SQL_NO_CACHE * FROM student WHERE  name LIKE '%abc';

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

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

11、数据库和表的字符集统一使用utf8mb4

统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的字符集进行比较前需要进行转换会造成索引失效。

一般性建议：

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

5.3 关联查询优化

join方式连接多个表，本质就是各个表之间数据的循环匹配。

结论1：对于内连接来说，查询优化器可以决定谁来作为驱动表，谁作为被驱动表出现 结论2：对于内连接来讲，如果表的连接条件中只能有一个字段有索引，则有索引的字段所在的表会被作为被驱动表 结论3：对于内连接来说，在两个表的连接条件都存在索引的情况下，会选择小表作为驱动表。小表驱动大表

MySQL5.5版本之前，只支持一种表间关联方式，就是嵌套循环(Nested Loop Join)。如果关联表的数据量很大，则join关联的执行时间会非常长。

在MySQL5.5以后的版本中，MySQL通过引入BNLJ算法来优化嵌套执行。

1、Simple Nested-Loop Join（简单嵌套循环连接）

算法相当简单，从表A中取出一条数据1，遍历表B，将匹配到的数据放到result…以此类推，驱动表A中的每一条记录与被驱动表B的记录进行判断: 可以看到这种方式效率是非常低的，以上述表A数据100条，表B数据1000条计算，则A*B=10万次，开销统计如下:

开销统计 SNLJ
外表扫描次数
内表扫描次数 A
读取记录数 A+B*A
JOIN比较次数 B*A
回表读取记录次数 0

当然mysql肯定不会这么粗暴的去进行表连接，所以就出现了后面的两种Nested-Loop Join优化算法。

2、Index Nested-Loop Join（索引嵌套循环连接）

Index Nested-Loop Join 其优化的思路主要是为了减少内层表数据的匹配次数，所以要求被驱动表上必须有索引才行。

通过外层表匹配条件直接与内层表索引进行匹配，避免和内层表的每条记录去比较，这样极大的减少了对内层表的匹配次数。 驱动表中的每条记录通过被驱动表的索引进行访问，因为索引查询的成本是比较固定的，故mysql优化器都倾向与使用记录数少的表作为驱动表（外表）。

如果被驱动表加索引，效率是非常高的，但如果索引不是主键索引，还得进行一次回表查询。相比下，如果被驱动表的索引是主键索引，效率会更高。

3、Block Nested-Loop Join（块嵌套循环连接）

如果存在索引，那么会使用index的方式进行join，如果join的列没有索引，被驱动表要扫描的次数太多了。每次访问被驱动表，其表中的记录都会被加载到内存中，然后再从驱动表中取一条与其匹配，匹配结束后清除内存，然后再从驱动表中加载一条记录，然后把被驱动表的记录再加载到内存匹配，这样周而复始，大大增加了IO的次数。为了减少被驱动表的IO次数，就出现了 Block Nested-Loop Join 的方式。

不再是逐条获取驱动表的数据，而是一块一块的获取，引入了join buffer缓冲区，将驱动表join相关的部分数据列（大小受join buffer的限制）缓存到join buffer中，然后全表扫描被驱动表，被驱动表的每一条记录一次性和join buffer中的所有驱动表记录进行匹配（内存中操作），将简单嵌套循环中的多次比较合并成一次，降低了被驱动表的访问频率。

注意：

  • 这里缓存的不只是关联表的列，select后面的列也会缓存起来;
  • 在一个有N个join关联的sql中会分配N-1个join buffer，所以查询的时候尽量减少不必要的字段，可以让join buffer中存放更多的列；

相关参数设置：

  • block_nested_loop：
    • 通过 show variables like '%optimizer_switch%' 查看 block_nested_lopp 状态，默认是开启的。
  • join_buffer_size：
    • 驱动表能不能一次加载完，要看join buffer能不能存储所有的数据，默认情况下 join_buffer_size=256k。
    • join_buffer_size的最大值在32位系统可以申请4G，而在64位操作系统下可以申请大于4G的Join buffer空间（64位windows除外，其最大值会被截断为4GB并发出警告）。

JOIN小结：

  • 整体效率比较：INLJ > BNLJ > SNLJ
  • 永远用小结果集驱动大结果集(其本质就是减少外层循环的数据数量) (小的度量单位指的是 表行数 * 每行大小) 
    1
2
3
4
5

    # 推荐
select t1.b,t2.* from t1 straight_join t2 on (t1.b=t2.b) where t2.id<=180;

# 不推荐
select t1.b,t2.* from t2 straight.join t1 on (t1.b=t2.b) where t2.id<=100;
  • 为被驱动表匹配的条件增加索引(减少内层表的循环匹配次数)
  • 增大join buffer size的大小(一次缓存的数据越多，那么内层包的扫表次数就越少)
  • 减少驱动表不必要的字段查询（字段越少，join buffer所缓存的数据就越多)

4、Hash Join

从MySQL的8.0.20版本开始将废弃BNLJ，因为从MySQL8.0.18版本开始就加入了hash join默认都会使用hash join

  • Nested Loop：对于被连接的数据子集较小的情况下，Nested Loop是个较好的选择。
  • Hash Join 是做大数据集连接时的常用方式，优化器使用两个表中较小（相对较小）的表利用Join Key在内存中建立散列值，然后扫描较大的表并探测散列值，找出与Hash表匹配的行：
    • 这种方式适用于较小的表完全可以放入内存中的情况，这样总成本就是访问两个表的成本之和；
    • 在表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要求有较大的临时段从而尽量提高I/O的性能；
    • 它能够很好的工作于没有索引的大表和并行查询的环境中，并提供最好的性能；
    • Hash Join只能应用于等值连接，这是由Hash的特点决定的；

小结：

  • 保证被驱动表的JOIN字段已经创建了索引需要JoIN的字段，数据类型保持绝对一致。
  • LEFT JOIN时，选择小表作为驱动表，大表作为被驱动表。减少外层循环的次数。
  • INNER JOIN时，MySQL会自动将小结果集的表选为驱动表。选择相信MySQL优化策略。
  • 能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数)
  • 不建议使用子查询，建议将子查询sQL拆开结合程序多次查询，或使用JOIN来代替子查询。
  • 衍生表建不了索引

5.4 子查询优化

MySQL从4.1版本开始支持子查询，使用子查询可以进行SELECT语句的嵌套查询，即一个SELECT查询的结果作为另一个SELECT语句的条件，子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。

子查询是 MySQL、的一项重要的功能，可以通过一个SQL、语句实现比较复杂的查询，但是，子查询的执行效率不高。 原因：

  • 执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录，查询完毕后，再撤销这些临时表，这样会消耗过多的CPU和IO资源，产生大量的慢查询；
  • 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响；
  • 对于返回结果集比较大的子查询，其对查询性能的影响也就越大；

在MySQL中，可以使用连接（JOIN）查询来替代子查询，连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能就会更好。

Tips: 尽量不要使用NOT IN 或者 NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL替代

5.5 排序优化

SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中 避免全表扫描，在 ORDER BY 子句避免使用 FileSort排序。当然，某些情况下全表扫描，或者 FileSort 排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。

尽量使用 Index 完成 ORDER BY排序，如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列；如果不同就使用联合索引。

无法使用 Index 时，需要对 FileSort 方式进行调优。

所有的排序都是在条件过滤之后才执行的。所以，如果条件过滤掉大部分数据的话，剩下几百几千条数据进行排序其实并不是很消耗性能，即使索引优化了排序，但实际提升性能很有限。

Tips:

  • 两个索引同时存在，mysql自动选择最优的方案。但是，随着数据量的变化，选择的索引也会随之变化的。
  • 当 范围条件 和 group by或者order by 的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。

filesort算法：双路排序和单路排序 排序的字段如果不在索引列上，则filesort会有两种算法：双路排序 和 单路排序

双路排序（慢）：

  • MySQL 4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和order by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出；
  • 从磁盘取排序字段，在buffer进行排序，再从磁盘取其它字段；

取一批数据，要对磁盘进行两次扫描，众所周知，IO是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。

单路排序(快)： 从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机lO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。

结论及引申出的问题：

  • 由于单路是后出的，总体而言好过双路
  • 但是用单路有问题
    • 在sort_buffer中，单路比多路要多占用很多空间，因为单路是把所有字段都取出,所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序〈创建tmp文件，多路合并），排完再取sort_buffer容量大小，再排…从而多次I/O。
    • 单路本来想省一次l/o操作，反而导致了大量的I/0操作，反而得不偿失。

5.6 GROUP BY优化

group by 使用索引的原则几乎跟order by一致 ，group by 即使没有过滤条件用到索引，也可以直接使用索引。

group by 先排序再分组，遵照索引创建的最佳左前缀法则。

当无法使用索引列，可以增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置。

where 效率高于 having，能写在 where 限定的条件就不要写在 having 中。 减少使用 order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。order by、group by、distinct 这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。

包含了 order by、group by、distinct 这些查询的语句，where 条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。

5.7 优化分页查询

优化思路一： 在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。

1
2

EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 2000000,10) a
WHERE t.id = a.id;

优化思路二： 该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询。

1

EXPLAIN SELECT * FROM student WHERE id > 2000000 LIMIT 10;

5.8 优先考虑覆盖索引

1、什么是覆盖索引？

理解方式一 ：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做 覆盖索引。

理解方式二 ：非聚簇复合索引的一种形式，它包括在查询里的SELECT、JOIN和WHERE子句用到的所有列（即建索引的字段正好是覆盖查询条件中所涉及的字段）。

简单说就是，索引列+主键包含SELECT 到 FROM之间查询的列。

2、覆盖索引的利弊

覆盖索引好处：

  • 避免Innodb表进行索引的二次查询（回表）
    • Innodb是以聚集索引的顺序来存储的，对于Innodb来说，二级索引子节点中所保存的是行的主键信息，如果是用二级索引查询数据，在查找到相应的键值后，还需通过主键进行二次查询才能获取我们真实所需要的数据。
    • 在覆盖索引中，二级索引的键值中可以获取所要的数据，避免了对主键的二次查询，减少了IO操作，提升了查询效率。
  • 可以把随机IO变成顺序IO加快查询效率
    • 由于覆盖索引是按键值的顺序存储的，对于I0密集型的范围查找来说，对比随机从磁盘读取每一行的数据IO要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的IO转变成索引查找的顺序IO。

覆盖索引弊端： 索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了，这是业务DBA，或者称为业务数据架构师的工作。

5.9 索引条件下推

1、索引条件下推简介

Index Condition Pushdown(ICP) 是MySQL 5.6中新特性，是一种在存储引擎层使用索引过滤数据的优化方式。

如果没有ICP，存储引擎会遍历索引以定位基表中的行，并将它们返回给MySQL服务器，由MySQL服务器评估WHERE后面的条件是否保留行。

启用ICP后，如果部分WHERE条件可以仅使用索引中的列进行筛选，则MySQL服务器会把这部分WHERE条件放到存储引擎筛选，然后，存储引擎通过使用索引条目来筛选数据，并且只有在满足这一条件时才从表中读取行：

  • 好处：ICP可以减少存储引擎必须访问基表的次数和MysQL服务器必须访问存储引擎的次数；
  • 但是，ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例；

2、使用前后的扫描过程

在不使用ICP索引扫描的过程：

  • storage层：只将满足index key条件的索引记录对应的整行记录取出，返回给server层；
  • server 层：对返回的数据，使用后面的where条件过滤，直至返回最后一行；

使用ICP扫描的过程：

  • storage层：首先将index key条件满足的索引记录区间确定，然后在索引上使用index filter进行过滤，将满足index filter条件的索引记录才去回表取出整行记录返回server层，不满足index filter条件的索引记录丢弃，不回表、也不会返回server层； server 层：对返回的数据，使用table filter条件做最后的过滤；

3、ICP的开启/关闭

默认情况下启用索引条件下推，可以通过设置系统变量 optimizer_switch 控制：index_condition_pushdown

1
2
3
4

#打开索引下推
SET optimizer_switch = 'indeI_condition_pushdown=off';
#关闭索引下推
SET optimizer_switch = 'index_condition_pushdown=on ';

当使用索引条件下推时，EXPLAIN语句输出结果中Extra列内容显示为 Using index condition

4、ICP的使用条件

  • 如果表访问的类型为range、ref、eq_ref和ref_or_null可以使用ICP；
  • ICP可以用于InnoDB和MyISAM表，包括分区表InnoDB和MyISAM表；
  • 对于InnoDB表，ICP仅用于二级索引，ICP的目标是减少全行读取次数，从而减少I/O操作；
  • 当SQL使用覆盖索引时，不支持ICP，因为这种情况下使用ICP不会减少I/O；
  • 相关子查询的条件不能使用ICP；

5.10 其它查询优化策略

1、EXISTS 和 IN 的区分

索引是个前提，其实选择与否还会要看表的大小，可以将选择的标准理解为小表驱动大表。

2、COUNT(*)与COUNT(具体字段)效率

场景1： COUNT(*) 和 COUNT(1) 都是对所有结果进行 COUNT，COUNT(*) 和 COUNT(1)本质上并没有区别（二者执行时间可能略有差别，不过还是可以把它俩的执行效率看成是相等的）。如果有WHERE子句，则是对所有符合筛选条件的数据行进行统计；如果没有WHERE子句，则是对数据表的数据行数进行统计。

场景2： 如果是MyISAM存储引擎，统计数据表的行数只需要O(1)的复杂度，这是因为每张MyISAM的数据表都有一个meta信息存储了row_count值，而一致性则是由表级锁来保证的。

如果是InnoDB存储引擎，因为InnoDB支持事务，采用行级锁和MVCC机制，所以无法像MyISAM一样，维护一个row_count变量，因此需要采用扫描全表，是O(n)的复杂度，进行循环+计数的方式来完成统计。

场景3： 在InnoDB引擎中，如果采用COUNT(具体字段)来统计数据行数，要尽量采用二级索引，因为主键采用的索引是聚簇索引，聚簇索引包含的信息多，明显会大于二级索引（非聚簇索引），对于COUNT(*)和COUNT(1)来说，它们不需要查找具体的行，只是统计行数，系统会自动采用占用空间更小的二级索引来进行统计。

如果有多个二级索引，会使用key_len小的二级索引进行扫描，当没有二级索引的时候，才会采用主键索引来进行统计。

3、关于SELECT(*)

在表查询中，建议明确字段，不要使用 * 作为查询的字段列表，推荐使用SELECT <字段列表> 查询。原因：

  • MySQL 在解析的过程中，会通过查询数据字典将 * 按序转换成所有列名，这会大大的耗费资源和时间。
  • 无法使用覆盖索引

4、LIMIT 1对优化的影响

针对的是会扫描全表的 SQL 语句，如果可以确定结果集只有一条，那么加上LIMIT 1 的时候，当找到一条结果的时候就不会继续扫描了，这样会加快查询速度。

如果数据表已经对字段建立了唯一索引，那么可以通过索引进行查询，不会全表扫描的话，就不需要加上LIMIT 1了。

5、多使用COMMIT

只要有可能，在程序中尽量多使用 COMMIT，这样程序的性能得到提高，需求也会因为 COMMIT 所释放的资源而减少。

COMMIT 所释放的资源：

  • 回滚段上用于恢复数据的信息；
  • 被程序语句获得的锁；
  • redo / undo log buffer 中的空间；
  • 管理上述 3 种资源中的内部花费；