优化5个表SQL查询(存储=>项=>单词)

Question

表

存储(100,000行)：id (pk)，名称，lat，lng，

store_items (9,000,000行)：store_id (fk)，item_id (fk)

项目(200,000行)：id(pk)，名称，

item_words (1,000,000行)：item_id(fk)，word_id(fk)

字(50,000行)：id(pk)，word VARCHAR(255)

注意:所有ids都是整数。

========

指数

创建唯一的索引storeitems_storeid_itemid_i ON store_items(store_id，item_id)；

创建唯一的索引itemwords_wordid_itemid_i ON item_words(word_id，item_id)；

在单词(Word)上创建唯一的索引words_word_i；

注意:我更喜欢多列索引(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张表

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张表

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)

我猜索引或查询/数据库设计的问题。但一定有办法让它快速运转。谷歌在某种程度上做到了这一点，他们的桌子要大得多！

Answer 1

( 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不为FTS使用mysql

Answer 2

考虑一下去规范化结构--第一个候选是100万条记录的item_words表--直接把单词放到表格中。创建一个独特的单词列表可能更容易通过视图实现(这取决于您需要这些数据的频率，例如，您需要提取一个包含与关键字相关的产品的商店列表)。第二，创建索引视图(在MySQL中不是一个选项，而是在其他商业数据库上的一个选项)。

Answer 3

如果给定store_id，则没有可用于查找item_id的索引。如果store_id的基数足够低，它可能会从storeitems_storeid_itemid_i中获得一些好处，但是由于您有100,000个商店，这可能不是很有用。您可以尝试在store_items上创建一个首先列出item_id的索引：

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

此外，我不确定在where子句中添加联接条件是否会对性能产生不良影响，但您可能会尝试将查询更改为如下所示：

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