数据库的优化，提高查询效率的办法

在项目中会遇到很多写原生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)  尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

在项目开发中初期写过很多质量很差的查询语句，表的设计也没做好。所以做了这个总结，希望以后的开发中改进，写出优质效率的代码