印刻数据库

外观

MySQL 索引设计与优化 ​

索引是提高 MySQL 查询性能的关键组件，合理的索引设计可以显著提升查询速度，而不当的索引设计则可能导致性能下降。了解索引设计原则和优化方法对于构建高性能 MySQL 应用至关重要。

索引基本概念 ​

索引的作用 ​

  • 加速查询：通过索引快速定位数据，减少磁盘 I/O
  • 确保数据唯一性：唯一索引可以防止重复数据
  • 优化排序和分组：索引可以避免临时表的创建
  • 加速连接操作：索引可以加速表之间的连接

索引数据结构 ​

MySQL 主要使用两种索引数据结构：

  • B-Tree 索引：

    • 最常用的索引类型
    • 适用于全值匹配、范围查询和排序
    • 支持前缀索引
    • InnoDB 和 MyISAM 存储引擎都支持

  • 哈希索引：

    • 只支持精确匹配
    • 不支持范围查询和排序
    • 适合键值对查询
    • Memory 存储引擎默认使用

索引设计原则 ​

选择合适的列作为索引 ​

  • 高选择性列：选择唯一值比例高的列作为索引
  • 频繁查询的列：选择经常出现在 WHERE 子句中的列
  • 连接条件列：选择用于表连接的列
  • 排序和分组列：选择用于 ORDER BY 和 GROUP BY 的列

索引列顺序 ​

  • 将选择性最高的列放在索引的最前面
  • 考虑查询的过滤条件顺序
  • 避免冗余索引

避免过度索引 ​

  • 每个索引都会占用存储空间
  • 索引会增加写操作的开销
  • 索引过多会导致优化器选择错误的索引

考虑数据分布 ​

  • 了解数据的分布情况
  • 对于倾斜数据，考虑使用直方图
  • 对于频繁更新的列，谨慎创建索引

不同类型索引的设计与优化 ​

B-Tree 索引 ​

设计要点 ​

  • 合适的索引长度：使用前缀索引减少索引大小
  • 复合索引顺序：选择性高的列在前
  • 避免函数操作：函数会使索引失效
  • 避免隐式类型转换：类型转换会使索引失效

优化示例 ​

sql
-- 创建前缀索引
CREATE INDEX idx_email ON users(email(20));

-- 创建复合索引
CREATE INDEX idx_name_age ON users(name, age);

-- 避免在索引列上使用函数
-- 不好的查询
SELECT * FROM users WHERE YEAR(created_at) = 2023;
-- 好的查询
SELECT * FROM users WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31';

唯一索引 ​

设计要点 ​

  • 用于确保数据唯一性
  • 可以加速查询
  • 主键索引是特殊的唯一索引

优化示例 ​

sql
-- 创建唯一索引
CREATE UNIQUE INDEX idx_username ON users(username);

-- 主键索引（自动创建）
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL
);

全文索引 ​

设计要点 ​

  • 用于全文搜索
  • 适用于大文本字段
  • InnoDB 从 MySQL 5.6 开始支持

优化示例 ​

sql
-- 创建全文索引
CREATE FULLTEXT INDEX idx_content ON articles(content);

-- 使用全文索引查询
SELECT * FROM articles WHERE MATCH(content) AGAINST('MySQL 索引优化');

空间索引 ​

设计要点 ​

  • 用于地理空间数据
  • 支持点、线、面等几何类型
  • InnoDB 和 MyISAM 支持

优化示例 ​

sql
-- 创建空间索引
CREATE SPATIAL INDEX idx_location ON locations(point);

-- 使用空间索引查询
SELECT * FROM locations WHERE MBRContains(GeomFromText('Polygon((...))'), point);

索引创建和维护 ​

索引创建策略 ​

  • 批量创建：避免在生产高峰创建索引
  • 在线创建：使用 ALGORITHM=INPLACE 减少锁表时间
  • 测试索引：在测试环境验证索引效果

索引维护 ​

  • 定期分析表：更新统计信息
  • 重建索引：修复碎片
  • 删除冗余索引：减少索引维护开销

示例命令 ​

sql
-- 在线创建索引
ALTER TABLE users ADD INDEX idx_email(email) ALGORITHM=INPLACE LOCK=NONE;

-- 分析表
ANALYZE TABLE users;

-- 重建索引
OPTIMIZE TABLE users;

-- 删除索引
DROP INDEX idx_email ON users;

索引使用和优化 ​

查看索引使用情况 ​

sql
-- 查看索引使用统计
SHOW INDEX FROM users;

-- 查看索引使用情况
SHOW STATUS LIKE 'Handler_read%';

-- 查看慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

使用 EXPLAIN 分析索引 ​

sql
-- 分析查询计划
EXPLAIN SELECT * FROM users WHERE name = 'John';

-- 分析扩展信息
EXPLAIN EXTENDED SELECT * FROM users WHERE name = 'John';

-- 分析格式化输出
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE name = 'John';

优化索引使用 ​

  • 覆盖索引：查询只使用索引列，避免回表
  • 索引合并：使用多个索引并合并结果
  • 索引下推：在存储引擎层使用索引过滤数据

常见索引问题和解决方案 ​

