读《编写高质量SQL语句的61个有效方法》有感

1、数据库设计

确保所有表都有主键

避免存储冗余数据

• 数据库规范化的目标是消除冗余数据，并在处理数据时最小化资源消耗
• 通过消除冗余数据，避免插入、更新和删除时出现异常
• 通过消除冗余数据，尽量减少数据的不一致性

消除重复数据组

每列只存储一个属性

当表包含多列时，搜索和分组即使可能做到，也会极其困难

理解为什么存储计算列通常有害无益

• 许多数据库系统允许在创建表时定义计算列，但应该注意性能影响，特别是在使用非确定性表达式和函数的时候
• 可以像定义普通列一样定义计算列，然后使用触发器来维护，但编写出触发器代码可能会很复杂，且影响性能
• 计算列会对数据库系统产生额外的开销，只有利大于弊的时候才考虑使用它
• 希望在计算列上创建一个索引，以小号更多的存储空间和较慢的更新作为交换，获得一些便利性
• 当不能使用索引时，使用视图来做计算通常可以替代表里创建计算列

定义外键以确保引用完整性

• 外键明显有助于保证相关表之间的数据完整性
• 表中存在违反约束的数据，向表中添加约束将会失败
• 部分数据库创建外键会自动创建索引，提高连接查询性能

确保表间关系的合理性

当第三范式不够用时，采用更多范式

2、可编程性与索引设计

创建索引时空值的影响

• 如果要搜索空值，但列中的大多数值都可能为NULL，最好不要对列进行索引
• 每种数据库处理索引中的NULL方法都不同，在可能包含NULL的列上创建索引时要考虑数据库系统

创建索引时谨慎以最小化索引和数据扫描

• 创建正确的索引以提高性能
• 确保创建的索引都被使用

索引不只是过滤

• WHERE子句中的列是否包含在索引中会影响查询性能
• SELECT子句中的列是否被索引也会影响查询效率
• 连接查询的列是否被索引可能会影响查询效率
• 索引也可能对ORDER BY子句的效率产品影响
• 多个索引的存在会对写入操作产生性能影响

不要过度使用触发器

• 触发器可以适用场景
  维护重复或派生数据
  复杂列的约束
  复杂的默认值：可以使用触发器根据其他列、记录或表中的数据生成默认值
  数据库间引用完整性
• 触发器不可移植
• 仅在绝对必要时才使用触发器，如果可能，确保触发器是幂等

使用过滤索引包含或排除数据子集

使用声明式约束替代编码校验

• NOT NULL 确保不为空
• UNION 确保指定字段的值唯一
• PRIMARY KEY 唯一标识
• FOREIGH KEY 外键
• CHECK
  单个字段：只能在该字段中存储指定的值
  表：可以对某些字段中的值基于同一行中的其他字段中的值进行限制
• DEFAULT 定义默认值

了解数据库使用的SQL方言并编写相应的代码

不同的数据库，对空值的排序、限制结果集、boolean数据类型、SQL函数、UNIQUE约束都可能不同

了解何时在索引中使用计算结果

3、当你不能改变设计时

使用视图来简化不能更改的内容

• 提炼特定的数据
• 简化或清晰列的名称
• 将数据从不同的表汇总起来
• 简化数据操作
• 保护敏感数据
• 提供向后兼容
• 自定义数据
• 提供数据汇总
• 注意：尽量不要在视图上创建视图

使用ETL将非关系数据转换为有用信息

创建汇总表并维护

使用UNION语句将非规范化数据列转行

• UNION查询的SELECT必须具有相同的列
• 每列的数据类型必须兼容
• UNION会移除重复行

4、过滤与查找数据

了解关系代数及其如何在SQL中实现

• 选择（限制）——在FROM子句中定义所需的数据及源头，然后使用WHERE或HAVING子句过滤返回的记录
• 投影——使用SELECT子句（包括聚合函数）和GROUP BY子句来定义数据库系统返回哪些列
• 连接——在FROM子句中使用JOIN关键字来执行连接。可以指定INNER JOIN 或OUTER JOIN来扩展此功能
• 交集——必须具有相同列的两个集合上执行相交操作:INTERSECT
• 笛卡儿积——将第一个集合中所有行与第二个集合中的所有行组合的结果：CROSS JOIN
• 并集——合并两个具有相同列的集合，添加UNION关键字。可以指定UNION ALL不删除重复记录
• 除
• 差集——减去另一个数据及，关键字为EXCEPT

查找不匹配或缺失的记录

• 使用NOT EXISTS 操作符通常比NOT IN 运算符快
• 使用DBMS查询分析器来确定哪种方式最适合

了解何时使用CASE解决问题

• 需要解决IF…THEN…ELSE这类问题时
• 可以使用简单的CASE来执行相等判断和基于搜索的CASE以使用复杂的条件
• 可以使用值表达式的地方都可以使用CASE，包括作为SELECT子句中的列定义或作为WHERE或HAVING子句中条件的一部分

了解解决多条件查询的技术

如需完美匹配，先对数据进行除操作

如何按时间范围正确地过滤日期和时间的列

• 不要依赖隐式日期转换；使用显式转换函数来处理日期字符
• 不要将函数应用于日期和时间列，否则查询将不能使用索引
• 舍入误差可能导致日期和时间值不正确；使用>=和<替代BETWEEN

