当前位置:Gxlcms > 数据库问题 > MyCat:对MySQL数据库进行分库分表

MyCat:对MySQL数据库进行分库分表

时间:2021-07-01 10:21:17 帮助过:21人阅读

2 <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> 3 <mycat:schema xmlns:mycat="http://io.mycat/"> 4 <schema name="mycatdb" checkSQLschema="false" sqlMaxLimit="100"> 5 <!-- auto sharding by id (long) --> 6 <table name="t_person" dataNode="dn1,dn2" rule="mod-long" /> 7 <table name="t_user" primaryKey="id" dataNode="dn1,dn2" rule="sharding-by-murmur" /> <!-- 全局表 --> 8 <!-- table name="province" type="global" dataNode="dn1,dn2,dn3" /> 9 10 <table name="student" dataNode="dn1,dn2" rule="auto-sharding-long-sharejoin" /> 11 <table name="score" dataNode="dn2,dn3" rule="auto-sharding-long-sharejoin" /> 12 <table name="score" dataNode="dn1,dn2" rule="auto-sharding-long-sharejoin" /> 13 <!-- ER分片 --> 14 <table name="customer" dataNode="dn1,dn2,dn3" rule="auto-sharding-long-customer"> 15 <childTable name="orders" joinKey="customer_id" parentKey="id"/> 16 </table --> 17 18 <table name="user" primaryKey="id" dataNode="dn1,dn2" rule="mod-long-test"> 19 <childTable name="cell" joinKey="user_id" parentKey="id"/> 20 <childTable name="note" joinKey="user_id" parentKey="id"/> 21 <childTable name="lit" joinKey="user_id" parentKey="id"/> 22 <childTable name="lit_usr" joinKey="user_id" parentKey="id"/> 23 </table> 24 25 </schema> 26 27 <dataNode name="dn1" dataHost="localhost1" database="db1" /> 28 <dataNode name="dn2" dataHost="localhost1" database="db2" /> 29 <dataNode name="dn3" dataHost="localhost1" database="db3" /> 30 31 <dataHost name="localhost1" maxCon="500" minCon="100" balance="2" 32 writeType="1" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> 33 <heartbeat>select user()</heartbeat> 34 35 <writeHost host="hostM1" url="localhost:3306" user="root" 36 password="" > 37 </writeHost> 38 39 </dataHost> 40 </mycat:schema>
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45

2、rule.xml

