MySQL调优学习笔记

MySQL 调优学习笔记：监控报警 · 慢SQL排查 · 调优实践

内容覆盖 MySQL 调优的完整路径：监控报警 → 排查慢 SQL → 系统调优（基础优化、表设计、索引、SQL 语句）。

一、MySQL 调优总览

MySQL 调优主要分为三个步骤：监控报警、排查慢 SQL、MySQL 调优。

flowchart LR A[① 监控报警 Prometheus + Grafana 发现性能问题] --> B[② 排查慢 SQL 慢查询日志 + EXPLAIN 定位问题 SQL] B --> C[③ MySQL 调优 基础优化 / 表设计 / 索引 / SQL] C -.->|持续监控| A

二、监控报警

使用监控工具（例如 Prometheus + Grafana）监控 MySQL，发现查询性能变慢时，报警提醒运维人员。

监控要点包括：MySQL 运行时长、当前 QPS、InnoDB 缓冲池大小、连接数、客户端线程活动、表锁情况、线程缓存等指标。先监控、再优化，调优是一个持续改进的过程。

三、排查慢 SQL

3.1 开启慢查询日志

查看慢查询次数：

show status like 'slow_queries';

开启慢查询日志，修改慢查询阈值：

set slow_query_log = 'ON';  -- 开启慢查询日志
set long_query_time = 1;    -- 设置慢查询阈值（秒）

3.2 找出最慢的几条 SQL

使用慢查询日志分析工具 mysqldumpslow 找出最慢的几条语句，其常用参数如下：

参数	说明
`-a`	不将数字抽象成 N、字符串抽象成 S
`-s`	排序方式：`c` 访问次数、`l` 锁定时间、`r` 返回记录、`t` 查询时间、`al` 平均锁定时间、`ar` 平均返回记录数、`at` 平均查询时间（默认）、`ac` 平均查询次数
`-t`	返回前面多少条数据
`-g`	后边搭配一个正则匹配模式，大小写不敏感

示例： 按照查询时间排序，查看前五条慢查询 SQL 语句：

mysqldumpslow -s t -t 5 /var/lib/mysql/xxx-slow.log

输出示例：

