mysql优化之多表优化实例

1.驱动表的介绍

定义：

1.指定了where查询条件时，满足查询条件且行数少的表为驱动表

2.没有指定where条件时，查询行数少的表为驱动表

3.在join连接情况下：

3.1. 当使用了left join时，左表是驱动表，右表示被驱动表。(不考虑是否使用索引)

3.2. 使用right join时刚好相反，右表为驱动表，左表是被驱动表。(不考虑是否使用索引)

3.3  使用join时，mysql会选择数据量比较少的表作为驱动表，大表作为被驱动表（不存在索引的情况下）

2.多表联查实例优化

例子：查询女性客户的数量与平均月薪 & 不同城市的客户数量与平均月薪

explain select count(*),avg(s.monthsalary) from customers c,salary s where c.gender = 0 and c.id=s.id;

expain结果显示使用了customers作为了驱动表，而第二个出现的salary作为了驱动表。（使用了explain后，第一行显示的就是表就是驱动表）

当我们明确了驱动表和被驱动表之后，后面的优化也不难了。我们只需要给各表添加相应的索引就行了（以及注意使用 join 连接表的时候不能把被驱动表放在驱动表的位置 ）

3. join查询优化思路

3.1.join的原理

       在 MySQL 中，只有一种 Join 算法，减少大名鼎鼎的 Nested Loop Join , Nested Loop Join 实际上就是通过驱动表的结果集作为循环基础数据，然后取这些循环基础数据作为过滤比较条件到下一个表中查询数据，然后合并结果。如果还有第三个参与 Join ，则通过前两个表的 Join 结果集作为循环基础数据，再一次通过循环查询条件到第三个表中查询数据，第四个 Join 、第五个 Join 都是按照前面的结果集作为循环的基础数据，再去通过循环查询得到最终的数据，以此类推

3.2 优化思路

原则1：最有效的办法只有一个，那就是让驱动表的结果集尽可能的小，这也正是优化基本原则之一 “永远用小结果集驱动大的结果集”

原则2 ：优化被驱动表的比较过滤条件字段（加索引），从而减少内层循环中的资源消耗，达到优化

原则3：其他设置，比如Join Buffer设置

4.索引下推（ICP）补充

什么是icp索引下推

ICP（Index Condition Pushdown）是 MySQL 利用索引（辅助索引）元组和筛字段在索引中的 WHERE 条件从表中提取数据记录的一种优化操作。(减少回表以及相关IO)

ICP 的思想是：存储引擎在访问索引的时候检查筛选字段在索引中的 where 条件，如果索引元组中的数据不满足推送的索引条件，那么就直接过滤掉该条数据记录,  从而不从回表查询，并且不需要返回到server层去比较数据。

例子：比如以下的SQL

select * from user where age>20 and birthday="03-01"

如果开启了索引下推优化，执行步骤如下：

1. 存储引擎根据索引查找出age>20的用户id，并使用索引中的birthday字段过滤掉不符合birthday="03-01"条件的记录，最后得到id=4；
2. 存储引擎到表格中取出id=4的1条记录，返回给服务层；
3. 服务层过滤掉不符合birthday="03-01"条件的记录，最后返回查询结果为id=4的1行记录。