mysql優化之慢查詢代碼優化（mysql 查詢變慢）

本文目錄一覽：

• 1、MySQL查詢效率很慢的問題如何分析和解決
• 2、MySQL刪除千萬級數據量導致的慢查詢優化
• 3、mysql如何優化以下語句，查詢耗時太久了？
• 4、mysql 存儲過程執行太慢怎麼優化

MySQL查詢效率很慢的問題如何分析和解決

MySQL 在崩潰恢復時，會遍歷打開所有 ibd 文件的 header page 驗證數據字典的準確性，如果 MySQL 中包含了大量表，這個校驗過程就會比較耗時。 MySQL 下崩潰恢復確實和表數量有關，表總數越大，崩潰恢復時間越長。另外磁盤 IOPS 也會影響崩潰恢復時間，像這裡開發庫的 HDD IOPS 較低，因此面對大量的表空間，校驗速度就非常緩慢。另外一個發現，MySQL 8 下正常啟用時居然也會進行表空間校驗，而故障恢復時則會額外再進行一次表空間校驗，等於校驗了 2 遍。不過 MySQL 8.0 里多了一個特性，即表數量超過 5W 時，會啟用多線程掃描，加快表空間校驗過程。

如何跳過校驗MySQL 5.7 下有方法可以跳過崩潰恢復時的表空間校驗過程嘛？查閱了資料，方法主要有兩種：

1. 配置 innodb_force_recovery可以使 srv_force_recovery != 0 ，那麼 validate = false，即可以跳過表空間校驗。實際測試的時候設置 innodb_force_recovery =1，也就是強制恢復跳過壞頁，就可以跳過校驗，然後重啟就是正常啟動了。通過這種臨時方式可以避免崩潰恢復後非常耗時的表空間校驗過程，快速啟動 MySQL，個人目前暫時未發現有什麼隱患。2. 使用共享表空間替代獨立表空間這樣就不需要打開 N 個 ibd 文件了，只需要打開一個 ibdata 文件即可，大大節省了校驗時間。自從聽了姜老師講過使用共享表空間替代獨立表空間解決 drop 大表時性能抖動的原理後，感覺共享表空間在很多業務環境下，反而更有優勢。

臨時冒出另外一種解決想法，即用 GDB 調試崩潰恢復，通過臨時修改 validate 變量值讓 MySQL 跳過表空間驗證過程，然後讓 MySQL 正常關閉，重新啟動就可以正常啟動了。但是實際測試發現，如果以 debug 模式運行，確實可以臨時修改 validate 變量，跳過表空間驗證過程，但是 debug 模式下代碼運行效率大打折扣，反而耗時更長。而以非 debug 模式運行，則無法修改 validate 變量，想法破滅。

MySQL刪除千萬級數據量導致的慢查詢優化

有人刪了千萬級的數據，結果導致頻繁的慢查詢。

線上收到大量慢查詢告警，於是檢查慢查詢的SQL，發現不是啥複雜SQL，這些SQL主要針對一個表，基本都是單行查詢，看起來應該不會有慢查詢。這種SQL基本上都是直接根據索引查找出來的，性能應該極高。

是否可能慢查詢不是SQL問題，而是MySQL生產服務器的問題？特殊情況下，MySQL出現慢查詢還真不是SQL問題，而是他自己生產服務器的負載太高，導致SQL語句執行慢。比如現在MySQL服務器的

磁盤I/O負載高，每秒執行大量高負載的隨機I/O，但磁盤本身每秒能執行的隨機I/O有限，導致正常SQL在磁盤執行時，若跑一些隨機IO，你的磁盤太忙，顧不上你了，導致你本來很快的一個SQL，要等很久才能執行完畢，這時就可能導致正常SQL也變成慢查詢。

也許網絡負載高，導致你一個SQL語句要發到MySQL，光是等待獲取一個和MySQL的連接，都很難，要等很久或MySQL自己網絡負載太高，帶寬打滿，帶寬打滿後，你一個SQL也許執行很快，但其查出來的數據返回給你，網絡都送不出去，也會變成慢查詢。

