1. 機器學習

• 評估假設(shè)

  一旦我們通過以下方式在我們的預測中做了一些故障排除：

  • 獲得更多培訓示例
  • 嘗試更小的功能集
  • 嘗試其他功能
  • 嘗試多項式特征
  • 增加或減少λ

  我們可以繼續(xù)評估我們的新假設(shè)。

  假設(shè)對于訓練樣例可能具有低誤差，但仍然不準確（因為過擬合）。因此，為了評估一個假設(shè)，給定一個訓練樣本數(shù)據(jù)集，我們可以將數(shù)據(jù)分成兩組：訓練集和測試集。通常情況下，訓練集包含70％的數(shù)據(jù)，測試集是剩下的30％。

  [圖片上傳失敗...(image-ec2eb7-1525879347213)]

  • 模型選擇和訓練/驗證/測試集

    我們的數(shù)據(jù)集分成三組的一種方法是：

    • 訓練集：60%
    • 交叉驗證集：20％
    • 測試集：20％

    現(xiàn)在我們可以使用以下方法為三個不同的集合計算三個單獨的錯誤值：

    1. 使用每個多項式的訓練集來優(yōu)化Θ中的參數(shù)。
    2. 使用交叉驗證集找出具有最小誤差的多項式度d。
    3. 使用帶有測試集的估計泛化錯誤 Jtest(Θ(d))，（d =具有較低誤差的多項式的θ）;

    這樣，多項式d的程度還沒有使用測試集進行訓練。

  • 診斷偏差(欠擬合)與方差(過擬合)

    [圖片上傳失敗...(image-b38e0e-1525879347213)]

  • 正則化和偏向/方差

    [圖片上傳失敗...(image-79b699-1525879347213)]

    在上圖中，我們看到了λ 升增加，我們的適應變得更加僵硬。另一方面，如λ 接近0，我們傾向于過度擬合數(shù)據(jù)。那么我們?nèi)绾芜x擇我們的參數(shù)λ 得到它“恰到好處”？為了選擇模型和正則化項λ，我們需要：

    1. 創(chuàng)建一個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. 創(chuàng)建一個不同程度的模型或任何其他變體。
    3. 迭代通過 升s和每個 λ 通過所有的模型來學習一些Θ.
    4. 使用學習的Θ（用λ計算）計算交叉驗證錯誤 JCV(Θ) 沒有正則化或λ= 0。
    5. 選擇交叉驗證集合中產(chǎn)生最低錯誤的最佳組合。
    6. 使用最佳組合 Θ and λ, apply it on Jtest(Θ) 看看它是否有一個很好的概括性的問題。

  • 學習曲線

    在少數(shù)幾個數(shù)據(jù)點（如1,2或3）上訓練算法將很容易產(chǎn)生0個錯誤，因為我們總是可以找到一個接近這些點數(shù)的二次曲線。因此：

    • 隨著訓練集變大，二次函數(shù)的誤差增加。
    • 經(jīng)過一定的m或訓練集大小后，誤差值將平穩(wěn)。

    經(jīng)歷高偏見：

    [圖片上傳失敗...(image-4de8af-1525879347213)]

    經(jīng)歷高方差：

    [圖片上傳失敗...(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.

    診斷神經(jīng)網(wǎng)絡

    • 一個參數(shù)較少的神經(jīng)網(wǎng)絡容易出現(xiàn)不足。這也是計算更便宜。
    • 具有更多參數(shù)的大型神經(jīng)網(wǎng)絡容易過度擬合。這在計算上也是昂貴的。在這種情況下，您可以使用正則化（增加λ）來解決過度擬合問題。

    使用單個隱藏層是一個很好的默認開始。您可以使用交叉驗證集在許多隱藏層上訓練您的神經(jīng)網(wǎng)絡。然后您可以選擇性能最好的一個。

    模型復雜性的影響：

    • 低階多項式（低模型復雜度）具有高偏差和低方差。在這種情況下，模型不太一致。
    • 高階多項式（高模型復雜度）非常適合訓練數(shù)據(jù)，測試數(shù)據(jù)極其糟糕。這些對訓練數(shù)據(jù)的偏倚低，但差異很大。
    • 實際上，我們希望選擇一個介于兩者之間的模型，這個模型可以很好地推廣，但是也可以很好地擬合數(shù)據(jù)。

  • 優(yōu)先處理什么工作

    系統(tǒng)設(shè)計示例：

    給定一個電子郵件數(shù)據(jù)集，我們可以為每個電子郵件構(gòu)建一個向量。這個向量中的每個條目代表一個單詞。該矢量通常包含10,000到50,000個條目，通過查找我們的數(shù)據(jù)集中最常用的單詞來收集。如果在電子郵件中找到一個單詞，我們將分配它的相應條目1，否則如果沒有找到，條目將是0.一旦我們已經(jīng)準備好了所有的x向量，我們將訓練我們的算法，最后，我們可以用它來分類電子郵件是否是垃圾郵件。

    ?

    那么你怎么能花時間來提高這個分類器的準確度呢？

    • 收集大量數(shù)據(jù)（例如“蜜罐”項目，但并不總是工作）
    • 開發(fā)復雜的功能（例如：在垃圾郵件中使用電子郵件標題數(shù)據(jù)）
    • 開發(fā)算法以不同的方式處理您的輸入（識別垃圾郵件中的拼寫錯誤）。

    很難判斷哪個選項最有用。

  • 錯誤分析

    解決機器學習問題的推薦方法是：

    • 從一個簡單的算法開始，快速實施它，并盡早在交叉驗證數(shù)據(jù)上進行測試。
    • 繪制學習曲線來決定是否有更多的數(shù)據(jù)，更多的功能等可能有所幫助。
    • 手動檢查交叉驗證集中示例的錯誤，并嘗試找出發(fā)生大部分錯誤的趨勢。

  • FI值

    如果我們定義一個測試集中，正樣本個數(shù)為P, 負樣本個數(shù)為N, 那么我們可以知道：P=TP+FN, N=TN+FP
    $$
    Recall=TP/(TP+FN)=TP/P
    $$

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

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

    可以看到，recall 體現(xiàn)了分類模型H對正樣本的識別能力，recall 越高，說明模型對正樣本的識別能力越強，precision 體現(xiàn)了模型對負樣本的區(qū)分能力，precision越高，說明模型對負樣本的區(qū)分能力越強。F1-score 是兩者的綜合。F1-score 越高，說明分類模型越穩(wěn)健。

• 支持向量機

  支持向量機從本質(zhì)上來講，就是最大間隔分類器。

  這邊從邏輯回歸引出支持向量機，以及它最重要的思路——最大間隔。

  考慮邏輯回歸：

  img

  我們看下在SVM中對costfunction的改變 ：

  [圖片上傳失敗...(image-a8a915-1525879347213)]

  • Large Margin Intuition

    這里寫圖片描述

    ?

    SVM通過尋找分類中淡黃色背景的那條線作為邊界，而不是其余滿足條件的邊界，因此SVM又被稱為大間隔分類器。

    這里寫圖片描述
    這里寫圖片描述

  • The mathematics behind large margin classification
    下面我們從數(shù)學角度看一下SVM過簡化問題，我們知道要求的最小值為||θ||的最小值，即θ的范數(shù)最小值

    這里寫圖片描述

    下面看一下限制條件代表的含義，通過高中數(shù)學，我們知道兩個向量相乘的幾何含義如下

    這里寫圖片描述

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

    假如選擇了下面圖中的綠色線作為邊界，我們會發(fā)現(xiàn)p(i)比較小，這樣不能得出||θ||的最小值

    這里寫圖片描述

    如果選擇下面的綠色線作為邊界，我們可以得到較小的||θ||值

    這里寫圖片描述

  • 核函數(shù)

    [圖片上傳失敗...(image-43c0a6-1525879347213)]

    通過下面的圖我們可以看出δ2對函數(shù)圖形的改變

    這里寫圖片描述
    • 關(guān)于參數(shù)對算法的影響 :

    大C:低偏差，高方差（對應低λ）

    小C:高偏差，低方差（對應高λ）

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

    • 使用SVM步驟 ：
    這里寫圖片描述

2. SQL反模式

10. 取整錯誤

目標：使用小數(shù)取代整數(shù)

反模式：使用Float類型

          根據(jù)IEEE754標識，float類型使用二進制格式編碼實數(shù)數(shù)據(jù)。

          缺點：

(1) 舍入的必要性： 并不是所有的十進制中描述的信息都能使用二進制存儲，出于一些必要的因，浮點數(shù)通常是舍入到了一個非常接近的值。

舉例：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 --這個才能正確查出數(shù)據(jù)！

? 59.95，在二進制中存儲了最接近59.95的值，用十進制表示就是59.950000762939

（2）在SQL中使用FLOAT

? FLOAT類型的列中實際存儲的數(shù)據(jù)可能并不完全等于它的值。如果將這個值擴大十億倍，就能看出其中的區(qū)別

? 示例：上例中的59.95

? 變通的方法就是將浮點數(shù)看做“近似相等”，即兩個值之間的差值足夠小就認為它們相等。

?

**如何識別反模式**：任何使用Float、Real或者Double Percent類型的設(shè)計都有可能是反模式

?

**合理使用反模式**：如果要存儲的值取值范圍很大，大于integer、numeric的范圍，那只能用float了?？茖W計算類的程序通常使用float。

?

**解決方案**：使用numeric、decimal類型

                使用numeric、decimal代替float，他們不會對存儲的有理數(shù)進行舍入，因此select * from A where rate=59.95會返回記錄。

               示例: ALTER TABLE Accounts ADD COLUMN hourly_rate NUMERIC(9, 2);    -- 9代表總位數(shù)，2代表精確度

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

**結(jié)論**：盡可能不要使用浮點數(shù)

11. 每日新花樣

目標：限定列的有效值，將一列的有效字段值約束在一個固定的集合中。類似于數(shù)據(jù)字典。

反模式：在列定義上指定可選值

? 1、 對某一列定義一個檢查約束項，這個約束不允許往列中插入或者更新任何會導致約束失敗的值：

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

? 2、使用域或者用戶自定義類型（UDT）等方法。即使用這些方法約束某一列只能接受一個特定集合的數(shù)據(jù)，并且能很方便的將這約束應用到整個域上。但這些特性并沒有得到大多數(shù)關(guān)系數(shù)據(jù)庫的支持。

3、使用觸發(fā)器：編寫一個觸發(fā)器，當修改指定列的內(nèi)容時觸發(fā)，將被修改的值和允許輸入的值進行匹配，

? 如果不符合則產(chǎn)生一個錯誤中斷操作。

缺點：

? 1、 無法查詢出所有的約束值來供上層應用程序使用。

? 不能用select distinct status from Bugs，因為可能有的status目前還沒有存儲。維護不好的話，還有可能造成列表和數(shù)據(jù)庫存儲的值，不一致。

2、增加新的約束值，需要修改數(shù)據(jù)庫定義或者觸發(fā)器。

3、廢棄或修改某個值，可能要修改大量的數(shù)據(jù)，風險很大也不合理。

4、可移植性差，check約束、域，或者UDT在各種數(shù)據(jù)庫中的支持形式并不統(tǒng)一。

如何識別反模式：當出現(xiàn)以下情況時，可能是反模式

1、我們不得不將數(shù)據(jù)庫下線，才能在程序中加入一個新的選項。

2、這個Status列可以填入這些候選值中的一個。我們不應該改變這個后選值列表。

3、程序代碼中關(guān)于業(yè)務規(guī)則的選項列表和數(shù)據(jù)庫中的值又不同步了。

合理使用反模式：

1、在候選值幾乎不變化的時候可以使用。

2、存儲沒有業(yè)務邏輯且不需要改變的候選值時非常方便。

? 比如存儲一對二選一切相互對立的值：Left/Right、On/Off等。

3、Check約束可以再更多的場景下使用，比如用來檢測一個時間區(qū)間中start永遠小于end。

解決方案：創(chuàng)建一張檢查表（類似于字典表），每一行包含一個允許在列中出現(xiàn)的候選值，然后在目標表中定義一個外鍵約束。

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

結(jié)論：在驗證固定集合的候選值時使用元數(shù)據(jù)；

? 在驗證可變集合的候選值時使用數(shù)據(jù)。

12. 幽靈文件

目標：存儲圖片或其他多媒體大文件

反模式：圖片存儲在數(shù)據(jù)庫外的文件系統(tǒng)中，數(shù)據(jù)庫表中存儲文件的對應的路徑和名稱。

缺點：

1、文件不支持Delete操作。使用SQL語句刪除一條記錄時，對應的文件不會被刪除，需要使用額外的程序來操作。

2、文件不支持事務隔離

3、文件不支持回滾操作

4、文件不支持數(shù)據(jù)庫備份工具：備份工具不知道如何將通過路徑引用的哪些文件也包含在備份操作當中。

5、文件不支持sql的訪問權(quán)限設(shè)置

6、文件不是sql數(shù)據(jù)類型：字段中存儲的是文件的路徑，數(shù)據(jù)庫不會驗證這個字符串是否是一個有效的路徑，也不會驗證對應的文件是否存在。任何將這個字符串作為路徑處理的邏輯都依賴于你的程序邏輯。

如何識別反模式：典型的使用反模式的項目通常沒有考慮以下幾個或者全部問題

1、數(shù)據(jù)備份和回復的過程是怎樣的？怎么對一個備份進行驗證？

? 你有沒有在一個干凈的系統(tǒng)或者別的系統(tǒng)上對備份回復的數(shù)據(jù)進行測試？

2、圖片文件堆積在那里，還是當他們孤立的時候就從系統(tǒng)中移除？移除他們的過程是怎么樣的？

? 這是一個自動的還是手動的過程？

3、系統(tǒng)中的哪些用戶有權(quán)限查看這些圖片？進入權(quán)限是怎么限制的？當用戶請求查看他們無權(quán)查看的圖片時會發(fā)生什么？

4、我能撤銷對圖片的變更嗎？如果能，是應用程序來回復圖片之前的狀態(tài)嗎？

合理使用反模式：如下是將圖片或者大文件存儲在數(shù)據(jù)庫之外的好理由

1、這個數(shù)據(jù)庫在沒有圖片的時候能精藝很多，因為圖片相比于簡單的數(shù)據(jù)類型來說大很多；

2、當不包含圖片時備份數(shù)據(jù)庫會更快并且備份的文件更小。你必須額外的執(zhí)行一次文件備份，

? 但這些比備份一個大型數(shù)據(jù)庫要更容易管理；

3、如果圖片是存儲在數(shù)據(jù)庫之外的文件系統(tǒng)中，對圖片的預覽或者編輯就能夠使用更簡單直接的處理方式。

如果這些圖片存在文件系統(tǒng)中的好處是重要的，那么可以將大文件存儲在數(shù)據(jù)庫之外。

解決方案：在需要的時候使用BLOB類型。

所有的數(shù)據(jù)庫產(chǎn)品都支持BLOB類型，支持你存儲任何二進制數(shù)據(jù)。

注: 存儲在數(shù)據(jù)庫之外的數(shù)據(jù)不由數(shù)據(jù)庫管理

13. 使用索引

目標：優(yōu)化性能

? 改善性能最好的技術(shù)就是在數(shù)據(jù)庫中合理地使用索引。

索引也是數(shù)據(jù)結(jié)構(gòu)，它能使數(shù)據(jù)庫將指定列中的某個值快速定位在相應的行。

反模式：無規(guī)劃的使用索引

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

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

（1）大多數(shù)數(shù)據(jù)庫會自動地位主鍵建立索引，因此額外再定義一個索引就是冗余。這個額外的索引并無任何好處，它只會成為額外的開銷。

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

（3）使用組合索引是一個很好的選擇，但是大部分創(chuàng)建的組合索引都是冗余或者很少使用。

? 組合索引的順序也很重要：在查詢條件、聯(lián)合條件或者排序規(guī)則上使用索引時按照從左到右的順序。

3、執(zhí)行了一些讓索引無能為力的查詢

Like、表達式，都可能使索引無效。

如何識別反模式：當出現(xiàn)以下情況時，可能是反模式

1、這是我的查詢語句，怎樣使它更快？

2、我在每個字段上都定義了索引，為什么它沒有變的更？

3、我聽說索引會使數(shù)據(jù)庫變慢，所以我不使用它。

合理使用反模式：

分離率是衡量數(shù)據(jù)庫索引的一個指標。分離率越高索引效率就越高。需要時刻關(guān)注你的數(shù)據(jù)庫中索引的分離率，并且拋棄那些低效的索引。

解決方案：MENTOR你的索引

1、Measure測量：大多數(shù)數(shù)據(jù)庫都提供了一些方法來記錄執(zhí)行SQL查詢的耗時，因此可以來定位最耗時的查詢。

2、Explain解釋：測量之后，分析查詢變慢的原因。

? 每個數(shù)據(jù)庫都使用一種優(yōu)化工具為每次查詢選擇最合適的索引，并生產(chǎn)分析報表，稱之為“查詢執(zhí)行計劃（QEP）”。

3、Nominate挑選：有了查詢優(yōu)化工具的QEP報表，應該仔細地查找那些沒有使用索引的查詢操作。

4、Test測試：創(chuàng)建完索引之后，需要重新測試跟蹤那些查詢，確認你的改動確實提升了性能。

5、Optimize優(yōu)化：索引是小型的、頻繁使用的數(shù)據(jù)結(jié)構(gòu)，因為很適合將他們常駐在內(nèi)存中。內(nèi)存操作的性能是磁盤I/O操作的好幾倍。

? 數(shù)據(jù)庫服務器允許你配置緩存所需要的系統(tǒng)內(nèi)存大小。

6、Rebuild重組：索引在平衡的時候其效率最高，當你更新或者刪除記錄時，索引就逐漸變得不平衡。

? 要想最大限度地使用索引，因此要定期對索引進行維護。

結(jié)論：了解你的數(shù)據(jù)庫，了解你的查詢，然后MENTOR你的索引。

14. 對未知的恐懼

目標：辨別并使用Null值

反模式：將Null值作為普通的值，反之亦然

1、在表達式中使用Null：

? Null值與空字符串是不一樣的，Null值參與任何的加、減、乘、除等其他運算，結(jié)果都是Null；

? Null值與False也不同。And、Or和Not三個bool操作如果設(shè)計Null，結(jié)果很迷惑。

2、搜索運行為空的列：任何與Null的比較逗返回“未知”，既不是True，也不是False。

? 在Where表達式中只能使用 Is Null 或者 Is Not Null，其他操作都查詢不到結(jié)果。

3、不能使用Null作為參數(shù)傳入查詢表達式中。

要避免上述問題，可以使用約束，設(shè)置列禁止Null。存儲值必須是有意義的內(nèi)容。

如何識別反模式：當出現(xiàn)以下情況時，可能是反模式

1、我如何將沒有值（Null）的列取出來？

2、將字符串與Null進行拼接操作，結(jié)果返回Null

合理使用反模式：

使用Null并不是反模式，反模式是將Null作為一個普通值處理或者使用一個普通的值來取代Null的作用。有一種情況可以講Null視為普通值，就是導入或者導出數(shù)據(jù)的時候。

解決方案：將Null值視為特殊值

1、在標量表達式中使用null

進行=、<>、+、||操作時，只要有一個null值，結(jié)果就為null。

2、在布爾表達式中使用null

布爾表達式中，只有：null and false結(jié)果為false，null or true結(jié)果為true，其它情況結(jié)果都是null。

3、檢索null值

使用 is null、is not null

4、聲明not null列

有時候可以通過使用default值來避免null，但有時候卻不可以這么做。

5、使用動態(tài)默認值

SqlServer中的Coalesec()與isnull()函數(shù)

15. 模棱兩可的分組

目標：查詢得到每組的max（或者min等其他聚合函數(shù)）值，并且得到這個行的其他字段

反模式：引用非分組列

1、單值規(guī)則：跟在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字句中出現(xiàn)的列能夠保證他們在每一組都只有一個值，無論這個組匹配多少行；

? Max()等表達式也能保證每組都返回單一的值，即傳回參數(shù)中的最大值。

如何識別反模式：當輸入一個違背了單值規(guī)則的查詢時，會立刻返回給你一個錯誤。數(shù)據(jù)庫會返回不同的錯誤信息。

在SQLite和MySQL中，有歧義的列可能包含不可預測的和不可靠的數(shù)據(jù)。

合理使用反模式：

沒有歧義的關(guān)系叫做“功能依賴”，最常見的就是表的主鍵和對應的值。

解決方案：使用無歧義的列

1、只查詢功能依賴的列：將有歧義的列排除在查詢之外。

2、使用關(guān)聯(lián)子查詢：關(guān)聯(lián)子查詢會引用外連接查詢，并且根據(jù)外聯(lián)結(jié)果查詢中的每一條記錄最終返回不同的結(jié)果。

? 關(guān)聯(lián)子查詢的性能不是最好的，因為外聯(lián)結(jié)查詢結(jié)果中的每一條記錄都會執(zhí)行一遍關(guān)聯(lián)的子查詢。

3、使用衍生表：使用衍生表來執(zhí)行子查詢，先得到一個臨時的結(jié)果，然后用這個臨時表和原表進行連接查詢。

? 性能相比子查詢更好一些。但是數(shù)據(jù)庫必須將臨時表得到的記錄存在一張臨時表中，因此這個方案也不是最好的。

4、使用Join：創(chuàng)建一個聯(lián)結(jié)查詢區(qū)匹配哪些可能不存在的記錄。這樣的查詢結(jié)果被稱為外連接查詢。該方案使用與針對大量數(shù)據(jù)查詢并且可伸縮性比較關(guān)鍵時。能更好的適應數(shù)據(jù)量的變化，但是難以理解與維護。

5、對額外的列使用聚合函數(shù)。只有確定最新的bug_id對應的Bug的日期也是最新的時候，才能使用這個方案，也就是說，Bug是按照時間順序提交的。

6、鏈接同組所有值：MySQL與SQLite提供了一個叫做Group_Contract（）函數(shù)，能將這一組中的所有的值連在一起作為單一值返回，多個值之間用逗號分隔。其他數(shù)據(jù)庫不支持該函數(shù)。

結(jié)論：遵循單值規(guī)則，避免獲得模棱兩可的查詢結(jié)果。

16. 使用隨機數(shù)排序

目標：隨機排序，使用高效的SQL語句查詢獲取隨機數(shù)據(jù)樣本。

反模式：使用RAND()隨機函數(shù)

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

缺點：無法利用索引，每次選擇的時候都不同且不可預測。進行全表遍歷，性能極差。

如何識別反模式：當出現(xiàn)以下情況時，可能是反模式

1、在SQL中，返回一個隨機行速度非常慢；

2、要獲取所有的記錄然后隨機一個。要如何增加程序可使用的內(nèi)存大??？

3、有些列出現(xiàn)的頻率比別的列要高一些，這個隨機算法不是很隨機。

合理使用反模式：在數(shù)據(jù)量很小的時候，可以使用隨機排序。

解決方案：沒有具體的排序……

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可能是不連續(xù)的，所以可能有時候無法查詢到結(jié)果。

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的后一條數(shù)據(jù)。
order by b1.bugId limit 1;

? 當縫隙中是縫隙不是很大并且每個值都要被等概率選擇的重要性不是很高時，可以考慮使用此方案。

3、索取所有的鍵值，隨機選擇一個，再使用這個隨機選擇的主鍵查找完整的記錄。

4、使用偏移量選擇隨機行

計算總的數(shù)據(jù)行數(shù)，隨機選擇0到總行數(shù)之間的一個值，然后用這個值作為位移來獲取隨機行

? 5、專有解決方案

每種數(shù)據(jù)庫都提供專有的解決方案

結(jié)論：有些查詢是無法優(yōu)化的，換種方法試試看。

17. 可憐人的搜索引擎

目標：全文搜索

? 使用SQL搜索關(guān)鍵字，同時保證快速和精確，依舊是相當?shù)乩щy。

? SQL的一個基本原理（以及SQL所繼承的關(guān)系原理）就是一列中的單個數(shù)據(jù)是原子性的。

反模式：模式匹配

? 使用Like 或者正則表達式。

缺點：（1）無法使用索引，進行全表遍歷，非常耗時，性能極低。

（2）有時候會返回意料之外的結(jié)果。select * from bugs where description like '%one%'，

? 返回結(jié)果可能是money、prone、lonely。

? 正則表達式可能會為單詞邊界提供一個模式來解決單詞的匹配問題:

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

如何識別反模式：當出現(xiàn)以下情況時，可能是反模式

1、如何在like表達式的2個通配符之間插入一個變量？

2、如何寫一個正則表達式來檢查一個字符串是否包含多個單詞、不包含一個特定的單詞，或者包含給定單詞的任意形式？

3、網(wǎng)站的搜索功能在增加了很多文檔進去之后慢的不可理喻。

合理使用反模式：

1、性能總是最重要的，如果一些查詢過程很少執(zhí)行，就不必要花很多功夫去對它進行優(yōu)化

2、使用模式匹配操作進行很復雜的查詢是很困難的，但是如果你為了一些簡單的需求設(shè)計這樣的模式匹配，它們能幫助你用最少的工作量獲得正確的結(jié)果。

解決方案：使用正確的工具

最好的方案就是使用特殊的搜索引擎技術(shù)，而不是SQL。

另一個方案是將結(jié)果保存起來從而減少重復的搜索開銷。

1、MySQL中的全文索引：可以再一個類型為Char、varchar或者Text的列上定義一個全文索引。然后使用Match函數(shù)來搜索。

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

3、SQL Server中的全文搜索：使用Contains()操作符來使用全文索引。使用之前需要通過復雜的步驟來配置。

4、PostgreSQL的文本搜索：提供一個復雜大可大量配置的方式來將文本轉(zhuǎn)換為可搜索的詞匯集合，并且讓這些文檔能夠進行模式匹配搜索。

5、SQLite的全文搜索：使用SQLite的擴展組件來實現(xiàn)。

6、第三方搜索引擎：

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

（2）Apache Lucene:是一個針對Java程序的成熟搜索引擎。

? 7、實現(xiàn)自己的搜索引擎：

? 使用反向索引方案：反向索引就是一個所有可能被搜索的單詞列表。

（1）定義一個KeyWords表來記錄所有用戶搜索的關(guān)鍵字，然后定義一個交叉表來建立多對多的關(guān)系。

（2）將每個關(guān)鍵字和匹配的內(nèi)容添加到交叉表中。

當有新的搜索單詞，就使用like查詢結(jié)果，并將結(jié)果保存到交叉表里，這樣下次就不必like了。當有新的文檔入庫，就需要用觸發(fā)器（或者定時）去填充交叉表。

結(jié)論：不必使用SQL來解決所有問題。

18. 意大利面條式查詢

目標：減少SQL查詢數(shù)據(jù)

反模式：使用一步操作解決復雜問題

使用一個查詢來獲得所有結(jié)果的最常見后果就是產(chǎn)生了一個笛卡爾積。導致查詢性能降低。

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;

你碰巧知道對于給定的這個產(chǎn)品，有12個Bug被修復了，有7個Bug是打開的，因此，結(jié)果看上去很耐人尋味：

Product_id count_fixed count_open

1 84 84

如何識別反模式：當出現(xiàn)以下情況時，可能是反模式

1、為什么我的求和、技術(shù)返回的結(jié)果異常地大？

2、我一整天都在和整個變態(tài)的查詢語句做斗爭。SQL并不是那么的難寫，如果你和單條SQL查詢糾結(jié)了很長時間，

? 應該重新考慮實現(xiàn)方式。

3、試試再加一個Distinct去除重復數(shù)據(jù)。

解決方案：分而治之

1、用多個sql語句查詢得到數(shù)據(jù)，再進行整合?；蛘遳nion多個sql的結(jié)果。

2、使用SQL語句批量生成多個動態(tài)SQL語句。

結(jié)論：盡管SQL支持用一行代碼解決復雜的問題，但是也別做不切實際的事情。

19. 使用*號，隱式的列

目標：減少輸入

反模式：捷徑會讓你迷失方向

使用通配符和未命名的列能夠達到減少輸入的目的，但是這個習慣會帶來一些危害。

1、破壞代碼重構(gòu)：增加一列后，使用隱式的Insert插入語句報錯；

2、查詢中使用通配符，影響性能和擴展性。大量數(shù)據(jù)在網(wǎng)絡之間傳輸，可能會造成阻塞。

如何識別反模式：當出現(xiàn)以下情況時，可能是反模式

1、程序由于還使用老的列名而掛掉。

改變了數(shù)據(jù)庫里的一張表，添加、刪除、重命名列，或者改變列的順序。但沒能更新全部使用到這張表的代碼。

2、花了幾天時間終于找到了網(wǎng)絡的瓶頸，終于減小了到數(shù)據(jù)庫服務器的龐大的通信量。

合理使用反模式：

1、在開發(fā)或者測試環(huán)境下可以使用通配符，在生成環(huán)境下盡量不要使用。

2、可以在聯(lián)結(jié)查詢中的每個獨立的表使用通配符。在通配符之前加上表名或者別名作為前綴。

3、如果你的程序需要在增加、刪除、重命名或者重新配置列時依舊能自動適應及調(diào)整，

那最好還是使用通配符。

解決方案：明確列出列名

結(jié)論：無論如何都不要使用通配符

20. 明文密碼

目標：恢復或重置密碼

反模式：使用明文存儲密碼

1、存儲密碼

使用明文存儲密碼或者在網(wǎng)絡上傳遞密碼是不安全的。

? 如果攻擊者截取到你用來插入（或者修改）密碼的sql語句，就可以獲得密碼。

黑客獲取密碼的方式有很多種：

（1）在客戶端和服務器端數(shù)據(jù)庫交互的網(wǎng)絡線路上接貨數(shù)據(jù)包。比如使用Wireshark黑客軟件。

（2）在數(shù)據(jù)庫服務器上搜索SQL的查詢?nèi)罩尽?/p>

