MySQL-原理篇-⑥调优

学习核心

• mysql单机性能（参考MySQL5.7官方测试结果）

• explain执行计划

  • explain 关键字拆解（理解常见SQL语句的explain输出结果分析）

• 索引调优

  • 掌握常见索引失效的场景，结合explain进行分析
  • 正确地建立和使用索引

• SQL优化

  • 掌握如何定位一条慢SQL语句
  • 掌握如何对慢SQL语句进行优化
  • 掌握分页场景的SQL优化

• 连接池

  • 连接池的概念核心和应用

• MySQL性能优化

  • 不局限于SQL调优场景，从整体上对MySQL进行性能调优

学习资料

• 数据库调优参考学习资料
• MySQL是怎样运行的：从根儿上理解MySQL =》 查询优化的百科全书- Explain 详解
• explain 关键字解析（学习视频）
• 如何编写优质SQL
• SQL优化和压力工具中的参数分析
• MySQL 连接池优化 实战篇

数据准备

案例准备（基于一些常见业务场景拆解SQL调优思路）

​ MySQL版本：5.7.44 隔离级别：可重复读

# 参考示例表
CREATE TABLE single_table (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    UNIQUE KEY idx_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

​ 案例准备：基于上面的表结构，构建两个表s1、s2，并分别向其中插入1w条数据用作案例测试。其中主键ID设置为自增，其余数据均随机生成用作模拟测试

创建数据表s1、s2

# 1.创建表s1
CREATE TABLE s1 (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    UNIQUE KEY idx_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

# 2.创建表s2（复刻s1的表结构）
-- create table s2 as select * from s1;  // 采用这种方式只是复刻表结构，但并没有创建索引（此处不采用这种方式）
CREATE TABLE s2(
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    UNIQUE KEY idx_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

构建随机函数（用于随机生成字符串信息）

# 1.构建随机函数
DELIMITER //
CREATE DEFINER = `root` @`localhost` FUNCTION rand_string2 ( n INT ) RETURNS VARCHAR ( 255 ) CHARSET utf8mb4 COLLATE utf8mb4_general_ci #该函数会返回一个字符串
BEGIN
	DECLARE
		chars_str VARCHAR ( 100 ) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
	DECLARE
		return_str VARCHAR ( 255 ) DEFAULT '';
	DECLARE
		i INT DEFAULT 0;
	WHILE
			i < n DO
			
			SET return_str = CONCAT(
				return_str,
			SUBSTRING( chars_str, FLOOR( 1+RAND ()* 52 ), 1 ));
		
		SET i = i + 1;
		
	END WHILE;
	RETURN return_str;
	
END // 
DELIMITER;

# 2.查看随机函数是否生成成功(可借助客户端工具查看)

// 如果不允许创建函数，则需要开启相关配置（[Err] 1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation）
set global log_bin_trust_function_creators=1;

构建存储过程，模拟插入数据

# 1.普通存储过程构建

# 向表s1插入数据
DELIMITER //
CREATE DEFINER = `root` @`localhost` PROCEDURE `insert_table_s1` (
	IN min_num INT ( 10 ),
	IN max_num INT ( 10 )) 
	
BEGIN
	DECLARE
		i INT DEFAULT 0;
	
	SET autocommit = 0;
	REPEAT
			
		SET i = i + 1;
		INSERT INTO s1
		VALUES
			(
				( min_num + i ),
				rand_string2 ( 6 ),
				( min_num + 30 * i + 5 ),
				rand_string2 ( 6 ),
				rand_string2 ( 10 ),
				rand_string2 ( 5 ),
				rand_string2 ( 10 ),
			rand_string2 ( 10 ));
		UNTIL i = max_num 
	END REPEAT;
	COMMIT;

END // 

DELIMITER;

# 向表s2插入数据
DELIMITER //
CREATE DEFINER = `root` @`localhost` PROCEDURE `insert_table_s2` (
	IN min_num INT ( 10 ),
	IN max_num INT ( 10 )) 
	
BEGIN
	DECLARE
		i INT DEFAULT 0;
	
	SET autocommit = 0;
	REPEAT
			
		SET i = i + 1;
		INSERT INTO s2
		VALUES
			(
				( min_num + i ),
				rand_string2 ( 6 ),
				( min_num + 30 * i + 5 ),
				rand_string2 ( 6 ),
				rand_string2 ( 10 ),
				rand_string2 ( 5 ),
				rand_string2 ( 10 ),
			rand_string2 ( 10 ));
		UNTIL i = max_num 
	END REPEAT;
	COMMIT;

END // 

DELIMITER;


# 删除存储过程
DROP PROCEDURE 存储过程名称;

# 2.调用存储过程分别往两个表中插入数据
CALL insert_table_s1(10001,10000); // 插入1w条数据耗时1s左右
CALL insert_table_s2(10001,10000); // 插入1w条数据耗时1s左右

调优思路

MySQL调优思路

• 数据库版本：确认MySQL版本和单机性能支持
• 筛选出慢查询语句：通过慢查询日志筛选出慢查询语句（超出指定执行时间的SQL语句，这个阈值可结合业务需求自行定义）、借助show profiles;分析分析SQL执行成本
• SQL执行计划分析：检索出慢查询语句，并通过explain语句分析SQL执行计划
• 索引调优：结合SQL执行计划针对性地对慢查询语句进行SQL优化
• 连接池优化：池化思想，支持数据库连接管理和复用，提升MySQL执行效率
• 性能优化：针对单机、高并发场景从整体上拆解MySQL性能优化，而不局限于SQL调优

• 硬件资源优化：花钱提升服务器硬件
• 操作系统优化：调整操作系统内核参数（例如调整IO调度策略等）
• MySQL软件优化：针对数据库软件版本进行优化（例如调整事务隔离级别、调整InnoDB引擎的日志刷盘时机）
• SQL优化：通过SQL优化提升SQL执行效率

​ MySQL的优化涉及面会很广，常见的包括但不限于索引调优、SQL优化、连接池优化、性能优化等，结合实际场景选择合适的优化手段进行覆盖，其中效果最显著、成本最低的优化方式自然是SQL优化

​ 所谓漏斗模型：即根据图示理解对于MySQL来说影响性能的点有哪些，哪些是关键的（上图倒过来就像是一个漏斗），可以看到SQL优化的优化价值是最高的

1.筛选慢查询语句

方式1：从慢查询日志中获取

​ 结合【MySQL-原理篇-日志】相关的学习 todo：慢查询日志分析（url 关联），可通过分析慢查询日志定位慢查询语句，MySQL的慢查询日志，用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。

# 查看慢查询日志是否开启
show variables like '%slow_query_log%';
- slow_query_log   慢查询日志开启状态
- slow_query_log_file 慢查询日志存放位置

# 1.开启慢查询日志(开启后可再次查看状态进行确认)
set global slow_query_log='ON';

# 2.修改long_query_time阈值（执行时间超出这个阈值的SQL会被记录在慢查询日志中）
show variables like '%long_query_time%'; # 默认是10s
set global long_query_time = 1; # 可自定义该阈值（注意该值修改后生效的时机，例如设置global的方式对当前session的long_query_time失效，对新连接的客户端有效）

# 3.查看当前系统中有多少条慢查询记录
SHOW GLOBAL STATUS LIKE '%Slow_queries%';

# 一般在调优场景中可开启慢查询日志，正常情况下要关闭慢查询日志避免其对数据库性能造成影响

​ 查看SQL执行成本（可通过show profiles; 进行查看当前会话中执行语句的执行成本，用于SQL调优的测量），默认是关闭的，开启后可保存最近15次的运行结果

# 查看show profile启用状态
show variables like '%profiling%';

# 开启show profile（如果没有指定global，当前设定只针对当前会话有效）
set profiling='ON';

# 执行show profiles
show profiles; # 查看当前会话有哪些profiles(执行了哪些SQL)

# 查看最近一次查询的开销
show profile;

# 查询指定Query ID的开销
show profile cpu,block io for query 10;

方式2：通过 show full processlist 实时获取交互的 SQL

# 实时获取交互的SQL
show full processlist;

• ID：作为一个标识 ID，如果你打算 kill 一个 SQL，可以根据 ID 来进行
• User：当前正在执行 SQL 的用户，只显示你登录账号权限范围内能够看到的用户
• Host：显示这个语句是从哪个 ID 和端口上发出的
• db：当前线程使用的库名
• Command：连接执行的命令状态，一般是 Sleep、Query、Connect 等
• Time：状态持续的时间（单位是秒）
• State：显示当前 SQL 语句的状态，这是一个非常重要的判断标识，比如多次刷新命令时，发现 SQL 常处于 Sending data，那么这条 SQL 大概率是存在问题的
• Info：显示正在执行的 SQL 语句，这是一种直接拿到慢 SQL 的方式

2.SQL执行计划分析（explain）

explain 详解

​ 一条查询语句在经过MySQL查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划，这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行查询等等。可以通过EXPLAIN语句来查看某个查询语句的具体执行计划，从而可以有针对性的提升查询语句的性能

# 查看explain语句的核心字段
EXPLAIN SELECT 1; 

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

# 不只是针对select语句，对于insert、update、delete语句一样可以分析其执行计划，一般来说select的场景可能应用会比较多（对查询语句优化的场景比较常见）

explain 语句输出字段详解

列名	描述
id	在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
select_type	SELECT关键字对应的那个查询的类型
table	表名
partitions	匹配的分区信息
type	针对单表的访问方法
possible_keys	可能用到的索引
key	实际上使用的索引
key_len	实际使用到的索引长度
ref	当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows	预估的需要读取的记录条数
filtered	某个表经过搜索条件过滤后剩余记录条数的百分比
Extra	一些额外的信息

​ 在针对MySQL原理篇章的学习中，其实陆陆续续都会使用到这个关键字，在实际过程中分析不要死记硬背，而是结合场景分析一些==“核心字段”==所提供的信息

（1）table （关联表名）

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

# 单表查询
explain select * from s1;

# 连接查询
explain select * from s1 inner join s2;

（2）id （大查询语句中每一个select关键字对应一个唯一id）

​ 参考上述示例，示例中对于单表查询的记录id为1、连接查询的多条记录id都为1，因为其在各自的查询语句中都是同属于一个select关键字，因此对应一个唯一的id。如果是针对大查询语句（例如一条语句中可能有多个select关键字出现），则每个select关键字都对应一个唯一的id

# 案例1：连接查询
explain select * from s1,s2;

# 案例2：查询中包含子查询
explain select * from s1 where key1 in (select key1 from s2);

# 案例3：查询中包含union的情况
explain select * from s1 union select * from s2;

特殊场景分析：【 id 为NULL】如何理解？

​ 分析【案例3】中的【id为null】这条记录，此处可以结合union的执行原理进行分析，因为union涉及对多个查询的结果集合并去重，因此需要借助一个内部临时表（此处创建的内部临时表的名称则为<union1,2>），id为NULL则表示这个临时表是为了合并两个查询结果集而创建的。

# 查询中包含union all的情况
explain select * from s1 union all select * from s2;

​ 查看union all的执行计划，由于union all不涉及去重，因此也不需要创建临时表，则其执行计划如下所示（即没有id为null的临时表记录）

特殊场景分析：查询优化器可能会将子查询转化为连接查询

​ 此处需要注意的是，查询优化器可能对涉及子查询的查询语句进行重写，从而转换为连接查询。所以如果想知道查询优化器对某个包含子查询的语句是否进行了重写，可通过查看执行计划进行确认

explain select * from s1 where key1 in (select key3 from s2 where common_field = 'a'); 

​ 从执行计划分析，该语句本来是个子查询（正常情况分析每个select关键字会对应一个唯一ID），但是在此处执行计划中的s1、s2表对应记录的ID值都为1，则说明此时查询优化器将这个子查询操作转化为连接查询

（3）select_type（SELECT关键字对应的那个查询的类型）

​ 基于前面的id字段解读，一条大的查询语句里边可以包含若干个SELECT关键字，每个SELECT关键字代表着一个小的查询语句，而每个SELECT关键字的FROM子句中都可以包含若干张表（这些表用来做连接查询），每一张表都对应着执行计划输出中的一条记录，对于在同一个SELECT关键字中的表来说，它们的id值是相同的。

每一个SELECT关键字代表的小查询都定义了一个称之为select_type的属性，通过某个小查询的select_type属性，就可以知道这个小查询在整个大查询中扮演了一个什么角色，每个值的描述如下所示

名称	描述
SIMPLE	Simple SELECT (not using UNION or subqueries)
PRIMARY	Outermost SELECT
UNION	Second or later SELECT statement in a UNION
UNION RESULT	Result of a UNION
SUBQUERY	First SELECT in subquery
DEPENDENT SUBQUERY	First SELECT in subquery, dependent on outer query
DEPENDENT UNION	Second or later SELECT statement in a UNION, dependent on outer query
DERIVED	Derived table
MATERIALIZED	Materialized subquery
UNCACHEABLE SUBQUERY	A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNION	The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)

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

explain select * from s1;
explain select * from s1 inner join s2;

PRIMARY：对于包含UNION、UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，最左边查询（外层查询）的select_type值就是PRIMARY

explain select * from s1 union select * from s2;

UNION：对于包含UNION、UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，除了最左边的小查询外其余小查询的select_type值为UNION

​ 参考上述示例

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

​ 参考上述示例

SUBQUERY：需满足下述条件的第一个子查询（满足下述条件的子查询的第一个SELECT关键字代表的那个查询的select_type就是SUBQUERY）

• 包含子查询的查询语句不能够转为对应的semi-join的形式
• 子查询是不相关子查询
• 查询优化器决定采用将该子查询物化的方案来执行该子查询

explain select * from s1 where key1 in (select key1 from s2) or key3 = 'a';

​ 外层查询的select_type就是PRIMARY，子查询的select_type就是SUBQUERY

DEPENDENT SUBQUERY：依赖子查询（满足下述条件的子查询的第一个SELECT关键字代表的那个查询的select_type就是DEPENDENT SUBQUERY）

• 包含子查询的查询语句不能够转为对应的semi-join的形式
• 子查询是相关子查询（子查询的检索需要依赖其他表）

explain select * from s1 where key1 in (select key1 from s2 where s1.key2 = s2.key2) or key3 = 'a';

​ select_type 为 DEPENDENT SUBQUERY的查询可能会被执行多次（因为子查询的检索依赖于外部的查询（依赖于其他的表）），MySQL中针对依赖子查询的优化有两种思路：

• 转化为派生表关联：将子查询转化为排查表进行连接查找
• 升级MySQL版本：MySQL8.0版本中优化器对子查询的优化已相对完备，会将依赖子查询进行优化

DEPENDENT UNION：在包含UNION或者UNION ALL的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小查询之外，其余的小查询的select_type的值就是DEPENDENT UNION（对比UNION其加了一个限定条件：即各个小查询都依赖于外层查询）

explain select * from s1 where key1 in (select key1 from s2 where key1 = 'a' union select key1 from s1 where key1 = 'b');

• 第1条记录：外层查询，对应select_type为PRIMARY
• 第2条记录：子查询的第一个子查询，无法转化为semi-join的相关子查询，对应select_type为DEPENDENT SUBQUERY
• 第3条记录：对应select_type为DEPENDENT UNION
• 第4条记录：union 需要对合并结果集进行去重，因此创建 id为2、3的合并结果集，对应select_type为UNION RESULT

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

explain select * from (select key1,count(*) as c from s1 group by key1)as derived_s1 where c>1;

​ 结合执行计划分析，id为2的记录代表子查询的执行方式，其select_type为DERIVED，说明该子查询是以物化的方式执行的。id为1的记录代表着外层查询，观察其table列为<derived2>（而不是指定的别名derived_s1），则表示该查询是针对派生表物化之后的表进行查询的

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

explain select * from s1 where key1 in (select key1 from s2);

​ 查看执行计划的第3条记录，对应的是单表查询，其select_type为MATERIALIZED，表示查询优化器先把子查询先转化为物化表。

​ 查看执行计划的前两条记录id值都为1（对应同一个select概念），说明查询优化器将这个子查询操作优化为两表连接查询，其中第2条记录中的table为<subquery2>说明该表其实就是id为2对应的子查询执行之后产生的物化表，将该物化表结果集与s1进行连接查询

UNCACHEABLE SUBQUERY、UNCACHEABLE UNION （比较不常用的场景，待后续补充完善）

（4）type（针对单表的访问方法）

​ 执行计划的一条记录就代表着MySQL对某个表的执行查询时的访问方法，其中的type列就表明了这个访问方法是什么

explain select * from s1 where key1 = 'a';

​ 对使用InnoDB存储引擎的表进行单表访问的一些访问方法，完整的访问方法如下：system，const，eq_ref，ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery，range，index，ALL

​ 关注核心常用的字段说明：

type 字段	说明
const（主键或者唯一索引扫描）	使用了主键或者唯一索引与常量值进行比较
eq_ref（连接查询时，主键或者唯一索引扫描）	连接查询时，使用了主键或者唯一索引与常量值进行比较
ref（非唯一索引扫描（即普通的二级索引））	使用了普通的二级索引与常量值进行比较
range（索引范围扫描）	索引范围扫描
index（全索引扫描）	对二级索引进行全扫描（例如使用了索引覆盖，但需要对扫描整个索引）
ALL（全表扫描）	全表数据扫描，性能最差，应尽量避免

system

​ 当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，那么对该表的访问方法就是system

# 构建测试数据
CREATE TABLE t(i int) Engine=MyISAM;
INSERT INTO t VALUES(1);
# 查看执行计划
explain select * from t;

​ 结合上述内容分析，type为system的条件必须满足两个条件：

• 表中只有一条记录
• 表使用的存储引擎的统计数据是精确的（MyISAM、Memory）

const

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

explain select * from s1 where id = 10086 ;  -- 主键与常数等值匹配
explain select * from s1 where key2 = 10036; -- 唯一二级索引与常数等值匹配

eq_ref

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

explain select * from s1 inner join s2 on s1.id = s2.id

​ 此处连接查询中s1作为驱动表、s2作为被驱动表，两者通过主键的等值匹配来进行访问，所以记录2中的type为eq_ref

ref

​ 当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是ref（为什么说可能，因为还有一个ref_or_null）

fulltext

​ 全文索引

ref_or_null

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

explain select * from s1 where key1 = 'a' or key1 is null;

index_merge

​ 一般情况下对于某个表的查询只能使用到一个索引，但介绍单表访问方法时有提到在某些场景下可以使用Intersection、Union、Sort-Union这三种索引合并的方式来执行查询，可结合案例看一下执行计划中是怎么体现MySQL使用索引合并的方式来对某个表执行查询的

explain select * from s1 where key1 = 'a' or key3 = 'a';

​ 从执行计划的type列的值是index_merge就可以看出，MySQL打算使用索引合并的方式来执行对s1表的查询

unique_subquery

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

explain select * from s1 where key2 in (select id from s2 where s1.key1 = s2.key1) or key3 = 'a';

​ 执行计划的第二条记录的type值就是unique_subquery，说明在执行子查询时会使用到id列的索引

index_subquery

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

# todo  待确认....
EXPLAIN SELECT * FROM s1 WHERE common_field IN (SELECT key3 FROM s2 where s1.key1 = s2.key1) OR key3 = 'a';

range

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

explain select * from s1 where key1 in ('a','b','c');

index

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

explain select * from s1 where key_part3 = 'a';

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

​ 对于组合索引而言，这本来应该是一种索引失效的场景（因为前置的索引条件没有，MySQL并不知道从哪个根节点入手），理论预期是全表扫描，但为什么最终还是走了idx_key_part？

​ 分析此处为什么key为idx_key_part：对于使用InnoDB存储引擎的表来说，二级索引的记录只包含索引列和主键列的值，而聚簇索引中包含用户定义的全部列以及一些隐藏列，所以扫描二级索引的代价比直接全表扫描，也就是扫描聚簇索引的代价更低一些。此时再来看语句，需要检索的是key_part2字段，且搜索条件为key_part3字段，这两个列恰好在联合索引的定义范围内，因此MySQL执行器认为如果此处选择走联合索引idx_key_part（恰好也不需要回表）其执行成本低于全表扫描，则选择了走idx_key_part。

​ 如果是基于下面的案例，检索条件和筛选结果中包含了不在联合索引中的字段，则需要进行回表操作，则无法利用联合索引idx_key_part

ALL

​ 全表扫描（案例可以参考前面的内容），最简单的案例就是selec * from s1;

（5）possible keys 和 key

​ 在EXPLAIN语句输出的执行计划中，possible_keys列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些，key列表示实际用到的索引有哪些。需注意两者并没有特别必然的强联系，可以结合上述案例进行拆解分析

​ possible_keys是符合MySQL定义下可能会使用到的索引（一般通过该列来初步确认SQL语句中可能涉及到的索引，如果出现索引失效的场景则其也不会列出）

​ key是实际使用到的索引，它的根据一般是基于possible_keys。但是会有一种特殊的场景存在，就是possible_keys列是空的，而key列展示的是实际使用到的索引

​ 参考上面的type为index中的案例分析：因为possible_keys会列出理论上可能会使用到的索引，而对于联合索引(key_part1,key_part2,key_part3)而言，如果执行explain select * from s1 where key_part3 = 'a';并不满足最左匹配原则，即理论上该联合索引对于该语句而言是失效的，且也有没基于key_part3的额外构建索引，因此该语句执行的possible_keys为null。但是在实际执行过程中，MySQL会通过查询优化器计算使用不同索引的成本之后来决定相应的执行计划，当它发现如果使用idx_key_part联合索引的执行成本会比全表扫描的执行成本要低的话，就选择了走idx_key_part，因此对应的key为idx_key_part

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

（6）key_len

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

• 对于使用固定长度类型的索引列来说，它实际占用的存储空间的最大长度就是该固定值，对于指定字符集的变长类型的索引列来说，比如某个索引列的类型是VARCHAR(100)，使用的字符集是utf8，那么该列实际占用的最大存储空间就是100 × 3 = 300个字节
• 如果该索引列可以存储NULL值，则key_len比不可以存储NULL值时多1个字节
• 对于变长字段来说，都会有2个字节的空间来存储该变长列的实际长度

# 案例1：id 列类型为INT且不可以存储NULL值
explain select * from s1 where id =  10086; // 4

# 案例2：当索引列可以存储NULL值
explain select * from s1 where key2 =  10066; // 4+1=5

# 案例3：可变长度的索引（varchar(100)）
explain select * from s1 where key1 =  'a'; // key1类型为varchar(100),该列允许存储null且为可变长列=》100*3 + 1 +2 = 303

​ 此处需注意区分InnoDB行格式中介绍的概念：存储变长字段的实际长度可能占用1个字节或者2个字节。此处执行计划的生成是在MySQL server层中的功能，并不是针对具体某个存储引擎的功能，在执行计划中输出key_len列主要是为了区分某个使用联合索引的查询具体用了几个索引列（因此按照上述规则计算即可），而不是为了准确的说明针对某个具体存储引擎存储变长字段的实际长度占用的空间到底是占用1个字节还是2个字节。

# 联合索引idx_key_part:(key_part1,key_part2,key_part3)=>索引(key_part1)、(key_part1,key_part2)、(key_part1,key_part2,key_part3)

# 下述语句使用到联合索引idx_key_part中的1个索引列
explain select * from s1 where key_part1 = 'a'; // 303

# 下述语句使用到联合索引idx_key_part中的2个索引列
explain select * from s1 where key_part1 = 'a' and key_part2 = 'b'; // 303 + 303

（7）ref

​ 当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery其中之一时，ref列展示的就是与索引列作等值匹配的东东是什么，比如只是一个常数或者是某个列

# 案例1
explain select * from s1 where key1 = 'a';
-- type为ref(普通二级索引的等值查询),ref列的值为const(表示与key1列作等值匹配的对象是一个常数)

# 案例2
explain select * from s1 inner join s2 on s1.id = s2.id;
-- type为eq_ref(连接查询时通过主键索引等值匹配),ref列的值为[数据库名.s1.id](说明与s2中的id列作等值匹配的对象是[数据库名.s1.id])

# 案例3
explain select * from s1 inner join s2 on s2.key1 = upper(s1.key1);
-- type为ref(普通二级索引的等值查询),ref列的值为func(表示与key1列作等值匹配的对象是一个函数)

（8）rows

扫描行数：

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

# 案例1
explain select * from s1 where common_field > 'z'; 

# 案例2
explain select * from s1 where key1 > 'z';

（9）filtered

​ 之前在分析连接查询的成本时提出过一个condition filtering的概念，就是MySQL在计算驱动表扇出时采用的一个策略：

• 如果使用的是全表扫描的方式执行的单表查询，那么计算驱动表扇出时需要估计出满足搜索条件的记录到底有多少条。
• 如果使用的是索引执行的单表扫描，那么计算驱动表扇出的时候需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。

# 案例1：
explain select * from s1 where key1 > 'z' and common_field = 'a';

# 案例2
explain select * from s1 inner join s2 on s1.key1 = s2.key1 where s1.common_field = 'a';

​ 对于单表查询来说，这个filtered列的值没什么意义，更关注在连接查询中驱动表对应的执行计划记录的filtered值

​ 案例1：从执行计划的key列中可以看出来，该查询使用idx_key1索引来执行查询，从rows列可以看出满足key1 > 'z'的记录有372条。执行计划的filtered列就代表查询优化器预测在这372条记录中，有多少条记录满足其余的搜索条件，也就是common_field = 'a'这个条件的百分比。此处filtered列的值是10.00，说明查询优化器预测在372条记录中有10.00%的记录满足common_field = 'a'这个条件

​ 案例2：从执行计划中可以看出来，查询优化器打算把s1当作驱动表，s2当作被驱动表。驱动表s1表的执行计划的rows列为9895， filtered列为10.00，这意味着驱动表s1的扇出值就是9895× 10.00% = 989.5，这说明还要对被驱动表执行大约989次查询

（10）Extra

​ Extra列是用来说明一些额外信息的，可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。MySQL提供的额外信息有好几十个，挑一些平时常见的或者比较重要的额外信息进行学习

​ 关注一些特例（需要注意的状态，一些异常状态可能会对性能产生不良影响，意味着需要查询优化）：

• Using filesoft：表示SQL需要进行额外的步骤来对返回的结果集进行排序（会根据连接类型、存储排序键值、匹配条件的全部行记录进行排序）
• Using temporaey：表示MySQL需要创建一个临时表存储结果（例如去重操作），非常消耗性能

No tables used：查询语句的没有FROM子句

Impossible WHERE：查询语句的WHERE子句永远为FALSE

No matching min/max row：查询列表处有MIN或者MAX聚集函数，但是并没有符合WHERE子句中的搜索条件的记录

Using index：查询列表以及搜索条件中只包含属于某个索引的列（在可使用索引覆盖的情况下）

Using index condition：在查询语句的执行过程中将要使用索引条件下推特性

旧版MySQL：对于条件key1 > 'z'会使用到idx_key1索引，对于key1 like '%b'这个条件在以前的MySQL版本中是无法使用索引的

• 【1】先根据key1 > 'z'获取到二级索引记录
• 【2】根据步骤【1】获取到的记录进行回表，找到完整的记录再匹配记录是否满足key1 like '%b'这个条件，将符合条件的记录加入到最后的结果集

新版MySQL：引入索引下推概念，对执行过程进行优化（以减少回表次数，提升效率）

• 【1】先根据key1 > 'z'获取到二级索引记录
• 【2】根据步骤【1】获取到的记录，先不着急回表，而是先检测一下是否满足key1 like '%b'这个条件（因为恰好该二级索引记录中可以提供这个条件校验的支持）
  • 如果条件不满足，无需回表
  • 如果条件满足，需回表获取完整记录，然后将获取到的记录加入结果集

Using where：

• 当使用全表扫描来执行对某个表的查询，并且该语句的WHERE子句中有针对该表的搜索条件时
• 当使用索引访问来执行对某个表的查询，并且该语句的WHERE子句中有除了该索引包含的列之外的其他搜索条件时

Using join buffer (Block Nested Loop)：基于块的嵌套循环算法（针对连接查询）

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

• Using join buffer (Block Nested Loop)：这是因为对表s2的访问不能有效利用索引，只好退而求其次，使用join buffer来减少对s2表的访问次数，从而提高性能
• Using where：可以看到查询语句中有一个s1.common_field = s2.common_field条件，因为s1是驱动表，s2是被驱动表，所以在访问s2表时，s1.common_field的值已经确定下来了，所以实际上查询s2表的条件就是s2.common_field = 一个常数，所以提示了Using where额外信息

Not exists：当使用左（外）连接时，如果WHERE子句中包含要求被驱动表的某个列等于NULL值的搜索条件，而且那个列又是不允许存储NULL值的

Using intersect(...)、Using union(...)和Using sort_union(...) ：xx合并的方式执行查询

如果执行计划的Extra列出现了Using intersect(...)提示，说明准备使用Intersect索引合并的方式执行查询，括号中的...表示需要进行索引合并的索引名称；

如果出现了Using union(...)提示，说明准备使用Union索引合并的方式执行查询；

如果出现了Using sort_union(...)提示，说明准备使用Sort-Union索引合并的方式执行查询

Zero limit：当的LIMIT子句的参数为0时，表示压根儿不打算从表中读出任何记录

Using filesort：如果某个查询需要使用文件排序的方式执行查询，就会在执行计划的Extra列中显示Using filesort提示

​ 有一些情况下对结果集中的记录进行排序是可以使用到索引的：EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10;（因为key1索引本身有序，因此不需要额外排序）

​ 但是很多情况下排序操作无法使用到索引，只能在内存中（记录较少的时候）或者磁盘中（记录较多的时候）进行排序，设计MySQL的大佬把这种在内存中或者磁盘上进行排序的方式统称为文件排序（英文名：filesort）。如果某个查询需要使用文件排序的方式执行查询，就会在执行计划的Extra列中显示Using filesort提示

Using temporary：使用到了临时表

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

• 场景1：单表去重

• 场景2：union（并操作，涉及到去重）

• 场景3：group by 语句

EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field;
# 等价于（在一些MySQL版本中MySQL会在包含GROUP BY子句的查询中默认添加上ORDER BY子句）
EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field ORDER BY common_field;
-- 所以Extra会有Using temporary; Using filesort提示，如果不希望为包含group by的子句的查询进行排序，可以显式指定ORDER BY NULL
EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field ORDER BY NULL;

​ 执行计划中出现Using temporary并不是一个好的征兆，因为建立与维护临时表要付出很大成本的，所以最好能使用索引来替代掉使用临时表，比方说下面这个包含GROUP BY子句的查询就不需要使用临时表

Start temporary, End temporary

​ 查询优化器会优先尝试将IN子查询转换成semi-join，而semi-join又有好多种执行策略，当执行策略为DuplicateWeedout时，也就是通过建立临时表来实现为外层查询中的记录进行去重操作时，驱动表查询执行计划的Extra列将显示Start temporary提示，被驱动表查询执行计划的Extra列将显示End temporary提示

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key3 FROM s2 WHERE common_field = 'a'); // 待验证

LooseScan：在将In子查询转为semi-join时，采用的是LooseScan执行策略

FirstMatch(tbl_name)：在将In子查询转为semi-join时，采用的是FirstMatch执行策略

JSON格式的执行计划

​ MySQL8.0版本可通过format参数指定执行计划格式：format = tree、format = json

# SQL
EXPLAIN FORMAT=JSON SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 WHERE s1.common_field = 'a'

# SQL
EXPLAIN FORMAT=TREE SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 WHERE s1.common_field = 'a'
-> Nested loop inner join  (cost=1360.08 rows=990)
    -> Filter: ((s1.common_field = 'a') and (s1.key1 is not null))  (cost=1013.75 rows=990)
        -> Table scan on s1  (cost=1013.75 rows=9895)
    -> Single-row index lookup on s2 using idx_key2 (key2=s1.key1), with index condition: (cast(s1.key1 as double) = cast(s2.key2 as double))  (cost=0.25 rows=1)

# JSON输出参考（MySQL8.0.21）
{
  "query_block": {
    "select_id": 1,      // select 关键字对应ID号码
    "cost_info": {
      "query_cost": "1360.07"     // 整个查询的预计执行成本
    },
    "nested_loop": [   // 几个表之间采用嵌套循环连接算法执行
      {
        "table": {
          "table_name": "s1",     // s1表是驱动表
          "access_type": "ALL",   // 访问方法为ALL，意味着使用全表扫描访问
          "possible_keys": [      // 可能使用的索引
            "idx_key1"    
          ],
          "rows_examined_per_scan": 9895,   // 查询一次s1表大致需要扫描9895条记录（相当于rows）
          "rows_produced_per_join": 989,    // 驱动表s1的扇出是989
          "filtered": "10.00",
          "cost_info": {
            "read_cost": "914.80",
            "eval_cost": "98.95",
            "prefix_cost": "1013.75",       // 单次查询s1表总共的成本
            "data_read_per_join": "1M"      // 读取的数据量
          },
          "used_columns": [                 // 执行查询中涉及到的列
            "id",
            "key1",
            "key2",
            "key3",
            "key_part1",
            "key_part2",
            "key_part3",
            "common_field"
          ],
            // 对s1表访问时针对单表查询的条件
          "attached_condition": "((`db_mysql_base`.`s1`.`common_field` = 'a') and (`db_mysql_base`.`s1`.`key1` is not null))"
        }
      },
      {
        "table": { 
          "table_name": "s2",           // s2表是被驱动表
          "access_type": "eq_ref",      // 访问方法为ref，意味着使用索引等值匹配的方式访问
          "possible_keys": [            // 可能使用的索引
            "idx_key2"
          ], 
          "key": "idx_key2",            // 实际使用的索引
          "used_key_parts": [           // 使用到的索引列
            "key2"
          ],
          "key_length": "5",
          "ref": [
            "db_mysql_base.s1.key1"
          ],
          "rows_examined_per_scan": 1,      // 查询一次s2表大致需要扫描1条记录（相当于rows）
          "rows_produced_per_join": 989,    // 被驱动表s2的扇出是989（由于后边没有多余的表进行连接，所以这个值也没什么用）
          "filtered": "100.00",             // condition filtering代表的百分比
          "index_condition": "(cast(`db_mysql_base`.`s1`.`key1` as double) = cast(`db_mysql_base`.`s2`.`key2` as double))",    // s2表使用索引进行查询的搜索条件
          "cost_info": {
            "read_cost": "247.38",
            "eval_cost": "98.95",
            "prefix_cost": "1360.08",       // 单次查询s1、多次查询s2表总共的成本
            "data_read_per_join": "1M"      // 读取的数据量
          },
          "used_columns": [                 // 执行查询中涉及到的列
            "id",
            "key1",
            "key2",
            "key3",
            "key_part1",
            "key_part2",
            "key_part3",
            "common_field"
          ]
        }
      }
    ]
  }
}

cost_info解析（核心关注prefix_cost）

# S1
"cost_info": {
    "read_cost": "914.80",
    "eval_cost": "98.95",
    "prefix_cost": "1013.75",       // 单次查询s1表总共的成本
    "data_read_per_join": "1M"      // 读取的数据量
},

• read_cost计算规则：
  • IO成本
  • 检测rows × (1 - filter)条记录的CPU成本
• eval_cost计算规则：检测 rows × filter条记录的成本
• prefix_cost就是单独查询s1表的成本：read_cost + eval_cost
• data_read_per_join表示在此次查询中需要读取的数据量

# S2
"cost_info": {
    "read_cost": "247.38",
    "eval_cost": "98.95",
    "prefix_cost": "1360.08",       // 单次查询s1、多次查询s2表总共的成本
    "data_read_per_join": "1M"      // 读取的数据量
},

​ 由于s2表是被驱动表，所以可能被读取多次，这里的read_cost和eval_cost是访问多次s2表后累加起来的值，prefix_cost的值代表的是整个连接查询预计的成本，也就是单次查询s1表和多次查询s2表后的成本的和（1013.75 + 247.38 + 98.95 = 1360.08 ）

Extended EXPLAIN

​ 在使用EXPLAIN语句查看了某个查询的执行计划后，紧接着还可以使用SHOW WARNINGS语句查看与这个查询的执行计划有关的一些扩展信息

EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.common_field IS NOT NULL;
SHOW WARNINGS;

​ SHOW WARNINGS展示出来的信息有三个字段，分别是Level、Code、Message。最常见的就是Code为1003的信息，当Code值为1003时，Message字段展示的信息类似于查询优化器将原查询语句重写后的语句。比如上面的查询本来是一个左（外）连接查询，但是有一个s2.common_field IS NOT NULL的条件，着就会导致查询优化器把左（外）连接查询优化为内连接查询，从SHOW WARNINGS的Message字段也可以看出来，原本的LEFT JOIN已经变成了JOIN。

/* select#1 */ select `db_mysql_base`.`s1`.`key1` AS `key1`,`db_mysql_base`.`s2`.`key1` AS `key1` from `db_mysql_base`.`s1` join `db_mysql_base`.`s2` where ((`db_mysql_base`.`s1`.`key1` = `db_mysql_base`.`s2`.`key1`) and (`db_mysql_base`.`s2`.`common_field` is not null))

但需注意，Message字段展示的信息类似于查询优化器将查询语句重写后的语句，并不是等价于原可执行语句，也就是说Message字段展示的信息并不是标准的查询语句，在很多情况下并不能直接拿到黑框框中运行，它只能作为帮助理解MySQL将如何执行查询语句的一个参考依据而已

3.索引调优

​ 结合 MySQL-原理篇-索引 核心知识理解，了解常用的索引调优思想和SQL问题排查（理解建立索引的一些基本原则和注意事项，一步步进行拆解。对于一些可能造成性能影响的慢SQL语句进行优化）

​ 结合explain字段分析索引失效的原因，正确地建立和使用索引

建立和使用索引的正确思路

考虑索引查询的回表问题

​ 在构建二级索引的时候要考虑查询场景的回表问题，如果回表次数过多，其对检索效率也是有一定影响的。为了避免回表，往往借助索引覆盖特性来避免额外的回表消耗。

​ 这也是为什么尽量避免select *写法的主要原因，要跟踪其本质，而不是无脑背答案

建立索引需要遵守的原则

​ 首先要理解索引的原理和核心定义，始终记住一句话合理建立索引可以有效提升检索效率，但也需考虑索引维护的成本，以及避免一些索引失效的场景，不要死记硬背

• 索引建立需要遵守的原则

  • 对于频繁作为查询条件的字段可以酌情创建索引

  • 对于经常作为范围取值、排序、分组的字段，通过构建索引提升效率（因为索引具有有序性）

  • 主外键关联的字段：在连接查找的时候会频繁使用，需为外键建立索引以提升多表查询性能

  • 建立联合索引：需注意最左匹配原则，按照字段的优先级顺序进行组合。尽可能考虑到联合索引的命中率，可以使用联合索引替代一些单值索引的场景，以减少对同一字段的索引维护

  • 覆盖索引的充分利用：索引建立也要考虑到索引的正确使用，结合联合索引概念提高索引的命中率同时，借助覆盖索引机制来有效提升检索效率

  • 对于唯一索引，如果确认不会利用该字段排序，那可以将结构改为Hash结构

  • 对于前缀索引，一般建议索引值不应过长，因此可以引入前缀索引

• 索引建立时的注意事项

  • 对于频繁增删改的字段不建议建立索引，因为索引为了保证有序性需维护相应的结构，存在一定的成本消耗
  • 索引在大数据量场景更能发挥优势，如果一个表的数据量并不是特别的大，不建议建立索引，因为索引具有一定的维护成本（不要捡了芝麻丢西瓜）
  • 索引一般不建议建立在有限状态的列字段上（涉及到大数据量回表问题），但如果业务场景需要还是可以考虑按需引入
  • 如果索引字段无序不建议添加索引（尤其是主键索引最好确保有序性），字段无需容易造成页分裂，维护成本也高
  • 注意索引失效的场景：一些需要进行函数等参与计算的字段（考虑建立函数索引，普通索引会失效）、联合索引中不满足最左原则的场景也会导致失效
  • 索引的数量不是越多也好，但也不是非要限定在一个数值，从整体上看索引对于大数据量的检索效率提升是非常客观的，合理创建索引而不拘束于限制索引数量，以更好地提升数据库性能

联合索引的最左前缀原则

​ 联合索引的构建尤其要结合实际业务场景去设计：

• 考虑字段的优先级=》进而确定索引的排序规则
• 充分利用覆盖索引、索引下推的特性优势=》尽量减少回表次数

4.SQL优化

SQL优化的目的

• 减少磁盘IO：尽量避免全表扫描、尽量使用索引、尽量使用覆盖索引避免回表操作
• 减少内存 CPU 消耗：尽可能减少排序、分组、去重之类的操作、尽量减少事务持有锁的时间

解决慢SQL的思路

• 【1】找到慢SQL语句：开启慢查询日志，通过show profiles定位慢SQL语句；或者通过show processlist 定位正在执行的慢SQL
• 【2】分析SQL执行计划：通过explain关键字分析SQL
• 【3】针对性地优化SQL

​ 在排查接口响应慢的原因时，可以考虑将慢查询日志打开，看下接口响应慢的原因是否由慢SQL导致的（通过long_query_time设定慢查询阈值，超出阈值则定义为慢SQL），如果是慢SQL，则可针对日志中的SQL进行分析和调优

# 1.查看慢查询日志（确认其开启状态和日志存放位置）
show variables like '%query_log%';

# 2.开启慢查询日志（此处set操作如果不指定作用域默认只对当前会话有效,此处限定为global）
set global slow_query_log = ON; # 开启慢查询日志
set long_query_time = 2; # 设置慢查询阈值(如果指定了global,查询发现不生效..)

# 3.模拟执行SQL
select * from s1;
select * from s1 inner join s2;
select sleep(3); # 模拟沉睡3s
select sleep(5); # 模拟沉睡5s

# 4.查看对应路径的慢查询日志记录的信息

常见SQL优化思路

• 一般 SQL 优化思路

  • 优化数据访问：通过 limit 子句缩减数据行数、避免select *

  • 拆分查询：分而治之的思想，将一个大查询拆分多个小查询，每个小查询只返回一部分查询结果

  • 覆盖索引、索引下推：当索引中的列包含所有查询中需要使用的列的时候，可以避免回表

  • 避免索引失效：检查 SQL 是否因为写的不合理，导致索引失效

  • 分解联表查询：让业务层分多个查询来聚合，或者增加冗余字段减少联表查询排序优化:对于有排序场景，如果 extra 显示 filesort，则需要考虑对排序的字段建立索引，避免文件排序

• 如果SQL和索引都没问题，但查询还是很慢，可从其他点切入

  • 分批查询：针对一个大查询可以拆分多个小查询，每个小查询只返回一部分查询数据
  • 增加缓存：针对频繁读取的热点数据，可以放到 Redis 缓存，避免每次都要请求 MySQL
  • 分表：如果表的数据量很大，比如表数据千万级别了，可以考虑分表，通过减少每次查询数据总量来解决数据查询缓慢的问题
  • 主从复制：针对读多写少的场景，可以搭建 MySQL 主从模式来分摊读请求的流量
  • 分库：针对写多读少的场景，单库的性能无法抗住高并发流量，就需要进行分库，把并发请求分散到多个实例中求

常见的SQL优化场景

（1）外连接与内连接的查询优化

（2）子查询优化和排序优化

子查询优化

排序优化

​ 排序优化主要是针对一些额外排序的场景，例如order by子句，MySQL会按照指定的规则进行排序。如果通过explain分析发现Extra出现using filesoft（使用文件排序），说明MySQL执行该语句时存在额外排序的操作，该SQL有一定的优化空间。

​ 在前面的索引学习中了解到，不是所有的order by语句都需要额外的排序。此处要注意理解一个概念：是因为没有用到排序才会有filesort，而不是说超过了sort_buffer_size才会有filesort

​ 如果排序字段存在索引有可能可以避免额外排序的性能消耗（因为索引字段本身有序，因此不需进行额外的排序操作），结合下述案例进一步拆解。此处需注意理解，这个有可能的场景的分析实际上取决于SQL实际执行是否真的使用到了该索引

# 案例1：普通字段排序（common_field无索引）
explain select * from s1 order by common_field desc;
- common_field无索引字段无索引，因此会出现filesort

# 案例1
explain select key2 from s1 order by key2 desc;
# 案例2
explain select * from s1 order by key2 desc;
# 案例3
explain select key1,key2 from s1 order by key2 desc;

​

​ 结合上述执行结果进行分析：在key2上存储索引，理解为什么出现明明在order by字段上创建了索引却还是出现using filesort的奇怪现象？

​ 要理解explain的执行结果，得先理解MySQL的查询优化器的工作原理，其核心在于为SQL执行语句选择一种执行成本最低的方式来生成相应的执行计划。梳理思路在于不要理所应该认为MySQL一定会走索引，而是要分析如果走索引和不走索引各自的成本，然后理解MySQL为什么要这样选择。

• 案例1：检索和排序字段都使用key2，可通过检索二级索引key2就可完成语句执行（二级索引key2本身有序，因此也省去了额外排序的操作）
• 案例2：检索字段为全字段、排序字段为key2，从概念上理解可能会误认为本质上应该会走key2索引，但是实际上却走了全表扫描。为了理解这一过程，需要分析走索引和不走索引各自的执行成本
  • 如果走key2索引：在遍历二级索引key2的过程中需要检索其他表字段，但是二级索引只存储了key2和主键ID值，因此需要进行回表操作（即根据主键ID回到主键索引查找完整记录）
  • 如果走全表扫描：直接遍历全表数据进行检索、排序

​ 结合上述过程分析，MySQL如果选择走key2索引的话其还包括回表过程，其执行成本可能并不少于全表扫描（包括遍历key2索引和相应回表过程），因此MySQL认为选择走全表扫描的方案更优，因此不走索引key2。类似地，案例3中除了检索key2字段还需要key1字段，这一过程也是需要回表操作，因此MySQL在案例3中还是选择了全表扫描。

​ 结合上述分析，MySQL在案例2、案例3中并没有选择走key2索引，因此Extra中出现了using filesort，这点与前面的定义是吻合的，可以参考下述案例进行验证（下述语句走key2索引，因此无序额外排序操作）

对 using filesort 的解读

简化理解：将using filesort的出现理解为【order by没用到索引】即可吗，至于里面是内存排序还是外部排序则取决于sort_buffer_size

判断order by子句中的字段索引（假设排序字段索引为idx）是否在SQL执行过程中真正被用到

• 如果idx没有被用到，则一定会出现using filesort（即需要排序），此时排序的方式有两种（这个排序方式的选择则取决于sort_buffer_size）

  • 内部排序：将筛选出来的记录存放在内存临时表，然后根据相应字段进行快速排序
  • 外部文件排序：内存放不下时，需借助外部文件进行归并排序（借助磁盘空间），将一部分排序结果放在磁盘上（将需要排序的数据分成若干份，然后对每一份数据进行单独排序后存在临时文件中，最后将这些文件合并成一个有序的大文件）

• 如果idx被用到，则不需要额外的排序操作（因为索引本身有序，也就避免了额外的排序操作）

​ 因此，在分析Extra中会不会出现using filesort只需要关注【order by有没有用到索引】：通过explain查看order by的字段相关索引是否真正被用到（确认explain结果的key列），如果没有则一定会出现using filesort，基于此进一步分析order by内部的排序规则对性能的影响（取决于sort_buffer_size的大小）

结合上述思路，可以进一步分析为什么一般情况下不建议对大文本字段进行排序？

【1】如果不希望出现using filesort，则考虑对大文本字段建立索引，但对大文本索引字段的维护成本也相对较高

【2】在对大文本字段进行排序的时候，由于需要排序的数据字段比较大（在同等数据量的情况下，对比小文本字段排序，其更容易超出sort_buffer_size范围），此时排序方式则不仅仅是内存操作，还会涉及到磁盘操作，进而影响检索性能

​ 虽然可以考虑用前缀索引方式减少索引维护成本，但是无法做到全文本匹配，始终具备局限性

针对排序优化方案

• 建立合适的索引（单字段索引或者组合索引），借助索引的有序性避免额外的排序操作
• 适当设置sort_buffer_size的大小，让MySQL尽可能地多使用内存、较少磁盘访问，也尽量避免对大文本字段进行排序操作

（3）GROUP BY 分组优化、分页查询优化

GROUP BY 分组优化

​ 在进行GROUP BY 分组查询过程中，一般会使用where、having过滤条件，两者主要区别如下：

• WHERE 子句在 GROUP BY 分组和聚合函数之前对数据行进行过滤，where 子句无法使用聚合函数
• HAVING 子句对 GROUP BY 分组和聚合函数之后的数据行进行过滤，having 子句可以使用聚合函数

​ 虽然两者最终呈现的效果是一样的，但是根据执行过程分析，显然在分组之前先使用where过滤掉不满足条件的记录可以有效提升分组查询效率

分页查询优化

​ 分页场景主要用于避免一次性大数据量的网络传输造成响应较慢的情况，通过将数据进行分页以降低服务器和网络传输的压力。在系统实现分页操作的时候，一般是通过limit关键字实现，如果偏移量太大，就存在性能问题

​ 以limit 100000,20为例进行分析：select * from t_player order by score limit 100000,20

• 如果使用了全表扫描：依次遍历100020条记录，然后剔除掉前100000条记录，只返回20条记录给客户端
• 如果使用了二级索引（性能损失会加剧）：最坏的场景考虑，如果每条记录都需执行一次回表操作，就算前100000条记录是不需要的也要执行回表操作，就会存在不可避免的100000次随机IO进而影响性能

案例准备

# 参考示例表
CREATE TABLE t_player (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) UNIQUE,
    score INT,
    PRIMARY KEY (id),
    KEY idx_name (name),
    KEY idx_score (score)
) Engine=InnoDB CHARSET=utf8;
# 数据引入
insert into t_player(id,name,score) 
values(1,'player001',80),(2,'player002',73),(3,'player003',80),(4,'player004',30),(5,'player005',60),
(6,'player006',60),(7,'player007',65),(8,'player008',60),(9,'player009',75),(10,'player010',90),
(11,'player011',80),(12,'player012',50),(13,'player013',60),(14,'player014',80),(15,'player015',70),
(16,'player016',80),(17,'player017',73),(18,'player018',90),(19,'player019',95),(20,'player020',60);

