当前位置:Gxlcms > 数据库问题 > (转)MYSQL 的分区 、分表、集群

(转)MYSQL 的分区 、分表、集群

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

/data下面(可以通过my.cnf中的datadir来查看),一张表主要对应着三个文件,一个是frm存放表结构的,一个是myd存放表数据的, 一个是myi存表索引的。如果一张表的数据量太大的话,那么myd,myi就会变的很大,查找数据就会变的很慢,这个时候我们可以利用mysql的分区功能, 在物理上将这一张表对应的三个文件,分割成许多个小块,这样呢,我们查找一条数据时,就不用全部查找了,只要知道这条数据在哪一块,然后在那一块找就行了。 如果表的数据太大,可能一个磁盘放不下,这个时候,我们可以把数据分配到不同的磁盘里面去

分区的二种方式

a,横向分区

什么是横向分区呢?就是横着来分区了,举例来说明一下,假如有100W条数据,分成十份,前10W条数据放到第一个分区,第二个10W条数据放到第二个分区,依此类推。也就是把表分成了十分,根用merge来分表,有点像哦。取出一条数据的时候,这条数据包含了表结构中的所有字段,也就是说横向分区,并没有改变表的结构。

b,纵向分区

什么是纵向分区呢?就是竖来分区了,举例来说明,在设计用户表的时候,开始的时候没有考虑好,而把个人的所有信息都放到了一张表里面去,这样这个表里面就会有比较大的字段,如个人简介,而这些简介呢,也许不会有好多人去看,所以等到有人要看的时候,在去查找,分表的时候,可以把这样的大字段,分开来。

mysql提供的分区属于第一种,横向分区,并且细分成很多种方式:

1.1 MySQL5.1及以上支持分区功能

  1. <span style="color: #000000;">查看是否支持分区
  2. </span><span style="color: #008080;">mysql</span>> show variables like "%part%"<span style="color: #000000;">;
  3. </span>+-------------------+-------+
  4. | Variable_name | Value |
  5. +-------------------+-------+
  6. | have_partitioning | YES |
  7. +-------------------+-------+<span style="color: #000000;">
  8. row in set (</span>0.00 sec)

1.2 range 分区

      这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区

  1. <span style="color: #000000;">create table t_range(
  2.    id int(</span>11),<span style="color: #000000;">
  3.    money int(</span>11) unsigned not <span style="color: #0000ff;">null</span>,
  4.    <span style="color: #008080;">date</span><span style="color: #000000;"> datetime
  5.   )partition by </span><span style="color: #008080;">range</span>(year(<span style="color: #008080;">date</span><span style="color: #000000;">))(
  6.   partition p2007 values less than (</span>2008),<span style="color: #000000;">
  7.   partition p2008 values less than (</span>2009),<span style="color: #000000;">
  8.   partition p2009 values less than (</span>2010<span style="color: #000000;">)
  9.   partition p2010 values less than maxvalue </span><span style="color: #008000;">#</span><span style="color: #008000;">MAXVALUE 表示最大的可能的整数值</span>
  10. <span style="color: #000000;">  );
  11. RANGE分区在如下场合特别有用:
  12. </span>1)、当需要删除一个分区上的“旧的”数据时,<span style="color: #000000;">只删除分区即可。如果你使用上面最近的那个例子给出的分区方案,你只需简单地使用”ALTER TABLE employees DROP PARTITION p0;”
  13.   来删除所有在1991年前就已经停止工作的雇员相对应的所有行。对于有大量行的表,这比运行一个如”DELETE FROM employees WHERE YEAR (separated) </span><= 1990<span style="color: #000000;">;”
  14.   这样的一个DELETE查询要有效得多。
  15. </span>2<span style="color: #000000;">)、想要使用一个包含有日期或时间值,或包含有从一些其他级数开始增长的值的列。
  16. </span>3<span style="color: #000000;">)、经常运行直接依赖于用于分割表的列的查询。
  17.   例如,当执行一个如”SELECT </span><span style="color: #008080;">COUNT</span>(*) FROM employees WHERE YEAR(separated) = 2000<span style="color: #000000;"> GROUP BY store_id;”这样的查询时,
  18.   MySQL可以很迅速地确定只有分区p2需要扫描,这是因为余下的分区不可能包含有符合该WHERE子句的任何记录</span>