Count: 1  Time=312.04s (312s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@xxx
CALL insert_stu1(N,N)

Count: 1  Time=1.25s (1s)  Lock=0.00s (0s)  Rows=11.0 (11), root[root]@localhost
select * from student where name = 'S'

Count: 1  Time=1.19s (1s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost
select * from student where stuno = N

3.3 分析查询计划

使用 EXPLAIN 分析 SQL 执行计划（访问类型、记录条数、索引长度等），主要关注以下字段：

possible_keys： 查询可能用到的索引；
key： 实际使用的索引；
key_len： 实际使用的索引的字节数长度；
type： 访问类型，看有没有走索引。all（全表扫描）、ref（命中非唯一索引）、const（命中主键/唯一索引）、range（范围索引查询）、index_merge（使用多个索引）、system（一行记录时，快速查询）；
Extra： 额外信息，看有没有走索引：
- using index： 覆盖索引，不回表；
- using filesort： 需要额外的排序。排序分为索引排序和 filesort 排序，索引排序一般更快；深分页等查询数据量大时 filesort 更快；
- using index condition： 索引下推（MySQL 5.6 开始支持）。联合索引某字段是模糊查询（非左模糊）时，该字段进行条件判断后，后面几个字段可以直接条件判断，判断过滤后再回表对不包含在联合索引内的字段条件进行判断；
- using where： 不走索引，全表扫描。

执行计划各个列的作用：

列名	说明
`id`	每个 SELECT 子句或 join 操作都会被分配一个唯一编号，编号越小优先级越高；id 相同的语句可被认为是一组；id 为 NULL 表示独立的子查询，子查询优先级都比主查询高
`select_type`	查询的类型：主查询（primary）、普通查询（simple）、联合查询、子查询（subquery）、derived（from 表临时子查询）、union（union 后查询）、union result
`table`	表名，显示当前这行的数据来自哪个表
`partitions`	匹配的分区信息，如果表未分区则为 NULL
`type`	访问类型，根据索引、全表扫描等方法来执行查询的优化策略：`all`（全表扫描）、`ref`（命中非唯一索引）、`const`（命中主键/唯一索引）、`range`（范围索引查询）、`index_merge`（使用多个索引）、`system`（一行记录时，快速查询）
`possible_keys`	可能用到的索引，列出 MySQL 能够使用哪些索引来查询。如果该列只有一个 possible_keys，通常意味着这个查询是高效的；如果这个列有多个 possible_keys，并且 MySQL 只使用了其中一个，则需要考虑是否需要在该列上增加一个联合索引
`key`	实际上使用的索引。如果没有明确的指定 KEY，MySQL 会根据查询条件自动选择最优的索引
`key_len`	实际使用到索引的字节数长度。越短表示越快，一般表示索引字段越小越好
`ref`	当使用索引列等值查询时，与索引列进行等值匹配的对象信息：常量等值查询 const、表达式/函数使用到时 func、关联查询显示关联字段名
`rows`	预估的需要读取的记录条数。数值越小越好，表示结果集越小，查询越高效
`filtered`	某个表经过搜索条件过滤后剩余记录条数的百分比。这个值越小越好，说明可通过索引直接返回数据
`Extra`	额外信息，看有没有走索引，还是全表扫描了，一般搭配 type 字段看：`Using index`（使用到覆盖索引）、`Using where`（未使用索引查询）、`Using temporary`（临时表存储结果集，排序/分组会使用）、`Using filesort`（排序操作未用索引）、`Using join buffer`（连接条件未用索引）、`Impossible where`（where 约束语句可能有问题导致没有结果集）

四、MySQL 调优

4.1 基础优化

4.1.1 缓存优化

MySQL 调整缓冲池大小等参数，同时可引入 Redis 作为缓存层。

提示：InnoDB 使用缓冲池（Buffer Pool）缓存记录和索引。

4.1.2 硬件优化

服务器加内存条、升级 SSD 固态硬盘、把磁盘 I/O 分散在多个设备、配置多处理器。

4.1.3 参数优化

关闭不必要的服务和日志：调优结束后关闭慢查询日志；
调整最大连接数：max_connections；
线程池缓存线程数：thread_cache_size，缓存空闲线程，有连接时直接分配该线程处理连接；
缓冲池大小：innodb_buffer_pool_size。

4.1.4 定期清理垃圾

对于不再使用的表、数据、日志、缓存等，应该及时清理，避免占用过多的 MySQL 资源，从而提高 MySQL 的性能。

4.1.5 使用合适的存储引擎

MyISAM：适合读取频繁、写入较少的场景（因为表级锁、B+ 树叶存地址）。
InnoDB：适合并发写入的场景（因为行级锁、B+ 树叶存记录）。

对比	InnoDB	MyISAM
特点	支持外键和事务	不支持外键和事务
行表锁	行锁，操作时只锁某一行，不对其它行有影响，适合高并发的操作	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作
缓存	缓存索引和数据，对内存要求较高，内存大小对性能有决定性的影响	只缓存索引，不缓存真实数据
关注点	事务：并发写、事务、更大资源	性能：节省资源、消耗少、简单业务、查询快
默认使用	MySQL 5.5 及其之后	MySQL 5.5 之前

补充： InnoDB 支持外键和事务，行锁适合高并发，缓存索引和数据，内存要求高（因为要缓存索引和记录），适合存大数据量，增删改性能更优（行级锁高并发），耗费磁盘（因为有多个非聚簇索引，索引可能比记录空间还大）。MyISAM 不支持外键和事务，表锁不适合高并发，缓存索引和数据地址，内存要求低（因为不用缓存记录），查询性能更优（因为查询时 InnoDB 要维护 MVCC 一致性，且多缓存了记录），节省磁盘（因为磁盘不存完整记录）。

4.1.6 读写分离

读写分离能有效提高查询性能。主从同步用到 bin log 和 relay log。

4.1.7 分库分表

分库分表：数据量级到达千万级以上后，进行垂直拆分（分库）、水平拆分（分表）、垂直 + 水平拆分（分库分表）。

概念：

只分表：单表数据量大，读写出现瓶颈，这个表所在的库还可以支撑未来几年的增长；
只分库：整个数据库读写出现性能瓶颈，将整个库拆开；
分库分表：单表数据量大，所在库也出现性能瓶颈，就要既分库又分表；
垂直拆分：把字段分开。例如 spu 表的 pic 字段特别长，建议把这个 pic 字段拆到另一个表（同库或不同库）；
水平拆分：把记录分开。例如表数据量到达百万，拆成四张 20 万的表。

拆分原则：

数据量增长情况	数据表类型	优化核心思想
数据量为千万级，是一个相对稳定的数据量	状态表	能不拆就不拆，读需求水平扩展
数据量为千万级，可能达到亿级或更高	流水表	业务拆分，面向分布式存储设计
数据量为千万级，可能达到亿级或更高	流水表	设计数据统计需求存储的分布式扩展
数据量为千万级，不应该有这么多的数据	配置表	小而简，避免大一统

分库分表步骤：

MySQL 调优：数据量能稳定在千万级，近几年不会到达亿级，其实是不用着急拆的，先尝试 MySQL 调优，优化读写性能；
目标评估：评估拆几个库、几张表。举例：当前 20 亿，5 年后评估为 100 亿，分几个表？分几个库？一个合理的答案：1024 个表、16 个库。按 1024 个表算，拆分完单表 200 万，5 年后为 1000 万（1024 个表 × 200w ≈ 100 亿）；
表拆分：
- 业务层拆分：混合业务拆分为独立业务、冷热分离；
- 数据层拆分：
  - 按日期拆分：这种使用方式比较普遍，尤其是按照日期维度的拆分，程序层面的改动很小，但扩展性方面的收益很大。例如日维度拆分 test_20191021、月维度拆分 test_201910、年维度拆分 test_2019；
  - 按主键范围拆分：例如【1, 200w】主键在一个表，【200w, 400w】主键在一个表。优点是单表数据量可控；缺点是流量无法分摊，写操作集中在最后面的表；
  - 中间表映射：表随意拆分，引入中间表记录查询的字段值，以及它对应的数据在哪个表里。优点是灵活；缺点是引入中间表让流程变复杂；
  - hash 切分：sharding_key % N。优点是数据分片均匀、流量分摊；缺点是扩容需要迁移数据、跨节点查询问题；
  - 按分区拆分：hash、range 等方式。不建议，因为数据其实难以实现水平扩展；
sharding_key（分表字段）选择：尽量选择查询频率最高的字段，然后根据表拆分方式选择字段；
代码改造：修改代码里的查询、更新语句，让其适应分库分表后的情况；
数据迁移：最简单的就是停机迁移，复杂点的就是不停机迁移，要考虑增量同步和全量同步的问题：
- 全量同步（老库到新库的数据迁移，要控制好迁移效率，解决增量数据的一致性）：
  - 定时任务：定时任务查老库写新库；
  - 中间件：使用中间件迁移数据；
- 增量同步（老库迁移到新库期间，新增删改命令的落库不能出错）：
  - 同步双写：同步写新库和老库；
  - 异步双写（推荐）：写老库，监听 binlog 异步同步到新库；
  - 中间件同步工具：通过一定的规则将数据同步到目标库表；
数据一致性校验和补偿：假设采用异步双写方案，在迁移完成后，逐条对比新老库数据，一致则跳过，不一致则补偿：
- 新库存在、老库不存在：新库删除数据；
- 新库不存在、老库存在：新库插入数据；
- 新库存在、老库存在：比较所有字段，不一致则将新库更新为老库数据；
灰度切读：灰度发布指黑（旧版本）与白（新版本）之间，让一些用户继续用旧版本，一些用户开始用新版本，如果用户对新版本没什么意见，就逐步把所有用户迁移到新版本，实现平滑过渡发布。原则：
- 有问题及时切回老库；
- 灰度放量先慢后快，每次放量观察一段时间；
- 支持灵活的规则：门店维度灰度、百分比灰度；
停老用新：下线老库，用新库读写。

4.2 表设计优化

4.2.1 混合业务分表、冷热数据分表

例如把一个大的任务表，分离成任务表和历史任务表，任务完成后把记录移动到历史任务表。任务表是热数据，历史任务表是冷数据，从而提高查询性能。

4.2.2 联合查询改为中间关系表

例如属性表和属性分组表，不使用连接查询，使用"属性-属性分组表"存储每条属性与"属性关系"的 id。

4.2.3 遵循三个范式

每个属性不可再分、表必须有且只有一个主键、非主键列必须直接依赖于主键。

4.2.4 字段建议非空约束

① 可能查询出现空指针问题；② 导致聚合函数不准确，因为它会忽略 null；③ 不能用 = 判断，只能用 is null 判断；④ null 和其他值运算只能是 null，可能让你不小心把它当成 0；⑤ null 值比空字符更占用空间，空值长度是 0，null 长度是 1 bit；⑥ 不覆盖索引情况下，is not null 无法用索引。

4.2.5 使用冗余字段

虽然列字段不能太多，但为查询效率可增加冗余字段。

4.2.6 数据类型优化

整数类型：考虑好数值范围，前期可以使用 int 保证稳定性。非负数类型要用 UNSIGNED，同样字节数下存储的数值范围更大。主键一般使用 bigint，布尔类型用 tinyint；
能整数就不要用文本类型：跟文本类型数据相比，大整数往往占用更少的存储空间；
避免使用 TEXT、BLOB 数据类型：这两个大数据类型排序时不能使用临时内存表，只能使用磁盘临时表，效率很差，建议别用，或分表到单独扩展表里。LongBlob 类型能存储 4G 文件；
避免使用枚举类型：排序很慢；
使用 TIMESTAMP 存储时间：TIMESTAMP 使用 4 字节，DATETIME 使用 8 个字节，同时 TIMESTAMP 具有自动赋值以及自动更新的特性。缺点是只能存到 2038 年，MySQL 5.6.4 版本可以参数配置，自动修改它为 BIGINT 类型；
DECIMAL 存浮点数：Decimal 类型为精准浮点数，在计算时不会丢失精度，尤其是财务相关的金融类数据。占用空间由定义的宽度决定，每 4 个字节可以存储 9 位数字，并且小数点要占用一个字节。可用于存储比 bigint 更大的整型数据。

4.3 索引优化

4.3.1 考虑索引失效的 11 个场景

① 尽量全值匹配： 查询 age and classId and name 时，(age, classId, name) 索引比 (age, classId) 快。

② 考虑最左前缀： 联合索引把频繁查询的列放左。索引 (a, b, c)，只能查 (a, b, c)、(a, b)、(a)。

③ 主键尽量有序： 如果主键不有序，需要查找目标位置再插入，并且如果目标位置所在数据页满了就必须得分页，造成性能损耗。可以选择自增策略或 MySQL 8.0 有序 UUID 策略。

④ 计算、函数导致索引失效： 计算例如 where num + 1 = 2，函数例如 abs(num) 取绝对值。

⑤ 类型转换导致索引失效： 例如 name = 123，而不是 name = '123'。又例如使用了不同字符集。

⑥ 范围条件右边的列索引失效： 例如 (a, b, c) 联合索引，查询条件 a、b、c，如果 b 使用了范围查询，那么 b 右边的 c 索引失效。建议把需要范围查询的字段放在最后。范围包括：<、<=、>、>= 和 between。

⑦ 没覆盖索引时，"不等于"导致索引失效： 因为"不等于"不能精准匹配，全表扫描二级索引树再回表，效率不如直接全表扫描聚簇索引树。但使用覆盖索引时，联合索引数据量小，加载到内存所需空间比聚簇索引树小，且不需要回表，索引效率优于全表扫描聚簇索引树。

覆盖索引： 一个索引包含了满足查询结果的数据就叫做覆盖索引，不需要回表等操作。

⑧ 没覆盖索引时，左模糊查询导致索引失效： 例如 LIKE '%abc'。因为字符串开头不能精准匹配，原理同⑦。

⑨ 没覆盖索引时，is not null、not like 无法使用索引： 因为不能精准匹配，原理同⑦。

⑩ "OR" 前后存在非索引列，导致索引失效： MySQL 里，即使 or 左边条件满足，右边条件依然要进行判断。

⑪ 不同字符集导致索引失效： 建议 utf8mb4，不同的字符集进行比较前需要进行转换，会造成索引失效。

4.3.2 遵循索引设计原则

命名：索引的字段个数尽量别超过 5 个，命名格式 idx_col1_col2；
在频繁查询（特别是分组、范围、排序查询）的列建立索引；
频繁更新的表，不要创建过多索引；
唯一特性的字段，适合创建索引；
很长的 varchar 字段，适合根据区分度和长度创建前缀索引；
多个字段都要创建索引时，联合索引优于单值索引；
避免创建过多索引，避免索引失效；
尽量用有序的字段作为主键索引： 防止乱序时新主键前移到已满的数据页，导致插入后分裂数据页，造成性能损耗。

4.3.3 连接查询优化

外连接时优先给被驱动表连接字段加索引： 外连接查询时，右表就是被驱动表，建议加索引。因为左表是查所有数据，右表是按条件查询，所以右表的条件字段创建索引价值更高一点；
内连接时优化器自动非索引驱动索引表、小表驱动大表： 先优先选有索引的表做被驱动表。两个表都没有索引时，查询优化器会自动让小表驱动大表。被驱动表的 JOIN 字段创建索引会极大地提高查询效率；
两表连接字段类型必须一致： 两个表 JOIN 字段数据类型保持绝对一致，防止自动类型转换导致索引失效。

4.3.4 子查询优化

关联替代子查询： 能够直接多表关联的尽量直接关联，不用子查询（减少查询的趟数）。子查询是一个 SELECT 查询的结果作为另一个 SELECT 语句的条件。

-- 取所有不为班长的同学（子查询写法）
SELECT a.* FROM student a WHERE a.stuno NOT IN (
    SELECT monitor FROM class b WHERE monitor IS NOT NULL
);

-- 优化成关联查询
SELECT a.* FROM student a LEFT OUTER JOIN class b
ON a.stuno = b.monitor WHERE b.monitor IS NULL;

多次查询代替子查询： 不建议使用子查询，建议将子查询 SQL 拆开结合程序多次查询，或使用 JOIN 来代替子查询。

4.3.5 排序优化

优化器自动选择排序方式： MySQL 支持索引排序和 FileSort 排序。索引保证记录有序性，性能高，推荐使用；FileSort 排序是内存中排序，数据量大时产生临时文件在磁盘里排序，效率低还占用大量 CPU。并不是说 FileSort 一定效率低，一些情况它可能效率高，例如没覆盖索引的左模糊、"不等于"、not null 等索引失效情况下，全表扫描效率比非聚簇索引树遍历再回表更高；
要符合最左前缀： where 后条件和 order by 字段创建联合索引，顺序需要符合最左前缀。例如索引 (a, b, c)，查询 where a = 1 order by b, c；
要么全升序要么全降序： 排序顺序必须要么全部 DESC，要么全部 ASC，乱序会导致索引失效；
待排序数量大时，尽管索引没失效，索引效率不如 filesort： 待排序数据量大约超过一万个，就不走索引走 filesort 了。建议用 limit 和 where 过滤，减少数据量。数据量很大时，索引排序完需要回表查所有数据，性能很差，还不如 FileSort 在内存中排序效率高。并不是说使用 limit 一定会走索引排序，关键看的是数据量，数据量过大时优化器会使用 FileSort 排序；
范围查询右边排序索引失效： 例如索引 (a, b, c)，查询 where a > 1 order by b, c，导致 b、c 排序不能走索引，需要 filesort；
范围查询过滤量大时，优先范围字段加索引： 当【范围条件】和【group by 或 order by】的字段出现二选一时，如果过滤的数据足够多、而需要排序的数据并不多，优先把索引放在范围字段上。这样即使范围查询导致排序索引失效，效率依然比只索引排序字段时候高；如果只能过滤一点点，那就优先索引放到排序字段上；
调优 FileSort： 无法使用 Index 排序时，需要对 FileSort 方式进行调优，例如增大 sort_buffer_size（排序缓冲区大小）和 max_length_for_sort_data（排序数据最大长度）。

4.3.6 分组优化

跟排序基本一个思路。排序分组都比较耗费 CPU，能不用就不用。

where 效率高于 having：where 是分组前过滤，having 是分组后过滤。

4.3.7 深分页查询优化

需求：返回第 2000000 ~ 2000010 条记录。

① 主键有序的表根据主键排序，先过滤再排序： 直接查范围之后的几个数据。

EXPLAIN SELECT * FROM student WHERE id > 2000000 LIMIT 10;

② 主键不有序的表根据主键排序，先给主键分页，然后内连接原表： 当前表内连接排序截取后的主键表，连接字段是主键。因为查主键是在聚簇索引树查，不用回表，排序和分页很快。

EXPLAIN SELECT * FROM student t,
    (SELECT id FROM student ORDER BY id LIMIT 2000000, 10) a
WHERE t.id = a.id;

③ 主键有序的表根据非主键排序： 得到上一页最后一条记录 x，那么目标页码的所有记录 id 都比 x.id 小（因为逆序，且排序依据其实是 age, id，主键自增），目标页码的所有记录 age 都比 x.age 小或等于。

EXPLAIN SELECT * FROM student
WHERE id < #{x.id} AND age >= #{x.age}
ORDER BY age DESC LIMIT 10;

4.3.8 尽量覆盖索引

一个索引包含了满足查询结果的数据，因为不需要回表，所以查询效率高。覆盖索引时"左模糊"和"不等于"不能让索引失效。

示例：

-- 没覆盖索引的情况下，左模糊查询导致索引失效（type=ALL 全表扫描）
CREATE INDEX idx_age_name ON student(age, NAME);

EXPLAIN SELECT * FROM student WHERE NAME LIKE '%abc';

执行计划结果：type=ALL（全表扫描）、rows=7737618、Extra=Using where，说明左模糊 + 非覆盖索引导致索引失效。

补充概念： 索引是高效找到行的方法，但一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行——索引叶子节点存储了它们索引的数据，当能通过读取索引就得到想要的数据，就不需要读取行了。覆盖索引是非聚簇索引的一种形式，它包括在查询里的 SELECT、JOIN 和 WHERE 子句用到的所有列（即建索引的字段正好覆盖查询条件中所涉及的字段）。简单说就是：索引列 + 主键包含 SELECT 到 FROM 之间查询的列。

4.3.9 字符串前缀索引

例如 email(6)，给字符串前缀而不是整个字符串添加索引，前缀长度要根据区分度和长度进行取舍。

MySQL 是支持前缀索引的。默认地，如果创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。

-- 索引包含整个字符串
mysql> alter table teacher add index index1(email);

-- 索引只包含字符串前 6 个字符
mysql> alter table teacher add index index2(email(6));

两种索引的执行过程对比（以 email = 'zhangssxyz@xxx.com' 为例）：

使用 index1（包含整个字符串）： ① 从 index1 索引树找到满足索引值 'zhangssxyz@xxx.com' 的记录，取得 ID2；② 回表到主键查到主键值为 ID2 的行，判断 email 值正确，加入结果集；③ 取下一条记录，发现已经不满足条件，循环结束。整个过程只需要回主键索引取一次数据，系统认为只扫描了一行；
使用 index2（前缀索引 email(6)）： ① 从 index2 索引树找到满足索引值 'zhangs' 的记录，第一个是 ID1；② 回表判断 email 不是目标值，丢弃；③ 取下一条记录，发现仍是 'zhangs'，取出 ID2，回表取整行判断，值对了，加入结果集；④ 重复上一步，直到取到的值不再是 'zhangs'，循环结束。

结论： 使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。区分度越高越好，因为区分度越高，重复的键值越少。

4.3.10 尽量使用 MySQL 5.6 支持的索引下推

索引下推（ICP，Index Condition Pushdown） 是 MySQL 5.6 的新特性，是一种在存储引擎层使用索引过滤数据的优化方式。

如果没有 ICP： 联合索引某字段是模糊查询（非左模糊）时，该字段进行条件判断后，后面几个字段不能用来直接条件判断，必须回表后再判断；
启用 ICP 后： 联合索引某字段是模糊查询（非左模糊）时，该字段进行条件判断后，后面几个字段可以直接条件判断，判断过滤后再回表对不包含在联合索引内的字段条件进行判断。主要优化点是在回表之前过滤，减少回表次数。主要应用：模糊查询（非左模糊）导致索引里该字段后面的字段无序，必须要回表判断，而使用了索引下推，就不需要回表，直接在联合索引树里判断。

举例：

CREATE INDEX idx_name_age ON student(name, age);

-- 索引失效；非覆盖索引时，左模糊导致索引失效
EXPLAIN SELECT * FROM student WHERE name like '%bc%' AND age = 30;

-- 索引成功；MySQL 5.6 引入索引下推，where 后面的 name 和 age 都在联合索引里，可以既过滤又索引，不用回表，索引生效
EXPLAIN SELECT * FROM student WHERE `name` like 'bc%' AND age = 30;

-- 索引成功；name 走索引，age 用到索引下推过滤，classid 不在联合索引里，需要回表
EXPLAIN SELECT * FROM student WHERE `name` like 'bc%' AND age = 30 AND classid = 2;

好处： 某些场景下 ICP 可以大大减少回表次数，提高性能。ICP 可以减少存储引擎必须访问基表的次数和 MySQL 服务器必须访问存储引擎的次数。但是，ICP 的加速效果取决于在存储引擎内通过 ICP 筛选的数据的比例。

4.3.11 写多读少的场景，尽量用普通索引

查询时普通索引和唯一索引效率差不多；更新时普通索引效率更高，因为有 change buffer（写缓存） 将更新后的数据页缓存到内存，下次访问时或后台定期会执行 merge 操作，将该数据页写入磁盘。change buffer 在事务提交时会写入 redo log，保证数据持久化。

普通索引： 不加任何限制条件，如 create index idx_name on student(name)；
唯一索引： UNIQUE 参数限制索引唯一，如 create UNIQUE index idx_name on student(name)。

写缓存（change buffer）详解： 当需要更新一个数据页时，如果数据页在内存中就直接更新；如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作，保证数据逻辑的正确性。

merge： 将 change buffer 中的操作应用到原数据页、得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge；在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。

好处： 将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升；而且数据读入内存需要占用 buffer pool，这种方式还能够避免占用内存，提高内存利用率。唯一索引的更新不能使用 change buffer，实际上也只有普通索引可以使用。

做好区分：

读数据用的是缓冲池 buffer pool；
重做日志有个 redo log buffer，是将缓冲池里更新的数据写入 redo log buffer，事务提交时根据刷盘策略，将 redo log buffer 刷盘到 redo log file 或 page cache。

4.4 SQL 优化

合理选用 EXISTS 和 IN： 遵循小表驱动大表原则，左边表小就用 EXISTS，左边表大就用 IN；
尽量 COUNT(1) 或 COUNT(*)： InnoDB 下 COUNT(1)、COUNT(*) 时，查询优化器会优先选用有索引的、占用空间最小的二级索引树进行统计，只有找不到非聚簇索引树时才使用聚簇索引树统计（空间占用大）。当然也能 COUNT(最小空间二级索引字段)，但麻烦，不如交给优化器自动选择。MyISAM 时就无所谓了，用哪个时间复杂度都是 O(1)；
尽量 SELECT 明确字段： 建议明确字段。查询优化器解析 * 号为所有列名耗费时间，并且 * 号无法使用覆盖索引；
全表扫描时尽量用 "LIMIT"： 当全表扫描时，并且你知道结果集记录数量时，用 limit 限制，这样扫描足够数量后就停止，不再扫描完全表了；如果有索引，就无需用 limit 了；
使用 limit N，少用 limit M, N： 特别是大表或 M 比较大的时候；
将长事务拆为多个小事务： 尽量多使用 COMMIT，用编程式事务而不是声明式事务，降低事务粒度。提交事务可以释放的资源：回滚段上用于恢复数据的信息、锁、redo / undo log buffer 中的空间；
先查再删改： UPDATE、DELETE 语句一定要有明确的 WHERE 条件；
尽量 UNION ALL 而不是 UNION： UNION ALL 不去重，速度更快。

写在最后

本文档整理了 MySQL 调优的完整方法论，核心思路是：先监控、再优化；先索引、再 SQL；先 SQL、再配置；小步快跑，持续改进。调优的本质是：用最少的资源、最快的速度，返回用户想要的数据。

监控报警：Prometheus + Grafana 持续监控关键指标；
排查慢 SQL：慢查询日志 + mysqldumpslow + EXPLAIN 执行计划分析；
基础优化：缓存、硬件、参数、存储引擎选型、读写分离、分库分表；
表设计优化：冷热分表、中间表、三范式、非空约束、冗余字段、数据类型；
索引优化：11 种索引失效场景、设计原则、连接/子查询/排序/分组/深分页优化、覆盖索引、前缀索引、索引下推、普通索引与 change buffer；
SQL 优化：EXISTS/IN、COUNT、明确字段、LIMIT、事务粒度、UNION ALL。

一句话总结：索引加得好，SQL 写得妙，系统跑得快！

posted @ 2026-09-18 14:33 H杰阅读(1) 评论(0) 收藏举报

刷新页面返回顶部

ttsuccess

好好学习，天天向上！