MySQL 优化实践

1. SQL 优化的重要性

1.1 提高系统性能

通过优化 SQL 查询，可以减少查询执行时间，降低系统资源的占用，从而提高整个数据库系统的性能和吞吐量。

1.2 改善用户体验

优化后的 SQL 查询能够更快速地返回结果，减少用户等待时间，提升用户体验和满意度。

1.3 节约资源成本

优化 SQL 可以减少系统资源的占用，包括 CPU、内存和磁盘 IO 等，从而节约硬件资源成本，延长硬件使用寿命。

1.4 提升系统稳定性

优化 SQL 可以减少数据库锁的竞争，降低死锁的概率，提升系统的稳定性和可用性。

1.5 降低维护成本

优化后的 SQL 查询可以减少数据库管理员的维护工作，减轻系统维护的负担，降低维护成本。

1.6 符合业务需求

优化 SQL 可以使数据库系统更好地满足业务需求，提供更加及时和准确的数据支持，有利于业务发展和决策分析。

2. SQL 优化基础

2.1 核心知识点概览

SQL 优化基础主要包含以下四个方面：

查询语句的结构：理解 SELECT 语句的语法和执行顺序
索引设计：掌握索引类型、数据结构和创建原则
数据库表设计：遵循表设计规范和最佳实践
查询执行计划：使用 EXPLAIN 分析查询性能

2.2 数据库表设计规范

A. 命名规范 && 字符集

数据库表名、字段名、索引名等都需要命名规范（见名知义）
数据库库、表、开发程序等都需要统一字符集

B. 合适的字段类型 && 长度

同一表中，所有 varchar 字段的长度加起来，不能大于 65535
如果有这样的需求，请使用 TEXT/LONGTEXT 类型

C. 主键设计

主键最好是毫无意义的一串独立不重复的数字
比如 UUID，又或者 Auto_increment 自增的主键，或者是雪花算法生成的主键等

D. 删除方式 && 保留字

优先考虑逻辑删除，而不是物理删除
避免使用 MySQL 保留字，如 select、interval、desc 等

E. 索引设计原则

一般单表索引个数不要超过 5 个
使用 MySQL 的内置函数，会导致索引失效
索引的规则，如覆盖索引，最左匹配原则

F. 第三范式（3NF）（无需严格遵守）

对属性的原子性，不可再分解
对记录的唯一性，即不存在部分依赖
对字段的冗余性，任何字段不能由其他字段派生出来，字段没有冗余，不存在传递依赖

2.3 索引类型详解

2.3.1 索引存储方式

B-树索引

又称为 BTREE 索引，目前大部分的索引都是采用 B-树索引来存储的。

B-树索引是一个典型的数据结构，其包含的组件主要有以下几个：

叶子节点：包含的条目直接指向表里的数据行。叶子节点之间彼此相连，一个叶子节点有一个指向下一个叶子节点的指针。
分支节点：包含的条目指向索引里其他的分支节点或者叶子节点。
根节点：一个 B-树索引只有一个根节点，实际上就是位于树的最顶端的分支节点。

基于这种树形数据结构，表中的每一行都会在索引上有一个对应值。

Hash 索引

哈希（Hash）一般直接音译成"哈希"的，就是把任意长度的输入（又叫作预映射，pre-image）通过散列算法变换成固定长度的输出，该输出就是散列值。

哈希索引也称为散列索引或 HASH 索引
MySQL 目前仅有 MEMORY 存储引擎和 HEAP 存储引擎支持这类索引
MEMORY 存储引擎可以支持 B-树索引和 HASH 索引，且将 HASH 当成默认索引
HASH 索引不是基于树形的数据结构查找数据，而是根据索引列对应的哈希值的方法获取表的记录行
哈希索引的最大特点是访问速度快

2.3.2 索引分类

