SQL查询提速秘诀,避免锁死数据库的数据库代码

发表于:2018-3-02 10:06

字体: | 上一篇 | 下一篇 | 我要投稿

 作者:数据分析师    来源:ITPUB

#
SQL
分享:
  由于数据库领域仍相对不成熟,每个平台上的 SQL 开发人员都在苦苦挣扎,一次又一次犯同样的错误。当然,数据库厂商在取得一些进展,并继续在竭力处理较重大的问题。
  无论 SQL 开发人员在 SQL Server、Oracle、DB2、Sybase、MySQL,还是在其他任何关系数据库平台上编写代码,并发性、资源管理、空间管理和运行速度都仍困扰着他们。
  问题的一方面是,不存在什么灵丹妙药;针对几乎每条最佳实践,我都可以举出至少一个例外。
  我们说调优数据库既是门艺术,又是门科学,这是有道理的,因为很少有全面适用的硬性规则。你在一个系统上解决的问题在另一个系统上不是问题,反之亦然。
  说到调优查询,没有正确的答案,但这并不意味着就此应该放弃。你可以遵循以下一些原则,有望收到很好的效果。
  不要用 UPDATE 代替 CASE
  这个问题很常见,却很难发觉,许多开发人员常常忽视这个问题,原因是使用 UPDATE 再自然不过,这似乎合乎逻辑。
  以这个场景为例:你把数据插入一个临时表中,如果另一个值存在,需要它显示某个值。
  也许你从 Customer 表中提取记录,想把订单金额超过 100000 美元的客户标记为“Preferred”。
  因而,你将数据插入到表中,运行 UPDATE 语句,针对订单金额超过 100000 美元的任何客户,将 CustomerRank 这一列设为“Preferred”。
  问题是,UPDATE 语句记入日志,这就意味着每次写入到表中,要写入两次。
  解决办法:在 SQL 查询中使用内联 CASE 语句,这检验每一行的订单金额条件,并向表写入“Preferred”标记之前,设置该标记,这样处理性能提升幅度很惊人。
  不要盲目地重用代码
  这个问题也很常见,我们很容易拷贝别人编写的代码,因为你知道它能获取所需的数据。
  问题是,它常常获取过多你不需要的数据,而开发人员很少精简,因此到头来是一大堆数据。
  这通常表现为 WHERE 子句中的一个额外外连接或额外条件。如果你根据自己的确切要求精简重用的代码,就能大幅提升性能。
  需要几列,就提取几列
  这个问题类似第 2 个问题,但这是列所特有的。很容易用 SELECT* 来编写所有查询代码,而不是把列逐个列出来。
  问题同样是,它提取过多你不需要的数据,这个错误我见过无数次了。开发人员对一个有 120 列、数百万行的表执行 SELECT* 查询,但最后只用到其中的三五列。
  因此,你处理的数据比实际需要的多得多,查询返回结果是个奇迹。你不仅处理过多不需要的数据,还夺走了其他进程的资源。
  不要查询两次(double-dip)
  这是我看到好多人犯的另一个错误:写入存储过程,从一个有数亿行的表中提取数据。
  开发人员想提取住在加利福尼亚州,年收入高于 4 万美元的客户信息。于是,他查询住在加利福尼亚州的客户,把查询结果放到一个临时表中。
  然后再来查询年收入高于 4 万美元的客户,把那些结果放到另一个临时表中。最后他连接这两个表,获得最终结果。
  你是在逗我吧?这应该用一次查询来完成,相反你对一个超大表查询两次。别犯傻了:大表尽量只查询一次,你会发现存储过程执行起来快多了。
  一种略有不同的场景是,某个过程的几个步骤需要大表的一个子集时,这导致每次都要查询大表。
  想避免这个问题,只需查询这个子集,并将它持久化存储到别处,然后将后面的步骤指向这个比较小的数据集。
  知道何时使用临时表
  这个问题解决起来要麻烦一点,但效果显著。在许多情况下可以使用临时表,比如防止对大表查询两次。还可以使用临时表,大幅减少连接大表所需的处理能力。
  如果你必须将一个表连接到大表,该大表上又有条件,只需将大表中所需的那部分数据提取到临时表中,然后再与该临时表连接,就可以提升查询性能。
  如果存储过程中有几个查询需要对同一个表执行类似的连接,这同样大有帮助。
  预暂存数据
  这是我最爱聊的话题之一,因为这是一种经常被人忽视的老方法。
  如果你有一个报表或存储过程(或一组)要对大表执行类似的连接操作,通过提前连接表,并将它们持久化存储到一个表中来预暂存数据,就可以对你大有帮助。
  现在,报表可以针对该预暂存表来运行,避免大连接。你并非总是可以使用这个方法,但一旦用得上,你会发现这绝对是节省服务器资源的好方法。
  请注意:许多开发人员避开这个连接问题的做法是,将注意力集中在查询本身上,根据连接创建只读视图,那样就不必一次又一次键入连接条件。
  但这种方法的问题是,仍要为需要它的每个报表运行查询。如果预暂存数据,你只要运行一次连接(比如说报表前 10 分钟),别人就可以避免大连接了。
  你不知道我有多喜欢这一招,在大多数环境下,有些常用表一直被连接起来,所以没理由不能先预暂存起来。
  批量删除和更新
  这是另一个经常被忽视的技巧,如果你操作不当,删除或更新来自大表的大量数据可能是一场噩梦。
  问题是,这两种语句都作为单一事务来运行。如果你需要终结它们,或者它们在执行时系统遇到了问题,系统必须回滚(roll back)整个事务,这要花很长的时间。
  这些操作在持续期间还会阻塞其他事务,实际上给系统带来了瓶颈,解决办法就是,小批量删除或更新。
  这通过几个方法来解决问题:
  无论事务因什么原因而被终结,它只有少量的行需要回滚,那样数据库联机返回快得多。
  小批量事务被提交到磁盘时,其他事务可以进来处理一些工作,因而大大提高了并发性。
  同样,许多开发人员一直固执地认为:这些删除和更新操作必须在同一天完成。事实并非总是如此,如果你在归档更是如此。
  如果你需要延长该操作,可以这么做,小批量有助于实现这点;如果你花更长的时间来执行这些密集型操作,切忌拖慢系统的运行速度。
  使用临时表来提高游标性能
  如果可能的话,最好避免游标。游标不仅存在速度问题,而速度问题本身是许多操作的一大问题,还会导致你的操作长时间阻塞其他操作,这大大降低了系统的并发性。
  然而无法总是避免使用游标,避免不了使用游标时,可以改而对临时表执行游标操作,以此摆脱游标引发的性能问题。
  不妨以查阅一个表,基于一些比较结果来更新几个列的游标为例。你也许可以将该数据放入临时表中,然后针对临时表进行比较,而不是针对活动表进行比较。
  然后你可以针对小得多,锁定时间很短的活动表运行单一的 UPDATE 语句。
  进行这样的数据修改可大大提高并发性。最后我要说,你根本不需要使用游标,总是会有一种基于集合的解决方法。
  使用表值函数
  这是一直以来我最爱用的技巧之一,因为它是只有专家才知道的那种秘诀。
  在查询的 SELECT 列表中使用标量函数时,该函数因结果集中的每一行而被调用,这会大幅降低大型查询的性能。
  然而可以将标量函数转换成表值函数,然后在查询中使用 CROSS APPLY,就可以大幅提升性能,这个奇妙的技巧可以显著提升性能。
  不要对同一批次的许多表执行大型操作
  这个似乎很明显,但实则不然。我会用另一个鲜活的例子,因为它更能说明问题。
  我有一个系统存在大量的阻塞,众多操作处于停滞状态。结果查明,每天运行几次的删除例程在删除显式事务中 14 个表的数据。处理一个事务中的所有 14 个表意味着,锁定每个表,直到所有删除完成。
  解决办法就是,将每个表的删除分解成单独的事务,以便每个删除事务只锁定一个表。
  这解放了其他表,缓解了阻塞,让其他操作得以继续运行。你总是应该把这样的大事务分解成单独的小事务,以防阻塞。
  不要使用触发器
  这个与前一个大体一样,但还是值得一提。触发器的问题:无论你希望触发器执行什么,都会在与原始操作同一个的事务中执行。
  如果你写一个触发器,以便更新 Orders 表中的行时将数据插入到另一个表中,会同时锁定这两个表,直到触发器执行完毕。
  如果你需要在更新后将数据插入到另一个表中,要将更新和插入放入到存储过程中,并在单独的事务中执行。
  如果你需要回滚,就很容易回滚,不必同时锁定这两个表。与往常一样,事务要尽量短小,每次不要锁定多个资源。
  不要在 GUID 上聚类
  这么多年后,我难以相信我们居然还在为这个问题而苦恼。但我仍然每年遇到至少两次聚类 GUID。
  GUID(全局唯一标识符)是一个 16 字节的随机生成的数字。相比使用一个稳定增加的值(比如 DATE 或 IDENTITY),按此列对你表中的数据进行排序导致表碎片化快得多。
  几年前我做过一项基准测试,我将一堆数据插入到一个带聚类 GUID 的表中,将同样的数据插入到另一个带 IDENTITY 列的表中。
  GUID 表碎片化极其严重,仅仅过了 15 分钟,性能就下降了几千个百分点。
  5 小时后,IDENTITY 表的性能才下降了几个百分点,这不仅仅适用于 GUID,它适用于任何易失性列。
  如果只需查看数据是否存在,就不要计数行
  这种情况很常见,你需要查看数据存在于表格中,根据这番检查的结果,你要执行某个操作。
  我经常见到有人执行 SELECT COUNT(*)FROMdbo.T1来检查该数据是否存在:
  SET @CT=(SELECT COUNT(*) FROM
  dbo.T1);
  If@CT>0
  BEGIN
  <Do something>
  END
  这完全没必要,如果你想检查数据是否存在,只要这么做:
  If EXISTS (SELECT 1 FROM dbo.T1)
  BEGIN
  <Do something>
  END
  不要计数表中的一切,只要取回你找到的第一行。SQL Server 聪明得很,会正确使用 EXISTS,第二段代码返回结果超快。
  表越大,这方面的差距越明显。在你的数据变得太大之前做正确的事情。调优数据库永不嫌早。
  实际上,我只是在我的其中一个生产数据库上运行这个例子,针对一个有 2.7 亿行的表。
  第一次查询用时 15 秒,包含 456197 个逻辑读取,第二次查询不到 1 秒就返回结果,只包含 5 个逻辑读取。
  然而如果你确实需要计数表的行数,表又很大,另一种方法就是从系统表中提取,
  SELECT rows fromsysindexes 将为你获得所有索引的行数。
  又由于聚类索引代表数据本身,所以只要添加 WHERE indid = 1,就能获得表行,然后只需包含表名称即可。
  所以,最后的查询是:
  SELECT rows from sysindexes where object_name(id)='T1'and indexid =1
  在我 2.7 亿行的表中,不到 1 秒就返回结果,只有 6 个逻辑读取,现在性能不一样了。
  不要进行逆向搜索
  以简单的查询 SELECT * FROMCustomers WHERE RegionID <> 3 为例。你不能将索引与该查询结合使用,因为它是逆向搜索,需要借助表扫描来逐行比较。如果你需要执行这样的任务,可能发现如果重写查询以使用索引,性能会好得多。
  该查询很容易重写,就像这样:
  SELECT * FROM Customers WHERE RegionID<3 UNION ALL SELECT * FROM Customers WHERE RegionID
  这个查询将使用索引,所以如果你的数据集很大,其性能会远胜过表扫描版本。
  当然,没有什么是那么容易的,也许性能更糟,所以使用之前先试一下。它百分之百管用,虽然涉及太多的因素。
  最后,我意识到这个查询违反了第 4 条规则:不要查询两次,但这也表明没有硬性规则。虽然我们在这里查询两次,但这么做是为了避免开销很大的表扫描。
  你无法一直运用所有这些技巧,但如果牢记它们,有一天你会用它们来解决一些大问题。
  要记住的最重要一点是,别将我说的话当成教条。在你的实际环境中试一下,同样的解决办法不是在每种情况下都管用,不过我排查糟糕的性能时一直使用这些方法,而且屡试不爽。

上文内容不用于商业目的,如涉及知识产权问题,请权利人联系博为峰小编(021-64471599-8017),我们将立即处理。
《2023软件测试行业现状调查报告》独家发布~

关注51Testing

联系我们

快捷面板 站点地图 联系我们 广告服务 关于我们 站长统计 发展历程

法律顾问:上海兰迪律师事务所 项棋律师
版权所有 上海博为峰软件技术股份有限公司 Copyright©51testing.com 2003-2024
投诉及意见反馈:webmaster@51testing.com; 业务联系:service@51testing.com 021-64471599-8017

沪ICP备05003035号

沪公网安备 31010102002173号