1. 当数据库变慢时我们在想什么

某天下午，运维同事突然在群里@你："用户中心接口响应慢了3倍！"此时的你就像接到火警的消防员，打开慢查询日志看到满屏的"Using filesort"和"Using temporary"。这时候才发现，精心设计的索引就像拼错的乐高积木，看似完整实则存在结构隐患。

2. 三大经典索引选择误区

2.1 误区一：索引数量论英雄

新人小王为users表创建了这样的索引：

-- 用户表结构（MySQL 8.0）
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    phone CHAR(11),
    created_at DATETIME,
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_phone (phone),
    INDEX idx_created_at (created_at)
);

当执行组合查询时：

SELECT * FROM users 
WHERE username = '张三' 
AND created_at > '2023-01-01'
ORDER BY email LIMIT 10;

这个查询会随机选择username或created_at索引，然后在内存中做结果合并。就像同时打开三个导航软件，系统反而不知道该走哪条路线。

问题本质：离散的单列索引无法有效支持组合查询，导致索引合并（index_merge）效率低下

2.2 误区二：WHERE条件就是全部

开发老张为订单表设计的索引：

-- 订单表结构（MySQL 8.0）
CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    user_id INT,
    status TINYINT,
    amount DECIMAL(10,2),
    INDEX idx_user_status (user_id, status)
);

-- 高频查询语句
SELECT * FROM orders 
WHERE user_id = 1001 
ORDER BY amount DESC 
LIMIT 20;

虽然WHERE条件用到了索引，但排序操作需要额外的文件排序。就像用姓名查到了图书馆书籍位置，却要现场按出版日期重新整理书架。

核心矛盾：索引仅覆盖查询条件，未包含排序字段，导致额外排序开销

2.3 误区三：联合索引顺序不重要

在商品搜索场景中：

-- 商品表结构（MySQL 8.0）
CREATE TABLE products (
    id INT PRIMARY KEY,
    category_id INT,
    price DECIMAL(10,2),
    sales_count INT,
    INDEX idx_category_price (category_id, price)
);

-- 常见查询
SELECT * FROM products 
WHERE category_id = 5 
ORDER BY sales_count DESC 
LIMIT 50;

这个索引就像把快递按省份和街道分类，但用户要的是按签收时间排序。索引字段顺序与查询模式不匹配，导致无法有效利用索引排序。

排序困境：联合索引字段顺序未遵循"等值查询在前，范围排序在后"原则

3. 破解困局的实战解决方案

3.1 组合拳索引设计

针对2.1中小王的案例，重构索引：

-- 删除冗余单列索引
DROP INDEX idx_username ON users;
DROP INDEX idx_created_at ON users;

-- 创建覆盖索引
ALTER TABLE users ADD INDEX idx_username_created_email 
(username, created_at, email);

优化后的查询可以直接走索引完成条件过滤和排序，执行时间从120ms降至15ms。就像给快递车规划了直达路线，中途无需换车转运。

3.2 索引排序字段内嵌

解决2.2中老张的问题：

-- 调整索引包含排序字段
ALTER TABLE orders DROP INDEX idx_user_status;
ALTER TABLE orders ADD INDEX idx_user_status_amount 
(user_id, status, amount DESC);

现在排序操作可以直接利用索引的有序性，文件排序消失。就像图书馆在书籍入库时就按出版日期排列，读者可以直接按架取书。

3.3 联合索引黄金公式

重构商品表索引：

ALTER TABLE products DROP INDEX idx_category_price;
ALTER TABLE products ADD INDEX idx_category_sales 
(category_id, sales_count DESC);

调整后的索引完全匹配查询模式，性能提升80%。这相当于在快递分拣时直接按"省份+签收时间"分类，与派送需求完美契合。

4. 技术方案选型指南

4.1 适用场景分析

  • 组合索引：适合固定维度的多条件查询（如电商平台的筛选+排序）
  • 覆盖索引：适合频繁访问特定字段集的查询（如用户信息展示）
  • 排序索引：适合需要分页排序的大结果集（如排行榜数据）

4.2 性能平衡艺术

优势维度：

  • 查询速度提升3-10倍
  • 减少80%以上的临时表使用
  • 内存消耗降低约40%

成本考量：

  • 索引维护会降低10%-15%的写入速度
  • 每个额外索引增加约5%的存储空间
  • 索引重建期间会有短暂性能波动

4.3 避坑备忘录

  1. 定期使用SHOW INDEX FROM table检查冗余索引
  2. 用EXPLAIN验证是否出现"Using filesort"
  3. 字符串字段优先使用前缀索引
  4. 避免在更新频繁的字段建索引
  5. 注意索引长度限制（767字节）

5. 总结与展望

经过多个项目的实战验证，正确的索引策略可以使查询性能产生量级提升。但需要特别注意：索引设计不是一次性工作，需要随着业务发展持续优化。建议每月进行慢查询分析，每季度做全表索引健康检查。记住：好的索引就像称职的助手，它应该在正确的时间出现在正确的位置，而不是越多越好。