MySQL 索引创建策略

索引创建基本原则

1. 选择性原则

• 选择性：索引列中不同值的数量与总行数的比值
• 高选择性索引：选择性>0.2，适合创建索引
• 低选择性索引：选择性<0.1，不适合创建索引（如性别、状态列）
• 计算方法：SELECT COUNT(DISTINCT column_name)/COUNT(*) FROM table_name

2. 最左前缀原则

• 复合索引：遵循最左前缀匹配规则，查询条件必须包含索引的最左列
• 索引顺序：将选择性高的列放在前面
• 匹配规则：从左到右依次匹配，遇到范围查询（>、<、BETWEEN）停止匹配
• 示例：创建索引 idx_name_age(name, age)，能匹配 name=、name= AND age=、name= AND age>，但不能匹配 age=

3. 覆盖索引原则

• 覆盖索引：查询的所有字段都包含在索引中，不需要回表查询
• 优势：减少IO操作，提高查询性能
• 示例：SELECT name, age FROM users WHERE name='test'，创建索引 idx_name_age(name, age) 即可覆盖查询

4. 索引列类型原则

• 整数类型：比字符串类型索引更高效
• 字符串类型：考虑使用前缀索引，避免全文索引
• 日期类型：适合创建索引，支持范围查询
• 避免在索引列上使用函数：会导致索引失效

索引创建时机

1. 适合创建索引的场景

• 频繁作为查询条件的列：WHERE子句中经常出现的列
• 用于连接的列：JOIN操作中的连接列
• 用于排序的列：ORDER BY子句中的列
• 用于分组的列：GROUP BY子句中的列
• 唯一性约束的列：UNIQUE、PRIMARY KEY约束
• 频繁作为聚合函数参数的列：如COUNT、SUM等

2. 不适合创建索引的场景

• 表行数很少：索引开销大于查询收益
• 频繁更新的列：索引维护成本高
• 低选择性的列：如性别、状态列
• TEXT、BLOB等大字段：索引占用空间大，查询效率低
• 频繁进行全表扫描的查询：索引无法优化

索引类型选择

1. 主键索引

• 特点：唯一且非空，InnoDB表自动创建聚簇索引
• 选择：优先选择自增整数类型，避免使用UUID
• 优势：自增主键能保证数据顺序写入，减少页分裂
• 示例：PRIMARY KEY (id)

2. 唯一索引

• 特点：列值唯一，允许为空
• 使用场景：需要保证列值唯一性的字段
• 示例：UNIQUE KEY idx_email (email)

3. 普通索引

• 特点：最基本的索引类型，无唯一性限制
• 使用场景：频繁作为查询条件的列
• 示例：INDEX idx_name (name)

4. 复合索引

• 特点：包含多个列的索引
• 设计原则：将选择性高的列放在前面，遵循最左前缀原则
• 示例：INDEX idx_name_age (name, age)

5. 前缀索引

• 特点：对字符串列的前N个字符创建索引
• 适用场景：长字符串列，如URL、TEXT类型
• 选择前缀长度：平衡索引大小和选择性
• 计算方法：SELECT COUNT(DISTINCT LEFT(column_name, N))/COUNT(*) FROM table_name
• 示例：INDEX idx_title (title(20))

6. 全文索引

• 特点：用于全文搜索，支持自然语言搜索
• 适用场景：大文本字段的搜索，如文章内容
• 限制：仅MyISAM和InnoDB（MySQL 5.6+）支持
• 示例：FULLTEXT KEY idx_content (content)

7. 空间索引

• 特点：用于地理空间数据类型的索引
• 适用场景：存储地理位置信息的列
• 示例：SPATIAL INDEX idx_location (location)

索引创建最佳实践

1. 表设计阶段的索引规划

• 分析业务查询：根据实际查询场景设计索引
• 避免过度索引：每个表建议不超过5-8个索引
• 考虑索引维护成本：索引会增加写操作开销
• 使用EXPLAIN分析查询：验证索引是否被有效使用

2. 复合索引设计

• 选择合适的列顺序：选择性高的列在前
• 覆盖常用查询：设计能覆盖多个查询的复合索引
• 避免冗余索引：如已有(idx_a, idx_b)，不需要单独的(idx_a)
• 考虑范围查询：范围查询列放在索引的最后

3. 前缀索引优化

• 确定合适的前缀长度：通过选择性计算确定
• 监控前缀索引性能：定期检查查询效率
• 考虑业务需求：确保前缀能满足查询精度
• 示例：

  -- 计算不同前缀长度的选择性
  SELECT 
      COUNT(DISTINCT LEFT(email, 5))/COUNT(*) AS sel_5,
      COUNT(DISTINCT LEFT(email, 10))/COUNT(*) AS sel_10,
      COUNT(DISTINCT LEFT(email, 15))/COUNT(*) AS sel_15
  FROM users;

4. 索引维护策略

• 定期分析表：使用ANALYZE TABLE更新统计信息
• 定期重建索引：使用OPTIMIZE TABLE或ALTER TABLE ... FORCE
• 监控索引使用情况：通过performance_schema或sys.schema_unused_indexes查看未使用的索引
• 删除冗余索引：定期清理无用的索引

索引创建性能优化

1. 在线创建索引

• MySQL 5.6+：支持InnoDB表的在线索引创建（ALGORITHM=INPLACE）
• 优势：创建索引过程中不阻塞DML操作
• 示例：

  ALTER TABLE users ADD INDEX idx_name (name) ALGORITHM=INPLACE, LOCK=NONE;

2. 批量创建索引

• 避免频繁单个创建：多个索引一起创建比逐个创建更高效
• 示例：

  ALTER TABLE users 
  ADD INDEX idx_name (name),
  ADD INDEX idx_age (age),
  ADD INDEX idx_email (email);

