MySQL-调优&sql优化&排查

调优

MySQL的性能优化实践

MySQL调优的五个维度

• ①客户端与连接层的优化：调整客户端DB连接池的参数和DB连接层的参数。
• ②MySQL结构的优化：合理的设计库表结构，表中字段根据业务选择合适的数据类型、索引。
• ③MySQL参数优化：调整参数的默认值，根据业务将各类参数调整到合适的大小。
• ④整体架构优化：引入中间件减轻数据库压力，优化MySQL架构提高可用性。
• ⑤编码层优化：根据库表结构、索引结构优化业务SQL语句，提高索引命中率。
• 性能收益排序为④ > ② > ⑤ > ③ > ①，不过带来的性能收益越大，也就意味着成本会更高

MySQL连接层优化策略

公式：最大连接数 = (CPU核心数 * 2) + 有效磁盘数（SSD）

• 偶发高峰类业务的连接数配置
  • 公式+同时缩短连接的存活时间，及时释放空闲的数据库连接，以此确保资源的合理分配
• 分库分表情况下的连接数配置
  • 根据每个节点的硬件配置，来规划出合理的连接数

MySQL结构的优化方案

三方面：表结构、字段结构以及索引结构

表结构的优化

• 字段数量一定不要太多
  • 遵循《数据库三范式》
  • 一张表最多最多只能允许设计30个字段左右，否则会导致查询时的性能明显下降
• 反范式：空间换时间的思想
• 主键的选择一定要合适
  • 一张表中必须要有主键，其次主键最好是顺序递增的数值类型，最好为int类型
• 对于实时性要求不高的数据建立中间表
  • 统计数据，通常情况下都会基于多表做联查
  • 每日定期更新中间表的数据，从而达到减小连表查询的开销，同时也能进一步提升查询速度
• 根据业务特性为每张不同的表选择合适的存储引擎
  • InnoDB、MyISAM

字段结构的优化

• 在保证足够使用的范围内，选择最小数据类型，因为它们会占用更少的磁盘、内存和CPU缓存，同时在处理速度也会更快。
• 尽量避免索引字段值为NULL，定义字段时应尽可能使用NOT NULL关键字，因为字段空值过多会影响索引性能。
• 在条件允许的情况下，尽量使用最简单的类型代替复杂的类型，如IP的存储可以使用int而并非varchar，因为简单的数据类型，操作时通常需要的CPU资源更少。

索引结构的优化

• 索引字段的组成尽量选择多个，如果一个表中需要建立多个索引，应适当根据业务去将多个单列索引组合成一个联合索引，这样做一方面可以节省磁盘空间，第二方面还可以充分使用索引覆盖的方式查询数据，能够在一定程度上提升数据库的整体性能。
• 对一个值较长的字段建立索引时，可以选用字段值的前N个字符创建索引，也就是对于值较长的字段尽量建立前缀索引
• 索引类型的选择一定要合理，对于经常做模糊查询的字段，可以建立全文索引来代替普通索引
• 索引结构的选择可以根据业务进行调整，在某些不会做范围查询的字段上建立索引时，可以选用hash结构代替B+Tree结构，因为Hash结构的索引是所有数据结构中最快的，散列度足够的情况下，复杂度仅为O(1)。

MySQL参数优化的选项

• 调整InnoDB缓冲区
• 调整工作线程的缓冲区
  • sort_buffer_size：排序缓冲区大小，影响group by、order by…等排序操作。
  • read_buffer_size：读取缓冲区大小，影响select…查询操作的性能。
  • join_buffer_size：联查缓冲区大小，影响join多表联查的性能。
• 调整临时表空间
• 调整空闲线程的存活时间

架构优化与SQL优化

• 引入缓存中间件解决读压力
  • Redis
• 引入消息中间件解决写压力
  • MQ消息中间件
• MySQL主从读写分离
• MySQL双主双写热备
  • 弊端是指存储容量的上限+木桶效应，因为多主模式中的每个节点都会存储完整的数据，因此当数据增长达到硬件的最大容量时，就无法继续写入数据了，此时只能通过加大磁盘的形式进一步提高存储容量，但硬件也不可能无限制的加下去，而且由于多主是基于主从架构实现的，因为具备木桶效应，要加得所有节点一起加，否则另一个节点无法同步写入数据时，就会造成所有节点无法写入数据
• MySQL分库分表思想

SQL优化

核心思想：减小查询的数据量、提升SQL的索引命中率即可

编写SQL时的注意点

• 查询时尽量不要使用*
  • 分析成本变高。
  • 网络开销变大。
  • 内存占用变高。
  • 维护性变差。
• 连表查询时尽量不要关联太多表
  • 数据量会随表数量呈直线性增长，数据量越大检索效率越低。
  • 当关联的表数量过多时，无法控制好索引的匹配，涉及的表越多，索引不可控风险越大。
• 多表查询时一定要以小驱大
• 不要使用like左模糊和全模糊查询
• 查询时尽量不要对字段做空值判断
  判断null的情况不会走索引
• 不要在条件查询=前对字段做任何运算
• !=、!<>、not in、not like、or…要慎用
• 必要情况下可以强制指定索引
• 避免频繁创建、销毁临时表
• 尽量将大事务拆分为小事务执行
• 从业务设计层面减少大量数据返回的情况
  • 一次性将所有数据全部返回
• 尽量避免深分页的情况出现
  • limit 100000,10：首先会查询出100010条数据，然后丢弃掉前面的10W条数据，将最后的10条数据返回
• SQL务必要写完整，不要使用缩写法
• 基于联合索引查询时请务必确保字段的顺序性
• 客户端的一些操作可以批量化完成
  • 批量插入
• 明确仅返回一条数据的语句可以使用limit 1

MySQL索引优化

explain分析工具

索引优化参考项

• key：如果该值为空，则表示未使用索引查询，此时需要调整SQL或建立索引。
• type：这个字段决定了查询的类型，如果为index、all就需要进行优化。
• rows：这个字段代表着查询时可能会扫描的数据行数，较大时也需要进行优化。
• filtered：这个字段代表着查询时，表中不会扫描的数据行占比，较小时需要进行优化。
• Extra：这个字段代表着查询时的具体情况，在某些情况下需要根据对应信息进行优化。

索引优化实践

• 把SQL的写法进行优化，对于无法应用索引，或导致出现大数据量检索的语句，改为精准匹配的语句。
• 对于合适的字段上建立索引，确保经常作为查询条件的字段，可以命中索引去检索数据。

排查

SQL语句执行出错排查

• ErrorCode：错误码，上述错误信息中的1064。
• SQLState：SQL状态，也就是前面信息中的42000。
• ErrorInfo：错误详情，：后面跟的一大长串描述则是具体的错误详情。

MySQL线上慢查询语句排查

• 查看慢查询日志
• 排查SQL执行缓慢问题
  • 先根据本地慢查询日志文件中的记录，得到具体慢查询SQL执行的相关信息。
  • 查看Look_time的耗时，判断本次执行缓慢，是不是由于并发事务导致的长时间阻塞。
  • 如果不是，则通过explain索引分析工具，先判断索引的使用情况。

MySQL线上机器故障排查

• 客户端连接异常
• MySQL死锁频发
• 服务器CPU100%
• MySQL磁盘100%

参考文章：https://juejin.cn/column/7140138832598401054