MySQL调优之查询优化

MySQL调优之查询优化

1.查询慢的原因

查询执行比较慢的可能原因如下：网络、CPU、IO、上下文切换、系统调用、生成统计信息、锁等待时间等等。

2.优化方向

1. 优化数据访问

查询性能低下的主要原因是访问的数据太多，某些查询不可避免的需要筛选大量的数据，我们可以通过减少访问数据量的方式进行优化。

1. 确认应用程序是否在检索大量超过需要的数据
2. 确认mysql服务器层是否在分析大量超过需要的数据行
3. 是否向数据库请求了不需要的数据
   1. 查询不需要的记录
   2. 多表关联时返回全部列
   3. 总是取出全部列
   4. 重复查询相同的数据

2. 优化执行过程

1. 查询缓存

在解析一个查询语句之前，如果查询缓存是打开的，那么mysql会优先检查这个查询是否命中查询缓存中的数据，如果查询恰好命中了查询缓存，那么会在返回结果之前会检查用户权限，如果权限没有问题，那么mysql会跳过所有的阶段，就直接从缓存中拿到结果并返回给客户端

2.查询优化处理

mysql查询完缓存之后会经过以下几个步骤：解析SQL、预处理、优化SQL执行计划，这几个步骤出现任何的错误，都可能会终止查询。

1. 语法解析器和预处理

2. 查询优化器

以下是MySQL能够处理的优化类型：

1. 重新定义关联表顺序。
   1. 数据表的关联并不是一定按照查询中指定的顺序，优化器会做一些调整。
2. 将外连接转换为内连接
   1. 如果where条件，库表结构让一个外连接等价与一个内连接的时候，MySQL能够识别到并重写查询，让其可以调整执行顺序。
3. 使用等价变换原则
   1. MySQL可以使用一些等价变换来简化并规范表达式，他可以合并和减少一些比较，还可以移除一些恒成立和一些恒不成立的判断，比如（ 5=5 and a > 5 ）就将被改写为a>5,所以有些人会写where 1 = 1，这个就会被查询优化器优化掉，
4. 优化count、min、和max
   1. 要找到最小值，就是B-Tree树最左端的数据，同样最大值就是B-Tree最右端的数，没有任何条件的count（*）会直接使用存储引擎提供的一些参数，比如：myisam引擎已经维护了这个数据，则直接返回。
5. 索引覆盖
6. 提前终止查询
   1. MySQL发现满足需求的时候就会提前终止查询，比如使用了limit，再比如发现一个恒不成立条件，这时MySQL会立刻返回一个空结果。
7. 等值传播
   1. 两张表的on关联字段如果在where中有筛选，则默认对两张表都有筛选
8. 列表in的比较
   1. mysql中的in并不是使用or来进行处理的，而是先对in内的数据进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，复杂度为O(logN)，当in中的数据大的时候，处理速度就体现出来的。

3. 关联查询

Simple Nested-Loop Join：

Index Nested-Loop Join：

Block Nested-Loop Join：

（1）Join Buffer会缓存所有参与查询的列而不是只有Join的列。
（2）可以通过调整join_buffer_size缓存大小
（3）join_buffer_size的默认值是256K，join_buffer_size的最大值在MySQL 5.1.22版本前是4G-1，而之后的版本才能在64位操作系统下申请大于4G的Join Buffer空间。
（4）使用Block Nested-Loop Join算法需要开启优化器管理配置的optimizer_switch的设置block_nested_loop为on，默认为开启。

-- 查看不同的顺序执行方式对查询性能的影响：
explain select film.film_id,film.title,film.release_year,actor.actor_id,actor.first_name,actor.last_name from film inner join f
ilm_actor using(film_id) inner join actor using(actor_id);
-- 查看执行的成本：
show status like 'last_query_cost'; 
-- 按照自己预想的规定顺序执行：
explain select straight_join film.film_id,film.title,film.release_year,actor.actor_id,actor.first_name,actor.last_name from fil
m inner join film_actor using(film_id) inner join actor using(actor_id);
--  查看执行的成本：
show status like 'last_query_cost'; 

4.排序优化

MySQL有两种排序算法：

1. 两次传输排序（旧版本使用）
   1. 读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需要的数据行。
2. 打次传输排序（新版本使用）：
   1. 先读取查询所需要的所有列，然后在根据给定列进行排序，最后直接返回排序结果。相比较两次排序，对于IO密集型的应用，这样说的效率高了很多

2.优化特定类型的查询

1. 优化count()查询

count()是特殊的函数，有两种不同的作用，一种是某个列值的数量，也可以统计行数。在myisam引擎下，如果没有where条件则count(*)才是比较快的。
使用近似值：
在某些应用场景中，不需要完全精确的值，可以参考使用近似值来代替，比如可以使用explain来获取近似的值
其实在很多OLAP的应用中，需要计算某一个列值的基数，有一个计算近似值的算法叫hyperloglog。
**
一般情况下，count()需要扫描大量的行才能获取精确的数据，其实很难优化，在实际操作的时候可以考虑使用索引覆盖扫描，或者增加汇总表，或者增加外部缓存系统。

2. 优化关联查询

1. 确保on或者using子句中的列上有索引，在创建索引的时候就要考虑到关联的顺序

当表A和表B使用列C关联的时候，如果优化器的关联顺序是B、A，那么就不需要再B表的对应列上建上索引，没有用到的索引只会带来额外的负担，一般情况下来说，只需要在关联顺序中的第二个表的相应列上创建索引

2. 确保任何的groupby和order by中的表达式只涉及到一个表中的列，这样mysql才有可能使用索引来优化这个过程

3. 优化子查询

子查询的优化最重要的优化建议是尽可能使用关联查询代替（5.6以上版本忽略此优化）

4. 优化group by和distinct

通常MySQL使用同样的办法来优化这两种查询，那就是MySQL优化器会在内部处理的时候相互转化这两类查询，都通过使用索引来进行优化。
当无法使用索引时，Group by通过两种策略来完成：

1. 使用临时表或文件排序来做分组。
2. 通过使用提示SQL_BIG_RESULT和SQL_SMALL_RESULT来让优化器按照你希望的方式运行。

如果没有通过order by 显示指定排序列，当在group by的时候结果集会自动按照分组字段进行排序，可以使用order by null去除文件对齐，也可以显示指定排序规则。

distinct优化：当distinct后面有多个字段时，MySQL会认为要过滤掉后面两个字段都重复的记录。此时可以借助group by进行优化。
–select count(distinct name) from user;(效率较低)
–select count(1) from ( select name from user group by name)(效率更高)

4. 优化limit分页

在很多应用场景中我们需要将数据进行分页，一般会使用limit加上偏移量的方法实现，同时加上合适的orderby 的子句，如果这种方式有索引的帮助，效率通常不错，否则的化需要进行大量的文件排序操作，还有一种情况，当偏移量非常大的时候，前面的大部分数据都会被抛弃，这样的代价太高。
要优化这种查询的话，要么是在页面中限制分页的数量，要么优化大偏移量的性能

优化此类查询的最简单的办法就是尽可能地使用覆盖索引，而不是查询所有的列

5. 优化union查询

mysql总是通过创建并填充临时表的方式来执行union查询，因此很多优化策略在union查询中都没法很好的使用。经常需要手工的将where、limit、order by等子句下推到各个子查询中，以便优化器可以充分利用这些条件进行优化

除非确实需要服务器消除重复的行，否则一定要使用union all，因此没有all关键字，mysql会在查询的时候给临时表加上distinct的关键字，这个操作的代价很高