MySQL添加分区添加索引

最新推荐文章于 2025-06-07 08:36:45 发布

boredbird32

最新推荐文章于 2025-06-07 08:36:45 发布

阅读量2.9k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.youkuaiyun.com/boredbird32/article/details/51067753

MySQL 专栏收录该内容

4 篇文章

订阅专栏

本文介绍了名为rpt_all_order_list_d的订单报表表结构，包括字段定义、注释及索引设置。此外，还详细说明了如何为该表添加基于时间范围的分区，以提高查询效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、创建表

CREATE TABLE `rpt_all_order_list_d` (
  `add_time` datetime DEFAULT NULL COMMENT '添加时间',
  `account_no` varchar(32) DEFAULT NULL COMMENT '会员ID',
  `order_id` varchar(32) DEFAULT NULL COMMENT '订单ID',
  `fin_product_id` varchar(32) DEFAULT NULL COMMENT '金融产品ID',
  `trade_type` bigint(20) DEFAULT NULL COMMENT '1:申购;',
  `status_code` varchar(1) DEFAULT NULL COMMENT 'Y:成功，N:失败',
  `trade_money` decimal(16,2) DEFAULT NULL COMMENT '交易金额',
  KEY `index_add_time` (`add_time`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8

2、往现有的表中添加分区

ALTER TABLE `rpt_all_order_list_d`  
PARTITION BY RANGE (TO_DAYS(add_time))  
 (PARTITION p0 VALUES LESS THAN (TO_DAYS('2015-01-01')),  
 PARTITION p1 VALUES LESS THAN (TO_DAYS('2015-02-01')) ,  
 PARTITION p2 VALUES LESS THAN (TO_DAYS('2015-03-01')) ,  
 PARTITION p3 VALUES LESS THAN (TO_DAYS('2015-04-01')) ,  
  PARTITION p4 VALUES LESS THAN (TO_DAYS('2015-05-01')) ,  
  PARTITION p5 VALUES LESS THAN (TO_DAYS('2015-06-01')) ,  
  PARTITION p6 VALUES LESS THAN (TO_DAYS('2015-07-01')) ,  
  PARTITION p7 VALUES LESS THAN (TO_DAYS('2015-08-01')) ,  
  PARTITION p8 VALUES LESS THAN (TO_DAYS('2015-09-01')) ,  
  PARTITION p9 VALUES LESS THAN (TO_DAYS('2015-10-01')) ,  
  PARTITION p10 VALUES LESS THAN (TO_DAYS('2015-11-01')),  
  PARTITION p11 VALUES LESS THAN (TO_DAYS('2015-12-01')), 
PARTITION p12 VALUES LESS THAN (TO_DAYS('2016-01-01')),  
 PARTITION p13 VALUES LESS THAN (TO_DAYS('2016-02-01')) ,  
 PARTITION p14 VALUES LESS THAN (TO_DAYS('2016-03-01')) ,  
 PARTITION p15 VALUES LESS THAN (TO_DAYS('2016-04-01')) ,  
  PARTITION p16 VALUES LESS THAN (TO_DAYS('2016-05-01')) ,  
  PARTITION p17 VALUES LESS THAN (TO_DAYS('2016-06-01')) ,  
  PARTITION p18 VALUES LESS THAN (TO_DAYS('2016-07-01')) ,  
  PARTITION p19 VALUES LESS THAN (TO_DAYS('2016-08-01')) ,  
  PARTITION p20 VALUES LESS THAN (TO_DAYS('2016-09-01')) ,  
  PARTITION p21 VALUES LESS THAN (TO_DAYS('2016-10-01')) ,  
  PARTITION p22 VALUES LESS THAN (TO_DAYS('2016-11-01')),  
  PARTITION p23 VALUES LESS THAN (TO_DAYS('2016-12-01')),   
PARTITION p24 VALUES LESS THAN (TO_DAYS('2017-01-01')),  
 PARTITION p25 VALUES LESS THAN (TO_DAYS('2017-02-01')) ,  
 PARTITION p26 VALUES LESS THAN (TO_DAYS('2017-03-01')) ,  
 PARTITION p27 VALUES LESS THAN (TO_DAYS('2017-04-01')) ,  
  PARTITION p28 VALUES LESS THAN (TO_DAYS('2017-05-01')) ,  
  PARTITION p29 VALUES LESS THAN (TO_DAYS('2017-06-01')) ,  
  PARTITION p30 VALUES LESS THAN (TO_DAYS('2017-07-01')) ,  
  PARTITION p31 VALUES LESS THAN (TO_DAYS('2017-08-01')) ,  
  PARTITION p32 VALUES LESS THAN (TO_DAYS('2017-09-01')) ,  
  PARTITION p33 VALUES LESS THAN (TO_DAYS('2017-10-01')) ,  
  PARTITION p34 VALUES LESS THAN (TO_DAYS('2017-11-01')),  
  PARTITION p35 VALUES LESS THAN (TO_DAYS('2017-12-01')), 
  PARTITION p36 VALUES LESS THAN MAXVALUE );

3、添加索引

ALTER TABLE rpt_all_order_list_d  ADD INDEX index_add_time (add_time)
ALTER TABLE rpt_fin_reg_auth_all  ADD INDEX index_auth_time (auth_time)