MySQL數據庫開發的36 條軍規！[SQL 效率/速度 提升/加快]
MySQL數據庫開發的36 條軍規！[SQL 效率/速度 提升/加快]
資料來源:https://mp.weixin.qq.com/s?__biz=MzAwMDg5MTAyMw==&mid=2247485640&idx=2&sn=a8ae6985879a689d8471d24732fe76b3&chksm=9ae34928ad94c03e2cd928b0690a1909a11e43ff0c2d057695fd8c2b7ede674c25aedc353fbb&scene=126&sessionid=1581985485&key=b3900b1fc989b015e8fd0503299c050999c337e87e2e4e5f22e8bc6d4b0d733e0e7841ef43b90f85691daf7632b60ca95a36d29db48389e59dab364658b52a3b937e009b2fbe358bb994639ed72d5023&ascene=1&uin=MjIwODk2NDgxNw%3D%3D&devicetype=Windows+10&version=62080079&lang=zh_TW&exportkey=AnyDDQZmAOfOuy7mhQwEzfg%3D&pass_ticket=roT%2BBX2IFGRvda5t4c9GQghCiTOl%2FL5GHZItVfWmLpams9po44SDab3je2xSzeQB
核心軍規
▲盡量不在數據庫做運算
▲控制單表數據量純INT不超過10M條，含Char不超過5M條
▲保持表身段苗條
▲平衡範式和冗餘
▲拒絕大SQL，複雜事務，大批量任務
字段類軍規
▲用好數值字段，盡量簡化字段位數
▲把字符轉化為數字
▲優先使用Enum或Set
▲避免使用Null字段
▲少用並拆封Text/Blob
▲不在數據庫中存圖片
索引類軍規
▲謹慎合理添加索引
▲字符字段必須建立前綴索引?
▲不在索引列做運算
▲自增列或全局ID做InnoDB主鍵
▲盡量不用外鍵
SQL類軍規
▲SQL盡可能簡單
▲保持事務連接短小
▲盡可能避免使用SP/Trigger/Function
▲盡量不用Select *
▲改寫Or為IN()
▲改寫Or為Union
▲避免負向查詢和%前綴模糊查詢
▲Count不要使用在可Null的字段上面
▲減少Count(*)
▲Limit高效分頁，SELECT * FROM message WHERE id > 9527 (or sub select) limit 10
▲使用Union ALL 而不用Union
▲分解鏈接，保證高並發
▲Group By 去除排序
▲同數據類型的列值比較
▲Load Data導入數據，比Insert快20倍
▲打散大批量更新，盡量凌晨操作
約定類軍規
▲隔離線上線下
▲禁止未經DBA認證的子查詢
▲永遠不在程序段顯式加鎖
▲表字符集統一使用UTF8MB4
3 thoughts on “MySQL數據庫開發的36 條軍規！[SQL 效率/速度 提升/加快]”
除了不要SELECT * ，數據庫還有哪些技巧 [SQL 效率/速度 提升/加快]
https://mp.weixin.qq.com/s?__biz=MzAwMjk5Mjk3Mw==&mid=2247490533&idx=3&sn=27b03356fce22f898a3d92ad01e43a90&chksm=9ac0a5c7adb72cd1bad026591d46ca48a234b7bab8b523d4c5e94dd99c07d7991442a13a46f4&scene=126&sessionid=1593572325&key=aa494bdf4b4d432bfd7e310e25ad4a1ddd7e6f5e717a3d7af84c06aeec6bc33c8e8d529201532a168db1a512edabf69010c098605bf762df143ef0742130d53ace64afc14f53dc9876e9311d5cc71bee&ascene=1&uin=MjIwODk2NDgxNw%3D%3D&devicetype=Windows+10+x64&version=62090523&lang=zh_TW&exportkey=ApOtAhbRjcOYBIQG0qBvP8s%3D&pass_ticket=OY70c9Ez82dFgHJuUmbZvyLuQWqpM6rITLwiPgNP9K3WGj%2BC8bRGstlT%2FjWgF2pl
技巧1 比較運算符能用“=”就不用“” ~ “=”增加了索引的使用機率。
技巧2 明知只有一條查詢結果，那請使用“LIMIT 1” ~ “LIMIT 1”可以避免全表掃描，找到對應結果就不會再繼續掃描了。
技巧3 為列選擇合適的數據類型 ~ 能用TINYINT就不用SMALLINT，能用SMALLINT就不用INT，道理你懂的，磁盤和內存消耗越小越好嘛。
技巧4 將大的DELETE，UPDATE or INSERT 查詢變成多個小查詢 ~ 能寫一個幾十行、幾百行的SQL語句是不是顯得逼格很高？然而，為了達到更好的性能以及更好的數據控制，你可以將他們變成多個小查詢。
技巧5 使用UNION ALL 代替UNION，如果結果集允許重複的話 ~ 因為UNION ALL 不去重，效率高於UNION。
技巧6 為獲得相同結果集的多次執行，請保持SQL語句前後一致 ~ 這樣做的目的是為了充分利用查詢緩衝。
技巧7 盡量避免使用“SELECT *” ~ 如果不查詢表中所有的列，盡量避免使用SELECT *，因為它會進行全表掃描，不能有效利用索引，增大了數據庫服務器的負擔，以及它與應用程序客戶端之間的網絡IO開銷。
技巧8 WHERE 子句裡面的列盡量被索引 ~ 只是“盡量”哦，並不是說所有的列。因地制宜，根據實際情況進行調整，因為有時索引太多也會降低性能。
技巧9 JOIN 子句裡面的列盡量被索引 ~ 同樣只是“盡量”哦，並不是說所有的列。
技巧10 ORDER BY 的列盡量被索引 ~ ORDER BY的列如果被索引，性能也會更好。
技巧11 使用LIMIT 實現分頁邏輯 ~ 不僅提高了性能，同時減少了不必要的數據庫和應用間的網絡傳輸。
技巧12 使用EXPLAIN 關鍵字去查看執行計劃 ~ EXPLAIN 可以檢查索引使用情況以及掃描的行。
SQL查找是否”存在”，別再count了！[SQL 效率/速度 提升/加快]
https://mp.weixin.qq.com/s?__biz=MzA5MTkxMDQ4MQ==&mid=2648937576&idx=2&sn=816dba7f0cb5235b6ed0df2f4e25d8f3&chksm=88620c56bf1585407fbc22d6784bc3f0ff7f91091f9fa5dbba3c56acc092e6fbd8fbfb23a0a6&scene=126&sessionid=1601889978&key=bf49e72a192178bf577d5a6fb6ee90693aaaef11dbc1ba4bf1cd869fdc583d403395fd8b50d8e8133e1915338d6576d2d504de6622c073c4c0d9191ae25cf830d689c84486e0e16d35658ee0930dff9ea530d5cae471eb05a60de8710258451d75883c38311aeecc846c297cdc02b0a0574eb73f4e437593aef065f12152f364&ascene=1&uin=MjIwODk2NDgxNw%3D%3D&devicetype=Windows+10+x64&version=6300002f&lang=zh_TW&exportkey=AlTbzVdfdEiVVNn4RcEsoUw%3D&pass_ticket=Z%2BRZEUf7mr7CGrBcuX%2FN56qudWaAkX4QG6OqLIvPg9UcmjOAGLAWHzGXV5GJTRxp&wx_header=0
原本: SELECT count(*) FROM table WHERE a = 1 AND b = 2
推薦: SELECT 1 FROM table WHERE a = 1 AND b = 2 LIMIT 1
https://mp.weixin.qq.com/s/6TThlJICZzgGqad-sX1BrQ
讀取適當的記錄LIMIT M,N，而不要讀多餘的記錄
select id,name from t limit 866613, 20 [X]
使用上述sql語句做分頁的時候，可能有人會發現，隨著表數據量的增加，直接使用limit分頁查詢會越來越慢。
對於 limit m, n 的分頁查詢，越往後面翻頁（即m越大的情況下）SQL的耗時會越來越長，對於這種應該先取出主鍵id，然後通過主鍵id跟原表進行Join關聯查詢。因為MySQL 並不是跳過offset 行，而是取 offset+N 行，然後放棄前offset 行，返回N 行，那當offset 特別大的時候，效率就非常的低下，要么控制返回的總頁數，要么對超過特定閾值的頁數進行SQL 改寫。
優化的方法如下：可以取前一頁的最大行數的id（將上次遍歷到的最末尾的數據ID傳給數據庫，然後直接定位到該ID處，再往後面遍歷數據），然後根據這個最大的id來限制下一頁的起點。比如此列中，上一頁最大的id是866612。sql可以採用如下的寫法：
select id,name from table_name where id> 866612 limit 20 [O]