Mysql資料庫效能

標籤：帶來   應該   尾碼   備份   劃線   ext   html   原則   磁碟   

Mysql資料庫設計規範https://www.cnblogs.com/Luke-Me/p/8994432.html

　　我們在項目一開始的設計中，就要忙著考慮資料庫的設計，表、欄位、索引、sql等等，而在項目比較大型的時候，團隊開發中由於多人同時進行，那麼儘早的進行設計規範是項目開發非常關鍵的一步，那麼關於資料庫設計規範有哪些呢，包括以下6項：　

複製代碼
1.資料庫命名規範

　　2.資料庫基本設計規範

　　3.資料庫索引設計規範

　　4.資料庫欄位設計規範　　

　　5.SQL開發規範

　　6.資料庫操作行為規範
複製代碼
接下來逐一詳細介紹一下各個規範：

1.資料庫命名規範：

　　1）所有資料庫物件名稱必須使用小寫字母並用底線分割（MySQL對大小寫是敏感的）

　　2）禁止使用MySQL的保留關鍵字（比如：select user,from,age from tb_user 這時候識別不出from關鍵字，除非使用from，反向單引號來區別）

　　3）資料庫物件的命名最後能夠見名識義，並且最好不要超過32個字元 ，例如：使用者資料庫 bd_userdb (百度_使用者資料庫) ，使用者帳號表 user_account

　　4）臨時庫表必須以tmp首碼並以日期為尾碼，tmp_user_20180505 提供更加明確的表名

　　　 備份庫，備份表必須以bak首碼並以日期為尾碼，bak_userdb_20180504 bak_user_20180505

　　5）所有儲存相同資料的列名和類型必須一致，例如：不同庫表中的user_id（int unsigned not null），那麼名稱和類型必須是一致的，否則會產生查詢效率降低等各種問題

2.資料庫基本設計規範：

　　1）所有表必須使用InnoDB儲存引擎。在MySQL5.6以後，innodb已經成為了預設儲存引擎，它支援事務、行級鎖，更好的恢複性，高並發下效能更好

　　2）資料庫和表的字元集統一使用UTF-8。為了避免亂碼、效能等問題

　　3）所有的表和欄位都需要添加註釋。使用comment添加備忘資訊，從一開始就進行資料庫字典的維護

　　4）盡量控制單表資料量的大小，建議控制在500萬行以內。使用曆史資料歸檔、分庫分表操作手段來控制資料量的大小

　　5）謹慎使用MySQL中的分區表。跨分區查詢效率比較低，建議採用物理分表的方式來管理大資料

　　6）盡量做到冷資料分離，減小表寬度。欄位太多的情況，盡量分表，將常用的放在一塊，不常用的欄位分到其他表中，有效減少磁碟的IO，保證熱資料的快取命中率

　　7）禁止在表中建立預留欄位。由於無法預知預留欄位的類型，後期對改欄位進行修改會耗費很多資源，對錶進行鎖定等問題

　　8）禁止儲存圖片、檔案等二進位檔案，造成MySQL的效能影響。這些應該儲存到專門的圖片、檔案伺服器中，資料庫中只儲存對應的資訊標識。

　　　 禁止線上上做資料庫壓力測試

　　　 禁止從開發環境、測試環境直連產生環境資料庫

3.索引設計規範：

　　1）限制每張表索引的數量，建議單表索引不超過5個 。索引並不是越多越好，能提高查詢效率，也能降低效率。應該根據實際情況來建立索引。

　　2）每個InnoDB表中必須有一個主鍵（唯一非空列）。不用使用頻繁更新的列為主鍵，不使用MD5，UUID,HASH,字串列作為主鍵。主鍵建議選擇使用自增ID值

　　3）常見索引列建議：where從句中的列 order by、group by、distinct 中的欄位，多表join的關聯列，如果在字句中是單個列，那就單獨索引，有多個列，那可以建立聯合索引

　　4）如何選擇索引列的順序，區分度最高（比如主鍵列）的列，欄位長度小，使用頻繁的列放在聯合索引的最左側

　　5）避免建立冗餘和重複的索引：index（a,b,c），index(a,b)，index(a) 對於a就是重複索引

　　6）對於頻繁的查詢優先考慮使用覆蓋索引：包含了所有查詢欄位的索引

　　7）盡量避免使用外鍵約束。外鍵會影響父表與子表的寫操作從而降低效能

4.資料庫欄位設計規範：

　　1）優先選擇符合儲存需要的最小資料類型。例如：將字串轉化為數字儲存

