我们所有使用关系数据库的人都知道(或正在学习)SQL是不同的。获得期望的结果,并有效地这样做,涉及到一个乏味的过程,其部分特征是学习不熟悉的范例,并发现一些我们最熟悉的编程模式在这里不起作用。常见的反模式是什么?
当前回答
同一查询中的相同子查询。
其他回答
FROM TableA, TableB WHERE语法用于连接而不是FROM TableA内部连接TableB上 假设查询将以某种方式返回,而不放入ORDER BY子句,因为这是在查询工具中测试时显示的方式。
没有使用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'
)
编写查询的开发人员没有很好地了解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.)
Human readable password fields, egad. Self explanatory. Using LIKE against indexed columns, and I'm almost tempted to just say LIKE in general. Recycling SQL-generated PK values. Surprise nobody mentioned the god-table yet. Nothing says "organic" like 100 columns of bit flags, large strings and integers. Then there's the "I miss .ini files" pattern: storing CSVs, pipe delimited strings or other parse required data in large text fields. And for MS SQL server the use of cursors at all. There's a better way to do any given cursor task.
编辑是因为有太多了!
The Altered View - A view that is altered too often and without notice or reason. The change will either be noticed at the most inappropriate time or worse be wrong and never noticed. Maybe your application will break because someone thought of a better name for that column. As a rule views should extend the usefulness of base tables while maintaining a contract with consumers. Fix problems but don't add features or worse change behavior, for that create a new view. To mitigate do not share views with other projects and, use CTEs when platforms allow. If your shop has a DBA you probably can't change views but all your views will be outdated and or useless in that case. The !Paramed - Can a query have more than one purpose? Probably but the next person who reads it won't know until deep meditation. Even if you don't need them right now chances are you will, even if it's "just" to debug. Adding parameters lowers maintenance time and keep things DRY. If you have a where clause you should have parameters. The case for no CASE - 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查询中将NULL替换为0
- 在SQL中修改表的模式名
- 如何在SQL Server 2005的一条语句中更新两个表?
- 如何创建临时表与SELECT * INTO tempTable从CTE查询
- 用于查找计数为>的记录的SQL查询
- “从Table1左连接Table2”和“从Table2右连接Table1”可以互换吗?
- 在SQL Server的选择语句中使用带TOP的变量,而不是动态的
- 自然连接和内部连接的区别
- MySQL现在()+1天
- 在SQL中转换月号到月名函数
- 改变一个varchar列的最大长度?
- 外键约束:何时使用ON UPDATE和ON DELETE
- 暂时关闭约束(MS SQL)
- WHERE子句中的IF子句
- Postgresql列表和排序表的大小