在项目中会遇到很多写原生sql语句进行查询的问题。如果数据量比较大的时候，语句写的不好会导致查询效率很低，甚至会报错（比如in超过1000）。我收集了一下优化数据库查询效率的办法和建议，作学习用。
1.数据库设计上
(1) 查询时尽量避免全表搜索，对where和order by 涉及的列建立索引
(2) 避免在where语句中对字段进行null判断，否则将导致引擎放弃使用索引而进行全表搜索
例子：select unit.id from unit where ORGID is null;
在设计时可以将字段设置一个默认值，number可以设置0（如果是涉及0处理的可以约定设置为-1），字符串类型设置为“空”、“empty”。在查询时便可以这样查询
select id from unit where orgid = 'empty';
(3) 索引列保持唯一性，如果索引列上数据大量重复，查询效率不会得到提高。
(4) 索引不是越多越好，因为索引提高了select的效率但是降低了update 和insert的效率。要综合考虑后才建立索引，且不能建太多。
(5) 尽可能避免更新索引列，因为索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。所以一般建立索引的都是主键和不需要更新的字段。
(6) 不要将数字类型和时间类型的设置为字符串类型，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，如果是数字类型的比较一次即可。
(7) 避免频繁创建和删除临时表，以减少系统表资源的消耗
(8) 对临时表要显性的删除，避免库对系统表长期的锁定。
2.sql语句的优化
(1)避免在where子语句中使用!=或者<>，会导致全表搜索。可以用not exist 和 = 代替，如
select rid from unit where (name not like '%汇总%' and name <>'省直单位' and name <>'广东省');
会导致全表查询，可以用(select * 也会降低效率，最好要什么就列什么，如果是转化为实体的便可以这么写）
select count(*) from unit a where 1=1 and not exist (select 1 from unit b where name like '%汇总%' and name = '省直单位' and name = '广东省' and a.rid = b.rid)
(2)避免在where子语句中使用or，可以用union all 代替（项目中遇到了动态参数，就是传一个集合或者模糊查询的参数用到了or，解决办法另说）
select rid from house where noneuse_area > 10 or noneuse_area = 0 ;
可以用一下语句替代
select rid from house where noneuse_area > 10 union all select rid from house where noneuse_area = 0;
(3) 尽量避免使用in 和 not in ，in有字数限制且效率比较低。若是in的数字有规律可寻可拆解成上面的union all 或者 写成between 格式。如：
select rid from unit where staffnumber in(1,2,3) ;
可以写成
select rid from unit where staffnumber between 1 and 3;
字符串的可以用not exists 进行连接（包括in (:ids) 这种参数形式，将ids查询语句放入子语句即可）如：
select * from unit where (name not like '%汇总%' and name <> '省直单位' and name <> '广东省') and not in (select rid from view_bgyf_organinfo);
可以写成
select * from unit t where (name not like '%汇总%' and name <> '省直单位' and name <> '广东省') and exists(select rid from view_bgyf_organinfo t1 where t1.orgid=t.orgid)；
(4) 模糊查询与索引的关系（结论：后模糊匹配才能让索引有效）
a.前模糊查询 like ‘%keyword’ 索引失效，但可以通过反转函数+like前模糊查询+建立反转函数索引 走反转函数的索引，避免全表查询
b.全模糊查询 like ‘%keyword%’ 会使索引失效，也无法像a那样用反向索引解决。但可以使用locate函数（mysql）或者 instr函数（oracle），但函数也会使索引失效，后面会提到。
如原语句：
select rid from unit where name like '%广东%';
mysql可以写为
select rid from unit where locate('广东',unit.name)>0;
oracle可以写为(注意instr方法参数顺序和locate不一样）
select rid from unit where instr(unit.name,'广东')>0;(select * from unit where 1=1 and instr(unit.NAME,'广东',1,1 )>0 查询结果一样，instr后面的参数：第三位为从第几位开始找，第四位为第几次出现)；
c.后模糊匹配 like "keyword%" 索引有效
(5) 在where子语句使用参数也会使索引失效，但能用with强制查询使用索引，提高效率。
如：select * from UNIT_HISTORY where name ='省直单位' and HISTORY_VERSION = (:historyVersion);
可以写成 ：select * from UNIT_HISTORY with (index(索引名)) where name ='省直单位' and HISTORY_VERSION = (:historyVersion);
(6) 避免在where子语句中对字段进行表达式操作(即“=”左边进行函数、算术运算或其他表达式运算），这同样会导致引擎放弃索引进行全表搜索
如：select rid,noneuse_area,address,createtime from HOUSEwhere OVERGROUND_AREA*0.75<1000;
可以写成：select rid,noneuse_area,address,createtime from HOUSEwhere OVERGROUND_AREA<1000*(4/3);
(7) 尽量避免使用函数
如mysql中的datediff(date1，date2) ,可以用>,<代替（oracle中日期相减date1-date2能达到同样的目的。
(8) 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
在项目开发中初期写过很多质量很差的查询语句，表的设计也没做好。所以做了这个总结，希望以后的开发中改进，写出优质效率的代码
网友评论