mysql分区表测试

mysql分区表测试

mysql部署情况

使用docker-compose在10.xx.xx.1机器进行部署, 端口3307为master，3308为slave实例, master+slave ,以下的写入操作在master进行，读取操作在slave中进行

表结构

使用范围进行分区，时间戳一天一张表.

CREATE TABLE login_user (
id INT,
user_name VARCHAR(20),
create_date TIMESTAMP,
UNIQUE KEY (id,create_date)
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(create_date) ) (
PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2018-10-02 00:00:00') ),
PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2018-10-03 00:00:00') ),
PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2018-10-04 00:00:00') ),
PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2018-10-05 00:00:00') ),
PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2018-10-06 00:00:00') ),
PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2018-10-07 00:00:00') ),
PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2018-10-08 00:00:00') ),
PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2018-10-09 00:00:00') ),
PARTITION p9 VALUES LESS THAN ( UNIX_TIMESTAMP('2018-10-10 00:00:00') ),
PARTITION p10 VALUES LESS THAN (UNIX_TIMESTAMP('2018-10-11 00:00:00') )
);

分区情况

mysql root@localhost:my_database> select partition_name part,partition_expression expr,partition_description descr,table_rows from information_schema.partitions where table_schema = schema() and table_name='log
in_user';
+------+------------------------------+------------+------------+
| part | expr | descr | table_rows |
+------+------------------------------+------------+------------+
| p1 | UNIX_TIMESTAMP(create_date) | 1538438400 | 0 |
| p2 | UNIX_TIMESTAMP(create_date) | 1538524800 | 0 |
| p3 | UNIX_TIMESTAMP(create_date) | 1538611200 | 0 |
| p4 | UNIX_TIMESTAMP(create_date) | 1538697600 | 0 |
| p5 | UNIX_TIMESTAMP(create_date) | 1538784000 | 0 |
| p6 | UNIX_TIMESTAMP(create_date) | 1538870400 | 0 |
| p7 | UNIX_TIMESTAMP(create_date) | 1538956800 | 0 |
| p8 | UNIX_TIMESTAMP(create_date) | 1539043200 | 0 |
| p9 | UNIX_TIMESTAMP(create_date) | 1539129600 | 0 |
| p10 | UNIX_TIMESTAMP(create_date) | 1539216000 | 0 |
+------+------------------------------+------------+------------+
10 rows in set
Time: 0.015s

文件存储相关

多文件存储在IO层面减少多线程访问竞态条件.

I have no name!@4b8b48b630f3:/bitnami/mysql/data/my_database$ ls -alh
total 1.2M
drwxr-x--- 2 1001 root 4.0K Nov 7 04:03 .
drwxrwxr-x 6 root root 4.0K Nov 7 02:48 ..
-rw-r----- 1 1001 root 61 Nov 7 02:47 db.opt
-rw-r----- 1 1001 root 112K Nov 7 04:16 login_user#P#p1.ibd
-rw-r----- 1 1001 root 112K Nov 7 04:16 login_user#P#p10.ibd
-rw-r----- 1 1001 root 112K Nov 7 04:16 login_user#P#p2.ibd
-rw-r----- 1 1001 root 112K Nov 7 04:16 login_user#P#p3.ibd
-rw-r----- 1 1001 root 112K Nov 7 04:16 login_user#P#p4.ibd
-rw-r----- 1 1001 root 112K Nov 7 04:16 login_user#P#p5.ibd
-rw-r----- 1 1001 root 112K Nov 7 04:16 login_user#P#p6.ibd
-rw-r----- 1 1001 root 112K Nov 7 04:16 login_user#P#p7.ibd
-rw-r----- 1 1001 root 112K Nov 7 04:16 login_user#P#p8.ibd
-rw-r----- 1 1001 root 112K Nov 7 04:03 login_user#P#p9.ibd
-rw-r----- 1 1001 root 8.5K Nov 7 04:02 login_user.frm

写入测试数据

