珠海专业php培训课程

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 END 这完全没必要，如果你想检查数据是否存在，只要这么做：If EXISTS (SELECT 1 FROM dbo.T1) BEGIN 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 条规则：不要查询两次，但这也表明没有硬性规则。虽然我们在这里查询两次，但这么做是为了避免开销很大的表扫描。你无法一直运用所有这些技巧，但如果牢记它们，有一天你会用它们来解决一些大问题。要记住的最重要一点是，别将我说的话当成教条。在你的实际环境中试一下，同样的解决办法不是在每种情况下都管用，不过我排查糟糕的性能时一直使用这些方法，而且屡试不爽。