优化方式1：减少扫描次数

​ 从业务上进项改进，将“第几页”=》“下一页”：先记录上一页最后一条记录的ID（此处理解为分页序号字段），然后下次读取的时候直接从该记录的位置开始扫描，以此避免MySQL扫描大量不需要的行然后再抛弃掉的问题，构建方案如下：

-- 记录score为prev_score (第一页查找20条记录)
select score from t_player order by score limit 20;
-- 下一页(从score的下一条记录开始查找)
select score from t_player where score > prev_score order by score limit 20;
.... 以此类推 ....

场景问题1：由于“分页序号字段”的不唯一性导致分页数据不准确

​ 基于上述这种方案思路，可能会存在一个问题，假设score并不唯一，那么在分页的时候通过这种方式则有可能出现“漏记录”（score > prev_score）或者“记录重复出现”（score >= prev_score）的情况而导致分页数据不准确

# 不分页场景
select score from t_player order by score;

# 分页场景：第一页
select score from t_player order by score limit 5; # 筛选的分页最后一条记录score为60
# 分页场景：第二页
select score from t_player where score > 60 order by score limit 5; # 出现漏记录
select score from t_player where score > 60 order by score limit 5; # 出现重复记录

​

# 基于这个场景查询能更能反馈上述分页数据不准确的情况（检索字段为id、score，因此不需要回表，会走idx_score）

