1）结合业务场景选择分区键，避免跨分区查询

2）对分区表进行查询最好在where 从句中包含分区键

3）具有主键或唯一索引的表，主键或者唯一索引必须是分区键的一部分

列子

CREATE TABLE customer_login_log(

customer_id int UNSIGNED NOT NULL,

login_time DATETIME NOT NULL,

login_ip int UNSIGNED not null,

login_type TINYINT not NULL

)ENGINE =INNODB

PARTITION BY RANGE (YEAR(login_time))(

PARTITION p0 VALUES less than(2020),

PARTITION p1 VALUES less than(2021),

PARTITION p2 VALUES less than(2022)

);

INSERT INTO customer_login_log (customer_id,login_time,login_ip,login_type) VALUES 

(1001,'2020-6-22',0,1),(1001,'2021-5-22',0,1);

分区的查询

SELECT table_name,partition_name,partition_description,

table_rows from information_schema.`PARTITIONS`

where table_name ='customer_login_log'

新增一个分区

ALTER TABLE customer_login_log ADD PARTITION 

(PARTITION p3 VALUES less than(2023))

删除一个分区

alter TABLE  customer_login_log DROP PARTITION p2

建立一个存储过期的数据

CREATE TABLE arch_customer_login_log(

customer_id int UNSIGNED NOT NULL,

login_time DATETIME NOT NULL,

login_ip int UNSIGNED not null,

login_type TINYINT not NULL

)ENGINE =INNODB

将 p1分区的数据移植

alter TABLE customer_login_log exchange PARTITION p1 WITH TABLE

arch_customer_login_log;