前言
每个提供DBMS服务的云平台，背后都有一个/批顶级DBA，通过平台把这些顶级DBA批量复制，提供平台化服务。
作为用户可以更容易入手，入门门槛降低
作为非用户/野生DBA，可以从平台的白皮书中学习顶级DBA优化思路
厂商MySQL优化建议
AWS
Caching: innodb_buffer_pool_size
Database writes： innodb_flush_neighbors /innodb_log_file_size
read replica ：SSD/MTS
合适的innodb_buffer_pool_size避免lru原因频繁读磁盘淘汰出内存（ hotdataset）
数据写入考虑SSD的高性能，调整innodb_flush_neighbors=0不合并顺序刷新加快响应；设置innodb_log_file_size大小（1小时），减少checkpoint的频率，如ck被迫刷脏
读副本（从库）数据扩展读能力，在从库上使用SSD加快IO/使用MTS多线程回放
验证案例：
aliyun
RDS MySQL慢SQL问题
RDS MySQL内存使用问题
RDS MySQL I/O高问题
RDS MySQL活跃线程数高问题
RDS MySQL慢SQL问题
SQL异常:库表结构设计不合理、索引缺失、扫描行数太多
实例瓶颈:业务量持续增长超过设计上限,硬件老化，数据量一增长/分布倾斜
版本升级：执行计划变化，分析索引使用情况、扫描的行数等，预估查询效率，重构SQL语句、调整索引，提升查询效率
参数设置不当：innodb_buffer_pool_instances，join_buffer_size设置不合理
缓存失效：监控缓存命中率、QPS、TPS；使用Thread Pool、Fast Query Cache、自动SQL限流等功能提高性能
批量操作：大批量的数据导入、删除、查询操作；查看Binlog大小监控；在业务低峰期执行大批量操作，或将大批量操作拆分后分批执行
未关闭事务：突然变慢，查看CPU和IOPS的使用率并不高，而且活跃会话持续增多；检查导致事务冲突的锁并中止对应的SQL语句
定时任务：负载随时间有规律性变化，可能是存在定时任务；调整定时任务的执行时间，建议在业务低峰期执行
RDS MySQL内存使用问题
多语句（multiple statements）：多个SQL语句用英文分号（;）分隔，然后一起发给MySQL，监控网络流量会有突增；尽量避免multiple statements的SQL发送方式
缓冲池（Buffer Pool）问题：
数据页预热不足导致查询的延迟较高，脏页累积太多。当未刷新脏页的最旧LSN和当前LSN的距离超过76%时，会触发用户线程同步刷新脏页，导致实例性能严重下降，优化方式是均衡写入负载、避免写入吞吐过高
innodb_buffer_pool_instances设置较小，高QPS负载情况下，缓冲池的锁竞争会比较激烈，将参数innodb_buffer_pool_instances设置为8或16
临时表
内存临时表大小受到参数tmp_table_size和max_heap_table_size限制，超过限制后将转化为磁盘临时表，如果瞬间有大量的连接创建大量的临时表，可能会造成内存突增。MySQL 8.0实现了新的temptable engine，所有线程分配的内存临时表大小之和必须小于参数temptable_max_ram，temptable_max_ram默认为1 GB，超出后转换为磁盘临时表
其他原因
如果实例内表特别多或QPS很高，Table Cache可能也会消耗内存，建议实例避免创建太多表或设置参数table_open_cache过大。
自适应哈希索引占用的内存默认是Bufffer Pool的1/64。如果查询或写入长度非常大的Blob大字段，会对大字段动态分配内存，也会造成内存增加
RDS MySQL I/O高问题
高吞吐导致实例I/O高
表上有很多索引或大字段，频繁地更新、删除、插入，读取数据和刷新脏页时会有大量的I/O；调整刷脏参数，尽量平缓刷脏，调整刷脏参数
innodb_max_dirty_pages_pct
innodb_max_dirty_pages_pct_lwm
innodb_io_capacity 影响刷新脏页和写入缓冲池的速率
innodb_io_capacity_max
临时表导致实例I/O高
慢SQL排序、去重等操作导致创建很大的临时表；进行SQL优化，避免慢SQL
读取冷数据导致实例I/O高
如果SQL查询或修改的数据不在缓冲池（Buffer Pool），产生大量的I/O吞吐；根据业务场景重新设计缓存策略，或者升级实例规格
DDL语句导致实例I/O高
DDL语句rebuild刷新新表产生的脏页，这些都会导致大量的I/O吞吐。另外一种场景是删除大表造成的I/O抖动;使用阿里云自研内核AliSQL提供的异步删除大文件功能解决问题
大事务写Binlog导致实例I/O高
事务只有在提交时才会写Binlog文件，如果存在大事务，Binlog文件刷新到磁盘时，会造成很高的I/O吞吐;尽量将事务拆分，避免大事务和降低刷新磁盘频率
RDS MySQL活跃线程数高问题
查看活跃线程数
查看会话连接情况，如果线程数过高，说明实例会话有阻塞
排查慢SQL堆积问题
show processlist;命令查看是否有慢SQL;使用SQL限流功能或结束会话，降低慢SQL的影响
排查表缓存（Table Cache）问题
Table Cache不足时，会导致大量SQL处于Opening table状态;将参数table_open_cache（不需要重启实例）和table_open_cache_instances（需要重启实例）调大
排查元数据锁（MDL）问题
出现MDL锁时，会导致大量SQL处于Waiting for table metadata lock的状态;表上有未提交事务或慢SQL，会阻塞DDL操作，DDL操作又会阻塞其他的SQL，最终导致活跃线程数升高
中止未提交事务、慢SQL或正在执行的DDL
排查行锁冲突问题
通过show engine innodb status;命令查看是否有大量会话处于Lock wait状态;需要通过优化热点更新、降低事务大小、及时提交事务等方法避免行锁冲突
tencent
CPU 利用率过高
内存使用率过高
慢查询数过高
CPU 利用率过高
1.用户线程
慢查询
order by，group by，临时表，join ；慢 SQL 进行分析，建立对应索引（快速查找，有序），减少不必要sort(需求)，group by（条件不下推）
计算量大
单纯的数据量比较多，导致计算量巨大； 读写分离，把单个大查询拆分成多个小查询
高 QPS（Queries Per Second ）
单纯的 QPS 压力高,业务过载：挂载只读实例，优化查询语句
2.系统线程
在实际的环境中，系统线程遇到问题的情况会比较少，当然有一些 bug 可能会有影响
内存使用率过高
出现内存突增，内存持续增长不释放的情况
优化慢 SQL，减少 session 级的私有内存使用量。可以通过 DBbrain 对慢 SQL 进行分析。
2. 减少无效的长连接，在不影响业务的情况下，降低程序侧的连接池配置或者降低程序侧的并发度。可以通过 DBbrain 查看当前会话信息。
3. 监控内存使用情况（可选，适用于 MySQL 5.7 及以上版本）：开启 performance_schema 的内存监控功能，需要开启 performance_schema 后，在 performance_schema 库中查询名字为 memory_summary 开头的表来得知内存使用情况，例如，全局维度的内存利用率分析表：memory_summary_global_by_event_name。
4. 优化完成之后的手段：升级云数据库 MySQL 的配置。
慢查询数过高
出现内存突增，内存持续增长不释放的情况
优化慢 SQL，减少 session 级的私有内存使用量。可以通过 DBbrain 对慢 SQL 进行分析。
2. 减少无效的长连接，在不影响业务的情况下，降低程序侧的连接池配置或者降低程序侧的并发度。可以通过 DBbrain 查看当前会话信息。
3. 监控内存使用情况（可选，适用于 MySQL 5.7 及以上版本）：开启 performance_schema 的内存监控功能，需要开启 performance_schema 后，在 performance_schema 库中查询名字为 memory_summary 开头的表来得知内存使用情况，例如，全局维度的内存利用率分析表：memory_summary_global_by_event_name。
4. 优化完成之后的手段：升级云数据库 MySQL 的配置。
总结
从各云平台关于mysql的优化来看，有些是基于mysql官方版本的参数，慢SQL优化，有些则是基于内核修改的一些特性；这里只做官方特性进行总结
优化思路
1.基于需求设计选择合适规格的资源，一个好系统是设计出来的，也是有上下限
2.对于优化要充分发挥资源的能力，不低估避免资源浪费，也不高估避免运行过载（压力超过资源上限）
3.当超过设计规划上限，该升级资源升级资源；如：扩展cpu,内存，只读实例读写分离，写扩展迁移到分布式数据库
资源使用率
CPU: 连接数,活跃连接数,排序,聚合,高TPS,高QPS,慢查询,短连接,contextswitch
IO: 索引缺失，索引失效，io低估 (innodb_io_capacity ,innodb_flush_neighbors )
MEM: 内存泄漏,连接,排序溢出,刷脏(innodb_max_dirty_pages_pct),LRU淘汰热数据
注：cpu,io,mem是铁三角关系，互为影响
1.io能力不足可以通过加大内存caching来弥补
2.加大内存对应数据库/OS参数未调整，触发刷脏的量加大又影响IO，尤其是用户态刷脏会阻塞正常访问
3.加大内存掩盖无索引逻辑读等问题又会冲击cpu，毕竟内存操作由cpu驱动
4.加强cpu意味并发执行能力强，同样会进一步影响io,加剧io争用
所有遇到某些瓶颈问题需要具体分析，辩证思考、验证
优化总结
规范操作
1.业务低峰期执行：定时任务，表维护、索引维护
大批量的数据导入、删除、查询操作（分批拆分）
2.大事务拆分小事务,减少锁持有时间，避免影响binlog大小（下游消费binlog超大文件报错等），从库同步延时
3.尽量避免multiple statements(多个SQL语句用英文分号（;)的SQL发送方式
4.主动提交事务，避免持有锁（MDL,行锁）造成阻塞；避免占用redo/undo影响purge clean
5.使用连接池，避免短连接连接风暴
合理使用资源（不低估/不高估）
1.尽量caching数据,发挥内存优势 ：
innodb_buffer_pool_size 能保持热数据在内存 或 (机器内存-系统运行内存-连接私有内存)*75%
join_buffer_size，sort_buffer_size tmp_table_size 连接排序内存操作
2.合理利用IO：
关闭 innodb_flush_neighbors 利用现在SSD普及发挥SSD随机IO优势，无需为把随机IO变为顺序IO批量刷脏
增大innodb_log_file_size避免被动checkpoint刷脏
innodb_max_dirty_pages_pct 尽量不累积脏页，让脏页平缓刷新
iinnodb_io_capacity 设置iops一半， 影响刷新脏页和写入缓冲池的速率
innodb_io_capacity_max
3.减少锁竞争:
innodb_buffer_pool_instances 大于16G内存考虑8,16,降低缓冲池的锁竞争
SQL优化
1.合理建立索引：索引左前缀，区分度高，满足order列 ,满足关联列，保持不冗余（满足：覆盖索引，避免排序，快速查询,节约空间）
2.避免不必要排序，如：order a.a,b.b 这种无法利用索引
3.返回少量行的关联执行计划尽量用到INLJ/BKA,大量行用到hash join
4.对慢SQL中设计不合理的如关联列做not null，字段长度满足需要即可；热表范式设计，非热表反范式设计（减少关联查询）
5.利用索引加速分页查询，如记录last_id，避免limit offset查找几万条记录后
参考：
https://docs.aws.amazon.com/whitepapers/latest/optimizing-mysql-on-ec2-using-amazon-ebs/mysql-benchmark-observations-and-considerations.html
RDS MySQL活跃线程数高的原因及解决方案_云数据库 RDS(RDS)-阿里云帮助中心
云数据库 MySQL 性能相关-故障处理-文档中心-腾讯云
