1. 机器学习

• 评估假设

  一旦我们通过以下方式在我们的预测中做了一些故障排除：

  • 获得更多培训示例
  • 尝试更小的功能集
  • 尝试其他功能
  • 尝试多项式特征
  • 增加或减少λ

  我们可以继续评估我们的新假设。

  假设对于训练样例可能具有低误差，但仍然不准确（因为过拟合）。因此，为了评估一个假设，给定一个训练样本数据集，我们可以将数据分成两组：训练集和测试集。通常情况下，训练集包含70％的数据，测试集是剩下的30％。

  [图片上传失败...(image-ec2eb7-1525879347213)]

  • 模型选择和训练/验证/测试集

    我们的数据集分成三组的一种方法是：

    • 训练集：60%
    • 交叉验证集：20％
    • 测试集：20％

    现在我们可以使用以下方法为三个不同的集合计算三个单独的错误值：

    1. 使用每个多项式的训练集来优化Θ中的参数。
    2. 使用交叉验证集找出具有最小误差的多项式度d。
    3. 使用带有测试集的估计泛化错误 Jtest(Θ(d))，（d =具有较低误差的多项式的θ）;

    这样，多项式d的程度还没有使用测试集进行训练。

  • 诊断偏差(欠拟合)与方差(过拟合)

    [图片上传失败...(image-b38e0e-1525879347213)]

  • 正则化和偏向/方差

    [图片上传失败...(image-79b699-1525879347213)]

    在上图中，我们看到了λ 升增加，我们的适应变得更加僵硬。另一方面，如λ 接近0，我们倾向于过度拟合数据。那么我们如何选择我们的参数λ 得到它“恰到好处”？为了选择模型和正则化项λ，我们需要：

    1. 创建一个lambda表（即λ∈{0,0.01,0.02,0.04,0.08,0.16,0.32,0.64,1.28,2.56,5.12,10.24}）;
    2. 创建一个不同程度的模型或任何其他变体。
    3. 迭代通过 升s和每个 λ 通过所有的模型来学习一些Θ.
    4. 使用学习的Θ（用λ计算）计算交叉验证错误 JCV(Θ) 没有正则化或λ= 0。
    5. 选择交叉验证集合中产生最低错误的最佳组合。
    6. 使用最佳组合 Θ and λ, apply it on Jtest(Θ) 看看它是否有一个很好的概括性的问题。

  • 学习曲线

    在少数几个数据点（如1,2或3）上训练算法将很容易产生0个错误，因为我们总是可以找到一个接近这些点数的二次曲线。因此：

    • 随着训练集变大，二次函数的误差增加。
    • 经过一定的m或训练集大小后，误差值将平稳。

    经历高偏见：

    [图片上传失败...(image-4de8af-1525879347213)]

    经历高方差：

    [图片上传失败...(image-a0a2f4-1525879347213)]

  • 决定下一步做什么

    Our decision process can be broken down as follows:

    • Getting more training examples: Fixes high variance

    • Trying smaller sets of features: Fixes high variance

    • Adding features: Fixes high bias

    • Adding polynomial features: Fixes high bias

    • Decreasing λ: Fixes high bias

    • Increasing λ: Fixes high variance.

    诊断神经网络

    • 一个参数较少的神经网络容易出现不足。这也是计算更便宜。
    • 具有更多参数的大型神经网络容易过度拟合。这在计算上也是昂贵的。在这种情况下，您可以使用正则化（增加λ）来解决过度拟合问题。

    使用单个隐藏层是一个很好的默认开始。您可以使用交叉验证集在许多隐藏层上训练您的神经网络。然后您可以选择性能最好的一个。

    模型复杂性的影响：

    • 低阶多项式（低模型复杂度）具有高偏差和低方差。在这种情况下，模型不太一致。
    • 高阶多项式（高模型复杂度）非常适合训练数据，测试数据极其糟糕。这些对训练数据的偏倚低，但差异很大。
    • 实际上，我们希望选择一个介于两者之间的模型，这个模型可以很好地推广，但是也可以很好地拟合数据。

  • 优先处理什么工作

    系统设计示例：

    给定一个电子邮件数据集，我们可以为每个电子邮件构建一个向量。这个向量中的每个条目代表一个单词。该矢量通常包含10,000到50,000个条目，通过查找我们的数据集中最常用的单词来收集。如果在电子邮件中找到一个单词，我们将分配它的相应条目1，否则如果没有找到，条目将是0.一旦我们已经准备好了所有的x向量，我们将训练我们的算法，最后，我们可以用它来分类电子邮件是否是垃圾邮件。

    ​

    那么你怎么能花时间来提高这个分类器的准确度呢？

    • 收集大量数据（例如“蜜罐”项目，但并不总是工作）
    • 开发复杂的功能（例如：在垃圾邮件中使用电子邮件标题数据）
    • 开发算法以不同的方式处理您的输入（识别垃圾邮件中的拼写错误）。

    很难判断哪个选项最有用。

  • 错误分析

    解决机器学习问题的推荐方法是：

    • 从一个简单的算法开始，快速实施它，并尽早在交叉验证数据上进行测试。
    • 绘制学习曲线来决定是否有更多的数据，更多的功能等可能有所帮助。
    • 手动检查交叉验证集中示例的错误，并尝试找出发生大部分错误的趋势。

  • FI值

    如果我们定义一个测试集中，正样本个数为P, 负样本个数为N, 那么我们可以知道：P=TP+FN, N=TN+FP
    $$
    Recall=TP/(TP+FN)=TP/P
    $$

    $$
    Precision=TP/(TP+FP)
    $$

    $$
    F1=2⋅Precision⋅Recall/(Precision+Recall)
    $$

    可以看到，recall 体现了分类模型H对正样本的识别能力，recall 越高，说明模型对正样本的识别能力越强，precision 体现了模型对负样本的区分能力，precision越高，说明模型对负样本的区分能力越强。F1-score 是两者的综合。F1-score 越高，说明分类模型越稳健。

