最近在学习MySQL数据库建表、分区、索引一些基础知识,这篇文章将简单的介绍关于建表的一些建议、分区和索引的使用,重点介绍5.5版本之后MySQL分区对日期字段的支持。
建表(使用的引擎都是InnoDB,[url=http://database.51cto.com/art/201506/481965.htm]详情见这篇文章[/url],MyISAM引擎已经停止开发了)
1.--除了业务功能需要的字段外,建议增加一列,使用自增id(auto_increment),同时推荐所有的字段不允许为空,因为空字段会需要额外的空间开销;
2.--将需要分区的列加入到主键之中,mysql建立分区时,如果存在主键,那么分区使用的列必须包含在主键之中;(一知半解地说说,应该和物理位置有关系,InnoDB引擎会根据主键建立聚集索引,索引和数据放在一起。)
下面是建表语句:添加自增id列,同时将需要分区的列名加入到主键中;采用COLUMNS关键字直接对日期进行分区。
分区的效果
1.--读写速度加快:可以同时访问多个的物理区域,对于sum()、count()函数有效果,加快查询速度。
2.--查询速度加快:尽可能少的访问最少的数据获得查询结果,如果确切的知道某个数据在哪个区,那么引擎在执行查询语句时,只会扫描该区的数据。
对于第一点,用下面这张图张图(用Echarts画的)解释:
[img]http://dl2.iteye.com/upload/attachment/0114/3541/37d84fca-c4ca-3e52-b3c5-a10650e962f7.png[/img]
不使用索引和分区,扫描全表(150万的数据),需要25秒;
单独使用分区,10秒;
单独使用主键索引,5秒;
同时使用主键和分区,4秒。
由此可见,使用分区或者主键索引,可以大大降低count(*)所需要的时间,而主键索引效果又比分区好很多,这也就是为什么在建表时,建议增加一列自增id作为主键的原因之一;
使用分区更多是因为第二点,加快查询速度
[img]http://dl2.iteye.com/upload/attachment/0114/3543/6f03f6a4-aac4-3ccc-9b6c-3eabbfdd4a21.png[/img]
这条查询语句在执行时,只使用了p1、p2分区的数据,而非全表,必然会加快查询速度。
值得一提地是在写sql语句中发现的另外两点:
1.--查询条件“1<num and num<2”和“1<num<2”使用分区的区别
2.--查询时“<、>”何时有效。
对于第一点,仍旧在已经建好的这张表上实验就能知道
使用and查询
[img]http://dl2.iteye.com/upload/attachment/0114/3549/dffea70d-2d1a-3267-aef9-f1e5a1e9ae6d.png[/img]
不使用and查询
[img]http://dl2.iteye.com/upload/attachment/0114/3547/e60999c4-01f0-355b-be93-189bef4c32c4.png[/img]
使用and查询,只扫描了p3分区;而不使用and查询时,扫描了整个分区。
对于第二点,需要重新建一张表来说明
建表语句有点长,看着有点蛋疼,实际上就是根据月份的天数分了31个区,每个月的同一天数据都会塞到一个分区,用着也很蛋疼,开发的时候数据比较少,项目跑上3年,分区跟没建一样,这也是我不能理解的地方。
闲话少说,还是回归正题,当我在这张表上使用">、<"查询时,发现分区失效了。
[img]http://dl2.iteye.com/upload/attachment/0114/3551/ea51815f-ad47-392c-ad25-d860ab3c0083.png[/img]
这种分区方式其实是在MySQL5.5之前普遍地做法,[url=http://database.51cto.com/art/201002/184392.htm]看了一篇老外的博客[/url],上面是这么解释的:使用YEAR或TO_DAYS定义一个分区的确让人费解,查询时不得不使用赤裸列,因为加了函数的查询不能识别分区。
其实并不准确,这种分区方式,仅支持部分查询,比如“in、=”,再贴两张图吧
[img]http://dl2.iteye.com/upload/attachment/0114/3565/1a44cb05-6c2e-3a88-a067-e4e0e83347c0.png[/img]
[img]http://dl2.iteye.com/upload/attachment/0114/3567/44e89a38-8c44-35cb-8a66-019abed76eb4.png[/img]
这也是同事当初给我说的,查询时尽量用in,并没有错。
顺便解释一下为什么第二张表会多出一个日期字段date_ymd,date字段类型是datetime,入库时带上时间,直接根据这个字段分区,是可以的。关键是,没法用,因为,这么建分区,不支持上文说的“<、>”,所以才会使用触发器增加了一个新的字段date_ymd,查询时,现根据date_ymd字段确定分区,而后在这个分区中查询数据。
建表(使用的引擎都是InnoDB,[url=http://database.51cto.com/art/201506/481965.htm]详情见这篇文章[/url],MyISAM引擎已经停止开发了)
1.--除了业务功能需要的字段外,建议增加一列,使用自增id(auto_increment),同时推荐所有的字段不允许为空,因为空字段会需要额外的空间开销;
2.--将需要分区的列加入到主键之中,mysql建立分区时,如果存在主键,那么分区使用的列必须包含在主键之中;(一知半解地说说,应该和物理位置有关系,InnoDB引擎会根据主键建立聚集索引,索引和数据放在一起。)
下面是建表语句:添加自增id列,同时将需要分区的列名加入到主键中;采用COLUMNS关键字直接对日期进行分区。
CREATE TABLE `anycast_table1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`host_id` int(11) NOT NULL,
`zebra` varchar(20) NOT NULL DEFAULT 'N/A',
`ospf` varchar(20) NOT NULL DEFAULT 'N/A',
`linktest` varchar(20) NOT NULL,
`date` datetime NOT NULL,
`date_ymd` date NOT NULL DEFAULT '0000-00-00',
`serviceip` varchar(256) NOT NULL DEFAULT 'N/A',
`host_type` char(1) DEFAULT '1',
`ospf6` varchar(20) DEFAULT 'N/A',
PRIMARY KEY (`id`,`date`),
KEY `hostindex` (`host_id`,`date`)
) ENGINE=InnoDB AUTO_INCREMENT=4594 DEFAULT CHARSET=gb2312
/*!50500 PARTITION BY RANGE COLUMNS(`date`)
(PARTITION range_p1 VALUES LESS THAN ('2015-12-29') ENGINE = InnoDB,
PARTITION range_p2 VALUES LESS THAN ('2015-12-30') ENGINE = InnoDB,
PARTITION range_p3 VALUES LESS THAN ('2015-12-31') ENGINE = InnoDB,
PARTITION range_p4 VALUES LESS THAN ('2016-01-01') ENGINE = InnoDB,
PARTITION range_p5 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */
分区的效果
1.--读写速度加快:可以同时访问多个的物理区域,对于sum()、count()函数有效果,加快查询速度。
2.--查询速度加快:尽可能少的访问最少的数据获得查询结果,如果确切的知道某个数据在哪个区,那么引擎在执行查询语句时,只会扫描该区的数据。
对于第一点,用下面这张图张图(用Echarts画的)解释:
[img]http://dl2.iteye.com/upload/attachment/0114/3541/37d84fca-c4ca-3e52-b3c5-a10650e962f7.png[/img]
不使用索引和分区,扫描全表(150万的数据),需要25秒;
单独使用分区,10秒;
单独使用主键索引,5秒;
同时使用主键和分区,4秒。
由此可见,使用分区或者主键索引,可以大大降低count(*)所需要的时间,而主键索引效果又比分区好很多,这也就是为什么在建表时,建议增加一列自增id作为主键的原因之一;
使用分区更多是因为第二点,加快查询速度
[img]http://dl2.iteye.com/upload/attachment/0114/3543/6f03f6a4-aac4-3ccc-9b6c-3eabbfdd4a21.png[/img]
这条查询语句在执行时,只使用了p1、p2分区的数据,而非全表,必然会加快查询速度。
值得一提地是在写sql语句中发现的另外两点:
1.--查询条件“1<num and num<2”和“1<num<2”使用分区的区别
2.--查询时“<、>”何时有效。
对于第一点,仍旧在已经建好的这张表上实验就能知道
使用and查询
[img]http://dl2.iteye.com/upload/attachment/0114/3549/dffea70d-2d1a-3267-aef9-f1e5a1e9ae6d.png[/img]
不使用and查询
[img]http://dl2.iteye.com/upload/attachment/0114/3547/e60999c4-01f0-355b-be93-189bef4c32c4.png[/img]
使用and查询,只扫描了p3分区;而不使用and查询时,扫描了整个分区。
对于第二点,需要重新建一张表来说明
CREATE TABLE `anycast_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`host_id` int(11) NOT NULL,
`zebra` varchar(20) NOT NULL DEFAULT 'N/A',
`ospf` varchar(20) NOT NULL DEFAULT 'N/A',
`linktest` varchar(20) NOT NULL,
`date` datetime NOT NULL,
`date_ymd` date NOT NULL DEFAULT '0000-00-00',
`serviceip` varchar(256) NOT NULL DEFAULT 'N/A',
`host_type` char(1) DEFAULT '1',
`ospf6` varchar(20) DEFAULT 'N/A',
PRIMARY KEY (`id`,`date_ymd`),
KEY `hostindex` (`host_id`,`date`)
) ENGINE=MyISAM AUTO_INCREMENT=4594 DEFAULT CHARSET=gb2312
/*!50100 PARTITION BY RANGE (dayofmonth(`date_ymd`))
(PARTITION range_p1 VALUES LESS THAN (2) ENGINE = MyISAM,
PARTITION range_p2 VALUES LESS THAN (3) ENGINE = MyISAM,
PARTITION range_p3 VALUES LESS THAN (4) ENGINE = MyISAM,
PARTITION range_p4 VALUES LESS THAN (5) ENGINE = MyISAM,
PARTITION range_p5 VALUES LESS THAN (6) ENGINE = MyISAM,
PARTITION range_p6 VALUES LESS THAN (7) ENGINE = MyISAM,
PARTITION range_p7 VALUES LESS THAN (8) ENGINE = MyISAM,
PARTITION range_p8 VALUES LESS THAN (9) ENGINE = MyISAM,
PARTITION range_p9 VALUES LESS THAN (10) ENGINE = MyISAM,
PARTITION range_p10 VALUES LESS THAN (11) ENGINE = MyISAM,
PARTITION range_p11 VALUES LESS THAN (12) ENGINE = MyISAM,
PARTITION range_p12 VALUES LESS THAN (13) ENGINE = MyISAM,
PARTITION range_p13 VALUES LESS THAN (14) ENGINE = MyISAM,
PARTITION range_p14 VALUES LESS THAN (15) ENGINE = MyISAM,
PARTITION range_p15 VALUES LESS THAN (16) ENGINE = MyISAM,
PARTITION range_p16 VALUES LESS THAN (17) ENGINE = MyISAM,
PARTITION range_p17 VALUES LESS THAN (18) ENGINE = MyISAM,
PARTITION range_p18 VALUES LESS THAN (19) ENGINE = MyISAM,
PARTITION range_p19 VALUES LESS THAN (20) ENGINE = MyISAM,
PARTITION range_p20 VALUES LESS THAN (21) ENGINE = MyISAM,
PARTITION range_p21 VALUES LESS THAN (22) ENGINE = MyISAM,
PARTITION range_p22 VALUES LESS THAN (23) ENGINE = MyISAM,
PARTITION range_p23 VALUES LESS THAN (24) ENGINE = MyISAM,
PARTITION range_p24 VALUES LESS THAN (25) ENGINE = MyISAM,
PARTITION range_p25 VALUES LESS THAN (26) ENGINE = MyISAM,
PARTITION range_p26 VALUES LESS THAN (27) ENGINE = MyISAM,
PARTITION range_p27 VALUES LESS THAN (28) ENGINE = MyISAM,
PARTITION range_p28 VALUES LESS THAN (29) ENGINE = MyISAM,
PARTITION range_p29 VALUES LESS THAN (30) ENGINE = MyISAM,
PARTITION range_p30 VALUES LESS THAN (31) ENGINE = MyISAM,
PARTITION range_p31 VALUES LESS THAN (32) ENGINE = MyISAM) */
建表语句有点长,看着有点蛋疼,实际上就是根据月份的天数分了31个区,每个月的同一天数据都会塞到一个分区,用着也很蛋疼,开发的时候数据比较少,项目跑上3年,分区跟没建一样,这也是我不能理解的地方。
闲话少说,还是回归正题,当我在这张表上使用">、<"查询时,发现分区失效了。
[img]http://dl2.iteye.com/upload/attachment/0114/3551/ea51815f-ad47-392c-ad25-d860ab3c0083.png[/img]
这种分区方式其实是在MySQL5.5之前普遍地做法,[url=http://database.51cto.com/art/201002/184392.htm]看了一篇老外的博客[/url],上面是这么解释的:使用YEAR或TO_DAYS定义一个分区的确让人费解,查询时不得不使用赤裸列,因为加了函数的查询不能识别分区。
其实并不准确,这种分区方式,仅支持部分查询,比如“in、=”,再贴两张图吧
[img]http://dl2.iteye.com/upload/attachment/0114/3565/1a44cb05-6c2e-3a88-a067-e4e0e83347c0.png[/img]
[img]http://dl2.iteye.com/upload/attachment/0114/3567/44e89a38-8c44-35cb-8a66-019abed76eb4.png[/img]
这也是同事当初给我说的,查询时尽量用in,并没有错。
顺便解释一下为什么第二张表会多出一个日期字段date_ymd,date字段类型是datetime,入库时带上时间,直接根据这个字段分区,是可以的。关键是,没法用,因为,这么建分区,不支持上文说的“<、>”,所以才会使用触发器增加了一个新的字段date_ymd,查询时,现根据date_ymd字段确定分区,而后在这个分区中查询数据。