我们所有使用关系数据库的人都已经学习(或正在学习)SQL是不同的.引出期望的结果,并且有效地进行,涉及一个繁琐的过程,其部分特征是学习不熟悉的范例,并发现我们最熟悉的一些编程模式在这里不起作用.您见过(或自己承诺)的常见反模式有哪些？
我一直对大多数程序员在数据访问层中混合UI逻辑的倾向感到失望:
SELECT
FirstName + ' ' + LastName as "Full Name",
case UserRole
when 2 then "Admin"
when 1 then "Moderator"
else "User"
end as "User's Role",
case SignedIn
when 0 then "Logged in"
else "Logged out"
end as "User signed in?",
Convert(varchar(100), LastSignOn, 101) as "Last Sign On",
DateDiff('d', LastSignOn, getDate()) as "Days since last sign on",
AddrLine1 + ' ' + AddrLine2 + ' ' + AddrLine3 + ' ' +
City + ', ' + State + ' ' + Zip as "Address",
'XXX-XX-' + Substring(
Convert(varchar(9), SSN), 6, 4) as "Social Security #"
FROM Users
通常,程序员这样做是因为他们打算将他们的数据集直接绑定到网格,并且它只是方便SQL Server格式服务器端而不是客户端上的格式.
如上所示的查询非常脆弱,因为它们将数据层紧密耦合到UI层.最重要的是,这种编程风格彻底防止了存储过程的重用.
这是我的前三名.
编号1.未指定字段列表.(编辑:防止混淆:这是一个生产代码规则.它不适用于一次性分析脚本 - 除非我是作者.)
SELECT * Insert Into blah SELECT *
应该
SELECT fieldlist Insert Into blah (fieldlist) SELECT fieldlist
数字2.使用游标和while循环,当一个带循环变量的while循环可以.
DECLARE @LoopVar int
SET @LoopVar = (SELECT MIN(TheKey) FROM TheTable)
WHILE @LoopVar is not null
BEGIN
-- Do Stuff with current value of @LoopVar
...
--Ok, done, now get the next value
SET @LoopVar = (SELECT MIN(TheKey) FROM TheTable
WHERE @LoopVar < TheKey)
END
Number 3. DateLogic到字符串类型.
--Trim the time Convert(Convert(theDate, varchar(10), 121), datetime)
应该
--Trim the time DateAdd(dd, DateDiff(dd, 0, theDate), 0)
我看到最近的一次飙升"一个问题比两个更好,amiright？"
SELECT * FROM blah WHERE (blah.Name = @name OR @name is null) AND (blah.Purpose = @Purpose OR @Purpose is null)
此查询需要两个或三个不同的执行计划,具体取决于参数的值.只生成一个执行计划并将其粘贴到此sql文本的缓存中.无论参数的值如何,都将使用该计划.这导致间歇性的不良性能.编写两个查询(每个预期的执行计划一个查询)要好得多.
人类可读的密码字段,egad.自我解释.
对索引 列使用LIKE,我几乎总是想说LIKE.
回收SQL生成的PK值.
惊喜没人提到神表.没有什么比100列的位标志,大字符串和整数更像"有机".
然后是"我想念.ini文件"模式:在大文本字段中存储CSV,管道分隔的字符串或其他解析所需的数据.
而对于如何使用游标的MS SQL服务器在所有.有一种更好的方法来执行任何给定的游标任务.
编辑,因为有这么多!
不必深入挖掘它:不使用预备语句.
使用无意义的表别名:
from employee t1, department t2, job t3, ...
使读取大型SQL语句变得比它需要的更难
var query = "select COUNT(*) from Users where UserName = '"
+ tbUser.Text
+ "' and Password = '"
+ tbPassword.Text +"'";
盲目地信任用户输入
不使用参数化查询
明文密码
我的bugbears是由管理总监最好的朋友狗美容师的8岁儿子和仅仅存在的狡猾的查找表组成的450列访问表,因为有人不知道如何正确地规范化数据结构.
通常,此查找表如下所示:
ID INT, Name NVARCHAR(132), IntValue1 INT, IntValue2 INT, CharValue1 NVARCHAR(255), CharValue2 NVARCHAR(255), Date1 DATETIME, Date2 DATETIME
我已经失去了我见过的客户数量,他们的系统依赖于这样的可憎之处.
那些我最不喜欢的是
在创建表,sprocs等时使用空格.我可以使用CamelCase或under_scores和单数或复数以及大写或小写,但必须引用一个表或列[带空格],特别是如果[奇怪的间隔](是的,我遇到过这个真的很烦我.
非规范化数据.表格不一定要完全正常化,但是当我遇到一个员工表,其中包含有关当前评估分数或主要内容的信息时,它告诉我,我可能需要在某个时刻制作一个单独的表格,然后尝试让他们同步.我将首先规范化数据然后如果我看到非规范化有帮助的地方,我会考虑它.
过度使用视图或游标.视图有一个目的,但是当每个表被包装在一个视图中时它太多了.我不得不使用游标几次,但通常你可以使用其他机制.
访问.程序可以反模式吗？我的工作中有SQL Server,但很多人都使用访问权限,因为它对非技术用户来说具有可用性,"易用性"和"友好性".这里有太多的东西要进去,但如果你曾经在类似的环境中,你知道.
使用SP作为存储过程名称的前缀,因为它将首先搜索系统过程位置而不是自定义位置.
过度使用临时表和游标.
要存储时间值,只应使用UTC时区.不应使用当地时间.
使用@@ IDENTITY而不是SCOPE_IDENTITY()
引用这个答案:
@@ IDENTITY返回在所有范围内为当前会话中的任何表生成的最后一个标识值.你需要在这里小心,因为它跨越范围.您可以从触发器获取值,而不是当前语句.
SCOPE_IDENTITY返回为当前会话中的任何表和当前范围生成的最后一个标识值.一般你想要使用什么.
IDENT_CURRENT返回在任何会话和任何范围内为特定表生成的最后一个标识值.这使您可以指定您想要该值的表,以防上述两个不是您需要的(非常罕见).如果要获取尚未插入记录的表的当前IDENTITY值,可以使用此方法.
重新使用"死"字段来表示它不适用的东西(例如,将用户数据存储在"传真"字段中) - 尽管很快就可以快速修复!
select some_column, ... from some_table group by some_column
并假设结果将按some_column排序.我已经看到了Sybase的一些假设(暂时).
SELECT FirstName + ' ' + LastName as "Full Name", case UserRole when 2 then "Admin" when 1 then "Moderator" else "User" end as "User's Role", case SignedIn when 0 then "Logged in" else "Logged out" end as "User signed in?", Convert(varchar(100), LastSignOn, 101) as "Last Sign On", DateDiff('d', LastSignOn, getDate()) as "Days since last sign on", AddrLine1 + ' ' + AddrLine2 + ' ' + AddrLine3 + ' ' + City + ', ' + State + ' ' + Zip as "Address", 'XXX-XX-' + Substring(Convert(varchar(9), SSN), 6, 4) as "Social Security #" FROM Users
或者,将所有内容塞进一行.
FROM TableA, TableB WHEREJOINS 的语法而不是FROM TableA INNER JOIN TableB ON
假设在不放置ORDER BY子句的情况下以某种方式返回查询,只是因为这是在查询工具中测试期间出现的方式.
在他们职业生涯的前六个月学习SQL,在接下来的10年里从不学习任何其他知识.特别是不学习或有效使用窗口/分析SQL功能.特别是使用over()和partition by.
窗口函数(如聚合函数)对定义的一组(一组)行执行聚合,但不是每组返回一个值,窗口函数可以为每个组返回多个值.
有关窗口函数的详细概述,请参阅O'Reilly SQL Cookbook附录A.
我需要在这里放置我自己最喜欢的,只是为了使列表完整.我最喜欢的反模式不是测试您的查询.
这适用于:
您的查询涉及多个表.
您认为您的查询具有最佳设计,但不必费心测试您的假设.
您接受第一个有效的查询,不知道它是否接近优化.
任何针对非典型或不充分数据的测试都不计算在内.如果它是存储过程,请将test语句放入注释中并将其保存,并显示结果.否则,将其放入带有结果的代码中的注释中.
临时表滥用.
特别是这种事情:
SELECT personid, firstname, lastname, age INTO #tmpPeople FROM People WHERE lastname like 's%' DELETE FROM #tmpPeople WHERE firstname = 'John' DELETE FROM #tmpPeople WHERE firstname = 'Jon' DELETE FROM #tmpPeople WHERE age > 35 UPDATE People SET firstname = 'Fred' WHERE personid IN (SELECT personid from #tmpPeople)
不要从查询构建临时表,只删除不需要的行.
是的,我在生产数据库中看到了这种形式的代码页.
反向观点:过度迷恋正常化.
大多数SQL/RBDB系统都提供了许多非常有用的功能(事务,复制),即使对于非标准化数据也是如此.磁盘空间很便宜,有时它可以比编写1NF模式更简单(代码更简单,开发时间更快)来处理/过滤/搜索获取的数据,并处理其中的所有麻烦(复杂的连接,讨厌的子选择)等).
我发现过度规范化的系统往往是过早优化,特别是在早期开发阶段.
(更多关于它的想法...... http://writeonly.wordpress.com/2008/12/05/simple-object-db-using-json-and-python-sqlite/)
我只是根据SO上的一些SQL响应将这个放在一起.
认为触发器是数据库,因为事件处理程序是OOP,这是一个严肃的反模式.有这种看法,任何旧的逻辑都可以被放入触发器中,当事务(事件)发生在表上时被触发.
不对.其中一个最大的区别是触发器是同步的 - 具有复仇性,因为它们在设置操作上是同步的,而不是在行操作上.在OOP方面,恰恰相反 - 事件是实现异步事务的有效方式.
没有任何评论的存储过程或函数...
1)我不知道它是一个"官方"反模式,但我不喜欢并试图避免字符串文字作为数据库列中的魔术值.
MediaWiki的表'image'的一个例子:
img_media_type ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO",
"MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE") default NULL,
img_major_mime ENUM("unknown", "application", "audio", "image", "text",
"video", "message", "model", "multipart") NOT NULL default "unknown",
(我只是注意到不同的外壳,另一件要避免的事情)
我设计了这样的情况,例如使用int主键对表ImageMediaType和ImageMajorMime进行int查找.
2)依赖于特定NLS设置的日期/字符串转换
CONVERT(NVARCHAR, GETDATE())
没有格式标识符
查询中的相同子查询.
改变视图 - 一种经常改变的视图,没有通知或理由.这种变化要么在最不合适的时候被注意到,要么更糟糕,并且从未被注意到.也许你的应用程序会破坏,因为有人想到该列的更好名称.通常,视图应该在保持与消费者的合同的同时扩展基表的有用性.修复问题但不添加功能或更糟糕的更改行为,因为这会创建新视图.要减轻不与其他项目共享视图,并在平台允许时使用CTE.如果您的商店有DBA,您可能无法更改视图,但在这种情况下,您的所有视图都将过时或无用.
!Paramed - 查询可以有多个目的吗？可能但是下一个阅读它的人在深度冥想之前不会知道.即使你现在不需要它们,你也有机会,即使它只是"调试".添加参数可以缩短维护时间并保持干燥.如果你有一个where子句,你应该有参数.
没有案例的情况 -
SELECT
CASE @problem
WHEN 'Need to replace column A with this medium to large collection of strings hanging out in my code.'
THEN 'Create a table for lookup and add to your from clause.'
WHEN 'Scrubbing values in the result set based on some business rules.'
THEN 'Fix the data in the database'
WHEN 'Formating dates or numbers.'
THEN 'Apply formating in the presentation layer.'
WHEN 'Createing a cross tab'
THEN 'Good, but in reporting you should probably be using cross tab, matrix or pivot templates'
ELSE 'You probably found another case for no CASE but now I have to edit my code instead of enriching the data...' END
我发现最多的两个，并且在性能方面可能会产生可观的成本：
使用游标而不是基于集合的表达式。我猜想这在程序员有步骤地思考时经常发生。
当关联到派生表时，可以使用相关子查询来完成这项工作。
将内容放在临时表中,特别是从SQL Server切换到Oracle的人有过度使用临时表的习惯.只需使用嵌套的select语句.
编写查询但不了解SQL应用程序(单个查询和多用户系统)的快速或慢速的开发人员.这包括对以下方面的无知:
物理I/O最小化策略,因为大多数查询的瓶颈是I/O而不是CPU
不同类型的物理存储访问的性能影响(例如,大量的顺序I/O将比许多小型随机I/O更快,但如果您的物理存储是SSD,则会更少!)
如果DBMS产生错误的查询计划,如何手动调整查询
如何诊断糟糕的数据库性能,如何"调试"慢查询,以及如何读取查询计划(或EXPLAIN,取决于您选择的DBMS)
锁定策略以优化吞吐量并避免多用户应用程序中的死锁
批处理和其他技巧处理数据集的重要性
表和索引设计,以最好地平衡空间和性能(例如,覆盖索引,尽可能保持索引较小,将数据类型减少到所需的最小尺寸等)
京公网安备 11010802040832号 | 京ICP备19059560号-6 