如何寫出一手好 SQL ?

来源:https://www.cnblogs.com/javastack/archive/2022/05/10/16253870.html
-Advertisement-
Play Games

來源:編碼磚家 鏈接:cnblogs.com/xiaoyangjia/p/11267191.html 背景 最近頻繁出現慢SQL告警,執行時間最長的竟然高達5分鐘。導出日誌後分析,主要原因竟然是沒有命中索引和沒有分頁處理 。 其實這是非常低級的錯誤,我不禁後背一涼,團隊成員的技術水平亟待提高啊。改造 ...


來源:編碼磚家  
鏈接:cnblogs.com/xiaoyangjia/p/11267191.html

背景

最近頻繁出現慢SQL告警,執行時間最長的竟然高達5分鐘。導出日誌後分析,主要原因竟然是沒有命中索引和沒有分頁處理

其實這是非常低級的錯誤,我不禁後背一涼,團隊成員的技術水平亟待提高啊。改造這些SQL的過程中,總結了一些經驗分享給大家,如果有錯誤歡迎批評指正。

MySQL性能

最大數據量

拋開數據量和併發數,談性能都是耍流氓 。MySQL沒有限制單表最大記錄數,它取決於操作系統對文件大小的限制。

文件系統 單文件大小限制
FAT32 最大4G
NTFS 最大64GB
NTFS5.0 最大2TB
EXT2 塊大小為1024位元組,文件最大容量16GB;塊大小為4096位元組,文件最大容量2TB
EXT3 塊大小為4KB,文件最大容量為4TB
EXT4 理論可以大於16TB

《阿裡巴巴Java開發手冊》提出單表行數超過500萬行或者單表容量超過2GB,才推薦分庫分表。性能由綜合因素決定,拋開業務複雜度,影響程度依次是硬體配置、MySQL配置、數據表設計、索引優化。500萬這個值僅供參考,並非鐵律。

博主曾經操作過超過4億行數據的單表,分頁查詢最新的20條記錄耗時0.6秒,SQL語句大致是 select field_1,field_2 from table where id < #{prePageMinId} order by id desc limit 20,prePageMinId是上一頁數據記錄的最小ID。雖然當時查詢速度還湊合,隨著數據不斷增長,有朝一日必定不堪重負。

分庫分表是個周期長而風險高的大活兒,應該儘可能在當前結構上優化,比如升級硬體、遷移歷史數據等等,實在沒轍了再分。對分庫分表感興趣的同學可以閱讀分庫分表的基本思想。

最大併發數

併發數是指同一時刻資料庫能處理多少個請求,由maxconnections和maxuserconnections決定。maxconnections是指MySQL實例的最大連接數,上限值是16384,maxuser*connections是指每個資料庫用戶的最大連接數。

MySQL會為每個連接提供緩衝區,意味著消耗更多的記憶體。如果連接數設置太高硬體吃不消,太低又不能充分利用硬體。一般要求兩者比值超過10%,計算方法如下:

max_used_connections / max_connections * 100% = 3/100 *100% ≈ 3%

查看最大連接數與響應最大連接數:

show variables like '%max_connections%';show variables like '%max_user_connections%';

在配置文件my.cnf中修改最大連接數

[mysqld]max_connections = 100max_used_connections = 20

查詢耗時0.5秒

建議將單次查詢耗時控制在0.5秒以內,0.5秒是個經驗值,源於用戶體驗的 3秒原則 。如果用戶的操作3秒內沒有響應,將會厭煩甚至退出。響應時間=客戶端UI渲染耗時+網路請求耗時+應用程式處理耗時+查詢資料庫耗時,0.5秒就是留給資料庫1/6的處理時間。

實施原則

