SQL的优化（一）

最新推荐文章于 2024-09-23 09:14:08 发布

只有自己努力才是真

最新推荐文章于 2024-09-23 09:14:08 发布

阅读量287

点赞数

分类专栏：数据库文章标签：优化 sql

本文链接：https://blog.csdn.net/css1223mjf/article/details/55062608

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

很多时候，我们为了让程序运行的更快，往往需要做一些调整。例如：从程序代码的结构下手，从设计方面下手，从数据库的执行方面下手等。
在应用程序中的SQL查询可能不能按照您想要的方式对它进行响应。要么它就不会返回数据，要么它响应数据的时间很长。如果响应的时间长的话会影响到
程序的速度从而影响到用户的体验。
我们用户总是希望程序响应迅速，能够在瞬间就把数据解析并显示出来。就我来说，如果在网上浏览一个页面，需要耗费十几秒或者五六秒才能加载出来，早就把它关闭了。

其实对SQL查询的优化就两个字：测试。多测试不同的方案，多调整数据库及其查询需要占用的时间、进行分析。这样总会得到一个最佳的优化方案。
优化SQL从几方面下手：

①、从INSERT返回IDENTITY。

在如何执行SQL INSERT后搜索IDENTITY的值。通常，不在于如何编写检索值的查询，而在于在哪里以及何时进行检索。

	select @@IDENTITY

从这个语句可以看出并不复杂，但是如果这个最新的语句不是INSERT的话，或者针对非INSERT SQL的其他链接运行了此SQL，那么是不会获取期望的值。
这个语句必须是接在INSERT语句之后的。
例如：

	INSERT INTO Student（username，password） values （‘JOHN’，‘123456’）；
	SELECT @@IDENTITY；

②、内嵌视图和临时表：
在有些的时候，查询是需要将数据与其他一下可能只是通过执行GROUP BY然后执行查询才能收集到的数据进行连接。
例如：

	CREATE TABLE temp（StudentId int not null，username varchar(20） not null）；

这些SQL语句会创建一个临时表存储数据，用于与其他的数据或者表进行联接，然后没有用以后把该临时表删除。这种方式有个缺点，会对查询进行大量的I/O操作。
所以，建议采用创建视图的方式来替代临时表。视图其实本质来说也是一张表，这种方式会大大的降低对I/O的操作和磁盘的访问。

 	CREATE VIEW temp as SELECT * FROM STUDENT；

这两种方式相对比较，视图的方式会查询效率更高，而长度更短。临时表会消耗大量的资源，而视图能节省资源的消耗。

③、尽量避免LEFT JOIN和NULL
在很多时候我们需要执行LEFT JOIN和NULL值。但是也并不是全部都是需要使用，所以我们要调整数据的形态。虽然说LEFT JOIN这个工具使用的价值比较大，因为它能
检索出第一个表中的所有行、第二个表中所有匹配的行、以及第二个表中与第一个表不匹配的所有行。
但是，LEFT JOIN消耗的资源非常之多，因为它们包含了NULL的数据在里面。如果能够避免使用它，重新编写查询使用相对客观的INNER JOIN方式来查询，则可以降低代价。
例如：

在测试查询的速度时，需要进行多次运行此查询，然后取一个平均值。因为查询（或者存储过程）可能会存储在内存中的过程缓存中。还有的就是，运行查询的时候，可能正在针对相同的表运行其他查询。当其他查询锁定和解锁这些表时，可能会导致您的查询要排队等待等。
还有一点避免LEFT JOIN的方式是可以围绕这个特点来设计数据库。可以尽量不要在数据库的字段数据中出现NULL值，可以创建“No Category”类型的值，从而指定外键的关系不允许为NULL值。

④、灵活的使用笛卡尔乘积
对于笛卡尔乘积，在许多的情况下，它们消耗的资源太多，从而无法高效的使用。其实从很多方面都能得到笛卡尔乘积的重要性，例如：当你想要一个客户的所有订单，不管是有没销量的订单都需要得到，如果从中使用inner join，则会获得客户有销量的订单，但是不能满足需求。这个时候使用笛卡尔乘积就能很好的解决这个问题了，可以去获得所有的订单数据。
笛卡尔乘积本质上是将第一个表与第二个表数据相乘，生成了一个行的集合，其中包含了第一个表中的行数和第二个表中的行数相乘的结果。但是由于笛卡尔乘积占用的资源会比较多，而不需要真正的笛卡尔乘积，则可以谨慎的使用cross join。例如：如果对产品和类别执行了cross join，然后使用where字句、distinct或者group by来筛选出大多数行，那么使用inner join会获得同样的结果，而效率会很显著的提高。如果需要为所有的可能性都返回数据，则笛卡尔乘积可能会非常满足这个要求。

⑤、拾遗补零
假设您将按区域对所有销售人员进行分组并将他们的销售额进行小计，但是您只想要那些数据库中标记为处于活动状态的销售人员。您可以按区域对销售人员分组，并使用
HAVING 子句消除那些未处于活动状态的销售人员，也可以在 WHERE 子句中执行此操作。在 WHERE 子句中执行此操作会减少需要分组的行数，所以比在 HAVING 子句中执行此操作效率更高。
HAVING 子句中基于行的条件的筛选会强制查询对那些在 WHERE 子句中会被去除的数据进行分组。
同样使用 DISTINCT 关键字查找数据行的单独报表，可以来代替使用 GROUP BY 子句。在这种情况下，使用 DISTINCT 关键字的 SQL 效率更高。请在需要计算聚合函数（SUM、COUNT、MAX 等）的情况下再使用 GROUP BY。另外，如果您的查询总是自己返回一个唯一的行，则不要使用 DISTINCT 关键字。在这种情况下，DISTINCT 关键字只会增加系统开销。