优化MySql查询:订购时太慢 [英] Optimize MySql query: Too slow when ordering

问题描述

(已编辑)有关该应用本身的更多详细信息，请参见: 简单但繁重的应用程序消耗了很多资源.如何优化? (采用的解决方案是同时使用联接和全文搜索)

(edited) For more details about the app it self, please, also see: Simple but heavy application consuming a lot of resources. How to Optimize? (The adopted solution was use both joins and fulltext search)

我有以下查询在25秒内运行大约50万行.如果我删除订单，则需要0.5秒.

I have the following query running up to roughly 500.000 rows in 25 seconds. If I remove the ORDER, it takes 0.5 seconds.

Fisrt测试

保留ORDER并删除所有t.和土.列，查询需要7秒钟.

Keeping the ORDER and removing all t. and tu. columns, the query takes 7 seconds.

第二项测试

如果我在i.created_at字段中添加或删除索引，则响应时间保持不变.

If I add or remove an INDEX to the i.created_at field the response time remain the same.

查询:

**我已经注意到，GROUP BY和ORDER BY都降低了查询速度(在更改联接的查询中，我也获得了一点收获.收获是10秒，但根本没有问题遗迹).修改后，EXPLAIN已停止返回文件排序，但仍返回使用临时" **

**EDITED: I'VE NOTICED THAT BOTH GROUP BY AND ORDER BY SLOW DOWN THE QUERY (I've also achieve a little gain in the query changing the joins. The gain was to 10secs, but at all, the problem remains). With the modification, the EXPLAIN have stopped to return filesort, but stills returning "using temporary" **

SELECT SQL_NO_CACHE
        DISTINCT `i`.`id`, 
        `i`.`entity`, 
        `i`.`created_at`, 
        `i`.`collected_at`, 

        `t`.`status_id` AS  `twt_status_id`, 
        `t`.`user_id` AS `twt_user_id`, 
        `t`.`content` AS `twt_content`, 
        `tu`.`id` AS `twtu_id`, 
        `tu`.`screen_name` AS `twtu_screen_name`, 
        `tu`.`profile_image` AS `twtu_profile_image`


        FROM `mtrt_items` AS `i`

        LEFT JOIN `mtrt_users` AS `u` ON i.user_id =u.id

        LEFT JOIN `twt_tweets_content` AS `t` ON t.id =i.id
        LEFT JOIN `twt_users` AS `tu` ON u.id = tu.id

        INNER JOIN `mtrt_items_searches` AS `r` ON i.id =r.item_id
        INNER JOIN `mtrt_searches` AS `s` ON s.id =r.search_id
        INNER JOIN `mtrt_searches_groups` AS `sg` ON sg.search_id =s.id
        INNER JOIN `mtrt_search_groups` AS `g` ON sg.group_id =g.id
        INNER JOIN `account_clients` AS `c` ON g.client_id =c.id                

    ORDER BY `i`.`created_at` DESC 
    LIMIT 100 OFFSET 0

这是EXPLAIN (已编辑):

+----+-------------+-------+--------+--------------------+-----------+---------+------------------------+------+------------------------------+
| id | select_type | table | type   | possible_keys      | key       | key_len | ref                    | rows | Extra                        |
+----+-------------+-------+--------+--------------------+-----------+---------+------------------------+------+------------------------------+
|  1 | SIMPLE      | c     | index  | PRIMARY            | PRIMARY   | 4       | NULL                   |    1 | Using index; Using temporary |
|  1 | SIMPLE      | g     | ref    | PRIMARY,client_id  | client_id | 4       | clubr_new.c.id         |    3 | Using index                  |
|  1 | SIMPLE      | sg    | ref    | group_id,search_id | group_id  | 4       | clubr_new.g.id         |    1 | Using index                  |
|  1 | SIMPLE      | s     | eq_ref | PRIMARY            | PRIMARY   | 4       | clubr_new.sg.search_id |    1 | Using index                  |
|  1 | SIMPLE      | r     | ref    | search_id,item_id  | search_id | 4       | clubr_new.s.id         | 4359 | Using where                  |
|  1 | SIMPLE      | i     | eq_ref | PRIMARY            | PRIMARY   | 8       | clubr_new.r.item_id    |    1 |                              |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY            | PRIMARY   | 8       | clubr_new.i.user_id    |    1 | Using index                  |
|  1 | SIMPLE      | t     | eq_ref | PRIMARY            | PRIMARY   | 4       | clubr_new.i.id         |    1 |                              |
|  1 | SIMPLE      | tu    | eq_ref | PRIMARY            | PRIMARY   | 8       | clubr_new.u.id         |    1 |                              |
+----+-------------+-------+--------+--------------------+-----------+---------+------------------------+------+------------------------------+

