性能优化-分库分表

在mysql中，每个数据库最多可创建20亿个表，一个表允许定义1024列。

 

单表能存储的最大上限：

MyISAM存储引擎：64 PB

Innodb存储引擎：64 TB （包括这个表的所有索引等其他相关数据）

从以上数据可以看出，MySQL的单表上限足够大，所以单表大小限制已经不是有MySQL数据库本身来决定，而是由所在主机操作系统上面的文件系统来决定了。

一、业务场景：

        随着业务不断发展，数据表的数据量也会随之不断增长，当我们单张表的数据量过大时，对应系统整体的性能都会下降，这个时候，或许增加索引也不能很明显提高搜索性能，反而付出了更多的写数据的性能。

一般业界认为，单张表超过500万条记录，就应该考虑分表了。

实际情况都不一样，这个值只是参考。

二、解决办法：

        为了解决以上业务场景出现的问题，从整体性能优化考虑，我们一般采用的做法是分库分表策略。

分库分表概念：

分库：将存储在同一个数据库的数据，拆分存储到多个数据库，降低单个数据库的访问压力。

分表：将存储在同一张表的数据，拆分存储到多张表，降低单张表的访问压力。

（一）分表：

        一张表的存储容量是有上限的，具体容量上限受到很多因素影响（比如操作系统、文件系统、表的字段数等）；同时，随着单张表所存储的记录数越来越大，访问该表的性能也在逐步缓慢下降，当达到一定的数据量之后，访问性能会急剧下降，导致影响系统性能和稳定性。

        此时，我们就需要对这张表进行拆分，拆成多张表。

        分表拆分策略：

        1、水平拆分：

        按照某个字段的某种规则，将一张表的数据拆分存储到多张表中。

        特点：

        （1）一张表只保存一部分数据，每张表的记录数可能不一样。

        （2）拆分出的多张表的表结构都一样（字段相同、索引相同）

        水平拆分策略算法有哪些呢？

        范围、枚举、时间、取模、哈希、指定等。

        2、垂直拆分：

        将该表中一些不常用字段或者大文本字段拆分出来，新建附属信息表来存储。

        特点：

        （1）表中存储所有的数据，只是个别字段划分出去，新建的附属表也存储所有的数据记录

        （2）字段发生改变，要评估对业务系统的影响

        以上两种方式可以选择其中一种方式优化，也可以两种方式都做。但是垂直拆分要考虑对原有旧代码的影响面大不大，从而评估优化的难度和耗时。条件允许的情况下，建议以上两种优化方式都做。

（二）分库：

        一个数据库的连接数是有上限的，能同时处理的并发量也是有上限的，当我们的业务系统变得越来越繁杂时，如果都存储在一个数据库中，那么就会因为数据库达到瓶颈而影响业务系统的正常使用。

mysql5.5默认的最大连接数为151，上限为100000。

修改mysql默认的最大连接数
在/etc/my.cnf文件中[mysqld]部分增加max_connections=1000，重启mysql服务。

        此时，我们就需要对数据库进行拆分，将一个库拆分成多个数据库。

        数据库拆分原则：根据系统业务实际情况，将原本存储在一个数据库中的数据分散存储到多个数据库中去，减少单库的负载。

        分库拆分策略：

        水平拆分：把同一个表拆到不同的数据库中；        

        垂直拆分：把不同的表拆到不同的数据库中；

        1、水平拆分：

        将一张表的数据分别存储到多个数据库中，一个库中只保存一部分数据；

水平拆分-优点：

        1. 不存在单库大数据，高并发的性能瓶颈。

        2. 对应用透明，应用端改造较少。     

        3. 提高了系统的稳定性跟负载能力。

水平拆分-缺点：

        1. 拆分规则难以抽象。

        2. 分片事务一致性难以解决。

        3. 数据多次扩展难度跟维护量极大。

        4. 跨库join性能较差。

        2、垂直拆分：

        将数据库中的各个表，依据业务情况将一些表放在一个数据库中，一些表放在另外一个数据库中。

垂直拆分-优点：

        1. 拆分后业务清晰，拆分规则明确。

        2. 系统之间整合或扩展容易。

        3. 数据维护简单。

垂直拆分-缺点：

        1. 部分业务表无法join连表，只能通过接口方式解决，提高了系统复杂度。

三、总结

        现实环境中，分表分库并不是只能选择其一，在复杂的业务系统中，更多情况下是多种策略组合的方式来进行整体性能优化。

        举例：垂直分库+水平分表组合策略下的结果，如下图：