mysql查询性能问题，加了order by速度慢了差不多50倍

Question

基本情况：

数据表差不多有一千万条数据，用的是mycat分库。

数据表的里的索引有

1. PRIMARY id
2. AppName (AppName, custidStatus, channel)

建表语句如下

CREATE TABLE `eis_email_history` (
  `id` bigint(20) NOT NULL DEFAULT '0',
  `AppName` int(11) NOT NULL DEFAULT '0',
  `emailto` varchar(256) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '收件人email',
  `emailfrom` varchar(256) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '寄件人email',
  `subject` varchar(256) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '邮件标题',
  `content` text COLLATE utf8_unicode_ci NOT NULL COMMENT '邮件内容',
  `sendtime` int(11) NOT NULL DEFAULT '0' COMMENT '发送邮件的unixtime',
  `sendstatus` tinyint(4) NOT NULL DEFAULT '0' COMMENT '发件状态，0：发送队列中，1：已发送，2：发送失败，3：预制邮箱记录的邮箱暂时不可用',
  `channel` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '来源渠道',
  `AmazonOrderId` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '???',
  `ASIN` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'asin',
  `attachment` text COLLATE utf8_unicode_ci COMMENT '邮件附件，格式为，有多个附件的要分行记录，每行2个参数（以“tab制表符”+“/”+“tab制表符”分隔），第一个参数必选，为文件所在服务器路径，第二个参数为可选，如果输入第二个参数，那么附件名即为第二参数，否则就已文件本身名称为附件名',
  `templateId` bigint(20) NOT NULL DEFAULT '0' COMMENT '模板Id',
  `custidStatus` int(20) NOT NULL DEFAULT '0' COMMENT '差评Id',
  PRIMARY KEY (`id`),
  KEY `emailto` (`emailto`(255)),
  KEY `sendtime` (`sendtime`),
  KEY `sendstatus` (`sendstatus`),
  KEY `emailfrom` (`emailfrom`(255)),
  KEY `asin` (`ASIN`),
  KEY `orderid` (`AmazonOrderId`),
  KEY `AppName` (`AppName`,`custidStatus`,`channel`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

首先是没有加order by的

SELECT 
  `id`,
  `emailto`,
  `channel`,
  `AppName`,
  `AmazonOrderId` 
FROM
  `eis_email_history` 
WHERE `AppName` = 21 
  AND `custidStatus` IN (0, 1, 2) 
  AND `channel` = '***' 
LIMIT 50 

这个查询基本都是秒查询,然后explain的结果是：

然后是加了order by id desc的,

SELECT 
  `id`,
  `emailto`,
  `channel`,
  `AppName`,
  `AmazonOrderId` 
FROM
  `eis_email_history` 
WHERE `AppName` = 21 
  AND `custidStatus` IN (0, 1, 2) 
  AND `channel` = '***' 
ORDER BY id DESC 
LIMIT 50 

然后这个查询基本都在1分钟以上，explain结果如下，

看情况是加了order by导致where条件的索引没有使用而使用了主键扫描

Answer 1

慢的原因主要是排序，尤其是分片的排序，
mycat会在所有分片进行排序操作取limit50，然后在mycat内存中再次排序取limit50
如果不排序的话，mycat只需要随便取一个分片的50条即可，这个计算量差别是很大的，分片越多越慢
再就是你的写法确实需要改进一下
按主键排序的话，innodb的索引是带有主键的，
所以where加order是可以走索引（覆盖索引），前提是select不能有索引字段以外的列

SELECT 
  a.`id`,
  a.`emailto`,
  a.`channel`,
  a.`AppName`,
  a.`AmazonOrderId` 
from eis_email_history a join 
(select id FROM
  `eis_email_history` 
 WHERE `AppName` = 21 
  AND `custidStatus` IN (0, 1, 2) 
  AND `channel` = '***' 
 ORDER BY id DESC 
 LIMIT 50) b 
on a.id=b.id;

Answer 2

关于order by的查询优化可以看一下:

• MySQL ORDER BY/LIMIT performance: late row lookups

主要介绍了两个方法：

• 第一个是FORCE INDEX (PRIMARY)：这个理解很直白就是强行加索引
• 第二个是late row lookups，也就是文章的重点，其实就是先构造一个只有id的子查询，然后再join一起。这样极大的提高效率。如下示例代码，o是通过你的表和只有id查询出来的临时字表，l是要join一起包含所有字段的表。

SELECT  xx,xxx,....
FROM    (
        SELECT  id
        FROM    <你的表>
        ORDER BY
                id
        LIMIT <返回条数的范围>
        ) o 
JOIN    <你的表> l
ON      l.id = o.id  
ORDER BY
        l.id

Answer 3

把范围查询放在最后;

索引:
AppName (AppName, channel, custidStatus)

SELECT 
  `id`,
  `emailto`,
  `channel`,
  `AppName`,
  `AmazonOrderId` 
FROM
  `eis_email_history` 
WHERE `AppName` = 21 
  AND `channel` = '***' 
  AND `custidStatus` IN (0, 1, 2) 
ORDER BY id DESC 
LIMIT 50 

Answer 4

排序也是遵循索引顺序的，所以索引顺序是至关重要的一部分。

是可以的，不过我发现你的主键是没有自增的，你可以检查下是否有受主键 ID 的一个影响，又或者 mysql 的版本影响。我所使用的 mysql 版本为 5.5.53

Answer 5

编辑一下我的答案，晚上回来查了一下《高性能mysql》。我的答案如下：将索引改为

AppName (id, AppName, custidStatus, channel)

order by 子句需要索引的列顺序需要满足索引的最左前缀的要求，所以ID需要排在第一。

有一种情况下order by子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候。但是查询的where

WHERE `AppName` = 21 
  AND `custidStatus` IN (0, 1, 2) 
  AND `channel` = '***' 
)

custidStatus列中有多个等于条件，对于排序来说，这也是一种范围查询，所以不满足最左前缀的要求。所以下面的索引是错误的。

AppName (AppName, custidStatus, channel，id)

PS：这是晚上专门查书看的资料，有疑问或者觉得我错误的可以再讨论一下？

Answer 6

explain
SELECT
    `id`
FROM
    `eis_email_history`
WHERE
    `AppName` = 21
AND `custidStatus` IN (0, 1, 2)
AND `channel` = '***'
ORDER BY
    `id`
LIMIT 50

这个执行看下？

Answer 7

分享下 https://segmentfault.com/a/11...