mysql - MySQL JOIN 性能不佳 - 如何改进？

Question

有点通用的问题标题，但我有以下查询:

SELECT t.from_number, COUNT(*) AS calls
FROM t
WHERE t.organisation_id = 999
AND t.direction = 'inbound'
AND t.start_time BETWEEN '2014-03-26' AND NOW()
AND t.from_number != ''
GROUP BY t.from_number
ORDER BY calls DESC LIMIT 20

它的执行时间为488ms。

但是，除了从该表中检索数据之外，我还需要查找该号码属于谁。

SELECT t.from_number, COUNT(*) AS calls
FROM t
LEFT JOIN n on CONCAT('44', n.number) = t.from_number
WHERE t.organisation_id = 999
AND t.direction = 'inbound'
AND t.start_time BETWEEN '2014-03-26' AND NOW()
AND t.from_number != ''
GROUP BY t.from_number
ORDER BY calls DESC LIMIT 20

一旦我添加 JOIN，查询执行时间就会跳至 8 - 12 秒，而这只是为了找到该号码所属的组织，我需要另一个联接之后从组织表中检索组织名称。

t 和 n 的基数分别为 > 2,000,000 和 ~ 63,000，并且，正如您从上面可以猜到的，每个数字的存储方式略有不同:

t 将数字存储为 123456789，因为国家/地区代码 (44) 存储在单独的列中，但 n > 将数字存储为 44123456789 这就是为什么我需要使用 CONCAT 但我认为这不会影响性能，因为它不在 WHERE 中> 条款。

据我所知，我已经为每个表中的重要列建立了索引。

对于如何提高这些表的查询性能，有什么建议吗？

更新

解释已添加输出

id, select_type, table, possible_keys, key, key_len, ref, rows, Extra

1   SIMPLE  t   index_merge organisation_id,start_time,direction,from_number    organisation_id,direction   4,13    NULL    4174    Using intersect(organisation_id,direction); Using where; Using temporary; Using filesort
1   SIMPLE  n   index   NULL    number  768 NULL    62759   Using index

Answer 1

问题出在 JOIN 子句上:

LEFT JOIN n on CONCAT('44', n.number) = t.from_number

它使用函数 CONCAT('44', n.number) 的结果连接表。

某些数据库(如 Oracle)可以基于函数创建索引，但其他数据库(如 MySQL)则不能。因此，它不能使用表n上的任何索引来进行连接。

解决方案是使用所用函数的结果在 n 上创建一个新列并为其建立索引。

您可以使用类似于以下的代码:

ALTER TABLE n ADD COLUMN extended_number varchar(128) null;

UPDATE n
   SET extended_number = CONCAT('44', number);

CREATE INDEX ext_numb_idx 
          ON n.extended_number;

此后，修改查询的JOIN子句:

   SELECT t.from_number, COUNT(*) AS calls
     FROM t
LEFT JOIN n on n.extended_number = t.from_number
    WHERE t.organisation_id = 999
      AND t.direction = 'inbound'
      AND t.start_time BETWEEN '2014-03-26' AND NOW()
      AND t.from_number != ''
 GROUP BY t.from_number
 ORDER BY calls DESC LIMIT 20

然后 MySQL 将使用新创建的索引，并且执行查询的速度会快得多。