mysql - 加入两个大表很慢

Question

我有两个大表，我必须加入

第一个:

CREATE TABLE IF NOT EXISTS `cdr` (
  `calldate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `clid` varchar(80) NOT NULL DEFAULT '',
  `src` varchar(80) NOT NULL DEFAULT '',
  `dst` varchar(80) NOT NULL DEFAULT '',
  `dcontext` varchar(80) NOT NULL DEFAULT '',
  `channel` varchar(80) NOT NULL DEFAULT '',
  `dstchannel` varchar(80) NOT NULL DEFAULT '',
  `lastapp` varchar(80) NOT NULL DEFAULT '',
  `lastdata` varchar(80) NOT NULL DEFAULT '',
  `duration` decimal(11,6) NOT NULL DEFAULT '0.000000',
  `billsec` decimal(11,6) NOT NULL DEFAULT '0.000000',
  `disposition` varchar(45) NOT NULL DEFAULT '',
  `amaflags` int(11) NOT NULL DEFAULT '0',
  `accountcode` varchar(20) NOT NULL DEFAULT '',
  `uniqueid` varchar(32) NOT NULL DEFAULT '',
  `userfield` varchar(255) NOT NULL DEFAULT '',
  `cost` char(20) NOT NULL DEFAULT 'none',
  `zone` char(60) NOT NULL DEFAULT 'none',
  `profile` char(10) NOT NULL DEFAULT 'none',
  `tariff` char(8) NOT NULL DEFAULT 'none',
  `status` char(10) NOT NULL DEFAULT 'none',
  `answer` datetime NOT NULL,
  `end` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for table `cdr`
--
ALTER TABLE `cdr`
 ADD KEY `src` (`src`), 
 ADD KEY `accountcode` (`accountcode`), 
 ADD KEY `status` (`status`), 
 ADD KEY `uniqueid` (`uniqueid`), 
 ADD KEY `calldate` (`calldate`);

第二个

CREATE TABLE IF NOT EXISTS `routes` (
  `id` int(4) NOT NULL,
  `route` char(35) NOT NULL,
  `zonenum` int(4) NOT NULL,
  `comment` char(50) CHARACTER SET latin2 DEFAULT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '1',
  `wholesaledst` varchar(60) NOT NULL,
  `nabava` char(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


ALTER TABLE `routes`
ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `route` (`route`), ADD KEY `zonenum` (`zonenum`);

第一个表包含大约 350 万行，第二个表包含大约 35000 行。对于第一个表中的每条记录，我必须从第二个表中获取 zonenum。

这是我的问题

SELECT src, accountcode, zonenum, calldate, answer, end
FROM cdr  
LEFT OUTER JOIN routes ON src LIKE route
WHERE calldate BETWEEN '2015-03-01' AND '2015-04-01' AND status = 'INCOMING' AND accountcode != 110 AND disposition = 'ANSWERED';

例如src 看起来像 095346435，route 看起来像 095%

查询需要大约 7 分钟才能执行。如果我删除加入它只有 1.5 秒。当检查 mysql 慢速查询日志时，它说查询已经检查了 140 万行，这大约是 where 子句之后的行数 * routes 表中的行数。我试过使用子查询、临时表……一切，但总是太慢有什么办法可以加快查询速度吗？还是我错过了一些索引？请帮助我，我很绝望。

更新

如果有帮助，这里是EXPLAIN结果

Answer 1

由于 routes 小得多，因此可能值得连接到包含它的子查询。在该查询中，您可以SELECT LEFT(route, 3) AS rtPre, zonenum 并加入 rtPre，如下所示:

SELECT cdr.src, cdr.accountcode, r.zonenum, cdr.calldate, cdr.answer, cdr.end
FROM cdr  
   LEFT JOIN (
      SELECT LEFT(route, 3) AS rtPre, zonenum
      FROM routes
   ) AS r ON LEFT(cdr.src, 3) = r.rtPre
WHERE cdr.calldate BETWEEN '2015-03-01' AND '2015-04-01' 
   AND cdr.status = 'INCOMING' 
   AND cdr.accountcode != 110 
   AND cdr.disposition = 'ANSWERED'
;

如果仍然没有帮助，您可以将该子查询插入到临时表中，并在 rtPre 上建立索引；并在 JOIN 中使用该表。

如果这种查询要频繁运行，您甚至可能需要考虑在 routes 中建立一个永久性“前缀”字段，它可以被索引。

...当然，这做出了一个巨大的假设，即所有 cdr.src 值都是 3 个字符和一个 %。 (如果这是一个错误的假设，前缀类型的解决方案可能仍然可用。LEFT(cdr.src, [standard prefix length]) = r.rtPre AND r.route LIKE cdr.src 可能需要利用最小前缀减少所需的 LIKE 比较。