《sql 语言艺术》 概要

http://www.oreilly.com/catalog/artofsql （原书）
http://www.oreilly.com.cn/book.php?bn=978-7-121-05834-9 (中文版)

  数据库技术是企业信息化开发的基础。在制造业信息化领域从业多年，自己对这块知识却一直零碎杂乱，应该理顺思路，为架构设计的目标建立基础。

       此书以明确应用原则，讨论各种SQL应用场景，利于实践。

第一章 制定计划：为性能而设计

程序首要目标：满足业务需求。设计SQL支持关系模型（表内不同字段间存在”关系“）。

关系就是表，不同字段联系在一起定义关系。

关系理论的关键原理：关系不包含重复数据，且记录之间没有顺序。

总结：建模是业务需求具体化的过程。

规范化的重要性--使混沌变得有序。

满足第三范式3NF。

真正的风险是数据的不一致性，难于编写的数据输入控制代码、性能、BUG以及模型无法演进，

规范化会增加上述风险几率。

松散、不同类型的信息组织，便于运用的数据模型，步骤：

第一步：确保原子性（属性）。无法再分割，仅把原子性属性定义为表中的字段。

如果where子句中必须引用属性的一部分，说明不属性未达到原子性级别，丧失两个优点：

1高效搜索能力，因常规索引以具有原子性的值作为键（全文索引不能实时更新）。

2由数据库保证的数据正确性，使用函数检查 将 复杂难维护。

还必须精通字符串函数，要避免多值塞进同一个字符串中。

原则上如果组织数据，实际上取决于如果使用数据。必须考虑业务需求，因为它决定原子级的地址属性，根据业务需求分析适用性。

下一步是确定唯一标识各记录的主键（复合键常见）。尽量使用实际意义的主键，而不是序列数（键的技术代用品）。

一旦所有属性都具有原子性、具确定了键，数据就符合1NF。

第二步：检查对键的完全依赖性

属性不是键确定行所特有的，属性部分依赖于键（复合键）。

影响：

数据冗余，公共属性会被重复保存，两个问题：

一。冗余数据容易引起数据不一致，修改更费时。

二。浪费存储空间。

查询性能，全表扫描

为消除键的部分依赖性，必须建立新表，新表的键都是原始表的键的一部分。

去除只依赖部分键的属性后，表就符合2NF。

第三步：检查属性独立性

除主键所包含的属性外，不能根据任何其他属性确还一个属性的值。此数据集满足3NF。

必须对2NF的数据集中的每一对属性检查，确定3NF。

不符合3NF的风险和2NF相同。

3NF重要原因：

合理规范化的模型可应对需求变更。3NF模型有足够的灵活性以适应变化。

规范化使数据重复降至最少，重复数据时空浪费，引出数据不一致。

在程序中保证数据一致性相当于将数据完整性的保护从数据库移到应用层。

总结：规定化重要基础，模型必须具原子性。

 

有值、无值、空值

数据库设计缺陷征兆：表字段大多为空值，特别是两字段不可同时有值。这违背2NF或3NF。

存在空值意味存在问题，动摇查询优化的基础。

where条件必须明确，任何中间状态或空值是不确定的。

关系模型的完备性 是以二值逻辑为基础。包含空值的模型属于三值逻辑（真、假、不确定）。

设计初期依赖性分析就应解决。

空值的使用必然导致三值逻辑，造成语义不一致。而数据需要复制说明依赖性分析不到位。

客户/地址问题

关系模型之父 E.F.Codd，十二条规则，简明定义关系数据库必备的特性。

总结：空值对程序逻辑是危险的，必须使用空值时，一定清楚特定情况下的影响。

限用boolean型字段

理解子类型

表过宽（太多属性），是对数据项间的关系了解不深。

相同的属性可以共用，但独有的属性要确保分开，引出子类型的话题。

父表中主键的集合，是所有子类型中主键的并集，所有子类型表中的主键交集必为空（如果仅属一种子类型），子类型的主键同时是外键，建立于父表的参照关系。

给子类型指定完全独立于父表主键的主键，是极其错误的。子类型不同于主从关系，从主键可区分。

如果子类型主键不是父表主键的子集，很多方面都会导致性质降低。

高效访问数据库的主要原则之一，原理：分而治之。

总结：表中有些字段出现空值，表明需要引入子类型。

子类型可能被误用，大量查询频繁引用父表不是高效做法。子类型间必须有逻辑差异性。不可受OO启发，设计出继承性很强的shema..