相比NoSQL資料庫,MySQL是個嬌氣脆弱的家伙。它就像體育課上的女同學,一點糾紛就和同學鬧彆扭(擴容難),跑兩步就氣喘吁吁(容量小併發低),常常身體不適要請假(SQL約束太多)。如今大家都會搞點分散式,應用程式擴容比資料庫要容易得多,所以實施原則是 資料庫少幹活,應用程式多幹活

  • 充分利用但不濫用索引,須知索引也消耗磁碟和CPU。
  • 不推薦使用資料庫函數格式化數據,交給應用程式處理。
  • 不推薦使用外鍵約束,用應用程式保證數據準確性。
  • 寫多讀少的場景,不推薦使用唯一索引,用應用程式保證唯一性。
  • 適當冗餘欄位,嘗試創建中間表,用應用程式計算中間結果,用空間換時間。
  • 不允許執行極度耗時的事務,配合應用程式拆分成更小的事務。
  • 預估重要數據表(比如訂單表)的負載和數據增長態勢,提前優化。

數據表設計

數據類型

數據類型的選擇原則:更簡單或者占用空間更小。

  • 如果長度能夠滿足,整型儘量使用tinyint、smallint、medium_int而非int。
  • 如果字元串長度確定,採用char類型。
  • 如果varchar能夠滿足,不採用text類型。
  • 精度要求較高的使用decimal類型,也可以使用BIGINT,比如精確兩位小數就乘以100後保存。

儘量採用timestamp而非datetime。

類型 占據位元組 描述
datetime 8位元組 '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999
timestamp 4位元組 '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'

相比datetime,timestamp占用更少的空間,以UTC的格式儲存自動轉換時區。

避免空值

MySQL中欄位為NULL時依然占用空間,會使索引、索引統計更加複雜。從NULL值更新到非NULL無法做到原地更新,容易發生索引分裂影響性能。儘可能將NULL值用有意義的值代替,也能避免SQL語句裡面包含 is not null的判斷。

text類型優化

由於text欄位儲存大量數據,表容量會很早漲上去,影響其他欄位的查詢性能。建議抽取出來放在子表裡,用業務主鍵關聯。

索引優化

索引分類

  1. 普通索引:最基本的索引。
  2. 組合索引:多個欄位上建立的索引,能夠加速複合查詢條件的檢索。
  3. 唯一索引:與普通索引類似,但索引列的值必須唯一,允許有空值。
  4. 組合唯一索引:列值的組合必須唯一。
  5. 主鍵索引:特殊的唯一索引,用於唯一標識數據表中的某一條記錄,不允許有空值,一般用primary key約束。
  6. 全文索引:用於海量文本的查詢,MySQL5.6之後的InnoDB和MyISAM均支持全文索引。由於查詢精度以及擴展性不佳,更多的企業選擇Elasticsearch。

索引優化

  1. 分頁查詢很重要,如果查詢數據量超過30%,MYSQL不會使用索引。

  2. 單表索引數不超過5個、單個索引欄位數不超過5個。

  3. 字元串可使用首碼索引,首碼長度控制在5-8個字元。

  4. 欄位唯一性太低,增加索引沒有意義,如:是否刪除、性別。

  5. 合理使用覆蓋索引,如下所示:

    select loginname, nickname from member where login_name = ?

loginname, nickname兩個欄位建立組合索引,比login_name簡單索引要更快

SQL優化

分批處理

博主小時候看到魚塘挖開小口子放水,水面有各種漂浮物。浮萍和樹葉總能順利通過出水口,而樹枝會擋住其他物體通過,有時還會卡住,需要人工清理。MySQL就是魚塘,最大併發數和網路帶寬就是出水口,用戶SQL就是漂浮物。

不帶分頁參數的查詢或者影響大量數據的update和delete操作,都是樹枝,我們要把它打散分批處理,舉例說明:業務描述:更新用戶所有已過期的優惠券為不可用狀態。

SQL語句:update status=0 FROMcoupon WHERE expire_date <= #{currentDate} and status=1;如果大量優惠券需要更新為不可用狀態,執行這條SQL可能會堵死其他SQL,分批處理偽代碼如下:

