阅读本文你将获得

• ✅ 掌握慢查询日志的配置与分析方法

• ✅ 深入理解 B+ 树索引及回表原理

• ✅ 熟练运用 EXPLAIN 分析 SQL 执行计划

• ✅ 获得真实生产场景的 SQL 优化方案

简介

为什么明明加了索引，SQL 依然跑得慢？面对百万、千万级大表，如何精准定位性能瓶颈？本文基于真实业务场景，从慢查询日志开启、InnoDB 索引 B+ 树原理出发，深度剖析了索引失效、关联查询、分页排序等 10+ 种典型慢查询场景。结合 EXPLAIN 执行计划实战，手把手教你如何将 SQL 耗时从秒级优化至毫秒级，构建一套可复制的数据库性能调优体系。

MySQL慢查询相关优化技术

1.引言

背景

随着数据量的增长及业务的日益复杂，数据库慢查询问题愈发凸显，成为了影响系统性能和用户体验的关键瓶颈。不仅会导致响应时间延长，降低系统的吞吐量，还可能引发一系列连锁反应，如增加服务器负载、占用过多系统资源，甚至影响到整个业务流程的正常运行，在高并发、大数据量的场景下，慢查询问题尤为突出。

目的

深入研究 MySQL 慢查询相关的优化技术，探索更加有效的优化策略和方法，具有重要的理论意义和实际应用价值。

通过本课题的研究，有望为解决实际应用中的慢查询问题提供新的思路和解决方案。

2.MySQL慢查询概述

什么是慢查询?

慢查询是指在MySQL数据库中执行时间较长的查询语句，当系统中存在大量慢查询，会严重影响数据库的性能和响应时间。

慢查询的影响

• 查询性能下降：慢查询需要较长的执行时间，占用了数据库的资源和处理能力，导致其他查询的响应时间延长，整体性能下降。

• 锁定资源：慢查询可能会涉及大量的行或表，导致数据库锁定这些资源，其他查询需要等待，从而造成系统的阻塞。

• 增加系统负载：慢查询需要消耗更多的CPU和内存资源，当并发查询增加时，系统负载会显著上升，可能导致系统崩溃或响应缓慢。

如何开启慢查询日志

Mysql默认慢查询功能是关闭的，当慢查询开关打开后，并且执行的SQL语句达到参数设定的阈值后，就会触发慢查询功能打印出日志。

通过修改MySQL的配置文件（通常为my.cnf或my.ini）来完成的。配置示例如下：

Shell [mysqld] # 是否开启慢查询日志 slow_query_log = 1 # 慢查询日志路径 slow_query_log_file = /var/log/mysql/mysql-slow.log # 单位s long_query_time = 2 # 没有使用索引的SQL是否记录日志 log_queries_not_using_indexes = 1

1.开启慢查询日志

查询是否开启慢查询日志：show variables like 'slow_query_log';

如图所示已是开启状态 ON

2.慢查询时间设置

查询超过多少秒的记录到慢查询日志中：show variables like 'long_query_time';

mysql默认是10s

3.常见导致慢查询的原因

常见查询慢的原因主要有两方面，一个是索引使用不当，一个是大量数据的操作。

索引使用不当是导致慢查询的常见原因之一，比如未创建必要的索引、索引失效、违反最左匹配原则等都会使得数据库在查询时无法有效地利用索引来快速定位数据，从而导致全表扫描或低效的索引扫描，增加查询时间。

表数据量大也是一个重要因素，当表中的数据量巨大时，即使索引使用正确，数据库处理和检索大量数据仍需要耗费较多的时间和资源，此外，大量数据可能导致分页查询、排序、分组等操作的性能下降。

除此之外，还有其他一些可能导致查询慢的原因：

1. 复杂的查询逻辑：查询语句包含多层嵌套、复杂的子查询、多个连接操作或复杂的条件判断，这会增加数据库解析和执行查询的难度和时间。
2. 缺少合适的数据库设计：表结构不合理，例如过度范式化或反范式化，可能影响查询性能。
3. 服务器硬件资源不足：如内存、CPU 性能有限，无法快速处理大量的查询请求。
4. 数据库参数配置不当：例如缓冲区大小、并发连接数等设置不合理。
5. 频繁的并发访问：高并发情况下，数据库的锁竞争可能导致查询等待，从而延长查询时间。
6. 网络延迟：如果数据库服务器与应用服务器之间的网络延迟较高，也会影响查询的响应时间。

索引使用不当

缺失必要索引

1. 进行多表关联操作，但关联列上未创建索引。
2. 对大数据的表进行范围查询（如日期范围、数值范围），而对应的列未建立索引。
3. 经常按照某个列进行排序或分组，但该列没有索引。
4. 数据量不断增长，原本查询性能尚可的表由于数据量增加而变得缓慢，但未及时添加必要索引。

过多或无效索引

1. 因系统变更、业务变动等原因，导致遗留一些不再使用或不再有效的索引。
2. 盲目添加索引，为了优化个别查询，过度添加索引，而没有综合考虑对数据插入、更新和删除操作的性能影响。
3. 多人协作开发：不同的开发人员可能各自创建了一些重复或不必要的索引，而没有进行有效的沟通和协调。