1.3 list分区

      这种模式允许系统通过预定义的列表的值来对数据进行分割。

  1. <span style="color: #000000;">create table t_list(
  2.   a int(</span>11),<span style="color: #000000;">
  3.   b int(</span>11<span style="color: #000000;">)
  4.   )(partition by </span><span style="color: #0000ff;">list</span><span style="color: #000000;"> (b)
  5.   partition p0 values in (</span>1,3,5,7,9),<span style="color: #000000;">
  6.   partition p1 values in (</span>2,4,6,8,0<span style="color: #000000;">)
  7. );
  8. LIST分区没有类似如“VALUES LESS THAN MAXVALUE”这样的包含其他值在内的定义。将要匹配的任何值都必须在值列表中找到。</span>

1.4 hash分区

   这中模式允许通过对表的一个或多个列的Hash Key进行计算,最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。

 

  1. <span style="color: #000000;">CREATE TABLE employees (
  2. id INT NOT </span><span style="color: #0000ff;">NULL</span>,<span style="color: #000000;">
  3. fname VARCHAR(</span>30),<span style="color: #000000;">
  4. lname VARCHAR(</span>30),<span style="color: #000000;">
  5. 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;">
  6. 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;">
  7. job_code INT</span>,<span style="color: #000000;">
  8. store_id INT
  9. )
  10. PARTITION BY HASH(store_id)
  11. PARTITIONS </span>4;

 

1.5 key分区

      上面Hash模式的一种延伸,这里的Hash Key是MySQL系统产生的。

1.6 子分区

子分区是分区表中每个分区的再次分割,子分区既可以使用HASH希分区,也可以使用KEY分区。这 也被称为复合分区(composite partitioning)。

1,如果一个分区中创建了子分区,其他分区也要有子分区

2,如果创建了了分区,每个分区中的子分区数必有相同

