MySQL连接的糟糕性能--如何改进？

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,000，正如您从上面可以猜到的那样，数字的存储方式略有不同：

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

据我所知，我已经对每个表中的重要列进行了索引。

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

更新

添加EXPLAIN输出

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)的结果连接表。

一些数据库(如甲骨文)可以创建基于funcion的索引，但其他数据库(如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将使用新创建的索引，并将以更快的速度执行查询。