数据库索引优化：这次怎么落地的

作者: 醉月思发布日期: 2021-10-23 分类: 技术实践

这次做数据库索引优化，从简单索引到复合索引，再到自适应索引，。

MySQL InnoDB 默认使用 B+Tree。

索引原理

B-Tree 索引

MySQL InnoDB 默认使用 B+Tree。

graph TB A[根节点] --> B[中间节点1] A --> C[中间节点2] B --> D[叶子节点1] B --> E[叶子节点2] C --> F[叶子节点3] C --> G[叶子节点4] style D fill:#90EE90 style E fill:#90EE90 style F fill:#90EE90 style G fill:#90EE90

特点：

平衡树，所有叶子节点深度相同
数据只存储在叶子节点
适合范围查询

Hash 索引

基于哈希表的索引，只支持等值查询。

特点：

查询速度快 O(1)
不支持范围查询
不支持排序

索引类型

普通索引

-- 创建普通索引
CREATE INDEX idx_users_email ON users(email);

-- 或者
ALTER TABLE users ADD INDEX idx_users_email (email);

-- 查看索引
SHOW INDEX FROM users;

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

唯一索引

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

-- 或者在创建表时指定
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100)
);

复合索引

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

-- 查询使用索引
-- 能使用索引
SELECT * FROM users WHERE name = 'Alice';
SELECT * FROM users WHERE name = 'Alice' AND age = 30;

-- 不能使用索引（不满足最左前缀）
SELECT * FROM users WHERE age = 30;

-- 部分使用索引
SELECT * FROM users WHERE name = 'Alice';

全文索引

-- 创建全文索引
CREATE FULLTEXT INDEX idx_posts_content ON posts(content);

-- 全文搜索
SELECT * FROM posts 
WHERE MATCH(content) AGAINST('search term');

-- 布尔模式
SELECT * FROM posts 
WHERE MATCH(content) AGAINST('+apple -banana' IN BOOLEAN MODE);

索引优化策略

选择性高的字段

-- 计算选择性
SELECT COUNT(DISTINCT email) / COUNT(*) AS selectivity FROM users;

-- 选择性高（接近 1）适合建索引
-- 选择性低（接近 0）不适合建索引

-- 例如：性别字段选择性低，不适合建索引
SELECT COUNT(DISTINCT gender) / COUNT(*) AS selectivity FROM users;
-- 结果可能是 0.0001（只有男女两种）

遵循最左前缀

-- 创建索引
CREATE INDEX idx_users_name_age_gender ON users(name, age, gender);

-- 能使用索引
WHERE name = 'Alice'
WHERE name = 'Alice' AND age = 30
WHERE name = 'Alice' AND age = 30 AND gender = 'F'

-- 能使用部分索引
WHERE name = 'Alice' AND gender = 'F'
WHERE name = 'Alice' AND age > 30

-- 不能使用索引
WHERE age = 30
WHERE gender = 'F'
WHERE age = 30 AND gender = 'F'

避免在索引列上计算

-- 不能使用索引
SELECT * FROM users WHERE YEAR(created_at) = 2023;

-- 能使用索引
SELECT * FROM users WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31';

-- 不能使用索引
SELECT * FROM users WHERE age + 1 = 31;

-- 能使用索引
SELECT * FROM users WHERE age = 30;

使用覆盖索引

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

-- 使用覆盖索引（不需要回表）
SELECT name, age FROM users WHERE name = 'Alice';

-- 不能使用覆盖索引（需要回表）
SELECT * FROM users WHERE name = 'Alice';

索引失效场景

LIKE 查询

-- 索引失效（前导通配符）
SELECT * FROM users WHERE name LIKE '%Alice%';

-- 索引有效
SELECT * FROM users WHERE name LIKE 'Alice%';

OR 条件

-- 索引失效（没有为所有字段建索引）
SELECT * FROM users WHERE name = 'Alice' OR age = 30;

-- 索引有效（两个字段都有索引）
CREATE INDEX idx_users_name ON users(name);
CREATE INDEX idx_users_age ON users(age);
SELECT * FROM users WHERE name = 'Alice' OR age = 30;

类型转换

-- 索引失效（隐式类型转换）
SELECT * FROM users WHERE phone_number = 1234567890;

-- 索引有效
SELECT * FROM users WHERE phone_number = '1234567890';

索引维护

分析表

-- 分析表，更新统计信息
ANALYZE TABLE users;

