SQL查询优化笔记

1、对于分页查询，事先进行count查询总的记录数，如果count结果小于分页数，将limit设置为$num，避免全表扫描

$num = mysql_query("select count(*) from table where ...", $conn);
$limit = 10;
if ($num < $limit) {
    mysql_query("select * from table limit 0,$num");
} else {
    mysql_query("select * from table limit 0,$limit");
}

2、避免在for循环里操作数据库

3、一开始页面不需要展示的元素（或折叠起来的元素），可以在页面完成后再通过ajax刷新到页面上

4、优化count查询同一列不同值的数量

select count(color = 'blue' or null) as blue,count(color = 'red' or null) as red from table;

select sum(color = 'blue') as blue,sum(color = 'red') as red from table;

5、limit查询

//优化limit，通过“延迟关联”，扫描尽量少的字段

select id,title,desc,field_N from table limit 1000,5;

//改为

select id,title,desc,field_N from table 
inner join (
    select id from table limit 1000,5
) as temp using(id);

select * from table limit 20;

假设得到id为500;

则下一页：

select * from table where id > 500 limit 20;

使用explain结果中的rows列的值作为结果集总数的近似值

6、UNION查询

除非确实需要服务器消除重复的行，否则就一定要使用UNION ALL。如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这会导致对整个临时表的数据做唯一性检查，这样做的代价非常高。

7、使用用户自定义变量

//简单示例：
set @now = 0;
update t1 set lastUpdated = NOW() where id = 1 and @now = NOW();
select $now;






//最常见问题：没有注意到赋值和读取变量的时候可能是在查询的不同阶段
set @rownum = 0;
select actor_id,@rownum = @rownum + 1 as cnt from sakila.actor where @rownum <= 1;

actor_id  cnt
    1      1
    2      2


//解决：让变量的赋值和取值发生在执行查询的同一阶段
set @rownum = 0;
select actor_id,@rownum as rownum from sakila.actor where (@rownum = @rownum + 1) <= 1;

actor_id  rownum
    1        1






//“偷懒”的UNION：用户表(users)、不活跃用户表(users_archived)
select id from users where id = 123
union all
select id from users_archived where id = 123;

改为
select greatest(@found = -1, id) as id, 'users' as which_tbl
from users where id = 1
union all
    select id, 'users_archived'
    from users_archived where id = 1 and @found is null
union all
    select 1, 'reset' from dual where (@found = null) is not null;