如何寫出一手好 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
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...