1. MySQL索引基础认知
第一次接触MySQL索引时，我误以为它只是简单的"目录"，直到有次处理百万级数据查询才发现认知偏差。索引本质上是一种特殊的数据结构，它能以空间换时间的方式大幅提升查询效率。在InnoDB存储引擎中，默认使用B+树作为索引结构，这种多路平衡查找树能保持O(logN)的查询复杂度。
注意：虽然索引能加速查询，但每个额外索引都会增加约1.2倍的写入开销，需要权衡读写比例
2. 索引创建全指南
2.1 单列索引创建
最基本的索引创建语法如下：
ALTER TABLE 表名 ADD INDEX 索引名 (列名);例如为用户表的手机号字段创建索引：
ALTER TABLE users ADD INDEX idx_mobile (mobile);实测在500万用户数据中，无索引时查询耗时约1.2秒，创建索引后仅需0.003秒。
2.2 复合索引设计
当查询条件涉及多个字段时，复合索引往往比单列索引更高效。创建语法：
ALTER TABLE orders ADD INDEX idx_composite (user_id, create_time);这里有个关键经验：遵循最左前缀原则。假设索引是(A,B,C)，只有以下查询能利用索引：
- WHERE A=?
- WHERE A=? AND B=?
- WHERE A=? AND B=? AND C=?
3. 索引优化实战技巧
3.1 索引选择性优化
索引选择性是指不重复的索引值与表记录数的比值。经验值：
- 高于0.3：优秀
- 0.1-0.3：尚可
- 低于0.1：考虑放弃
计算选择性的SQL：
SELECT COUNT(DISTINCT 列名)/COUNT(*) FROM 表名;3.2 覆盖索引妙用
当查询的所有字段都包含在索引中时，引擎无需回表查询数据页。例如：
-- 创建覆盖索引 ALTER TABLE products ADD INDEX idx_cover (category, price, name); -- 以下查询能利用覆盖索引 SELECT category, price, name FROM products WHERE category='电子产品';4. 索引使用避坑指南
4.1 索引失效场景
这些常见操作会导致索引失效：
- 使用!=或<>操作符
- 对索引列使用函数（如DATE()）
- 隐式类型转换（如字符串列用数字查询）
- LIKE以通配符开头（'%关键字'）
- OR条件未全部索引
4.2 索引维护策略
建议每月执行一次索引健康检查：
-- 查看索引使用频率 SELECT * FROM sys.schema_index_statistics WHERE table_schema='你的数据库名'; -- 重建碎片化索引 ALTER TABLE 表名 ENGINE=InnoDB;5. 特殊索引类型解析
5.1 全文索引应用
对于文本搜索场景，常规索引效率低下。全文索引创建方式：
ALTER TABLE articles ADD FULLTEXT INDEX ft_content (content);使用时需用MATCH AGAINST语法：
SELECT * FROM articles WHERE MATCH(content) AGAINST('数据库优化');5.2 空间索引实践
地理数据查询需要空间索引：
ALTER TABLE locations ADD SPATIAL INDEX sp_coordinate (coordinate);查询附近5公里的地点：
SELECT * FROM locations WHERE ST_Distance_Sphere(coordinate, POINT(116.404, 39.915)) <= 5000;6. 生产环境索引管理
6.1 在线DDL操作
MySQL 5.6+支持在线添加索引不影响业务：
ALTER TABLE 表名 ADD INDEX 索引名 (列名), ALGORITHM=INPLACE, LOCK=NONE;6.2 索引监控方案
推荐使用Performance Schema监控索引：
-- 开启监控 UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE '%statement/%'; -- 查看索引使用情况 SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage;经过多年实践，我发现索引优化是个持续过程。每次业务查询模式变化都可能需要调整索引策略。建议建立定期的索引审查机制，结合慢查询日志不断优化。
