时间:2021-07-01 10:21:17 帮助过:3人阅读
分区的二种方式
a,横向分区
什么是横向分区呢?就是横着来分区了,举例来说明一下,假如有100W条数据,分成十份,前10W条数据放到第一个分区,第二个10W条数据放到第二个分区,依此类推。也就是把表分成了十分,根用merge来分表,有点像哦。取出一条数据的时候,这条数据包含了表结构中的所有字段,也就是说横向分区,并没有改变表的结构。
b,纵向分区
什么是纵向分区呢?就是竖来分区了,举例来说明,在设计用户表的时候,开始的时候没有考虑好,而把个人的所有信息都放到了一张表里面去,这样这个表里面就会有比较大的字段,如个人简介,而这些简介呢,也许不会有好多人去看,所以等到有人要看的时候,在去查找,分表的时候,可以把这样的大字段,分开来。
mysql提供的分区属于第一种,横向分区,并且细分成很多种方式:
1.1 MySQL5.1及以上支持分区功能
- <span style="color: #000000;">查看是否支持分区
- </span><span style="color: #008080;">mysql</span>> show variables like "%part%"<span style="color: #000000;">;
- </span>+-------------------+-------+
- | Variable_name | Value |
- +-------------------+-------+
- | have_partitioning | YES |
- +-------------------+-------+<span style="color: #000000;">
- row in set (</span>0.00 sec)
1.2 range 分区
这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区
- <span style="color: #000000;">create table t_range(
- id int(</span>11),<span style="color: #000000;">
- money int(</span>11) unsigned not <span style="color: #0000ff;">null</span>,
- <span style="color: #008080;">date</span><span style="color: #000000;"> datetime
- )partition by </span><span style="color: #008080;">range</span>(year(<span style="color: #008080;">date</span><span style="color: #000000;">))(
- partition p2007 values less than (</span>2008),<span style="color: #000000;">
- partition p2008 values less than (</span>2009),<span style="color: #000000;">
- partition p2009 values less than (</span>2010<span style="color: #000000;">)
- partition p2010 values less than maxvalue </span><span style="color: #008000;">#</span><span style="color: #008000;">MAXVALUE 表示最大的可能的整数值</span>
- <span style="color: #000000;"> );
- RANGE分区在如下场合特别有用:
- </span>1)、当需要删除一个分区上的“旧的”数据时,<span style="color: #000000;">只删除分区即可。如果你使用上面最近的那个例子给出的分区方案,你只需简单地使用”ALTER TABLE employees DROP PARTITION p0;”
- 来删除所有在1991年前就已经停止工作的雇员相对应的所有行。对于有大量行的表,这比运行一个如”DELETE FROM employees WHERE YEAR (separated) </span><= 1990<span style="color: #000000;">;”
- 这样的一个DELETE查询要有效得多。
- </span>2<span style="color: #000000;">)、想要使用一个包含有日期或时间值,或包含有从一些其他级数开始增长的值的列。
- </span>3<span style="color: #000000;">)、经常运行直接依赖于用于分割表的列的查询。
- 例如,当执行一个如”SELECT </span><span style="color: #008080;">COUNT</span>(*) FROM employees WHERE YEAR(separated) = 2000<span style="color: #000000;"> GROUP BY store_id;”这样的查询时,
- MySQL可以很迅速地确定只有分区p2需要扫描,这是因为余下的分区不可能包含有符合该WHERE子句的任何记录</span>
1.3 list分区
这种模式允许系统通过预定义的列表的值来对数据进行分割。
- <span style="color: #000000;">create table t_list(
- a int(</span>11),<span style="color: #000000;">
- b int(</span>11<span style="color: #000000;">)
- )(partition by </span><span style="color: #0000ff;">list</span><span style="color: #000000;"> (b)
- partition p0 values in (</span>1,3,5,7,9),<span style="color: #000000;">
- partition p1 values in (</span>2,4,6,8,0<span style="color: #000000;">)
- );
- LIST分区没有类似如“VALUES LESS THAN MAXVALUE”这样的包含其他值在内的定义。将要匹配的任何值都必须在值列表中找到。</span>
1.4 hash分区
这中模式允许通过对表的一个或多个列的Hash Key进行计算,最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。
- <span style="color: #000000;">CREATE TABLE employees (
- id INT NOT </span><span style="color: #0000ff;">NULL</span>,<span style="color: #000000;">
- fname VARCHAR(</span>30),<span style="color: #000000;">
- lname VARCHAR(</span>30),<span style="color: #000000;">
- hired </span><span style="color: #008080;">DATE</span> NOT <span style="color: #0000ff;">NULL</span> <span style="color: #0000ff;">DEFAULT</span> ‘1970-01-01‘,<span style="color: #000000;">
- separated </span><span style="color: #008080;">DATE</span> NOT <span style="color: #0000ff;">NULL</span> <span style="color: #0000ff;">DEFAULT</span> ‘9999-12-31‘,<span style="color: #000000;">
- job_code INT</span>,<span style="color: #000000;">
- store_id INT
- )
- PARTITION BY HASH(store_id)
- PARTITIONS </span>4;
1.5 key分区
上面Hash模式的一种延伸,这里的Hash Key是MySQL系统产生的。
1.6 子分区
子分区是分区表中每个分区的再次分割,子分区既可以使用HASH希分区,也可以使用KEY分区。这 也被称为复合分区(composite partitioning)。
1,如果一个分区中创建了子分区,其他分区也要有子分区
2,如果创建了了分区,每个分区中的子分区数必有相同
3,同一分区内的子分区,名字不相同,不同分区内的子分区名子可以相同(5.1.50不适用
- <span style="color: #008080;">mysql</span>> CREATE TABLE <span style="color: #0000ff;">IF</span><span style="color: #000000;"> NOT EXISTS `sub_part` (
- </span>-> `news_id` int(11) NOT <span style="color: #0000ff;">NULL</span> COMMENT ‘新闻ID‘,
- -> `content` varchar(1000) NOT <span style="color: #0000ff;">NULL</span> <span style="color: #0000ff;">DEFAULT</span> ‘‘ COMMENT ‘新闻内容‘,
- -> `u_id` int(11) NOT <span style="color: #0000ff;">NULL</span> <span style="color: #0000ff;">DEFAULT</span> 0s COMMENT ‘来源IP‘,
- -> `create_time` <span style="color: #008080;">DATE</span> NOT <span style="color: #0000ff;">NULL</span> <span style="color: #0000ff;">DEFAULT</span> ‘0000-00-00 00:00:00‘ COMMENT ‘时间‘
- -> ) ENGINE=INNODB <span style="color: #0000ff;">DEFAULT</span> CHARSET=<span style="color: #000000;">utf8
- </span>-> PARTITION BY <span style="color: #008080;">RANGE</span><span style="color: #000000;">(YEAR(create_time))
- </span>-><span style="color: #000000;"> SUBPARTITION BY HASH(TO_DAYS(create_time))(
- </span>-> PARTITION p0 VALUES LESS THAN (1990)(SUBPARTITION s0,SUBPARTITION s1,SUBPARTITION s2),
- -> PARTITION p1 VALUES LESS THAN (2000)(SUBPARTITION s3,SUBPARTITION s4,SUBPARTITION good),
- -> PARTITION p2 VALUES LESS THAN MAXVALUE(SUBPARTITION tank0,SUBPARTITION tank1,<span style="color: #000000;">SUBPARTITION tank3)
- </span>-><span style="color: #000000;"> );
- Query OK</span>, 0 rows affected (0.07 sec)
- <span style="color: #000000;">分区的优点
- </span>1<span style="color: #000000;">,分区可以分在多个磁盘,存储更大一点
- </span>2<span style="color: #000000;">,根据查找条件,也就是where后面的条件,查找只查找相应的分区不用全部查找了
- </span>3<span style="color: #000000;">,进行大数据搜索时可以进行并行处理。
- </span>4,跨多个磁盘来分散数据查询,来获得更大的查询吞吐量
1.7 分区管理
a.删除分区
alter table user drop partion p4
b.新增分区
- alter table user add partition(partition p4 values less than MAXVALUE);<span style="color: #008000;">#</span><span style="color: #008000;">新增range分区</span>
- alter table list_part add partition(partition p4 values in(25,26,27)) <span style="color: #008000;">#</span><span style="color: #008000;">新增list分区</span>
- alter table hash_part add partition partitions 4; <span style="color: #008000;">#</span><span style="color: #008000;"> hash重新分区</span>
- alter table key_part add partition partitions 4; <span style="color: #008000;">#</span><span style="color: #008000;">key 重新分区
- //子分区添加新分区,虽然我没有指定子分区,但是系统会给子分区命名的 </span>
- <span style="color: #000000;">alter table sub1_part add partition(partition p3 values less than MAXVALUE);
- </span><span style="color: #008000;">//</span><span style="color: #008000;">range重新分区 </span>
- ALTER TABLE user REORGANIZE PARTITION p0,p1,p2,p3,<span style="color: #000000;">p4 INTO (PARTITION p0 VALUES LESS THAN MAXVALUE);
- </span><span style="color: #008000;">//</span><span style="color: #008000;">list重新分区 </span>
- ALTER TABLE list_part REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES in (1,2,3,4,5<span style="color: #000000;">));
- </span><span style="color: #008000;">#</span><span style="color: #008000;">hash和key分区不能用REORGANIZE,官方网站说的很清楚</span>
参考文献:http://blog.csdn.net/yongchao940/article/details/55266603
http://www.cnblogs.com/mliudong/p/3625522.html
2.分表管理
2.1 MySQL集群
利用mysql cluster ,mysql proxy,mysql replication,drdb等等
- <span style="color: #000000;">有人会问mysql集群,根分表有什么关系吗?虽然它不是实际意义上的分表,但是它启到了分表的作用,做集群的意义是什么呢?为一个数据库减轻负担,说白了就是减少sql排队队列中的sql的数量,
- 举个例子:有10个sql请求,如果放在一个数据库服务器的排队队列中,他要等很长时间,如果把这10个sql请求,分配到5个数据库服务器的排队队列中,一个数据库服务器的队列中只有2个,
- 这样等待时间是不是大大的缩短了呢?这已经很明显了。所以我把它列到了分表的范围以内;集群我们在第三部分详情说明;
- 优点:扩展性好,没有多个分表后的复杂操作(php代码)
- 缺点:单个表的数据量还是没有变,一次操作所花的时间还是那么多,硬件开销大。</span>
2.2 预先估计会出现的大数据并且访问频繁的表,将其分为若干个表
我事先建100个这样的表,message_00,message_01,message_02..........message_98,message_99.然后根据用户的ID来判断这个用户的聊天信息放到哪张表里面,
你可以用hash的方式来获得,可以用求余的方式来获得,方法很多,各人想各人的吧。下面用hash的方法来获得表名:
- <?<span style="color: #000000;">php
- </span><span style="color: #0000ff;">function</span> get_hash_table(<span style="color: #800080;">$table</span>,<span style="color: #800080;">$userid</span><span style="color: #000000;">) {
- </span><span style="color: #800080;">$str</span> = <span style="color: #008080;">crc32</span>(<span style="color: #800080;">$userid</span><span style="color: #000000;">);
- </span><span style="color: #0000ff;">if</span>(<span style="color: #800080;">$str</span><0<span style="color: #000000;">){
- </span><span style="color: #800080;">$hash</span> = "0".<span style="color: #008080;">substr</span>(<span style="color: #008080;">abs</span>(<span style="color: #800080;">$str</span>), 0, 1<span style="color: #000000;">);
- }</span><span style="color: #0000ff;">else</span><span style="color: #000000;">{
- </span><span style="color: #800080;">$hash</span> = <span style="color: #008080;">substr</span>(<span style="color: #800080;">$str</span>, 0, 2<span style="color: #000000;">);
- }
- </span><span style="color: #0000ff;">return</span> <span style="color: #800080;">$table</span>."_".<span style="color: #800080;">$hash</span><span style="color: #000000;">;
- }
- </span><span style="color: #0000ff;">echo</span> get_hash_table(‘message‘,‘user18991‘); <span style="color: #008000;">//</span><span style="color: #008000;">结果为message_10 </span>
- <span style="color: #0000ff;">echo</span> get_hash_table(‘message‘,‘user34523‘); <span style="color: #008000;">//</span><span style="color: #008000;">结果为message_13</span>
- <span style="color: #000000;">
- 优点:避免一张表出现几百万条数据,缩短了一条sql的执行时间
- 缺点:当一种规则确定时,打破这条规则会很麻烦,上面的例子中我用的hash算法是crc32,如果我现在不想用这个算法了,改用md5后,会使同一个用户的消息被存储到不同的表中,
- 这样数据乱套了。扩展性很差。</span>
2.3 利用merge存储引擎来实现分表
merge分表,分为主表和子表,主表类似于一个壳子,逻辑上封装了子表,实际上数据都是存储在子表中的。
- <span style="color: #008080;">mysql</span>> CREATE TABLE <span style="color: #0000ff;">IF</span><span style="color: #000000;"> NOT EXISTS `user1` (
- </span>-> `id` int(11) NOT <span style="color: #0000ff;">NULL</span> AUTO_INCREMENT,
- -> `name` varchar(50) <span style="color: #0000ff;">DEFAULT</span> <span style="color: #0000ff;">NULL</span>,
- -> `sex` int(1) NOT <span style="color: #0000ff;">NULL</span> <span style="color: #0000ff;">DEFAULT</span> ‘0‘,
- -> PRIMARY <span style="color: #008080;">KEY</span><span style="color: #000000;"> (`id`)
- </span>-> ) ENGINE=MyISAM <span style="color: #0000ff;">DEFAULT</span> CHARSET=utf8 AUTO_INCREMENT=1<span style="color: #000000;"> ;
- Query OK</span>, 0 rows affected (0.05<span style="color: #000000;"> sec)
- </span><span style="color: #008080;">mysql</span>> CREATE TABLE <span style="color: #0000ff;">IF</span><span style="color: #000000;"> NOT EXISTS `user2` (
- </span>-> `id` int(11) NOT <span style="color: #0000ff;">NULL</span> AUTO_INCREMENT,
- -> `name` varchar(50) <span style="color: #0000ff;">DEFAULT</span> <span style="color: #0000ff;">NULL</span>,
- -> `sex` int(1) NOT <span style="color: #0000ff;">NULL</span> <span style="color: #0000ff;">DEFAULT</span> ‘0‘,
- -> PRIMARY <span style="color: #008080;">KEY</span><span style="color: #000000;"> (`id`)
- </span>-> ) ENGINE=MyISAM <span style="color: #0000ff;">DEFAULT</span> CHARSET=utf8 AUTO_INCREMENT=1<span style="color: #000000;"> ;
- Query OK</span>, 0 rows affected (0.01<span style="color: #000000;"> sec)
- </span><span style="color: #008080;">mysql</span>> INSERT INTO `user1` (`name`, `sex`) VALUES(‘张映‘, 0<span style="color: #000000;">);
- Query OK</span>, 1 row affected (0.00<span style="color: #000000;"> sec)
- </span><span style="color: #008080;">mysql</span>> INSERT INTO `user2` (`name`, `sex`) VALUES(‘tank‘, 1<span style="color: #000000;">);
- Query OK</span>, 1 row affected (0.00<span style="color: #000000;"> sec)
- </span><span style="color: #008080;">mysql</span>> CREATE TABLE <span style="color: #0000ff;">IF</span><span style="color: #000000;"> NOT EXISTS `alluser` (
- </span>-> `id` int(11) NOT <span style="color: #0000ff;">NULL</span> AUTO_INCREMENT,
- -> `name` varchar(50) <span style="color: #0000ff;">DEFAULT</span> <span style="color: #0000ff;">NULL</span>,
- -> `sex` int(1) NOT <span style="color: #0000ff;">NULL</span> <span style="color: #0000ff;">DEFAULT</span> ‘0‘,
- -><span style="color: #000000;"> INDEX(id)
- </span>-> ) TYPE=MERGE UNION=(user1,user2) INSERT_METHOD=LAST AUTO_INCREMENT=1<span style="color: #000000;"> ;
- Query OK</span>, 0 rows affected, 1 warning (0.00<span style="color: #000000;"> sec)
- 创建主表的时候有个INSERT_METHOD,指明插入方式,取值可以是:</span>0<span style="color: #000000;"> 不允许插入;FIRST 插入到UNION中的第一个表; LAST 插入到UNION中的最后一个表。
- 通过主表查询的时候,相当于将所有子表合在一起查询。这样并不能体现分表的优势,建议还是查询子表。
- 优点:扩展性好,并且程序代码改动的不是很大
- 缺点:这种方法的效果比第二种要差一点,查询性能不高</span>
参考资料:http://blog.51yip.com/mysql/949.html
3.集群
- <span style="color: #008080;">MySQL</span> Proxy就是这么一个中间层代理,简单的说,<span style="color: #008080;">MySQL</span><span style="color: #000000;"> Proxy就是一个连接池,负责将前台应用的连接请求转发给后台的数据库,并且通过使用lua脚本,可以实现复杂的连接控制和过滤,从而实现读写分离和负载
- 平衡。对于应用来说,</span><span style="color: #008080;">MySQL</span><span style="color: #000000;"> Proxy是完全透明的,应用则只需要连接到MySQL Proxy的监听端口即可。当然,这样proxy机器可能成为单点失效,但完全可以使用多个proxy机器做为冗余,在应用服务器的连接池配置
- 中配置到多 个proxy的连接参数即可。</span>
参考资料:http://www.cnblogs.com/phpstudy2015-6/p/6706465.html
http://blog.51yip.com/mysql/399.html
(转)MYSQL 的分区 、分表、集群
标签:应用服务 判断 1年 详情 运行 har list分区 结果 相同