这是mtrt_items表:

+--------------+-------------------------------------------------------+------+-----+---------+----------------+
| Field        | Type                                                  | Null | Key | Default | Extra          |
+--------------+-------------------------------------------------------+------+-----+---------+----------------+
| id           | bigint(20)                                            | NO   | PRI | NULL    | auto_increment |
| entity       | enum('twitter','facebook','youtube','flickr','orkut') | NO   | MUL | NULL    |                |
| user_id      | bigint(20)                                            | NO   | MUL | NULL    |                |
| created_at   | datetime                                              | NO   | MUL | NULL    |                |
| collected_at | datetime                                              | NO   |     | NULL    |                |
+--------------+-------------------------------------------------------+------+-----+---------+----------------+

 CREATE TABLE `mtrt_items` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `entity` enum('twitter','facebook','youtube','flickr','orkut') COLLATE utf8_unicode_ci NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `created_at` datetime NOT NULL,
  `collected_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `mtrt_user_id` (`user_id`),
  KEY `entity` (`entity`),
  KEY `created_at` (`created_at`),
  CONSTRAINT `mtrt_items_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `mtrt_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=309650 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

twt_tweets_content是MyISAM，也用于fulltext搜索:

The twt_tweets_content is MyISAM and is also used for fulltext searches:

+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id        | int(11)      | NO   | PRI | NULL    |       |
| user_id   | int(11)      | NO   | MUL | NULL    |       |
| status_id | varchar(100) | NO   | MUL | NULL    |       |
| content   | varchar(200) | NO   | MUL | NULL    |       |
+-----------+--------------+------+-----+---------+-------+

推荐答案

而不是将Order By放入主查询中，而是像这样包装它:

Instead of placing the Order By into the main query, wrap it, like so:

SELECT * FROM (   
  ... your query
) ORDER BY `created at`

看看查询计划.您会发现，在您的情况下，在执行外部联接之前对表mtrt_items进行了排序.在我提供的部分重写中，排序是在外部联接之后应用的，并且应用在更小的集合上.

Take a look at the query plan. You will find that in your case, the sort is performed on your table mtrt_items before the outer join is performed. In the rewrite I've partially provided, the sort is applied after the outer joins, and is applied on a much smaller set.

更新

假设将LIMIT应用于大型集合(500,000?)，您似乎可以在执行任何联接之前执行顶部操作.

Assuming that the LIMIT is being applied to a large set (500,000?), it looks like you can perform the top before doing any of the joins.

SELECT * from (
    SELECT 
    `id`, ... `created_at`, ...
    ORDER BY `i`.`created_at` DESC 
    LIMIT 100 OFFSET 0) as i

    LEFT JOIN `mtrt_users` AS `u` ON i.user_id =u.id

    LEFT JOIN `twt_tweets_content` AS `t` ON t.id =i.id
    LEFT JOIN `twt_users` AS `tu` ON t.user_id = tu.id

    INNER JOIN `mtrt_items_searches` AS `r` ON i.id =r.item_id
    INNER JOIN `mtrt_searches` AS `s` ON s.id =r.search_id
    INNER JOIN `mtrt_searches_groups` AS `sg` ON sg.search_id =s.id
    INNER JOIN `mtrt_search_groups` AS `g` ON sg.group_id =g.id
    INNER JOIN `account_clients` AS `c` ON g.client_id =c.id                

GROUP BY i.id

这篇关于优化MySql查询:订购时太慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！