索引与优化

合集 - MySQL(1)
1.索引与优化
收起

索引的数据结构

为什么使用索引?

索引是存储引擎用于快速找到数据记录的一种数据结构，就好比一本教科书的目录部分，通过目录中找到对应文章的页码，便可快速定位到需要的文章。MySQL中也是一样的道理，进行数据查找时，首先查看查询条件是否命中某条索引，符合则通过索引查找相关数据，如果不符合则需要全表扫描，即需要一条一条地查找记录，直到找到与条件符合的记录。

image-20230930215454206

如上图所示，数据库没有索引的情况下，数据分布在硬盘不同的位置上面，读取数据时，摆臂需要前后摆动查询数据，这样操作非常消耗时间。如果数据顺序摆放，那么也需要从1到6行按顺序读取，这样就相当于进行了6次IO操作，依旧非常耗时。如果我们不借助任何索引结构帮助我们快速定位数据的话，我们查找 Col 2 = 89 这条记录，就要逐行去查找、去比较。从Col 2 = 34 开始，进行比较，发现不是，继续下一行。我们当前的表只有不到10行数据，但如果表很大的话，有上千万条数据，就意味着要做很多很多次硬盘I/0才能找到。现在要查找 Col 2 = 89 这条记录。CPU必须先去磁盘查找这条记录，找到之后加载到内存，再对数据进行处理。这个过程最耗时间就是磁盘I/O（涉及到磁盘的旋转时间（速度较快），磁头的寻道时间(速度慢、费时)）

假如给数据使用 二叉树 这样的数据结构进行存储，如下图所示

image-20230930215505125

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

这就是我们为什么要建索引，目的就是为了 减少磁盘I/O的次数，加快查询速率。

索引及其优缺点

2.1 索引概述

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

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

索引是在存储引擎中实现的，因此每种存储引擎的索引不一定完全相同，并且每种存储引擎不一定支持所有索引类型。同时，存储引擎可以定义每个表的 最大索引数和 最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。有些存储引擎支持更多的索引数和更大的索引长度。

2.2 优点

  1. 类似大学图书馆建书目索引，提高数据检索的效率，降低 数据库的IO成本 ，这也是创建索引最主 要的原因。

  2. 通过创建 唯一索引，可以保证数据库表中每一行 数据的唯一性 。

  3. 在实现数据的 参考完整性方面，可以 加速表和表之间的连接 。换句话说，对于有依赖关系的子表和父表联合查询时， 可以提高查询速度。

  4. 在使用分组和排序子句进行数据查询时，可以显著 减少查询中分组和排序的时间 ，降低了CPU的消耗。

2.3 缺点

  1. 创建索引和维护索引要 耗费时间 ，并且随着数据量的增加，所耗费的时间也会增加。

  2. 索引需要占 磁盘空间 ，除了数据表占数据空间之外，每一个索引还要占一定的物理空间， 存储在磁盘上 ，如果有大量的索引，索引文件就可能比数据文件更快达到最大文件尺寸。

  3. 虽然索引大大提高了查询速度，同时却会 降低更新表的速度 。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。 因此，选择使用索引时，需要综合考虑索引的优点和缺点。

提示:索引可以提高查询的速度，但是会影响插入记录的速度。这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后再创建索引。

InnoDB中的索引

3.1 索引之前的查找

SELECT [列名列表] FROM 表名 WHERE 列名 = xxx;

  1. 在一个页中的查找

    • 以主键为搜索条件

      可以在页目录中使用 二分法 快速定位到对应的槽,然后再遍历该槽中对应分组的记录即可快速找到指定记录.

    • 以其他列为搜索条件

      因为在数据页中并没有对非主键列建立页目录,所以我们无法通过二分法快速定位到相应的槽.这种情况下只能从 最小记录 开始 依次遍历单链表中的每条记录 ,然后对比每条记录是否符合搜索条件.

  2. 在很多页中查找

    在很多页中查找记录的活动可以分为两个步骤

    1. 定位到记录所在的页.

    2. 从所在的页内中查找相应的记录.

    在没有索引的情况下，不论是根据主键列或者其他列的值进行查找，由于我们并不能快速的定位到记录所在的页，所以只能 从第一个页沿着双向链表 一直往下找，在每一个页中根据我们上面的查找方式去查 找指定的记录。因为要遍历所有的数据页，所以这种方式显然是 超级耗时 的。如果一个表有一亿条记录呢？此时 索引 应运而生。

3.2 InnoDB中的索引方案:B+ Tree

一个B+树的节点其实可以分成好多层，规定最下边的那层，也就是存放我们用户记录的那层为第 0 层， 之后依次往上加。

其实真实环境中一个页存放的记录数量是非常大的，假设所有存放用户记录 的叶子节点代表的数据页可以存放 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 （页目录），所以在页面内也可以通过 二分法 实现快速 定位记录。

常见的索引概念

