資料庫的規範和SQL優化技巧總結

現總結工作與學習中關於資料庫的規範設計與優化技巧

1.規範背景與目的

MySQL資料庫與 Oracle、 SQL Server 等資料庫相比，有其內核上的優勢與劣勢。我們在使用MySQL資料庫的時候需要遵循一定規範，揚長避短。
本規範旨在幫助或指導RD、QA、OP等技術人員做出適合線上業務的資料庫設計。
在資料庫變更和處理流程、資料庫表設計、SQL編寫等方面予以規範，從而為公司業務系統穩定、健康地運行提供保障。

2.設計規範

2.1資料庫設計

說明：
以下所有規範會按照【高危】、【強制】、【建議】三個級別進行標註，遵守優先順序從高到低。
對於不滿足【高危】和【強制】兩個級別的設計，強制要求修改。

禁止使用的sql語句

【高危】

  • 禁用update | delete t1 … where a = xx limit xxx; 這種帶limit的更新語句。因為會導致主從不一致，導致數據錯亂。
  • 禁止使用關聯子查詢，如update t1 set … where name in(select…) 效率極其低下
  • 禁用procedure、function\trigger\views\ event\外鍵約束。因為他們消耗資料庫資源，降低資料庫實例可擴展性。推薦都在程式端實現。
  • 禁用insert into .. on duplicate key update.. 在高並發環境下，會造成主從不一致。
  • 禁止連表更新語句

2.1.1 庫的創建

  • 【強制】庫的名稱必須控制在32個字元以內，庫名稱格式：業務系統名稱_子系統名稱，同一模組使用的表名盡量使用統一前綴
  • 【強制】創建資料庫時必須顯式指定字符集，並且字符集只能是utf8或者utf8mb4.創建資料庫SQL舉例：create database db1 default character set utf8;

2.1.2 表結構

  1. 【強制】
  • 對於關係表，表名和表名之間盡量體現join的關係，如user和group表直接的關係：x_user_group_xx
  • 表和列的名稱必須控制在32個字元以內，表名只能使用字母、數字和下劃線，一律小寫
  • 表名與模組名強相關，如渠道系統採用「qd_」作為前綴
  • 創建表時必須顯式指定字符集為utf8或者utf8mb4
  • 創建表時必須顯式指定表存儲引擎類型，如無特殊需求，一律為InnoDB。當需要使用除InnoDB/MyISAM/Memory以外的存儲引擎時，必須通過審核才能使用。（InnoDB表支援事務、行鎖、宕機恢復、MVCC等關係型資料庫重要特性，為業界使用最多的MySQL引擎。）
  • 建表必須使用comment 添加註釋
  • 中間表用於保留中間結果集，名稱必須以tmp_開頭。備份表名稱必須以bak_開頭。
  • 對於超過100W行的數據量大的表進行alter table時，必須在業務低峰期執行。因為alter table 會產生表鎖，期間會阻塞對於該表的所有寫入，對於業務可能會產生極大影響
  1. 【建議】
  • 核心表須有創建時間和更新時間
  • 表中所有欄位加上NOT NULL屬性，業務可以根據需要定義DEFAULT值。因為使用NULL值會存在每一行都佔用額外存儲空間、數據遷移容易出錯、聚合函數計算結果偏差等問題
  • 表中有blob、text等大欄位，垂直查分到其他表中。
  • 有些欄位可以適當冗餘，比如user_name，不僅僅存user_id,減少join查詢

2.1.3 列數據類型優化

  1. 【建議】
  • 表中數據值很少的狀態status、類型type等欄位使用tinytint或者smallint類型節省存儲空間
  • 不推薦使用blob，text等類型，比較浪費硬碟和記憶體空間。
  • 文本數據盡量用varchar存儲。因為varchar是變長存儲，比char更省空間。一般verchar類型欄位樹不要超過2700.
  • 時間類型盡量選取timestamp。因為datetime佔用8個位元組，timestamp佔用4個位元組

2.1.4 索引設計

  1. 【強制】
  • InnoDB表必須主鍵為id int/bigint auto_increment,且主鍵禁止被更新。
  • 單個索引中每個索引記錄的長度不能超過64k
  1. 【建議】
  • 主鍵的索引以「pk_」開頭，唯一索引以「uk_」開頭，普通索引以「idx_」開頭，一律使用小寫格式，以表名/欄位的名稱或縮寫作為後綴。
  • 單個表的索引個數不能超過7個
  • 在建立索引時，多考慮建立聯合索引，並把區分度最高的欄位方在前面。
  • 在多表關聯的join中，保證驅動表的連接列上有索引，這樣join執行效率會更高
  • 保證表中互相不存在冗餘的索引。

2.1.5 程式DAO設計

1.【建議】

  • 對於log或者history類型的表，歲時間增長容易越來越大，因此上線錢DBA必須簡歷表數據清理或歸檔方案。
  • 主表機芯增、刪、改，從表進行查操作
  • 對於單表讀寫比大於10:1的數據行或單個列，可以將熱點數據放在快取里（如mechache或redis），加快訪問速度，降低MySQL壓力。

