Mysql系列五:数据库分库分表中间件mycat的安装和mycat配置详解
?一、mycat的安装环境准备:准备一台虚拟机192.168.152.128 1. 下载mycatcd /softwarewget http:dl.mycat.ioRELEASEMycatserverRELEASElinux.tar.gz2. 解压mycattar zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz? 3. 剪切mycat到/usr/local目录下mv softwaremycat usrlocal? 4. 启动mycatusrlocalmycatbinusrlocalmycatbinmycat status 查看启动状态说明:这里有个小插曲,启动一会mycat又会自己停止通过查看日志 vim /usr/local/mycat/logs/wrapper.log 报:Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=64M; support was removed in 8.0 这是因为使用的是jdk8的版本,jdk8以后就没有永久代了,而mycat的wrapper.conf里面配置了jdk8以前的永久代的内存大小,我们只需要注释这个配置就可以了 重新启动mycat: /usr/local/mycat/bin/mycat start就不会出现启动一会又自己停止的问题了 连接mycat: mysql -uroot -p123456 -h192.168.152.128 -P8066可以看到mycat连接成功 5. linux下mycat常用命令usrlocalmycatbinusrlocalmycatbinusrlocalmycatbinusrlocalmycatbinusrlocalmycatbinusrlocalmycatbinmycat status 查看启动状态二、mycat配置详解1. 首先查看mycat的文件目录mycat的主要配置文件都在conf目录下,核心的配置文件是schema.xml、server.xml、rule.xml这3个配置文件,下面我们来对这3个配置文件进行详细介绍 2.?schema.xmlMycat主要管理
<span style="color: #0000ff"></ <span style="color: #800000">mycat:schema<span style="color: #0000ff">>3.?server.xmlMycat这个文件主要和 0 0
<span style="color: #0000ff"></ <span style="color: #800000">mycat:server<span style="color: #0000ff">>属性 |
1234 ZK
定义分片规则策略文件。
<span style="color: #008000"><!--</span><span style="color: #008000">
tableRule标签:定义table分片策略
</span><span style="color: #008000">--></span>
<span style="color: #0000ff"><</span><span style="color: #800000">tableRule </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="rule1"</span><span style="color: #0000ff">></span>
<span style="color: #008000"><!--</span><span style="color: #008000">
rule标签:策略定义标签
</span><span style="color: #008000">--></span>
<span style="color: #0000ff"><</span><span style="color: #800000">rule</span><span style="color: #0000ff">></span>
<span style="color: #008000"><!--</span><span style="color: #008000">
columns标签:对应的分片字段
</span><span style="color: #008000">--></span>
<span style="color: #0000ff"><</span><span style="color: #800000">columns</span><span style="color: #0000ff">></span>id<span style="color: #0000ff"></</span><span style="color: #800000">columns</span><span style="color: #0000ff">></span>
<span style="color: #008000"><!--</span><span style="color: #008000">
algorithm标签:tableRule分片策略对应的function名称
</span><span style="color: #008000">--></span>
<span style="color: #0000ff"><</span><span style="color: #800000">algorithm</span><span style="color: #0000ff">></span>func1<span style="color: #0000ff"></</span><span style="color: #800000">algorithm</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"></</span><span style="color: #800000">rule</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"></</span><span style="color: #800000">tableRule</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">tableRule </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="rule2"</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">rule</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">columns</span><span style="color: #0000ff">></span>user_id<span style="color: #0000ff"></</span><span style="color: #800000">columns</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">algorithm</span><span style="color: #0000ff">></span>func1<span style="color: #0000ff"></</span><span style="color: #800000">algorithm</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"></</span><span style="color: #800000">rule</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"></</span><span style="color: #800000">tableRule</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">tableRule </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="sharding-by-intfile"</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">rule</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">columns</span><span style="color: #0000ff">></span>sharding_id<span style="color: #0000ff"></</span><span style="color: #800000">columns</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">algorithm</span><span style="color: #0000ff">></span>hash-int<span style="color: #0000ff"></</span><span style="color: #800000">algorithm</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"></</span><span style="color: #800000">rule</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"></</span><span style="color: #800000">tableRule</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">tableRule </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="auto-sharding-long"</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">rule</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">columns</span><span style="color: #0000ff">></span>id<span style="color: #0000ff"></</span><span style="color: #800000">columns</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">algorithm</span><span style="color: #0000ff">></span>rang-long<span style="color: #0000ff"></</span><span style="color: #800000">algorithm</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"></</span><span style="color: #800000">rule</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"></</span><span style="color: #800000">tableRule</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">tableRule </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="mod-long"</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">rule</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">columns</span><span style="color: #0000ff">></span>id<span style="color: #0000ff"></</span><span style="color: #800000">columns</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">algorithm</span><span style="color: #0000ff">></span>mod-long<span style="color: #0000ff"></</span><span style="color: #800000">algorithm</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"></</span><span style="color: #800000">rule</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"></</span><span style="color: #800000">tableRule</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">tableRule </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="sharding-by-murmur"</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">rule</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">columns</span><span style="color: #0000ff">></span>id<span style="color: #0000ff"></</span><span style="color: #800000">columns</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">algorithm</span><span style="color: #0000ff">></span>murmur<span style="color: #0000ff"></</span><span style="color: #800000">algorithm</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"></</span><span style="color: #800000">rule</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"></</span><span style="color: #800000">tableRule</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">tableRule </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="crc32slot"</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">rule</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">columns</span><span style="color: #0000ff">></span>id<span style="color: #0000ff"></</span><span style="color: #800000">columns</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">algorithm</span><span style="color: #0000ff">></span>crc32slot<span style="color: #0000ff"></</span><span style="color: #800000">algorithm</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"></</span><span style="color: #800000">rule</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"></</span><span style="color: #800000">tableRule</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">tableRule </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="sharding-by-month"</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">rule</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">columns</span><span style="color: #0000ff">></span>create_time<span style="color: #0000ff"></</span><span style="color: #800000">columns</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">algorithm</span><span style="color: #0000ff">></span>partbymonth<span style="color: #0000ff"></</span><span style="color: #800000">algorithm</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"></</span><span style="color: #800000">rule</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"></</span><span style="color: #800000">tableRule</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">tableRule </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="latest-month-calldate"</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">rule</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">columns</span><span style="color: #0000ff">></span>calldate<span style="color: #0000ff"></</span><span style="color: #800000">columns</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">algorithm</span><span style="color: #0000ff">></span>latestMonth<span style="color: #0000ff"></</span><span style="color: #800000">algorithm</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"></</span><span style="color: #800000">rule</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"></</span><span style="color: #800000">tableRule</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">tableRule </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="auto-sharding-rang-mod"</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">rule</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">columns</span><span style="color: #0000ff">></span>id<span style="color: #0000ff"></</span><span style="color: #800000">columns</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">algorithm</span><span style="color: #0000ff">></span>rang-mod<span style="color: #0000ff"></</span><span style="color: #800000">algorithm</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"></</span><span style="color: #800000">rule</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"></</span><span style="color: #800000">tableRule</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">tableRule </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="jch"</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">rule</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">columns</span><span style="color: #0000ff">></span>id<span style="color: #0000ff"></</span><span style="color: #800000">columns</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">algorithm</span><span style="color: #0000ff">></span>jump-consistent-hash<span style="color: #0000ff"></</span><span style="color: #800000">algorithm</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"></</span><span style="color: #800000">rule</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"></</span><span style="color: #800000">tableRule</span><span style="color: #0000ff">></span>
<span style="color: #008000"><!--</span><span style="color: #008000">
function标签:定义分片函数
</span><span style="color: #008000">--></span>
<span style="color: #0000ff"><</span><span style="color: #800000">function </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="murmur"</span><span style="color: #ff0000">
class</span><span style="color: #0000ff">="io.mycat.route.function.PartitionByMurmurHash"</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">property </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="seed"</span><span style="color: #0000ff">></span>0<span style="color: #0000ff"></</span><span style="color: #800000">property</span><span style="color: #0000ff">></span><span style="color: #008000"><!--</span><span style="color: #008000"> 默认是0 </span><span style="color: #008000">--></span>
<span style="color: #0000ff"><</span><span style="color: #800000">property </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="count"</span><span style="color: #0000ff">></span>2<span style="color: #0000ff"></</span><span style="color: #800000">property</span><span style="color: #0000ff">></span><span style="color: #008000"><!--</span><span style="color: #008000"> 要分片的数据库节点数量,必须指定,否则没法分片 </span><span style="color: #008000">--></span>
<span style="color: #0000ff"><</span><span style="color: #800000">property </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="virtualBucketTimes"</span><span style="color: #0000ff">></span>160<span style="color: #0000ff"></</span><span style="color: #800000">property</span><span style="color: #0000ff">></span><span style="color: #008000"><!--</span><span style="color: #008000"> 一个实际的数据库节点被映射为这么多虚拟节点,默认是160倍,也就是虚拟节点数是物理节点数的160倍 </span><span style="color: #008000">--></span>
<span style="color: #008000"><!--</span><span style="color: #008000"> <property name="weightMapFile">weightMapFile</property> 节点的权重,没有指定权重的节点默认是1。以properties文件的格式填写,以从0开始到count-1的整数值也就是节点索引为key,以节点权重值为值。所有权重值必须是正整数,否则以1代替 </span><span style="color: #008000">--></span>
<span style="color: #008000"><!--</span><span style="color: #008000"> <property name="bucketMapPath">/etc/mycat/bucketMapPath</property>
用于测试时观察各物理节点与虚拟节点的分布情况,如果指定了这个属性,会把虚拟节点的murmur hash值与物理节点的映射按行输出到这个文件,没有默认值,如果不指定,就不会输出任何东西 </span><span style="color: #008000">--></span>
<span style="color: #0000ff"></</span><span style="color: #800000">function</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">function </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="crc32slot"</span><span style="color: #ff0000">
class</span><span style="color: #0000ff">="io.mycat.route.function.PartitionByCRC32PreSlot"</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">property </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="count"</span><span style="color: #0000ff">></span>2<span style="color: #0000ff"></</span><span style="color: #800000">property</span><span style="color: #0000ff">></span><span style="color: #008000"><!--</span><span style="color: #008000"> 要分片的数据库节点数量,必须指定,否则没法分片 </span><span style="color: #008000">--></span>
<span style="color: #0000ff"></</span><span style="color: #800000">function</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">function </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="hash-int"</span><span style="color: #ff0000">
class</span><span style="color: #0000ff">="io.mycat.route.function.PartitionByFileMap"</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">property </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="mapFile"</span><span style="color: #0000ff">></span>partition-hash-int.txt<span style="color: #0000ff"></</span><span style="color: #800000">property</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"></</span><span style="color: #800000">function</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">function </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="rang-long"</span><span style="color: #ff0000">
class</span><span style="color: #0000ff">="io.mycat.route.function.AutoPartitionByLong"</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">property </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="mapFile"</span><span style="color: #0000ff">></span>autopartition-long.txt<span style="color: #0000ff"></</span><span style="color: #800000">property</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"></</span><span style="color: #800000">function</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">function </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="mod-long"</span><span style="color: #ff0000"> class</span><span style="color: #0000ff">="io.mycat.route.function.PartitionByMod"</span><span style="color: #0000ff">></span>
<span style="color: #008000"><!--</span><span style="color: #008000"> how many data nodes </span><span style="color: #008000">--></span>
<span style="color: #0000ff"><</span><span style="color: #800000">property </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="count"</span><span style="color: #0000ff">></span>3<span style="color: #0000ff"></</span><span style="color: #800000">property</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"></</span><span style="color: #800000">function</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">function </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="func1"</span><span style="color: #ff0000"> class</span><span style="color: #0000ff">="io.mycat.route.function.PartitionByLong"</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">property </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="partitionCount"</span><span style="color: #0000ff">></span>8<span style="color: #0000ff"></</span><span style="color: #800000">property</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">property </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="partitionLength"</span><span style="color: #0000ff">></span>128<span style="color: #0000ff"></</span><span style="color: #800000">property</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"></</span><span style="color: #800000">function</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">function </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="latestMonth"</span><span style="color: #ff0000">
class</span><span style="color: #0000ff">="io.mycat.route.function.LatestMonthPartion"</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">property </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="splitOneDay"</span><span style="color: #0000ff">></span>24<span style="color: #0000ff"></</span><span style="color: #800000">property</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"></</span><span style="color: #800000">function</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">function </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="partbymonth"</span><span style="color: #ff0000">
class</span><span style="color: #0000ff">="io.mycat.route.function.PartitionByMonth"</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">property </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="dateFormat"</span><span style="color: #0000ff">></span>yyyy-MM-dd<span style="color: #0000ff"></</span><span style="color: #800000">property</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">property </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="sBeginDate"</span><span style="color: #0000ff">></span>2015-01-01<span style="color: #0000ff"></</span><span style="color: #800000">property</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"></</span><span style="color: #800000">function</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">function </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="rang-mod"</span><span style="color: #ff0000"> class</span><span style="color: #0000ff">="io.mycat.route.function.PartitionByRangeMod"</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">property </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="mapFile"</span><span style="color: #0000ff">></span>partition-range-mod.txt<span style="color: #0000ff"></</span><span style="color: #800000">property</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"></</span><span style="color: #800000">function</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">function </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="jump-consistent-hash"</span><span style="color: #ff0000"> class</span><span style="color: #0000ff">="io.mycat.route.function.PartitionByJumpConsistentHash"</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"><</span><span style="color: #800000">property </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="totalBuckets"</span><span style="color: #0000ff">></span>3<span style="color: #0000ff"></</span><span style="color: #800000">property</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"></</span><span style="color: #800000">function</span><span style="color: #0000ff">></span>
<span style="color: #0000ff"></
<span style="color: #800000">mycat:rule<span style="color: #0000ff">>rule是定义分片规则的
三、mycat工作原理
四、mycat基础概念
1. mycat支持的数据库
2. 逻辑库(Schema)
在实际应用中,业务开发人员并不需要知道中间件的存在,只需要知道数据库的概念,所以数据库中间件可以被看作是一个或者多个数据库集群构成的逻辑库。
3. 逻辑表
在分布式数据库中,读写数据的表就是逻辑表。逻辑表,可以是数据切分后分布在一个或多个分片库中,也可以不做数据切分不分片,只有一个表构成?
4. DataNode、DataHost、Rule
DataNode:数据切分后,一个大表被分到不同的分片数据库上面,每个分片所在的数据库就是分片节点
DataHost:数据切分后,每个分片节点(DataNode)不一定都会独占一台机器,同一机器上面可以有多个分片数据库。一个或多个分片节点(DataNode)所在的机器就是节点主机(DataHost)
Rule:分片规则,选择合适的分片规则很重要,将极大的避免后续处理数据的难度
5. myca分片原则
5.1 数据拆分之前的思考
分片规则虽然能解决大表对数据库系统的压力,但它并不是万能的,也有一些不利之处。因此首要问题是:
1)分不分库
2)分哪些库
3)什么规则分
4)分多少片
5.2 mycat数据切分原则
1)达到一定数量级才分,一般是单表达到800万数据就要考虑数据拆分了。
2)不到800万,但跟大表(超800万的表)有关联查询的表也要拆分,在此称为大表关联表
3)大表关联表如何拆分:小于100万的使用全局表;大于100万小于800万跟大表使用同样的拆分策略;无法跟大表使用相同规则的,可以考虑从java代码上分步骤查询,不用关联查询,或者破例使用全局表。
4)全局表:如品牌表250万,跟大表order关联,又无法跟大表使用同样的拆分策略,也做成了全局表。全局表必须满足的条件:没有太激烈的并发update(如多线程同时update同一条记录);虽有多线程update,但不是操作同一条记录;批量insert没问题。
5)拆分字段是不可修改的
6)拆分字段只能是一个字段,如果想按照两个字段拆分,必须新建一个冗余字段,冗余字段的值使用两个字段的值拼接而成(如大区+年月拼接成:“区域_年月日” 字段)
7)拆分算法的选择和合理性判断:按照选定的算法拆分后每个库中单表不得超过800万记录
8)能不拆分的尽量不拆分。如果某个表不跟其他表关联查询,数据量又少、直接不拆分,使用单库即可
5.3 Mycat分库分表原则
1)能不分就不分,1000万以内的表,不建议分片,通过合适的索引,读写分离等方式,可以很好的解决性能问题
2)分片数量尽量少,分片尽量均匀分布在多个DataHost上,因为一个查询SQL跨分片越多,则总体性能越差,虽然要好于所有数据在一个分片的结果,只在必要的时候进行扩容,增加分片数量。
3)分片规则需要慎重选择,分片规则的选择,需要考虑数据的增长模式,数据的访问模式,分片关联性问题,以及分片扩容问题,最近的分片策略为范围分片,枚举分片,一致性Hash分片,这几种分片都有利于扩容
4)尽量不要在一个事物中的SQL跨越多个分片,分布式事物一直是一个不好处理的问题
5)查询条件尽量优化,尽量避免select * 的方式,大量数据结果集下,会消耗大量的CPU资源和带宽,查询应尽量避免返回大的结果集,并且应尽量为频繁使用的查询语句建立索引
5.4 mycat常用分片规则
1)时间类:按天分片、自然月分片、单月小时分片
2)哈希类:Hash固定分片、日期范围Hash分片、截取数字Hash求模范围分片、截取数字Hash分片、一致性Hash分片
3)取模类:取模分片、取模范围分片、范围求模分片
4)其他类:枚举分片、范围约定分片、应用指定分片、冷热数据分片
(编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!