• 支持向量机

  支持向量机从本质上来讲，就是最大间隔分类器。

  这边从逻辑回归引出支持向量机，以及它最重要的思路——最大间隔。

  考虑逻辑回归：

  img

  我们看下在SVM中对costfunction的改变 ：

  [图片上传失败...(image-a8a915-1525879347213)]

  • Large Margin Intuition

    这里写图片描述

    ​

    SVM通过寻找分类中淡黄色背景的那条线作为边界，而不是其余满足条件的边界，因此SVM又被称为大间隔分类器。

    这里写图片描述
    这里写图片描述

  • The mathematics behind large margin classification
    下面我们从数学角度看一下SVM过简化问题，我们知道要求的最小值为||θ||的最小值，即θ的范数最小值

    这里写图片描述

    下面看一下限制条件代表的含义，通过高中数学，我们知道两个向量相乘的几何含义如下

    这里写图片描述

    通过上面可知，我们要求||θ||的最小值，因此我们希望p(i)尽量大。

    假如选择了下面图中的绿色线作为边界，我们会发现p(i)比较小，这样不能得出||θ||的最小值

    这里写图片描述

    如果选择下面的绿色线作为边界，我们可以得到较小的||θ||值

    这里写图片描述

  • 核函数

    [图片上传失败...(image-43c0a6-1525879347213)]

    通过下面的图我们可以看出δ2对函数图形的改变

    这里写图片描述
    • 关于参数对算法的影响 :

    大C:低偏差，高方差（对应低λ）

    小C:高偏差，低方差（对应高λ）

    大δ2：fi分布更平滑，高偏差，低方差
    小δ2：fi分布更集中，低偏差，高方差

    • 使用SVM步骤 ：
    这里写图片描述

2. SQL反模式

10. 取整错误

目标：使用小数取代整数

反模式：使用Float类型

          根据IEEE754标识，float类型使用二进制格式编码实数数据。

          缺点：

(1) 舍入的必要性： 并不是所有的十进制中描述的信息都能使用二进制存储，出于一些必要的因，浮点数通常是舍入到了一个非常接近的值。

举例：select rate from A where id=123 --Result:59.95

​ select * from A where rate=59.95 --Result：empty set; no rows match.

​ select * from A where ABS(rate-59.95)<0.000001 --这个才能正确查出数据！

​ 59.95，在二进制中存储了最接近59.95的值，用十进制表示就是59.950000762939

（2）在SQL中使用FLOAT

​ FLOAT类型的列中实际存储的数据可能并不完全等于它的值。如果将这个值扩大十亿倍，就能看出其中的区别

​ 示例：上例中的59.95

​ 变通的方法就是将浮点数看做“近似相等”，即两个值之间的差值足够小就认为它们相等。

​

**如何识别反模式**：任何使用Float、Real或者Double Percent类型的设计都有可能是反模式

​

**合理使用反模式**：如果要存储的值取值范围很大，大于integer、numeric的范围，那只能用float了。科学计算类的程序通常使用float。

​

**解决方案**：使用numeric、decimal类型

                使用numeric、decimal代替float，他们不会对存储的有理数进行舍入，因此select * from A where rate=59.95会返回记录。

               示例: ALTER TABLE Accounts ADD COLUMN hourly_rate NUMERIC(9, 2);    -- 9代表总位数，2代表精确度

　　        注意：numeric、decimal在sqlserver乃至sybase中，是完全一样的。

**结论**：尽可能不要使用浮点数

11. 每日新花样

目标：限定列的有效值，将一列的有效字段值约束在一个固定的集合中。类似于数据字典。

反模式：在列定义上指定可选值

​ 1、 对某一列定义一个检查约束项，这个约束不允许往列中插入或者更新任何会导致约束失败的值：

 create table Bugs(status varchar(20) check(status in('new','in progress','fixed')));

​ 2、使用域或者用户自定义类型（UDT）等方法。即使用这些方法约束某一列只能接受一个特定集合的数据，并且能很方便的将这约束应用到整个域上。但这些特性并没有得到大多数关系数据库的支持。

3、使用触发器：编写一个触发器，当修改指定列的内容时触发，将被修改的值和允许输入的值进行匹配，