-- 查看统计信息
SHOW TABLE STATUS LIKE 'users';

重建索引

-- 重建表（包含重建索引）
ALTER TABLE users ENGINE=InnoDB;

-- 优化表
OPTIMIZE TABLE users;

删除未使用的索引

-- 查看未使用的索引（MySQL 5.7+）
SELECT * FROM sys.schema_unused_indexes;

-- 删除未使用的索引
DROP INDEX idx_unused ON users;

踩过的坑

坑一：索引太多

为所有字段都建了索引，导致写操作变慢。

解决：只为必要的字段建索引。

-- 只为高频查询的字段建索引
CREATE INDEX idx_users_email ON users(email);  -- 登录查询
CREATE INDEX idx_users_name ON users(name);    -- 搜索查询

-- 不要这样做
CREATE INDEX idx_users_city ON users(city);
CREATE INDEX idx_users_country ON users(country);
CREATE INDEX idx_users_zip ON users(zip);

坑二：复合索引顺序不对

复合索引顺序不对，导致索引失效。

解决：按照查询频率和选择性排序。

-- 查询 1：WHERE name = ? AND age = ?
-- 查询 2：WHERE name = ?

-- 正确的索引顺序
CREATE INDEX idx_users_name_age ON users(name, age);

-- 错误的索引顺序
CREATE INDEX idx_users_age_name ON users(age, name);

坑三：索引列过多

复合索引列太多，导致索引过大。

解决：控制索引列数，不超过 3 列。

-- 不要这样做
CREATE INDEX idx_users_all ON users(name, age, gender, city, country, phone);

-- 应该这样做
CREATE INDEX idx_users_name_age ON users(name, age);
CREATE INDEX idx_users_city ON users(city);

坑四：索引碎片

长时间运行后，索引产生碎片，影响性能。

解决：定期重建索引。

# 定期重建索引
0 0 1 * * mysql -u root -p -e "OPTIMIZE TABLE users;"

索引检查清单

建索引前

查询频率高吗？
选择性高吗？
写操作频率低吗？
数据量大吗？

建索引后

验证索引被使用
检查查询性能
监控索引大小
定期维护索引

写在最后

索引这东西，不是越多越好，是越合适越好。

解决了：

查询速度
排序速度
连接性能

带来了：

写入性能下降
存储空间占用
维护成本

建索引之前先评估：

查询模式
数据量
写入频率
硬件资源

不是所有查询都需要索引，有时候全表扫描更快。

这次索引优化花了两周，从简单索引到复合索引，再到索引维护。优化完成后，查询性能提升了 10 倍，但写入性能下降了 20%。

版权声明: 本文首发于指尖魔法屋-数据库索引优化：这次怎么落地的（https://blog.thinkmoon.cn/post/75-database-index-optimization-btree-ahash-practice/）转载或引用必须申明原指尖魔法屋来源及源地址！

利用 Cursor ACP 模式搭建飞书助手机器人，无需任何大模型接口

用 lark-cli 收飞书消息，Python 桥接 Cursor ACP（agent acp），项目代码里不集成任何大模型 API；适合已有 Cursor 订阅、想在本机跑 Agent 的场景。

📁 技术实践

📅 2026-07-07

用 GolemBot 把 Codex 接到飞书机器人

这篇文章记录一种少写代码的方案：用 GolemBot 接飞书机器人，用 Codex 做 Agent，再通过飞书 CLI Skills 读取文档、需求清单和 bug 清单。

📁 技术实践

📅 2026-06-09

数据库事务隔离级别：这次怎么落地的

对账脚本和线上下单并发跑，同一笔库存前后两次读出来的数对不上——隔离级别不是背概念，是这种时候才知道默认配置到底够不够用。

📁 技术实践

📅 2021-09-02

数据库索引优化：这次怎么落地的

索引原理

B-Tree 索引

Hash 索引

索引类型

普通索引

唯一索引

复合索引

全文索引

索引优化策略

选择性高的字段

遵循最左前缀

避免在索引列上计算

使用覆盖索引

索引失效场景

LIKE 查询

OR 条件

类型转换

索引维护

分析表

重建索引

删除未使用的索引

踩过的坑

坑一：索引太多

坑二：复合索引顺序不对

坑三：索引列过多

坑四：索引碎片

索引检查清单

建索引前

建索引后

写在最后

评论

相关阅读