通过格式良好的SQL提高效率和准确性

格式良好的SQL并不会比乱七八糟的SQL运行效果更好。数据库其实不怎么关心SQL语句中你把逗号放到了字段名的前面还是后面。为了你自己思路清楚，应该做一个有效率的SQL编写者，我建议你遵守以下这些格式规则。在本文中我将分享如何通过格式良好的SQL语句提升生产率。我定义的效率指的是能从SQL 输出准确的结果，并且代码清晰易于理解、修改和调试。我只列出了“SELECT”语句，因为我写的SQL语句99%都是查询语句。格式化SQL代码是非常个性化的事，我也很清楚因人而异，开发者都认为自己的格式化规则是最合理的。

样例问题

下面是一个典型的SQL应用场景，业务报表的数据来自三张表，客户表、销售表和地域表。基于2015年一月份的数据，该报表需要展示在每个行政区内的客户总数和销量总数。该需求通过一个简单的SQL语句就可以实现，需要关联查询三张表。

数据可能出现的问题

虽然SQL很简单，但保证你的结果正确仍然是真正的关键，因为有下面一些原因可能导致错误：

数据可能来自不同的数据源。也就是说你不能保证这几个表之间的完整性。具体举例来说，你不能假定客户表中所有的邮政编码都是有效的邮政编码，并且一定在地域表中存在。
录入客户表数据的应用可能捕获到未经验证的地点数据，可能会包括错误的邮政编码。
邮政编码表可能不是完整的。新发布的邮政编码可能没有在发布后及时导入到表中。

第一原则

对我来说，相比于编写清晰易读的SQL，从SQL得到正确的结果才是第一要务。我要做的第一件事就是编写下面的SQL语句来获取客户总数。在我写完整个语句之后我会再调整它。

我写的第一个语句是这样的：

SELECTCOUNT(DISTINCT cust_id) as count_customersFROMcustomers 

Result: 

count_customers 

“10”

这个查询很重要，因为它紧紧围绕第一原则。因为没有SQL管理查询，也就没有依赖，我知道这就是客户数量的正确结果。我把这个结果记下来，因为我总需要拿这个数字来衡量后面的SQL(是否正确)，在本文后面也会多次提到。

下一步要做的事就是添加必要的字段和表完成查询。我特意把“添加”这个词高亮标注出来，因为根据我的规则，我会在应用第一原则时把能获取相同结果的查询注释掉。下面就是我最终格式化的查询语句。

格式化SQL

下面就是根据我的格式化思路推荐的格式化SQL。

SELECT 

0 

,c.cust_post_code 

,p.location 

,COUNT(DISTINCT c.cust_id) number_customers 

,SUM(s.total_amount) as total_sales 

FROM 

customers c 

JOIN post_codes p ON c.cust_post_code = p.post_code 

JOIN sales s ON c.cust_id = s.cust_id 

WHERE 

1=1 

AND s.sales_date BETWEEN ‘2015-01-01' AND ‘2015-01-31' 

—AND s.order_id = 5 

GROUP BY 

c.cust_post_code 

,p.location

总是使用表别名

时间会证明这么做是有必要的。如果你没有对SQL语句中用到的每个字段使用别名，在将来某个时候可能会给这个查询语句添加进来别的同名字段。到那时候你的查询乃至报表就会产生错误(出现了重名字段名)。

逗号放到字段之前

在调试或者测试我的查询语句时，这么做可以方便地注释掉某个字段，而不需要修改其它行，所有的逗号都没有缺少或多余。不这么做的话你可能总要调整逗号才能保证语句正确。如果你经常要调试语句，这么做会带来极大方便，效率会更高。这个做法对“SELECT”部分和“GROUP BY”子句部分同样适用。

在开发时我使用“SELECT 0”作为语句的开始，迁移到正式环境时它很容易删除掉。这样我们就可以在后面所有字段前面都写都好了。没有这个“0”的话，如果我想注释掉第一个字段(本例中是“c.cust_post_code”)，我就必须处理后面的逗号问题。我必须临时注释掉它，将来还要加回来。在“GROUP BY”语句中也是一样的。这个“0”是额外加的。

把“JOIN”放到独立行

把“JOIN”语句放到独立行有以下好处：

这么做很容易看到本查询语句涉及的所有表，只需要看滚动“JOIN”语句就可以了。

使用“JOIN”相比于在“WHERE”子句中列出所有表和表达式关系，可以把所有逻辑关系都放到一个地方。我们不可能总是吧“JOIN”语句放到一行中，但是至少应该放到一起。

这么做的话要注释掉“JOIN”语句也是相对容易的。这在调试时非常有用，你可能需要知道是否是“JOIN”引起了数据问题。

列模式编辑

在处理大量字段的情况时，列模式编辑非常方便。下面是我曾经做过的第一个动态GIF展示，你可以注释掉所有非聚集字段。我使用了列模式编辑，而不仅仅是注释掉字段：

创建全部索引

在使用字段较多的UNION语句时：

注释掉“GROUP BY”子句的字段清单

测试查询结果

