鍍金池/ 問答/數(shù)據(jù)庫  網(wǎng)絡(luò)安全/ mysql查詢性能問題，加了order by速度慢了差不多50倍

mysql查詢性能問題，加了order by速度慢了差不多50倍

基本情況：

數(shù)據(jù)表差不多有一千萬條數(shù)據(jù)，用的是mycat分庫。

數(shù)據(jù)表的里的索引有
  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 '郵件標(biāo)題',
  `content` text COLLATE utf8_unicode_ci NOT NULL COMMENT '郵件內(nèi)容',
  `sendtime` int(11) NOT NULL DEFAULT '0' COMMENT '發(fā)送郵件的unixtime',
  `sendstatus` tinyint(4) NOT NULL DEFAULT '0' COMMENT '發(fā)件狀態(tài)，0：發(fā)送隊(duì)列中，1：已發(fā)送，2：發(fā)送失敗，3：預(yù)制郵箱記錄的郵箱暫時(shí)不可用',
  `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 '郵件附件，格式為，有多個(gè)附件的要分行記錄，每行2個(gè)參數(shù)（以“tab制表符”+“/”+“tab制表符”分隔），第一個(gè)參數(shù)必選，為文件所在服務(wù)器路徑，第二個(gè)參數(shù)為可選，如果輸入第二個(gè)參數(shù)，那么附件名即為第二參數(shù)，否則就已文件本身名稱為附件名',
  `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
這個(gè)查詢基本都是秒查詢,然后explain的結(jié)果是：
圖片描述

然后是加了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

然后這個(gè)查詢基本都在1分鐘以上，explain結(jié)果如下，圖片描述

看情況是加了order by導(dǎo)致where條件的索引沒有使用而使用了主鍵掃描
回答
編輯回答
維他命

關(guān)于order by的查詢優(yōu)化可以看一下:

主要介紹了兩個(gè)方法：

  • 第一個(gè)是FORCE INDEX (PRIMARY)：這個(gè)理解很直白就是強(qiáng)行加索引
  • 第二個(gè)是late row lookups，也就是文章的重點(diǎn)，其實(shí)就是先構(gòu)造一個(gè)只有id的子查詢，然后再join一起。這樣極大的提高效率。如下示例代碼，o是通過你的表和只有id查詢出來的臨時(shí)字表，l是要join一起包含所有字段的表。
SELECT  xx,xxx,....
FROM    (
        SELECT  id
        FROM    <你的表>
        ORDER BY
                id
        LIMIT <返回條數(shù)的范圍>
        ) o 
JOIN    <你的表> l
ON      l.id = o.id  
ORDER BY
        l.id
2017年2月27日 07:13
編輯回答
懶洋洋

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

2017年2月15日 16:40
編輯回答
舊顏

慢的原因主要是排序，尤其是分片的排序，
mycat會(huì)在所有分片進(jìn)行排序操作取limit50，然后在mycat內(nèi)存中再次排序取limit50
如果不排序的話，mycat只需要隨便取一個(gè)分片的50條即可，這個(gè)計(jì)算量差別是很大的，分片越多越慢
再就是你的寫法確實(shí)需要改進(jìn)一下
按主鍵排序的話，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;
2017年2月10日 21:32
編輯回答
浪婳

把范圍查詢放在最后;

索引:
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
2018年6月4日 17:07
編輯回答
尛憇藌

排序也是遵循索引順序的，所以索引順序是至關(guān)重要的一部分。
圖片描述

是可以的，不過我發(fā)現(xiàn)你的主鍵是沒有自增的，你可以檢查下是否有受主鍵 ID 的一個(gè)影響，又或者 mysql 的版本影響。我所使用的 mysql 版本為 5.5.53

2017年7月17日 02:32
編輯回答
膽怯

編輯一下我的答案，晚上回來查了一下《高性能mysql》。我的答案如下：將索引改為

AppName (id, AppName, custidStatus, channel)

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

有一種情況下order by子句可以不滿足索引的最左前綴的要求，就是前導(dǎo)列為常量的時(shí)候。但是查詢的where

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

custidStatus列中有多個(gè)等于條件，對于排序來說，這也是一種范圍查詢，所以不滿足最左前綴的要求。所以下面的索引是錯(cuò)誤的。

AppName (AppName, custidStatus, channel，id)

PS：這是晚上專門查書看的資料，有疑問或者覺得我錯(cuò)誤的可以再討論一下？

2017年3月23日 13:57
編輯回答
陌顏
explain
SELECT
    `id`
FROM
    `eis_email_history`
WHERE
    `AppName` = 21
AND `custidStatus` IN (0, 1, 2)
AND `channel` = '***'
ORDER BY
    `id`
LIMIT 50

這個(gè)執(zhí)行看下？

2018年1月16日 18:37