|
|
|
|
移动端

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

我们说调优数据库既是门艺术,又是门科学,这是有道理的,因为很少有全面适用的硬性规则。你在一个系统上解决的问题在另一个系统上不是问题,反之亦然。说到调优查询,没有正确的答案,但这并不意味着就此应该放弃。你可以遵循以下一些原则,有望收到很好的效果。

作者:数据分析师来源:ITPUB|2018-02-27 15:48

人工智能+区块链的发展趋势及应用调研报告


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来检查该数据是否存在:

  1. SET @CT=(SELECT COUNT(*) FROM  
  2. dbo.T1);  
  3. If@CT>0  
  4. BEGIN  
  5. <Do something>  
  6. END 

这完全没必要,如果你想检查数据是否存在,只要这么做:

  1. If EXISTS (SELECT 1 FROM dbo.T1)  
  2. BEGIN  
  3. <Do something>  
  4. END 

不要计数表中的一切,只要取回你找到的第一行。SQL Server 聪明得很,会正确使用 EXISTS,第二段代码返回结果超快。

表越大,这方面的差距越明显。在你的数据变得太大之前做正确的事情。调优数据库永不嫌早。

实际上,我只是在我的其中一个生产数据库上运行这个例子,针对一个有 2.7 亿行的表。

第一次查询用时 15 秒,包含 456197 个逻辑读取,第二次查询不到 1 秒就返回结果,只包含 5 个逻辑读取。

然而如果你确实需要计数表的行数,表又很大,另一种方法就是从系统表中提取,

SELECT rows fromsysindexes 将为你获得所有索引的行数。

又由于聚类索引代表数据本身,所以只要添加 WHERE indid = 1,就能获得表行,然后只需包含表名称即可。

所以,最后的查询是:

  1. SELECT rows from sysindexes where object_name(id)='T1'and indexid =1 

在我 2.7 亿行的表中,不到 1 秒就返回结果,只有 6 个逻辑读取,现在性能不一样了。

不要进行逆向搜索

以简单的查询 SELECT * FROMCustomers WHERE RegionID <> 3 为例。你不能将索引与该查询结合使用,因为它是逆向搜索,需要借助表扫描来逐行比较。如果你需要执行这样的任务,可能发现如果重写查询以使用索引,性能会好得多。

该查询很容易重写,就像这样:

  1. SELECT * FROM Customers WHERE RegionID<3 UNION ALL SELECT * FROM Customers WHERE RegionID 

这个查询将使用索引,所以如果你的数据集很大,其性能会远胜过表扫描版本。

当然,没有什么是那么容易的,也许性能更糟,所以使用之前先试一下。它百分之百管用,虽然涉及太多的因素。

最后,我意识到这个查询违反了第 4 条规则:不要查询两次,但这也表明没有硬性规则。虽然我们在这里查询两次,但这么做是为了避免开销很大的表扫描。

你无法一直运用所有这些技巧,但如果牢记它们,有一天你会用它们来解决一些大问题。

要记住的最重要一点是,别将我说的话当成教条。在你的实际环境中试一下,同样的解决办法不是在每种情况下都管用,不过我排查糟糕的性能时一直使用这些方法,而且屡试不爽。

【编辑推荐】

  1. Java数据库读写分离-数据库中间件DBProxy
  2. 如何将一个数据库中的一个表复制到另一个数据库的表中去
  3. Java如何连接MySQL数据库进行操作
  4. MySQL统计信息以及执行计划预估方式初探
  5. 简单几招提高MySQL安全性
【责任编辑:庞桂玉 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

读 书 +更多

Expert C# 2005 Business Objects中文版

本书描述了怎样应用面向对象的概念来进行.NET应用程序的架构、设计和开发。作者将重点放在了面向业务的对象,即业务对象和怎样在包括Web和...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