亿级数据量系统 db 数据库性能优化方案

一、数据库性能瓶颈主要原因

1、数据库连接

MySQL数据库默认连接为100，我们可以通过配置initialSize、minIdle、maxActive等进行调优，但由于硬件资源的限制，数据库连接不可能无限制的增加，对大型单体应用单实例数据库可能会出现最大连接数不能满足实际需求的情况，这时就会系统业务阻塞。

2、表数据量大（空间存储问题）

普遍观点认为单表数据量超过1000万条时就是出现数据库读取性能瓶颈。从索引角度分析，如果索引未被命中，数据库系统就会全表扫描，数据量越大，扫描全表的时间就会越长；即使索引被命中了，由于B+TREE索引是存放在硬盘上的，数据量越大B+TREE层次越深，IO次数也就越多。

3、硬件资源限制

硬件资源直接影响QPS每秒查询数/TPS每秒事务数。

二、数据性能优化方案

常见的数据性能优化方案：SQL优化、缓存、创建索引、读写分离、分库分表等。

解决大数据量性能优化，真正有效方案是采用分布式数据存储，即上面所述读写分离和分库分表。

1、读写分离

读写分离基于主从复制，采用区别读、写多数据源方式进行数据的存储和加载。数据的存储（增删改）指定写数据源，数据的读取查询指定读数据源。

通过读写分离复制与master相同的数据源（一主多从），多数据源可以部署到不同主机上，从而可以解决数据里连接瓶颈和硬件资源限制问题。

2、分库分表

对数据的库表进行拆分，用分片的方式对数据进行管理。

垂直拆分

数据库垂直拆分将单一库拆分多个领域数据库，使各领域数据库移植性更好，功能划分更清晰。同时也能解决数据库连接、硬件资源限制问题。任何一种方案在解决问题的同时，也会带来新的问题，分库分表也不例外，比如关联查询变得复杂、分布式事务问题等。

水平拆分

水平拆分是将大表按照一定规则拆分成若干个小表，比如将3000万数据量的一张单表user拆分为3个小表user01、user02、user03。主要解决了单表数据量大问题，从而也就解决了存储空间带来的数据库性能瓶颈。

3、优化查询

经过对数据库的了解后，我发现，数据库查询的最该优化的地方还是数据库优化。首先就是

加索引

索引要加给需要查询的列，对于执行的sql，我们要使用EXPLAIN进行查询分析，看查询是否走的索引。

如果查询返回的数据过多，会导致cpu和内存占用过大，用show profile for query去查看查询状态时，sending data过大时，就可能是返回数据过多。sending data的耗时来源于sending和sort的时间之和，去掉排序，时间也能快很多。还要关注一下limit这里，比如limit 2000,60它这里实际会走一个扫描前2000个，如果有条件就比较好了，比如id>2000 limit 1,60这样其实就好的很多。所以对于返回结果比较大的查询，引出了第二个策略

切分条件

切分条件就是查询的时候将条件分的细一些，这样查出的每段数据都很少一些，limit的时候，扫描也少一些。

4、多线程查询

既然做了切分条件，那么多线程查询也是必不可少的。多线程查询再聚合数据，这才能将时间效率提到最高，对与多线程，主要还是java后台做的

参考资料

https://blog.csdn.net/wangpf2011/article/details/100116677 https://blog.csdn.net/luo4105/article/details/78580786