【MySQL】MySQL 索引

发表于2021-10-28更新于2022-03-12字数统计23.6k阅读时长145分阅读次数

image-20210913132511709

MySQL官方对索引的定义为：索引（Index）是帮助 MySQL 高效查找数据的排好序的数据结构。其中，排好序体现在 B+ 树最底层页结构组成的双向链表结构上。

索引的本质：索引是一种数据结构。

  • 查找：体现在 WHERE、ON 等条件判断上
  • 排序：体现在 ORDER BY、GROUP BY 分组排序上

索引简介

索引在数据库表的字段上添加，是为了提高查询效率而存在的一种机制。索引是各种数据库进行优化时的重要手段，优化时优先考虑的因素就是索引。一张表的一个字段可以添加一个索引，多个字段联合起来也可以添加索引。

索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。MySQL 在查询方面主要是两种方式：

  • 全表扫描：一条一条检索，效率较低
  • 根据索引检索：先通过索引定位大概位置，然后在此局部范围内扫描，效率较高

索引是在 MySQL 的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型的。MySQL 目前提供了以下4种索引：

  • BTREE 索引 ： 最常见的索引类型，大部分索引都支持 B 树索引。
  • HASH 索引：只有Memory引擎支持 ， 使用场景简单 。
  • R-tree 索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。
  • Full-text （全文索引） ：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从Mysql5.6版本开始支持全文索引。
MyISAM、InnoDB、Memory三种存储引擎对各种索引类型的支持
索引 InnoDB 引擎 MyISAM 引擎 Memory 引擎
BTREE 索引 支持 支持 支持
HASH 索引 不支持 不支持 支持
R-tree 索引 不支持 支持 不支持
Full-text 5.6 版本之后支持 支持 不支持

我们平常所说的索引，如果没有特别指明，都是指B+树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用 B+tree 索引，统称为索引。

索引的分类

在一个表中，主键索引只能有一个，唯一索引可以有多个

  • 主键索引（PRIMARY KEY)：唯一的标识，主键不可重复，只能有一列作为主键
  • 唯一索引（UNIQUE KEY）：唯一索引的名字不能重复出现，避免重复的列出现，唯一索引可以有多个
  • 常规索引（KEY/INDEX）：默认的，用INDEX或KEY来设置
  • 全文索引（FULLTEXT）：快速定位数据（一般用Elastic Search做全文索引）

索引的数据结构

索引的数据结构理论上可以采用以下五种：

  • 二叉树
  • 红黑树
  • Hash 表
  • B 树
  • B+ 树

在 MySQL 当中，索引采用最多的是 B+ 树（多路搜索树）数据结构。遵循左小右大原则存放，采用中序遍历方式遍历取数据。

MySQL 索引结构

为什么 MySQL 采用 B+ 树结构而不采用其他数据结构呢？下面依次分析其他几种结构存在的弊端。

二叉树和红黑树

二叉树与红黑树的每一个节点自身只能存储一个索引，且只能指向两个索引，这种存储效率是很低的，当数据量较大时，树的高度会很高。千万级的索引量需要树高达十几二十层，这样进行检索需要遍历许多层，效率不够高。

二叉树索引的示意图：

image-20211030140707972

Hash 表

使用 Hash 表的结构，能够提高确定常数值（const）的查询效率，例如：

1
select * from t1 where t1.col1 = 22;

因为每个索引值的 Hash 值基本都不相同，因此查找时可以直接定位到其位置。

但是其缺点也很明显：当需要区间查找时，使用 Hash 表就无法实现快速的定位目标区间。因为其只能快速定位单个索引的位置，无法定位一个区间内的位置。例如：

1
select * from t1 where t1.col1 > 22;

这种情况下，Hash 表就无法快速检索了。因此开发中基本不采用这种结构。

Memory 存储引擎使用 Hash 表结构，因为其数据都是在内存中的，因此遍历的速度是很快的，哪怕是范围查询，也可以在内存中快速的遍历出符合条件的数据。但 InnoDB 的数据是在硬盘中的，频繁的遍历需要大量的IO操作，降低了效率

B 树

B 代表 Balanced，平衡

B 树是一种自平衡多叉树，一个节点可以拥有2个以上的子节点（最大子节点数 + 1 = 其阶数m）。一个 B 树的节点内可以划分多个区间，从而在检索时能够快速定位到子区间。在实际应用中的B树的阶数m都非常大（通常大于100），所以即使存储大量的数据，B 树的高度仍然比较小。B 树的特点：

  • 叶节点具有相同的深度（体现了 B 树平衡的特性），叶节点的指针为空
  • 所有索引元素不重复（B+ 树的叶子节点会包含所有索引）
  • 节点中的数据索引从左到右递增排序
  • 每个节点都同时存储索引值和其存储的数据（B+ 树只有叶子结点存储数据，非叶子节点不存储数据，MyISAM只保存数据逻辑地址，数据和索引的存储位置是分开的）

2-3 树和 2-3-4 树是一种特殊的 B 树。

B 树结构图解：

1555903449006

img

上图是一颗阶数为 4 的 B 树。在实际应用中的B树的阶数 m 都非常大（通常大于100），所以即使存储大量的数据，B树的高度仍然比较小。每个结点中存储了关键字（key）和关键字对应的数据（data），以及孩子结点的指针。其中，key 代表表中某条记录的索引值，data 为其在硬盘上的逻辑地址（以 MyISAM 为例）。在数据库中我们将B树（和B+树）作为索引结构，可以加快查询速度，此时 B 树中的 key 就表示索引键，而 data 表示了这个键对应的条目在硬盘上的逻辑地址。

至于 data 中是存储真实数据还是数据的逻辑地址，要视存储引擎而定

简易结构图：

image-20211028163552324

B 树的优点

B 树相比于二叉树和红黑树而言，每一个节点能存储的索引数量大大增加，从而在数据量相同的情况下极大地减少了整棵树的高度。通过调大 B 树每一个节点存储的索引个数（阶数m - 1），可以在千万级的数据量的情况下，保持树的高度不超过4。这与二叉树红黑树需要十几层的结构相比，无疑提高了性能。

但 B 树与下面要介绍的 B+ 树相比，还是存在许多弊端的。

B+ 树

B+ 树是 B 树的变种，其拥有比 B 树更快的查询效率。B+ 树的特点：

  • 只有叶子节点存储数据，非叶子节点只存储索引和页地址，不存储数据
  • 叶子节点包含所有索引字段
  • 叶子节点中的数据索引从左到右递增排序（这也是索引被称为排好序的数据结构的原因）
  • 叶子节点间使用两个指针相互连接，构成一个双向链表，提高区间访问的性能

B+ 树中每个“叶子节点”实际上是一个页（Page），本节先用叶子节点称呼以方便理解，下文再详细分析页的结构

B+ 树结构图解：

1555906287178

图中每一个磁盘块即为一个页结构

根节点层的索引是常驻内存的，直接省掉一次 IO 成本。高版本的 MySQL 不仅根节点层，其叶子节点层也会直接放在内存中，效率更高。

B+ 树的优点

1、千万级数据量下，B+ 树最少可以只占 3 层。

原因：MySQL 里，每一个节点（页）分配的物理空间为 16kb。一个索引的类型为 bigint，占 8 个字节；两个索引间的指针地址占 6 个字节，因此每一个非叶子节点（页）最多分配 16 kb / (8 + 6) b = 1170 个。因此两页就可以分配 1170*1170 个索引，那么只需要三层（前两层只放索引，最后一层放索引和数据）即可容纳上千万条索引。这与二叉树和红黑树需要十几二十层相比，无疑提高了效率。

2、B+ 树只在叶子节点存储数据，从而在占用相同空间大小（高度相同）的情况下，B+ 树所能存储的索引数远大于 B 树。

原因：B 树的所有节点都会存储数据，这样每一个索引（bigint类型，占8个字节）都需要配上一个数据（可能占 1kb 个字节）（以 InnoDB 引擎为例），从而一个单位大约占 1kb 个字节。而 B+ 树的非叶子节点只用存储索引，无需存储数据地址，因此一个单位只占8个字节。这就导致了在占用相同空间大小的情况下，B+ 树所能存储的索引数远大于 B 树。

3、B+ 树可以很好地处理区间查找。

原因：B+ 树的叶子节点之间使用两个指针相互连接，构成一个双向链表结构。同时这条双向链表上的索引值是按照升序排列，因此也是排好序的。这样在进行区间查找时，只要找到这个区间的临界索引值，即可按着某一个方向一直遍历目标区间的所有数据。

例如上图中要想查询主键大于15的所有数据，只需要先找到主键为15的节点位置，然后一直沿着链表升序方向遍历即可得到所有主键大于15的数据。这一点上，B 树是无法做到的。

B+ 树与 B 树的区别

B+ 树可以看成是对 B 树的优化改造：

  • B+ 树只在叶子结点存储数据的逻辑地址，非叶子节点只存储索引（从而增加了每一层可存放的索引个数）
  • B+ 树的非叶子节点上存在的索引也会出现在叶子节点上
  • B+ 树的叶子节点组成了一个双向链表结构（从而提高了区间查找效率）

InnoDB 中的页（Page）

页结构即为前面 B+ 树中的叶子节点的实际数据结构

页（Page） 是 InnoDB 存储的最基本结构，也是 InnoDB 磁盘管理的最小单位，与数据库相关的所有内容都存储在Page结构里。Page分为几种类型：数据页（B-Tree Node），Undo页（Undo Log Page），系统页（System Page），事务数据页（Transaction System Page）等。每个数据页的大小为 16kb。

页是 InnoDB 从磁盘中读取数据时的一个基本单位，即一次从磁盘中最少取 16 kb 的数据组成一个页单位。

InnoDB 取数据时的单位为页，而不是行。一次取一页，而不是一次取一行

页的结构示意图：

