数据分库分表
原创
一.概述
分库分表，顾名思义，既分库亦分表，拆分方式有垂直和水平，通过将单一的数据库，表进行拆分来提高整体数据库的性能
那么导致性能瓶颈的因素有哪些呢？
1.大量的DML操作特别是order，group，count等，会极大消耗cpu资源，导致出现cpu瓶颈
2.数据库缓存不足，导致大量的请求转向磁盘，降低了事务效率，另并发较大也容易出现网络阻塞
出现性能瓶颈时
- 可以优先通过硬件提升，如增大内存，nvme，ssd；提高网络带宽以及提高cpu性能等;
- 可以优化表的结构如索引，视图等。如一张很大的表可以通过创建视图将常用column整合，提高查询速度；
- 进行分库分表
INS：
当一张表每秒产生十万级数据时，如何实时去处理这些数据
1.通过数据库中间件canal订阅binlog，实时采集binlog的变化，通过mq形式将数据转发到服务进行处理
2.数据通过中间件mycat进行水平分片，通过rule设定分片规则，分别下发到不同数据库
二.分类
垂直拆分：
垂直分库，将库表根据实际业务场景拆分到若干个datanode
特点：datanode数据库相同，表结构不同，表数据不同 垂直分表，将表，根据column拆分到若干个datanode 特点：datanode表结构不同，数据不同水平拆分：
水平分库，将一个数据库及其表数据，按照设定的分配rule拆分到若干个datanode
特点：库表结构相同，但数据不同 水平分表，将表根据column拆分，按照设定的分配rule将数据分别存储到不同datanode 特点：表结构相同，但数据不同三.实现方式（中间件）
mycat
开源数据库中间件，依赖于java环境，在前端相当于一个数据库，在后端与datanode通过jdbc，或mysql原生协议通信
通过conf中sehema,server,rule.xml的配置可以实现分库分表，将库表数据存储在后端的datanode中
目录结构：
schema.xml:定义逻辑库表，以及datanode和datahost信息，还有分片规则
server.xml:配置root，user的密码以及访问权限
rule.xml：配置分片算法以及参数调整
四.环境准备
192.168.112.83 中间件 datanode1
192.168.112.68 datanode2
192.168.112.81 datanode3
mycat下载连接：Index of /1.6.7.1/ (mycat.org.cn)
- 中间件：
直接tar解压至/usr/local目录
并安装jdk配置环境变量
JAVA_HOME=/usr/local/jdk-11.0.10
PATH=$JAVA_HOME/bin:$PATH
CLASSPATH=$JAVA_HOME/lib/tools.jar:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib
export JAVA_HOME PATH CLASSPATH
切换至bin，启动mycat
./mycat start
在logs/wrapper.log可以看到启动失败
原因jdk版本过高，可以更换1.8，再重新启动就正常了
- 数据库节点设置
firewall-cmd --zone=public --add-port=8066 --permanent
firewall-cmd --zone=public --add-port=9200 --permanent
systemctl start mysqld
设置最大错误连接数：
cat >> /etc/my.cnf <<EOF
max_connect_errors=1000
mysql -uroot -p #登录数据库,创建远程连接用户并授权以及zzc数据库
create user 'root'@'%'identified with mysql_native_password by '666666';
grant all priviliges on *.* to 'root'@'%';
create database zzc;
五.水平分表
schema.xml配置如下：
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="USERDB" checkSQLschema="false" sqlMaxLimit="100">
<table name="zzc" dataNode="dn1,dn2,dn3" rule="mod-long" />
<table name="tb\_01" dataNode="dn1,dn2,dn3" rule="mod-long" />
</schema>
<dataNode name="dn1" dataHost="localhost1" database="zzc" />
<dataNode name="dn2" dataHost="localhost2" database="zzc" />
<dataNode name="dn3" dataHost="localhost3" database="zzc" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="jdbc:mysql://192.168.112.83:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root"
password="666666">
</writeHost>
</dataHost>
<dataHost name="localhost2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="jdbc:mysql://192.168.112.68:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root"
password="666666">
</writeHost>
</dataHost>
<dataHost name="localhost3" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="jdbc:mysql://192.168.112.82:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root"
password="666666">
</writeHost>
</dataHost>
</mycat:schema>server.xml配置如下
<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License");
- you may not use this file except in compliance with the License. - You
may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0
- - Unless required by applicable law or agreed to in writing, software -
distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT
WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the
License for the specific language governing permissions and - limitations
under the License. -->
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
<system>
<property name="nonePasswordLogin">0</property> <!-- 0为需要密码登陆、1为不需要密码登陆 ,默认为0，设置为1则需要指定默认账户-->
<property name="useHandshakeV10">1</property>
<property name="useSqlStat">0</property> <!-- 1为开启实时统计、0为关闭 -->
<property name="useGlobleTableCheck">0</property> <!-- 1为开启全加班一致性检测、0为关闭 -->
<property name="sequnceHandlerType">2</property>
<!--<property name="sequnceHandlerPattern">(?:(\s\*next\s+value\s+for\s\*MYCATSEQ\_(\w+))(,|\)|\s)\*)+</property>-->
<!--必须带有MYCATSEQ\_或者 mycatseq\_进入序列匹配流程 注意MYCATSEQ\_有空格的情况-->
<property name="sequnceHandlerPattern">(?:(\s\*next\s+value\s+for\s\*MYCATSEQ\_(\w+))(,|\)|\s)\*)+</property>
<property name="subqueryRelationshipCheck">false</property> <!-- 子查询中存在关联查询的情况下,检查关联字段中是否有分片字段 .默认 false -->
<!-- <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议-->
<!-- <property name="fakeMySQLVersion">5.6.20</property>--> <!--设置模拟的MySQL版本号-->
<!-- <property name="processorBufferChunk">40960</property> -->
<!--
<property name="processors">1</property>
<property name="processorExecutor">32</property>
-->
<!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena | type 2 NettyBufferPool -->
<property name="processorBufferPoolType">0</property>
<!--默认是65535 64K 用于sql解析时最大文本长度 -->
<!--<property name="maxStringLiteralLength">65535</property>-->
<!--<property name="sequnceHandlerType">0</property>-->
<!--<property name="backSocketNoDelay">1</property>-->
<!--<property name="frontSocketNoDelay">1</property>-->
<!--<property name="processorExecutor">16</property>-->
<!--
<property name="serverPort">8066</property> <property name="managerPort">9066</property>
<property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property>
<property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> -->
<!--分布式事务开关，0为不过滤分布式事务，1为过滤分布式事务（如果分布式事务内只涉及全局表，则不过滤），2为不过滤分布式事务,但是记录分布式事务日志-->
<property name="handleDistributedTransactions">0</property>
<!--
off heap for merge/order/group/limit 1开启 0关闭
-->
<property name="useOffHeapForMerge">0</property>
<!--
单位为m
-->
<property name="memoryPageSize">64k</property>
<!--
单位为k
-->
<property name="spillsFileBufferSize">1k</property>
<property name="useStreamOutput">0</property>
<!--
单位为m
-->
<property name="systemReserveMemorySize">384m</property>
<!--是否采用zookeeper协调切换 -->
<property name="useZKSwitch">false</property>
<!-- XA Recovery Log日志路径 -->
<!--<property name="XARecoveryLogBaseDir">./</property>-->
<!-- XA Recovery Log日志名称 -->
<!--<property name="XARecoveryLogBaseName">tmlog</property>-->
<!--如果为 true的话 严格遵守隔离级别,不会在仅仅只有select语句的时候在事务中切换连接-->
<property name="strictTxIsolation">false</property>
<property name="useZKSwitch">true</property>
</system>
<!-- 全局SQL防火墙设置 -->
<!--白名单可以使用通配符%或着\*-->
<!--例如<host host="127.0.0.\*" user="root"/>-->
<!--例如<host host="127.0.\*" user="root"/>-->
<!--例如<host host="127.\*" user="root"/>-->
<!--例如<host host="1\*7.\*" user="root"/>-->
<!--这些配置情况下对于127.0.0.1都能以root账户登录-->
<!--
<firewall>
<whitehost>
<host host="1\*7.0.0.\*" user="root"/>
</whitehost>
<blacklist check="false">
</blacklist>
</firewall>
-->
<user name="root" defaultAccount="true">
<property name="password">666666</property>
<property name="schemas">USERDB</property>
<!-- 表级 DML 权限设置 -->
<!--
<privileges check="false">
<schema name="USERDB" dml="1111" >
<table name="zzc" dml="1010"></table>
<table name="tb\_01" dml="1010"></table>
</schema>
</privileges>
-->
</user>
<user name="user">
<property name="password">666666</property>
<property name="schemas">USERDB</property>
<property name="readOnly">true</property>
</user>
</mycat:server>启动mycat
注：启动前先console检测是否正常若显示successfully，start
查看是否启动 :
lsof -i:8066
错误日志:
tail -f /logs/wrapper.log
登录mycat：
mysql -h 192.168.112.83 -P 8066 -uroot -p
USE USERDB;
SHOW TABLES;#可以看到schema下的tb_01
create table tb_01(id int auto_increment primary key, name char(10), age tinyint unsigned, salary int not null );
insert into tb_01(id, name, age, salary) values(1, '莎莉', 20, 5000), (2,'李琴', 22, 6000), (3, '咩咩', 25, 5566), (4, 'lilu', 29, 7888), (5, '肖聪', 38, 8888), (6, '大智', 32, 7777);
六.测试结果：
常见错误：
注意nycatjdk与mycat版本schema间补充：
schema标签：
一个mycat实例可以定义多个逻辑库，逻辑库只存在于mycat中并不真实存在，逻辑库内有可以有若干个逻辑表
，datanode定义table所属的节点，rule设置分片规则，分片规则映射在rule.xml
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。