类似水平分表，但是它是mysql在物理层进行水平分表，与对应的应用程序来说，它还是一张表
mysql5.1开始以插件的形式支持分区
mysql常用分区类型有 RANGE LIST HASH KEY
show plugins; //通过show plugins命令，如果显示partition active 表示分区插件已启动
innodb引擎的表有两种表空间：共享表空间、独占表空间
如果innodb的表要做分区，必须是独占表空间，而且个人也比较建议使用独占表空间（性能更高、灵活性更高），唯一的缺点就是占用硬盘容量更大
|
1 2 3 4 5 6 |
show variables like "innodb_file_per_table"; //通过该命令，如果显示 ON 则表示独立表空间已开启 //在my.ini中配置独占表空间，以下4行必须同时配置，且目录必须是存在的目录 innodb_data_home_dir = "C:\mysql\data\" //代表数据库文件所存放的目录 innodb_log_group_home_dir = "C:\mysql\data\" //日志存放目录 innodb_data_file_path=ibdata1:10M:autoextend //默认在innodb_data_home_dir所配置的目录中，名为ibdata1，可扩展大小为10M innodb_file_per_table=1 //1表示开启独占表空间 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
//创建分区表 CREATE TABLE part_tab( c1 int DEFAULT null, c2 VARCHAR(30) DEFAULT null, c3 date DEFAULT null ) 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 ); //创建未分区表 CREATE TABLE `no_part_tab` ( `c1` int(11) DEFAULT NULL, `c2` varchar(30) DEFAULT NULL, `c3` date DEFAULT NULL ); //向分区表插入800万条数据 //由于我用的是innodb引擎 如果my.cnf中没有配置过autocommit的话，默认是自动提交的 //可以通过show variables like "%autocommit%";查看 //所以要设置成非默认提交，每100W条插入一次，否则会很慢 delimiter// create procedure load_part_tab() BEGIN DECLARE v INT DEFAULT 0; SET autocommit=0; WHILE v < 8000000 DO INSERT INTO part_tab VALUES(v,'testing',ADDDATE('1995-01-01',(RAND(v)*36520) mod 3652)); set v=v+1; IF v%1000000=0 AND v<>0 THEN COMMIT; END IF; END WHILE; COMMIT; SET autocommit=1; END; // delimiter; call load_part_tab(); //此时分区表的800万条数据已经插完了，将其导入非分区表即可 insert into no_part_tab select * from part_tab; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
//运行2次时间分别为 0.542s 0.366s count值为795181 SELECT COUNT(1) FROM part_tab WHERE c3> DATE'1995-01-01' and c3<date'1995-12-31'; //运行2次时间分别为 4.141s 4.247s count值为795181 SELECT COUNT(1) FROM no_part_tab WHERE c3> DATE'1995-01-01' and c3<date'1995-12-31'; //分别为两个表增加索引 create index idx_of_c3 on part_tab(c3); create index idx_of_c3 on no_part_tab(c3); //再次运行上面的查询语句 //分区表时间：0.471s 0.359s 未分区表时间：0.495s 0.368s //分别在where条件中加入一个未索引的字段 count值为0 //0.552s 0.409s SELECT COUNT(1) FROM part_tab WHERE c3> DATE'1995-01-01' and c3<date'1995-12-31' and c2='hello'; //4.437s 4.451s SELECT COUNT(1) FROM no_part_tab WHERE c3> DATE'1995-01-01' and c3<date'1995-12-31' and c2='hello'; |
从测试可看出，如果表的数据量不是很大的情况下，建立索引可以起到同样的优化效果，只有表数据量较大（估计2000万条以上吧- -）或者一些不方便建立索引的表上，选择分区技术也是不错的方案。