int pageNo = 1;
int PAGE_SIZE = 100;
while(true) {
    List<Integer> batchIdList = queryList('select id FROM `coupon` WHERE expire_date <= #{currentDate} and status = 1 limit #{(pageNo-1) * PAGE_SIZE},#{PAGE_SIZE}');
    if (CollectionUtils.isEmpty(batchIdList)) {
        return;
    }
    update('update status = 0 FROM `coupon` where status = 1 and id in #{batchIdList}')
    pageNo ++;
}

操作符<>優化

通常<>操作符無法使用索引,舉例如下,查詢金額不為100元的訂單:select id from orders where amount != 100;如果金額為100的訂單極少,這種數據分佈嚴重不均的情況下,有可能使用索引。鑒於這種不確定性,採用union聚合搜索結果,改寫方法如下:

(select id from orders where amount > 100) union all(select id from orders where amount < 100 and amount > 0)

OR優化

在Innodb引擎下or無法使用組合索引,比如:

select id,product_name from orders where mobile_no = '13421800407' or user_id = 100;

OR無法命中mobileno + userid的組合索引,可採用union,如下所示:

(select id,product_name from orders where mobile_no = '13421800407') union(select id,product_name from orders where user_id = 100);

此時id和product_name欄位都有索引,查詢才最高效。

IN優化

  1. IN適合主表大子表小,EXIST適合主表小子表大。由於查詢優化器的不斷升級,很多場景這兩者性能差不多一樣了。
  2. 嘗試改為join查詢,舉例如下:

select id from orders where user_id in (select id from user where level = 'VIP');

採用JOIN如下所示:

select o.id from orders o left join user u on o.user_id = u.id where u.level = 'VIP';

不做列運算

通常在查詢條件列運算會導致索引失效,如下所示:查詢當日訂單

select id from order where date_format(create_time,'%Y-%m-%d') = '2019-07-01';

date_format函數會導致這個查詢無法使用索引,改寫後:

select id from order where create_time between '2019-07-01 00:00:00' and '2019-07-01 23:59:59';

避免Select all

如果不查詢表中所有的列,避免使用 SELECT *,它會進行全表掃描,不能有效利用索引。

Like優化

like用於模糊查詢,舉個例子(field已建立索引):

SELECT column FROM table WHERE field like '%keyword%';

這個查詢未命中索引,換成下麵的寫法:

SELECT column FROM table WHERE field like 'keyword%';

去除了前面的%查詢將會命中索引,但是產品經理一定要前後模糊匹配呢?全文索引fulltext可以嘗試一下,但Elasticsearch才是終極武器。

Join優化

join的實現是採用Nested Loop Join演算法,就是通過驅動表的結果集作為基礎數據,通過該結數據作為過濾條件到下一個表中迴圈查詢數據,然後合併結果。如果有多個join,則將前面的結果集作為迴圈數據,再次到後一個表中查詢數據。

  1. 驅動表和被驅動表儘可能增加查詢條件,滿足ON的條件而少用Where,用小結果集驅動大結果集。
  2. 被驅動表的join欄位上加上索引,無法建立索引的時候,設置足夠的Join Buffer Size。
  3. 禁止join連接三個以上的表,嘗試增加冗餘欄位。

Limit優化

limit用於分頁查詢時越往後翻性能越差,解決的原則:縮小掃描範圍 ,如下所示:

select * from orders order by id desc limit 100000,10 耗時0.4秒select * from orders order by id desc limit 1000000,10耗時5.2秒

先篩選出ID縮小查詢範圍,寫法如下:

select * from orders where id > (select id from orders order by id desc  limit 1000000, 1) order by id desc limit 0,10耗時0.5秒

如果查詢條件僅有主鍵ID,寫法如下:

select id from orders where id between 1000000 and 1000010 order by id desc耗時0.3秒

如果以上方案依然很慢呢?只好用游標了,感興趣的朋友閱讀JDBC使用游標實現分頁查詢的方法

其他資料庫