过多或无效的索引会增加数据维护的成本，降低数据写入的性能，并且可能会占用不必要的存储空间，因此需要定期对索引进行审查和优化。

索引失效场景

1.对索引列进行计算、函数操作、类型转换

例如，SELECT * FROM table WHERE FUNCTION(index_column) = value ，这里对索引列使用了函数，可能导致索引失效。

索引列上使用表达式，如 SELECT * FROM table WHERE index_column + 1 = 5 。

索引列类型不匹配，查询条件中的值与索引列的数据类型不一致。

字符集不一致的列匹配也会导致索引失效。

2.违背最左匹配原则

对于联合索引，建立联合索引时会遵守最左匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，如果查询条件不是最左开始，则索引不会生效。

3.范围查询后的列索引失效

比如 <、<=、>、>= 和between等，会导致后面列索引失效，因此建立索引时候对范围查询列放最后。

4.不等于操作/not /like左通配符/or导致索引失效

!= 或 <> 操作会导致索引失效无法被有效使用。

is not null/not like/not in/not exists等操作也会导致索引失效。

like以左通配符开头也会导致索引失效。

or前后存在非索引列会导致索引失效。

大量数据的操作

表数据量过大

随着业务的发展，数据积累迅速增加，当数据量达到一定规模，常规的查询和操作性能显著下降，阿里巴巴开发规范推荐单表行数不超过 500 万行，超过后查询性能会大大下降。

未分页处理

在获取大量数据结果时，如果不进行分页，一次性返回所有数据，会导致增加网络传输负担，容易造成程序OOM，同时对于大量数据的插入、更新、删除操作，应采用批量处理方式，减少与数据库的交互次数，提高效率。

4.慢查询优化技术和策略

MySQL性能优化金字塔

架构优化

选择合适的数据库

系统设计时候就要根据业务需求和数据特点选择合适的数据库，是使用关系型数据库MySQL呢，还是使用非关系型数据库Redis，还是选择搜索引擎ES。

设计合理的表结构

1. 字段类型选择

• 为每个字段选择最恰当的数据类型，以节省存储空间并提高处理效率。例如，对于固定长度的字符串使用 CHAR ，对于可变长度的字符串使用 VARCHAR 。

• 避免使用过大的数据类型，除非确实需要。

2. 表分区、分表、分库、历史表

• 如果表的数据量非常大，可以考虑对表进行分区，例如按照时间、范围或哈希值进行分区，以提高查询和维护性能。

• 如果表中有过大的数据类型或者表过宽，建议进行垂直分表，将字段进行拆分。

• 如果一个表中数据量过大，且无法进行拆分，考虑将历史数据进行定期迁移到历史表。

缓存

mysql优化

1.索引优化

索引原理

MySQL官方对索引定义：是存储引擎用于快速查找记录的一种数据结构，需要额外开辟空间和数据维护工作。

• InnoDB引擎采用B+树结构创建索引，通过物理数据页存储，在数据.ibd文件文件中，利用数据页(page)存储。

• 索引可以加快检索速度，但是同时也会降低增删改操作速度，索引维护需要代价，因此索引创建需要权衡利弊。

下图为B+树在MySQL中主键索引的存储结构，其中叶子节点存储整条记录，非业主节点存储主键字段。

索引类型

1.主键（聚集）索引

主键索引是唯一索引的特殊类型，一个表只能有一个主键索引，且不允许有空值，InnoDB 存储引擎会自动为主键创建主键索引，如果没有显式定义主键，InnoDB 会选取一个唯一索引（如果存在）作为主键索引，如果表中既没有主键也没有唯一索引，InnoDB 会自动生成一个隐藏的、递增的列作为主键索引。

主键索引中B+树的叶子节点中存放了索引值和行记录。

2.辅助索引

辅助索引是最基本的索引类型，没有任何约束（即索引列的值可以重复），主要用于提高查询效率。

辅助索引叶子节点存放主键数据。

3.回表

辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引（聚集索引）来找到一个完整的行记录。这个过程也被称为回表。也就是根据辅助索引的值查询一条完整的用户记录需要使用到2棵B+树----一次辅助索引，一次聚集索引。

4.唯一索引

唯一索引保证索引列的值唯一，但允许有空值（一个表中可以有多个唯一索引）。

5.单值索引

单值索引，也称为单列索引，是指索引建立在表的一个列上的索引，这种索引可以加速对该列的查询操作，在创建单值索引时，你只需要指定一个列名，如果表中的数据在该列上有很大的差异（即数据分布广泛），那么这种索引将非常有效。

例如，假设你有一个名为user的表，其中有一个名为email的列，并且你希望在这个列上加速查询，你可以创建如下的单值索引：

SQL CREATE INDEX idx_email ON user(email);

6.联合索引

联合索引，也称为复合索引或多列索引，是指索引建立在表的多个列上的索引。这种索引可以加速对这些列组合在一起的查询操作，当查询条件中包含了索引中的列（并且列的顺序与索引中的顺序一致，或者索引中的前缀列）时，MySQL 可以利用这个索引来加速查询。

例如，如果你希望加速对user表中name和phone_number列的组合查询，你可以创建一个联合索引：

SQL CREATE INDEX idx_name_phonNumber ON user(name, phone_number);