（3）從服務器或者備份介質(zhì)上讀取數(shù)據(jù)庫備份文件內(nèi)的數(shù)據(jù)。

2、驗證密碼：同上。

3、在Email中發(fā)送密碼：Email的收發(fā)都需要經(jīng)由網(wǎng)絡層傳輸，數(shù)據(jù)可能會在其他的路由節(jié)點上被黑客劫持。

如何識別反模式：當出現(xiàn)以下情況時，可能是反模式合理使用反模式：

1、能夠恢復你的密碼；

2、將密碼通過郵件以明文或可逆轉(zhuǎn)的加密的格式發(fā)給你的程序。

合理使用反模式：

1、你的程序可能需要使用密碼來訪問一個地第三方的服務，這意味著你的程序可能是一個客戶端，

? 必須使用可讀的格式來存儲這個密碼。最好的做法是使用一些程序能夠解碼的加密方法來存儲，

而不是直接使用明文的方式存儲在數(shù)據(jù)庫中。

2、并不是所有的程序都有被攻擊的風險，也不是所有的程序都有敏感的程序需保護的信息。

內(nèi)文通訊的程序，只使用認證機制就可以了。

解決方案：

1、先加密再存儲

? （1）哈希是指將輸入字符串轉(zhuǎn)換成為另一個新的、不可識別的字符串的函數(shù)。

