`

常见的sql优化

 
阅读更多

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

2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,

Sql 代码 : select id from t where num is null;

可以在 num 上设置默认值 0,确保表中 num 列没有 null 值,然后这样查询:

Sql 代码 : select id from t where num=0;

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

4.应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,

Sql 代码 : select id from t where num=10 or num=20;

可以这样查询:

Sql 代码 : select id from t where num=10 union all select id from t where num=20;

5.in 和 not in 也要慎用,否则会导致全表扫描,如:

Sql 代码 : select id from t where num in(1,2,3);

对于连续的数值,能用 between 就不要用 in 了:

Sql 代码 : select id from t where num between 1 and 3;

6.下面的查询也将导致全表扫描:

Sql 代码 : select id from t where name like '%c%';

若要提高效率,可以考虑全文检索。

7.如果在 where 子句中使用参数,也会导致全表扫描。因为 SQL 只有在运行时才会解析局部变量,但优 化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然 而,如果在编译时建立访问计 划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:

Sql 代码 : select id from t where num=@num ;

可以改为强制查询使用索引:

Sql 代码 : select id from t with(index(索引名)) where num=@num ;

8.应尽量避免在 where 子句中对字段进行表达式操作, 这将导致引擎放弃使用索引而进行全表扫描。

Sql 代码 : select id from t where num/2=100;

可以这样查询:

Sql 代码 : select id from t where num=100*2;

9.应尽量避免在 where 子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:

Sql 代码 : select id from t where substring(name,1,3)='abc';#name 以 abc 开头的 id

应改为:

Sql 代码 : select id from t where name like 'abc%';

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

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

12.不要写一些没有意义的查询,如需要生成一个空表结构:

Sql 代码 : select col1,col2 into #t from t where 1=0;

这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:

Sql 代码 : create table #t(…);

13.很多时候用 exists 代替 in 是一个好的选择:

Sql 代码 : select num from a where num in(select num from b);

用下面的语句替换:

Sql 代码 : select num from a where exists(select 1 from b where num=a.num);

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

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

16.应尽可能的避免更新 clustered 索引数据列, 因为 clustered 索引数据列的顺序就是表记录的物理存储顺序,一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列,那么需要考虑是否应将该索引建为 clustered 索引。

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

18.尽可能的使用 varchar/nvarchar 代替 char/nchar , 因为首先变长字段存储空间小, 可以节省存储空间, 其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。

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

20.尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。

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

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

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

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

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

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

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

28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF .无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

29.尽量避免大事务操作,提高系统并发能力。 sql 优化方法使用索引来更快地遍历表。 缺省情况下建立的索引是非群集索引,但有时它并不是最佳的。在非群集索引下,数据在物理上随机存放在数据页上。合理的索引设计要建立在对各种查询的分析和预测上。一般来说:

a.有大量重复值、且经常有范围查询( > ,< ,> =,< =)和 order by、group by 发生的列,可考虑建立集群索引;

b.经常同时存取多列,且每列都含有重复值可考虑建立组合索引;

c.组合索引要尽量使关键查询形成索引覆盖,其前导列一定是使用最频繁的列。索引虽有助于提高性能但 不是索引越多越好,恰好相反过多的索引会导致系统低效。用户在表中每加进一个索引,维护索引集合就 要做相应的更新工作。

30.定期分析表和检查表。

分析表的语法:ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb1_name[, tbl_name]...

以上语句用于分析和存储表的关键字分布,分析的结果将可以使得系统得到准确的统计信息,使得SQL能够生成正确的执行计划。如果用户感觉实际执行计划并不是预期的执行计划,执行一次分析表可能会解决问题。在分析期间,使用一个读取锁定对表进行锁定。这对于MyISAM,DBD和InnoDB表有作用。

例如分析一个数据表:analyze table table_name
检查表的语法:CHECK TABLE tb1_name[,tbl_name]...[option]...option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

检查表的作用是检查一个或多个表是否有错误,CHECK TABLE 对MyISAM 和 InnoDB表有作用,对于MyISAM表,关键字统计数据被更新

CHECK TABLE 也可以检查视图是否有错误,比如在视图定义中被引用的表不存在。

31.定期优化表。

优化表的语法:OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb1_name [,tbl_name]...

如果删除了表的一大部分,或者如果已经对含有可变长度行的表(含有 VARCHAR、BLOB或TEXT列的表)进行更多更改,则应使用OPTIMIZE TABLE命令来进行表优化。这个命令可以将表中的空间碎片进行合并,并且可以消除由于删除或者更新造成的空间浪费,但OPTIMIZE TABLE 命令只对MyISAM、 BDB 和InnoDB表起作用。

例如: optimize table table_name

注意: analyze、check、optimize执行期间将对表进行锁定,因此一定注意要在MySQL数据库不繁忙的时候执行相关的操作。

分享到:
评论

相关推荐

    常见sql 优化的方法

    ### 常见SQL优化的方法:深入解析与实践 在数据库管理与开发中,SQL查询的性能直接影响到系统的响应速度和资源消耗。本文将详细探讨常见的SQL优化方法,特别是针对Oracle和SQL Server这两种广泛使用的数据库管理...

    基于Oracle数据库的几种常见SQL优化策略.pdf

    基于Oracle数据库的几种常见SQL优化策略.pdf 本文档主要讨论了基于Oracle数据库的几种常见SQL优化策略,以提高数据库的处理性能和查询效率。在当前大数据和物联网发展的背景下,数据库的性能优化变得越来越重要。...

    基于Oracle数据库的几种常见SQL优化策略研究.pdf

    本文将详细介绍几种常见且有效的Oracle数据库SQL优化策略。 首先,明确SQL语句优化的必要性是开展优化工作的前提。SQL语句在数据分析和应用中使用极其频繁,对于大多数应用软件而言,几乎所有的数据库操作都涉及SQL...

    SQL优化 SQL优化软件 SQL优化工具

    SQL优化是数据库管理中的关键环节,它涉及到提升查询性能、减少资源消耗以及改善系统整体效率。SQL优化软件和工具能够帮助数据库管理员(DBA)和开发人员找出性能瓶颈,优化查询逻辑,从而提高数据库系统的响应速度...

    收获,不止SQL优化--抓住SQL的本质1

    - **常见误区**:纠正读者在SQL优化过程中可能存在的错误观念。 - **颠覆传统观点**:挑战一些传统的优化方法,提出新的思考方向。 - **真诚交流**:作者通过提供额外的学习资源等方式,鼓励读者进行深入思考,并...

    18 SQL优化--其他SQL的优化.doc

    ### 常见SQL优化技巧 #### 大批量插入数据 - 使用 `LOAD DATA INFILE` 命令高效地导入数据。 ```sql mysql&gt; LOAD DATA INFILE 'data.txt' INTO TABLE db.my_table; ``` - 在导入大量数据到 MyISAM 表时,可以先...

    SQL优化 '%5400%' LIKE操作符

    #### 二、常见SQL优化技巧 除了上述执行计划的选择外,还有一些常见的SQL优化技巧可以提高查询效率: - **避免使用 `IN` 和 `NOT IN`**:`IN` 和 `NOT IN` 子句可能会导致全表扫描。尤其是在子查询中使用它们时更...

    基于案例学SQL优化

    本主题"基于案例学SQL优化"将深入探讨如何通过实际案例来理解和实践SQL优化的策略和技术。 首先,我们要明确SQL优化的重要性。当数据库规模增大,查询复杂度增加时,未优化的SQL语句可能导致响应时间过长,影响用户...

    ORACLE-SQL性能优化大全.pdf

    - **SQL优化机制**: - **SQL语句处理过程**:理解SQL语句在Oracle中的处理流程对于优化至关重要。 - **共享SQL区域**:Oracle会在内存的共享池中缓存已执行过的SQL语句,以便后续执行时可以直接使用而无需重新...

    sql优化的几种方法

    ### SQL优化的关键方法 #### 一、选择最有效的表名顺序 在构建SQL查询时,尤其是在涉及多个表的连接查询中,表名的顺序对于查询性能有着重要影响。Oracle数据库的解析器是从右向左处理FROM子句中的表名。为了优化...

    sql 语句优化详解及案例

    #### 常见SQL优化策略 1. **合理利用索引**:索引能够加速数据检索过程,但过多或不合理的索引也会增加磁盘空间的消耗和数据更新时的开销。因此,根据查询模式设计合适的索引策略是优化SQL语句的基础。 2. **避免...

    【整理】数据库面试题索引sql优化+数据库SQL优化总结之百万级数据库优化

    本文将深入探讨数据库面试中的常见问题,特别是关于SQL优化和针对大规模数据库的优化策略。首先,我们来看看"数据库面试题索引sql优化.pdf"可能涵盖的内容。 1. **SQL基础与语法**:面试通常会涉及到SQL的基本概念...

    DB2之SQL优化浅析.pdf

    虽然文档中未给出具体的案例,但我们可以假设一些常见的SQL优化场景: - **Case 1: 复杂查询优化** - 原始SQL可能涉及多个表的联接和子查询,通过分解查询、使用合适的索引和连接顺序,可以显著提升性能。 - **...

    Oracle数据库sql优化手册

    Oracle数据库sql优化手册 主要是针对常见的sql进行优化

    sql优化脚本

    在SQL优化领域,提高数据库性能是一项至关重要的任务。"sql优化脚本"通常是指一系列用于改进查询效率、减少资源消耗和提升系统响应时间的代码片段。以下是对这些知识点的详细阐述: 1. **查询优化**:SQL查询优化是...

    SQL优化.pdf

    理解并合理运用SQL优化规则可以帮助开发者写出更加高效和健壮的SQL语句,从而在工作中避免常见的性能瓶颈。这些规则涉及了SQL语句的编写规范、数据库结构的调整技巧,以及查询参数的处理策略,是数据库管理和开发...

    db2数据库性能优化小技巧

    #### 2.2 常见SQL优化策略 - **索引优化**:合理创建索引,避免过多或过少的索引,确保索引能够被有效利用。 - **查询优化**:简化查询条件,减少不必要的JOIN操作,避免使用SELECT *等全字段查询方式。 - **统计...

    SQL优化经验大全

    ### SQL优化经验大全 在数据库管理和开发过程中,SQL查询优化是一项至关重要的技能。良好的SQL查询不仅可以提高系统的响应速度,还能显著降低系统资源消耗。本文将详细介绍30多条SQL优化的经验,帮助您更好地理解和...

    1从案例中推导SQL优化的总体思路与误区

    通过对SQL优化的总体思路和常见误区的探讨,我们可以发现,很多传统观点并不适用于现代数据库环境。因此,在进行SQL优化时,应该结合实际需求和数据库系统的特性来做出决策。同时,随着技术的发展,不断地学习和更新...

Global site tag (gtag.js) - Google Analytics