在对数据库进行操作时,如果SQL语句书写不当,对程序的效率会造成很大影响。
提高SQL效率可以从一下几个方面入手:
1,数据库设计与规划
- Primary Key字段的长度尽量小,能用small integer就不要用integer。例如对于员工数据表,如果能用员工编号做主键,就不要用身份证号码。
- 字符字段如果长度固定,就不要用varchar或nvarchar类型,而应该用char或nchar类型。例如身份证号码、银行密码等字段。
- 字符长度如果长度不固定,则应该使用varchar或nvarchar类型。除了可节省存储空间外,存取硬盘也会有效率。例如地址、个人介绍等。
- 涉及字段时,如果其值可有可无,最好也给一个默认值,并设成不允许NULL。因为有些数据库在存放和查询有NULL的数据表时,会花费额外的运算动作,例如SQL Server数据库。
2,适当地创建索引
- Primary Key字段可以自动地创建索引,而Foreign Key字段不可以。为Foreign Key字段手动创建索引,即使是很少被JOIN的数据表也有必要这么做。
- 为经常被查询或排序的字段创建索引。
- 创建索引字段的长度不宜过长,不要用超过20个字节的字段,例如地址等。
- 不要为内容重复性高德字段创建索引,例如性别等。
- 不要为使用率低的字段建立索引。
- 不宜为过多字段建立索引,否则会影响到INSERT、UPDATE和DELETE语句的性能。
- 如果数据表存放的数据很少,就不必刻意使用索引。
3,使用索引功能
在查询数据表时,使用索引查询可以极大提升查询速度,但是如果where子句书写不当,即使某些列存在索引,也不能使用该索引查询,而同样会使用全表扫描,这就造成了查询速度的降低。在where语句中避免使用一下关键字:
NOT、!=、<>、!>、!<、EXISTS、IN、LIKE、||。
使用LIKE关键字做模糊查询时,即使已经为某个字段建立了索引,但需要以常量字符开头才会用到索引,如果以“%”开头则不会使用索引。例如name like '%To‘ 不启用name字段上的索引;而 name like ‘To%’会启用name字段上的索引。
4,避免在where子句中对字段使用函数
5,AND与OR的使用
在AND运算中,只要有一个条件用到索引,即可大幅提升查询速度。但在OR运算中,则要所有的条件都有使用到索引才能提升查询速度,因此使用OR运算符需要特别小心。
6,JOIN与子查询
相对于子查询,如果能使用JOIN完成的查询,一般建议使用后者。原因除了JOIN的语法较容易理解外,在多数的情况下,JOIN的性能也会比子查询高。
7,其它查询技巧
DISTINCT、ORDER BY语法,会让数据库做额外的计算。如果没有要过滤重复记录的需求,使用UNION ALL会比UNION更好,因为后者会加入类似DISTINCT的算法。
8,尽可能使用存储过程(Stored Procedure)
Stored Procedure除了经过事先编译、性能较好以外,也可能减少SQL语句在网络上的传递,方便商业逻辑的重复使用。