6

基本情况:

数据表差不多有一千万条数据,用的是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条件的索引没有使用而使用了主键扫描
张胜 148
2018-08-13 提问
7 个回答
5

慢的原因主要是排序,尤其是分片的排序,
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;
2

关于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
2

把范围查询放在最后;

索引:
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 
1

排序也是遵循索引顺序的,所以索引顺序是至关重要的一部分。
图片描述

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

1

我看了下我这个是因为加了order by id,然后id是主键,查询就使用了主键索引,然后where条件里的AppName那个索引没有使用导致的查询慢

张胜 · 2018年08月13日

展开评论
1

编辑一下我的答案,晚上回来查了一下《高性能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:这是晚上专门查书看的资料,有疑问或者觉得我错误的可以再讨论一下?

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

这个执行看下?

撰写答案

推广链接