一个java菜鸟对sql语句优化的总结

最新推荐文章于 2024-08-12 17:39:22 发布

慕阳Joseph

最新推荐文章于 2024-08-12 17:39:22 发布

阅读量755

点赞数 1

分类专栏： sql 文章标签：数据库 mysql

本文链接：https://blog.youkuaiyun.com/Dr_Joseph/article/details/73550613

版权

sql 专栏收录该内容

2 篇文章

订阅专栏

从毕业到现在从事java开发一年了，一直考虑过如何去写运行高效的代码，以至于经常需要用循环对数据库进行一次又一次的访问和操作，这大大降低了程序的运行效率，于是我从网上整理了一些提高sql查询效率的tips，希望能让初学的coder们养成一个好的习惯，写出高效的sql。

应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0
应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20
避免出现SELECT * FROM table 语句，要明确查出的字段。
一个SQL语句中，如果一个where条件过滤的数据库记录越多，定位越准确，则该where条件越应该前移。 ORACLE采用自下而上的顺序解析where子句,根据这个原理,表之间的连接必须写在其他where条件之前, 那些可以过滤掉最大数量记录的条件必须写在where子句的末尾.
不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
用union all 代替 union，数据库执行union操作，首先先分别执行union两端的查询，将其放在临时表中，然后在对其进行排序，过滤重复的记录。
当已知的业务逻辑决定query A和query B中不会有重复记录时，应该用union all代替union，以提高查询效率。
很多时候用 exists 代替 in 是一个好的选择：
select num from a where num in(select num from b)
用下面的语句替换：
select num from a where exists(select 1 from b where num=a.num) 。
子查询问题。对于能用连接方式或者视图方式实现的功能，不要用子查询。例如：select name from customer where customer_id in ( select customer_id from order where money>1000)。应该用如下语句代替：select name from customer inner join order on customer.customer_id=order.customer_id where order.money>100。
尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
使用表的别名(Alias)，当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误。
用EXISTS替换DISTINCT，当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换, EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果. 例如：
select DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E WHERE D.DEPT_NO = E.DEPT_NO （低效）

SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT ‘X’ FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);（高效）
用where子句替换having子句，避免使用having子句, having只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过where子句限制记录的数目,那就能减少这方面的开销. (非oracle中)on、where、having这三个都可以加条件的子句中，on是最先执行，where次之，having最后，因为on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的，where也应该比having快点的，因为它过滤数据后才进行sum，在两个表联接时才用on的，所以在一个表的时候，就剩下where跟having比较了。在这单表查询统计的情况下，如果要过滤的条件没有涉及到要计算字段，那它们的结果是一样的，只是where可以使用rushmore技术，而having就不能，在速度上后者要慢如果要涉及到计算的字段，就表示在没计算之前，这个字段的值是不确定的，根据上篇写的工作流程，where的作用时间是在计算之前就完成的，而having就是在计算后才起作用的，所以在这种情况下，两者的结果会不同。在多表联接查询时，on比where更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where进行过滤，然后再计算，计算完后再由having进行过滤。由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什么时候起作用，然后再决定放在那里。
创建临时表可以提高查询速度，如果在1000万的数据中查询结果，那么以下第二种查询效率会高很多倍。
(1)select * from t_user where name=’suns’;
(2)select * from t_user tu,user us where tu.id=us.id and us.name=’suns’;