索引按照物理实现方式，索引可以分为 2 种：聚簇（聚集）和非聚簇（非聚集）索引。我们也把非聚簇索引称为二级索引或者辅助索引。

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式（所有的用户记录都存储在了叶子结点），也就是所谓的 索引即数据，数据即索引。

术语"聚簇"表示当前数据行和相邻的键值聚簇的存储在一起

特点:

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

  • 使用主键值的大小进行记录和页的排序,这包括三个方面的含义:

    • 页内 的记录是按照主键的大小顺序排成一个 单向链表 。

    • 各个存放 用户记录的页 也是根据页中用户记录的主键大小顺序排成一个 双向链表 。

    • 存放 目录项记录的页 分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个 双向链表 。

我们 把具有这两种特性的B+树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点处。这种聚簇索引并不需要我们在MySQL语句中显式的使用INDEX 语句去创建， InnDB 存储引擎会 自动 的为我们创建聚簇索引。

优点:

  • 数据访问更快 ，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快

  • 聚簇索引对于主键的 排序查找 和 范围查找 速度非常快

  • 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以 节省了大量的io操作 。

缺点:

  • 插入速度严重依赖于插入顺序 ，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键

  • 更新主键的代价很高 ，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新

  • 二级索引访问需要两次索引查找 ，第一次找到主键值，第二次根据主键值找到行数据

二级索引(辅助索引/非聚簇索引)

如果我们想以别的列作为搜索条件该怎么办？肯定不能是从头到尾沿着链表依次遍历记录一遍。

答案：我们可以多建几颗B+树，不同的B+树中的数据采用不同的排列规则。比方说我们用c2列的大小作为数据页、页中记录的排序规则，再建一课B+树.

这个B+树与上边介绍的聚簇索引的B+树有几处不同：

  • B+树的叶子节点存储的并不是完整的用户记录,而只是 C2列+主键 这两个列的值

  • 目录项的记录中不再是 主键+页号 的搭配,而变成了 C2列+页号 的搭配.

  • 使用记录C2列的大小进行记录和页的排序,这包括三个方面的含义

    • 页内的记录是按照C2列的大小顺序排成一个 单向链表

    • 各个存放 用户记录的页 也是根据页中记录的C2列大小顺序排成一个 双向链表

    • 存放 目录项记录的页 分为不同的层次，在同一层次中的页也是根据页中目录项记录的C2列大小顺序排成一个 双向链表 。

     

所以如果想通过 C2 列的值查找某些记录的话就可以使用这个B+树了.以查找C2列的值为4的记录为例,查找过程如下:

  1. 根据 根页面确定 目录项记录页

  2. 通过 目录项记录页确定用户记录真实所在的页

  3. 在真实存储用户记录的页中定位到具体的记录

  4. 因为这个B+树的叶子节点中的记录只存了C2和C1(主键)两个列,所以再根据主键值去聚簇索引中再查找一遍完整的用户记录(这个过程称为 回表)

问题：

由此可知根据c2列的值查询一条完整的用户记录需要使用到 2 棵B+树!!为什么我们还需要一次 回表 操作呢？直接把完整的用户记录放到叶子节点不OK吗？

回答：

如果把完整的用户记录放到叶子结点是可以不用回表。但是太占地方了，相当于每建立一课B+树都需要把所有的用户记录再都拷贝一遍，这就有点太浪费存储空间了。因为这种按照非主键列建立的B+树需要一次回表操作才可以定位到完整的用户记录，所以这种B+树也被称为二级索引，或者辅助索引。由于使用的是c2列的大小作为B+树的排序规则，所以我们也称这个B+树为c2列建立的索引。

非聚簇索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个非聚簇索引。

联合索引

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

  • 先把各个记录和页按照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+树。

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

  1. 根页面万年不动

实际上B+树的形成过程是这样的：

  • 每当为某个表创建一个B+树索引（聚簇索引不是人为创建的，默认就有）的时候，都会为这个索引创建一个 根结点 页面。最开始表中没有数据的时候，每个B+树索引对应的 根结点 中即没有用户记录，也没有目录项记录。

  • 随后向表中插入用户记录时，先把用户记录存储到这个根节点 中。

  • 当根节点中的可用 空间用完时 继续插入记录，此时会将根节点中的所有记录复制到一个新分配的页，比如 页a 中，然后对这个新页进行 页分裂 的操作，得到另一个新页，比如页b 。这时新插入的记录根据键值（也就是聚簇索引中的主键值，二级索引中对应的索引列的值）的大小就会被分配到 页a 或者 页b 中，而 根节点 便升级为存储目录项记录的页。

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

  1. 内节点中目录项记录的唯一性

对于二级索引的内节点的目录项记录的内容实际上是由三个部分构成的：

  • 索引列的值

  • 主键值

  • 页号

MyISAM中的索引

B树索引使用存储引擎如表所示：

索引 / 存储引擎MyISAMInnoDBMemory
B-Tree索引(其实就是B+树) 支持 支持 支持

