MYSQL 中 exists 語句執行效率變低

darren__chan發表於2019-09-11

        在ORACLE 中,我們常常推薦使用exists 來替代in,往往也能取得比較好的最佳化效果。在ORACLE應用遷往MYSQL的過程中,我們發現部分in 的子查詢語句帶到MYSQL中,其執行效率變得非常低下,這很讓人覺得匪夷所思。於是,我分析了一波。

    對兩個表,分別是一大一小進行關聯查詢:

mysql> select count(*) from users;
+----------+
| count(*) |
+----------+
|       19 |
+----------+
1 row in set
mysql> select count(*) from orders;
+----------+
| count(*) |
+----------+
|    86310 |
+----------+
1 row in set
mysql>

開啟profile,發現無論是子查詢是大表還是小表 ,exists的語句總是比in執行慢:

mysql> show profiles;
+----------+------------+--------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                      |
+----------+------------+--------------------------------------------------------------------------------------------+
|        1 | 1.08661625 | select count(1) from orders o where o.user_id in(select u.id from users u)                 |
|        2 | 1.56956275 | select count(1) from orders o where exists (select 1 from users u where u.id = o.user_id)  |
|        3 | 0.81266425 | select count(1) from users u where u.id in(select o.user_id from orders o)                 |
|        4 |  8.4164905 | select count(1) from users u where exists (select 1 from  orders o where u.id = o.user_id) |
+----------+------------+--------------------------------------------------------------------------------------------+
4 rows in set


而檢視exists語句的profile內容,發現其存在多個executing 和sending data過程,這是整個sql執行的主要耗時過程:

mysql> show profile for query 2
;
+----------------------------+----------+
| Status                     | Duration |
+----------------------------+----------+
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.5E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 1E-6     |
| Sending data               | 1.3E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.7E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.1E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 1E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.1E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.2E-5   |
| executing                  | 2E-6     |
| Sending data               | 1.5E-5   |
| end                        | 3E-6     |
| query end                  | 3E-6     |
| waiting for handler commit | 1E-5     |
| closing tables             | 9E-6     |
| freeing items              | 0.000152 |
| cleaning up                | 1.7E-5   |
+----------------------------+----------+
100 rows in set


而在in 子查詢中,sending data這個過程只有一次,這也是整個sql執行主要耗時地方:

mysql> show profile for query 1;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 9.3E-5   |
| Executing hook on transaction  | 6E-6     |
| starting                       | 8E-6     |
| checking permissions           | 5E-6     |
| checking permissions           | 4E-6     |
| Opening tables                 | 0.004849 |
| init                           | 1.8E-5   |
| System lock                    | 1.4E-5   |
| optimizing                     | 1.4E-5   |
| statistics                     | 3.1E-5   |
| preparing                      | 2.2E-5   |
| executing                      | 3E-6     |
| Sending data                   | 1.081273 | 《《《《《《《《《《《《《《《《《《《
| end                            | 1.3E-5   |
| query end                      | 3E-6     |
| waiting for handler commit     | 1E-5     |
| closing tables                 | 5.2E-5   |
| freeing items                  | 0.000171 |
| cleaning up                    | 2.9E-5   |
+--------------------------------+----------+


關於sending data和executing 解析:

Sending data


The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query.


 說Sending data 是:執行緒正在為一個select語句讀取和處理行,並且傳送資料到客戶端。因為這期間操作傾向於大量的磁碟訪問(讀取),所以這常是整個查詢週期中執行時間最長的階段。(這是MySQL 5.5的解釋,5.7的解釋完全一樣,但是5.7多了一個Sending to client狀態)


這樣就清楚了,Sending data 做了 讀取,處理(過濾,排序等。。)和傳送 三件事情,接下來再看該狀態下的cpu 和 io 資訊 以分析語句的瓶頸是 讀取還是處理 ,再做相應的最佳化調整。。



executing


The thread has begun executing a statement.


在exists語句中不斷地executing和Sending data 應該是在不斷地掃描抓取資料進行匹配,那這應該與MYSQL 的演算法有關,透過trace檢視其實是在多次子查詢 join_execution:

  {
            "subselect_execution": {
              "select#": 2,
              "steps": [
                {
                  "join_execution": {
                    "select#": 2,
                    "steps": [
                    ] /* steps */
                  } /* join_execution */
                }
              ] /* steps */
            } /* subselect_execution */
          },


想知道這是雞肋嗎,不知有沒有相關的解析?

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29863023/viewspace-2656538/,如需轉載,請註明出處,否則將追究法律責任。

相關文章