首先说下什么是分表也分区
日常开发中我们经常会遇到大表的情况,所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大,导致数据库在查询和插入的时候耗时太长,性能低下,如果涉及联合查询的情况,性能会更加糟糕。分表和表分区的目的就是减少数据库的负担,提高数据库的效率,通常点来讲就是提高表的增删改查效率。
什么是分表?
分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表,我们可以称为子表,每个表都对应三个文件,MYD数据文件,.MYI索引文件,.frm表结构文件。这些子表可以分布在同一块磁盘上,也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的子表名,然后去操作它。
什么是分区?
分区和分表相似,都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表,而分区是将数据分段划分在多个位置存放,可以是同一块磁盘也可以在不同的机器。分区后,表面上还是一张表,但数据散列到多个位置了。app读写的时候操作的还是大表名字,db自动去组织分区的数据。
mysql分表和分区有什么联系呢?
1.都能提高mysql的性高,在高并发状态下都有一个良好的表现。 2.分表和分区不矛盾,可以相互配合的,对于那些大访问量,并且表数据比较多的表,我们可以采取分表和分区结合的方式(如果merge这种分表方式,不能和分区配合的话,可以用其他的分表试),访问量不大,但是表数据很多的表,我们可以采取分区的方式等。 3.分表技术是比较麻烦的,需要手动去创建子表,app服务端读写时候需要计算子表名。采用merge好一些,但也要创建子表和配置子表间的union关系。 4.表分区相对于分表,操作方便,不需要创建子表。自5.1开始对分区(Partition)有支持
= 水平分区(根据列属性按行分)= 举个简单例子:一个包含十年发票记录的表可以被分区为十个不同的分区,每个分区包含的是其中一年的记录。 === 水平分区的几种模式:=== * Range(范围) – 这种模式允许DBA将数据划分不同范围。例如DBA可以将一个表通过年份划分成三个分区,80年代(1980's)的数据,90年代(1990's)的数据以及任何在2000年(包括2000年)后的数据。 * Hash(哈希) – 这中模式允许DBA通过对表的一个或多个列的Hash Key进行计算,最后通过这个Hash码不同数值对应的数据区域进行分区,。例如DBA可以建立一个对表主键进行分区的表。 * Key(键值) – 上面Hash模式的一种延伸,这里的Hash Key是MySQL系统产生的。 * List(预定义列表) – 这种模式允许系统通过DBA定义的列表的值所对应的行数据进行分割。例如:DBA建立了一个横跨三个分区的表,分别根据2004年2005年和2006年值所对应的数据。 * Composite(复合模式) - 很神秘吧,哈哈,其实是以上模式的组合使用而已,就不解释了。举例:在初始化已经进行了Range范围分区的表上,我们可以对其中一个分区再进行hash哈希分区。 = 垂直分区(按列分)= 举个简单例子:一个包含了大text和BLOB列的表,这些text和BLOB列又不经常被访问,这时候就要把这些不经常使用的text和BLOB了划分到另一个分区,在保证它们数据相关性的同时还能提高访问速度。 [分区表和未分区表试验过程] *创建分区表,按日期的年份拆分[sql]
- mysql> CREATE TABLE part_tab ( c1 int default NULL, c2 varchar(30) default NULL, c3 date default NULL) engine=myisam
- PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (1995),
- PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) ,
- PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) ,
- PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) ,
- PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) ,
- PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010),
- PARTITION p11 VALUES LESS THAN MAXVALUE );
注意最后一行,考虑到可能的最大值
*创建未分区表[sql]
- mysql> create table no_part_tab (c1 int(11) default NULL,c2 varchar(30) default NULL,c3 date default NULL) engine=myisam;
> delimiter // /* 设定语句终结符为 //,因存储过程语句用;结束 */
[sql]
- mysql> CREATE PROCEDURE load_part_tab()
- begin
- declare v int default 0;
- while v < 8000000
- do
- insert into part_tab
- values (v,'testing partitions',adddate('1995-01-01',(rand(v)*36520) mod 3652));
- set v = v + 1;
- end while;
- end
- //
- mysql> delimiter ;
- mysql> call load_part_tab();
Query OK, 1 row affected (8 min 17.75 sec)
[sql]
- mysql> insert into no_part_tab select * from part_tab;
Query OK, 8000000 rows affected (51.59 sec)
Records: 8000000 Duplicates: 0 Warnings: 0* SQL性能
[sql]
- mysql> select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';
+----------+
| count(*) | +----------+ | 795181 | +----------+1 row in set (0.55 sec)
[sql]
- mysql> select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';
+----------+
| count(*) | +----------+ | 795181 | +----------+ 1 row in set (4.69 sec) 结果表明分区表比未分区表的执行时间少90%。 * 通过explain语句来分析执行情况[sql]
- mysql > explain select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'\G
/* 结尾的\G使得mysql的输出改为列模式 */
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: no_part_tab type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 8000000 Extra: Using where 1 row in set (0.00 sec)
[sql]
- mysql> explain select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'\G
*************************** 1. row ***************************
id: 1 select_type: SIMPLE table: part_tab type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 798458 Extra: Using where 1 row in set (0.00 sec) explain语句显示了SQL查询要处理的记录数目 * 试验创建索引后情况[sql]
- mysql> create index idx_of_c3 on no_part_tab (c3);
Query OK, 8000000 rows affected (1 min 18.08 sec)
Records: 8000000 Duplicates: 0 Warnings: 0[sql]
- mysql> create index idx_of_c3 on part_tab (c3);
Query OK, 8000000 rows affected (1 min 19.19 sec)
Records: 8000000 Duplicates: 0 Warnings: 0 创建索引后的数据库文件大小列表: 2008-05-24 09:23 8,608 no_part_tab.frm 2008-05-24 09:24 255,999,996 no_part_tab.MYD 2008-05-24 09:24 81,611,776 no_part_tab.MYI 2008-05-24 09:25 0 part_tab#P#p0.MYD 2008-05-24 09:26 1,024 part_tab#P#p0.MYI 2008-05-24 09:26 25,550,656 part_tab#P#p1.MYD 2008-05-24 09:26 8,148,992 part_tab#P#p1.MYI 2008-05-24 09:26 25,620,192 part_tab#P#p10.MYD 2008-05-24 09:26 8,170,496 part_tab#P#p10.MYI 2008-05-24 09:25 0 part_tab#P#p11.MYD 2008-05-24 09:26 1,024 part_tab#P#p11.MYI 2008-05-24 09:26 25,656,512 part_tab#P#p2.MYD 2008-05-24 09:26 8,181,760 part_tab#P#p2.MYI 2008-05-24 09:26 25,586,880 part_tab#P#p3.MYD 2008-05-24 09:26 8,160,256 part_tab#P#p3.MYI 2008-05-24 09:26 25,585,696 part_tab#P#p4.MYD 2008-05-24 09:26 8,159,232 part_tab#P#p4.MYI 2008-05-24 09:26 25,585,216 part_tab#P#p5.MYD 2008-05-24 09:26 8,159,232 part_tab#P#p5.MYI 2008-05-24 09:26 25,655,740 part_tab#P#p6.MYD 2008-05-24 09:26 8,181,760 part_tab#P#p6.MYI 2008-05-24 09:26 25,586,528 part_tab#P#p7.MYD 2008-05-24 09:26 8,160,256 part_tab#P#p7.MYI 2008-05-24 09:26 25,586,752 part_tab#P#p8.MYD 2008-05-24 09:26 8,160,256 part_tab#P#p8.MYI 2008-05-24 09:26 25,585,824 part_tab#P#p9.MYD 2008-05-24 09:26 8,159,232 part_tab#P#p9.MYI 2008-05-24 09:25 8,608 part_tab.frm 2008-05-24 09:25 68 part_tab.par* 再次测试SQL性能
[sql]
- mysql> select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';
+----------+
| count(*) | +----------+ | 795181 | +----------+1 row in set (2.42 sec) /* 为原来4.69 sec 的51%*/
重启mysql ( net stop mysql, net start mysql)后,查询时间降为0.89 sec,几乎与分区表相同。
[sql]
- mysql> select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';
+----------+
| count(*) | +----------+ | 795181 | +----------+ 1 row in set (0.86 sec) * 更进一步的试验 ** 增加日期范围[sql]
- mysql> select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1997-12-31';
+----------+
| count(*) | +----------+ | 2396524 | +----------+ 1 row in set (5.42 sec)[sql]
- mysql> select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1997-12-31';
+----------+
| count(*) | +----------+ | 2396524 | +----------+1 row in set (2.63 sec)
** 增加未索引字段查询
[sql]
- mysql> select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date
- '1996-12-31' and c2='hello';
+----------+
| count(*) | +----------+ | 0 | +----------+ 1 row in set (0.75 sec)[sql]
- mysql> select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1996-12-31' and c2='hello';
+----------+
| count(*) | +----------+ | 0 | +----------+ 1 row in set (11.52 sec) = 初步结论 =* 分区和未分区占用文件空间大致相同 (数据和索引文件) * 如果查询语句中有未建立索引字段,分区时间远远优于未分区时间 * 如果查询语句中字段建立了索引,分区和未分区的差别缩小,分区略优于未分区。 = 最终结论 = * 对于大数据量,建议使用分区功能。 * 去除不必要的字段 * 根据手册, 增加myisam_max_sort_file_size 会增加分区性能