即使多个存储引擎支持同一种类型的索引，但是他们的实现原理也是不同的。Innodb和MyISAM默认的索 引是B+tree索引；而Memory默认的索引是Hash索引。

MyISAM引擎使用 B+Tree 作为索引结构，叶子节点的data域存放的是 数据记录的地址 。

MyISAM索引的原理

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

  • 将表中的记录 按照记录的插入顺序 单独存储在一个文件中,称之为数据文件,这个文件并不划分为若干个数据页,有多少记录就往这个文件中塞多少记录就成了.由于在插入的时候 并没有刻意按照主键大小排序 ,所以我们并不能在这些数据上使用二分法查找.

  • 使用 MyISAM 存储引擎的表会把索引信息另外存储到一个称为 索引文件 的文件中.MyISAM会单独为表中的主键创建一个索引,只不过在索引的叶子节点中存储的不是完整的用户记录,而是 主键值+数据记录地址 的组合.

image-20230930215601614

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

image-20230930215621648

同样也是一颗B+Tree,data域保存数据记录的地址.因此,MyISAM中索引检索的算法为:首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其data域的值,然后根据data域的地址,读取相应数据记录.

MyISAM与InnoDB对比

MyISAM的索引方式都是“非聚簇”的，与InnoDB包含1个聚簇索引是不同的。小结两种引擎中索引的区别：

① 在InnoDB存储引擎中，我们只需要根据主键值对 聚簇索引 进行一次查找就能找到对应的记录，而在 MyISAM 中却需要进行一次 回表 操作，意味着MyISAM中建立的索引相当于全部都是 二级索引 。

② InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是分离的 ，索引文件仅保存数据记录的地址。

③ InnoDB的非聚簇索引data域存储相应记录 主键的值 ，而MyISAM索引记录的是 地址 。换句话说， InnoDB的所有非聚簇索引都引用主键作为data域。

④ MyISAM的回表操作是十分快速的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通 过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。

⑤ InnoDB要求表 必须有主键 （ MyISAM可以没有 ）。如果没有显式指定，则MySQL系统会自动选择一个 非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

小结:

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助,比如:

例1: 知道了InnoDB的索引实现后,就很容易明白 为什么不建议使用过长的字段作为主键 因为所有的二级索引都引用主键索引,过长的主键索引会使得二级索引变得很大.

例2: 用非单调的字段作为主键在InnoDB中不是个好主意,因为InnoDB数据文件本身是一颗B+Tree,非单调的主键会造成在插入新记录时,数据文件为了维护B+Tree的特性而频繁的分裂调整,十分抵消,而使用自增字段作为主键则是一个很好的选择.

image-20230930215643332

索引的代价

索引是个好东西，可不能乱建，它在空间和时间上都会有消耗：

  • 空间上的代价

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

  • 时间上的代价

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

一个表上索引建的越多，就会占用越多的存储空间，在增删改记录的时候性能就越差。为了能建立又好又少的索引，我们得学学这些索引在哪些条件下起作用的。

索引的创建与设计原则

1.索引的声明与使用

1.1 索引的分类

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

从 功能逻辑 上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引。

按照 物理实现方式 ，索引可以分为 2 种：聚簇索引和非聚簇索引。

按照 作用字段个数 进行划分，分成单列索引和联合索引。

1.普通索引

在创建普通索引时,不附加任何限制条件,只用于提高查询效率.这类索引可以创建在任何数据类型中,其值是否唯一或非空,要由字段本身的完整性约束条件决定.建立索引后,可以通过索引进行查询.

2.唯一性索引

使用 UNIQUE参数 可以设置索引为唯一性索引,在创建唯一性索引时,限制该索引的值必须是唯一的,但允许有空值.在一张表里可以有多个唯一索引

3.主键索引

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

why?这是由主键索引的物理实现方式决定的,因为数据存储在文件中只能按照一种顺序存储.

4.单列索引

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

5.多列(组合、联合)索引

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

6.全文检索

7.空间索引

目前只有MyISAM引擎支持空间检索.

1.2 索引的创建

MySQL支持多种方法在单个或多个列上创建索引：在创建表的定义语句 CREATE TABLE 中指定索引列，使用 ALTER TABLE 语句在存在的表上创建索引，或者使用 CREATE INDEX 语句在已存在的表上添加索引。

创建表的时候创建索引

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

举例：

CREATE TABLE dept(
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(20)
);
​
CREATE TABLE emp(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(20) UNIQUE,
dept_id INT,
CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id)
)

如果显式创建表时创建索引的话，基本语法格式如下：

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. 创建普通索引

在book表中的year_publication字段上建立普通索引，SQL语句如下：

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)
);

 

2. 创建唯一索引

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

 

该语句执行完毕之后，使用SHOW CREATE TABLE查看表结构：

SHOW INDEX FROM test1 \G

 

3. 主键索引

设定为主键后数据库会自动建立索引，innodb为聚簇索引，语法：

  • 随表一起建索引：

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;

 

  • 修改主键索引：必须先删除掉(drop)原索引，再新建(add)索引