​ 如果不符合则产生一个错误中断操作。

缺点：

​ 1、 无法查询出所有的约束值来供上层应用程序使用。

​ 不能用select distinct status from Bugs，因为可能有的status目前还没有存储。维护不好的话，还有可能造成列表和数据库存储的值，不一致。

2、增加新的约束值，需要修改数据库定义或者触发器。

3、废弃或修改某个值，可能要修改大量的数据，风险很大也不合理。

4、可移植性差，check约束、域，或者UDT在各种数据库中的支持形式并不统一。

如何识别反模式：当出现以下情况时，可能是反模式

1、我们不得不将数据库下线，才能在程序中加入一个新的选项。

2、这个Status列可以填入这些候选值中的一个。我们不应该改变这个后选值列表。

3、程序代码中关于业务规则的选项列表和数据库中的值又不同步了。

合理使用反模式：

1、在候选值几乎不变化的时候可以使用。

2、存储没有业务逻辑且不需要改变的候选值时非常方便。

​ 比如存储一对二选一切相互对立的值：Left/Right、On/Off等。

3、Check约束可以再更多的场景下使用，比如用来检测一个时间区间中start永远小于end。

解决方案：创建一张检查表（类似于字典表），每一行包含一个允许在列中出现的候选值，然后在目标表中定义一个外键约束。

CREATE TABLE BugStatus ( status VARCHAR(20) PRIMARY KEY);

结论：在验证固定集合的候选值时使用元数据；

​ 在验证可变集合的候选值时使用数据。

12. 幽灵文件

目标：存储图片或其他多媒体大文件

反模式：图片存储在数据库外的文件系统中，数据库表中存储文件的对应的路径和名称。

缺点：

1、文件不支持Delete操作。使用SQL语句删除一条记录时，对应的文件不会被删除，需要使用额外的程序来操作。

2、文件不支持事务隔离

3、文件不支持回滚操作

4、文件不支持数据库备份工具：备份工具不知道如何将通过路径引用的哪些文件也包含在备份操作当中。

5、文件不支持sql的访问权限设置

6、文件不是sql数据类型：字段中存储的是文件的路径，数据库不会验证这个字符串是否是一个有效的路径，也不会验证对应的文件是否存在。任何将这个字符串作为路径处理的逻辑都依赖于你的程序逻辑。

如何识别反模式：典型的使用反模式的项目通常没有考虑以下几个或者全部问题

1、数据备份和回复的过程是怎样的？怎么对一个备份进行验证？

​ 你有没有在一个干净的系统或者别的系统上对备份回复的数据进行测试？

2、图片文件堆积在那里，还是当他们孤立的时候就从系统中移除？移除他们的过程是怎么样的？

​ 这是一个自动的还是手动的过程？

3、系统中的哪些用户有权限查看这些图片？进入权限是怎么限制的？当用户请求查看他们无权查看的图片时会发生什么？

4、我能撤销对图片的变更吗？如果能，是应用程序来回复图片之前的状态吗？

合理使用反模式：如下是将图片或者大文件存储在数据库之外的好理由

1、这个数据库在没有图片的时候能精艺很多，因为图片相比于简单的数据类型来说大很多；

2、当不包含图片时备份数据库会更快并且备份的文件更小。你必须额外的执行一次文件备份，

​ 但这些比备份一个大型数据库要更容易管理；

3、如果图片是存储在数据库之外的文件系统中，对图片的预览或者编辑就能够使用更简单直接的处理方式。

如果这些图片存在文件系统中的好处是重要的，那么可以将大文件存储在数据库之外。

解决方案：在需要的时候使用BLOB类型。

所有的数据库产品都支持BLOB类型，支持你存储任何二进制数据。

注: 存储在数据库之外的数据不由数据库管理

13. 使用索引

目标：优化性能

​ 改善性能最好的技术就是在数据库中合理地使用索引。

索引也是数据结构，它能使数据库将指定列中的某个值快速定位在相应的行。

反模式：无规划的使用索引

1、不使用索引或索引不足

2、使用了太多的索引或一些无效的索引

（1）大多数数据库会自动地位主键建立索引，因此额外再定义一个索引就是冗余。这个额外的索引并无任何好处，它只会成为额外的开销。

（2）字符串索引很大，而且也不太可能对它进行全匹配查找。

（3）使用组合索引是一个很好的选择，但是大部分创建的组合索引都是冗余或者很少使用。

​ 组合索引的顺序也很重要：在查询条件、联合条件或者排序规则上使用索引时按照从左到右的顺序。

3、执行了一些让索引无能为力的查询

Like、表达式，都可能使索引无效。

如何识别反模式：当出现以下情况时，可能是反模式

1、这是我的查询语句，怎样使它更快？

2、我在每个字段上都定义了索引，为什么它没有变的更？

3、我听说索引会使数据库变慢，所以我不使用它。

合理使用反模式：

分离率是衡量数据库索引的一个指标。分离率越高索引效率就越高。需要时刻关注你的数据库中索引的分离率，并且抛弃那些低效的索引。

解决方案：MENTOR你的索引

