江 Blog

Jiang in Thinking

SQL反模式

2025-01-23

这是阅读SQL 反模式(SQL Antipatterns: Avoiding the Pitfalls of Database Programming)的一些笔记.

毕竟是2011多年前的书, 有些建议在当前可能并非最佳实践. 但是书中大部分观点, 放在现在指导我们正确使用关系型数据库仍然不过时.

所谓专家, 就是在一个很小的领域里把所有错误都犯过的人

逻辑数据库设计反模式 Logical Database Design Antipatterns

使用分隔符存储多值属性

CREATE TABLE Products (
    product_id   SERIAL PRIMARY KEY,
    product_name VARCHAR(1000),
    account_id   VARCHAR(100)    -- comma-separated list
);

INSERT INTO Products (product_id, product_name, account_id)
VALUES (DEFAULT, 'Visual C++', '12,34');

带来后果:

解决办法:

CREATE TABLE Contacts (
    product_id BIGINT UNSIGNED NOT NULL,
    account_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (product_id, account_id),
    KEY(account_id)
);

INSERT INTO Contacts (product_id, account_id)
VALUES (123, 12), (123, 34), (345, 23), (567, 12), (567, 34);

对一个字符串做索引的开销会比int高得多.

Hierarchical Structure: Always Depend on One’s Parent

A good book that covers hierarchical queries is Joe Celko’s Trees and Hierarchies in SQL for Smarties [Cel04]. Another book that covers trees and even graphs is SQL Design Patterns [Tro06] by Vadim Tropashko.

Primary Key: id or xx_id

Foreign Keys Restrictions

书中观点与目前(2020s)大家的最佳实践不一样, 高并发的互联网应用往往不用外键约束.

Reason not use foreign keys, from skeema doc.

Companies that restrict foreign keys typically do so for these reasons:

实体-属性-值 EAV(Entity-Attribute-Value)

多态关联 Polymorphic Associations

使用公共父表(Common Super-Table), 表之间的继承.

多列属性 Multicolumn Attributes

CREATE TABLE Bugs (
    bug_id SERIAL PRIMARY KEY, description VARCHAR(1000),
    tag1 VARCHAR(20),
    tag2 VARCHAR(20),
    tag3 VARCHAR(20)
);

元数据分裂 Metadata Tribbles

物理数据库设计反模式 Physical Database Design Antipatterns

使用浮点数

改使用decimal , 在MySQL, PostgreSQL中同numeric等价.

枚举值

幽灵文件 Phantom Files

乱用索引 Index Shotgun

查询反模式 Query Antipatterns

NULL使用

模凌两可的分组 Ambiguous Groups

随机选择 Random Selection

Poor Man’s Search Engine

复杂查询 Spaghetti Query

Implicit Columns 隐式列

应用开发反模式 Application Development Antipatterns

明文密码

SQL注入

伪键洁癖 Pseudokey Neat-Freak

非礼勿视 See No Evil

Diplomatic Immunity

Magic Beans

参考书目