对大表按编号拆表后 如何维护原先的唯一索引

Question

一个网络游戏的数据库，用的是mysql
有2张表 accounttable 和 roletable 
accounttable 字段包括：账号编号（主键），账号名称（唯一约束），其他账号信息字段。
roletable    字段包括：账号编号，角色编号（账号编号，角色编号联合起来做为主键），角色名称（唯一约束），其它角色信息字段。
角色表的‘账号编号’在程序逻辑中做为 账号表的‘账号编号’的外键使用，数据库中不建立外键关联。现在由于roletable的数据量达到百万行，总数据量超过30G，为了提高查找和更新的效率，想对roletable拆表，假定按照账号编号拆成10张，账号编号%10==n的记录放在roletable_n中。现在想请问下 对于未拆分之前的roletable，有一个唯一约束的字段‘角色名称’，当拆表后，如何对这10张表的‘角色名称’字段一起建立一个唯一约束？还是说只能通过程序逻辑，在程序中对10张表进行逐个查找来进行判断？

Answer 1

用MYSQL？
用分区表不行吗？

Answer 2

建议使用分区表，分区表只是按分区公式从物理存储是分成不同文件以提高查询效率，逻辑上仍然是一张表。相关的约束（主键，唯一键）仍然起作用。 外键则不行。具体可以参考一下文档中的说明。
MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html

Answer 3

我建了一个测试表：
CREATE TABLE `mytable` (
  `nId` int(1) NOT NULL default '0',
  `strName` char(10) NOT NULL default '',
  PRIMARY KEY  (`nId`),
  UNIQUE KEY `strName` (`strName`)
) ENGINE=MyISAM
PARTITION BY RANGE (nId%5) (
    PARTITION p0 VALUES LESS THAN (1),
    PARTITION p1 VALUES LESS THAN (2),
    PARTITION p2 VALUES LESS THAN (3),
    PARTITION p3 VALUES LESS THAN (4),
    PARTITION p4 VALUES LESS THAN (5)
)
报告的错误是：
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function我想对上表的strName列建立唯一索引 请问应该怎么做？

Answer 4

strName在你的定义k中本来就是唯一的
UNIQUE KEY `strName` (`strName`)
应该是其它列的问题

Answer 5

ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function
关于Partitioning Keys, Primary Keys, and Unique Keys的限制在5.1中分区表对唯一约束有明确的规定，每一个唯一约束必须包含在分区表的分区键（也包括主键约束）。

Answer 6

既然如此，那么问题就出来了：
对于下面表，用主键做了分区键，同时其他列即有唯一约束，又不可以包含到分区键的时候，该怎么做呢 ： 
CREATE TABLE `mytable` ( 
  `nId` int(1) NOT NULL default '0', 
  `strName` char(10) NOT NULL default '', 
  PRIMARY KEY  (`nId`), 
  UNIQUE KEY `strName` (`strName`) 
)

Answer 7

简单看了下， 你的表记录太小，没必要分表！  达到亿级别再谈分表吧！ 但是问题还得解决是吧？ 当达到亿万级别后，分表的话，还是涉及到这个外键的问题。 如果你不想通过应用程序来做，或者你们的开发很难沟通而不愿意修改的话，那么， 你可以建立一张第三方表来保存这些关联，JOIN的时候把这个小表加上就行。
PS：
   我的客户中， 表记录数我一般会在10条记录才拆分的。 除非你的业务中把一些大内容的东西保存在数据里面。那就另当别论了!  还有其他问题，加我QQ群讨论！ 群号码博客上有！

Answer 8

再加一张表 会不会反而降低查找和更新的速度呢？
我的表中确实存在10几个BLOB字段

Answer 9

存在10几个BLOB字段，你的瓶颈就在IO上了，300W的数据有30G，可想而知所以我觉得你应该做的是纵向的表拆分，而不是横向的；
一个表有10几个BLOB的话，我猜测你这些BLOB并不是全部都是一开始就有值的那就是把经常查的，字段“内容”少的放到一个表
把字段内容大的，例如blob这种，分到其他表，甚至多个表中

Answer 10

纵向拆分不太现实
我描述问题的时候没有表达全面说一下：
这些字段的使用频率是一样的。
更新的时候除了主键不更新，其余字段需要一起更新。
查找的时候同样是对某条记录的所有字段一起读取。这是一个C/S架构的项目，S端包括若干 逻辑Server 和 DBServer，逻辑Server需要定时更新数据，比如每隔1分钟，将逻辑Server上的所有数据向MySql保存一次。保存的时候是将逻辑Server上的数据发到DBServer，由DBServer保存。逻辑Server逐条发送，DBServer逐条做Update操作。读取的时候同样是逻辑Server将主键信息数据发到DBServer，DBServer负责查找后发回给逻辑Server。
现在对逻辑Server做大量修改是不太现实的，主要是对DBServer做优化，以及对数据库表结构做优化。所以如果做纵向拆分，可能反而会降低查找的效率，因为需要做连接查询，或者多次查询才能将原先的一条记录读取出来。

Answer 11

第3 我现在需要提高查询和更新的效率的目的是：由于逻辑Server需要定时更新数据，比如1分钟更新一次这样
那么如果我的DBServer可以达到每分钟更新1W条，即意味着数据库支持逻辑Server同时进行1W条的数据量的运算。所以我希望能够使查询和更新的速度尽可能的快。目前的数据库：
数据库中主要有2张比较大的表（其中一张100W条记录6G，另一张更大100W条记录13G），其它还有5、6张小表（每张100W条记录的数据不超过100MB），这两张大表中均存在十几个blob字段。

Answer 12

第4 blob字段中存的均为比较长的结构数组（会C/C++的应该更容易理解，即struct[]），所以如果将blob字段拆分为普通字段也不太现实第5 目前已经尝试做的修改是参考了11楼的想法，再加了一张表temp，这张表只有2个字段：id和name，id为主键，name上加唯一约束。其它表结构均不变，即2张表中都保存name列，人为制造一点冗余数据。在需要判断唯一约束的地方通过temp表来判断（temp表只有2列，比较小），原先的查找和更新逻辑则仍然使用原表。这样对原表横向分表后，查找和更新的效率要比不分表时提高一点，同时数据库也可以进行唯一约束的判断。只是不知道这样做是否是正确的思路第6 还是关于blob字段，如果我将这10几个blob字段合并成1个blob字段（记录大小不变），能不能提高查找和更新的速度？