我在 MySQL 中有 3 个表:

innodb_verion     1.1.8
version           5.5.29-log

Table: artist
Columns:
    id              int(11)
    new_id          varchar(50)

Table: collection
Columns:
    id              int(11)
    new_id          varchar(50)

Table: artist_collection
Columns:
    artist_id       int(11) PK
    collection_id   int(11) PK

艺术家和收藏表包含 100 行(我只是提取了一个子集来测试这个问题)

artist_collection 表包含超过 800 万行，并且主键创建为:

ALTER TABLE artist_collection ADD PRIMARY KEY (artist_id,collection_id);

当我对艺术家和收藏进行相同的连接时，收藏比艺术家花费的时间要长得多:

SELECT artist.new_id AS 'i' FROM artist INNER JOIN artist_collection ON artist.id=artist_collection.artist_id LIMIT 100;
100 row(s) returned  0.001 sec

SELECT collection.new_id AS 'i' FROM collection INNER JOIN artist_collection ON collection.id=artist_collection.collection_id LIMIT 100;
100 row(s) returned  2.636 sec

这很重要，因为这些原始表有超过 1000 万行，我不想在我的查询中使用 LIMIT。即使我执行了 LIMIT 1000，慢速查询也需要大约 35 秒才能完成，而快速查询大约需要 0.020 秒。然后我对两个查询都进行了 EXPLAIN EXTENDED 以查看实际情况:

EXPLAIN EXTENDED SELECT artist.id AS 'i' FROM artist INNER JOIN artist_collection ON artist.id=artist_collection.artist_id LIMIT 100;

id    select_type    table              type    possible_keys    key      key_len    ref              rows    filtered    Extra
1     SIMPLE         artist             ALL     NULL             NULL     NULL       NULL             82      100.00
1     SIMPLE         artist_collection  ref     PRIMARY          PRIMARY  4          tbl.artist.id    2       100.00      Using index

EXPLAIN EXTENDED SELECT collection.id AS 'i' FROM collection INNER JOIN artist_collection ON collection.id=artist_collection.collection_id LIMIT 100;

id    select_type    table              type    possible_keys    key      key_len    ref      rows     filtered    Extra
1     SIMPLE         collection         ALL     NULL             NULL     NULL       NULL     101      100.00
1     SIMPLE         artist_collection  index   NULL             PRIMARY  8          NULL     8725465  100.00      Using where; Using index; Using join buffer

如您所见，它并没有平等对待查询。然后我跑了:

ALTER TABLE artist_collection DROP PRIMARY KEY;
ALTER TABLE artist_collection ADD PRIMARY KEY (collection_id,artist_id);

这导致问题自行逆转，现在艺术家 JOIN 花费了很长时间。所以我的问题是:

1) MySQL 是否以不同方式解释 PRIMARY KEY 的第一个和第二个条目？
2) 到底发生了什么？
3) 如何使查询相等(快速)？

最佳答案

考虑以这种方式存储的复合索引中的记录:

+---------------+-----------+
| collection_id | artist_id |
+---------------+-----------+
|             1 |         1 |
|             1 |         2 |
|             1 |         3 |
|             2 |         1 |
|             2 |         2 |
|             3 |         1 |
+---------------+-----------+

鉴于数据首先按 collection_id 排序，查找所有 collection_id = 1 非常简单。但是，查找所有 artist_id 不是。

即使您同时搜索 collection_id = 1 和 artist_id = 1，这也是一项简单的任务(您不需要扫描所有 artist_id 值) .

MySQL 也发生了类似的事情。当您仅通过 collection_id 加入时，此索引非常有用。当您同时通过 collection_id 和 artist_id 加入时，此索引再次有用。但是，当仅加入 artist_id 时，此索引不会有太大帮助。

如果您也打算仅加入 artist_id，那么您可能需要在 artist_id 上添加另一个索引。您应该在您的 exaplain 中看到该新索引将用于这些查询。

关于MySQL 主键对顺序影响 JOIN 速度，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/19842869/