sql反模式分析2

第八章 多列屬性
? ? ? ? ?目標：存儲多值屬性 ?為一個bug設置多個標簽
? ? ? ? ?反模式：創建多個列，為bugs創建tag1，tag2，tag3幾個列保存標簽。標簽必須放于其中一個。
? ? ? ? ? ? ? ? ?1.查詢數據，比如搜索這三列，可以使用in語句
? ? ? ? ? ? ? ? ?2.添加和刪除 ?update bugs set tag1=nullif(tag1,'perfor'),tag2=nullif(tag2,'perfor'),tag3=nullif(tag3,'perfor')這個能把值更新到其中為空的那一列，如果都不為空，則不作更新。 ? ? ? ? ? ?3.確保唯一性。無法確保三列的值不一樣。
? ? ? ? ? ? ? ? 4.處理不斷增長的值集。三列可能不夠用，如果在不斷的增加列，性能開銷將越來越大，而且sql查詢更新將越來越復雜。
? ? ? ? ?解決方案：創建從屬表 ?
? ? ? ? ?將具有同樣意義的值存在同一列中。
? ?第九章：元數據分類 ?案例：為一個客戶表增加每年的收入情況，每年的收入情況都存在單獨的列中，導致每一年就需要新增一個列存儲該年的收入。
? ? ? ? ? ?目標：支持可擴展性
? ? ? ? ? ?反模式： 克隆表與克隆列
? ? ? ? ? ? ? ? 1.不斷產生的新表 按照年份對bug表進行拆分，拆成bugs_2008,bugs_2009等多張表，然后按照需要需要修改對應的sql。
? ? ? ? ? ? ? ?2.管理數據完整性 如果有數據被誤寫到其他表中，則可能導致統計的一年的bugs數之類的數據不準確。沒有任何辦法自動對數據和相關表名做限制。但可以在每張表創建的時候使用check的約束。
? ? ? ? ? ? ? ?3.同步數據 ?如果發現某條記錄原來是在2009這個表中，但時間弄錯了，需要修改為2008的，這樣訂正數據就比較麻煩。需要好幾條sql。
? ? ? ? ? ? ? 4.確保唯一性。 如果需要做數據遷移，則需要保證記錄的主鍵id值不會與目標表的主鍵記錄沖突。而且對于那些只支持單表ID唯一的數據庫產品，實現這樣的功能還需要定義一張額外的表存儲產品主鍵的值。
? ? ? ? ? ? ? 5.跨表查詢 ?如果需要查詢所有的bugs數,則需要把每個表用union進行查詢.
? ? ? ? ? ? ? 6.同步元數據.如果值在某個表增加一列,其他表沒有增加,則聯合查詢不用使用*,需要列出所有列名.
? ? ? ? ? ? ?7.管理引用完整性. 其他表就不能引用bugs的外鍵了,因為有多個bugs表.
? ? ? ? ? ? ?8.標識元數據分裂列 如果有其他表保護bugx_fiexd_2008,bugx_fixed_2009，則以后肯定需要增加bugx_fixed_2010.
? ? ? ? ? 解決方案： 手工分割表的一個合理使用場景是歸檔數據。把沒用的數據遷移到歷史表中。
? ? ? ? ? ? ? ?1.使用水平分區。MYSQL5.1所支持的分區特性，在createtable時執行pritition by hash(year(date)reported)) partitions 4.
? ? ? ? ? ? ? ?2.使用垂直分區。根據列來對表進行拆分。將一些BLOB或者TEXT字段拆分到其他表存儲。
? ? ? ? ? ? ? ?3.解決元數據分裂列。創建關聯表。
? ? ? ? ? 別讓數據繁衍元數據。