? 哈希算法是不可逆的。

（2）在SQL中使用哈希

哈希函數(shù)并是標準的SQL語言，因此你可能要依賴于所使用的數(shù)據(jù)庫提供的哈希擴展。

? insert into Accounts(AccountId,AccountName,password)

? values(123,'billKarwin', SHA2('xyzzy'));---MySQL6.0.5的擴展函數(shù)

? （3）給哈希加料

使用哈希值替換原來的明文密碼，但是黑客仍然能夠破解你的哈希值。通過他們預先準備好的自己的數(shù)據(jù)庫，里面存儲的可能的密碼與對應的哈希值，然后比較久可以找出明文密碼。預防這種“字典攻擊”的一種方法是給你的密碼加密表達式加點佐料。具體方法是在將用戶密碼傳入哈希函數(shù)進行加密之前，將其和一個無意義的字符串拼接在一起。

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

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

? 佐料的合理長度應該是8個字節(jié)。

（4）在SQL中隱藏密碼

? 只要不將明文密碼放到SQL查詢語句中，就能避免泄露。

? 具體做法：在程序代碼中生成密碼的哈希字符串，然后在SQL查詢中使用哈希串。

（5）在網(wǎng)絡傳輸中，還有一個地方是攻擊者有機會接貨網(wǎng)絡數(shù)據(jù)包的：在用戶的瀏覽器和網(wǎng)站服務器之間。