约束应明确声明

      数据中存在隐含约束是一种不良设计。尽可能多的定义约束。键标记各种唯一性约束。约束有助于控制被输入值的范围。约束有两个主要作用：

一。有助于保证数据完整性。中要定义的规定正确，约束能保证数据都符合规则。

二。为DBMS核心，优化器，提供重要信息。

数据库中的信息只需定义一次，所有程序就可以一致地使用。

关于数据使用考虑的隐含业务规则，必须被编码到每个存取数据的程中。

总结：数据语义属于DBMS。不能放在应用程序中。

过于灵活的危险性：

通用属性，将所有信息当成字符串存，的确避免空值，是便于随时增加新属性；但同时要求必填属性存为字符串，使数据类型、数据库完整性、参照完整性完全丧失。简单查询值表被join多次。

总结：直正的设计灵活性来自合理的数据建模。

历史数据难题

关系设计的直正难题，如何处理某一时间段相关的数据（不是某一时间点）。

总结：要处理不断变化且需保存变化历史的数据，必须根据数据改变的快慢程序，决定设计策略。

设计与性能

性能调优两个方面：

一方面，整体性能，CPU REM I/O .

另一方面，修改具体查询。

增加索引并不属于数据库调优，因索引必须在设计初即被正确定义。

总结：设计不仅做到功能，必须考虑性能。

处理流程

操作模式：异步模式（批处理系统）、同步模式（交易系统）

大规模批处理时，最关心吞吐量。

总结：数据模型设计必须考虑数据流。

数据集中化

对远程数据的透明引用，是性能杀手。

查询本地数据最有效方式是嵌套的循环，但数据位于远程时，优化器只有两种选择，效果都不佳：

一。使用套嵌循环，每次开销大。

二。建立远程数据的本地副本，但副本无索引。

系统复杂性

建立备用后援数据库。保证同步，恢复检查。

小结：成功的数据建模应严格遵守基本的设计原则。

 

第二章 高效访问数据库

查询识别

记录功能（oracle dbms_application_info包，记录哪个应用正在执行，何时正在做什么，应用通过oracle V$ 动态视图（显示内存中发生的情况））

易识别的语句有助于定位性能问题。

保持数据库连接稳定

数据库连接是重操作，耗时，程序与数据库间交互开销，使用数组传递。因逐行处理面临上下文切换问题。

保持数据库Schema稳定

在应用程序中使用数据库定义语言DDL建立、修改或删除数据库对象，是很差的方式，应禁止。除分区和临时表外，没必要动态DDL。

DDL的作用是以核心数据库的数据字典为基础，数据字典是所有数据库操作的中心，所以任何对数据字典的操作都会引起全局加锁，对系统性能影响巨大。唯一可接受的DDL操作是对表的truncate清空操作。能极快的清空表所有数据（不可回滚恢复）。

查询临时表的语召效率永远比永久表差。更不可把永久表当作临时表来用，因DBMS会收集统计信息给优化器查询用。

总结：暂时工作表意味以不太合理的方式存储更多信息。

用SQL处理集合

SQL是完全基于集合来处理数据。

将一次“大批量数据的处理”，分割成多次“小块处理”是个坏主意，除非修改太昂贵。

因为这方法太低效：

1。占用过多空间保存原始数据，以备事务回滚之需。

2。万一修改失败，回滚消耗过长。

实践：“从头开始重做”比“确定失败发生的时间和位置，接着已提交部分重做”更容易简单也快。

借助游标循环处理慢，能换成几个甚至一个语句最好。

动作丰富的SQL语句

SQL不是过程性语言，取数据、循环处理、再插回，错误的做法：

坏习惯、SQL知识缺乏、盲从功能需求。

其实许多复杂操作可由一条SQL语句完成，避免在SQL中引入“过程逻辑“主因有二：

数据库访问，总会跨多个软件层，甚至网络访问。调用重复多次，对性能有影响。

在SQL中引入过程逻辑，意味性能和维护问题应由程序承担。

总结：尽可能把事情交给数据库优化器处理。

充分利用每次数据库访问

OO最佳实践，不要和关系数据库处理相混。将表等同于类、字段等同于属性，都是致命错误。

接近DBMS核心

没必要编程实现隐含实现的功能：

Transact-SQL @@ROWCOUNT PL/SQL SQL%ROWCOUNT

利用监控功能。

把逻辑放到查询中，尽可能把条件逻辑放到SQL语句中。

SQL不需要循环能力，因它本质上是在操作集合，SQL只需要执行条件逻辑的能力。

一次完成多个更新

