使用innodb时的表模式设计

Question

我在为我们的系统设计表模式时遇到了问题。

情况是这样的：

1. 我们的系统有很多项目（超过 2000 万个），每个项目都有一个唯一的 id，但是每个项目可能有很多记录。例如，对于 id 1 的项目，大约有 5000 条记录，每条记录有 20 多个属性。需要通过其 id 和一个或多个属性的状态来标识，以便在 select、update 或 delete 中使用。

2. 我想使用innodb

但问题是使用innodb时，必须要有簇索引。 由于上述情况看来必须要找到一个簇索引所以我只能使用一个auto_increment int作为key

目前的设计如下：

create table record (
item_key int(10) unsigned NOT NULL AUTO_INCREMENT,
item_id int(10) unsigned NOT NULL,
attribute_1 char(32) NOT NULL,
attribute_2 int(10) unsigned NOT NULL,
.
.
.
.
.
attribute_20 int(10) unsigned NOT NULL,
PRIMARY KEY (`item_key`),
KEY `iattribute_1` (`item_id`,`attribute_1`),
KEY `iattribute_2` (`item_id`,`attribute_2`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=latin1

sql语句：

select * from records 
  where item_id=1 and attribute_1='a1' and attribute_2 between 10 and 1000;

update 和 delete 语句类似。

我不认为这是一个好的设计，但我也想不出其他的了；欢迎所有建议。

---

抱歉，如果我没有把问题说清楚。

1. 我想要访问（选择、更新、删除、插入）的是记录，而不是项目。 项目有自己的属性，但在上面的描述中，我提到的属性属于记录。

2. 每个项目可以有很多记录，例如项目 1 大约有 5000 条记录。

3. 每条记录有42个属性，其中一些可以为NULL，每条记录都有一个唯一的id，这个id在不同的项目中是唯一的，但这个id是一个字符串而不是数字

4. 我想以这种方式访问​​记录：

   A.我只会在一次或一次查询中获取（或更新或删除）属于一项特定项目的记录

   B.我将获取或更新查询中所有属性或某些特定属性的值

   C.查询条件中的属性可能与我想要的属性不一样。

所以可能会有这样的 SQL 语句：

Select attribute_1, attribute_N from record_table_1 where item_id=1 and attribute_K='some value' and attribute_M between 10 and 100

而我认为原来的设计不好的原因是：

1. 我不能选择一个属性或记录 id 作为主键，因为它没有用，在每次查询，我都必须指定项目id和一些属性作为查询条件（例如“where item_id=1 and attribute_1='value1' and attribte_2 between 2 and 3），所以我只能使用auto_increment int数字作为主要key。这样做的结果是每个查询都必须扫描两个b树，并且看起来辅助索引的扫描无效。

2. 复合键似乎也没用，因为查询条件可能会因许多属性而异。

3. 按照原来的设计，好像我加了很多索引来满足不同的查询，不然就得处理全表扫描的问题，但是很明显索引太多不利于update、delete ，插入操作。

Answer 1

如果您想要集群索引并且不想使用 myisam 引擎，那么听起来您应该使用两个表：一个用于项目的唯一属性，另一个用于项目的每个实例（具有指定的属性）。

Answer 2

你是对的，架构是错误的。将属性 1..20 作为表中的字段并不是执行此操作的方法，您需要一个单独的表来存储此信息。该表将包含此记录中的 item_key 以及它自己的 key 和 value，因此第二个表将具有允许更好的索引寻找。

类似于以下内容：

查看该图，很明显出现问题，因为 record< /code> 表太空了，它对我来说看起来不正确，所以也许我在原来的问题中遗漏了一些东西......

复合键

我想也许你正在寻找复合键而不是聚集索引，这是不同的事情。您可以通过以下方式实现这一目标：

create table record (
item_id int(10) unsigned NOT NULL,
attribute_1 char(32) NOT NULL,
attribute_2 int(10) unsigned NOT NULL,
.
.
.
.
.
attribute_20 int(10) unsigned NOT NULL,
PRIMARY KEY (`item_id`,`attribute_1`,`attribute_2`),
KEY `iattribute_1` (`item_id`,`attribute_1`),
KEY `iattribute_2` (`item_id`,`attribute_2`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=latin1