数据库调优思考步骤

想要调优，那么久得先进行分析，哪些可以调？怎么调？
其总体分为观察（Show status） 和 行动（Action） 两个部分。字母 S 的部分代表观察（会使用相应的分析工具），字母 A 代表的部分是行动（对应分析可以采取的行动）。

不同的调优取得的效果与成本：

性能分析

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

1、开启慢查询（实际工作中定位慢查询应该是由dba管）

//在linux或者可视化工具执行都可以
查询是否开启慢查询
show variables like "%slow%";

设置开启慢查询日志
set global slow_query_log='ON';

2、修改long_query_time阈值
默认的时间是10s

//查看
show variables like '%long_query_time%';

//修改为1s，个人实践就不要设置太长了
//这是设置当前会话的
set long_query_time=1;
//全局（设置全局对当前会话无效）
set global long_query_time = 1;

3、查询当前系统中有多少条慢查询记录

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

这里我自己查了两次，所以有两条记录

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

这里有个问题：因为我这个设备没有装虚拟机，演示不了虚拟机的命令操作，所以只用Windows的mysql命令行窗口操作；如果我执行这个命令：

mysqldumpslow --help

解决办法：https://www.cnblogs.com/qcfeng/p/5959685.html

mysqldumpslow 命令的具体参数如下：
-a: 不将数字抽象成N，字符串抽象成S
-s: 是表示按照何种方式排序：
c: 访问次数
l: 锁定时间
r: 返回记录
t: 查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间 （默认方式）
ac:平均查询次数
-t: 即为返回前面多少条的数据；
-g: 后边搭配一个正则匹配模式，大小写不敏感的；

window上必须写绝对路径,比如 C:\LAPTOP-CFOFME7Q-slow.log

如果你的MySQL是默认安装的，就是不是自己指定安装目录的，日志文件会在C:\ProgramData\MySQL\MySQL Server 8.0\Data下。如果你运行加上这个的绝对路径可能会报错。

解决：将慢查询日志文件复制出来，例如我就复制在了C盘下

按照查询时间排序，查看前五条 SQL 语句
mysqldumpslow.pl -s t -t 5 C:\LAPTOP-CFOFME7Q-slow.log

参考：

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

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

1、查看是否开启SHOW PROFILE

show variables like 'profiling';

2、开启 show profile

set profiling = 'ON';

3、查看当前会话都有哪些 profiles

show profiles;

查看最近一次查询的开销

show profile;

这个在可视化工具上的剖析看也是一样的，只是会话不同，结果会不同：

show profile的常用查询参数：
① ALL：显示所有的开销信息。 
② BLOCK IO：显示块IO开销。 
③ CONTEXT SWITCHES：上下文切换开销。 
④ CPU：显示CPU开销信息。 
⑤ IPC：显示发送和接收开销信息。 
⑥ MEMORY：显示内存开销信息。 
⑦ PAGE FAULTS：显示页面错误开销信息。 
⑧ SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息。 
⑨ SWAPS：显示交换次数开销信息。

eg：show profile cpu,block io for query 2;

3、分析查询语句：EXPLAIN

1、查看某个查询的执行计划的话，可以在具体的查询语句前边加一个 EXPLAIN

EXPLAIN SELECT key1 FROM `db1` WHERE key1 = "f3e43d7b-78a5-aafe-7b25-e07971ff2877"

各个列的作用

执行计划输出中各列详解

1、table

不论查询语句有多复杂，里边儿包含了多少个表，到最后也是需要对每个表进行单表访问的，所以EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名。

2、id

查询语句中每出现一个 SELECT 关键字，就会为它分配一个唯一的 id 值。这个 id 值就是EXPLAIN 语句的第一个列。
1、一个 SELECT 关键字后边的 FROM 子句中可以跟随多个表，所以在连接查询的执行计划中，每个表都会对应一条记录，但是这些记录的id值都是相同的，如上图。
2、又：在连接查询的执行计划中，每个表都会对应一条记录，这些记录的id列的值是相同的，出现在前边的表表示驱动表，出现在后边的表表示被驱动表。如上图：db3是驱动表，db2作为被驱动表。

-----------------------------分割线--------------------------------------------------

3、对于包含子查询的查询语句来说，就可能涉及多个 SELECT 关键字，所以在包含子查询的查询语句的执行计划
中，每个 SELECT 关键字都会对应一个唯一的 id 值