4. 创建单列索引

引举:

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

 

该语句执行完毕之后，使用SHOW CREATE TABLE查看表结构：

SHOW INDEX FROM test2 \G

 

5. 创建组合索引

举例：创建表test3，在表中的id、name和age字段上建立组合索引，SQL语句如下：

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)
);

 

该语句执行完毕之后，使用SHOW INDEX 查看：

SHOW INDEX FROM test3 \G

 

在test3表中，查询id和name字段，使用EXPLAIN语句查看索引的使用情况：

EXPLAIN SELECT * FROM test3 WHERE id=1 AND name='songhongkang' \G

 

可以看到，查询id和name字段时，使用了名称为MultiIdx的索引，如果查询 (name, age) 组合或者单独查询name和age字段，会发现结果中possible_keys和key值为NULL, 并没有使用在t3表中创建的索引进行查询。

1.3 索引的删除

1. 使用ALTER TABLE删除索引 ALTER TABLE删除索引的基本语法格式如下：

ALTER TABLE table_name DROP INDEX index_name;

 

2. 使用DROP INDEX语句删除索引 DROP INDEX删除索引的基本语法格式如下：

DROP INDEX index_name ON table_name;

 

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

MySQL8.0索引新特性

2.1 支持降序索引

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

MySQL在8.0版本之前创建的仍然是升序索引，使用时进行反向扫描，这大大降低了数据库的效率。在某些场景下，降序索引意义重大。例如，如果一个查询，需要对多个列进行排序，且顺序要求不一致，那么使用降序索引将会避免数据库使用额外的文件排序操作，从而提高性能。

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

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

在MySQL 5.7版本中查看数据表ts1的结构，结果如下:

image-20231001090252633

从结果可以看出，索引仍然是默认的升序

 

在MySQL 8.0版本中查看数据表ts1的结构，结果如下：

image-20231001090408110

从结果可以看出，索引已经是降序了。

 

2.2 隐藏索引

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

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

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

注意：

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

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

1. 创建表时直接创建

在MySQL中创建隐藏索引通过SQL语句INVISIBLE来实现，其语法形式如下：

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

上述语句比普通索引多了一个关键字INVISIBLE，用来标记索引为不可见索引。

2. 在已经存在的表上创建

可以为已经存在的表设置隐藏索引，其语法形式如下：

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

 

3. 通过ALTER TABLE语句创建

语法形式如下：

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

 

4. 切换索引可见状态

已存在的索引可通过如下语句切换可见状态：

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

 

如果将index_cname索引切换成可见状态，通过explain查看执行计划，发现优化器选择了index_cname索引。

注意

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

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

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

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

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

mysql> select @@optimizer_switch \G

 

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

use_invisible_indexes=off

 

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

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

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

 

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

mysql> select @@optimizer_switch \G
*************************** 1. row ***************************
@@optimizer_switch:
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_
intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_co
st_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on
,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on
,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_ind
exes=on,skip_scan=on,hash_join=on
1 row in set (0.00 sec)

 

此时，在输出结果中可以看到如下属性配置。

use_invisible_indexes=on

 

use_invisible_indexes属性的值为on，说明此时隐藏索引对查询优化器可见。

（3）使用EXPLAIN查看以字段invisible_column作为查询条件时的索引使用情况。

explain select * from classes where cname = '高一2班';

 

查询优化器会使用隐藏索引来查询数据。

（4）如果需要使隐藏索引对查询优化器不可见，则只需要执行如下命令即可。

mysql> set session optimizer_switch="use_invisible_indexes=off";
Query OK, 0 rows affected (0.00 sec)

 

再次查看查询优化器的开关设置。

mysql> select @@optimizer_switch \G

 

此时，use_invisible_indexes属性的值已经被设置为“off”。

 

索引的设计原则

3.1 哪些情况适合创建索引

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

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

例如，学生表中学号是具有唯一性的字段，为该字段建立唯一性索引可以很快确定某个学生的信息，如果使用姓名的话，可能存在同名现象，从而降低查询速度。

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源：Alibaba）

说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。

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

某个字段在SELECT语句的WHERE条件中经常被用到，那么就需要给这个字段创建索引了。尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。

3. UPDATE、DELETE的WHERE条件列

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

4. 经常 GROUP BY 和 ORDER BY 的列

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

5. DISTINCT 字段需要创建索引

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

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

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

其次， 对 WHERE 条件创建索引 ，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下， 没有 WHERE 条件过滤是非常可怕的。

最后， 对用于连接的字段创建索引 ，并且该字段在多张表中的 类型必须一致 。比如 course_id 在 student_info 表和 course 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。

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

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

我们在定义表结构的时候要显式的指定列的类型，以整数类型为例，有TINYINT、MEDIUMINT、INT、BIGINT等，他们占用的存储空间依次递增，能表示的整数范围也是依次递增。如果我们想要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引使用较小的类型，这是因为：

  • 数据类型越小，在查询时进行的比较操作越快。

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

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

