我们所有使用关系数据库的人都知道(或正在学习)SQL是不同的。获得期望的结果,并有效地这样做,涉及到一个乏味的过程,其部分特征是学习不熟悉的范例,并发现一些我们最熟悉的编程模式在这里不起作用。常见的反模式是什么?
当前回答
临时表滥用。
特别是这类事情:
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)
不要从查询中构建临时表,只是为了删除不需要的行。
是的,我在生产db中看到过这种形式的代码页。
其他回答
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())
没有格式标识符
没有注释的存储过程或函数…
同一查询中的相同子查询。
编写查询的开发人员没有很好地了解SQL应用程序(包括单个查询和多用户系统)的快慢。这包括对以下方面的无知:
physical I/O minimization strategies, given that most queries' bottleneck is I/O not CPU perf impact of different kinds of physical storage access (e.g. lots of sequential I/O will be faster than lots of small random I/O, although less so if your physical storage is an SSD!) how to hand-tune a query if the DBMS produces a poor query plan how to diagnose poor database performance, how to "debug" a slow query, and how to read a query plan (or EXPLAIN, depending on your DBMS of choice) locking strategies to optimize throughput and avoid deadlocks in multi-user applications importance of batching and other tricks to handle processing of data sets table and index design to best balance space and performance (e.g. covering indexes, keeping indexes small where possible, reducing data types to minimum size needed, etc.)
没有使用With子句或适当的连接并依赖子查询。
反模式:
select
...
from data
where RECORD.STATE IN (
SELECT STATEID
FROM STATE
WHERE NAME IN
('Published to test',
'Approved for public',
'Published to public',
'Archived'
))
好: 我喜欢使用with子句使我的意图更易于阅读。
with valid_states as (
SELECT STATEID
FROM STATE
WHERE NAME IN
('Published to test',
'Approved for public',
'Published to public',
'Archived'
)
select ... from data, valid_states
where data.state = valid_states.state
最好的:
select
...
from data join states using (state)
where
states.state in ('Published to test',
'Approved for public',
'Published to public',
'Archived'
)
推荐文章
- 如何在Ruby On Rails中使用NuoDB手动执行SQL命令
- 查询JSON类型内的数组元素
- 确定记录是否存在的最快方法
- 获得PostgreSQL数据库中当前连接数的正确查询
- 在SQL选择语句Order By 1的目的是什么?
- 单元测试反模式目录
- 我如何循环通过一组记录在SQL Server?
- 如何从命令行通过mysql运行一个查询?
- 外键约束可能导致循环或多条级联路径?
- 使用LIMIT/OFFSET运行查询,还可以获得总行数
- 当恢复sql时,psql无效命令\N
- 货币应该使用哪种数据类型?
- 如何选择每一行的列值不是独特的
- 如何改变字符集(和排序)在整个数据库?
- mySQL:: insert到表,数据从另一个表?