作為一名後端開發人員,務必精通作為存儲核心的MySQL或SQL Server,也要積極關註NoSQL資料庫,他們已經足夠成熟並被廣泛採用,能解決特定場景下的性能瓶頸。

分類 資料庫 特性
鍵值型 Memcache 用於內容緩存,大量數據的高訪問負載
鍵值型 Redis 用於內容緩存,比Memcache支持更多的數據類型,並能持久化數據
列式存儲 HBase Hadoop體系的核心資料庫,海量結構化數據存儲,大數據必備。
文檔型 MongoDb 知名文檔型資料庫,也可以用於緩存
文檔型 CouchDB Apache的開源項目,專註於易用性,支持REST API
文檔型 SequoiaDB 國內知名文檔型資料庫
圖形 Neo4J 用於社交網路構建關係圖譜,推薦系統等

參考:https://www.jianshu.com/p/6864abb4d885

雞湯:你既然已經做出了選擇,又何必去問為什麼選擇。 ——衛莊

近期熱文推薦:

1.1,000+ 道 Java面試題及答案整理(2022最新版)

2.勁爆!Java 協程要來了。。。

3.Spring Boot 2.x 教程,太全了!

4.別再寫滿屏的爆爆爆炸類了,試試裝飾器模式,這才是優雅的方式!!

5.《Java開發手冊(嵩山版)》最新發佈,速速下載!

