分表分库虽然能解决大表对数据库系统的压力,但它并不是万能的,也有一些不利之处,因此首要问题是,
分不分库,分哪些库,什么规则分,分多少分片。
原则一:能不分就不分,1000 万以内的表,不建议分片,通过合适的索引,读写分离等方式,可以很好的解决性能问题。
原则二:分片数量尽量少,分片尽量均匀分布在多个DataHost 上,因为一个查询SQL 跨分片越多,则总体
性能越差,虽然要好于所有数据在一个分片的结果,只在必要的时候进行扩容,增加分片数量。
原则三:分片规则需要慎重选择,分片规则的选择,需要考虑数据的增长模式,数据的访问模式,分片关联
性问题,以及分片扩容问题,最近的分片策略为范围分片,枚举分片,一致性Hash 分片,这几种分片都有利于扩容。
原则四:尽量不要在一个事务中的SQL 跨越多个分片,分布式事务一直是个不好处理的问题。
原则五:查询条件尽量优化,尽量避免Select * 的方式,大量数据结果集下,会消耗大量带宽和CPU 资源,
查询尽量避免返回大量结果集,并且尽量为频繁使用的查询语句建立索引。
这里特别强调一下分片规则的选择问题,如果某个表的数据有明显的时间特征,比如订单、交易记录等,则
他们通常比较合适用时间范围分片,因为具有时效性的数据,我们往往关注其近期的数据,查询条件中往往带有
时间字段进行过滤,比较好的方案是,当前活跃的数据,采用跨度比较短的时间段进行分片,而历史性的数据,则采用比较长的跨度存储。
总体上来说,分片的选择是取决于最频繁的查询SQL 的条件,因为不带任何Where 语句的查询SQL,会便
利所有的分片,性能相对最差,因此这种SQL 越多,对系统的影响越大,所以我们要尽量避免这种SQL 的产生。
如何准确统计和分析当前系统中最频繁的SQL 呢?有几个简单做法:
- 采用特殊的JDBC 驱动程序,拦截所有业务SQL,并写程序进行分析
- 采用Mycat 的SQL 拦截器机制,写一个插件,拦截所欲SQL,并进行统计分析
- 打开MySQL 日志,分析统计所有SQL
找出每个表最频繁的SQL,分析其查询条件,以及相互的关系,并结合ER 图,就能比较准确的选择每个表的分片策略。
mysql> create database db1;
mysql> create database db2;
mysql> create database db3;
mysql> create database db4;
mysql> create database db5;
分别在每个库里面建表
create table test (
id int(11) not null auto_increment ,
name varchar(10) default '' not null,
primary key(id)
);
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| db3 |
| db4 |
| db5 |
| mysql |
| performance_schema |
| test |
+--------------------+
9 rows in set (0.00 sec)
[root@anedbtest01 conf]# cat schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="testdb" checkSQLschema="true" sqlMaxLimit="100">
<table name="travelrecord" dataNode="dn1" autoIncrement="true" primaryKey="ID" />
<table name="t1" dataNode="dn1" autoIncrement="true" primaryKey="ID" />
<table name="test" dataNode="dn$1-5" autoIncrement="true" rule="auto-sharding-long" primaryKey="ID" />
</schema>
<dataNode name="dn1" dataHost="shard" database="db1" />
<dataNode name="dn2" dataHost="shard" database="db2" />
<dataNode name="dn3" dataHost="shard" database="db3" />
<dataNode name="dn4" dataHost="shard" database="db4" />
<dataNode name="dn5" dataHost="shard" database="db5" />
<dataHost name="shard" maxCon="1000" minCon="10" balance="2" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="127.0.0.1:3306" user="root" password="qwe123">
<readHost host="hostS2" url="127.0.0.1:3307" user="root" password="qwe123" />
</writeHost>
</dataHost>
</mycat:schema>
[root@anedbtest01 conf]# cat rule.xml
<tableRule name="auto-sharding-long">
<rule>
<columns>id</columns>
<algorithm>rang-long</algorithm>
</rule>
</tableRule>
<function name="rang-long"
class="io.mycat.route.function.AutoPartitionByLong">
<property name="mapFile">autopartition-long.txt</property>
</function>
[root@anedbtest01 backup]# cat autopartition-long.txt
# range start-end ,data node index
# K=1000,M=10000.
0-5=0
5-10=1
10-15=2
15-20=3
20-25=4
配置好后,重启reload mycat,插入数据测试
[root@anedbtest01 bin]# /mnt/mysql5641/bin/mysql -uroot -pqwe123 -P8066 -h127.0.0.1
mysql> insert into test(id,name) values(1,'a');
mysql> insert into test(id,name) values(5,'b');
mysql> insert into test(id,name) values(10,'c');
mysql> insert into test(id,name) values(15,'d');
mysql> insert into test(id,name) values(20,'e');
mysql> insert into test(id,name) values(25,'e');
mysql> insert into test(id,name) values(1000,'ssss');
ERROR 1064 (HY000): can't find any valid datanode :TEST -> ID -> 1000
说明没有在分片范围内的,就会报错
mysql> use db1;
mysql> select *From test;
+----+------+
| id | name |
+----+------+
| 1 | a |
+----+------+
1 row in set (0.00 sec)
mysql> use db2
mysql> select *from test;
Empty set (0.00 sec)
mysql> select *from test;
+----+------+
| id | name |
+----+------+
| 10 | c |
+----+------+
1 row in set (0.00 sec)
mysql> use db3
mysql> select *from test;
+----+------+
| id | name |
+----+------+
| 15 | d |
+----+------+
1 row in set (0.00 sec)
mysql> use db4
mysql> select *from test;
+----+------+
| id | name |
+----+------+
| 20 | e |
+----+------+
1 row in set (0.00 sec)
mysql> use db5
mysql> select *From test;
+----+------+
| id | name |
+----+------+
| 25 | e |
+----+------+
1 row in set (0.00 sec)