优化MySql查询:排序太慢

Question

(编辑)有关it self应用程序的更多详细信息，请参见：Simple but heavy application consuming a lot of resources. How to Optimize? (所采用的解决方案同时使用联接和全文搜索)

下面的查询在25秒内运行到大约500.000行。如果我删除命令，它需要0.5秒。

Fisrt试验

保留ORDER并删除所有t和tu。列，查询时间为7秒。

第二次测试

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

查询：

**编辑:我注意到，按组和按顺序都减慢了查询速度(我在查询更改联接时也取得了一些收获。增加了10秒钟，但问题仍然存在)。修改后，解释已经停止返回文件短，但是仍然返回“使用临时”**

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搜索：

+-----------+--------------+------+-----+---------+-------+
| 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    |       |
+-----------+--------------+------+-----+---------+-------+

Answer 1

与其将Order By放在主查询中，不如将其包装起来，如下所示：

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

查看查询计划。您会发现，在执行外部联接之前，在表mtrt_items上执行排序。在我部分提供的重写中，排序是在外部联接之后应用的，并且应用在一个小得多的集合上。

更新

假设限制被应用于一个大集合(500,000?)，看起来您可以在执行任何连接之前执行顶部操作。

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

Answer 2

在初始查询中不要包含VARCHAR/TEXT字段。这将使用内存引擎创建排序所需的临时表，这将大大提高效率。以后，您可以使用另一个查询收集文本字段，而无需进行任何排序，只需在主键字段上设置一个条件，然后将脚本中的数据合并(假设您正在使用其中的一个)。

还可以去掉任何联接、(内部或外部)，而这些数据实际上并不是从这些数据中提取的。