image-20211029143152391

图里省略了每个数据对应的索引信息，只画出了数据信息和页目录。索引信息实际上是和数据存储在一起的

一个页结构中：

  • 用户数据区域：存储每条索引对应的真实数据
  • 页目录区域：相当于一个本书的目录，便于快速定位到目标索引位置。其内存储着索引分组，一个分组包含六条索引。页目录的目的：用少量空间换取遍历时间。例如想在当前页内查找索引值为 10 的记录，不需要从第一个索引开始一一遍历每个索引，而是可以直接在页目录内遍历，先找到大致区间，然后再在该子区间内逐个遍历直到找到目标索引
  • 页头：存储着前向指针和后向指针，用于链接前后两个页组成一条双向链表

对比上文中分析的 B+ 树结构：

1555906287178

该图中只画了每条索引和其数据，省略了页结构中的页目录

一个磁盘块即为一个页结构，上层的页结构中只保存索引值和其对应的地址（因此可以存储非常多的索引，大约1170个），最底层的页结构中既保存了索引值，又保存了真实数据。通常将最底层的页叫数据页，上层的页叫索引页。

索引特性里的排好序体现在：

  • 最底层的每一个页内部都是排好序的
  • 每个页和相邻的两个页之间也是排好序的

全表扫描方式：沿着最底层页组成的双向链表进行遍历，从最左侧页的第一个索引开始，沿着双向链表一直遍历所有索引。

页分裂

如果新插入的一条数据的索引值小于目前页中索引的最大值，并且当前页的数据量即将超出 16kb，此时即需要进行页分裂：将当前数据插入到当前页内索引大小合适的位置，并将原先的索引最大的数据调整到新的一页中，也就说整棵树结构进行了调整。

可以看出，这种页分裂的操作会增加额外的操作，无疑会降低效率。

而如果使用自增索引，插入新数据时就不会出现分裂的情况，因为新插入的索引值肯定比原先的都大，所以不需要进行页分裂。效率就会明显提升。

复合索引的数据结构

复合索引同样采用 B+ 树的数据结构，只不过从单个索引变为了多个索引：

image-20211028202151731

索引的比较顺序是有优先级的，先判断高优先级的索引，再判断低优先级的索引（索引的优先级是由创建索引时的顺序决定的）。以上图为例，先比较第一个索引 name ，再比较第二个索引 age，最后比较第三个索引 position。

复合索引时，就涉及到了索引最左前缀原则。

存储引擎中索引的实现

MyISAM

MyISAM 索引文件和数据文件是非聚集（分离）的（非聚集索引）：索引和数据存储的位置是分离的，其在磁盘上的位置：

  • *.frm 表结构的定义文件
  • *.MYD 数据文件（data）
  • *.MYI 索引文件（index）

以主键索引为例，描述 MyISAM 中 B+ 树的索引和真实数据间的关系：

image-20211028155748763

可以看到，整棵树上索引值的存储地址和真实数据的存储地址是分开的，每个索引只携带其对应数据的逻辑地址而不存储真实数据。

InnoDB

InnoDB 索引文件和数据文件是聚集的（聚集索引）：索引和数据是存储在一起的，叶子结点包含了完整的数据记录，其在磁盘上的位置：

  • *.frm 表结构的定义文件
  • *.ibd 数据和索引共同存储在一个文件中

以主键索引为例，描述 InnoDB 中 B+ 树的索引和真实数据间的关系：

image-20211028204317324

可以看到，索引和数据是存储在一起的，叶子节点中既包含索引值，又包含数据。这样做的好处，用内存空间换查询效率：直接将数据本身也加载到内存中，这样查询数据时减少了一次向磁盘中另一个文件查询数据内容的 IO 操作，效率会更高一些。相应的代价是每一层存储的节点数量要少于只存储逻辑地址的方式，因为一个数据占用的内存空间要远大于一个地址。

非主键索引（第二索引）则存储的不是真实数据，而是存储的对应数据的主键索引的值：

image-20211028205257723

根据第二索引进行查找时，将先找到目标数据的主键索引，然后再去根据主键索引值查找真实数据，这样性能明显是不如直接根据主键索引查找。这个过程也被称为回表（回去找主键索引树上存储的表数据）。

但这样做的好处是：

  • 节省存储空间：真实数据只存储在内存中的一个地方，节省了空间
  • 保证数据的一致性：因为数据只有一份，所以高并发下不会出现数据更新不一致的情况

为什么 InnoDB 表必须有主键，且推荐索引使用自增整数类型？

  1. 使用整型的原因：整型数据比大小比较快，占用字节也比较少（UUID这种字符串比大小比较慢，占用字节也多）
  2. 使用自增的原因：如果使用自增索引，那么使用 insert 新添加的索引只需要向当前最大索引后面添加即可，并不需要重新调整整棵树的结构，不需要让树进行分裂再重新平衡，这样插入速度就会比较快；而如果不自增的话， 随便插入很容易让节点进行分裂、让树重新调整平衡，速度就会降低

InnoDB 中的两种扫描方式

  • 全表扫描方式：沿着最底层页组成的双向链表进行遍历，从最左侧页的第一个索引开始，沿着双向链表一直遍历所有索引。
  • 索引 + 回表扫描方式：主要针对非主键索引。先按照非主键索引进行快速查询，查到其对应的主键值后，再回表去查询主键树中的真实数据。

InnoDB 会在不同的场景下权衡使用哪种方式，找出一种更高效的方式进行实际查询。

索引语法

创建索引

1
CREATE INDEX emp_ename_index ON emp(ename);

给emp表的ename字段添加索引并起别名：emp_ename_index。

删除索引

1
DROP INDEX emp_ename_index ON emp;

将emp表上的emp_ename_index索引对象删除。

查看某一个SQL语句是否使用了索引进行检索

1
EXPLAIN SELECT * FROM emp WHERE ename = 'KING';

索引的使用

1
2
3
4
5
6
7
8
9
10
11
12
-- 1. 在创建表的时候给字段增加索引
-- 2. 创建完毕后，增加索引
SHOW INDEX FROM student;

-- 增加一个全文索引  ADD FULLTEXT INDEX 索引名（列名）
ALTER TABLE school.`student` ADD FULLTEXT INDEX `studentName`(`studentName`);

-- EXPLAIN 分析sql执行的状况 

EXPLAIN SELECT * FROM student; -- 非全文索引

SELECT * FROM student WHERE MATCH(studentName) AGAINST('张');

索引设计原则

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。

  • 索引不是越多越好（因为索引也是需要维护的，太多的话反而降低性能）
  • 不要对经常变动的数据加索引（因为DML之后，索引需要重新排序）
  • 小数据量的表不需要加索引（数据量庞大时才需要）
  • 对查询频次较高，且数据量比较大的表建立索引
  • 索引一般加在常用来查询的字段上（常出现在WHERE之后）
  • 建议通过主键或UNIQUE字段修饰的字段进行查询（区分度越高）
  • 使用短索引，索引创建之后也是使用硬盘来存储的。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升 MySQL 访问索引的 I/O 效率。
  • 利用最左前缀原则，N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率。

EXPLAIN 指令

EXPLAIN：SQL 的执行计划，使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的。

查询 SQL 语句的执行计划 ：

1
explain select * from tb_item where id = 1;

1552487489859

1
explain select * from tb_item where title = '阿尔卡特 (OT-979) 冰川白 联通3G手机3';

1552487526919

各个字段的含义：

  • id：表的读取顺序。
  • select_type：数据读取操作的操作类型。
  • possible_keys：哪些索引可以使用。
  • key：哪些索引被实际使用。
  • ref：表之间的引用。
  • rows：每张表有多少行被优化器查询。
  • extra：额外信息

EXPLAIN 字段

id

id：表的读取和加载顺序。值有以下三种情况：

  • id相同，执行顺序由上至下。
  • id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。
  • id相同不同，同时存在。永远是id大的优先级最高，id相等的时候顺序执行。

示例：

1
EXPLAIN SELECT * FROM t_role r , (SELECT * FROM user_role ur WHERE ur.`user_id` = '2') a WHERE r.id = a.role_id ;

1556103294182

先执行 id = 2 的，然后按上到下顺序执行 id = 1 的。

select_type

select_type：数据查询的类型，主要是用于区别，普通查询、联合查询、子查询等的复杂查询。

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

type

type：访问类型排列。

从最好到最差依次是：system>const>eq_ref>ref>range>index>ALL。除了ALL没有用到索引，其他级别都用到索引了。

一般来说，得保证查询至少达到range级别，最好达到ref。

  • system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计。
  • const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转化为一个常量。
  • eq_ref：唯一性索引扫描，读取本表中和关联表表中的每行组合成的一行，查出来只有一条记录。除了 system 和const 类型之外，这是最好的联接类型。
  • ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的所有行（有多行匹配）
  • range：只检索给定范围的行，一般就是在WHERE语句中出现了BETWEEN、< >、in等的查询。这种范围扫描索引比全表扫描要好，因为它只需要开始于索引树的某一点，而结束于另一点，不用扫描全部索引。
  • index：Full Index Scan，全索引扫描，index和ALL的区别为index类型只遍历索引树。也就是说虽然ALL和index都是读全表，但是index是从索引中读的，ALL是从磁盘中读取的。
  • ALL：Full Table Scan，没有用到索引，全表扫描。

下面逐个分析比较常见的几个类型。

  1. const：唯一性索引扫描（常数）。出现在使用primary key或者unique索引、并只匹配一行数据时。例如：
1
select * from t_user where id = 1;

因为要查询的索引是唯一的，因此可以在对应的索引树上直接快速定位到目标数据，不需要做额外的遍历。只需要有限次地在树中查找即可快速定位。

  1. eq_ref：唯一性索引扫描。和 const 一样，同样要求查询的是唯一性索引，但必须要联表查询，常用于A表的主键关联B表的主键。因为要查找的索引是唯一的，所以可以直接定位到目标数据，不需要在树上进行大量遍历。例如：