8. 使用字符串前缀创建索引

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

  • B+树索引中的记录需要把该列完整字符串存储起来，更费时。而且字符串越长，在索引中占用的存储空间越大。

  • 如果B+树索引中索引存储的字符串很长，那在做字符串比较时会占用更多的时间。

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

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

通过不同长度去计算，与全表的选择性对比：

公式：

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

越接近于1越好，说明越有区分度

 

Alibaba《Java开发手册》

【 强制 】在 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 t1 计算区分度，越接近1越好，一般超过33%就算比较高效的索引了。

扩展：联合索引把区分度高(散列性高)的列放在前面。

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

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

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

3.2 限制索引的数目

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

  1. 每个索引都需要占用磁盘空间,索引越多,需要的磁盘空间就越大.

  2. 索引会影响INSERT DELETE UPDATE等语句的性能，因为表中的数据更改的同时，索引也会进行调整和更新，会造成负担。

  3. 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，会增加MySQL优化器生成执行计划的时间，降低查询性能。

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

1. 在where中使用不到的字段

WHERE条件 (包括 GROUP BY、ORDER BY) 里用不到的字段不需要创建索引，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的。

2. 表中数据量小的表

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

3. 有大量重复数据的列

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

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

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

第一层含义：频繁更新的字段不一定要创建索引。因为更新数据的时候，也需要更新索引，如果索引太多，在更新索引的时候也会造成负担，从而影响效率。

第二层含义：避免对经常更新的表创建过多的索引，并且索引中的列尽可能少。此时，虽然提高了查询速度，同时却降低更新表的速度。

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

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

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

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

7. 不要定义冗余或者重复的索引

冗余索引

举例：

CREATE TABLE person_info(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    birthday DATE NOT NULL,
    phone_number CHAR(11) NOT NULL,
    country varchar(100) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_name_birthday_phone_number (name(10), birthday, phone_number),
    KEY idx_name (name(10))
);

我们知道，通过 idx_name_birthday_phone_number 索引就可以对 name 列进行快速搜索，再创建一 个专门针对 name 列的索引就算是一个 冗余索引 ，维护这个索引只会增加维护的成本，并不会对搜索有 什么好处。

 

重复索引

另一种情况，我们可能会对某个列 重复建立索引 ，比方说这样：

CREATE TABLE repeat_index_demo (
col1 INT PRIMARY KEY,
col2 INT,
UNIQUE uk_idx_c1 (col1),
INDEX idx_c1 (col1)
);

我们看到，col1 既是主键、又给它定义为一个唯一索引，还给它定义了一个普通索引，可是主键本身就 会生成聚簇索引，所以定义的唯一索引和普通索引是重复的，这种情况要避免。

索引优化与查询优化

索引优化

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

  • 使用索引可以快速地定位表中的某条记录，从而提高数据库查询的速度，提高数据库的性能。

  • 如果查询时没有使用索引，查询语句就会扫描表中的所有记录。在数据量大的情况下，这样的查询速度会很慢。

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

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

1.1 全值匹配我最爱

创建索引可以帮助我们极大提高查询效率

1.2 最左前缀法则

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

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

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

拓展：Alibaba《Java开发手册》

索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

1.3 主键插入顺序

对于一个使用InnoDB存储引擎的表来说，在我们没有显式的创建索引时，表中的数据实际上都是存储在聚簇索引De叶子节点的。而记录又是存储在数据页中的，数据页和记录又是按照记录主键值从小到大的顺序进行排序，所以如果我们插入的记录的主键值是依次增大的话，那么我们每插满一个数据页就换到下一个数据页继续插，而如果我们插入的主键值忽大忽小的话，就比较麻烦了，假设某个数据页存储的记录已经满了，它存储的主键值在1~100之间：

image-20231001103840051

如果此时再插入一条主键值为 9 的记录，那它插入的位置就如下图：

image-20231001103857975

可这个数据页已经满了，再插进来咋办呢？我们需要把当前 页面分裂 成两个页面，把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着什么？意味着： 性能损耗！所以如果我们想尽量避免这样无谓的性能损耗，最好让插入的记录的 主键值依次递增，这样就不会发生这样的性能损耗了。 所以我们建议：让主键具有 AUTO_INCREMENT ，让存储引擎自己为表生成主键，而不是我们手动插入。

索引失效案例

1.1 计算、函数、类型转换（自动或手动）导致索引失效

计算导致索引失效：student表的字段stuno上设置有索引

索引优化生效：

EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno = 900000;

索引优化失效：

EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;

 

函数导致索引失效：student表的字段name上设置有索引

索引优化生效：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';

索引优化失效：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';

 

函数导致索引失效：student表的字段name上设置有索引

索引优化生效：

EXPLAIN SELECT id, stuno, NAME FROM student WHERE NAME LIKE 'abc%';

索引优化失效：

EXPLAIN SELECT id, stuno, name FROM student WHERE SUBSTRING(name, 1,3)='abc';

 