1、Measure测量：大多数数据库都提供了一些方法来记录执行SQL查询的耗时，因此可以来定位最耗时的查询。

2、Explain解释：测量之后，分析查询变慢的原因。

​ 每个数据库都使用一种优化工具为每次查询选择最合适的索引，并生产分析报表，称之为“查询执行计划（QEP）”。

3、Nominate挑选：有了查询优化工具的QEP报表，应该仔细地查找那些没有使用索引的查询操作。

4、Test测试：创建完索引之后，需要重新测试跟踪那些查询，确认你的改动确实提升了性能。

5、Optimize优化：索引是小型的、频繁使用的数据结构，因为很适合将他们常驻在内存中。内存操作的性能是磁盘I/O操作的好几倍。

​ 数据库服务器允许你配置缓存所需要的系统内存大小。

6、Rebuild重组：索引在平衡的时候其效率最高，当你更新或者删除记录时，索引就逐渐变得不平衡。

​ 要想最大限度地使用索引，因此要定期对索引进行维护。

结论：了解你的数据库，了解你的查询，然后MENTOR你的索引。

14. 对未知的恐惧

目标：辨别并使用Null值

反模式：将Null值作为普通的值，反之亦然

1、在表达式中使用Null：

​ Null值与空字符串是不一样的，Null值参与任何的加、减、乘、除等其他运算，结果都是Null；

​ Null值与False也不同。And、Or和Not三个bool操作如果设计Null，结果很迷惑。

2、搜索运行为空的列：任何与Null的比较逗返回“未知”，既不是True，也不是False。

​ 在Where表达式中只能使用 Is Null 或者 Is Not Null，其他操作都查询不到结果。

3、不能使用Null作为参数传入查询表达式中。

要避免上述问题，可以使用约束，设置列禁止Null。存储值必须是有意义的内容。

如何识别反模式：当出现以下情况时，可能是反模式

1、我如何将没有值（Null）的列取出来？

2、将字符串与Null进行拼接操作，结果返回Null

合理使用反模式：

使用Null并不是反模式，反模式是将Null作为一个普通值处理或者使用一个普通的值来取代Null的作用。有一种情况可以讲Null视为普通值，就是导入或者导出数据的时候。

解决方案：将Null值视为特殊值

1、在标量表达式中使用null

进行=、<>、+、||操作时，只要有一个null值，结果就为null。

2、在布尔表达式中使用null

布尔表达式中，只有：null and false结果为false，null or true结果为true，其它情况结果都是null。

3、检索null值

使用 is null、is not null

4、声明not null列

有时候可以通过使用default值来避免null，但有时候却不可以这么做。

5、使用动态默认值

SqlServer中的Coalesec()与isnull()函数

15. 模棱两可的分组

目标：查询得到每组的max（或者min等其他聚合函数）值，并且得到这个行的其他字段

反模式：引用非分组列

1、单值规则：跟在Select之后的选择列表中的每一列，对于每个分组来说都必须返回且仅返回一直值。

          select ProductId,Max(DateReported) as Latest
          from Bugs  as b
          Join BugProducts  as bp
          on b.BugId = bp.BugId
          Group by ProductId;

在Group By字句中出现的列能够保证他们在每一组都只有一个值，无论这个组匹配多少行；

​ Max()等表达式也能保证每组都返回单一的值，即传回参数中的最大值。

如何识别反模式：当输入一个违背了单值规则的查询时，会立刻返回给你一个错误。数据库会返回不同的错误信息。

在SQLite和MySQL中，有歧义的列可能包含不可预测的和不可靠的数据。

合理使用反模式：

没有歧义的关系叫做“功能依赖”，最常见的就是表的主键和对应的值。

解决方案：使用无歧义的列

1、只查询功能依赖的列：将有歧义的列排除在查询之外。

2、使用关联子查询：关联子查询会引用外连接查询，并且根据外联结果查询中的每一条记录最终返回不同的结果。

​ 关联子查询的性能不是最好的，因为外联结查询结果中的每一条记录都会执行一遍关联的子查询。

3、使用衍生表：使用衍生表来执行子查询，先得到一个临时的结果，然后用这个临时表和原表进行连接查询。

​ 性能相比子查询更好一些。但是数据库必须将临时表得到的记录存在一张临时表中，因此这个方案也不是最好的。

4、使用Join：创建一个联结查询区匹配哪些可能不存在的记录。这样的查询结果被称为外连接查询。该方案使用与针对大量数据查询并且可伸缩性比较关键时。能更好的适应数据量的变化，但是难以理解与维护。

5、对额外的列使用聚合函数。只有确定最新的bug_id对应的Bug的日期也是最新的时候，才能使用这个方案，也就是说，Bug是按照时间顺序提交的。

6、链接同组所有值：MySQL与SQLite提供了一个叫做Group_Contract（）函数，能将这一组中的所有的值连在一起作为单一值返回，多个值之间用逗号分隔。其他数据库不支持该函数。

结论：遵循单值规则，避免获得模棱两可的查询结果。

16. 使用随机数排序

目标：随机排序，使用高效的SQL语句查询获取随机数据样本。