若CPU負載過高，也會導致CPU過於繁忙去執行別的任務，沒時間執行你的SQL。

所以慢查詢不一定是SQL本身導致，若覺得SQL不應該會慢查詢，結果他那個時間段跑這個SQL 就是慢，應排查當時MySQL服務器的負載，尤其看看磁盤、網絡及 CPU 的負載，是否正常。

當某個離線作業瞬間大批量把數據往MySQL里灌入的時，他一瞬間服務器磁盤、網絡以及CPU的負載會超高。

此時你一個正常SQL執行下去，短時間內一定會慢查詢，類似問題，優化手段更多是控制你導致MySQL負載過高的那些行為，比如灌入大量數據，最好在業務低峰期灌入，別影響高峰期的線上系統運行。

但看了下MySQL服務器的磁盤、網絡以及CPU負載，一切正常，似乎也不是這問題導致。看起來無解了？

慢 SQL 的頭兩步排查手段：

這兩種辦法都不奏效之後，第三步：用MySQL profilling工具去細緻的分析SQL語句的執行過程和耗時。

這個工具可以對SQL語句的執行耗時進行非常深入和細緻的分析

打開profiling，使用

接着MySQL就會自動記錄查詢語句的profiling信息。此時若執行show profiles，就會給你列出各種查詢語句的profiling信息，會記錄下來每個查詢語句的query id，所以你要針對你需要分析的query找到對他的query id，我們當時就是針對慢查詢的那個SQL語句找到了query id。

然後針對單個查詢語句，看其profiling信息，使用show profile cpu, block io for query xx，這裡的xx是數字，此時就可以看到具體的profile信息。

除了cpu以及block io以外，還能指定去看這個SQL語句執行時候的其他各項負載和耗時。

會給你展示出來SQL語句執行時候的各種耗時，比如磁盤IO的耗時，CPU等待耗時，發送數據耗時，拷貝數據到臨時表的耗時等，SQL執行過程中的各種耗時都會展示。

檢查該SQL語句的profiling信息後，發現問題，其Sending Data耗時最高，幾乎使用1s，佔據SQL執行耗時的99%！其他環節耗時低可以理解，畢竟這種簡單SQL執行速度真的很快，基本就是10ms級別，結果跑成1s，那肯定Sending Data就是問題根源！

這Sending Data在幹啥呢？

MySQL官方釋義：為一個SELECT語句讀取和處理數據行，同時發送數據給客戶端的過程，簡單來說就是為你的SELECT語句把數據讀出來，同時發送給客戶端。

但這過程為啥這麼慢？profiling確實是提供給我們更多的線索了，但似乎還是沒法解決問題。但已經捕獲到異常關鍵點，就是Sending Data的耗時很高！

接着：

看innodb存儲引擎的一些狀態，此時發現一個奇怪的指標：history list length，值特別高，達到上萬。

MVCC就是多個事務在對同一個數據， 有人寫，有人讀，此時可以有多種隔離級別，對一個數據有個多版本快照鏈條，才能實現MVCC和各種隔離級別。

所以當你有大量事務執行時，就會構建這種undo多版本快照鏈條，此時history list length就會很高。然後在事務提交後，會有一個多版本快照鏈條的自動purge清理機制，清理了，該值就會降低。一般該值不應過高，所以注意到第二個線索：history list length過高，即大量的undo多版本鏈條數據沒有清理。推測可能有的事務長時間運行，所以其多版本快照不能被purge清理，進而導致history list length過高。

經過這倆線索推測，在大量簡單SQL變成慢查詢時，SQL因為Sending Data環節異常，耗時過高；同時此時出現一些長事務長時間運行，大量的頻繁更新數據，導致有大量undo多版本快照鏈條，還無法purge清理。

因為發現有大量的更新語句在活躍，而且有那種長期活躍的長事務一直在跑而沒有結束，問了下系統負責人，在後台跑了個定時任務：他居然開了一個事務，然後在一個事務里刪除上千萬數據，導致該事務一直在運行。

