- 关联查询优化:(永远是小表驱动大表)
1、保证被驱动表(join 后的表为被驱动表)的join字段已经被索引
2、left join 时,选择小表作为驱动表,大表作为被驱动表。但是 left join 时一定是左边是驱动表,右边是被驱动表
3、inner join 时,mysql会自己帮你把小结果集的表选为驱动表。
- 关联查询优化:
1、保证被驱动表(join 后的表为被驱动表)的join字段已经被索引
2、left join 时,选择小表作为驱动表,大表作为被驱动表。但是 left join 时一定是左边是驱动表,右边是被驱动表
3、inner join 时,mysql会自己帮你把小结果集的表选为驱动表。
4、子查询尽量不要放在被驱动表,有可能使用不到索引。
select a.name ,bc.name from t_emp a left join
(select b.id , c.name from t_dept b
inner join t_emp c on b.ceo = c.id)bc
on bc.id = a.deptid.
上段查询中用到了子查询,必然 bc 表没有索引。肯定会进行全表扫描
上段查询 可以直接使用 两个 left join 优化
select a.name , c.name from t_emp a
left outer join t_dept b on a.deptid = b.id
left outer join t_emp c on b.ceo=c.id
所有条件都可以使用到索引
若必须用到子查询,可将子查询设置为驱动表,,因为驱动表的type 肯定是 all,而子查询返回的结果表没有索引,必定也是all
- 子查询优化:用in还是exist
有索引的情况下 用 inner join 是最好的 其次是 in ,exists最糟糕
无索引的情况下用
小表驱动大表 因为join 方式需要distinct ,没有索引distinct消耗性能较大
所以 exists性能最佳 in其次 join性能最差?
无索引的情况下大表驱动小表
in 和 exists 的性能应该是接近的 都比较糟糕 exists稍微好一点 超不过5% 但是inner join 优于使用了 join buffer 所以快很多
如果left join 则最慢
- order by关键字优化:
ORDER BY子句,尽量使用Index方式排序,避免使用FileSort方式排序:
MySQL支持二种方式的排序,FileSort和Index,Index效率高.它指MySQL扫描索引本身完成排序。FileSort方式效率较低。
ORDER BY满足两情况,会使用Index方式排序:
1、ORDER BY 语句使用索引最左前列,使用Where子句与Order BY子句条件列组合满足索引最左前列
2、where子句中如果出现索引的范围查询(即explain中出现range)会导致order by 索引失效。
提高Order By的速度
1. Order by时select * 是一个大忌只Query需要的字段, 这点非常重要。在这里的影响是:
1.1 当Query的字段大小总和小于max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时,会用改进后的算法——单路排序, 否则用老算法——多路排序。
1.2 两种算法的数据都有可能超出sort_buffer的容量,超出之后,会创建tmp文件进行合并排序,导致多次I/O,但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size。
2. 尝试提高 sort_buffer_size
不管用哪种算法,提高这个参数都会提高效率,当然,要根据系统的能力去提高,因为这个参数是针对每个进程的
3. 尝试提高 max_length_for_sort_data
提高这个参数, 会增加用改进算法的概率。但是如果设的太高,数据总容量超出sort_buffer_size的概率就增大,明显症状是高的磁盘I/O活动和低的处理器使用率.
- 分页查询的优化---limit
EXPLAIN SELECT SQL_NO_CACHE * FROM emp ORDER BY deptno LIMIT 10000,40
那我们就给deptno这个字段加上索引吧。
然并卵。
优化: 先利用覆盖索引把要取的数据行的主键取到,然后再用这个主键列与数据表做关联:(查询的数据量小了后)
EXPLAIN SELECT SQL_NO_CACHE * FROM emp INNER JOIN (SELECT id FROM emp e ORDER BY deptno LIMIT 10000,40) a ON a.id=emp.id
- GROUP BY关键字优化:
group by实质是先排序后进行分组,遵照索引建的最佳左前缀
当无法使用索引列,增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置
where高于having,能写在where限定的条件就不要去having限定了。
- 去重优化:
尽量不要使用 distinct 关键字去重:优化
t_mall_sku 表
id shp_id kcdz
------ ------ --------------------
3 1 北京市昌平区
4 1 北京市昌平区
5 5 北京市昌平区
6 3 重庆
8 8 天津
例子:select kcdz form t_mall_sku where id in( 3,4,5,6,8 ) 将产生重复数据,
select distinct kcdz form t_mall_sku where id in( 3,4,5,6,8 ) 使用 distinct 关键字去重消耗性能
优化: select kcdz form t_mall_sku where id in( 3,4,5,6,8 ) group by kcdz 能够利用到索引
Mysql查询优化
最新推荐文章于 2024-08-02 13:27:36 发布