Mysql高级优化知识笔记--查询优化

1.为什么优化

• 性能下降SQL慢
• 执行时间长
• 等待时间长

查询数据过多【要尽量拆，条件过滤尽量少】

关联了太多的表，太多join：

join 原理。用  A 表的每一条数据 扫描 B表的所有数据。所以尽量先过滤。

没有利用到索引：
	提示：索引针对 列 建索引。但并不可能每一列都建索引
	索引并非越多越好。当数据更新了，索引会进行调整。也会很消耗性能。
	且 mysql 并不会把所有索引都用上，只会根据其算法挑一个索引用。所以建的准很重要。

服务器调优及各个参数设置（缓冲、线程数等）(DBA的工作)

2.查询优化

• 使用索引

https://mp.csdn.net/mdeditor/99209173

• 单表查询优化

 #查询 category_id 为1 且  comments 大于 1 的情况下,views 最多的 article_id。 
 EXPLAIN SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
 #结论：很显然,type 是 ALL,即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须的。
 
 
#开始优化：
# 1.1 新建索引+删除索引
#ALTER TABLE `article` ADD INDEX idx_article_ccv ( `category_id` , `comments`, `views` );
create index idx_article_ccv on article(category_id,comments,views);
DROP INDEX idx_article_ccv ON article
# 1.2 第2次EXPLAIN
EXPLAIN SELECT id,author_id FROM `article` WHERE category_id = 1 AND comments >1 ORDER BY views DESC LIMIT 1;
#结论：
#type 变成了 range,这是可以忍受的。但是 extra 里使用 Using filesort 仍是无法接受的。
#但是我们已经建立了索引,为啥没用呢?
#这是因为按照 BTree 索引的工作原理,
# 先排序 category_id,
# 如果遇到相同的 category_id 则再排序 comments,如果遇到相同的 comments 则再排序 views。
#当 comments 字段在联合索引里处于中间位置时,
#因comments > 1 条件是一个范围值(所谓 range),
#MySQL 无法利用索引再对后面的 views 部分进行检索,即 range 类型查询字段后面的索引无效。
 
 # 1.3 删除第一次建立的索引
DROP INDEX idx_article_ccv ON article;
 # 1.4 第2次新建索引
#ALTER TABLE `article` ADD INDEX idx_article_cv ( `category_id` , `views` ) ;
create index idx_article_cv on article(category_id,views);
 
# 1.5 第3次EXPLAIN
EXPLAIN SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
#结论：可以看到,type 变为了 ref,Extra 中的 Using filesort 也消失了,结果非常理想。
DROP INDEX idx_article_cv ON article;

• 关联查询优化

# 下面开始explain分析
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
#结论：type 有All
 
# 添加索引优化
ALTER TABLE `book` ADD INDEX Y ( `card`);
 
# 第2次explain
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
#可以看到第二行的 type 变为了 ref,rows 也变成了优化比较明显。
#这是由左连接特性决定的。LEFT JOIN 条件用于确定如何从右表搜索行,左边一定都有,
#所以右边是我们的关键点,一定需要建立索引。
 
# 删除旧索引 + 新建 + 第3次explain
DROP INDEX Y ON book;
ALTER TABLE class ADD INDEX X (card);
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

建议：
1、保证被驱动表的join字段已经被索引
2、left join 时，选择小表作为驱动表，大表作为被驱动表。
3、inner join 时，mysql会自己帮你把小结果集的表选为驱动表。
   因为 驱动表无论如何都会被全表扫描？。所以扫描次数越少越好
4、子查询尽量不要放在被驱动表，有可能使用不到索引。

• 子查询优化

用in 还是 exists？？
 
有索引 大表驱动小表
select sql_no_cache sum(sal) from emp where deptno in (select deptno from dept);
select sql_no_cache sum(sal) from emp where exists (select 1 from dept where emp.deptno=dept.deptno); 
 ##用 exists 是否存在，存在返回一条记录，exists 是作为一个查询判断用，所以 select 后返回什么不重要。
select sql_no_cache sum(sal) from emp inner  join dept on  emp.deptno=dept.deptno;

有索引 小表驱动大表
select sql_no_cache sum(e.sal) from (select * from emp where id<10000) e  where  exists (select 1 from  emp where e.deptno=emp.deptno);
select sql_no_cache sum(e.sal) from (select * from emp where id<10000) e inner join (select distinct deptno from  emp) m on m.deptno=e.deptno;
 select sql_no_cache sum(sal) from emp where deptno in (select deptno from dept);
 
 **有索引小驱动大表 性能优于 大表驱动小表**
 
无索引 小表驱动大表
select sql_no_cache sum(e.sal) from (select * from emp where id<10000) e  where  exists (select 1 from  emp where e.deptno=emp.deptno);
select sql_no_cache sum(e.sal) from (select * from emp where id<10000) e inner join (select distinct deptno from  emp) m on m.deptno=e.deptno;
 select sql_no_cache sum(sal) from emp where deptno in (select deptno from dept);

无索引大表驱动小表
select sql_no_cache sum(sal) from emp where deptno in (select deptno from dept);
select sql_no_cache sum(sal) from emp where exists (select 1 from dept where emp.deptno=dept.deptno);
select sql_no_cache sum(sal) from emp inner  join dept on  emp.deptno=dept.deptno;
 
结论：
有索引的情况下 用  inner join 是最好的  其次是 in  ，exists最糟糕
 
无索引的情况下用 
小表驱动大表 因为join 方式需要distinct ，没有索引distinct消耗性能较大 
所以  exists性能最佳 in其次  join性能最差？
 
无索引的情况下大表驱动小表
in 和 exists 的性能应该是接近的  都比较糟糕  exists稍微好一点 超不过5%     
但是inner join 优于使用了 join buffer 所以快很多
如果left join 则最慢

 

• order by关键字优化

（1）ORDER BY子句，尽量使用Index方式排序,避免使用FileSort方式排序
	MySQL支持二种方式的排序，FileSort和Index，Index效率高.
	它指MySQL扫描索引本身完成排序。FileSort方式效率较低。
	
	ORDER BY满足两情况，会使用Index方式排序:
		①.ORDER BY 语句使用索引最左前列
		②.使用Where子句与Order BY子句条件列组合满足索引最左前列
		③.where子句中如果出现索引的范围查询(即explain中出现range)会导致order by 索引失效。
（2）尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀

• 分页查询的优化—limit

  EXPLAIN    SELECT  SQL_NO_CACHE * FROM emp  ORDER  BY  deptno   LIMIT 10000,40
 
 
优化：  先利用覆盖索引把要取的数据行的主键取到，然后再用这个主键列与数据表做关联：(查询的数据量小了后)
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

 实践证明： ①、order by 后的字段（XXX）有索引 ②、sql 中有 limit 时，
    当 select id 或 XXX字段索引包含字段时 ，显示 using index
    当 select 后的字段含有 order by 字段索引不包含的字段时，将显示 using filesort
 

• 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 能够利用到索引