# 不分页场景
select id,score from t_player order by score;

# 分页场景：第一页
select id,score from t_player order by score limit 5; # 筛选的分页最后一条记录id为8
# 分页场景：第二页
select id,score from t_player where score > 60 order by score limit 5; # 出现漏记录
select id,score from t_player where score >= 60 order by score limit 5; # 出现重复记录

场景问题2：由于“分页序号字段”的无序性导致分页数据错误

​ 基于此场景可能会联想到，既然检索的score会重复，选择一个不会重复的字段是不是就能避免上面的问题。但此处需要注意一点分页都是基于原数据集基础上做拆分，因此必须保证SQL语句执行生成要分页的数据记录的一致性：例如select * from t_player order by score和select * from t_player order by id这两条语句产生的结果集记录顺序完全不同

# 根据ID来作为分页的依据
# 不分页场景
select id,score from t_player order by score;

# 分页场景：第一页
select id,score from t_player order by score limit 5; # 筛选的分页最后一条记录id为8
# 分页场景：第二页(where id>max(id))
select id,score from t_player where id > 8 order by id limit 5; # 分页思路错误：因为此时要进行分页的原记录和不分页时的记录并不一致，即要进行分页的原记录集并不是同一个，其分页数据也会出错
select id,score from t_player where id > 8 order by score limit 5; # 虽然要分页的原记录集一致，但变相加了个id检索条件，显然分页数据是错误的

