目录


  • 一、驱动表的介绍与选择
  • 1.mysql的优化器如何选择驱动表？
  • 二、join优化实例（重点）
  • 1.女性客户数量与平均薪资 & 不同城市客户数量与平均薪资
  • 2.列出没有手机号码，或者没有照片，或者没有年终奖的客户姓名
  • 3.join优化思路：
  • 4.索引icp的解释
  • 三、where过滤运行过程（理解即可）



一、驱动表的介绍与选择

1.mysql的优化器如何选择驱动表？

联合查询的类型：
left join – 左边是驱动表，右边的作为被驱动表
right join – 右边是驱动表，左边的作为被驱动表
inner join – 查询的结果集数据量少，消耗少的表作为驱动表

驱动表的选择本质：会根据索引以及where条件过滤数据之后选择结果集更小的表作为驱动表



二、join优化实例（重点）

1.女性客户数量与平均薪资 & 不同城市客户数量与平均薪资

覆盖索引 优化

desc customers1;  
desc salary;

show index from customers1;
show index from salary;

select count(*),avg(salary.monthsalary) from customers1 inner join salary on customers1.id = salary.id where customers1.gender = 0;
select count(*),avg(salary.monthsalary) from customers1 inner join salary on customers1.id = salary.id group by customers1.city;

子查询优化 版本5.7以上才适用（会把子查询转换为 join），5.6的版本建议大家适用上面的覆盖索引

select count(*),avg(salary.monthsalary) from salary where id in
	(
		select id from customers1 where gender = 0
	)
	
	select count(*),avg(salary.monthsalary) from salary where id in
	(
		select id from customers1 where group by
	)

2.列出没有手机号码，或者没有照片，或者没有年终奖的客户姓名



explain select `name` from customers1,salary where customers1.id=salary.id and (mobile = '0' or photo is null or salary.yearbonus = 0);
	
	select `name` from customers1 where mobile = '0' or photo = '0'
	union all 
	select `name` from customers1 where id in(
		select id from salary where salary.yearbonus=0
	)
	
	alter table customers1 add index idx_mobile_photo_name(mobile,photo,`name`)
	
	alter table salary add index idx_yearbonus(yearbonus);

3.join优化思路：

1.尽可能的减少join语句中的nested loop的循环次数，也就是驱动表的结果集尽可能的要小-》以小的结果集驱动大的 四两拨千斤
2.优先优化nested loop的内层循环
保证被驱动表上的join 条件字段以及被索引的目的 3.join语句的关联字段确定是索引字段
4.当无法保证驱动表join条件字段被索引并且内存资源充足，join BUFFER
all index range

4.索引icp的解释

icp：是mysql利用索引和筛选字段在where中从表中提取数据记录的一种优化

开启icp 可以通过where查询过滤掉很多不与索引匹配或者不与where匹配的数据

开启icp语句

set @@optimizer_switch="index_condition_pushdown=on";
set profiling=1;

三、where过滤运行过程（理解即可）

mysql 多表关联 强制使用索引 mysql 多表关联优化_字段