MYsql分区使用

作者官方网站:http://www.wxl568.cn

  几种获取MySQL分区表信息的常用方法

SHOW CREATE TABLE 可以查看创建分区表的CREATE语句
SHOW TABLE STATUS 可以查看表是否为分区表
查看INFORMATION_SCHEMA.PARTITIONS表 可以查看表具有哪几个分区、分区的方法、分区中数据的记录数等重要信息
select * from INFORMATION_SCHEMA.PARTITIONS
EXPLAIN PARTITIONS SELECT 查看select语句怎样使用分区

分区优点

1，分区可以分在多个磁盘，存储更大一点

2，根据查找条件，也就是where后面的条件，查找只查找相应的分区不用全部查找了

3，进行大数据搜索时可以进行并行处理。

4，跨多个磁盘来分散数据查询，来获得更大的查询吞吐量

下面实例整理

CREATE TABLE `tt_clock_log_wq` (
    `ID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `EMP_CODE` VARCHAR(20) NOT NULL COMMENT '工号',
    `EQUIPMENT_NO` VARCHAR(255) NOT NULL COMMENT '设备编码',
    `MAC_ADDRESS` VARCHAR(50) NULL DEFAULT NULL COMMENT 'MAC地址（IOS丰声无法获取）',
    `CLOCK_TIME` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '打卡时间',
    `LONGITUDE` DOUBLE NOT NULL COMMENT '经度',
    `LATITUDE` DOUBLE NOT NULL COMMENT '纬度',
    `ADDRESS_ID` BIGINT(20) NULL DEFAULT NULL COMMENT '打卡物理地点ID（外勤打卡的情况下为空）',
    `STATUS` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '状态1、成功 2、待审核 3、已提交ECP 4、失败',
    `PERSON_TYPE` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '1为SAP，2为PMP',
    `REMARK` VARCHAR(255) NULL DEFAULT NULL COMMENT '备注',
    `OUTSIDE_CLOCK_ADDRESS` VARCHAR(255) NULL DEFAULT NULL COMMENT '外勤打卡地址',
    `SYNC_STATUS` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '是否已经同步，0为未同步，1为同步成功，2为同步失败，3为准备同步，4为同步中',
    `SYNC_TM` DATETIME NULL DEFAULT NULL COMMENT '同步时间',
    `mobile_Root` INT(1) NULL DEFAULT NULL COMMENT '是否越狱/root权限 0代表正常/1代表root/2代表没有获取到',
    `phone_Mode` INT(1) NULL DEFAULT NULL COMMENT '0代表安卓/1代表ios',
    `RUN_ID` VARCHAR(50) NULL DEFAULT NULL COMMENT 'ECP流程_RUN_ID',
    `ECP_STATUS` VARCHAR(10) NULL DEFAULT NULL COMMENT 'ECP回调状态 2(归档)、4(撤销)、5（驳回到发起节点）、10（删除）',
    `SYNC_FAIL_NUM` TINYINT(4) NULL DEFAULT '0' COMMENT '同步失败次数',
    `CREATE_TM` DATETIME NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `BRAND_TYPE` VARCHAR(100) NULL DEFAULT NULL,
    `RESERVE1` VARCHAR(100) NULL DEFAULT NULL,
    `RESERVE2` VARCHAR(100) NULL DEFAULT NULL,
    `RESERVE3` VARCHAR(100) NULL DEFAULT NULL,
    `sapEtl` INT(4) NULL DEFAULT '0',
    `RESERVE4` VARCHAR(100) NULL DEFAULT NULL COMMENT '备用4状态',
    PRIMARY KEY (`ID`,`CREATE_TM`),
    INDEX `IDX_TT_CLOCK_LOG_TIME_EMP` (`CLOCK_TIME`, `EMP_CODE`),
    INDEX `IDX_TT_CLOCK_LOG_EMP` (`EMP_CODE`, `CLOCK_TIME`) USING BTREE
)
COMMENT='GPS打卡记录表'
COLLATE='utf8mb4_general_ci'
/*!50100 PARTITION BY RANGE  COLUMNS(CREATE_TM)
(PARTITION p20181103 VALUES LESS THAN ('2018-11-03') ENGINE = InnoDB,
 PARTITION p20181104 VALUES LESS THAN ('2018-11-04') ENGINE = InnoDB,
 PARTITION p20181105 VALUES LESS THAN ('2018-11-05') ENGINE = InnoDB,
 PARTITION p20181106 VALUES LESS THAN ('2018-11-06') ENGINE = InnoDB,
 PARTITION p20181107 VALUES LESS THAN ('2018-11-07') ENGINE = InnoDB,
 PARTITION p20181108 VALUES LESS THAN ('2018-11-08') ENGINE = InnoDB,
 PARTITION p20181109 VALUES LESS THAN ('2018-11-09') ENGINE = InnoDB,
 PARTITION p20181110 VALUES LESS THAN ('2018-11-10') ENGINE = InnoDB,
 PARTITION p20181111 VALUES LESS THAN ('2018-11-11') ENGINE = InnoDB,
 PARTITION p20181112 VALUES LESS THAN ('2018-11-12') ENGINE = InnoDB,
 PARTITION p20181113 VALUES LESS THAN ('2018-11-13') ENGINE = InnoDB,
 PARTITION p20181114 VALUES LESS THAN ('2018-11-14') ENGINE = InnoDB,
 PARTITION p20181115 VALUES LESS THAN ('2018-11-15') ENGINE = InnoDB,
 PARTITION p20181116 VALUES LESS THAN ('2018-11-16') ENGINE = InnoDB,
 PARTITION p20181117 VALUES LESS THAN ('2018-11-17') ENGINE = InnoDB,
 PARTITION p20181118 VALUES LESS THAN ('2018-11-18') ENGINE = InnoDB,
 PARTITION p20181119 VALUES LESS THAN ('2018-11-19') ENGINE = InnoDB,
 PARTITION p20181120 VALUES LESS THAN ('2018-11-20') ENGINE = InnoDB,
 PARTITION p20181121 VALUES LESS THAN ('2018-11-21') ENGINE = InnoDB,
 PARTITION p20181122 VALUES LESS THAN ('2018-11-22') ENGINE = InnoDB,
 PARTITION p20181123 VALUES LESS THAN ('2018-11-23') ENGINE = InnoDB,
 PARTITION p20181124 VALUES LESS THAN ('2018-11-24') ENGINE = InnoDB,
 PARTITION p20181125 VALUES LESS THAN ('2018-11-25') ENGINE = InnoDB,
 PARTITION p20181126 VALUES LESS THAN ('2018-11-26') ENGINE = InnoDB,
 PARTITION p20181127 VALUES LESS THAN ('2018-11-27') ENGINE = InnoDB,
 PARTITION p20181128 VALUES LESS THAN ('2018-11-28') ENGINE = InnoDB,
 PARTITION p20181129 VALUES LESS THAN ('2018-11-29') ENGINE = InnoDB,
 PARTITION p20181130 VALUES LESS THAN ('2018-11-30') ENGINE = InnoDB,
 PARTITION p20181201 VALUES LESS THAN ('2018-12-01') ENGINE = InnoDB,
 PARTITION p20181202 VALUES LESS THAN ('2018-12-02') ENGINE = InnoDB,
 PARTITION p20181203 VALUES LESS THAN ('2018-12-03') ENGINE = InnoDB,
 PARTITION p20181204 VALUES LESS THAN ('2018-12-04') ENGINE = InnoDB,
 PARTITION p20181205 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB)  */;