這種長事務的運行會導致你刪除時，僅只是對數據加了一個刪除標記，事實上並沒有徹底刪除。此時你若和長事務同時運行的其它事務里再查詢，他在查詢時可能會把那上千萬被標記為刪除的數據都掃描一遍。因為每次掃描到一批數據，都發現標記為刪除了，接着就會再繼續往下掃描，所以才導致一些查詢語句很慢。

那為何你啟動一個事務，在事務里查詢，憑什麼就要去掃描之前那個長事務標記為刪除狀態的上千萬的垃圾數據？講道理，那些數據都被刪了，跟你沒關係了呀，你可以不去掃描他們 嘛！

而問題癥結在於，那個 刪除千萬級數據的事務是個長事務 ！即當你啟動新事務查詢時，那個刪除千萬級數據的長事務一直在運行，它是活躍的！結合MVCC的Read View機制，當你啟動一個新事務查詢時，會生成一個Read View。你的新事務查詢時，會根據ReadView去判斷哪些數據可見及可見的數據版本號，因為每個數據都有個版本鏈條，有時你能可見的僅是這個數據的一個 歷史 版本。

所以正是因為該長事務一直在運行，還在刪除大量數據，而且這些數據僅是邏輯刪除，所以此時你新開事務的查詢還是會讀到所有邏輯刪除數據，也就會出現千萬級的數據掃描，導致了慢查詢！

所以禁止在業務高峰期運行那種刪除大量數據的語句，因為這可能導致一些正常的SQL都變慢查詢，因為那些SQL也許會不斷掃描你標記為刪除的大量數據，好不容易掃描到一批數據，結果發現是標記為刪除的，於是繼續掃描下去，導致慢查詢！

直接kill那個正在刪除千萬級數據的長事務，所有SQL很快恢復正常。此後，大量數據清理全部放在凌晨執行，那個時候就沒什麼人使用系統了，所以查詢也很少。

mysql如何優化以下語句，查詢耗時太久了？

根據所描述的問題，可嘗試在mms_profitcenter 的FOrderID ，FSuffix列上建立索引，再查詢試試。 下面提供30種mysql常用優化方法供參考：

1.對查詢進行優化，應盡量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引。

2.應盡量避免在 where 子句中使用!=或操作符，否則將引擎放棄使用索引而進行全表掃描。

3.應盡量避免在 where 子句中對字段進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描，如：

select id from t where num is null

可以在num上設置默認值0，確保表中num列沒有null值，然後這樣查詢：

select id from t where num=0

4.應盡量避免在 where 子句中使用 or 來連接條件，否則將導致引擎放棄使用索引而進行全表掃描，如：

select id from t where num=10 or num=20

可以這樣查詢：

select id from t where num=10

union all

select id from t where num=20

5.下面的查詢也將導致全表掃描：

select id from t where name like ‘%abc%’

若要提高效率，可以考慮全文檢索。

6.in 和 not in 也要慎用，否則會導致全表掃描，如：

select id from t where num in(1,2,3)

對於連續的數值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

7.如果在 where 子句中使用參數，也會導致全表掃描。因為SQL只有在運行時才會解析局部變量，但優化程序不能將訪問計劃的選擇推遲到運行時；它必須在編譯時進行選擇。然而，如果在編譯時建立訪問計劃，變量的值還是未知的，因而無法作為索引選擇的輸入項。如下面語句將進行全表掃描：

select id from t where num=@num

可以改為強制查詢使用索引：

select id from t with(index(索引名)) where num=@num

8.應盡量避免在 where 子句中對字段進行表達式操作，這將導致引擎放棄使用索引而進行全表掃描。如：

select id from t where num/2=100

應改為:

select id from t where num=100*2

9.應盡量避免在where子句中對字段進行函數操作，這將導致引擎放棄使用索引而進行全表掃描。如：

select id from t where substring(name,1,3)=’abc’–name以abc開頭的id

