🚀 一、SQL 与索引优化（最直接有效）

✅ 1. 合理使用索引

• 为 WHERE、ORDER BY、GROUP BY 字段创建索引
• 使用联合索引替代多个单列索引
• 遵循最左前缀原则（INDEX(a,b,c) 可用于 a=1, a=1 AND b=2，但不能用于 b=2）

✅ 2. 避免索引失效

• ❌ 不要在索引列上使用函数：WHERE YEAR(create_time) = 2024
• ❌ 避免隐式类型转换：WHERE user_id = '123'（user_id 是 INT）
• ❌ 避免前导通配符：LIKE '%abc'
• ❌ 避免 OR 连接无索引字段

✅ 3. 使用覆盖索引

• 让查询字段都包含在索引中，避免“回表”
• 示例：SELECT name, age FROM users WHERE dept='tech' + INDEX idx_dept_name_age(dept, name, age)

✅ 4. 避免 SELECT *

• 只查询需要的字段，减少数据传输和回表开销
• 尤其避免在大文本字段（TEXT）表中使用

✅ 5. 优化 JOIN 查询

• 确保 JOIN 字段有索引
• 小表驱动大表（MySQL 会自动优化，但需注意）
• 避免多表 JOIN（超过 3 张表需警惕）

✅ 6. 合理使用 LIMIT 分页

• 避免 LIMIT 1000000, 20（偏移量大，性能差）
• 改用“记录上次 ID”方式分页：

  SELECT * FROM users WHERE id > 1000000 ORDER BY id LIMIT 20;

---

📐 二、表结构与数据类型优化

✅ 7. 选择合适的数据类型

• 用 INT 而不是 VARCHAR 存数字
• 用 TINYINT 存状态（0/1），不用 INT
• 用 DATETIME 而不是 VARCHAR 存时间

✅ 8. 避免使用 NULL

• 尽量设置字段为 NOT NULL + 默认值
• NULL 值会影响索引效率和查询逻辑

✅ 9. 垂直分表

• 将大字段（如 content, resume）拆到单独的表
• 主表只保留高频查询字段，提升查询速度

✅ 10. 水平分表（分库分表）

• 数据量巨大（千万级以上）时，按 user_id、time 等字段分表
• 工具：ShardingSphere、MyCat

---

⚙️ 三、执行计划与慢查询分析

✅ 11. 使用 EXPLAIN 分析 SQL

• 查看是否使用索引（key 字段）
• 查看扫描行数（rows）
• 查看是否使用文件排序（Using filesort）、临时表（Using temporary）

EXPLAIN SELECT * FROM users WHERE email = 'xxx';

✅ 12. 开启慢查询日志

• 记录执行时间超过阈值的 SQL
• 配置：

  SET long_query_time = 1;
  SET slow_query_log = ON;

• 分析工具：mysqldumpslow、pt-query-digest

---

🧩 四、数据库架构优化

✅ 13. 读写分离

• 主库负责写，从库负责读
• 减少主库压力，提升读性能
• 工具：MySQL Replication + 中间件（如 ShardingSphere）

✅ 14. 数据库连接池优化

• 使用 HikariCP、Druid 等高性能连接池
• 合理设置最大连接数、超时时间

✅ 15. 使用缓存（Redis / Memcached）

• 缓存热点数据（如用户信息、配置）
• 减少数据库查询压力
• 策略：Cache Aside、Read/Write Through

✅ 16. 使用搜索引擎（Elasticsearch）

• 复杂查询（全文搜索、多条件组合）交给 ES
• MySQL 只负责事务和精确查询

---

🧪 五、服务器与配置优化

✅ 17. 合理配置 MySQL 参数

• innodb_buffer_pool_size：设置为物理内存的 70%-80%
• query_cache_size：已废弃（MySQL 8.0），不用
• max_connections：根据业务调整

✅ 18. 使用 SSD 硬盘

• I/O 性能远高于机械硬盘
• 对数据库性能提升显著

✅ 19. 定期分析表和重建索引

ANALYZE TABLE users;  -- 更新统计信息
OPTIMIZE TABLE users; -- 重建表和索引（谨慎使用）

---

🛠️ 六、开发与设计层面的优化

✅ 20. 避免在循环中查询数据库

• ❌ 错误：

  for (User user : users) {
      userDao.selectById(user.getId()); // N+1 查询
  }

• ✅ 正确：批量查询

  List<User> users = userDao.selectByIds(userIds);

✅ 21. 使用批量操作

• 批量插入：

  INSERT INTO users (name, age) VALUES ('A',1),('B',2),('C',3);

• 批量更新：使用 CASE WHEN 或临时表

✅ 22. 使用数据库连接池监控

• 监控连接数、等待时间、慢查询
• 工具：Druid Monitor、Prometheus + Grafana

---

📊 七、优化效果对比（示例）

优化前	优化后	性能提升
SELECT * FROM users WHERE name = '张三'（无索引）	SELECT id,name FROM users WHERE name = '张三' + 索引	1000ms → 10ms
LIMIT 1000000, 20	WHERE id > 1000000 LIMIT 20	800ms → 20ms
直接查大表	Redis 缓存热点数据	50ms → 2ms

---

✅ 总结：数据库查询优化 checklist

类别	优化项
SQL 与索引	加索引、覆盖索引、避免 SELECT *、EXPLAIN 分析
表结构	合理数据类型、非空、垂直/水平分表
架构	读写分离、缓存、搜索引擎
配置	连接池、buffer_pool、SSD
开发	避免循环查库、批量操作、监控

🔥 记住：
优化不是一蹴而就的，而是“持续分析 + 逐步改进”。
掌握这些方法，你就能应对 95% 的数据库性能问题。