优化大型查询和表结构的问题 [英] Problems to optimize large query and tables structure
问题描述
我不是数据库专家，所以我已经有一段时间了，我会尽可能多地阅读，并且由于社区的回答，我可以对查询和表结构进行一些更改.即使阅读了很多东西，我也被卡住了，所以我来提出第一个问题.
I'm not an DB expert, so I've been around for a while, reading as much as I can and thanks to the community answers I could make several changes to my query and tables structure. Even after reading a lot of stuff I got stuck, so I came to make my first question.
我有一个网站，用户可以在其中发布自己的故事.每个故事都可以具有流派，警告，多个作者，分配多个角色等.
I have a website where the users post their own stories. Each story can have genres, warnings, multiple authors, multiple characters assigned, etc.
我们正在运行MySQL 5.x，表是InnoDB，用PHP编写的网站.使用GROUP_CONCAT对结果中的每个故事返回一行.之前使用GROUP BY故事ID进行了尝试，但是每个查询大约需要16秒才能完成，非常慢.有了这个新版本，他们花费0.175，但是例如，如果WHERE中的流派不存在，则查询需要23秒！对于测试，每个表都有100万条记录，而authors表则有150万条记录.我试图放置一些额外的索引，以查看将使用哪个MySQL.
We're running MySQL 5.x, tables are InnoDB, website written in PHP. Using GROUP_CONCAT to return a single row per story on the result. Tried previously using GROUP BY story id, but every query was taking around 16 secs to complete, was very slow. With this new one, they take 0.175, but for example, if the genre in WHERE doesn't exists, the query takes 23 secs! For the tests, every table has 1 million records, authors table with 1.5 million. I've tried to place some extra indexes to see which one MySQL will use.
我试图通过一对多关系使事情规范化.在这里，我仅介绍几个表格，因为该解决方案可能涵盖了整个问题.非常感谢您的帮助！
I've tried to get things normalized with one to many relationship. Here I'll present only a few tables as the solution will probably cover the entire problem. Any help is greatly appreciated, thanks for your time!
表格
CREATE TABLE `fanfiction_authors` (
`uid` int(11) NOT NULL AUTO_INCREMENT,
`penname` varchar(100) NOT NULL,
`penname_url` varchar(100) NOT NULL,
PRIMARY KEY (`uid`),
KEY `penname_url` (`penname_url`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1000000 ;
-- --------------------------------------------------------
CREATE TABLE `fanfiction_stories` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(200) NOT NULL,
`sinopse` text NOT NULL,
PRIMARY KEY (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1000000 ;
-- --------------------------------------------------------
CREATE TABLE `fanfiction_stories_authors` (
`sid` int(11) NOT NULL,
`uid` int(11) NOT NULL,
KEY `sid_uid` (`sid`,`uid`),
KEY `sid` (`sid`),
KEY `uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
CREATE TABLE `fanfiction_stories_genres` (
`key_id` int(11) NOT NULL AUTO_INCREMENT,
`sid` int(11) NOT NULL,
`genre_id` int(11) NOT NULL,
PRIMARY KEY (`key_id`),
KEY `sid` (`sid`),
KEY `genre_id` (`genre_id`),
KEY `sid_genreid` (`sid`,`genre_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1000000 ;
-- --------------------------------------------------------
CREATE TABLE `fanfiction_stories_stats` (
`sid` int(11) NOT NULL,
`reviews` int(11) NOT NULL,
`recomendacoes` int(11) NOT NULL,
PRIMARY KEY (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
CREATE TABLE `fanfiction_stories_warnings` (
`key_id` int(11) NOT NULL AUTO_INCREMENT,
`sid` int(11) NOT NULL,
`warning_id` int(11) NOT NULL,
PRIMARY KEY (`key_id`),
KEY `sid` (`sid`),
KEY `warning_id` (`warning_id`),
KEY `warningid_sid` (`sid`,`warning_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1000000 ;
----
查询
SELECT
st.sid, st.title, st.sinopse,
(SELECT GROUP_CONCAT(CAST(genre_id AS CHAR)) FROM fanfiction_stories_genres WHERE sid = st.sid) as genres,
stats.reviews, stats.recomendacoes,
(SELECT GROUP_CONCAT(CAST(warning_id AS CHAR)) FROM fanfiction_stories_warnings WHERE sid = st.sid) as warnings_ids
FROM
fanfiction_stories AS st
LEFT JOIN fanfiction_stories_stats AS stats ON st.sid = stats.sid
WHERE
st.sid IN (SELECT sid FROM fanfiction_stories_warnings WHERE warning_id = 5) AND
st.sid IN (SELECT sid FROM fanfiction_stories_genres WHERE genre_id = 300)
ORDER BY
st.sid ASC
LIMIT 20
我在这里无法解释我的解释，因此我将打印屏幕上传到了Dropbox.无法嵌入图片，因为我是这里的菜鸟，抱歉.
这是当我们拥有有效类型时的扩展说明(您会发现类型为300的故事).
This is the explain extended when we have a valid genre (You will find stories with genre number 300).
http://dl.dropbox.com/u/14508898/Printscreen/stackoverflow_explain_print_001. PNG
这是当我们使用无效类型(您将找不到类型900的故事)时扩展的解释.
This is the explain extended when we have a invalid genre (You will NOT find stories with genre number 900).
http://dl.dropbox.com/u/14508898/Printscreen/stackoverflow_explain_print_002. PNG
你们能帮我吗?我的归一化正确吗?我在做什么错了?
Can you guys help me, please? Is my normalization correct? What am I doing wrong?
提前谢谢！
推荐答案
您可以使用JOIN来保存2个内部选择，这肯定会在两种情况下(genre_id = 300和genre_id = 900)加快处理速度.
You can save 2 of the inner selects by using JOIN instead, which will surely speed things up in both cases (genre_id = 300 and genre_id = 900).
SELECT
st.sid, st.title, st.sinopse,
(SELECT GROUP_CONCAT(CAST(genre_id AS CHAR)) FROM fanfiction_stories_genres WHERE sid = st.sid) as genres,
stats.reviews, stats.recomendacoes,
(SELECT GROUP_CONCAT(CAST(warning_id AS CHAR)) FROM fanfiction_stories_warnings WHERE sid = st.sid) as warnings_ids
FROM
fanfiction_stories AS st
LEFT JOIN fanfiction_stories_stats AS stats ON st.sid = stats.sid
JOIN fanfiction_stories_warnings w ON st.sid = w.sid AND w.warning_id = 5
JOIN fanfiction_stories_genres g ON st.sid = g.sid AND g.genre_id = 300
GROUP BY st.sid
ORDER BY st.sid ASC
LIMIT 20
这篇关于优化大型查询和表结构的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
