MYsql分区使用

最新推荐文章于 2024-10-14 09:51:30 发布

原创最新推荐文章于 2024-10-14 09:51:30 发布 · 472 阅读

0 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

24 篇文章

订阅专栏

本文详细介绍了MySQL中分区表的创建及优化方法，包括通过多种方式获取分区表信息，如SHOW CREATE TABLE、SHOW TABLE STATUS及INFORMATION_SCHEMA.PARTITIONS表等。文章还列举了一个具体的GPS打卡记录表分区实例，展示了如何利用CREATE_TIME进行范围分区，提高数据检索效率。

作者官方网站: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) */;