关于MySQL的分区(partion)

最新推荐文章于 2025-09-06 09:33:29 发布

转载最新推荐文章于 2025-09-06 09:33:29 发布 · 72 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/streetpasser/p/4633215.html

文章标签：

#数据库

本文将介绍如何创建并使用分区表，通过实例演示了如何为表按年份进行分区，以及如何利用分区表提高查询效率。此外，文章还对比了未分区表与分区表在查询特定时间段数据时的表现。

 1 CREATE TABLE part_tab
 2 (  c1 int default NULL,
 3 c2 varchar(30) default NULL,
 4 c3 date default NULL
 5 ) engine=myisam
 6 PARTITION BY RANGE (year(c3)) 
 7 (
 8 PARTITION p0 VALUES LESS THAN (1995),
 9 PARTITION p1 VALUES LESS THAN (1996) , 
10 PARTITION p2 VALUES LESS THAN (1997) ,
11 PARTITION p3 VALUES LESS THAN (1998) ,
12 PARTITION p4 VALUES LESS THAN (1999),
13 PARTITION p5 VALUES LESS THAN (2000) , 
14 PARTITION p6 VALUES LESS THAN (2001) ,
15 PARTITION p7 VALUES LESS THAN (2002) , 
16 PARTITION p8 VALUES LESS THAN (2003) ,
17 PARTITION p9 VALUES LESS THAN (2004) , 
18 PARTITION p10 VALUES LESS THAN (2010),
19 PARTITION p11 VALUES LESS THAN MAXVALUE 
20 );
21 
22 
23 create table no_part_tab
24 (c1 int(11) default NULL,
25 c2 varchar(30) default NULL,
26 c3 date default NULL
27 ) engine=myisam;
28 
29 
30 delimiter //
31 CREATE PROCEDURE load_part_tab()
32 begin
33 declare v int default 0;
34           while v < 8000000
35   do
36   insert into part_tab(c1,c2,c3)
37   values (v,'testing partitions',adddate('1995-01-01',(rand(v)*36520) mod 3652));
38   set v = v + 1;
39   end while;
40   end
41 //
42 
43 delimiter ;
44 call load_part_tab();
45 explain select count(*) from no_part_tab where
46 c3 > date '1995-01-01' and c3 < date '1995-12-31';
47 
48 explain select count(*) from part_tab where
49 c3 > date '1995-01-01' and c3 < date '1995-12-31';
50 
51 
52 
53 
54 CREATE TABLE part_tab2
55 (  
56 c1 int default NULL
57 ) engine=myisam
58 PARTITION BY RANGE (c1) 
59 (
60 PARTITION p0 VALUES LESS THAN (5),
61 PARTITION p1 VALUES LESS THAN (10),
62 PARTITION p2 VALUES LESS THAN MAXVALUE
63 );
64 
65 insert into part_tab2 values(2),(3);