影响一个系统的运行速度的原因是多方面的，前端、后端、数据库、中间件、服务器、网络等等，今天我们从常常被关注的数据库角度出发。
跟系统的优化方向一样，数据库的优化，也是多方面的，其中涵盖着SQL语句的执行情况，数据库自身的情况等等，数据库种类众多，下面我们以目前常用的Mysql数据库为例。
Mysql性能优化的几个方向
- 连接配置优化（连接池、业务过程中切换数据库等比较耗时）
- 服务器配置
- 客户端优化
- 架构优化
- 缓存设计
- 读写分离
- 分库分表
- 拆分非实时的业务请求
- SQL分析与优化
- 慢查询
- 查看运行中的线程
- 查看服务器运行状态
- 查看存储引擎运行状态
- Explain执行计划
- SQL与索引优化
- 存储引擎与表结构
- 选择正确的存储引擎
- 优化字段
连接配置优化
-
连接常见问题 ： error 1040: Too many connections
通过命令查看数据库最大连接数、最大响应的连接数、SQL进程列表、客户端超时时间等指标情况：
show variables like 'max_connections
show status like 'max_used_connections
show processlist
show variables like 'wait_time'
我们可以从以下几个方面解决连接数不够的问题：
服务器配置优化：
1. 增加可用连接数，修改max_connections，默认值为151个，可根据数据服务器性能配置到1k左右，同时建议业务系统注意控制连接数
客户端优化
1. 客户端能够优化的就是尽量减少和服务端建立连接的次数，目前常见的解决方案就是通过**连接池**复用连接，不过连接池不是越大越好，盲目加大连接池有可能使系统执行效率降低，因为每个连接都需要创建一个单独的线程去处理，连接数越多就需要创建更多的线程，线程数超过CPU个数时，CPU就要通过分配时间片的方式进行上下文切换，频繁切换上下文会导致大量的性能开销，Hikari给出的一个建议大小为：CPU核心数*2+1
2. 通过检查看是不是有多条慢SQL导致连接数无法释放，优化慢SQL
3. 通过检查看是不是有未及时释放的已经不活动的sleep连接，默认客户端超时时间是28800s(8小时)，可以稍微将此值调小，避免产生过多不活动的连接
架构优化
缓存设计
日益复杂的系统我们时常会出现一些慢查询，有的确实因为数据量大或者关联多张表或者计算逻辑复杂，使得长时间占用连接，不过有的业务对数据的时效性要求不是那么高，像我们常见的日报、周报等业务，都给了我们缓冲的时间，我们可以在系统没有那么繁忙的时间段内，进行计算统计等，然后将一些数据存储在缓存系统中，当真实发送报表的时候可以直接从已经处理好的缓存系统中获取数据，减轻数据库的压力，而且还能够提升查询效率
关于增设缓存方面，有以下几点建议：
1. 要考虑缓存的有效期、失效策略等，以控制缓存内存占用
2. 避免缓存击穿：主要针对不入缓存的数据，尤其是查询为null时，一般建议即便是查询为null也短暂缓存一下null标识（如缓存5s）；此外也可考虑缓存个bitmap、布隆过滤器来判断数据是否存在；
3. 避免缓存雪崩：主要担心大批量数据同时超时，一般建议对这批数据设置不同的超时时间（赋予秒级差异）
读写分离
目前我们的大多数系统都以微服务的架构，原来的单体架构等难以支撑高并发的场景，数据库方面都采用集群-读写分离的方案来提升查询效率，集群中一台master机器负责写数据，多个slave节点机器负责数据查询。
主从复制技术使我们可以实现master与各slave阶段的数据保持相对一致（有时会出现主从延迟），master的写操作通过记录在binlog中，各slave节点通过I/O线程读取master节点的binlog文件，完成主数据库写操作的同步
####### 垂直分库
- 将一个包含"订单相关表"、"项目相关表"、"风险相关表"、"用户相关表"等组成的单体数据库进行垂直切分为"订单数据库"、"风险数据库"等集群数据库
- 将一张单表多个字段垂直切分为多个字段，例如将任务计划的ID、Name、Description、Attachments等字段中的冷字段Description、Attachments分成另外一张表
读写分离很好的解决了读压力问题，每次读压力增加，可以通过加从库的方式水平扩展。但是写操作的压力随着业务爆发式的增长没有得到有效的缓解，比如用户提交订单越来越慢。通过监控MySQL数据库，我们发现，数据库写操作越来越慢，一次普通的insert操作，甚至可能会执行1秒以上。
另一方面，业务越来越复杂，多个应用系统使用同一个数据库，其中一个很小的非核心功能出现延迟，常常影响主库上的其它核心业务功能。这时，主库成为了性能瓶颈，我们意识到，必需得再一次做架构升级，将主库做拆分，一方面以提升性能，另一方面减少系统间的相互影响，以提升系统稳定性。这一次，我们将系统按业务进行了垂直拆分。如下图所示，将最初庞大的数据库按业务拆分成不同的业务数据库，每个系统仅访问对应业务的数据库，尽量避免或减少跨库访问。
垂直分库过程，我们也遇到不少挑战，最大的挑战是：不能跨库join，同时需要对现有代码重构。单库时，可以简单的使用join关联表查询；拆库后，拆分后的数据库在不同的实例上，就不能跨库使用join了。
例如，通过商家名查询某个商家的所有订单，在垂直分库前，可以join商家和订单表做查询，也可以直接使用子查询，如下如示：
select * from tb_order where supplier_id in (select id from supplier where name=’商家名称’)；
分库后，则要重构代码，先通过商家名查询商家id，再通过商家id查询订单表，如下所示：
select id from supplier where name=’商家名称’
select * from tb_order where supplier_id in (supplier_ids )
垂直分库过程中的经验教训，使我们制定了SQL最佳实践，其中一条便是程序中禁用或少用join，而应该在程序中组装数据，让SQL更简单。一方面为以后进一步垂直拆分业务做准备，另一方面也避免了MySQL中join的性能低下的问题。
经过垂直分库拆分之后，每个应用程序只访问对应的数据库，一方面将单点数据库拆分成了多个，分摊了主库写压力；另一方面，拆分后的数据库各自独立，实现了业务隔离，不再互相影响。
####### 水平分库（分表）
例如：订单表，按照一定的规则（比如按创建时间、完成时间等）保存到多个库表中
读写分离，通过从库水平扩展，解决了读压力；垂直分库通过按业务拆分主库，缓存了写压力，但系统依然存在以下隐患：
单表数据量越来越大。如订单表，单表记录数很快就过亿，超出MySQL的极限，影响读写性能。
核心业务库的写压力越来越大，已不能再进一次垂直拆分，此时的系统架构中，MySQL 主库不具备水平扩展的能力。
此时，我们需要对MySQL进一步进行水平拆分。
水平分库面临的第一个问题是，按什么逻辑进行拆分。一种方案是按城市拆分，一个城市的所有数据在一个数据库中；另一种方案是按订单ID平均拆分数据。按城市拆分的优点是数据聚合度比较高，做聚合查询比较简单，实现也相对简单，缺点是数据分布不均匀，某些城市的数据量极大，产生热点，而这些热点以后可能还要被迫再次拆分。按订单ID拆分则正相反，优点是数据分布均匀，不会出现一个数据库数据极大或极小的情况，缺点是数据太分散，不利于做聚合查询。比如，按订单ID拆分后，一个商家的订单可能分布在不同的数据库中，查询一个商家的所有订单，可能需要查询多个数据库。针对这种情况，一种解决方案是将需要聚合查询的数据做冗余表，冗余的表不做拆分，同时在业务开发过程中，减少聚合查询。
经过反复思考，我们最后决定按订单ID做水平分库。从架构上，将系统分为三层：
- 应用层：即各类业务应用系统
- 数据访问层：统一的数据访问接口，对上层应用层屏蔽读写分库、分表、缓存等技术细节。
- 数据层：对DB数据进行分片，并可动态的添加shard分片。
水平分库的技术关键点在于数据访问层的设计，数据访问层主要包含三部分：
- 分布式缓存
- 数据库中间件
- 数据异构中间件
而数据库中间件需要包含如下重要的功能：
- ID生成器：生成每张表的主键
- 数据源路由：将每次DB操作路由到不同的分片数据源上
ID生成器
ID生成器是整个水平分库的核心，它决定了如何拆分数据，以及查询存储-检索数据。ID需要跨库全局唯一，否则会引发业务层的冲突。此外，ID必须是数字且升序，这主要是考虑到升序的ID能保证MySQL的性能（若是UUID等随机字符串，在高并发和大数据量情况下，性能极差）。同时，ID生成器必须非常稳定，因为任何故障都会影响所有的数据库操作。
我们系统中ID生成器的设计如下所示。
- 整个ID的二进制长度为64位
- 前36位使用时间戳，以保证ID是升序增加
- 中间13位是分库标识，用来标识当前这个ID对应的记录在哪个数据库中
- 后15位为自增序列，以保证在同一秒内并发时，ID不会重复。每个分片库都有一个自增序列表，生成自增序列时，从自增序列表中获取当前自增序列值，并加1，做为当前ID的后15位
- 下一秒时，后15位的自增序列再次从1开始。
水平分库是一个极具挑战的项目，我们整个团队也在不断的迎接挑战中快速成长。
拆分非实时的业务请求
在某些场景下，我们并不是对所有的事件都需要实时的处理，应尽量避免过多请求到数据据库而导致崩溃，需要考虑与实时业务错开访问：
1. 可以通过消息队列的方式，将消息先存入消息队列，然后再根据服务器负载情况有条不紊的从消息队列中消费请求
2. 拆分离线任务，如统计类，安排在凌晨执行
SQL分析与优化
在我们日常系统优化中，SQL执行的效率也常常是重要的一环，我们常常会去优化系统中的慢查询,优化前，我们得先知道慢查询在哪里，什么原因导致的，进而才能针对性地进行优化。
慢查询状态
因为开启慢查询日志有性能代价，所以默认情况下Mysql是关闭慢查询日志功能的，我们可以通过一些SQL命令更好的了解一些状态：
show_query_log当前慢查询是否开启show_query_log_file表示慢查询的保存位置log_query_time判定慢查询的时间限制，默认一般是10s，通常我们设置为1sshow full processlist查看运行的所有线程show status查看服务器运行状态- Max_used_connections 服务器启动后已同时使用的最大连接数量
explain执行计划（可以查看基础的执行策略、成本估算、是否使用了索引）
SQl优化
通过上述的命令我们可以查看有哪些问题，发现问题后，我们可以进一步进行优化，一些常见的优化方式：
- 创建合适的索引（并不一定创建了就会被使用到，要结合实际情况分析处理）
- 索引与筛选值数据类型不一致会导致索引失效
- Mysql索引常见的为最左匹配原则,也常常是索引设置顺序不一致导致不会命中的一个原因
- 索引也并不是越多越好，太多也会降低性能，尽量是where筛选条件中的“尽量”被索引
- 使用关联查询代替子查询
- 使用小表驱动大表
- 避免大偏移量的limit语句
- 避免大量的where In
- 避免范围查询，尽量使用表的主键或是一二级索引列
- 避免过多的关联表查询，可拆分为多条SQL查询，后进行内存数据合并与拆分处理
- 尽量避免使用“Select *”,大多数情况下我们也是无需查询所有字段的
- 是否类的可选用tinyint，而不是int甚至是bigint
- 固定长度的字符串，建议选择char，而不是varchar（varchar需要额外的空间记录字段所占用的长度）
- 非空字段建议设置Not Null并提供默认值
- 尽量不存储大文件，存储文件的地址即可
- 将大字段冷数据与热数据拆分隔离，避免Select * 导致查询性能下降
- 在已知结果仅会有一条的时候使用limit 1，像PHP Laravel框架提供给我们的first方法而不使用get方法
- SQL并不是写的越长越显得逼格高，反而，大多数情况下为了更好的性能，我们尽可能方便的情况下将其拆分为多条小的查询（以前可能网络带宽和速度较慢，常会听说尽量减少与数据库交互，所以会出现一些复杂的SQL语句）
- order by的字段尽量被索引
- Mysql 5.7已支持JSON数据类型，并且提供了大量函数，不过比较耗时，我们可以针对表JSON字段建立一个虚拟列并建立索引，绑定上面表中的JSON列提高查询效率
-- 针对常见的web系统中会有分页的场景，会存在一些慢SQL（避免大偏移量的limit语句）
select * from own_project limit 99999999,100 -- 常见的分页器或分页语句
select * from own_project where id>=99999999 limit 100 -- 先过滤ID，因为使用到了主键索引，所以相比第一条SQL语句效率要高很多
-- JSON类型优化（创建虚拟列、并简历索引）
ALTER TABLE project_progress ADD COLUMN milestone_type_base bigint GENERATER ALWAYS AS (json_unquote(json_extract(`extra_properties`, '$.milestone_type_base'))) VIRTUAL
ALTER TABLE project_progress ADD INDEX idx_milestone_type_base(milestone_type_base)
存储引擎与表结构
-
优化表结构中的数据类型，使用正确的最小的数据类型，以减少内存空间的浪费
-
我们常常使用的Mysql默认存储引擎InnoDB，还有一些可以适配相应场景的存储引擎：
- InnoDB 支持事务，锁粒度为行级锁，可以支持更高的并发，支持外键约束
- MyISAM 主要用于查询操作多的业务场景，不支持事务
- Memory 主要用于临时表的业务场景，将数据存在内存以实现提高访问速度，旦服务器出现故障，数据都会丢失