​ 因此要确保原分页数据的一致性，则必须限定按照score排序，但是如果基于score排序就会打乱id的排序规则，那么此处根据id>max(id)这个条件限定查找的记录就失去了意义，因为它相当于变相无缘无故加了个id检索条件

​ 那么如何正常的使用这种业务分页的设计思路呢？结合上述案例分析，这种方案只能限定要设定的分页序号字段必须为唯一（主键/唯一字段、或者组合的字段必须唯一）来进行分页：

• 通过限定排序规则可以确保原始的分页记录一致（即要分页的记录确定下来不会变），进而确保记录的有序性 （如果不显式指定order by的话，序号本身有序也是可以的）
• 通过限定排序字段的唯一性、有序性，避免上述场景中分页数据错误问题
  • 唯一性用于避免可能存在的因排序字段的数据重复导致的分页数据丢失或者重复读取等问题
  • 有序性则用于保证id>max(id)的可行性

​ 这种设计思路可以参考oracle的伪列设计思想，可以理解为要进行分页的记录集按顺序分派序号（伪列），然后根据这个序号（有序）进行分页。

​ 例如案例中：id为自增主键是满足这种场景的。如果使用的是uuid（则需通过order by id 来进行限定以确保记录的有序性）

# id为自增主键，本身有序
select id,score from t_player order by id asc;
select id,score from t_player order by id asc limit 5; # 第1页 最后一条记录为
select id,score from t_player order by id asc where id > 8 limit 5; # 

