昨天遇到一個問題, 200萬的表裡查詢9萬條資料, 耗時達63秒. 200萬資料不算多, 查詢9萬也還好. 怎麼用了這麼長的時間呢? 問題是一句非常簡單的sql.
select * from tk_template_product t WHERE t.product_id=1135 前提: product_id已經添加了索引, 可依然慢的無法接受.
目标: 優化sql, 至少要在5秒以内完成
針對這個問題開始優化. 優化過程如下:
1. sql語句優化
2. 使用explain分析
3. 使用profile分析SQL執行狀态
4. MySQL Sending data導緻查詢很慢的問題詳細分析
5. mysql查詢慢Sending data耗時問題
6. 啟用MySQL查詢緩存,
7. 最終的解決方案
1. sql語句優化. 将 * 改為具體查詢某個字段,
select t.id from tk_template_product t WHERE t.product_id=1136 AND t.deleted=0
隻是查詢了一個字段, 資料量還是200萬查詢9萬, 耗時6-7秒. 多一個字段, 時間翻一倍.
網上還有其他sql語句優化的點, 但是, 我這個語句用不上呀, 這已經是一個最簡單的sql語句了
2. 使用explain對sql語句進行分析, 看看是否使用了索引
首先懷疑索引沒有建好，于是使用explain檢視查詢計劃
explain select product_id, t.`template_id` from `tk_template_product` t where t.product_id=1136 and t.`deleted` = 0;
查詢結果:
從explain的結果來看，整個語句的索引設計是沒有問題的，
下面來看看這幾個字段你的含義:
- id
我的了解是SQL執行的順序的辨別,SQL從大到小的執行
1. id相同時，執行順序由上至下
2. 如果是子查詢，id的序号會遞增，id值越大優先級越高，越先被執行
3.id如果相同，可以認為是一組，從上往下順序執行；在所有組中，id值越大，優先級越高，越先執行
- select_type
示查詢中每個select子句的類型
(1) SIMPLE(簡單SELECT,不使用UNION或子查詢等)
(2) PRIMARY(查詢中若包含任何複雜的子部分,最外層的select被标記為PRIMARY)
(3) UNION(UNION中的第二個或後面的SELECT語句)
(4) DEPENDENT UNION(UNION中的第二個或後面的SELECT語句，取決于外面的查詢)
(5) UNION RESULT(UNION的結果)
(6) SUBQUERY(子查詢中的第一個SELECT)
(7) DEPENDENT SUBQUERY(子查詢中的第一個SELECT，取決于外面的查詢)
(8) DERIVED(派生表的SELECT, FROM子句的子查詢)
(9) UNCACHEABLE SUBQUERY(一個子查詢的結果不能被緩存，必須重新評估外連結的第一行)
- table
顯示這一行的資料是關于哪張表的，有時不是真實的表名字,看到的是derivedx(x是個數字,我的了解是第幾步執行的結果)
我這裡顯示的是表的别名
- type
表示MySQL在表中找到所需行的方式，又稱“通路類型”。
常用的類型有： ALL, index, range, ref, eq_ref, const, system, NULL（從左到右，性能從差到好）
ALL：Full Table Scan， MySQL将周遊全表以找到比對的行
index: Full Index Scan，index與ALL差別為index類型隻周遊索引樹
range:隻檢索給定範圍的行，使用一個索引來選擇行
ref: 表示上述表的連接配接比對條件，即哪些列或常量被用于查找索引列上的值(我們這裡顯示的這一項, 表示使用了索引)
eq_ref: 類似ref，差別就在使用的索引是唯一索引，對于每個索引鍵值，表中隻有一條記錄比對，簡單來說，就是多表連接配接中使用primary key或者 unique key作為關聯條件
const、system: 當MySQL對查詢某部分進行優化，并轉換為一個常量時，使用這些類型通路。如将主鍵置于where清單中，MySQL就能将該查詢轉換為一個常量,system是const類型的特例，當查詢的表隻有一行的情況下，使用system
NULL: MySQL在優化過程中分解語句，執行時甚至不用通路表或索引，例如從一個索引列裡選取最小值可以通過單獨索引查找完成。
- possible_keys
指出MySQL能使用哪個索引在表中找到記錄，查詢涉及到的字段上若存在索引，則該索引将被列出，但不一定被查詢使用
該列完全獨立于EXPLAIN輸出所示的表的次序。這意味着在possible_keys中的某些鍵實際上不能按生成的表次序使用。
如果該列是NULL，則沒有相關的索引。在這種情況下，可以通過檢查WHERE子句看是否它引用某些列或适合索引的列來提高你的查詢性能。如果是這樣，創造一個适當的索引并且再次用EXPLAIN檢查查詢
- Key
key列顯示MySQL實際決定使用的鍵（索引）
如果沒有選擇索引，鍵是NULL。要想強制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
- key_len
表示索引中使用的位元組數，可通過該列計算查詢中使用的索引的長度（key_len顯示的值為索引字段的最大可能長度，并非實際使用長度，即key_len是根據表定義計算而得，不是通過表内檢索出的）
不損失精确性的情況下，長度越短越好
- ref
表示上述表的連接配接比對條件，即哪些列或常量被用于查找索引列上的值
- rows
表示MySQL根據表統計資訊及索引選用情況，估算的找到所需的記錄所需要讀取的行數
- Extra
該列包含MySQL解決查詢的詳細資訊,有以下幾種情況：
Using where:列資料是從僅僅使用了索引中的資訊而沒有讀取實際的行動的表傳回的，這發生在對表的全部的請求列都是同一個索引的部分的時候，表示mysql伺服器将在存儲引擎檢索行後再進行過濾
Using temporary：表示MySQL需要使用臨時表來存儲結果集，常見于排序和分組查詢
Using filesort：MySQL中無法利用索引完成的排序操作稱為“檔案排序”
Using join buffer：改值強調了在擷取連接配接條件時沒有使用索引，并且需要連接配接緩沖區來存儲中間結果。如果出現了這個值，那應該注意，根據查詢的具體情況可能需要添加索引來改進能。
Impossible where：這個值強調了where語句會導緻沒有符合條件的行。
Select tables optimized away：這個值意味着僅通過使用索引，優化器可能僅從聚合函數結果中傳回一行
總結：
• EXPLAIN不會告訴你關于觸發器、存儲過程的資訊或使用者自定義函數對查詢的影響情況
• EXPLAIN不考慮各種Cache
• EXPLAIN不能顯示MySQL在執行查詢時所作的優化工作
• 部分統計資訊是估算的，并非精确值
• EXPALIN隻能解釋SELECT操作，其他操作要重寫為SELECT後檢視執行計劃。
這個步驟, 能夠得出的結論是, 我的這個sql語句使用了緩存, 緩存字段是product_id, 但是并沒有顯示出為什麼會這麼慢
3. 使用status檢視mysql運作狀态
show table status
可以檢視到表中的行數, 每一行的容量大小, 以及總容量大小. 表可存儲資料, 剩餘存儲資料數等資訊
4. show processlist : 這時再通過show processlist指令來檢視目前正在運作的SQL，從中找出運作慢的SQL語句，找到執行慢的語句後，再用explain指令檢視這些語句的執行計劃。
這裡可以看出是哪個ip發出的請求, 通路的哪個資料庫, 指令是否已經執行完成, 耗時等資訊. 通過這些可以看到正在執行的慢查詢. 然後再用profile進行分析, 看看到底慢在哪裡了
5. 使用profile分析SQL執行狀态
打開profile, mysql可以通過profiling指令檢視到執行查詢SQL消耗的時間。預設情況下，mysql是關閉profiling的
select @@have_profiling;
select @@profiling; 第一次執行語句, 傳回結果可能是0.
0：表示profiling功能是關閉；
1：表示打開的。
設定profiling打開. set session profiling=1;
select @@profiling;
以上表示profiling已經打開了
下面, 使用profile分析SQL，可以看到執行兩次後,Send data和sending cached result to clien執行效率的變化
執行一個sql語句
select count(*) from dtc_mntk.tk_template_product;
執行show profiles; 查詢所有 sql執行的時間
show profiles;
找到剛剛我們執行sql語句的第query_id, 通過下面的sql查詢具體分析
show profile for query 2;
通過分析, 可以看出Sending data耗時最多,
下面是我要查詢的資料的内容
select t.id from dtc_mntk.`tk_template_product` t where t.product_id=1136 and t.`deleted` = 0;
耗時: 11秒, 隻是查詢了一個字段
show profile for query 4;
可以看到sending data耗時10秒, 也就是基本上所有的耗時都在這裡了, 接下來就要分析, 如何優化sending data
查詢一下“Sending data”狀态的含義，原來這個狀态的名稱很具有誤導性，所謂的“Sending data”并不是單純的發送資料，而是包括“收集 + 發送 資料”。
這裡的關鍵是為什麼要收集資料，原因在于：mysql使用“索引”完成查詢結束後，mysql得到了一堆的行id，如果有的列并不在索引中，mysql需要重新到“資料行”上将需要傳回的資料讀取出來傳回個用戶端。
那麼sending data為什麼會耗時這麼長呢?
有幾種原因
- 字段的長度過大, 可以通過show table status來檢視表的狀态
紅色圈出的部分就是我們查詢的相關表的資訊. Avg_row_length: 表示平均每一行的長度. 這裡大約0.05K, 這個還不是很大. 但像下面那個8192, 表示大約8K, 這就很恐怖了, 每行大約8M, 這可能不是資料量真的有這麼大, 而是, 設定的字段長度設定的很大. 需要優化.
如果字段設定就要求這麼大, 那麼查詢出來的時候, 最好不查詢這一列
【解決方法】
找到了問題的根本原因，解決方法也就不難了。有幾種方法：
1）查詢時去掉列長度較大的查詢，但這受限于業務的實作，可能需要業務做較大調整
2）表結構優化，将列長度較大的表進行拆分到另外的表，這個改動較大，需要已有業務配合修改，且如果業務還是要繼續查詢這個description的資訊，則優化後的性能也不會有很大提升。
6. 啟用MySQL查詢緩存
https://www.cnblogs.com/mengfanrong/p/5335724.html
參考這篇文章, 可以設定mysql緩存, 但并不是所有設定了的緩存都會生效. 比如我查詢的這個9萬條資料, 緩存是不生效的, 因為資料量很大
7. 最後解決方案
比較滑稽的事, 上面做了這麼多工作, 最後的解決方案是修改sql語句. 換一個寫法. 為什麼呢? 這次讓我對jpa的用法有了一個更深刻的認識
JPA的用法總結:
1. 使用起來非常友善, 内部定義了很多配合方法, 簡化sql.
2. 使用的是單表查詢, 單表查詢比連表要快很多, 查詢出來以後, 将業務邏輯在代碼裡拼接, io消耗比與資料庫互動少很多.
JPA使用注意事項:
1. 不可使用FindAll(), 盡量不要使用這個方法, 把所有資料查詢出來, 然後在代碼裡處理邏輯. 不帶limit, 這個方法就有坑, 資料量小還可以, 資料量大傳輸會特别耗時
2. 如果隻是要擷取記錄條數, 而不需要擷取内容的時候, 直接sql查詢使用count(). 不要Find出來再size(). 這個坑也是在資料量大的時候.
我這個問題就是, 查詢出來了所有的資料, 9萬條啊, 然後使用stream分組, 分組後去size, 這個操作在資料庫一個sql就完事, 用時不到1秒. 可把所有資料查出來, 竟然用了68秒.