影响数据库执行性能的原因:
1 计算机硬件问题
2 数据库管理系统问题(ORACLE,SQL SERVER,MYSQL,DB2...)
3 数据库设计问题(例如:索引的情况)
4 SQL写法问题
5 实际应用数据量的多少
作为程序员,我们一般不能决定计算机硬件,数据库系统,数据库设计,以及实际应用数据量的多少,所以,我们可以在SQL的写法上下工夫。
第一个原则: 避免 LEFT JOIN 和 NULL
LEFT JOIN 消耗的资源非常之多,因为它们包含与 NULL(不存在)数据匹配的数据。
在某些情况下,这是不可避免的,但是代价可能非常高。
LEFT JOIN 比 INNER JOIN 消耗资源更多,所以如果您可以重新编写查询以使得该查询不使用任何 LEFT JOIN,则会得到非常可观的回报
第二个原则:在where子句中应把最具限制性的条件放在最前面。
在下面两条select语句中:
select * from table1 where field1<=10000 and field1>=0;
select * from table1 where field1>=0 and field1<=10000;
如果数据表中的数据field1都>=0,则第一条select语句要比第二条select语句效率高的多,因为第二条select语句的第一个条件耗费了大量的系统资源。
第三个原则:where子句中字段的顺序应和索引中字段顺序一致。
在下面的select语句中:
select * from tab where a=… and b=… and c=…;
若有索引index(a,b,c),则where子句中字段的顺序应和索引中字段顺序一致。
第四个原则:SELECT中尽量避免select * 的查询
select field3,field4 from tb where field1='sdf' 快
select * from tb where field1='sdf' 慢
因为后者在索引扫描后要多一步ROWID表访问。
第五个原则:>=,<=要比>,<速度快
select field3,field4 from tb where field1>='sdf' 快
select field3,field4 from tb where field1>'sdf' 慢
因为前者可以迅速定位索引。
第六个原则:模糊查询的时候,‘R%’要比‘%R’速度快
select field3,field4 from tb where field2 like 'R%' 快
select field3,field4 from tb where field2 like '%R' 慢
第七个原则:如果一个表有两万条记录,建议不使用函数;如果一个表有五万条以上记录,严格禁止使用函数!两万条记录以下没有限制。
第八个原则:用exists/not exists代替in/not in操作
第九个原则:使用count(*)而不要使用count(column_name),避免使用count(distinct column_name)。
第十个原则:等号右边尽量不要使用字段名。
第十一个原则:特别要避免一个查询里既使用join又使用group by
第十二个原则:尽量少用子查询,特别是相关子查询。因为这样会导致效率下降。
不过这个好像和第八个原则不太一致。
第十三个原则:尽量避免使用distinct、order by、group by、having、join、cumpute,因为这些语句会加重tempdb的负担。
第十四个原则:一种查询的写法,在多表关联的时候很有效果
select A.ID, A.名字, B.部门名称, C.病房名称, D.诊疗室名称
from 患者基本情报 A,
部门基本情报 B,
病房基本情报 C,
诊疗科基本情报 D
where B.部门ID = A.部门ID
and C.病房ID = A.病房ID
and D.诊疗科ID = A.诊疗科ID
如果改换为另外一种写法:
select A.ID, A.名字,
(select B.部门名称 from 部门基本情报 B where B.部门ID = A.部门ID),
(select C.病房名称 from 病房基本情报 C where C.病房ID = A.病房ID),
(select D.诊疗室名称 from 诊疗科基本情报 D where D.诊疗科ID = A.诊疗科ID)
from 患者基本情报 A