? 當用戶提交了一個登錄表單時，瀏覽器將用戶的密碼以明文方式發(fā)送到服務器端，隨后服務器端才能

? 使用這個密碼進行哈希運算。

? 解決方法：在用戶的瀏覽器發(fā)送表單數(shù)據(jù)之前就進行哈希運算。

? 但是這個方案也有一些不足的地方，就是你需要再進行正確的哈希運算之前，還要通過別的途徑來獲得和

? 這個密碼相關(guān)聯(lián)的佐料。這種方案是在從瀏覽器向服務器端提交表單密碼時，使用安全的HTTP(https)鏈接。

2、重置密碼而不是恢復密碼

21. SQL注入

目標：編寫SQL動態(tài)查詢，防止SQL注入

通常所說的“SQL動態(tài)查詢”是指將程序中的變量和基本SQL語句拼接成一個完整的查詢語句。

反模式：將未經(jīng)驗證的輸入作為代碼執(zhí)行

當向SQL查詢的字符串中插入別的內(nèi)容，而這些被插入的內(nèi)容以你不希望的方式修改了查詢語法時，SQL注入就成功了。

傳統(tǒng)的SQL注入案例中，所插入的內(nèi)容首先完成了一個查詢，然后再執(zhí)行第二個完整的查詢邏輯比如：@bugId的值是1234;Delete from Bugs，最后的SQL語句變成如下格式：

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

