公司的数据量半年达到了一千万条数据,现在的数据量在三千万条左右,之前加了索引,现在数据量多了又变得很慢了,网上说建表分区可以解决问题,但是不知道怎么在已建的表上增加表分区,请求大神的帮助!先谢谢了
1条回答 默认 最新
- 阿蓝好多个8 2016-12-02 00:45关注
这个是我总结的可以看看。
2、表分区
1) 查询是否支持表分区:
mysql5.6以前的: SHOW VARIABLES LIKE '%partition%';
mysql> SHOW VARIABLES LIKE '%partition%';
+-----------------------+-------+
|Variable_name | Value |
+-----------------------+-------+
| have_partition_engine | YES |
+-----------------------+-------+
如果是yes表示你当前的配置支持分区
5.6以后的版本: SHOW PLUGINS;
最后一行,可以看到partition是ACTIVE的,表示支持分区2)查询某个表的分区信息
selectpartition_name,
partition_expression,
partition_description,
table_rows
from
INFORMATION_SCHEMA.partitions
where
-- table_schema='accountrecord_copy'
table_name = '表名';
3)时间分区
alter table member
PARTITION BY RANGE ((year(createTime)*100+month(createTime))*100+day(createTime)) (
PARTITION s20100401 VALUES LESS THAN (20160401),
PARTITION s20100402 VALUES LESS THAN (20160701),
PARTITION s20100403 VALUES LESS THAN (20161001),
PARTITION s20100404 VALUES LESS THAN (20170101),
PARTITION s20100405 VALUES LESS THAN (20170401),
PARTITION s20100406 VALUES LESS THAN (20170701),
PARTITION p1 VALUES LESS THAN (MAXVALUE)
);
或alter table member
partition by range COLUMNS (createTime)
(
PARTITION p0 VALUES LESS THAN ('2016-03-01'),
PARTITION p1 VALUES LESS THAN ('2016-06-01'),
PARTITION p2 VALUES LESS THAN ('2016-09-01'),
PARTITION p3 VALUES LESS THAN ('2016-12-01'),
PARTITION p21 VALUES LESS THAN MAXVALUE
);
4) key 分区
alter table accountrecord_copy
partition by key(id) partitions 4;
id可以不指定 默认为主键,本回答被题主选为最佳回答 , 对您是否有帮助呢?解决 无用评论 打赏 举报