MySQL 8.4 LTS 性能调优:索引优化策略

作为一名在深夜被长事务告警惊醒无数次的DBA，笔者深知一条没有索引的SELECT语句能让生产环境CPU飙升到100%，还可能让SQL从几秒变成几小时跑不完。MySQL的索引策略早已从简单的单列索引进化为多维度的性能工程。今天咱们就聊聊如何用索引让查询速度提升百倍。

01. 索引基础与最左前缀原则

索引是MySQL查询优化的核心武器，但设计不当的复合索引可能比没有索引更慢。MySQL 中的最左前缀原则的案例很多。

场景：查询涉及album_id、privacy、rating等多个过滤条件，还有GROUP BY和ORDER BY操作。

-- 表结构
CREATE TABLE photos (
    id INT NOT NULL AUTO_INCREMENT,
    album_id INT DEFAULT NULL,
    privacy CHAR(1) NOT NULL DEFAULT 'A',
    rating INT DEFAULT 0,
    title VARCHAR(255) DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE photo_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    album_id INT NOT NULL,
    owner_id CHAR(1) NOT NULL,
    hidden CHAR(1) NOT NULL DEFAULT '0',
    valid_upload TINYINT(1) NOT NULL DEFAULT 0, -- 1 or 0
    url TEXT, 
    INDEX idx_lookup_filter (owner_id, hidden, valid_upload, album_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

那么针对下面这条查询语句如何优化。

SELECT title, created_at, updated_at
FROM photos ph
INNER JOIN photo_data pd ON pd.album_id = ph.album_id
WHERE ph.rating IN (12)
  AND pd.owner_id IN('S')
  AND ph.privacy = 'S'
  AND pd.hidden ='0'
  AND pd.valid_upload = 1
  AND pd.url is not NULL
GROUP BY ph.id
HAVING count(ph.id)>2
ORDER BY ph.id DESC
LIMIT 12;

依据最左前缀原则：复合索引(album_id, privacy, rating)只有在查询条件中包含最左侧列album_id时才能生效。如果WHERE子句只包含privacy和rating，该索引将部分失效或完全失效。

需要创建索引：

ALTER TABLE photos ADD INDEX IX_album_privacy_rating 
  (album_id, privacy, rating);

此外，MySQL 8.0开始，索引可以包含函数表达式（Functional Indexes），这在处理JSON字段或函数过滤时特别有用。

02. 索引覆盖与避免回表

索引覆盖（Covering Index）是性能优化的黄金法则。当查询的所有列都在索引中时，MySQL无需回表查询数据行。

场景：覆盖索引设计

-- 表结构
CREATE TABLE users (
  user_id int(11) NOT NULL AUTO_INCREMENT,
  loc_id int(11) DEFAULT NULL,
  user_name varchar(50) NOT NULL,
  user_static int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY(user_id)
) ENGINE=InnoDB;

CREATE TABLE locations (
  loc_id int(11) NOT NULL AUTO_INCREMENT,
  site_id int(11) NOT NULL,
  loc_name varchar(50) NOT NULL,
  loc_mapping char(36) NOT NULL,
  PRIMARY KEY(loc_id)
) ENGINE=MEMORY;

-- 查询
SELECT loc.site_id, loc.loc_name, usr.user_name
FROM users usr
INNER JOIN locations loc ON usr.loc_id = loc.loc_id
WHERE loc.loc_mapping = 'daa9a225';

如果查询非常频繁，可以考虑将这些列加入索引形成覆盖索引，彻底消除回表操作，达到“优”级性能。

CREATE INDEX IX_loc_mapping ON locations(loc_mapping) USING HASH;
CREATE INDEX IX_loc_id ON users(loc_id);  -- 覆盖索引选项

覆盖索引 vs 非覆盖索引对比：

类型	查询列位置	I/O操作	性能等级
覆盖索引 (Covering Index)	全部在索引内	仅索引扫描 (Index Only Scan)	优 (Fastest)
普通索引 (Secondary Index)	部分在索引内	索引 + 回表 (Index Lookups + RID)	良 (Good)
全表扫描 (Full Table Scan)	无索引可用	扫描全表 (Sequential Scan)	差 (Slow)

03. 索引失效场景与执行计划验证

了解什么情况下索引会失效比知道如何使用索引更重要。

场景：索引特性与限制

-- 场景1：二级索引访问
-- 二级索引访问并不总是比表扫描快，当需要回表的数据比例过高时，优化器会选择全表扫描

-- 场景2：索引列上的函数操作（索引失效）
-- 错误用法
EXPLAIN SELECT * FROM users WHERE YEAR(birth_date) = 2000;
-- type: ALL（全表扫描，因为使用了函数）

-- 正确用法
EXPLAIN SELECT * FROM users 
WHERE birth_date BETWEEN '2000-01-01' AND '2000-12-31';
-- type: range（使用索引范围扫描）

场景：索引验证

-- 准备测试表
CREATE TABLE appointments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    attendant_id INT NOT NULL,
    payment DECIMAL(10, 2) NOT NULL,
    credit DECIMAL(10, 2) NOT NULL,
    attendant_session_id INT NOT NULL,
    created_by VARCHAR(50) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_covering_search (attendant_session_id, created_by, attendant_id, payment, credit)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO appointments (attendant_id, payment, credit, attendant_session_id, created_by) VALUES
(101, 5000.00, 100.00, 510, 'jsmith'),
(102, 3500.00, 0.00, 510, 'jsmith'),
(103, 12000.00, 500.00, 510, 'jsmith'),
(104, 4000.00, 50.00, 511, 'jsmith'),
(105, 6000.00, 200.00, 510, 'ajohnson'),
(106, 2500.00, 0.00, 999, 'guest');

-- 验证索引是否被使用
EXPLAIN ANALYZE
SELECT attendant_id, payment, credit
FROM appointments
WHERE attendant_session_id = 510
  AND created_by = 'jsmith'\G

-- 期望输出
-- type: ref
-- key: IX_session_creator
-- key_len: 计算值（varchar(20) + int，考虑字符集）
--

预期输出，使用索引，估算行数 rows 显著小于全表行数。

mysql> EXPLAIN
    -> SELECT attendant_id, payment, credit
    -> FROM appointments
    -> WHERE attendant_session_id = 510
    ->   AND created_by = 'jsmith'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: appointments
   partitions: NULL
         type: ref
possible_keys: idx_covering_search
          key: idx_covering_search
      key_len: 206
          ref: const,const
         rows: 3
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

隐式类型转换陷阱：

-- 索引失效：字符串列与数字比较
CREATE INDEX idx_name ON employees(name);
EXPLAIN SELECT * FROM employees WHERE name = 123;
-- type: ALL（索引失效，因为发生了隐式类型转换）

-- 正确使用
EXPLAIN SELECT * FROM employees WHERE name = '123';
-- type: ref（索引生效）

04. 索引提示与优化器干预

当优化器选择了错误的执行计划时，可以使用索引 USE INDEX 提示强制指定索引。

-- 表结构：photos表有多个索引
-- PRIMARY (id), ix_o (owner_id), ix_r (rating), ix_p (privacy), ix_l (location)

-- 原始查询（优化器错误地选择了PRIMARY索引，导致Using filesort）
EXPLAIN SELECT photos.* FROM photos
WHERE owner_id = 44185
  AND privacy = '0'
  AND location like '%home%'
  AND rating IN (1)
ORDER BY id DESC
LIMIT 0, 10;
-- 输出显示：key=PRIMARY, Extra: Using where; Using filesort

SELECT photos.* 
-- 使用USE INDEX强制指定ix_o
FROM photos USE INDEX(ix_o)
WHERE owner_id = 44185
  AND privacy = '0'
  AND location like '%home%'
  AND rating IN (1)
ORDER BY id DESC
LIMIT 0, 10;

索引提示类型对比：

提示类型	语法	作用	适用场景
USE INDEX	USE INDEX(idx)	建议优化器使用，但允许忽略	数据分布变化大的表
FORCE INDEX	FORCE INDEX(idx)	强制使用指定索引（除非没法用）	明确知道索引更优时
IGNORE INDEX	IGNORE INDEX(idx)	排除特定索引	避免使用低效索引

05. 函数索引与JSON索引

MySQL 8.4 中可以使用函数索引。

-- 为大写查询创建函数索引（避免全表扫描）
CREATE INDEX idx_name_upper ON employees((UPPER(name)));

-- 查询时自动使用
SELECT * FROM employees WHERE UPPER(name) = 'SMITH';

JSON字段索引（MySQL 8.0+）：

-- 虚拟列+索引
CREATE TABLE fshop (
  id INT PRIMARY KEY,
  product JSON,
  name VARCHAR(20) AS (product->>'$.name') VIRTUAL,
  INDEX idx_name (name)
);

-- 查询JSON字段使用索引
SELECT * FROM fshop WHERE name = 'apple';

MySQL 9.0 引入了 JSON Duality Views 新特性，也可以使用虚拟列索引，这个我们后面再做介绍。

06. 索引维护与性能监控

索引不是创建后就一劳永逸，需要定期维护和监控。

索引统计信息更新：

-- 更新表统计信息（优化器依赖）
ANALYZE TABLE photos;

-- 查看索引使用情况
SELECT 
  TABLE_NAME,
  INDEX_NAME,
  CARDINALITY,
  SEQ_IN_INDEX
FROM information_schema.STATISTICS
WHERE TABLE_NAME = 'photos';

索引碎片整理：

-- 查看索引大小和碎片率（大表维护需要关注）
SELECT 
  table_name,
  index_name,
  ROUND(SUM(data_length)/1024/1024, 2) AS 'index_size_MB'
FROM information_schema.STATISTICS
WHERE table_schema = 'mydb'
GROUP BY table_name, index_name;

好了，本期内容就先到这里。

---

Have a nice day ~ ☕

🌻 近期内容 ▼

• Oracle Skills开源：AI工程正在进入"技能时代"
• 苦等三年！Oracle AI Database 26ai本地服务器版终于来了
• MySQL 8.0结束生命周期，8.4.9 LTS、9.7.0发版上线：一个时代的交接与新生

👉 这里有得聊

如果对国产基础软件（操作系统、数据库、中间件）、AI、Vibe Coding、OpenClaw 、Hermes Agent 等感兴趣，可以加群一起聊聊。关注微信公众号：(少安事务所)，后台回复[群]，即可看到入口。如果这篇文章为你带来了灵感或启发，请帮忙『点赞、推荐、转发』吧，感谢！ღ( ´･ᴗ･` )~