在写mysql存储过程时,比如有一张数据量很大的log表,我们要取符合条件的20条,为了方便我们一般会这样写:
SELECT * FROM logs ORDER BY id DESC LIMIT in_page,in_limit;
其中in_page,in_limit 是传入的参数,当没有其他条件时,这样查询没有什么问题,假如我们要查询某人,某时的log时,为了方便我们一般会这么写:
SELECT * FROM logs WHERE CASE WHEN in_username = "" THEN true ELSE username LIKE CONCAT("%",in_username,"%") END
AND CASE WHEN time = "" THEN true ELSE time = in_time END
ORDER BY id DESC LIMIT in_page,in_limit;
其中in_username,in_time是传入的参数,这样写的后果是当数据量很大的情况下查询会很慢,原因是WHERE条件语句中case when后面的username和time字段的索引将失效!
为了提高查询效率,提高索引的使用效率where语句后面尽量不要使用case when,需要做判断的条件尽量在查询之前判断,组合成完整sql后在执行。
以上sql可以修改为:
SET kusername = CONCAT("'%",in_username,"%'");
IF in_username = "" THEN
SET continion = true;
ELSE
SET continion = CONCAT(continion," username LIKE ",kusername );
END IF;
IF in_time <> "" THEN
SET continion = CONCAT(continion," AND time = ",in_time);
END IF;
SET sqltxt = "SELECT * FROM logs WHERE";
SET pagetxt = CONCAT(" ORDER BY id DESC LIMIT ",def_page,",",in_pagesize);
SET sqltxt=CONCAT(sqltxt,continion,pagetxt);
SET @sqltxt = sqltxt;
PREPARE stmt FROM @sqltxt;
EXECUTE stmt;
需要注意的是WHERE后面的条件参数尽量不要使用函数方法,如:
SELECT * FROM logs WHERE DATE_SUB(time,INTERVAL 1 DAY)=in_time ORDER BY id DESC LIMIT 20;
这样会导致mysql进行全表操作,失去time索引的作用。