sql反模式分析2
第八章 多列屬性
? ? ? ? ?目標(biāo):存儲多值屬性 ?為一個(gè)bug設(shè)置多個(gè)標(biāo)簽
? ? ? ? ?反模式:創(chuàng)建多個(gè)列,為bugs創(chuàng)建tag1,tag2,tag3幾個(gè)列保存標(biāo)簽。標(biāo)簽必須放于其中一個(gè)。
? ? ? ? ? ? ? ? ?1.查詢數(shù)據(jù),比如搜索這三列,可以使用in語句
? ? ? ? ? ? ? ? ?2.添加和刪除 ?update bugs set tag1=nullif(tag1,'perfor'),tag2=nullif(tag2,'perfor'),tag3=nullif(tag3,'perfor')這個(gè)能把值更新到其中為空的那一列,如果都不為空,則不作更新。 ? ? ? ? ? ?3.確保唯一性。無法確保三列的值不一樣。
? ? ? ? ? ? ? ? 4.處理不斷增長的值集。三列可能不夠用,如果在不斷的增加列,性能開銷將越來越大,而且sql查詢更新將越來越復(fù)雜。
? ? ? ? ?解決方案:創(chuàng)建從屬表 ?
? ? ? ? ?將具有同樣意義的值存在同一列中。
? ?第九章:元數(shù)據(jù)分類 ?案例:為一個(gè)客戶表增加每年的收入情況,每年的收入情況都存在單獨(dú)的列中,導(dǎo)致每一年就需要新增一個(gè)列存儲該年的收入。
? ? ? ? ? ?目標(biāo):支持可擴(kuò)展性
? ? ? ? ? ?反模式: 克隆表與克隆列
? ? ? ? ? ? ? ? 1.不斷產(chǎn)生的新表 按照年份對bug表進(jìn)行拆分,拆成bugs_2008,bugs_2009等多張表,然后按照需要需要修改對應(yīng)的sql。
? ? ? ? ? ? ? ?2.管理數(shù)據(jù)完整性 如果有數(shù)據(jù)被誤寫到其他表中,則可能導(dǎo)致統(tǒng)計(jì)的一年的bugs數(shù)之類的數(shù)據(jù)不準(zhǔn)確。沒有任何辦法自動對數(shù)據(jù)和相關(guān)表名做限制。但可以在每張表創(chuàng)建的時(shí)候使用check的約束。
? ? ? ? ? ? ? ?3.同步數(shù)據(jù) ?如果發(fā)現(xiàn)某條記錄原來是在2009這個(gè)表中,但時(shí)間弄錯(cuò)了,需要修改為2008的,這樣訂正數(shù)據(jù)就比較麻煩。需要好幾條sql。
? ? ? ? ? ? ? 4.確保唯一性。 如果需要做數(shù)據(jù)遷移,則需要保證記錄的主鍵id值不會與目標(biāo)表的主鍵記錄沖突。而且對于那些只支持單表ID唯一的數(shù)據(jù)庫產(chǎn)品,實(shí)現(xiàn)這樣的功能還需要定義一張額外的表存儲產(chǎn)品主鍵的值。
? ? ? ? ? ? ? 5.跨表查詢 ?如果需要查詢所有的bugs數(shù),則需要把每個(gè)表用union進(jìn)行查詢.
? ? ? ? ? ? ? 6.同步元數(shù)據(jù).如果值在某個(gè)表增加一列,其他表沒有增加,則聯(lián)合查詢不用使用*,需要列出所有列名.
? ? ? ? ? ? ?7.管理引用完整性. 其他表就不能引用bugs的外鍵了,因?yàn)橛卸鄠€(gè)bugs表.
? ? ? ? ? ? ?8.標(biāo)識元數(shù)據(jù)分裂列 如果有其他表保護(hù)bugx_fiexd_2008,bugx_fixed_2009,則以后肯定需要增加bugx_fixed_2010.
? ? ? ? ? 解決方案: 手工分割表的一個(gè)合理使用場景是歸檔數(shù)據(jù)。把沒用的數(shù)據(jù)遷移到歷史表中。
? ? ? ? ? ? ? ?1.使用水平分區(qū)。MYSQL5.1所支持的分區(qū)特性,在createtable時(shí)執(zhí)行pritition by hash(year(date)reported)) partitions 4.
? ? ? ? ? ? ? ?2.使用垂直分區(qū)。根據(jù)列來對表進(jìn)行拆分。將一些BLOB或者TEXT字段拆分到其他表存儲。
? ? ? ? ? ? ? ?3.解決元數(shù)據(jù)分裂列。創(chuàng)建關(guān)聯(lián)表。
? ? ? ? ? 別讓數(shù)據(jù)繁衍元數(shù)據(jù)。
? ?物理數(shù)據(jù)庫設(shè)計(jì)反模式
? ? ? ?第10章:取整錯(cuò)誤。
? ? ? ? ? 目標(biāo):使用小數(shù)取代整數(shù),運(yùn)算結(jié)果必須準(zhǔn)確。
? ? ? ? ? 反模式:使用float類型
? ? ? ? ? ? ? ? ? 無限循環(huán)小數(shù)無法使用存儲表示。
? ? ? ? ? ? ? ? ? 在SQL中使用FLOAT類型,放大查詢結(jié)果差異比較大。無法使用比較操作,必須使用近似相等查詢,但是閥值需要使用合適。
? ? ? ? ? 解決方案:oracle的FLOAT類型表示的是精確值,而BINARY_FLOAT則是非精確值。
? ? ? ? ? ? ? ? ? ?使用NUMERIC類型。SQL的NUMERIC或者DECIMAL類型來代替FLOAT存儲小數(shù)。 NUMERIC(9,2) 精度,刻度 這樣仍然無法存儲無限精度的數(shù)據(jù)。
? ? ? ? ? 盡可能不要使用浮點(diǎn)數(shù)。
? ? ?第11章:每日新花樣 ?需要給稱呼列加入約束指定這些候選值
? ? ? ? ? 目標(biāo):限定列的有效值 ?希望數(shù)據(jù)庫能夠拒絕無效值的輸入
? ? ? ? ? 反模式:在列定義上指定可選值。很多數(shù)據(jù)庫設(shè)計(jì)人員習(xí)慣在定義列的時(shí)候指定所有可選的有效數(shù)據(jù)。
? ? ? ? ? ? ? ? ? ?create table bugs(status varchar(20) check(status in('new','in','fixed')).
? ? ? ? ? ? ? ? ? ?mysql也支持用ENUM關(guān)鍵詞來約束。但是mysql存儲的是序數(shù),而非字符串。
? ? ? ? ? ? ? ? ? 1.中間的是哪個(gè) ?無法獲得status列中值的枚舉列表,如果使用distinct來查詢bugs表,但是剛開始沒數(shù)據(jù),查詢的結(jié)果為空。如果使用INFORMATION_SHEMA系統(tǒng)視圖,則還需要解決解決格式。
? ? ? ? ? ? ? ? ?2.添加新口味。添加或者刪除一個(gè)候選值。沒有什么語法支持從ENUM或者check約束中添加或者刪除一個(gè)值。只能用一個(gè)新的集合重新定義這一列。一些數(shù)據(jù)庫只有在表為空表是才能改變某一列的數(shù)據(jù)。那么就需要先將數(shù)據(jù)導(dǎo)出,改變之后再導(dǎo)入。
? ? ? ? ? ? ? ? ?3.老的口味永不消失。舊的值無法刪除。
? ? ? ? ? ? ? ? 4.可一致性地下。check約束,域和UDT在各種數(shù)據(jù)庫支持形式不同意。ENUM是mysql特有的特性。
? ? ? ? 解決方案:在數(shù)據(jù)中指定值,通過創(chuàng)建一張檢查表bug_status,定義status列中出現(xiàn)的候選值,然后定義一個(gè)外鍵約束。
? ? ? ? ? ? ? ? ?1.查詢候選值集合。直接查詢檢查表。
? ? ? ? ? ? ? ? ?2.更新檢查表中的數(shù)據(jù)。插入更新操作很方便。
? ? ? ? ? ? ? ? ?3.支持廢棄數(shù)據(jù)??梢酝ㄟ^在bug_status表增加一列來表示是否已經(jīng)棄用。
? ? ? ? ? ? ? ? 4.良好的可移植性。
? ? ? ? 在驗(yàn)證固定集合的候選值時(shí)使用元數(shù)據(jù)。在驗(yàn)證可變集合的候選值時(shí)使用數(shù)據(jù)。
? ? ?第12章:幽靈文件 ?只保存數(shù)據(jù)庫文件,沒有保存數(shù)據(jù)庫中保存的文件路徑對應(yīng)的數(shù)據(jù)庫外的文件。
? ? ? ? ? ?目標(biāo):存儲圖片或其他多媒體大文件。
? ? ? ? ? ?反模式:假設(shè)必須使用文件系統(tǒng),可以使用BLOB字段存儲文件,或者只在數(shù)據(jù)庫存儲文件路徑。
? ? ? ? ? ? ? ? ? 1.文件不支持DELETE ?垃圾回收問題。如果圖片在數(shù)據(jù)庫之外,刪除某條記錄之后無法自動將對應(yīng)文件刪除。
? ? ? ? ? ? ? ? ? 2.文件不支持事務(wù)隔離。數(shù)據(jù)庫事務(wù)在提交之前,所有改變對外都不可見。但是數(shù)據(jù)庫之外的文件改變則立刻體現(xiàn)到外界。
? ? ? ? ? ? ? ? ? 3.文件不支持回滾操作。數(shù)據(jù)庫可以回滾,但是文件系統(tǒng)無法回滾。
? ? ? ? ? ? ? ? ?4.文件不支持?jǐn)?shù)據(jù)庫備份工具。
? ? ? ? ? ? ? ? ?5.文件不支持SQL的訪問權(quán)限設(shè)置。
? ? ? ? ? ? ? ? ?6.文件不是SQL數(shù)據(jù)類型。無法驗(yàn)證文件路徑是否正確。
? ? ? ? ? 解決方案:在需要時(shí)使用BLOB類型。
? ? ? ? ? ? ? ? ? ?MYSQL MEDIUMBLOB:16M oracle:LONGRAW 2GB
? ? ? ? ? ? ? ? ? ?MYSQL有l(wèi)oad_file()用來讀取一個(gè)文件存儲到BLOB列
? ? ? ? ? 存儲在數(shù)據(jù)庫之外的數(shù)據(jù)不由數(shù)據(jù)庫管理。
? ? 第13章: 亂用索引
? ? ? ? ? 目標(biāo):優(yōu)化性能
? ? ? ? ? 反模式:無規(guī)劃的使用索引
? ? ? ? ? ? ? ? ? 1.無索引
? ? ? ? ? ? ? ? ? 2.索引過多 ?不需使用的索引無法獲得任何好處,只有開銷。
? ? ? ? ? ? ? ? ? 3.索引也無能為力 常犯的錯(cuò)誤是進(jìn)行一個(gè)無法使用索引的查詢
? ? ? ? ? 解決方案:所有不重復(fù)的值的記錄和總計(jì)數(shù)條數(shù)之比越低,索引的效率就越低。
? ? ? ? ? ? ? ? ?1.測量 ?ORACLE:TKProf mysql:慢查詢?nèi)罩?br /> ? ? ? ? ? ? ? ? ?2.解釋 ?查詢執(zhí)行計(jì)劃
? ? ? ? ? ? ? ? ?3.挑選 ? 索引覆蓋
? ? ? ? ? ? ? ? ?4.測試?
? ? ? ? ? ? ? ? ?5.優(yōu)化 ?索引預(yù)載入:mysql使用 load index into cache語句。
? ? ? ? ? ? ? ? ?6.重建:更新或者刪除導(dǎo)致索引修改,需要定期對索引進(jìn)行維護(hù)。mysql:analyze table or optimize table oracle:alter index rebuild
? ? ? ? ?了解你的數(shù)據(jù),了解你的查詢請求,然后MENTOR你的索引。
? ?
? 查詢反模式
? ? ?第14章:對未知的恐懼。
? ? ? ? ? ?目標(biāo):辨別懸空值 SQL支持一個(gè)特殊的空值,NULL。
? ? ? ? ? ? ? 增加記錄時(shí)使用NULL代替那些還不確定的值。
? ? ? ? ? ? ?一個(gè)給定的列如果沒有合適的值,可以使用NULL代替。
? ? ? ? ? ? ?當(dāng)傳入?yún)?shù)無效時(shí),一個(gè)函數(shù)的返回值也可以是NULL。
? ? ? ? ? ? 在外聯(lián)結(jié)查詢中,NULL被用來當(dāng)做未匹配的列的占位符。
? ? ? ? ?反模式:將NULL作為普通的值,反之亦然。
? ? ? ? ? ? ?1.在表達(dá)式中使用NULL。 如果某個(gè)字段為NULL,表達(dá)式結(jié)果也是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.在查詢參數(shù)中使用NULL 不能在查詢參數(shù)assin_to=?傳入NULL值
? ? ? ? ? ? ?4.避免上述問題:使用默認(rèn)值來代替NULL,按時(shí)查詢計(jì)算時(shí)仍然需要制定<>默認(rèn)值
? ? ? ? 解決方案:將NULL視為特殊值
? ? ? ? ? ? ? ?1.在標(biāo)量表達(dá)式中使用NULL 表達(dá)式中一個(gè)值為NULL,則結(jié)果就為NULL。
? ? ? ? ? ? ? ?2.在布爾表達(dá)式中使用NULL。
? ? ? ? ? ? ? 3.檢索NULL值。SQL-99中額外定義了一個(gè)比較斷言 IS DISTINCT FROM
? ? ? ? ? ? ? 4.聲明NOT NULL列。
? ? ? ? ? ? ?5.動態(tài)默認(rèn)值。使用COALESCE()函數(shù)返回一個(gè)非NULL的參數(shù)。
? ? ? ? ?使用NULL來表示任意類型的懸空值。
? ?第15章:模棱兩可的分組
? ? ? ?目標(biāo):獲取每組的最大值
? ? ? ?反模式: 引用非分組列
? ? ? ? ? ? ? ?1.單值規(guī)則 一個(gè)分組只能返回單一的值?
? ? ? ? ? ? ? ?2.我想要的查詢 如果分組后通過max獲得的有兩列的值是一樣的,那么就無法返回哪條記錄的其他列。不能使用max和min兩個(gè)聚合函數(shù)定位到不同的記錄。
? ? ? ?解決方案: 無歧義的使用列
? ? ? ? ? ? ? ?1.只查詢功能依賴的列;
? ? ? ? ? ? ? 2.使用關(guān)聯(lián)子查詢
? ? ? ? ? ? ? 3.使用衍生表
? ? ? ? ? ? ? 4.使用join?
? ? ? ? ? ? ? 5.對額外的列使用聚合函數(shù)
? ? ? ? ? ? ?6.連接同組所有值 mysql使用GROUP_CONCAT()函數(shù)將這一組中所有的值連在一起。
? ? ? ? 遵循單值規(guī)則,避免獲得模棱兩可的查詢結(jié)果。
? ? 第16章:隨機(jī)選擇 ?設(shè)計(jì)一個(gè)隨機(jī)廣告展示的查詢
? ? ? ? 目標(biāo):獲得樣壞死記錄
? ? ? ?反模式:隨機(jī)排序 select * from bugs order by rand() limit 1; 使用rand()簡單,但是無法利用索引,因?yàn)闆]有索引會基于隨機(jī)函數(shù)返回的值,導(dǎo)致一次全表排序。
? ? ? ?解決方案:沒有具體的順序。
? ? ? ? ? ? ? ? ?1.從1到最大值之間隨機(jī)選擇 ?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.選擇下一個(gè)最大值。 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.獲得所有鍵值,隨機(jī)選擇一個(gè)。 程序選擇一個(gè),查詢兩次
? ? ? ? ? ? ? ? 4.使用偏移量選擇隨機(jī)行。
? ? ? ? ? ? ? ? 5.專有解決方案。SQL server 使用tablesample函數(shù)。 oracle使用sample函數(shù)。
? ? ? ? 有些查詢是無法優(yōu)化的,換種方式試試看。
? ? ?第17章:可憐人的搜索引擎
? ? ? ? ?目標(biāo):全文檢索
? ? ? ? ?反模式:模糊匹配斷言 SQL提供了模式匹配斷言來比較字符串,最常用的就是like語句。還有REGEXP正則表達(dá)式匹配。 不過缺點(diǎn)當(dāng)然就是性能問題了。
? ? ? ? ?解決方案:使用正確的工具
? ? ? ? ? ? ? ? ? 1.數(shù)據(jù)庫擴(kuò)展 mysql能夠?qū)har,varchar,text定義一個(gè)全文索引,使用match進(jìn)行全文查詢.oracle使用context支持,然后通過contains()操作符搜索.sqlserver和postgreSQL也有對全文索引的支持.
? ? ? ? ? ? ? ? 2.第三方搜索引擎:Sphinx search lucene
? ? ? ? ?你不必使用SQL來解決所有的問題.
? ? ? 第18章:意大利苗條查詢
? ? ? ? ? 目標(biāo):減少sql查詢數(shù)量
? ? ? ? ? 反模式:使用一部操作解決復(fù)雜問題
? ? ? ? ? ? ? ? ? 1.副作用 查詢多少bug已經(jīng)修復(fù),多少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,而實(shí)際上12個(gè)fixed,7個(gè)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標(biāo)記 ?根據(jù)單個(gè)結(jié)果集再使用union all合并。
? ? ? ? ? 盡管SQL支持用一行代碼解決復(fù)雜的問題,但也別做不切實(shí)際的事情。
? ? ? ?第19章:隱式的列 ?聯(lián)合查詢?nèi)绻麅蓚€(gè)表有列名一樣的列,則會只取其中一個(gè)。
? ? ? ? ? ? 目標(biāo):減少輸入 ?可以使用*獲取所有列
? ? ? ? ? ? 反模式:捷徑會讓你迷失方向
? ? ? ? ? ? ? ? ? ? 1.破壞代碼重構(gòu),比如增加一列之后,原來insert沒有指定列名的則現(xiàn)在會報(bào)錯(cuò),少一列的值了。 select也一樣,如果刪除一列后,應(yīng)用代碼獲取列的可能也會出錯(cuò)。
? ? ? ? ? ? ? ? ? ? 2.隱藏的開銷 獲取一些沒用的列,會增加網(wǎng)絡(luò)開銷和性能。
? ? ? ? ? ? 解決方案:明確列出列名
? ? ? ? ? ?隨便拿,但是拿了就必須吃掉。
? ? ?
? ? 應(yīng)用程序開發(fā)反模式
? ? ? ? ? ?第20章 明文密碼
? ? ? ? ? ? ? 目標(biāo):恢復(fù)和重置密碼 ?現(xiàn)在一般都是使用郵箱讓用戶恢復(fù)和重置密碼
? ? ? ? ? ? ? 反模式:使用明文存儲密碼
? ? ? ? ? ? ? ? ? ? ? 1.存儲密碼 sql被劫持?
? ? ? ? ? ? ? ? ? ? ? 2.驗(yàn)證密碼
? ? ? ? ? ? ? ? ? ? ? 3.在email中發(fā)送密碼,email有可能被劫持
? ? ? ? ? ? ?解決方案:先哈希,后存儲
? ? ? ? ? ? ? ? ? ? mysql擴(kuò)展支持SHA2()函數(shù)返回256位的哈希串
? ? ? ? ? ? ? ? ? ? 哈希暴力破解,可以先加密后再進(jìn)行哈希。
? ? ? ? ? ? ? ? ? ? 在SQL中隱藏密碼,在程序中生成哈希串之后,在sql中直接使用哈希串。
? ? ? ? ? ? ? ? ? ?重置密碼,而非恢復(fù)密碼。
? ? ? ? ? ? 如果密碼對你可讀,那么對于攻擊者也是如此。
? ? ? ? ? 第21章:SQL注入
? ? ? ? ? ? ? 目標(biāo):編寫SQL動態(tài)查詢
? ? ? ? ? ? ? 反模式:將未經(jīng)驗(yàn)證的輸入作為代碼執(zhí)行
? ? ? ? ? ? ? 解決方案:不相信任何人
? ? ? ? ? ? ? ? ? ? 1.過濾輸入內(nèi)容。
? ? ? ? ? ? ? ? ? ? 2.參數(shù)化動態(tài)內(nèi)容
? ? ? ? ? ? ? ? ? ? 3.給動態(tài)輸入的值加引號
? ? ? ? ? ? ? ? ? ? 4.將用戶與代碼隔離
? ? ? ? ? ? ? ? ? ?5.找個(gè)可靠的人來幫你審查代碼
? ? ? ? ? ? ? 讓用戶輸入內(nèi)容,但永遠(yuǎn)別讓用戶輸入代碼。
? ? ? ? ? 第22章:偽鍵潔癖
? ? ? ? ? ? ? ? 目標(biāo):整理數(shù)據(jù)
? ? ? ? ? ? ? ? 反模式:填充角落
? ? ? ? ? ? ? ? ? ? ?1.不按照順序分配編號?
? ? ? ? ? ? ? ? ? ? 2.為現(xiàn)有行重新編號
? ? ? ? ? ? ? ? ? ? 3.制造數(shù)據(jù)差異 重新主鍵不是一個(gè)好習(xí)慣
? ? ? ? ? ? ? ?解決方案:1.定義行號。2.使用GUID 太長,隨機(jī)的,需要16字節(jié)
? ? ? ? ? ? ? ?將偽鍵當(dāng)做行的唯一性標(biāo)識,但他們不是行號。
? ? ? ? ?第23章:非禮勿視
? ? ? ? ? ? ? 目標(biāo):寫更少的代碼
? ? ? ? ? ? ? 反模式:無米之炊 忽略數(shù)據(jù)庫API的返回值,將程序代碼跟SQL混在一起
? ? ? ? ? ? ? ? ? 1.沒有診斷的診斷 在多條sql順序執(zhí)行過程中,最好對結(jié)果進(jìn)行診斷,保證錯(cuò)誤能夠快速定位。
? ? ? ? ? ? ? ? ? 2.字里行間 花費(fèi)大量時(shí)間調(diào)試生成sql字符串的代碼
? ? ? ? ? ? ? 解決方案:優(yōu)雅的從錯(cuò)誤中恢復(fù)
? ? ? ? ? ? ? ? ?1.保持節(jié)奏 檢查數(shù)據(jù)庫API的返回狀態(tài)和異常。
? ? ? ? ? ? ? ? ?2.回溯你的腳步。sql語句記錄,輸出,調(diào)試。
? ? ? ? ? 發(fā)現(xiàn)并解決代碼中的問題已經(jīng)很苦難了,就別再盲目的干了。
? ? ? ? 第24章:外交豁免權(quán)
? ? ? ? ? ? ? 目標(biāo):最佳實(shí)踐 使用版本控制工具管理源代碼,編寫單元測試腳本;編寫文檔,代碼注釋。
? ? ? ? ? ? ? 反模式:將SQL視為二等公民。
? ? ? ? ?目標(biāo):存儲多值屬性 ?為一個(gè)bug設(shè)置多個(gè)標(biāo)簽
? ? ? ? ?反模式:創(chuàng)建多個(gè)列,為bugs創(chuàng)建tag1,tag2,tag3幾個(gè)列保存標(biāo)簽。標(biāo)簽必須放于其中一個(gè)。
? ? ? ? ? ? ? ? ?1.查詢數(shù)據(jù),比如搜索這三列,可以使用in語句
? ? ? ? ? ? ? ? ?2.添加和刪除 ?update bugs set tag1=nullif(tag1,'perfor'),tag2=nullif(tag2,'perfor'),tag3=nullif(tag3,'perfor')這個(gè)能把值更新到其中為空的那一列,如果都不為空,則不作更新。 ? ? ? ? ? ?3.確保唯一性。無法確保三列的值不一樣。
? ? ? ? ? ? ? ? 4.處理不斷增長的值集。三列可能不夠用,如果在不斷的增加列,性能開銷將越來越大,而且sql查詢更新將越來越復(fù)雜。
? ? ? ? ?解決方案:創(chuàng)建從屬表 ?
? ? ? ? ?將具有同樣意義的值存在同一列中。
? ?第九章:元數(shù)據(jù)分類 ?案例:為一個(gè)客戶表增加每年的收入情況,每年的收入情況都存在單獨(dú)的列中,導(dǎo)致每一年就需要新增一個(gè)列存儲該年的收入。
? ? ? ? ? ?目標(biāo):支持可擴(kuò)展性
? ? ? ? ? ?反模式: 克隆表與克隆列
? ? ? ? ? ? ? ? 1.不斷產(chǎn)生的新表 按照年份對bug表進(jìn)行拆分,拆成bugs_2008,bugs_2009等多張表,然后按照需要需要修改對應(yīng)的sql。
? ? ? ? ? ? ? ?2.管理數(shù)據(jù)完整性 如果有數(shù)據(jù)被誤寫到其他表中,則可能導(dǎo)致統(tǒng)計(jì)的一年的bugs數(shù)之類的數(shù)據(jù)不準(zhǔn)確。沒有任何辦法自動對數(shù)據(jù)和相關(guān)表名做限制。但可以在每張表創(chuàng)建的時(shí)候使用check的約束。
? ? ? ? ? ? ? ?3.同步數(shù)據(jù) ?如果發(fā)現(xiàn)某條記錄原來是在2009這個(gè)表中,但時(shí)間弄錯(cuò)了,需要修改為2008的,這樣訂正數(shù)據(jù)就比較麻煩。需要好幾條sql。
? ? ? ? ? ? ? 4.確保唯一性。 如果需要做數(shù)據(jù)遷移,則需要保證記錄的主鍵id值不會與目標(biāo)表的主鍵記錄沖突。而且對于那些只支持單表ID唯一的數(shù)據(jù)庫產(chǎn)品,實(shí)現(xiàn)這樣的功能還需要定義一張額外的表存儲產(chǎn)品主鍵的值。
? ? ? ? ? ? ? 5.跨表查詢 ?如果需要查詢所有的bugs數(shù),則需要把每個(gè)表用union進(jìn)行查詢.
? ? ? ? ? ? ? 6.同步元數(shù)據(jù).如果值在某個(gè)表增加一列,其他表沒有增加,則聯(lián)合查詢不用使用*,需要列出所有列名.
? ? ? ? ? ? ?7.管理引用完整性. 其他表就不能引用bugs的外鍵了,因?yàn)橛卸鄠€(gè)bugs表.
? ? ? ? ? ? ?8.標(biāo)識元數(shù)據(jù)分裂列 如果有其他表保護(hù)bugx_fiexd_2008,bugx_fixed_2009,則以后肯定需要增加bugx_fixed_2010.
? ? ? ? ? 解決方案: 手工分割表的一個(gè)合理使用場景是歸檔數(shù)據(jù)。把沒用的數(shù)據(jù)遷移到歷史表中。
? ? ? ? ? ? ? ?1.使用水平分區(qū)。MYSQL5.1所支持的分區(qū)特性,在createtable時(shí)執(zhí)行pritition by hash(year(date)reported)) partitions 4.
? ? ? ? ? ? ? ?2.使用垂直分區(qū)。根據(jù)列來對表進(jìn)行拆分。將一些BLOB或者TEXT字段拆分到其他表存儲。
? ? ? ? ? ? ? ?3.解決元數(shù)據(jù)分裂列。創(chuàng)建關(guān)聯(lián)表。
? ? ? ? ? 別讓數(shù)據(jù)繁衍元數(shù)據(jù)。
? ?物理數(shù)據(jù)庫設(shè)計(jì)反模式
? ? ? ?第10章:取整錯(cuò)誤。
? ? ? ? ? 目標(biāo):使用小數(shù)取代整數(shù),運(yùn)算結(jié)果必須準(zhǔn)確。
? ? ? ? ? 反模式:使用float類型
? ? ? ? ? ? ? ? ? 無限循環(huán)小數(shù)無法使用存儲表示。
? ? ? ? ? ? ? ? ? 在SQL中使用FLOAT類型,放大查詢結(jié)果差異比較大。無法使用比較操作,必須使用近似相等查詢,但是閥值需要使用合適。
? ? ? ? ? 解決方案:oracle的FLOAT類型表示的是精確值,而BINARY_FLOAT則是非精確值。
? ? ? ? ? ? ? ? ? ?使用NUMERIC類型。SQL的NUMERIC或者DECIMAL類型來代替FLOAT存儲小數(shù)。 NUMERIC(9,2) 精度,刻度 這樣仍然無法存儲無限精度的數(shù)據(jù)。
? ? ? ? ? 盡可能不要使用浮點(diǎn)數(shù)。
? ? ?第11章:每日新花樣 ?需要給稱呼列加入約束指定這些候選值
? ? ? ? ? 目標(biāo):限定列的有效值 ?希望數(shù)據(jù)庫能夠拒絕無效值的輸入
? ? ? ? ? 反模式:在列定義上指定可選值。很多數(shù)據(jù)庫設(shè)計(jì)人員習(xí)慣在定義列的時(shí)候指定所有可選的有效數(shù)據(jù)。
? ? ? ? ? ? ? ? ? ?create table bugs(status varchar(20) check(status in('new','in','fixed')).
? ? ? ? ? ? ? ? ? ?mysql也支持用ENUM關(guān)鍵詞來約束。但是mysql存儲的是序數(shù),而非字符串。
? ? ? ? ? ? ? ? ? 1.中間的是哪個(gè) ?無法獲得status列中值的枚舉列表,如果使用distinct來查詢bugs表,但是剛開始沒數(shù)據(jù),查詢的結(jié)果為空。如果使用INFORMATION_SHEMA系統(tǒng)視圖,則還需要解決解決格式。
? ? ? ? ? ? ? ? ?2.添加新口味。添加或者刪除一個(gè)候選值。沒有什么語法支持從ENUM或者check約束中添加或者刪除一個(gè)值。只能用一個(gè)新的集合重新定義這一列。一些數(shù)據(jù)庫只有在表為空表是才能改變某一列的數(shù)據(jù)。那么就需要先將數(shù)據(jù)導(dǎo)出,改變之后再導(dǎo)入。
? ? ? ? ? ? ? ? ?3.老的口味永不消失。舊的值無法刪除。
? ? ? ? ? ? ? ? 4.可一致性地下。check約束,域和UDT在各種數(shù)據(jù)庫支持形式不同意。ENUM是mysql特有的特性。
? ? ? ? 解決方案:在數(shù)據(jù)中指定值,通過創(chuàng)建一張檢查表bug_status,定義status列中出現(xiàn)的候選值,然后定義一個(gè)外鍵約束。
? ? ? ? ? ? ? ? ?1.查詢候選值集合。直接查詢檢查表。
? ? ? ? ? ? ? ? ?2.更新檢查表中的數(shù)據(jù)。插入更新操作很方便。
? ? ? ? ? ? ? ? ?3.支持廢棄數(shù)據(jù)??梢酝ㄟ^在bug_status表增加一列來表示是否已經(jīng)棄用。
? ? ? ? ? ? ? ? 4.良好的可移植性。
? ? ? ? 在驗(yàn)證固定集合的候選值時(shí)使用元數(shù)據(jù)。在驗(yàn)證可變集合的候選值時(shí)使用數(shù)據(jù)。
? ? ?第12章:幽靈文件 ?只保存數(shù)據(jù)庫文件,沒有保存數(shù)據(jù)庫中保存的文件路徑對應(yīng)的數(shù)據(jù)庫外的文件。
? ? ? ? ? ?目標(biāo):存儲圖片或其他多媒體大文件。
? ? ? ? ? ?反模式:假設(shè)必須使用文件系統(tǒng),可以使用BLOB字段存儲文件,或者只在數(shù)據(jù)庫存儲文件路徑。
? ? ? ? ? ? ? ? ? 1.文件不支持DELETE ?垃圾回收問題。如果圖片在數(shù)據(jù)庫之外,刪除某條記錄之后無法自動將對應(yīng)文件刪除。
? ? ? ? ? ? ? ? ? 2.文件不支持事務(wù)隔離。數(shù)據(jù)庫事務(wù)在提交之前,所有改變對外都不可見。但是數(shù)據(jù)庫之外的文件改變則立刻體現(xiàn)到外界。
? ? ? ? ? ? ? ? ? 3.文件不支持回滾操作。數(shù)據(jù)庫可以回滾,但是文件系統(tǒng)無法回滾。
? ? ? ? ? ? ? ? ?4.文件不支持?jǐn)?shù)據(jù)庫備份工具。
? ? ? ? ? ? ? ? ?5.文件不支持SQL的訪問權(quán)限設(shè)置。
? ? ? ? ? ? ? ? ?6.文件不是SQL數(shù)據(jù)類型。無法驗(yàn)證文件路徑是否正確。
? ? ? ? ? 解決方案:在需要時(shí)使用BLOB類型。
? ? ? ? ? ? ? ? ? ?MYSQL MEDIUMBLOB:16M oracle:LONGRAW 2GB
? ? ? ? ? ? ? ? ? ?MYSQL有l(wèi)oad_file()用來讀取一個(gè)文件存儲到BLOB列
? ? ? ? ? 存儲在數(shù)據(jù)庫之外的數(shù)據(jù)不由數(shù)據(jù)庫管理。
? ? 第13章: 亂用索引
? ? ? ? ? 目標(biāo):優(yōu)化性能
? ? ? ? ? 反模式:無規(guī)劃的使用索引
? ? ? ? ? ? ? ? ? 1.無索引
? ? ? ? ? ? ? ? ? 2.索引過多 ?不需使用的索引無法獲得任何好處,只有開銷。
? ? ? ? ? ? ? ? ? 3.索引也無能為力 常犯的錯(cuò)誤是進(jìn)行一個(gè)無法使用索引的查詢
? ? ? ? ? 解決方案:所有不重復(fù)的值的記錄和總計(jì)數(shù)條數(shù)之比越低,索引的效率就越低。
? ? ? ? ? ? ? ? ?1.測量 ?ORACLE:TKProf mysql:慢查詢?nèi)罩?br /> ? ? ? ? ? ? ? ? ?2.解釋 ?查詢執(zhí)行計(jì)劃
? ? ? ? ? ? ? ? ?3.挑選 ? 索引覆蓋
? ? ? ? ? ? ? ? ?4.測試?
? ? ? ? ? ? ? ? ?5.優(yōu)化 ?索引預(yù)載入:mysql使用 load index into cache語句。
? ? ? ? ? ? ? ? ?6.重建:更新或者刪除導(dǎo)致索引修改,需要定期對索引進(jìn)行維護(hù)。mysql:analyze table or optimize table oracle:alter index rebuild
? ? ? ? ?了解你的數(shù)據(jù),了解你的查詢請求,然后MENTOR你的索引。
? ?
? 查詢反模式
? ? ?第14章:對未知的恐懼。
? ? ? ? ? ?目標(biāo):辨別懸空值 SQL支持一個(gè)特殊的空值,NULL。
? ? ? ? ? ? ? 增加記錄時(shí)使用NULL代替那些還不確定的值。
? ? ? ? ? ? ?一個(gè)給定的列如果沒有合適的值,可以使用NULL代替。
? ? ? ? ? ? ?當(dāng)傳入?yún)?shù)無效時(shí),一個(gè)函數(shù)的返回值也可以是NULL。
? ? ? ? ? ? 在外聯(lián)結(jié)查詢中,NULL被用來當(dāng)做未匹配的列的占位符。
? ? ? ? ?反模式:將NULL作為普通的值,反之亦然。
? ? ? ? ? ? ?1.在表達(dá)式中使用NULL。 如果某個(gè)字段為NULL,表達(dá)式結(jié)果也是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.在查詢參數(shù)中使用NULL 不能在查詢參數(shù)assin_to=?傳入NULL值
? ? ? ? ? ? ?4.避免上述問題:使用默認(rèn)值來代替NULL,按時(shí)查詢計(jì)算時(shí)仍然需要制定<>默認(rèn)值
? ? ? ? 解決方案:將NULL視為特殊值
? ? ? ? ? ? ? ?1.在標(biāo)量表達(dá)式中使用NULL 表達(dá)式中一個(gè)值為NULL,則結(jié)果就為NULL。
? ? ? ? ? ? ? ?2.在布爾表達(dá)式中使用NULL。
? ? ? ? ? ? ? 3.檢索NULL值。SQL-99中額外定義了一個(gè)比較斷言 IS DISTINCT FROM
? ? ? ? ? ? ? 4.聲明NOT NULL列。
? ? ? ? ? ? ?5.動態(tài)默認(rèn)值。使用COALESCE()函數(shù)返回一個(gè)非NULL的參數(shù)。
? ? ? ? ?使用NULL來表示任意類型的懸空值。
? ?第15章:模棱兩可的分組
? ? ? ?目標(biāo):獲取每組的最大值
? ? ? ?反模式: 引用非分組列
? ? ? ? ? ? ? ?1.單值規(guī)則 一個(gè)分組只能返回單一的值?
? ? ? ? ? ? ? ?2.我想要的查詢 如果分組后通過max獲得的有兩列的值是一樣的,那么就無法返回哪條記錄的其他列。不能使用max和min兩個(gè)聚合函數(shù)定位到不同的記錄。
? ? ? ?解決方案: 無歧義的使用列
? ? ? ? ? ? ? ?1.只查詢功能依賴的列;
? ? ? ? ? ? ? 2.使用關(guān)聯(lián)子查詢
? ? ? ? ? ? ? 3.使用衍生表
? ? ? ? ? ? ? 4.使用join?
? ? ? ? ? ? ? 5.對額外的列使用聚合函數(shù)
? ? ? ? ? ? ?6.連接同組所有值 mysql使用GROUP_CONCAT()函數(shù)將這一組中所有的值連在一起。
? ? ? ? 遵循單值規(guī)則,避免獲得模棱兩可的查詢結(jié)果。
? ? 第16章:隨機(jī)選擇 ?設(shè)計(jì)一個(gè)隨機(jī)廣告展示的查詢
? ? ? ? 目標(biāo):獲得樣壞死記錄
? ? ? ?反模式:隨機(jī)排序 select * from bugs order by rand() limit 1; 使用rand()簡單,但是無法利用索引,因?yàn)闆]有索引會基于隨機(jī)函數(shù)返回的值,導(dǎo)致一次全表排序。
? ? ? ?解決方案:沒有具體的順序。
? ? ? ? ? ? ? ? ?1.從1到最大值之間隨機(jī)選擇 ?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.選擇下一個(gè)最大值。 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.獲得所有鍵值,隨機(jī)選擇一個(gè)。 程序選擇一個(gè),查詢兩次
? ? ? ? ? ? ? ? 4.使用偏移量選擇隨機(jī)行。
? ? ? ? ? ? ? ? 5.專有解決方案。SQL server 使用tablesample函數(shù)。 oracle使用sample函數(shù)。
? ? ? ? 有些查詢是無法優(yōu)化的,換種方式試試看。
? ? ?第17章:可憐人的搜索引擎
? ? ? ? ?目標(biāo):全文檢索
? ? ? ? ?反模式:模糊匹配斷言 SQL提供了模式匹配斷言來比較字符串,最常用的就是like語句。還有REGEXP正則表達(dá)式匹配。 不過缺點(diǎn)當(dāng)然就是性能問題了。
? ? ? ? ?解決方案:使用正確的工具
? ? ? ? ? ? ? ? ? 1.數(shù)據(jù)庫擴(kuò)展 mysql能夠?qū)har,varchar,text定義一個(gè)全文索引,使用match進(jìn)行全文查詢.oracle使用context支持,然后通過contains()操作符搜索.sqlserver和postgreSQL也有對全文索引的支持.
? ? ? ? ? ? ? ? 2.第三方搜索引擎:Sphinx search lucene
? ? ? ? ?你不必使用SQL來解決所有的問題.
? ? ? 第18章:意大利苗條查詢
? ? ? ? ? 目標(biāo):減少sql查詢數(shù)量
? ? ? ? ? 反模式:使用一部操作解決復(fù)雜問題
? ? ? ? ? ? ? ? ? 1.副作用 查詢多少bug已經(jīng)修復(fù),多少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,而實(shí)際上12個(gè)fixed,7個(gè)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標(biāo)記 ?根據(jù)單個(gè)結(jié)果集再使用union all合并。
? ? ? ? ? 盡管SQL支持用一行代碼解決復(fù)雜的問題,但也別做不切實(shí)際的事情。
? ? ? ?第19章:隱式的列 ?聯(lián)合查詢?nèi)绻麅蓚€(gè)表有列名一樣的列,則會只取其中一個(gè)。
? ? ? ? ? ? 目標(biāo):減少輸入 ?可以使用*獲取所有列
? ? ? ? ? ? 反模式:捷徑會讓你迷失方向
? ? ? ? ? ? ? ? ? ? 1.破壞代碼重構(gòu),比如增加一列之后,原來insert沒有指定列名的則現(xiàn)在會報(bào)錯(cuò),少一列的值了。 select也一樣,如果刪除一列后,應(yīng)用代碼獲取列的可能也會出錯(cuò)。
? ? ? ? ? ? ? ? ? ? 2.隱藏的開銷 獲取一些沒用的列,會增加網(wǎng)絡(luò)開銷和性能。
? ? ? ? ? ? 解決方案:明確列出列名
? ? ? ? ? ?隨便拿,但是拿了就必須吃掉。
? ? ?
? ? 應(yīng)用程序開發(fā)反模式
? ? ? ? ? ?第20章 明文密碼
? ? ? ? ? ? ? 目標(biāo):恢復(fù)和重置密碼 ?現(xiàn)在一般都是使用郵箱讓用戶恢復(fù)和重置密碼
? ? ? ? ? ? ? 反模式:使用明文存儲密碼
? ? ? ? ? ? ? ? ? ? ? 1.存儲密碼 sql被劫持?
? ? ? ? ? ? ? ? ? ? ? 2.驗(yàn)證密碼
? ? ? ? ? ? ? ? ? ? ? 3.在email中發(fā)送密碼,email有可能被劫持
? ? ? ? ? ? ?解決方案:先哈希,后存儲
? ? ? ? ? ? ? ? ? ? mysql擴(kuò)展支持SHA2()函數(shù)返回256位的哈希串
? ? ? ? ? ? ? ? ? ? 哈希暴力破解,可以先加密后再進(jìn)行哈希。
? ? ? ? ? ? ? ? ? ? 在SQL中隱藏密碼,在程序中生成哈希串之后,在sql中直接使用哈希串。
? ? ? ? ? ? ? ? ? ?重置密碼,而非恢復(fù)密碼。
? ? ? ? ? ? 如果密碼對你可讀,那么對于攻擊者也是如此。
? ? ? ? ? 第21章:SQL注入
? ? ? ? ? ? ? 目標(biāo):編寫SQL動態(tài)查詢
? ? ? ? ? ? ? 反模式:將未經(jīng)驗(yàn)證的輸入作為代碼執(zhí)行
? ? ? ? ? ? ? 解決方案:不相信任何人
? ? ? ? ? ? ? ? ? ? 1.過濾輸入內(nèi)容。
? ? ? ? ? ? ? ? ? ? 2.參數(shù)化動態(tài)內(nèi)容
? ? ? ? ? ? ? ? ? ? 3.給動態(tài)輸入的值加引號
? ? ? ? ? ? ? ? ? ? 4.將用戶與代碼隔離
? ? ? ? ? ? ? ? ? ?5.找個(gè)可靠的人來幫你審查代碼
? ? ? ? ? ? ? 讓用戶輸入內(nèi)容,但永遠(yuǎn)別讓用戶輸入代碼。
? ? ? ? ? 第22章:偽鍵潔癖
? ? ? ? ? ? ? ? 目標(biāo):整理數(shù)據(jù)
? ? ? ? ? ? ? ? 反模式:填充角落
? ? ? ? ? ? ? ? ? ? ?1.不按照順序分配編號?
? ? ? ? ? ? ? ? ? ? 2.為現(xiàn)有行重新編號
? ? ? ? ? ? ? ? ? ? 3.制造數(shù)據(jù)差異 重新主鍵不是一個(gè)好習(xí)慣
? ? ? ? ? ? ? ?解決方案:1.定義行號。2.使用GUID 太長,隨機(jī)的,需要16字節(jié)
? ? ? ? ? ? ? ?將偽鍵當(dāng)做行的唯一性標(biāo)識,但他們不是行號。
? ? ? ? ?第23章:非禮勿視
? ? ? ? ? ? ? 目標(biāo):寫更少的代碼
? ? ? ? ? ? ? 反模式:無米之炊 忽略數(shù)據(jù)庫API的返回值,將程序代碼跟SQL混在一起
? ? ? ? ? ? ? ? ? 1.沒有診斷的診斷 在多條sql順序執(zhí)行過程中,最好對結(jié)果進(jìn)行診斷,保證錯(cuò)誤能夠快速定位。
? ? ? ? ? ? ? ? ? 2.字里行間 花費(fèi)大量時(shí)間調(diào)試生成sql字符串的代碼
? ? ? ? ? ? ? 解決方案:優(yōu)雅的從錯(cuò)誤中恢復(fù)
? ? ? ? ? ? ? ? ?1.保持節(jié)奏 檢查數(shù)據(jù)庫API的返回狀態(tài)和異常。
? ? ? ? ? ? ? ? ?2.回溯你的腳步。sql語句記錄,輸出,調(diào)試。
? ? ? ? ? 發(fā)現(xiàn)并解決代碼中的問題已經(jīng)很苦難了,就別再盲目的干了。
? ? ? ? 第24章:外交豁免權(quán)
? ? ? ? ? ? ? 目標(biāo):最佳實(shí)踐 使用版本控制工具管理源代碼,編寫單元測試腳本;編寫文檔,代碼注釋。
? ? ? ? ? ? ? 反模式:將SQL視為二等公民。
轉(zhuǎn)載于:https://www.cnblogs.com/zhwj184/archive/2012/07/22/3119630.html
總結(jié)
- 上一篇: A Star寻路相关资料汇总
- 下一篇: multisim页面不够大_multis