Mysql的分区

最新推荐文章于 2025-04-18 09:14:16 发布

weixin_30437337

最新推荐文章于 2025-04-18 09:14:16 发布

阅读量51

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/bing-yu12/p/6635398.html

本文介绍了一个用于存储用户间分享消息的数据表结构，包括表字段的设计及其含义，并使用了多种索引以提高查询效率，同时通过时间范围分区来优化大量数据的管理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

DROP TABLE
IF EXISTS et_msg_201703;

CREATE TABLE
IF NOT EXISTS et_user_msg_201703 (
    `mid` BIGINT auto_increment ,
    `send_username` VARCHAR (255) NOT NULL COMMENT '发送消息者',
    `receive_username` VARCHAR (255) NOT NULL COMMENT '接受消息者,发送者发送的对象',
    `video_source_id` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '分享视频的来源地址:0,youtube;1,facebook;2,ted',
    `video_thumb_url` VARCHAR (255),
    `videoId` VARCHAR (255) DEFAULT '' COMMENT '视频的videoId',
    `share_url` VARCHAR (255),
    `share_title` VARCHAR (255),
    `share_text` TEXT,
    `share_time` INT (11),
    `share_status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0,未读;1,已读',
    `share_type` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0,分享类消息;1,聊天类消息',
    KEY (send_username),
    PRIMARY key (mid,share_time),
    KEY (receive_username),
    KEY (
        send_username,
        receive_username,
        share_status
    )
) ENGINE = INNODB charset = utf8 PARTITION BY RANGE (share_time)(
    PARTITION p1
    VALUES
        LESS THAN (1488322800),
        PARTITION p2
    VALUES
        LESS THAN (1488409200),
        PARTITION p3
    VALUES
        LESS THAN (1488495600),
        PARTITION p4
    VALUES
        LESS THAN (1488582000),
        PARTITION p5
    VALUES
        LESS THAN (1488668400),
        PARTITION p6
    VALUES
        LESS THAN (1488754800),
        PARTITION p7
    VALUES
        LESS THAN (1488841200),
        PARTITION p8
    VALUES
        LESS THAN (1488927600),
        PARTITION p9
    VALUES
        LESS THAN (1489014000),
        PARTITION p10
    VALUES
        LESS THAN (1489100400),
        PARTITION p11
    VALUES
        LESS THAN (1489186800),
        PARTITION p12
    VALUES
        LESS THAN (1489273200),
        PARTITION p13
    VALUES
        LESS THAN (1489359600),
        PARTITION p14
    VALUES
        LESS THAN (1489446000),
        PARTITION p15
    VALUES
        LESS THAN (1489532400),
        PARTITION p16
    VALUES
        LESS THAN (1489618800),
        PARTITION p17
    VALUES
        LESS THAN (1489705200),
        PARTITION p18
    VALUES
        LESS THAN (1489791600),
        PARTITION p19
    VALUES
        LESS THAN (1489878000),
        PARTITION p20
    VALUES
        LESS THAN (1489964400),
        PARTITION p21
    VALUES
        LESS THAN (1490050800),
        PARTITION p22
    VALUES
        LESS THAN (1490137200),
        PARTITION p23
    VALUES
        LESS THAN (1490223600),
        PARTITION p24
    VALUES
        LESS THAN (1490310000),
        PARTITION p25
    VALUES
        LESS THAN (1490396400),
        PARTITION p26
    VALUES
        LESS THAN (1490482800),
        PARTITION p27
    VALUES
        LESS THAN (1490565600),
        PARTITION p28
    VALUES
        LESS THAN (1490652000),
        PARTITION p29
    VALUES
        LESS THAN (1490738400),
        PARTITION p30
    VALUES
        LESS THAN (1490824800),
        PARTITION p31
    VALUES
        LESS THAN (1490911200)
);