mysql查询语句优化，请问这条语句有还能再优化吗？

Question

需求是查询最近100天的图书浏览数据，根据浏览的图书id分组查询统计前23的查阅量排序

语句为：

SELECT ip_times.query ,books.name,books.cover, COUNT(books.id) AS VALUE FROM ip_times,books WHERE (ip_times.path='/GetBookById' AND books.id=ip_times.query AND DATE_SUB(CURDATE(), INTERVAL 100 DAY) <= DATE(ip_times.date)) GROUP BY ip_times.query ORDER BY VALUE DESC LIMIT 23

问题出现的环境背景及自己尝试过哪些方法

我试着修改COUNT(*)为COUNT(books.id)，减少到了5秒，但是还想再快点，请问有什么更好的方式呢？谢谢

Answer 1

SELECT ip.query, b.name, b.cover, ip.co AS VALUE
FROM (
    SELECT query, COUNT(ip_times.query) AS co
    FROM ip_times
    WHERE DATE_SUB(CURDATE(), INTERVAL 100 DAY) <= DATE(ip_times.date)
        AND path = '/GetBookById'
    GROUP BY ip_times.query
    ORDER BY co DESC
    LIMIT 23
) ip 
LEFT JOIN books b ON ip.query = b.id

Answer 2

代码一定要写得可读性高，先找出排前面的23个图书ID，再去联小表查询效率最高

SELECT
    id
    name,
    cover
FROM books
WHERE EXISTS(
    SELECT 1 FROM (
        SELECT
            query,
            COUNT(1) as value
        FROM ip_times
        WHERE
            path='/GetBookById'
            AND DATE(a.date) > DATE_SUB(CURDATE(), INTERVAL 100 DAY)
        GROUP BY query
        ORDER BY value desc
        LIMIT 23
    ) a WHERE a.query=books.id
)

Answer 3

从sql语句中DATE_SUB(CURDATE(), INTERVAL 100 DAY) <= DATE(ip_times.date)可以看出，该同学是想查询 ip_times.date >= 100天之前的时间，那么我认为DATE_SUB(CURDATE(), INTERVAL 100 DAY)这个时间我们肯定是知道了，可以在代码中先计算出来，不要放在sql语句中计算；另外DATE(ip_times.date)这个值是取 ip_times.date的日期部分，我认为也是没有必要的，比如 2019-01-09 14:00:00和2019-01-09都是>=100天前的那个时间呀，所以直接用ip_times.date去比较，不要用date()函数。

所以这段sql可以优化为 ip_times.date >= '2018-10-01',试试看查询是不是快了很多。。

总之，sql查询语句中能不用函数尽量不用，严重影响sql查询的效率。

Answer 4

给经常需要作为查询条件的字段设置索引？

Answer 5

ip_times.date是varchar？如果是varchar，建议改成date。尽量在where中不用函数，即使用，也别用在索引列上。
其实这种调优对不了解你们业务要做什么，表结构的很难给出很好的答案。
你可以用explain来查询一下执行计划。然后针对性的调优。

Answer 6

从sql语句中DATE_SUB(CURDATE(), INTERVAL 100 DAY) <= DATE(ip_times.date)可以看出，该同学是想查询 ip_times.date >= 100天之前的时间，那么我认为DATE_SUB(CURDATE(), INTERVAL 100 DAY)这个时间我们肯定是知道了，可以在代码中先计算出来，不要放在sql语句中计算；另外DATE(ip_times.date)这个值是取 ip_times.date的日期部分，我认为也是没有必要的，比如 2019-01-09 14:00:00和2019-01-09都是>=100天前的那个时间呀，所以直接用ip_times.date去比较，不要用date()函数。

Answer 7

explain + 一点mysql的玄学 = 可以解决一切sql卡慢问题，最不济也能知道具体哪个环节出了问题再去改进，甚至有时候已经都不是mysql自身能解决的问题。以上回答说的可能都有道理，但是我觉得凡是这种sql性能的问题，提问的时候就两种方式：1.给出带所有数据的原表（这个查询其实不复杂，居然要5秒，可能表数据量也不是几百几千条）；2.给出mysql执行计划(就像debug这个环节必不可少一样)。