show PROCESSLIST 查看进程
explain 查看执行计划
explain PARTITIONS 带分区的执行计划
选择小表作为主表
能用join尽量不用left join
优化查询条件的顺序
关联、查询、排序字段建索引
查询字段建分区
RANGE 分区
ALTER TABLE `t_opportunity_follow`
PARTITION BY RANGE (to_days(gjdate)) (
PARTITION p201612 VALUES LESS THAN (to_days('2017-01-01')),
PARTITION p201701 VALUES LESS THAN (to_days('2017-02-01')),
PARTITION p9999 VALUES LESS THAN (MAXVALUE) );
alter table range_columns
PARTITION BY RANGE COLUMNS(hiredate) (
PARTITION p1 VALUES LESS THAN ( '20151202' ),
PARTITION p2 VALUES LESS THAN ( '20151203' ),
PARTITION p3 VALUES LESS THAN ( '20151204' ),
PARTITION p4 VALUES LESS THAN ( '20151205' ),
PARTITION p5 VALUES LESS THAN ( '20151206' ),
PARTITION p6 VALUES LESS THAN ( '20151207' ),
PARTITION p7 VALUES LESS THAN ( '20151208' ),
PARTITION p8 VALUES LESS THAN ( '20151209' ),
PARTITION p9 VALUES LESS THAN ( '20151210' ),
PARTITION p10 VALUES LESS THAN ('20151211' )
);
LIST分区
ALTER TABLE expenses
PARTITION BY LIST COLUMNS (category)
(
PARTITION p01 VALUES IN ( 'lodging', 'food'),
PARTITION p02 VALUES IN ( 'flights', 'ground transportation'),
PARTITION p03 VALUES IN ( 'leisure', 'customer entertainment'),
PARTITION p04 VALUES IN ( 'communications'),
PARTITION p05 VALUES IN ( 'fees')
);
hash分区
CREATETABLE hash_datetime (
id INT,
hiredate DATETIME)
PARTITION BY HASH( TO_DAYS(hiredate) )
PARTITIONS 10;
TIMESTAMP类型使用UNIX_TIMESTAMP方法
key分区
alter table t_report
PARTITION BY key( Project_ID )
PARTITIONS 20;
删除分区
ALTER TABLE employees DROP PARTITION p0；删处分区和数据
alter table …remove partitioning 删除分区不删除数据
