寻找MySQL优化和微调提示 [英] Looking for MySQL optimization and fine-tuning tips

问题描述

我希望你能帮我这个。我有一个Twitter服务（ http://foller.me ），该服务即将更新为公开beta 2，但我不想这样做，直到我可以在至少2或3秒的页面出来。目前的版本很简单，但我正在开发的开发版本是相当复杂。

这是所有关于我的关系和配置文件表，其中包含〜2百万行。配置文件表包含一般的twitter用户信息，关系表包含[ twitter_id ， followed_by ] UNIQUE索引。有一个cron作业，每隔一段时间运行一次，并询问Twitter API关于用户关注者并将数据插入关系数据库。

我使用InnoDB，所以该表未被锁定，但是被频繁更新，因此查询缓存没有获得足够的命中，特别是当cron作业正在执行时。

语句如 SELECT screen_name FROM profiles WHERE twitter_id IN（SELECT followed_by FROM relationships WHERE twitter_id ='kovshenin'）。我确信这不是一个很好的方法来做，而这些是（加上许多其他人）在mysql-slow日志中，当我打开。

反正，我需要一些关于如何为我的项目实现良好运行时间的一般提示。

非常感谢。

 选择screen_name 
 FROM profiles p 
 WHERE EXISTS 
（
 SELECT 1 
 FROM关系r 
 WHERE r.twitter_id ='kovshenin'
和r.followed_by = p.twitter_id 
）
  

此外，由于您有一个 UNIQUE 索引，您很可能会受益于以 JOIN 重写查询：

  SELECT p.screen_name 
 FROM关系r 
 JOIN个人资料p 
 ON p.twitter_id = r.followed_by 
 WHERE r.twitter_id =' kovshenin'
  

您可能还会受益于使用常用列创建多个索引， c $ c> DML 开销。

例如，此查询：

  SELECT p.screen_name 
 FROM relation r 
 JOIN个人资料p 
 ON p.twitter_id = r.followed_by 
 WHERE r.twitter_id ='kovshenin '
  

甚至不会查看个人资料如果您在个人资料（twitter_id，screen_name）上创建复合索引。

如果选择 screen_name （并且只有 screen_name ，i。 e。它总是在 SELECT 列表中的唯一列）是一个足够为您的应用程序的查询，你应该考虑创建这样的索引。

I hope you can help me with this one. I got a twitter service ( http://foller.me ) which is about to be updated to public beta 2, but I don't want to do this until I could give the pages out in at least 2 or 3 seconds. The current version is simple enough, but the dev version I'm working on is quite complex.

It's all about my relations and profiles tables which both contain ~ 2 million rows. The profile table contains general twitter user info and the relations table contains [twitter_id, followed_by] entries which are both in a UNIQUE index. There's a cron job that runs every once in a while and asks the Twitter API about the users followers and inserts data into the relations database.

I'm using InnoDB, so the table isn't being locked, but updated frequently, thus the query cache isn't getting enough hits, especially when the cron job is executing.

I've got statements like SELECT screen_name FROM profiles WHERE twitter_id IN (SELECT followed_by FROM relations WHERE twitter_id = 'kovshenin'). I'm sure that's not a very good way to do it and these are the ones (plus lots of others) that come up in the mysql-slow log when I turn it on.

Anyways, I need some general tips on how to acomplish good runtime for my project.

Thanks so much.

解决方案

Try to rewrite your query as follows:

SELECT  screen_name
FROM    profiles p
WHERE   EXISTS
        (
        SELECT  1
        FROM    relations r
        WHERE   r.twitter_id = 'kovshenin'
                AND r.followed_by = p.twitter_id
        )

Also, since you have a UNIQUE index, you will most probably benefit from rewriting your query as a JOIN:

SELECT  p.screen_name
FROM    relations r
JOIN    profiles p
ON      p.twitter_id = r.followed_by
WHERE   r.twitter_id = 'kovshenin'

You may also benefit from creating several indexes with frequently used columns at the expense of extra DML overhead.

For instance, this query:

SELECT  p.screen_name
FROM    relations r
JOIN    profiles p
ON      p.twitter_id = r.followed_by
WHERE   r.twitter_id = 'kovshenin'

will not even look into profiles, if you create a composite index on profiles (twitter_id, screen_name).

If selecting the screen_name (and only the screen_name, i. e. it's always the only column in the SELECT list) is a query common enough for your application, you should consider creating such an index.

这篇关于寻找MySQL优化和微调提示的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！