数据库优化痛点

SQL 执行慢、没有合适索引、锁等待时间长、数据库资源（IO/CPU）耗尽

怎么办？

整体和层次

整体

用户发起请求，到收到结果经历的部分：

1. 客户端
2. Web 端
3. 数据库缓存
4. 连接池
5. 数据库

层次	业务层	物理层	优化策略
第一层	查询需求	SQL 层	减少查询
第二层	查询优化器	MySQL 层	查询路径优化
第三层	存储引擎	InnoDB 层	锁和缓存优化
第四层	资源	硬件层	硬件优化、扩容

优化方法论

• 尽量从上层做优化，上层的优化效果更好

优化策略

• 上层异常
  • 数据库上层缓存（比如 Redis）失效，导致数据库压力大的最佳优化是恢复上层缓存

• 减少查询
  • 思考是否不必要的查询落到数据库上，能否减少

• 查询路径优化
  • 索引是一种数据结构，为查询优化器提供了可供选择的查询路径
  • 查询优化器上的优化

• 锁和缓存优化
  • 避免大事务、长事务，减少锁，增大存储引擎缓存

• 硬件优化
  • 优化硬件设置（操作系统层参数）或硬件本身。扩容资源（CPU、内存和 IO 等）

查询优化器

• limit 提前终止查询
• 由于查询优化器依赖存储引擎提供的统计信息来评估成本，而 InnoDB 的统计信息又是抽样的，可能不准确，所以可能需要重新定义表关联顺序，需要满足以下两点：
  1. 使用小表驱动大表
  2. 在被驱动表的关联列上有索引

SQL 优化和索引优化

1. 索引是存储引擎用于快速定位记录的一种数据结构
2. 索引对于良好的性能非常关键，好的索引能够轻易将查询性能提高几个数量级
3. 索引并不一定是最好的解决方案
   1. 小型表（< 1 万）全表（主键）扫描更高效
   2. 中型以上表（> 10 万）索引效果好，但影响写入性能，单表索引不能过多，看看能不能将多个索引合成组合索引，要权衡索引的最佳顺序
4. 主键索引
   1. 要单调递增，避免页分裂等情况
   2. 占用空间尽可能小，因为二级索引叶子节点存放的是主键索引
5. MySQL 版本
   1. MySQL5.6 之后引入了索引下推，会对索引中存在的的数据直接进行过滤，减少回表次数
6. SQL 编写
   1. 尽量只查需要的字段，可能会使用上覆盖索引，而且如果字段大的话，取出来也是个不小的开销
   2. 避免不使用索引的写法 不使用索引的情况

参数配置

1. IO 线程
   1. 默认 read 线程和 write 线程都是 4 个，在服务器配置高的时候，建议调整线程数，尽量利用核心资源
2. innodb_file_per_table
   1. 在 5.6.6 之后已经默认为 ON，但建议哪个版本都设置为 ON，因为不开启的话，所有的表数据都存在一个文件里，不利于表空间文件维护，并且 drop table 后并不会删除数据，而开启之后会直接删除这个文件
   2. 还会存放表的索引，在我们对文件提取的时候，根据索引找到内存页的偏移量，文件越大，性能也越低
3. MRR 优化

默认只有在优化器认为 MRR 可以带来优化的情况下才会走 MRR，如果你想不管什么时候能走 MRR 的都走 MRR 的话，你要把 mrr_cost_based 设置为 off，不过最好不要这么干，因为这确实是一个坑，MRR 不一定什么时候都好，全表扫描有时候会更加快
技术分享 | 用好 MySQL 的 MRR 优化器_ActionTech的博客-CSDN博客

4. MySQL 参数 - 掘金 (juejin.cn)

硬件优化

1. 大表分区，防止单个表占用磁盘空间过大，检索效率慢，但要注意如何分区
2. 单主多从，从库读取，分散读压力
3. 水平或垂直拆分
4. 多主多从