MySQL分表分区 解决千万条数据存储

  • A+
所属分类：Mysql

现在的项目是一秒钟单张表插入两条数据，一天也就是86400条数据，一个月2592000，数据有点多，并且以前的数据查看的机会比较少，一般都是查看最近几天或者几周的，网上搜集了一些资料，暂时的解决方案是用分表、分区技术。

分表

MySQL分表的话就是创建一个'外壳'表，向这个表插入数据会放到其他表里面

CREATE TABLE `alluser` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `sex` int(1) NOT NULL DEFAULT '0',
  KEY `id` (`id`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=(`user1`,`user2`);

CREATE TABLE `user1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `sex` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

CREATE TABLE `user2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `sex` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

总表alluser必须要和分表user1、user2一模一样，字段、索引都要一样。但是分表会有一些问题需要解决，首先你对alluser插入数据的时候INSERT_METHOD=LAST是插入最后一张表(或者指定为FIRST，插入到最后一张表)，不是很灵活，当然了你可以再服务端做代码修改，插入数据库的时候选择插入user1还是user2那么你就得修改服务端代码，不够友好。合并表是一种将被淘汰的技术，在未来的版本中可能被删除(高性能MySQL257页)

分区

mysql分区就是把一张表的物理存储映射到不同的地方，在我这个项目中，按照日期进行分区非常合适，每三个月映射到一个分区。分区有四种RANGE分区、LIST分区、HASH分区、KEY分区，关于分区更多操作查看官方文档吧

表结构(表有三十多个字段，只展示了一部分）

CREATE TABLE `slabdatarealtime` (
  `SlabNumber` int(11) NOT NULL AUTO_INCREMENT COMMENT '板坯唯一标志',
  `SlabTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '板坯生产时间',
  `LiberalSideWater` float NOT NULL COMMENT '自由侧冷却水流量',
  PRIMARY KEY (`SlabNumber`,`SlabTime`) USING BTREE,
  KEY `SlabTime` (`SlabTime`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1264637 DEFAULT CHARSET=utf8

创建分区

alter table slabdatarealtime partition by range columns(SlabTime)
(
partition slabdatarealtime20180103 values less than('2018-04-01 00:00:00'),
partition slabdatarealtime20180306 values less than('2018-07-01 00:00:00'),
partition slabdatarealtime20180609 values less than('2018-10-01 00:00:00'),
partition slabdatarealtime20180912 values less than('2019-01-01 00:00:00')
)
或者
alter table slabdatarealtime partition by range(to_days(SlabTime))
(
partition slabdatarealtime2018up values less than (to_days('2018-07-01')),
partition slabdatarealtime2018down values less than (to_days('2019-01-01')),
partition slabdatarealtime2019up values less than (to_days('2019-07-01')),
partition slabdatarealtime2019down values less than (to_days('2020-01-01')),
partition slabdatarealtime2020up values less than (to_days('2020-07-01')),
partition slabdatarealtime2020down values less than (to_days('2021-01-01'))
)

partiton分区子句可以使用各种函数。但是有一个要求，表达式返回的值要是一个确定的整数，且不能是一个常数。这里使用了to_days()返回天数，类似时间戳。在MySQL5.5中，还可以使用range columns 类型的分区，这样即使是基于时间的分区也无须再将其转化为一个整数。

注意：

  • 分区列SlabTime要是主键的一部分，不然会报错。
  • SlabTime一开始是timestamp类型，结果提示不能用于分区，就修改成了datetime,datetime和timestamp区别不大
  • 发现的一个问题，通过range columns(SlabTime)分区，explain partitions select s.* from slabdatarealtime s where s.SlabTime > '2018-04-23 17:32:48' and s.SlabTime < '2018-05-23 23:34:34' 发现type 为all，也就是没有使用索引，通过range(to_days(SlabTime)) type为range走了索引的

分区常用操作：

  • 查看分区是否起作用

explain partitions select s.* from slabdatarealtime s
where s.SlabTime > '2018-04-23 17:32:48' 
and s.SlabTime < '2018-05-23 23:34:34'

输出

id  select_type table   partitions  type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  s   slabdatarealtime20180306    ALL SlabTime                22359   Using where

可以看到只在slabdatarealtime20180306 分区进行查找。

  • 从分区查找
select * from slabdatarealtime PARTITION(slabdatarealtime20180306)
  • 查看有一个表所有分区
SELECT
  partition_name part, 
  partition_expression expr, 
  partition_description descr, 
  table_rows 
FROM
  INFORMATION_SCHEMA.partitions
WHERE
  TABLE_SCHEMA = schema() 
  AND TABLE_NAME='slabdatarealtime';

结果

part                          expr             descr          table_rows
slabdatarealtime20180103    `SlabTime`  '2018-04-01 00:00:00'   0
slabdatarealtime20180306    `SlabTime`  '2018-07-01 00:00:00'   22359
slabdatarealtime20180609    `SlabTime`  '2018-10-01 00:00:00'   0
slabdatarealtime20180912    `SlabTime`  '2019-01-01 00:00:00'   0
  • 删除分区(会把里面的数据也删除掉)
alter table slabdatarealtime drop partition slabdatarealtime20180103
  • 删除分区，保留数据
alter table slabdatarealtime remove partitioning
  • 增加分区
ALTER TABLE slabdatarealtime ADD PARTITION(
PARTITION slabdatarealtime20190103 VALUES LESS THAN ('2019-04-01 00:00:00')
);
  • 合并分区
ALTER TABLE test1 REORGANIZE PARTITION slabdatarealtime20180609,slabdatarealtime20180912 INTO (PARTITION slabdatarealtime20180612 VALUES less than('2019-01-01 00:00:00'));

分区的一些优点包括：

  • 与单个磁盘或文件系统分区相比，可以存储更多的数据
  • 对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。
  • 通常和分区有关的其他优点包括下面列出的这些。MySQL 分区中的这些功能目前还没有实现，但是在我们的优先级列表中，具有高的优先级；我们希望在5.1的生产版本中，能包括这些功能。
  • 一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE 语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。
  • 涉及到例如SUM() 和 COUNT()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT(orders) as order_total FROM sales GROUP BY salesperson_id；”。通过“并行”， 这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。
  • 通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。

对于这张表，我们主要是查询，插入要求不是很严格，可以把表的搜索引擎修改为MyISAM，创建B+tree索引，这里有篇文章写的非常棒什么是B+树。

alter table slabdatarealtime engine=MyISAM

修改了表的搜索引擎后，各个区的搜索引擎也会自动变成MyISAM

导出查询结果

想要把查询的结果导出备份一下，以防万一。

select * from slabdatarealtime partition(slabdatarealtime2018down) into outfile 'D:\\slabdatarealtime2018down.csv'
fields terminated by ',' optionally enclosed by '#' lines terminated by '\r\n'

fields TERMINATED BY ','设置字段的分割符
OPTIONALLY ENCLOSED BY '#'设置字段内容若为字符串，则使用'#'包含
LINES TERMINATED BY '\r\n'数据行分割符
如果把D盘改成C盘的话，可能会提示没有权限的错误，所有直接导入到D盘吧

导出之后还需要导入到数据库中

load data infile 'D:\\slabdatarealtime2018down.csv'
into table slabdatarealtime fields terminated by ',' optionally enclosed by '#' lines terminated by '\r\n'

参考资料：

  • 我的微信
  • 这是我的微信扫一扫
  • weinxin
  • 我的微信公众号
  • 我的微信公众号扫一扫
  • weinxin