show PROCESSLIST 查看進(jìn)程
explain 查看執(zhí)行計(jì)劃
explain PARTITIONS 帶分區(qū)的執(zhí)行計(jì)劃
選擇小表作為主表
能用join盡量不用left join
優(yōu)化查詢條件的順序
關(guān)聯(lián)歇僧、查詢俱萍、排序字段建索引
查詢字段建分區(qū)
RANGE 分區(qū)
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分區(qū)
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分區(qū)
CREATETABLE hash_datetime (
? id INT,
? hiredate DATETIME)
PARTITION BY HASH( TO_DAYS(hiredate) )
PARTITIONS 10;
TIMESTAMP類型使用UNIX_TIMESTAMP方法
key分區(qū)
alter table t_report
PARTITION BY key( Project_ID? )
PARTITIONS 20;
刪除分區(qū)
ALTER TABLE employees DROP PARTITION p0盗忱；刪處分區(qū)和數(shù)據(jù)
alter table …remove partitioning 刪除分區(qū)不刪除數(shù)據(jù)