3. 利用低峰期创建索引

• 选择业务低峰期：减少对生产环境的影响
• 监控系统负载：创建索引过程中监控CPU、IO等指标
• 考虑使用pt-online-schema-change：对于大表，使用第三方工具进行在线schema变更

4. 大表索引创建策略

• 评估影响：大表创建索引前，评估执行时间和资源消耗
• 使用临时表：先在临时表上创建索引，再替换原表
• 考虑分区表：对分区表创建索引，可减少单索引大小
• 使用并行创建：MySQL 8.0+支持并行索引创建（innodb_parallel_read_threads）

索引创建常见错误

1. 过度索引

• 问题：创建过多索引，增加写操作开销和存储成本
• 解决：定期清理无用索引，保留必要的索引
• 检测：使用sys.schema_redundant_indexes查看冗余索引

2. 索引列顺序错误

• 问题：复合索引列顺序不合理，导致索引无法被有效使用
• 解决：根据查询频率和选择性调整索引列顺序
• 示例：将查询频率高的列放在前面

3. 忽略最左前缀原则

• 问题：查询条件不包含索引的最左列，导致索引失效
• 解决：调整查询条件或索引顺序
• 示例：如果创建了idx_a_b，查询WHERE b=1无法使用索引

4. 在索引列上使用函数

• 问题：导致索引失效，触发全表扫描
• 解决：避免在WHERE子句中对索引列使用函数
• 示例：

  -- 错误：索引失效
  SELECT * FROM users WHERE DATE(create_time) = '2023-01-01';
  
  -- 正确：索引有效
  SELECT * FROM users WHERE create_time BETWEEN '2023-01-01 00:00:00' AND '2023-01-01 23:59:59';

5. 使用UUID作为主键

• 问题：UUID是随机字符串，导致数据写入时频繁页分裂
• 解决：优先使用自增整数作为主键，或使用有序UUID
• 示例：

  -- 有序UUID（基于时间戳）
  SELECT UUID_TO_BIN(UUID(), TRUE);

版本兼容性考虑

MySQL 5.5及以下

• 限制：不支持在线索引创建，创建索引时会阻塞表
• 建议：使用pt-online-schema-change工具进行在线schema变更
• 索引类型：全文索引仅MyISAM支持

MySQL 5.6-5.7

• 改进：支持InnoDB在线索引创建（ALGORITHM=INPLACE）
• 全文索引：InnoDB开始支持全文索引
• 索引优化：引入了更多索引相关的优化

MySQL 8.0+

• 新特性：支持并行索引创建
• 索引类型：引入了隐藏索引（INVISIBLE INDEX）
• 性能提升：索引维护和查询性能进一步优化
• 示例：

  -- 创建隐藏索引
  ALTER TABLE users ADD INDEX idx_temp (temp) INVISIBLE;
  
  -- 切换索引可见性
  ALTER TABLE users ALTER INDEX idx_temp VISIBLE;

常见问题（FAQ）

Q1: 如何确定是否需要创建索引？

A1: 考虑以下因素：

• 列的选择性：选择性高的列适合创建索引
• 查询频率：频繁作为查询条件的列需要索引
• 写操作频率：写操作频繁的表，索引数量应适当减少
• 表的大小：小表不需要过多索引

Q2: 复合索引的列顺序如何选择？

A2: 复合索引列顺序选择原则：

• 选择性高的列放在前面
• 频繁作为查询条件的列放在前面
• 范围查询列放在最后
• 考虑覆盖索引，将查询中需要的列包含在索引中

Q3: 如何选择前缀索引的长度？

A3: 选择前缀索引长度的方法：

• 计算不同前缀长度的选择性，选择接近完整列选择性的最短前缀
• 考虑索引大小和查询性能的平衡
• 示例：SELECT COUNT(DISTINCT LEFT(column_name, N))/COUNT(*) FROM table_name

Q4: 如何处理大表的索引创建？

A4: 大表索引创建策略：

• 选择业务低峰期进行操作
• 使用在线索引创建（ALGORITHM=INPLACE）
• 考虑使用pt-online-schema-change工具
• 评估影响，监控系统负载
• 对于超大型表，考虑分区表或分库分表

Q5: 如何检测和删除无用索引？

A5: 检测和删除无用索引的方法：

• 使用sys.schema_unused_indexes查看未使用的索引
• 使用sys.schema_redundant_indexes查看冗余索引
• 分析慢查询日志，查看索引使用情况
• 删除索引前，先将其设置为隐藏索引（MySQL 8.0+），观察一段时间后再删除

Q6: 为什么有时候创建了索引，查询还是慢？

A6: 可能的原因：

• 索引没有被使用（索引失效）
• 索引选择性低，导致扫描行数过多
• 查询需要回表，没有使用覆盖索引
• 统计信息过时，导致优化器选择了错误的执行计划
• 解决方法：使用EXPLAIN分析查询执行计划，调整索引或查询语句

Q7: 在线创建索引会影响业务吗？

A7: 在线创建索引（ALGORITHM=INPLACE）的影响：

• 不会阻塞DML操作（INSERT、UPDATE、DELETE）
• 可能会增加系统负载，特别是IO负载
• 对于大表，创建过程可能需要较长时间
• 建议在业务低峰期进行操作，并监控系统负载

Q8: 如何优化UUID主键的性能？

A8: UUID主键性能优化方法：

• 使用有序UUID：UUID_TO_BIN(UUID(), TRUE)
• 考虑使用自增整数作为主键，UUID作为业务字段
• 对于分布式系统，考虑使用雪花算法生成有序ID
• 调整innodb_autoinc_lock_mode参数，优化自增主键生成性能