反模式：使用RAND()随机函数

​ SELECT * FROM Employees AS e ORDER BY RAND() Limit 1

缺点：无法利用索引，每次选择的时候都不同且不可预测。进行全表遍历，性能极差。

如何识别反模式：当出现以下情况时，可能是反模式

1、在SQL中，返回一个随机行速度非常慢；

2、要获取所有的记录然后随机一个。要如何增加程序可使用的内存大小？

3、有些列出现的频率比别的列要高一些，这个随机算法不是很随机。

合理使用反模式：在数据量很小的时候，可以使用随机排序。

解决方案：没有具体的排序……

1、从1到最大值之间随机选择

select b1.* 
from Bugs as b1
join (select ceiling(rand()*(select max(bugId) from Bugs)) as randId) as b2
  on (b1.bugId = b2.bugId);

​ 因为id可能是不连续的，所以可能有时候无法查询到结果。

2、选择下一个最大值

select b1.* 
from Bugs as b1
join (select ceiling(rand()*(select max(bugId) from Bugs)) as randId) as b2
where  b1.bugId >= b2.bugId --为了避免id不存在，我们找上面那条id的后一条数据。
order by b1.bugId limit 1;

​ 当缝隙中是缝隙不是很大并且每个值都要被等概率选择的重要性不是很高时，可以考虑使用此方案。

3、索取所有的键值，随机选择一个，再使用这个随机选择的主键查找完整的记录。

4、使用偏移量选择随机行

计算总的数据行数，随机选择0到总行数之间的一个值，然后用这个值作为位移来获取随机行

​ 5、专有解决方案

每种数据库都提供专有的解决方案

结论：有些查询是无法优化的，换种方法试试看。

17. 可怜人的搜索引擎

目标：全文搜索

​ 使用SQL搜索关键字，同时保证快速和精确，依旧是相当地困难。

​ SQL的一个基本原理（以及SQL所继承的关系原理）就是一列中的单个数据是原子性的。

反模式：模式匹配

​ 使用Like 或者正则表达式。

缺点：（1）无法使用索引，进行全表遍历，非常耗时，性能极低。

（2）有时候会返回意料之外的结果。select * from bugs where description like '%one%'，

​ 返回结果可能是money、prone、lonely。

​ 正则表达式可能会为单词边界提供一个模式来解决单词的匹配问题:

SELECT * FROM Bugs WHERE description REGEXP '[[:<:]]one[[:>:]]';

如何识别反模式：当出现以下情况时，可能是反模式

1、如何在like表达式的2个通配符之间插入一个变量？

2、如何写一个正则表达式来检查一个字符串是否包含多个单词、不包含一个特定的单词，或者包含给定单词的任意形式？

3、网站的搜索功能在增加了很多文档进去之后慢的不可理喻。

合理使用反模式：

1、性能总是最重要的，如果一些查询过程很少执行，就不必要花很多功夫去对它进行优化

2、使用模式匹配操作进行很复杂的查询是很困难的，但是如果你为了一些简单的需求设计这样的模式匹配，它们能帮助你用最少的工作量获得正确的结果。

解决方案：使用正确的工具

最好的方案就是使用特殊的搜索引擎技术，而不是SQL。

另一个方案是将结果保存起来从而减少重复的搜索开销。

1、MySQL中的全文索引：可以再一个类型为Char、varchar或者Text的列上定义一个全文索引。然后使用Match函数来搜索。

2、Oracle中的文本索引：Context、Ctxcat、Ctxxpath、Ctxule。

3、SQL Server中的全文搜索：使用Contains()操作符来使用全文索引。使用之前需要通过复杂的步骤来配置。

4、PostgreSQL的文本搜索：提供一个复杂大可大量配置的方式来将文本转换为可搜索的词汇集合，并且让这些文档能够进行模式匹配搜索。

5、SQLite的全文搜索：使用SQLite的扩展组件来实现。

6、第三方搜索引擎：

（1）Sphinx Search：开源的搜索引擎，用于MySQL以及PostgreSQL来配套使用。

（2）Apache Lucene:是一个针对Java程序的成熟搜索引擎。

​ 7、实现自己的搜索引擎：

​ 使用反向索引方案：反向索引就是一个所有可能被搜索的单词列表。

（1）定义一个KeyWords表来记录所有用户搜索的关键字，然后定义一个交叉表来建立多对多的关系。

（2）将每个关键字和匹配的内容添加到交叉表中。

当有新的搜索单词，就使用like查询结果，并将结果保存到交叉表里，这样下次就不必like了。当有新的文档入库，就需要用触发器（或者定时）去填充交叉表。

结论：不必使用SQL来解决所有问题。

18. 意大利面条式查询

目标：减少SQL查询数据

反模式：使用一步操作解决复杂问题

使用一个查询来获得所有结果的最常见后果就是产生了一个笛卡尔积。导致查询性能降低。

SELECT p.product_id, 
  COUNT(f.bug_id) AS count_fixed,
  COUNT(o.bug_id) AS count_open,