场景问题3：为什么一定要order by？

​ order by 子句在此处的作用用于确保分页的原始记录集的有序性。此处思考一个问题，id既然为自增主键，那这个order by id还有必要加进去吗？可以看下面的示例来思考这个问题

​ 这点要回归SQL的执行过程进行拆解，结合测试结果可以看到在不指定order by id子句的场景下，两个语句得到的记录顺序完全不同，主要归咎于其执行过程中MySQL查询优化器的选择。

​ select * from t_player=>MySQL选择全表扫描，因此得到的这个分页结果集是按照表中的记录顺序读取的，因此看起来是有序的（如果表中记录无序则遍历得到的也是以表记录顺序为参考）

​ select id,score from t_player=>MySQL选择走idx_score索引，因此得到图示中的记录顺序（这个顺序是索引顺序，即按照score排序得到的），但是此时最终得到的分页记录中id是无序的（如果按照这个版本做分页，那么就会变成上面的【场景问题2-分页序号字段（此处为id）无序导致分页数据错误】）

​ 综上所述，通过显示限定order by 分页序号字段来确保记录的有序性，从而规避上面的场景问题

​ 这种方案可以总结为如下：

# 选定一个字段作为【分页序号字段】（类似oracle的伪列概念），该字段在分页的结果集中必须确保唯一性，则其可以采用如下思路进行分页优化