? ?物理數據庫設計反模式
? ? ? ?第10章：取整錯誤。
? ? ? ? ? 目標：使用小數取代整數，運算結果必須準確。
? ? ? ? ? 反模式：使用float類型
? ? ? ? ? ? ? ? ? 無限循環小數無法使用存儲表示。
? ? ? ? ? ? ? ? ? 在SQL中使用FLOAT類型，放大查詢結果差異比較大。無法使用比較操作，必須使用近似相等查詢，但是閥值需要使用合適。
? ? ? ? ? 解決方案：oracle的FLOAT類型表示的是精確值，而BINARY_FLOAT則是非精確值。
? ? ? ? ? ? ? ? ? ?使用NUMERIC類型。SQL的NUMERIC或者DECIMAL類型來代替FLOAT存儲小數。 NUMERIC（9,2) 精度，刻度 這樣仍然無法存儲無限精度的數據。
? ? ? ? ? 盡可能不要使用浮點數。
? ? ?第11章：每日新花樣 ?需要給稱呼列加入約束指定這些候選值
? ? ? ? ? 目標：限定列的有效值 ?希望數據庫能夠拒絕無效值的輸入
? ? ? ? ? 反模式：在列定義上指定可選值。很多數據庫設計人員習慣在定義列的時候指定所有可選的有效數據。
? ? ? ? ? ? ? ? ? ?create table bugs(status varchar(20) check(status in('new','in','fixed')).
? ? ? ? ? ? ? ? ? ?mysql也支持用ENUM關鍵詞來約束。但是mysql存儲的是序數，而非字符串。
? ? ? ? ? ? ? ? ? 1.中間的是哪個 ?無法獲得status列中值的枚舉列表，如果使用distinct來查詢bugs表，但是剛開始沒數據，查詢的結果為空。如果使用INFORMATION_SHEMA系統視圖，則還需要解決解決格式。
? ? ? ? ? ? ? ? ?2.添加新口味。添加或者刪除一個候選值。沒有什么語法支持從ENUM或者check約束中添加或者刪除一個值。只能用一個新的集合重新定義這一列。一些數據庫只有在表為空表是才能改變某一列的數據。那么就需要先將數據導出，改變之后再導入。
? ? ? ? ? ? ? ? ?3.老的口味永不消失。舊的值無法刪除。
? ? ? ? ? ? ? ? 4.可一致性地下。check約束，域和UDT在各種數據庫支持形式不同意。ENUM是mysql特有的特性。
? ? ? ? 解決方案：在數據中指定值，通過創建一張檢查表bug_status，定義status列中出現的候選值，然后定義一個外鍵約束。
? ? ? ? ? ? ? ? ?1.查詢候選值集合。直接查詢檢查表。
? ? ? ? ? ? ? ? ?2.更新檢查表中的數據。插入更新操作很方便。
? ? ? ? ? ? ? ? ?3.支持廢棄數據。可以通過在bug_status表增加一列來表示是否已經棄用。
? ? ? ? ? ? ? ? 4.良好的可移植性。
? ? ? ? 在驗證固定集合的候選值時使用元數據。在驗證可變集合的候選值時使用數據。
? ? ?第12章：幽靈文件 ?只保存數據庫文件，沒有保存數據庫中保存的文件路徑對應的數據庫外的文件。
? ? ? ? ? ?目標：存儲圖片或其他多媒體大文件。
? ? ? ? ? ?反模式：假設必須使用文件系統，可以使用BLOB字段存儲文件,或者只在數據庫存儲文件路徑。
? ? ? ? ? ? ? ? ? 1.文件不支持DELETE ?垃圾回收問題。如果圖片在數據庫之外，刪除某條記錄之后無法自動將對應文件刪除。
? ? ? ? ? ? ? ? ? 2.文件不支持事務隔離。數據庫事務在提交之前，所有改變對外都不可見。但是數據庫之外的文件改變則立刻體現到外界。
? ? ? ? ? ? ? ? ? 3.文件不支持回滾操作。數據庫可以回滾，但是文件系統無法回滾。
? ? ? ? ? ? ? ? ?4.文件不支持數據庫備份工具。
? ? ? ? ? ? ? ? ?5.文件不支持SQL的訪問權限設置。
? ? ? ? ? ? ? ? ?6.文件不是SQL數據類型。無法驗證文件路徑是否正確。
? ? ? ? ? 解決方案：在需要時使用BLOB類型。
? ? ? ? ? ? ? ? ? ?MYSQL MEDIUMBLOB：16M oracle：LONGRAW 2GB
? ? ? ? ? ? ? ? ? ?MYSQL有load_file()用來讀取一個文件存儲到BLOB列
? ? ? ? ? 存儲在數據庫之外的數據不由數據庫管理。
? ? 第13章： 亂用索引
? ? ? ? ? 目標：優化性能
? ? ? ? ? 反模式：無規劃的使用索引
? ? ? ? ? ? ? ? ? 1.無索引
? ? ? ? ? ? ? ? ? 2.索引過多 ?不需使用的索引無法獲得任何好處，只有開銷。
? ? ? ? ? ? ? ? ? 3.索引也無能為力 常犯的錯誤是進行一個無法使用索引的查詢
? ? ? ? ? 解決方案：所有不重復的值的記錄和總計數條數之比越低，索引的效率就越低。
? ? ? ? ? ? ? ? ?1.測量 ?ORACLE:TKProf mysql:慢查詢日志
? ? ? ? ? ? ? ? ?2.解釋 ?查詢執行計劃
? ? ? ? ? ? ? ? ?3.挑選 ? 索引覆蓋
? ? ? ? ? ? ? ? ?4.測試?
? ? ? ? ? ? ? ? ?5.優化 ?索引預載入：mysql使用 load index into cache語句。
? ? ? ? ? ? ? ? ?6.重建：更新或者刪除導致索引修改，需要定期對索引進行維護。mysql：analyze table or optimize table oracle:alter index rebuild
? ? ? ? ?了解你的數據，了解你的查詢請求，然后MENTOR你的索引。
? ?
? 查詢反模式
? ? ?第14章：對未知的恐懼。
? ? ? ? ? ?目標：辨別懸空值 SQL支持一個特殊的空值，NULL。
? ? ? ? ? ? ? 增加記錄時使用NULL代替那些還不確定的值。
? ? ? ? ? ? ?一個給定的列如果沒有合適的值，可以使用NULL代替。
? ? ? ? ? ? ?當傳入參數無效時，一個函數的返回值也可以是NULL。
? ? ? ? ? ? 在外聯結查詢中，NULL被用來當做未匹配的列的占位符。
? ? ? ? ?反模式：將NULL作為普通的值，反之亦然。
? ? ? ? ? ? ?1.在表達式中使用NULL。 如果某個字段為NULL，表達式結果也是NULL。
? ? ? ? ? ? ?2.搜索允許為空的列： select * from bugs where aggin_to=123 或者select * from bugs where not(assin_to=123)都不會返回這列為null的值。
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 而且查詢null或者非null是不能用where assin_to=NULL或者assin_to<>NULL。使用is null
? ? ? ? ? ? ?3.在查詢參數中使用NULL 不能在查詢參數assin_to=?傳入NULL值
? ? ? ? ? ? ?4.避免上述問題：使用默認值來代替NULL，按時查詢計算時仍然需要制定<>默認值
? ? ? ? 解決方案：將NULL視為特殊值
? ? ? ? ? ? ? ?1.在標量表達式中使用NULL 表達式中一個值為NULL，則結果就為NULL。
? ? ? ? ? ? ? ?2.在布爾表達式中使用NULL。
? ? ? ? ? ? ? 3.檢索NULL值。SQL-99中額外定義了一個比較斷言 IS DISTINCT FROM
? ? ? ? ? ? ? 4.聲明NOT NULL列。
? ? ? ? ? ? ?5.動態默認值。使用COALESCE（）函數返回一個非NULL的參數。
? ? ? ? ?使用NULL來表示任意類型的懸空值。
? ?第15章：模棱兩可的分組
? ? ? ?目標：獲取每組的最大值
? ? ? ?反模式： 引用非分組列
? ? ? ? ? ? ? ?1.單值規則 一個分組只能返回單一的值?
? ? ? ? ? ? ? ?2.我想要的查詢 如果分組后通過max獲得的有兩列的值是一樣的，那么就無法返回哪條記錄的其他列。不能使用max和min兩個聚合函數定位到不同的記錄。
? ? ? ?解決方案： 無歧義的使用列
? ? ? ? ? ? ? ?1.只查詢功能依賴的列；
? ? ? ? ? ? ? 2.使用關聯子查詢
? ? ? ? ? ? ? 3.使用衍生表
? ? ? ? ? ? ? 4.使用join?
? ? ? ? ? ? ? 5.對額外的列使用聚合函數
? ? ? ? ? ? ?6.連接同組所有值 mysql使用GROUP_CONCAT()函數將這一組中所有的值連在一起。
? ? ? ? 遵循單值規則，避免獲得模棱兩可的查詢結果。
? ? 第16章：隨機選擇 ?設計一個隨機廣告展示的查詢
? ? ? ? 目標：獲得樣壞死記錄
? ? ? ?反模式：隨機排序 select * from bugs order by rand() limit 1; 使用rand（）簡單，但是無法利用索引，因為沒有索引會基于隨機函數返回的值，導致一次全表排序。
? ? ? ?解決方案：沒有具體的順序。
? ? ? ? ? ? ? ? ?1.從1到最大值之間隨機選擇 ?select b1.* from bugs as b1 join(select(ceil(rand() *(select max(bug_id) from bugs)) as rand_id) as b2 on(b1.bug_id=b2.bug_id);
? ? ? ? ? ? ? ? ?2.選擇下一個最大值。 select b1.* from bugs as b1 join(select(ceil(rand() *(select max(bug_id) from bugs)) as rand_id) as b2 where b1.bug_id>=b2.bug_id order by b1.bug_id limit 1;
? ? ? ? ? ? ? ? 3.獲得所有鍵值，隨機選擇一個。 程序選擇一個，查詢兩次
? ? ? ? ? ? ? ? 4.使用偏移量選擇隨機行。
? ? ? ? ? ? ? ? 5.專有解決方案。SQL server 使用tablesample函數。 oracle使用sample函數。
? ? ? ? 有些查詢是無法優化的，換種方式試試看。
? ? ?第17章：可憐人的搜索引擎
? ? ? ? ?目標：全文檢索
? ? ? ? ?反模式：模糊匹配斷言 SQL提供了模式匹配斷言來比較字符串，最常用的就是like語句。還有REGEXP正則表達式匹配。 不過缺點當然就是性能問題了。
? ? ? ? ?解決方案：使用正確的工具
? ? ? ? ? ? ? ? ? 1.數據庫擴展 mysql能夠對char,varchar,text定義一個全文索引,使用match進行全文查詢.oracle使用context支持,然后通過contains()操作符搜索.sqlserver和postgreSQL也有對全文索引的支持.
? ? ? ? ? ? ? ? 2.第三方搜索引擎:Sphinx search lucene
? ? ? ? ?你不必使用SQL來解決所有的問題.
? ? ? 第18章:意大利苗條查詢
? ? ? ? ? 目標:減少sql查詢數量
? ? ? ? ? 反模式:使用一部操作解決復雜問題
? ? ? ? ? ? ? ? ? 1.副作用 查詢多少bug已經修復，多少bug還打開。select p.product_id,count(f.bug_id) as count_fixed,count（o.bug_id) as count_open from bugsproduct p left outer join bugs f on(p.bug_id=f.bug_id and f.status='fixed') left outer join bugx o on(p.bug_id=o.bug_id and o.status='open') where p.product_id=1 group by p.product
? ? ? ? ? ? ? ? ? ? 這條sql查詢出來count_fixed和count_open都是84，而實際上12個fixed，7個open，剛好84是12*7 這種查詢是有問題。
? ? ? ? ?解決方案：分而治之
? ? ? ? ? ? ? ? ?1.分兩條sql來查詢 。select p.product_id,count(f.bug_id) as count_fixe from bugsproduct p left outer join bugs f on(p.bug_id=f.bug_id and f.status='fixed') where p.product_id=1 group by p.product 。select p.product_id,count（o.bug_id) as count_open from bugsproduct p left outer join bugx o on(p.bug_id=o.bug_id and o.status='open') where p.product_id=1 group by p.product
? ? ? ? ? ? ? ? ?2.尋找union標記 ?根據單個結果集再使用union all合并。
? ? ? ? ? 盡管SQL支持用一行代碼解決復雜的問題，但也別做不切實際的事情。
? ? ? ?第19章：隱式的列 ?聯合查詢如果兩個表有列名一樣的列，則會只取其中一個。
? ? ? ? ? ? 目標：減少輸入 ?可以使用*獲取所有列
? ? ? ? ? ? 反模式：捷徑會讓你迷失方向
? ? ? ? ? ? ? ? ? ? 1.破壞代碼重構，比如增加一列之后，原來insert沒有指定列名的則現在會報錯，少一列的值了。 select也一樣，如果刪除一列后，應用代碼獲取列的可能也會出錯。
? ? ? ? ? ? ? ? ? ? 2.隱藏的開銷 獲取一些沒用的列，會增加網絡開銷和性能。
? ? ? ? ? ? 解決方案：明確列出列名
? ? ? ? ? ?隨便拿，但是拿了就必須吃掉。
? ? ?
? ? 應用程序開發反模式
? ? ? ? ? ?第20章 明文密碼
? ? ? ? ? ? ? 目標：恢復和重置密碼 ?現在一般都是使用郵箱讓用戶恢復和重置密碼
? ? ? ? ? ? ? 反模式：使用明文存儲密碼
? ? ? ? ? ? ? ? ? ? ? 1.存儲密碼 sql被劫持?
? ? ? ? ? ? ? ? ? ? ? 2.驗證密碼
? ? ? ? ? ? ? ? ? ? ? 3.在email中發送密碼，email有可能被劫持
? ? ? ? ? ? ?解決方案：先哈希，后存儲
? ? ? ? ? ? ? ? ? ? mysql擴展支持SHA2（）函數返回256位的哈希串
? ? ? ? ? ? ? ? ? ? 哈希暴力破解，可以先加密后再進行哈希。
? ? ? ? ? ? ? ? ? ? 在SQL中隱藏密碼，在程序中生成哈希串之后，在sql中直接使用哈希串。
? ? ? ? ? ? ? ? ? ?重置密碼，而非恢復密碼。
? ? ? ? ? ? 如果密碼對你可讀，那么對于攻擊者也是如此。
? ? ? ? ? 第21章：SQL注入
? ? ? ? ? ? ? 目標：編寫SQL動態查詢
? ? ? ? ? ? ? 反模式：將未經驗證的輸入作為代碼執行
? ? ? ? ? ? ? 解決方案：不相信任何人
? ? ? ? ? ? ? ? ? ? 1.過濾輸入內容。
? ? ? ? ? ? ? ? ? ? 2.參數化動態內容
? ? ? ? ? ? ? ? ? ? 3.給動態輸入的值加引號
? ? ? ? ? ? ? ? ? ? 4.將用戶與代碼隔離
? ? ? ? ? ? ? ? ? ?5.找個可靠的人來幫你審查代碼
? ? ? ? ? ? ? 讓用戶輸入內容，但永遠別讓用戶輸入代碼。
? ? ? ? ? 第22章：偽鍵潔癖
? ? ? ? ? ? ? ? 目標：整理數據
? ? ? ? ? ? ? ? 反模式：填充角落
? ? ? ? ? ? ? ? ? ? ?1.不按照順序分配編號?
? ? ? ? ? ? ? ? ? ? 2.為現有行重新編號
? ? ? ? ? ? ? ? ? ? 3.制造數據差異 重新主鍵不是一個好習慣
? ? ? ? ? ? ? ?解決方案：1.定義行號。2.使用GUID 太長，隨機的，需要16字節
? ? ? ? ? ? ? ?將偽鍵當做行的唯一性標識，但他們不是行號。
? ? ? ? ?第23章：非禮勿視
? ? ? ? ? ? ? 目標：寫更少的代碼
? ? ? ? ? ? ? 反模式：無米之炊 忽略數據庫API的返回值，將程序代碼跟SQL混在一起
? ? ? ? ? ? ? ? ? 1.沒有診斷的診斷 在多條sql順序執行過程中，最好對結果進行診斷，保證錯誤能夠快速定位。
? ? ? ? ? ? ? ? ? 2.字里行間 花費大量時間調試生成sql字符串的代碼
? ? ? ? ? ? ? 解決方案：優雅的從錯誤中恢復
? ? ? ? ? ? ? ? ?1.保持節奏 檢查數據庫API的返回狀態和異常。
? ? ? ? ? ? ? ? ?2.回溯你的腳步。sql語句記錄，輸出，調試。
? ? ? ? ? 發現并解決代碼中的問題已經很苦難了，就別再盲目的干了。
? ? ? ? 第24章：外交豁免權
? ? ? ? ? ? ? 目標：最佳實踐 使用版本控制工具管理源代碼，編寫單元測試腳本；編寫文檔，代碼注釋。
? ? ? ? ? ? ? 反模式：將SQL視為二等公民。