MYSQL中exists語句執(zhí)行效率變低
在ORACLE 中,我們常常推薦使用exists 來替代in,往往也能取得比較好的優(yōu)化效果。在ORACLE應(yīng)用遷往MySQL的過程中，我們發(fā)現(xiàn)部分in 的子查詢語句帶到MYSQL中，其執(zhí)行效率變得非常低下，這很讓人覺得匪夷所思。于是，我分析了一波。
成都創(chuàng)新互聯(lián)，為您提供網(wǎng)站建設(shè)、成都網(wǎng)站制作、網(wǎng)站營銷推廣、網(wǎng)站開發(fā)設(shè)計，對服務(wù)戶外休閑椅等多個行業(yè)擁有豐富的網(wǎng)站建設(shè)及推廣經(jīng)驗。成都創(chuàng)新互聯(lián)網(wǎng)站建設(shè)公司成立于2013年,提供專業(yè)網(wǎng)站制作報價服務(wù),我們深知市場的競爭激烈，認真對待每位客戶，為客戶提供賞心悅目的作品。 與客戶共同發(fā)展進步，是我們永遠的責(zé)任！
對兩個表，分別是一大一小進行關(guān)聯(lián)查詢:
mysql> select count(*) from users; +----------+ | count(*) | +----------+ | 19 | +----------+ 1 row in set mysql> select count(*) from orders; +----------+ | count(*) | +----------+ | 86310 | +----------+ 1 row in set mysql>
開啟profile，發(fā)現(xiàn)無論是子查詢是大表還是小表 ，exists的語句總是比in執(zhí)行慢：
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內(nèi)容，發(fā)現(xiàn)其存在多個executing 和sending data過程，這是整個sql執(zhí)行的主要耗時過程：
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執(zhí)行主要耗時地方：
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 | +--------------------------------+----------+
關(guān)于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語句讀取和處理行，并且發(fā)送數(shù)據(jù)到客戶端。因為這期間操作傾向于大量的磁盤訪問（讀取），所以這常是整個查詢周期中運行時間最長的階段。（這是MySQL 5.5的解釋，5.7的解釋完全一樣，但是5.7多了一個Sending to client狀態(tài)）
這樣就清楚了，Sending data 做了 讀取，處理（過濾，排序等。。）和發(fā)送 三件事情，接下來再看該狀態(tài)下的cpu 和 io 信息 以分析語句的瓶頸是 讀取還是處理 ，再做相應(yīng)的優(yōu)化調(diào)整。。
executing
The thread has begun executing a statement.
在exists語句中不斷地executing和Sending data 應(yīng)該是在不斷地掃描抓取數(shù)據(jù)進行匹配，那這應(yīng)該與MYSQL 的算法有關(guān)，通過trace查看其實是在多次子查詢 join_execution：
{
"subselect_execution": {
"select#": 2,
"steps": [
{
"join_execution": {
"select#": 2,
"steps": [
] /* steps */
} /* join_execution */
}
] /* steps */
} /* subselect_execution */
},
想知道這是雞肋嗎，不知有沒有相關(guān)的解析？
分享題目：MYSQL中exists語句執(zhí)行效率變低
鏈接地址：http://www.rwnh.cn/article6/igidig.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供自適應(yīng)網(wǎng)站、外貿(mào)網(wǎng)站建設(shè)、網(wǎng)站內(nèi)鏈、手機網(wǎng)站建設(shè)、網(wǎng)站設(shè)計、微信公眾號
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)
- 公司域名注冊解決方案 2021-08-18
- 域名注冊需要注意的問題？ 2022-05-25
- 域名注冊有什么限制條件嗎？ 2022-06-18
- 域名注冊服務(wù)商哪家強？ 2022-07-16
- 淺析域名注冊時間與網(wǎng)站排名？ 2016-10-28
- 域名注冊證書怎么弄？有什么用 2021-03-01
- 注冊域名應(yīng)該選擇企業(yè)還是個人？ 2016-08-19
- 注冊域名需要注意什么 2016-09-15
- 企業(yè)網(wǎng)站建設(shè)中的域名注冊解析及服務(wù)器配置 2021-02-21
- 域名注冊交易平臺哪家好 2022-08-03
- 創(chuàng)新互聯(lián)的域名注冊服務(wù) 2014-02-19
- 企業(yè)網(wǎng)站建設(shè)沒有域名 選擇域名注冊時多考慮這些小心機 2022-05-21