MySQL排序非常缓慢

Question

MySQL排序非常缓慢

4

我知道有很多关于Order By的问题，也有各种各样的解决方案，但是我想要的是针对我下面设置的数据结构的具体问题。

我已经设置了以下2个表：

表设置

CREATE TABLE `record` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `file_group_id` INT(11) NOT NULL,
  `status_id` INT(11) NOT NULL,
  `title` VARCHAR(3000) NOT NULL,
  `date_created` DATETIME NOT NULL,
  `user_created` INT(11) NOT NULL,
  `publish_date` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_RECORD_FGID` (`file_group_id`)
);

CREATE TABLE `record_meta_text` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `record_id` INT(11) NOT NULL,
  `column_id` INT(11) NOT NULL,
  `value` VARCHAR(3000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_RMT_VALUE` (`value`(800)),
  KEY `IDX_RMT_COL_ID` (`column_id`),
  KEY `IDX_RMT_RECORD_ID_COL_ID` (`record_id`,`column_id`,`value`(800))
)

Query without Order By

SELECT
    r.title AS col_title,
    rmt_2780.value AS col_2780,
    rmt_2781.value AS col_2781,
    rmt_18474.value AS col_18474
FROM 
    record r
INNER JOIN record_meta_text AS rmt_2780 ON rmt_2780.record_id = r.id AND rmt_2780.column_id = 2780
INNER JOIN record_meta_text AS rmt_2781 ON rmt_2781.record_id = r.id AND rmt_2781.column_id = 2781
INNER JOIN record_meta_text AS rmt_18474 ON rmt_18474.record_id = r.id AND rmt_18474.column_id = 18474
WHERE 
    r.file_group_id = 2350 
    AND r.status_id = 1
LIMIT 0, 50

输出和解释

执行时间：0.004秒

这是EXPLAIN输出：

    id  select_type  table      type    possible_keys                            key                       key_len  ref                             rows  Extra        
------  -----------  ---------  ------  ---------------------------------------  ------------------------  -------  ----------------------------  ------  -------------
     1  SIMPLE       r          ref     PRIMARY,IDX_RECORD_FGID                  IDX_RECORD_FGID           4        const                         527895  Using where  
     1  SIMPLE       rmt_18474  ref     IDX_RMT_COL_ID,IDX_RMT_RECORD_ID_COL_ID  IDX_RMT_RECORD_ID_COL_ID  8        file_cabinet_data.r.id,const       1  (NULL)       
     1  SIMPLE       rmt_2780   ref     IDX_RMT_COL_ID,IDX_RMT_RECORD_ID_COL_ID  IDX_RMT_RECORD_ID_COL_ID  8        file_cabinet_data.r.id,const       1  (NULL)       
     1  SIMPLE       rmt_2781   ref     IDX_RMT_COL_ID,IDX_RMT_RECORD_ID_COL_ID  IDX_RMT_RECORD_ID_COL_ID  8        file_cabinet_data.r.id,const       1  (NULL)

按顺序查询

SELECT
    r.title AS col_title,
    rmt_2780.value AS col_2780,
    rmt_2781.value AS col_2781,
    rmt_18474.value AS col_18474
FROM 
    record r
INNER JOIN record_meta_text AS rmt_2780 ON rmt_2780.record_id = r.id AND rmt_2780.column_id = 2780
INNER JOIN record_meta_text AS rmt_2781 ON rmt_2781.record_id = r.id AND rmt_2781.column_id = 2781
INNER JOIN record_meta_text AS rmt_18474 ON rmt_18474.record_id = r.id AND rmt_18474.column_id = 18474
WHERE 
    r.file_group_id = 2350 
    AND r.status_id = 1
ORDER BY col_2780
LIMIT 0, 50

输出和解释

执行时间：35.237秒

    id  select_type  table      type    possible_keys                            key                       key_len  ref                             rows  Extra                                         
------  -----------  ---------  ------  ---------------------------------------  ------------------------  -------  ----------------------------  ------  ----------------------------------------------
     1  SIMPLE       r          ref     PRIMARY,IDX_RECORD_FGID                  IDX_RECORD_FGID           4        const                         527895  Using where; Using temporary; Using filesort  
     1  SIMPLE       rmt_2780   ref     IDX_RMT_COL_ID,IDX_RMT_RECORD_ID_COL_ID  IDX_RMT_RECORD_ID_COL_ID  8        file_cabinet_data.r.id,const       1  (NULL)                                        
     1  SIMPLE       rmt_2781   ref     IDX_RMT_COL_ID,IDX_RMT_RECORD_ID_COL_ID  IDX_RMT_RECORD_ID_COL_ID  8        file_cabinet_data.r.id,const       1  (NULL)                                        
     1  SIMPLE       rmt_18474  ref     IDX_RMT_COL_ID,IDX_RMT_RECORD_ID_COL_ID  IDX_RMT_RECORD_ID_COL_ID  8        file_cabinet_data.r.id,const       1  (NULL)

问题和解决方案

我的问题是，如何使这个order by不要花费太长时间。在多种情况下，我将有多个order by条件，以及where语句。LIMIT / OFFSET用于分页。

目前record表有1,139,119条记录。

目前file_meta_text表有7,584,428条记录。

我有一个带有基本模式和查询的SQLFiddle，但没有添加数据（记录太多）：

http://sqlfiddle.com/#!2/6ffc3/1

任何帮助都将不胜感激。

- CodeLikeBeaker

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- AndreaPosadino · Answer 1

我可以建议使用（因为有where子句）：

KEY `IDX_RECORD_FGID` (`file_group_id`,`status_id`)

改为：

KEY `IDX_RECORD_FGID` (`file_group_id`)

并且（因为完全索引）

 KEY  `IDX_RMT_VALUE` (`value`)

改为：

KEY  `IDX_RMT_VALUE` (`value`(800))

但是要记住，在900字节之后，MySQL就不会索引，这就是关于order by的问题，这是一个使用未完全索引的字段的MySQL操作。您确定需要长度为3000的"value"字段吗？

我认为您可以使用这个更简洁的查询来获取相同的信息：

SELECT
    r.title AS col_title,
    rmt.value AS value,
    rmt.column_id AS column
FROM 
    record r
INNER JOIN record_meta_text AS rmt ON rmt.record_id = r.id
WHERE 
    rmt.column_id IN(2780,2781,18474)
    AND
    r.file_group_id = 2350 
    AND r.status_id = 1
LIMIT 0, 50