一、优化索引设计

1. 哪些情况需要建索引？

• 频繁作为查询条件的列（WHERE、JOIN、ON 后的列）
• 排序 / 分组的列（ORDER BY、GROUP BY 后的列）
• 联合索引：多条件查询时，按 “最左前缀原则” 创建联合索引（如 WHERE a=? AND b=? 可建 (a,b) 索引）

2. 避免无效索引

• 不建索引：
  • 表数据量极小（如几百行）
  • 频繁更新的列（索引会增加写入开销）
  • 低基数列（如性别、状态只有几个值的列）
• 避免重复索引（如同时建 (a) 和 (a,b) 索引，前者冗余）
• 删除长期未使用的索引（可通过 sys.schema_unused_indexes 查看）

3. 索引失效的常见场景

• 使用函数或运算操作索引列（如 WHERE SUBSTR(name,1,3)='abc'）
• 隐式类型转换（如字符串列用数字查询 WHERE phone=13800138000）
• WHERE 子句中用 NOT、!=、<>、NOT IN 等可能导致全表扫描
• LIKE 以通配符开头（如 WHERE name LIKE '%abc'）

二、优化 SQL 语句

1. 避免全表扫描

• 确保查询条件能命中索引（用 EXPLAIN 分析执行计划）
• 不写无 WHERE 条件的全表查询（如 SELECT * FROM large_table）

2. 优化 SELECT 子句

• 只查询需要的列，避免 SELECT *（减少数据传输和内存消耗）
  sql

  -- 差：查询所有列
  SELECT * FROM users WHERE age > 30;
  
  -- 好：只查需要的列
  SELECT id, name FROM users WHERE age > 30;
  

   

3. 优化 JOIN 查询

• 小表驱动大表（JOIN 时，将数据量小的表作为驱动表）
• 确保 JOIN 条件列有索引（如 a JOIN b ON a.id = b.a_id，需在 b.a_id 建索引）
• 避免 JOIN 过多表（建议不超过 5 张表，否则考虑分拆查询）

4. 优化子查询

• 复杂子查询改用 JOIN（子查询可能产生临时表，性能较差）
  sql

  -- 子查询（较差）
  SELECT name FROM users WHERE id IN (SELECT user_id FROM orders WHERE status=1);
  
  -- JOIN 改写（较好）
  SELECT u.name FROM users u JOIN orders o ON u.id = o.user_id WHERE o.status=1;
  

   

5. 优化排序和分组

• ORDER BY/GROUP BY 的列尽量包含在索引中（避免额外排序）
• 避免 SELECT DISTINCT（可用 GROUP BY 替代，或通过索引优化）

三、使用 EXPLAIN 分析执行计划

用法

EXPLAIN SELECT id, name FROM users WHERE age > 30;

关键字段解读

• type：访问类型，性能从好到差为 system > const > eq_ref > ref > range > index > ALL（目标是至少达到 range 或 ref）
• key：实际使用的索引（NULL 表示未用索引）
• rows：预估扫描行数（值越小越好）
• Extra：额外信息（如 Using index 表示覆盖索引，Using filesort 表示需要额外排序，需优化）

四、优化表结构

1. 选择合适的数据类型

• 优先用小类型（如 INT 替代 BIGINT，VARCHAR(20) 替代 VARCHAR(255)）
• 时间用 DATETIME 或 TIMESTAMP（而非字符串）
• 枚举值用 ENUM 类型（如性别、状态）

2. 拆分大表

• 垂直拆分：将大表按字段关联性拆分为小表（如用户表拆分为 user_base 和 user_detail）
• 水平拆分：按规则拆分数据（如按时间分表 orders_2023、orders_2024，按用户 ID 哈希分表）

3. 避免使用 NULL

• 尽量用默认值替代 NULL（如用 0 表示数量，'' 表示空字符串），NULL 会增加索引和查询复杂度。

五、其他优化技巧

1. 控制结果集大小

• 用 LIMIT 限制返回行数（尤其分页查询）
  sql

  -- 分页查询优化（避免大偏移量，如 LIMIT 100000, 10）
  SELECT * FROM articles WHERE id > 100000 LIMIT 10;  -- 假设 id 是自增主键
  

   

2. 优化数据库配置

• 调整 my.cnf/my.ini 参数：
  • innodb_buffer_pool_size：InnoDB 缓存池（建议设为物理内存的 50%-70%）
  • query_cache_size：查询缓存（MySQL 8.0 已移除，低版本按需开启）
  • max_connections：最大连接数（避免连接耗尽）

3. 定期维护表

• 分析表：ANALYZE TABLE 表名;（更新索引统计信息，帮助优化器生成更好的执行计划）
• 优化表：OPTIMIZE TABLE 表名;（回收碎片，适用于频繁删除 / 更新的表）

4. 避免锁等待

• 长事务拆分为短事务（减少锁持有时间）
• 批量操作分批次执行（如一次更新 1000 行，而非 100 万行）

总结