FROM BugsProducts p
LEFT OUTER JOIN Bugs f ON (p.bug_id = f.bug_id AND f.status = 'FIXED')
LEFT OUTER JOIN Bugs o ON (p.bug_id = o.bug_id AND o.status = 'OPEN')
WHERE p.product_id = 1
GROUP BY P.product_id;

你碰巧知道对于给定的这个产品，有12个Bug被修复了，有7个Bug是打开的，因此，结果看上去很耐人寻味：

Product_id count_fixed count_open

1 84 84

如何识别反模式：当出现以下情况时，可能是反模式

1、为什么我的求和、技术返回的结果异常地大？

2、我一整天都在和整个变态的查询语句做斗争。SQL并不是那么的难写，如果你和单条SQL查询纠结了很长时间，

​ 应该重新考虑实现方式。

3、试试再加一个Distinct去除重复数据。

解决方案：分而治之

1、用多个sql语句查询得到数据，再进行整合。或者union多个sql的结果。

2、使用SQL语句批量生成多个动态SQL语句。

结论：尽管SQL支持用一行代码解决复杂的问题，但是也别做不切实际的事情。

19. 使用*号，隐式的列

目标：减少输入

反模式：捷径会让你迷失方向

使用通配符和未命名的列能够达到减少输入的目的，但是这个习惯会带来一些危害。

1、破坏代码重构：增加一列后，使用隐式的Insert插入语句报错；

2、查询中使用通配符，影响性能和扩展性。大量数据在网络之间传输，可能会造成阻塞。

如何识别反模式：当出现以下情况时，可能是反模式

1、程序由于还使用老的列名而挂掉。

改变了数据库里的一张表，添加、删除、重命名列，或者改变列的顺序。但没能更新全部使用到这张表的代码。

2、花了几天时间终于找到了网络的瓶颈，终于减小了到数据库服务器的庞大的通信量。

合理使用反模式：

1、在开发或者测试环境下可以使用通配符，在生成环境下尽量不要使用。

2、可以在联结查询中的每个独立的表使用通配符。在通配符之前加上表名或者别名作为前缀。

3、如果你的程序需要在增加、删除、重命名或者重新配置列时依旧能自动适应及调整，

那最好还是使用通配符。

解决方案：明确列出列名

结论：无论如何都不要使用通配符

20. 明文密码

目标：恢复或重置密码

反模式：使用明文存储密码

1、存储密码

使用明文存储密码或者在网络上传递密码是不安全的。

​ 如果攻击者截取到你用来插入（或者修改）密码的sql语句，就可以获得密码。

黑客获取密码的方式有很多种：

（1）在客户端和服务器端数据库交互的网络线路上接货数据包。比如使用Wireshark黑客软件。

（2）在数据库服务器上搜索SQL的查询日志。

（3）从服务器或者备份介质上读取数据库备份文件内的数据。

2、验证密码：同上。

3、在Email中发送密码：Email的收发都需要经由网络层传输，数据可能会在其他的路由节点上被黑客劫持。

如何识别反模式：当出现以下情况时，可能是反模式合理使用反模式：

1、能够恢复你的密码；

2、将密码通过邮件以明文或可逆转的加密的格式发给你的程序。

合理使用反模式：

1、你的程序可能需要使用密码来访问一个地第三方的服务，这意味着你的程序可能是一个客户端，

​ 必须使用可读的格式来存储这个密码。最好的做法是使用一些程序能够解码的加密方法来存储，

而不是直接使用明文的方式存储在数据库中。

2、并不是所有的程序都有被攻击的风险，也不是所有的程序都有敏感的程序需保护的信息。

内文通讯的程序，只使用认证机制就可以了。

解决方案：

1、先加密再存储

​ （1）哈希是指将输入字符串转换成为另一个新的、不可识别的字符串的函数。

​ 哈希算法是不可逆的。

（2）在SQL中使用哈希

哈希函数并是标准的SQL语言，因此你可能要依赖于所使用的数据库提供的哈希扩展。

​ insert into Accounts(AccountId,AccountName,password)

​ values(123,'billKarwin', SHA2('xyzzy'));---MySQL6.0.5的扩展函数

​ （3）给哈希加料

使用哈希值替换原来的明文密码，但是黑客仍然能够破解你的哈希值。通过他们预先准备好的自己的数据库，里面存储的可能的密码与对应的哈希值，然后比较久可以找出明文密码。预防这种“字典攻击”的一种方法是给你的密码加密表达式加点佐料。具体方法是在将用户密码传入哈希函数进行加密之前，将其和一个无意义的字符串拼接在一起。

​ SHA2('password') = '5exxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxd8';

​ SHA2('password-0xT!sp9') = '72xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx5b';

​ 佐料的合理长度应该是8个字节。

（4）在SQL中隐藏密码

​ 只要不将明文密码放到SQL查询语句中，就能避免泄露。

​ 具体做法：在程序代码中生成密码的哈希字符串，然后在SQL查询中使用哈希串。

（5）在网络传输中，还有一个地方是攻击者有机会接货网络数据包的：在用户的浏览器和网站服务器之间。

​ 当用户提交了一个登录表单时，浏览器将用户的密码以明文方式发送到服务器端，随后服务器端才能

