如何寫出一手好 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
  • 通過WPF的按鈕、文本輸入框實現了一個簡單的SpinBox數字輸入用戶組件並可以通過數據綁定數值和步長。本文中介紹了通過Xaml代碼實現自定義組件的佈局,依賴屬性的定義和使用等知識點。 ...
  • 以前,我看到一個朋友在對一個系統做初始化的時候,通過一組魔幻般的按鍵,調出來一個隱藏的系統設置界面,這個界面在常規的菜單或者工具欄是看不到的,因為它是一個後臺設置的關鍵界面,不公開,同時避免常規用戶的誤操作,它是作為一個超級管理員的入口功能,這個是很不錯的思路。其實Winform做這樣的處理也是很容... ...
  • 一:背景 1. 講故事 前些天有位朋友找到我,說他的程式每次關閉時就會自動崩潰,一直找不到原因讓我幫忙看一下怎麼回事,這位朋友應該是第二次找我了,分析了下 dump 還是挺經典的,拿出來給大家分享一下吧。 二:WinDbg 分析 1. 為什麼會崩潰 找崩潰原因比較簡單,用 !analyze -v 命 ...
  • 在一些報表模塊中,需要我們根據用戶操作的名稱,來動態根據人員姓名,更新報表的簽名圖片,也就是電子手寫簽名效果,本篇隨筆介紹一下使用FastReport報表動態更新人員簽名圖片。 ...
  • 最新內容優先發佈於個人博客:小虎技術分享站,隨後逐步搬運到博客園。 創作不易,如果覺得有用請在Github上為博主點亮一顆小星星吧! 博主開始學習編程於11年前,年少時還只會使用cin 和cout ,給單片機點點燈。那時候,類似async/await 和future/promise 模型的認知還不是 ...
  • 之前在阿裡雲ECS 99元/年的活動實例上搭建了一個測試用的MINIO服務,以前都是直接當基礎設施來使用的,這次準備自己學一下S3相容API相關的對象存儲開發,因此有了這個小工具。目前僅包含上傳功能,後續計劃開發一個類似圖床的對象存儲應用。 ...
  • 目錄簡介快速入門安裝 NuGet 包實體類User資料庫類DbFactory增刪改查InsertSelectUpdateDelete總結 簡介 NPoco 是 PetaPoco 的一個分支,具有一些額外的功能,截至現在 github 星數 839。NPoco 中文資料沒多少,我是被博客園群友推薦的, ...
  • 前言 前面使用 Admin.Core 的代碼生成器生成了通用代碼生成器的基礎模塊 分組,模板,項目,項目模型,項目欄位的基礎功能,本篇繼續完善,實現最核心的模板生成功能,並提供生成預覽及代碼文件壓縮下載 準備 首先清楚幾個模塊的關係,如何使用,簡單畫一個流程圖 前面完成了基礎的模板組,模板管理,項目 ...
  • 假設需要實現一個圖標和文本結合的按鈕 ,普通做法是 直接重寫該按鈕的模板; 如果想作為通用的呢? 兩種做法: 附加屬性 自定義控制項 推薦使用附加屬性的形式 第一種:附加屬性 創建Button的附加屬性 ButtonExtensions 1 public static class ButtonExte ...
  • 在C#中,委托是一種引用類型的數據類型,允許我們封裝方法的引用。通過使用委托,我們可以將方法作為參數傳遞給其他方法,或者將多個方法組合在一起,從而實現更靈活的編程模式。委托類似於函數指針,但提供了類型安全和垃圾回收等現代語言特性。 基本概念 定義委托 定義委托需要指定它所代表的方法的原型,包括返回類 ...