-- 记录score为prev_max_sort_num (第一页查找N条记录)
select [sort_num] from t_player order by [sort_num] limit N;
-- 下一页(从score的下一条记录开始查找)
select [sort_num] from t_player where [sort_num] > [prev_max_sort_num] order by [sort_num] limit N;
.... 以此类推 ....

​ 这个【分页序号字段】的选择在这个场景中只需要确保唯一性，通过上述格式语句规范来严格限定分页规则和标准即可

# 类似oracle的伪列设计思路：
- 此处t为要进行分页的原始结果集，它可以简单也可以复杂)
- nt是一个新表，它是由t表的每一行记录和对应的rowid组合的记录结果集（其中rowid是唯一的）
select 
from (
	select rowid,t.*
	from t
)as nt

-- 对nt表进行分页优化：
# 第1页
select rowid from nt order by nt limit N;
# 下一页
select rowid from nt where rowid > prev_max_rowid order by nt limit N;

- 可以看到此处oracle的rowid即为要指定的分页排序字段，即对照前面的sort_num

优化方式2：减少回表（对于使用二级索引进行分页的场景）

​ select * from t_player order by score offset 10000 limit 20

​ 根据场景分析limit 100000,20，越到后面的分页检索效率越低的原因在于偏移量越来越大，不可避免的回表次数越来越多，就会造成性能下降。换个角度思考，可否通过拆分SQL的方式来进行优化？因为语句中的前100000条记录实际上都是没必要进行回表，可以考虑以最小成本的方式去处理，拆分SQL：

select * from t_player where id in (select id from t_player order by score offset 10000 limit 20)

• 【1】先筛选出真正要获取的分页记录ID（例如此处获取20条数据记录ID：select id from t_player order by score offset 10000 limit 20）
• 【2】然后根据这些记录ID再回表查询数据获取到完整的记录

​ 基于上述步骤，检索语句offset部分不需要反查询聚簇索引(因为score二级索引上就有主键信息)，只有limit出来的10个主键ID才会去查询聚簇索引，只会进行1次IO

😒全表检索场景（此处是错误的案例解析方向，由于走全表则无关回表概念，就会让优化方案执行效果看起来像是无效一样）

# 开启show profile 跟踪SQL语句的执行成本
show VARIABLES like '%profil%';
set profiling = ON;

# 以前面创建的s1表为例
select * from s1 limit 9800,20;
select * from s1 limit 20 offset 9800;

select * from s1 where id in (select id from s1 limit 20 offset 9800); // MySQL 5.7.44和8.0.21 都不允许这种语法
-- 提示错误：[Err] 1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' 的意思是，这版本的 MySQL 不支持使用 LIMIT 子句

- 解决方案（再多嵌套一层）
select * from s1 where id in (select id from (select id from s1 limit 20 offset 9800)as t);

# 执行上述语句后执行show profiles查看SQL执行成本
show profiles;   

​ 参考全表记录为1W，MySQL 不同版本版本执行分析如下：

​ 测试100w的数据场景下的执行效果：

# 以创建的t_limit表为例（表结构和s1一致）
select * from t_limit limit 800000,20;
select * from t_limit limit 20 offset 800000;
select * from t_limit where id in (select id from (select id from t_limit limit 20 offset 800000)as t);
show profiles;  

-- 100w 数据测试
select * from t_limit limit 100,20;
select * from t_limit limit 20 offset 100;

# 方案1：减少回表
select * from t_limit where id in (select id from (select id from t_limit limit 20 offset 100)as t);

# 方案2：id > max(id)的构建思路（但必须满足id有序）
select * from t_limit where id > 100 limit 20;
show profiles;  

​ 调整 offset 参数，分别确认不同 offset 下分页的执行效率：

​ limit m,n与limit n offset m执行原理是一样的，都是跳过m条数据读取n条数据，但是有时候执行的时候会发现部分情况下这两种方式的耗时会不太相同，但实际上误差范围内可以忽略不记（不免会有其他因素影响执行）

​ 方案1中通过筛选分页的ID然后进行检索，这种方案的执行效率随着偏移量的增大并没有得到多大的提升

​ 方案2中基于"下一页"的分页思路，始终将耗时控制在一个比较持衡的水平（毫秒级别）

📣基于二级索引的回表场景案例：据此可以非常清晰看到大数据场景中优化方案的效率提升

还是基于t_limit（key1是二级普通索引，100w数据量）、t_user_batch（account是二级普通索引，5000w数据量）进行测试

• 正常基于二级索引构建检索：会发现MySQL优化器为limit、offset limit语句选择执行计划还是全表扫描，但从测试结果中可以看到，优化方案有显著的效率提升

  # 100w数据测试
  select * from t_limit order by key1 limit 950000,20;
  select * from t_limit order by key1 limit 20 offset 950000;
  select * from t_limit where id in (select id from (select id from t_limit order by key1 limit 20 offset 950000)as t);
  show profiles; 
  
  # 5000w数据测试
  select * from t_user_batch order by account  limit 20000000,20;
  select * from t_user_batch order by account limit 20 offset 20000000;
  select * from t_user_batch where uid in (select uid from (select uid from t_user_batch order by account limit 20 offset 20000000)as t);
  show profiles;  
  

• 明确二级索引案例构建

​ 基于上述案例，试着用二级索引构建检索，查询效率虽然有大幅提高，但通过explain 查看语句，发现目前的案例中对于没有优化前的 limit和 offset limit 语句，MYSQL优化器为其选择的执行计划还是全表扫描，但优化后的语句确实是有提升的。为了排除索引和全表扫描对测试结果的影响，补充了测试了一下能够走二级索引的案例，这种优化方案的效率将原来的检索提到了不到 1s就能完成

# 100w数据测试（先通过explain查看语句是否走索引，然后确认这个语句执行可能会有多少条记录，然后选择合适的limit offset参数，确保可以查到数据）
select * from t_limit where key1 like 'f%' order by key1  limit 60000,20;
select * from t_limit where key1 like 'f%' order by key1 limit 20 offset 60000;
select * from t_limit where id in (select id from (select id from t_limit where key1 like 'f%' order by key1 limit 20 offset 60000)as t);
show profiles;  

explain select * from t_limit;
explain select * from t_limit where key1 like 'f%' order by key1  limit 60000,20;
explain select * from t_limit where key1 like 'f%' order by key1 limit 20 offset 60000;
explain select * from t_limit where id in (select id from (select id from t_limit where key1 like 'f%' order by key1 limit 20 offset 60000)as t);

# 5000w数据测试（先通过explain查看语句是否走索引，然后确认这个语句执行可能会有多少条记录，然后选择合适的limit offset参数，确保可以查到数据）
# 确认语句是否走idx 确认大概的数据量和分页参数
explain select account from t_user_batch where account like 'z%';
explain select * from t_user_batch where account like 'z%' limit 1500000,20;
explain select * from t_user_batch where account like 'z%' limit 20 offset 1500000;
explain select * from t_user_batch where uid in (select uid from (select uid from t_user_batch where account like 'z%' limit 20 offset 1500000)as t);

select * from t_user_batch where account like 'z%' order by account desc limit 1500000,20;
select * from t_user_batch where account like 'z%' order by account desc limit 20 offset 1500000;
select * from t_user_batch where uid in (select uid from (select uid from t_user_batch where account like 'z%' order by account desc limit 20 offset 1500000)as t);
show profiles;  

扩展思考：为什么MySQL不直接丢掉无用的数据呢？

​ 在"减少回表"这个优化方向上会联想到索引覆盖、索引下推，由于此处是select *因此要获取到完整的数据记录不免要进行回表，上述方案2的思路就是利用索引覆盖摘取到要获取的分页记录，然后只对分页记录进行回表。还有一种是索引下推（MySQL5.6之后提供），在遍历索引的时候不直接回表，而是根据现有索引的条件过滤数据，进而达到减少回表次数的目的。

​ 因此可能会想，为什么MySQL不选择直接丢掉offset记录呢？其实可以理解为offset并未下推，可以先review一下查找流程：

• 存储引擎通过二级索引查找，获取主键值；
• 进行回表操作，将完整记录返回给上层；
• 上层判断是否需要该记录，需要则返回给客户端，不需要则跳过该记录；
• 存储引擎接着查找下一条；
• 重复第二步

​ 可以看到存储引擎层是没有offset信息，可以从两方面理解MySQL为什么不做？