​ 使用这个密码进行哈希运算。

​ 解决方法：在用户的浏览器发送表单数据之前就进行哈希运算。

​ 但是这个方案也有一些不足的地方，就是你需要再进行正确的哈希运算之前，还要通过别的途径来获得和

​ 这个密码相关联的佐料。这种方案是在从浏览器向服务器端提交表单密码时，使用安全的HTTP(https)链接。

2、重置密码而不是恢复密码

21. SQL注入

目标：编写SQL动态查询，防止SQL注入

通常所说的“SQL动态查询”是指将程序中的变量和基本SQL语句拼接成一个完整的查询语句。

反模式：将未经验证的输入作为代码执行

当向SQL查询的字符串中插入别的内容，而这些被插入的内容以你不希望的方式修改了查询语法时，SQL注入就成功了。

传统的SQL注入案例中，所插入的内容首先完成了一个查询，然后再执行第二个完整的查询逻辑比如：@bugId的值是1234;Delete from Bugs，最后的SQL语句变成如下格式：

​ Select * from Bugs where bugId = 1234;Delete from Bugs

​ 1、意外无处不在

​ 由于字符串引起的语法错误，SQL语句是不会被执行的。

风险较大的是产生的SQL没有任何语法错误，并且以一种你所不希望的方式执行。

2、对Web安全的严重威胁

当攻击者能够使用SQL注入操控你的SQL查询语句时，就变成了一个巨大的威胁。

​ 通常做法是在参数后插入额外的字符串，改变对应SQL语句的意义，例如：

​ Update Account

​ set password = SHA2('zyxzy')

​ where accountId = 123 or true --在传入accountId参数等于123的后面，添加了 or true

​ 理解SQL注入的关键，也是如何防止SQL注入的关键：SQL注入是通过在SQL语句被数据库解析之前，

以修改其语法的形式工作的。只要在解析语句之前插入动态部分，就存在SQL注入的风险。

3、寻找解决方法

（1）转义：对传入的参数字符串进行转义操作，使它们不至于成为字符串的结束符。

​ 使用2个连续的单引号或者反斜杠来转义。实现原理是在将应用程序中的数据插入到SQL语句之前

​ 就进行转换。这种技术能减少由于动态内容中不匹配是引号做造成的SQL注入的风险，但在非字符串

​ 内容的情况下，这种技术就会失效。

（2）查询参数：查询参数的做法是在准备查询语句的时候，在对应参数的地方使用“参数占位符”。随后，

​ 在执行这个预先准备好的查询时提供一个参数。

​ 该方法的确是应对SQL注入的强劲解决方案，但是这还不是一个通用的解决方案，因为查询参数总是被视为是一个字面值。

（a）多个值的列表不可以当成单一参数；

（b）表名无法作为参数；

​ （c）列名无法作为参数；

​ （d）SQL关键字无法作为参数；

（3）存储过程：存储过程是包含固定的SQL语句，这些语句在定义这个存储过程的时候被解析的。

​ 在存储过程也可以使用SQL动态查询的，这样也存在安全隐患。

（4）数据访问框架ORM：对于所有允许你使用字符串方式传入SQL语句的框架来说，都无法抵御SQL注入的攻击。

如何识别反模式：几乎所有的数据库应用程序都动态地构建SQL语句，如果使用拼接字符串的形式或者将变量插入到字符串的

方法来构建SQL语句，这样的sql语句就会受到SQL注入攻击的威胁。

合理使用反模式：没有任何理由使用反模式

解决方案：

1、过滤输入内容，将所有不合法的字符从用户输入中剔除掉。

2、参数化动态内容：如果查询中的变化部分是一些简单的类型，应该使用查询参数将其和SQL表达式分离。

​ 如果是在RDBMS解析完SQL语句之后才插入这个参数值，没有哪种SQL注入的功能能改变一个参数化了查询的语法结构。

​ 即使攻击者尝试使用带有恶意的参数值，诸如123 or true ，关系型数据库管理系统也会将这个字符串当成一个完整的值插入

​ Update Account