7.最左匹配原则

在 MySQL 的索引中，最左匹配原则是指在创建联合索引（即基于多个列创建的索引）时，查询语句中使用索引的条件必须从索引的最左侧列开始，并且按照索引定义的列顺序依次匹配，才能使用到该索引。

例如，如果创建了一个联合索引 (a, b, b) ，那么以下查询可以使用到该索引：

1. WHERE a=?
2. WHERE a=? AND b=?
3. WHERE a=? AND b=? AND c=?

而以下查询则不能完全使用该索引：

1. WHERE b=? （跳过了最左列 a）
2. WHERE b=? AND b=? （跳过了最左列 a）

最左匹配原则的主要原因是 MySQL 索引的组织结构和存储方式，遵循最左匹配原则可以充分利用索引来提高查询效率，减少数据的检索范围。

8.覆盖索引

当一个索引包含了查询中所需要的全部列数据时，就称为覆盖索引，这意味着数据库在执行查询时，只需要通过索引本身就能够获取到满足查询条件的所有数据，而无需再根据索引回表查询实际的数据行，从而减少了磁盘 I/O 操作，显著提高查询效率。

例如，假设有一个表user ，包含列 id 、 name 、age 、 grade ，如果有一个索引是 (id, name, age)，而执行的查询语句是 SELECT id, name, age FROM user WHERE id = 10 ，那么这个索引就是覆盖索引，因为通过这个索引就能够直接获取到查询所需的全部列数据。

9.降序索引

默认创建的普通索引是根据索引列升序创建的，在一些下面场景中创建降序索引可以提高查询性能

• 时间序列数据：对于按照时间戳排序的数据，降序索引可以更快地获取最新的记录。

• 排序列表：在处理需要按照某个字段降序排列的列表时，降序索引可以提高查询性能。

对于这些场景，降序索引可以大大降低查询过程中MySQL的IO次数。

下面是为user表中的modify_time字段创建降序索引的方式：

SQL CREATE INDEX index_modify_time ON user(modify_time DESC);

10.前缀索引

前缀索引就是对文本的前几个字符建立索引（具体是几个字符在建立索引时指定），这样建立起来的索引更小，所以查询更快。

一般来说使用前缀索引，可能都是因为整个字段的数据量太大，没有必要针对整个字段建立索引，前缀索引仅仅是选择一个字段的部分字符作为索引，这样一方面可以节约索引空间，另一方面则可以提高索引效率。

例如，假设有一个表 user，包含email列（`email` varchar(128) COMMENT 'Email'），如果要根据email查询user信息。

SQL select * from user where email='xxx@email.com';

则可以创建下面索引

SQL CREATE INDEX index_prefix_email ON user(email(16));

可以看到原user表中email长度为128，创建前缀索引16位，索引大小成倍减小

如何确认前缀索引长度？

SQL SELECT MAX(LENGTH(email)) FROM account_db.user; 34 SELECT COUNT(DISTINCT email) / COUNT(*) FROM account_db.user; 0.9402 SELECT COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) FROM account_db.user; 0.9344 SELECT COUNT(DISTINCT LEFT(email, 15)) / COUNT(*) FROM account_db.user; 0.9401 SELECT COUNT(DISTINCT LEFT(email, 16)) / COUNT(*) FROM account_db.user; 0.9402

可以看到16位的时候100w数据可以唯一性识别。

11.函数索引

函数索引是一种特殊类型的索引，它基于表中列上的函数或表达式的值创建。

例如，如果user表中有一个存储注册日期的列 create_date，如果需要根据注册年份来进行查询，则可以创建函数索引如下：

SQL CREATE INDEX idx_year ON user (YEAR(create_date))；

函数索引的优点在于：

1. 提高特定查询的性能：对于那些基于函数或表达式结果的频繁查询，能够加快数据检索速度。
2. 优化复杂查询：当查询条件涉及对列值的复杂计算或转换时，函数索引可以避免在查询执行时进行实时计算，从而提高效率。

优化手段

使用合适的索引

• 选择区分度比较高的，且经常用于查询、连接和排序的列创建索引。

• 遵循最左匹配原则，建立组合索引时候，区分度高的字段放最左边。

• 查询尽量走覆盖索引，避免回表。

• 多表关联，关联字段必须有索引。

• order by相关字段建议走索引。

• 对varchar类型长度很长的字段建立前缀索引，提高查询效率。

• 避免索引失效问题（如避免查询字段的函数操作和表达式操作，如有必要创建函数索引代替、in代替!=、范围查询放条件最后、避免%开头的模糊查询等）。

合理的表设计

• 避免同一个字段在不同表数据类型不一致，如表a中id为int类型，表b中的a_id为varchar类型，如果表a通过id关联表b的a_id查询会导致因字段类型不同造成的隐式转换，导致索引失效走全表扫描。

• 合理评估字段可能存储的数据最大长度，如age，明确不超过3位数，设计成int(3)而不是其他数值。

• 字段必须指定长度，如int类型不指定长度，MySQL默认11位，如果这时候只使用了一位，大大浪费了磁盘空间，同时也降低了MySQL的查询效率。

定期维护索引

• 定期检查和更新索引

• 删除不再使用或低效的索引。

