关于mysql查询优化的几点
By 小鸟游·飒
如果是长语句复杂查询...请善用子查询,优先查询完简单条件结果,再进行下一步操作。尽可能拆分的细小一些。
关于逻辑语句,可以的话尽量不要使用。
对于索引,如果是频繁更新的表,那么索引的维护将会变得非常不友好,所以尽可能的规范自己的查询习惯,确定好固定格式便于索引格式的固定。
废话多一句,查询还是尽可能不要使用复杂查询,内容允许的话尽可能少的查询次数提取更多数据。
对于大量数据的IN查询...参考内存表的方式
对group的一些个人看法,如果union可以解决,可以考虑用union来替代group方式
-
create temporary table tmp_deviceid(deviceid int primary key);
insert into tmp_deviceid (deviceid) values (6310),(6311),(6312),(6313),(6314),(6315),(6316),(6317),(6318),(6319),(6320),(6321),(6322),(6323),(6324),(6325),(6326),(6327),(6328),(6329),(6330),(6331),(6332),(6333),(6334),(6335),(6336),(6337),(6338),(6339),(6340),(6341),(6342),(6343),(6344),(6345),(6346),(6347),(6348),(6349),(6350),(6351),(6352),(6353),(6354),(6355),(6356),(6357),(6358),(6359),(6360),(6361),(6362),(6363),(6364),(6365),(6366),(6367),(6368),(6369),(6370),(6371),(6372),(6373),(6374),(6376),(6377),(6378),(6379),(6380),(6381),(6382),(6383),(6438),(6439),(6440),(6441),(6442),(6443),(6446),(6447),(6448),(6449),(6450),(6451),(6452),(6453),(6454),(6455),(6456),(6457),(6458),(6459),(6460),(6461),(6462),(6463),(6464),(6465),(6466),(6467),(6468),(6469),(6470),(6471),(6472),(6473),(6474),(6475),(6476),(6477),(6478),(6479),(6480),(6481),(6482),(6483),(6484),(6485),(6486),(6487),(6488),(6489),(6490),(6491),(6492),(6493),(6494);
SELECT `companyid` as cid,`activenum` as a,`bikenum` as b,`timeto` as t
FROM tmp_deviceid b left join `bikeactivity` a on a.deviceid=b.deviceid
WHERE `timeto` > '2019-04-01 00:00:00' AND `timeto` < '2019-04-02 24:00:00'
AND `deviceid` IN ORDER BY `timeto` ASC;
drop table tmp_deviceid;
测试部分
-
count之类的函数查询,结果出现奇怪的偏差
select count(***) from ******
改进为
select cast(count(1) as char) as num from ******
是否解决:未知(观察中)