覺得不錯,別忘了隨手點贊+轉發哦!


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 本文只是通過一個實例來講述如何獲得python中所有的單字元的字母表,不僅僅是局限於英文的abcd,可能還有其他語言如ᵝᵞᵟᵠ等。在實際寫python的過程中可能不一定用得到,但是不失為一個挺有趣的功能探索。 ...
  • 在C/C++中有個叫指針的玩意存在感極其強烈,而說到指針又不得不提到記憶體管理。現在時不時能聽到一些朋友說指針很難,實際上說的是記憶體操作和管理方面的難。(這篇筆記咱也會結合自己的理解簡述一些相關的記憶體知識) 最近在寫C程式使用指針的時候遇到了幾個讓我印象深刻的地方,這裡記錄一下,以便今後回顧。 “經一 ...
  • 面向對象和麵向過程的區別 區別簡述 面向過程(Procedure Oriented):以過程為核心,強調**事件的流程、順序,**如:C語言。 面向對象(Object Oriented):以對象為核心,強調**事件的角色、主體,**如:C++、Java。 區別 1.思路不同 2.特點不同 3.優勢不 ...
  • 現象: 最近將pyinsatller升級到最新的 Version: 5.0.1版本後(之前一直用的是3.5版本同樣方法未遇到問題,今次更新到最新版本後5.0.1後打包就遇到問題,具體是這中間哪個版本開始有變化也不清楚了,也不去追究,凡是在新版本中遇到問題就在新版本中解決),詳細現象及解決辦法如下: ...
  • 昨天突發奇想想來玩一玩,然後安裝了一下午才成功,基本所有該踩的坑都踩了,但當時沒截圖,現在靠著記憶寫一下。 官網鏈接:https://www.mongodb.com/try/download/community。 需要註意的是:超過次數就必須要登錄才能下載。(證明我真的試過很多次) 1.最開始出現的 ...
  • 今天某個項目的數據有些問題,需要查詢日誌看看具體的情況 結果在執行 cat ***.log |grep "關鍵字" 命令後包如下錯誤: grep: memory exhausted 思路1: 既然提示 記憶體問題,是不是日誌文件太大了, 用 du -sh * 命令查看 後文件也就 300M 思路2: ...
  • 下載&安裝Cmake 進入下載頁面 Download | CMake 選擇安裝包版本 打開安裝包,下一步之後選擇添加path 選擇完安裝文件夾開始安裝 下載&配置OpenCV 進入下載頁面 Releases - OpenCV 選擇版本下載(我下的是 Sources,Windows版是已經構建好的,不 ...
  • 多線程筆記(二) 1. Synchronized 和 Lock 的區別 synchronized是Java的關鍵字,是 JVM 層面的內置功能和實現。 Lock是一個介面,是代碼層面的實現 synchronized可以隱式的獲取,釋放鎖 lock是顯式的獲取,釋放鎖 synchronized在發生異 ...
一周排行
    -Advertisement-
    Play Games
  • 用例演示 - 創建實體 本節將演示一些示例用例並討論可選場景。 創建實體 從實體/聚合根類創建對象是實體生命周期的第一步。聚合/聚合根規則和最佳實踐部分 建議為Entity類創建一個主構造函數,以保證創建一個有效的實體。因此,無論何時我們需要創建實體的實例,我們都應該使用那個構造函數 參見下麵的問題 ...
  • 領域邏輯 & 應用邏輯 如前所述,領域驅動設計中的業務邏輯分為兩部分(層):領域邏輯和應用邏輯: 領域邏輯由系統的核心領域規則組成,應用邏輯實現應用特定的用例 雖然定義很明確,但實現起來可能並不容易。您可能無法決定哪些代碼應該位於應用程式層,哪些代碼應該位於領域層。本節試圖解釋其中的差異 多個應用程 ...
  • 表弟大學快畢業了,學了一個學期Python居然還不會寫學生管理系統,真的給我丟臉啊,教他又不肯學,還讓我直接給他寫,我真想兩巴掌上去,最終還是寫了給他,誰讓他是我表弟呢,關鍵時候還是得幫他一把! 寫完了放在那也是放著,所以今天分享給大家吧! 話不多說,咱們直接開始吧! 代碼解析 一、登錄頁面 1、定 ...
  • Zookeeper3.7源碼剖析 能力目標 掌握Zookeeper中Session的管理機制 能基於Client進行Debug測試Session創建/刷新操作 能搭建Zookeeper集群源碼配置 掌握集群環境下Leader選舉啟動過程 能說出Zookeeper選舉過程中的概念 能說出Zookeep ...
  • 前言 今天給大家分享一下我自己寫的筆記,純純的都是乾貨,關於字好像也能看。這是我學python整理出來的一些資料,希望對大家 有用。想要更多的資料那就的給一個關註了… python學習交流Q群:903971231### #導入Counter from collections import Count ...
  • Hi,大家好,我是Mic 一個工作5年的粉絲找到我。 他說: “Mic老師,你要是能回答出這個問題,我就佩服你” 我當場就懵了,現在打賭都這麼隨意了嗎? 我問他問題是什麼,他說“Kafka如何避免重覆消費的問題!” 下麵看看普通人和高手的回答! 普通人: Kafka怎麼避免重覆消費就是我們可以通過 ...
  • 前言 Steam是由美國電子游戲商Valve於2003年9月12日推出的數字發行平臺,被認為是電腦游戲界最大的數位發行平臺之一,Steam平臺是全球最大的綜合性數字發行平臺之一。玩家可以在該平臺購買、下載、討論、上傳和分享游戲和軟體。 而每周的steam會開啟了一輪特惠,可以讓游戲打折,而玩家就會 ...
  • 本篇內容將在上一篇已有的內容基礎上,進一步的聊一下項目中使用JPA的一些高階複雜場景的實踐指導,覆蓋了主要核心的JPA使用場景,可以讓你在需求開發的時候對JPA的使用更加的游刃有餘。 ...
  • 1.路徑處理 1.找模塊:sys.path import sys print(sys.path) - 1.理解 - 1.是python去查找包或模塊 - 2.項目開始根目錄,python內置的目錄 - 3.雖然說python的安裝目錄下也可以存放我們寫的模塊,但是不建議(太多了,不大好找) - 4. ...
  • Go 語言入門練手項目系列 01 基於命令行的圖書的增刪查改 02 文件管理 持續更新中... > 本文來自博客園,作者:Arway,轉載請註明原文鏈接:https://www.cnblogs.com/cenjw/p/gobeginner-proj-bookstore-cli.html 介紹 這是一 ...