一、为什么会分表分库
https://www.cnblogs.com/scode2/p/8718509.html
数据库数据会随着业务的发展而不断增多,因此数据操作,如增删改查的开销也会越来越大。再加上物理服务器的资源有限(CPU、磁盘、内存、IO 等)。最终数据库所能承载的数据量、数据处理能力都将遭遇瓶颈。
一个表总数据量都已经达到了两三千万了!扛不住啊!数据库磁盘容量不断消耗掉!高峰期并发达到惊人的5000~8000!
换句话说需要合理的数据库架构来存放不断增长的数据,这个就是分库分表的设计初衷。目的就是为了缓解数据库的压力,大限度提高数据操作的效率。
二、数据分表
如果单表的数据量过大,例如千万级甚至更多,那么在操作表的时候就会加大系统的开销。
每次查询会消耗数据库大量资源,如果需要多表的联合查询,这种劣势就更加明显了。
比如你单表都几千万数据了,你确定你能抗住么?绝对不行,单表数据量太大,会极大影响你的sql执行的性能,到了后面你的sql可能就跑的很慢了。一般来说,就以我的经验来看,单表到几百万的时候,性能就会相对差一些了,你就得分表了。
以 MySQL 为例,在插入数据的时候,会对表进行加锁,分为表锁定和行锁定。
无论是哪种锁定方式,都意味着前面一条数据在操作表或者行的时候,后面的请求都在排队,当访问量增加的时候,都会影响数据库的效率。
那么既然一定要分表,那么每张表分配多大的数据量比较合适呢?这里建议根据业务场景和实际情况具体分析。
一般来说 MySQL 数据库单表记录最好控制在 500 万条(这是个经验数字)。既然需要将数据从一个表分别存放到多个表中,那么来看看下面两种分表方式吧。
垂直分表
根据业务把一个表中的字段(Field)分到不同的表中。这些被分出去的数据通常根据业务需要,例如分出去一些不是经常使用的字段,一些长度较长的字段。
一般来说,会将较少的访问频率很高的字段放到一个表里去,然后将较多的访问频率很低的字段放到另外一个表里去。因为数据库是有缓存的,你访问频率高的行字段越少,就可以在缓存里缓存更多的行,性能就越好。这个一般在表层面做的较多一些。
一般被拆分的表的字段数比较多。主要是避免查询的时候出现因为数据量大而造成的“跨页”问题。
一般这种拆分在数据库设计之初就会考虑,尽量在系统上线之前考虑调整。已经上线的项目,做这种操作是要慎重考虑的。
水平分表
将一个表中的数据,按照关键字(例如:ID)(或取 Hash 之后)对一个具体的数字取模,得到的余数就是需要存放到的新表的位置。
ID 分别为 01-04 的四条记录,如果分配到 3 个表中,那么对 3 取模得到的余数分别是:
- ID:01 对 3 取模余数为 1 ,存到“表 1”。
- ID:02 对 3 取模余数为 2 ,存到“表 2”。
- ID:03 对 3 取模余数为 3 ,存到“表 3”。
- ID:04 对 3 取模余数为 1 ,存到“表 1”。
首先,我们得在配置文件中定义分片策略,application.yml:
server:
port: 8001
mybatis:
config-location: classpath:mybatis/mybatis-config.xml
mapper-locations: classpath:mybatis/mappers/*.xml
sharding:
jdbc:
datasource:
names: youclk_0,youclk_1
youclk_0:
type: org.apache.commons.dbcp.BasicDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://mysql:3306/youclk_0?useSSL=false
username: root
password: youclk
youclk_1:
type: org.apache.commons.dbcp.BasicDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://mysql:3306/youclk_1?useSSL=false
username: root
password: youclk
config:
sharding:
default-database-strategy:
inline:
sharding-column: number
algorithm-expression: youclk_${number % 2}
tables:
user:
actual-data-nodes: youclk_${0..1}.user
具体每个参数的含义在官方文档有详细解释,其实看名称也能理解个大概了,我定义将 number 为偶数的数据存入 youclk_0,奇数存入 youclk_1。
UserMapper.xml:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.youclk.data.repository.UserRepository">
<resultMap id="BaseResultMap" type="com.youclk.data.entity.User">
<id column="id" property="id" jdbcType="CHAR"/>
<result column="number" property="number" jdbcType="INTEGER"/>
<result column="createTime" property="create_time" jdbcType="DATE"/>
</resultMap>
<sql id="Base_Column_List">
id, number, createTime
</sql>
<insert id="insert">
INSERT INTO user (
id, number
)
VALUES (
uuid(),
#{number,jdbcType=INTEGER}
)
</insert>
</mapper>
User:
@Data
public class User {
private String id;
private Integer number;
private Date createTime;
}
UserRepository:
@Mapper
public interface UserRepository {
void insert(User user);
}
UserService:
@Service
public class UserService {
@Resource
private UserRepository userRepository;
public void insert() {
for (int i = 0; i < 10; i++) {
User user = new User();
user.setNumber(i);
userRepository.insert(user);
}
}
}
三、数据分库
说完了分表,再来谈谈分库。每个物理数据库支持数据都是有限的,每一次的数据库请求都会产生一次数据库链接,当一个库无法支持更多访问的时候,我们会把原来的单个数据库分成多个,帮助分担压力。
一个库一般最多支撑到并发2000,一定要扩容了,而且一个健康的单库并发值你最好保持在每秒1000左右,不要太大。
这里有几类分库的原则,可以根据具体场景进行选择:
- 根据业务不同分库,这种情况都会把主营业务和其他功能分开。例如可以分为订单数据库,核算数据库,评论数据库。
- 根据冷热数据进行分库,用数据访问频率来划分,例如:近一个月的交易数据属于高频数据,2-6 个月的交易数据属于中频数据,大于 6 个月的数据属于低频数据。
- 根据访问数据的地域/时间范围进行分库。
四、 主从与读写分离
通常数据分库之后,每一个数据库包含多个数据表,多个数据库会组成一个 Cluster/Group,提高了数据库的可用性,并且可以把读写做分离。
为什么要做主从?
我们先来探讨以下这几个场景:
- 我们知道每台数据库服务器有他的最大连接数和 IOPS,若有一天他无法再满足我们的业务需求,那相比于在单台服务器上去做性能堆叠,是是否横向去扩展几台 Slave 去分担 Master 的压力更加合理。
- 如果服务对数据库的需求是 IO 密集型的,那可能会经常遇到行锁等待等问题,若要鱼与熊掌兼得,读写分离是否是更好的选择。
- 如果我们的系统需要做很多报表,或者统计和数据分析,这些业务往往相当地耗费资源但又不是很重要,那针对此,我们是否应该开几台 Slave,让他们去小黑屋里慢慢执行,别来影响我处理核心业务的效率。
主从部署
我以 MySQL 为例,一般部署架构为一台 Master 和 n 台 Slave,Master 的主责为写,并将数据同步至 Slave,Slave 主要提供查询功能。
为了测试方便,我直接使用 Docker 来部署,首先创建主从的配置文件:
master.cnf:
[mysqld]
server_id = 1
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
default-storage-engine=INNODB
#Optimize omit
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
log-bin = /var/lib/mysql/binlog
log_bin_trust_function_creators=1
binlog_format = ROW
expire_logs_days = 99
sync_binlog = 0
slow-query-log=1
slow-query-log-file=/var/log/mysql/slow-queries.log
long_query_time = 3
log-queries-not-using-indexes
slave.cnf:
[mysqld]
server_id = 2
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
default-storage-engine=INNODB
#Optimize omit
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
log-bin = /var/lib/mysql/binlog
log_bin_trust_function_creators=1
binlog_format = ROW
expire_logs_days = 99
sync_binlog = 0
relay_log=slave-relay-bin
log-slave-updates=1
slave-skip-errors=all
slow-query-log=1
slow-query-log-file=/var/log/mysql/slow-queries.log
long_query_time = 3
然后进行 compose 编排,加入 warm 集群,docker-compose.yml:
version: '3.5'
services:
mysql-master:
image: mysql
ports:
- 3301:3306
networks:
- proxy
- youclk
volumes:
- /Users/Jermey/Documents/data/db/cluster/master/mysql:/var/lib/mysql
- /Users/Jermey/Documents/data/db/cluster/master/conf.d:/etc/mysql/conf.d
environment:
MYSQL_ROOT_PASSWORD: youclk
mysql-slave:
image: mysql
ports:
- 3302:3306
networks:
- proxy
- youclk
volumes:
- /Users/Jermey/Documents/data/db/cluster/slave/mysql:/var/lib/mysql
- /Users/Jermey/Documents/data/db/cluster/slave/conf.d:/etc/mysql/conf.d
environment:
MYSQL_ROOT_PASSWORD: youclk
networks:
proxy:
external: true
youclk:
external: true
接下来就是配置主从关系:
docker exec -it cluster_mysql-master mysql -p
CREATE USER 'reader'@'%' IDENTIFIED BY 'youclk';
GRANT REPLICATION SLAVE ON *.* TO 'reader'@'%';
show master status\G
docker exec -it cluster_mysql-slave mysql -p
CHANGE MASTER TO \
MASTER_HOST='mysql-master',\
MASTER_PORT=3306,\
MASTER_USER='reader',\
MASTER_PASSWORD='youclk',\
MASTER_LOG_FILE='binlog.000004',\
MASTER_LOG_POS=154;
start slave;
show slave status\G
3.3 读写分离
Master 库主要负责写操作,Slave 库主要负责读操作。在应用访问数据库的时候会通过一个负载均衡代理,通过判断读写操作把请求路由到对应的数据库。
基于 Sharding-JDBC 的读写分离实现非常简单,改一下配置文件,其余几乎是无感知的,application.yml:
server:
port: 8001
mybatis:
config-location: classpath:mybatis/mybatis-config.xml
mapper-locations: classpath:mybatis/mappers/*.xml
sharding:
jdbc:
datasource:
names: ds_master,ds_slave
ds_master:
type: org.apache.commons.dbcp.BasicDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://mysql:3301/youclk_0?useSSL=false
username: root
password: youclk
ds_slave:
type: org.apache.commons.dbcp.BasicDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://mysql:3302/youclk_0?useSSL=false
username: root
password: youclk
config:
masterslave:
load-balance-algorithm-type: round_robin
name: ds_ms
master-data-source-name: ds_master
slave-data-source-names: ds_slave
sharding:
props:
sql.show: true
@Test
public void selectAndInsertTest() {
userService.selectAll();
userService.insert();
}
如果是读操作,也会根据数据库设置的权重或者平均分配请求。另外,还有数据库健康监控机制,定时发送心跳检测数据库的健康状况。
如果 Slave 出现问题,会启动熔断机制停止对其的访问;如果 Master 出现问题,通过选举机制选择新的 Master 代替。
数据库扩容
分库之后的数据库会遇到数据扩容或者数据迁移的情况。这里推荐两种数据库扩容的方案。