MySQL调优(四)查询优化

使用索引

• 全值匹配我最爱
  

• 最佳左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
and忽略左右关系，即使没有按顺序，由于优化器的存在，会自动优化，经过试验结论，建立了 idx_nameAge 索引id为 主键

1.当使用覆盖索引的方式时,即使不是以name开头，也会使用idx_nameAge索引。
即select后的字段有索引，where后的字段也有索引，则无关执行顺序。
2.除开上述条件，才满足最左前缀法则。

3.不在索引列上做任何操作(计算，函数、(自动or手动)类型转换)，会导致索引失效而转向全表扫描。

tips： varchar 加单引号也是因为这个原因

4.存储引擎不能使用索引中范围条件右边的列

范围 若有索引则能使用到索引，范围条件右边的索引会失效(范围条件右边与范围条件使用的同一个组合索引，右边的才会失效。若是不同索引则不会失效)

5.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select*

6.mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全盘扫描

使用 != 和 <> 的字段索引失效( != 针对数值类型。 <> 针对字符类型 前提 where and
后的字段在混合索引中的位置比比当前字段靠后 where age != 10 and name=‘xxx’
,这种情况下，mysql自动优化，将 name=‘xxx’ 放在 age ！=10 之前，name 依然能使用索引。只是 age
的索引失效)

7.is not null 也无法使用索引，但是is null是可以使用索引的

8.like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作
like ‘%abc%’ type 类型会变成 all
like ‘abc%’ type 类型为 range ，算是范围，可以使用索引

tips: 解决like '%字符串%'时索引不被使用的方法??

建立覆盖索引

9.字符串不加单引号索引失效
底层进行转换使索引失效，使用了函数造成索引失效

10.少用 or，用它来连接时会索引失效

口诀: 全值匹配我最爱，最左前缀要遵守。 带头大哥不能死，中间兄弟不能断。 索引列上少计算，范围之后全失效。LIKE百分写最右，覆盖索引不写星。 不等空值还有or，索引失效要少用。 var引号不可丢。

一般性建议

• 对于单键索引，尽量选择针对当前query过滤性更好的索引
• 在选择组合索引的时候，当query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。（避免索引过滤性好的索引失效）
• 在选择 组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
• 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

单表查询优化

案例:查询 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;

优化:新建索引并适当转换顺序
create index idx_article_cv on article(category_id,views);

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 也消失了,结果非常理想。

多表查询优化

EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

影响:type为all 全部扫描

#添加索引优化：
ALTER TABLE book ADD INDEX Y ( card);

在右表添加索引的关键是左表是一定被扫描的，替右表添加索引就一定会加快查询速度
建议:

1.保证被驱动表的join字段已经被索引（需要被查询）
2.left join时，小表作为驱动表，大表作为被驱动表。

但left join时一定是左边驱动表，右边被驱动表

3.inner join时，mysql会帮你把小结果集的表选为驱动表

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还是exists？

有索引的情况下 用 inner join 是最好的 其次是 in ，exists最糟糕




有索引，小表驱动大表

无索引的情况下用
小表驱动大表 因为join 方式需要distinct ，没有索引distinct消耗性能较大
所以 exists性能最佳 in其次 join性能最差？

无索引的情况下大表驱动小表
in 和 exists 的性能应该是接近的 都比较糟糕 exists稍微好一点 超不过5% 但是inner join 优于使用了 join buffer 所以快很多
如果left join 则最慢

order by关键字优化

orderby子句，尽量使用index方式排序，避免使用FileSort方式排序

MySQL支持两种方式的排序，FileSort和Index，index效率高，它指MySQL扫描索引本身完成排序，FileSort方式效率较低

ORDER BY满足两情况，会使用Index方式排序

• ORDER BY 语句使用索引最左前列
• 使用Where子句与Order By子句条件列组合满足索引最左前列
• where子句中如果出现索引的范围查询(即explain中出现range) 会导致order by索引失效

尽可能在索引列上完成排序操作，遵照索引建的最佳做前缀

第二种中，where a = const and b > const order by b , c 不会出现 using filesort b , c 两个衔接上了
但是：where a = const and b > const order by c 将会出现 using filesort 。因为 b 用了范围索引，断了。而上一个 order by 后的b 用到了索引，所以能衔接上 c

如果不在索引列上，filesort有两种算法:
mysql就要启动双路排序和单路排序

双路排序
MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据，
读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出

多路排序需要借助 磁盘来进行排序。所以 取数据，排好了取数据。两次 io操作。比较慢 单路排序 ，将排好的数据存在内存中，省去了一次 io
操作，所以比较快，但是需要内存空间足够。

从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段

取一批数据，要对磁盘进行了两次扫描，众所周知，I\O是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。
单路排序

从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，
它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间，
因为它把每一行都保存在内存中了。

结论及引出的问题

• 单路优于双路
• 要考虑sort_buffer 对单路的影响

在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出, 所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取取sort_buffer容量大小，再排……从而多次I/O。
本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。

优化策略

• 增大sort_buffer_size参数的设置
• 增大max_length_for_sort_data参数的设置
• 去掉select后面不需要的字段
  select 后的多了，排序的时候也会带着一起，很占内存，所以去掉没有用的
• 提高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活动和低的处理器使用率.

分页查询的优化

加索引没用

解决方案:
先利用覆盖索引把要取的数据行的主键取到，然后再用这个主键列与数据表做关联：(查询的数据量小了后)

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

优化前:

优化后:

1.order by 后的字段（XXX）有索引
2.sql 中有 limit 时，
当 select id 或 XXX字段索引包含字段时 ，显示 using index
当 select 后的字段含有 bouder 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 能够利用到索引