釋出了新一版的資料庫開發設計規範，增加了基於效能考慮的部分內容：

貼在這裡跟大家共享

基於效能考慮的原則：

所有的表需設定主鍵欄位，mysql innodb 引擎必須設定主鍵欄位，主鍵欄位不能太長，建議以獨立於業務資料的自增長數值欄位為主鍵欄位。
mysql 資料庫單行的長度應儘可能的小，主要是基於mysql 的資料塊的結構的考慮。
PG 資料庫頻繁更新的欄位與長文字欄位建議分離儲存，主要基於平pg資料庫的mvcc機制的考慮。
單表的索引數目應該控制在5個以內，同一個欄位上索引控制在2個以內，複合索引的欄位數控制在3個以內。針對mysql的covering index 最佳化原則，可以適當控制索引欄位數
where 條件中，限制資料量最嚴格的欄位一般建議作為複合索引的前導欄位，如果各欄位的選擇性相近，應把訪問最頻繁的欄位作為前導欄位。
索引應該具有高選擇性,原則count(distinct)/count(*) >0.6不應該給“性別列"新增索引,針對PG 資料庫，可以考慮針對某些欄位的高選擇性，建立條件索引。
Pg資料庫在同一個表上可以使用到多個索引，mysql 與oracle 在這方面有些欠缺，pg資料庫可以支撐較複雜的sql查詢，pg更適合於一個sql搞定所有的事情，而不是多個sql 搞定一個事情。
Mysql 資料庫不適合巢狀sql的執行，高併發下有效能問題，建議把巢狀sql 拆解為多個單表查詢的sql進行
group by 或者order by 的欄位應該設定為前導欄位（需根據業務調整）。
檢視中不允許order by 排序。
Mysql中order by 字句的欄位名如果落在不同的表上，會生成filesort，高併發下，嚴重影響效能。
Mysql 資料庫如果group by 確定不需要排序可以使用order by null 避免排序操作執行，mysql 測試效能可以提高15%左右，PG 不支援該操作，oracle 10g 預設是不排序的了。
針對mysql的字元型別欄位的查詢可以考慮新增字首索引，一般情況下前6個字元的索引已經可以達到3000w的區分度。（26^6=308915776）。
索引列不建議參與運算，索引列上的函式計算將導致索引失效，除非建立函式索引。
針對高併發的WEB應用，儘量避免使用外來鍵，一定要使用外來鍵約束，則外來鍵欄位上應建立索引。資料一致性要求高的應用，可謹慎使用外來鍵。
注意where條件中資料型別的一致性，特別是索引欄位，避免條件隱性轉換而造成不走索引，例如：cms系統中channelid為字元型如果條件 where chennalid='123'則能走索引，而where channelid=123 是不會走索引的，原因是 oracle 解析的時候做了下隱性轉換 where to_number(channel_id)=123.
PG 資料庫針對java jdbc 的遊標使用，必須設定autocommit=off 示例： con.setAutoCommit(false);

st =con.createStatement(ResultSet.TYPE_FO

RWARD_ONLY,

ResultSet.CONCUR_READ_ONLY);

st.setFetchSize(100000);

ResultSet rs = st.executeQuery(sql)；

mysql的query_cache 的快取原則是sql語句的hash儲存，sql語句中應該禁止使用執行時求值的函式，例如 where date 》 now（）應該修改為where date 》‘2012-01-01’這樣格式的靜態語句。
涉及到實時入庫的問題，針對計數資料入庫，建議先寫記憶體，然後批次入庫的策略，避免陷入效能問題。
針對資料庫某個欄位的修改，建議單獨設定函式/方法處理，而不應該將整行資料，一起回寫資料庫，在近期的幾個專案，出現過幾次，因為更新單個欄位而回寫整個物件，造成資料庫的大量的垃圾日誌資訊。
Sql語句的編寫，原則上應該使用繫結變數，pg 與oracle 資料庫必須使用繫結變數，優先選用jdbc 的prepareStatement()方法，mysql在這方面比較弱，沒有強制要求。
警惕基於limit m,n 方式的分頁操作，offset 越大，效能越差，應該考慮基於主鍵分頁方式。
Mysql資料庫的二級索引，在欄位的列表的最後，加上primarykey 欄位，在大多數情況下，可以改善最佳化器的最佳化策略，對一部分的排序操作有幫助，不會帶來額外的空間損耗，這個功能從原始碼級別上已經核實過了。
對錶欄位進行distinct 操作最好能轉換成group by 操作，雖然同樣是對錶執行全表掃描，但是group by 的優勢在於，掃描到相同的值時不再讀取，而distinct 是每筆資料都要抓到記憶體去排序的。用group by減少了I/O操作。

資料庫設計開發規範--基於效能的考慮部分

相關文章