mysql 数据量大,使用月分区,加快速度大数据查询
来源:互联网 发布:淘宝客是什么意思 编辑:程序博客网 时间:2024/05/21 09:49
本文的原文连接是: 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=utf8PARTITION 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=utf8PARTITION 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
- mysql 数据量大,使用月分区,加快速度大数据查询
- MySQL临时表的简单用法 在大数据量时有时会加快查询速度
- MySQL临时表的简单用法 在大数据量时有时会加快查询速度
- MySQL临时表的简单用法 在大数据量时有时会加快查询速度
- 提高MYSQL大数据量查询的速度
- 提高MYSQL大数据量查询的速度
- 提高MYSQL大数据量查询的速度
- 提高MYSQL大数据量查询的速度
- 提高MySQL大数据量查询的速度
- Mysql数据量大提高查询速度
- 提高MYSQL大数据量查询的速度
- 加快大表关联查询速度
- 数据库----提高大数据量查询速度
- mysql 数据库大数据量查询
- mysql 插入大数据量和分区
- 大数据量数据全部查询(Java+MySQL)
- WITH语句,有效加快大数据量下的查询效率
- ORACEL 大数据表 加快查询速度方案 总结
- hibernate框架
- synchronized, reentrantlock,condition
- qt debug输出
- 子域名枚举工具Sublist3r
- C语言基本类型与其数据存储方式
- mysql 数据量大,使用月分区,加快速度大数据查询
- python使用中文
- Pandas秘籍【第四章】
- PE 文件结构分析
- eclipse,debug执行到this调用方法时,就会出现无响应的情况
- INNER JOIN,LEFT JOIN,RIGHT JOIN的连接方式
- mysqlbinlog 查看
- 冒泡排序
- 微服务拆分需要考虑的必要因素与坚持原则