单库调优

一：系统中性能优化的核心思维

• 单个节点的性能表现，逃不过和CPU，内存和磁盘打交道。【最大利用率在80% - 85%最佳】
• 优秀且使用的架构胜过千万次调优
• 读写参半中项目集群和双主热备，写大于读引入消息中间件MQ，读大于写引入缓存redis/或者es，动静分离，读写分离
• 预防大于一切，不要卡点设计，不要出现问题的时候再进行考虑
• 无需追求完美，要理性的权衡利弊。
• 步骤：发现问题瓶颈 -> 排查瓶颈原因 -> 定位瓶颈的位置 -> 解决性能瓶颈

二：MySQL性能优化实践

① 客户端与连接层的优化：调整客户端DB连接池的参数和DB连接层的参数。

② MySQL结构的优化：合理的设计库表结构，表中字段根据业务选择合适的数据类型、索引。

③ MySQL参数优化：调整参数的默认值，根据业务将各类参数调整到合适的大小。

④ 整体架构优化：引入中间件减轻数据库压力，优化MySQL架构提高可用性。

⑤ 编码层优化：根据库表结构、索引结构优化业务SQL语句，提高索引命中率。

纵观现在MySQL中的各类优化手段，基本上都是围绕着上述的五个维度展开

这五个性能优化项中，通常情况下，带来的性能收益排序为④ > ② > ⑤ > ③ > ①，不过带来的性能收益越大，也就意味着成本会更高。

1：连接层的优化

1.1：连接数是越大越好吗？

一个客户端连接是一条线程，那数据库的最大连接数调整到1W，岂不是代表着同时可以支持1W个客户端同时操作啦？

在不考虑硬件的情况下确实如此，但结合硬件来看待，就不行了

数据库连接数越大，也就意味着内部创建出的工作线程会越多，线程越多代表需要的CPU配置得更高

比如现在有300个客户端连接，内部创建了300条工作线程处理，但服务器的CPU仅有32个核心，那当前服务器最多只能支持32条线程同时工作

其他268条线程怎么办呢？为了其他线程能够正常执行，CPU就会以时间片调度的模式工作，不同核心在不同线程间反复切换执行

由于线程数远超核心数，因此会导致线程上下文切换的开销，远大于线程执行的开销。

数据库的连接数该设置成多少合适呢？

对于MySQL连接池的最大连接数，这点无需咱们关心，重点调整的是客户端连接池的连接数，原因如下：

• MySQL实例一般情况下只为单个项目提供服务，你把应用程序那边的连接数做了限制，自然也就限制了服务端的连接数
• 同时，不将MySQL的最大连接数和客户端连接池的最大连接数保持的一致的原因是有可能数据库实例不仅仅只为单个项目提供服务，比如你有时候会通过终端工具远程连接MySQL，如果你将两个连接池的连接数保持一致，就很有可能导致MySQL连接数爆满，最终造成终端无法连上MySQL。

而对于设置大小，对于几乎所有的数据库有一个通用的公式：最大连接数 = (CPU核心数 * 2) + 有效磁盘数(SSD固态硬盘的个数)

注意事项

• C3P0、DBCP、Druid、HikariCP…等连接池的底层本质上是一个线程池，对于线程池而言，想要处理足够高的并发，那应该再配备一个较大的等待队列，也就是当目前池中无可用连接时，其他的用户请求/待执行的SQL语句则会加入队列中阻塞等待
• 实际业务中，还需要考虑SQL的执行时长，比如一类业务的SQL执行只需10ms，而另一类SQL由于业务过为繁琐，每次调用时会产生一个大事务，一次执行下来可能需要5s+，那这两种情况都采用相同的连接池可以吗？可以是可以，但大事务会影响其他正常的SQL，因此想要完美的解决这类问题，最好再单独开一个连接池，为大事务或执行耗时较长的SQL提供服务

1.2：偶发高峰类业务的连接数配置

对于这类业务，常驻线程数不适合太多，因为并发来临时会导致创建大量连接，而并发过后一直保持数据库连接会导致资源被占用

所以对于类似的业务，可以将最大连接数按之前的公式配置，而常驻连接数则可以配成CPU核数+1，同时缩短连接的存活时间

及时释放空闲的数据库连接，以此确保资源的合理分配。

1.3：分库分表情况下的连接数配置

在数据库部署了多节点的情况下，要记得根据每个节点的硬件配置，来规划出合理的连接数。

1.4：小结

连接层的调优，实际上是指调整它的参数，即常驻连接数、最大连接数、空闲连接存活时间以及等待队列的容量

对于最佳连接数的计算，首先要把CPU核数放首位考虑，紧接着是磁盘，最后是网络带宽

• 因为带宽会影响SQL执行时间，综合考虑后才能计算出最合适的连接数大小。

2：结构的优化

结构就是表结构，字段结构和索引结构

2.1：表结构的优化

• 表结构设计时的第一条也是最重要的一条，字段数量一定不要太多
  • 正常情况下应当遵循第三范式(3NF)的原则设计，尽可能的根据业务将表结构拆分的更为精细化
    • 一方面能够确保内存中缓存的数据更多，同时也更便于维护
    • 另一方法精细的SQL表可以使得SQL语句效率更高
  • 一张表最多最多只能允许设计30个字段左右，否则会导致查询时的性能明显下降。
• 允许必要的冗余字段，即必要条件下可以满足反范式
  • 不要无脑冗余
  • 最大好处是能够减少连表查询次数，用空间换时间的思想
