趕集網Mysql36條軍規 · PHP筆記+解決方案

# 1.核心軍規 ### 1.1盡量不在數據庫做運算 * 盡量不在數據庫做運算 * 復雜運算到程序端CPU * 舉例: md5() / Order by Rand()不要出現 ### 1.2控制單表數據量 * 純INT不超1000W * 含CHAR不超500W * 建議單庫不超過300-400個表 ### 1.3保持表身段苗條 * 表字段數少而精 * 單表字段數上限控制在20~50個 ### 1.4平衡范式與冗余 * 沒有絕對的對與錯 * 適當時犧牲范式、加入冗余 * 但會增加代碼復雜度 ### 1.5拒絕3B * 大SQL (BIG SQL) * 大事務 (BIG Transaction) * 大批量 (BIG Batch) # 2.字段類軍規 ### 2.1用好數值字段類型 * TINYINT(1Byte) * SMALLINT(2B) * MEDIUMINT(3B) * INT(4B)、BIGINT(8B) * DECIMAL(M,D) ### 2.2將字符轉化為數字 * 數字型VS字符串型索引，更高效，查詢更快 * 舉例：用無符號INT存儲IP，而非CHAR(15) ### 2.3優先使用ENUM或SET * ENUM占用1字節，轉為數值運算 * SET視節點定，最多占用8字節 * 比較時需要加‘ 單引號(即使是數值 ### 2.4避免使用NULL字段 * 很難進行查詢優化 ### 2.5少用并拆分TEXT/BLOB * TEXT類型處理性能遠低亍VARCHAR * 若必須使用則拆分到單獨的表 ### 2.6不在數據庫里存圖片 # 3.索引類軍規 ### 3.1謹慎合理添加索引 * 索引不是越多越好 ### 3.2字符字段必須建前綴索引 ### 3.3不在索引列做運算 * 無法使用索引 * 導致全表掃描 ### 3.4自增列或全局ID做INNODB主鍵 ### 3.5盡量不用外鍵 * 高并發時容易死鎖 * 由程序保證約束 # 4.SQL類軍規 ### 4.1SQL語句盡可能簡單 * 拒絕大SQL，拆解成多條簡單SQL * 減少鎖表時間，特別是MyISAM ### 4.2保持事務(連接)短小 * 事務/連接使用原則：即開即用，用完即關 * 不破壞一致性前提下，使用多個短事務代替長事務 ### 4.3盡可能避免使用SP/TRIG/FUNC * 盡可能少用觸發器 * 盡可能少用存儲過程 * 減用使用MySQL凼數對結果進行處理 * 由客戶端程序負責 ### 4.4盡量不用 SELECT * * 更安全的設計：減少表變化帶來的影響 * 特別是有TEXT/BLOB時 ### 4.5改寫OR為IN() * 注意控制IN的個數，建議n小亍200 ### 4.6改寫OR為UNION * 減少對不同字段進行 "or" 查詢 ### 4.7避免負向查詢和% 前綴模糊查詢 * 避免負向查詢【NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、 NOT LIKE】等 * 避免 % 前綴模糊查詢，使用不了索引，導致全表掃描 ### 4.8減少COUNT(*) * COUNT(*)的資源開銷大，盡量少用 * 盡量用單獨統計表，定期重算 ### 4.9LIMIT高效分頁 * 推薦分頁一： Select * from table WHERE id>=23423 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; Select * from table WHERE id in (123,456…) ; ### 4.10用UNION ALL 而非 UNION * UNION有去重開銷 * 若無需對結果進行去重，則用UNION ALL ### 4.11分解聯接保證高并發 * 高并發DB不建議進行兩個表以上的JOIN ### 4.12GROUP BY 去除排序 * GROUP BY 實現分組自動排序 * 無需排序：Order by NULL * 特定排序：Group by DESC/ASC ### 4.13同數據類型的列值比較 * 原則：數字對數字，字符對字符 ### 4.14Load data 導數據 * 批量數據快導入：Load data比insert快約20倍 * 盡量丌用 INSERT ... SELECT ### 4.15打散大批量更新 * 大批量更新凌晨操作，避開高峰 * 白天上限默認為100條/秒（特殊再議） # 5.約定類軍規 ### 5.1隔離線上線下 * 開發無線上庫操作權限 * 原則：線上連線上，線下連線下 ### 5.2禁止未經DBA確認的子查詢 ### 5.3永遠不在程序端顯式加鎖永遠不在程序端對數據庫顯式加鎖高幵發時是災難，極難調試和排查并發扣款等一致性問題，采用事務，相對值修改，Commit前二次較驗沖突 ### 5.4統一字符集為UTF8 * 校對規則：utf8_general_ci ### 5.5統一命名規范 * 庫表等名稱統一用小寫 * 索引命名默認為“idx_字段名” * 注意避免用保留字命名