1
select * from t_user u, t_role r where u.id = r.id;

上述查询用到了两个表，并且两个表要匹配的字段都是唯一性索引，这时也能有限次地在树中快速定位到目标数据。

  1. ref：非唯一性索引扫描。与 const 相比，区别在于要查询的不是唯一性索引，有多条记录可以与目标索引匹配，返回结果也是多行数据（const只返回一行）。因为这些行的索引值是相同的，所以同样可以在对应索引树上快速定位到这些行的数据，同样不需要额外遍历。例如：
1
select * from t_user where name = "bill";  -- name 索引不是唯一性索引
  1. range：只检索给定范围的行，一般就是在WHERE语句中出现了BETWEEN、< >、in等的查询。这种范围扫描索引比全表扫描要好，因为它只需要开始于索引树的某一点，而结束于另一点，不用扫描全部索引（只需要先定位到临界值的位置，然后沿着最底层页组成的双向链表一直遍历即可）。例如：
1
select * from t_user where id > 5;

查找时，先查询 id = 2 的数据位置，然后沿着双向链表的方向一直向后遍历所有数据即可。

  1. index：全索引扫描。常出现在查询某个索引对应的全部数据（而不是整个表的所有数据），与 all 的区别在于，不需要去主键树上遍历整个表的数据，而只需要在目标索引树上遍历最底层页上的所有索引数据即可。这是因为查询时只要求返回索引数据，不需要全表数据，所以没必要去主键树上遍历全表数据，只需要在目标索引树上遍历全部目标索引值即可。效率略高于 all。例如：
1
select name from t_user;

因为 name 所在的索引树上天然就保存了 name 的信息（因为索引数据就是 name 字段本身），所以直接在该索引树上遍历即可得到所有 name 数据。

  1. all：全表扫描。效率最差的一种查询，需要去主键树上遍历全表数据。例如：
1
select * from t_user;

index 与 all 的区别在于：要查询的结果是否恰好是建了索引的列

possible_keys 和 key

possible_keys：显示可能应用在这张表中的索引，一个或者多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

key：实际使用的索引。如果为NULL，则没有使用索引。查询中如果使用了覆盖索引，则该索引仅仅出现在key列表中。示例（col1、col2 组成了复合索引）：

1
2
3
4
5
mysql> explain select col1, col2 from t1;

-- possible_keys: NULL
-- key: idx_col1_col2
-- Extra: Using index

这种情况，因为 sql 语句中没有显式指定用索引去查找，所以 possible_keys 显示为 NULL，而又因为要查找的 col1、col2 恰好建了索引，所以 key 为 idx_col1_col2。同时只出现了 Using index，而没有 Using where ，表明索引只用来读取数据而没有执行查找动作。

上述例子不是全表扫描，而是索引的原因：col1和col2索引不涉及存储数据，所以每一页存储的索引更多，从而页数更少，所以从磁盘读取页数据的次数更少，效率更高，所以可以直接从这些索引构成的树上遍历得到覆盖索引值。

key_len

key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。在不损失精度的情况下，长度越短越好。

key_len计算规则：https://blog.csdn.net/qq_34930488/article/details/102931490

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
mysql> desc pms_category;
+---------------+------------+------+-----+---------+----------------+
| Field         | Type       | Null | Key | Default | Extra          |
+---------------+------------+------+-----+---------+----------------+
| cat_id        | bigint(20) | NO   | PRI | NULL    | auto_increment |
| name          | char(50)   | YES  |     | NULL    |                |
| parent_cid    | bigint(20) | YES  |     | NULL    |                |
| cat_level     | int(11)    | YES  |     | NULL    |                |
| show_status   | tinyint(4) | YES  |     | NULL    |                |
| sort          | int(11)    | YES  |     | NULL    |                |
| icon          | char(255)  | YES  |     | NULL    |                |
| product_unit  | char(50)   | YES  |     | NULL    |                |
| product_count | int(11)    | YES  |     | NULL    |                |
+---------------+------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)


mysql> explain select cat_id from pms_category where cat_id between 10 and 20 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pms_category
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY  # 用到了主键索引，通过查看表结构知道，cat_id是bigint类型，占用8个字节
      key_len: 8        # 这里只用到了cat_id主键索引，所以长度就是8！
          ref: NULL
         rows: 11
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

ref

ref：当前表的索引引用了其他表的哪些索引。如果没引用其他表的索引，就是 const，否则就显示那些索引。示例：

1
2
3
4
5
6
7
8
9
10
11
12
mysql> explain select t2.* 
    -> from t1, t2, t3 
    -> where t1.id = t2.id and t1.id = t3.id
    -> and t1.other_colum = '';
    
+-----------------+-------------+
| table | type    | ref         |
+-----------------+-------------+
| t1    | ref     | const       |
| t3    | eq_ref  | test.t1.ID  |
| t2    | eq_ref  | test.t1.ID  |
+-----------------+-------------+

rows

rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录需要读取的行数。

Extra

Extra：包含不适合在其他列中显示但十分重要的额外信息，主要用于显示 ORDER BY 和 GROUP BY 语句进行排序时的执行计划。

  • Using filesort：出现在使用 order by 进行排序的情况下，说明 MySQL 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取（因为查询时没有指定按照索引列进行 order by，而是按照非索引列进行排序）。MySQL 中无法利用索引完成的排序操作成为"文件内排序"。
  • Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用了临时表。常见于分组查询group by。临时表对系统性能损耗很大，比Using filesort更糟糕。
  • Using index：表示相应的SELECT操作中使用了覆盖索引（要查询的字段恰好全部都建了索引），避免访问了表的数据行（只需要访问索引列），效率不错！如果同时出现Using where，表示索引被用来执行索引键值的查找；如果没有同时出现Using where，表明索引用来读取数据而非执行查找动作。

排序时，如果是根据索引列进行排序，则会 Using index，如果根据非索引列进行排序，则会 Using filesort。

注意：如果 order by 使用到了多个索引，则这几个索引必须事先创建成一个复合索引，并且书写时的顺序和创建复合索引时的顺序必须一致，否则还是会进行额外的 Using filesort。因为复合索引排序时的顺序是固定好“先左后右”的，如果 order by 时没有遵循这个顺序，就无法按照复合索引树进行快速遍历（这点和 where很不一样，where里出现顺序无关，而order by 有关，因为 where里的条件不需要有顺序，而 order by 书写的顺序是有影响的，先排前面写的，再排后面写的）。

Using temporary 常见于 group by，场景举例（col1 和 col2 共同组成复合索引，col1 在前，col2 在后）：

1
2
3
mysql> explain select col1 from t1 where col1 in ('ac', 'ab', 'aa') group by col2;

-- Extra: Using where; Using index; Using temporary; Using filesort

这种情况下因为 group by 里直接跳过了 col1 直接对 col2 进行分组，就会导致前面用索引查出来的 col1 数据不能直接按照索引顺序获取到分组结果（因为跳过了 col1 直接对 col2 分组），必须把这些数据先存储到一个临时表里，然后按照 col2 进行排序分组（因为获取到的 col1 数据是先按照 col1 排序，然后再按照 col2 排序的，所以获取到的数据必须得先存储到临时表里，然后再额外进行一次排序）

优化方法就是，group by 改成 group by col1, col2，此时 Extra 里的内容就是：Extra: Using where; Using index for group-by

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
# 排序没有使用索引（使用非索引列进行排序）
mysql> explain select name from pms_category where name='Tangs' order by cat_level \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pms_category
   partitions: NULL
         type: ref
possible_keys: idx_name_parentCid_catLevel
          key: idx_name_parentCid_catLevel
      key_len: 201
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where; Using index; Using filesort
1 row in set, 1 warning (0.00 sec)

#~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
# 排序使用到了索引（使用索引列进行排序）

mysql> explain select name from pms_category where name='Tangs' order by parent_cid,cat_level\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pms_category
   partitions: NULL
         type: ref
possible_keys: idx_name_parentCid_catLevel
          key: idx_name_parentCid_catLevel
      key_len: 201
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

覆盖索引：就是 select 的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。

  • Using index：使用覆盖索引的时候就会出现
  • Using where：where 语句中使用到了索引，如果不显示这个，说明没有在 where 中使用索引
  • Using index condition：查找使用了索引，但是需要回表查询数据
  • Using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

如果出现 Using index 的同时没有同时出现 Using where，表明索引只用来读取数据而没有执行查找动作。例如：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 注意：如果要使用覆盖索引，一定不能写SELECT *，要写出具体的字段。
mysql> explain select cat_id from pms_category \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pms_category
   partitions: NULL
         type: index
possible_keys: NULL       
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 1425
     filtered: 100.00
        Extra: Using index   # select的数据列只用从索引中就能够取得，不必从数据表中读取   
1 row in set, 1 warning (0.00 sec)

其他：

  • Using join buffer：使用了连接缓存。
  • impossible where：WHERE子句的值总是false，不能用来获取任何元组。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> explain select name from pms_category where name = 'zs' and name = 'ls'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Impossible WHERE   # 不可能字段同时查到两个名字
1 row in set, 1 warning (0.00 sec)

索引失效情况

  • 不满足最佳左前缀原则
  • 在索引列上做任何操作（计算、函数、（自动或手动）类型转换）都会导致索引失效而转向全表扫描。
  • 字符串不加单引号索引失效（变相等于第二条：对索引列做强制类型转换操作）
  • 范围查询条件右边的索引会全部失效
  • 在使用 != 或者 <> 的时候有时可能无法使用索引会导致全表扫描
  • is null、is not null 有时可能会无法使用索引（视情况而定，并不绝对）
  • 以 % 开头的 like 模糊查询（like '%abc' ），会变成全表扫描（但使用覆盖索引就不会全表扫描了）
  • 用 or 分割开的条件， 如果 or 前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到
  • 如果 MySQL 评估使用索引比全表更慢，则不使用索引

