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

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

驗證結論：

創建表：

--班級表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 只有被關聯表有where條件,且其過濾條件優于關聯表的情況下，mysql優化器才轉成inner join.

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