　　 對於非負數優先選用無符號型來儲存。例如:主鍵id，無符號比有符號多出一倍的儲存空間。

　　　有符號：signed int -2147483648 ~ 2147483647

　　　無符號：unsigned int 0 ~ 4294967295

　　　varchar(N) N代表的是字元數，而不是位元組數，使用UTF8儲存漢字varchar(255)=765個位元組

　　　過大的長度會消耗更多的記憶體，根據欄位長度來分配記憶體。

　　2）避免使用Text、Blob資料類型，若需要使用，盡量分配到專門的擴充表中

　　3）避免使用Enum枚舉類型。order by操作效率低。禁止使用數值作為ENum枚舉值

　　4）儘可能把所有列定義為NOT NULL。索引NULL列需要更多的儲存空間來儲存。索引會失效。

　　5）避免使用字串來儲存日期時間，使用TIMESTAMP或DATATIME來儲存時間

　　6）與財務相關的金額類型資料，必須使用Decimal類型。保證資料精度，計算時不丟失精度。

5.資料庫SQL開發規範：

　　1）建議使用先行編譯語句進行資料庫操作。只傳參數，比傳遞sql更加高效，相同語句一次解析之後，多次使用，節約sql解析的成本，提高處理效率。

　　2）避免資料類型的隱式轉換。隱式轉換導致索引失效，一般在where字句條件中出現的類型轉換，導致了索引失效。

　　3）合理利用已存在索引，而不是盲目添加索引。

　　　　避免使用雙%的查詢條件：like ‘%123%‘，只要出現首碼%，索引失效。

　　　　一個SQL只能利用到複合索引的一列進行範圍查詢，若聯合索引 index(a,b,c) 對a進行範圍查詢，那麼b和c將失效，應當將a放到最右側

　　　　使用left join 或 not exists 來最佳化 not in 操作，not in會使索引失效

　　4）程式串連不同資料庫時應該使用不同的帳號，禁止跨庫查詢

　　5）禁止使用 select * 必須使用 select <欄位列表> 查詢，消耗過多的IO和cpu以及網路頻寬資源

　　6）禁止使用不含欄位的insert 語句,為了減少表結構的變更帶來的影響：insert into table values(‘a‘,‘b‘,‘c‘); 應當指明要插入的列，insert into table(c1,c2,c3) values(‘a‘,‘b‘,‘c‘);

　　7）避免使用子查詢，可以將子查詢最佳化為join操作：子查詢都會建立暫存資料表，佔用cpu和io資源，子查詢結果集無法使用索引。

　　8）避免使用join關聯太多的表：

　　　　每關聯一張表，多佔用一部分記憶體（join_buffer_size）

　　　　會產生暫存資料表操作，影響查詢效率

　　　　MySQL最多允許關聯61張表，建議不超過5張表

　　9）減少同資料庫的互動次數

　　10）使用in代替or。in的值不超過500個，in可以有效使用索引，or不行。

　　11）禁止使用order by rand() 進行隨機排序，這個操作對效能有很大影響，盡量通過程式來得到隨機值再從資料庫中擷取資料。

　　12）禁止在where從句中對列進行函數轉換和計算，造成索引的失效。where data(createtime) = ‘2018-01-01‘ ，盡量在程式中進行計算

　　13）在明顯不會出現重複值的時候使用union all 而不是union。union會先載入所有資料到暫存資料表中然後去重，而union all不會去重。

　　14）拆分複雜的大SQL成多個小SQL。並存執行小SQL來提高處理效率

6.資料庫操作行為規範：

　　1）超過100萬行的批量寫操作，要分批多次進行操作：

　　　　大大量操作可能造成嚴重的主從延遲問題

　　　　binlog日誌為row格式時，胡產生大量的日誌，造成資源不足

　　　　避免產生大事務的操作

　　2）對於大表使用pt-online-schema-change工具來修改表結構。過程是：先建立新表，然後複製舊錶資料到新表，將新表名稱改成舊錶名稱，最後刪除舊錶

　　3）禁止為程式使用的帳號賦予super超管許可權

　　4）對於程式串連資料庫帳號，遵循許可權最小的原則。程式使用資料庫支行和只能在一個DB下使用，不準跨庫，程式使用的帳號原則上不準有drop許可權

以上就是MySQL的一些設計規範，當然不是說一定要遵循以上的原則，具體視實際應用情境而定，通過DBA指導來指定原則。

本文原創，轉載請標註出處：http://www.cnblogs.com/Luke-Me/p/8994432.html

Mysql資料庫效能