我有一个统计表,包含帐号、业务数据以及日期字段,数据量会比较多,而且大多数查询条件是帐号以及时间段。
我建立两个测试表,一个分区,一个不分区。
分区函数用如下形式:PARTITION BY RANGE (datediff(settledate,'2008-05-31'))
(PARTITION p0806 VALUES LESS THAN (30) ENGINE = InnoDB,
PARTITION p0807 VALUES LESS THAN (61) ENGINE = InnoDB,
PARTITION p0808 VALUES LESS THAN (92) ENGINE = InnoDB,
PARTITION p0809 VALUES LESS THAN (122) ENGINE = InnoDB,
PARTITION p0810 VALUES LESS THAN (153) ENGINE = InnoDB,
PARTITION p0811 VALUES LESS THAN (183) ENGINE = InnoDB,
PARTITION p0812 VALUES LESS THAN (214) ENGINE = InnoDB,
PARTITION p0901 VALUES LESS THAN (245) ENGINE = InnoDB,
PARTITION p0902 VALUES LESS THAN (273) ENGINE = InnoDB,
PARTITION p0903 VALUES LESS THAN (304) ENGINE = InnoDB,
PARTITION p0904 VALUES LESS THAN (334) ENGINE = InnoDB,
PARTITION p0905 VALUES LESS THAN (365) ENGINE = InnoDB,
PARTITION p0906 VALUES LESS THAN (395) ENGINE = InnoDB,
PARTITION p0907 VALUES LESS THAN (426) ENGINE = InnoDB,
PARTITION p0908 VALUES LESS THAN (457) ENGINE = InnoDB,
PARTITION p0909 VALUES LESS THAN (487) ENGINE = InnoDB,
PARTITION p0910 VALUES LESS THAN (518) ENGINE = InnoDB,
PARTITION p0911 VALUES LESS THAN (548) ENGINE = InnoDB,
PARTITION p0 VALUES LESS THAN MAXVALUE ENGINE = InnoDB)然后两个表里面都插入400多万的数据,进行测试的时候,性能基本没有什么差异啊。
是分区表的问题还是我没有选择好的分区函数的关系?
我建立两个测试表,一个分区,一个不分区。
分区函数用如下形式:PARTITION BY RANGE (datediff(settledate,'2008-05-31'))
(PARTITION p0806 VALUES LESS THAN (30) ENGINE = InnoDB,
PARTITION p0807 VALUES LESS THAN (61) ENGINE = InnoDB,
PARTITION p0808 VALUES LESS THAN (92) ENGINE = InnoDB,
PARTITION p0809 VALUES LESS THAN (122) ENGINE = InnoDB,
PARTITION p0810 VALUES LESS THAN (153) ENGINE = InnoDB,
PARTITION p0811 VALUES LESS THAN (183) ENGINE = InnoDB,
PARTITION p0812 VALUES LESS THAN (214) ENGINE = InnoDB,
PARTITION p0901 VALUES LESS THAN (245) ENGINE = InnoDB,
PARTITION p0902 VALUES LESS THAN (273) ENGINE = InnoDB,
PARTITION p0903 VALUES LESS THAN (304) ENGINE = InnoDB,
PARTITION p0904 VALUES LESS THAN (334) ENGINE = InnoDB,
PARTITION p0905 VALUES LESS THAN (365) ENGINE = InnoDB,
PARTITION p0906 VALUES LESS THAN (395) ENGINE = InnoDB,
PARTITION p0907 VALUES LESS THAN (426) ENGINE = InnoDB,
PARTITION p0908 VALUES LESS THAN (457) ENGINE = InnoDB,
PARTITION p0909 VALUES LESS THAN (487) ENGINE = InnoDB,
PARTITION p0910 VALUES LESS THAN (518) ENGINE = InnoDB,
PARTITION p0911 VALUES LESS THAN (548) ENGINE = InnoDB,
PARTITION p0 VALUES LESS THAN MAXVALUE ENGINE = InnoDB)然后两个表里面都插入400多万的数据,进行测试的时候,性能基本没有什么差异啊。
是分区表的问题还是我没有选择好的分区函数的关系?
解决方案 »
- 我在学校的机器用SQL server 2000建了过程和视图 但是在MySql怎么不好用了 请问需要改哪些语法,帮改下,过几天答辩,还得用自己的电脑
- SQLyog Enterprise 连接不上
- 一个复杂的查询,销售系统数据库设计与开发应用
- phpmyadmin同时管理多个数据库
- 如何将一个建好的数据库的字体由UTF-8改成GBK
- linux下mysql安装菜鸟问题,急!马上给分
- 安装完驱动程序后出现了个问题
- 查询一行中为NULL或为空的字段数量
- MySQL 5.5.23 Group By的问题
- mysql 多实例脚本启动 报错 ERROR 2002
- 一个关于mysql主从数据库的问题,大家探讨一下
- mysql一条最简单的SQL语句,执行缺很慢,求解
year()的情况和你目前的不一样。
mysql优化器支持以下两种内置的日期函数进行分区:TO_DAYS()
YEAR()