MySQL : Left outer join sql takes long time

Question

我有这个查询，执行它需要很长时间

SELECT DISTINCT ticket.`id`, 
                `sender`, 
                `text`, 
                `receivedtime`, 
                `priorityid`, 
                `cityid`, 
                `categoryid`, 
                `statusid`, 
                `activeuserid`, 
                `note`, 
                `operationid`, 
                '' AS SMSHistory, 
                '' AS replySMS, 
                '' AS ticketHistory 
FROM   `ticket` 
       LEFT OUTER JOIN tickethistory 
                    ON tickethistory.ticketid = ticket.id 
       LEFT OUTER JOIN users 
                    ON tickethistory.uid = users.uid 
ORDER  BY ticket.`id` DESC 
LIMIT  0, 50 

这是表格结构:

门票:

CREATE TABLE   `ticket` (
    `id` int(10) NOT NULL AUTO_INCREMENT,
    `sender` varchar(50) NOT NULL,
    `text` text NOT NULL,
    `receivedTime` datetime NOT NULL,
    `priorityId` int(10) NOT NULL,
    `cityId` int(10) NOT NULL,
    `categoryId` int(10) NOT NULL,
    `statusId` int(10) NOT NULL,
    `activeUserId` int(11) NOT NULL,
    `note` text NOT NULL,
    `operationId` int(10) NOT NULL,
    `gateway` varchar(80) NOT NULL,
    KEY `Index 1` (`id`),
    KEY `sender` (`sender`),
    KEY `priorityId` (`priorityId`),
    KEY `cityId` (`cityId`),
    KEY `categoryId` (`categoryId`),
    KEY `statusId` (`statusId`)
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

  ----

Ticketh历史:

  CREATE TABLE   `tickethistory` (
    `id` int(10) NOT NULL AUTO_INCREMENT,
    `ticketId` int(10) NOT NULL,
    `uid` int(11) NOT NULL,
    `actionId` int(10) NOT NULL,
    `time` datetime NOT NULL,
    `param1` text NOT NULL,
    `param2` text NOT NULL,
    `param3` text NOT NULL,
    KEY `Index 1` (`id`),
    KEY `ticketId` (`ticketId`),
    KEY `uid` (`uid`)
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

  ---- 

用户:

CREATE TABLE IF NOT EXISTS `users` (
  `Uid` int(11) NOT NULL AUTO_INCREMENT,
  `UserName` varchar(255) NOT NULL,
  `Upassword` varchar(32) NOT NULL,
  `UName` text NOT NULL,
  `Ucountry` text NOT NULL,
  `Umobile` varchar(255) NOT NULL,
  `UregisterDate` date NOT NULL DEFAULT '0000-00-00',
  `Ugroup` char(1) NOT NULL DEFAULT 'U',
  `Usender` varchar(11) NOT NULL DEFAULT 'SMS',
  `Ucredits` decimal(11,2) NOT NULL DEFAULT '0.00',
  `Uemail` varchar(255) NOT NULL,
  `CreditUpdatedDate` date DEFAULT NULL,
  `USMSC` varchar(30) NOT NULL,
  `repeatedDuration` int(10) DEFAULT '0',
  `langid` varchar(10) DEFAULT 'Ar',
  `parentId` int(11) NOT NULL DEFAULT '0',
  `Usess` varchar(255) NOT NULL DEFAULT '0',
  PRIMARY KEY (`Uid`),
  UNIQUE KEY `UserName` (`UserName`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

---

这是解释命令的结果:

  'id';'select_type';'table';'type';'possible_keys';'key';'key_len';'ref';'rows';'Extra'
  '1';'SIMPLE';'ticket';'ALL';'';'';'';'';'348580';'Using temporary; Using filesort'
  '1';'SIMPLE';'tickethistory';'ref';'ticketId';'ticketId';'4';'ticket.id';'2';'Distinct'
  '1';'SIMPLE';'users';'eq_ref';'PRIMARY';'PRIMARY';'4';'tickethistory.uid';'1';'Using index; Distinct'

Answer 1

EXPLAIN 表明它正在使用临时表和文件排序来对 TICKET 表中的记录进行排序。这有点奇怪，因为您正在排序的 ID 上有一个索引，但考虑到它匹配 350K 条记录，这可能就是它速度慢的原因。

当您尝试查找最新记录时，请尝试包含限制性“where”子句，例如将搜索限制为上周(不要忘记在 receivedTime 上创建索引)。

您还可以考虑不使用从 ticketHistory 到 User 的外部联接 - UID 列不是 NULL，因此没有匹配用户的记录不应该存在。