美文网首页
mysql分区表测试

mysql分区表测试

作者: youkale | 来源:发表于2018-11-26 21:01 被阅读21次

    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磁盘动态扩容

    相关文章

      网友评论

          本文标题：mysql分区表测试

          本文链接：https://www.haomeiwen.com/subject/avuiqqtx.html

          延伸阅读

          深度阅读

          • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

          栏目导航

          热点阅读

          关于我们|服务条款|联系我们|mysql分区表测试|投稿指南|网站地图|RSS订阅|排版工具|手机版

          提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

          Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

          备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

          本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

          所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！