MySQL学习笔记5-调优篇

公号：码农充电站pro

主页：https://codeshellme.github.io

这 5 篇文章是我在学习 MySQL 的过程中，总结的笔记：

• 第一篇 MySQL 学习笔记1-基础篇
  • 1，关于 SQL
  • 2，一条 SQL 的执行步骤
  • 3，MySQL 存储引擎
  • 4，数据库的基本操作
  • 5，关于自增主键
  • 6，SELECT 语句顺序
  • 7，WHERE 子句
  • 8，DISTINCT 去重
  • 9，关于 COUNT(*) 操作
  • 10，MYSQL 函数
  • 11，GROUP BY 数据分组
  • 12，子查询(嵌套查询)
  • 13，JOIN 连接查询
  • 14，VIEW 视图
  • 15，存储过程
  • 16，临时表
  • 17，MySQL 权限管理
  • 18，Python 操作 MySQL 的库
• 第二篇 MySQL 学习笔记2-进阶篇-上
  • 19，MySQL 的基础架构
  • 20，数据库缓冲池
  • 21，数据库中的存储结构
  • 22，InnoDB 中表数据的存储
• 第三篇 MySQL 学习笔记3-进阶篇-中
  • 23，事务处理
  • 24，事务的隔离级别
  • 25，MySQL 中的锁
  • 26，MVCC 多版本并发控制
  • 27，MySQL 传输数据的原理
• 第四篇 MySQL 学习笔记4-进阶篇-下
  • 28，Join 语句的原理
  • 29，MySQL 如何进行排序
  • 30，MySQL 中 kill 命令的原理
  • 31，MySQL 中的 mysqldump 命令
  • 32，MySQL 主从同步
  • 33，MySQL 主备原理
• 第五篇 MySQL 学习笔记5-调优篇
  • 34，关于 MySQL 索引
  • 35，定位数据库 SQL 性能问题

34，关于 MySQL 索引

索引的本质目的是快速定位想要查找的数据。

34.1，MySQL 索引的种类

MySQL 中的索引分为：

• 普通索引：没有任何约束，主要用于提高查询效率。
• 唯一索引：在普通索引的基础上增加了数据唯一性的约束，一张数据表里可以有多个唯一索引。
• 主键索引：在唯一索引的基础上增加了不为空的约束，也就是 NOT NULL+UNIQUE，一张表里最多只有一个主键索引。
  • 与主键索引相对应的是非主键索引（又叫二级索引），在使用非主键索引查询数据时，会涉及到回表，因此比主键索引略慢。
  • 主键索引又叫聚集索引，非主键索引又叫非聚集索引。
    • 聚集索引：叶子节点包含了行中的所有数据。
    • 非聚集索引：叶子节点只包含了行 id，不包含其它数据，只有通过行 id 去回表才能查到其它数据。
• 全文索引：使用的不多，MySQL 自带的全文索引只支持英文。
  • 通常可以采用专门的全文搜索引擎，比如 ElasticSearch。

InnoDB 中的数据保存在主键索引上，所以，对于全表扫描(select * from t;)，是直接扫描表 t 的主键索引。

34.2，普通索引与唯一索引的选择

这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响，建议你尽量选择普通索引，因为普通索引可以利用change buffer，而唯一索引不能（具体见 24.5 节 change buffer）。

一个即有主键索引，又有非主键索引的例子：

create table T( id int primary key, k int not null, name varchar(16), index (k) ) engine=InnoDB; # 表中 R1~R5 的 (ID,k) 值分别为 # (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)

其索引结构如下：

由图可知，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

以下 SQL 分别使用主键索引和非主键索引：

• select * from T where ID=500 主键索引
• select * from T where k=5 非主键索引

34.3，单一索引与联合索引

• 单一索引：索引列为一列；
• 联合索引：多个列组合在一起创建的索引。
  • 创建联合索引时，需要注意索引的顺序，因为联合索引 (x, y, z) 和 (z, y, x) 在使用的时候效率可能会存在差别。
  • 联合索引存在最左匹配原则，就是当查询条件中有多个列时，必须按照索引的创建顺序，从左到右，并且不能跳过某一列。
    • 比如联合索引 (x, y, z)，如果查询条件是 WHERE x=1 AND y=2 AND z=3，就可以匹配上联合索引；
      • WHERE x=1 和 WHERE x=1 AND y=2 也可以使用到索引
    • 如果查询条件是 WHERE y=2，就无法匹配上联合索引（因为跳过了 x）。
      • WHERE y=2 AND z=3 和 WHERE z=3 也无法使用索引
    • 使用一部分（非全部）索引的情况 （可观察 explain 结果中的 key_len）
      • WHERE x=1 AND z=3 （因为跳过了 y）
      • WHERE x=1 AND y>2 AND z=3（因为 y 的条件是范围，所以此时只会用到索引的 x 和 y，而不会用到 z）
    • SQL条件语句中的字段顺序不重要，因为在逻辑查询优化阶段会自动进行查询重写

