MySQL-索引与 SQL 优化

coderCatIce
52 阅读22分钟

MySQL 索引与 SQL 优化

一、索引核心知识

1. 索引本质与作用

  • 本质：是一个有序的数据结构，类似数据库表中数据的 “目录”，基于 B+ 树（主流）、哈希等结构实现，核心目的是避免全表扫描。

  • 优缺点：

    • ✅ 优点：

      1. 加速查询（减少磁盘 IO 次数，直接定位数据位置）；

      2. 辅助排序（利用索引有序性，避免全表排序，降低 CPU 消耗）；

    • ❌ 缺点：

      1. 降低 DML 效率（INSERT/UPDATE/DELETE 时需同步维护索引结构，额外消耗资源）；

      2. 占用额外存储空间（索引文件独立于数据文件，过量索引会浪费磁盘）。

2. 索引分类（按存储结构 / 功能）

（1）按存储结构分：聚簇索引 vs 二级索引（InnoDB核心）
类型特点关联关系
聚簇索引数据与索引共存，叶子节点存储完整数据；一张表仅 1 个聚簇索引1. 主键索引默认是聚簇索引；2. 无主键时，InnoDB 会选第一个唯一非空索引作为聚簇索引；3. 无上述索引时，自动生成隐藏聚簇索引（row_id）
二级索引叶子节点存储聚簇索引键（主键值） ；一张表可多个二级索引查询时需通过二级索引找到主键，再回表查询聚簇索引获取完整数据（回表查询）
（2）按功能分：常用索引类型
索引类型特点适用场景
主键索引非空 + 唯一，默认是聚簇索引（数据与索引共存）主键查询（WHERE id=100）、关联查询（JOIN ON id）
唯一索引字段值唯一（可存 NULL），属于二级索引；加速查询 + 数据去重唯一字段查询（手机号、邮箱，WHERE tel='138xxx'）
普通索引无约束，属于二级索引；仅用于加速查询高频查询字段（姓名、年龄、部门 ID，WHERE dept_id=1）
联合索引多字段组合（如 idx_name_age(name,age)），属于二级索引；遵循 “最左前缀原则”多字段组合查询（WHERE name='张三' AND age=25）、排序（ORDER BY name,age）
全文索引支持长文本关键词匹配（如文章、详情）；MySQL 5.6+ 支持 InnoDB 全文索引模糊搜索（MATCH(content) AGAINST('MySQL 优化')）
（3）单列索引 vs 联合索引（核心对比）
类型特点适用场景
单列索引仅包含单个字段的索引；维护成本低，灵活性高，但多字段查询时可能触发回表 / 索引合并单字段高频查询场景（如仅按 dept_id 查询用户）
联合索引包含多个字段的索引；可利用最左前缀法则，支持多字段查询，减少回表次数多字段组合查询 / 排序场景（如 WHERE name='张三' AND age=25、ORDER BY dept_id, age）
核心对比1. 单列索引多字段查询可能触发 “索引合并”（如 idx_name + idx_age），效率低于联合索引；2. 联合索引可实现覆盖索引，单列索引无法做到；3. 联合索引维护成本高于单列索引优先选联合索引（覆盖高频组合查询），避免创建过多单列索引导致 DML 性能下降
（4）前缀索引（针对长字符串优化）

  • 定义：对字符串字段的前 N 个字符创建索引，而非完整字符串，减少索引存储空间。

  • 适用场景：长字符串字段（如地址、简介），完整索引占用空间大，且前 N 个字符已能区分大部分数据。

  • 核心：计算选择性：

    • 选择性 = 不重复的数量 / 总行数（越接近 1，代表索引项越接近UNIQUE，索引效率越高）。

    • 计算 SQL：

      -- 计算 name 字段不同前缀长度的选择性（目标：找到最小 N 使选择性接近 1）
SELECT 
  COUNT(DISTINCT LEFT(name, 1))/COUNT(*) AS len1,
  COUNT(DISTINCT LEFT(name, 2))/COUNT(*) AS len2,
  COUNT(DISTINCT LEFT(name, 3))/COUNT(*) AS len3
FROM user;

  • 创建语法：

    -- 对 name 字段前 3 个字符创建前缀索引
