我们有一个在 MySQL 上运行的生产项目,采用以下方案 [显然有更多的列,但我省略了不相关的列以简化问题]
# Has approximately 9 million rows
CREATE TABLE users
(
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(200) NOT NULL
);
# A few hundreds
CREATE TABLE items
(
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(200) NOT NULL,
category TINYINT NOT NULL
);
# More than 22 million rows
CREATE TABLE user_items
(
id BIGINT AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT NOT NULL,
item_id BIGINT NOT NULL,
date_created DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
is_active TINYINT DEFAULT 0 NOT NULL,
CONSTRAINT user_items_items_id_fk FOREIGN KEY (item_id) REFERENCES items (ID),
CONSTRAINT user_items_users_id_fk FOREIGN KEY (user_id) REFERENCES users (ID)
);
一个用户可以拥有多个项目,拥有同一项目的多个实例,但每个类别只能有一个活动项目。只有 4 个类别,但我们将来可以有更多
我们有多个看起来像这样的查询。您可以说这对于我们系统的许多功能来说非常普遍
SELECT `users`.*, user_items.`item_id` AS `active_item`
FROM `users`
LEFT JOIN `user_items` ON `users`.`id` = `user_items`.`user_id`
LEFT JOIN `items` ON `user_items`.`item_id` = `items`.`id`
WHERE `users`.`id` = @userId AND `user_items`.`is_active` AND `items`.`category` = @category
最近,所有此类查询都在变慢。我们开始感受到打击。
我们对一些查询运行 EXPLAIN,结果显示它是user_items
表。
|id |select_type |table |partitions|type |possible_keys |key |key_len|ref |rows|filtered|Extra |
|---|------------------|-----------------|----------|------|---------------------------------------|------------|-------|--------------|----|--------|-------------------------------|
|1 |PRIMARY |players | |const |PRIMARY |PRIMARY |4 |const |1 |100 |Using temporary; Using filesort|
|1 |PRIMARY |users | |const |PRIMARY |PRIMARY |4 |const |1 |100 | |
|1 |PRIMARY |rankings | |const |PRIMARY |PRIMARY |4 |const |1 |100 | |
|1 |PRIMARY |tournaments | |const |PRIMARY |PRIMARY |4 |const |1 |100 | |
|1 |PRIMARY |profiles | |const |PRIMARY |PRIMARY |4 |const |1 |100 | |
|1 |PRIMARY |user_achievements| |ref |PRIMARY,achievement_id |PRIMARY |4 |const |6 |100 | |
|1 |PRIMARY |achievements | |eq_ref|PRIMARY |PRIMARY |92 |achievement_id|1 |100 |Using where |
|1 |PRIMARY |user_trophies | |ref |trophies_fk_idx,users_fk_idx |users_fk_idx|4 |const |1 |100 | |
|1 |PRIMARY |trophies | |eq_ref|PRIMARY |PRIMARY |92 |trophy_id |1 |100 |Using where |
|4 |DEPENDENT SUBQUERY|user_trophies | |ref |users_fk_idx |users_fk_idx|4 |const |14 |100 |Using index |
|3 |SUBQUERY |user_items | |ref |is_active,FK_user_items_item_id,user_id|user_id |4 |const |2141|2.72 |Using where |
|3 |SUBQUERY |items | |eq_ref|PRIMARY,category_id |PRIMARY |8 |item_id |1 |67.4 |Using where |
|2 |DEPENDENT SUBQUERY| | | | | | | | | |no matching row in const table |
我们考虑过添加索引,user_items.is_active
但这是一个布尔值,数据非常倾斜,因为用户可以拥有数百个项目,但只有少数项目处于活动状态。我认为该指数弊大于利。
我在徘徊是否有任何替代方法可以提高此类查询的性能
首先,您可能会考虑从此查询中删除用户表:
如果您获取很多行(例如用户拥有的所有项目),则用户中的列将在每一行中重复。即使您只获取一个项目,您真的需要从 users 表中获取行吗?我想您可能已经在其他地方的代码中获得了它包含的所有信息,例如在会话验证时选择了用户行。
在具有几个 INT 列的表中,SELECT * 与仅选择您需要的列一样快,您不必为此担心。但是,如果用户表包含大型 TEXT 列,例如他们的简历、论坛签名或多个 URL 列,则选择 users.* 最终可能会移动大量数据并阻塞数据库带宽,如果您实际上没有使用信息。
--
表项很小,因此可以将其缓存在客户端的 RAM 中。那就不用查询了。
--
数据完整性:您当前的结构不允许一种简单的方法让数据库约束检查一个类别中是否只有一个项目是活动的。此外,category 列位于 items 表中,因此无法在 user_items 表中对其进行索引,这意味着数据库必须扫描所有活动项目行才能在类别中找到活动项目。这可能是也可能不是问题,具体取决于行数。
建议:将items的主键修改为(category_id,item_id),并引用users_items中的主键,以保证完整性的方式将category复制到表users_items中。还要在 item_id 上添加唯一约束,以避免使用 item_id 重写所有代码以在每个选择上到处添加 category_id。
我看到 category_id 是一个 tinyint,它暗示试图保存一些字节,在这种情况下 item_id 不需要是 bigint,更改为 short 将在 users_items 和相关索引中节省一些空间。
好的。现在 category_id 被复制到 users_items 中,让我们做这个约束。与 postgres 不同,mysql 不执行条件索引,因此我建议如下:
这利用了 MySQL 允许在 UNIQUE 索引约束中“重复” NULL 值这一事实。这在语义上是正确的,因为“NULL”在某种程度上意味着“未知”,因此即使在唯一约束中也允许具有未知值的多行是有意义的。另请注意 NULL 不等于 NULL。然而,TRUE IS NOT NULL,因此每个 (user_id,category_id) 只能有一行且 is_active=TRUE。所以现在你的约束将被强制执行。
该索引还允许快速搜索每个用户和类别的活动项目,这正是您想要的。
可以改用 (user_id,is_active,category_id)。这可能具有更好的缓存位置,因为所有具有 is_active=TRUE 的索引页将按索引顺序聚集在一起。
出于性能原因,现在我建议使用 (user_id,item_id,id) 作为 users_items 的主键。这是因为 InnoDB 按主键对表进行集群,因此这会将一个用户的项目的所有行放在缓存中,这是一件好事,因为您的缓存将填充当前玩游戏的用户的行,而不是来自无用的行目前没有玩游戏的用户。由于 (user_id,item_id) 不是唯一的,因此必须生成行 ID 会带来额外的复杂性。我正要建议添加一个计数列以将同一用户拥有的多个项目合并到一行中,但如果你这样做,它可能意味着 users_items 还为项目的每个实例提供奖金和其他修饰符,所以不会不工作。
注意使用(user_id,item_id,id)作为主键,那么item_id在items表中必须是唯一的,不同类别的两个item不能有相同的item_id。所以 items 表有点有两个主键,一个是官方的。
由于缺乏选择性,仅 is_active 上的索引很可能是无用的。
(user_id, item_id, is_active) 上的索引没有类别,所以嗯。
上面建议的主键中免费包含 (user_id, item_id) 上的索引,因此无需复制它。
另一种选择是将活动项目放在一个表中,将非活动项目放在另一个表中。缺点:很烦人,你必须移动行。优点:如果您的用户像普通的 RPG 玩家一样,他们有 4 个活动项目和 999999 件垃圾,那么您的活动表将很小,缓存良好且速度很快。您还可以为活动项目创建更多索引,而无需使用非活动项目来增加它们。
首先,您可以将外部连接更改为内部连接,因为两个外部表都有 NFC 条件:
其次,以下索引应该有助于获得优化的查询计划。