问题：索引失效 ​

解决方案：

  • 检查查询条件是否使用了函数
  • 检查是否存在隐式类型转换
  • 检查索引列顺序是否与查询条件匹配
  • 检查是否使用了 NOT IN、!= 等操作符

问题：索引碎片 ​

解决方案：

  • 定期重建索引
  • 使用 OPTIMIZE TABLE 命令
  • 考虑使用分区表

问题：索引选择错误 ​

解决方案：

  • 更新统计信息
  • 使用 FORCE INDEX 提示
  • 优化查询语句
  • 调整索引设计

版本差异 ​

MySQL 5.7 及之前版本 ​

  • 全文索引支持有限
  • 不支持降序索引
  • 统计信息更新机制相对简单
  • 索引优化器功能有限

MySQL 8.0 ​

  • 支持降序索引
  • 增强了全文索引功能
  • 改进了统计信息收集
  • 优化器功能增强
  • 支持隐藏索引
  • 支持直方图

主要版本差异示例 ​

特性MySQL 5.7MySQL 8.0
降序索引不支持支持
隐藏索引不支持支持
直方图不支持支持
全文索引基本支持增强支持
统计信息简单增强

索引设计最佳实践 ​

设计阶段 ​

  • 理解业务需求：根据查询模式设计索引
  • 分析查询日志：找出频繁执行的查询
  • 考虑数据增长：设计可扩展的索引
  • 与开发团队协作：了解应用的查询模式

实施阶段 ​

  • 从小规模开始：先创建必要的索引，然后根据需要添加
  • 测试性能：在测试环境验证索引效果
  • 监控索引使用：定期检查索引使用情况
  • 避免过早优化：先确保查询逻辑正确，再进行索引优化

维护阶段 ​

  • 定期审查：每季度或半年审查一次索引
  • 更新统计信息：定期更新表统计信息
  • 删除无用索引：移除未使用的索引
  • 文档化：记录索引的用途和设计理由

常见问题（FAQ） ​

Q1: 如何确定哪些列需要创建索引？ ​

A1: 可以通过以下方法确定：

  • 分析慢查询日志，找出频繁执行的查询
  • 使用 EXPLAIN 分析查询计划
  • 查看表的查询模式，找出经常出现在 WHERE 子句中的列
  • 考虑连接条件和排序分组列

Q2: 复合索引和多个单列索引有什么区别？ ​

A2: 主要区别：

  • 复合索引可以覆盖多个列，减少索引数量
  • 复合索引遵循最左前缀原则
  • 多个单列索引在某些情况下会被索引合并使用
  • 复合索引更适合多列查询

Q3: 什么时候应该使用前缀索引？ ​

A3: 前缀索引适用于：

  • 长字符串列，如 VARCHAR(255) 或 TEXT
  • 前几个字符具有较高选择性的列
  • 需要减少索引存储空间的情况

Q4: 如何处理索引碎片？ ​

A4: 可以通过以下方法处理：

  • 使用 OPTIMIZE TABLE 命令重建表
  • 使用 ALTER TABLE ... FORCE 重建索引
  • 对于 InnoDB 表，可以使用 ALTER TABLE ... ALGORITHM=INPLACE
  • 定期重建索引

Q5: MySQL 8.0 中的降序索引有什么优势？ ​

A5: 降序索引的优势：

  • 可以优化 ORDER BY ... DESC 查询
  • 避免了排序操作
  • 提高了查询性能
  • 减少了临时表的创建

Q6: 如何监控索引使用情况？ ​

A6: 可以通过以下方法监控：

  • 使用 Performance Schema 监控索引访问
  • 查看 SHOW STATUS LIKE 'Handler_read%' 输出
  • 使用 pt-index-usage 工具分析慢查询日志
  • 定期检查 INFORMATION_SCHEMA.STATISTICS

索引优化案例 ​

案例 1：复合索引优化 ​

问题：查询 SELECT * FROM orders WHERE customer_id = 123 AND status = 'active' 执行缓慢

分析：

  • 表中有 customer_id 和 status 两个单列索引
  • 优化器只能使用其中一个索引

解决方案：

  • 创建复合索引 CREATE INDEX idx_customer_status ON orders(customer_id, status);
  • 优化后，查询可以使用复合索引直接定位数据

案例 2：覆盖索引优化 ​

问题：查询 SELECT id, name, email FROM users WHERE status = 'active' 执行缓慢

分析：

  • 表中有 status 索引，但查询需要返回 id, name, email 列
  • 优化器需要先通过索引找到 id，然后回表查询 name 和 email

解决方案：

  • 创建覆盖索引 CREATE INDEX idx_status_id_name_email ON users(status, id, name, email);
  • 优化后，查询可以直接从索引中获取所有需要的列，避免回表

案例 3：前缀索引优化 ​

问题：表中有一个 VARCHAR(255) 的 email 列，创建索引会占用大量空间

分析：

  • 完整 email 列的索引会占用大量存储空间
  • 但 email 列的前 20 个字符已经具有较高的选择性

解决方案：

  • 创建前缀索引 CREATE INDEX idx_email ON users(email(20));
  • 优化后，索引大小显著减小，查询性能影响不大