- 创建分区表
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) ;
- 查询分区的数据
#查看属于part_201501分区的数据有多少条
SELECT * FROM test PARTITION (part_201501);
- 查看test表的所有分区
SELECT
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
FROM
information_schema.PARTITIONS
WHERE
TABLE_SCHEMA = SCHEMA ( )
AND TABLE_NAME = 'test';
- 建立索引
alter table test add index test_index (id) ;
1:test代表索引建立的表 test_index 代表索引名称 id代表索引绑定的列
2:show index from test 查看test表的索引
- 查看Mysql建表语句
show create table test;