2.查询语句优化

MySQL Explain执行计划分析

在 MySQL 中，EXPLAIN 用于查看 SQL 语句的执行计划，帮助分析查询性能并进行优化。通过 EXPLAIN 命令，你可以了解数据库如何处理你的查询，包括表的读取顺序、数据读取操作类型、索引使用情况、表之间的引用关系以及优化器查询的行数等信息。

如下：

SQL EXPLAIN SELECT         a.* FROM         account_db.USER a,         account_db.user_credentials b WHERE         a.uid = b.uid         AND a.create_time BETWEEN '2024-06-01 00:00:00'         AND '2024-06-18 23:59:59' ORDER BY         a.modify_time DESC         LIMIT 0,         20;

执行计划如下：

id列

在 MySQL 的 EXPLAIN 结果中，id 列表示查询中执行 SELECT 子句或操作表的顺序。id 列的值具有以下含义和规则：

1. id 相同：如果多个行的 id 值相同，可以认为它们是一组。在这一组内，执行顺序由上至下，即按照查询结果中出现的顺序依次执行。

1. id 不同：如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行。

1. id 相同与不同同时存在：这种情况下，id 相同的行可以看作一组，在组内按照从上往下的顺序执行。而在所有组中，id 值越大的组，其执行优先级越高，会越先执行。

select_type列

在 MySQL 中，EXPLAIN 命令输出结果中的 select_type 列用于描述查询的类型，常见的类型包括：

1. SIMPLE：简单的 SELECT 语句，不包含子查询或关联操作。

1. PRIMARY：最外层的查询，如果有子查询，那么子查询中的最外层查询也被标记为 PRIMARY。

1. SUBQUERY：包含在SELECT中的子查询（不在FROM 子句中） 。

1. DERIVED：包含在 FROM 子句中查询被标记为 DERIVED（衍生表）。

1. UNION：出现在 UNION 操作中的第二个及以后的 SELECT 语句。

1. UNION RESULT：UNION 的结果。

通过了解 select_type 的值，可以更好地理解查询的结构和执行方式，有助于优化查询性能。

table列

表示此次查询中所涉及的表的名称。通过查看该字段，可以明确查询语句正在操作哪个表。这对于理解查询的执行计划和优化查询非常有帮助，能直观地了解到数据的来源表。

partitions列

分区表查询才会显示，表示查询落到哪个分区上。

type列

在 MySQL 的 EXPLAIN 输出结果中，type 列表示访问类型，即 MySQL 决定如何查找表中的行。常见的访问类型及其性能由最差到最优的顺序如下：

• ALL：全表扫描，意味着 MySQL 必须从头到尾扫描整张表，去查找匹配的行，性能极差。但如果在查询里使用了 LIMIT n，虽然 type 依然是 ALL，但 MySQL 只需要扫描到符合条件的前 n 行数据，就会停止继续扫描。

• index：跟 ALL 一样会进行全表扫描，只是 MySQL 会按索引次序进行全表扫描，而不是直接扫描行数据。它的主要优点是避免了排序；最大的缺点是要承担按索引次序读取整个表的开销。若是按随机次序访问行，开销将会非常大。若在 Extra 列中看到“Using index”，说明 MySQL 正在使用覆盖索引，索引的数据中包含了查询所需的所有字段，因此只需要扫描索引树就能够完成查询任务，比按索引次序全表扫描的开销要少很多。

• range：范围扫描，通常出现在 IN()、BETWEEN 、> 、< 、 >= 、 <=等操作中，使用一个索引来检索给定范围的行。

• index_subquery：子查询中的索引查找。

• unique_subquery：子查询中的唯一索引查找。

• index_merge：表示使用了索引合并的优化方法，索引合并是指对同一个表的多个索引分别进行条件扫描，然后将它们各自的结果进行合并。

• ref_or_null：类似 ref，但是可以搜索值为 NULL 的行。

• ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。

• eq_ref：使用主键索引（primary key）或非空唯一索引（unique not null）进行等值扫描，对于前表中的每一行，后表最多只会返回一条符合条件的记录，这是除了 const 之外较好的连接类型。简单的 SELECT 查询通常不会出现这种 type。

• const/system：表示常量连接，当查询的某部分被优化并转化成一个常量时会出现。例如，用于主键或唯一索引的所有列与常数进行比较，表最多有一个匹配行，读取 1 次，速度比较快。如果表中只有一条记录匹配，则 type 为 system，这是 const 联接类型的特例。

• NULL：表示查询语句在执行过程中被优化，不用访问表，直接从索引中得到值，如在索引列中选取最小值。

优化查询的一个重要目标是尽量减少全表扫描，通过合理创建索引等方式，使查询使用更优的访问类型，如 eq_ref、ref 等，以提高查询性能，一般至少要优化到range级别。

possible_keys列

在 MySQL 的 EXPLAIN 命令输出结果中，possible_keys 列表示可能应用在此次查询中的索引，是MySQL 基于表结构和查询语句所推测出的可能有助于提高查询性能的索引，但查询时候不一定使用。

key列

MySQL 在实际执行查询时使用的索引。

key_len列

在 MySQL 的 EXPLAIN 命令输出结果中，key_len 列表示使用的索引字段的长度。