insert into login_user(id,user_name,create_date) values (1,'test','2018-10-01 01:01:03');
insert into login_user(id,user_name,create_date) values (2,'test','2018-10-02 01:02:03');
insert into login_user(id,user_name,create_date) values (3,'test','2018-10-03 01:03:03');
insert into login_user(id,user_name,create_date) values (4,'test','2018-10-04 01:04:03');
insert into login_user(id,user_name,create_date) values (5,'test','2018-10-05 01:05:03');
insert into login_user(id,user_name,create_date) values (6,'test','2018-10-06 01:06:03');
insert into login_user(id,user_name,create_date) values (7,'test','2018-10-07 01:07:03');
insert into login_user(id,user_name,create_date) values (8,'test','2018-10-08 01:08:03');
insert into login_user(id,user_name,create_date) values (9,'test','2018-10-08 01:09:03');
insert into login_user(id,user_name,create_date) values (10,'test','2018-10-10 01:10:03');

insert into login_user(id,user_name,create_date) values (11,'test','2018-10-01 02:01:03');
insert into login_user(id,user_name,create_date) values (12,'test','2018-10-02 02:02:03');
insert into login_user(id,user_name,create_date) values (13,'test','2018-10-03 02:03:03');
insert into login_user(id,user_name,create_date) values (14,'test','2018-10-04 02:04:03');
insert into login_user(id,user_name,create_date) values (15,'test','2018-10-05 02:05:03');
insert into login_user(id,user_name,create_date) values (16,'test','2018-10-06 02:06:03');
insert into login_user(id,user_name,create_date) values (17,'test','2018-10-07 02:07:03');
insert into login_user(id,user_name,create_date) values (18,'test','2018-10-08 02:08:03');
insert into login_user(id,user_name,create_date) values (19,'test','2018-10-08 02:09:03');
insert into login_user(id,user_name,create_date) values (20,'test','2018-10-10 02:10:03');

insert into login_user(id,user_name,create_date) values (21,'test','2018-10-01 03:01:03');
insert into login_user(id,user_name,create_date) values (22,'test','2018-10-02 03:02:03');
insert into login_user(id,user_name,create_date) values (23,'test','2018-10-03 03:03:03');
insert into login_user(id,user_name,create_date) values (24,'test','2018-10-04 03:04:03');
insert into login_user(id,user_name,create_date) values (25,'test','2018-10-05 03:05:03');
insert into login_user(id,user_name,create_date) values (26,'test','2018-10-06 03:06:03');
insert into login_user(id,user_name,create_date) values (27,'test','2018-10-07 03:07:03');
insert into login_user(id,user_name,create_date) values (28,'test','2018-10-08 03:08:03');
insert into login_user(id,user_name,create_date) values (29,'test','2018-10-08 03:09:03');
insert into login_user(id,user_name,create_date) values (30,'test','2018-10-10 03:10:03');

使用非分区字段查询

全分区扫描

mysql root@localhost:my_database> explain select * from login_user where id = 1 ;
+----+-------------+------------+--------------------------------+------+---------------+-----+---------+-------+------+----------+--------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+--------------------------------+------+---------------+-----+---------+-------+------+----------+--------+
| 1 | SIMPLE | login_user | p1,p2,p3,p4,p5,p6,p7,p8,p9,p10 | ref | id | id | 5 | const | 1 | 100.0 | <null> |
+----+-------------+------------+--------------------------------+------+---------------+-----+---------+-------+------+----------+--------+
1 row in set
Time: 0.024s

使用分区字段进行查询

直接读取p1

mysql root@localhost:my_database> explain select * from login_user where create_date = '2018-10-01 01:01:03';
+----+-------------+------------+------------+------+---------------+--------+---------+--------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+--------+---------+--------+------+----------+-------------+
| 1 | SIMPLE | login_user | p1 | ALL | <null> | <null> | <null> | <null> | 3 | 33.33 | Using where |
+----+-------------+------------+------------+------+---------------+--------+---------+--------+------+----------+-------------+
1 row in set
Time: 0.020s

分区字段进行区间查询1