<tablerule>标签
columns—— 指定分片列的列名;
algorithm—- 选择分片算法(function标签中的name属性)
<function>标签
定义算法,class–分片算法类名及路径;
<count> 分片数,需要分成多少片;
<mapFile> 范围分片时使用的规则;
<type>默认值是0,表示分片列的值是整数,非0表示是字符串。

  1. <code class="hljs xml has-numbering">[root@dras-test conf]# vim rule.xml <span class="hljs-pi"><?xml version="1.0" encoding="UTF-8"?></span><span class="hljs-doctype"><!DOCTYPE mycat:rule SYSTEM "rule.dtd"></span><span class="hljs-tag"><<span class="hljs-title">mycat:rule</span> <span class="hljs-attribute">xmlns:mycat</span>=<span class="hljs-value">"http://io.mycat/"</span>></span> <span class="hljs-tag"><<span class="hljs-title">tableRule</span> <span class="hljs-attribute">name</span>=<span class="hljs-value">"mod-long"</span>></span> <span class="hljs-tag"><<span class="hljs-title">rule</span>></span> <span class="hljs-tag"><<span class="hljs-title">columns</span>></span>person_id<span class="hljs-tag"></<span class="hljs-title">columns</span>></span> <span class="hljs-tag"><<span class="hljs-title">algorithm</span>></span>mod-long<span class="hljs-tag"></<span class="hljs-title">algorithm</span>></span> <span class="hljs-tag"></<span class="hljs-title">rule</span>></span> <span class="hljs-tag"></<span class="hljs-title">tableRule</span>></span> <span class="hljs-tag"><<span class="hljs-title">tableRule</span> <span class="hljs-attribute">name</span>=<span class="hljs-value">"mod-long-test"</span>></span> <span class="hljs-tag"><<span class="hljs-title">rule</span>></span> <span class="hljs-tag"><<span class="hljs-title">columns</span>></span>id<span class="hljs-tag"></<span class="hljs-title">columns</span>></span> <span class="hljs-tag"><<span class="hljs-title">algorithm</span>></span>mod-long<span class="hljs-tag"></<span class="hljs-title">algorithm</span>></span> <span class="hljs-tag"></<span class="hljs-title">rule</span>></span> <span class="hljs-tag"></<span class="hljs-title">tableRule</span>></span> <span class="hljs-tag"><<span class="hljs-title">tableRule</span> <span class="hljs-attribute">name</span>=<span class="hljs-value">"auto-sharding-long-customer"</span>></span> <span class="hljs-tag"><<span class="hljs-title">rule</span>></span> <span class="hljs-tag"><<span class="hljs-title">columns</span>></span>id<span class="hljs-tag"></<span class="hljs-title">columns</span>></span> <span class="hljs-tag"><<span class="hljs-title">algorithm</span>></span>auto-sharding-long-customer<span class="hljs-tag"></<span class="hljs-title">algorithm</span>></span> <span class="hljs-tag"></<span class="hljs-title">rule</span>></span> <span class="hljs-tag"></<span class="hljs-title">tableRule</span>></span> <span class="hljs-tag"><<span class="hljs-title">tableRule</span> <span class="hljs-attribute">name</span>=<span class="hljs-value">"auto-sharding-long-sharejoin"</span>></span> <span class="hljs-tag"><<span class="hljs-title">rule</span>></span> <span class="hljs-tag"><<span class="hljs-title">columns</span>></span>id<span class="hljs-tag"></<span class="hljs-title">columns</span>></span> <span class="hljs-tag"><<span class="hljs-title">algorithm</span>></span>auto-sharding-long-sharejoin<span class="hljs-tag"></<span class="hljs-title">algorithm</span>></span> <span class="hljs-tag"></<span class="hljs-title">rule</span>></span> <span class="hljs-tag"></<span class="hljs-title">tableRule</span>></span> <span class="hljs-tag"><<span class="hljs-title">tableRule</span> <span class="hljs-attribute">name</span>=<span class="hljs-value">"sharding-by-murmur"</span>></span> <span class="hljs-tag"><<span class="hljs-title">rule</span>></span> <span class="hljs-tag"><<span class="hljs-title">columns</span>></span>uuid<span class="hljs-tag"></<span class="hljs-title">columns</span>></span> <span class="hljs-tag"><<span class="hljs-title">algorithm</span>></span>murmur<span class="hljs-tag"></<span class="hljs-title">algorithm</span>></span> <span class="hljs-tag"></<span class="hljs-title">rule</span>></span> <span class="hljs-tag"></<span class="hljs-title">tableRule</span>></span> <span class="hljs-tag"><<span class="hljs-title">function</span> <span class="hljs-attribute">name</span>=<span class="hljs-value">"mod-long"</span> <span class="hljs-attribute">class</span>=<span class="hljs-value">"io.mycat.route.function.PartitionByMod"</span>></span> <span class="hljs-comment"><!-- how many data nodes --></span> <span class="hljs-tag"><<span class="hljs-title">property</span> <span class="hljs-attribute">name</span>=<span class="hljs-value">"count"</span>></span>2<span class="hljs-tag"></<span class="hljs-title">property</span>></span> <span class="hljs-tag"></<span class="hljs-title">function</span>></span> <span class="hljs-tag"><<span class="hljs-title">function</span> <span class="hljs-attribute">name</span>=<span class="hljs-value">"auto-sharding-long-customer"</span> <span class="hljs-attribute">class</span>=<span class="hljs-value">"io.mycat.route.function.AutoPartitionByLong"</span>></span> <span class="hljs-tag"><<span class="hljs-title">property</span> <span class="hljs-attribute">name</span>=<span class="hljs-value">"mapFile"</span>></span>autopartition-long.txt<span class="hljs-tag"></<span class="hljs-title">property</span>></span> <span class="hljs-tag"></<span class="hljs-title">function</span>></span> <span class="hljs-tag"><<span class="hljs-title">function</span> <span class="hljs-attribute">name</span>=<span class="hljs-value">"auto-sharding-long-sharejoin"</span> <span class="hljs-attribute">class</span>=<span class="hljs-value">"io.mycat.route.function.AutoPartitionByLong"</span>></span> <span class="hljs-tag"><<span class="hljs-title">property</span> <span class="hljs-attribute">name</span>=<span class="hljs-value">"mapFile"</span>></span>autopartition-long-sharejoin.txt<span class="hljs-tag"></<span class="hljs-title">property</span>></span> <span class="hljs-tag"></<span class="hljs-title">function</span>></span> <span class="hljs-tag"><<span class="hljs-title">function</span> <span class="hljs-attribute">name</span>=<span class="hljs-value">"murmur"</span> <span class="hljs-attribute">class</span>=<span class="hljs-value">"io.mycat.route.function.PartitionByMurmurHash"</span>></span> <span class="hljs-tag"><<span class="hljs-title">property</span> <span class="hljs-attribute">name</span>=<span class="hljs-value">"seed"</span>></span>0<span class="hljs-tag"></<span class="hljs-title">property</span>></span><span class="hljs-comment"><!-- 默认是0 --></span> <span class="hljs-tag"><<span class="hljs-title">property</span> <span class="hljs-attribute">name</span>=<span class="hljs-value">"type"</span>></span>1<span class="hljs-tag"></<span class="hljs-title">property</span>></span><span class="hljs-comment"><!-- 默认是0, 表示integer, 非0表示string--></span> <span class="hljs-tag"><<span class="hljs-title">property</span> <span class="hljs-attribute">name</span>=<span class="hljs-value">"count"</span>></span>2<span class="hljs-tag"></<span class="hljs-title">property</span>></span><span class="hljs-comment"><!-- 要分片的数据库节点数量,必须指定,否则没法分片 --></span> <span class="hljs-tag"><<span class="hljs-title">property</span> <span class="hljs-attribute">name</span>=<span class="hljs-value">"virtualBucketTimes"</span>></span>160<span class="hljs-tag"></<span class="hljs-title">property</span>></span><span class="hljs-comment"><!-- 一个实际的数据库节点被映射为这么多虚拟节点,默认是160倍,也就是虚拟节点数是物理节点数的160倍 --></span> <span class="hljs-comment"><!-- <property name="weightMapFile">weightMapFile</property> 节点的权重,没有指定权重的节点默认是1。以properties文件的格式填写,以从0开始到count-1的整数值也就是节点索引为key,以节点权重值为值。>所有权重值必须是正整数,否则以1代替 --></span> <span class="hljs-tag"><<span class="hljs-title">property</span> <span class="hljs-attribute">name</span>=<span class="hljs-value">"bucketMapPath"</span>></span>/usr/local/mycat/logs/bucketMapPath<span class="hljs-tag"></<span class="hljs-title">property</span>></span> <span class="hljs-comment"><!-- 用于测试时观察各物理节点与虚拟节点的分布情况,如果指定了这个属性,会把虚拟节点的murmur hash值与物理节点的映射按行输出到这个文件,没有默认值,如果不指定,就不会输出任何东西 --></span> <span class="hljs-tag"></<span class="hljs-title">function</span>></span><span class="hljs-tag"></<span class="hljs-title">mycat:rule</span>></span></code><ul class="pre-numbering"><li style="color: rgb(153, 153, 153)">1</li><li style="color: rgb(153, 153, 153)">2</li><li style="color: rgb(153, 153, 153)">3</li><li style="color: rgb(153, 153, 153)">4</li><li style="color: rgb(153, 153, 153)">5</li><li style="color: rgb(153, 153, 153)">6</li><li style="color: rgb(153, 153, 153)">7</li><li style="color: rgb(153, 153, 153)">8</li><li style="color: rgb(153, 153, 153)">9</li><li style="color: rgb(153, 153, 153)">10</li><li style="color: rgb(153, 153, 153)">11</li><li style="color: rgb(153, 153, 153)">12</li><li style="color: rgb(153, 153, 153)">13</li><li style="color: rgb(153, 153, 153)">14</li><li style="color: rgb(153, 153, 153)">15</li><li style="color: rgb(153, 153, 153)">16</li><li style="color: rgb(153, 153, 153)">17</li><li style="color: rgb(153, 153, 153)">18</li><li style="color: rgb(153, 153, 153)">19</li><li style="color: rgb(153, 153, 153)">20</li><li style="color: rgb(153, 153, 153)">21</li><li style="color: rgb(153, 153, 153)">22</li><li style="color: rgb(153, 153, 153)">23</li><li style="color: rgb(153, 153, 153)">24</li><li style="color: rgb(153, 153, 153)">25</li><li style="color: rgb(153, 153, 153)">26</li><li style="color: rgb(153, 153, 153)">27</li><li style="color: rgb(153, 153, 153)">28</li><li style="color: rgb(153, 153, 153)">29</li><li style="color: rgb(153, 153, 153)">30</li><li style="color: rgb(153, 153, 153)">31</li><li style="color: rgb(153, 153, 153)">32</li><li style="color: rgb(153, 153, 153)">33</li><li style="color: rgb(153, 153, 153)">34</li><li style="color: rgb(153, 153, 153)">35</li><li style="color: rgb(153, 153, 153)">36</li><li style="color: rgb(153, 153, 153)">37</li><li style="color: rgb(153, 153, 153)">38</li><li style="color: rgb(153, 153, 153)">39</li><li style="color: rgb(153, 153, 153)">40</li><li style="color: rgb(153, 153, 153)">41</li><li style="color: rgb(153, 153, 153)">42</li><li style="color: rgb(153, 153, 153)">43</li><li style="color: rgb(153, 153, 153)">44</li><li style="color: rgb(153, 153, 153)">45</li><li style="color: rgb(153, 153, 153)">46</li><li style="color: rgb(153, 153, 153)">47</li><li style="color: rgb(153, 153, 153)">48</li><li style="color: rgb(153, 153, 153)">49</li><li style="color: rgb(153, 153, 153)">50</li><li style="color: rgb(153, 153, 153)">51</li><li style="color: rgb(153, 153, 153)">52</li><li style="color: rgb(153, 153, 153)">53</li><li style="color: rgb(153, 153, 153)">54</li><li style="color: rgb(153, 153, 153)">55</li><li style="color: rgb(153, 153, 153)">56</li><li style="color: rgb(153, 153, 153)">57</li><li style="color: rgb(153, 153, 153)">58</li><li style="color: rgb(153, 153, 153)">59</li><li style="color: rgb(153, 153, 153)">60</li></ul>