如每次更新的是彼此无关的记录，应合并成一个UPDATE操作，尽量减少对一个表的重复访问。

慎用自定义函数

自定义函数内部执行查询，阻碍基于开销的优化器对整个查询的优化效果。

简洁的SQL

尽可能少的SQL完成尽可多的事。

SQL的进攻式编程

一般建议是进行防御性编程，在开始处理前先检查所有参数的合法性。

但对数据库编程，尽量同时做几件事的进攻式编程有切实的优势。

应该先假设没有事情会出错，但如果出错了，就在出错的地方采取措施。

像“乐观并发控制”，它假设update冲突不会发生，只在真发生时才进行控制。

吞吐量高。

异常处理会迫使采用过程式逻辑（循环处理），尽量预测可能的异常情况，用声明式SQL过滤处理。

 

 

 

第三章 建立索引

关键数据的读取，为其提供高效的访问路径。基本策略是建立索引。

切入点

必须对搜索条件有了解，否则不要着手编写任何SQL语句。

程序的输入值为哪些，以及定义的数据子集的大小，是建立索引的基础。

索引的时空开销相当高，处理开销：

每当表中插入或删除记录时，索引要调整。当对已建立索引的字段进行更新时，也会调整。

维护一个索引的开销比一张表的开销还要大。

“为提升性能而避免使用触发器”，维护索引的开销与简单触发器大致相当。

在大规模并发访问环境中，过多索引会造成资源竞争和加锁。

对于事务处理的数据库而言，大部分SQL语句查或处理少数几行记录，此时索引对性能提升价值很大。如果表符合范式要求，需加索引的字段应很少。主键字段会被自动加上索引。唯一性字段可能在实现完整性约束时被加上索引。对接近唯一性的字段，也应加上索引。

经验，通用目的或事务处理型数据库而言，大部分表不需要加索引，因为许可表的查找是根据一组非常有限的条件来进行的。而决策支持系统中建立索引的准则与之相去甚远。

总结：在事务处理型数据库中，“太多索引”意味着设计不够稳定。

索引和目录

精确定位一条或多条记录的值，索引是一种以原子粒度访问数据的手段。而不是为了检索大量数据的。

总结：一定要清楚哪些字段加索引，为什么加！

让索引发挥作用

索引还是目录，检索比率决定。

判断索引适用性的依据，就是用键值作唯一条件检索出数据的百分比。10%，反映索引的选择性。

索引值相关记录的物理位置是否邻近很重要。如果散布于整个表中，检索单独一行和一组数据效率相差很大，这在处理日期时常发生。

总结：记录的排列顺序和索引键相同，可以提高范围查询的速度。

函数和类型转换对索引的影响

索引有树结构来实现-以避免对表大量插入、更新和删除后，索引快速退化。

如果需要对字段使用函数，原子性不符合业务需求。1NF都不符合。

为操作一个或多个字段的函数所产生的结果建立索引，函数索引，慎用。

时间段，缺少专门的表示时间间隔的数据类型，开始时间、完成时间、和持续时间。只有借助函数索引或冗余数据实现。

函数索引常暗示设计有问题。数据原子性问题没解决。

索引与外键

外键加索引，是常识，但慎重，为外键加上不必要的索引可能不妥，尤其对有很多外键的表。

对数据进行并发更新时，外键加索引尤为必要，以避免加锁时间过长。缩短“为确保数据完整性而锁定表”的时间。要同时更新存在“参考完整性约束”的两个表，可以引入索引。但事务型数据库，参照表常是只读的，此时是否为外键加索引的标准是性能，否则无必要。

总结：建立索引必须有理由。无论是外键，或其他字段。

同一个字段，多个索引

系统地为外键增加索引，常导致同一个字段属于多个索引的情况。

系统生成键

对系统生成键（自动递增的序号，Oracle系统计数器产生）要特别小心。比找最大值加1容易，远比用一个专用表保存“下一值”且加锁更方便（会使访问串行，降低访问速度）。

如果生成的序列号非常接近，做为键值插入到主索引时，将争用同一索引页。

系统为降低此问题影响，允许建立逆序索引，但不再支持范围搜索。

哈希索引也 避免 “更新同一索引项”，索引树内出现资源竞争点。