2.1.6 規範的建表語句示例

CREATE TABLE user (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(11) NOT NULL COMMENT 『用戶id』
  `username` varchar(45) NOT NULL COMMENT '真實姓名',
  `email` varchar(30) NOT NULL COMMENT 『用戶郵箱』,
  `nickname` varchar(45) NOT NULL COMMENT '昵稱',
  `avatar` int(11) NOT NULL COMMENT '頭像',
  `birthday` date NOT NULL COMMENT '生日',
  `sex` tinyint(4) DEFAULT '0' COMMENT '性別',
  `short_introduce` varchar(150) DEFAULT NULL COMMENT '一句話介紹自己，最多50個漢字',
  `user_resume` varchar(300) NOT NULL COMMENT '用戶提交的簡歷存放地址',
  `create_time` timestamp NOT NULL COMMENT 『用戶記錄創建的時間』,
  `update_time` timestamp NOT NULL COMMENT 『用戶資料修改的時間』,
  `user_review_status` tinyint NOT NULL COMMENT 『用戶資料審核狀態，1為通過，2為審核中，3為未通過，4為還未提交審核』,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_id` (`user_id`),
  KEY `idx_username`(`username`),
  KEY `idx_create_time`(`create_time`,`user_review_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='網站用戶基本資訊';

2.2 SQL編寫規範

2.2.1 DML語句

  1. 【強制】
  • select 語句必須指定具體欄位名稱，禁止寫成*。因為select *會將不該讀取的數據從mysql里讀取處理，造成網卡的壓力。且表欄位一旦更新，單model沒有來得及更新的話，系統會報錯。—————sql優化
  • where條件里等號左右欄位類型必須一致，否則無法利用索引。—————sql優化
  1. 【建議】
  • insert into … value(xxx), (xxx) …這裡的（xxx）不要超過5000個。值過多雖然上線很快，但會引起主從同步延遲。
  • select語句中不要使用union，推薦使用union all，並且union子句個數限制在5個以內。因為union all不需要去重，節省資料庫資源，提高性能。—————sql優化
  • in值列表限制在500以內，這麼做事圍欄減少底層掃描，減輕資料庫壓力從而加速查詢
  • 事務里批量更新數據需要控制數量，進行必要的sleep，做到少量多次。—————優化
  • where子句中盡量少使用圈模糊的like條件查詢，必須有其他等值或範圍查詢條件，否則無法利用索引。—————sql優化
  • 索引列不要使用函數或表達式，否則無法利用索引****—————sql優化
  • 分頁查詢，當limit起點較高時，可先用過濾條件進行過濾。如select a,b,c from t1 limit 10000,20;優化為: select a,b,c from t1 where id>10000 limit 20;

2.2.1 多表連接

  1. 【強制】
  • 禁止跨db的join語句，因為這樣可以減少模組間的耦合，為資料庫拆分奠定堅實的基礎
  • 禁止在業務的更新類SQL語句中使用join，比如update t1 join t2..
  1. 【建議】
  • 不建議使用子查詢，建議將子查詢SQL拆開結合程式多次查詢，或使用join來代替子查詢—————sql優化
  • 線上環境，多表join不要超過3個表
  • 多表連接查詢推薦使用別名，且SELECT列表中要用別名引用欄位
  • 在多表join中，盡量選取結果集較小的表作為驅動表，來join其他表

2.2.3 事務

  1. 【強制】
  • 程式設計必須考慮「資料庫事務隔離級別」帶來的影響，包括臟讀、不可重複度和幻讀。線上建議事務隔離級別為repeatable-read
  1. 【建議】
  • 事務中INSERT | UPDATE | DELETE | REPLACE 語句操作的行數控制在2000以內，以及WHERE子句中IN列表的傳參個數控制在500以內。
  • 批量操作是，需要控制事務處理隔離時間，進行必要的sleep，一般建議值5-10秒
  • 對於有auto_increment屬性欄位的表的插入操作，並發需要控制在200以內
  • 事務里包含SQL不超過5個（支付業務除外）。因為過長的事務會導致鎖數據較久，MySQL內部快取、連接消耗過多等雪崩問題。
  • 對於MySQL主從延遲嚴格敏感的select語句，請開啟事務強制訪問主庫

2.2.4 排序和分組

  1. 【建議】
  • 減少使用order by，和業務溝通能不排序就不排序，或將排序放到程式端去做。order by、group by、distinct 這些語句較為耗費CPU，資料庫的CPU資源是極其寶貴的。—————sql優化
  • order by、group by、distinct這些SQL盡量利用索引直接檢索出排序好的數據。如where a=1 order by可以利用key(a,b)。
  • 包含了order by、group by、distinct這些查詢的語句，where條件過濾出來的結果集請保持在1000行以內，否則SQL會很慢。

不斷積累中，未完待續…

若覺得博文不錯或對你有幫助，請點擊【推薦】或者【關注】，感謝你的支援

Tags:

VirMach 便宜 VPS

QNews

QNews