4、查询优化器可能对涉及子查询的查询语句进行重写，从而转换为连接查询。所以如果我们想知道查询优化器对某个包含子查询的语句是否进行了重写，直接查看执行计划就好了

查询优化器将子查询转换为了连接查询

5、一些特别的情况

UNION 子句会把多个查询的结果集合并起来并对结果集中的记录进行去重。 MySQL去重使用的是内部的临时表。正如上边的查询计划中所示， UNION 子句是为了把 id 为 1 的查询和 id 为 2 的查询的结果集合并起来并去重，所以在内部创建了一个名为 <union1, 2> 的临时表（就是执行计划第三条记录的 table列的名称）， id 为 NULL 表明这个临时表是为了合并两个查询的结果集而创建的。

跟 UNION 对比起来， UNION ALL 就不需要为最终的结果集进行去重，它只是单纯的把多个查询的结果集中的记录合并成一个并返回给用户，所以也就不需要使用临时表。所以在包含 UNION ALL 子句的查询的执行计划中，就没有那个 id 为 NULL 的记录，如下所示

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

3、select_type

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

英文看不懂没事，我也看不懂。。。。

1、SIMPLE

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

2、PRIMARY

对于包含 UNION 、 UNION ALL 或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询
的 select_type 值就是 PRIMARY。

3、UNION

对于包含 UNION 或者 UNION ALL 的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询以
外，其余的小查询的 select_type 值就是 UNION ，可以对比上一个例子的效果

4、UNION RESULT

MySQL 选择使用临时表来完成 UNION 查询的去重工作，针对该临时表的查询的 select_type 就是 UNION
RESULT

5、SUBQUERY

如果包含子查询的查询语句不能够转为对应的 （半连接）semi-join 的形式，并且该子查询是不相关子查询，并且查询优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个 SELECT 关键字代表的那个查询的 select_type 就是 SUBQUERY

由于select_type为SUBQUERY的子查询由于会被物化，所以只需要执行一遍。

半连接的点下篇再补充

6、DEPENDENT SUBQUERY

如果包含子查询的查询语句不能够转为对应的 semi-join 的形式，并且该子查询是相关子查询，则该子查询
的第一个 SELECT 关键字代表的那个查询的 select_type 就是 DEPENDENT SUBQUERY

select_type为DEPENDENT SUBQUERY的查询可能会被执行多次。

7、DEPENDENT UNION

在包含 UNION 或者 UNION ALL 的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小查询之外，其余的小查询的 select_type 的值就是 DEPENDENT UNION 。

大概意思就是：大查询有个子查询；子查询里有一个union链接的两个小查询

8、DERIVED

对于采用物化的方式执行的包含派生表的查询，该派生表对应的子查询的 select_type 就是 DERIVED

如果派生表可以通过和外层查询合并的方式执行的话（mysql内部会执行优化）：

临时表，派生表，物化表的区别：https://blog.csdn.net/Smallc0de/article/details/111552824

9、MATERIALIZED

当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，该子查询对应的 select_type 属性就是 MATERIALIZED。

这个我用自己的表演示不出来，奇怪了，我表的数据也挺多的，一样的sql语句，死活不物化给我看，知道就行了算了

10、剩下那两个书里说不常用，不介绍了，那我也不会，也不记了

4、partitions

一般情况下我们的查询语句的执行计划的 partitions 列的值都是 NULL 。

5、type

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

1、system

当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，那么对该表的访问方法就是 system 。可自行创建一个MyISAM引擎的表放入一条数据测试。

表改成使用InnoDB存储引擎

2、const

根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是 const 。

3、eq_ref

在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是eq_ref

4、ref

通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是 ref

5、fulltext

略

6、ref_or_null

当对普通二级索引进行等值匹配查询，该索引列的值也可以是 NULL 值时，那么对该表的访问方法就可能是ref_or_null

7、index_merge

一般情况下对于某个表的查询只能使用到一个索引，但我们唠叨单表访问方法时特意强调了在某些场景下可以使用 Intersection 、 Union 、 Sort-Union 这三种索引合并的方式来执行查询（这部分知识点又得补了）

8、unique_subquery

类似于两表连接中被驱动表的 eq_ref 访问方法， unique_subquery 是针对在一些包含 IN 子查询的查询语句中，如果查询优化器决定将 IN 子查询转换为 EXISTS 子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的 type 列的值就是 unique_subquery

不加 or key3 = “a” 再后面就不会出现这样的效果，因为不加的话就是eq_ref了。

9、index_subquery

