索引是 MySQL 提升查询性能的核心，但不合理的索引会导致写入性能下降、内存浪费，常见索引原则如下（核心：“按需创建、高效使用”）：

1. 索引创建原则

• 1. 为高频查询字段创建索引：优先为WHERE子句、JOIN关联字段、ORDER BY/GROUP BY字段创建索引（这些字段是查询的过滤、关联、排序核心）；
  • 示例：SELECT * FROM user WHERE age > 20（age 字段创建索引）、SELECT * FROM user JOIN order ON user.id = order.user_id（order.user_id 创建索引）。
• 2. 联合索引遵循 “最左前缀原则”：联合索引（如idx_age_name (age, name)）的查询效率依赖字段顺序，查询时需从左到右使用字段，否则索引失效；
  • 有效查询：WHERE age=20、WHERE age=20 AND name='张三'（使用最左前缀 age 或 age+name）；
  • 无效查询：WHERE name='张三'（跳过最左前缀 age，索引失效）。
• 3. 避免过度索引：索引并非越多越好，每个索引会占用磁盘空间，且写入操作（INSERT/UPDATE/DELETE）时需维护索引（如更新字段值时，索引树需重新排序），过度索引会导致写入性能下降；
  • 建议：单表索引数量不超过 5 个，优先创建联合索引（覆盖多个查询场景），替代多个单列索引。
• 4. 不为低基数字段创建索引：低基数字段（如性别、状态，取值只有 2-3 种）的索引过滤性差（如WHERE gender='男'会匹配 50% 的数据），索引查询效率接近全表扫描，无需创建索引；
  • 反例：为gender（男 / 女）、status（0/1/2）创建索引。
• 5. 为字符串字段创建前缀索引：长字符串字段（如手机号、邮箱）创建完整索引会占用大量空间，可创建前缀索引（只索引前 N 个字符），平衡空间和效率；
  • 示例：CREATE INDEX idx_phone ON user(phone(11))（手机号前 11 位唯一，索引前 11 位）。

2. 索引使用原则

• 1. 避免索引失效的操作：
  • 避免在索引字段上做函数操作（如WHERE DATE(create_time)='2024-01-01'，改为WHERE create_time BETWEEN '2024-01-01 00:00:00' AND '2024-01-01 23:59:59'）；
  • 避免使用!=、<>、NOT IN、IS NULL/IS NOT NULL（可能导致索引失效，改为BETWEEN、IN）；
  • 避免使用模糊查询前缀通配符（如WHERE name LIKE '%张三'，索引失效；改为WHERE name LIKE '张三%'，索引有效）；
  • 避免字符串与数字比较（如WHERE phone=13800138000，phone 是字符串，改为WHERE phone='13800138000'）。
• 2. 优先使用覆盖索引：查询字段若均在索引中（如SELECT id, name FROM user WHERE age=20，索引idx_age_id_name (age, id, name)），无需回表查询数据文件，效率更高；
  • 示例：创建联合索引idx_age_id_name，覆盖查询字段 age（条件）、id（主键）、name（查询字段）。
• 3. 索引字段避免为 NULL：NULL 值会影响索引的排序和查询效率，建议字段设置为NOT NULL（如age INT NOT NULL DEFAULT 0），用默认值替代 NULL。
• 4. 定期维护索引：
  • 查看索引使用率：通过sys.schema_unused_indexes视图查看未使用的索引，及时删除；
  • 优化碎片化索引：频繁更新的表（如订单表）会导致索引碎片化，可通过OPTIMIZE TABLE优化（InnoDB 表需开启独立表空间）。