制定计划：为性能而设计
表内不同字段之间存在“关系”。关系就是表（表描述了关系），不同字段联系在一起的方式定义了关系。
关系理论的关键是：关系不包含重复数据，记录之间没有顺序。
总结：建模是业务需求具体化的过程。
规范化使混沌变得有序。
将不同类型信息组织起来，成为数据模型的步骤如下：
第一步：确保原子性（Atomicity）：原子是指无法分割的东西。把原子属性定义为表中的字段。如果在where子句中必须引用某属性的一部分，说明这个属性没有达到所需的原子性级别。
常规索引应该以具有原子性的值作为键。可采用其他手段（如建立全文索引）加速查询，但不能实时更新。
应该避免将多个值塞进同一字符串中。
“如何描述地址”实际取决于“要如何使用地址”
确定能唯一标识各记录的主键（primary key），主键可能是复合的。以标准的简化名称或唯一代码作为主键，运用具有实际意义的主键。所有属性都有原子性、且确定了键，数据就符合1NF了。
第二步：检查对键的完全依赖性
数据冗余有两个问题：（1）容易引起信息不一致（修改时费时）（2）存储空间浪费。

举例：
　　 (学号, 课程名称) → (姓名, 年龄, 成绩, 学分)
　　 这个数据库表不满足第二范式，因为存在如下决定关系：
　　 (课程名称) → (学分)   (学号) → (姓名, 年龄)  即存在组合关键字中的字段决定非关键字的情况。
　　 由于不符合2NF，这个选课关系表会存在如下问题：
　　 (1) 数据冗余：  同一门课程由n个学生选修，"学分"就重复n-1次；同一个学生选修了m门课程，姓名和年龄就重复了m-1次。
　　 (2) 更新异常：  若调整了某门课程的学分，数据表中所有行的"学分"值都要更新，否则会出现同一门课程学分不同的情况。
　　 (3) 插入异常：  假设要开设一门新的课程，暂时还没有人选修。这样，由于还没有"学号"关键字，课程名称和学分也无法记录入数据库。
　　 (4) 删除异常：  假设一批学生已经完成课程的选修，这些选修记录就应该从数据库表中删除。但是，与此同时，课程名称和学分信息也被删除了。很显然，这也会导致插入异常。
    为消除键的部分依赖性，必须建新表，新表的键都是原始表的一部分。把依赖于新键的所有属性转移到新表。在去除了只依赖部分键的属性后，表就符合2NF了。
第三步：检查属性独立性
数据集的每个属性都已完全依赖唯一键（unique key）,不能根据任何其他属性确定一个属性的值，这个数据集就满足3NF。
数据库设计的缺陷征兆：表的字段大多为空值（null），特别是某两个字段不可能同时有值。
存在空值意味着关系模型存在严重问题，动摇了查询优化的基础。完备性以二值逻辑为基础。Where子句中的条件必须明确，任何中间状态或空值都是不确定的。
包含空值的模型属于三值逻辑（真、假、不确定）
使用空值和隐含规则，是采用三值逻辑的典型表现。空值的使用必然导致三值逻辑，造成语义不一致。数据需要复制说明依赖性分析没有做到位。
总结：空值对程序逻辑是危险的，清楚它在特定情况下的影响。
限用Boolean型字段：SQL中不存在Boolean类型。一组Boolean型属性可组合成一个单独的状态属性，可能违反原子性基本原则。
理解子类型（Subtype）:相同的属性可共用，独有的属性要分开，引起的。给子类型表指定完全独立于父表主键的主键，是错误的。不同于主从关系。子类型主键不是父表主键的子集的话，导致性能降低。
总结：表中有些字段出现空值，表明需要引入子类型。
约束应明确声明：尽可能多定义约束。外键（Foreign key）可关系到主表（master table）。约束助于控制被输入值的范围。有两个主要作用：（1）保证数据完整性。（2）为优化器提供关于数据的信息。
数据库中的信息只需定义一次，所有程序不可以一致的使用。
总结：数据语义属于DBMS，别入在应用程序中。
真正的设计灵活来自合理的数据建模。
数据模型设计必须考虑数据流。
查询本地数据最有效的方式是嵌套的循环。
成功的数据建模应严格遵守基本的设计原则。

