1. 索引优化为何能带来10倍性能提升？
当数据库表数据量超过百万级时，没有索引的查询就像在图书馆里逐页翻找特定内容。我最近优化过一个电商平台的订单查询接口，原本需要8秒的查询在优化后仅需0.7秒。这种量级的性能飞跃主要来自三个机制：
索引的B+树结构使得查找时间复杂度从O(n)降到O(log n)。以包含1000万记录的用户表为例，全表扫描需要检查1000万个数据页，而通过索引通常只需3-4次磁盘I/O（假设树高为4）。
覆盖索引（Covering Index）能避免回表操作。当我们创建包含(user_id, username, email)的联合索引时，SELECT username FROM users WHERE user_id=?的查询可以直接从索引获取数据，无需访问主表。某社交平台应用此策略后，其核心接口的IOPS降低了72%。
索引条件下推（ICP）是MySQL5.6引入的重要特性。它允许在存储引擎层提前过滤数据，减少向上层传输的数据量。在某物流系统中，对WHERE status=1 AND create_time>'2023-01-01'的查询，使用ICP后传输数据量从230MB降至15MB。
关键提示：索引不是银弹，不当使用反而会降低性能。我曾遇到一个每张表创建10+个索引的案例，导致写入性能下降60%，因为每次INSERT都需要更新所有相关索引。
2. 索引类型选型的黄金法则
2.1 B-Tree索引的适用场景
B-Tree索引适合等值查询和范围查询，95%的OLTP场景都应优先考虑。某金融系统在账户表的account_number字段添加B-Tree索引后，查询耗时从1200ms降至8ms。但要注意：
- 最左前缀原则：对于
(A,B,C)的联合索引，WHERE A=1 AND B>2能使用索引，但WHERE B>2无法使用 - 索引列顺序应该将区分度高的字段放前面。用户表的
(gender, age)索引效果远差于(age, gender)
2.2 哈希索引的精准定位
哈希索引适合等值查询且不排序的场景。某缓存系统用哈希索引实现用户session查找，QPS从2000提升到15000。但要注意：
- 不支持范围查询
- 存在哈希冲突可能
- InnoDB的自适应哈希索引是自动管理的
2.3 全文索引的文本搜索优化
对于商品描述等文本字段，全文索引比LIKE '%keyword%'高效得多。某内容平台改用全文索引后，搜索延迟从2s降到200ms。关键配置：
ALTER TABLE articles ADD FULLTEXT INDEX ft_index (title, body); SELECT * FROM articles WHERE MATCH(title, body) AGAINST('数据库优化');3. 实战中的索引策略设计
3.1 联合索引的排列组合
设计联合索引时要考虑查询模式。电商平台典型场景：
-- 查询模式：按分类+状态+时间筛选商品 ALTER TABLE products ADD INDEX idx_category_status_time (category_id, status, create_time); -- 好的查询：能充分利用索引 SELECT * FROM products WHERE category_id=5 AND status=1 ORDER BY create_time DESC LIMIT 10; -- 差的查询：无法使用status之后的索引列 SELECT * FROM products WHERE status=1;3.2 前缀索引的存储优化
对于长字符串字段，前缀索引能显著减少索引大小。某日志系统对request_uri字段采用前20字符作为索引，索引大小减少80%：
ALTER TABLE access_log ADD INDEX idx_uri_prefix (request_uri(20));通过计算选择性确定最佳长度：
SELECT COUNT(DISTINCT LEFT(request_uri, 10))/COUNT(*) AS sel10, COUNT(DISTINCT LEFT(request_uri, 20))/COUNT(*) AS sel20, COUNT(DISTINCT LEFT(request_uri, 30))/COUNT(*) AS sel30 FROM access_log;3.3 函数索引的巧妙应用
MySQL 8.0+支持函数索引，某国际化应用对用户邮箱统一小写处理：
ALTER TABLE users ADD INDEX idx_lower_email ((LOWER(email)));4. 索引优化诊断工具箱
4.1 EXPLAIN的深度解读
分析这个执行计划：
EXPLAIN SELECT * FROM orders WHERE user_id=100 AND status='paid' ORDER BY create_time DESC;重点关注：
- type列：const > ref > range > index > ALL
- key_len：确认实际使用的索引长度
- Extra：Using filesort表示需要额外排序
4.2 慢查询日志分析
配置my.cnf捕获慢查询：
slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql-slow.log long_query_time = 1 log_queries_not_using_indexes = 1使用pt-query-digest分析：
pt-query-digest /var/log/mysql/mysql-slow.log > slow_report.txt4.3 索引效率评估
通过sys库分析索引使用情况：
SELECT * FROM sys.schema_unused_indexes; SELECT * FROM sys.schema_redundant_indexes;5. 高级优化技巧与避坑指南
5.1 索引跳跃扫描
MySQL 8.0的索引跳跃扫描特性，即使不满足最左前缀也能利用索引：
-- 索引 (gender, age) SELECT * FROM users WHERE age > 20; -- 8.0+可以转化为类似 WHERE gender IN('M','F') AND age > 205.2 不可见索引的灰度发布
先设置索引不可用，验证无性能影响再删除：
ALTER TABLE orders ALTER INDEX idx_test INVISIBLE; -- 观察期后 ALTER TABLE orders DROP INDEX idx_test;5.3 索引合并的陷阱
优化器可能合并多个单列索引，但效率通常不如联合索引：
-- 有index(a)和index(b) SELECT * FROM tbl WHERE a=1 AND b=2; -- 可能使用Index Merge而非更优的联合索引6. 真实案例：电商平台优化实录
某电商平台商品搜索接口优化过程：
- 原始查询（耗时1200ms）：
SELECT * FROM products WHERE category_id=5 AND price BETWEEN 100 AND 500 AND stock > 0 ORDER BY sales_volume DESC LIMIT 20;- 优化方案：
- 创建
(category_id, stock, price, sales_volume)联合索引 - 改写查询确保索引生效
- 最终效果：
- 查询时间降至85ms
- 服务器CPU负载从70%降到15%
血泪教训：曾因未考虑索引维护成本，在高峰时段添加大表索引导致主从延迟30分钟。现在都在业务低峰期执行：
ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE;
