mysql 分区实践

最新推荐文章于 2025-09-08 20:55:49 发布

转载最新推荐文章于 2025-09-08 20:55:49 发布 · 62 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://segmentfault.com/a/1190000009037315

文章标签：

#数据库

本文介绍MySQL中如何创建分区表，并通过实例演示了分区表与非分区表在大量数据插入过程中的区别。首先展示了如何创建基于范围的分区表，并创建了一个未分区的普通表作为对比。接着，使用存储过程分别向这两个表中插入500万条数据。

是否支持分区：

mysql> show variables like ‘%partition%’;
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| have_partitioning | YES |
+——————-+——-+

---1--- 创建分区表

CREATE TABLE `cy_test_vouchers_nopartition` (
  `cvid` INT(11) NOT NULL AUTO_INCREMENT,
  `extcredits1` VARCHAR(11) DEFAULT '0' COMMENT '抵用券 地多少钱/折扣上限',
  `discount` VARCHAR(20) DEFAULT '0' COMMENT '折扣比例',
  `transid` VARCHAR(11) DEFAULT NULL COMMENT 'transid',
  KEY `cvid` (`cvid`)
)
PARTITION BY RANGE COLUMNS(cvid) (
    PARTITION p0 VALUES LESS THAN (100000),
    PARTITION p1 VALUES LESS THAN (200000),
    PARTITION p2 VALUES LESS THAN (300000),
    PARTITION p3 VALUES LESS THAN (400000),
    PARTITION p4 VALUES LESS THAN (500000),
    PARTITION p5 VALUES LESS THAN (600000),
    PARTITION p6 VALUES LESS THAN (700000),
    PARTITION p7 VALUES LESS THAN (800000),
    PARTITION p8 VALUES LESS THAN (900000),
    PARTITION p9 VALUES LESS THAN (1000000),
    PARTITION p10 VALUES LESS THAN (1100000),
    PARTITION p11 VALUES LESS THAN (1200000),
    PARTITION p12 VALUES LESS THAN (1300000),
    PARTITION p13 VALUES LESS THAN (1400000),
    PARTITION p14 VALUES LESS THAN (1500000),
    PARTITION p15 VALUES LESS THAN (1600000),
    PARTITION p16 VALUES LESS THAN (1700000),
    PARTITION p17 VALUES LESS THAN (1800000),
    PARTITION p18 VALUES LESS THAN (1900000),
    PARTITION p19 VALUES LESS THAN MAXVALUE
);

---2--- 为了做对比，创建普通表（未做分区）

CREATE TABLE `cy_test_vouchers_nopartition` (
  `cvid` INT(11) NOT NULL AUTO_INCREMENT,
  `extcredits1` VARCHAR(11) DEFAULT '0' COMMENT '',
  `discount` VARCHAR(20) DEFAULT '0' COMMENT '',
  `transid` VARCHAR(11) DEFAULT NULL COMMENT 'transid',
  KEY `cvid` (`cvid`)
)ENGINE=InnoDB AUTO_INCREMENT=200771 DEFAULT CHARSET=utf8 COMMENT='test'

---3--- 用存储过程分别向两个表中插入500W条数据
向分区表插入：

DELIMITER $$
CREATE
    PROCEDURE `test`.`produce_part`()
    BEGIN
        DECLARE v INT;
        SET v = 1;
            WHILE v < 5000000
            DO
            INSERT INTO test.cy_test_vouchers_partition
            VALUES (v,'testing partitions',v+10,1);
             SET v = v + 1;
            END WHILE;
    END$$

DELIMITER ;

向普通表插入：

DELIMITER $$
CREATE
    PROCEDURE `test`.`produce_nopart`()
    BEGIN
        DECLARE v INT;
        SET v = 1;
            WHILE v < 5000000
            DO
            INSERT INTO test.cy_test_vouchers_nopartition
            VALUES (v,'testing partitions',v+10,1);
             SET v = v + 1;
            END WHILE;
    END$$

DELIMITER ;

---4--- 执行PROCEDURE

call produce_part();
call produce_nopart();