最新公告
  • 欢迎您光临欧资源网,本站秉承服务宗旨 履行“站长”责任,销售只是起点 服务永无止境!立即加入我们
  • 本文会提到52条SQL语句性能优化策略(图)

    本文将提到52种SQL语句性能优化策略。

    1、要优化查询,尽量避免全表扫描,首先考虑在WHERE和ORDER BY涉及的列上建索引。

    2、应尽量避免对WHERE子句中的字段进行NULL值判断。NULL 是创建表时的默认值,但大多数时候应该使用 NOT NULL,或者使用 0、-1 等特殊值作为 Defaults。

    3、您应该尽量避免在 WHERE 子句中使用 != 或运算符。MySQL 仅对以下运算符使用索引:=、BETWEEN、IN,有时还有 LIKE。

    4、 尽量避免在 WHERE 子句中使用 OR 连接条件,否则引擎会放弃使用索引,进行全表扫描。您可以使用 UNION 来合并查询: select id from t where num=10 union all select id from t where num=20。

    5、IN 和 NOT IN 也应该谨慎使用,否则会导致全表扫描。对于连续值,请使用 BETWEEN 而不是 IN:从 t 中选择 id,其中 num 介于 1 和 3 之间。

    6、下面的查询也会导致全表扫描: select id from t where name like ‘%abc%’ or select id from t where name like ‘%abc’ 为了提高效率,考虑全文搜索. 并从 t 中选择 id,其中 ‘abc%’ 之类的名称仅使用索引。

    7、如果在 WHERE 子句中使用参数,也可能导致全表扫描。

    8、尽量避免对WHERE子句中的字段进行表达式操作,尽量避免对WHERE子句中的字段进行函数操作。

    9、很多时候使用 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)。

    10、索引可以提高对应SELECT的效率,但也降低了INSERT和UPDATE的效率。因为在INSERT或UPDATE过程中可能会重建索引,所以如何建立索引需要慎重考虑,视具体情况而定。一张表的索引数不要超过6个。如果索引太多,要考虑是否需要在一些很少使用的列上建索引。

    11、 尽量避免更新聚集索引数据列,因为聚集索引数据列的顺序是表记录的物理存储顺序。一旦该列的值发生变化,会导致整张表记录顺序的调整,会耗费相当多的时间。很棒的资源。如果应用系统需要频繁更新聚集索引数据列,则需要考虑是否要将索引构建为聚集索引。

    12、尽可能使用数字字段。如果字段只包含数字信息,尽量不要设计字符字段,这样会降低查询和连接的性能,增加存储开销。

    13、尽量用varchar、nvarchar代替char、nchar。因为变长字段的存储空间小,可以节省存储空间,其次,对于查询来说,相对较小的字段的搜索效率明显更高。

    14、最好不要使用return all:select from t,用特定字段的列表替换“*”,不要返回任何没有使用的字段。

    15、 尽量避免向客户端返回大量数据。如果数据量太大,则应考虑相应的需求是否合理。

    16、使用表的别名(Alias):在SQL语句中连接多个表时,请使用表的别名,并在每个Column前加上别名。这减少了解析时间并减少了由列歧义引起的语法错误。

    17、使用“临时表”来暂存中间结果:

    简化 SQL 语句的一个重要方法是使用临时表来临时存储中间结果。然而,临时表的好处远不止这些。临时结果暂存在临时表中,后续查询在tempdb中,可以避免程序中多次扫描主表,大大减少程序执行过程中对“共享锁”的阻塞。“更新锁”减少阻塞并提高并发性能。

    18、有些SQL查询语句要加nolock,读写会互相阻塞,以提高并发性能。对于一些查询,可以加nolock来允许读时写,但缺点是可能会读到未提交的脏数据。

    使用 nolock 有 3 个原则:

    19、常用的简化规则如下:

    没有超过 5 个表连接(JOIN),考虑使用临时表或表变量来存储中间结果。使用较少的子查询,不要嵌套太深的视图。通常,建议不要嵌套超过 2 个视图。

    20、预先计算好要查询的结果并放入表中,查询时选择。这是SQL7.0之前最重要的方法,比如医院住院费的计算。

    21、OR子句可以分解成多个查询,多个查询可以通过UNION连接起来。它们的速度只与是否使用索引有关。如果查询需要使用联合索引,使用 UNION all 效率更高。多个 OR 子句不使用索引,将其重写为 UNION 形式并尝试匹配索引。一个关键问题是是否使用索引。

    22、在IN之后的值列表中sql 触发器 在插入前 改变值,将出现频率最高的值放在最前面,出现次数最少的值放在最后,减少判断次数。

    23、 尽量把数据处理放在服务器上,减少网络开销,比如使用存储过程。

    存储过程是经过编译、优化、组织成执行计划并存储在数据库中的 SQL 语句。它们是控制流语言的集合,当然速度很快。对于重复执行的动态SQL,可以使用临时存储过程,将过程(临时表)放在Tempdb中。

    24、服务器有足够内存时,配置线程数=最大连接数+5,可以最大化效率;否则,使用配置线程数

    25、查询的关联写法顺序相同:

    select a.personMemberID, * from chineseresume a,personmember b where personMemberID = b.referenceid and a.personMemberID = ‘JCNPRH39681’ (A = B, B = ‘Number’)

    select a.personMemberID, * from chineseresume a,personmember b where a.personMemberID = b.referenceid and a.personMemberID = ‘JCNPRH39681’ and b.referenceid = ‘JCNPRH39681’ (A = B, B = ‘Number’, A = ‘数字’)

    select a.personMemberID, * from chineseresume a,personmember b where b.referenceid = ‘JCNPRH39681’ and a.personMemberID = ‘JCNPRH39681’ (B = ‘Number’, A = ‘Number’)

    26、尽量用EXISTS代替select count(1)判断是否有记录。count函数只在统计表中所有行的时候使用,count(1) 比 count(*) 更有效。

    27、尽可能使用“>=”,而不是“>”。

    28、索引的使用说明:

    29、以下SQL条件语句中的列被正确索引,但是执行速度很慢:

    SELECT * FROM record WHERE substrING(card_no, 1, 4) = ‘5378’ –13 秒

    SELECT * FROM 记录 WHERE 金额/30

    SELECT * FROM record WHERE convert(char(10), date, 112) = ‘19991201’ –10 秒

    分析:

    在 SQL 运行时,对 WHERE 子句中的列进行的任何操作的结果都是逐列计算的,因此它必须在不使用该列上的索引的情况下进行表搜索。

    如果这些结果在查询编译时可用,它们可以通过 SQL 优化器进行优化,使用索引,避免表搜索,因此重写 SQL 如下:

    SELECT * FROM record WHERE card_no like ‘5378%’ —

    SELECT * FROM 记录 WHERE 金额

    SELECT * FROM 记录 WHERE 日期 = ‘1999/12/01’ —

    30、当有批量插入或更新时,使用批量插入或批量更新永远不会一条一条地更新记录。

    31、在所有的存储过程中,我永远不会使用循环来实现SQL语句。

    例如:列出上个月的每一天,我会使用connect by递归查询,从不使用从上个月的第一天到最后一天的循环。

    32、选择最有效的表名顺序(仅在基于规则的优化器中有效):

    Oracle 的解析器按从右到左的顺序处理 FROM 子句中的表名。FROM 子句中写入的最后一个表(底层表驱动表)将首先处理。FROM 子句中包含多个表。在 的情况下,必须选择记录最少的表作为基表。

    如果表连接查询超过3个,则需要选择交集表作为基表,交集表是指被其他表引用的表。

    33、通过在GROUP BY之前过滤掉不必要的记录来提高GROUP BY语句的效率。以下两个查询返回相同的结果,但第二个查询要快得多。

    低效:

    选择工作,平均(SAL)

    来自 EMP

    按工作分组

    有工作=’总统’

    或工作 = ‘经理’

    高效的:

    选择工作,平均(SAL)

    来自 EMP

    WHERE 工作 = ‘总统’

    或工作 = ‘经理’

    按工作分组

    34、SQL语句是大写的,因为Oracle总是先解析SQL语句,把小写字母转大写再执行。

    35、别名的使用,别名是大型数据库的应用技巧,即查询中表名和列名用字母加别名,查询速度1.5倍比建立连接表更快。

    36、避免死锁,在你的存储过程和触发器中总是以相同的顺序访问同一张表;尽可能缩短交易时间sql 触发器 在插入前 改变值,尽可能少涉及交易金额的数据;永远不要在交易中等待用户输入。

    37、避免使用临时表。除非有必要,否则应尽量避免使用临时表。相反,您可以改用表变量。大多数情况下(99%),表变量驻留在内存中,因此它们比驻留在 TempDb 数据库中的临时表快,因此对临时表的操作需要跨数据库通信,这自然会更慢。

    38、最好不要使用触发器:

    39、索引创建规则:

    40、MySQL查询优化总结:

    使用慢查询日志查找慢查询,使用执行计划确定查询是否正常运行,并始终测试您的查询以查看它们是否运行最佳。

    性能总会随时间变化,避免对整张表使用count(*),可能会锁住整张表,保持查询一致以便后续类似查询可以使用查询缓存,在合适的地方使用GROUP BY而不是DISTINCT,使用WHERE、GROUP BY 和 ORDER BY 子句中的索引列使索引保持简单,并且不在多个索引中包含同一列。

    有时MySQL会使用错误的索引,这种情况下使用USE INDEX,检查使用SQL_MODE=STRICT的问题,对于少于5条记录的索引字段,UNION时使用LIMIT而不是OR。

    要避免在更新前选择 SELECT,请使用 INSERT ON DUPLICATE KEY 或 INSERT IGNORE;不使用UPDATE实现,不使用MAX;使用索引字段和 ORDER BY 子句 LIMIT M, N 实际上可以减慢查询速度 在某些情况下,有谨慎使用,在 WHERE 子句中使用 UNION 代替子查询,重启 MySQL,记得预热数据库以确保数据在内存中并快速查询,考虑持久连接而不是多个连接以减少开销。

    基准查询,包括使用服务器上的负载,有时一个简单的查询会影响其他查询,当服务器上负载增加时,使用 SHOW PROCESSLIST 来查看速度慢和有问题的查询,在开发环境中产生的镜像数据中测试所有可疑查询。

    41、MySQL备份过程:

    42、查询缓冲区不会自动处理空格,因此,在编写SQL语句时,应尽量减少空格的使用,尤其是在SQL的开头和结尾处(因为查询缓冲区不会自动截断前导和尾随空格) .

    43、成员使用mid作为分表的标准,方便查询?在一般的业务需求中,查询基本上是基于用户名。通常,用户名应该是哈希模来划分表。

    如果分表,MySQL的partition函数就是这样做的,对代码是透明的;在代码级别实现它似乎不合理。

    44、我们应该为数据库中的每个表设置一个ID作为它的主键,最好是INT类型(推荐UNSIGNED),并设置自动增加的AUTO_INCREMENT标志。

    45、在所有存储过程和触发器的开头设置SET NOCOUNT ON,最后设置SET NOCOUNT OFF。存储过程和触发器的每条语句执行完毕后,无需向客户端发送 DONE_IN_PROC 消息。

    46、MySQL 查询可以启用高速查询缓存。这是提高数据库性能的有效 MySQL 优化方法之一。当多次执行相同的查询时,从缓存中获取数据并直接从数据库返回数据要快得多。

    47、EXPLAIN SELECT 查询用于跟踪查看性能:

    使用 EXPLAIN 关键字可以让您了解 MySQL 如何处理您的 SQL 语句。这可以帮助您分析查询语句或表结构的性能瓶颈。EXPLAIN 查询结果还将告诉您如何使用索引主键,以及如何搜索和排序数据表。

    48、只有一行数据时使用LIMIT 1:

    当您查询该表一段时间时,您已经知道结果只会有一个结果,但是因为您可能需要获取游标,或者您可能会检查返回的记录数。

    在这种情况下,添加 LIMIT 1 可以提高性能。这样,MySQL数据库引擎会在找到一条数据后停止搜索,而不是继续搜索下一条匹配的记录。

    49、为表选择合适的存储引擎:

    50、优化表的数据类型,选择合适的数据类型:

    原则:通常越小越好,简单越好,所有字段必须有默认值,尽量避免NULL。

    例如,在设计数据库表时,尽可能使用较小的整数类型,以占用更少的磁盘空间。(mediumint 比 int 更合适)

    例如时间字段:日期时间和时间戳。datetime 占 8 个字节,timestamp 占 4 个字节,只有一半。时间戳表示的范围是1970-2037,适合更新时间。

    MySQL 可以很好地支持大数据的访问,但一般来说,数据库中的表越小,查询在其上执行的速度就越快。

    因此,在创建表时,为了获得更好的性能,我们可以将表中字段的宽度设置得越小越好。

    例如:在定义邮政编码字段时,如果设置为CHAR(255),显然给数据库增加了不必要的空间。即使使用VARCHAR也是多余的,因为CHAR(6)可以完成任务很好。

    同样,如果可能的话,我们应该使用 MEDIUMINT 而不是 BIGIN 来定义整数字段,并且我们应该尽量将该字段设置为 NOT NULL,这样数据库以后在执行查询时就不必比较 NULL 值了。

    对于某些文本字段,例如“province”或“gender”,我们可以将它们定义为 ENUM 类型。因为在 MySQL 中,ENUM 类型被视为数值数据,数值数据的处理速度比文本类型快得多。这样,我们可以再次提高数据库的性能。

    51、String 数据类型:char、varchar、text 选择区别。

    52、对列的任何操作都会导致表扫描,包括数据库函数、计算表达式等,查询时尽量将操作移到等号右边。

    站内大部分资源收集于网络,若侵犯了您的合法权益,请联系我们删除!
    欧资源网 » 本文会提到52条SQL语句性能优化策略(图)

    常见问题FAQ

    免费下载或者VIP会员专享资源能否直接商用?
    本站所有资源版权均属于原作者所有,这里所提供资源均只能用于参考学习用,请勿直接商用。若由于商用引起版权纠纷,一切责任均由使用者承担。更多说明请参考 VIP介绍。
    提示下载完但解压或打开不了?
    最常见的情况是下载不完整: 可对比下载完压缩包的与网盘上的容量,若小于网盘提示的容量则是这个原因。这是浏览器下载的bug,建议用百度网盘软件或迅雷下载。若排除这种情况,可在对应资源底部留言,或 联络我们.。
    找不到素材资源介绍文章里的示例图片?
    对于PPT,KEY,Mockups,APP,网页模版等类型的素材,文章内用于介绍的图片通常并不包含在对应可供下载素材包内。这些相关商业图片需另外购买,且本站不负责(也没有办法)找到出处。 同样地一些字体文件也是这种情况,但部分素材会在素材包内有一份字体下载链接清单。
    欧资源网
    一个高级程序员模板开发平台

    发表评论