mysql —— 分表分区（1）

mr923

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);
　　Query OK, 0 rows affected (0.14 sec)
　　创建非分区表
　　mysql> create table no_part_tab ( c1 int default NULL, c2 varchar(30) default null, c3 date default null) engine=myisam;
　　Query OK, 0 rows affected (0.11 sec)
　　mysql> \d // #由于下面要用到存储过程，这里需要修改结束符为“//”。所谓的存储过程其实也就是众多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
　　-> //
　　Query OK, 0 rows affected (0.04 sec)
　　mysql> \d ; // 执行完这个存储过程后，需要将结束符修改回去
　　上面的存储过程实际上是为了创建大量的数据（800万条）
　　mysql> call load_part_tab(); // 调用load_part_tab这个存储过程
　　Query OK, 1 row affected (9 min 18.95 sec)
　　快速将part_tab里面的数据插入到no_part_tab里面
　　mysql> insert no_part_tab select * from part_tab;
　　Query OK, 8000000 rows affected (8.97 sec)
　　Records: 8000000 Duplicates: 0 Warnings: 0
　　测试一：
　　实验之前确保两个表里面的数据是一致的！保证实验的可比性
　　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.49 sec)
　　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 (3.94 sec)
mysql> desc select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'\G;　　*************************** 1. row ***************************

　　>　　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.09 sec)
　　ERROR:
　　No query specified
　　mysql> desc select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'\G;
　　*************************** 1. row ***************************

　　>　　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)
　　ERROR:
　　No query specified
　　结论：可以看到，做了分区之后，只需要扫描79万条语句，而不做分区的，则需要进行全表扫描，故可以看出，做了分区技术后，可以提高读写效率。
　　测试2：
　　创建索引，查看语句执行情况

　　mysql> create index>　　Query OK, 8000000 rows affected (32.68 sec)
　　Records: 8000000 Duplicates: 0 Warnings: 0
　　结果分析：
　　mysql> desc select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'\G;
　　*************************** 1. row ***************************

　　>　　select_type: SIMPLE
　　table: NO_part_tab
　　type: range

　　possible_keys:>
　　key:>　　key_len: 4
　　ref: NULL
　　rows: 785678
　　Extra: Using where; Using index
　　1 row in set (0.16 sec)
　　ERROR:
　　No query specified
　　结论：为未分区的表创建了索引之后，再次执行相同的语句，可以看到该SQL语句是根据range索引进行检索，而不是全表扫描了。明显效率也提高了。
　　测试3：
　　测试做索引与未作索引的读写效率。

　　mysql> create index>　　Query OK, 8000000 rows affected (31.85 sec)
　　Records: 8000000 Duplicates: 0 Warnings: 0
　　mysql> desc select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'\G;
　　*************************** 1. row ***************************

　　>　　select_type: SIMPLE
　　table: part_tab
　　type: index

　　possible_keys:>
　　key:>　　key_len: 4
　　ref: NULL
　　rows: 798458
　　Extra: Using where; Using index
　　1 row in set (0.14 sec)
　　ERROR:
　　No query specified
　　测试未创建索引字段
　　mysql> select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31' and c2='hello';
+----------+　　| count(*) |
　　+----------+
　　| 0 |
　　+----------+
　　1 row in set (4.90 sec)
　　结论：可以看到如果没通过索引进行检索所耗费的时间将长于通过索引进行检索。
　　测试4：删除
　　mysql> delete from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';
　　Query OK, 795181 rows affected (14.02 sec)
　　mysql> delete from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';
　　Query OK, 795181 rows affected (15.21 sec)
　　结论：可以看到，在删除方面，有分区的还是比没分区的快一点。从而体现了其便于数据管理的特点
　　方便数据管理这点，我通过下面的例子来说明：比如数据库的表t1记录的是今年一整年（12个月）公司的营业额，在未分区的情况下，也就是说数据文件都存放在同一个文件里面，那么假如现在要删除第一个季度的记录，那么需要全表扫描才能得出结果。但如果t1这个表事先做了分区，那么我只需要分别删除1,2,3这三个文件即可。所以从一定程度上，还是方便了管理。