mysql - 特定mysql查询的性能优化？

Question

如何在保持相同结果的同时优化以下查询的性能？

SELECT
    a.country,
    count(DISTINCT b.`hash`)
FROM
    audience a,
    behaviour b
WHERE
    DATE(b.`timestamp`) = '2016-03-31'
    AND b.`hash` = a.`hash`
GROUP BY
    a.country;

Answer 1

在 WHERE 子句中使用的所有列上添加索引，并使用 BETWEEN...AND 作为时间戳，而不是 DATE() 函数:

ALTER TABLE `behaviour` 
ADD INDEX `ts_IDX` (`timestamp`),
ADD INDEX `hash_IDX` (`hash`);
---------------------------------
ALTER TABLE `audience` 
ADD INDEX `hash_IDX` (`hash`);
---------------------------------
SELECT
    a.country,
    COUNT(DISTINCT b.`hash`)
FROM
    audience a
    INNER JOIN behaviour b ON b.`hash` = a.`hash`
WHERE
    b.`timestamp` BETWEEN '2016-03-31 00:00:00' AND '2016-03-31 23:59:59'
GROUP BY
    a.country;
---------------------------------

这应该有帮助。