用mysql表分区来优化大数据量的表

原文：http://jsczxy2.iteye.com/blog/1947050 根据公司数据库实际情况，订单表有可能会比预想中扩张速度快，这里可能需要预先准备下优化方案，传统方案是分表或者分库，不过目前最好的方案是使用mysql的表分区来优化。不过需要注意的是在表分区建立后mysql

原文：http://jsczxy2.iteye.com/blog/1947050

根据公司数据库实际情况，订单表有可能会比预想中扩张速度快，这里可能需要预先准备下优化方案，传统方案是分表或者分库，不过目前最好的方案是使用mysql的表分区来优化。不过需要注意的是在表分区建立后mysql查询缓存会失效，那么可以说暂时分表带来的好处在于更新、删除以及锁处理的时间会减少，但是如果查询并非针对表分区字段进行，那么查询的时间由于查询缓存失效反而会增加，这点需要取舍。

第一步:由于表分区必须在表建立的时候创建规则，而已经存在的没有创建过表分区规则的表需要重新做导入处理。方法如下:

Sql代码 ?

1. #这里使用HASH表分区，mysql会根据HASH字段来自动分配数据到不同的表分区，这种情况适用于没有表分区规则但是有需要分表来进行查询优化的情况。这里根据id字段hash规则创建2个表分区
2. CREATE?TABLE?`creater_bak`?(
3. ??`id`?int(11)?NOT?NULL,
4. ??`name`?varchar(100)?DEFAULT?NULL,
5. ??PRIMARY?KEY?(`id`)
6. )?ENGINE=InnoDB?DEFAULT?CHARSET=utf8
7. PARTITION?BY?HASH(id)?PARTITIONS?2

创建完成后开始导入原表数据：

Sql代码 ?

1. insert?into?creater_bak?select?*?from?creater;

导入以后的新表数据就是分布在不同的2个表分区中了。

如果数据量非常大，觉得预设的表分区数量太少，那么可以新增表分区，mysql会自动重新分配：

Sql代码 ?

1. #这里新增8个表分区，加上新建表时候的2个，一共10个表分区了
2. ALTER?TABLE?`creater_bak`?ADD?PARTITION?PARTITIONS?8;

最后修改表名为原表名即可。

PS：下面是使用RANGE形式表分区，其中一些注意点HASH表分区也一样要注意：

1.如果使用RANGE形式进行表分区，必须设定规则，例如：

Sql代码 ?

1. CREATE?TABLE?`creater_bak`?(
2. ??`id`?int(11)?NOT?NULL,
3. ??`name`?varchar(100)?DEFAULT?NULL,
4. ??PRIMARY?KEY?(`id`)
5. )?ENGINE=InnoDB?DEFAULT?CHARSET=utf8
6. PARTITION?BY?RANGE(id)?(
7. ????PARTITION?p0?VALUES?LESS?THAN?(500),
8. ????PARTITION?p1?VALUES?LESS?THAN?(1000),
9. ????PARTITION?p2?VALUES?LESS?THAN?MAXVALUE
10. )

2.如果想修改有规则的表分区，注意只能新增，不要随意删除，这里删除表分区会造成该表分区内部数据也一起被删除掉，千万注意。另外如果设定了MAXVALUE那么是不能新增的，虽然删除MAXVALUE那条表分区后可以新增，但是依然注意删除的MAXVALUE分区是否有数据，如果有则不能随意删除，最好的办法依然是重建一张新表，表在创建时候重新制定规则后把旧表导入新表，这样能保证不会丢失数据。虽然最好不要删除分区，但是依然下面介绍如何删除表分区以及新增表分区：

Sql代码 ?

1. #删除上面的MAXVALUE规则表分区(如果该表分区有数据，请勿随便使用此操作)
2. ALTER?TABLE?`creater_bak`?drop?PARTITION?p2;
3. #新增规则表分区，注意按规则步长来新增，否则会报错，这里步长为500
4. ALTER?TABLE?`creater_bak`?add?PARTITION(PARTITION?p2?VALUES?LESS?THAN?(1500))
5. ALTER?TABLE?`creater_bak`?add?PARTITION(PARTITION?p3?VALUES?LESS?THAN?MAXVALUE)

最后使用下面的语句可以查看分区搜索情况:

Sql代码 ?

1. EXPLAIN?PARTITIONS?select?*?from?`creater_bak`?b1?where?b1.`id`=11

最后附上官方中文文档:

http://dev.mysql.com/doc/refman/5.1/zh/partitioning.html

深入了解MySQL 5.5分区功能增强

本文出自：http://blog.chedushi.com, 原文地址：http://blog.chedushi.com/archives/9639, 感谢原作者分享。