索引类型	创建方式	说明
普通索引	使用 INDEX 或 KEY 关键字	最基本的索引类型，没有唯一性限制
唯一索引	使用 UNIQUE 关键字	索引列的值必须唯一，但允许有空值
主键索引	使用 PRIMARY KEY 关键字	特殊的唯一索引，不允许有空值。不能使用 CREATE INDEX 创建主键索引
空间索引	使用 SPATIAL 关键字	只能在存储引擎为 MyISAM 的表中创建，必须 NOT NULL
全文索引	使用 FULLTEXT 关键字	只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。仅 MyISAM 存储引擎支持
复合索引	多列组合	组合索引是将原表的多个列共同组成一个索引。多列索引是在表的多个字段上创建一个索引

2.4 SELECT 语句执行顺序

2.4.1 语法顺序

1. SELECT 
2. DISTINCT <select_list>
3. FROM <left_table>
4. <join_type> JOIN <right_table>
5. ON <join_condition>
6. WHERE <where_condition>
7. GROUP BY <group_by_list>
8. HAVING <having_condition>
9. ORDER BY <order_by_condition>
10. LIMIT <limit_number>

2.4.2 实际执行顺序

FROM <表名>                    # 选取表，将多个表数据通过笛卡尔积变成一个表
ON <筛选条件>                  # 对笛卡尔积的虚表进行筛选
JOIN                          # 指定 join，用于添加数据到 on 之后的虚表中，例如 left join 会将左表的剩余数据添加到虚表中
WHERE <where_condition>       # 对上述虚表进行筛选
GROUP BY <分组条件>           # 分组
                              # 用于 having 子句进行判断，在书写上这类聚合函数是写在 having 判断里面的
HAVING <分组筛选>             # 对分组后的结果进行聚合筛选
SELECT <返回数据列表>         # 返回的单列必须在 group by 子句中，聚合函数除外
DISTINCT                      # 数据除重
ORDER BY <排序条件>           # 排序
LIMIT <行数限制>              # 行数限制

2.5 查询执行计划（EXPLAIN）

2.5.1 什么是执行计划

执行计划是指一条 SQL 语句在经过 MySQL 查询优化器的优化后，具体的执行方式。MySQL 为我们提供了 EXPLAIN 语句，来获取执行计划的相关信息。

注意：EXPLAIN 语句并不会真的去执行相关的语句，而是通过查询优化器对语句进行分析，找出最优的查询方案，并显示对应的信息。

执行计划通常用于 SQL 性能分析、优化等场景。通过 explain 的结果，可以了解到：

数据表的查询顺序
数据查询操作的操作类型
哪些索引可以被命中
哪些索引实际会命中
每个数据表有多少行记录被查询等信息

2.5.2 使用示例

EXPLAIN SELECT a.assigned_to AS nickName, b.NAME AS projectName 
FROM dat_project b 
LEFT JOIN dat_workitem a ON a.project_id = b.id

2.5.3 EXPLAIN 结果解读 - query_type（查询类型）

query_type	说明	性能
SIMPLE	简单查询，不包含 UNION 或者子查询	-
PRIMARY	查询中如果包含子查询或其他部分，外层的 SELECT 将被标记为 PRIMARY	-
SUBQUERY	子查询中的第一个 SELECT	-
UNION	在 UNION 语句中，UNION 之后出现的 SELECT	-
DERIVED	在 FROM 中出现的子查询将被标记为 DERIVED	-
UNION RESULT	UNION 查询的结果	-

2.5.4 EXPLAIN 结果解读 - type（访问类型）

查询执行的类型，描述了查询是如何执行的。所有值的顺序从最优到最差排序为：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

访问类型	说明
system	如果表使用的引擎对于表行数统计是精确的 (如：MyISAM)，且表中只有一行记录的情况下，访问方法是 system，是 const 的一种特例
const	表中最多只有一行匹配的记录，一次查询就可以找到，常用于使用主键或唯一索引的所有字段作为查询条件
eq_ref	当连表查询时，前一张表的行在当前这张表中只有一行与之对应。是除了 system 与 const 之外最好的 join 方式，常用于使用主键或唯一索引的所有字段作为连表条件
index_merge	当查询条件使用了多个索引时，表示开启了 Index Merge 优化，此时执行计划中的 key 列列出了使用到的索引
index	查询遍历了整棵索引树，与 ALL 类似，只不过扫描的是索引，而索引一般在内存中，速度更快
ref	使用普通索引作为查询条件，查询结果可能找到多个符合条件的行
range	对索引列进行范围查询，执行计划中的 key 列表示哪个索引被使用了
ALL	全表扫描（最差）