3、说明

对于以上配置文件,选择一个来说明,其他类推。
对t_person表:

在sechma.xml中:

<table name="t_person" dataNode="dn1,dn2" rule="mod-long" />

说明,将其分别存在分片节点dn1和dn2上, 分别对应实际MySQL数据库的db1和db2:

<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataNode name="dn2" dataHost="localhost1" database="db2" />

数据库db1和db2又在分片主机localhost1上,localhost1是连接的实际MySQL服务器,

<writeHost host="hostM1" url="localhost:3306" user="root" password="" >

因此,t_person表会被按照rule=’mod-long’被分别存储在实际MySQL服务器的db1和db2中。

在rule.xml中,

mod-long算法指定其分片里是id,分片算法是mod-long,对id列进行取模。

count=2,说明对2取模,
取模后值为0,存入dn1,取模后值为1,存入dn2.

4、验证

在mycat数据库中创建含id列的t_person表,插入5条数据:

  1. <code class="hljs asciidoc has-numbering">[root@dras-test ~]# mysql -uroot -p123456 -h127.0.0.1 -P8066Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type <span class="hljs-emphasis">‘help;‘</span> or <span class="hljs-emphasis">‘\h‘</span> for help. Type <span class="hljs-emphasis">‘\c‘</span> to clear the current input statement.<span class="hljs-header">mysql> show databases;+----------+</span><span class="hljs-header">| DATABASE |+----------+</span><span class="hljs-header">| mycatdb |+----------+</span>1 row in set (0.00 sec)mysql> use mycatdb;Database changedmysql> mysql> create table t<span class="hljs-emphasis">_person(id int(11) primary key, name varchar(32));Query OK, 0 rows affected (0.04 sec)</span><span class="hljs-header">mysql> desc t_person;+-------+-------------+------+-----+---------+-------+</span><span class="hljs-header">| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+</span>| id | int(11) | NO | PRI | NULL | |<span class="hljs-header">| name | varchar(32) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+</span>2 rows in set (0.01 sec)mysql> mysql> insert into t<span class="hljs-emphasis">_person(id,name) values(1,"Moxiao1"),(2,"Moxiao2"),(3,"Moxiao3"),(4,"Moxiao4"),(5,"Moxiao5");Query OK, 5 rows affected (0.02 sec)Records: 3 Duplicates: 0 Warnings: 0</span>mysql> <span class="hljs-header">mysql> select * from t_person;+----+---------+</span><span class="hljs-header">| id | name |+----+---------+</span>| 2 | Moxiao2 || 4 | Moxiao4 || 1 | Moxiao1 || 3 | Moxiao3 |<span class="hljs-header">| 5 | Moxiao5 |+----+---------+</span>5 rows in set (0.04 sec)</code><ul class="pre-numbering"><li style="color: rgb(153, 153, 153)">1</li><li style="color: rgb(153, 153, 153)">2</li><li style="color: rgb(153, 153, 153)">3</li><li style="color: rgb(153, 153, 153)">4</li><li style="color: rgb(153, 153, 153)">5</li><li style="color: rgb(153, 153, 153)">6</li><li style="color: rgb(153, 153, 153)">7</li><li style="color: rgb(153, 153, 153)">8</li><li style="color: rgb(153, 153, 153)">9</li><li style="color: rgb(153, 153, 153)">10</li><li style="color: rgb(153, 153, 153)">11</li><li style="color: rgb(153, 153, 153)">12</li><li style="color: rgb(153, 153, 153)">13</li><li style="color: rgb(153, 153, 153)">14</li><li style="color: rgb(153, 153, 153)">15</li><li style="color: rgb(153, 153, 153)">16</li><li style="color: rgb(153, 153, 153)">17</li><li style="color: rgb(153, 153, 153)">18</li><li style="color: rgb(153, 153, 153)">19</li><li style="color: rgb(153, 153, 153)">20</li><li style="color: rgb(153, 153, 153)">21</li><li style="color: rgb(153, 153, 153)">22</li><li style="color: rgb(153, 153, 153)">23</li><li style="color: rgb(153, 153, 153)">24</li><li style="color: rgb(153, 153, 153)">25</li><li style="color: rgb(153, 153, 153)">26</li><li style="color: rgb(153, 153, 153)">27</li><li style="color: rgb(153, 153, 153)">28</li><li style="color: rgb(153, 153, 153)">29</li><li style="color: rgb(153, 153, 153)">30</li><li style="color: rgb(153, 153, 153)">31</li><li style="color: rgb(153, 153, 153)">32</li><li style="color: rgb(153, 153, 153)">33</li><li style="color: rgb(153, 153, 153)">34</li><li style="color: rgb(153, 153, 153)">35</li><li style="color: rgb(153, 153, 153)">36</li><li style="color: rgb(153, 153, 153)">37</li><li style="color: rgb(153, 153, 153)">38</li><li style="color: rgb(153, 153, 153)">39</li><li style="color: rgb(153, 153, 153)">40</li><li style="color: rgb(153, 153, 153)">41</li><li style="color: rgb(153, 153, 153)">42</li><li style="color: rgb(153, 153, 153)">43</li><li style="color: rgb(153, 153, 153)">44</li><li style="color: rgb(153, 153, 153)">45</li><li style="color: rgb(153, 153, 153)">46</li><li style="color: rgb(153, 153, 153)">47</li><li style="color: rgb(153, 153, 153)">48</li><li style="color: rgb(153, 153, 153)">49</li><li style="color: rgb(153, 153, 153)">50</li><li style="color: rgb(153, 153, 153)">51</li><li style="color: rgb(153, 153, 153)">52</li><li style="color: rgb(153, 153, 153)">53</li><li style="color: rgb(153, 153, 153)">54</li></ul>