下面详细分析这些索引失效的情况，并给出解决方案。

索引最左前缀原则

最左前缀原则：在使用复合索引的情况下，查询从索引的最左前列开始并且不跳过索引中的字段。如果不包含，就会出现索引失效的情况。

最左前缀原则只针对 where 条件语句，前面 select 里要查询的字段和这个原则没关系

image-20211028202151731

以上图为例，分析下面三个查找语句的执行情况：

1
2
3
4
5
KEY idx_name_age_position (name, age, position) USING BTREE

EXPLAIN SELECT * FROM empolyees WHERE name = 'BILL' AND age = 31;
EXPLAIN SELECT * FROM empolyees WHERE age = 30 AND position = 'dev';
EXPLAIN SELECT * FROM empolyees WHERE position = 'manager';

根据其创建索引时的顺序可知，这三个索引的优先级为：name > age > position

  • 第一句使用了前两个索引，因为复合索引是按照优先级排序的，从上图可知，前两个索引是排好序了，所以就算最后一个字段没有用于查询，也可以只使用前两个索引进行快速定位，将范围缩小到某一个局部节点，即找到了目标区间内的数据。
  • 第二句则跳过第一个索引，直接试图根据第二个索引去查找，会发现这时根本无法直接定位到一个小区间，因为第二个索引的优先级别比较低，哪个局部区间内都有该索引值，无法直接根据该索引快速定位到目标局部区域。例如在上图中不看第一个索引，只看第二个索引值，会发现第二个索引值只在每一个节点内是有序的，两个节点间是无序的，整棵树来看是无序的，没有符合最左前缀原则，因此这种情况下索引失效。
  • 第三句跳过了前两个所有，直接试图根据第三个索引去查找，同样没有符合最左前缀原则，因此这种情况下索引失效。

是否符合最左前缀原则和 WHRER 语句里几个字段的出现顺序无关，书写的顺序无所谓，只要包含左侧的索引即可。

索引列上不计算

在索引列上进行任何操作（包含类型转换），会使索引失效。

这是因为，对索引列进行操作时，操作后的结果就无法和原先索引组成的 B+ 树上的值进行关联对比，即操作后的值就无法在原先的树上找到对应值了，因此索引失效。例如：

explain

运算操作示例：

1
EXPLAIN SELECT * FROM t1 WHERE age + 1 = 1;

字符串不加单引号

该情况本质上也属于在索引列上进行类型转换操作

该情况常出现在忘记给字符串值添加单引号，导致索引值进行强制转换，从而失效。该情况发生了字符串类型的强制类型转换，将字符串类型的索引转换成了整型类型，从而导致索引失效。例如：

  • 覆盖索引情况： select name from t1 where name = 1 会先把 name 索引的所有值全部转成数字，再去和 1 比较是否相等，同时因为是覆盖索引，所以只需要遍历所有的 name 索引，不需要全表扫描
  • 全表扫描情况：select * from t1 where name = 1 会先把 name 索引的所有值全部转成数字，再去和 1 比较是否相等，同时因为不是覆盖索引，所以需要全表扫描，扫描每一个数据时将其 name 字段值转换成数字，然后再与 1 比较

MySQL 中字符串与整型类型转换时：

  • 如果字符是纯数字，转为对应数字（例如 '100' 转成数字 100）
  • 如果字符带有字符，转为 0（例如 'a' 转成数字 0）

整型类型与字符串类型转换时：直接将该数字变成一个字符串（例如 100 转为 '100'）

类型转换示例：

1
2
3
4
5
6
7
8
/* 使用正确，使用到了覆盖索引，type = index */
EXPLAIN SELECT `id`, `name` FROM `staffs` WHERE `name` = 'Ringo';

/* 使用错误，先将 name 转成 2000 再判断，使用到了覆盖索引，type = index */
EXPLAIN SELECT `id`, `name` FROM `staffs` WHERE `name` = 2000;

/* 使用错误，索引失效，全表扫描，type = ALL */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 2000;

1556172967493

范围之后全失效

范围查询条件右边的索引会全部失效。例如：

explain

分析该例子，存储引擎将使用前两个索引 name 和 age 进行快速查找，迅速定位到 name = 'zs' AND age > 18 范围内的数据。但是此时因为 age 使用了范围条件，导致 pos 索引无法在定位到的这些数据内快速定位到 pos = 'manager' 的数据，只能在这个范围数据内逐个遍历去定位，就无法利用 B+ 树的特性快速定位到目标数据了（本质是因为复合索引的特性，即使 age 不同的数据也可能会满足 pos = 'manager' ，所以要遍历所有 age > 18 的数据，看哪个里面的 pos = 'manager'，这也有点像最左匹配原则）。

就好像先定位到的范围内有 1000 条数据，这些数据都满足 name = 'zs' AND age > 18，但是这些数据都可能会满足 pos = 'manager' ，所以要一一测试。而反过来则不会失效，如果前面的都是等于条件，最后一个是大于条件，那么还是不影响利用复合索引快速定位。

覆盖索引尽量用

覆盖索引：只访问索引的查询，索引列和查询列一致

使用覆盖索引时不需要进行回表查询，能够提高性能。减少使用SELECT *，这样肯定会进行回表。例如：

使用覆盖索引

如果查询的有些列没有加索引，则会进行额外的回表操作去查询这些没有索引的列在主键树上的值，因此会降低性能。例如，下图中 password 字段没有添加索引，因此 Extra 里显示 Using index codition，表示进行了额外的回表：

1556173986068

不等有时会失效

该情况可归类于：如果 MySQL 评估使用索引比全表更慢，则不使用索引。

在使用 != 或者 <> 的时候有时可能无法使用索引会导致全表扫描。

索引完全失效的前提：查询结果为 select * 而非覆盖索引时完全失效， type = ALL。若查询结果为覆盖索引时索引仍有效， type = index

注意，是有时可能，而不是绝对不会。会出现这种情况的原因是：MySQL 的优化器会在执行语句前先分析表中要查询索引值的占比情况，判断当前要查询的索引值占所有索引值的比例。

  • 如果该比例比较低，则进行 != 或 <> 判断时，若去索引树上查找，会发现绝大多数的索引值都不等于目标值，这显然会浪费很多的时间，这时还不如直接去全表扫描来得快。这时 type = index 或者 type = ALL（区别在于是否为覆盖索引，如果是覆盖索引就是 index，否则为 ALL）
  • 如果该比例很高，说明很多索引值都等于目标值，那么进行 != 或 <> 判断时，会发现只有比较少的数据是需要去查找的（因为大多都是目标值，这些目标值是不需要查找的，相当于直接过滤掉了大量数据），那么这时就不需要去全表扫描了，直接在索引树上进行少量的查找即可快速定位到。这时 type = range

补充：比例较低时，如果是覆盖索引的情况，则会进行 index 而不是 ALL，因为这时要查的只是索引数据，没必要去全表扫描，而是直接遍历索引树上的双向链表即可。

注意：上面讨论的情况为!= 或 <>，如果是 =、 > 或 < 的情况同样会进行这种评估，评估使用索引能否带来的高效。

示例：表中 age 字段添加了索引，大多数的值为18，少数的值为 2。

1
2
3
4
5
6
7
8
9
10
11
12
13
/* 值为2的数据占比比较低，优化器衡量后决定不走索引树去查找了，直接遍历。
又因为是 select * 而不是 select age（覆盖索引0），所以是 ALL 而不是 index */
explain select * from test where age != 2;

/* 值为2的数据占比比较低，优化器衡量后决定不走索引树去查找了，直接遍历。
又因为是 select age（覆盖索引0），所以是 index */
explain select age from test where age != 2;

/* 值为18的数据占比比较高，优化器衡量后决定走索引树去查找，type = range */
explain select * from test where age != 18;

/* 值为18的数据占比比较高，优化器衡量后决定走索引树去查找，type = range */
explain select age from test where age != 18;

总结：这种情况适用于必须是不等这种排除当前数据的操作（不能是等于或大于小于类型），因为这会去花很多时间遍历，所以优化器就会权衡值不值得去索引树上遍历一次，还是直接去全表扫描更快一些，如果衡量结果是去全表扫描更快一些（目标值占比比较小），则就会进行全表扫描。

另外，这种分析同样适用于 “is NULL, is NOT NULL” 有时索引失效 的情况，原理是相同的。MySQL 会先分析 NULL 的占比，并根据该值大小进行决策是否直接进行全表扫描。

总结：优化器在发现要查找的（满足条件）的数据占比较低时（说明查找的代价较低），就会使用索引，此时查找的代价比较小，速度较快。

如果一个查询语句中用到了多个单列索引，那么优化器会评估选择辨识度最高的那个索引进行查找

like % 少加左

% 加在左边的 like 模糊查询（like '%abc' ），会变成全表扫描（但使用覆盖索引就不会全表扫描了，会变成 type = index）：

1
2
3
4
5
/* 索引失效，全表扫描，type = ALL */
EXPLAIN SELECT * FROM `staffs` WHERE `name` LIKE '%ing%';

/* 索引失效，全表扫描，type = ALL */
EXPLAIN SELECT * FROM `staffs` WHERE `name` LIKE '%ing';

但如果仅仅是尾部模糊匹配（% 加在右边），索引不会失效，这种情况其实类似于范围查找（like 'xx%' 效果类似于where name > 'xxx'，但是不同的是 like 'xx%' 后面的索引仍然生效，而 后者会失效），type = range：

1
2
/* 使用索引范围查询，type = range */
EXPLAIN SELECT * FROM `staffs` WHERE `name` LIKE 'Rin%';

如果一定要在左边加 %，而且还要保证索引不失效，那么使用覆盖索引来编写SQL，不需要全表扫描，性能略好：