select id from t where datediff(day,createdate,’2005-11-30′)=0–‘2005-11-30’生成的id

應改為:

select id from t where name like ‘abc%’

select id from t where createdate=’2005-11-30′ and createdate’2005-12-1′

10.不要在 where 子句中的“=”左邊進行函數、算術運算或其他表達式運算，否則系統將可能無法正確使用索引。

11.在使用索引字段作為條件時，如果該索引是複合索引，那麼必須使用到該索引中的第一個字段作為條件時才能保證系統使用該索引，否則該索引將不會被使用，並且應儘可能的讓字段順序與索引順序相一致。

12.不要寫一些沒有意義的查詢，如需要生成一個空表結構：

select col1,col2 into #t from t where 1=0

這類代碼不會返回任何結果集，但是會消耗系統資源的，應改成這樣：

create table #t(…)

13.很多時候用 exists 代替 in 是一個好的選擇：

select num from a where num in(select num from b)

用下面的語句替換：

select num from a where exists(select 1 from b where num=a.num)

14.並不是所有索引對查詢都有效，SQL是根據表中數據來進行查詢優化的，當索引列有大量數據重複時，SQL查詢可能不會去利用索引，如一表中有字段sex，male、female幾乎各一半，那麼即使在sex上建了索引也對查詢效率起不了作用。

15.索引並不是越多越好，索引固然可以提高相應的 select 的效率，但同時也降低了 insert 及 update 的效率，因為 insert 或 update 時有可能會重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。一個表的索引數最好不要超過6個，若太多則應考慮一些不常使用到的列上建的索引是否有必要。

16.應儘可能的避免更新 clustered 索引數據列，因為 clustered 索引數據列的順序就是表記錄的物理存儲順序，一旦該列值改變將導致整個表記錄的順序的調整，會耗費相當大的資源。若應用系統需要頻繁更新 clustered 索引數據列，那麼需要考慮是否應將該索引建為 clustered 索引。

17.盡量使用數字型字段，若只含數值信息的字段盡量不要設計為字符型，這會降低查詢和連接的性能，並會增加存儲開銷。這是因為引擎在處理查詢和連接時會逐個比較字符串中每一個字符，而對於數字型而言只需要比較一次就夠了。

18.儘可能的使用 varchar/nvarchar 代替 char/nchar ，因為首先變長字段存儲空間小，可以節省存儲空間，其次對於查詢來說，在一個相對較小的字段內搜索效率顯然要高些。

19.任何地方都不要使用 select * from t ，用具體的字段列表代替“*”，不要返回用不到的任何字段。

20.盡量使用表變量來代替臨時表。如果表變量包含大量數據，請注意索引非常有限（只有主鍵索引）。

21.避免頻繁創建和刪除臨時表，以減少系統表資源的消耗。

22.臨時表並不是不可使用，適當地使用它們可以使某些例程更有效，例如，當需要重複引用大型表或常用表中的某個數據集時。但是，對於一次性事件，最好使用導出表。

23.在新建臨時表時，如果一次性插入數據量很大，那麼可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果數據量不大，為了緩和系統表的資源，應先create table，然後insert。

24.如果使用到了臨時表，在存儲過程的最後務必將所有的臨時表顯式刪除，先 truncate table ，然後 drop table ，這樣可以避免系統表的較長時間鎖定。

25.盡量避免使用游標，因為游標的效率較差，如果游標操作的數據超過1萬行，那麼就應該考慮改寫。

26.使用基於游標的方法或臨時表方法之前，應先尋找基於集的解決方案來解決問題，基於集的方法通常更有效。

27.與臨時表一樣，游標並不是不可使用。對小型數據集使用 FAST_FORWARD 游標通常要優於其他逐行處理方法，尤其是在必須引用幾個表才能獲得所需的數據時。在結果集中包括“合計”的例程通常要比使用游標執行的速度快。如果開發時間允許，基於游標的方法和基於集的方法都可以嘗試一下，看哪一種方法的效果更好。

