1、对于
数据量较大或者
字段较多的查询,不要直接select *,可以
先select id,再根据id查(in取别名、join法)。
此处示例是分页查询,普通查询也一样。
优化前:
SELECT * FROM ucdb_0.userinfo_27 WHERE
subscribe=1 AND aid=55745644
ORDER BY subscribe_time DESC
LIMIT 300000,10;
耗时:11.517s
错误写法:
SELECT * FROM ucdb_0.userinfo_27 WHERE
id in(
SELECT id FROM ucdb_0.userinfo_27 WHERE
subscribe=1 AND aid=55745644
ORDER BY subscribe_time DESC
LIMIT 300000,10
)
ORDER BY subscribe_time DESC;
直接in是不行的,因为MySQL不支持in子查询中含有limit。
正确写法:
(1)in取别名法
SELECT * FROM ucdb_0.userinfo_27 WHERE
id in(
SELECT t.id FROM (
SELECT id FROM ucdb_0.userinfo_27 WHERE
subscribe=1 AND aid=55745644
ORDER BY subscribe_time DESC
LIMIT 300000,10
)AS t
)
ORDER BY subscribe_time DESC;
耗时:1.240s
(2)join查询
SELECT * FROM (
SELECT id FROM ucdb_0.userinfo_27 WHERE
subscribe=1 AND aid=55745644
ORDER BY subscribe_time DESC
LIMIT 300000,10
)a
left join ucdb_0.userinfo_27 b on a.id=b.id
ORDER BY subscribe_time DESC;
耗时:1.201s
这两种写法本质上是一样的,都是转化为对id查询。
总结:
优化前要11s多,优化后只要1s多。
在sql脚本中,join法比in法写法简单。
在Java框架中,in法容易实现(先查出idList,再in idList查询),join法不容易实现。
查询优化:
select id结合where优化、索引优化。
where优化:
等值在前,不等值在后
索引优化:
少值在前,多值在后