读《程序员的SQL金典》[4]--SQL调优

2023-10-26 16:31:22

一、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‘则会使用全表扫描。

读《程序员的SQL金典》[4]--SQL调优,布布扣,bubuko.com

读《程序员的SQL金典》[4]--SQL调优

码农公寓

一、SQL注入

二、索引

三、SQL调优方案十二条

相关文章