建站
咨詢
售后
建站咨詢
新聞中心
當(dāng)你想對(duì)兩個(gè)表進(jìn)行差分運(yùn)算時(shí)，你有兩種選擇：使用NOT EXISTS 的子查詢或者NOT IN 。后者可以說更易于編寫，可以使查詢方法更加明顯。現(xiàn)代數(shù)據(jù)庫系統(tǒng)可以優(yōu)化兩種執(zhí)行計(jì)劃從而查詢到類似的結(jié)果，可以在外部和內(nèi)部處理查詢的相關(guān)性(我說“現(xiàn)代”，因?yàn)樵谏鲜兰o(jì)90年代中期我已經(jīng)吸取教訓(xùn)，當(dāng)時(shí)我正在使用Oracle 7.3，它沒有這個(gè)功能)。
創(chuàng)新互聯(lián)建站是一家集網(wǎng)站建設(shè),大廠企業(yè)網(wǎng)站建設(shè),大廠品牌網(wǎng)站建設(shè),網(wǎng)站定制,大廠網(wǎng)站建設(shè)報(bào)價(jià),網(wǎng)絡(luò)營銷,網(wǎng)絡(luò)優(yōu)化,大廠網(wǎng)站推廣為一體的創(chuàng)新建站企業(yè)，幫助傳統(tǒng)企業(yè)提升企業(yè)形象加強(qiáng)企業(yè)競爭力?？沙浞譂M足這一群體相比中小企業(yè)更為豐富、高端、多元的互聯(lián)網(wǎng)需求。同時(shí)我們時(shí)刻保持專業(yè)、時(shí)尚、前沿，時(shí)刻以成就客戶成長自我，堅(jiān)持不斷學(xué)習(xí)、思考、沉淀、凈化自己，讓我們?yōu)楦嗟钠髽I(yè)打造出實(shí)用型網(wǎng)站。
創(chuàng)新互聯(lián)建站是專業(yè)的萬寧網(wǎng)站建設(shè)公司，萬寧接單;提供成都做網(wǎng)站、網(wǎng)站建設(shè),網(wǎng)頁設(shè)計(jì),網(wǎng)站設(shè)計(jì),建網(wǎng)站,PHP網(wǎng)站建設(shè)等專業(yè)做網(wǎng)站服務(wù);采用PHP框架,可快速的進(jìn)行萬寧網(wǎng)站開發(fā)網(wǎng)頁制作和功能擴(kuò)展;專業(yè)做搜索引擎喜愛的網(wǎng)站,專業(yè)的做網(wǎng)站團(tuán)隊(duì),希望更多企業(yè)前來合作!
兩種結(jié)構(gòu)有一個(gè)很大的不同：如果子查詢返回的結(jié)果為NULL，那么 NOT IN 的條件將不執(zhí)行，因?yàn)?NULL不等于它或不等于其它值。但是如果你注意到這一點(diǎn)，它們是等價(jià)的。事實(shí)上，這些消息告訴我們，NOT IN 查詢更快，人們更喜歡用它查詢。
這篇文章是關(guān)于一個(gè)數(shù)據(jù)庫顯著變慢的情況，而空值正是罪魁禍?zhǔn)住?/p>
考慮以下兩個(gè)可能是用來追蹤點(diǎn)擊流數(shù)據(jù)的表。由于我們跟蹤匿名和注冊(cè)用戶, EVENTS.USER_ID是可空的。然而，當(dāng)用戶不空，二級(jí)指標(biāo)標(biāo)就會(huì)具有較高的基數(shù)。
- create table USERS
- (
- ID integer auto_increment primary key,
- ...
- )
- create table EVENTS
- (
- ID integer auto_increment primary key,
- TYPE smallint not null,
- USER_ID integer
- ...
- )
- create index EVENTS_USER_IDX on EVENTS(USER_ID);
好的，現(xiàn)在讓我們使用這些表：從一小部分用戶開始，我們想找到那些沒有特定事件的用戶。 使用NOT IN子句，并確保null值不出現(xiàn)在內(nèi)部結(jié)果中，查詢?nèi)缦滤荆?/p>
- select ID
- from USERS
- where ID in (1, 7, 2431, 87142, 32768)
- and ID not in
- (
- select USER_ID
- from EVENTS
- where TYPE = 7
- and USER_ID is not null
- );
對(duì)于我的測(cè)試數(shù)據(jù)集，USERS表有100,000行，EVENTS表有10,000,000行，并且EVENTS表中大約75%的USER_ID為空。 我在我的筆記本電腦上運(yùn)行這條查詢，它有一個(gè)Core i7處理器，12 GB的RAM和一個(gè)SSD。
我一直運(yùn)行了約2分鐘，這真是...哇。
讓我們用NOT EXISTS和相關(guān)的子句替換NOT IN：
- select ID
- from USERS
- where ID in (1, 7, 2431, 87142, 32768)
- and not exists
- (
- select 1
- from EVENTS
- where USER_ID = USERS.ID
- and TYPE = 7
- );
這個(gè)版本運(yùn)行在0.01秒，這比我預(yù)期的時(shí)間更短。
是時(shí)候比較一下執(zhí)行計(jì)劃了。 ***個(gè)計(jì)劃來自NOT IN查詢，第二個(gè)來自NOT EXISTS。
- +----+--------------------+--------+------------+----------------+-----------------+-----------------+---------+------+------+----------+--------------------------+
- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
- +----+--------------------+--------+------------+----------------+-----------------+-----------------+---------+------+------+----------+--------------------------+
- | 1 | PRIMARY | USERS | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 5 | 100.00 | Using where; Using index |
- | 2 | DEPENDENT SUBQUERY | EVENTS | NULL | index_subquery | EVENTS_USER_IDX | EVENTS_USER_IDX | 5 | func | 195 | 10.00 | Using where |
- +----+--------------------+--------+------------+----------------+-----------------+-----------------+---------+------+------+----------+--------------------------+
執(zhí)行計(jì)劃幾乎相同：都是從USERS表中選擇行，然后使用嵌套循環(huán)連接(“DEPENDENT SUBQUERY”)從EVENTS表中檢索行。都聲稱使用EVENTS_USER_IDX在子查詢中選擇行。并且他們?cè)诿恳徊蕉脊烙?jì)了相似的行數(shù)。
但更仔細(xì)地查看連接類型。 NOT IN版本使用 index_subquery，而NOT EXISTS版本使用 ref。再查看ref列：NOT EXISTS版本使用了對(duì)其它列的顯式引用，而NOT IN使用了一個(gè)函數(shù)。這里發(fā)生了什么?
index_subquery連接類型表示MySQL將掃描索引以查找子查詢的相關(guān)行?？赡苁沁@個(gè)問題嗎?我不這么認(rèn)為，因?yàn)镋VENTS_USER_IDX索引是“narrow”類型：它只有一列，所以引擎不應(yīng)該讀取大量的塊來查找對(duì)應(yīng)的外部查詢的ID行(的確，我嘗試了各種查詢來測(cè)試這個(gè)索引，并且所有的運(yùn)行都在幾百分之一秒內(nèi))。
為了獲取更多信息，我轉(zhuǎn)向使用“extended”執(zhí)行計(jì)劃。 要查看此計(jì)劃，請(qǐng)使用explain extended作為查詢前綴，并接著使用 show warnings得到被MySQL優(yōu)化器優(yōu)化后的查詢語句。 這是從NOT IN查詢得到的(為了清晰重新格式化了)：
- /* select#1 */ select `example`.`USERS`.`ID` AS `ID`
- from `example`.`USERS`
- where ((`example`.`USERS`.`ID` in (1,7,2431,87142,32768))
- and (not(
- (`example`.`USERS`.`ID`,
- (
- (
- (`example`.`USERS`.`ID`) in EVENTS on EVENTS_USER_IDX checking NULL where ((`example`.`EVENTS`.`TYPE` = 7) and (`example`.`EVENTS`.`USER_ID` is not null)) having
- (`example`.`EVENTS`.`USER_ID`)))))))
我找不到“on EVENTS_USER_IDX checking NULL”的解釋，但我認(rèn)為發(fā)生的是：優(yōu)化器認(rèn)為它正在執(zhí)行一個(gè)IN查詢，可以在結(jié)果中包含NULL; 在做出此決定時(shí)，它不考慮where子句中的空檢查。 因此，它將檢查(examine)USER_ID為null的750萬行，以及與外部查詢的值匹配的幾十行。 通過“檢查(examine)”，我的意思是它將讀取表行，然后應(yīng)用不為null條件。 此外，基于運(yùn)行查詢所花費(fèi)的時(shí)間，我認(rèn)為它為外部查詢中的每個(gè)候選值執(zhí)行了此操作。
所以，本文的論點(diǎn)是：每當(dāng)你想在可為空的列上使用IN或NOT IN子查詢時(shí)，請(qǐng)重新思考并使用EXISTS或NOT EXISTS代替。
網(wǎng)站題目：MySQL：什么時(shí)候NOTIN不等于NOTEXISTS
地址分享：http://www.dlmjj.cn/article/cogcods.html
