Mysql第六天 查詢 1，mysql第六天查詢

Mysql第六天 查詢 1，mysql第六天查詢

查詢的一般流程是 用戶端到伺服器，這之間有網路。 在伺服器上進行解析，產生執行計畫，執行。並且返回給用戶端。 執行中又會包含大量的調用儲存引擎檢索資料，以及檢索後的處理比如排序等。
總體來說，時間一般花費在網路、CPU計算、產生統計資訊、執行計畫，鎖等待，記憶體不足時的I/O操作等等。

先說兩個用於查看效能指標的sql.

SELECT @@profiling;SET profiling = 1;select count(*) from test;show profiles;// 來查看語句的執行時間，這個能夠最直觀的看到sql的快慢

第二個語句是：

explain select * from biz_pay_task where jd_order_id=42596246804;

其結果如下：
+—-+————-+————–+——+—————+—————+———+——-+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+————–+——+—————+—————+———+——-+——+——-+
| 1 | SIMPLE | biz_pay_task | ref | i_jd_order_id | i_jd_order_id | 8 | const | 1 | |
+—-+————-+————–+——+—————+—————+———+——-+——+——-+
主要是select_type 、key 、rows、type 這幾個選項

• key 使用了哪個索引
• rows mysql評估的可能需要檢索的數量
• type對應了查詢所使用的類型，比如All代表全表掃描，ref代表索引掃描，還會有範圍掃描、唯一索引掃描等等。最好都能夠達到ref的層級。

通常可以從如下幾個方面進行考慮

是否向資料庫請求了不需要的資料

• 沒有使用LIMIT，而是查處了大量的資料，只是用了前幾行
• 多表關聯時返回全部列，這樣會有很多重複列，最好明確指定
• 預設使用select * 盡量只返回需要的列

是否掃描了額外的記錄

這個不是很好確定，通常加上合適的索引之後就能夠減少掃描的數量，但是對於分組統計類的sql卻不能使用索引的方式了。一般我們可以有如下的方式最佳化：

• 使用覆蓋索引
• 該表庫表結構，使用單獨的匯總表
• 重寫複雜查詢，讓Mysql最佳化器能夠以更好的方式執行這個查詢

重構查詢方式一個複雜查詢還是多個簡單查詢

這個衝突放大了就是多個簡單sql語句，然後在代碼裡計算，還是用預存程序把所有計算都完成。
以前認為資料庫查詢計算很快，而跟用戶端之間的通訊的開銷是很大的。但是現在可能會越來越考慮可用性，重用性等，一個複雜查詢變得不那麼重要了。

切分查詢

每個sql的功能完全一樣，但是只是完成一小部分。
最經典的使用方式是分頁，也就是使用limit關鍵字， 可以分頁查或者是分頁刪除。
特別是刪除，因為會佔用交易記錄和鎖，因此更有必要使用分頁。我們可以用下面的虛擬碼來表示分頁刪除：

rows_affected = 0;do{    rows_affected = do_query(DELETE FROM test LIMIT 10000)} while rows_affected > 0;

分解關聯查詢

把join分解為多個查詢，比如：

select * from Student s JOIN grade g on s.gradeId = g.id where s.name="張三";// 可以改寫為：SELECT * FROM Student s where s.name="張三";SELECT * FROM Grade g where g.id in (#上面查出的結果#)；

這樣看起來一模一樣，並且還會增加串連次數。但是卻能帶來如下的好處：

• 讓緩衝效率更高。 如果第一個查詢已經執行過，那麼就可以跳過。
• 減少鎖競爭
• 在應用程式層做關聯，可以對資料庫進行拆分，獲得更好的擴充性
• 使用IN（）代替關聯查詢，本身會比關聯查詢更高效
• 在應用程式層可以重用第一次的查詢結果，比如做緩衝。

查詢執行基礎

• 用戶端發送請求給伺服器
• 伺服器先查詢快取，如果命中直接返回
• 如果沒命中進行解析及預先處理，再由最佳化器產生執行計畫
• 根據執行計畫調用API來執行查詢
• 將結果返回給用戶端

Mysql 用戶端與伺服器端的通訊

半雙工的通訊方式，決定了不能限制流量，發出請求後只能等待結果。
下面的參數能夠設定接收包的大小，太小了，可能導致請求失敗

show VARIABLES like '%max_allowed_packet%';//my.cnfmax_allowed_packet = 20M// 命令set global max_allowed_packet = 2*1024*1024*10

通常使用mysql的用戶端包，都是從mysql伺服器中擷取了sql中返回的所有資料，並且緩衝，之後操作的都是緩衝中的資料。 這樣有個問題是如果結果集過大有可能記憶體溢出。
JDBC可以用如下的辦法來不使用這種返回的方式：

    stmt = (com.mysql.jdbc.Statement) con.createStatement();    stmt.setFetchSize(1);    // 開啟流方式返回機制    stmt.enableStreamingResults();    // 類似利用mysql機制的方法還有：setLocalInfileInputStream ，可以跟LOAD DATA LOCAL INFILE一起快速插入

串連狀態

SHOW FULL PROCESSLIST;

+———+——+———————-+——————–+———+——+——-+———————–+
| Id | User | Host | db | Command | Time | State | Info |
+———+——+———————-+——————–+———+——+——-+———————–+
| 1897957 | root | 192.168.147.34:60520 | biz | Sleep | 172 | | NULL |
通過State能夠看到連接線程的狀態。
Sleep是線程等待用戶端發送新請求
Query是正在查詢，等等。

查詢快取

是通過大小寫敏感的 Hash尋找來實現的，因此只要sql改動了一點就不能命中了

