sql优化(SQL optimization)

很大一部分是摘抄的哈。

一、为什么要对SQL进行优化

我们开发项目上线初期,由于业务数据量相对较少,一些SQL的执行效率对程序运行效率的影响不太明显,而开发和运维人员也无法判断SQL对程序的运行效率有多大,故很少针对SQL进行专门的优化,而随着时间的积累,业务数据量的增多,SQL的执行效率对程序的运行效率的影响逐渐增大,此时对SQL的优化就很有必要。

    编写过程:    select dinstinct  ..from  ..join ..on ..where ..group by ...having ..order by ..limit ..        解析过程:                   from .. on.. join ..where ..group by ....having ...select dinstinct ..order by limit ...

二、SQL优化的一些方法

1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: select id from t where num is null 可以在num上设置默认值0,确保表中num列没有null值,然后这样查询: select id from t where num=0

3.应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。

4.应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如: select id from t where num=10 or num=20 可以这样查询: select id from t where num=10 union all select id from t where num=20

5.in 和 not in 也要慎用,否则会导致全表扫描,如: select id from t where num in(1,2,3) 对于连续的数值,能用 between 就不要用 in 了: select id from t where num between 1 and 3

6.下面的查询也将导致全表扫描: select id from t where name like ‘%abc%’

7.应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如: select id from t where num/2=100 应改为: select id from t where num=100*2

8.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如: select id from t where substring(name,1,3)=’abc’–name以abc开头的id 应改为: select id from t where name like ‘abc%’

9.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

10.在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。

11.不要写一些没有意义的查询,如需要生成一个空表结构: select col1,col2 into #t from t where 1=0 这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样: create table #t(…)

12.很多时候用 exists 代替 in 是一个好的选择: select num from a where num in(select num from b) 用下面的语句替换: select num from a where exists(select 1 from b where num=a.num)

13.并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,如一表中有字段sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。

14.索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率, 因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。 一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。

15.尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。 这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。

16.尽可能的使用 varchar 代替 char ,因为首先变长字段存储空间小,可以节省存储空间,

其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。

17.任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。

18.避免频繁创建和删除临时表,以减少系统表资源的消耗。

19.临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件,最好使用导出表。

20.在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log , 以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。

21.如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。

22.尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。

23.使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。

24.与临时表一样,游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。 在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许,基于游标的方法和基于集的方法都可以尝试一下,看哪一种方法的效果更好。

25.尽量避免大事务操作,提高系统并发能力。

26.尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。

————————

A large part is excerpted.

< strong > I. why should SQL be optimized < / strong >

At the initial stage of the launch of our development project, due to the relatively small amount of business data, the impact of the execution efficiency of some SQL on the operation efficiency of the program is not obvious, and the development and operation and maintenance personnel can not judge how much SQL affects the operation efficiency of the program, so there is little special optimization for SQL. With the accumulation of time, the amount of business data increases, The impact of SQL execution efficiency on the running efficiency of the program is gradually increasing. At this time, it is necessary to optimize SQL.

    编写过程:    select dinstinct  ..from  ..join ..on ..where ..group by ...having ..order by ..limit ..        解析过程:                   from .. on.. join ..where ..group by ....having ...select dinstinct ..order by limit ...

< strong > II. Some methods of SQL optimization < / strong >

1. To optimize the query, try to avoid full table scanning. First, consider establishing indexes on the columns involved in where and order by.

2. Try to avoid judging the null value of the field in the where clause, otherwise the engine will give up using the index and scan the whole table. For example, select id from t where num is null. You can set the default value 0 on num to ensure that there is no null value in the num column in the table, and then query: select id from t where num = 0

3. Try to avoid using it in the where clause= Or < > operator, otherwise the engine will abandon the index and perform a full table scan.

4.应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如: select id from t where num=10 or num=20 可以这样查询: select id from t where num=10 union all select id from t where num=20