• 限制场景太多：因为要考虑场景的通用性和多个引擎的兼容性，如果考虑不完善的话，将会是一个灾难性的定义
• 分层涉及里面：这件事本身是sql层的，不该由存储引擎做

​ 但很多自研DB会根据自己的业务逻辑来扩展存储引擎实现，例如阿里云、腾讯云等

减少锁持有的时间

案例1：调整事务的SQL执行顺序

# 先执行更新后执行查询
begin;
update ......;
select ......;
commit;

# 先执行查询后执行更新
begin;
select ......;
update ......;
commit;

​ 执行update语句的时候会对记录加next-key锁（行级锁），这个锁是在事务提交之后才释放的，如果update和select语句之间没有什么依赖关系，则应考虑将加锁操作的语句放在事务靠后的位置，进而检索加锁时间，以提升整体的并发性能。

​ 例如在压测系统的某个功能时，发现接口并发能力不太行，通过排查代码发现存在一个事务，这个事务涉及到多个sql语句操作，但是事务一开始就先执行了update语句，于是进一步确认update语句和select语句这些sql之间有没有涉及到相互依赖，如果没有则可通过调整事务的SQL执行顺序，将update语句放在事务靠后的位置，以减少事务持有锁的时间，调整后接口的并发性能也就上来了

​ 场景分析：假设要实现一个电影票在线交易业务，顾客 A要在影院 B 购买电影票。简化一下这个业务需要涉及到以下操作：

• 【1】从顾客 A 账户余额中扣除电影票价；（update）
• 【2】给影院 B 的账户余额增加这张电影票价；（update）
• 【3】记录一条交易日志；（insert）

​ 即要完成这个交易需要 update 两条记录，并 insert 一条记录。为了保证交易的原子性要把这三个操作放在一个事务，思考你会怎样安排这三个语句在事务中的顺序呢?

​ 正常业务逻辑下一般开发会按照业务顺序进行定义，以此来帮助理解业务。但是如果接口并发场景中出现性能低下的问题时，则需要考虑如何进一步优化接口来提升性能

​ 试想如果同时有另外一个顾客 C 要在影院 B 买票，那么这两个事务冲突的部分就是语句 2了。因为它们要更新同一个影院账户的余额，需要修改同一行数据。根据两阶段锁协议，不论怎样安排语句顺序，所有的操作需要的行锁都是在事务提交的时候才释放的。所以，如果把语句 2 安排在最后，比如按照 3、1、2 这样的顺序，那么影院账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待，提升了并发度。

案例2：分批删除

​ 删除大量数据表数据的时候，最好采用分批删除的方式，如果直接执行删除操作 ，那么delete 语句产生的行锁,要在所有数据删除完之后，才会释放锁，锁持有的时间会很长，会影响其他事务的操作。

​ 改进方式：采用 limit 的方式来分批删除，比如每次取 1000 条记录进行删除，这也可以减少锁持有的时间

场景1：连续的范围删除场景

​ 场景分析：有个登录表，登录游戏玩家每次登录的信息，久而久之，这几个表的数据量达到了两亿多条。每天都在上报，采集，由于没有定期删除，数据大量累积。大概有一年左右的数据，一个表的数据已经达到亿级别的。这样算下来，一个表的数据至少是几十GB了。因此需要删除过期的数据，暂时保留近三个月的统计数据。delete from table_name where create_time < '2017-04-06'

​ 但是在执行语句的时候出现问题：The total number of locks exceeds the lock table size in MySQL

​ 解决方案：

• 思路1：索引 =》确认是否在create_time 上创建了索引？建议通过ID删除（按照ID删除的速度和按照二级索引列删除的速度不是一个数量级别的）

• 思路2：分批 =》通过limit 方式进行分批删除

  # 找出符合条件的create_time和collect_time的最大ID
  select max(id) from table_name where create_time < '2017-04-06'; # 千万左右的数据大概需要10多秒
  
  # 接着按id删除，一次删除10k，循环删除
  delete from table_name where id < maxId limit 10000 # 此处id是有序的，以此确保可通过这种范围条件限制正确删除数据（参考之前的分页场景）
  

  基于上述思路：通过分析删除场景，从索引、分批两方面切入删除操作，进而提升删除效率并有效避免大事务问题及减少事务持有锁的时间

场景2：不连续的范围删除场景

​ 场景分析：如果要删除的数据范围是不连续的数据ID这种情况，需要通过前端批量传入ID来进行删除，例如delete from table_name where id in (1,5,16,92,158...54689);这种形式，如果需要删除的ID多达10000个

​ 解决方案：基于这种场景只能通过减少批量删除的ID、增加分批删除的次数来进行优化（例如前端限制传入ID个数，或者在后台对前端传入的ID进行分批删除处理）

​

（4）覆盖索引 与 索引下推 （升级版本的优化方向）

​ 覆盖索引和索引下推的概念在【MySQL-原理篇-索引】的学习中已经学习，此处简单复盘下相应的内容：

• 覆盖索引：通过二级索引的B+Tree就能查询到结果的过程称为【覆盖索引】，避免了回表过程，以提升了查询效率
• 索引下推：在遍历二级索引的B+Tree时，不着急回表，而是通过条件过滤（可以使用二级索引的数据进行匹配）优先过滤掉一些不满足的数据，只有满足条件才会进行回表操作，进而减少回表次数，提升查询效率

​ 覆盖索引和索引下推一般较常用于组合索引的场景中，因此可以结合组合索引的应用进行拆解分析，也需理解单索引的分析

（5）其他查询优化策略

5.连接池优化

数据库连接池核心

​ 核心：连接复用、连接管理

​ 数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个；释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏。这项技术能明显提高对数据库操作的性能

​ 数据库连接是一种关键的有限的昂贵的资源，这一点在多用户的网页应用程序中体现得尤为突出。 一个数据库连接对象均对应一个物理数据库连接，每次操作都打开一个物理连接，使用完都关闭连接，这样造成系统的 性能低下。 数据库连接池的解决方案是在应用程序启动时建立足够的数据库连接，并讲这些连接组成一个连接池(简单说：在一个“池”里放了好多半成品的数据库联接对象)，由应用程序动态地对池中的连接进行申请、使用和释放。对于多于连接池中连接数的并发请求，应该在请求队列中排队等待。并且应用程序可以根据池中连接的使用率，动态增加或减少池中的连接数。 连接池技术尽可能多地重用了消耗内存地资源，大大节省了内存，提高了服务器地服务效率，能够支持更多的客户服务。通过使用连接池，将大大提高程序运行效率，同时，可以通过其自身的管理机制来监视数据库连接的数量、使用情况等

不使用数据库库连接池：实现简单，但是频繁创建和销毁数据库连接占据一定的资源

• TCP建立连接的3次握手
• MySQL认证的3次握手
• SQL执行
• MySQL关闭连接
• TCP的四次握手关闭

使用数据库连接池：通过复用连接避免了重复创建和销毁的开销（减少网络开销等），提升系统性能

​ 只有第一次访问的时候，需要建立连接。 但是之后的访问，均会复用之前创建的连接，直接执行 SQL 语句。

数据库连接池如何工作

​ 数据库连接池工作的核心在于：创建连接池、使用/管理连接池、关闭连接池

• 创建连接池：与线程池等池化对象类似，数据库连接池会在进程启动之初，根据配置初始化，并在池中创建了几个连接对象，以便使用时能从连接池中获取。连接池中的连接不能随意创建和关闭，以避免创建、关闭所带来的系统开销。

• 使用、管理连接池中：连接池管理策略是连接池机制的核心，连接池内连接的分配和释放对系统的性能有很大的影响。合理的策略可以保证数据库连接的有效复用，避免频繁的建立、释放连接所带来的系统资源开销。通常，数据库连接池的管理策略如下：

  • 当请求数据库连接时，首先查看连接池中是否有空闲连接
  • 如果存在空闲连接，则将连接分配给客户使用
  • 如果没有空闲连接，则查看当前所开的连接数是否已经达到最大连接数。若未达到，就重新创建一个连接，并分配给请求的客户；如果达到，就按设定的最大等待时间进行等待，若超出最大等待时间，则抛出异常给客户
  • 当客户释放数据库连接时，先判断该连接的引用次数是否超过了规定值。如果超过，就从连接池中删除该连接；否则保留为其他客户服务。

• 关闭连接池：当应用程序退出时，关闭连接池中所有的连接，释放连接池相关的资源，该过程正好与创建相反

数据库连接池核心参数

• 最小连接数：是连接池一直保持的数据库连接，所以如果应用程序对数据库连接的使用量不大，将会有大量的数据库连接资源被浪费.
• 最大连接数：是连接池能申请的最大连接数，如果数据库连接请求超过次数，后面的数据库连接请求将被加入到等待队列中，这会影响以后的数据库操作
• 最大空闲时间
• 获取连接超时时间
• 超时重试连接次数

数据连接池问题

• 并发问题：为了保证连接管理服务具有最大的通用性，必须考虑多线程环境，即并发问题

• 事务处理：事务具有原子性，此时要求对数据库的操作符合“ALL-OR-NOTHING”原则,即对于一组 SQL 语句要么全做，要么全不做。当 2 个线程共用一个连接 Connection 对象，而且各自都有自己的事务要处理时候，对于连接池是一个很头疼的问题，因为即使 Connection 类提供了相应的事务支持，可是仍然不能确定那个数据库操作是对应那个事务的，这是由于有２个线程都在进行事务操作而引起的。为此可以使用每一个事务独占一个连接来实现，虽然这种方法有点浪费连接池资源但是可以大大降低事务管理的复杂性

• 连接池的分配与释放：连接池的分配与释放，对系统的性能有很大的影响。合理的分配与释放，可以提高连接的复用度，从而降低建立新连接的开销，同时还可以加快用户的访问速度。 对于连接的管理可使用一个 List。即把已经创建的连接都放入 List 中去统一管理。每当用户请求一个连接时，系统检查这个 List 中有没有可以分配的连接。如果有就把那个最合适的连接分配给他；如果没有就抛出一个异常给用户。

• 连接池的配置与维护：

  • 连接池中到底应该放置多少连接，才能使系统的性能最佳？系统可采取设置最小连接数（minConnection）和最大连接数（maxConnection）等参数来控制连接池中的连接。

    • 最小连接数是系统启动时连接池所创建的连接数

      • 如果创建过多，则系统启动就慢，但创建后系统的响应速度会很快；
      • 如果创建过少，则系统启动的很快，响应起来却慢
      • 可以在开发时设置较小的最小连接数（提升开发效率），而在系统实际使用时设置较大的（对于用户而言，提升用户访问速度）

    • 最大连接数是连接池中允许连接的最大数目，具体设置多少，要看系统的访问量，可通过软件需求上得到

  • 如何确保连接池中的最小连接数呢？有动态和静态两种策略

    • 动态即每隔一定时间就对连接池进行检测，如果发现连接数量小于最小连接数，则补充相应数量的新连接，以保证连接池的正常运转
    • 静态是发现空闲连接不够时再去检查

