mysql通过建立分区和索引来提高查询效率

一，创建分区

DROP TABLE IF EXISTS `test`;
CREATE TABLE  test  (
   id int(10) unsigned NOT NULL AUTO_INCREMENT,
    time datetime NOT NULL COMMENT '业务时间',
  PRIMARY KEY ( id , time )
)  ENGINE=InnoDB AUTO_INCREMENT=12001 DEFAULT CHARSET=utf8
PARTITION BY RANGE (TO_DAYS(time))
(PARTITION part_201501 VALUES LESS THAN (TO_DAYS('2015-02-01'))ENGINE = InnoDB,
 PARTITION part_201502 VALUES LESS THAN (TO_DAYS('2015-03-01'))ENGINE = InnoDB,
 PARTITION part_201503 VALUES LESS THAN (TO_DAYS('2015-04-01'))ENGINE = InnoDB,
 PARTITION part_201504 VALUES LESS THAN (TO_DAYS('2015-05-01'))ENGINE = InnoDB,
 PARTITION part_201505 VALUES LESS THAN (TO_DAYS('2015-06-01'))ENGINE = InnoDB,
 PARTITION part_201506 VALUES LESS THAN (TO_DAYS('2015-07-01'))ENGINE = InnoDB) ;

1：如果test的表存在即删除

2：创建test表，绑定主键

3：PARTITION part_201501 VALUES LESS THAN (TO_DAYS('2015-02-01'))ENGINE = InnoDB

​ 建立分区 PARTITION后面接分区名（part_201501 ） LESS THAN (TO_DAYS('2015-02-01') 代表值小于2015- 02-01属于该分区。

4：插入数据

5：SELECT * FROM test PARTITION (part_201501);

​ 查看属于part_201501分区的数据有多少条

6：select partition_name part,partition_expression expr,partition_description descr,table_rows from information_schema.partitions where TABLE_SCHEMA =schema() and TABLE_NAME='test';

查看test表的所有分区。

二，建立索引

alter table test add index test_index (id) ;

1：test代表索引建立的表 test_index 代表索引名称 id代表索引绑定的列

2：show index from test 查看test表的索引