為部門整理的mysql_db使用軍規，整理mysql_db軍規

為部門整理的mysql_db使用軍規，整理mysql_db軍規
mysql_db使用軍規:

1、禁止開發測試人員在IDC環境手工刪除和修改資料

 

2、所有需求通過DB工具系統提交

 

3、禁止在IDC環境DB進行測試

 

4、IDC環境提交的sql語句一定要經過非正式環境驗證，且經過"explain sql;"檢驗過執行計畫有走索引

 

5、IDC環境庫表建立統一用小寫，庫表用英文簡稱，力求精簡

 

6、禁止web機器直連DB

 

7、每條記錄要儲存資料的建立和修改時間，表通常要有主鍵，使用innodb引擎

 

8、IDC環境db只授權增查改,刪除許可權DBA評估

 

9、預估和控制單表的資料量在百萬以內，資料量過大需清理或分表

 

10、IDC環境禁止使用mysql視圖，預存程序，觸發器，自訂函數

 

 

一、表的一句話最佳化

 

1.   int型不超過1000w，含char則不超過500w

數字和字元裝不下的情況，考慮多欄位。

 

2.   按時間分表,按主鍵模數/hash分表，按量分表

紅包是按量來的。

 

3.   限制單庫表的數量在萬級以內   

 

4.   拒絕text和blob類型

實在避免不了要用text和blob類型，拆表吧。或者弄成本地儲存，多機器分區儲存。

 

5.   分區的演算法可以按時間

比如天、月，便於針對性的查詢，命中率100%

 

 

二、欄位的一句話最佳化

 

1   長度可以冗餘，可適量10%左右    

tinyint(1Byte)smallint(2Byte)mediumint(3Byte)int(4Byte)bigint(8Byte)認清長度，選擇好類型。

 

2   你認識null嗎?

避免使用NULL欄位,因為NULL欄位很難查詢最佳化；NULL欄位的索引需要額外空間；NULL欄位的複合索引無效。

錯誤的例子：`Fpacket_name` char(32) default null。

正確的例子：`Fpacket_name` varchar(60) NOT NULL DEFAULT ''。

`Fface_value` int(10) unsigned NOT NULLDEFAULT '0'。"

 

3   業務上有關聯的欄位，要定義相同類型

相同的類型做語句操作有助於提高操作效率，減少轉換成本。

 

4   選擇類型請用數字、枚舉

數字表示意思的，來替代字串。

正確的例子：性別，0男，1女；時間用時間戳記的數字形式；IP用數字型等等。

 

三、語句的一句話最佳化

 

1     利用explain神器來最佳化語句利弊

Type結果集：顯示串連使用了何種類型。從最好到最差的連線類型為const、eq_reg、ref、range、index和all。

 

2     Truncate比delete要快

Delete 計數器不清零,按行刪, 慢。Truncate相當於刪掉重建,最快。大量刪除最好匯出有用資料,然後刪掉舊錶,新表重新命名。

 

3     用join代替子查詢

Join原理,nested loop。Left Join資料量小的在前面，Straight_JOIN。

 

4     內建函數運算

不要讓MYSQL用自己函數，他已經很累，盡量在程式內實現，比如now()，放到程式裡取到了再傳入給mysql處理。更不要在mysql去處理大邏輯運算。

 

5     要知道一條語句是依賴一個CPU核心的

一條語句一個核心，大語句可以拆開多語句，多核機共用，還可以減少mysql鎖時間。

 

6     不要select *

除非你查詢的所有欄位都要用到。。。。否則你佔用這麼多記憶體，寬頻，CPU時間，IO幹鳥蛋。

 

7     如果能用in，就不要用or

or的時間複雜度是n,in的時間複雜度是log(n)。

錯誤的例子：select Fpacket_name from t_account_packet where Fpacket_id =68698080 or Fpacket_id = 68711068;

正確的例子：select Fpacket_name from t_account_packet where Fpacket_id in(68698080,68711068);"

 

8     如果能用union，就不要用or

和上邊同理

 

9     合理使用limit

拍拍資料一般都是自增的，所以定位的話一般都要倒序來看最近時間的。但limit又是最慢的一個倒序合理結合limit的話，能體現出更高的效率。

 

最近的2個批次，正確的例子：select Fpacket_id from t_account_packet order by Fpacket_id desclimit 2；

錯誤的思路：select count(*) from t_account_packet ; =>879446;

                   selectFpacket_id from t_account_packet limit 879444,879446;

最近的第2個批次，正確的例子：selectFpacket_id from t_account_packet order by Fpacket_id desc limit 1,1；"

錯誤的思路：select count(*) from t_account_packet ; =>879446;

                   selectFpacket_id from t_account_packet limit 879444,879445;

 

10  如果能用load data，就不要用insert

做幸運占卜師活動的時候，預設是要載入很多血型和性格相關資料的。當時用的是source+逐行insert方法導資料，或者考慮load data，它的原理是在執行load之前,會關掉索引,當load全部執行完成後,再重新建立索引。而insert的原理是：每插入一條則更新一次資料庫,更新一次索引。所以要慢很多倍，具體多少倍，依賴待處理的數量級。

 

 

四、索引的一句話最佳化

 

1.   盡量選擇區分度高的索引進行檢索 

錯誤的例子：name。正確的例子：id。

 

2.   不易過多

表資料與索引的容量比保持在1:1      ，至少一條語句中存在一個索引。

 

3.   索引是從左向右原則  

 

4.   利用explain神器來執行和分析索引的覆蓋   

 

5.   不要用索引欄位做計算    

你見過哪個應急通道上，有自駕車站位的？

 

6.   要認識他們MyISAM(重搜尋), Innodb(預設，事務性、重業務)

innodb主鍵推薦使用自增列