mysql 分区实践

是否支持分区：

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();