类型转换导致索引失效：student表的字段name（varchar类型）上设置有索引

索引优化生效：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';

索引优化失效：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;

name=123发生类型转换，索引失效。

 

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

  1. 系统经常出现的sql如下：

EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;

  1. 那么索引 idx_age_classId_name 这个索引还能正常使用么？

不能，范围右边的列不能使用。比如：(<) (<=) (>) (>=) 和 between 等

image-20231001111028582

如果这种sql出现较多，应该建立：

create index idx_age_name_classId on student(age,name,classId);

将范围查询条件放置语句最后：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = 'abc' AND student.classId>20;

image-20231001111057940

应用开发中范围查询，例如：金额查询，日期查询往往都是范围查询。应将查询条件放置where语句最后。

（创建的联合索引中，务必把范围涉及到的字段写在最后）

1.3 不等于(!= 或 <>)导致索引失效

1.4 is null可以使用索引,is not null无法使用索引

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

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

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

在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为'%'，索引就不会起作用。只有'%'不在第一个位置，索引才会起作用。

索引优化生效：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name LIKE 'ab%';

索引优化失效：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name LIKE '%ab%';

拓展：Alibaba《Java开发手册》

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

1.6 OR 前后存在的非索引的列,索引失效

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

因为OR的含义就是两个只要满足一个即可，因此只有一个条件列进行了索引是没有意义的，只要有条件列没有进行索引，就会进行全表扫描，因此索引的条件列也会失效。

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

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

一般性建议

  • 对于单列索引，尽量选择针对当前query过滤性更好的索引

  • 在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。

  • 在选择组合索引的时候，尽量选择能够当前query中where子句中更多的索引。

  • 在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面。

总之，书写SQL语句时，尽量避免造成索引失效的情况

关联查询优化

1. 采用左外连接

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

LEFT JOIN 条件用于确定如何从右表搜索行，左边一定都有，所以 右边是我们的关键点,一定需要建立索引 。

2. 采用内连接

换成 inner join（MySQL自动选择驱动表）

EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card=book.card;

对于内连接来说，查询优化器可以决定谁作为驱动表，谁作为被驱动表出现的

MySQL会选择将表数据较大的表作为被驱动表。

3. join语句原理

join方式连接多个表，本质就是各个表之间数据的循环匹配。MySQL5.5版本之前，MySQL只支持一种表间关联方式，就是嵌套循环(Nested Loop Join)。如果关联表的数据量很大，则join关联的执行时间会很长。在MySQL5.5以后的版本中，MySQL通过引入BNLJ算法来优化嵌套执行。

驱动表就是主表，被驱动表就是从表、非驱动表。

  • 对于内连接来说：

SELECT * FROM A JOIN B ON ...

A一定是驱动表吗？不一定，优化器会根据你查询语句做优化，决定先查哪张表。先查询的那张表就是驱动表，反之就是被驱动表。通过explain关键字可以查看。

  • 对于外连接来说：

SELECT * FROM A LEFT JOIN B ON ...
# 或
SELECT * FROM B RIGHT JOIN A ON ...

通常，大家会认为A就是驱动表，B就是被驱动表。但也未必。

1.Simple Nested-Loop Join (简单嵌套循环连接)

算法相当简单，从表A中取出一条数据1，遍历表B，将匹配到的数据放到result.. 以此类推，驱动表A中的每一条记录与被驱动表B的记录进行判断：

image-20231001164048015

可以看到这种方式效率是非常低的，以上述表A数据100条，表B数据1000条计算，则A*B=10万次。

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

 

2.Index Nested-Loop Join(索引嵌套循环连接)

Index Nested-Loop Join其优化的思路主要是为了减少内层表数据的匹配次数，所以要求被驱动表上必须有索引才行。通过外层表匹配条件直接与内层表索引进行匹配，避免和内存表的每条记录去进行比较，这样极大的减少了对内存表的匹配次数。

image-20231001165405894

驱动表中的每条记录通过被驱动表的索引进行访问，因为索引查询的成本是比较固定的，故mysql优化器都倾向于使用记录数少的表作为驱动表（外表）。

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

 

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

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

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

image-20231001171039220

注意 :

这里缓存的不只是关联的列,select后面的列也会缓存起来.

在一个有N个join关联的sql中会分配N-1个join buffer.所以查询的时候尽量减少不必要的字段,可以让join buffer中存放更多的列.

开销统计SNLJINLJBNLJ
外表扫描次数 1 1 1
内表扫描次数 A 0 A*used_column_size/join_buffer_size
读取记录数 A+B*A A+B(match) A+B(A*used_column_size/join_buffer_size)
JOIN比较次数 B*A A*Index(Height) B*A
回表读取记录次数 0 B(match)(if possible) 0

参数设置

  • block_nested_loop

    通过 show variables like '%optimizer_switch%' ,查看 block_nested_loop 状态.默认是开启的.

  • join_buffer_size

    驱动表能不能一次加载完,要看join buffer能不能存储所有的数据,默认情况下 join_buffer_size=256k .

