MySQL：优化无主键的表（索引、外键）
每个会员有0个或多个订单。每个订单至少包含 1 件商品。 memberid - varchar，而不是整数 - 没关系（请不要提到这不是很好，我无法更改它）。 因此，共有 3 个表：members、orders 和 order_items。订单和 order_items 如下：
CREATE TABLE `orders` (
`orderid` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`memberid` VARCHAR( 20 ),
`Time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
`info` VARCHAR( 3200 ) NULL ,
PRIMARY KEY (orderid) ,
FOREIGN KEY (memberid) REFERENCES members(memberid)
) ENGINE = InnoDB;
CREATE TABLE `order_items` (
`orderid` INT(11) UNSIGNED NOT NULL,
`item_number_in_cart` tinyint(1) NOT NULL , --- 5 items in cart= 5 rows
`price` DECIMAL (6,2) NOT NULL,
FOREIGN KEY (orderid) REFERENCES orders(orderid)
) ENGINE = InnoDB;
因此，order_items 表如下所示：
orderid - item_number_in_cart - 价格：
...
1000456 - 1 - 24.99
1000456 - 2 - 39.99
1000456 - 3 - 4.99
1000456 - 4 - 17.97
1000457 - 1 - 20.00
1000458 - 1 - 99.99
1000459 - 1 - 2.99
1000459 - 2 - 69.99
1000460 - 1 - 4.99
...
如您所见， order_items 表没有主键（我认为有创造没有意义该表的 auto_increment id，因为一旦我们想要提取数据，我们总是将其提取为 WHERE orderid='1000456' order by item_number_in_card asc - 整个块，id 对查询没有帮助）。 一旦数据被插入到 order_items 中，它就不会被更新，而只是被选择。
问题是：
- 我认为将索引放在购物车中的 item_number_in_cart 上是个好主意。有人可以证实一下吗？
- 我还需要对 order_items 做些什么来提高性能，或者看起来不错吗？我可能会错过一些东西，因为我是新手。
先感谢您。
Each member has 0 or more orders. Each order contains at least 1 item.
memberid - varchar, not integer - that's OK (please do not mention that's not very good, I can't change it).
So, thera 3 tables: members, orders and order_items. Orders and order_items are below:
CREATE TABLE `orders` (
`orderid` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`memberid` VARCHAR( 20 ),
`Time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
`info` VARCHAR( 3200 ) NULL ,
PRIMARY KEY (orderid) ,
FOREIGN KEY (memberid) REFERENCES members(memberid)
) ENGINE = InnoDB;
CREATE TABLE `order_items` (
`orderid` INT(11) UNSIGNED NOT NULL,
`item_number_in_cart` tinyint(1) NOT NULL , --- 5 items in cart= 5 rows
`price` DECIMAL (6,2) NOT NULL,
FOREIGN KEY (orderid) REFERENCES orders(orderid)
) ENGINE = InnoDB;
So, order_items table looks like:
orderid - item_number_in_cart - price:
...
1000456 - 1 - 24.99
1000456 - 2 - 39.99
1000456 - 3 - 4.99
1000456 - 4 - 17.97
1000457 - 1 - 20.00
1000458 - 1 - 99.99
1000459 - 1 - 2.99
1000459 - 2 - 69.99
1000460 - 1 - 4.99
...
As you see, order_items table has no primary keys (and I think there is no sense to create an auto_increment id for this table, because once we want to extract data, we always extract it as WHERE orderid='1000456' order by item_number_in_card asc - the whole block, id woudn't be helpful in queries).
Once data is inserted into order_items, it's not UPDATEd, just SELECTed.
The questions are:
- I think it's a good idea to put index on item_number_in_cart. Could anybody please confirm that?
- Is there anything else I have to do with order_items to increase the performance, or that looks pretty good? I could miss something because I'm a newbie.
Thank you in advance.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
主键可以跨越多个列。您不能使用列的
PRIMARY属性来执行此操作，但您可以定义具有多个列的单独主键：此外，主键只是一个唯一键，其中每列都不为空某个名字；您可以在不可为空的列上创建自己的唯一键以获得相同的效果。
通过索引
item_number_in_cart不太可能获得很大的性能提升；由于给定订单的订单项数量往往较小，因此按item_number_in_cart排序不会占用太多时间或内存。但是，将列包含在主键中将有助于数据一致性。Primary keys can span multiple columns. You can't use the
PRIMARYattribute of columns to do this, but you can define a separate primary key with multiple columns:Moreover, a primary key is simply a unique key where every column is not null with a certain name; you can create your own unique keys on non-nullable columns to get the same effects.
You'll not likely get much of a performance improvement by indexing
item_number_in_cart; as the number of line items for a given order will tend to be small, sorting byitem_number_in_cartwon't take much time or memory. However, including the column in a primary key will help with data consistency.将不会使用
item_number_in_cart上的索引。它是很小的整数，选择性不够，一旦你有 2 条记录，引擎甚至不会考虑它。您可以将其作为第二列添加到orderid上的现有索引中（由于您在orderid上创建了 FK 约束，mysql 会自动在此字段上添加索引）。你说
order_items中的数据从未更新过，但我认为可以删除；在没有主键的情况下这样做将会出现问题。Index on
item_number_in_cartwon't be used. It's tiny int, not selective enough, and won't even considered by the engine once you have 2 records. You can add it as a second column to the existing index onorderid(since you created FK constraint onorderid, mysql automatically adds an index on this field).You say that data in
order_itemsnever updated, but I think it can be deleted; doing so without primary key will be problematic.好吧，无论如何我都会有一个 autoinc，因为我非常相信代理键，但正如 alex07 所建议的，索引，甚至 orderid 的主键，item_number_in_cart 应该可以解决问题。请注意，按 item_number 排序将使用两遍排序（获取数据，然后按数字顺序对其进行排序），因此索引/键将直接将其切掉，因此即使使用代理键，您也需要该索引。
Well I'd be having an autoinc anyway, as I'm a big believer in surrogate keys, but as suggested by alex07 an index, or even primary key of orderid,item_number_in_cart should sort things out. Note the order by item_number will be using a two pass sort, (get the data and then sort it in the number order) so an index / key will chop that out straight off so you'd want that index even with a surrogate key.