MySQL 索引优化

索引类型

1. 普通索引（INDEX）

   • 最基本的索引类型
   • 没有唯一性限制

   CREATE INDEX index_name ON table_name (column_name);
   

2. 唯一索引（UNIQUE）

   • 确保列中的值是唯一的
   • 可以包含NULL值

   CREATE UNIQUE INDEX index_name ON table_name (column_name);
   

3. 主键索引（PRIMARY KEY）

   • 特殊的唯一索引
   • 不允许NULL值

   ALTER TABLE table_name ADD PRIMARY KEY (column_name);
   

4. 复合索引（Composite Index）

   • 多列组成的索引
   • 遵循最左前缀原则

   CREATE INDEX index_name ON table_name (col1, col2, col3);
   

5. 全文索引（FULLTEXT）

   • 用于全文搜索
   • 只支持CHAR、VARCHAR和TEXT列

   CREATE FULLTEXT INDEX index_name ON table_name (column_name);
   

索引设计原则

1. 选择合适的列建立索引

   • 经常用于查询的列
   • 经常用于连接的列
   • 经常用于排序的列

2. 索引列的选择性

   • 选择性越高，索引效果越好
   • 计算选择性：COUNT(DISTINCT column_name) / COUNT(*)

3. 前缀索引

   • 对于长字符串列，可以只索引开始的部分

   CREATE INDEX index_name ON table_name (column_name(length));
   

4. 避免过多索引

   • 索引会占用存储空间
   • 影响插入、更新和删除性能
   • 合理使用复合索引代替单列索引

索引使用优化

1. EXPLAIN分析

   EXPLAIN SELECT * FROM users WHERE name = 'John';
   

2. 避免索引失效的情况

   • 避免在索引列上使用函数
   • 避免隐式类型转换
   • 注意最左前缀原则
   • 避免使用NOT IN和!=

3. 覆盖索引

   • 查询的列都包含在索引中
   • 可以避免回表查询

   -- 好的例子
   SELECT id, name FROM users WHERE name = 'John';
   -- name列上有索引，包含了id和name
   

4. 强制索引

   SELECT * FROM users FORCE INDEX (index_name) WHERE age > 20;
   

索引维护

1. 定期分析表

   ANALYZE TABLE table_name;
   

2. 重建索引

   ALTER TABLE table_name DROP INDEX index_name;
   ALTER TABLE table_name ADD INDEX index_name (column_name);
   

3. 监控索引使用情况

   SHOW INDEX FROM table_name;
   

最佳实践

1. 定期检查和优化

   • 使用EXPLAIN分析查询计划
   • 监控索引使用情况
   • 删除不必要的索引

2. 合理使用复合索引

   • 遵循最左前缀原则
   • 考虑查询模式
   • 优化索引顺序

3. 注意索引维护成本

   • 权衡查询性能和维护成本
   • 避免过度索引
   • 定期优化和重建索引