3,同一分区内的子分区,名字不相同,不同分区内的子分区名子可以相同(5.1.50不适用

 

  1. <span style="color: #008080;">mysql</span>> CREATE TABLE <span style="color: #0000ff;">IF</span><span style="color: #000000;"> NOT EXISTS `sub_part` (
  2. </span>-> `news_id` int(11) NOT <span style="color: #0000ff;">NULL</span> COMMENT ‘新闻ID‘,
  3. -> `content` varchar(1000) NOT <span style="color: #0000ff;">NULL</span> <span style="color: #0000ff;">DEFAULT</span> ‘‘ COMMENT ‘新闻内容‘,
  4. -> `u_id` int(11) NOT <span style="color: #0000ff;">NULL</span> <span style="color: #0000ff;">DEFAULT</span> 0s COMMENT ‘来源IP‘,
  5. -> `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 ‘时间‘
  6. -> ) ENGINE=INNODB <span style="color: #0000ff;">DEFAULT</span> CHARSET=<span style="color: #000000;">utf8
  7. </span>-> PARTITION BY <span style="color: #008080;">RANGE</span><span style="color: #000000;">(YEAR(create_time))
  8. </span>-><span style="color: #000000;"> SUBPARTITION BY HASH(TO_DAYS(create_time))(
  9. </span>-> PARTITION p0 VALUES LESS THAN (1990)(SUBPARTITION s0,SUBPARTITION s1,SUBPARTITION s2),
  10. -> PARTITION p1 VALUES LESS THAN (2000)(SUBPARTITION s3,SUBPARTITION s4,SUBPARTITION good),
  11. -> PARTITION p2 VALUES LESS THAN MAXVALUE(SUBPARTITION tank0,SUBPARTITION tank1,<span style="color: #000000;">SUBPARTITION tank3)
  12. </span>-><span style="color: #000000;"> );
  13. Query OK</span>, 0 rows affected (0.07 sec)
  1. <span style="color: #000000;">分区的优点
  2. </span>1<span style="color: #000000;">,分区可以分在多个磁盘,存储更大一点
  3. </span>2<span style="color: #000000;">,根据查找条件,也就是where后面的条件,查找只查找相应的分区不用全部查找了
  4. </span>3<span style="color: #000000;">,进行大数据搜索时可以进行并行处理。
  5. </span>4,跨多个磁盘来分散数据查询,来获得更大的查询吞吐量

1.7 分区管理

a.删除分区

  alter table user drop partion p4

b.新增分区

 

  1. alter table user add partition(partition p4 values less than MAXVALUE);<span style="color: #008000;">#</span><span style="color: #008000;">新增range分区</span>
  2. alter table list_part add partition(partition p4 values in(25,26,27)) <span style="color: #008000;">#</span><span style="color: #008000;">新增list分区</span>
  3. alter table hash_part add partition partitions 4; <span style="color: #008000;">#</span><span style="color: #008000;"> hash重新分区</span>
  4. alter table key_part add partition partitions 4; <span style="color: #008000;">#</span><span style="color: #008000;">key 重新分区
  5. //子分区添加新分区,虽然我没有指定子分区,但是系统会给子分区命名的 </span>
  6. <span style="color: #000000;">alter table sub1_part add partition(partition p3 values less than MAXVALUE);
  7. </span><span style="color: #008000;">//</span><span style="color: #008000;">range重新分区 </span>
  8. ALTER TABLE user REORGANIZE PARTITION p0,p1,p2,p3,<span style="color: #000000;">p4 INTO (PARTITION p0 VALUES LESS THAN MAXVALUE);
  9. </span><span style="color: #008000;">//</span><span style="color: #008000;">list重新分区 </span>
  10. 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;">));
  11. </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等等

 

  1. <span style="color: #000000;">有人会问mysql集群,根分表有什么关系吗?虽然它不是实际意义上的分表,但是它启到了分表的作用,做集群的意义是什么呢?为一个数据库减轻负担,说白了就是减少sql排队队列中的sql的数量,
  2. 举个例子:有10个sql请求,如果放在一个数据库服务器的排队队列中,他要等很长时间,如果把这10个sql请求,分配到5个数据库服务器的排队队列中,一个数据库服务器的队列中只有2个,
  3. 这样等待时间是不是大大的缩短了呢?这已经很明显了。所以我把它列到了分表的范围以内;集群我们在第三部分详情说明;
  4. 优点:扩展性好,没有多个分表后的复杂操作(php代码)
  5. 缺点:单个表的数据量还是没有变,一次操作所花的时间还是那么多,硬件开销大。</span>

2.2 预先估计会出现的大数据并且访问频繁的表,将其分为若干个表

我事先建100个这样的表,message_00,message_01,message_02..........message_98,message_99.然后根据用户的ID来判断这个用户的聊天信息放到哪张表里面,

你可以用hash的方式来获得,可以用求余的方式来获得,方法很多,各人想各人的吧。下面用hash的方法来获得表名:

 

  1. <?<span style="color: #000000;">php
  2. </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;">) {
  3. </span><span style="color: #800080;">$str</span> = <span style="color: #008080;">crc32</span>(<span style="color: #800080;">$userid</span><span style="color: #000000;">);
  4. </span><span style="color: #0000ff;">if</span>(<span style="color: #800080;">$str</span><0<span style="color: #000000;">){
  5. </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;">);
  6. }</span><span style="color: #0000ff;">else</span><span style="color: #000000;">{
  7. </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;">);
  8. }
  9. </span><span style="color: #0000ff;">return</span> <span style="color: #800080;">$table</span>."_".<span style="color: #800080;">$hash</span><span style="color: #000000;">;
  10. }
  11. </span><span style="color: #0000ff;">echo</span> get_hash_table(‘message‘,‘user18991‘); <span style="color: #008000;">//</span><span style="color: #008000;">结果为message_10 </span>
  12. <span style="color: #0000ff;">echo</span> get_hash_table(‘message‘,‘user34523‘); <span style="color: #008000;">//</span><span style="color: #008000;">结果为message_13</span>
  13. <span style="color: #000000;">
  14. 优点:避免一张表出现几百万条数据,缩短了一条sql的执行时间
  15. 缺点:当一种规则确定时,打破这条规则会很麻烦,上面的例子中我用的hash算法是crc32,如果我现在不想用这个算法了,改用md5后,会使同一个用户的消息被存储到不同的表中,
  16.    这样数据乱套了。扩展性很差。</span>

2.3 利用merge存储引擎来实现分表

merge分表,分为主表和子表,主表类似于一个壳子,逻辑上封装了子表,实际上数据都是存储在子表中的。

  1. <span style="color: #008080;">mysql</span>> CREATE TABLE <span style="color: #0000ff;">IF</span><span style="color: #000000;"> NOT EXISTS `user1` (
  2. </span>-> `id` int(11) NOT <span style="color: #0000ff;">NULL</span> AUTO_INCREMENT,
  3. -> `name` varchar(50) <span style="color: #0000ff;">DEFAULT</span> <span style="color: #0000ff;">NULL</span>,
  4. -> `sex` int(1) NOT <span style="color: #0000ff;">NULL</span> <span style="color: #0000ff;">DEFAULT</span> ‘0‘,
  5. -> PRIMARY <span style="color: #008080;">KEY</span><span style="color: #000000;"> (`id`)
  6. </span>-> ) ENGINE=MyISAM <span style="color: #0000ff;">DEFAULT</span> CHARSET=utf8 AUTO_INCREMENT=1<span style="color: #000000;"> ;
  7. Query OK</span>, 0 rows affected (0.05<span style="color: #000000;"> sec)
  8. </span><span style="color: #008080;">mysql</span>> CREATE TABLE <span style="color: #0000ff;">IF</span><span style="color: #000000;"> NOT EXISTS `user2` (
  9. </span>-> `id` int(11) NOT <span style="color: #0000ff;">NULL</span> AUTO_INCREMENT,
  10. -> `name` varchar(50) <span style="color: #0000ff;">DEFAULT</span> <span style="color: #0000ff;">NULL</span>,
  11. -> `sex` int(1) NOT <span style="color: #0000ff;">NULL</span> <span style="color: #0000ff;">DEFAULT</span> ‘0‘,
  12. -> PRIMARY <span style="color: #008080;">KEY</span><span style="color: #000000;"> (`id`)
  13. </span>-> ) ENGINE=MyISAM <span style="color: #0000ff;">DEFAULT</span> CHARSET=utf8 AUTO_INCREMENT=1<span style="color: #000000;"> ;
  14. Query OK</span>, 0 rows affected (0.01<span style="color: #000000;"> sec)
  15. </span><span style="color: #008080;">mysql</span>> INSERT INTO `user1` (`name`, `sex`) VALUES(‘张映‘, 0<span style="color: #000000;">);
  16. Query OK</span>, 1 row affected (0.00<span style="color: #000000;"> sec)
  17. </span><span style="color: #008080;">mysql</span>> INSERT INTO `user2` (`name`, `sex`) VALUES(‘tank‘, 1<span style="color: #000000;">);
  18. Query OK</span>, 1 row affected (0.00<span style="color: #000000;"> sec)
  19. </span><span style="color: #008080;">mysql</span>> CREATE TABLE <span style="color: #0000ff;">IF</span><span style="color: #000000;"> NOT EXISTS `alluser` (
  20. </span>-> `id` int(11) NOT <span style="color: #0000ff;">NULL</span> AUTO_INCREMENT,
  21. -> `name` varchar(50) <span style="color: #0000ff;">DEFAULT</span> <span style="color: #0000ff;">NULL</span>,
  22. -> `sex` int(1) NOT <span style="color: #0000ff;">NULL</span> <span style="color: #0000ff;">DEFAULT</span> ‘0‘,
  23. -><span style="color: #000000;"> INDEX(id)
  24. </span>-> ) TYPE=MERGE UNION=(user1,user2) INSERT_METHOD=LAST AUTO_INCREMENT=1<span style="color: #000000;"> ;
  25. Query OK</span>, 0 rows affected, 1 warning (0.00<span style="color: #000000;"> sec)
  26. 创建主表的时候有个INSERT_METHOD,指明插入方式,取值可以是:</span>0<span style="color: #000000;"> 不允许插入;FIRST 插入到UNION中的第一个表; LAST 插入到UNION中的最后一个表。
  27. 通过主表查询的时候,相当于将所有子表合在一起查询。这样并不能体现分表的优势,建议还是查询子表。
  28. 优点:扩展性好,并且程序代码改动的不是很大
  29. 缺点:这种方法的效果比第二种要差一点,查询性能不高</span>

参考资料:http://blog.51yip.com/mysql/949.html

3.集群

  1. <span style="color: #008080;">MySQL</span> Proxy就是这么一个中间层代理,简单的说,<span style="color: #008080;">MySQL</span><span style="color: #000000;"> Proxy就是一个连接池,负责将前台应用的连接请求转发给后台的数据库,并且通过使用lua脚本,可以实现复杂的连接控制和过滤,从而实现读写分离和负载
  2. 平衡。对于应用来说,</span><span style="color: #008080;">MySQL</span><span style="color: #000000;"> Proxy是完全透明的,应用则只需要连接到MySQL Proxy的监听端口即可。当然,这样proxy机器可能成为单点失效,但完全可以使用多个proxy机器做为冗余,在应用服务器的连接池配置
  3. 中配置到多 个proxy的连接参数即可。</span>

参考资料:http://www.cnblogs.com/phpstudy2015-6/p/6706465.html

        http://blog.51yip.com/mysql/399.html

(转)MYSQL 的分区 、分表、集群

标签:应用服务   判断   1年   详情   运行   har   list分区   结果   相同   

人气教程排行