五:分库分表
MySQL分库分表主要解决的问题是,当数据规模大到一定程度时,查询性能会急剧下降,甚至数据容量突破单机磁盘上限,不得不在多个机器节点上分散存储。然而过多的切分会使系统过于复杂难以维护,因此个人建议是能不切分就不切分,切分也优先按功能领域作垂直切分,仅对某些数据规模过大的表作水平切分。
5.1 基础概念及常见问题
常用架构:
M-S一主多从:主不能停机,从机过多会出现延迟
M-S-S级联复制:使用级联复制减轻主机的IO压力,但从机延迟更大了
M-M-S双主复制:M1负责写 M2负责同步从机,双主还可以搭建高可用
复制流程:[client]commit - [master]binlog- dump thread - [slave] - IO thread - SQL thread
垂直切分:按照功能模块,各个微服务使用自己的数据库。如商品库,订单库...
水平切分:数据库横向扩展,单表数据规模大,垂直切分后仍不能满足高性能存储要求[数据量级突破磁盘上限,比如10亿级查询很慢],根据id取模、字段枚举、日期、一致性hash等方式,将一张表数据拆分到多张表存储。
分库分表常见问题:跨节点join、分布式事务、跨节点分页及排序
分库分表常见方案:
mysql自身支持了主从同步是读写分离的基础,使用HAProxy和keepalived实现服务的高可用。
mysql cluster:官方mysql集群,好像还不是很成熟,没有经历过大规模生产环境的验证
ShardingSphere:由JDBC Proxy Sidecar组成,加入了apache孵化,理念较新使用yml配置简单,发展较快;但是官方文档过时,网上资料少,入门比较难,需要团队有一定的研发实力[新技术嘛可能有bug,我曾花了一整个周末照着官网文档就为了实现一个简单的springboot主从复制项目,搞来搞去最后项目还是没起得来,官网提供的配置有问题]
mycat:经过生产验证的有丰富功能的产品,使用xml配置稍复杂。
5.2 主从复制
5.2.1 mysql安装
docker run --name mysql3306 -p 3306:3306 --privileged=true -ti -e MYSQL_ROOT_PASSWORD=1234 -e MYSQL_DATABASE=enjoy -e MYSQL_USER=user -e MYSQL_PASSWORD=1234 -v /root/docker/mysql/3306/conf:/etc/mysql/conf.d -v /root/docker/mysql/3306/data/:/var/lib/mysql -v /root/docker/mysql/3306/logs/:/var/log/mysql -d mysql:5.7
5.2.2 主从复制配置my.cnf
master配置 server-id=1013306 //我这里是机器号加端口号,可自行配置保证不重复。 log-bin=mysql-bin //开启复制功能 auto_increment_increment=2 //步长 主主时,保证id不重复。一般为主机个数 auto_increment_offset=1 //初始偏移量 m1为1 m2为2 lower_case_table_names=1 //忽略大小写 slave配置 server-id=1023306 log-bin=mysql-bin auto-increment-increment=2 auto-increment-offset=2 lower_case_table_names=1
5.2.3 主从复制权限配置
1.在master mysql添加权限 GRANT REPLICATION SLAVE,FILE,REPLICATION CLIENT ON *.* TO 'user'@'%' IDENTIFIED BY '1234'; FLUSH PRIVILEGES; //刷新权限 show master status; //查看master的二进制日志用于从机绑定主机,如果为空说明log-bin没开启,修改好配置文件后重启服务 show processlist; //查看进程信息 show global variables like '%log%'; //查看二进制日志是否开启 2.在slave中设置master的信息,相当于slave绑定master change master to master_host='192.168.42.101',master_port=3306,master_user='root', master_password='1234',master_log_file='mysql-bin.000001',master_log_pos=154; start slave; //开启slave,启动SQL和IO线程 show slave status; //查看slave的状态 slave_io slave_sql3.查看日志 docker logs -f mysql3306
ps:必须两边数据一致 change...才会生效,可以使用冷备或者热备或者清空数据库
5.2.4 半同步复制
mysql主从复制有半同步复制和异步复制两种模式,当半同步启动时会自动使用半同步模式,半同步超时时会切为异步模式。
1.加载插件lib,所有节点都要配置 mysql窗口安装 主库:install plugin rpl_semi_sync_master soname 'semisync_master.so'; 从库:install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; show plugins; //查看插件安装情况: show global status like "%sync%"; //查询主库状态信息: show global variables like '%log%'; //查询主库参数信息;可以手动改 set global xxx 如下面的配置修改 2.启动半同步: 主库: set global rpl_semi_sync_master_enabled = {0|1}; //1:启用,0:禁止 set global rpl_semi_sync_master_timeout = 10000; //单位为ms 默认10s 半同步等待时间,超时自动切换为异步 从库:set global rpl_semi_sync_slave_enabled = {0|1}; //1:启用,0:禁止 3.重启io_thread stop slave io_thread; start slave io_thread;
5.3 mycat
5.3.1 schema.xml
<mycat:schema xmlns:mycat="http://io.mycat/">
<!--逻辑库,跟数据库的database的概念相同-->
<schema name="enjoyDB" checkSQLschema="true" dataNode="localdn">
<!--
name:逻辑表的名称,名称必须唯一
dataNode:值必须跟dataNode标签中的name对应,如果值过多可以用 dataNode="dn$0-99,cn$100-199"
rule:分片规则配置,定义在rule.xml中,必须与tableRule中的name对应
primaryKey:该逻辑表对应真实表的主键
-->
<table name="t_order" dataNode="localdn" autoIncrement="true" subTables="t_order$1-3" primaryKey="order_id" rule="mod-long">
</table>
</schema>
<dataNode name="localdn" dataHost="localhost1" database="consult" />
<!--
maxCon:指定每个读写实例连接池的最大连接。
minCon:指定每个读写实例连接池的最小连接,初始化连接池的大小
balance:
1. balance="0", 不开启读写分离机制,所有读操作都发送到当前可用的writeHost上。
2. balance="1",全部的readHost与stand by writeHost参与select语句的负载均衡,简单的说,当双主双从模式(M1->S1,M2->S2,并且M1与 M2互为主备),正常情况下,M2,S1,S2都参与select语句的负载均衡。
3. balance="2",所有读操作都随机的在writeHost、readhost上分发。
4. balance="3",所有读请求随机的分发到writeHost对应的readhost执行,writerHost不负担读压力,注意balance=3只在1.4及其以后版本有,1.3没有。
writeType:
负载均衡类型,目前的取值有3种:
1. writeType="0", 所有写操作发送到配置的第一个writeHost,第一个挂了切到还生存的第二个writeHost,重新启动后已切换后的为准,切换记录在配置文件中:dnindex.properties .
2. writeType="1",所有写操作都随机的发送到配置的writeHost。
3. writeType="2",没实现
switchType:
-1 表示不自动切换
1 默认值,自动切换
2 基于MySQL主从同步的状态决定是否切换
心跳语句为 show slave status
3 基于MySQL galary cluster的切换机制(适合集群)(1.4.1)
心跳语句为 show status like ‘wsrep%’
-->
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<!--heartbeat标签
MYSQL可以使用select user()
Oracle可以使用select 1 from dual
-->
<heartbeat>select user()</heartbeat>
<connectionInitSql></connectionInitSql>
<!--
如果writeHost指定的后端数据库宕机,那么这个writeHost绑定的所有readHost都将不可用。另一方面,由于这个writeHost宕机系统会自动的检测到,并切换到备用的writeHost上去
-->
<writeHost host="hostM1" url="jdbc:mysql://localhost:3306" user="root"
password="123456">
</writeHost>
</dataHost>
</mycat:schema>
5.3.2 rule.xml
<tableRule name="mod-long">
<rule>
<columns>order_id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<property name="count">3</property>
<property name="mapFile">autopartition-long.txt</property> //这个适用于配置属性多,自定义配置文件的情况
</function>
//由自定义的类去实现各种分片规则,如mod、jumpConsistentHash
5.4 sharding-jdbc
application.properties
这个因版本不同配置也不同[spring-boot-starter-jdbc 2.2.2.RELEASE],需要结合源码查看
//分表
spring.shardingsphere.datasource.names=ds0,ds1
spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://192.168.67.142:3400/consult?characterEncoding=utf-8
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456
spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://192.168.67.143:3400/consult?characterEncoding=utf-8
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order
spring.shardingsphere.sharding.default-database-strategy.standard.sharding-column=createTime
spring.shardingsphere.sharding.default-database-strategy.standard.precise-algorithm-class-name=cn.enjoy.algorithm.MonthPreciseShardingAlgorithm
spring.shardingsphere.sharding.tables.t_order.key-generator.column=orderId
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
spring.shardingsphere.props.sql.show=true
//读写分离
spring.shardingsphere.datasource.names=master,slave0
spring.shardingsphere.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master.url=jdbc:mysql://192.168.67.142:3400/consult
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=123456
spring.shardingsphere.datasource.slave0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave0.url=jdbc:mysql://192.168.67.142:3401/consult
spring.shardingsphere.datasource.slave0.username=root
spring.shardingsphere.datasource.slave0.password=123456
spring.shardingsphere.masterslave.name=ms
spring.shardingsphere.masterslave.master-data-source-name=master
spring.shardingsphere.masterslave.slave-data-source-names=slave0
spring.shardingsphere.props.sql.show=true
5.5 小结
就如同springMVC是基于servlet规范一样,mycat sharding-jdbc ORM框架以及transaction其实也是基于JDBC规范,主要就是通过代理获取DataSource的Connection,然后代理prepareStatement,完成属性设置后excute执行代理方法。其实就是不断的在jdbc的基础上套壳,使得sql的执行更智能化