2.5.5 EXPLAIN 结果解读 - Extra（额外信息）

Extra 值	说明	性能影响
Using index condition	表示查询优化器选择使用了索引条件下推这个特性	✅ 优化
Using where	表明查询使用了 WHERE 子句进行条件过滤。一般在没有使用到索引的时候会出现	⚠️ 需注意
Using join buffer	连表查询的方式，表示当被驱动表的没有使用索引的时候，MySQL 会先将驱动表读出来放到 join buffer 中，再遍历被驱动表与驱动表进行查询	⚠️ 需注意
Using filesort	在排序时使用了外部的索引排序，没有用到表内索引进行排序	❌ 需优化
Using temporary	MySQL 需要创建临时表来存储查询的结果，常见于 ORDER BY 和 GROUP BY	❌ 需优化
Using index	表明查询使用了覆盖索引，不用回表，查询效率非常高	✅ 最优

3. 优化建议

3.1 Schema 与数据类型优化

A. 数据类型遵循小而简单的原则

选择尽可能小的、简单的数据类型，可以减少存储空间，提高处理速度。

B. 避免太多的列、关联；避免使用 NULL

表结构设计应避免过多的列
减少不必要的表关联
尽量避免使用 NULL 值，可使用默认值替代

C. 混用范式化和反范式化

根据实际业务场景，合理混用范式化和反范式化设计，在数据一致性和查询性能之间取得平衡。

D. 加快 ALTER TABLE 操作的速度

巧用.frm 文件可以加快 ALTER TABLE 操作的速度，减少大表结构变更的时间。

3.2 索引相关的数据结构和算法

常见的索引数据结构包括：

二叉查找树：基础的树形数据结构
平衡二叉树：自平衡的二叉查找树
B+Tree：MySQL InnoDB 引擎使用的索引结构，适合范围查询

3.3 索引优化目标

效率高：提高查询效率，减少响应时间
减少开销：降低存储和计算开销
覆盖索引：尽可能使用覆盖索引，避免回表查询

3.4 索引失效的常见场景

1. 未遵循最左前缀匹配

使用联合索引时，没有遵循最左前缀匹配原则，导致索引失效。

2. 使用函数、计算、不等于、范围查询等

-- ❌ 索引失效
WHERE YEAR(create_time) = 2024
WHERE amount + 100 > 500
WHERE status != 1
WHERE age BETWEEN 20 AND 30

3. like 模糊匹配以通配符开头

-- ❌ 索引失效
WHERE name LIKE '%张%'

-- ✅ 可以使用索引
WHERE name LIKE '张%'

4. 索引字段使用 is not null

-- ❌ 可能导致索引失效
WHERE column_name IS NOT NULL

5. OR 前后存在非索引的列

-- ❌ 索引失效
WHERE indexed_column = 1 OR non_indexed_column = 2

6. 类型转换

-- ❌ 隐式类型转换导致索引失效
WHERE varchar_column = 123  -- 123 是数字，varchar_column 是字符串

3.5 SQL 优化最佳实践

避免使用 SELECT *

查询 SQL 尽量不要使用 select *，而是 select 具体字段。

使用 LIMIT 1

如果知道查询结果只有一条或者只要最大/最小一条记录，建议用 limit 1。

避免使用 OR 连接条件

应尽量避免在 where 子句中使用 or 来连接条件。

优化 LIMIT 深分页问题

注意优化 limit 深分页问题，可使用延迟关联等方式优化。

使用 WHERE 条件限定数据

使用 where 条件限定要查询的数据，避免返回多余的行。

避免在索引列上使用内置函数

尽量避免在索引列上使用 MySQL 的内置函数。

避免在 WHERE 子句中进行表达式操作

应尽量避免在 where 子句中对字段进行表达式操作。

避免使用!=或<>操作符

应尽量避免在 where 子句中使用!=或<>操作符。

