MySQL索引及慢SQL分析

MaxSun
0 阅读8分钟

MySQL索引及慢SQL

一、索引类型

B+树索引（默认）

B + 树是一种多路平衡查找树

根节点：

存储指向中间节点的指针，数量通常只有一个

中间节点（非叶子节点）：

仅存储索引键+指向叶子节点的指针，不存储实际数据，作用时快速定位叶子节点

叶子节点：

  • 存储完整的索引键+数据（主键索引）或索引键+主键值（二级索引）
  • 所有叶子节点通过双向链表链接（B+树和B树最核心的区别）
  • 叶子节点高度一致（平衡树特性），保证任意查询的IO次数相同

聚簇索引（主键索引）

叶子节点直接存储整行数据，查询主键时可直接返回数据，无需回表

每张InnoDB表有且只有一个聚簇索引

二级索引（辅助索引）

叶子节点存储索引值+主键值，查询二级索引需要先找到主键值，再通过聚簇索引查完整数据（回表）

每张表可以创建多个二级索引

覆盖索引

覆盖索引 = 二级索引包含了查询需要的所有数据 → 跳过回表步骤 当查询的所有字段（SELECT、WHERE、ORDER BY 等）都能从同一个二级索引的 B + 树中直接获取，无需回表查询聚簇索引（主键索引），这个二级索引就被称为 “覆盖索引”

联合索引（复合索引）

最左前缀匹配原则（核心）

联合索引的字段顺序直接影响效率

  • 高频筛选字段放左侧；
  • 等值查询优先放左侧
  • 区分度高的字段放左侧；
  • 范围查询字段放右侧（范围字段后的索引失效）

范围查询截断索引：联合索引中，某个字段做范围查询（>/</BETWEEN）后，右侧字段的索引能力失效

避免冗余联合索引

二、索引创建核心原则

💡

索引的核心价值是加速查询，但会降低写入（增删改）性能，因此创建索引的核心是：在查询效率和写入性能之间找到最优平衡

1. 适合建索引的字段

  • 查询条件高频字段：WHERE、JOIN ON、GROUP BY、ORDER BY 中频繁出现的字段
  • 区分度高的字段：如手机号、身份证号（几乎唯一）
  • 字符串前缀字段：对长字符串（长文本、URL等）可以创建前缀索引，避免索引体积过大
  • 联合索引：多字段联合查询时，优先建联合索引（利用最左前缀匹配）；如果可以包含查询结果字段，避免回表查询

2. 不适合建索引的字段

  • 更新频繁的字段：索引会放大写入开销
  • 区分度极低的字段：如性别、逻辑删除等，索引过滤效果差
  • 小表（<1000 行）：索引维护成本高于全表扫描
  • 查询结果占比高的字段：如WHERE amount > 0 （几乎匹配所有行），索引失效

三、索引使用策略

1. 避免索引失效

  • 索引字段做函数/运算
  • 字符串查询不加引号
  • 联合索引不满足最左前缀法则
  • OR连接非索引字段
  • 使用NOT IN，≠，IS NOT NULL

2.高效使用索引的技巧

  • 优先使用覆盖索引：查询字段全部包含再索引中，避免回表（EXPLAIN中Extra=Using index）；
  • 排序/分组用索引覆盖：GROUP BY/ORDER BY字段包含在索引中，避免文件排序（Extra=Using filesort）；
  • 分页查询优化：用索引字段分页，避免LIMIT 1000000,10 全表扫描

四、索引维护（最佳实践）

  1. **定期分析索引：**用ANALYZE TABLE更新表统计信息，让优化器选择最优索引；

    ANALYZE TABLE `order`;

  2. 查看索引使用情况：通过sys.schema_unused_indexes找到未使用的索引，考虑删除；

    SELECT * FROM sys.schema_unused_indexes WHERE table_schema='your_db';

  3. 避免过度索引：一张表索引数量控制在 5-8 个以内，过多会导致写入慢、优化器选择耗时；

  4. **定期重建碎片索引：**大表索引碎片化严重时，重建索引（MySQL 8 在线执行）

    ALTER TABLE `order` REBUILD INDEX idx_user_id ALGORITHM=INPLACE;

  5. 大表加索引（MySQL 8.0 在线 DDL）：MySQL 8 支持ALTER TABLE ... ADD INDEX在线执行（无锁），但仍需避开业务高峰：

    ALTER TABLE `order` ADD INDEX idx_order_amount(amount) ALGORITHM=INPLACE, LOCK=NONE;

  6. 大表删索引：先隐藏（ALTER INDEX ... INVISIBLE）验证无影响，再删除；

五、EXPLAIN（解释执行计划）

1. 语法格式

-- 基础用法：分析 SELECT 语句
EXPLAIN SELECT * FROM `order` WHERE user_id=123 AND status=1;

-- 扩展用法：分析 DELETE/UPDATE（MySQL 8.0+ 支持）
EXPLAIN UPDATE `order` SET status=2 WHERE id=100;

-- 查看扩展信息（如使用的索引具体字段）
EXPLAIN EXTENDED SELECT * FROM user WHERE phone='13800138000';

2. EXPLAIN输出结果字段概述

