MySQL使用規范 一、 核心規范 www.2cto.com 1. 不用數據庫做運營，如md5()、order by rand() 2. 控制單表數據量 a) 單表純int不超過1000w b) 單表含char不超過500w c) 單庫不超過300-400個表 3. 表字段數少而精 a) 影響因素 i. IO高效 ii. 全表遍歷 iii. 表修復快 iv. 提高並發 v. Alter table快 b) 單表字段上限控制在20-50個 4. 拒絕大SQL、大事務、大批量 二、 字段規范 www.2cto.com 1. 用好數值字段類型 a) TINYINT（1 Byte） b) SMALLINT(2B) c) MEDIUMINT(3B) d) INT(4B)、BIGINT（8B） e) FLOAT(4B)、DOUBLE(8B) f) DECIMAL(M,D) 2. 盡量用數值型字段，非字符串型 a) 更高效 b) 查詢更快 c) 占用空間更小 3. 優先使用enum或set a) Enum占1字節，轉為數值運算 b) Set視節點訂，最多占8字節 c) 比較時需要加單引號 4. 避免使用NULL字段 a) 很難進行查詢優化 b) NULL列加索引，需要額外空間 c) 含NULL符合索引無效 5. 少用或拆分TEXT/BLOB a) Text類型處理性能遠低於varchar b) 如必須使用則拆分到單獨的表 6. 不在數據庫裡存圖片 三、 索引規范 1. 謹慎合理添加索引 a) 改善查詢，減慢更新，索引不是越多越好 b) 能不加索引盡量不加，最好不超過字段數20% 2. 字符字段必須建前綴索引 a) Mysq的區分度是成平次增長的 3. 不在索引列做數學運算或函數運算 a) 無法使用索引 b) 導致全表掃描 4. 自增列或全局id做innodb主鍵 5. 盡量不用外鍵 a) 外鍵可節省開發量 b) 有額外開銷 c) 逐行操作 d) 可到達其他表，意味著鎖 e) 高並發時容易死鎖 四、 SQL規范 1. 拒絕大sql，拆解成多條簡單sql a) 簡單sql緩存命中率更高 b) 減少鎖表時間，特別是myisam c) 用上多cpu 2. 保持事務、db連接短小精悍 a) 使用原則：即開即用，用完即關 b) 與事務無關的操作放到事務外面，減少鎖資源的占用 c) 不破壞一致性前提下，使用多個短事務代替長事務 3. 盡可能避免使用存儲過程、觸發器、函數 4. 盡量不用select *，只取需要的數據列 5. 改下or為in a) Or效率：O(n) b) In 效率：O(log n) c) 當N很大時，or會慢很多，建議in的n小於200 6. 如果不同字段，改寫or為union 7. 避免負向查詢和%前綴模糊 a) Not、!=、<>等 b) 前綴模糊使用不了索引，會導致全表掃描 8. 減少count a) Count(1)==count(*)全表掃描 b) 實時數據：用memcache，雙向更新，凌晨跑基准 c) 非實時統計：盡量用單獨統計表，定期重算 9. Limt高效分頁 a) Limit 偏移量越大則越慢 b) 推薦分頁：select * from table where id>id limit 10; 10. 用union all而非union a) 若無需對結果進行去重，則用union all，union有去重開銷 11. 分解鏈接保證高並發 a) 高並發db不建議進行兩個表以上的join 12. Group by 去除排序 a) Group by 實現包括：分組、排序 b) 無需排序：order by null c) 特定排序：group by desc 13. 盡量保證同數據類型的列值進行比較 a) 數值列於字符類型比較 i. 同時轉換為雙精度 ii. 進行比對 b) 字符列與數值類型比較 i. 字符列整列轉數值 ii. 不用使用索引查詢 14. Load data a) 批量裝載比單行快，不需要刷新緩存 b) 無索引比有索引裝載快 c) 盡量不用insert select 15. 打散大批量更新 16. 常見檢測db的命令 a) Show profile b) Mysqlsla c) Mysqldumpslow d) Show slow log e) Show processlist f) Show query_response_time（percona） g) Explan 17. 盡量用連接代替子查詢 18. 統一字符集：utf8，校對規則：utf8_general_ci
