读《程序员的SQL金典》[4]--SQL调优
一、SQL注入
如果程序中采用sql拼接的方式书写代码,那么很可能存在SQL注入漏洞。避免的方式有两种:
1. 对于用户输入过滤敏感字母;
2. 参数化SQL(推荐)。
二、索引
①索引分类
- 聚簇索引:数据表的物理顺序和索引顺序相同。每个表只能建立一个聚簇索引,应该在表中经常访问的列或者按顺序访问的列建立聚簇索引。
- 非聚簇索引:数据表的物理顺序和索引顺序不同。每个表可以建立多个非聚簇索引。
②碎片整理
在删除一条数据项记录时,并不会删除对应的索引项。所以经过一段时间后数据库中会出现索引碎片,降低效率。进行随便整理的办法可以进行索引重建。
例如:
ALTER TABLE [dbo]. [test] DROP CONSTRAINT [DF__bAuto__47A6A41B] GO ALTER TABLE [dbo]. [test] ADD CONSTRAINT [DF__bAuto__47A6A41B] DEFAULT ((0)) FOR [name] GO
③全表扫描和索引查找
全表扫描:就是在整个数据表中逐条检索每条记录,当数据量大的时候,性能低下。
索引查找:就是当表中创建了索引并且查询语句符合索引条件时,只对索引进行检索,而不必对每条记录进行筛选,性能大大提高。
三、SQL调优方案十二条
在调优的时候不要追求完美,先用工具追踪到最占资源的SQL进行优化,往往能起到事半功倍的效果。
常用优化方式:
1.创建必要的索引
在经常检索的字段创建索引,能起到非常大的优化效果。
2.避免在索引列上进行计算
如果在索引列上进行计算或者使用函数,那么DBMS优化器将不会使用索引而是使用全表扫描。
SELECT *FROM T_Employee WHERE FSalary * 12 >25000; ==> SELECT *FROM T_Employee WHERE FSalary >25000/12;
3.参数化SQL
如果SQL是根据用户输入动态生成的,那么可以将用户输入进行参数处理。这样不仅能够避免SQL注入漏洞,而且能提高性能。因为DBMS在第一次执行的时候会进行查询优化和预编译,再次执行的时候可以直接使用预编译结果,从而提高执行效率。
4.调整where子句连接顺序
where子句中尽量把子查询放在其他筛选条件之前,可以提高效率。
例如:
SELECT * FROM T_Person WHERE 25 < (SELECT COUNT(*) FROM T_Manager WHERE FManagerId=2) AND FSalary > 50000 AND FPosition= ‘MANAGER’ ;
5.避免使用*
在SELECT语句中写明需要查询的列名。即使要查询所有列,也不要偷懒使用*查询,因为这样在DBMS执行的过程中仍然要解析出所有列名,浪费性能。
6.列出表名
在使用多表连接查询时,尽量在字段前带上表名前缀,这样既容易理解又能减少查询过程中的解析时间。
7.用WHERE 子句替换HAVING子句
HAVING子句会在查询出所有结果后才对结果进行过滤,一般用于对聚合函数运算的过滤,其它情况进行条件筛选尽量使用WHERE子句。
8. 用EXISTS替换IN
第二种写法要好于第一种写法。
SELECT * FROM T_Employee WHERE FNumber> 0 AND FDEPTNO IN (SELECT FNumber FROM T_Department WHERE FMangerName = ‘Tome‘) ==> SELECT * FROM T_Employee WHERE FNumber > 0 AND EXISTS (SELECT 1 FROM T_Department WHERE T_Department. FDEPTNO = EMP.FNumber AND FMangerName = ‘MELB’)
9.用表连接替换EXISTS
一般来说表连接的效率要优于EXISTS。
例如:
SELECT FName FROM T_Employee WHERE EXISTS ( SELECT 1 FROM T_Department WHERE T_Employee.FDepartNo= FNumber AND FKind=‘A‘ ); ==> SELECT FName FROM T_Department, T_Employee WHERE T_Employee. FDepartNo = T_Departmen. FNumber AND FKind = ‘A’ ;
10.用UNION ALL替换UNION
如果进行合并的两个表肯定不会重复记录,那么使用UNION ALL会效率高些。因为UNION方法会一直尝试进行合并。
SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS1 WHERE TRAN_DATE = ‘20010101‘ UNION SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS2 WHERE TRAN_DATE =‘20010102‘ ==> SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS1 WHERE TRAN_DATE =‘20010101‘ UNION ALL SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS2 WHERE TRAN_DATE = ‘20010102‘
11.避免隐式类型转换
例如FAge字段类型为字符串,但是一般数据库中的数值类型优先级比字符串类型高,因此会进行隐式类型转换。
SELECT FId,FAge,FName FROM T_Person WHERE FAge=10 相当于 SELECT FId,FAge,FName FROM T_Person WHERE TO_INT(FAge)=10 ==> SELECT FId,FAge,FName FROM T_Person WHERE FAge=‘10‘
12.防止检索范围过宽
如果DBMS优化器认为检索范围过宽,则会使用全表扫描而放弃索引查询。
以下几种情况容易被认为检索范围过宽:
- 使用IS NOT NULL;
- 使用不等于判断;
- 使用LIKE匹配字符串时,使用‘a%‘可以用到索引,但是使用‘%b‘或者‘a%b‘则会使用全表扫描。
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。