发动战争：高效访问数据库
在SQL中插入注释有助于辨别查询在程序中的位置。
易识别的语句有助于定位性能问题。
保持数据库Schema稳定：没和必要动态建立、修改或删除数据库对象。
数据定义语言（DDL）的作用是以核心数据库的数据字典（数据库操作的中心）为基础，任何对数据字典的操作都会引起全局加锁，对系统性能影响大。唯一可接受的DDL操作是对表的truncate(清空)操作，可极快的清空表的所有数据。不能通过回滚恢复。
用SQL处理集合：
大批量数据分割成小块处理极低效：
占用过多空间保存原始数据，备回滚之需
万一失败，回滚消耗过长的实践。
总结：尽可能多的把事情交给数据库优化器来处理。
统计记录意味着重复全部搜索，对相同数据处理了两次。
没必要编程实现数据库隐含实现的功能。
把逻辑放到查询中：将过程逻辑放在SQL语句中，不是宿主语言。过程性语言特点在于拥有执行迭代（循环）和条件（if...then...else结构）逻辑的能力。SQL只需要执行条件逻辑。
总结：尽量把条件逻辑放到SQL语句中，不是SQL的宿主语言中。
用一个语句处理多个更新，尽量减少对同一个表的重复访问。
使用查找函数会使批处理程序的性能极差，查询时间增加。
总结：优化器对自定义函数的代码无能为力。
以概论为基础进行编程。假设最可能的结果；不是的确必要，不要采用异常捕捉的处理方式。
异常处理使我们采用过程式逻辑，尽量预测可能的异常情况。

战术部署：建立索引
维护一个索引的开销，比维护一张表的开销还要大。
维护索引的开销与简单触发器带来的开销相当。
在事务处理型数据库中，太多索引意味着设计不够稳定。
目录与索引关键区别是：目录项指示正文块。
索引是以原子粒度访问数据库的手段，不是为了检索大量数据。
判断索引适用性的依据，是用键值作唯一条件检索数据的百分比。
记录的排列顺序与索引键相同，可以提高范围查询的速度。
如果需要对字段使用函数，说明表中原子性的数据不符合业务需求。边1NF都不符合！！
调用函数将进一步导致索引无法改善性能--只会增加索引的总维护成本。
为外键加不必要的索引不妥，尤其对具有很多外键的表。对数据进行并发更新时，外键加索引尤为重要，以避免加锁时间过长。好处是每个进程所需时间大幅缩短，也缩短了“为确保数据完整性而锁定表”的时间。
建立索引必须有理由。
为外键增加索引，导致同一个字段属于多个索引的情况。
为每个外键建立索引，可能会造成多余的索引。
主键索引主要是确保主键的唯一性。
如果只有一个生成器，生成的序列号就很接近，将键值插入到主键索引中时所有进程将争用同一索引页。
Oracle允许建立逆序索引，保存索引之前先将组成键的bit序列进行逆向排列。
最高效的索引访问是唯一索引，基于主键搜索。

4.机动灵活：思考SQL语句
特定存储模型决定了查询优化的方式。优化器检查下列因素:定义了哪些索引、数据的物理布局、可用内存大小，及可用于执行查询任务的处理器数。它很重视查询直接或间接涉及的表和索引的数据量。以关系理论为支柱的优化器主要用于关系操作层。尽量在关系层完成大部分处理。
索引指硬盘地址，并非临时存储地址。
关系（及描述关系的表）是无序的，关系中的元组（即记录）可被存储或检索。
SQL艺术的五大要素：
获得结果集所需访问的数据量
定义结果集所需的查询条件
结果集的大小
获得结果集所涉及的表的数量
多少用户会同时修改这些数据
只要查询有可能返回零结果集时，先检查那个最大可能导致空结果集的条件--执行非常快捷时。条件的顺序与条件所在上下文的关系十分密切。
总结：当视图返回不必要的元素时，别把视图内嵌在查询中，而是应将视图分解，将其组成部分加到查询主体中。
避免在最高层使用distinct是一条基本原则。
内层查询不再依赖外层查询，变成了非关联子查询，只须执行一次。
对大量记录做存在性检查时，选择in 还是exists须斟酌。非关联子查询可被改写成from子句中的内嵌视图。In会隐式的剔除重复项目，当子查询改写为from子句中的内嵌视图时，必须要显式的消除重复项目。
任何影响聚合函数结果的条件都应放在having子句中，因为在group by之前无从知道聚合函数的结果。与聚合函数无关的条件都要放在where子句中。
总结：尽早过滤掉不需要的数据。
当查询返回的记录数超过表中数据总量的10%时，不要使用索引
当查询的结果集很大时，索引未必必要。