28.在所有的存儲過程和觸發器的開始處設置 SET NOCOUNT ON ，在結束時設置 SET NOCOUNT OFF 。無需在執行存儲過程和觸發器的每個語句後向客戶端發送 DONE_IN_PROC 消息。

29.盡量避免向客戶端返回大數據量，若數據量過大，應該考慮相應需求是否合理。

30.盡量避免大事務操作，提高系統並發能力。

mysql 存儲過程執行太慢怎麼優化

1.當我們請求mysql服務器的時候,MySQL前端會有一個監聽,請求到了之後,服務器得到相關的SQL語句,執行之前(虛線部分為執行),還會做權限的判斷

2.通過權限之後,SQL就到MySQL內部,他會在查詢緩存中,看該SQL有沒有執行過,如果有查詢過,則把緩存結果返回,說明在MySQL內部,也有一個查詢緩存.但是這個查詢緩存,默認是不開啟的,這個查詢緩存,和我們的Hibernate，Mybatis的查詢緩存是一樣的,因為查詢緩存要求SQL和參數都要一樣,所以這個命中率是非常低的（沒什麼卵用的意思）。

3.如果我們沒有開啟查詢緩存,或者緩存中沒有找到對應的結果,那麼就到了解析器,解析器主要對SQL語法進行解析

4.解析結束後就變成一顆解析樹,這個解析樹其實在Hibernate裡面也是有的,大家回憶一下,在以前做過Hibernate項目的時候,是不是有個一個antlr.jar。這個就是專門做語法解析的工具.因為在Hibernate裡面有HQL，它就是通過這個工具轉換成SQL的,我們編程語言之所以有很多規範、語法,其實就是為了便於這個解析器解析,這個學過編譯原理的應該知道.

5.得到解析樹之後,不能馬上執行,這還需要對這棵樹進行預處理,也就是說,這棵樹,我沒有經過任何優化的樹,預處理器會這這棵樹進行一些預處理,比如常量放在什麼地方,如果有計算的東西,把計算的結果算出來等等…

6.預處理完畢之後,此時得到一棵比較規範的樹,這棵樹就是要拿去馬上做執行的樹,比起之前的那棵樹,這棵得到了一些優化

7.查詢優化器，是MySQL裡面最關鍵的東西,我們寫任何一條SQL,比如SELECT * FROM USER WHERE USERNAME = toby AND PASSWORD = 1,它會怎麼去執行?它是先執行username = toby還是password = 1?每一條SQL的執行順序查詢優化器就是根據MySQL對數據統計表的一些信息,比如索引,比如表一共有多少數據,MySQL都是有緩存起來的,在真正執行SQL之前,他會根據自己的這些數據,進行一個綜合的判定,判斷這一次在多種執行方式裡面,到底選哪一種執行方式,可能運行的最快.這一步是MySQL性能中,最關鍵的核心點,也是我們的優化原則.我們平時所講的優化SQL,其實說白了,就是想讓查詢優化器,按照我們的想法,幫我們選擇最優的執行方案,因為我們比MySQL更懂我們的數據.MySQL看數據,僅僅只是自己收集到的信息,這些信息可能是不準確的,MySQL根據這些信息選了一個它自認為最優的方案,但是這個方案可能和我們想象的不一樣.

8.這裡的查詢執行計劃,也就是MySQL查詢中的執行計劃,比如要先執行username = toby還是password = 1

9.這個執行計劃會傳給查詢執行引擎,執行引擎選擇存儲引擎來執行這一份傳過來的計劃,到磁盤中的文件中去查詢,這個時候重點來了,影響這個查詢性能最根本的原因是什麼?就是硬盤的機械運動,也就是我們平時熟悉的IO,所以一條查詢語句是快還是慢,就是根據這個時間的IO來確定的.那怎麼執行IO又是什麼來確定的?就是傳過來的這一份執行計劃.(優化就是制定一個我們認為最快的執行方案,最節省IO,和執行最快)

10.如果開了查詢緩存,則返回結果給客戶端,並且查詢緩存也放一份。