字段名核心作用
id查询的执行顺序（数字越大越先执行）
select_type查询类型（简单查询 / 子查询 / 联表等）
table执行的表名
type数据访问方式（最重要字段，判断是否走索引）
possible_keys可能用到的索引（优化器候选）
key实际用到的索引（核心：非 NULL 表示命中）
key_len使用的索引长度（判断联合索引命中的字段数）
ref与索引比较的列 / 常量（如 const 表示等值匹配）
rows预估扫描行数（越小越好）
Extra额外信息（如是否回表、文件排序，关键优化点）

3. 核心字段讲解

  • type（数据访问方式）

    EXPLAIN 中最核心的字段，直接反映查询的效率，目标是至少达到 range 级别，最优是 const/system。

    type 级别含义效率典型场景
    system表只有 1 行数据（系统表）最优SELECT * FROM mysql.user LIMIT 1
    const通过主键 / 唯一索引等值查询，仅 1 行匹配最优SELECT * FROM user WHERE id=1（主键）
    eq_ref联表查询中，主键 / 唯一索引等值匹配优SELECT * FROM order o JOIN user u ON o.user_id=u.id
    ref非唯一索引等值查询，多行匹配良SELECT * FROM user WHERE phone='13800138000'（普通索引）
    range索引范围查询（> / < / BETWEEN / IN）中SELECT * FROM order WHERE create_time BETWEEN '2026-01-01' AND '2026-03-01'
    index扫描整个索引（未命中数据筛选）差SELECT count(*) FROM user（扫描主键索引）
    ALL全表扫描（未走索引）最差SELECT * FROM user WHERE status=1（status 无索引）

  • key（实际使用的索引）

    • NULL：未使用任何索引（全表扫描）
    • 非 NULL：显示实际命中的索引名（如 idx_user_id_status）
    • possible_keys 是候选索引，key 是实际使用的，若 possible_keys 有值但 key 为 NULL，说明索引失效。

  • key_len：使用的索引长度

    • 用于判断联合索引命中的字段数，长度越大，命中的字段越多（单位：字节）。
    💡

    示例：联合索引 idx(user_id, status)

    （user_id 是 INT (4 字节)，status 是 TINYINT (1 字节)）；

    • 若 key_len=4：仅命中 user_id；
    • 若 key_len=5：命中 user_id + status（完整联合索引）。

  • Extra：额外信息（优化关键）

    • 这是定位问题的核心字段，常见值及含义：
    Extra 值含义优化建议
    Using index命中覆盖索引（无需回表）最优状态，无需优化
    Using where索引筛选后，还需过滤数据检查索引是否覆盖筛选条件
    Using filesort需额外文件排序（未用索引排序）优化索引，让排序字段包含在索引中
    Using temporary需创建临时表（如 GROUP BY 无索引）给 GROUP BY 字段建索引
    Using join buffer联表查询未走索引，使用连接缓冲区给联表字段（ON 后的字段）建索引
    Using index condition索引下推（ICP），仅 MySQL 5.6 + 支持正常优化，无需处理
    NULL走索引但需回表改为覆盖索引，避免回表

六、慢SQL

1. 慢SQL日志

  1. 配置慢查询参数

    -- 1. 临时开启（重启失效）
SET GLOBAL slow_query_log = ON;  -- 开启慢查询日志
SET GLOBAL slow_query_log_file = '/var/lib/mysql/slow.log';  -- 日志文件路径
SET GLOBAL long_query_time = 1;  -- 阈值：执行时间>1秒的SQL记录（建议设1秒，线上可设0.5秒）
SET GLOBAL log_queries_not_using_indexes = ON;  -- 记录未使用索引的SQL（即使耗时短）
SET GLOBAL log_slow_admin_statements = ON;  -- 记录ALTER/ANALYZE等管理语句

-- 2. 永久生效（修改my.cnf/my.ini）
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

  2. 分析慢查询日志

    1. mysqldumpslow（MySQL 自带）

      # 查看最慢的10条SQL
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log

# 常用参数说明
# -s：排序方式（t=按时间，l=按锁时间，r=按返回行数）
# -t：返回前N条
# -g：模糊匹配（如只看order表的SQL）
mysqldumpslow -s t -t 10 -g 'order' /var/lib/mysql/slow.lo

    2. pt-query-digest（推荐，需先安装）

      # 安装（CentOS）
yum install percona-toolkit -y

# 分析慢日志，输出详细报告
pt-query-digest /var/lib/mysql/slow.log > slow_report.txt

      报告核心内容：

      • 总执行时间 / 次数：如某 SQL 执行 1000 次，总耗时 500 秒（平均 0.5 秒 / 次）；
      • 锁等待时间：判断是否因锁导致慢；
      • 扫描行数：扫描行数远大于返回行数，说明索引失效。

  3. 实时定位慢 SQL（应急场景）：用 SHOW PROCESSLIST

    -- 查看当前运行的SQL，筛选耗时久的
SHOW FULL PROCESSLIST WHERE Command != 'Sleep' AND Time > 10;
-- Time：已运行秒数，Info：SQL内容

2. 分析慢SQL执行计划

瓶颈类型EXPLAIN 特征典型原因
全表扫描type=ALL、key=NULL未建索引 / 索引失效 / 区分度低
索引失效possible_keys 有值、key=NULL字段运算 / 字符串无引号 / 联合索引不满足最左前缀
回表次数多Extra=NULL（无 Using index）未用覆盖索引，需多次查询聚簇索引
文件排序 / 临时表Extra=Using filesort/Using temporary排序 / 分组字段未在索引中
联表效率低type=ALL（联表表）、Using join buffer联表字段未建索引
avatar
文章
阅读
粉丝