了如指掌：理解物理实现
并发用户数很大的系统中两个冲突目标：一个是，尽量以紧凑的方式存储数据，有助于查询尽快找到所有数据。另一个是，尽量装数据分散存储，多个进程并发写入，不会造成资源争用。
不缩短响应时间，就要减少数据库访问数据页的数量，手段如下：
使每页的数据密度更高
将一个检索处理最可能访问的数据聚集在一起。
总结：读与写不会和睦相处：读操作希望数据聚集在一起，而并发的写入操作希望数据是分散保存的。
当键值指向大量记录时，高效的做法是从头到尾扫描表并忽略索引。当一个字段包含多重复值时（即低区别度），为该字段建索引没什么用（至少对事务处理型数据库如此），除非某个值可选择性很高且频繁出现在where子句中。
在索引中加入额外的字段（含有查询所需关键数据），能提高某个频繁运行的查询的速度。
把“尽量在索引中多存储数据”原则扩展到极限，在主键索引中存储表中所有数据，不需要表。此方法能节省存储空间，节省时间。表即是索引，对堆结构讲，就是“索引组织表”（IOT）。
插入索引的开销比插入普通表还高。索引有强大的内部排序功能。有些查询，通过索引数据即可完成。
记录强制排序：主键绝不会被更新。
“聚集索引”和“索引组织表”的缺点：表被改选成树状结构，分层排序。任一种层次结构，有利于一类数据操作和一条访问路径，但其他访问路径比较糟糕。原本库文件内数据物理分布整齐，由于加链接、增加溢出页等，数据的物理分布不再整齐，降低了性能。
对聚集数据进行范围查询效率惊人，其他查询大打折扣。
循环分区是一种不受数据影响的内部机制。对并发的随机查询有帮助，是随意散布数据的机制，不根据逻辑数据相关性来分组数据。
数据驱动分区：要求逻辑上有一个单独的表，具有能被其他表参照的真正主键。
哈希分区对范围搜索没有优势。
范围分区适合处理历史性数据。
当值的分布不均匀时，链表分区比较有用。
理想情况是：为select设计的数据聚集方式，与为insert设计的数据散布方式是一致的。
总结：用数据分区来分散数据、还是聚集数据，取决于你的需求。
对分区表进行查询，当数据按分区键均匀分布时，收益最大。
预连接表是物理上存储在一起的表，准则是连接条件。
预连接表是改进查询的一种专门技术，但几乎会影响所有其他数据库操作。

锦囊妙计：认识经典SQL模式
过滤数据遇到的最典型的9种情况如下：
1小结果集，源表少，查询条件直接针对源表:查少量记录,利用索引,参照不同表时连接顺序,取决于如何更快过滤不需要的记录.连接表时唯一性索引非常有用.非唯一性索引和唯一性索引上的范围表达时,执行范围扫描.频繁更新的字段会增加索引维护成本.
2小结果集，查询条件涉及源表之外的表:数据来自一个表,但查询条件针对其他表,不需从条件表返回任何数据.可用两法:A使用连接,加上distinct去除重复.B使用关联或非关联子查询.
3小结果集，多个宽泛条件，结果取交集:要根据不同条件的过滤能力和已存在哪些索引而定. 事务处理型数据库索引因更改频繁成本高.
4小结果集，一个源表，查询条件宽泛且涉及多个源表之外的表:全表多次扫描,结果嵌套循环,哈希连接或合并连接.确定结果集的哪种组合产生的记录数最少.FROM子句中采用嵌套查询,使表关联变得明确.
5大结果集:批处理环境.条件可选择性不高.借助哈希或合并连接进行全表扫描是合适的.尽量扫描数据返回比例最高的表/索引,扫描时过滤条件必须是粗粒度的,返回的数据量比较大.使扫描更有价值,扫描结束再重新贯彻"尽快去除不必要的数据"这一原则,处理大量记录时,关联子查询是性能杀手,避免子查询相互依赖.
6结果集来自基于一个表的自连接:"从不同角度看待相同数据",重点保证索引访问的高效.可使用基于滑到窗口工作的函数.
7结果集以聚合函数为基础获得:汇总,结果集大小取决于group by字段的基数,而不是查询条件的精确性.尽可能把动作放到SQL查询中.将对数据的处理交给SQL聚合函数.AND和乘法OR和加法间关系密切.
8结果集通过简单搜索或基于日期的范围搜索获得.数据项的可选择性取决于项的总数/取样频率/时间长短.

