mysql的分区和索引详解_Mysql分区和索引记录

分区

前提：当数据量达到800w的时候，用create_date进行设置索引查询，也需要花费5s以上，所以还得进行分区操作，达到更快的效果。设置好分区后，查询就是0.5s。

开始操作：用create_date创建分区，需要把create_date设置为主键

所以，首先，建id和create_date的联合主键

CREATE TABLE `t_service_object` (

`id` varchar(64) NOT NULL,

`type` int(1) DEFAULT NULL COMMENT '类型 1 服务 2 包 3 类 4 方法',

`object_name` varchar(255) DEFAULT NULL COMMENT '名称',

`parent_id` varchar(64) DEFAULT NULL,

`create_date` date NOT NULL,

`is_coverage` int(1) DEFAULT NULL COMMENT '0 未被覆盖 1被覆盖',

PRIMARY KEY (`id`,`create_date`) USING BTREE

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

创建分区：

如果数据量很大，在有数据执行这个，会等待比较久。750w数据等待了好几分钟。

ALTER TABLE t_service_object partition by range(TO_DAYS(create_date))( PARTITION BY RANGE (YEAR(sale_date)*100+MONTH(sale_date)) (

PARTITION Jan VALUES less than(TO_DAYS('2020-01-31')),

PARTITION Feb VALUES less than(TO_DAYS('2020-02-29')),

PARTITION Mar VALUES less than(TO_DAYS('2020-03-31')),

PARTITION Apr VALUES less than(TO_DAYS('2020-04-30')),

PARTITION May VALUES less than(TO_DAYS('2020-05-31')),

PARTITION Jun VALUES less than(TO_DAYS('2020-06-30')),

PARTITION Jul VALUES less than(TO_DAYS('2020-07-31')),

PARTITION Aug VALUES less than(TO_DAYS('2020-08-31')),

PARTITION Sept VALUES less than(TO_DAYS('2020-09-30')),

PARTITION Oct VALUES less than(TO_DAYS('2020-10-31')),

PARTITION Nov VALUES less than(TO_DAYS('2020-11-30')),

PARTITION Dece VALUES less than(TO_DAYS('2020-12-31'))

);

查询分区：

select

partition_name part,

partition_expression expr,

partition_description descr,

table_rows

from information_schema.partitions where

table_schema = schema()

and table_name='t_service_object';

执行完后，有新插入数据会自动到相应的分区里面去。

另一种创建分区方法：

PARTITION BY RANGE (YEAR(sale_date)*100+MONTH(sale_date)) (

PARTITION p201001 VALUES LESS THAN (201002),

PARTITION p201002 VALUES LESS THAN (201003),

PARTITION p201003 VALUES LESS THAN (201004),

PARTITION p201004 VALUES LESS THAN (201005),

PARTITION p201005 VALUES LESS THAN (201006),

PARTITION p201006 VALUES LESS THAN (201007),

PARTITION p201007 VALUES LESS THAN (201008),

PARTITION p201008 VALUES LESS THAN (201009),

PARTITION p201009 VALUES LESS THAN (201010),

PARTITION pcatchall VLAUES LESS THAN MAXVALUE );

索引

创建组合索引

ALTER TABLE t_service_object_two ADD INDEX index_titme_time

(id,create_date)

创建主键索引

ALTER TABLE t_service_object_two ADD PRIMARY KEY (

create_date )

普通索引

ALTER TABLE t_service_object_two ADD INDEX index_name (

create_date )

查看索引

show index from t_service_object_two

删除索引

drop index index_titme_time on t_service_object_two;

标签：less,分区,PARTITION,DAYS,索引,VALUES,Mysql,date,than

来源： https://blog.csdn.net/qq_41583016/article/details/112488834