sql优化实战一

查询实战:

        SELECT
        u.user_id AS userId,
        u.user_name AS userName,
        tt.lastRequestDay AS lastRequestDay,
        tt.lastRequestTime AS lastRequestTime,
        tt.lastMethod AS lastMethod,
        tt.lastUrl AS lastUrl,
        COUNT(*) AS ipCount,
        u.ip_address AS ipAddress
        FROM
        sys_access_log AS u,
                           (SELECT t.request_day AS lastRequestDay,
                                   t.last_request_time AS lastRequestTime,
                                   t.method AS lastMethod,
                                   t.url AS lastUrl, 
                                   t.ip_address AS forIpAddress, 
                                   t.user_name AS forUserName
                            FROM sys_access_log AS t 
                            order by lastRequestTime desc
        ) AS tt
        where tt.forIpAddress = u.ip_address 
          AND tt.forUserName = u.user_name 
          AND u.request_day between '2017-05-20' AND '2017-05-21'
        group by u.ip_address, u.user_name
        order by ipCount DESC, lastRequestTime DESC

emmm,没有然后了,无响应

 

优化后:

        SELECT
        u.user_id AS userId,
        u.user_name AS userName,
                 (SELECT t.last_request_time
                  FROM sys_access_log AS t
                  WHERE t.ip_address = u.ip_address
                  AND t.user_name = u.user_name
                  order by t.last_request_time desc
                  limit 1
        ) AS lastRequestTime,
                 (SELECT t.method
                  FROM sys_access_log AS t
                  WHERE t.ip_address = u.ip_address
                  AND t.user_name = u.user_name
                  order by t.last_request_time desc
                  limit 1
        ) AS lastMethod,
                 (SELECT t.url
                  FROM sys_access_log AS t
                  WHERE t.ip_address = u.ip_address
                  AND t.user_name = u.user_name
                  order by t.last_request_time desc
                  limit 1
        ) AS lastUrl,
        COUNT(*) AS ipCount,
        u.ip_address AS ipAddress
        FROM
        sys_access_log AS u
        where u.request_day between '2017-05-20' AND '2017-05-21'
        group by u.ip_address, u.user_name
        order by ipCount DESC, lastRequestTime DESC

响应时间为0.3秒

 

结论:

如果只要一条就查一条