A大量数据项,少量历史数据,使用子查询,使用OLAP函数,属于非关系层,作用在查询做最后处理.B少量数据项,大量历史数据,大量排序,降低关系层厚度,在关系多做工作,增加过滤条件的数量.
9结果集和别的数据存在与否有关。识别例外.not in()搭配非关联子查询,或not exists()搭配关联子查询.在子查询出现在高效搜索条件后,推荐后者,但当子查询恰好是唯一条件时,使用not in较好.
需要连接（join）表时，唯一性索引很有用。
维护一个计算字段上的索引，代价要高于静态字段上的索引，因为被修改的键会在索引内“移动”
频繁更新的字段会增加索引维护的成本。
处理大量记录时，关联子查询是性能杀手。当一个查询包含多个子查询时，必须让它们操作各不相同、自给自足的数据子集，避免子查询相互依赖。
所有非键字段与键相关、并完整依赖于键，非键字段之间没有任何依赖。
总结：当多个选取条件用于同一个表的不同记录时，可以使用基于滑动窗口工作的函数。
糟糕的SQL编程有个特点：在SQL查询之处存在大量代码，以循环方式对返回数据进行些加、减、乘、除之类的处理。这些低效的工作应该交给SQL的聚合函数。
OR依赖sum,而AND依赖乘积。
OLAP函数属于SQL的非关系层.作用是：在查询中做最后处理。在过滤已完成后对结果集进行处理。
一个表中记录与另一表中数据不匹配，解决方案有两个：not in()搭配非关联子查询，或not exists()搭配关联子查询。在子查询出现在高交搜索条件之后，使用not exists是对的，因为高效过滤条件已消除大量无关数据。当子查询恰好是唯一条件时，使用not in比较好。
当存在关联子查询时，就必须执行外层查询，对所有通过过滤条件的记录，执行内层查询。外层查询和内层查询相互依赖，外层查询把数据传递给内层查询。
外连接主要目的是，返回来自一个表的所有信息及连接表中的对应信息。

变换战术：处理层次结构
层次结构不是“组件之间关系的表达”，而是“访问树的方式”。访问树由DBMS引擎以过程性方式实现，而过程性操作是违背关系理论的主要表现之一。
树状结构VS主从关系，如下四个差异：
树状结构的保存只需一个表。表与它本身之间有种主/从关系，不是两个类型不同的表的关系。
与根节点的距离是层次结构的重要信息。主从关系中，不是主表、就是明细表。
主/从关系中，可以有明确的外键完整性约束。
多重父节点。数有两种实体类型，一个是节点，另一个是节点之间的连结。
用SQL数据库描述树结构
树的三种模型描述：
邻接模型（Adjacency model）原因在于：层次中的父记录ID作为子记录的属性，树中两个相邻节点被明确关系在一起。
物化路径模型（Materialized path model）思想是：把树中每个节点，与它在树中位置的描述数据结合。
嵌套集合模型（Nested set model）：每个节点被赋予一对数字，父节点的两个数字定义的间隔总是将其所有子孙所定义的间隔包含在其中。此模型思想是以两个数字为特定节点的路径编码，解释成有理数（即分数）的分子和分母。计算量大，对存储程序要求高。它是基于指针的解决方案，设计关系方法的目标正是要逃离指针的沼泽。
层次结构主要困难是不存在“最佳描述”。
connect by是过程性的，但自底向上、自顶向下的查询表现不错，应为字段建立适当的索引。
当connect by或递归with不可用时，物化路径模型是良好的替代品。
connect by重大缺点：同一时间只知道两个层次：当前记录（子节点）及其父节点。
物化路径绝不该是键，即使它们具唯一性。动态环境无法造就牢固的层次结构。
物化路径不该暗示任何兄弟节点的排序。

孰优孰劣：认识困难，处理困难
总结：遇到“前置码比较”或“基于键的一部分比较”等特殊情况时，可使用范围条件来表达，并尽量使用下限值和上限值。
动态建立查询应使用参数标记，并以绑定变量方式传值。
减少join次数。
用PHP绑定变量的过程：
每个参数的位置上写一个“？”
调用bind_param()，它第一个变量是字符串，每个字符代表一个要绑定的值，也说明了要传递参数的类型。

多条战线：处理并发
并发修改数据：修改数据操作越频繁，维持良好性能难度就越大。修改操作本质上比查询代价更高，加锁机制和资源争用会恶化上述情况。
在事务内，应该：
避免SQL语句上的循环处理。
减少程序和数据库的交互次数。
跨机器交互的次数降到最少（如，运用存储程序或数组读取）
总结：事务型活动密集时，无需加锁的操作一定不要加锁。
使加锁时间最短，必须进行频繁的提交。提交是代价极高处理,对批处理,避免频繁提交.
事务是多并发交互式,还是批处理式?并发用户数越多，提交的间隔就应该越短。
并发性取决于完整性保护机制，包括锁和其他控制。
事务空间：表或索引由物理块组成，每个物理块中为事务条目预留了空间。DBA第一个手段是调整事务条目所占空间的大小。每项事务条目为一个低级锁，当多个session竞争同一个物理块的写权限时，事务条目的争用是资源冲突的重要原因。
数据分区在提高插入性能的同时，对查询性能有较大影响。
避免竞争最佳方法，是避免使用顺序产生的代理键。最好不要采用自增字段。