join_buffer_size的最大值在32位系统可以申请4G,而在64位操作系统下可以申请大于4G的join buffer空间(Windows系统除外,其最大值会被截断为4G并警告)

Join 小结

  1. 整体效率比较 INLJ > BNLJ > SNLJ

  2. 永远用小结果集驱动大结果集(其本质就是减少外层循环的数据数量)(晓得度量单位指的是表行数*每行大小)

  3. 为被驱动表匹配的条件增加索引(减少内层表的循环次数)

  4. 增大 join buffer size 的大小(一次缓存的数据越多,那么内层表的扫表次数就越少)

  5. 减少驱动表不必要的字段查询(字段越少,join buffer能缓存的数据就越多)

  6. 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的性能。

      • 它能够很好的工作于没有索引的大表和并行查询的环境中，并提供最好的性能。大多数人都说它是Join的重型升降机。Hash Join只能应用于等值连接（如WHERE A.COL1 = B.COL2），这是由Hash的特点决定的。

image-20231001174011236

 

小结:

保证被驱动表的JOIN字段已经创建了索引

需要JOIN 的字段，数据类型保持绝对一致。

LEFT JOIN 时，选择小表作为驱动表， 大表作为被驱动表 。减少外层循环的次数。

INNER JOIN 时，MySQL会自动将小结果集的表选为驱动表 。选择相信MySQL优化策略。

能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数)

不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用 JOIN 来代替子查询。

衍生表建不了索引

 

4. 子查询优化

MySQL从4.1版本开始支持子查询，使用子查询可以进行SELECT语句的嵌套查询，即一个SELECT查询的结 果作为另一个SELECT语句的条件。

子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作 。

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

① 执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表 ，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表 。这样会消耗过多的CPU和IO资源，产生大量的慢查询。

② 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引 ，所以查询性能会受到一定的影响。

③ 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

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

举例1：查询学生表中是班长的学生信息

  • 使用子查询

# 创建班级表中班长的索引
CREATE INDEX idx_monitor ON class(monitor);

EXPLAIN SELECT * FROM student stu1
WHERE stu1.`stuno` IN (
SELECT monitor
FROM class c
WHERE monitor IS NOT NULL
)

 

  • 推荐使用多表查询

EXPLAIN SELECT stu1.* FROM student stu1 JOIN class c
ON stu1.`stuno` = c.`monitor`
WHERE c.`monitor` is NOT NULL;

 

举例2：取所有不为班长的同学

  • 不推荐

EXPLAIN SELECT SQL_NO_CACHE a.*
FROM student a
WHERE a.stuno NOT IN (
	SELECT monitor FROM class b
    WHERE monitor IS NOT NULL
);

  • 推荐：

EXPLAIN SELECT SQL_NO_CACHE a.*
FROM student a LEFT OUTER JOIN class b
ON a.stuno = b.monitor
WHERE b.monitor IS NULL;

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

 

5.排序优化

在MySQL中，支持两种排序方式，分别是 FileSort 和 Index 排序。

  • Index 排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高。

  • FileSort 排序则一般在 内存中 进行排序，占用CPU较多。如果待排结果较大，会产生临时文件 I/O 到磁盘进行排序的情况，效率较低。

优化建议：

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

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

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

小结

INDEX a_b_c(a,b,c)
order by 能使用索引最左前缀
- ORDER BY a
- ORDER BY a,b
- ORDER BY a,b,c
- ORDER BY a DESC,b DESC,c DESC
如果WHERE使用索引的最左前缀定义为常量，则order by 能使用索引
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b = const ORDER BY c
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b > const ORDER BY b,c
不能使用索引进行排序
- ORDER BY a ASC,b DESC,c DESC /* 排序不一致 */
- WHERE g = const ORDER BY b,c /*丢失a索引*/
- WHERE a = const ORDER BY c /*丢失b索引*/
- WHERE a = const ORDER BY a,d /*d不是索引的一部分*/
- WHERE a in (...) ORDER BY b,c /*对于排序来说，多个相等条件也是范围查询*/

  1. 两个索引同时存在，mysql自动选择最优的方案。但是， 随着数据量的变化，选择的索引也会随之变化的。

  2. 当【范围条件】和【group by 或者 order by】的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。

 

filesort算法:双路排序和单路排序

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

双路排序 （慢）

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

  • 从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段 。

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

单路排序 （快）

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

结论及引申出的问题

  • 由于单路是后出的，总体而言好过双路

  • 但是用单路有问题

    • 在sort_buffer中，单路要比多路多占用很多空间，因为单路是把所有字段都取出，所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取sort_buffer容量大小，再排...从而多次I/O。

    • 单路本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。

优化策略

1. 尝试提高 sort_buffer_size

2.尝试提高 max_length_for_sort_data

3. Order by 时select * 是一个大忌。最好只Query需要的字段。

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会很慢。

 

7.优先考虑索引覆盖

