mysql - 用户评价-sql架构优化

Question

我有一个用户数据排名模式的小草图:

CREATE TABLE user 
    (
     user_id int auto_increment primary key, 
     nickname varchar(50), 
     details varchar(30)
    );

CREATE TABLE rank 
    (
     rank_id int auto_increment primary key, 
     level int,
     type varchar(20) 
    );

CREATE TABLE user_rank
    (
      user_id int auto_increment not null,
      rank_id int auto_increment not null,
      FOREIGN KEY (user_id) REFERENCES user(user_id),
      FOREIGN KEY (rank_id) REFERENCES rank(rank_id),
    );


INSERT INTO user
(nickname, details)
VALUES
('Horax', 'admin@sqlfiddle.com'),
('Goan', '@sqlfiddle');

INSERT INTO rank
(level, type)
VALUES
(10, 'bronze'),
(15, 'gold');

INSERT INTO user_rank
(user_id, rank_id)
VALUES
(1, 1),
(2, 2),
(3, 3),
(4, 4);

我想选择 (a) N 个评分最高的用户，(b) 随时通过 user_id 快速获取用户昵称和用户详细信息，数据量很大，比方说，100 万条记录:

SELECT * FROM user_rank LEFT JOIN user ON user_rank.user_id = user.user_id
LEFT JOIN rank ON rank.rank_id = user_rank.rank_id 
ORDER by level DESC
LIMIT N

和

SELECT user.nickname, user.details FROM user_rank LEFT JOIN user ON user_rank.user_id = user.user_id
LEFT JOIN rank ON rank.rank_id = user_rank.rank_id 
WHERE user.user_id = 10

如何以性能的方式调整此架构和评级查询？您看到哪些弱点？

感谢各位同事的帮助!

Answer 1

你最终会有多少行？

这是一个经典的多对多关系表，对吗？它不会起作用，而且效率非常低。将其更改为:

CREATE TABLE user_rank (
    user_id int not null,
    rank_id int not null,
    PRIMARY KEY(user_id, rank_id),
    INDEX(rank_id, user_id)
) ENGINE=InnoDB;

(如果您愿意，可以重新添加外键。)

在rank中，您同时拥有rank_id和level。如果level在表中是唯一的，请将其设为主键并删除rank_id(并将其他引用更改为rank_id)。

这些更改应该可以很好地帮助您处理数百万行。

如果用户没有很多级别，那么就删除该表!只需将level放入user表中(并去掉rank_id)。