使用多个左联接优化MySQL查询 [英] Optimizing MySQL query with multiple left joins
问题描述
我有一个NewsStories表格，剩下一些相关表格.每个新闻故事可以具有多个图像，类别和地址.因此查询实质上是:
I have a NewsStories tables which I'm left joining with some related tables. Each News story can have multiple images, categories and addresses. So the query is essentially:
SELECT * FROM NewStories
LEFT JOIN Images ON Newstories.id=Images.story_id
LEFT JOIN Categories ON NewsStories.id=Categories.story_id
LEFT JOIN Addresses ON NewsStories.id=Addresses.story_id
WHERE ...
每个故事通常有一些图像和地址，以及1或2个类别. NewsStories表包含大约10,000条文章.
There are usually a few images and addresses per story, and 1 or 2 categories. The NewsStories table has about 10,000 articles.
问题在于性能相当慢(大约15到20秒，尽管它的确变化很大，有时甚至低至5秒).
The trouble is that the performance is rather slow (in the order of 15-20 seconds, although it does vary quite a bit and sometimes drops to as low as 5 seconds).
我想知道是否有更好的方法来组织查询以加快查询速度(我对SQL还是很陌生).
I was wondering if there's a better way of organizing the query to speed it up (I'm quite new to SQL).
特别是，给定故事的行数乘以图像数乘以地址数乘以类别数似乎很浪费.
In particular it seems quite wasteful that the number of rows for a given story is multiplied by the number of images times the number of addresses times the number of categories.
我实质上是在尝试将新闻报道的属性重构为一个可以在前端操作的对象.
I'm essentially trying to reconstruct the properties of the News story into a single object which I can manipulate in a frontend.
这是解释(如果格式不正确，我们深表歉意).我猜我没有正确地索引地址，如果它是在哪里使用".正确吗?
Here's the explain (apologies if the formatting doesn't come out correctly). I'm guessing I'm not indexing Addresses properly if it's "Using where". Is that correct?
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE Addresses ALL NULL NULL NULL NULL 6640 Using where
1 SIMPLE NewsStories eq_ref PRIMARY PRIMARY 767 NewsStories.Addresses.story_id 1 Using where
1 SIMPLE Images ref PRIMARY PRIMARY 767 NewsStories.NewsStories.id 1 Using index
1 SIMPLE Categories ref PRIMARY PRIMARY 767 NewsStories.NewStories.id 1
推荐答案
- 确保在WHERE语句和ON条件中的字段上具有索引，默认情况下对主键进行索引，但如果需要，也可以手动创建索引.
-
检查是否真的必须选择所有表中的每一列?如果不是，请确保仅选择所需的列，避免使用 select *
仔细检查是否确实需要左联接，如果不需要，请使用INNER JOIN.
Double check if you really need LEFT JOINS, if no, use INNER JOINs.
如果在完成查询调整后仍然存在性能问题，请考虑对模式进行规范化以消除联接
If performance is still an issue after you're done tweaking your query, consider denormalizing your schema to eliminate joins
您可能还需要考虑通过使用诸如sphinxsearch和memcached之类的缓存应用程序来减少数据库的负载
You may also want to consider reducing the load on the database by using caching applications like sphinxsearch and memcached
检查所有联接都不是视图联接，而不是实际表联接
Check none of your joins are to views rather than actual tables
参考:
http://dev.mysql.com/doc/refman/5.0/en/create-index.html
这篇关于使用多个左联接优化MySQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
创建[UNIQUE | FULLTEXT | SPATIAL] INDEX index_name [index_type] 开启tbl_name(index_col_name，...) [index_type]
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type] ON tbl_name (index_col_name,...) [index_type]
index_col_name: col_name [(长度)] [ASC | DESC]
index_col_name: col_name [(length)] [ASC | DESC]
index_type: 使用{BTREE |哈希}
index_type: USING {BTREE | HASH}
