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;