本文的原文连接是: http://blog.csdn.net/freewebsys/article/details/78624090
未经博主允许不得转载。
博主地址是:http://blog.csdn.net/freewebsys
1,关于mysql 分区
https://dev.mysql.com/doc/refman/5.7/en/partitioning-overview.html
mysql 分区也是一个强大的功能。
一般情况下,大家都想着做数据库分表。直接用下划线命名。
其实在mysql 当中可以直接使用分区表。进行底层的数据拆分。
让数据访问的速度更快。
2,举个股票数据的栗子
股票的每天的交易数据都是很大的,所以需要讲数据库表按月,或者按季度进行拆分。
--创建 分区表。每月的分区小于下个月 1号的日期。
CREATE TABLE `stat_stock_all` (
`date` date NOT NULL,
`code` varchar(255) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`change` varchar(255) DEFAULT NULL,
`open` varchar(255) DEFAULT NULL,
`preclose` varchar(255) DEFAULT NULL,
`close` varchar(255) DEFAULT NULL,
`high` varchar(255) DEFAULT NULL,
`low` varchar(255) DEFAULT NULL,
`volume` varchar(255) DEFAULT NULL,
`amount` varchar(255) DEFAULT NULL,
PRIMARY KEY (`date`,`code`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
PARTITION BY RANGE(TO_DAYS(`date`))
(
PARTITION p201612 VALUES LESS THAN (TO_DAYS('2017-01-01')),
PARTITION p201701 VALUES LESS THAN (TO_DAYS('2017-02-01')),
PARTITION p201702 VALUES LESS THAN (TO_DAYS('2017-03-01')),
PARTITION p201703 VALUES LESS THAN (TO_DAYS('2017-04-01')),
PARTITION p201704 VALUES LESS THAN (TO_DAYS('2017-05-01')),
PARTITION p201705 VALUES LESS THAN (TO_DAYS('2017-06-01')),
PARTITION p201706 VALUES LESS THAN (TO_DAYS('2017-07-01')),
PARTITION p201707 VALUES LESS THAN (TO_DAYS('2017-08-01')),
PARTITION p201708 VALUES LESS THAN (TO_DAYS('2017-09-01')),
PARTITION p201709 VALUES LESS THAN (TO_DAYS('2017-10-01')),
PARTITION p201710 VALUES LESS THAN (TO_DAYS('2017-11-01')),
PARTITION p201711 VALUES LESS THAN (TO_DAYS('2017-12-01'))
);
参考:
https://dev.mysql.com/doc/refman/5.6/en/partitioning-management-range-list.html
说明,按月拆分的时候,需要指定,date字段 less 小于下个月1 号。才是这个月的数据。
需要使用 to_days 函数,要是按季度拆分的话如下:
CREATE TABLE `stat_stock_all` (
`date` date NOT NULL,
`code` varchar(255) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`change` varchar(255) DEFAULT NULL,
`open` varchar(255) DEFAULT NULL,
`preclose` varchar(255) DEFAULT NULL,
`close` varchar(255) DEFAULT NULL,
`high` varchar(255) DEFAULT NULL,
`low` varchar(255) DEFAULT NULL,
`volume` varchar(255) DEFAULT NULL,
`amount` varchar(255) DEFAULT NULL,
PRIMARY KEY (`date`,`code`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
PARTITION BY RANGE(TO_DAYS(`date`))
(
PARTITION p2017q1 VALUES LESS THAN (TO_DAYS('2017-04-01')),
PARTITION p2017q2 VALUES LESS THAN (TO_DAYS('2017-07-01')),
PARTITION p2017q3 VALUES LESS THAN (TO_DAYS('2017-10-01')),
PARTITION p2017q4 VALUES LESS THAN (TO_DAYS('2018-01-01')),
PARTITION p2018q1 VALUES LESS THAN (TO_DAYS('2018-04-01')),
PARTITION p2018q2 VALUES LESS THAN (TO_DAYS('2018-07-01')),
PARTITION p2018q3 VALUES LESS THAN (TO_DAYS('2018-10-01')),
PARTITION p2018q4 VALUES LESS THAN (TO_DAYS('2019-01-01')),
PARTITION p2019q1 VALUES LESS THAN (TO_DAYS('2019-04-01')),
PARTITION p2019q2 VALUES LESS THAN (TO_DAYS('2019-07-01')),
PARTITION p2019q3 VALUES LESS THAN (TO_DAYS('2019-10-01')),
PARTITION p2019q4 VALUES LESS THAN (TO_DAYS('2020-01-01')),
PARTITION p2020q1 VALUES LESS THAN (TO_DAYS('2020-04-01')),
PARTITION p2020q2 VALUES LESS THAN (TO_DAYS('2020-07-01')),
PARTITION p2020q3 VALUES LESS THAN (TO_DAYS('2020-10-01')),
PARTITION p2020q4 VALUES LESS THAN (TO_DAYS('2021-01-01')),
PARTITION p2021q1 VALUES LESS THAN (TO_DAYS('2021-04-01')),
PARTITION p2021q2 VALUES LESS THAN (TO_DAYS('2021-07-01')),
PARTITION p2021q3 VALUES LESS THAN (TO_DAYS('2021-10-01')),
PARTITION p2021q4 VALUES LESS THAN (TO_DAYS('2022-01-01')),
PARTITION p2022q1 VALUES LESS THAN (TO_DAYS('2022-04-01')),
PARTITION p2022q2 VALUES LESS THAN (TO_DAYS('2022-07-01')),
PARTITION p2022q3 VALUES LESS THAN (TO_DAYS('2022-10-01')),
PARTITION p2022q4 VALUES LESS THAN (TO_DAYS('2023-01-01'))
);
https://dev.mysql.com/doc/refman/5.7/en/partitioning-management-range-list.html
特别说明:
mysql 有 year 函数,但是没有 to_month 函数,造成不能直接获得年+月这样的数据。
只能使用 TO_DAYS 这样的函数进行折中的使用了。
只要是 能range 到一个数据就行了。实际使用当中,可以提前把几年的数据分区都做了。
这样的话查询速度就快多了。
3,分区删除,增加,查询
--删除一个表的分区。
ALTER TABLE stat_stock_all DROP PARTITION p201612;
--增加一个表分区。
ALTER TABLE stat_stock_all ADD PARTITION (PARTITION p201712 VALUES LESS THAN (TO_DAYS('2018-01-01')));
--直接按照分区查询查询全部表分区数据
SELECT * FROM stat_stock_all PARTITION (p201711,p201710);
--查询分区信息
SELECT DISTINCT *
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME='stat_stock_all'
展示分区表查询结果:
可以详细的看到分区的列。查询的时候直接输入所在分区。
EXPLAIN PARTITIONS SELECT * FROM stat_stock_all PARTITION (p201711,p201710);
分析是否命中分区查询
4,总结
mysql 的分区表非常方便的使用,可以通过最简单的方式解决数据的问题。
基本上按照月进行拆分就可以了。如果数据量更大的话。再按照每月 上中下旬进行拆分。
实在不行,就分库优化了。
本文的原文连接是: http://blog.csdn.net/freewebsys/article/details/78624090
未经博主允许不得转载。
博主地址是:http://blog.csdn.net/freewebsys