mysql 5.1 分区_mysql 5.1.51 按timestamp 字段分区不支持分区裁剪？？

最新推荐文章于 2022-05-14 22:45:59 发布

懒人美美

最新推荐文章于 2022-05-14 22:45:59 发布

阅读量177

点赞数

文章标签： mysql 5.1 分区

本文链接：https://blog.csdn.net/weixin_42298427/article/details/113905050

版权

MySQL 分区表查询优化时间戳贷款优化日志

关键词由CSDN通过智能技术生成

创建表：

loan_opt_log_pp | CREATE TABLE `loan_opt_log_pp` (

`id` bigint(20) NOT NULL,

`apply_id` bigint(20) DEFAULT NULL,

`admin_id` int(11) DEFAULT NULL,

`log_belong` char(1) DEFAULT NULL,

`remark` varchar(512) DEFAULT NULL,

`create_dt` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

`update_dt` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

`log_type` char(4) DEFAULT NULL,

`refuse_reason` varchar(512) DEFAULT NULL,

KEY `create_dt1` (`create_dt`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

/*!50100 PARTITION BY RANGE (UNIX_TIMESTAMP(create_dt))

(PARTITION p201012 VALUES LESS THAN (1293811200) ENGINE = InnoDB,

PARTITION p201101 VALUES LESS THAN (1301587200) ENGINE = InnoDB,

PARTITION p201102 VALUES LESS THAN (1309449600) ENGINE = InnoDB,

PARTITION p201103 VALUES LESS THAN (1317398400) ENGINE = InnoDB,

PARTITION p201104 VALUES LESS THAN (1325347200) ENGINE = InnoDB,

PARTITION p201201 VALUES LESS THAN (1333209600) ENGINE = InnoDB,

PARTITION p201202 VALUES LESS THAN (1341072000) ENGINE = InnoDB,

PARTITION p201203 VALUES LESS THAN (1349020800) ENGINE = InnoDB,

PARTITION p201204 VALUES LESS THAN (1356969600) ENGINE = InnoDB,

PARTITION p201301 VALUES LESS THAN (1364745600) ENGINE = InnoDB,

PARTITION p201302 VALUES LESS THAN (1372608000) ENGINE = InnoDB,

PARTITION p201303 VALUES LESS THAN (1380556800) ENGINE = InnoDB,

PARTITION p201304 VALUES LESS THAN (1388505600) ENGINE = InnoDB,

PARTITION p201401 VALUES LESS THAN (1396281600) ENGINE = InnoDB,

PARTITION p201402 VALUES LESS THAN (1404144000) ENGINE = InnoDB,

PARTITION p201403 VALUES LESS THAN (1412092800) ENGINE = InnoDB,

PARTITION p201404 VALUES LESS THAN (1420041600) ENGINE = InnoDB,

PARTITION p201501 VALUES LESS THAN (1427817600) ENGINE = InnoDB,

PARTITION p201502 VALUES LESS THAN (1435680000) ENGINE = InnoDB,

PARTITION p201503 VALUES LESS THAN (1443628800) ENGINE = InnoDB,

PARTITION p201504 VALUES LESS THAN (1451577600) ENGINE = InnoDB,

PARTITION p_max VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ |

分区裁剪：

mysql> explain partitions select * from loan_opt_log_pp where create_dt =timestamp('2010-12-22 00:00:00');

+----+-------------+-----------------+------------+------+---------------+------------+---------+-------+------+-------------+

+----+-------------+-----------------+------------+------+---------------+------------+---------+-------+------+-------------+

| 1 | SIMPLE | loan_opt_log_pp | p201012 | ref | create_dt1 | create_dt1 | 4 | const | 1 | Using where |

+----+-------------+-----------------+------------+------+---------------+------------+---------+-------+------+-------------+

1 row in set (0.00 sec)

mysql> explain partitions select * from loan_opt_log_pp

-> where create_dt between timestamp('2010-08-22 00:00:00') and timestamp('2011-08-22 00:00:00');

+----+-------------+-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+---------------+------+---------+------+--------+-------------+

1 row in set (0.00 sec)