我们有一个在 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
但这是一个布尔值,数据非常倾斜,因为用户可以拥有数百个项目,但只有少数项目处于活动状态。我认为该指数弊大于利。
我在徘徊是否有任何替代方法可以提高此类查询的性能