? 1、意外無處不在

? 由于字符串引起的語法錯誤，SQL語句是不會被執(zhí)行的。

風險較大的是產(chǎn)生的SQL沒有任何語法錯誤，并且以一種你所不希望的方式執(zhí)行。

2、對Web安全的嚴重威脅

當攻擊者能夠使用SQL注入操控你的SQL查詢語句時，就變成了一個巨大的威脅。

? 通常做法是在參數(shù)后插入額外的字符串，改變對應SQL語句的意義，例如：

? Update Account

? set password = SHA2('zyxzy')

? where accountId = 123 or true --在傳入accountId參數(shù)等于123的后面，添加了 or true

? 理解SQL注入的關(guān)鍵，也是如何防止SQL注入的關(guān)鍵：SQL注入是通過在SQL語句被數(shù)據(jù)庫解析之前，

以修改其語法的形式工作的。只要在解析語句之前插入動態(tài)部分，就存在SQL注入的風險。

3、尋找解決方法

（1）轉(zhuǎn)義：對傳入的參數(shù)字符串進行轉(zhuǎn)義操作，使它們不至于成為字符串的結(jié)束符。

? 使用2個連續(xù)的單引號或者反斜杠來轉(zhuǎn)義。實現(xiàn)原理是在將應用程序中的數(shù)據(jù)插入到SQL語句之前

