在日常優(yōu)化過程中，發(fā)現一個怪事情，同一個SQL出現兩個完全不一樣執(zhí)行計劃，left join 連驅動表都可以變成不一樣。

對于left join，如果where條件里有被關聯(lián)表過濾，left join有可能被轉成inner join ,本案例中shopInfo有ShopCategory = ’LOC’過濾條件； 保證shopInfo的記錄非NULL，因此left join在優(yōu)化過程中可以轉為inner join。 那么O和S的JOIN順序就是可以交換的。

驗證結論：

創(chuàng)建表：

--班級表CREATE TABLE T_CLASS( class_id int not null, class_name VARCHAR2(100));添加索引alter table T_CLASS add index inx_class_id(class_id);

--學生表CREATE TABLE T_STUDENT( student_id int not null, class_id int not null, student_name VARCHAR(100), age int, sex int )添加索引alter table T_STUDENT add index index_age(AGE);

--班級數據insert into T_CLASS (CLASS_ID, CLASS_NAME)values (1, ’一班’);insert into T_CLASS (CLASS_ID, CLASS_NAME)values (2, ’二班’);insert into T_CLASS (CLASS_ID, CLASS_NAME)values (3, ’三班’);insert into T_CLASS (CLASS_ID, CLASS_NAME)values (4, ’四班’);insert into T_CLASS (CLASS_ID, CLASS_NAME)values (5, ’五班’);

--學生數據insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)values (1, 1, ’李1’, 3, ’1’);insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)values (2, 1, ’李2’, 2, ’1’);insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)values (3, 1, ’李3’, 3, ’1’);insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)values (4, 2, ’李4’, 4, ’1’);insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)values (5, 2, ’李5’, 3, ’2’);insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)values (6, 2, ’李6’, 3, ’1’);insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)values (7, 3, ’李7’, 6, ’2’);insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)values (8, 3, ’李8’, 4, ’2’);insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)values (9, 2, ’李9’, 2, ’2’);insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)values (10, 2, ’李10’, 3, ’1’);insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)values (11, 3, ’李11’, 3, ’2’);insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)values (12, 2, ’李12’, 8, ’2’);insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)values (13, 1, ’李13’, 6, ’2’);

案例1：B表有where條件且不為null

案例2： A表和B表均有where條件且不為null

案例3：A表和B表均有where條件且不為null,刪除B表索引

結論：

left join 只有被關聯(lián)表有where條件,且其過濾條件優(yōu)于關聯(lián)表的情況下，mysql優(yōu)化器才轉成inner join.

到此這篇關于mysql left join快速轉inner join的過程的文章就介紹到這了,更多相關mysql left join inner join內容請搜索好吧啦網以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持好吧啦網！