遵循最左匹配原则

使用联合索引时，注意索引列的顺序，一般遵循最左匹配原则。

在查询和排序列上建立索引

对查询进行优化，应考虑在 where 及 order by 涉及的列上建立索引。
批量插入数据

如果插入数据过多，考虑批量插入。
使用覆盖索引

在适当的时候，使用覆盖索引。
使用 EXPLAIN 分析

使用 explain 分析你的 SQL 计划。

4. 实践案例分析

本章节结合实际业务场景，展示 SQL 优化的具体案例和效果对比。

注：原 PPT 中此章节为过渡页，实际案例可参考前述优化建议中的应用。

5. 思考与总结

5.1 数据类型选择建议

NULL 值处理

通常来说把可为 NULL 的列改为 NOT NULL 不会对性能提升有多少帮助，只是如果计划在列上创建索引，就应该将该列设置为 NOT NULL。

整数类型宽度

对整数类型指定宽度，比如 INT(11)，没有任何卵用。INT 使用 32 位（4 个字节）存储空间，那么它的表示范围已经确定，所以 INT(1) 和 INT(20) 对于存储和计算是相同的。

UNSIGNED 类型

UNSIGNED 表示不允许负值，大致可以使正数的上限提高一倍。比如 TINYINT 存储范围是 -128 ~ 127，而 UNSIGNED TINYINT 存储的范围却是 0 - 255。

DECIMAL vs BIGINT

通常来讲，没有太大的必要使用 DECIMAL 数据类型。即使是在需要存储财务数据时，仍然可以使用 BIGINT。比如需要精确到万分之一，那么可以将数据乘以一百万然后使用 BIGINT 存储。这样可以避免浮点数计算不准确和 DECIMAL 精确计算代价高的问题。

TIMESTAMP vs DATETIME
TIMESTAMP 使用 4 个字节存储空间，DATETIME 使用 8 个字节存储空间
TIMESTAMP 只能表示 1970 - 2038 年，比 DATETIME 表示的范围小得多
TIMESTAMP 的值因时区不同而不同
枚举类型

大多数情况下没有使用枚举类型的必要，其中一个缺点是枚举的字符串列表是固定的，添加和删除字符串（枚举选项）必须使用 ALTER TABLE（如果只是在列表末尾追加元素，不需要重建表）。

列数量控制

schema 的列不要太多。原因是存储引擎的 API 工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列，这个转换过程的代价是非常高的。如果列太多而实际使用的列又很少的话，有可能会导致 CPU 占用过高。

大表 ALTER TABLE

大表 ALTER TABLE 非常耗时，MySQL 执行大部分修改表结果操作的方法是用新的结构创建一个张空表，从旧表中查出所有的数据插入新表，然后再删除旧表。尤其当内存不足而表又很大，而且还有很大索引的情况下，耗时更久。当然有一些奇技淫巧可以解决这个问题，有兴趣可自行查阅。

5.2 思考题

问题 1：大表新增字段或索引

问题：线上的一张表如果数据量很大（千万级），如何新增字段或索引？

思考方向： - 使用在线 DDL 工具（如 pt-online-schema-change） - 在业务低峰期执行 - 先创建索引，再添加字段 - 考虑使用无锁变更方案

问题 2：存储过程的使用

问题：存储过程非常不容易维护，也会增加使用成本，应该把业务逻辑放到客户端。既然客户端都能干这些事，那为什么还要存储过程？

思考方向： - 存储过程的优势：减少网络传输、封装复杂逻辑、权限控制 - 存储过程的劣势：调试困难、版本管理复杂、迁移成本高 - 现代架构趋势：业务逻辑上移到应用层

问题 3：视图的必要性

问题：JOIN 本身也挺方便的，直接查询就好了，为什么还需要视图呢？

思考方向： - 视图的优势：简化复杂查询、封装业务逻辑、权限控制、数据抽象 - 视图的使用场景：报表查询、数据脱敏、接口标准化

posted @ 2026-03-27 22:18 JackpotHan 阅读(1) 评论(0) 收藏举报

刷新页面返回顶部

JackpotHan