我必须使用外连接“OUTER”列出所有客户，因为不是所有客户的邮政编码都在地域表里有相应的邮政编码。我可以通过包含和排除不同字段和表反复操作来确保我查询的结果与最开始那个查询(单独查询客户的那个语句)结果相同，这其实是对第一原则的遵守。

SELECT0,c.cust_post_code—,p.location,COUNT(DISTINCT c.cust_id) number_customers,SUM(s.total_amount) as total_salesFROMcustomers c—LEFT OUTER JOIN post_codes p ON c.cust_post_code = p.post_codeJOIN sales s ON c.cust_id = s.cust_idWHERE1=1AND s.sales_date BETWEEN ‘2015-01-01' AND ‘2015-01-31'—AND c.cust_post_code = 2000—AND p.post_code = 200GROUP BYc.cust_post_code—,p.location

像这样的SQL对我来说意味着我必须写独立的测试来检查数据。通过注释掉的那几行语句我可以使用第一原则验证我查询数据的准确性。这么做提高了我的效率和报表。

以上就是本文的全部内容，希望对大家的学习有所帮助。

赞 (0)

相关推荐

  • C++操作MySQL大量数据插入效率低下的解决方法

    通常来说C++操作MySQL的时候,往Mysql中插入10000条简单数据,速度非常缓慢,居然要5分钟左右, 而打开事务的话,一秒不到就搞定了! 具体实现代码如下: #include <iostream> #include <winsock2.h> #include <string> #include "mysql.h" #pragma comment(lib, "libmysql.lib"); using namespace s

  • C#适用于like语句的SQL格式化函数

    本文实例讲述了C#适用于like语句的SQL格式化函数,分享给大家供大家参考. 具体实现代码如下: 复制代码 代码如下: /// <summary> /// 对字符串进行sql格式化,并且符合like查询的格式. /// </summary> /// <param name="str">要转换的字符串</param> /// <returns>格式化后的字符串</returns> public static str

  • Oracle提高SQL执行效率的3种方法

    Oracle提供了多种方法用于减少花在剖析Oracle SQL表达式上的时间,在执行带有大量执行计划的复杂查询时剖析过程会拖累系统的性能.现在我们来简要地看看这些方法中的几种. 1.使用ordered提示 Oracle必须花费大量的时间来剖析多表格的合并,用以确定表格合并的最佳顺序.如果SQL表达式涉及七个乃至更多的表格合并,那么有时就会需要超过30分钟的时间来剖析,因为Oracle必须评估表格合并所有可能的顺序.八个表格就会有40,000多种顺序.Ordered这个提示(hint)和其他的提示

  • 提高MySQL中InnoDB表BLOB列的存储效率的教程

    提高MySQL中InnoDB表BLOB列的存储效率的教程

    首先,介绍下关于InnoDB引擎存储格式的几个要点: 1.InnoDB可以选择使用共享表空间或者是独立表空间方式,建议使用独立表空间,便于管理.维护.启用 innodb_file_per_table 选项,5.5以后可以在线动态修改生效,并且执行 ALTER TABLE xx ENGINE = InnoDB 将现有表转成独立表空间,早于5.5的版本,修改完这个选项后,需要重启才能生效: 2.InnoDB的data page默认16KB,5.6版本以后,新增选项 innodb_page_size

  • SQL查询效率注意事项小结

    一.查询条件精确,针对有参数传入情况 二.SQL逻辑执行顺序 FROM-->JOIN-->WHERE-->GROUP-->HAVING-->DISTINCT-->ORDER-->TOP 三.横向 查询需要的字段 当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个Column上这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误 四.少做重复工作 控制同一语句的多次执行,特别是一些基础数据的多次执行是很多程序员很少注意的 减少多次的数

  • sql格式化工具集合

    第一款在线格式化工具: Instant SQL Formatter 功能强劲,可以设置 第二款,和第一款类似,功能也一样,只不过是不同的UI而已,SQLPP 第三款是针对MySQL的,是作者从phpMyAdmin中抠出来的,php-SQL-Format ,这个在我本地出了点小问题,复杂SQL格式化不了,我和作者沟通,他说有可能是SQLParser的问题,没有深究,我又观察了下,我觉得是过程中传递参数的问题,复杂的SQL语句,返回的是pma中php代码格式. if ($ != jQuery) {

  • SQL Server中的SQL语句优化与效率问题

    很多人不知道SQL语句在SQL SERVER中是如何执行的,他们担心自己所写的SQL语句会被SQL SERVER误解.比如: select * from table1 where name='zhangsan' and tID > 10000 和执行: select * from table1 where tID > 10000 and name='zhangsan' 一些人不知道以上两条语句的执行效率是否一样,因为如果简单的从语句先后上看,这两个语句的确是不一样,如果tID是一个聚合索引,那

  • mysql格式化小数保留小数点后两位(小数点格式化)

    复制代码 代码如下: SELECT FORMAT(12562.6655,2); 结果:12,562.67 查看文档:Formats the number X to a format like '#,###,###.##', rounded to D decimal places, and returns the result as a string. If D is 0, the result has no decimal point or fractional part.整数部分超过三位的时候

  • 通过格式良好的SQL提高效率和准确性

    通过格式良好的SQL提高效率和准确性

    格式良好的SQL并不会比乱七八糟的SQL运行效果更好.数据库其实不怎么关心SQL语句中你把逗号放到了字段名的前面还是后面.为了你自己思路清楚,应该做一个有效率的SQL编写者,我建议你遵守以下这些格式规则.在本文中我将分享如何通过格式良好的SQL语句提升生产率.我定义的效率指的是能从SQL 输出准确的结果,并且代码清晰易于理解.修改和调试.我只列出了"SELECT"语句,因为我写的SQL语句99%都是查询语句.格式化SQL代码是非常个性化的事,我也很清楚因人而异,开发者都认为自己的格式化

  • 正则用(?&gt;…)实现固化分组提高效率

    具体来说,使用「(?>-)」的匹配与正常的匹配并无差别,但是如果匹配进行到此结构之后(也就是,进行到闭括号之后),那么此结构体中的所有备用状态都会被放弃(不能被回溯). 也就是说,在固化分组匹配结束时,它已经匹配的文本已经固化为一个单元,只能作为整体而保留或放弃.括号内的子表达式中未尝试过的备用状态都不复存在了,所以回溯永远也不能选择其中的状态(至少是,当此结构匹配完成时,"锁定(locked in)"在其中的状态). 例子: 比如要处理一批数据,原来格式为123.456,后来因

  • MySQL分页分析原理及提高效率

    MySQL分页分析原理及提高效率 PERCONA PERFORMANCE CONFERENCE 2009上,来自雅虎的几位工程师带来了一篇"Efficient Pagination Using MySQL"的报告,有很多亮点,本文是在原文基础上的进一步延伸. 首先看一下分页的基本原理: MySQL> explain SELECT * FROM message ORDER BY id DESC LIMIT 10000, 20\G ***************** 1. row *

  • MySQL中Stmt 预处理提高效率问题的小研究

    MySQL中Stmt 预处理提高效率问题的小研究

    复制代码 代码如下: DELIMITER $$ set @stmt = 'select userid,username from myuser where userid between ? and ?'; prepare s1 from @stmt; set @s1 = 2; set @s2 = 100; execute s1 using @s1,@s2; deallocate prepare s1; $$ DELIMITER ; 用这种形式写的查询,可以随意替换参数,给出代码的人称之为预处理,

  • 关于Oracle多表连接,提高效率,性能优化操作

    执行路径:ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用:我们发现,单表数据的统计比多表统计的速度完全是两个概念.单表统计可能只要0.02秒,但是2张表联合统计就可能要几十表了. 这是因为ORACLE只对简单的表提供高速缓冲(cache buffering) ,这个功能并不适用于多表连接查询..数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了. 当你向ORACLE提交一个SQL语句,ORAC

  • Oracle批量查询、删除、更新使用BULK COLLECT提高效率

    BULK COLLECT(成批聚合类型)和数组集合type类型is table of 表%rowtype index by binary_integer用法笔记. 例1: 批量查询项目资金账户号为 "320001054663"的房屋账户信息并把它们打印出来 . DECLARE TYPE acct_table_type IS TABLE OF my_acct%ROWTYPE INDEX BY BINARY_INTEGER; v_acct_table acct_table_type; BE

  • php 查找数组元素提高效率的方法详解

    1.php in_array方法说明 PHP查找数组元素是否存在,一般会使用in_array方法. bool in_array ( mixed $needle , array $haystack [, bool $strict = FALSE ] ) 参数说明: needle 待搜索的值,如果needle是字符串,比较是区分大小写的. haystack 用来比较的数组 strict 如果第三个参数 strict 的值为 TRUE 则 in_array() 函数还会检查 needle 的类型是否和

  • MySql批量插入优化Sql执行效率实例详解

    MySql批量插入优化Sql执行效率实例详解 itemcontractprice数量1万左右,每条itemcontractprice 插入5条日志. updateInsertSql.AppendFormat("UPDATE itemcontractprice AS p INNER JOIN foreigncurrency AS f ON p.ForeignCurrencyId = f.ContractPriceId SET p.RemainPrice = f.RemainPrice * {0},

  • javascript 包裹节点 提高效率

    原理基本是这样,如果传入的是字符串,那么让它们变成一个元素节点,不过这元素节点也可以有许多层,在最内层把要包裹的元素放进.把字符串变成元素节点的方法有如下几个.1,createElement,IE可以连元素属性也一起创建,但只能创建一层.2,innerHTML,不过需要对原来的字符串进行处理,IE与FF都有许多意想不到的默认行为,可以为你多加一点东西或少加一点东西.3,createContextualFragment,由于Opera的行为有点怪异,需要选中修改元素的位置.经日本人的测试,它转换字

  • Oracle 计算时间格式平均值的sql 语句

    Oracle 计算时间格式平均值的sql 语句 select to_char((to_date('2019-07-01', 'yyyy-mm-dd') + numtodsinterval(avg(begin_time_second), 'second')),'hh24:mi:ss') avg_begin_time, to_char((to_date('2019-07-01', 'yyyy-mm-dd') + numtodsinterval(avg(end_time_second), 'secon

随机推荐