mysql + left join

一、mysql left join 的原则上有两点

    1、左表为小表
    2、右表的关联字段是索引 

      （无视以上两点，一般不要用left join~~！ ）

遇到的问题：右表（c）是大表，分别 有两个小表(a和b)

当 a left join c 的时候可以使用上c的关联字段索引，

但是b left join c 的时候无法使用上索引，全文搜索了，

两个小表的结构表面看起来一样，后面使用 show full columns from [tables]

发现不能使用索引的 b,c 表的 关联字段字符集 不一样

 

二、left join 情况下 使用 order  by 排序

mysql在多个left join的情况下使用order by排序，就算是其中一个表的主键也仍然使用file sort排序，数据量多的话就相当的慢。

优化前语句

select * from a left join b on a.id=b.a_id order a.id desc

优化后语句

select * from a left join b on a.id=b.a_id join (select id from a order by id desc) a_order on a.id = a_order.id

实际工作中100W+的几个表使用LEFT JOIN要20分钟才能得到结果，语句优化后3秒。

 

三、索引失效的部分总结

1、对单字段建了索引，where条件多字段。

例：建了以下索引：

查询语句：

select * from template t  where t.logicdb_id = 4 and t.sync_status = 1

2、建立联合索引，where条件单字段。与上面情况正好相反。

例：建了以下索引：

查询语句：

select * from template t  where t.sync_status = 4

3、对索引列运算，运算包括（+、-、*、/、！、<>、%、like'%_'（%放在前面）、or、in、exist等），导致索引失效。

4、类型错误，如字段类型为varchar，where条件用number。

例：template_id字段是varchar类型。

错误写法：select * from template t where t.template_id = 1

正确写法：select * from template t where t.template_id = '1'

5、对索引应用内部函数，这种情况下应该建立基于函数的索引。

例：select * from template t  where ROUND(t.logicdb_id) = 1

此时应该建ROUND(t.logicdb_id)为索引。

6、查询表的效率要比应用索引查询快的时候。

7、is null 索引失效；is not null Betree索引生效。导致的原因，个人认为应该是，mysql没有在null写进索引。还要看应用的数据库而定。

 

四、mysql牺牲了group by来增加left join的速度（前提是加了索引）。

        https://blog.csdn.net/zhangjq520/article/details/73836042