一份练习题：

创建联合索引的例子：

CREATE TABLE `tuser` ( `id` int(11) NOT NULL, `id_card` varchar(32) DEFAULT NULL, `name` varchar(32) DEFAULT NULL, `age` int(11) DEFAULT NULL, `ismale` tinyint(1) DEFAULT NULL, PRIMARY KEY (`id`), # 主键索引 KEY `id_card` (`id_card`), # 普通索引 KEY `name_age` (`name`,`age`) # 联合索引 ) ENGINE=InnoDB

联合索引的结构图如下：

对于 SQL where name like ‘张 %’" 也可以使用到最左前缀原则。

关于联合索引的索引顺序

因为可以支持最左前缀，所以当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引了。因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

那么，如果既有联合查询，又有基于 a、b 各自的查询呢？查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护 (a,b)、(b) 这两个索引。

34.4，关于索引下推

索引下推的机制是为了减少回表次数，从而提高查询效率。

对于 SQL 语句：

select * from tuser where name like '张%' and age=10 and ismale=1;

在 MySQL 5.6 之前，只能从 ID3 开始一个个回表。到主键索引上找出数据行，再对比字段值。

下图中，在 (name,age) 索引里面我特意去掉了 age 的值**，这个过程 InnoDB 并不会去看 age 的值**，只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此，需要回表 4 次。

而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

在下图中，InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。在这个例子中，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次。

34.5，关于索引覆盖

索引的实际数据存储在 B+ 树的叶子节点上：

• 唯一索引的叶子节点存储了行的所有数据，任何时候都不需要回表操作。
• 普通索引的叶子节点只存储了行 id 和相应的索引列，如果需要别的列数据，则需要回表。

索引覆盖指的是，普通索引的叶子节点存储了我们所需要的所有（列）数据，从而不需要进行回表，加快了查询速度。

如果要想使用到索引覆盖，尽量不要使用 select *，而只 select 索引中的字段。

34.6，什么时候使用索引

不需要创建索引的情况：

• 表中的数据比较少的情况下，比如不到 1000 行；
• 表中的数据重复度大，比如高于 10% 的时候。

34.7，如何创建索引

参照这里。

MySQL 5.6 版本以后，创建索引都支持 Online DDL

34.8，如何设计索引

• 首先一张表的索引个数不宜过多，否则一条记录的增加和修改，会因为过多的索引造成额外的负担。
  • 针对这个情况，当你需要新建索引的时候，首先考虑在原有的索引片上增加索引，也就是采用复合索引的方式，而不是新建一个新的索引。
  • 另外，我们可以定期检查索引的使用情况，对于很少使用到的索引可以及时删除，从而减少索引数量。
• 其次，在索引片中，也需要控制索引列的数量，通常情况下我们将 WHERE 里的条件列添加到索引中，而 SELECT 中的非条件列则不需要添加。除非 SELECT 中的非条件列数少，并且该字段会经常使用到。
• 另外，单列索引和复合索引的长度也需要控制，在 MySQL InnoDB 中，系统默认单个索引长度最大为 767 bytes，如果单列索引长度超过了这个限制，就会取前缀索引，也就是取前 255 字符。这实际上也是告诉我们，字符列会占用较大的空间，在数据表设计的时候，尽量采用数值类型替代字符类型，尽量避免用字符类型做主键，同时针对字符字段最好只建前缀索引。

34.9，为字符串数据设计索引（前缀索引）

MySQL 支持前缀索引，即你可以定义字符串的一部分作为索引，如果创建索引时不指定前缀长度，那么索引就会包含整个字符串。

例如下面两个 SQL：

# index1 索引中，包含了每个记录的整个字符串 alter table table_name add index index1(email); # index2 索引中，对于每个记录都是只取前 6 个字节 alter table table_name add index index2(email(6));

index1 与 index2 的结构如下：

index1 与 index2 的区别：

• index1：占用空间更大，性能更好
• index2：占用空间较小，性能较低，性能低的原因是：
  • 每次在索引上定位到数据之后，还需要回表去判断字符串是否相等
  • 前缀索引也无法利用索引覆盖的特性

在使用前缀索引是，要确定适当的长度，才能确保即节省空间，又不至于太多的性能损失。

34.10，索引的底层数据结构

MySQL 的索引存储在磁盘（速度很慢）上，读取索引时与磁盘的交互越少（重点在于将树的高度降低），性能也就越高。

1，关于平衡二叉树

平衡二叉树不适合作为 MySQL 索引，主要是由于树的深度比较深，从而磁盘交互就比较多。常见的平衡二叉树有：

• 平衡二叉搜索树：查询时间复杂度O(log2n)，树的深度为 O(log2n)

2，关于 B 树

B 树是平衡的多叉树，它的高度远小于平衡二叉树。B 树的一个节点可以存储 M 个子节点，M 成为 B 树的阶。在文件系统和数据库系统中的索引结构经常采用 B 树来实现。

3，关于 B+ 树

B+ 树是B 树的改进版，通常用在数据库中，InnoDB 使用的就是 B+ 树。

B+ 树的优点：

• B+ 树查询效率更稳定。因为 B+ 树每次只有访问到叶子节点才能找到对应的数据，而在 B 树中，非叶子节点也会存储数据，这样就会造成查询效率不稳定的情况，有时候访问到了非叶子节点就可以找到关键字，而有时需要访问到叶子节点才能找到关键字。
• B+ 树的查询效率更高，这是因为通常 B+ 树比 B 树更矮胖（阶数更大，深度更低），查询所需要的磁盘 I/O 也会更少。同样的磁盘页大小，B+ 树可以存储更多的节点关键字。
• 在查询范围上，B+ 树的效率也比 B 树高。这是因为所有关键字都出现在 B+ 树的叶子节点中，并通过有序链表进行了链接。而在 B 树中则需要通过中序遍历才能完成查询范围的查找，效率要低很多。

4，关于 Hash 索引

Hash 索引与B+ 树的区别：

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

34.11，MySQL 如何使用索引查询数据

一般我们在写 SQL 语句的时候，并没有指定使用哪个索引，使用哪个索引是由 MySQL 自身（优化器）来确定的。

不过我们也可以使用 force 关键字来告诉 MySQL 使用我们指定的索引去查询数据：

# 告诉 MySQL 使用索引 a 来查询数据 select * from t force index(a) where a between 10000 and 20000;

优化器如何选择索引

优化器会根据扫描行数，是否使用临时表，是否排序，是否要回表等因素进行综合判断。

优化器在执行 SQL 之前并不能准确的知道扫描行数，而只能根据统计信息来估算记录数。

可以使用 explain 命令查看一个 SQL 的预估扫描行数（其中 rows 预估扫描行数）：

这个统计信息就是索引的“区分度”（一个索引上不同的值越多，这个索引的区分度就越好）。一个索引上不同的值的个数，我们称之为“基数”（cardinality），这个基数越大，索引的区分度越好。

命令 show index from table_name 可以查看一个表的索引基数（但不一定准确）：

MySQL 通过采样统计来的到索引基数。

重建索引信息

MySQL 有时候会判断错误扫描行数，当你认为 MySQL 判断错误时（大多数时候MySQL是不会选择错误的），可以使用 analyze table table_name 命令来修正索引信息：

34.12，什么是回表

回表是指，InnoDB 在普通索引 a 上查到主键 id 的值后，再根据一个个主键 id 的值到主键索引上去查整行数据的过程。

那么，回表过程是一行行的查数据，还是批量的查数据？

以下面语句为例：

# 字段 a 上有索引 select * from t1 where a>=1 and a<=100;

默认的查询过程是这样的：主键索引是一棵 B+ 树，在这棵树上，每次只能根据一个主键 id 查到一行数据。因此，回表是一行行搜索主键索引的。

流程图如下：

随着 a 的值递增顺序查询的话，id 的值就变成随机的，那么就会出现随机访问，性能相对较差。虽然“按行查”这个机制不能改，但是调整查询的顺序，还是能够加速的。

因为大多数的数据都是按照主键递增顺序插入得到的，所以，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。这就是 MRR 优化的设计思路，MRR 即 Multi-Range Read，主要目的是尽量使用顺序读盘。

MRR 优化后的执行流程：

• 根据索引 a，定位到满足条件的记录，将 id 值放入 read_rnd_buffer 中 ;
• 将 read_rnd_buffer 中的 id 进行递增排序；
• 排序后的 id 数组，依次到主键 id 索引中查记录，并作为结果返回。

read_rnd_buffer 的大小由 read_rnd_buffer_size 参数控制。如果步骤 1 中，read_rnd_buffer 放满了，就会先执行完步骤 2 和 3，然后清空 read_rnd_buffer。之后继续找索引 a 的下个记录，并继续循环。

注意：如果想要稳定地使用 MRR 算法，需要设置set optimizer_switch="mrr_cost_based=off"。（官方文档的说法是：现在的优化器策略，判断消耗的时候，会更倾向于不使用 MRR，把 mrr_cost_based 设置为 off，就是固定使用 MRR ）

使用了 MRR 优化后的执行流程和 explain 结果：

explain：

可以看到 Extra 字段多了 Using MRR，表示用上了 MRR 优化。

MRR 能够提升性能的核心在于，这条查询语句在索引 a 上做的是一个范围查询，可以得到足够多的主键 id。这样通过排序以后，再去主键索引查数据，能体现出“顺序性”的优势。

34.13，索引失效的几种情况

以下几种 where 子句会导致索引失效：

• 在 where 子句中对字段做函数处理或数据类型转换或表达式计算。比如：
  • select * from table_name where month(time_col) = 3
  • select * from table_name where id + 1 > 10
  • 注意：当主键是整数类型条件是字符串时，会走索引，比如：
  • select * from member WHERE id = 90000 与
  • select * from member WHERE id = '90000' 是一样的（前提 id 是数字类型）。
• 在 where 子句中对字段使用<>，!=，或进行 NULL 判断（包括 IS NULL, IS NOT NULL）。
• 在 where 子句中使用 IN 或 NOT IN。
• 在 where 子句中使用 like 语句，且以 % 开头。
• 在 where 子句中使用到字符串类型字段时，没有加单引号''
  • 这种情况会导致数据类型转换
  • 比如：select * from member WHERE name = 90000（name 是字符串类型）
• 在 where 子句中，如果在 OR 前的条件列进行了索引，而在 OR 后的条件列没有进行索引，那么索引会失效。
  • 只要用到 OR 就会导致索引失效

35，定位数据库 SQL 性能问题

35.1，数据库优化步骤

35.2，慢查询

查看慢查询是否已经开启：

mysql > show variables like '%slow_query_log';

打开慢查询：

mysql > set global slow_query_log='ON';

注意：这种设置只对当前数据库生效，数据库重启后则会失效。若想永久生效则需在配置文件中设置。

在配置文件中设置，可永久生效：

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

mysql > show variables like '%slow_query_log%';

查看慢查询的时间阈值（单位秒，默认是10）：

mysql > show variables like '%long_query_time%';

注意：判断条件是大于该时间阈值，而不是大于等于

设置慢查询时间阈值：

mysql > set global long_query_time = 3;

如果在设置值后，查询发现没有改变，可以关闭当前连接，重新建立与 MySQL 的连接，再次查询。 或者使用 show global variables like '%long_query_time%'

如果将 long_query_time 的值设置为 0，那么所有的查询语句都将被记录在慢查询日志中。

一般情况下，生产环境会设置为 1 秒。

以下命令可以查看当前系统中慢SQL的条数：

> show global status like '%slow_queries%'

关于慢查询，还可以参考这里。

35.3，分析慢查询日志

使用 MySQL 自带的 mysqldumpslow 工具统计慢查询日志（这个工具是个 Perl 脚本，需要先安装好 Perl）。

mysqldumpslow 命令的具体参数如下：

• -s：采用 order 排序的方式，排序方式可以有以下几种。分别是：
  • c（访问次数）
  • t（查询时间）
  • l（锁定时间）
  • r（返回记录）
  • ac（平均查询次数）
  • al（平均锁定时间）
  • ar（平均返回记录数）
  • at（平均查询时间），为默认排序方式。
• -t：返回前 N 条数据 。
• -g：后面可以是正则表达式，对大小写不敏感。

比如要按照查询时间排序，查看前两条 SQL 语句：

perl mysqldumpslow.pl -s t -t 2 "C:\ProgramData\MySQL\MySQL Server 8.0\Data\DESKTOP-4BK02RP-slow.log"

35.4，EXPLAIN 命令

关于 Explain 命令的官方地址：https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

当定位到了查询慢的 SQL 之后，就可以使用 EXPLAIN 工具做针对性的分析：

EXPLAIN SELECT comment_id, product_id, comment_text, product_comment.user_id, user_name FROM product_comment JOIN user on product_comment.user_id = user.user_id

结果如下：

每列的含义如下：

• id：每个select子句的标识id，描述表的加载顺序
  • id 相同时，执行顺序由上到下
  • id 不同时，id 值越大，越先执行
• select_type：select 语句的类型，常见类型：
  • SIMPLE：简单查询（查询中不包含子查询或UNION）
  • PRIMARY：主查询（最外层查询-最后加载），查询中包含子查询，那么最外层查询被标记为 PRIMARY
  • SUBQUERY：子查询
  • DERIVED：衍生临时表
  • UNION：UNION 之后的 select 被标记为 UNION
  • UNION RESULT：多个 UNION 合并的结果
• table：当前使用的表名
• partitions：显示查询将访问的分区，如果你的查询是基于分区表
• type：当前表的访问方式
• possible_keys：有可能被使用到的索引（理论上要用到的索引）
• key：经过优化器评估最终使用的索引（实际上用到的索引，从 possible_keys 中选出）
  • 如果值为 NULL 表示没有使用索引
  • 如果值为 PRIMARY 表示使用了主键索引
• key_len：使用到的索引长度，值越小，表示性能越好
• ref：引用到的上一个表的列
• rows：要扫描的行数
• filtered：表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数
• Extra：额外的信息说明

数据表的访问类型所对应的 type 列是比较关键的信息，type 可能有以下取值：

在这些情况里：

• all 是最坏的情况，表示全表扫描。
• index 和 all 差不多，只不过 index 对索引表进行全扫描，这样做的好处是不再需要对数据进行排序，但是开销依然很大。
  • 例如：select id from t;
  • 如果我们在 Extral 列中看到 Using index，说明采用了索引覆盖，也就是索引可以覆盖所需的 SELECT 字段，就不需要进行回表，这样就减少了数据查找的开销。
• range 表示索引范围扫描。
• index_merge 说明查询同时使用了两个或以上的索引，最后取了交集或者并集。
• ref 表示非唯一索引，或者是唯一索引的非唯一性前缀。
• eq_ref 表示使用主键或唯一索引时产生的访问方式，通常使用在多表联查中。
• const 表示使用了主键或者唯一索引（所有的部分）与常量值进行比较。
  • 例如：select * from t where id = 1;
  • 需要说明的是：const 类型和 eq_ref 都使用了主键或唯一索引，不过这两个类型有所区别，const 是与常量进行比较，查询效率会更快，而 eq_ref 通常用于多表联查中。
• system 一般用于 MyISAM 或 Memory 表，属于 const 类型的特例，当表只有一行时连接类型为 system。

除了 all 类型外，其他类型都可以使用到索引，但不同的连接方式的效率也会有所不同，效率从低到高依次为：

• all < index < range < index_merge < ref < eq_ref < const/system。

我们在查看执行计划的时候，通常希望执行计划至少可以使用到 range 级别以上的连接方式，如果只使用到了 all 或者 index 连接方式，我们可以从 SQL 语句和索引设计的角度上进行改进。

Extra 字段的含义

我们从 Extra 字段中的内容，也可以获取很多有用的信息。

Extra 字段的常用取值及含义：

• Using where：表示使用了过滤（SQL中有 where 子句）
• Using filesort：表示使用了文件排序（不利于性能）
• Using index：表示使用了索引覆盖（有利于性能）
• Using index condition：表示使用了普通索引（二级索引）过滤
• Using temporary：表示查询过程中使用到了临时表（不利于性能）
  • 常见于 order by 和 group by
• Impossible WHERE：表示 where 子句的条件始终为 false
• Impossible HAVING：表示 having 子句的条件始终为 false

说明： 如果 where 子句中是普通字段（非索引字段）的过滤，这种过滤是在 server 层完成的，也就是 Using where（主键id 有些特殊）； 如果 where 子句中是普通索引（二级索引）的过滤，这种过滤是在 engine 层完成的，也就是 Using index condition。

35.5，SHOW PROFILE 命令

SHOW PROFILE 相比 EXPLAIN 能看到更进一步的执行解析，包括 SQL 都做了什么、所花费的时间等。

默认情况下，profiling 是关闭的：

mysql > show variables like 'profiling';

开启命令如下：

mysql > set profiling = 'ON';

查看下当前会话都有哪些 profiles：

mysql > show profiles;

查看上一个查询的开销：

mysql > show profile; # 也可以查看指定的 Query ID 的开销，比如： # `show profile for query 2` # 查询结果是一样的

命令 > show profile cpu,block io for query Query_ID号 可以查看更详细的信息，下面这些提示都是不利因素：

注意：SHOW PROFILE 命令将被弃用，我们可以从 information_schema 中的 profiling 数据表进行查看。