读取p1,p2,p3,p4,p5,p6,p7,p8 , 控制查询范围很重要，尽可能减少区间

mysql root@localhost:my_database> explain select * from login_user where create_date between '2018-10-01 01:01:03' and '2018-10-08 01:08:03' ;
+----+-------------+------------+-------------------------+------+---------------+--------+---------+--------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+-------------------------+------+---------------+--------+---------+--------+------+----------+-------------+
| 1 | SIMPLE | login_user | p1,p2,p3,p4,p5,p6,p7,p8 | ALL | <null> | <null> | <null> | <null> | 27 | 11.11 | Using where |
+----+-------------+------------+-------------------------+------+---------------+--------+---------+--------+------+----------+-------------+
1 row in set
Time: 0.019s

分区字段进行分区查询2

读取p1,p2,p3 ,不影响其他分区

mysql root@localhost:my_database> explain select * from login_user where create_date between '2018-10-01 01:01:03' and '2018-10-03 01:08:03' ;
+----+-------------+------------+------------+------+---------------+--------+---------+--------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+--------+---------+--------+------+----------+-------------+
| 1 | SIMPLE | login_user | p1,p2,p3 | ALL | <null> | <null> | <null> | <null> | 9 | 11.11 | Using where |
+----+-------------+------------+------------+------+---------------+--------+---------+--------+------+----------+-------------+
1 row in set
Time: 0.018s

分区字段进行分组统计

读取p1,p2,p3 ,不影响其他分区

mysql root@localhost:my_database> explain select id , count(1) from login_user where create_date between '2018-10-01 01:01:03' and '2018-10-03 01:08:03' group by id ;

+----+-------------+------------+------------+-------+---------------+-----+---------+--------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+-----+---------+--------+------+----------+--------------------------+
| 1 | SIMPLE | login_user | p1,p2,p3 | index | id | id | 10 | <null> | 9 | 11.11 | Using where; Using index |
+----+-------------+------------+------------+-------+---------------+-----+---------+--------+------+----------+--------------------------+
1 row in set
Time: 0.021s

使用分区字段进行小于查询

直接读取p1

mysql root@localhost:my_database> explain delete from login_user where create_date < '2018-10-02 00:00:00'
+----+-------------+------------+------------+------+---------------+--------+---------+--------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+--------+---------+--------+------+----------+-------------+
| 1 | DELETE | login_user | p1 | ALL | <null> | <null> | <null> | <null> | 3 | 100.0 | Using where |
+----+-------------+------------+------------+------+---------------+--------+---------+--------+------+----------+-------------+
1 row in set
Time: 0.023s

插入测试

直接插入对应的分区表

mysql root@localhost:my_database> explain insert into login_user(id,user_name,create_date) values (5,'test','2018-10-05 01:05:03');
+----+-------------+------------+------------+------+---------------+--------+---------+--------+--------+----------+--------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+--------+---------+--------+--------+----------+--------+
| 1 | INSERT | login_user | p5 | ALL | <null> | <null> | <null> | <null> | <null> | <null> | <null> |
+----+-------------+------------+------------+------+---------------+--------+---------+--------+--------+----------+--------+
1 row in set
Time: 0.018s

分区表扩容及移动方案.

因为分区表有数量限制(1024),当数据接近1024份时，需要进行老数据的归档(将老数据迁移出分区表), 并且建立新的分区用来存放接下来的数据范围.

ALTER TABLE login_user PARTITION BY RANGE (UNIX_TIMESTAMP(create_date))
(
PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2018-10-02 00:00:00') ),
PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2018-10-03 00:00:00') )
);

分区表DBA相关

LVM磁盘动态扩容

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 204,590评论 6赞 478
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 86,808评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 151,151评论 0赞 337
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,779评论 1赞 277
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,773评论 5赞 367
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,656评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,022评论 3赞 398
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,678评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 41,038评论 1赞 299
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,659评论 2赞 321
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,756评论 1赞 330
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,411评论 4赞 321
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,005评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,973评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,203评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,053评论 2赞 350
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,495评论 2赞 343