什么是覆盖索引?

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

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

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

索引覆盖的利弊

好处：

  1. 避免InnoDB表进行索引的二次查询（回表）

    InnoDB是以聚集索引的顺序来存储的，对于InnoDB来说，二级索引在叶子节点中所保存的是行的主键信息，如果是用二级索引查询数据，在查找到相应的键值后，还需通过主键进行二次查询才能获取到我们真实所需要的数据。

    在覆盖索引中，二级索引的键值中可以获取所要的数据，避免了对主键的二次查询，减少了IO操作，提升了查询效率

  2. 可以把随机IO变成顺序IO加快查询效率

    由于覆盖索引是按键值的顺序存储的，对于IO密集型的范围查找来说，对比随机从磁盘读取每一行的数据IO要少得多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的IO转变成索引查找的顺序IO。

弊端：

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

8.优化分页查询

一般分页查询时,通过创建覆盖索引能够比较好的提高性能.一个常见又非常头疼的问题就是 limit 200000010,10,此时需要MySQL排序前200000010条记录,仅仅返回200000000-200000010的记录,其他记录丢弃,查询排序的代价非常大.

EXPLAIN SELECT * FROM student LIMIT 200000000,10;

 

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

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

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

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

9.索引条件下推

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

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

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

    • 好处：ICP可以减少存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎的次数。

    • 但是，ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例。

ICP的开启/关闭

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

# 打开索引下推
SET optimizer_switch = 'index_condition_pushdown=on';

# 关闭索引下推
SET optimizer_switch = 'index_condition_pushdown=off';

 

  1. 如果表的访问类型为 range 、 ref 、 eq_ref 或者 ref_or_null 可以使用ICP。

  2. ICP可以使用InnDB和MyISAM表，包括分区表InnoDB和MyISAM表

  3. 对于InnoDB表，ICP仅用于二级索引。ICP的目标是减少全行读取次数，从而减少I/O操作。

    【ICP主要针对于减少回表操作，只有二级索引可以进行回表操作 -_-||】

  4. 当SQL使用覆盖索引时，不支持ICP优化方法。因为这种情况下使用ICP不会减少I/O。

    【覆盖索引都不需要回表了，还用个鸡毛ICP啊 -_-||】

  5. 相关子查询的条件不能使用ICP

10.其他查询优化策略

1.EXISTS 和 IN 的区分

问题：不太理解哪种情况下应该使用 EXISTS，哪种情况应该用 IN。选择的标准是看能否使用表的索引吗？

回答：索引是个前提，其实选择与否还是要看表的大小。你可以将选择的标准理解为小表驱动大表 。在这种方式下效率是最高的。

比如：

SELECT * FROM A WHERE cc IN (SELECT cc FROM B);
SELECT * FROM A WHERE EXISTS (SELECT cc FROM B WHERE B.cc=A.CC);

当A小于B时，用EXISTS。因为EXISTS的实现，相当于外表循环，实现的逻辑类似于：

for i in A
	for j in B
		if j.cc == i.cc then...

当B小于A时，用 IN。因为实现的逻辑类似于：

for i in B
	for j in A
		if j.cc == i.cc then...

哪个表小就用哪个表来驱动，A表小就用EXISTS，B表小就用IN。

2.COUNT(*) 和COUNT(具体字段)效率

问：在 MySQL 中统计数据表的行数，可以使用三种方式： SELECT COUNT(*) 、 SELECT COUNT(1) 和 SELECT COUNT(具体字段) ，使用这三者之间的查询效率是怎样的？

  1. COUNT(*) 和COUNT(1) 都是对所有结果进行COUNT，本质上没有区别（执行时间可能略有差别，但可以认为他们的执行效率是相等的）。

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

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

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

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

关于SELECT(*)

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

① MySQL 在解析的过程中，会通过查询数据字典将"*"按序转换成所有列名，这会大大的耗费资源和时间。

② 无法使用覆盖索引

LIMIT 1 对优化的影响

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

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

多使用COMMIT

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

COMMIT 所释放的资源：

  • 回滚段上用于恢复数据的信息

  • 被程序语句获得的锁

  • redo / undo log buffer 中的空间

  • 管理上述 3 种资源中的内部花费

posted @   小陈_winwah  阅读(61)  评论(2)  编辑  收藏  举报
编辑推荐：
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· AI与.NET技术实操系列：向量存储与相似性搜索在 .NET 中的实现
· 基于Microsoft.Extensions.AI核心库实现RAG应用
· Linux系列：如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
阅读排行：
· winform 绘制太阳，地球，月球 运作规律
· AI与.NET技术实操系列（五）：向量存储与相似性搜索在 .NET 中的实现
· 超详细：普通电脑也行Windows部署deepseek R1训练数据并当服务器共享给他人
· 【硬核科普】Trae如何「偷看」你的代码？零基础破解AI编程运行原理
· 上周热点回顾（3.3-3.9）
点击右上角即可分享
微信分享提示
AI IDE Trae