它可以帮助您了解索引的使用情况和计算索引所占用的存储空间。

通过分析 key_len 的值，可以判断查询是否有效地使用了索引，以及是否存在索引过度或不足的情况，从而有助于优化查询和索引设计。

key_len计算规则如下：

1. 所有索引字段，如果没有设置 NOT NULL，则需要加 1 个字节。这是因为需要额外的空间来标记该字段是否为 NULL。

1. 定长字段的长度计算：

• tinyint：占 1 个字节；

• int：占 4 个字节；

• bigint：占 8 个字节；

• date：占 3 个字节；

• datetime：占 5 个字节；

• char(n)：占 n 个字符。

1. 变长字段（如 varchar(n)）：则是 n 个字符 + 2 个字节，其中的 2 字节用于存储字符串的长度信息。
2. 不同的字符集，一个字符占用的字节数不同：

• latin1 编码：每个字符占用 1 个字节；
• gbk 编码：每个字符占用 2 个字节；

• utf8 编码：每个字符占用 3 个字节；

• utf8mb4 编码：每个字符占用 4 个字节。

索引长度的计算公式为：（Character Set：utf8mb4=4, utf8=3, gbk=2, latin1=1）× 列长度 + 1（允许 null）+ 2（变长列）

例如，对于一个使用 utf8 编码的表，其中有一个定义为 varchar(20) 且不为空的字段，那么它的索引长度为：20×3 + 2 = 62 字节。其中 20×3 是字符占用的字节数（每个字符 3 字节），+2 是变长字段所需的额外 2 字节。

再如，一个定义为 int 且不为空的字段，其索引长度就是 4 字节，因为 int 类型固定占用 4 字节，且无需额外的字节来标记是否为 NULL。

MySQL 的 InnoDB 引擎对索引长度有限制，最大为 767 字节。

ref列

在 MySQL 的 EXPLAIN 命令输出结果中，ref 列显示了在索引的比较中，被用于与索引列进行对比的列或常量。

常见的情况包括：

• 如果是一个常量值，例如数字或字符串，会显示为 const。

• 如果是与其他表的列进行关联比较，会显示关联表的相关列名。

例如，如果查询条件是 WHERE table1.column1 = table2.column2 ，对于 table1 的 EXPLAIN 结果，ref 列可能会显示 table2.column2 。

ref 列的值可以帮助您了解查询是如何利用索引进行关联和比较操作的，对于优化查询性能有一定的参考价值。如果 ref 列的值不理想（例如显示为 NULL ），可能需要重新审视索引的创建和查询语句的编写。

rows列

在 MySQL 的 EXPLAIN 命令输出结果中，rows 列表示 MySQL 预估在执行查询时需要扫描的行数。

这个预估值是基于表的统计信息和索引等因素计算得出的。

较小的 rows 值通常意味着查询效率可能较高，因为需要处理的数据量较少。如果 rows 值较大，可能需要考虑优化查询，例如添加合适的索引、改进查询语句的逻辑等，以减少需要扫描的行数，提高查询性能。

filtered列

在 MySQL 的 EXPLAIN 命令输出结果中，filtered 列表示按表条件过滤后，剩下的结果集的估计百分比。

例如，filtered 值为 50 ，表示经过条件过滤后，大约剩下 50% 的行。

这个值有助于了解查询条件的筛选效果。值越高，说明筛选效果越好，返回的结果集越符合预期，通常也意味着查询的效率可能相对较高。

Extra列

在 MySQL 的 EXPLAIN 命令输出结果中，Extra 列提供了有关查询执行的额外信息，这些信息对于理解查询的执行方式和性能特征非常有用。以下是一些常见的 Extra 值及其含义：

1. Using index：表示查询使用了覆盖索引，即只需要从索引中就能获取到满足查询所需的全部数据，无需回表查询数据行。

1. Using where：表示 MySQL 服务器在存储引擎检索行后再进行过滤。这意味着索引用于查找匹配的行，但不是所有的结果都满足查询条件，还需要进行额外的过滤。

1. Using temporary：表示 MySQL 需要创建一个临时表来存储结果以完成查询。这通常发生在查询包含了排序、分组操作，而不能通过索引直接完成这些操作时。

1. Using filesort：表示 MySQL 无法利用索引完成排序操作，需要额外进行文件排序。这可能会影响查询性能。

1. Impossible WHERE：表示 WHERE 子句的条件总是为假，因此没有返回任何行。

1. Select tables optimized away：表示优化器发现一个查询语句可以直接从索引中获取结果，无需实际访问表。

1. Using index condition：表示查询使用了索引下推，在没有索引下推的情况下，MySQL 会根据索引获取到匹配的行，然后回表获取完整的行数据，再根据 WHERE 子句中除索引列之外的条件进行过滤。而有了索引下推之后，在根据索引获取数据时，会同时将 WHERE 子句中可以在索引中进行判断的条件下推到存储引擎层进行处理，存储引擎根据这些条件先对索引中的数据进行过滤，只返回满足条件的数据给服务器层，减少了回表的次数和需要传输的数据量，从而提高查询性能。

通过分析 Extra 列的值，可以更好地理解查询的执行过程，发现潜在的性能问题，并采取相应的优化措施。

优化手段

