mysql 那些事-sql优化

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 删除分区不删除数据