MySQL表设计与分区实践-优快云博客

本文详细介绍了MySQL中表的设计方法以及如何通过分区提高查询效率。包括创建表时的字段定义、注释添加以及如何设置分区策略等内容。

CREATE TABLE `tr` (
`id` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`purchased` year DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (purchased)
(PARTITION p0 VALUES LESS THAN (2016) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (2017) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (2018) ENGINE = InnoDB) */

DROP TABLE IF EXISTS `act_ks_sjtm`;
CREATE TABLE `act_ks_sjtm` (
  `id` varchar(50) NOT NULL COMMENT '主键',
  `ks_id` varchar(50) DEFAULT NULL COMMENT '考试ID',
  `sjxx_id` varchar(50) DEFAULT NULL COMMENT '试卷ID',
  `txxx_id` varchar(50) DEFAULT NULL COMMENT '题型信息ID',
  `zjgz_id` varchar(50) DEFAULT NULL COMMENT '组卷规则ID',
  `zjgz_mx_id` varchar(50) DEFAULT NULL COMMENT '组件规则明细ID',
  `tmxx_id` varchar(50) DEFAULT NULL COMMENT '题目信息ID',
  `create_time` varchar(50) DEFAULT NULL COMMENT '创建时间',
  `year` year(4) DEFAULT NULL COMMENT '年份用于分库',
  PRIMARY KEY (`id`,`year`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='试卷题目'

/*!50100 PARTITION BY RANGE (year)  
(PARTITION p00 VALUES LESS THAN (2018) ENGINE = InnoDB,
 PARTITION p01 VALUES LESS THAN (2019) ENGINE = InnoDB,
 PARTITION p02 VALUES LESS THAN (2020) ENGINE = InnoDB,
 PARTITION p03 VALUES LESS THAN (2021) ENGINE = InnoDB,
 PARTITION p04 VALUES LESS THAN (2022) ENGINE = InnoDB,
 PARTITION p05 VALUES LESS THAN (2023) ENGINE = InnoDB,
 PARTITION p06 VALUES LESS THAN (2024) ENGINE = InnoDB,
 PARTITION p07 VALUES LESS THAN (2025) ENGINE = InnoDB,
 PARTITION p08 VALUES LESS THAN (2026) ENGINE = InnoDB,
 PARTITION p09 VALUES LESS THAN (2027) ENGINE = InnoDB,
 PARTITION p10 VALUES LESS THAN (2028) ENGINE = InnoDB) */

注意：分区字段必须包含在主键中，形成联合主键

--查询表分区

SELECT
partition_name part,
partition_expression expr,
partition_description descr,
FROM_DAYS(partition_description) lessthan_sendtime,
table_rows
FROM
INFORMATION_SCHEMA.partitions
WHERE
TABLE_SCHEMA = SCHEMA()
AND TABLE_NAME='tets_tb';

http://yuelangyc.iteye.com/blog/2006880

转载于:https://my.oschina.net/cccyb/blog/917806