? 就進行轉(zhuǎn)換。這種技術(shù)能減少由于動態(tài)內(nèi)容中不匹配是引號做造成的SQL注入的風險，但在非字符串

? 內(nèi)容的情況下，這種技術(shù)就會失效。

（2）查詢參數(shù)：查詢參數(shù)的做法是在準備查詢語句的時候，在對應參數(shù)的地方使用“參數(shù)占位符”。隨后，

? 在執(zhí)行這個預先準備好的查詢時提供一個參數(shù)。

? 該方法的確是應對SQL注入的強勁解決方案，但是這還不是一個通用的解決方案，因為查詢參數(shù)總是被視為是一個字面值。

（a）多個值的列表不可以當成單一參數(shù)；

（b）表名無法作為參數(shù)；

? （c）列名無法作為參數(shù)；

? （d）SQL關(guān)鍵字無法作為參數(shù)；

（3）存儲過程：存儲過程是包含固定的SQL語句，這些語句在定義這個存儲過程的時候被解析的。

? 在存儲過程也可以使用SQL動態(tài)查詢的，這樣也存在安全隱患。

（4）數(shù)據(jù)訪問框架ORM：對于所有允許你使用字符串方式傳入SQL語句的框架來說，都無法抵御SQL注入的攻擊。

如何識別反模式：幾乎所有的數(shù)據(jù)庫應用程序都動態(tài)地構(gòu)建SQL語句，如果使用拼接字符串的形式或者將變量插入到字符串的

