MYSQL之表分区----按日期分区
来源:互联网 发布:离线搜题软件 编辑:程序博客网 时间:2024/05/10 15:25
错误的按日期分区例子
最直观的方法,就是直接用年月日这种日期格式来进行常规的分区:
PLAIN TEXT
CODE:
上面的例子中,就是直接用"Y-m-d"的格式来对一个table进行分区,可惜想当然往往不能奏效,会得到一个错误信息:
ERROR 1064 (42000): VALUES value must be of same type as partition function near '),
partition p1 VALUES LESS THAN ('2010-01-01'))' at line 3
上述分区方式没有成功,而且明显的不经济,老练的DBA会用整型数值来进行分区:
PLAIN TEXT
CODE:
Query OK, 0 rows affected (0.01 sec)
搞定?接着往下分析
PLAIN TEXT
CODE:
万恶的mysql居然对上面的sql使用全表扫描,而不是按照我们的日期分区分块查询。原文中解释到MYSQL的优化器并不认这种日期形式的分区,花了大量的篇幅来引诱俺走上歧路,过分。
正确的日期分区例子
mysql优化器支持以下两种内置的日期函数进行分区:
TO_DAYS()
YEAR()
看个例子:
PLAIN TEXT
CODE:
Query OK, 0 rows affected (0.00 sec)
以to_days()函数分区成功,我们分析一下看看:
PLAIN TEXT
CODE:
可以看到,mysql优化器这次不负众望,仅仅在p1分区进行查询。在这种情况下查询,真的能够带来提升查询效率么?下面分别对这次建立的part_date3和之前分区失败的part_date1做一个查询对比:
PLAIN TEXT
CODE:
可以看到,分区正确的话query花费时间为4秒,而分区错误则花费时间40秒(相当于没有分区),效率有90%的提升!所以我们千万要正确的使用分区功能,分区后务必用explain验证,这样才能获得真正的性能提升。
注意:
在mysql5.1中建立分区表的语句中,只能包含下列函数:
最直观的方法,就是直接用年月日这种日期格式来进行常规的分区:
PLAIN TEXT
CODE:
mysql> create table rms (d date) -> partition by range (d) -> (partition p0 values less than ('1995-01-01'), -> partition p1 VALUES LESS THAN ('2010-01-01'));
上面的例子中,就是直接用"Y-m-d"的格式来对一个table进行分区,可惜想当然往往不能奏效,会得到一个错误信息:
ERROR 1064 (42000): VALUES value must be of same type as partition function near '),
partition p1 VALUES LESS THAN ('2010-01-01'))' at line 3
上述分区方式没有成功,而且明显的不经济,老练的DBA会用整型数值来进行分区:
PLAIN TEXT
CODE:
mysql> CREATE TABLE part_date1 -> ( c1 int default NULL, -> c2 varchar(30) default NULL, -> c3 date default NULL) engine=myisam -> partition by range (cast(date_format(c3,'%Y%m%d') as signed)) -> (PARTITION p0 VALUES LESS THAN (19950101), -> PARTITION p1 VALUES LESS THAN (19960101) , -> PARTITION p2 VALUES LESS THAN (19970101) , -> PARTITION p3 VALUES LESS THAN (19980101) , -> PARTITION p4 VALUES LESS THAN (19990101) , -> PARTITION p5 VALUES LESS THAN (20000101) , -> PARTITION p6 VALUES LESS THAN (20010101) , -> PARTITION p7 VALUES LESS THAN (20020101) , -> PARTITION p8 VALUES LESS THAN (20030101) , -> PARTITION p9 VALUES LESS THAN (20040101) , -> PARTITION p10 VALUES LESS THAN (20100101), -> PARTITION p11 VALUES LESS THAN MAXVALUE );
Query OK, 0 rows affected (0.01 sec)
搞定?接着往下分析
PLAIN TEXT
CODE:
mysql> explain partitions -> select count(*) from part_date1 where -> c3> '1995-01-01' and c3 <'1995-12-31'\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: part_date1 partitions: p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11 type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 8100000 Extra: Using where1 row in set (0.00 sec)
万恶的mysql居然对上面的sql使用全表扫描,而不是按照我们的日期分区分块查询。原文中解释到MYSQL的优化器并不认这种日期形式的分区,花了大量的篇幅来引诱俺走上歧路,过分。
正确的日期分区例子
mysql优化器支持以下两种内置的日期函数进行分区:
TO_DAYS()
YEAR()
看个例子:
PLAIN TEXT
CODE:
mysql> CREATE TABLE part_date3 -> ( c1 int default NULL, -> c2 varchar(30) default NULL, -> c3 date default NULL) engine=myisam -> partition by range (to_days(c3)) -> (PARTITION p0 VALUES LESS THAN (to_days('1995-01-01')), -> PARTITION p1 VALUES LESS THAN (to_days('1996-01-01')) , -> PARTITION p2 VALUES LESS THAN (to_days('1997-01-01')) , -> PARTITION p3 VALUES LESS THAN (to_days('1998-01-01')) , -> PARTITION p4 VALUES LESS THAN (to_days('1999-01-01')) , -> PARTITION p5 VALUES LESS THAN (to_days('2000-01-01')) , -> PARTITION p6 VALUES LESS THAN (to_days('2001-01-01')) , -> PARTITION p7 VALUES LESS THAN (to_days('2002-01-01')) , -> PARTITION p8 VALUES LESS THAN (to_days('2003-01-01')) , -> PARTITION p9 VALUES LESS THAN (to_days('2004-01-01')) , -> PARTITION p10 VALUES LESS THAN (to_days('2010-01-01')), -> PARTITION p11 VALUES LESS THAN MAXVALUE );
Query OK, 0 rows affected (0.00 sec)
以to_days()函数分区成功,我们分析一下看看:
PLAIN TEXT
CODE:
mysql> explain partitions -> select count(*) from part_date3 where -> c3> date '1995-01-01' and c3 <date '1995-12-31'\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: part_date3 partitions: p1 type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 808431 Extra: Using where1 row in set (0.00 sec)
可以看到,mysql优化器这次不负众望,仅仅在p1分区进行查询。在这种情况下查询,真的能够带来提升查询效率么?下面分别对这次建立的part_date3和之前分区失败的part_date1做一个查询对比:
PLAIN TEXT
CODE:
mysql> select count(*) from part_date3 where -> c3> date '1995-01-01' and c3 <date '1995-12-31';+----------+| count(*) |+----------+| 805114 |+----------+1 row in set (4.11 sec) mysql> select count(*) from part_date1 where -> c3> date '1995-01-01' and c3 <date '1995-12-31';+----------+| count(*) |+----------+| 805114 |+----------+1 row in set (40.33 sec)
可以看到,分区正确的话query花费时间为4秒,而分区错误则花费时间40秒(相当于没有分区),效率有90%的提升!所以我们千万要正确的使用分区功能,分区后务必用explain验证,这样才能获得真正的性能提升。
注意:
在mysql5.1中建立分区表的语句中,只能包含下列函数:
ABS()CEILING() and FLOOR() (在使用这2个函数的建立分区表的前提是使用函数的分区键是INT类型),例如mysql> CREATE TABLE t (c FLOAT) PARTITION BY LIST( FLOOR(c) )( -> PARTITION p0 VALUES IN (1,3,5), -> PARTITION p1 VALUES IN (2,4,6) -> );; ERROR 1491 (HY000): The PARTITION function returns the wrong type mysql> CREATE TABLE t (c int) PARTITION BY LIST( FLOOR(c) )( -> PARTITION p0 VALUES IN (1,3,5), -> PARTITION p1 VALUES IN (2,4,6) -> ); Query OK, 0 rows affected (0.01 sec)DAY()DAYOFMONTH()DAYOFWEEK()DAYOFYEAR()DATEDIFF()EXTRACT()HOUR()MICROSECOND()MINUTE()MOD()MONTH()QUARTER()SECOND()TIME_TO_SEC()TO_DAYS()WEEKDAY()YEAR()YEARWEEK()
0 0
- MYSQL之表分区----按日期分区
- MYSQL之表分区----按日期分区
- mysql 按日期分区
- MySQL分区按日期
- mysql 5.5之后 按日期表分区
- mysql按日期分区函数
- MySQL按日期月份分区实例解析
- mysql 5.1新功能 -- 按日期分区
- mysql 5.1新功能 -- 按日期分区
- 【20150622】自动按日期分区的分区表建立
- UICollectionView (仿系统相册,按日期分区 item瀑布流)
- MySQL分区之分区概述
- MySQL分区之RANGE分区
- MySQL分区之RANGE分区
- MySQL分区之RANGE分区
- MySQL分区之RANGE分区
- mysql分区之range分区
- MySQL表分区 之二
- Android NDK开发指南(一) Application.mk文件
- 中国版TestFlight?Testin推免费手游内测分发工具Pre.im
- 学习网站
- 解决VirtualBox下安装虚拟机(Ubuntu)出错(不能为虚拟电脑Ubuntu打开一个新的任务)的问题
- Delete Node in a Linked List
- MYSQL之表分区----按日期分区
- HDU 1028 Ignatius and the Princess III
- ruby调用shell问题——找不到自己环境变量中的程序
- javassist学习三
- CodeChef COUNTARI Arithmetic Progressions FFT + 分块
- Objective-C 属性特性详解
- APP源码分享-你最美源码
- ubuntu14.04 64位 adb配置问题
- 连接数据库报“You have an error in your SQL syntax”异常的解决