• 主键的选择一定要合适。
  • 首先一张表中必须要有主键，其次主键最好是顺序递增的数值类型，最好为int类型
  • 迫不得已的情况下，再考虑使用其他类型的字段作为主键，但也至少需要保持递增性，例如雪花算法，而不是UUID
• 对于实时性要求不高的数据建立中间表
  • 切记要实时性不高
  • 中间表可以一定程度上减小连表查询的开销，同时也能进一步提升查询速度
• 根据业务特性为每张不同的表选择合适的存储引擎
  • 正常的表都选择InnoDB
  • 很少发生变更的表，就可以选择MyISAM引擎，比如字典表、标签表、权限表…
    

2.2：字段结构的优化

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

2.3：索引结构的优化

• 索引字段的组成尽量选择多个

  • 如果一个表中需要建立多个索引，应适当根据业务去将多个单列索引组合成一个联合索引
    • 可以节省磁盘空间
    • 可以充分使用索引覆盖的方式查询数据，能够在一定程度上提升数据库的整体性能。

• 对一个值较长的字段建立索引时，尽量建立前缀索引，而不是通过完整的字段值建立索引

  • 索引字段值越小，单个节点中能存储的索引键会越多，一个节点存下的索引键越多，索引树会越矮，查询性能自然会越高

• 索引类型的选择一定要合理

  • 对于经常做模糊查询的字段，可以建立全文索引来代替普通索引
    • 基于普通索引做like查询会导致索引失效
    • 采用全文索引的方式做模糊查询效率会更高更快，并且全文索引的功能更为强大。

• 索引结构的选择可以根据业务进行调整

  • 在某些不会做范围查询的字段上建立索引时，可以选用hash结构代替B+Tree结构
    • Hash结构的索引是所有数据结构中最快的，散列度足够的情况下，复杂度仅为O(1)。

3：参数的优化方案

这个一般是专业的数据库人员做的，了解一下就好

4：架构和SQL优化

4.1：引入缓存中间件解决读压力

正常的项目业务中，往往读请求的数量远超写请求，如果将所有的读请求都落入数据库处理，这自然会对MySQL造成巨大的访问压力，严重的情况下甚至会由于流量过大，直接将数据库打到宕机

为了解决这系列问题，通常都会在应用程序和数据库之间架设一个缓存，例如最常用的Redis

4.2：引入消息中间件解决写压力

redis只能解决读操作频繁的问题，对于写操作频繁的操作可以使用MQ进行削峰填谷：

4.3：MySQL架构优化

Redis也好，MQ也罢，这都属于在MySQL之前架设中间件，以此来减少真正抵达数据库的请求数量

但打铁还需自身硬，万一经过Redis、MQ后，那些必须要走MySQL执行的请求依旧超出单机MySQL的承载范围时，如若MySQL依旧以单机形式在线上运行，这绝对会导致线上频繁宕机的情况出现。

MySQL架构优化方案一般有三种：主从架构、双主架构、分库分表架构。

主从架构

适合读大于写的情况

主节点的数据变更后，从节点也会基于bin-log日志去同步数据，但这种模式下会存在些许的数据不一致性，因为同步是需要时间的

向主节点修改一条数据后，立马去从节点中查询，这时不一定能够看到最新的数据，因为这时数据也许还未被同步过来。

所以，选择用这种方案来提升性能，必然也会出现些许问题，这也是你必须要接受的，如果项目业务对数据实时性要求特别高，哪就不要考虑主从架构

双主双写架构

适合写大于读的场景

两个节点互为主从，两者之间相互同步数据，同时都具备处理读/写请求的能力，当出现读/写操作时，可以将请求抛给其中任意一个节点处理。

⚠️ 为了兼容两者之间的数据，对于每张表的主键要处理好，如果表的主键是int自增类型的，请一定要手动设置一下自增步长和起始值

可以将步长设置为2，起始值分别为1、2，这样做的好处是啥？能够确保主键的唯一性，设置后两个节点自增ID的序列如下：

• 节点1：[1、3、5、7、9、11、13、15、17、19…]
• 节点2：[2、4、6、8、10、12、14、16、18、20…]

当插入数据的SQL语句发往节点1时，会按照奇数序列自增ID，发往节点2时会以偶数序列自增ID，然后双方相互同步数据，最终两个MySQL节点都会具备完整的数据，因此后续的读请求，无论发往哪个节点都可以读到数据。

既然可以双主，为何为三主，四主?

当然可以，不过没必要这么做，因为当需要上三主、四主…的项目，直接就做分库分表更实在，而且这种多主模式存在一个很大的弊端就是存储容量的上限+木桶效应，多主模式中的每个节点都会存储完整的数据，因此当数据增长达到硬件的最大容量时，就无法继续写入数据了，此时只能通过加大磁盘的形式进一步提高存储容量，但硬件也不可能无限制的加下去，而且由于多主是基于主从架构实现的，因为具备木桶效应，要加得所有节点一起加，否则另一个节点无法同步写入数据时，就会造成所有节点无法写入数据。

分库分表

上述是分库分表的一种情况，这种分库的模式被称为垂直分库，也就是根据业务属性的不同，会创建不同的数据库，然后由不同的业务连接不同的数据库，各自之间数据分开存储，节点之间数据不会同步，以这种方式来部署MySQL，即提高了数据库的整体吞吐量和并发能力，同时也不存在之前的存储容量的木桶问题。

⚠️ 不要认为分库分表是一种很完美的解决方案，实际上当你对项目做了分库分表之后，带来的问题、要解决的问题只会更多，只不过相较于分库分表带来的收益而言，解决问题的成本是值得的，所以才会使用分库分表技术。