5. In and not in should also be used with caution, otherwise it will lead to full table scanning. For example, select id from t where num in (1,2,3) for continuous values, use between instead of in: select id from t where num between 1 and 3

6. The following query will also cause a full table scan: select id from t where name like ‘% ABC%’

7. Expression operations on fields in the where clause should be avoided as far as possible, which will cause the engine to give up using the index and scan the whole table. For example, select id from t where num / 2 = 100 should be changed to: select id from t where num = 100 * 2

8. Try to avoid functional operations on fields in the where clause, which will cause the engine to give up using indexes and scan the whole table. For example: select id from t where substring (name, 1,3) =’abc ‘– the ID starting with ABC should be changed to: select id from t where name like’ ABC% ‘

9. Do not perform functions, arithmetic operations or other expression operations on the left of “=” in the where clause, otherwise the system may not use the index correctly.

10. When using the index field as a condition, if the index is a composite index, the first field in the index must be used as a condition to ensure that the system can use the index, otherwise the index will not be used, and the field order should be consistent with the index order as much as possible.

11. Do not write meaningless queries. If you need to generate an empty table structure: select col1, col2 into #t from t where 1 = 0, such codes will not return any result set, but will consume system resources, you should change it to this: create table #t (…)

12.很多时候用 exists 代替 in 是一个好的选择: select num from a where num in(select num from b) 用下面的语句替换: select num from a where exists(select 1 from b where num=a.num)

13. Not all indexes are valid for queries. SQL optimizes queries based on the data in the table. When there are a large number of duplicate data in the index column, SQL queries may not use indexes. For example, if there are fields sex, male and female in a table, almost half of each, then even if an index is built on sex, it will not play a role in query efficiency.

14. The more indexes, the better. Indexes can not only improve the efficiency of corresponding selection, but also reduce the efficiency of insert and update. Because the index may be rebuilt during insert or update, how to build an index needs careful consideration, depending on the specific situation. The index number of a table should not exceed 6. If it is too many, consider whether it is necessary to build an index on some infrequently used columns.

15. Try to use numeric fields. If the fields containing only numerical information are not designed as characters, it will reduce the performance of query and connection and increase the storage overhead. This is because the engine will compare each character in the string one by one when processing queries and connections. For numeric types, only one comparison is enough.

16. Use varchar instead of char as much as possible, because first, the storage space of variable length fields is small, which can save storage space,

Secondly, for queries, the search efficiency in a relatively small field is obviously higher.

17. Do not use select * from t anywhere, replace “*” with a specific field list, and do not return any fields that are not used.

18. Avoid frequent creation and deletion of temporary tables to reduce the consumption of system table resources.

19. Temporary tables are not unusable. Proper use of them can make some routines more effective, for example, when it is necessary to repeatedly reference a data set in a large table or common table. However, for one-time events, it is best to use the export table.

20. When creating a temporary table, if a large amount of data is inserted at one time, select into can be used instead of create table to avoid causing a large number of logs and improve the speed; If the amount of data is small, in order to ease the resources of the system table, create table first and then insert.

21. If temporary tables are used, be sure to explicitly delete all temporary tables at the end of the stored procedure, truncate table first, and then drop table, so as to avoid locking the system tables for a long time.

22. Try to avoid using cursors because the efficiency of cursors is poor. If the data operated by cursors exceeds 10000 rows, rewriting should be considered.

23. Before using cursor based methods or temporary table methods, you should first find a set based solution to solve the problem. Set based methods are usually more effective.

24. Like temporary tables, cursors are not unusable. Using fast for small datasets_ Forward cursors are generally superior to other row by row methods, especially when several tables must be referenced to obtain the required data. Routines that include “totals” in the result set are usually executed faster than using cursors. If development time permits, both cursor based and set based methods can be tried to see which method works better.

25. Try to avoid large transaction operations and improve system concurrency.

26. Try to avoid returning large amounts of data to the client. If the amount of data is too large, consider whether the corresponding requirements are reasonable.