1.避免全表扫描

尽量通过索引来定位数据，减少全表扫描的发生。例如，使用准确的条件过滤，避免使用LIKE '%value'这种无法利用索引的模糊查询。

2.限制返回结果集

只获取实际需要的列和行数，使用LIMIT关键字来限制结果集的大小，而不是查询所有字段和行数。

3.简化查询逻辑

减少不必要的子查询、复杂的连接和多层嵌套，尽量将复杂的查询分解为简单的步骤。

4.关联查询优化

join的原理

join包括内连接和外连接，常用的inner join和left join，本质是各表的数据循环匹配，一个表驱动另一个表来返回结果，对inner join查询优化器会根据情况选择驱动表和被驱动表，而left join通常左边的是驱动表，右边的是被驱动表。

left join一定是左表驱动右表吗？

SQL EXPLAIN SELECT         a.* FROM         account_db.user_credentials a left join         account_db.user b on a.uid = b.uid WHERE         b.gender = 0         LIMIT 0,         20;

SQL EXPLAIN SELECT         a.* FROM         account_db.user_credentials a left join         account_db.user b on a.uid = b.uid WHERE         b.gender = 0 and a.status = 0         LIMIT 0,         20;

SQL EXPLAIN SELECT         a.* FROM         account_db.user_credentials a left join         account_db.user b on a.uid = b.uid and b.gender = 0 LIMIT 0,20;

可以看出如果left join 右边表条件在where后面，查询优化器会根据扫描行数选择数量最小的表作为驱动表，而如果查询条件放置在on后面则不会，其他情况都是遵循左表为驱动表，右表为被驱动表规则。

关联字段为什么必须添加索引

如果不存在索引，mysql采用的是快速嵌套循环连接（Block Nested-Loop Join），即将驱动表中的数据一块块放入join buffer缓存去，然后在全表扫描被驱动表，被驱动表每一条记录和join buffer中的记录进行匹配。

快速嵌套循环连接（Block Nested-Loop Join）

其中join buffer存放了驱动表要查询的字段和关联的字段，因此查询时候尽量减少查询字段可以在join buffer中一次加载更多的驱动表数据，减少IO次数。

存在索引，MySQL会选择索引嵌套循环连接（Index Nested-Loop Join）, 通过驱动表匹配条件直接与被驱动表的索引进行匹配，避免与被驱动表每条记录进行比较，极大减少了与被驱动表的IO次数。

索引嵌套循环连接（Index Nested-Loop Join）

如果此时被驱动表的索引中包含了连接条件和查询结果则不用回表。

5.子查询优化

子查询是MySQL的一项重要的功能，可以帮助我们通过一个SQL语句实现比较复杂的查询，但是，子查询的执行效率不高。

子查询会生成临时表，临时表是无法使用索引。

对结果集比较大的子查询可以使用连接JOIN来代替，性能会更好。

如果预估结果集较小则可以使用。

使用 NOT IN 或者 NOT EXISTS，可以用 LEFT JOIN xxx ON xx WHERE xx IS NULL替代，或者对NOT IN一个枚举类中的某些值，可以修改为IN其他值。

6.排序/分组优化

排序的原理

对排序和分组字段建立索引，避免使用文件排序(filesort)可以提高查询效率，如果无法使用索引排序则需要对filesort进行优化。

单路排序（快）

1. 一次IO将需要查询的字段和排序字段一起加载到sort_buffer中。
2. 在sort_buffer进行排序。
3. 返回结果。

多路排序（慢）

1. 第一次IO将排序字段从文件加载到sort_buffer中。
2. 在sort_buffer中进行排序。
3. 第二次IO去文件中取其他字段
4. 返回结果。

默认采用单路排序，但如果查询数据较多，超出了sort_buffer大小，则会每次取sort_buffer容量大小的数据排序，再创建temp文件进行多路合并。

优化

1. 可以通过尝试提高 sort_buffer_size值，InnoDB存储引擎默认1M。

SQL -- 查看当前的 sort_buffer_size 值 SHOW VARIABLES LIKE 'sort_buffer_size'; -- 设置 sort_buffer_size 为 2MB SET sort_buffer_size = 2097152;

1. Order by时select *是一个大忌，减少查询字段，只查询必要字段，可以看到查询效率大大增加。

select *

SQL SELECT SQL_NO_CACHE * FROM user t order by t.STATUS limit 10000,10; 执行时间 4.328s

查询部分字段

SQL SELECT SQL_NO_CACHE t.job_id, t.uid, t.create_time FROM user t order by t.STATUS limit 10000,10; 执行时间 0.552s

7.优化分页查询

优化前：根据uid排序，分页查询user表中数据，explain分析可知采用全表扫描方式

SQL SELECT * FROM user t ORDER BY uid limit 2000000,10; 执行时间 0.75s

优化后：先在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容，explain分析可知MySQL先根据user表主键索引查询出符合条件的10条uid生成临时表，再用临时表去关联user的主键索引去查询数据。

SQL SELECT * FROM user t,(SELECT uid FROM user ORDER BY uid LIMIT 2000000,10) a WHERE t.uid = a.uid; 执行时间 0.405s

速度快了近一倍。

8.Limit 1优化和唯一索引优化

