Tables

stores (100,000 rows): id (pk), name, lat, lng, ...

store_items (9,000,000 rows): store_id (fk), item_id (fk)

items (200,000 rows): id(pk), name, ...

item_words (1,000,000 rows): item_id(fk), word_id(fk)

words (50,000 rows): id(pk), word VARCHAR(255)

注意:所有id都是整数。

========

Indexes

CREATE UNIQUE INDEX storeitems_storeid_itemid_i ON store_items(store_id,item_id);

CREATE UNIQUE INDEX itemwords_wordid_itemid_i ON item_words(word_id,item_id);

CREATE UNIQUE INDEX words_word_i ON words(word);

注意:我更喜欢多列索引(storeitems_storeid_itemid_i 和 itemwords_wordid_itemid_i)因为:http://www.mysqlperformanceblog.com/2008/08/22/multiple-column-index-vs-multiple-indexes/

查询

select s.name, s.lat, s.lng, i.name
from words w, item_words iw, items i, store_items si, stores s
where iw.word_id=w.id
and i.id=iw.item_id
and si.item_id=i.id
and s.id=si.store_id
and w.word='MILK';

问题:耗时是 20-120 秒(取决于单词)!!!

explain $QUERY$
+----+-------------+-------+--------+-------------------------------------------------------+-----------------------------+---------+-----------------------------+------+-------------+
| id | select_type | table | type   | possible_keys                                         | key                         | key_len | ref                         | rows | Extra       |
+----+-------------+-------+--------+-------------------------------------------------------+-----------------------------+---------+-----------------------------+------+-------------+
|  1 | SIMPLE      | w     | const  | PRIMARY,words_word_i                                  | words_word_i                | 257     | const                       |    1 | Using index |
|  1 | SIMPLE      | iw    | ref    | itemwords_wordid_itemid_i,itemwords_itemid_fk         | itemwords_wordid_itemid_i   | 4       | const                       |    1 | Using index |
|  1 | SIMPLE      | i     | eq_ref | PRIMARY                                               | PRIMARY                     | 4       | iw.item_id                  |    1 |             |
|  1 | SIMPLE      | si    | ref    | storeitems_storeid_itemid_i,storeitems_itemid_fk      | storeitems_itemid_fk        | 4       | iw.item_id                  |   16 | Using index |
|  1 | SIMPLE      | s     | eq_ref | PRIMARY                                               | PRIMARY                     | 4       | si.store_id                 |    1 |             |

我希望耗时少于 5 秒!!!有什么想法吗？？？

==============

我尝试了什么

我试图通过向查询中添加表来查看执行时间何时会增加。

1张 table

select * from words where word='MILK';

Elapsed time: 0.4 sec

2个表

select count(*)
from words w, item_words iw
where iw.word_id=w.id
and w.word='MILK';

Elapsed time: 0.5-2 sec (depending on word)

3个表

select count(*)
from words w, item_words iw, items i
where iw.word_id=w.id
and i.id=iw.item_id
and w.word='MILK';

Elapsed time: 0.5-2 sec (depending on word)

4张 table

select count(*)
from words w, item_words iw, items i, store_items si
where iw.word_id=w.id
and i.id=iw.item_id
and si.item_id=i.id
and w.word='MILK';

Elapsed time: 20-120 sec (depending on word)

我猜是索引或查询/数据库设计的问题。但是必须有一种方法可以让它快速工作。 Google 以某种方式做到了，而且他们的表格要大得多!

最佳答案

a) 您实际上是在编写查询以在 mysql 中执行 FTS -> 使用真正的 FTS，如 lucene。

b) 显然，添加 9M 行连接是性能问题

c) 如何像这样限制该连接(也许它正在使用当前查询计划完成):

SELECT
    s.name, s.lat, s.lng, i.name
FROM
    (SELECT * FROM words WHERE word='MILK') w
INNER JOIN
    item_words iw
ON
    iw.word_id=w.id
INNER JOIN
    items i
ON
    i.id=iw.item_id
INNER JOIN
    store_items si
ON
    si.item_id=i.id
INNER JOIN
    stores s
ON
    s.id=si.store_id;

这背后的逻辑是，不是连接完整的表然后限制结果，而是从限制你将连接的表开始，这(如果连接顺序恰好是我写的)将大大减少你的工作集和内部查询运行时间。

d) Google 不使用 mysql 进行 FTS

关于mysql - 优化 5 表 SQL 查询(stores => items => words)，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/6592529/