- 浏览: 247192 次
- 性别:
- 来自: 长沙
文章分类
最新评论
-
maozilee:
njoiop 写道看得懂吗,看到这个还不错(http://ww ...
使用百度地图API将谷歌地图坐标转换为百度地图坐标 -
njoiop:
看得懂吗,看到这个还不错(http://www.haoserv ...
使用百度地图API将谷歌地图坐标转换为百度地图坐标 -
di1984HIT:
不错。,不错
MySQL 5.5中使用UNIX_TIMESTAMP分区【按天】 -
zhouhongyan123:
js地址变了!! http://developer.baidu ...
GPS定位转百度坐标,精度很高! -
tanier53:
那我最近也在做大批量导入这个功能,你说得比较好,但我有疑问数据 ...
用Java向数据库中插入大量数据时的优化
mysql 5.1已经到了beta版,官方网站上也陆续有一些文章介绍,比如上次看到的Improving Database Performance with Partitioning。在使用分区的前提下,可以用mysql实现非常大的数据量存储。今天在mysql的站上又看到一篇进阶的文章 —— 按日期分区存储。如果能够实现按日期分区,这对某些时效性很强的数据存储是相当实用的功能。下面是从这篇文章中摘录的一些内容。
错误的按日期分区例子
最直观的方法,就是直接用年月日这种日期格式来进行常规的分区:
-
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会用整型数值来进行分区:
-
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)
搞定?接着往下分析
-
mysql> explain partitions
-
-> select count(*) from part_date1 where
-
-> c3> date '1995-01-01' and c3 <date '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: ALL
-
possible_keys: NULL
-
key: NULL
-
key_len: NULL
-
ref: NULL
-
rows: 8100000
-
Extra: Using where
-
1 row in set (0.00 sec)
万恶的mysql居然对上面的sql使用全表扫描,而不是按照我们的日期分区分块查询。原文中解释到MYSQL的优化器并不认这种日期形式的分区,花了大量的篇幅来引诱俺走上歧路,过分。
正确的日期分区例子
mysql优化器支持以下两种内置的日期函数进行分区:
- TO_DAYS()
- YEAR()
看个例子:
-
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()函数分区成功,我们分析一下看看:
-
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: ALL
-
possible_keys: NULL
-
key: NULL
-
key_len: NULL
-
ref: NULL
-
rows: 808431
-
Extra: Using where
-
1 row in set (0.00 sec)
可以看到,mysql优化器这次不负众望,仅仅在p1分区进行查询。在这种情况下查询,真的能够带来提升查询效率么?下面分别对这次建立的part_date3和之前分区失败的part_date1做一个查询对比:
-
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验证,这样才能获得真正的性能提升。
热切期待msyql 5.1稳定版发布!
发表评论
-
BoneCP学习笔记——配置参数
2012-09-01 00:01 1249一、BoneCP配置文件格式( ... -
常用MySQL配置以及修改最大连接数的3种方法
2012-08-30 10:20 2329修改 MYSQL 最大连接数的3种方法: 方法一:进入M ... -
MySQL 忘记密码重置密码
2011-12-20 11:30 974分别在Windows下和Linux下重置了MYSQL的root ... -
CentOS下配置MySQL (Linux文件系统移动MySQL数据库的位置)
2011-12-12 17:08 2177一、下载MySQL的安装文件 完全安装MySQL需要下 ... -
#2002 无法登录 MySQL 服务器
2011-12-12 17:03 5854#2002 – 服务器没有响应 (或者本地 MySQL ... -
CentOS 5.5使用yum安装Apache+PHP+MySQL
2011-12-12 12:26 1644本文摘自互联网,COPY主义,也要申明一下! ... -
CentOS yum安装mysql后 Can’t connect to local MySQL server through socket ‘/var/lib/
2011-12-12 12:22 3168CentOS Can’t connect to loca ... -
MySQL的奇异表现
2011-10-28 16:44 870发现mysql的 类型如果为varchar,那么,它依然能够进 ... -
启用MySQL慢查询查看日志配置方法
2011-10-07 12:49 1331MySQL慢查询记录日志对于跟踪PHP+MySQL体系下的 ... -
mysql_secure_installation
2011-10-07 12:43 3706打开终端,输入 ... -
MySQL更改默认数据文件存储目录
2011-10-07 12:39 2687很多朋友都遇到过在CentOS Linux下用RPM或是 ... -
Innodb如何使用内存
2011-09-24 12:36 0译这个文章的目的: 最近经常被问起Innodb是 ... -
mysql给同一个表添加多个索引的测试
2011-09-27 21:05 1267分别给xc表添加ind_name和ind_status ... -
MySQL慢查询的2种分析方案
2011-09-27 21:04 801以下的文章主要介绍的是MySQL慢查询分析方法,前一段日 ... -
MySQL 5.5中使用UNIX_TIMESTAMP分区【按天】
2011-09-25 00:09 5126RT不用多说,同行一看 ... -
MySQL性能优化——设计篇
2011-09-23 22:36 0设计篇 最近项目中,我们常常碰到单表过5亿的情况出现, ... -
MySQL 5.5 使用 Event定期自动维护/执行Procedure
2011-09-27 21:04 3558最近有个项目,对于数据库的并发性要求很高,对于事物,Rollb ... -
准备升级到:MySQL 5.5!
2011-09-23 15:55 2148MySQL进入Oracle产品体系 ... -
MySQL灾难恢复之官方方法
2011-09-23 15:51 1029备份你可以用 mysqldump 工具。 定时,你可以 ... -
MySQL Replace INTO的高级使用
2011-09-23 12:58 909REPLACE的运行与INSERT很相 ...
相关推荐
详细介绍mysql5.1 分区技术,通过对list range hash key四种分区技术的举例阐述Mysql分区,18.2.1. RANGE分区 18.2.2. LIST分区 18.2.3. HASH分区 18.2.4. KEY分区 18.2.5. 子分区 18.2.6. MySQL分区处理NULL值的...
MySQL5.1新功能-分区
1.6.1. MySQL 5.1的新特性 1.7. MySQL信息源 1.7.1. MySQL邮件列表 1.7.2. IRC(在线聊天系统)上的MySQL社区支持 1.7.3. MySQL论坛上的MySQL社区支持 1.8. MySQL标准的兼容性 1.8.1. MySQL遵从的标准是什么 1.8.2. ...
MYSQL5.1表分区初探[参考].pdf
1.6.1. MySQL 5.1的新特性 1.7. MySQL信息源 1.7.1. MySQL邮件列表 1.7.2. IRC(在线聊天系统)上的MySQL社区支持 1.7.3. MySQL论坛上的MySQL社区支持 1.8. MySQL标准的兼容性 1.8.1. MySQL遵从的标准是什么 ...
1.6.1. MySQL 5.1的新特性 1.7. MySQL信息源 http://doc.mysql.cn/mysql5/refman-5.1-zh.html-chapter/(第 1/24 页)2006-11-02 19:12:13 MySQL 5.1 Reference Manual 1.7.1. MySQL邮件列表 1.7.2. IRC(在线聊天...
MySQL 5.1参考手册.chm 前言 1. 一般信息 1.1. 关于本手册 1.2. 本手册采用的惯例 1.3. MySQL AB概述 1.4. MySQL数据库管理系统概述 1.4.1. MySQL的历史 1.4.2. MySQL的的主要特性 1.4.3. MySQL稳定性 1.4.4. MySQL...
1.6.1. MySQL 5.1的新特性 1.7. MySQL信息源 1.7.1. MySQL邮件列表 1.7.2. IRC(在线聊天系统)上的MySQL社区支持 1.7.3. MySQL论坛上的MySQL社区支持 1.8. MySQL标准的兼容性 1.8.1. MySQL遵从的标准是什么 1.8.2. ...
MySQL 5.1的新特性 1.7. MySQL信息源 1.7.1. MySQL邮件列表 1.7.2. IRC(在线聊天系统)上的MySQL社区支持 1.7.3. MySQL论坛上的MySQL社区支持 1.8. MySQL标准的兼容性 1.8.1. MySQL遵从的标准是...
MySQL_5.1_HTML_chapter.rar_详细手册(html版),内容详细,主要部分:13. SQL语句语法、18. 分区、A. 问题和常见错误
MySQL 5.1参考手册 这是MySQL参考手册的翻译版本,关于MySQL参考手册,请访问dev.mysql.com。 原始参考手册为英文版,与英文版参考手册相比,本翻译版可能不是最新的。 This translation was done by MySQL ...
MySQL5.1 中文手册 这是MySQL参考手册的翻译版本,关于MySQL参考手册,请访问dev.mysql.com。 原始参考手册为英文版。 -------------------------------------------------------------------------------- ...
表分区的测试使用,主要内容来自于其他博客文章以及mysql5.1的参考手册mysql测试版本:mysql5.5.28mysql物理存储文件(有mysql配置的datadir决定存储路径)格式简介数据库engine为MYISAMfrm表结构文件,myd表数据...
MySQL 分区技术(是mysql 5.1以版本后开始用->是甲骨文mysql技术团队维护人员以插件形式插入到mysql里面的技术) 1、概述 数据库单表到达一定量后,性能会有衰减,像mysql\sql server等犹为明显,所以需要把这些数据...
mysql5.1开始支持数据表分区了。如:某用户表的记录超过了600万条,那么就可以根据入库日期将表分区,也可以根据所在地将表分区。当然也可根据其他的条件分区。二、为什么要对表进行分区为了改善大型表以及具有各种...
最近需要把一个oralce数据库向mysql数据库移植。oracle库用到了job和存储过程,因为mysql中没有job,所有要用新功能event代替一下,这个是5.1以后才有的新功能,所以稍微研究了一下。
记录了以下五大部分 一、grant用户授权 二、开启mysql bin-log日志 三、mysql主从复制 四、mysql分区技术,仅中mysql5.1之后的版本中支持。 五、mysql性能优化
在 Mysql 5.1(后) 有了几种分区类型: RANGE分区: 基于属于一个给定连续区间的列值, 把多行分配给分区 LIST分区: 类似于按 RANGE 分区, 区别在于 LIST 分区是基于列值匹配一个离散值集合中的某个值来进行选择 ...