index_subquery 与 unique_subquery 类似，只不过访问子查询中的表时使用的是普通的索引。

ps：子查询里用到的需是普通索引，联合索引也不行

10、range

使用索引获取某些 范围区间 的记录，那么就可能使用到 range 访问方法

11、index

使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是 index。

上述查询中的搜索列表中只有name一个列，而且搜索条件中也只有age 一个列，这两个列又恰好包含在 key23 这个索引中，可是搜索条件 age 不能直接使用该索引进行 ref 或者 range 方式的访问，只能扫描整个 key23 索引的记录，所以查询计划的 type 列的值就是 index 。

12、ALL

全表扫描

6、possible_keys和key

possible_keys 列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些， key 列表示实际用到的索引有哪些

possible_keys列中的值并不是越多越好，可能使用的索引越多，查询优化器计算查询成本时就得花费更长时间，所以如果可以的话，尽量删除那些用不到的索引。

7、key_len

key_len 列表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度，它是由这三个部分构成的：

对于使用固定长度类型的索引列来说，它实际占用的存储空间的最大长度就是该固定值，

对于指定字符集的变长类型的索引列来说，比如某个索引列的类型是 VARCHAR(100) ，使用的字符集是 utf8 ，那么该列实际占用的最大存储空间就是 100 × 3 = 300 个字节。

如果该索引列可以存储 NULL 值，则 key_len 比不可以存储 NULL 值时多1个字节。
对于变长字段来说，都会有2个字节的空间来存储该变长列的实际长度。

eg： id 列的类型是 INT ，并且不可以存储 NULL 值，所以在使用该列的索引时 key_len 大小就是 4，如果int类型的不是主键，可以为null， key_len 列就变成了 5

8、ref

当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是 const 、 eq_ref 、 ref 、 ref_or_null 、
unique_subquery 、 index_subquery 其中之一时， ref 列展示的就是与索引列作等值匹配的东东是个啥。

例如：看到 ref 列的值是 const ，表明在使用索引执行查询时，与id列作等值匹配的对象是一个常
数，

9、rows

如果查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的 rows 列就代表预计需要扫描的行
数，如果使用索引来执行查询时，执行计划的 rows 列就代表预计扫描的索引记录行数。

10、filtered

在单表查询中：
rows x filtered 就是满足除索引条件外的数据数量
在多表查询中：
驱动表的 rows x filtered 就是被驱动表要执行的次数
被驱动表的rows x filtered 就是满足除索引条件外的数据数量

11、Extra

Extra 列是用来说明一些额外信息的，我们可以通过这些额外信息来更准确的理解 MySQL 到底将如何执行给定的查询语句。
我挑几个感觉会常见的或者比较重要的记一下好了，太多了。

1、Using index condition（索引下推）

有些搜索条件中虽然出现了索引列，但却不能使用到索引

上面sql的执行步骤：

先根据 key1 > 'z' 这个条件，定位到二级索引 idx_key1 中对应的二级索引记录。
对于指定的二级索引记录，先不着急回表，而是先检测一下该记录是否满足 key1 LIKE '%a' 这个条
件，如果这个条件不满足，则该二级索引记录压根儿就没必要回表。
对于满足 key1 LIKE '%a' 这个条件的二级索引记录执行回表操作。

我们说回表操作其实是一个随机 IO ，比较耗时，所以是一个改进，但是可以省去好多回表操作的成本。这个改进称之为 索引条件下推 （英文名： Index Condition Pushdown ）。

2、Using where

当我们使用全表扫描来执行对某个表的查询，并且该语句的 WHERE 子句中有针对该表的搜索条件时，在Extra 列中会提示上述额外信息。

简单点说就是where条件中有没有带索引的条件列。

3、Using join buffer (Block Nested Loop)

在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度， MySQL 一般会为其分配一块名叫
join buffer 的内存块来加快查询速度，也就是我们所讲的 基于块的嵌套循环算法。

其实就是要查询到的rows太多，所以才要内存块。

4、Using temporary

在许多查询的执行过程中， MySQL 可能会借助临时表来完成一些功能，比如去重、排序之类的，比如我们在执行许多包含 DISTINCT 、 GROUP BY 、 UNION 等子句的查询过程中，如果不能有效利用索引来完成查询，MySQL 很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行计划的 Extra 列将会显示 Using temporary 提示

执行计划中出现 Using temporary 并不是一个好的征兆，因为建立与维护临时表要付出很大成本的，所以我们最好能使用索引来替代掉使用临时表