数据库连接池技术选型

常见的数据库连接池：

• HikariCP (opens new window)：HiKariCP 号称是跑的最快的连接池，并且是 SpringBoot 框架的默认连接池。
• Druid (opens new window)：Druid 是阿里巴巴开源的数据库连接池。Druid 内置强大的监控功能，监控特性不影响性能。功能强大，能防 SQL 注入，内置 Loging 能诊断 Hack 应用行为。
• DBCP (opens new window)： 由 Apache 开发的一个 Java 数据库连接池。commons-dbcp2 基于 commons-pool2 来实现底层的对象池机制。单线程，性能较差，适用于小型系统。官方自 2021 年后没有再更新。
• C3P0 (opens new window)：开源的 JDBC 连接池，实现了数据源和 JNDI 绑定，支持 JDBC3 规范和 JDBC2 的标准扩展。单线程，性能较差，适用于小型系统。官方自 2019 年后再没有更新。
• Tomcat-jdbc：Tomcat 在 7.0 以前使用 DBCP 做为连接池组件，从 7.0 后新增了 Tomcat jdbc pool 模块，基于 Tomcat JULI，使用 Tomcat 日志框架，完全兼容 dbcp，通过异步方式获取连接，支持高并发应用环境，超级简单核心文件只有 8 个，支持 JMX，支持 XA Connection

功能类别	功能	Druid	HikariCP	DBCP	Tomcat-jdbc	C3P0
性能	PSCache	是	否	是	是	是
LRU	是	否	是	是	是
SLB 负载均衡支持	是	否	否	否	否
稳定性	ExceptionSorter	是	否	否	否	否
扩展	扩展	Filter			JdbcIntercepter
监控	监控方式	jmx/log/http	jmx/metrics	jmx	jmx	jmx
支持 SQL 级监控	是	否	否	否	否
Spring/Web 关联监控	是	否	否	否	否
	诊断支持	LogFilter	否	否	否	否
连接泄露诊断	logAbandoned	否	否	否	否
安全	SQL 防注入	是	无	无	无	无
支持配置加密	是	否	否	否	否

​ 从数据库连接池最重要的性能角度来看：HikariCP 应该性能最好；Druid 也不错，并且有更多、更久的生产实践，更为可靠；而其他常见的数据库连接池性能远远不如。

​ 从功能角度来看：Druid 功能最全面，除基本的数据库连接池能力以外，还支持 sql 级监控、扩展、SQL 防注入以及监控等功能。

​ 综合来看：HikariCP 是 Spring Boot 首选数据库连接池，对于 Spring Boot 项目来说，无疑适配性最好。而非 Spring Boot 项目，可以优先考虑 Druid，在国内有大规模应用，中文社区支持良好

（1）HikariCP

HiKariCP 号称是跑的最快的连接池，并且是 SpringBoot 框架的默认连接池。

HiKariCP 为了提升性能，做了很多细节上的优化，例如：

• 使用 FastList 替代 ArrayList，通过初始化的默认值，减少了越界检查的操作
• 优化并精简了字节码，通过使用 Javassist，减少了动态代理的性能损耗，比如使用 invokestatic 指令代替 invokevirtual 指令
• 实现了无锁的 ConcurrentBag，减少了并发场景下的锁竞争

HikariCP 关键配置：

• maximum-pool-size：池中最大连接数（包括空闲和正在使用的连接）。默认值是 10，这个一般预估应用的最大连接数，后期根据监测得到一个最大值的一个平均值。要知道，最大连接并不是越多越好，一个 connection 会占用系统的带宽和存储。但是 当连接池没有空闲连接并且已经到达最大值，新来的连接池请求（HikariPool#getConnection）会被阻塞直到connectionTimeout（毫秒），超时后便抛出 SQLException。
• minimum-idle：池中最小空闲连接数量。默认值 10，小于池中最大连接数，一般根据系统大部分情况下的数据库连接情况取一个平均值。Hikari 会尽可能、尽快地将空闲连接数维持在这个数量上。如果为了获得最佳性能和对峰值需求的响应能力，我们也不妨让他和最大连接数保持一致，使得 HikariCP 成为一个固定大小的数据库连接池。
• connection-timeout：连接超时时间。默认值为 30s，可以接收的最小超时时间为 250ms。但是连接池请求也可以自定义超时时间（com.zaxxer.hikari.pool.HikariPool#getConnection(long)）。
• idle-timeout：空闲连接存活最大时间，默认 600000（十分钟）
• max-lifetime：连接池中连接的最大生命周期。当连接一致处于闲置状态时，超过 8 小时数据库会主动断开连接。为了防止大量的同一时间处于空闲连接因为数据库方的闲置超时策略断开连接（可以理解为连接雪崩），一般将这个值设置的比数据库的“闲置超时时间”小几秒，以便这些连接断开后，HikariCP 能迅速的创建新一轮的连接。
• pool-name：连接池的名字。一般会出现在日志和 JMX 控制台中。默认值：auto-genenrated。建议取一个合适的名字，便于监控。
• auto-commit：是否自动提交池中返回的连接。默认值为 true。一般是有必要自动提交上一个连接中的事物的。如果为 false，那么就需要应用层手动提交事物。

参考配置：

# 连接池名称
spring.datasource.hikari.pool-name = SpringTutorialHikariPool
# 最大连接数，小于等于 0 会被重置为默认值 10；大于零小于 1 会被重置为 minimum-idle 的值
spring.datasource.hikari.maximum-pool-size = 10
# 最小空闲连接，默认值10，小于 0 或大于 maximum-pool-size，都会重置为 maximum-pool-size
spring.datasource.hikari.minimum-idle = 10
# 连接超时时间（单位：毫秒），小于 250 毫秒，会被重置为默认值 30 秒
spring.datasource.hikari.connection-timeout = 60000
# 空闲连接超时时间，默认值 600000（10分钟），大于等于 max-lifetime 且 max-lifetime>0，会被重置为0；不等于 0 且小于 10 秒，会被重置为 10 秒
# 只有空闲连接数大于最大连接数且空闲时间超过该值，才会被释放
spring.datasource.hikari.idle-timeout = 600000
# 连接最大存活时间，不等于 0 且小于 30 秒，会被重置为默认值 30 分钟。该值应该比数据库所设置的超时时间短
spring.datasource.hikari.max-lifetime = 1800000

（2）Druid

Druid 是阿里巴巴开源的数据库连接池。Druid 连接池为监控而生，内置强大的监控功能，监控特性不影响性能。功能强大，能防 SQL 注入，内置 Loging 能诊断 Hack 应用行为。

Druid 关键配置：

# 数据库访问配置
# 主数据源，默认的
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/druid
spring.datasource.username=root
spring.datasource.password=root

# 下面为连接池的补充设置，应用到上面所有数据源中
# 初始化大小，最小，最大
spring.datasource.initialSize=5
spring.datasource.minIdle=5
spring.datasource.maxActive=20
# 配置获取连接等待超时的时间
spring.datasource.maxWait=60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.datasource.timeBetweenEvictionRunsMillis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
spring.datasource.minEvictableIdleTimeMillis=300000
spring.datasource.validationQuery=SELECT 1 FROM DUAL
spring.datasource.testWhileIdle=true
spring.datasource.testOnBorrow=false
spring.datasource.testOnReturn=false
# 打开PSCache，并且指定每个连接上PSCache的大小
spring.datasource.poolPreparedStatements=true
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
spring.datasource.filters=stat,wall,log4j
# 通过connectProperties属性来打开mergeSql功能；慢SQL记录
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# 合并多个DruidDataSource的监控数据
#spring.datasource.useGlobalDataSourceStat=true

6.性能优化

硬件配置

​ 现在基本上都是使用云服务器，就会涉及服务器配置选型，对于数据库处理复杂 SQL 而言，尽量选择高频 CPU，而且数据库一般都会开辟缓存池来存放数据，所以在服务器选型的时候内存大小也需要考虑。一般来说数据库服务器的硬件配置的重要性高于应用服务器配置，这方面了解下即可，测试工作基本上不会涉及数据库服务器的选型，而且一旦选型固定之后不会轻易改变数据库的硬件配置

MySQL系统配置选项

（1）max_connections

​ max_connections：MySQL可以接收到的最大连接数

（2）

todo：https://juejin.cn/post/7163894728201601060#heading-12

https://learn.lianglianglee.com/%e4%b8%93%e6%a0%8f/%e8%af%b4%e9%80%8f%e6%80%a7%e8%83%bd%e6%b5%8b%e8%af%95/18%20%20%e5%a6%82%e4%bd%95%e6%89%8d%e8%83%bd%e4%bc%98%e5%8c%96%20MySQL%20%e6%80%a7%e8%83%bd%ef%bc%9f.md

http://121.43.36.222:5244/%E8%B5%84%E6%BA%90/14-%E6%9E%81%E5%AE%A2%E6%97%B6%E9%97%B4/%E4%B8%93%E6%A0%8F%E8%AF%BE/2024/230%E5%90%8E%E7%AB%AF%E5%B7%A5%E7%A8%8B%E5%B8%88%E7%9A%84%E9%AB%98%E9%98%B6%E9%9D%A2%E7%BB%8F/21%EF%BD%9C%E6%95%B0%E6%8D%AE%E5%BA%93%E7%BB%BC%E5%90%88%E5%BA%94%E7%94%A8%EF%BC%9A%E6%80%8E%E4%B9%88%E4%BF%9D%E8%AF%81%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9A%84%E9%AB%98%E5%8F%AF%E7%94%A8%E3%80%81%E9%AB%98%E6%80%A7%E8%83%BD%EF%BC%9F.md

• todo：面试题
• 基础篇“https://mp.weixin.qq.com/s?__biz=Mzg5ODU2ODczMQ==&mid=2247486128&idx=1&sn=afbd3ca37f4727db9d32460f98e73d5a&chksm=c061cdc4f71644d228cad54d6a8395e27a46a9540f219a4f3776e5aa023608d131f9ec70b687#rd
• 原理篇：https://mp.weixin.qq.com/s?__biz=Mzg5ODU2ODczMQ==&mid=2247487888&idx=1&sn=973635eeaf7d1916e62bbceda72d27bd&chksm=c061d6e4f7165ff271ee4bfc71de630c6538802b1d49c747bc0eb0be9264ae7619ed0648c47c#rd
• 性能篇：https://mp.weixin.qq.com/s?__biz=Mzg5ODU2ODczMQ==&mid=2247494731&idx=1&sn=e1bb64dd33c008cf46d5995044589228&chksm=c0622b3ff715a229ef1831b468eb47b07aabd1edfb87637bf5c45408418dd37e3cf4ee9eca2e&scene=178&cur_album_id=2225658380164055048#rd