1.如果能确认结果唯一，则在SQL添加Limit 1的查询效率会远高于未添加的语句。

加limit 1前

SQL select SQL_NO_CACHE * FROM user t where t.reg_ts = "1600251723184" 执行时间 1.783s

加limit 1后

SQL select SQL_NO_CACHE * FROM user t where t.reg_ts = "1600251723184" limit 1 执行时间 0.097s

2.唯一索引优化

效果和limit 1一样，业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引，唯一索引虽然会影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的，另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

硬件/OS优化

• 使用高速的 SSD 磁盘而不是传统的 HDD 磁盘，可以显著提高磁盘 I/O 性能。

• 选择适合数据库操作的文件系统，如 EXT4 。

• 选择具有高核心数和高频率的 CPU，以处理并发的数据库操作。

• 确保 MySQL 有足够的内存可用，避免其他进程过度占用资源。

• 增加打开文件的限制（ulimit -n ），1024-》设置为65535，以支持更多的数据库连接和文件操作。

5.案例分析

网站端相关表

account_db

user

目前生产环境250w数据

SQL CREATE TABLE `user` (   `uid` bigint NOT NULL COMMENT 'User ID',   `create_time` datetime DEFAULT NULL COMMENT '创建时间',   `modify_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',   `modify_user_id` varchar(64) COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '修改人ID',   `avatar` varchar(1500) COLLATE utf8mb4_general_ci DEFAULT NULL,   `birthday` datetime DEFAULT NULL COMMENT 'User Birthday, UTC',   `email` varchar(128) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Email',   `gender` int DEFAULT '0' COMMENT 'User Gender,0:not specified,1:male,2:female',   `intro` text COLLATE utf8mb4_general_ci COMMENT 'User Introduction',   `job_id` bigint DEFAULT NULL COMMENT 'Job Id',   `name` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'User Name, must be unique',   `phone_number` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Phone Number',   `phone_region` varchar(16) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Phone Region',   `reg_client_ip` varchar(128) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Register Client Ip',   `reg_client_region` varchar(16) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Register Client Region',   `reg_client_type` int DEFAULT '0' COMMENT 'Register Client Type,0:android app,1:ios app,2:pc web,3:mobile web',   `status` int DEFAULT '1' COMMENT 'User Status,0:not activated,1:normal,2:disabled',   `user_group` int DEFAULT '0' COMMENT 'User Group,0:Normal,1:Inner Group,2:Outer Group,3:Dev Group',   `reg_ts` bigint DEFAULT NULL COMMENT 'Register Time',   `access_right` bigint DEFAULT '0' COMMENT 'User access right',   `reg_client_device` varchar(1024) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Register Client Device',   `register_type` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Register Type,0:Email、1:Phone、2:Google、3:Facebook',   PRIMARY KEY (`uid`),   UNIQUE KEY `unique_u_name` (`name`),   KEY `idx_u_email` (`email`),   KEY `idx_u_phone_number` (`phone_number`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

表结构优化

avatar 头像长度varchar(1500)明显过长，不过生产存在长度为1034长度的头像，可以通过短链服务对url长度进行优化，限制在20位左右。

birthday 格式应该是年月日 使用datetime占用8个字节，date类型占用3个字节。建议datetime -》 date类型

intro text类型，目前生产最大538位，建议将字段拆分出去。

reg_client_type、register_type、status字段 使用int类型（4个字节），建议修改为 tinyint 4个字节->1字节

reg_client_device 业务无关字段建议拆分出去

慢查询SQL优化

user相关慢查询次数14674次

1. 慢查询SQL1

本地执行：耗时1.242s

业务场景：PMS导出V1/列表查询。

SQL select this_.uid as uid1_11_0_, this_.create_time as create_t2_11_0_, this_.modify_time as modify_t3_11_0_, this_.modify_user_id as modify_u4_11_0_, this_.access_right as access_r5_11_0_, this_.avatar as avatar6_11_0_, this_.birthday as birthday7_11_0_, this_.gender as gender8_11_0_, this_.intro as intro9_11_0_, this_.job_id as job_id10_11_0_, this_.name as name11_11_0_, this_.reg_client_device as reg_cli12_11_0_, this_.reg_client_ip as reg_cli13_11_0_, this_.reg_client_region as reg_cli14_11_0_, this_.reg_client_type as reg_cli15_11_0_, this_.reg_ts as reg_ts16_11_0_, this_.register_type as registe17_11_0_, this_.status as status18_11_0_, this_.user_group as user_gr19_11_0_ from user this_ where this_.user_group=0 and this_.status=1 order by this_.modify_time desc limit 10000

执行计划如下，可以看出走的全表扫描，使用了文件排序

优化一：减少非必要字段查询，只查询所需字段

SQL SELECT SQL_NO_CACHE         this_.uid AS uid1_11_0_,         this_.reg_client_region AS reg_cli14_11_0_,         this_.birthday AS birthday7_11_0_,         this_.gender AS gender8_11_0_,         this_.reg_ts AS reg_ts16_11_0_,         this_.register_type AS registe17_11_0_,         this_.intro AS intro9_11_0_,         this_.job_id AS job_id10_11_0_,         this_.user_group AS user_gr19_11_0_ FROM         user this_ WHERE         this_.user_group = 0         AND this_.STATUS = 1 ORDER BY         this_.modify_time DESC         LIMIT 10000

优化后时间1.242s -> 0.790s，可以看到查询效率有较大提升。

优化二：通过子查询优化分页查询

SQL SELECT SQL_NO_CACHE         this_.uid AS uid1_11_0_,         this_.reg_client_region AS reg_cli14_11_0_,         this_.birthday AS birthday7_11_0_,         this_.gender AS gender8_11_0_,         this_.reg_ts AS reg_ts16_11_0_,         this_.register_type AS registe17_11_0_,         this_.intro AS intro9_11_0_,         this_.job_id AS job_id10_11_0_,         this_.user_group AS user_gr19_11_0_ FROM         user this_         INNER JOIN (         SELECT SQL_NO_CACHE                 this_.uid         FROM                 user this_         WHERE                 this_.user_group = 0                 AND this_.STATUS = 1         ORDER BY                 this_.modify_time DESC                 LIMIT 10000         ) t WHERE         this_.uid = t.uid

执行计划如下：

优化后时间0.790s -> 0.597s 有部分提升

优化三：通过在排序字段上创建索引优化文件排序

SQL ALTER TABLE `account_db`.`user` ADD INDEX `idx_modify_time`(`modify_time`) USING BTREE;

执行计划如下，可以看到已经不走文件排序了，使用的索引排序，通过回表方式进行过滤数据：

优化后时间0.597s -> 0.442s 再次提升

优化四：创建索引优化子查询

SQL ALTER TABLE `account_db`.`user` ADD INDEX `idx_user_group_status_time`(`user_group`, `status`, `modify_time`) USING BTREE;

执行计划如下，可以看到子查询即使用了索引查询，又使用索引排序：

优化后时间0.442s -> 0.172s 显著提升

优化五：创建降序索引进一步优化查询效率

SQL ALTER TABLE `account_db`.`user` ADD INDEX `idx_user_group_status_time`(`user_group`, `status`, `modify_time` DESC) USING BTREE;

执行计划如下，可以看到子查询走的覆盖索引using index：

优化后时间0.172s -> 0.169s，有部分提升。

优化六：表结构优化

待测试。

当然覆盖索引只是理想中的，结合业务来看，子查询可能涉及多个查询条件，优化到第三步已经可以了。

2.慢查询SQL2

本地执行：耗时3.315s

业务场景：假用户查询。

SQL select this_.uid as uid1_11_0_, this_.create_time as create_t2_11_0_, this_.modify_time as modify_t3_11_0_, this_.modify_user_id as modify_u4_11_0_, this_.access_right as access_r5_11_0_, this_.avatar as avatar6_11_0_, this_.birthday as birthday7_11_0_, this_.gender as gender8_11_0_, this_.intro as intro9_11_0_, this_.job_id as job_id10_11_0_, this_.name as name11_11_0_, this_.reg_client_device as reg_cli12_11_0_, this_.reg_client_ip as reg_cli13_11_0_, this_.reg_client_region as reg_cli14_11_0_, this_.reg_client_type as reg_cli15_11_0_, this_.reg_ts as reg_ts16_11_0_, this_.register_type as registe17_11_0_, this_.status as status18_11_0_, this_.user_group as user_gr19_11_0_ from user this_ where this_.user_group=2 order by this_.create_time desc

执行计划如下：

优化一：减少非必要字段查询，只查询所需字段

业务分析可知需要用到的字段只有uid

SQL SELECT         this_.uid AS uid1_11_0_ FROM         user this_ WHERE         this_.user_group = 2 ORDER BY         this_.create_time DESC

执行计划如下：

优化后时间3.315s -> 0.781s，显著提升。

优化二：排除不需要的排序字段

分析可以这边不用排序

SQL SELECT         this_.uid AS uid1_11_0_ FROM         user this_ WHERE         this_.user_group = 2

执行计划如下：

优化后时间 0.781s -> 0.547s，部分提升。

优化三：流式或者分页查询防止系统OOM

此处进行全表查询，涉及数据量大，不采用流式查询/分页查询易造成系统OOM。

3.慢查询SQL3

本地执行：耗时0.51s

业务场景：查询全部Register region接口。

SQL SELECT SQL_NO_CACHE DISTINCT         reg_client_region FROM user WHERE         reg_client_region IS NOT NULL         AND reg_client_region <> 'null'         AND reg_client_region <> ''

优化一：创建查询字段的索引

SQL    CREATE INDEX idx_reg_client_region ON user(reg_client_region);

执行计划如下，可以看到使用了索引：

优化后时间 0.51s -> 0.120s，显著提升。

优化二：考虑reg_client_region维护枚举字段or代码添加缓存

考虑维护枚举字段or代码添加缓存。

6.总结

MySQL 慢查询优化是一项系统工程，需要从表结构设计、索引策略、SQL 编写规范以及硬件配置多维度切入。

• 设计阶段：遵循字段最小化、范式平衡。

• 开发阶段：善用 EXPLAIN，避免索引失效。

• 运维阶段：持续监控慢日志，定期维护索引碎片。

掌握底层 B+ 树原理与执行计划分析，是为系统稳定运行保驾护航的关键技能。