集中兵力：应付大数据量
基于主键的搜索受数据量的影响不大。
处理大量记录的高效方法，是不要反复，并应用单一记录处理。
数据量增加时，排序操作受影响比扫描操作大，因为排序是复杂操作，需要多遍处理。
造成较大影响的不是记录数量，是字节数量---被排序的总数据量
总结：为降低查询对数据量增加的敏感度，在较深层的查询中只操作绝对必要的数据，将辅助性的jion操作留在外层。
尽量减小关联子查询对外层查询元素的依赖性。
范围扫描高效，尤其采用聚集索引，此时记录按照索引键的顺序存储。依靠比较键的索引搜索更注重顺序执行。
Insert代价很高，因为插入新记录时还必须维护“表上的索引”，而update只需要维护“更新字段上的索引”。Update的弱点有二：
update与搜索（where子句）不可分，加锁期间状况恶化。
先前的值（insert的情况下并不存在）必须保存在某个地方，以备回滚（rollback）.
Delete有所有缺点，既影响索引，又与where子句相伴，必须保存删除值以备回滚时用。
总结：所有更改数据的操作中，删除最有可能造成麻烦。
采用临时表的理由同用来支持大量面向表的操作，避免逐行操作。
事实表与维度表：星型schema
维度模型的原则是保存度量值，参考数据保存在维度表中，还包含自解释的标签，维度表是非规范化的。典型的维度模型有5到15个维度表，采用系统产生的主键，事实表包含所有维度表的外键。
总结：维度模型面向读操作而设计，经常忽略关系设计的规则。
维度模型比事务型模型简单。
对同一个表多次进行大规模更新，对物理层影响巨大。
维度表不含事实表的外键，事实表包含维度表的外键。
维度模型查询另一特点：查询条件不明确，通过取交集的方式获得不太大的结果集。
维度模型有个前提：先访问维度表，后访问事实表。是静态的。
加速加载的技巧是先舍弃索引，在数据加载后重建索引。

精于计谋：挽救响应时间
行转成列： 解决属性数量可变问题的合理手段就是定义子类型，子类型支持明确的参考完整性约束。语义完整性。
总结；用单一处理动作读取所有记录，把那些值散布在不同字段中，使用聚合函数把许多记录合并成一条，可以避免多重自连接。
列转成行： 枢轴表（pivot table）用要想把字段看成记录之时，对表做旋转或转动。
使用枢轴表提供的分割字符串的能力，可把值的列表当成单一字符串传递给语句。
总结：基于范围做聚合操作时，需要建立人造排序键，以便按需要的顺序显示结果。
优化器指令：有两个技巧可以限制优化器：
session环境中的特殊设置。
显式写入语句的本地指令。

明察秋毫：监控性能
性能达到临界值而缓慢下降，以下三个因素：
负载随时间呈规律性增长。
负载随表的大小不断增加而增长。
负载在大量删除/更新操作后大幅增长，临界值是因为物理存储的质量下降造成的。
总结：两个重要数据库负载指标是：CPU花费在语句解释上的总时间，以及执行查询需访问的数据存储而数量。
80%的结果是由20%的原因引起的。
负载大小必然和SQL语句相关，SQL语句必然和业务活动相关，业务活动必然和业务需求相关。
判断查询性能的唯一标准，是花了多长时间执行。
使优化器采用完全不同的执行计划，手段如下：
当返回少量记录时，应增加索引，或重建复合索引并调整其中的字段顺序，将非关联子查询转换成关联子查询。
当返回大量记录时，做法相反，并在from子句中使用子查询，建议表连接能以不同顺序进行。
用union 或with子句分解查询。
优化器指令。
总结：影响性能的重要因素
表的数据量
表有哪些索引
存储特性（如分区），和索引同样重要
查询条件的质量
结果集的大小
查询时一定要尽量“尽快去除多余数据”，保持优化器的自由，避免语句内部存在依赖性而限制了表的访问顺序。
以下情况时优化器无法高效工作：
通过很多语句，分别读取数据片段。无法跨越语句的界限进行优化。
随便使用SQL提供的各种非关系特性。
非关系特性是基于有限集合的操作（数组），不同于关系理论中的无限集合。