标题: SQL 查询如何优化 [打印本页] 作者: liyihongcug 时间: 2009-5-6 13:16 标题: SQL 查询如何优化 我的机器环境（linux+mysql5.0.4+java）,最近发现以下2条语句导致系统变慢
UserSessionLog 有6267689条记录 ，UserEntity 有5万条，
Query_time高达53秒，问原因（group by），如何优化
# Query_time: 53 Lock_time: 0 Rows_sent: 20 Rows_examined: 6267689
select log.userId, max(log.lastLoginTime) lastLoginTime, count(1) + 1 nrOfLogins, u.partnerId, u.userName from
UserSessionLog log, UserEntity u where log.userId = u.id group by log.userId order by lastLoginTime desc li
mit 20;
Query_time高达57秒，问原因（group by），如何优化
# Query_time: 57 Lock_time: 0 Rows_sent: 5 Rows_examined: 10619215
SET timestamp=1241525924;
select count(distinct(ul.userId)) number, CASE WHEN ul.lastaccess>curdate() THEN 1 WHEN (ul.lastaccess>subdate(curdate(),1)
and ul.lastaccess<curdate()) THEN 2 WHEN (ul.lastaccess>date_Sub(curdate(), INTERVAL DAYOFMONTH(curdate())-1 day)) THEN 3
WHEN (ul.lastaccess>date_sub(date_Sub(curdate(), INTERVAL DAYOFMONTH(curdate())-1 day),INTERVAL 1 month) and ul.lastaccess<DATE_SUB(sys
date(),INTERVAL 1 month)) THEN 4 ELSE 5 END as dateInterval from UserSessionLog ul,UserEntity u where u.id = ul.userid gro
up by dateInterval;作者: kider 时间: 2009-5-6 19:12
检查是否有索引
关联字段数据类型应该一样
把表UserEntity前置试试
简化逻辑，尽量不要用distinct
用explain分析优化
....作者: liyihongcug 时间: 2009-5-6 20:00
add the column