1
2
/* 索引不，type = index */
EXPLAIN SELECT `name` FROM `staffs` WHERE `name` LIKE '%in%';

案例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
/* 使用到了覆盖索引 */
EXPLAIN SELECT `id` FROM `staffs` WHERE `name` LIKE '%in%';

/* 使用到了覆盖索引 */
EXPLAIN SELECT `name` FROM `staffs` WHERE `name` LIKE '%in%';

/* 使用到了覆盖索引 */
EXPLAIN SELECT `age` FROM `staffs` WHERE `name` LIKE '%in%';

/* 使用到了覆盖索引 */
EXPLAIN SELECT `pos` FROM `staffs` WHERE `name` LIKE '%in%';

/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`, `name` FROM `staffs` WHERE `name` LIKE '%in%';

/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`, `age` FROM `staffs` WHERE `name` LIKE '%in%';

/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`,`name`, `age`, `pos` FROM `staffs` WHERE `name` LIKE '%in';

/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`, `name` FROM `staffs` WHERE `pos` LIKE '%na';

/* 索引失效 全表扫描 */
EXPLAIN SELECT `name`, `age`, `pos`, `add_time` FROM `staffs` WHERE `name` LIKE '%in';

模糊查询百分号一定加前边

OR 中一个没索引整体都不用索引

用 or 分割开的条件， 如果 or 前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。（推荐用 UNION代替 OR）

示例：name 字段是索引列 ， 而 createtime 不是索引列，中间是 OR 进行连接是不走索引的 ：

1
explain select * from tb_seller where name='黑马程序员' or createtime = '2088-01-01 12:00:00'\G;

1556174994440

MySQL 自己评估是否要使用索引

MySQL 的优化器会在执行语句前先分析语句和表中的情况，并据此判断是否要使用索引。如果MySQL评估使用索引比全表扫描更慢，则不使用索引（前提是必须得回表）。例如：

  • 使用 != 或 <> 时会计算索引临界值占所有值的比例
  • 使用 "is NULL, is NOT NULL" 时会计算 NULL 的占比
  • 使用 =、 > 或 < 时，会计算满足条件的临界值占所有值的比例

注意：索引完全失效的前提是查询结果为 select *而不是覆盖索引，如果查询的是覆盖索引，则索引仍然生效，type = index。因为覆盖索引不需要再回表查询，会出现失效是因为优化器认为直接去全表扫描的效率高于先去索引树遍历再去回表，所以如果查询结果是覆盖索引，根本不会回表，所以就不会失效，顶多是 index。

示例：优化器先计算目标索引值在表中的占比，如果发现占比较大，并且是 select * ，说明要将做很多次回表，还不如直接去全表扫描：

1556175445210

上述例子没有走索引，直接全表扫描的原因：如果走索引，先根据第一个索引定位到了符合条件的若干索引数据，然后再一一遍历到这些索引位置，获取其主键值，然后再去主键索引树上一一去查询，若索引数据较少还好，但如果符合条件的索引数据量极大（体现在上文中描述的占比较高），则 InnoDB 发现这种情况下，与其进行大量的回表，还不如直接去全表扫描来的高效。因为这种范围查找，在先使用第一个索引定位到后，还是要一一沿着双向链表进行遍历，找到所有区间内的索引，然后再对这些索引一个一个的去主键索引树上遍历，其实这样效率并不高，还不如直接去主键索引树上全表扫描来的快。

针对 > 或 < 的情况，是否直接进行全表扫描和设置的临界值大小有关系：如果临界值设置的太小，需要遍历很多，InnoDB就会判定直接全表扫描效率更高。如果临界值设置的比较大，遍历+回表的次数就会比较少，InnoDB就会判定索引 + 回表更高效。

当然上述讨论的情况都是查询结果必须要进行回表（例如 select *）。如果不需要回表（例如覆盖索引），则就不存在上面讨论的这些弊端，肯定还是会走索引，只不过可能是 index 级别。

覆盖索引情况分析：如果要查询的字段正好是索引，这样就不需要回表了，直接在目标索引树上进行查找，用该字段的值作为索引去查找，查找到的结果本身就是要查的目标字段，所以不用回表了，可以直接找到符合条件的目标字段。并且查出的结果本身就是排好序的，因为是在排好序的索引双向链表上直接返回的，本身就排好了序，不需要再 ORDER BY。

总结：在必须要回表的情况下，优化器在发现要查找的（满足条件）的数据占比较低时（说明查找的代价较低），就会使用索引，此时查找的代价比较小，速度较快。

索引失效相关题目

假设 a，b，c 组成了一个复合索引：index(a, b, c)

Where 语句 索引是否被使用
where a = 3 Y，使用到a
where a = 3 and b = 5 Y，使用到a，b
where a = 3 and b = 5 Y，使用到a，b，c
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4 N，没有用到a字段
where a = 3 and c = 5 使用到a，但是没有用到c，因为b断了
where a = 3 and b > 4 and c = 5 使用到a，b，但是没有用到c，因为c在范围之后
where a = 3 and b like ‘kk%’ and c = 4 Y，a，b，c都用到
where a = 3 and b like ‘%kk’ and c = 4 只用到a
where a = 3 and b like ‘%kk%’ and c = 4 只用到a
where a = 3 and b like ‘k%kk%’ and c = 4 Y，a，b，c都用到

综合案例：