书写可参数化搜索的查询以确保引擎使用索引

• 可参数化运算符：=、>、<、>=、<=、BETWEEN、LIKE(不包括前导通配符)、IS (NOT) NULL
• 可能是参数化，但性能提升可能没帮助：<>、IN、OR、NOT IN、 NOT EXISTS、NOT LIKE
• 避免使用不可参数化搜索的操作符
• 不要在WHERE子句条件中对一个或者多个字段使用函数（函数必须检查每一条记录，除非索引本身包含相同的函数）
• 不要对WHERE子句中的字段执行算术计算
• 使用LIKE 操作符时，只能在字符串末尾使用通配符LIKE ‘some%’，而不是 ‘%some%’、 LIKE ‘%some’

正确地定义左连接的右侧

• 在SQL中使用OUTER JOIN执行差集操作
• 当你对外部WHERE子句中的左连接加入右侧数据时使用过滤，将无法获得所需结果，反之亦然
• 要正确地过滤数据子集，必须在数据库执行外连接之前使用过滤

5、聚合

理解GROUP BY 的工作原理

• COUNT() 总数
• SUM() 和
• AVG() 平均值
• MIN() 最小的值
• MAX() 最大的值
• HAVING 过滤取决于聚合的结果

简化GROUP BY 子句

• GROUP BY 过多的列对查询性能有影响
• 对于同时需要聚合和详细的查询，先查询聚合再连接查询明细

利用GROUP BY或HAVING解决复杂的问题

分组前使用WHERE过滤，分组后使用HAVING过滤

避免使用GROUP BY来查找最大值或最小值

使用OUTER JOIN时避免获取错误的COUNT()

测试HAVING COUNT(x)<某数时包含零值记录

使用DISTINCT获取不重复的计数

知道如何使用窗口函数

创建行号与排名

• ROW_NUMBER()：row_number() over(partition by 分组列 order by 排序列 desc)
• RANK()：rank() over(partition by 分组列 order by 排序列 desc)
• 如果不想排名上有差距，可以使用DENSE_RANK()

创建可移动聚合函数

6、子查询

了解在何处使用子查询

• 可以在表、视图或返回表的函数及过程的位置使用子查询

select e.* from (select Id,Name from Employee) e
where Id>2

• 为IN或NOT IN条件提供一个列表的位置，使用返回单列的子查询

select * from Employee
where ManagerId in (select Id from Manager where IsDelete=0)

• 在任何使用列名的位置使用标量子查询，如在SELECT 语句中

select Id,Name,ManagerId,
(select Name from Manager where Id= e.ManagerId) as ManegerName from Employee e

了解关联和非关联查询的差异

尽可能使用公共表表达式而不是子查询

• CET(公共表达式)：WITH table AS ()

with T1 as(
select * from Employee
where Id>2
)
selec * from T1

• CET可以简化多次使用相同子查询的复杂查询
• CET允许直接嵌入到另一个查询中的子查询
• 可以使用递归CET遍历层次关系，并以有意义的方式进行展示

--生成一个1~100的数字列表
with SeqNumT as(
	select 1 as Num
	union all
	select Num+1
	from SeqNumT
	where Num<100
)
select Num from SeqNumT

使用连接而非子查询创建更高效的查询

• 不要认为按顺序解决问题是首选方法。SQL语句最适合按集合，而不是按行运行
• 了解DBMS优化器多种处理方式的特征，从而决定首选的解决方案
• 确保为任何连接都建立了适当的索引

7、获取与分析元数据

了解如何使用系统的查询分析器

• 了解如何获取DBMS的执行计划
• 了解如何读取其生成的执行计划

学习获取数据库的元数据

理解执行计划的工作原理

• 分析是否存在未使用的索引，定位未使用的原因
• 分析所使用的操作是否适合正在使用的查询
• 不要因为一个查询就加上索引来改善执行计划
• 数据分布不均的数据对统一查询会需要不同的优化。当执行计划被缓存和重用时（通常是存储过程或客户端处理过的语句），这个问题就特别严重

8、笛卡尔积

生成两张表所有航的组合并标示一张表中间接关联的领一张表的列

• 使用笛卡尔积产生升两个表之间的各种组合

理解如何以等分量排名

知道如何对表中的行进行配对

理解如何列出类别与前三偏好

9、计数表

根据计数表内定义的参数生成空行

使用计数表和窗口函数生成序列

根据计数表内定义的范围生成行

根据计数表定义的值范围转换某个表中的值

使用计数表行转列

10、层次数据建模

从邻接列表模型开始

• 邻表连接只是在表中添加一个自引用表的主键的一个外键
• 始终使用邻接列表模型构建一致的层次模型
  

对不常更新的数据使用嵌套集以提升查询性能

使用存储路径简化设置与搜索

• 存储路径的好处是容易理解和处理，如：1/3/8
• 必须对层次结构加上限制避免产生问题
• 存储路径的搜索仅在一个方向上有效，因为在开始或谓词中有通配符不能创建索引

使用祖先遍历闭包做复杂搜索

示例SQL源码
以上便是读《编写高质量SQL语句的61个有效方法》有感，很多自己不熟悉的也不深入的，如祖先遍历闭包等，便没有多写，有兴趣的可以自己买书看看或者去了解相关的。