mysql join主键_MySQL主键对顺序影响JOIN速度

我在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万行,并且创建了一个PRIMARY KEY：

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 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,inner-join,primary-key,explain

来源： https://codeday.me/bug/20190708/1407467.html