CREATE INDEX idx_name_prefix ON user(LEFT(name, 3));
-- 等价写法
ALTER TABLE user ADD INDEX idx_name_prefix (name(3));

注意：前缀索引的叶节点中只有前缀和主键，所以就算是仅仅查原文也是要回表查询的，而且回表之后拿到的也只是前缀相同的行，需要再比对完整行

3. 索引创建 / 删除语法

（1）创建索引
-- 1. 创建表时指定索引
CREATE TABLE `user` (
  `id` INT PRIMARY KEY, -- 主键索引（聚簇索引）
  `tel` VARCHAR(11) UNIQUE, -- 唯一索引
  `name` VARCHAR(20),
  `age` TINYINT,
  INDEX `idx_name_age` (`name`, `age`), -- 联合索引
  FULLTEXT INDEX `ft_content` (`content`), -- 全文索引
  INDEX `idx_name_prefix` (`name`(3)) -- 前缀索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. 给已有表添加索引
ALTER TABLE `user` ADD INDEX `idx_dept_id` (`dept_id`); -- 普通索引
ALTER TABLE `user` ADD UNIQUE INDEX `idx_email` (`email`); -- 唯一索引
ALTER TABLE `user` ADD FULLTEXT INDEX `ft_intro` (`intro`); -- 全文索引

-- 3. 直接创建索引（等价于 ALTER TABLE）
CREATE INDEX `idx_gender` ON `user` (`gender`);

-- 查看表中索引
SHOW INDEX FROM 表名;
（2）删除索引
-- 1. ALTER TABLE 删除
ALTER TABLE `user` DROP INDEX `idx_name_age`; -- 普通/唯一/联合/全文/前缀索引
ALTER TABLE `user` DROP PRIMARY KEY; -- 主键索引（需先删除外键约束）

-- 2. DROP INDEX 删除（不能删主键索引）
DROP INDEX `idx_dept_id` ON `user`;

4. 核心索引规则（避坑关键）

（1）最左前缀法则（联合索引核心）

  • 定义：联合索引（如 idx_a_b_c(a,b,c)）仅支持 “从左到右” 的字段组合查询，中间跳过字段则后续索引失效。

  • 注意：与 WHERE 中位置无关，只与是否存在有关。

  • 生效 / 失效示例：

    SQL 条件索引使用情况
    WHERE a=1生效（使用 a 字段索引）
    WHERE a=1 AND b=2生效（使用 a+b 字段索引）
    WHERE a=1 AND b=2 AND c=3生效（使用 a+b+c 字段索引）
    WHERE b=2失效（跳过左前缀 a）
    WHERE a=1 AND c=3仅 a 字段生效，c 字段失效（跳过 b）
（2）覆盖索引与回表查询

  • 回表查询：通过二级索引（如普通索引）查询时，仅能获取主键值，需再次查询聚簇索引才能拿到完整数据（2 次索引查询）。

  • 覆盖索引：查询的字段恰好是索引包含的字段（如联合索引 idx_name_age(name,age)，查询 SELECT name,age FROM user WHERE name='张三'），无需回表，效率极高。

  • 优化建议：高频查询的字段可纳入联合索引，避免回表（如 idx_dept_id_name(dept_id,name) 支持 SELECT name FROM user WHERE dept_id=1 覆盖查询）。

（3）SQL 提示（强制控制索引使用）

  • 作用：手动干预 MySQL 优化器的索引选择逻辑，解决 “优化器选错索引” 问题。

  • 常用提示：

    提示类型语法示例作用
    USE INDEXSELECT * FROM user USE INDEX(idx_name) WHERE name='张三'提示优化器优先使用指定索引（仅建议，优化器可忽略）
    IGNORE INDEXSELECT * FROM user IGNORE INDEX(idx_name) WHERE name='张三'提示优化器忽略指定索引
    FORCE INDEXSELECT * FROM user FORCE INDEX(idx_name) WHERE name='张三'强制优化器使用指定索引（优先级最高）
（4）索引失效场景（高频踩坑）

  1. 对索引字段使用函数 / 表达式（如 WHERE DATE(create_time)='2024-01-01'）；

    • 失效原因：索引存储的是字段原始值，函数 / 表达式会改变字段原始值的有序性，MySQL 无法通过索引直接匹配计算后的值，只能全表扫描后再计算。

  2. 字段类型不匹配，隐式转换时（如 tel 是 VARCHAR，查询 WHERE tel=13800138000 未加引号）；

    • 失效原因：MySQL 会对字段做隐式类型转换（如把 VARCHAR 型的 tel 转为数字），转换后破坏了索引的有序性，导致索引失效，退化为全表扫描。

  3. 模糊查询带 % 时，前缀失效（后缀不失效） ；

    • WHERE name LIKE '%三' 失效
    • WHERE name LIKE '三%' 可用
    • 失效原因：B+ 树索引是按字段前缀有序排列的，前缀 % 会导致无法定位索引起始位置，只能全表匹配；后缀 % 可利用索引的前缀有序性，定位到以 “三” 开头的索引范围，再匹配后续内容。

  4. OR 连接非索引字段时，涉及到的索引均失效；

    • 失效原因：MySQL 优化器无法同时利用 “索引字段” 和 “非索引字段” 的索引，为了统一执行逻辑，会放弃所有索引，直接全表扫描。

  5. 联合索引不满足最左前缀法则时；

    • 失效原因：联合索引的 B+ 树按 “最左字段→次左字段→后续字段” 的顺序有序排列，跳过左前缀字段后，无法利用后续字段的有序性，只能全表扫描。

  6. NOT IN/IS NOT NULL/!= 可能失效，视数据量而定，优先用 IN/IS NULL/BETWEEN；

    • 失效原因：NOT IN/!= 属于 “否定式查询”，MySQL 优化器判断这类查询大概率需要扫描大部分数据，使用索引的收益低于全表扫描，因此放弃索引；IS NOT NULL 同理，索引无法快速定位 “非空” 的全部数据范围。

  7. 联合索引中使用范围查询（>/</>=/<=/BETWEEN），范围查询字段后的索引列失效；

    • 示例（联合索引 idx_name_age_score(name,age,score)）：

      SQL 条件索引使用情况
      WHERE name='张三' AND age>20 AND score=90仅 name+age 生效，score 失效
      WHERE name='张三' AND age=25 AND score>90name+age+score 全生效（等值在前，范围在后）

    • 失效原因：范围查询会获取一个无序的结果集（如 age>20 包含 21、22、23… 等任意值），后续索引列（如 score）的有序性依赖前序字段的等值匹配，无序的范围结果会导致后续索引列无法被利用，索引被截断。

5. B+ 树页分裂与页合并（主键设计的核心依据）

（1）页分裂（Page Split）

  • 背景：InnoDB 中数据和索引以 “页” 为单位存储（默认页大小 16KB），聚簇索引（主键）的叶子节点按主键有序排列，每个页存储一定数量的行数据。

  • 触发条件：插入新数据时，目标页已存满（达到页填充因子，默认 15/16），需将该页拆分为两个新页，重新分配数据。

  • 影响：

    • 额外消耗 IO 和 CPU（拆分页、调整索引指针）；

    • 页分裂后页填充率降低（可能从 90% 降至 50%），浪费存储空间；

    • 频繁页分裂会导致索引碎片增多，查询效率下降。

（2）页合并（Page Merge）

  • 触发条件：删除数据后，相邻两个页的空闲空间总和超过阈值（默认页大小的 50%），InnoDB 会将两个页合并为一个页。

  • 影响：

    • 合并操作消耗资源（调整索引结构）；

    • 频繁删除 + 合并会导致索引结构频繁变动，影响查询稳定性。

（3）主键设计原则（基于页分裂 / 合并的优化）
设计原则核心逻辑反例 / 正例
主键自增（有序）新数据插入到当前页末尾，极少触发页分裂；页填充率高，存储空间利用率优反例：UUID 作为主键（无序，插入随机位置，频繁页分裂）；正例：INT/BIGINT 自增主键（id INT AUTO_INCREMENT）
主键类型最小化减小主键占用空间，提升页存储行数（主键越小，每页存的行越多，IO 次数越少），二级索引叶子节点存储主键值，主键越短，二级索引体积越小、查询 IO 也越少反例：VARCHAR (36) 存 UUID（占 36 字节）；正例：BIGINT（8 字节）/INT（4 字节）
主键唯一且非空聚簇索引要求主键唯一，避免 InnoDB 生成隐藏 row_id（额外开销）反例：无主键 / 主键允许 NULL；正例：id INT PRIMARY KEY NOT NULL AUTO_INCREMENT
避免主键更新主键更新会触发索引重构（删除旧行 + 插入新行，可能引发页分裂）反例：用手机号作为主键（可能修改）；正例：自增 ID 作为主键（永不修改）
慎用复合主键复合主键会增大主键长度，降低页存储效率，且二级索引叶子节点存储完整主键，占用更多空间反例：PRIMARY KEY (dept_id, user_id)；正例：单独自增 ID 作为主键，dept_id 建普通索引

6. 索引设计原则（核心：为谁建索引）

（1）为 “高频查询字段” 建索引
  • 优先给 WHERE 条件、JOIN 关联、ORDER BY/GROUP BY 字段建索引；
  • 低频查询（如每日仅 1 次的统计查询）无需建索引，避免浪费存储空间和 DML 性能。
（2）为 “高选择性字段” 建索引，尽量建立UNIQUE索引
  • 选择性 = 唯一值数量 / 总行数，选择性越高，索引过滤效果越好；
  • 反例：性别字段（仅男 / 女）选择性极低，建索引无意义；
  • 正例：手机号、身份证号字段选择性接近 1，建索引收益高。
（3）为 “小表” 最好不建索引
  • 数据量＜1000 行的小表，全表扫描效率可能高于索引查询（索引 IO 开销＞扫描开销）；
  • 小表优先优化 SQL 逻辑，而非创建索引。
（4）避免 “冗余索引”
  • 冗余索引示例：已建 idx_name_age(name,age)，再建 idx_name(name) 属于冗余；
  • 定期清理冗余索引（通过 SHOW INDEX 或第三方工具如 pt-duplicate-key-checker）。
（5）长字符串优先建 “前缀索引”
  • 如地址、简介等长字符串字段，无需建完整索引，通过计算选择性选择合适的前缀长度（如前 5 个字符）。
（6）优先选联合索引（覆盖高频组合查询）
  • 原因见2.（3）
（7）若索引列不能为NULL，用NOT NULL约束
  • 可以让优化器更有效地选择索引

7. 优化工具（核心补充）

（1）SHOW PROFILE（SQL 执行耗时分析）

  • 作用：精准分析 SQL 执行过程中各阶段的耗时（如 CPU、IO、锁等待等），定位性能瓶颈。

  • 使用步骤：

    1. 开启功能（默认关闭）：

      SET profiling = ON;  -- 会话级开启，仅当前连接有效
SET profiling_history_size = 100;  -- 保留最近100条执行记录

    2. 执行待分析的 SQL：

      SELECT * FROM user WHERE dept_id=1 ORDER BY age DESC LIMIT 10;

    3. 查看执行记录列表：

      SHOW PROFILES;  -- 显示所有SQL的执行ID、耗时、语句

    4. 查看指定 SQL 的详细耗时（替换 query_id 为实际 ID）：

      SHOW PROFILE FOR QUERY query_id;  -- 基础耗时分析
SHOW PROFILE CPU, BLOCK IO FOR QUERY query_id;  -- 含CPU、IO的详细分析

  • 关键指标：

    • Sending data：数据传输耗时（过长可能是结果集过大或索引失效）；
    • Waiting for table lock：表锁等待（MyISAM 常见，需优化锁逻辑）；
    • Creating tmp table：创建临时表（子查询 / 排序 / 分组导致，需优化 SQL）。
（2）SQL 执行频率统计（SHOW STATUS）

  • 作用：统计数据库整体执行频率，定位高频操作（如慢查询、高频更新）。

  • 常用命令：

    -- 查看所有状态变量（按关键字过滤）
SHOW GLOBAL STATUS LIKE 'Com_%';  -- 统计各类SQL执行次数
SHOW GLOBAL STATUS LIKE 'Innodb_%';  -- 统计InnoDB引擎相关指标

  • 核心指标解读：

    指标含义优化方向
    Com_selectSELECT 执行次数过高需优化查询（加索引、精简条件）
    Com_insert/update/delete增删改执行次数过高需优化批量操作，减少 IO
    Innodb_rows_readInnoDB 读取行数远大于 Com_select 说明全表扫描多
    Innodb_rows_updatedInnoDB 更新行数结合业务看是否有无效更新
    Slow_queries慢查询次数非 0 需分析慢查询日志
（3）慢查询日志（定位慢 SQL）

  • 作用：记录执行时间超过阈值的 SQL，是优化慢查询的核心工具。

  • 配置与使用：

    1. 开启慢查询日志（临时生效，重启失效）：

      SET GLOBAL slow_query_log = ON;  -- 开启慢查询日志
SET GLOBAL long_query_time = 1;  -- 阈值：执行时间>1秒记录（默认10秒）
SET GLOBAL log_queries_not_using_indexes = ON;  -- 记录未使用索引的SQL（慎用，易刷屏）

    2. 查看日志路径：

      SHOW VARIABLES LIKE 'slow_query_log_file';  -- 默认为 /var/lib/mysql/localhost-slow.log

    3. 分析慢查询日志（用 mysqldumpslow 工具）：

      # 查看最慢的10条SQL
mysqldumpslow -s t -t 10 /var/lib/mysql/localhost-slow.log
# 查看按执行次数排序的SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/localhost-slow.log

  • 关键参数：

    • slow_query_log：是否开启慢查询日志（生产建议开启）；
    • long_query_time：慢查询阈值（建议设 1-5 秒，按需调整）；
    • log_output：日志输出方式（FILE 或 TABLE，推荐 FILE）。

（4）EXPLAIN（执行计划分析）

  • 作用：分析 SQL 执行计划，查看索引使用、扫描行数、连接方式、查询类型等核心信息，是定位 SQL 性能问题的核心工具。

  • 使用方式：EXPLAIN + 待分析SQL（如 EXPLAIN SELECT * FROM user WHERE dept_id=1;）；进阶用法 EXPLAIN ANALYZE + SQL（MySQL 8.0+ 支持，可输出实际执行耗时）。

  • 核心字段解读：

字段含义优化目标 / 解读要点
id表示查询中执行子查询 / 操作的顺序；1. id 越大，执行优先级越高；2. id 相同，执行顺序从上到下；3. id 为 NULL，代表结果集（如衍生表、临时表）无优化目标，仅用于理解执行顺序
select_type⭐核心：见下单独描述无优化目标，用于区分查询类型，如 DERIVED 可能伴随临时表，需关注性能
table表示当前行执行的操作对应的表名（或衍生表标记如 derived2）确认操作是否命中目标表，排查关联查询的表顺序问题
type⭐核心：见下单独描述至少达到 range，最优 eq_ref/const；杜绝 ALL（全表扫描）和 index（全索引扫描）
possible_keys⭐MySQL 优化器认为可能适用的索引列表（仅为候选，不一定实际使用）若为空，说明无可用索引，需新增索引；若有值但 key 为空，说明优化器选错索引
key ⭐实际执行时使用的索引名称；若为 NULL，说明未使用索引非 NULL 表示使用索引；需确认是否为最优索引（可结合 SQL 提示 调整）
key_len实际使用的索引长度（字节）；联合索引中，可通过该值判断使用了索引的哪些字段（如 idx_name_age 中 key_len=20 表示仅用了 name 字段）长度越短越好（索引利用率高）；联合索引需确认是否用到全部目标字段
ref与索引匹配的列 / 常量；如 const（常量）、user.dept_id（表字段）若为 func，说明使用了函数计算，可能导致索引失效
rowsMySQL 优化器预估的扫描行数（非实际行数）行数越少越好；数值过大需优化索引或 SQL 条件
filtered⭐过滤率（百分比），表示扫描行中满足条件的比例；公式：filtered = 最终结果行数/读取总行数 × 100%过滤率越高越好；过低（如 <10%）说明 WHERE 条件过滤效果差，需优化条件
Extra⭐核心：见下单独描述避免 Using filesort/Using temporary/Using join buffer；优先出现 Using index
补充说明：

  • select_type字段核心值详解：

    • SIMPLE：简单查询（无子查询、无 UNION）；

    • PRIMARY：主查询（外层查询）；

    • SUBQUERY：子查询（内层查询，不依赖外层）；

    • DERIVED：衍生查询（FROM 中的子查询）；

    • UNION：UNION 中第二个及以后的查询；

    • UNION RESULT：UNION 的结果集

  • type 字段核心值详解：

    • ALL：全表扫描，需遍历整张表，性能最差；

    • index：全索引扫描（遍历整个索引树），比 ALL 略优，相当于有索引的全表扫描；

    • range：范围扫描（如 >/</IN/BETWEEN），仅扫描索引范围内的数据；

    • ref：非唯一索引等值匹配（如普通索引 idx_dept_id 匹配 dept_id=1）；

    • eq_ref：唯一索引等值匹配（如主键 / 唯一索引匹配 id=100），返回条数为一行，性能极佳；

    • const/system：常量匹配（如主键匹配 id=1，数据可直接缓存），返回条数为一行，性能最优。

  • Extra 字段核心值详解：

    • 字段优先级：Using index（覆盖索引）> Using where（仅过滤）> Using filesort/Using temporary（需优化）。

    • Using filesort：需额外排序（未用索引排序），需优化；

    • Using temporary：需创建临时表（如 GROUP BY 无索引），需优化；

    • Using index：覆盖索引（无需回表），最优；

    • Using where：使用 WHERE 条件过滤；

    • Using join buffer：连接缓存，关联查询效率低；

    • Impossible WHERE：WHERE 条件恒假（如 1=0）

二、SQL 优化实战

1. 查询语句优化（核心场景）

（1）避免全表扫描

  • 禁用 SELECT *：只查询需要的字段（减少数据传输 + 支持覆盖索引）；

    -- 错误（全字段查询，无法使用覆盖索引）
SELECT * FROM user WHERE dept_id=1;
-- 正确（仅查需要字段，若有 idx_dept_id_name 索引则触发覆盖查询）
SELECT id, name FROM user WHERE dept_id=1;

  • 必加 WHERE 条件：无过滤条件会扫描全表（如统计部门 1 用户数，需加 dept_id=1）；

  • 用 JOIN 代替子查询：子查询易生成临时表，JOIN 效率更高（MySQL 对 JOIN 优化更成熟）；

    -- 错误（子查询生成临时表）
SELECT * FROM user WHERE dept_id IN (SELECT id FROM dept WHERE name='技术部');
-- 正确（JOIN 效率更高）
SELECT u.* FROM user u JOIN dept d ON u.dept_id=d.id WHERE d.name='技术部';
（2）条件优化（避免索引失效）

  • 不用函数操作索引字段；

  • 字段类型严格匹配；

  • 模糊查询少用前缀 %；

  • 强制使用指定索引（SQL 提示）；

（3）分页优化（大偏移量问题）

  • 大偏移量 LIMIT 效率低（如 LIMIT 100000, 10 需扫描前 100010 条数据），优化方案：

    -- 方案1：主键过滤（适用于主键自增）
SELECT * FROM user WHERE id > 100000 ORDER BY id LIMIT 10;

-- 方案2：联合索引覆盖查询（避免回表）
SELECT id, name FROM user WHERE dept_id=1 ORDER BY id LIMIT 100000, 10;

-- 方案3：延迟关联（先查主键，再关联查完整数据）
SELECT u.* FROM user u JOIN (
  SELECT id FROM user WHERE dept_id=1 ORDER BY id LIMIT 100000, 10
) t ON u.id=t.id;
（4）排序 / 分组优化

  • 排序字段加索引：ORDER BY 字段若有索引，可避免全表排序（Using filesort）；

  • 若不可避免Using filesort，可以适当增大sort_buffer_size；

  • 联合索引包含排序字段：如 idx_dept_id_age(dept_id,age)，支持 WHERE dept_id=1 ORDER BY age（利用索引有序性）；

  • 避免 GROUP BY 无索引：GROUP BY 字段加索引，减少临时表生成（Using temporary）；

    -- 优化前（无索引，生成临时表+文件排序）
SELECT dept_id, COUNT(*) FROM user GROUP BY dept_id ORDER BY COUNT(*);
-- 优化后（idx_dept_id 索引，避免临时表）
SELECT dept_id, COUNT(*) FROM user GROUP BY dept_id ORDER BY COUNT(*);
（6）count优化

  • 可自己额外引入一个变量，自己记录表的总数目；

  • 效率：count(字段) < count(id) < count(1) ≈ count(*)

    • 原因是因为要取值
（7）insert优化

  • 批量插入可减少连接次数

  • 手动事务提交，将多条语句用一次事务提交，减少提交次数

  • 主键顺序插入

  • 大批量插入时使用load（离线导入的一种方式，非重点）

2. 表结构优化

  • 字段类型最小化：按实际需求选择最小类型（如年龄用 TINYINT（0-255）代替 INT，手机号用 VARCHAR(11) 代替 VARCHAR(20)）；

  • 避免 NULL 值：给字段设默认值（如 gender DEFAULT '未知'，avatar DEFAULT 'default.png'），NULL 会影响索引效率和查询判断；

  • 拆分大表（垂直拆分）：将字段多的表拆分为 “高频访问表” 和 “低频访问表”（如 user 拆分为 user_base（id、name、tel）和 user_extend（intro、address、create_time））；

  • 拆分大表（水平拆分）：按时间 / 地域 / 用户 ID 拆分（如订单表 order 拆分为 order_202401、order_202402，用户表按 id%10 拆分为 10 个分表）；

  • 合理分区：时间序列数据（订单表、日志表）按时间分区，查询仅扫描指定分区（如 ORDER BY create_time BETWEEN '2024-01-01' AND '2024-01-31' 仅扫描 202401 分区）；

  • 用 INT 代替 VARCHAR 存枚举值：如性别用 TINYINT(1)（0 = 女，1 = 男）代替 VARCHAR(2)，查询 / 排序效率更高。

3. 批量操作优化

  • 批量插入代替单条插入：减少网络 IO 和事务提交次数（推荐一次插入 1000-5000 条）；

    -- 错误（单条插入，100次 IO）
INSERT INTO user(name, tel) VALUES ('张三','138xxx');
INSERT INTO user(name, tel) VALUES ('李四','139xxx');
-- 正确（批量插入，1次 IO）
INSERT INTO user(name, tel) VALUES ('张三','138xxx'), ('李四','139xxx'), ('王五','137xxx');

  • 批量更新代替循环单条更新：用 CASE WHEN 或 JOIN 实现，减少事务开销；

    -- 批量更新多个用户年龄
UPDATE user 
SET age = CASE id 
  WHEN 1 THEN 25 
  WHEN 2 THEN 30 
  WHEN 3 THEN 28 
END 
WHERE id IN (1,2,3);

  • 批量删除优化：避免 DELETE FROM user（全表删除，锁表时间长），用 LIMIT 分批删除；

    -- 错误（全表删除，锁表）
DELETE FROM user WHERE create_time < '2023-01-01';
-- 正确（分批删除，每次删1000条）
WHILE EXISTS (SELECT 1 FROM user WHERE create_time < '2023-01-01') DO
  DELETE FROM user WHERE create_time < '2023-01-01' LIMIT 1000;
END WHILE;

4. 事务与锁优化

  • 事务尽量短小：长事务会占用锁资源，导致并发下降，甚至死锁（如避免在事务中执行非数据库操作，如调用第三方接口）；

  • 统一锁顺序：多表操作时，按固定顺序加锁（如先锁 user 表，再锁 order 表），避免死锁；

  • 用行锁代替表锁：InnoDB 行锁基于索引，查询需命中索引（否则退化为表锁）；

    -- 错误（未命中索引，退化为表锁）
UPDATE user SET age=25 WHERE name='张三'; -- name 无索引
-- 正确（命中索引，行锁）
UPDATE user SET age=25 WHERE id=100; -- id 是主键索引

  • 避免幻读：用 SELECT ... FOR UPDATE 加行锁（InnoDB Repeatable Read 隔离级别下），或提升隔离级别到 SERIALIZABLE（不推荐，影响并发）；

  • 禁用 FOR UPDATE 无索引查询：会导致表锁，如 SELECT * FROM user FOR UPDATE（无 WHERE 条件）。

avatar
文章
阅读
粉丝