【mysql】mysql查询性能问题，加了order by速度慢了差不多50倍
基本情况：
数据表差不多有一千万条数据，用的是mycat分库。
数据表的里的索引有
- PRIMARY id
- 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条件的索引没有使用而使用了主键扫描
回答
慢的原因主要是排序，尤其是分片的排序，
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;
关于order by的查询优化可以看一下:
主要介绍了两个方法：
- 第一个是
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
把范围查询放在最后;
索引:
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
排序也是遵循索引顺序的，所以索引顺序是至关重要的一部分。
是可以的，不过我发现你的主键是没有自增的，你可以检查下是否有受主键 ID 的一个影响，又或者 mysql 的版本影响。我所使用的 mysql 版本为 5.5.53
编辑一下我的答案，晚上回来查了一下《高性能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：这是晚上专门查书看的资料，有疑问或者觉得我错误的可以再讨论一下？
explainSELECT
`id`
FROM
`eis_email_history`
WHERE
`AppName` = 21
AND `custidStatus` IN (0, 1, 2)
AND `channel` = '***'
ORDER BY
`id`
LIMIT 50
这个执行看下？
分享下 https://segmentfault.com/a/11...
以上是 【mysql】mysql查询性能问题，加了order by速度慢了差不多50倍 的全部内容， 来源链接： utcz.com/a/71768.html
