在MySQL 5.6中优化SQL查询

Question

系统是Amazon (MySql 5.6.x)，基于Moodle2.8的软件是我目前正在开发的表：

CREATE TABLE `mdl_course_categories` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `idnumber` varchar(100) DEFAULT NULL,
  `description` longtext,
  `descriptionformat` tinyint(2) NOT NULL DEFAULT '0',
  `parent` bigint(10) NOT NULL DEFAULT '0',
  `sortorder` bigint(10) NOT NULL DEFAULT '0',
  `coursecount` bigint(10) NOT NULL DEFAULT '0',
  `visible` tinyint(1) NOT NULL DEFAULT '1',
  `visibleold` tinyint(1) NOT NULL DEFAULT '1',
  `timemodified` bigint(10) NOT NULL DEFAULT '0',
  `depth` bigint(10) NOT NULL DEFAULT '0',
  `path` varchar(255) NOT NULL DEFAULT '',
  `theme` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `mdl_courcate_par_ix` (`parent`),
  KEY `mdl_carcoute_tmid` (`timemodified`,`id`),
  KEY `mdl_tm_field` (`timemodified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我最初的查询如下：

SELECT 
    id,
    name,
    description,
    FROM_UNIXTIME(timemodified) AS timemodified,
    timemodified AS traw
FROM
    mdl_course_categories
WHERE
    timemodified BETWEEN 1360602072 AND 1446736233
        OR 
    id > 0
ORDER BY id ASC
LIMIT 0 , 50000

对此查询的解释是：

+----+-------------+-----------------------+-------+----------------------------------------+---------+---------+------+------+----------+-------------+
| id | select_type | table                 | type  | possible_keys                          | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------------------+-------+----------------------------------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | mdl_course_categories | index | PRIMARY,mdl_carcoute_tmid,mdl_tm_field | PRIMARY | 8       | NULL |   68 |   100.00 | Using where |
+----+-------------+-----------------------+-------+----------------------------------------+---------+---------+------+------+----------+-------------+

数据库专家建议我避免OR并将查询转换为：

SELECT * FROM
(
    (
        SELECT 
            id,
            name,
            description,
            FROM_UNIXTIME(timemodified) AS timemodified,
            timemodified AS traw
        FROM
            mdl_course_categories
        WHERE
            timemodified BETWEEN 1360602072 AND 1446736233
    )
    UNION ALL
    (
        SELECT 
            id,
            name,
            description,
            FROM_UNIXTIME(timemodified) AS timemodified,
            timemodified AS traw
        FROM
            mdl_course_categories
        WHERE
            id > 0
    )
) t
ORDER BY id ASC
LIMIT 0 , 50000

解释如下：

+----+--------------+-----------------------+-------+--------------------------------+-------------------+---------+------+------+----------+-----------------------+
| id | select_type  | table                 | type  | possible_keys                  | key               | key_len | ref  | rows | filtered | Extra                 |
+----+--------------+-----------------------+-------+--------------------------------+-------------------+---------+------+------+----------+-----------------------+
|  1 | PRIMARY      | <derived2>            | ALL   | NULL                           | NULL              | NULL    | NULL |   80 |   100.00 | Using filesort        |
|  2 | DERIVED      | mdl_course_categories | range | mdl_carcoute_tmid,mdl_tm_field | mdl_carcoute_tmid | 8       | NULL |   12 |   100.00 | Using index condition |
|  3 | UNION        | mdl_course_categories | range | PRIMARY                        | PRIMARY           | 8       | NULL |   68 |   100.00 | Using where           |
| NULL | UNION RESULT | <union2,3>            | ALL   | NULL                           | NULL              | NULL    | NULL | NULL |     NULL | Using temporary       |
+----+--------------+-----------------------+-------+--------------------------------+-------------------+---------+------+------+----------+-----------------------+

你认为如何？还能做得更好吗？

Answer 1

优化的查询将包括子查询中的limit和order by，并使用正确的索引：

SELECT *
FROM ((SELECT id, name, description,
              FROM_UNIXTIME(timemodified) AS timemodified, timemodified AS traw
       FROM mdl_course_categories
       WHERE timemodified BETWEEN 1360602072 AND 1446736233
       ORDER BY id
       LIMIT 50000
      ) UNION ALL
      (SELECT id, name, description,
              FROM_UNIXTIME(timemodified) AS timemodified, timemodified AS traw
       FROM mdl_course_categories
       WHERE id > 0 AND
             NOT timemodified BETWEEN 1360602072 AND 1446736233
       ORDER BY id
       LIMIT 50000
      )
     ) t
ORDER BY id ASC
LIMIT 0 , 50000;

您需要的索引是mdl_course_categories(id, timemodified)和mdl_course_categories(timemodified)。不幸的是，您不一定要退出排序，因为where子句有不等式。然而，对10万条记录进行排序应该比所有记录都要好。

注意，第二个WHERE子句被更改为从第一个子句中排除记录。