MySQL - 优化

17.8k
MySQL / 优化篇
SQL

目录指引：

表优化 ​

分区表 ​

基本介绍 ​

分区表是将大表的数据按分区字段分成许多小的子集，建立一个以 ftime 年份为分区的表：

sql
CREATE TABLE `t` (
    `ftime` datetime NOT NULL,
    `c` int(11) DEFAULT NULL,
    KEY (`ftime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE (YEAR(ftime))
(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
 PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
 PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
 PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
INSERT INTO t VALUES('2017-4-1',1),('2018-4-1',1);-- 这两行记录分别落在 p_2018 和 p_2019 这两个分区上

这个表包含了一个.frm 文件和 4 个.ibd 文件，每个分区对应一个.ibd 文件

  • 对于引擎层来说，这是 4 个表，针对每个分区表的操作不会相互影响
  • 对于 Server 层来说，这是 1 个表

分区策略 ​

打开表行为：第一次访问一个分区表时，MySQL 需要把所有的分区都访问一遍，如果分区表的数量很多，超过了 open_files_limit 参数（默认值 1024），那么就会在访问这个表时打开所有的文件，导致打开表文件的个数超过了上限而报错

通用分区策略：MyISAM 分区表使用的分区策略，每次访问分区都由 Server 层控制，在文件管理、表管理的实现上很粗糙，因此有比较严重的性能问题

本地分区策略：从 MySQL 5.7.9 开始，InnoDB 引擎内部自己管理打开分区的行为，InnoDB 引擎打开文件超过 innodb_open_files 时就会关掉一些之前打开的文件，所以即使分区个数大于 open_files_limit，也不会报错

从 MySQL 8.0 版本开始，就不允许创建 MyISAM 分区表，只允许创建已经实现了本地分区策略的引擎，目前只有 InnoDB 和 NDB 这两个引擎支持了本地分区策略

Server 层 ​

从 Server 层看一个分区表就只是一个表

  • Session A：

    sql
    SELECT * FROM t WHERE ftime = '2018-4-1';

  • Session B：

    sql
    ALTER TABLE t TRUNCATE PARTITION p_2017; -- blocked

现象：Session B 只操作 p_2017 分区，但是由于 Session A 持有整个表 t 的 MDL 读锁，就导致 B 的 ALTER 语句获取 MDL 写锁阻塞

分区表的特点：

  • 第一次访问的时候需要访问所有分区
  • 在 Server 层认为这是同一张表，因此所有分区共用同一个 MDL 锁
  • 在引擎层认为这是不同的表，因此 MDL 锁之后的执行过程，会根据分区表规则，只访问需要的分区

应用场景 ​

分区表的优点：

  • 对业务透明，相对于用户分表来说，使用分区表的业务代码更简洁

  • 分区表可以很方便的清理历史数据。按照时间分区的分区表，就可以直接通过 alter table t drop partition 这个语法直接删除分区文件，从而删掉过期的历史数据，与使用 drop 语句删除数据相比，优势是速度快、对系统影响小

使用分区表，不建议创建太多的分区，注意事项：

  • 分区并不是越细越好，单表或者单分区的数据一千万行，只要没有特别大的索引，对于现在的硬件能力来说都已经是小表
  • 分区不要提前预留太多，在使用之前预先创建即可。比如是按月分区，每年年底时再把下一年度的 12 个新分区创建上即可，并且对于没有数据的历史分区，要及时的 drop 掉

参考文档：https://time.geekbang.org/column/article/82560

临时表 ​

基本介绍 ​

临时表分为内部临时表和用户临时表

  • 内部临时表：系统执行 SQL 语句优化时产生的表，例如 Join 连接查询、去重查询等

  • 用户临时表：用户主动创建的临时表

    sql
    CREATE TEMPORARY TABLE temp_t like table_1;

临时表可以是内存表，也可以是磁盘表（多表操作 → 嵌套查询章节提及）

  • 内存表指的是使用 Memory 引擎的表，建立哈希索引，建表语法是 create table … engine=memory，这种表的数据都保存在内存里，系统重启时会被清空，但是表结构还在
  • 磁盘表是使用 InnoDB 引擎或者 MyISAM 引擎的临时表，建立 B+ 树索引，写数据的时候是写到磁盘上的

临时表的特点：

  • 一个临时表只能被创建它的 session 访问，对其他线程不可见，所以不同 session 的临时表是可以重名的
  • 临时表可以与普通表同名，会话内有同名的临时表和普通表时，执行 show create 语句以及增删改查语句访问的都是临时表
  • show tables 命令不显示临时表
  • 数据库发生异常重启不需要担心数据删除问题，临时表会自动回收

重名原理 ​

执行创建临时表的 SQL：

sql
create temporary table temp_t(id int primary key)engine=innodb;

MySQL 给 InnoDB 表创建一个 frm 文件保存表结构定义，在 ibd 保存表数据。frm 文件放在临时文件目录下，文件名的后缀是 .frm，前缀是 #sql{进程 id}_{线程 id}_ 序列号，使用 select @@tmpdir 命令，来显示实例的临时文件目录

MySQL 维护数据表，除了物理磁盘上的文件外，内存里也有一套机制区别不同的表，每个表都对应一个 table_def_key

  • 一个普通表的 table_def_key 的值是由 库名 + 表名 得到的，所以如果在同一个库下创建两个同名的普通表，创建第二个表的过程中就会发现 table_def_key 已经存在了
  • 对于临时表，table_def_key 在 库名 + 表名 基础上，又加入了 server_id + thread_id，所以不同线程之间，临时表可以重名

实现原理：每个线程都维护了自己的临时表链表，每次 session 内操作表时，先遍历链表，检查是否有这个名字的临时表，如果有就优先操作临时表，如果没有再操作普通表；在 session 结束时对链表里的每个临时表，执行 DROP TEMPORARY TABLE + 表名 操作

执行 rename table 语句无法修改临时表，因为会按照 库名 / 表名.frm 的规则去磁盘找文件，但是临时表文件名的规则是 #sql{进程 id}_{线程 id}_ 序列号.frm，因此会报找不到文件名的错误

主备复制 ​

创建临时表的语句会传到备库执行，因此备库的同步线程就会创建这个临时表。主库在线程退出时会自动删除临时表，但备库同步线程是持续在运行的并不会退出，所以这时就需要在主库上再写一个 DROP TEMPORARY TABLE 传给备库执行

binlog 日志写入规则：

  • binlog_format=row，跟临时表有关的语句就不会记录到 binlog
  • binlog_format=statment/mixed，binlog 中才会记录临时表的操作，也就会记录 DROP TEMPORARY TABLE 这条命令

主库上不同的线程创建同名的临时表是不冲突的，但是备库只有一个执行线程，所以 MySQL 在记录 binlog 时会把主库执行这个语句的线程 id 写到 binlog 中，在备库的应用线程就可以获取执行每个语句的主库线程 id，并利用这个线程 id 来构造临时表的 table_def_key

  • session A 的临时表 t1，在备库的 table_def_key 就是：库名 + t1 +“M 的 serverid" + "session A 的 thread_id”
  • session B 的临时表 t1，在备库的 table_def_key 就是 ：库名 + t1 +"M 的 serverid" + "session B 的 thread_id"

MySQL 在记录 binlog 的时不论是 create table 还是 alter table 语句都是原样记录，但是如果执行 drop table，系统记录 binlog 就会被服务端改写

sql
DROP TABLE `t_normal` /* generated by server */

跨库查询 ​

分库分表系统的跨库查询使用临时表不用担心线程之间的重名冲突，分库分表就是要把一个逻辑上的大表分散到不同的数据库实例上

比如将一个大表 ht，按照字段 f，拆分成 1024 个分表，分布到 32 个数据库实例上，一般情况下都有一个中间层 proxy 解析 SQL 语句，通过分库规则通过分表规则（比如 N%1024）确定将这条语句路由到哪个分表做查询

sql
select v from ht where f=N;

如果这个表上还有另外一个索引 k，并且查询语句：

sql
select v from ht where k >= M order by t_modified desc limit 100;

查询条件里面没有用到分区字段 f，只能到所有的分区中去查找满足条件的所有行，然后统一做 order by 操作，两种方式：

  • 在 proxy 层的进程代码中实现排序，拿到分库的数据以后，直接在内存中参与计算，但是对 proxy 端的压力比较大，很容易出现内存不够用和 CPU 瓶颈问题
  • 把各个分库拿到的数据，汇总到一个 MySQL 实例的一个表中，然后在这个汇总实例上做逻辑操作，执行流程：
    • 在汇总库上创建一个临时表 temp_ht，表里包含三个字段 v、k、t_modified
    • 在各个分库执行：select v,k,t_modified from ht_x where k >= M order by t_modified desc limit 100
    • 把分库执行的结果插入到 temp_ht 表中
    • 在临时表上执行：select v from temp_ht order by t_modified desc limit 100

优化步骤 ​

执行频率 ​

MySQL 客户端连接成功后，查询服务器状态信息：

sql
SHOW [SESSION|GLOBAL] STATUS LIKE '';
-- SESSION: 显示当前会话连接的统计结果，默认参数
-- GLOBAL: 显示自数据库上次启动至今的统计结果

  • 查看 SQL 执行频率：

    sql
    SHOW STATUS LIKE 'Com_____';

    Com_xxx 表示每种语句执行的次数

    image-20250320000337300

  • 查询 SQL 语句影响的行数：

    sql
    SHOW STATUS LIKE 'Innodb_rows_%';
    image-20250320004820682

Com_xxxx：这些参数对于所有存储引擎的表操作都会进行累计

Innodb_xxxx：这几个参数只是针对 InnoDB 存储引擎的，累加的算法也略有不同

参数含义
Com_select执行 SELECT 操作的次数，一次查询只累加 1
Com_insert执行 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慢查询的次数

定位低效 ​

SQL 执行慢有两种情况：

  • 偶尔慢：DB 在刷新脏页（学完事务就懂了）
    • redo log 写满了
    • 内存不够用，要从 LRU 链表中淘汰
    • MySQL 认为系统空闲的时候
    • MySQL 关闭时
  • 一直慢的原因：索引没有设计好、SQL 语句没写好、MySQL 选错了索引

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

  • 慢日志查询： 慢查询日志在查询结束以后才记录，执行效率出现问题时查询日志并不能定位问题

    配置文件修改：修改 .cnf 文件 vim /etc/mysql/my.cnf，重启 MySQL 服务器

    sh
    slow_query_log=ON
slow_query_log_file=/usr/local/mysql/var/localhost-slow.log
long_query_time=1	#记录超过long_query_time秒的SQL语句的日志
log-queries-not-using-indexes = 1

    使用命令配置：

    sql
    mysql> SET slow_query_log=ON;
mysql> SET GLOBAL slow_query_log=ON;

    查看是否配置成功：

    sql
    SHOW VARIABLES LIKE '%query%'

  • SHOW PROCESSLIST：实时查看当前 MySQL 在进行的连接线程，包括线程的状态、是否锁表、SQL 的执行情况，同时对一些锁表操作进行优化

    image-20250320004906769

EXPLAIN ​

执行计划 ​

通过 EXPLAIN 命令获取执行 SQL 语句的信息，包括在 SELECT 语句执行过程中如何连接和连接的顺序，执行计划在优化器优化完成后、执行器之前生成，然后执行器会调用存储引擎检索数据

查询 SQL 语句的执行计划：

sql
EXPLAIN SELECT * FROM table_1 WHERE id = 1;
image-20250320004929861
字段含义
idSELECT 的序列号
select_type表示 SELECT 的类型
table访问数据库中表名称，有时可能是简称或者临时表名称（<table_name>）
type表示表的连接类型
possible_keys表示查询时，可能使用的索引
key表示实际使用的索引
key_len索引字段的长度
ref表示与索引列进行等值匹配的对象，常数、某个列、函数等，type 必须在（range, const] 之间，左闭右开
rows扫描出的行数，表示 MySQL 根据表统计信息及索引选用情况，估算的找到所需的记录扫描的行数
filtered条件过滤的行百分比，单表查询没意义，用于连接查询中对驱动表的扇出进行过滤，查询优化器预测所有扇出值满足剩余查询条件的百分比，相乘以后表示多表查询中还要对被驱动执行查询的次数
extra执行情况的说明和描述

MySQL 执行计划的局限：

  • 只是计划，不是执行 SQL 语句，可以随着底层优化器输入的更改而更改
  • EXPLAIN 不会告诉显示关于触发器、存储过程的信息对查询的影响情况， 不考虑各种 Cache
  • EXPLAIN 不能显示 MySQL 在执行查询时的动态，因为执行计划在执行查询之前生成
  • EXPALIN 只能解释 SELECT 操作，其他操作要重写为 SELECT 后查看执行计划
  • EXPLAIN PLAN 显示的是在解释语句时数据库将如何运行 SQL 语句，由于执行环境和 EXPLAIN PLAN 环境的不同，此计划可能与 SQL 语句实际的执行计划不同，部分统计信息是估算的，并非精确值

SHOW WARINGS：在使用 EXPALIN 命令后执行该语句，可以查询与执行计划相关的拓展信息，展示出 Level、Code、Message 三个字段，当 Code 为 1003 时，Message 字段展示的信息类似于将查询语句重写后的信息，但是不是等价，不能执行复制过来运行

环境准备：

image-20250320004958531

id ​

id 代表 SQL 执行的顺序的标识，每个 SELECT 关键字对应一个唯一 id，所以在同一个 SELECT 关键字中的表的 id 都是相同的。SELECT 后的 FROM 可以跟随多个表，每个表都会对应一条记录，这些记录的 id 都是相同的，

  • id 相同时，执行顺序由上至下。连接查询的执行计划，记录的 id 值都是相同的，出现在前面的表为驱动表，后面为被驱动表

    sql
    EXPLAIN SELECT * FROM t_role r, t_user u, user_role ur WHERE r.id = ur.role_id AND u.id = ur.user_id ;
    image-20250320005020698

  • id 不同时，id 值越大优先级越高，越先被执行

    sql
    EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = 'stu1'))
    image-20250320005039682

  • id 有相同也有不同时，id 相同的可以认为是一组，从上往下顺序执行；在所有的组中，id 的值越大的组，优先级越高，越先执行

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

  • id 为 NULL 时代表的是临时表

select ​

表示查询中每个 select 子句的类型（简单 OR 复杂）

select_type含义
SIMPLE简单的 SELECT 查询，查询中不包含子查询或者 UNION
PRIMARY查询中若包含任何复杂的子查询，最外层（也就是最左侧）查询标记为该标识
UNION对于 UNION 或者 UNION ALL 的复杂查询，除了最左侧的查询，其余的小查询都是 UNION
UNION RESULTUNION 需要使用临时表进行去重，临时表的是 UNION RESULT
DEPENDENT UNION对于 UNION 或者 UNION ALL 的复杂查询，如果各个小查询都依赖外层查询，是相关子查询，除了最左侧的小查询为 DEPENDENT SUBQUERY，其余都是 DEPENDENT UNION
SUBQUERY子查询不是相关子查询，该子查询第一个 SELECT 代表的查询就是这种类型，会进行物化（该子查询只需要执行一次）
DEPENDENT SUBQUERY子查询是相关子查询，该子查询第一个 SELECT 代表的查询就是这种类型，不会物化（该子查询需要执行多次）
DERIVED在 FROM 列表中包含的子查询，被标记为 DERIVED（衍生），也就是生成物化派生表的这个子查询
MATERIALIZED将子查询物化后与与外层进行连接查询，生成物化表的子查询

子查询为 DERIVED：SELECT * FROM (SELECT key1 FROM t1) AS derived_1 WHERE key1 > 10

子查询为 MATERIALIZED：SELECT * FROM t1 WHERE key1 IN (SELECT key1 FROM t2)

type ​

对表的访问方式，表示 MySQL 在表中找到所需行的方式，又称访问类型

type含义
ALL全表扫描，如果是 InnoDB 引擎是扫描聚簇索引
index可以使用覆盖索引，但需要扫描全部索引
range索引范围扫描，常见于 between、<、> 等的查询
index_subquery子查询可以普通索引，则子查询的 type 为 index_subquery
unique_subquery子查询可以使用主键或唯一二级索引，则子查询的 type 为 index_subquery
index_merge索引合并
ref_or_null非唯一性索引（普通二级索引）并且可以存储 NULL，进行等值匹配
ref非唯一性索引与常量等值匹配
eq_ref唯一性索引（主键或不存储 NULL 的唯一二级索引）进行等值匹配，如果二级索引是联合索引，那么所有联合的列都要进行等值匹配
const通过主键或者唯一二级索引与常量进行等值匹配
systemsystem 是 const 类型的特例，当查询的表只有一条记录的情况下，使用 system
NULLMySQL 在优化过程中分解语句，执行时甚至不用访问表或索引

从上到下，性能从差到好，一般来说需要保证查询至少达到 range 级别， 最好达到 ref

key ​

possible_keys：

  • 指出 MySQL 能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用
  • 如果该列是 NULL，则没有相关的索引

key：

  • 显示 MySQL 在查询中实际使用的索引，若没有使用索引，显示为 NULL
  • 查询中若使用了覆盖索引，则该索引可能出现在 key 列表，不出现在 possible_keys

key_len：

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

Extra ​

其他的额外的执行计划信息，在该列展示：

  • No tables used：查询语句中使用 FROM dual 或者没有 FROM 语句
  • Impossible WHERE：查询语句中的 WHERE 子句条件永远为 FALSE，会导致没有符合条件的行
  • Using index：该值表示相应的 SELECT 操作中使用了覆盖索引（Covering Index）
  • Using index condition：第一种情况是搜索条件中虽然出现了索引列，但是部分条件无法形成扫描区间（索引失效），会根据可用索引的条件先搜索一遍再匹配无法使用索引的条件，回表查询数据；第二种是使用了索引条件下推优化
  • Using where：搜索的数据需要在 Server 层判断，无法使用索引下推
  • Using join buffer：连接查询被驱动表无法利用索引，需要连接缓冲区来存储中间结果
  • Using filesort：无法利用索引完成排序（优化方向），需要对数据使用外部排序算法，将取得的数据在内存或磁盘中进行排序
  • Using temporary：表示 MySQL 需要使用临时表来存储结果集，常见于排序、去重（UNION）、分组等场景
  • Select tables optimized away：说明仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
  • No tables used：Query 语句中使用 from dual 或不含任何 from 子句

参考文章：https://www.cnblogs.com/ggjucheng/archive/2012/11/11/2765237.html

PROFILES ​

SHOW PROFILES 能够在做 SQL 优化时分析当前会话中语句执行的资源消耗情况

  • 通过 have_profiling 参数，能够看到当前 MySQL 是否支持 profile： image-20250320005117798

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

    image-20250320005143789
    sql
    SET profiling=1; #开启profiling 开关；

  • 执行 SHOW PROFILES 指令， 来查看 SQL 语句执行的耗时:

    sql
    SHOW PROFILES;
    image-20250320005201687

  • 查看到该 SQL 执行过程中每个线程的状态和消耗的时间：

    sql
    SHOW PROFILE FOR QUERY query_id;
    image-20250320005215913

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

    image-20250320005233044
    • Status：SQL 语句执行的状态
    • Durationsql：执行过程中每一个步骤的耗时
    • CPU_user：当前用户占有的 CPU
    • CPU_system：系统占有的 CPU

TRACE ​

MySQL 提供了对 SQL 的跟踪， 通过 trace 文件可以查看优化器生成执行计划的过程

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

    sql
    SET optimizer_trace="enabled=on",end_markers_in_json=ON;	-- 会话内有效
SET optimizer_trace_max_mem_size=1000000;

  • 执行 SQL 语句：

    sql
    SELECT * FROM tb_item WHERE id < 4;

  • 检查 information_schema.optimizer_trace：

    sql
    SELECT * FROM information_schema.optimizer_trace \G; -- \G代表竖列展示

    执行信息主要有三个阶段：prepare 阶段、optimize 阶段（成本分析）、execute 阶段（执行）

索引优化 ​

创建索引 ​

索引是数据库优化最重要的手段之一，通过索引通常可以帮助用户解决大多数的 MySQL 的性能优化问题

sql
CREATE TABLE `tb_seller` (
	`sellerid` varchar (100),
	`name` varchar (100),
	`nickname` varchar (50),
	`password` varchar (60),
	`status` varchar (1),
	`address` varchar (100),
	`createtime` datetime,
    PRIMARY KEY(`sellerid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4;
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('xiaomi','小米科技','小米官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');
CREATE INDEX idx_seller_name_sta_addr ON tb_seller(name, status, address); # 联合索引
image-20250320005254944

避免失效 ​

语句错误 ​

  • 全值匹配：对索引中所有列都指定具体值，这种情况索引生效，执行效率高

    sql
    EXPLAIN SELECT * FROM tb_seller WHERE name='小米科技' AND status='1' AND address='西安市';
    image-20250320005322275

  • 最左前缀法则：联合索引遵守最左前缀法则

    匹配最左前缀法则，走索引：

    sql
    EXPLAIN SELECT * FROM tb_seller WHERE name='小米科技';
EXPLAIN SELECT * FROM tb_seller WHERE name='小米科技' AND status='1';
    image-20250320005339410

    违法最左前缀法则 ， 索引失效：

    sql
    EXPLAIN SELECT * FROM tb_seller WHERE status='1';
EXPLAIN SELECT * FROM tb_seller WHERE status='1' AND address='西安市';
    image-20250320005359201

    如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：

    sql
    EXPLAIN SELECT * FROM tb_seller WHERE name='小米科技' AND address='西安市';
    image-20250320005413395

    虽然索引列失效，但是系统会使用了索引下推进行了优化

  • 范围查询右边的列，不能使用索引：

    sql
    EXPLAIN SELECT * FROM tb_seller WHERE name='小米科技' AND status>'1' AND address='西安市';

    根据前面的两个字段 name ， status 查询是走索引的， 但是最后一个条件 address 没有用到索引，使用了索引下推

    image-20250320005428460

  • 在索引列上函数或者运算（+ - 数值）操作， 索引将失效：会破坏索引值的有序性

    sql
    EXPLAIN SELECT * FROM tb_seller WHERE SUBSTRING(name,3,2) = '科技';
    image-20250320005505822

  • 字符串不加单引号，造成索引失效：隐式类型转换，当字符串和数字比较时会把字符串转化为数字

    没有对字符串加单引号，查询优化器会调用 CAST 函数将 status 转换为 int 进行比较，造成索引失效

    sql
    EXPLAIN SELECT * FROM tb_seller WHERE name='小米科技' AND status = 1;
    image-20250320005522191

    如果 status 是 int 类型，SQL 为 SELECT * FROM tb_seller WHERE status = '1' 并不会造成索引失效，因为会将 '1' 转换为 1，并不会对索引列产生操作

  • 多表连接查询时，如果两张表的字符集不同，会造成索引失效，因为会进行类型转换

    解决方法：CONVERT 函数是加在输入参数上、修改表的字符集

  • 用 OR 分割条件，索引失效，导致全表查询：

    OR 前的条件中的列有索引而后面的列中没有索引或 OR 前后两个列是同一个复合索引，都造成索引失效

    sql
    EXPLAIN SELECT * FROM tb_seller WHERE name='阿里巴巴' OR createtime = '2088-01-01 12:00:00';
EXPLAIN SELECT * FROM tb_seller WHERE name='小米科技' OR status='1';
    image-20250320005541388

    AND 分割的条件不影响：

    sql
    EXPLAIN SELECT * FROM tb_seller WHERE name='阿里巴巴' AND createtime = '2088-01-01 12:00:00';
    image-20250320005600331

  • 以 % 开头的 LIKE 模糊查询，索引失效：

    如果是尾部模糊匹配，索引不会失效；如果是头部模糊匹配，索引失效

    sql
    EXPLAIN SELECT * FROM tb_seller WHERE name like '%科技%';
    image-20250320005627032

    解决方案：通过覆盖索引来解决

    sql
    EXPLAIN SELECT sellerid,name,status FROM tb_seller WHERE name like '%科技%';
    image-20250320005641628

    原因：在覆盖索引的这棵 B+ 数上只需要进行 like 的匹配，或者是基于覆盖索引查询再进行 WHERE 的判断就可以获得结果

系统优化 ​

系统优化为全表扫描：

  • 如果 MySQL 评估使用索引比全表更慢，则不使用索引，索引失效：

    sql
    CREATE INDEX idx_address ON tb_seller(address);
EXPLAIN SELECT * FROM tb_seller WHERE address='西安市';
EXPLAIN SELECT * FROM tb_seller WHERE address='北京市';

    北京市的键值占 9/10（区分度低），所以优化为全表扫描，type = ALL

    image-20250320005656396

  • IS NULL、IS NOT NULL 有时索引失效：

    sql
    EXPLAIN SELECT * FROM tb_seller WHERE name IS NULL;
EXPLAIN SELECT * FROM tb_seller WHERE name IS NOT NULL;

    NOT NULL 失效的原因是 name 列全部不是 null，优化为全表扫描，当 NULL 过多时，IS NULL 失效

    image-20250320005712759

  • IN 肯定会走索引，但是当 IN 的取值范围较大时会导致索引失效，走全表扫描：

    sql
    EXPLAIN SELECT * FROM tb_seller WHERE sellerId IN ('alibaba','huawei');-- 都走索引
EXPLAIN SELECT * FROM tb_seller WHERE sellerId NOT IN ('alibaba','huawei');

  • MySQL 实战 45 讲该章节最后提出了一种慢查询场景，获取到数据以后 Server 层还会做判断

底层原理 ​

索引失效一般是针对联合索引，联合索引一般由几个字段组成，排序方式是先按照第一个字段进行排序，然后排序第二个，依此类推，图示（a, b）索引，a 相等的情况下 b 是有序的

image-20250320005742061

  • 最左前缀法则：当不匹配前面的字段的时候，后面的字段都是无序的。这种无序不仅体现在叶子节点，也会导致查询时扫描的非叶子节点也是无序的，因为索引树相当于忽略的第一个字段，就无法使用二分查找

  • 范围查询右边的列，不能使用索引，比如语句： WHERE a > 1 AND b = 1，在 a 大于 1 的时候，b 是无序的，a > 1 是扫描时有序的，但是找到以后进行寻找 b 时，索引树就不是有序的了

    image-20250320005804788

  • 以 % 开头的 LIKE 模糊查询，索引失效，比如语句：WHERE a LIKE '%d'，前面的不确定，导致不符合最左匹配，直接去索引中搜索以 d 结尾的节点，所以没有顺序 image-20250320005815689

参考文章：https://mp.weixin.qq.com/s/B_M09dzLe9w7cT46rdGIeQ

查看索引 ​

sql
SHOW STATUS LIKE 'Handler_read%';	
SHOW GLOBAL STATUS LIKE 'Handler_read%';
image-20250320005841861

  • Handler_read_first：索引中第一条被读的次数，如果较高，表示服务器正执行大量全索引扫描（这个值越低越好）

  • Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数，值越低表示索引不经常使用（这个值越高越好）

  • Handler_read_next：按照键顺序读下一行的请求数，如果范围约束或执行索引扫描来查询索引列，值增加

  • Handler_read_prev：按照键顺序读前一行的请求数，该读方法主要用于优化 ORDER BY … DESC

  • Handler_read_rnd：根据固定位置读一行的请求数，如果执行大量查询并对结果进行排序则该值较高，可能是使用了大量需要 MySQL 扫描整个表的查询或连接，这个值较高意味着运行效率低，应该建立索引来解决

  • Handler_read_rnd_next：在数据文件中读下一行的请求数，如果正进行大量的表扫描，该值较高，说明表索引不正确或写入的查询没有利用索引

SQL 优化 ​

自增主键 ​

自增机制 ​

自增主键可以让主键索引尽量地保持在数据页中递增顺序插入，不自增需要寻找其他页插入，导致随机 IO 和页分裂的情况

表的结构定义存放在后缀名为.frm 的文件中，但是并不会保存自增值，不同的引擎对于自增值的保存策略不同：

  • MyISAM 引擎的自增值保存在数据文件中
  • InnoDB 引擎的自增值保存在了内存里，每次打开表都会去找自增值的最大值 max(id)，然后将 max(id)+1 作为当前的自增值；8.0 版本后，才有了自增值持久化的能力，将自增值的变更记录在了 redo log 中，重启的时候依靠 redo log 恢复重启之前的值

在插入一行数据的时候，自增值的行为如下：

  • 如果插入数据时 id 字段指定为 0、null 或未指定值，那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段
  • 如果插入数据时 id 字段指定了具体的值，比如某次要插入的值是 X，当前的自增值是 Y
    • 如果 X<Y，那么这个表的自增值不变
    • 如果 X≥Y，就需要把当前自增值修改为新的自增值

参数说明：auto_increment_offset 和 auto_increment_increment 分别表示自增的初始值和步长，默认值都是 1

语句执行失败也不回退自增 id，所以保证了自增 id 是递增的，但不保证是连续的（不能回退，所以有些回滚事务的自增 id 就不会重新使用，导致出现不连续）

自增 ID ​

MySQL 不同的自增 id 在达到上限后的表现不同：

  • 表的自增 id 如果是 int 类型，达到上限 2^32-1 后，再申请时值就不会改变，进而导致继续插入数据时报主键冲突的错误

  • row_id 长度为 6 个字节，达到上限后则会归 0 再重新递增，如果出现相同的 row_id，后写的数据会覆盖之前的数据，造成旧数据丢失，影响的是数据可靠性，所以应该在 InnoDB 表中主动创建自增主键报主键冲突，插入失败影响的是可用性，而一般情况下，可靠性优先于可用性

  • Xid 长度 8 字节，由 Server 层维护，只需要不在同一个 binlog 文件中出现重复值即可，虽然理论上会出现重复值，但是概率极小

  • InnoDB 的 max_trx_id 递增值每次 MySQL 重启都会被保存起来，重启也不会重置为 0，所以会导致一直增加到达上限，然后从 0 开始，这时原事务 0 修改的数据对当前事务就是可见的，产生脏读的现象

    只读事务不分配 trx_id，所以 trx_id 的增加速度变慢了

  • thread_id 长度 4 个字节，到达上限后就会重置为 0，MySQL 设计了一个唯一数组的逻辑，给新线程分配 thread_id 时做判断，保证不会出现两个相同的 thread_id：

    c++
    do {
	new_id = thread_id_counter++;
} while (!thread_ids.insert_unique(new_id).second);

参考文章：https://time.geekbang.org/column/article/83183

覆盖索引 ​

复合索引叶子节点不仅保存了复合索引的值，还有主键索引，所以使用覆盖索引的时候，加上主键也会用到索引

尽量使用覆盖索引，避免 SELECT *：

sql
EXPLAIN SELECT name,status,address FROM tb_seller WHERE name='小米科技' AND status='1' AND address='西安市';
image-20250320005911035

如果查询列，超出索引列，也会降低性能：

sql
EXPLAIN SELECT name,status,address,password FROM tb_seller WHERE name='小米科技' AND status='1' AND address='西安市';
image-20250320005923175

减少访问 ​

避免对数据进行重复检索：能够一次连接就获取到结果的，就不用两次连接，这样可以大大减少对数据库无用的重复请求

  • 查询数据：

    sql
    SELECT id,name FROM tb_book;
SELECT id,status FROM tb_book; -- 向数据库提交两次请求，数据库就要做两次查询操作
-- > 优化为:
SELECT id,name,statu FROM tb_book;

  • 插入数据：

    sql
    INSERT INTO tb_test VALUES(1,'Tom');
INSERT INTO tb_test VALUES(2,'Cat');
INSERT INTO tb_test VALUES(3,'Jerry');	-- 连接三次数据库
-- >优化为
INSERT INTO tb_test VALUES(1,'Tom'),(2,'Cat')，(3,'Jerry');	-- 连接一次

  • 在事务中进行数据插入：

    sql
    start transaction;
INSERT INTO tb_test VALUES(1,'Tom');
INSERT INTO tb_test VALUES(2,'Cat');
INSERT INTO tb_test VALUES(3,'Jerry');
commit;	-- 手动提交，分段提交

  • 数据有序插入：

    sql
    INSERT INTO tb_test VALUES(1,'Tom');
INSERT INTO tb_test VALUES(2,'Cat');
INSERT INTO tb_test VALUES(3,'Jerry');

增加 cache 层：在应用中增加缓存层来达到减轻数据库负担的目的。可以部分数据从数据库中抽取出来放到应用端以文本方式存储，或者使用框架（Mybatis）提供的一级缓存 / 二级缓存，或者使用 Redis 数据库来缓存数据

数据插入 ​

当使用 load 命令导入数据的时候，适当的设置可以提高导入的效率：

image-20250320005951328
sql
LOAD DATA LOCAL INFILE = '/home/klaus/sql1.log' INTO TABLE `tb_user_1` FIELD TERMINATED BY ',' LINES TERMINATED BY '\n'; -- 文件格式如上图

对于 InnoDB 类型的表，有以下几种方式可以提高导入的效率：

  1. 主键顺序插入：因为 InnoDB 类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率，如果 InnoDB 表没有主键，那么系统会自动默认创建一个内部列作为主键

    主键是否连续对性能影响不大，只要是递增的就可以，比如雪花算法产生的 ID 不是连续的，但是是递增的，因为递增可以让主键索引尽量地保持顺序插入，避免了页分裂，因此索引更紧凑

    • 插入 ID 顺序排列数据：
    image-20250320010010521
    • 插入 ID 无序排列数据：
    image-20250320010026345

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

    image-20250320010102015

  3. 手动提交事务：如果应用使用自动提交的方式，建议在导入前执行SET AUTOCOMMIT=0，关闭自动提交；导入结束后再打开自动提交，可以提高导入的效率。

    事务需要控制大小，事务太大可能会影响执行的效率。MySQL 有 innodb_log_buffer_size 配置项，超过这个值的日志会写入磁盘数据，效率会下降，所以在事务大小达到配置项数据级前进行事务提交可以提高效率

    image-20250320010116411

分组排序 ​

ORDER ​

数据准备：

sql
CREATE TABLE `emp` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `age` INT(3) NOT NULL,
  `salary` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;
INSERT INTO `emp` (`id`, `name`, `age`, `salary`) VALUES('1','Tom','25','2300');-- ...
CREATE INDEX idx_emp_age_salary ON emp(age, salary);

  • 第一种是通过对返回数据进行排序，所有不通过索引直接返回结果的排序都叫 FileSort 排序，会在内存中重新排序

    sql
    EXPLAIN SELECT * FROM emp ORDER BY age DESC;	-- 年龄降序
    image-20250320010133855

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

    sql
    EXPLAIN SELECT id, age, salary FROM emp ORDER BY age DESC;
    image-20250320010145655

  • 多字段排序：

    sql
    EXPLAIN SELECT id,age,salary FROM emp ORDER BY age DESC, salary DESC;
EXPLAIN SELECT id,age,salary FROM emp ORDER BY salary DESC, age DESC;
EXPLAIN SELECT id,age,salary FROM emp ORDER BY age DESC, salary ASC;
    image-20250320010212699

    尽量减少额外的排序，通过索引直接返回有序数据。需要满足 Order by 使用相同的索引、Order By 的顺序和索引顺序相同、Order by 的字段都是升序或都是降序，否则需要额外的操作，就会出现 FileSort

  • ORDER BY RAND() 命令用来进行随机排序，会使用了临时内存表，临时内存表排序的时使用 rowid 排序方法

优化方式：创建合适的索引能够减少 Filesort 的出现，但是某些情况下条件限制不能让 Filesort 消失，就要加快 Filesort 的排序操作

内存临时表，MySQL 有两种 Filesort 排序算法：

  • rowid 排序：首先根据条件取出排序字段和信息，然后在排序区 sort buffer（Server 层）中排序，如果 sort buffer 不够，则在临时表 temporary table 中存储排序结果。完成排序后再根据行指针回表读取记录，该操作可能会导致大量随机 I/O 操作

    说明：对于临时内存表，回表过程只是简单地根据数据行的位置，直接访问内存得到数据，不会导致多访问磁盘，优先选择该方式

  • 全字段排序：一次性取出满足条件的所有数据，需要回表，然后在排序区 sort buffer 中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法高

具体的选择方式：

  • MySQL 通过比较系统变量 max_length_for_sort_data 的大小和 Query 语句取出的字段的大小，来判定使用哪种排序算法。如果前者大，则说明 sort buffer 空间足够，使用第二种优化之后的算法，否则使用第一种。

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

    sql
    SET @@max_length_for_sort_data = 10000; 		-- 设置全局变量
SET max_length_for_sort_data = 10240; 			-- 设置会话变量
SHOW VARIABLES LIKE 'max_length_for_sort_data';	-- 默认1024
SHOW VARIABLES LIKE 'sort_buffer_size';			-- 默认262114

磁盘临时表：排序使用优先队列（堆）的方式

GROUP ​

GROUP BY 也会进行排序操作，与 ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作，所以在 GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引

  • 分组查询：

    sql
    DROP INDEX idx_emp_age_salary ON emp;
EXPLAIN SELECT age,COUNT(*) FROM emp GROUP BY age;
    image-20250320010253430

    Using temporary：表示 MySQL 需要使用临时表（不是 sort buffer）来存储结果集，常见于排序和分组查询

  • 查询包含 GROUP BY 但是用户想要避免排序结果的消耗， 则可以执行 ORDER BY NULL 禁止排序：

    sql
    EXPLAIN SELECT age,COUNT(*) FROM emp GROUP BY age ORDER BY NULL;
    image-20250320010330441

  • 创建索引：索引本身有序，不需要临时表，也不需要再额外排序

    sql
    CREATE INDEX idx_emp_age_salary ON emp(age, salary);

  • 数据量很大时，使用 SQL_BIG_RESULT 提示优化器直接使用直接用磁盘临时表

联合查询 ​

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

  • 执行查询语句：

    sql
    EXPLAIN SELECT * FROM emp WHERE id = 1 OR age = 30;	-- 两个索引，并且不是复合索引
    image-20250320010443299
    sh
    Extra: Using sort_union(idx_emp_age_salary,PRIMARY); Using where

  • 使用 UNION 替换 OR，求并集：

    注意：该优化只针对多个索引列有效，如果有列没有被索引，查询效率可能会因为没有选择 OR 而降低

    sql
    EXPLAIN SELECT * FROM emp WHERE id = 1 UNION SELECT * FROM emp WHERE age = 30;
    image-20250320010458558

  • UNION 要优于 OR 的原因：

    • UNION 语句的 type 值为 ref，OR 语句的 type 值为 range
    • UNION 语句的 ref 值为 const，OR 语句的 ref 值为 null，const 表示是常量值引用，非常快

嵌套查询 ​

MySQL 4.1 版本之后，开始支持 SQL 的子查询

  • 可以使用 SELECT 语句来创建一个单列的查询结果，然后把结果作为过滤条件用在另一个查询中
  • 使用子查询可以一次性的完成逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或者表锁死
  • 在有些情况下，子查询是可以被更高效的连接（JOIN）替代

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

  • 执行计划：

    sql
    EXPLAIN SELECT * FROM t_user WHERE id IN (SELECT user_id FROM user_role);
    image-20250320010514932

  • 优化后：

    sql
    EXPLAIN SELECT * FROM t_user u , user_role ur WHERE u.id = ur.user_id;
    image-20250320010528940

    连接查询之所以效率更高 ，是因为不需要在内存中创建临时表来完成逻辑上需要两个步骤的查询工作

分页查询 ​

一般分页查询时，通过创建覆盖索引能够比较好地提高性能

一个常见的问题是 LIMIT 200000,10，此时需要 MySQL 扫描前 200010 记录，仅仅返回 200000 - 200010 之间的记录，其他记录丢弃，查询排序的代价非常大

  • 分页查询：

    sql
    EXPLAIN SELECT * FROM tb_user_1 LIMIT 200000,10;
    image-20250320010546706

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

    sql
    EXPLAIN SELECT * FROM tb_user_1 t,(SELECT id FROM tb_user_1 ORDER BY id LIMIT 200000,10) a WHERE t.id = a.id;
    image-20250320010600495

  • 优化方式二：方案适用于主键自增的表，可以把 LIMIT 查询转换成某个位置的查询

    sql
    EXPLAIN SELECT * FROM tb_user_1 WHERE id > 200000 LIMIT 10;			-- 写法 1
EXPLAIN SELECT * FROM tb_user_1 WHERE id BETWEEN 200000 and 200010;	-- 写法 2
    image-20250320010614388

使用提示 ​

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

  • USE INDEX：在查询语句中表名的后面添加 USE INDEX 来提供 MySQL 去参考的索引列表，可以让 MySQL 不再考虑其他可用的索引

    sql
    CREATE INDEX idx_seller_name ON tb_seller(name);
EXPLAIN SELECT * FROM tb_seller USE INDEX(idx_seller_name) WHERE name='小米科技';
    image-20250320010640115

  • IGNORE INDEX：让 MySQL 忽略一个或者多个索引，则可以使用 IGNORE INDEX 作为提示

    sql
    EXPLAIN SELECT * FROM tb_seller IGNORE INDEX(idx_seller_name) WHERE name = '小米科技';
    image-20250320010653006

  • FORCE INDEX：强制 MySQL 使用一个特定的索引

    sql
    EXPLAIN SELECT * FROM tb_seller FORCE INDEX(idx_seller_name_sta_addr) WHERE NAME='小米科技';
    image-20250320010722329

统计计数 ​

在不同的 MySQL 引擎中，count(*) 有不同的实现方式：

  • MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高，但不支持事务
  • show table status 命令通过采样估算可以快速获取，但是不准确
  • InnoDB 表执行 count(*) 会遍历全表，虽然结果准确，但会导致性能问题

解决方案：

  • 计数保存在 Redis 中，但是更新 MySQL 和 Redis 的操作不是原子的，会存在数据一致性的问题

  • 计数直接放到数据库里单独的一张计数表中，利用事务解决计数精确问题：

    image-20250320010755840

    会话 B 的读操作在 T3 执行的，这时更新事务还没有提交，所以计数值加 1 这个操作对会话 B 还不可见，因此会话 B 查询的计数值和最近 100 条记录，返回的结果逻辑上就是一致的

    并发系统性能的角度考虑，应该先插入操作记录再更新计数表，因为更新计数表涉及到行锁的竞争，先插入再更新能最大程度地减少事务之间的锁等待，提升并发度

count 函数的按照效率排序：count(字段) < count(主键id) < count(1) ≈ count(*)，所以建议尽量使用 count(*)

  • count(主键 id)：InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来返回给 Server 层，Server 判断 id 不为空就按行累加
  • count(1)：InnoDB 引擎遍历整张表但不取值，Server 层对于返回的每一行，放一个数字 1 进去，判断不为空就按行累加
  • count(字段)：如果这个字段是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；如果这个字段定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加
  • count(*)：不取值，按行累加

参考文章：https://time.geekbang.org/column/article/72775

缓冲优化 ​

优化原则 ​

三个原则：

  • 将尽量多的内存分配给 MySQL 做缓存，但也要给操作系统和其他程序预留足够内存
  • MyISAM 存储引擎的数据文件读取依赖于操作系统自身的 IO 缓存，如果有 MyISAM 表，就要预留更多的内存给操作系统做 IO 缓存
  • 排序区、连接区等缓存是分配给每个数据库会话（Session）专用的，值的设置要根据最大连接数合理分配，如果设置太大，不但浪费资源，而且在并发数较高时会导致物理内存耗尽

缓冲内存 ​

Buffer Pool 本质上是 InnoDB 向操作系统申请的一段连续的内存空间。InnoDB 的数据是按数据页为单位来读写，每个数据页的大小默认是 16KB。数据是存放在磁盘中，每次读写数据都需要进行磁盘 IO 将数据读入内存进行操作，效率会很低，所以提供了 Buffer Pool 来暂存这些数据页，缓存中的这些页又叫缓冲页

工作原理：

  • 从数据库读取数据时，会首先从缓存中读取，如果缓存中没有，则从磁盘读取后放入 Buffer Pool
  • 向数据库写入数据时，会写入缓存，缓存中修改的数据会定期刷新到磁盘，这一过程称为刷脏

Buffer Pool 中每个缓冲页都有对应的控制信息，包括表空间编号、页号、偏移量、链表信息等，控制信息存放在占用的内存称为控制块，控制块与缓冲页是一一对应的，但并不是物理上相连的，都在缓冲池中

MySQL 提供了缓冲页的快速查找方式：哈希表，使用表空间号和页号作为 Key，缓冲页控制块的地址作为 Value 创建一个哈希表，获取数据页时根据 Key 进行哈希寻址：

  • 如果不存在对应的缓存页，就从 free 链表中选一个空闲缓冲页，把磁盘中的对应页加载到该位置
  • 如果存在对应的缓存页，直接获取使用，提高查询数据的效率

当内存数据页跟磁盘数据页内容不一致时，称这个内存页为脏页；内存数据写入磁盘后，内存和磁盘上的数据页一致，称为干净页

内存管理 ​

Free 链表 ​

MySQL 启动时完成对 Buffer Pool 的初始化，先向操作系统申请连续的内存空间，然后将内存划分为若干对控制块和缓冲页。为了区分空闲和已占用的数据页，将所有空闲缓冲页对应的控制块作为一个节点放入一个链表中，就是 Free 链表（空闲链表）

image-20250320010821319

基节点：是一块单独申请的内存空间（占 40 字节），并不在 Buffer Pool 的那一大片连续内存空间里

磁盘加载页的流程：

  • 从 Free 链表中取出一个空闲的缓冲页
  • 把缓冲页对应的控制块的信息填上（页所在的表空间、页号之类的信息）
  • 把缓冲页对应的 Free 链表节点（控制块）从链表中移除，表示该缓冲页已经被使用

参考文章：https://blog.csdn.net/li1325169021/article/details/121124440

Flush 链表 ​

Flush 链表是一个用来存储脏页的链表，对于已经修改过的缓冲脏页，第一次修改后加入到链表头部，以后每次修改都不会重新加入，只修改部分控制信息，出于性能考虑并不是直接更新到磁盘，而是在未来的某个时间进行刷脏

image-20250320010834310

后台有专门的线程每隔一段时间把脏页刷新到磁盘：

  • 从 Flush 链表中刷新一部分页面到磁盘：
    • 后台线程定时从 Flush 链表刷脏，根据系统的繁忙程度来决定刷新速率，这种方式称为 BUF_FLUSH_LIST
    • 线程刷脏的比较慢，导致用户线程加载一个新的数据页时发现没有空闲缓冲页，此时会尝试从 LRU 链表尾部寻找缓冲页直接释放，如果该页面是已经修改过的脏页就同步刷新到磁盘，速度较慢，这种方式称为 BUF_FLUSH_SINGLE_PAGE
  • 从 LRU 链表的冷数据中刷新一部分页面到磁盘，即：BUF_FLUSH_LRU
    • 后台线程会定时从 LRU 链表的尾部开始扫描一些页面，扫描的页面数量可以通过系统变量 innodb_lru_scan_depth 指定，如果在 LRU 链表中发现脏页，则把它们刷新到磁盘，这种方式称为 BUF_FLUSH_LRU
    • 控制块里会存储该缓冲页是否被修改的信息，所以可以很容易的获取到某个缓冲页是否是脏页

参考文章：https://blog.csdn.net/li1325169021/article/details/121125765

LRU 链表 ​

Buffer Pool 需要保证缓存的命中率，所以 MySQL 创建了一个 LRU 链表，当访问某个页时：

  • 如果该页不在 Buffer Pool 中，把该页从磁盘加载进来后会将该缓冲页对应的控制块作为节点放入 LRU 链表的头部，保证热点数据在链表头
  • 如果该页在 Buffer Pool 中，则直接把该页对应的控制块移动到 LRU 链表的头部，所以 LRU 链表尾部就是最近最少使用的缓冲页

MySQL 基于局部性原理提供了预读功能：

  • 线性预读：系统变量 innodb_read_ahead_threshold，如果顺序访问某个区（extent：16 KB 的页，连续 64 个形成一个区，一个区默认 1MB 大小）的页面数超过了该系统变量值，就会触发一次异步读取下一个区中全部的页面到 Buffer Pool 中
  • 随机预读：如果某个区 13 个连续的页面都被加载到 Buffer Pool，无论这些页面是否是顺序读取，都会触发一次异步读取本区所有的其他页面到 Buffer Pool 中

预读会造成加载太多用不到的数据页，造成那些使用频率很高的数据页被挤到 LRU 链表尾部，所以 InnoDB 将 LRU 链表分成两段，冷热数据隔离：

  • 一部分存储使用频率很高的数据页，这部分链表也叫热数据，young 区，靠近链表头部的区域
  • 一部分存储使用频率不高的冷数据，old 区，靠近链表尾部，默认占 37%，可以通过系统变量 innodb_old_blocks_pct 指定

当磁盘上的某数据页被初次加载到 Buffer Pool 中会被放入 old 区，淘汰时优先淘汰 old 区

  • 当对 old 区的数据进行访问时，会在控制块记录下访问时间，等待后续的访问时间与第一次访问的时间是否在某个时间间隔内，通过系统变量 innodb_old_blocks_time 指定时间间隔，默认 1000ms，成立就移动到 young 区的链表头部
  • innodb_old_blocks_time 为 0 时，每次访问一个页面都会放入 young 区的头部

参数优化 ​

InnoDB 用一块内存区做 IO 缓存池，该缓存池不仅用来缓存 InnoDB 的索引块，也用来缓存 InnoDB 的数据块，可以通过下面的指令查看 Buffer Pool 的状态信息：

sql
SHOW ENGINE INNODB STATUS\G

Buffer pool hit rate 字段代表内存命中率，表示 Buffer Pool 对查询的加速效果

核心参数：

  • innodb_buffer_pool_size：该变量决定了 Innodb 存储引擎表数据和索引数据的最大缓存区大小，默认 128M

    sql
    SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

    在保证操作系统及其他程序有足够内存可用的情况下，innodb_buffer_pool_size 的值越大，缓存命中率越高，建议设置成可用物理内存的 60%~80%

    sh
    innodb_buffer_pool_size=512M

  • innodb_log_buffer_size：该值决定了 Innodb 日志缓冲区的大小，保存要写入磁盘上的日志文件数据

    对于可能产生大量更新记录的大事务，增加该值的大小，可以避免 Innodb 在事务提交前就执行不必要的日志写入磁盘操作，影响执行效率，通过配置文件修改：

    sh
    innodb_log_buffer_size=10M

在多线程下，访问 Buffer Pool 中的各种链表都需要加锁，所以将 Buffer Pool 拆成若干个小实例，每个线程对应一个实例，独立管理内存空间和各种链表（类似 ThreadLocal），多线程访问各自实例互不影响，提高了并发能力

MySQL 5.7.5 之前 innodb_buffer_pool_size 只支持在系统启动时修改，现在已经支持运行时修改 Buffer Pool 的大小，但是每次调整参数都会重新向操作系统申请一块连续的内存空间，将旧的缓冲池的内容拷贝到新空间非常耗时，所以 MySQL 开始以一个 chunk 为单位向操作系统申请内存，所以一个 Buffer Pool 实例可以由多个 chunk 组成

  • 在系统启动时设置系统变量 innodb_buffer_pool_instance 可以指定 Buffer Pool 实例的个数，但是当 Buffer Pool 小于 1GB 时，设置多个实例时无效的
  • 指定系统变量 innodb_buffer_pool_chunk_size 来改变 chunk 的大小，只能在启动时修改，运行中不能修改，而且该变量并不包含缓冲页的控制块的内存大小
  • innodb_buffer_pool_size 必须是 innodb_buffer_pool_chunk_size × innodb_buffer_pool_instance 的倍数，默认值是 128M × 16 = 2G，Buffer Pool 必须是 2G 的整数倍，如果指定 5G，会自动调整成 6G
  • 如果启动时 chunk × instances > pool_size，那么 chunk 的值会自动设置为 pool_size ÷ instances

内存优化 ​

Change ​

InnoDB 管理的 Buffer Pool 中有一块内存叫 Change Buffer 用来对增删改操作提供缓存，可以通过参数来动态设置，设置为 50 时表示 Change Buffer 的大小最多占用 Buffer Pool 的 50%

  • 唯一索引的更新不能使用 Change Buffer，需要将数据页读入内存，判断没有冲突在写入
  • 普通索引可以使用 Change Buffer，直接写入 Buffer 就结束，不用校验唯一性

Change Buffer 并不是数据页，只是对操作的缓存，所以需要将 Change Buffer 中的操作应用到旧数据页，得到新的数据页（脏页）的过程称为 Merge

  • 触发时机：访问数据页时会触发 Merge、后台有定时线程进行 Merge、在数据库正常关闭（shutdown）的过程中也会触发
  • 工作流程：首先从磁盘读入数据页到内存（因为 Buffer Pool 中不一定存在对应的数据页），从 Change Buffer 中找到对应的操作应用到数据页，得到新的数据页即为脏页，然后写入 redo log，等待刷脏即可

说明：Change Buffer 中的记录，在事务提交时也会写入 redo log，所以是可以保证不丢失的

业务场景：

  • 对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 Change Buffer 的使用效果最好，常见的就是账单类、日志类的系统

  • 一个业务的更新模式是写入后马上做查询，那么即使满足了条件，将更新先记录在 Change Buffer，但之后由于马上要访问这个数据页，会立即触发 Merge 过程，这样随机访问 IO 的次数不会减少，并且增加了 Change Buffer 的维护代价

补充：Change Buffer 的前身是 Insert Buffer，只能对 Insert 操作优化，后来增加了 Update/Delete 的支持，改为 Change Buffer

Net ​

Server 层针对优化查询的内存为 Net Buffer，内存的大小是由参数 net_buffer_length定义，默认 16k，实现流程：

  • 获取一行数据写入 Net Buffer，重复获取直到 Net Buffer 写满，调用网络接口发出去
  • 若发送成功就清空 Net Buffer，然后继续取下一行；若发送函数返回 EAGAIN 或 WSAEWOULDBLOCK，表示本地网络栈 socket send buffer 写满了，进入等待，直到网络栈重新可写再继续发送

MySQL 采用的是边读边发的逻辑，因此对于数据量很大的查询来说，不会在 Server 端保存完整的结果集，如果客户端读结果不及时，会堵住 MySQL 的查询过程，但是不会把内存打爆导致 OOM

image-20250320011014398

SHOW PROCESSLIST 获取线程信息后，处于 Sending to client 状态代表服务器端的网络栈写满，等待客户端接收数据

假设有一个业务的逻辑比较复杂，每读一行数据以后要处理很久的逻辑，就会导致客户端要过很久才会去取下一行数据，导致 MySQL 的阻塞，一直处于 Sending to client 的状态

解决方法：如果一个查询的返回结果很是很多，建议使用 mysql_store_result 这个接口，直接把查询结果保存到本地内存

参考文章：https://blog.csdn.net/qq_33589510/article/details/117673449

Read ​

read_rnd_buffer 是 MySQL 的随机读缓冲区，当按任意顺序读取记录行时将分配一个随机读取缓冲区，进行排序查询时，MySQL 会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，大小是由 read_rnd_buffer_size 参数控制的

Multi-Range Read 优化，将随机 IO 转化为顺序 IO 以降低查询过程中 IO 开销，因为大多数的数据都是按照主键递增顺序插入得到，所以按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能

二级索引为 a，聚簇索引为 id，优化回表流程：

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

说明：如果步骤 1 中 read_rnd_buffer 放满了，就会先执行步骤 2 和 3，然后清空 read_rnd_buffer，之后继续找索引 a 的下个记录

使用 MRR 优化需要设进行设置：

sql
SET optimizer_switch='mrr_cost_based=off'

Key ​

MyISAM 存储引擎使用 key_buffer 缓存索引块，加速 MyISAM 索引的读写速度。对于 MyISAM 表的数据块没有特别的缓存机制，完全依赖于操作系统的 IO 缓存

  • key_buffer_size：该变量决定 MyISAM 索引块缓存区的大小，直接影响到 MyISAM 表的存取效率

    sql
    SHOW VARIABLES LIKE 'key_buffer_size';	-- 单位是字节

    在 MySQL 配置文件中设置该值，建议至少将1/4可用内存分配给 key_buffer_size：

    sh
    vim /etc/mysql/my.cnf
key_buffer_size=1024M

  • read_buffer_size：如果需要经常顺序扫描 MyISAM 表，可以通过增大 read_buffer_size 的值来改善性能。但 read_buffer_size 是每个 Session 独占的，如果默认值设置太大，并发环境就会造成内存浪费

  • read_rnd_buffer_size：对于需要做排序的 MyISAM 表的查询，如带有 ORDER BY 子句的语句，适当增加该的值，可以改善此类的 SQL 的性能，但是 read_rnd_buffer_size 是每个 Session 独占的，如果默认值设置太大，就会造成内存浪费

存储优化 ​

数据存储 ​

系统表空间是用来放系统信息的，比如数据字典什么的，对应的磁盘文件是 ibdata，数据表空间是一个个的表数据文件，对应的磁盘文件就是表名.ibd

表数据既可以存在共享表空间里，也可以是单独的文件，这个行为是由参数 innodb_file_per_table 控制的：

  • OFF：表示表的数据放在系统共享表空间，也就是跟数据字典放在一起
  • ON ：表示每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中（默认）

一个表单独存储为一个文件更容易管理，在不需要这个表时通过 drop table 命令，系统就会直接删除这个文件；如果是放在共享表空间中，即使表删掉了，空间也是不会回收的

数据删除 ​

MySQL 的数据删除就是移除掉某个记录后，该位置就被标记为可复用，如果有符合范围条件的数据可以插入到这里。符合范围条件的意思是假设删除记录 R4，之后要再插入一个 ID 在 300 和 600 之间的记录时，就会复用这个位置

image-20250320011040214

InnoDB 的数据是按页存储的如果删掉了一个数据页上的所有记录，整个数据页就可以被复用了，如果相邻的两个数据页利用率都很小，系统就会把这两个页上的数据合到其中一个页上，另外一个数据页就被标记为可复用

删除命令其实只是把记录的位置，或者数据页标记为了可复用，但磁盘文件的大小是不会变的，这些可以复用还没有被使用的空间，看起来就像是空洞，造成数据库的稀疏，因此需要进行紧凑处理

重建数据 ​

重建表就是按照主键 ID 递增的顺序，把数据一行一行地从旧表中读出来再插入到新表中，让数据更加紧凑。重建表时 MySQL 会自动完成转存数据、交换表名、删除旧表的操作，线上操作会阻塞大量的线程增删改查的操作

重建命令：

sql
ALTER TABLE A ENGINE=InnoDB

工作流程：新建临时表 tmp_table B（在 Server 层创建的），把表 A 中的数据导入到表 B 中，操作完成后用表 B 替换表 A，完成重建

重建表的步骤需要 DDL 不是 Online 的，因为在导入数据的过程有新的数据要写入到表 A 的话，就会造成数据丢失

MySQL 5.6 版本开始引入的 Online DDL，重建表的命令默认执行此步骤：

  • 建立一个临时文件 tmp_file（InnoDB 创建），扫描表 A 主键的所有数据页
  • 用数据页中表 A 的记录生成 B+ 树，存储到临时文件中
  • 生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中，对应的是图中 state2 的状态
  • 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件，对应的就是图中 state3
  • 用临时文件替换表 A 的数据文件
image-20250320011052781

Online DDL 操作会先获取 MDL 写锁，再退化成 MDL 读锁。但 MDL 写锁持有时间比较短，所以可以称为 Online； 而 MDL 读锁，不阻止数据增删查改，但会阻止其它线程修改表结构（可以对比 ANALYZE TABLE t 命令）

问题：重建表可以收缩表空间，但是执行指令后整体占用空间增大

原因：在重建表后 InnoDB 不会把整张表占满，每个页留了 1/16 给后续的更新使用。表在未整理之前页已经占用 15/16 以上，收缩之后需要保持数据占用空间在 15/16，所以文件占用空间更大才能保持

注意：临时文件也要占用空间，如果空间不足会重建失败

原地置换 ​

DDL 中的临时表 tmp_table 是在 Server 层创建的，Online DDL 中的临时文件 tmp_file 是 InnoDB 在内部创建出来的，整个 DDL 过程都在 InnoDB 内部完成，对于 Server 层来说，没有把数据挪动到临时表，是一个原地操作，这就是 inplace

两者的关系：

  • DDL 过程如果是 Online 的，就一定是 inplace 的
  • inplace 的 DDL，有可能不是 Online 的，截止到 MySQL 8.0，全文索引（FULLTEXT）和空间索引（SPATIAL）属于这种情况

并发优化 ​

MySQL Server 是多线程结构，包括后台线程和客户服务线程。多线程可以有效利用服务器资源，提高数据库的并发性能。在 MySQL 中，控制并发连接和线程的主要参数：

  • max_connections：控制允许连接到 MySQL 数据库的最大连接数，默认值是 151

    如果状态变量 connection_errors_max_connections 不为零，并且一直增长，则说明不断有连接请求因数据库连接数已达到允许最大值而失败，这时可以考虑增大 max_connections 的值

    MySQL 最大可支持的连接数取决于很多因素，包括操作系统平台的线程库的质量、内存大小、每个连接的负荷、CPU的处理速度、期望的响应时间等。在 Linux 平台下，性能好的服务器，可以支持 500-1000 个连接，需要根据服务器性能进行评估设定

  • innodb_thread_concurrency：并发线程数，代表系统内同时运行的线程数量（已经被移除）

  • back_log：控制 MySQL 监听 TCP 端口时的积压请求栈的大小

    如果 Mysql 的连接数达到 max_connections 时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即 back_log。如果等待连接的数量超过 back_log，将不被授予连接资源直接报错

    5.6.6 版本之前默认值为 50，之后的版本默认为 50 + (max_connections/5)，但最大不超过900，如果需要数据库在较短的时间内处理大量连接请求， 可以考虑适当增大 back_log 的值

  • table_open_cache：控制所有 SQL 语句执行线程可打开表缓存的数量

    在执行 SQL 语句时，每个执行线程至少要打开1个表缓存，该参数的值应该根据设置的最大连接数以及每个连接执行关联查询中涉及的表的最大数量来设定：max_connections * N

  • thread_cache_size：可控制 MySQL 缓存客户服务线程的数量

    为了加快连接数据库的速度，MySQL 会缓存一定数量的客户服务线程以备重用，池化思想

  • innodb_lock_wait_timeout：设置 InnoDB 事务等待行锁的时间，默认值是 50ms

    对于需要快速反馈的业务系统，可以将行锁的等待时间调小，以避免事务被长时间挂起； 对于后台运行的批量处理程序来说，可以将行锁的等待时间调大，以避免发生大的回滚操作

Q.E.D.
MySQL - 事务
MySQL - 存储引擎与索引