詞法分析與預先處理

分析稱解析樹， 其中如果存在語法錯誤則直接返回

查詢最佳化工具

產生執行計畫。
每個sql語句都可能有多種執行計畫，mysql使用預判的方式來估算最小成本的計劃。下面的語句可以看一下mysql的估算結果：

SELECT SQL_NO_CACHE COUNT(*) FROM biz_pay_task;SHOW STATUS LIKE 'Last_query_cost';

返回的是mysql認為的要做多少個頁的隨機尋找才能完成任務。

mysql通常有如下的最佳化方式：

• 重定義關聯表順序
• 等價變換規則： 移除恒等,合并比較等等，比如 1=1 AND a > 5會轉化為 a > 5
• 最佳化COUNT(), MIN(), MAX()
  MIN(),MAX()分別對應B-Tree的索引最前與最後，基本相當於常量引用的效率了

EXPLAIN select MAX(jd_order_id) FROM biz_pay_task;// 結果： Extra ： Select tables optimized away

表示啟用了此項最佳化。
COUNT(),需要儲存引擎支援，比如有的儲存引擎可以直接返回這個變數，不用去數

• 預估並轉化為常量運算式

// 查出一個訂單的擴充欄位之：第三方訂單號。EXPLAIN SELECT b.id, o.third_order_id FROM biz_pay_task b INNER JOIN order_snap o ON o.virtual_order_id = b.id WHERE b.id = 1;

結果：
+—-+————-+——-+——-+—————+———+———+——-+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+—————+———+———+——-+——+————-+
| 1 | SIMPLE | b | const | PRIMARY | PRIMARY | 8 | const | 1 | Using index |
| 1 | SIMPLE | o | ALL | NULL | NULL | NULL | NULL | 61 | Using where |
+—-+————-+——-+——-+—————+———+———+——-+——+————-+
可以看到是轉化為兩次查詢，第一次是主鍵查詢，第二次是一個where 查詢，第二個where查詢的時候會直接使用o.virtual_order_id=1來進行替換。使用常量值。

另外上面說的MIN()的情況也應該屬於這一種

• 覆蓋索引掃描
• 提前終止查詢
  比如Limit，到指定的位置就不往下尋找了
  比如下旬一個不存在的資料，從索引上就直接返回了，不會去查資料

EXPLAIN SELECT b.id FROM biz_pay_task b WHERE b.id = -1; // Extra是：Impossible WHERE noticed after reading const tables 

比如NOT EXIST, LEFT JOIN, lift join再來個例子：
查詢很有贈品的訂單
SELECT b.order_id FROM order LEFT JOIN order_sku o ON order.order_id = o.order_id WHERE o.skuName IS NULL;
這個查詢會找到第一個skuName 為NULL之後進入下一個訂單，而不會全部掃描。

其實跟Java的return, break; continue這種文法有點像。

• IN mysql會對其內容進行排序，使用二分尋找的方式，這樣比其他的資料庫要好，其他基本上都是跟多個OR是等價的。

關聯查詢

Mysql中對於關聯查詢的操作很簡單，就是嵌套迴圈。即先遍曆左邊中合格，然後根據每一個左表合格去查右表中的內容。

包括子查詢 也是使用的這種方式。

Mysql在執行時會把sql語句轉化為執行樹，是一顆左側深度優先的數，如：

關聯查詢最佳化
主要是對於內聯的操作。因為有很多情況內聯的表的順序不重要，因此mysql可能會改變遍曆順序優先遍曆資料很少的表。
舉個栗子：

EXPLAIN SELECT v.id, o.third_order_id FROM virtual_order v INNER JOIN order_snap o  ON v.id = o.virtual_order_id;// 這個sql跟 order_snap o INNER JOIN virtual_order v的效果是一樣的。執行的時候能夠看到：

+—-+————-+——-+——–+—————+———+———+——————————–+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——–+—————+———+———+——————————–+——+————-+
| 1 | SIMPLE | o | ALL | NULL | NULL | NULL | NULL | 344 | |
| 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 8 | virtual_biz.o.virtual_order_id | 1 | Using index |
+—-+————-+——-+——–+—————+———+———+——————————–+——+————-+
能夠看到先執行的o表， 因為mysql的最佳化點在於選擇驅動表，其會簡單的選擇資料少的為驅動表。

但是如果是很多表關聯的情況下， 因為組合會很多所以有可能會轉為其他的方式進行選擇。通常不建議關聯很多表

此外我們可以指定串連的順序，選擇驅動表。 使用STRAIGHT JOIN 關鍵字。 這樣我們可以保證驅動表是我們想要的，比如我們要盡量使得排序行為在驅動表中，這樣就會使查詢更快。

排序最佳化

基於索引排序， 使用快排，如果記憶體不夠則先對資料分塊，然後每塊分別排序，最後merge.

此外有兩種排序演算法
當不超過max_length_for_sort_data時，使用單次傳輸，否則時兩次傳輸。
單次傳輸時新版本才有的，會載入所有的列進行排序，這樣減少I/O，增加佔用記憶體
兩次傳輸，第一次載入排序列，排序，排好後再去拿其他資料。這樣減少佔用空間，但是會增加很多隨機I/O。

當關聯查詢需要排序時，如果在驅動表上，則會先排序。
不在則會先計算關連接果，然後放到暫存資料表中，再進行排序。

查詢執行引擎

產生的執行計畫是一個資料結構。
執行過程會通過api調用很多次儲存引擎。

返回結果給用戶端

返回結果集時會判斷能否緩衝，如果可以會先緩衝。
結果是增量返回的，因此在API端調用的時候可以設定，是否增量接收。

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。