深入浅出mysql笔记--18章SQL优化

一、定位执行慢的SQL语句

1、通过查询慢日志定位执行效率低的SQL语句。

2、通过show processlist 命令查看

3、通过explan 分析低效语句

type = all 全表，index 索引全扫描，range 索引范围扫描，ref 非唯一索引或者唯一索引的前缀扫描返回匹配某个单独值记录， eq_ref 扫描唯一索引，const/system 当作常量处理根据主键或者唯一索引

key: ....用到的索引

possible_keys：可能用到的索引

Extra: using where 除了利用索引来加速还需要根据索引的回表查询；using index 只匹配索引不用回表查询 术语覆盖索引；using index conditon 5.6已上版本应用了ICP把过滤操作下推到存储引擎 有些模糊理解 

二、确定问题采取优化措施

1、前缀索引在排序 order by 和group by操作的时候无法使用

2、Btree 索引 --》平衡二叉树

3、explain select * from actor where last_name like '%NI%'\G ，在前缀缀中用表达式，不会用到索引如 like ‘*NI' ，后缀会采用like ‘NI*' 

解决办法explain select * from (select actor_id from actor where last_name like '%NI%')a, actor b where a.actor_id = b.actor_id\G   通过二级索引获得满足条件的主键然后用主键去回表检索记录

4、数据隐含或者表达式不会用到索引 :隐式转换特别列是字符串有索引，where后条件中把字符常量值用引号引起来 防止进行隐式转换导致用不上索引

例如where a=1 解决办法 where a='1';

5、用or分割条件，如果or前面的列有索引，而后面的列没有索引，那么涉及到的索引都不会引用.

6、优化order by:尽量减少额外排序，通过索引直接返回有序数据。where条件与order by使用相同的索引。

7、优化嵌套查询改为join替代，因为嵌套查询要在内存中创建临表。

8、优化OR查询 ：在复合索引上用OR操作用不到索引，需要针对or每个条件单独建索引。

9、优化分页查询：现在索引上过滤或者排序，然后再通过join连接根据主键进行回表查询。例如：

select film_id ,description from film order by title limit 50,5\G;

优化后语句：select a.film_id,a.descrption from film a inner join (select film_id from film order by title limit 50,5)b on a.film_id = b.film_id\G;

先在title索引上过滤和排序，然后根据title索引上的主键在进行回表查询。

10、在统计查询中，借助中间表。 中间表可以灵活的添加索引和增加临时字段，从而达到提高统计效率和辅助统计的作用。

11、通过水平或者垂直拆分表。

12、外键列上缺少索引会带来三个问题，限制并发性、影响性能、还有可能造成死锁。所以对于绝大部分场景，我们应该尽量考虑在外键上面创建索引