MYSQL 中 exists 語句執行效率變低
在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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySql定位執行效率較低的SQL語句MySql
- in/exists和not in/not exists執行效率
- mySQL 執行語句執行順序MySql
- oracle使用profiler分析語句執行效率Oracle
- sql server中如何檢視執行效率不高的語句SQLServer
- 淺談Oracle中exists與in的執行效率問題Oracle
- MySQL語句執行分析(一)MySql
- MySQL語句執行分析(二)MySql
- PHP執行批量mysql語句PHPMySql
- MySQL exists 優化 in 效率MySql優化
- mysql執行sql語句過程MySql
- mysql的sql語句執行流程MySql
- mysql 語句的執行順序MySql
- 一條SQL語句在MySQL中如何執行的MySql
- Oracle中SQL語句執行效率的查詢與解決 (3)OracleSQL
- Oracle中SQL語句執行效率的查詢與解決 (2)OracleSQL
- Oracle中SQL語句執行效率的查詢與解決 (1)OracleSQL
- Oracle中SQL語句執行效率問題的查詢與解決OracleSQL
- 一條sql語句在mysql中是如何執行的MySql
- 一條 SQL 語句在 MySQL 中是如何執行的?MySql
- MySQL cron定時執行SQL語句MySql
- mysql sql語句執行超時設定MySql
- mysql如何跟蹤執行的sql語句MySql
- 檢視mysql正在執行的SQL語句MySql
- linux strace追蹤mysql執行語句LinuxMySql
- php連線mysql並執行sql語句PHPMySql
- 在事務中執行sql語句SQL
- 在nhibernate中執行SQL語句SQL
- 執行sql語句給外部變數賦值SQL變數賦值
- mysql查詢效率慢的SQL語句MySql
- MySQL 執行DDL語句 hang住了怎麼辦?MySql
- MySQL 查詢語句執行過程淺析MySql
- MySQL:一條更新語句是如何執行的MySql
- 讓dede執行php程式碼和mysql語句PHPMySql
- 在mysql查詢效率慢的SQL語句MySql
- 【SQL】Oracle sql語句 minus函式執行效率與join對比SQLOracle函式
- for語句執行順序
- sql語句批量執行SQL