​ set password = SHA2('zyxzy'

​ where accountId ='123 or true' --当做一个完整的字符串而不会造成威胁

​ 3、给动态输入的值加引号

​ 参数查询通常来说是最好的解决方案，但是在有些特殊的情况下，参数的占位符会导致查询优化器无法选择使用

​ 哪个索引来进行优化查询。

​ 4、找个可靠的人来帮你审查SQL语句

​ 在检查代码是否包含SQL注入风险的时候，参考一下几点：

（1）找出所有使用了程序变量、字符串链接或者替换等方法组成的SQL语句。

（2）跟踪在SQL语句中使用的动态内容的来源。找出所有的外部输入，比如用户输入、文件、系统环境、网络服务、 第三方代码，甚至于从数据库中获取的字符串。

​ （3）假设任何外部内容都是潜在的威胁，对于不受信任的内容都要进行过滤、验证或者使用数组映射的方式来处理。

（4）在将外部数据合并到SQL语句时，使用查询参数，或者用稳健的转义函数预先处理。

（5）在存储过程的代码以及任何其他使用SQL动态查询语句的地方都做同样的检查。

结论：让用户输入内容，但永远别让用户输入代码

22. 伪键洁癖，整理数据

目标：整理数据，使不连续的主键Id数据记录变的连续。

img

反模式：填充断档的数据空缺。

1、不按照顺序分配编号

​ 在插入新行时，通过遍历表，找到的第一个未分配的主键编号分配给新行，来代替原来自动分配的伪主键机制。

​ 使用Select Max(Id) + 1 这种查询语句，会出现并发访问的问题。

2、为现有数据行重新编号：通常做法是找到主键最大的行，然后用最小的未被使用的值来更新它。

缺点：（1）SQL语句比较麻烦；

（2）必须同时更新所有引用了你重新分配了主键的行的子记录；

（3）无法避免产生新的断档。

​ 3、制造数据差异

如果别的外部系统依赖于数据库中的主键来定义数据，那么你的更新操作就会导致那个系统中的引用失效。

重用主键不是一个号的注意，因为断档往往是由于一些合理的删除或者回滚数据所造成的。

别因为那些伪键看上去是没用的而重新分配他们。

如何识别反模式：当出现以下情况时，可能是反模式

1、在我回滚了一个插入操作后，要怎么重用囊而自动生成的标识？

​ 伪键一旦生成后不会回滚。如果非要回滚，RDBMS就必须在一耳光事务的声明周期内生成一个伪键，

​ 而这在多个客户端并发地插入数据时，会导致竞争或者死锁。

2、bugId为3的这条记录怎么了？

3、如何找到第一个未使用的Id？

4、自增长整形id的数字标识如果达到了最大值怎么办？

合理使用反模式：

没有理由要去改变伪键的值，由于它的值本身并没有什么重要的意义。如果这个主键列有实际的意义，那么这就是一个自然键，而不是伪键。

解决方案：

主键的值必须是唯一且非空的，因而你才能使用主键来唯一确定一行记录，但这是主键的唯一约束，

​ 他们不需要一定非得是连续值才能用来标记行。

1、定义行号：使用Row_Number()或者Limit等关键字来实现；

2、使用Guid：数据库全局唯一标识符。

​ 优点：（1）可以再多个数据库服务器上并发地生成伪键，而不用担心生成同样的值。

​ （2）不存在断档的问题。

​ 缺点：（1）Guid的值太长，不方便输入；

（2）Guid的值是随机的，因此找不到任何规则或者依靠最大值来判断哪一行的最新插入的；

（3）Guid的存储需要16字节，这比传统的4自检整形伪键占用更多的控件，并且查询的速度更慢

结论：将伪键当做行的唯一性标识，但它们不是行号。

23. 非礼勿视

目标：写更少的代码

反模式：无米之炊 忽略数据库API的返回值，将程序代码跟SQL混在一起
​ 1.没有诊断的诊断 在多条sql顺序执行过程中，最好对结果进行诊断，保证错误能够快速定位。
​ 2.字里行间 花费大量时间调试生成sql字符串的代码

解决方案：优雅的从错误中恢复
​ 1.保持节奏 检查数据库API的返回状态和异常。
​ 2.回溯你的脚步。sql语句记录，输出，调试。发现并解决代码中的问题已经很苦难了，就别再盲目的干了。

24. 外交豁免权

​ 目标：最佳实践 使用版本控制工具管理源代码，编写单元测试脚本；编写文档，代码注释。
​ 反模式：将SQL视为二等公民。

25. 魔豆

目标: 简化MVC的模型

反模式: 模型仅仅是活动记录

Robert L. Glass认为：

80%的软件工作是智力活动。相当大的比例是创造性的活动，很少是文书性的工作。

MVC架构指：模型-视图-控制器。我们使用MVC就是同时使用设计模式和软件框架。这是拆分程序逻辑的技术。

• View：处理输出。
• Controller：接收用户输入，处理响应逻辑，委托给Module执行操作，将结果传递给View。
• Module：程序核心，包括输入验证、业务逻辑，与数据库交互等。

MVC使用者通常犯错在：仅把Module当作DAO（数据访问对象）。

解决方案: 模型包含活动记录

不要把SQL查询语句传给Module对象，Module对象应该囊括了所有它需要的查询。

将复杂的查询代码写在Module对象里，并作为接口暴露出来。遵循DRY原则。

分离Module和DAO。Module和DAO/Active Record的关系是包含（has-a）而不是继承（is-a）。

Module负责创建DAO。一个聚集了一系列DAO的Module应该负责创建这些对象。Controller和View只使用Module暴露出来的接口，而不要处理与数据交互的逻辑（如传递SQL给Module，以求其返回执行结果）。

Module的接口应该是直接的（与业务逻辑直接相关，而不是过于抽象的接口），而不是数据库物理结构或CRUD操作。ADO/Active Record可以提供如find(), first(), insert(), save()之类的接口，但Module层的接口不要这么干。

将Module类和DAO解耦，并可以为一个DAO设计多个模型类。

框架很难给Module提供一个通吃的解决方案。