Mysql資料庫開發的36條原則 【小宅按】 前言這些原則都是經歷過實戰總結而成每一條原則背後都是血淋淋的教訓這些原則主要是針對資料庫開發人員,在開發過程中務必注意總是在災難發生後,纔想起容災的重要性;總是在喫過虧以後,才記得曾有人提醒過。 前言 這些原則都是經歷過實戰總結而成每一條原則背後都是血淋淋的教訓這些原則主要是針對資料庫開發人員,在開發過程中務必注意總是在災難發生後,纔想起容災的重要性; 總是在喫過虧以後,才記得曾有人提醒過。 一、核心原則 1.盡量不在資料庫做運算 俗話說:別讓腳趾頭想事情,那是腦瓜子的職責作為資料庫開發人員,我們應該讓資料庫多做她所擅長的事情: 盡量不在資料庫做運算 複雜運算移到程序端CPU 儘可能簡單應用MYSQL 舉例:在mysql中盡量不要使用如:md5()、Order by Rand()等這類運算函數 2.盡量控制單表數據量 大家都知道單表數據量過大後會影響數據查詢效率,嚴重情況下會導致整個庫都卡住一般情況下,按照一年內單表數據量預估: 純INT不超過1000W 含CHAR不超過500W 同時要盡量做好合理的分表,使單表數據量不超載,常見的分表策略有: 通過USERID來分表(根據ID區間分表):在金融行業應用較多,用戶量大、用戶特徵明顯 按DATE分表(按天、周、月分表):在電信行業應用非常多,如用戶上網記錄表、用戶簡訊表、話單表等 按AREA分表(省、市、區分表) 其他 分區表的適用場景主要有:① 表非常大,無法全部存在內存,或者只在表的最後有熱點數據,其他都是歷史數據; ② 分區表的數據更易維護,可以對獨立的分區進行獨立的操作;③ 分區表的數據可以分佈在不同的機器上,從而高效使用資源;④ 可以使用分區表來避免某些特殊的瓶頸;⑤ 可以備份和恢復獨立的分區。但是使用分區表同樣有一些限制,在使用的時候需要注意:① 一個表最多隻能有 1024 個分區;② 5.1版本中,分區表表達式必須是整數, 5.5可以使用列分區;③ 分區欄位中如果有主鍵和唯一索引列,那麼主鍵列和唯一列都必須包含進來;④ 分區表中無法使用外鍵約束;⑤ 需要對現有表的結構進行修改; ⑥ 所有分區都必須使用相同的存儲引擎;⑦ 分區函數中可以使用的函數和表達式會有一些限制;⑧ 某些存儲引擎不支持分區;⑨ 對於 MyISAM 的分區表,不能使用 load index into cache;⑩ 對於 MyISAM 表,使用分區表時需要打開更多的文件描述符。 3.盡量控制表欄位數量 單表的欄位數量也不能太多,根據業務場景進行優化調整,盡量調整表欄位數少而精,這樣有以下好處: IO高效 全表遍歷 表修復快 提高並發 alter table更快 那究竟單表多少欄位合適呢?按照單表1G體積,500W行數據量進行評估: 順序讀1G文件需N秒 單行不超過200Byte 單表不超50個純INT欄位 單表不超20個CHAR(10)欄位 ==>建議單表欄位數上限控制在20~50個 4.平衡範式與冗餘 資料庫表結構的設計也講究平衡,以往我們經常說要嚴格遵循三大範式,所以先來說說什麼是範式:第一範式:單個欄位不可再分。唯一性。 第二範式:不存在非主屬性只依賴部分主鍵。消除不完全依賴。 第三範式:消除傳遞依賴。用一句話來總結範式和冗餘:冗餘是以存儲換取性能,範式是以性能換取存儲。所以,一般在實際工作中冗餘更受歡迎一些。模型設計時,這兩方面的具體的權衡,首先要以企業提供的計算能力和存儲資源為基礎。其次,一般互聯網行業中都根據Kimball模式實施數據倉庫,建模也是以任務驅動的,因此冗餘和範式的權衡符合任務需要。 例如,一份指標數據,必須在早上8點之前處理完成,但計算的時間窗口又很小,要儘可能減少指標的計算耗時,這時在計算過程中要儘可能減少多表關聯,模型設計時需要做更多的冗餘。 5.拒絕3B 資料庫的並發就像城市交通,呈非線性增長 這就要求我們在做資料庫開發的時候一定要注意高並發下的瓶頸,防止因高並發造成資料庫癱瘓。這裡的拒絕3B是指: 大SQL(BIG SQL):要減少 大事務(BIG Transaction) 大批量(BIG Batch) 二、欄位類原則 1.用好數值欄位類型 三類數值類型: 整型:TINYINT(1Byte)、TINYINT(1Byte)、SMALLINT(2B)、MEDIUMINT(3B)、INT(4B)、BIGINT(8B) 浮點型:FLOAT(4B)、DOUBLE(8B) DECIMAL(M,D) 以幾個常見的例子來進行說明:1)INT(1) VS INT(11)很多人都分不清INT(1)和INT(11)的區別,想必大家也很好奇吧,其實1和11其實只是顯示長度的卻別而已,也就是不管int(x)x的值是什麼值,存儲數字的取值範圍還是int本身數據類型的取值範圍,x只是數據顯示的長度而已。2)BIGINT AUTO_INCREMENT 大家都知道,有符號int最大可以支持到約22億,遠遠大於我們的需求和MySQL單表所能支持的性能上限。對於OLTP應用來說,單表的規模一般要保持在千萬級別,不會達到22億上限。如果要加大預留量,可以把主鍵改為改為無符號int,上限為42億,這個預留量已經是非常的充足了。使用bigint,會佔用更大的磁碟和內存空間,內存空間畢竟有限,無效的佔用會導致更多的數據換入換出,額外增加了IO的壓力,對性能是不利的。因此推薦自增主鍵使用int unsigned類型,但不建議使用bigint。3)DECIMAL(N,0)當採用DECIMAL數據類型的時候,一般小數位數不會是0,如果小數位數設置為0,那建議使用INT類型 2.將字元轉化為數字 數字型VS字元串型索引有更多優勢: 更高效 查詢更快 佔用空間更小 舉例:用無符號INT存儲IP,而非CHAR(15)INT UNSIGNED可以用INET_ATON()和INET_NTOA()來實現IP字元串和數值之間的轉換 3.優先使用ENUM或SET 對於一些枚舉型數據,我們推薦優先使用ENUM或SET,這樣的場景適合:1)字元串型2)可能值已知且有限存儲方面:1)ENUM佔用1位元組,轉為數值運算2)SET視節點定,最多佔用8位元組3)比較時需要加『單引號(即使是數值)舉例:`sex` enum(F,M) COMMENT 性別; `c1` enum(0,1,2,3) COMMENT 審核; 4.避免使用NULL欄位 為什麼在資料庫表欄位設計的時候盡量都加上NOT NULL DEFAULT ,這裡面不得不說用NULL欄位的弊端: 很難進行查詢優化 NULL列加索引,需要額外空間 含NULL複合索引無效 舉例:1)`a` char(32) DEFAULT NULL 【不推薦】2)`b` int(10) NOT NULL 【不推薦】3)`c` int(10) NOT NULL DEFAULT 0 【推薦】 5.少用並拆分TEXT/BLOB TEXT類型處理性能遠低於VARCHAR 強制生成硬碟臨時表 浪費更多空間 VARCHAR(65535)==>64K(注意UTF-8) 盡量不用TEXT/BLOB數據類型如果業務需要必須用,建議拆分到單獨的表舉例: CREATE TABLE t1 ( id INT NOT NULL AUTO_INCREMENT, data TEXT NOT NULL, PRIMARY KEY(id) ) ENGINE=InnoDB; 6.不在資料庫裏存圖片 先上圖: 可見,如果將圖片全部存在資料庫,將使得資料庫體積變大,會造成讀寫速度變慢。圖片存資料庫的弊端: 對資料庫的讀/寫的速度永遠都趕不上文件系統處理的速度 資料庫備份變的巨大,越來越耗時間 對文件的訪問需要穿越你的應用層和資料庫層 推薦處理辦法:資料庫中保存圖片路徑按照年月日生成路徑。具體是按照年月日還是按照年月去生成路徑,根據自己需要(不一定是按照日期去生成)。理解為什麼要分散到多個文件夾中去纔是關鍵,涉及到一個原理就明白了:操作系統對單個目錄的文件數量是有限制的。當文件數量很多的時候。從目錄中獲取文件的速度就會越來越慢。所以為了保持速度,纔要按照固定規則去分散到多個目錄中去。圖片分散到磁碟路徑中去。資料庫欄位中保存的是類似於這樣子的」images/2012/09/25/ 1343287394783.jpg」原來上傳的圖片文件名稱會重新命名保存,比如按照時間戳來生成,1343287394783. jpg。這樣子是為了避免文件名重複,多個人往同一個目錄上傳圖片的時候會出現。反正用什麼樣的規則命名圖片,只要做到圖片名稱的唯一性即可。比如網站的並發訪問量大,目錄的生成分得月細越好。比如精確到小時,一個小時都可以是一個文件夾。同時0.001秒有兩個用戶同時在上傳圖片(因為那麼就會往同一個小時文件夾裡面存圖片)。因為時間戳是精確到秒的。為了做到圖片名稱唯一性而不至於覆蓋,生成可以在在時間戳後面繼續加毫秒微秒等。總結的規律是,並發訪問量越大。就越精確就好了。題外話:1)為什麼保存的磁碟路徑,是」images/2012/09/25/1343287394783.jpg」,而不是」 /images/2012/09/25/ 1343287394783.jpg」(最前面帶有斜槓)在頁面中需要取出圖片路徑展示圖片的時候,如果是相對路徑,則可以使用」./」+」images/2012/09/25/1343287394783.jpg」進行組裝。如果需要單獨的域名(比如做cdn加速的時候)域名,img1.xxx.com,http://img2.xxx.com這樣的域名,直接組裝 「http://img1.xxx.com/」+」images/2012/09/25/1343287394783.jpg」2)為什麼保存的磁碟路徑,是」images/2012/09/25/1343287394783.jpg」,而不是「http://www.xxx.com/images/2012/09/25/1343287394783.jpg"這裡其實涉及到CDN的知識,具體CDN的知識在此不多展開,簡而言之:cdn服務:對於靜態內容是非常適合的。所以像商品圖片,隨著訪問量大了後,租用cdn服務,只需要把圖片上傳到他們的伺服器上去。例子:北京訪問長沙伺服器,距離太遠。我完全可以把商品圖片,放到北京的雲服務(我覺得現在提供給網站使用的雲存儲其實就是cdn,給網站提供分流和就近訪問)上去。這樣子北京用戶訪問的時候,實際上圖片就是就近獲取。不需要很長距離的傳輸。自己用一個域名http://img.xxx.com來載入圖片。這個域名解析到北京的雲服務上去。做法:資料庫中保存的是」 images/2012/09/25/1343287394783.jpg」,這些圖片實際上不存儲在web伺服器上。上傳到北京的cdn伺服器上去。我從資料庫取出來,直接」http://img.xxx.com/」+」 images/2012/09/25/1343287394783.jpg」比如如果還有多個,就命名http://img1.xx.com、http://img2.xx.com反正可以隨便。所以如果把域名直接保存進去。就顯得很麻煩了。遷移麻煩。 三、索引類原則 1.謹慎合理添加索引 添加索引是為了改善查詢 添加索引會減慢更新 索引不是越多越好 能不加的索引盡量不加(綜合評估數據密度和數據分佈,最好不超過欄位數20%) 結合核心SQL有限考慮覆蓋索引 舉例:不要給「性別」列創建索引理論文章會告訴你值重複率高的欄位不適合建索引。不要說性別欄位只有兩個值,網友親測,一個欄位使用拼音首字母做值,共有26種可能,加上索引後,百萬加的數據量,使用索引的速度比不使用索引要慢!為什麼性別不適合建索引呢?因為你訪問索引需要付出額外的IO開銷,你從索引中拿到的只是地址,要想真正訪問到數據還是要對錶進行一次IO。假如你要從表的100萬行數據中取幾個數據,那麼利用索引迅速定位,訪問索引的這IO開銷就非常值了。但如果你是從100萬行數據中取50萬行數據,就比如性別欄位,那你相對需要訪問50萬次索引,再訪問50萬次表,加起來的開銷並不會比直接對錶進行一次完整掃描小。 2.字元欄位必須建前綴索引 區分度: 單字母區分度:26 4字母區分度:26*26*26*26 = 456,976 5字母區分度:26*26*26*26*26 = 11,881,376 6字母區分度:26*26*26*26*26*26 = 308,915,776 字元欄位必須建前綴索引,例如:`pinyin` varchar(100) DEFAULT NULL COMMENT 小區拼音, KEY `idx_pinyin` (`pinyin`(8)), ) ENGINE=InnoDB 3.不在索引列做運算 原因有兩點:1)會導致無法使用索引2)會導致全表掃描舉例: BAD SAMPLE:select * from table WHERE to_days(current_date) – to_days(date_col) <= 10 GOOD SAMPLE: select * from table WHERE date_col >= DATE_SUB(2011-10-22,INTERVAL 10 DAY); 4.自增列或全局ID做INNODB主鍵 對主鍵建立聚簇索引 二級索引存儲主鍵值 主鍵不應更新修改 按自增順序插入值 忌用字元串做主鍵 聚簇索引分裂 推薦用獨立於業務的AUTO_INCREMENT列或全局ID生成器做代理主鍵 若不指定主鍵,InnoDB會用唯一且非空值索引代替 5.盡量不用外鍵 線上OLTP系統盡量不用外鍵: 外鍵可節省開發量 有額外開銷 逐行操作 可「到達」其他表,意味著鎖 高並發時容易死鎖 建議由程序保證約束比如我們原來建表語句是這樣的:CREATE TABLE `user` ( `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 主鍵, `user_name` varchar(50) NOT NULL DEFAULT COMMENT 用戶名, PRIMARY KEY (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `order` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 主鍵, `total_price` decimal(10,2) NOT NULL DEFAULT 0.00, `user_id` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), KEY `for_indx_user_id` (`user_id`), CONSTRAINT `for_indx_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 不使用外鍵約束後:CREATE TABLE `user` ( `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 主鍵, `user_name` varchar(50) NOT NULL DEFAULT COMMENT 用戶名, PRIMARY KEY (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `order` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 主鍵, `total_price` decimal(10,2) NOT NULL DEFAULT 0.00, `user_id` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 不適用外鍵約束後,為了加快查詢我們通常會給不建立外鍵約束的欄位添加一個索引。CREATE TABLE `order` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 主鍵, `total_price` decimal(10,2) NOT NULL DEFAULT 0.00, `user_id` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), KEY `idx_user_id` (`user_id`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 實際開發中,一般不會建立外鍵約束。 四、SQL類原則 1.SQL語句儘可能簡單在開發過程中,我們盡量要保持SQL語句的簡單性,我們對比一下大SQL和多個簡單SQL 傳統設計思想 BUG MySQL NOT 一條SQL只能在一個CPU運算 5000+ QPS的高並發中,1秒大SQL意味著? 可能一條大SQL就把整個資料庫堵死 拒絕大SQL,拆解成多條簡單SQL 簡單SQL緩存命中率更高 減少鎖表時間,特別是MyISAM 用上多CPU 2.保持事務(連接)短小 事務/連接使用原則:即開即用,用完即關 與事務無關操作都放到事務外面,減少鎖資源的佔用 不破壞一致性前提下,使用多個短事務代替長事務 舉例:1)發帖時的圖片上傳等待2)大量的sleep連接 3.儘可能避免使用SP/TRIG/FUNC 線上OLTP系統中,我們應當: 儘可能少用存儲過程 儘可能少用觸發器 減少使用MySQL函數對結果進行處理 將上述這些事情都交給客戶端程序負責 4.盡量不用SELECT * 用SELECT * 時,將會更多的消耗CPU、內存、IO以及網路帶寬我們在寫查詢語句時,應當盡量不用SELECT * ,只取需要的數據列: 更安全的設計:減少表變化帶來的影響 為使用covering index提供可能性 Select/JOIN 減少硬碟臨時表生成,特別是有TEXT/BLOB時 舉例:不推薦:SELECT * FROM tag WHERE id = 999148 推薦:SELECT keyword FROM tag WHERE id = 999148 5.改寫OR為IN() 同一欄位,將or改寫為in()OR效率:O(n)IN效率:O(Log n)當n很大時,OR會慢很多注意控制IN的個數,建議n小於200舉例:不推薦:Select * from opp WHERE phone=12347856 or phone=42242233 推薦:Select * from opp WHERE phone in (12347856 , 42242233) 6.改寫OR為UNION 不同欄位,將or改為union 減少對不同欄位進行 "or" 查詢 Merge index往往很弱智 如果有足夠信心:set global optimizer_switch=index_merge=off; 舉例:不推薦:Select * from opp WHERE phone=010-88886666 or cellPhone=13800138000; 推薦:Select * from opp WHERE phone=010-88886666 union Select * from opp WHERE cellPhone=13800138000; 7.避免負向查詢和%前綴模糊查詢 在實際開發中,我們要盡量避免負向查詢,那什麼是負向查詢呢,主要有以下:NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等同時,我們還要避免%前綴模糊查詢,因為這樣會使用B+ Tree,同時會造成使用不了索引,並且會導致全表掃描,性能和效率可想而知舉例: 8.減少COUNT(*) 在開發中我們經常會使用COUNT(*),殊不知這種用法會造成大量的資源浪費,因為COUNT(*)資源開銷大,所以我們能不用盡量少用對於計數類統計,我們推薦: 實時統計:用memcache,雙向更新,凌晨跑基準 非實時統計:盡量用單獨統計表,定期重算 來對比一下COUNT(*)和其他幾個COUNT吧:`id` int(10) NOT NULL AUTO_INCREMENT COMMENT 公司的id, `sale_id` int(10) unsigned DEFAULT NULL, 結論:COUNT(*)=COUNT(1)COUNT(0)=COUNT(1)COUNT(1)=COUNT(100)COUNT(*)!=COUNT(col) 9.LIMIT高效分頁 傳統分頁:Select * from table limit 10000,10; LIMIT原理: Limit 10000,10 偏移量越大則越慢 推薦分頁:Select * from table WHERE id>=23423 limit 11; #10+1 (每頁10條) select * from table WHERE id>=23434 limit 11; 分頁方式二:Select * from table WHERE id >= ( select id from table limit 10000,1 ) limit 10; 分頁方式三:SELECT * FROM table INNER JOIN (SELECT id FROM table LIMIT 10000,10) USING (id) ; 分頁方式四:#先使用程序獲取ID: select id from table limit 10000,10; #再用in獲取ID對應的記錄 Select * from table WHERE id in (123,456…) ; 具體需要根據實際的場景分析並重組索引示例: 10.用UNION ALL 而非UNION 如果無需對結果進行去重,僅僅是對多表進行聯合查詢並展示,則用UNION ALL,因為UNION有去重開銷舉例:MySQL>SELECT * FROM detail20091128 UNION ALL SELECT * FROM detail20110427 UNION ALL SELECT * FROM detail20110426 UNION ALL SELECT * FROM detail20110425 UNION ALL SELECT * FROM detail20110424 UNION ALL SELECT * FROM detail20110423; 11.分解聯接保證高並發 高並發DB不建議進行兩個表以上的JOIN適當分解聯接保證高並發: 可緩存大量早期數據 使用了多個MyISAM表 對大表的小ID IN() 聯接引用同一個表多次 舉例:原SQL:MySQL> Select * from tag JOIN tag_post on tag_post.tag_id=tag.id JOIN post on tag_post.post_id=post.id WHERE tag.tag=『二手玩具』; 分解SQL:MySQL> Select * from tag WHERE tag=『二手玩具』; MySQL> Select * from tag_post WHERE tag_id=1321; MySQL> Select * from post WHERE post.id in (123,456,314,141) 12.GROUP BY 去除排序 使用GROUP BY可以實現分組和自動排序無需排序:Order by NULL特定排序:Group by DESC/ASC舉例: 13.同數據類型的列值比較 原則:數字對數字,字元對字元數值列與字元類型比較:同時轉換為雙精度進行比對字元列與數值類型比較:字元列整列轉數值,不會使用索引查詢舉例:欄位:`remark` varchar(50) NOT NULL COMMENT 備註,默認為空,MySQL>SELECT `id`, `gift_code` FROM gift WHERE `deal_id` = 640 AND remark=115127; 1 row in set (0.14 sec) MySQL>SELECT `id`, `gift_code` FROM pool_gift WHERE `deal_id` = 640 AND remark=115127; 1 row in set (0.005 sec) 14.Load data 導數據 批量數據快導入: 成批裝載比單行裝載更快,不需要每次刷新緩存 無索引時裝載比索引裝載更快 Insert values ,values,values 減少索引刷新 Load data比insert快約20倍 盡量不用INSERT ... SELECT,一個是有延遲,另外就是會同步出錯 15.打散大批量更新 大批量更新盡量凌晨操作,避開高峯 凌晨不限制 白天上線默認為100條/秒(特殊再議) 舉例:update post set tag=1 WHERE id in (1,2,3); sleep 0.01; update post set tag=1 WHERE id in (4,5,6); sleep 0.01; …… 16.Know Every SQL 作為DBA乃至資料庫開發人員,我們必須對資料庫的每條SQL都非常瞭解,常見的命令有: SHOW PROFILE MYSQLsla MySQLdumpslow explain Show Slow Log Show Processlist SHOW QUERY_RESPONSE_TIME(Percona) 五、約定類原則 1.隔離線上線下 構建資料庫的生態環境,確保開發無線上庫操作許可權原則:線上連線上,線下連線下 生產數據用pro庫 預生產環境用pre庫 測試用test庫 開發用dev庫 2.禁止未經DBA確認的子查詢 大部分情況優化較差 特別WHERE中使用IN id的子查詢 一般可用JOIN改寫 舉例:MySQL> select * from table1 where id in (select id from table2); MySQL> insert into table1 (select * from table2); //可能導致複製異常 3.永遠不在程序端顯式加鎖 外部鎖對資料庫丌可控 高幵發時是災難 極難調試和排查 對於類似並發扣款等一致性問題,我們採用事務來處理,Commit前進行二次校驗衝突 4.統一字符集為UTF8 5.統一命名規範 1)庫表等名稱統一用小寫2)索引命名默認為「idx_欄位名"3)庫名用縮寫,盡量在2~7個字母 DataSharing ==> ds4)注意避免用保留字命名 以上所有坑,建議資料庫開發人員都要銘記於心。更多精彩內容,請滑至頂部點擊右上角關注小宅哦~ 來源:華為雲社區原創 作者:真愛無敵 推薦閱讀: 相關文章 {{#data}} {{title}} {{/data}}