数据准备

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
/* 创建表 */
CREATE TABLE `test03`(
`id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
`c1` CHAR(10),
`c2` CHAR(10),
`c3` CHAR(10),
`c4` CHAR(10),
`c5` CHAR(10)
);

/* 插入数据 */
INSERT INTO `test03`(`c1`,`c2`,`c3`,`c4`,`c5`) VALUES('a1','a2','a3','a4','a5');
INSERT INTO `test03`(`c1`,`c2`,`c3`,`c4`,`c5`) VALUES('b1','b22','b3','b4','b5');
INSERT INTO `test03`(`c1`,`c2`,`c3`,`c4`,`c5`) VALUES('c1','c2','c3','c4','c5');
INSERT INTO `test03`(`c1`,`c2`,`c3`,`c4`,`c5`) VALUES('d1','d2','d3','d4','d5');
INSERT INTO `test03`(`c1`,`c2`,`c3`,`c4`,`c5`) VALUES('e1','e2','e3','e4','e5');

/* 创建复合索引 */
CREATE INDEX idx_test03_c1234 ON `test03`(`c1`,`c2`,`c3`,`c4`);

题目

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
/* 最好索引怎么创建的，就怎么用，按照顺序使用，避免让MySQL再自己去翻译一次 */

/* 1.全值匹配 用到索引c1 c2 c3 c4全字段 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c3` = 'a3' AND `c4` = 'a4';

/* 2.用到索引c1 c2 c3 c4全字段 MySQL的查询优化器会优化SQL语句的顺序*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c4` = 'a4' AND `c3` = 'a3';

/* 3.用到索引c1 c2 c3 c4全字段 MySQL的查询优化器会优化SQL语句的顺序*/
EXPLAIN SELECT * FROM `test03` WHERE `c4` = 'a4' AND `c3` = 'a3' AND `c2` = 'a2' AND `c1` = 'a1';

/* 4.用到索引c1 c2 c3字段，c4字段失效，范围之后全失效 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c3` > 'a3' AND `c4` = 'a4';

/* 5.用到索引c1 c2 c3 c4全字段 MySQL的查询优化器会优化SQL语句的顺序*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c4` > 'a4' AND `c3` = 'a3';

/* 
   6.用到了索引c1 c2 c3三个字段, c1和c2两个字段用于查找,  c3字段用于排序了但是没有统计到key_len中，c4字段失效
*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c4` = 'a4' ORDER BY `c3`;

/* 7.用到了索引c1 c2 c3三个字段，c1和c2两个字段用于查找, c3字段用于排序了但是没有统计到key_len中*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' ORDER BY `c3`;

/* 
   8.用到了索引c1 c2两个字段，c4失效，c1和c2两个字段用于查找，c4字段排序产生了Using filesort说明排序没有用到c4字段 ，因为断了c3，想隔着c3去排序c4是做不到的，需要额外的文件排序
*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' ORDER BY `c4`;

/* 9.用到了索引c1 c2 c3三个字段，c1用于查找，c2和c3用于排序 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c5` = 'a5' ORDER BY `c2`, `c3`;

/* 10.用到了c1一个字段，c1用于查找，c3和c2两个字段索引失效，产生了Using filesort */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c5` = 'a5' ORDER BY `c3`, `c2`;

/* 11.用到了c1 c2 c3三个字段，c1 c2用于查找，c2 c3用于排序 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND  `c2` = 'a2' ORDER BY c2, c3;

/* 12.用到了c1 c2 c3三个字段，c1 c2用于查找，c2 c3用于排序 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND  `c2` = 'a2' AND `c5` = 'a5' ORDER BY c2, c3;

/* 
   13.用到了c1 c2 c3三个字段，c1 c2用于查找，c2 c3用于排序 没有产生Using filesort 
      因为之前c2这个字段已经确定了是'a2'了，这是一个常量，再去ORDER BY c3,c2 这时候c2已经不用排序了！
      所以没有产生Using filesort 和(10)进行对比学习！
*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c5` = 'a5' ORDER BY c3, c2;


/* GROUP BY 表面上是叫做分组，但是分组之前必定排序。 */

/* 14.用到c1 c2 c3三个字段，c1用于查找，c2 c3用于排序，c4失效 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c4` = 'a4' GROUP BY `c2`,`c3`;

/* 15.用到c1这一个字段，c4失效，c2和c3排序失效产生了Using filesort */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c4` = 'a4' GROUP BY `c3`,`c2`;

总结：GROUP BY基本上都需要进行排序，索引优化几乎和ORDER BY一致，但是GROUP BY会有临时表的产生。

SQL 优化分析步骤

在应用的开发过程中，由于初期数据量小，开发人员写 SQL 语句时更重视功能上的实现，但是当应用系统正式上线后，随着生产数据量的急剧增长，很多 SQL 语句开始逐渐显露出性能问题，对生产的影响也越来越大，此时这些有问题的 SQL 语句就成为整个系统性能的瓶颈，因此我们必须要对它们进行优化。

当面对一个有 SQL 性能问题的数据库时，我们的分析顺序：

  • 观察至少 1 天，看看生产环境下的慢 SQL 情况
  • 定位低效率的 SQL：
    • 开启慢查询日志，设置阈值，比如超过 5 秒钟的就是慢 SQL，并将它抓取出来
    • 或 show processlist 实时查看 SQL 执行情况
  • EXPLAIN 分析 SQL 执行计划
  • show profiles 查询 SQL 在 MySQL 数据库中的执行细节和生命周期情况
  • 运维经理或者DBA，进行 MySQL 数据库服务器的参数调优

查看 SQL 执行频率

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。show [session|global] status 可以根据需要加上参数“session”或者“global”来显示 session 级（当前连接）的计结果和 global 级（自数据库上次启动至今）的统计结果。如果不写，默认使用参数是“session”。

下面的命令显示了当前 session 中所有统计参数的值：

1
show status like 'Com_______';

1552487172501

1
show status like 'Innodb_rows_%';

1552487245859

Com_xxx 表示每个 xxx 语句执行的次数，我们通常比较关心的是以下几个统计参数。

参数 含义
Com_select 执行 select 操作的次数，一次查询只累加 1。
Com_insert 执行 INSERT 操作的次数，批量插入只累加一次。
Com_update 执行 UPDATE 操作的次数。
Com_delete 执行 DELETE 操作的次数。
Innodb_rows_read select 查询返回的行数。
Innodb_rows_inserted 执行 INSERT 操作插入的行数。
Innodb_rows_updated 执行 UPDATE 操作更新的行数。
Innodb_rows_deleted 执行 DELETE 操作删除的行数。
Connections 试图连接 MySQL 服务器的次数。
Uptime 服务器工作时间。
Slow_queries 慢查询的次数。
  • Com_*** : 这些参数对于所有存储引擎的表操作都会进行累计。
  • Innodb_*** : 这几个参数只是针对 InnoDB 存储引擎的，累加的算法也略有不同。

定位低效率的 SQL

可以通过以下两种方式定位执行效率较低的 SQL 语句。

  • 慢查询日志：通过慢查询日志定位那些执行效率较低的 SQL 语句，用--log-slow-queries[=file_name]选项启动时，mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件。
  • show processlist：慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。

慢查询日志

  • MySQL的慢查询日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应时间超过阈值的语句，具体指运行时间超过 long_query_time 值的 SQL，则会被记录到慢查询日志中。
  • long_query_time 的默认值为 10，意思是运行 10 秒以上的语句。
  • 由慢查询日志来查看哪些 SQL 超出了我们的最大忍耐时间值，比如一条SQL执行超过5秒钟，我们就算慢 SQL，希望能收集超过 5 秒钟的 SQL，结合之前 explain 进行全面分析。

特别说明

默认情况下，MySQL 数据库没有开启慢查询日志，需要我们手动来设置这个参数。

当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

查看慢查询日志是否开以及如何开启

  • 查看慢查询日志是否开启：SHOW VARIABLES LIKE '%slow_query_log%';。

  • 开启慢查询日志：SET GLOBAL slow_query_log = 1;。使用该方法开启 MySQL 的慢查询日志只对当前数据库生效，如果 MySQL 重启后会失效。

1
2
3
4
5
6
7
8
9
10
11
12
13
# 1、查看慢查询日志是否开启
mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | OFF                                  |
| slow_query_log_file | /var/lib/mysql/1dcb5644392c-slow.log |
+---------------------+--------------------------------------+
2 rows in set (0.01 sec)

# 2、开启慢查询日志
mysql> SET GLOBAL slow_query_log = 1;
Query OK, 0 rows affected (0.00 sec)

如果要使慢查询日志永久开启，需要修改my.cnf文件，在[mysqld]下增加修改参数。

1
2
3
4
5
6
7
# my.cnf
[mysqld]
# 1.这个是开启慢查询。注意ON需要大写
slow_query_log=ON  

# 2.这个是存储慢查询的日志文件。这个文件不存在的话，需要自己创建
slow_query_log_file=/var/lib/mysql/slow.log

开启了慢查询日志后，什么样的SQL才会被记录到慢查询日志里面呢？

这个是由参数long_query_time控制的，默认情况下long_query_time的值为10秒。

MySQL中查看long_query_time的时间：SHOW VARIABLES LIKE 'long_query_time%';。

1
2
3
4
5
6
7
8
9
# 查看long_query_time 默认是10秒
# 只有SQL的执行时间>10才会被记录
mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

修改long_query_time的时间，需要在my.cnf修改配置文件

1
2
3
[mysqld]
# 这个是设置慢查询的时间，我设置的为1秒
long_query_time=1

查询慢查询日志的总记录条数：SHOW GLOBAL STATUS LIKE '%Slow_queries%';。

1
2
3
4
5
6
7
mysql> SHOW GLOBAL STATUS LIKE '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 3     |
+---------------+-------+
1 row in set (0.00 sec)

show processlist

慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。

1556098544349

1
2
3
4
5
6
7
8
1） id列，用户登录mysql时，系统分配的"connection_id"，可以使用函数connection_id()查看
2） user列，显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句
3） host列，显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户
4） db列，显示这个进程目前连接的是哪个数据库
5） command列，显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）等
6） time列，显示这个状态持续的时间，单位是秒
7） state列，显示使用当前连接的sql语句的状态，很重要的列。state描述的是语句执行中的某一个状态。一个sql语句，以查询为例，可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成
8） info列，显示这个sql语句，是判断问题语句的一个重要依据

EXPLAIN 分析执行计划

EXPLAIN：SQL 的执行计划，使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的。

通过以上步骤查询到效率低的 SQL 语句后，可以通过 EXPLAIN 或者 DESC 命令获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

查询SQL语句的执行计划 ：

1
explain select * from tb_item where id = 1;

1552487489859

1
explain select * from tb_item where title = '阿尔卡特 (OT-979) 冰川白 联通3G手机3';

1552487526919

各个字段的含义：

  • id：表的读取顺序。
  • select_type：数据读取操作的操作类型。
  • possible_keys：哪些索引可以使用。
  • key：哪些索引被实际使用。
  • ref：表之间的引用。
  • rows：每张表有多少行被优化器查询。
  • extra：额外信息

关于 EXPLAIN 指令的更多详细分析见 EXPLAIN 指令。

show profiles 分析 SQL

MySQL 从 5.0.37 版本开始增加了对 show profiles 和 show profile 语句的支持。show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

Show Profile：MySQL 提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量。默认情况下，参数处于关闭状态，并保存最近 15 次的运行结果。

1、通过 have_profiling 参数，能够看到当前 MySQL 是否支持 profile：

1552488401999

2、默认profiling是关闭的，可以通过 Set 语句在 Session 级别开启 profiling：

1552488372405

1
set profiling=1;  -- 开启profiling 开关；

3、执行一系列的操作，如下图所示：

1
2
3
4
5
6
7
8
9
show databases;

use db01;

show tables;

select * from tb_item where id < 5;

select count(*) from tb_item;

4、执行完上述命令之后，再执行show profiles 指令， 来查看SQL语句执行的耗时：

1552489017940

Duration：持续时间

5、诊断SQL：通过show profile for query query_id 语句可以查看到该SQL执行过程中每个线程的状态和消耗的时间：

1552489053763

Sending data 状态表示MySQL线程开始访问数据行并把结果返回给客户端，而不仅仅是返回个客户端。由于在Sending data状态下，MySQL线程往往需要做大量的磁盘读取操作，所以经常是整各查询中耗时最长的状态。

在获取到最消耗时间的线程状态后，MySQL支持进一步选择all、cpu、block io 、context switch、page faults等明细类型类查看MySQL在使用什么资源上耗费了过高的时间。例如，选择查看CPU的耗费时间 ：

1552489671119

字段 含义
Status sql 语句执行的状态（代表一个 SQL 的完整生命周期）
Duration sql 执行过程中每一个步骤的耗时
CPU_user 当前用户占有的cpu
CPU_system 系统占有的cpu

Show Profile查询参数备注：

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

Show Profile查询列表中若出现以下信息则说明当前 SQL 存在性能问题：

  • converting HEAP to MyISAM：查询结果太大，内存都不够用了，往磁盘上搬了。
  • Creating tmp table：创建临时表（拷贝数据到临时表，用完再删除），非常耗费数据库性能。
  • Copying to tmp table on disk：把内存中的临时表复制到磁盘，危险！！！
  • locked：死锁。

trace 分析优化器执行计划

MySQL 5.6 提供了对 SQL 的跟踪 trace，通过 trace文件能够进一步了解为什么优化器选择A计划, 而不是选择B计划。

打开 trace， 设置格式为 JSON，并设置 trace 最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。

1
2
SET optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;

执行SQL语句 ：

1
select * from tb_item where id < 4;

最后， 检查information_schema.optimizer_trace就可以知道MySQL是如何执行SQL的 ：

1
select * from information_schema.optimizer_trace\G;

SQL 优化技巧

索引优化的通用一般性建议：

  • 对于单值索引，尽量选择针对当前查询过滤性更好（辨识度更高）的索引
  • 在选择复合索引的时候，当前查询中过滤性最好的字段在索引字段顺序中，位置越靠前越好
  • 在选择复合索引的时候，尽量选择能够包含当前查询中的 WHERE 子句中更多字段的索引
  • 尽量使用覆盖索引（只访问索引的查询），避免使用 SELECT *

下面介绍针对具体语句的优化方法。

JOIN 语句优化

使用 JOIN 语句进行联表查询时，要遵循小表驱动大表原则：数据量小的表驱动数据量大的表。例如：

  • 左连接（LEFT JOIN）时，左表的数据量应该比较小，右表的数据量应该比较大，同时索引建在右表上，这样查询效率就很非常高
  • 右连接（RIGHT JOIN）时，右表的数据量应该比较小，左表的数据量应该比较大，同时索引建在左表上，这样查询效率就很非常高

案例

数据准备

1
2
3
4
5
6
7
8
9
10
11
12
DROP TABLE IF EXISTS `class`;
DROP TABLE IF EXISTS `book`;

CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
`card` INT(10) UNSIGNED NOT NULL COMMENT '分类' 
) COMMENT '商品类别';

CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
`card` INT(10) UNSIGNED NOT NULL COMMENT '分类'
) COMMENT '书籍';

两表连接查询的SQL执行计划

1、不创建索引的情况下，SQL 的执行计划。

explain

book和class两张表都是没有使用索引，全表扫描，那么如果进行优化，索引是创建在book表还是创建在class表呢？下面分别尝试：

2、左表（book表）创建索引 idx_book_card

1
2
/* 在book表创建索引 */
CREATE INDEX idx_book_card ON book(card);

在 book 表中有 idx_book_card 索引的情况下，查看 SQL 执行计划

explain

观察 rows：先遍历了左表的索引24行，然后再全表遍历右表，判断了22次

3、删除book表的索引，右表（class表）创建索引 idx_class_card

1
2
/* 在class表创建索引 */
CREATE INDEX idx_class_card ON class(card);

在 class表中有 idx_class_card 索引的情况下，查看 SQL 执行计划

explain

先在左表全表遍历24，然后用索引直接一次就定位到了目标位置

由此可见，左连接将索引创建在右表上更合适，右连接将索引创建在左表上更合适。

原因：左连接，一定会先扫描左表，左表的每一行都会要的，所以左表没必要加索引，因为加了也得全遍历一次该索引值，而右表只需要满足条件的行，所以要加索引，这样会很快定位到需要的值，回表后把数据拼装给左表满足ON条件的行上，然后左表剩余不满足ON的也要查出来，只不过其右表的值是NULL。

补充：IN和 EXISTS

1
2
3
4
5
/* IN适合B表比A表数据小的情况*/
SELECT * FROM `A` WHERE `id` IN (SELECT `id` FROM `B`)

/* EXISTS适合B表比A表数据大的情况 */
SELECT * FROM `A` WHERE EXISTS (SELECT 1 FROM `B` WHERE `B`.id = `A`.id);

本质是因为 SQL 的机读顺序，IN 的情况先执行括号内的查询，所以括号内的表要小（小表驱动大表）；而 EXISTS的情况先执行括号外的查询，所以括号外的表要小。

EXISTS：

  • 语法：SELECT....FROM tab WHERE EXISTS(subquery);该语法可以理解为：
  • 该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（true或是false）来决定主查询的数据结果是否得以保留。

提示：

  • EXISTS(subquery)子查询只返回true或者false，因此子查询中的SELECT *可以是SELECT 1 OR SELECT X，它们并没有区别。
  • EXISTS(subquery)子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担心效率问题，可进行实际检验以确定是否有效率问题。
  • EXISTS(subquery)子查询往往也可以用条件表达式，其他子查询或者JOIN替代，何种最优需要具体问题具体分析。

INSERT 语句优化

当进行数据的 insert 操作的时候，可以考虑采用以下几种优化方案。

1、添加主键，并且推荐索引使用自增数据类型

如果不设置主键，则 InnoDB 会自己寻找一个数据值都不相同的列，为其创建主键索引。如果找不到都不相同的，则会维护一个隐藏列（6字节的row id）作为主键，这无疑浪费了时间与空间。因此建议自己添加索引。

  1. 使用整型的原因：整型数据比大小比较快，占用字节也比较少（UUID这种字符串比大小比较慢，占用字节也多）
  2. 使用自增的原因：如果使用自增索引，那么使用 insert 新添加的索引只需要向当前最大索引后面添加即可，不会发生页分裂，也就不需要重新调整整棵树的结构，这样插入速度就会比较快；而如果不自增的话， 随便插入很容易出现页分裂、让树重新调整平衡，速度就会降低

2、按照索引顺序插入数据，避免无序插入

无序插入时会频繁发生页分裂，造成性能降低。

1
2
3
脚本文件介绍 :
	sql1.log  ----> 主键有序
	sql2.log  ----> 主键无序

插入有序的 ID 数据：

1555771750567

插入无序的 ID 数据：

1555771959734

3、关闭唯一性校验

MySQL 默认开启了唯一性校验，每次插入数据时都会进行校验，这样就会造成一定的性能降低，在插入大量数据时延迟尤其凸显。因此可以在插入大量数据时手动将其关闭，插入完成后再手动开启。

在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。

1555772132736

4、手动提交事务

MySQL 默认会自动开启事务，在每次插入数据前开启事务，在插入大量数据时会出现一定的延迟。因此可以在插入大量数据前先关闭事务，插入完成后再手动提交。

如果应用使用自动提交的方式，建议在插入大量数据前执行 SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行 SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。

1555772351208

5、多条 insert 语句合并成一条

如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个 insert语句快。

原始方式为：

1
2
3
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');

优化后的方案为 ：

1
insert into tb_test values(1,'Tom'),(2,'Cat')，(3,'Jerry');

6、大批量插入数据时建议分组提交，一组一组的插入提交

ORDER BY 语句优化

ORDER BY子句，尽量使用索引排序 Using index，避免使用文件排序 Using filesort。尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀原则。

MySQL 支持两种方式的排序：

  • 索引排序 Using index：MySQL 扫描索引本身即可完成排序，效率较高。需要满足条件：
    • 查询的结果是覆盖索引
    • ORDER BY 中索引满足最左前缀原则，并且顺序要和建索引时保持一致
    • 复合索引时，升序降序必须要统一，不能一个升序一个降序
  • 文件排序 Using filesort：使用了额外的文件排序，效率较差。

如果不在索引列上，MySQL 就要启动 File Sort。其实现有两种算法：双路排序算法和单路排序算法

1、双路排序算法：MySQL 4.1 之前使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和ORDER BY列，対他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。一句话，从磁盘取排序字段，在buffer中进行排序，再从磁盘取其他字段。

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

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

由于单路排序算法是后出的，总体而言效率好过双路排序算法。但是单路排序算法有问题：如果SortBuffer缓冲区太小，导致从磁盘中读取所有的列不能完全保存在SortBuffer缓冲区中，这时候单路复用算法就会出现问题，反而性能不如双路复用算法。

单路复用算法的优化策略：

  • 增大sort_buffer_size参数的设置。
  • 增大max_length_for_sort_data参数的设置。

MySQL 通过比较系统变量 max_length_for_sort_data 的大小和查询语句取出的字段总大小， 来判定是否那种排序算法，如果 max_length_for_sort_data 更大，那么使用第二种优化之后的算法；否则使用第一种。

可以适当提高 sort_buffer_size 和 max_length_for_sort_data 系统变量，来增大排序区的大小，提高排序的效率。

1556338367593

提高 ORDER BY 排序的速度：

  • ORDER BY时使用SELECT *是大忌，查什么字段就写什么字段，这点非常重要。在这里的影响是：
    • 当查询的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时，会使用单路排序算法，否则使用多路排序算法。
    • 两种排序算法的数据都有可能超出sort_buffer缓冲区的容量，超出之后，会创建tmp临时文件进行合并排序，导致多次IO，但是单路排序算法的风险会更大一些，所以要增大sort_buffer_size参数的设置。
  • 尝试提高sort_buffer_size：不管使用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的。
  • 尝试提高max_length_for_sort_data：提高这个参数，会增加用单路排序算法的概率。但是如果设置的太高，数据总容量sort_buffer_size的概率就增大，明显症状是高的磁盘IO活动和低的处理器使用率。

案例一

1556335817763

通过有序索引顺序扫描直接返回有序数据，这种情况即为Using index，不需要额外排序，操作效率高。

1556335866539

多字段排序

1556336352061

案例二

数据准备

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE `talA`(
`age` INT,
`birth` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO `talA`(`age`) VALUES(18);
INSERT INTO `talA`(`age`) VALUES(19);
INSERT INTO `talA`(`age`) VALUES(20);
INSERT INTO `talA`(`age`) VALUES(21);
INSERT INTO `talA`(`age`) VALUES(22);
INSERT INTO `talA`(`age`) VALUES(23);
INSERT INTO `talA`(`age`) VALUES(24);
INSERT INTO `talA`(`age`) VALUES(25);

/* 创建索引 */
CREATE INDEX idx_talA_age_birth ON `talA`(`age`, `birth`);

案例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
/* 1.使用索引进行排序了 不会产生Using filesort */
EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `age`;

/* 2.使用索引进行排序了 不会产生Using filesort（两个都建了索引，并且满足最左前缀原则） */
EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `age`,`birth`;

/* 3.没有使用索引进行排序 产生了Using filesort（因为没有满足最左前缀原则） */
EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `birth`;

/* 4.没有使用索引进行排序 产生了Using filesort（因为没有满足最左前缀原则） */
EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `birth`,`age`;

/* 5.没有使用索引进行排序 产生了Using filesort（因为没有满足最左前缀原则） */
EXPLAIN SELECT * FROM `talA` ORDER BY `birth`;

/* 6.没有使用索引进行排序 产生了Using filesort（因为没有满足最左前缀原则） */
EXPLAIN SELECT * FROM `talA` WHERE `birth` > '2020-08-04 07:42:21' ORDER BY `birth`;

/* 7.使用索引进行排序了 不会产生Using filesort（因为 birth 和 age 都建了索引） */
EXPLAIN SELECT * FROM `talA` WHERE `birth` > '2020-08-04 07:42:21' ORDER BY `age`;

/* 8.没有使用索引进行排序 产生了Using filesort（一个升序一个降序会失效） */
EXPLAIN SELECT * FROM `talA` ORDER BY `age` ASC, `birth` DESC;

ORDER BY 各种场景总结：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
/* 创建a b c三个字段的索引 */
idx_table_a_b_c(a, b, c)

/* 1.ORDER BY 能使用索引最左前缀 */
ORDER BY a;
ORDER BY a, b;
ORDER BY a, b, c;
ORDER BY a DESC, b DESC, c DESC;

/* 2.如果WHERE子句中使用索引的最左前缀定义为常量，则ORDER BY能使用索引 */
WHERE a = 'Ringo' ORDER BY b, c;
WHERE a = 'Ringo' AND b = 'Tangs' ORDER BY c;
WHERE a = 'Ringo' AND b > 2000 ORDER BY b, c;

/* 3.不能使用索引进行排序 */
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;  /* 对于排序来说，多个相等条件(a=1 or a=2)也是范围查询 */

GROUP BY 语句优化

GROUP BY 可能出现 Using temporary 和 Using filesort

由于 GROUP BY 实际上也同样会进行排序操作，而且与 ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在 GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引，按照 ORDER BY 语句优化的思路优化 GROUP BY。

GROUP BY 优化技巧：

  • GROUP BY 实质是先排序后进行分组，因此 ORDER BY 语句的优化思路也可以用在这里。例如：
    • 尽量在索引列上进行分组，使用覆盖索引
  • 手动禁用排序：如果查询结果不需要排序的话，可以在 GROUP BY 后添加 ORDER BY NULL
  • 当无法使用索引列时，会使用Using filesort进行排序，增大max_length_for_sort_data参数的设置和增大sort_buffer_size参数的设置，会提高性能
  • WHERE执行顺序高于HAVING，能写在WHERE限定条件里的就不要写在HAVING中

案例

优化前（删除索引后分组查询。默认会排序）：

1
2
3
drop index idx_emp_age_salary on emp;

explain select age,count(*) from emp group by age;

1556339573979

初优化（取消排序，但不添加索引，此时会出现临时表进行分组）：

1
explain select age,count(*) from emp group by age order by null;

1556339633161

最终优化（创建索引后再取消排序分组） ：

1
create index idx_emp_age_salary on emp(age,salary)；

1556339688158

嵌套子查询优化

MySQL 4.1 版本之后，开始支持SQL的子查询。这个技术可以使用 SELECT 语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性完成很多逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询是可以被更高效的连接查询（JOIN）替代。

连接查询之所以更有效率一些 ，是因为 MySQL 不需要在内存中创建临时表来完成查询。

示例：查找有角色的所有的用户信息 :

1
explain select * from t_user where id in (select user_id from user_role );

执行计划为 :

1556359399199

优化后 :

1
explain select * from t_user u , user_role ur where u.id = ur.user_id;

1556359482142

观察 rows：明显查询行数更少

OR 语句优化

对于包含 OR 的查询子句，如果要利用索引，则 OR 之间的每个条件列都必须用到单列索引， 而且不能使用到复合索引； 如果没有索引，则应该考虑增加索引。

建议使用 UNION替换 OR。

获取 emp 表中的所有的索引 ：

1556354464657

即使是复合索引，OR 也不走索引，除非左右都是单列索引，复合索引是不会分别为索引创建单列索引的

示例 ：

1
explain select * from emp where id = 1 or age = 30;

1556354887509

1556354920964

使用 UNION替换 OR：

1556355027728

我们来比较下重要指标，发现主要差别是 type 和 ref 这两项：

type 显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：

1
system > const > eq_ref > ref > fulltext > ref_or_null  > index_merge > unique_subquery > index_subquery > range > index > ALL
  • UNION 语句的 type 值为 ref，OR 语句的 type 值为 range，可以看到这是一个很明显的差距
  • UNION 语句的 ref 值为 const，OR 语句的 type 值为 null，const 表示是常量值引用，非常快

这两项的差距就说明了 UNION 要优于 OR 。

LIMIT 语句优化

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

1556361314783

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

1556416102800

2、可以把 Limit 查询转换成某个位置的查询（该方案适用于主键自增的表）。

1556363928151

建立唯一索引

如果可以的话，尽量建立唯一索引，使得查询都是 const 级别，区分度越大，查询效率越高。

使用 SQL 提示

SQL 提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

USE INDEX

在查询语句中表名的后面，添加 use index 来提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引。

1
create index idx_seller_name on tb_seller(name);

1556370971576

IGNORE INDEX

如果用户只是单纯的想让 MySQL 忽略一个或者多个索引，则可以使用 ignore index 作为 hint 。

1
explain select * from tb_seller ignore index(idx_seller_name) where name = '小米科技';

1556371004594

FORCE INDEX

为强制 MySQL 使用一个特定的索引，可在查询中使用 force index 作为 hint 。

1
create index idx_seller_address on tb_seller(address);

1556371355788

索引相关杂项

前缀索引

在对 varchar 类型的字段添加索引时，如果字段长度过长，那么索引长度就会比较长，那么同样大小的页结构中存储的索引数量就会降低，这样会导致一棵树上存储的索引个数降低，这显然是不利于快速查询。

因此可以对 varchar 类型的字段添加前缀索引，即只对前几个字符创建索引，从而增加每一页中索引的个数，提高查询效率。

添加前缀索引的方法：

1
CREATE INDEX emp_ename_index ON emp(ename(3));

这样只对 ename 字段的前三个字符创建索引。

索引条件下推 ICP

索引条件下推（ICP）只针对二级索引而言。当 WHERE 条件里的字段恰好有一些建立了复合索引时（可能 WHERE 的三个条件里有两个建立了复合索引，另一个没有），就可以在二级索引树上就会先根据 WHERE 条件进行过滤，将剩下的少量匹配数据再进行回表，查询出其他数据。如果是纯复合索引，则不需要再回表了。

其条件下推指的是，原本需要根据某一个索引定位到匹配的数据后进行回表，在回表查询到的大量数据里再进行条件过滤，得到符合另外两个字段的记录。但当有两个字段建立了复合索引，则就会在二级索引树上先进行过滤（条件下推到了二级索引树上），然后将匹配到的少量数据再进行回表，就能减少回表次数，提高效率。

FIC

FIC：Fast Index Creation。FIC 作用：可以让Innodb存储引擎避免创建临时表，提高索引创建效率。

在对索引字段进行增删改操作时，整个索引树的结构都需要进行调整（可能发生页分裂等过程），调整过程首先要保证是线程安全的，而且要尽可能的快速。那么如何安全且快速进行调整呢？

对于之前的版本，索引的添加或删除这类DDL操作，MySQL数据库的操作过程为如下：

  • 首先创建新的临时表，表结构通过命令ALTAR TABLE新定义的结构
  • 然后把原表中数据导入到临时表
  • 删除原表
  • 最后把临时表重命名为原来的表名

上述过程我们不难发现，若我们对一张大表进行索引的添加或者删除，需要很长的时间，致命的是若有大量的访问请求，意味着无法提供服务。

Innodb存储引擎从1.0.x版本开始支持Fast index Creation（快速索引创建），简称FIC。对于辅助索引的创建，会对创建索引的表加一个S锁（共享锁）。在创建的过程中，不需要重建表，因此速度有明显提升。对于删除辅助索引Innodb存储引擎只需要更新内部视图，并将辅助索引的空间标记为可用，同时删除MySQL数据库内部视图上对该表的索引定义即可。特别需要注意的时，临时表的创建路径是通过参数tmpdir设置的。必须确保tmpdir有足够的空间，否则将会导致辅助索引创建失败。

由于在创建辅助索引时加的是S锁，所以在这过程中只能对该表进行读操作，若有事务需要对该表进行写操作，那么数据库服务同样不可用。

需要注意的是，FIC方式只限定于辅助索引，对于主键的创建和删除同样需要重建一张表。

索引最佳实践

为什么千万级别数据量下 MySQL 可以快速查找到目标？

因为 MySQL 巧妙地运用了 B+ 树的优点，让数据量的增长和树的高度不成正比（拉宽了整棵树的宽度），哪怕是千万级的数据量，整棵树的高度也可以不超过5层，从而极大地提高了查询效率（只需要遍历不超过5次即可找到目标）

为什么 InnoDB 表必须有主键，且推荐索引使用自增整数类型？

如果不设置主键，则 InnoDB 会自己寻找一个数据值都不相同的列，为其创建主键索引。如果找不到都不相同的，则会维护一个隐藏列作为主键，这无疑浪费了时间与空间。因此建议自己添加索引。

  1. 使用整型的原因：整型数据比大小比较快，占用字节也比较少（UUID这种字符串比大小比较慢，占用字节也多）
  2. 使用自增的原因：如果使用自增索引，那么使用 insert 新添加的索引只需要向当前最大索引后面添加即可，不会发生页分裂，也就不需要重新调整整棵树的结构，这样插入速度就会比较快；而如果不自增的话， 随便插入很容易出现页分裂、让树重新调整平衡，速度就会降低

分布式数据库中不推荐自增索引，因为：待补充

其需要用到雪花算法做到全局UUID：待补充

尽量使用覆盖索引以减少回表，提高性能

覆盖索引：只访问索引的查询，索引列和查询列一致

使用覆盖索引时不需要进行回表查询，能够提高性能。减少使用SELECT *，这样肯定会进行回表。

更多 MySQL 内容，复习时见视频：https://www.bilibili.com/video/BV1xh411Z79d?spm_id_from=333.1007.top_right_bar_window_custom_collection.content.click