这篇文章主要讲解了“mysql left join快速转inner join的详细过程”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“mysql left join快速转inner join的详细过程”吧！
在日常优化过程中，发现一个怪事情，同一个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的详细过程这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是编程网，小编将为大家推送更多相关知识点的文章，欢迎关注！
