1. 优化查询, 应尽量避免全表扫描, 应该在用于检索数据和排序数据的字段上建立索引, 如 where 子句用于搜索, order by 子句用于排序, 所以在这两个子句涉及到的字段上需要建立索引.
2. 应该在 where 子句中使用否定的操作符, 如不等于(!= 或<>), 否则数据库引擎将放弃使用索引而进行全表扫描.
3. 在尽量避免在 where 子句中使用或 (or) 作为连接条件, 否则数据库引擎将放弃使用索引而进行全表扫描.
如下面的 SQL 语句可能会带来性能问题
- select id,name,age from persons
- where name = 'Bill' or age> 30
由于这条 SQL 语句使用了 or, 所以数据库引擎会进行全表扫描, 为了避免全表扫描, 可以将这条 SQL 语句改成下面的形式.
- select id,name,age from persons where name = 'Bill'
- union all
- select id,name,age from persons where num = 20
4. 应该尽量避免在 where 子句中使用 null 进行判断, 否则数据库引擎将放弃使用索引而进行全表扫描.
先看下面的 SQL 语句:
select id,name,age from persons where age is null
为了避免使用 null, 可以设置 age 字段的默认值为 0, 这样就可以通过下面的 SQL 语句达到同样的结果.
select id,name,age from persons where age = 0
5. 尽量不用使用 like 检索数据, 因为也会导致数据库引擎将放弃使用索引而进行全表扫描.
例如, 下面的 SQL 语句执行的效率会非常低:
select id,name,age from persons where name like '%John%'
如果真想进行模糊查询, 可以使用全文检索.
6. 在 where 子句中应尽量避免在字段中使用表达式(包括函数运算, 算数运算等), 否则据库引擎将放弃使用索引而进行全表扫描.
例如, 下面的 SQL 语句执行的性能比较差
select id,name,age from persons age / 2> 12
应该利用表达式变换, 改成下面的形式:
select id,name,age from persons age> 2 * 12
或者干脆改成下面的形式:
select id,name,age from persons age> 24
7. 应尽量避免使用 in 和 not in, 否则也会导致全表扫描.
如并不推荐下面的写法:
select id, name,age from persons where age in (22,23,24)
如果数值是连续的, 应该使用 between, 而不要用 in, 如果数值是不连续的, 可以分成多个 SQL, 用 union all 连接查询结果.
- select id,name,age from persons where age between 22 and 24
- select id,name,age from persons where age = 22
- union all
- select id,name,age from persons where age = 26
- union all
- select id,name,age from persons where age = 30
8. 应该尽量避免在 where 子句中使用参数, 否则也将导致全表扫描. 这是因为参数需要在 SQL 运行时才进行替换, 而 SQL 优化 (使用索引属于优化的一部分) 是在编译时进行的. 所以数据库引擎在检索到参数时, 由于参数的具体指是未知的, 所以也就无法优化了, 当然也就无法使用索引了.
不使用索引的 SQL 语句:
select id,name,age from persons where name = @name
为了使用索引, 可以改成下面强制使用索引的方式:
select id,name,age from persons with(index(name_index)) where name = @name
其中 name_index 是建立在 name 字段上的索引名.
9. 尽量不要执行一些没意义的查询, 如条件完全为 false 的查询:
select id,name,age into persons1 from persons where age <0
这样的代码会返回一个空结果集, 而且会大量消耗系统资源, 如果真的想建一个空表, 应该直接用 create table 语句.
10. 如果使用的索引是符合索引, 只有使用该符合索引的第 1 个字段作为条件时才能保证数据库引擎使用该符合索引, 否则该符合索引不会被使用. 并且应该尽可能让字段顺序与索引顺序一致. 例如, name_index 是 first_name 和 last_name 字段的符合索引, 使用下面的 SQL 语句会使用该索引.
- select id,first_name,last_name
- from persons
- where first_name = 'Bill'
11. 如果非要在 SQL 语句中使用 in, 那么使用 exists 代替 in 是一个好主意:
- select id,num from t
- where num in (select num from h)
应该用下面的 SQL 语句代替:
- select id,num form t
- where exists(select 0 from h where num = t.num)
12. 索引并不是在任何时候都有效, 如果索引列有大量重复的数据, 那么数据库引擎可能不会去利用索引. 例如, sex 字段的值只有两种可能: male 和 female, 可能这两个值各占一半, 这样在 sex 字段上建立索引就没有任何意义.
13. 能使用数值型字段就使用数值型字段. 因为比较数值型字段的效率要远比字符型字段的效率高, 这是因为比较字符型的值, 要一个字母一个字母地比较, 而数值型的值, 只是比较一个数. 所以如果只包含数值信息的值, 应该尽量使用数值类型的字段. 例如, age,salary 等.
14. 应尽量避免使用固定长度的字段, 如 char,nchar. 使用可变长度的字段是一个非常好的选择. 因为可变长度字段占用的空间是按需分配的, 所以占用空间比较少. 对于查询来说, 毫无疑问, 当然是占用空间小的字段的查询效率更高了.
15. 尽量按需返回字段和记录, 例如:
select id,name,age from persons where age> 20
尽量如要使用 "*" 返回所有不需要的字段, 也不需要一下就查询出所有的记录, 如下面的 SQL 语句在数据量很大时查询效率是非常低的.
select * from persons
16. 索引有利有弊, 增加索引, 可以提高 select 的执行效率, 但付出的代价是在进行 insert 和 update 操作时, 可能会降低效率. 因为进行 insert 和 update 操作时通常需要重建索引. 所以在一个表中并不是索引越多越好. 我的建议如下:
(1)如果一个表大多数时进行的是 select 操作, 那么索引多一些大多数时候确实可以提升性能, 但这有一个前提, 就是不能频繁进行 insert 和 update 操作.
(2)一个表中的索引数不能太多, 最好不要超过 6 个, 否则就好考虑优化一下数据库了.
17. 应尽可能的避免更新 clustered 索引数据列, 因为 clustered 索引数据列的顺序就是表记录的物理存储顺序, 一旦该列值改变将导致整个表记录的顺序的调整, 会耗费相当大的资源. 若应用系统需要频繁更新 clustered 索引数据列, 那么需要考虑是否应将该索引建为 clustered 索引.
18. 应尽量避免向客户端返回大理数据, 如果数据量过大, 应该改变一下需求, 或采用分页返回的方式, 如使用 MySQL 中的 limit 子句现在返回的数据.
19. 尽量避免使用游标, 因为游标的效率较差, 如果游标操作的数据超过 1 万行, 那么就应该采用其他方案.
20. 使用基于游标的方法或临时表方法之前, 应先寻找基于数据集的解决方案来解决问题, 基于数据集的方法通常更有效.
21. 如果使用到了临时表, 在存储过程的最后务必将所有的临时表显式删除, 先用 truncate table 清除表中的数据 , 然后 用 drop table 彻底删除物理表 , 这样可以避免系统表的较长时间锁定.
22. 避免频繁创建和删除临时表, 以减少系统表资源的消耗.
23. 在新建临时表时, 如果一次性插入的数据量很大, 那么可以使用 select into 代替 create table, 避免造成大量 log , 以提高执行效率; 如果数据量不大, 为了缓和系统表的资源, 应先 create table, 然后使用 insert 插入数据.
24. 在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON , 在结束时设置 SET NOCOUNT OFF . 无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息.
25. 尽量避免大事务操作, 提高系统并发能力.
26. 应尽量一次性插入多条数据, 例如, 使用下面的 SQL 语句性能会很低:
- insert into persons(id,name,age) values('Bill',24)
- insert into persons(id,name,age) values('Mike',26)
- insert into persons(id,name,age) values('John',20)
为了提升性能, 可以一次性插入这 3 条记录.
insert into persons(id,name,age) values('Bill',24),('Mike',26),('John',20)
27. 如果不得不使用 like 进行模糊查询时, 不要在关键字前面加 %.
反例:
select id,name,age from persons where name like '%abc%'
如果在关键字前面加 %, 那么查询是肯定要走全表查询的.
正例:
select id,name,age from persons where name like 'abc%'
28. 尽量用 union all 代替 union
union 和 union all 的差异主要是前者需要将两个 (或者多个) 结果集合并后再进行唯一性过滤操作, 这就会涉及到排序, 增加大量的 CPU 运算, 加大资源消耗及延迟. 所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候, 尽量使用 union all 而不是 union.
29. 尽量使用等值连接
等值连接就是 inner join, 也称为内联进, 而 left join 和 right join 是外连接.
先看下面的 SQL 语句
- select a.id,a.name,b.id,b.name from a left join b on a.id = b.id
- select a.id,a.name,b.id,b.name from a right join b on a.id = b.id
- select a.id,a.name,b.id,b.name from a inner join b on a.id = b.id
上面的 3 条 SQL 语句, 前两条分别使用了左连接和右连接, 而最后一条使用了内连接, 经过实际运行, 使用内连接的 SQL 语句的执行效率明显优于左连接和右连接. 所以在能满足需求的前提下, 应该尽可能使用内连接(等值连接).
30. 尽量用外连接来替换子查询
反例
select id,name from a where exists (select id from b where id>=10 and a.product_id=b.product_id)
在上面的 SQL 语句中, 数据库引擎会先对外表 a 执行全表查询, 然后根据 product_id 逐个执行子查询, 如果外层表 (a 表) 中的数据非常多, 查询性能会非常糟糕. 所以应该将 SQL 语句改成下面的形式:
select id,name from a inner join b on A.product_id=b.product_id where b.id>=10
来源: https://www.cnblogs.com/nokiaguy/p/12955286.html