在实际的物理MySQL服务器中,查看:

  1. <code class="hljs asciidoc has-numbering">[root@dras-test conf]# mysql -uroot -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 522063Server version: 5.1.71-log Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type <span class="hljs-emphasis">‘help;‘</span> or <span class="hljs-emphasis">‘\h‘</span> for help. Type <span class="hljs-emphasis">‘\c‘</span> to clear the current input statement.<span class="hljs-header">mysql> show databases;+--------------------+</span><span class="hljs-header">| Database |+--------------------+</span>| information<span class="hljs-emphasis">_schema || db1 || db2 || db3 || estudy || mysql || test || yundras |+--------------------+8 rows in set (0.00 sec)</span><span class="hljs-header">mysql> select * from db1.t_person;+----+---------+</span><span class="hljs-header">| id | name |+----+---------+</span>| 2 | Moxiao2 |<span class="hljs-header">| 4 | Moxiao4 |+----+---------+</span>2 rows in set (0.00 sec)<span class="hljs-header">mysql> select * from db2.t_person;+----+---------+</span><span class="hljs-header">| id | name |+----+---------+</span>| 1 | Moxiao1 || 3 | Moxiao3 |<span class="hljs-header">| 5 | Moxiao5 |+----+---------+</span>3 rows in set (0.00 sec)</code><ul class="pre-numbering"><li style="color: rgb(153, 153, 153)">1</li><li style="color: rgb(153, 153, 153)">2</li><li style="color: rgb(153, 153, 153)">3</li><li style="color: rgb(153, 153, 153)">4</li><li style="color: rgb(153, 153, 153)">5</li><li style="color: rgb(153, 153, 153)">6</li><li style="color: rgb(153, 153, 153)">7</li><li style="color: rgb(153, 153, 153)">8</li><li style="color: rgb(153, 153, 153)">9</li><li style="color: rgb(153, 153, 153)">10</li><li style="color: rgb(153, 153, 153)">11</li><li style="color: rgb(153, 153, 153)">12</li><li style="color: rgb(153, 153, 153)">13</li><li style="color: rgb(153, 153, 153)">14</li><li style="color: rgb(153, 153, 153)">15</li><li style="color: rgb(153, 153, 153)">16</li><li style="color: rgb(153, 153, 153)">17</li><li style="color: rgb(153, 153, 153)">18</li><li style="color: rgb(153, 153, 153)">19</li><li style="color: rgb(153, 153, 153)">20</li><li style="color: rgb(153, 153, 153)">21</li><li style="color: rgb(153, 153, 153)">22</li><li style="color: rgb(153, 153, 153)">23</li><li style="color: rgb(153, 153, 153)">24</li><li style="color: rgb(153, 153, 153)">25</li><li style="color: rgb(153, 153, 153)">26</li><li style="color: rgb(153, 153, 153)">27</li><li style="color: rgb(153, 153, 153)">28</li><li style="color: rgb(153, 153, 153)">29</li><li style="color: rgb(153, 153, 153)">30</li><li style="color: rgb(153, 153, 153)">31</li><li style="color: rgb(153, 153, 153)">32</li><li style="color: rgb(153, 153, 153)">33</li><li style="color: rgb(153, 153, 153)">34</li><li style="color: rgb(153, 153, 153)">35</li><li style="color: rgb(153, 153, 153)">36</li><li style="color: rgb(153, 153, 153)">37</li><li style="color: rgb(153, 153, 153)">38</li><li style="color: rgb(153, 153, 153)">39</li><li style="color: rgb(153, 153, 153)">40</li><li style="color: rgb(153, 153, 153)">41</li><li style="color: rgb(153, 153, 153)">42</li><li style="color: rgb(153, 153, 153)">43</li><li style="color: rgb(153, 153, 153)">44</li><li style="color: rgb(153, 153, 153)">45</li><li style="color: rgb(153, 153, 153)">46</li><li style="color: rgb(153, 153, 153)">47</li></ul>

t_person表成功被mycat自动分散到db1和db2两个库的t_person中。

解决单表数据量大的问题。在以分片列为条件进行查询时,会先查找其所在的分片,缩小查找范围。

  1. <code class="hljs asciidoc has-numbering"><span class="hljs-header">mysql> explain select * from t_person where id=3;+-----------+-----------------------------------------------+</span><span class="hljs-header">| DATA_NODE | SQL |+-----------+-----------------------------------------------+</span><span class="hljs-header">| dn2 | SELECT * FROM t_person WHERE id = 3 LIMIT 100 |+-----------+-----------------------------------------------+</span>1 row in set (0.01 sec)</code><ul class="pre-numbering"><li style="color: rgb(153, 153, 153)">1</li><li style="color: rgb(153, 153, 153)">2</li><li style="color: rgb(153, 153, 153)">3</li><li style="color: rgb(153, 153, 153)">4</li><li style="color: rgb(153, 153, 153)">5</li><li style="color: rgb(153, 153, 153)">6</li><li style="color: rgb(153, 153, 153)">7</li><li style="color: rgb(153, 153, 153)">8</li></ul>

MyCat:对MySQL数据库进行分库分表

标签:gpo   other   tty   styles   long   因此   word   ica   bdr   

人气教程排行