方法來構(gòu)建SQL語句，這樣的sql語句就會受到SQL注入攻擊的威脅。

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

解決方案：

1、過濾輸入內(nèi)容，將所有不合法的字符從用戶輸入中剔除掉。

2、參數(shù)化動態(tài)內(nèi)容：如果查詢中的變化部分是一些簡單的類型，應該使用查詢參數(shù)將其和SQL表達式分離。

? 如果是在RDBMS解析完SQL語句之后才插入這個參數(shù)值，沒有哪種SQL注入的功能能改變一個參數(shù)化了查詢的語法結(jié)構(gòu)。

? 即使攻擊者嘗試使用帶有惡意的參數(shù)值，諸如123 or true ，關(guān)系型數(shù)據(jù)庫管理系統(tǒng)也會將這個字符串當成一個完整的值插入

? Update Account

? set password = SHA2('zyxzy'

? where accountId ='123 or true' --當做一個完整的字符串而不會造成威脅

? 3、給動態(tài)輸入的值加引號

? 參數(shù)查詢通常來說是最好的解決方案，但是在有些特殊的情況下，參數(shù)的占位符會導致查詢優(yōu)化器無法選擇使用

? 哪個索引來進行優(yōu)化查詢。

? 4、找個可靠的人來幫你審查SQL語句

? 在檢查代碼是否包含SQL注入風險的時候，參考一下幾點：

（1）找出所有使用了程序變量、字符串鏈接或者替換等方法組成的SQL語句。

（2）跟蹤在SQL語句中使用的動態(tài)內(nèi)容的來源。找出所有的外部輸入，比如用戶輸入、文件、系統(tǒng)環(huán)境、網(wǎng)絡服務、 第三方代碼，甚至于從數(shù)據(jù)庫中獲取的字符串。

? （3）假設(shè)任何外部內(nèi)容都是潛在的威脅，對于不受信任的內(nèi)容都要進行過濾、驗證或者使用數(shù)組映射的方式來處理。

（4）在將外部數(shù)據(jù)合并到SQL語句時，使用查詢參數(shù)，或者用穩(wěn)健的轉(zhuǎn)義函數(shù)預先處理。

（5）在存儲過程的代碼以及任何其他使用SQL動態(tài)查詢語句的地方都做同樣的檢查。

結(jié)論：讓用戶輸入內(nèi)容，但永遠別讓用戶輸入代碼

22. 偽鍵潔癖，整理數(shù)據(jù)

目標：整理數(shù)據(jù)，使不連續(xù)的主鍵Id數(shù)據(jù)記錄變的連續(xù)。

img

反模式：填充斷檔的數(shù)據(jù)空缺。

1、不按照順序分配編號

? 在插入新行時，通過遍歷表，找到的第一個未分配的主鍵編號分配給新行，來代替原來自動分配的偽主鍵機制。

? 使用Select Max(Id) + 1 這種查詢語句，會出現(xiàn)并發(fā)訪問的問題。

2、為現(xiàn)有數(shù)據(jù)行重新編號：通常做法是找到主鍵最大的行，然后用最小的未被使用的值來更新它。

缺點：（1）SQL語句比較麻煩；

（2）必須同時更新所有引用了你重新分配了主鍵的行的子記錄；

（3）無法避免產(chǎn)生新的斷檔。

? 3、制造數(shù)據(jù)差異

如果別的外部系統(tǒng)依賴于數(shù)據(jù)庫中的主鍵來定義數(shù)據(jù)，那么你的更新操作就會導致那個系統(tǒng)中的引用失效。

重用主鍵不是一個號的注意，因為斷檔往往是由于一些合理的刪除或者回滾數(shù)據(jù)所造成的。

別因為那些偽鍵看上去是沒用的而重新分配他們。

如何識別反模式：當出現(xiàn)以下情況時，可能是反模式

1、在我回滾了一個插入操作后，要怎么重用囊而自動生成的標識？

? 偽鍵一旦生成后不會回滾。如果非要回滾，RDBMS就必須在一耳光事務的聲明周期內(nèi)生成一個偽鍵，

? 而這在多個客戶端并發(fā)地插入數(shù)據(jù)時，會導致競爭或者死鎖。

2、bugId為3的這條記錄怎么了？

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

4、自增長整形id的數(shù)字標識如果達到了最大值怎么辦？

合理使用反模式：

沒有理由要去改變偽鍵的值，由于它的值本身并沒有什么重要的意義。如果這個主鍵列有實際的意義，那么這就是一個自然鍵，而不是偽鍵。

解決方案：

主鍵的值必須是唯一且非空的，因而你才能使用主鍵來唯一確定一行記錄，但這是主鍵的唯一約束，

? 他們不需要一定非得是連續(xù)值才能用來標記行。

1、定義行號：使用Row_Number()或者Limit等關(guān)鍵字來實現(xiàn)；

2、使用Guid：數(shù)據(jù)庫全局唯一標識符。

? 優(yōu)點：（1）可以再多個數(shù)據(jù)庫服務器上并發(fā)地生成偽鍵，而不用擔心生成同樣的值。

? （2）不存在斷檔的問題。

? 缺點：（1）Guid的值太長，不方便輸入；

（2）Guid的值是隨機的，因此找不到任何規(guī)則或者依靠最大值來判斷哪一行的最新插入的；

（3）Guid的存儲需要16字節(jié)，這比傳統(tǒng)的4自檢整形偽鍵占用更多的控件，并且查詢的速度更慢

結(jié)論：將偽鍵當做行的唯一性標識，但它們不是行號。

23. 非禮勿視

目標：寫更少的代碼

反模式：無米之炊 忽略數(shù)據(jù)庫API的返回值，將程序代碼跟SQL混在一起
? 1.沒有診斷的診斷 在多條sql順序執(zhí)行過程中，最好對結(jié)果進行診斷，保證錯誤能夠快速定位。
? 2.字里行間 花費大量時間調(diào)試生成sql字符串的代碼

解決方案：優(yōu)雅的從錯誤中恢復
? 1.保持節(jié)奏 檢查數(shù)據(jù)庫API的返回狀態(tài)和異常。
? 2.回溯你的腳步。sql語句記錄，輸出，調(diào)試。發(fā)現(xiàn)并解決代碼中的問題已經(jīng)很苦難了，就別再盲目的干了。

24. 外交豁免權(quán)

? 目標：最佳實踐 使用版本控制工具管理源代碼，編寫單元測試腳本；編寫文檔，代碼注釋。
? 反模式：將SQL視為二等公民。

25. 魔豆

目標: 簡化MVC的模型

反模式: 模型僅僅是活動記錄

Robert L. Glass認為：

80%的軟件工作是智力活動。相當大的比例是創(chuàng)造性的活動，很少是文書性的工作。

MVC架構(gòu)指：模型-視圖-控制器。我們使用MVC就是同時使用設(shè)計模式和軟件框架。這是拆分程序邏輯的技術(shù)。

• View：處理輸出。
• Controller：接收用戶輸入，處理響應邏輯，委托給Module執(zhí)行操作，將結(jié)果傳遞給View。
• Module：程序核心，包括輸入驗證、業(yè)務邏輯，與數(shù)據(jù)庫交互等。

MVC使用者通常犯錯在：僅把Module當作DAO（數(shù)據(jù)訪問對象）。

解決方案: 模型包含活動記錄

不要把SQL查詢語句傳給Module對象，Module對象應該囊括了所有它需要的查詢。

將復雜的查詢代碼寫在Module對象里，并作為接口暴露出來。遵循DRY原則。

分離Module和DAO。Module和DAO/Active Record的關(guān)系是包含（has-a）而不是繼承（is-a）。

Module負責創(chuàng)建DAO。一個聚集了一系列DAO的Module應該負責創(chuàng)建這些對象。Controller和View只使用Module暴露出來的接口，而不要處理與數(shù)據(jù)交互的邏輯（如傳遞SQL給Module，以求其返回執(zhí)行結(jié)果）。

Module的接口應該是直接的（與業(yè)務邏輯直接相關(guān)，而不是過于抽象的接口），而不是數(shù)據(jù)庫物理結(jié)構(gòu)或CRUD操作。ADO/Active Record可以提供如find(), first(), insert(), save()之類的接口，但Module層的接口不要這么干。

將Module類和DAO解耦，并可以為一個DAO設(shè)計多個模型類。

框架很難給Module提供一個通吃的解決方案。