不使用索引的 MySQL 连接性能

Question

新人警告! MariaDB (MySQL) v 10.1.26 出现问题。我有 3 个表:FRIENDS、POSTS 和 USERS。

CREATE TABLE USERS(
  uid INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(40) NOT NULL UNIQUE,
  email VARCHAR(100) NOT NULL UNIQUE, 
  pwhash VARCHAR(200) NOT NULL,
);

CREATE TABLE FRIENDS(
  RID BIGINT AUTO_INCREMENT PRIMARY KEY,
  uid1 INT NOT NULL,                           #user 1 
  uid2 INT NOT NULL,                           #user 2 
  state INT NOT NULL,                          #status from user1 -> user2
  FOREIGN KEY (uid1) REFERENCES USERS(uid),
  FOREIGN KEY (uid2) REFERENCES USERS(uid)
);

CREATE TABLE POSTS(
  pstid BIGINT AUTO_INCREMENT PRIMARY KEY,
  uid INT NOT NULL,
  post_text VARCHAR(500) NOT NULL, 
  time DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY(uid) REFERENCES USERS(uid)
);

还有以下表索引:

CREATE INDEX users_idx1 ON USERS(username);
CREATE INDEX friends_idx1 ON FRIENDS(uid1);
CREATE INDEX friends_idx2 ON FRIENDS(uid2);
CREATE INDEX posts_idx1 ON POSTS(uid);
CREATE INDEX posts_idx2 ON POSTS(time);

我想快速查询好友最近发的100条帖子。当前sql:

[EXPLAIN EXTENDED] SELECT * FROM (POSTS as p) 
                   JOIN (FRIENDS as f) ON f.uid2 = p.uid AND 
                                          f.uid1 = ?(userid for current user) 
                                          ORDER BY p.time DESC LIMIT 100;

我的假设是 MySQL 会通过索引获取所有 friend 的 uid(f.uid2，其中 f.uid = currentuserid)，然后用它搜索帖子，用 ORDER BY 获取最新的并将结果限制为 100 行。但是，当我运行 EXPLAIN 时:

 id | select_type | table | type | possible_keys                    | key          | key_len | ref             | rows | filtered | Extra
 1  | SIMPLE      | f     | ref  | friends_idx1, friends_idx2, uid2 | friends_idx1 | 4       | const           |  1   | 100.00   | Using temporary; Using filesort
 1  | SIMPLE      | p     | ref  | posts_idx1                       | posts_idx1   | 4       |  friends.f.uid2 |  1   | 100.00   | --

当 p.time、f.uid1 和 f.uid2 被索引时，它说它使用文件排序和临时。欢迎任何有助于提高性能的帮助、解释或建议。提前致谢!

Answer 1

对于此查询:

SELECT *
FROM POSTS p JOIN 
     FRIENDS f
     ON f.uid2 = p.uid AND 
        f.uid1 = ?
ORDER BY p.time DESC
LIMIT 100;

此查询的最佳索引很可能是:friends(uid1, uid2) 和 posts(uid)。

为什么 MySQL 不使用你们的索引？这取决于表统计数据。根据解释，您的表中的数据很少。因此，指数不会产生影响。如果所有数据都适合单个数据页，则索引不太可能影响性能。