查询优化

查询慢的原因

  • 网络
  • CPU
  • IO
  • 上下文切换
  • 系统调用
  • 生成统计信息
  • 锁等待

优化数据访问

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

  • 确认应用程序是否在检索大量超过需要的数据

  • 确认Mysql服务器层是否在分析大量超过需要的数据行

    是否向数据库请求了不需要的数据

  • 查询不需要的记录

  • 多表关联时返回全部列
  • 总是取出全部列
  • 重复查询相同的数据

查询优化处理

语法解析器和预处理

mysql通过关键字将SQL语句进行解析，并生成一颗解析树，mysql解析器将使用mysql语法规则验证和解析查询，例如验证使用使用了错误的关键字或者顺序是否正确等等，预处理器会进一步检查解析树是否合法，例如表名和列名是否存在，是否有歧义，还会验证权限等等

查询优化器

很多情况下mysql会选择错误的执行计划原因如下：
  • 统计信息不准备
  • 执行计划成本估算不等同实际执行成本
  • mysql的最优可能跟你想的不一样 ： mysql优化基于成本模型的优化，但是可能不是最快的优化
  • mysql不考虑其他并发执行的查询
  • mysql不会考虑不受其控制的操作成本 比如存储过程或用户自定义函数的成本

优化器的优化策略

  • 静态优化 直接对解析树进行分析并完成优化
  • 动态优化 动态优化与查询的上下文有关，也可能跟取值 、索引对应的行数有关
  • Mysql对查询的静态优化只需要一次，但对动态优化在每次执行时都需要重重新评估

优化器的优化类型

  • 重新定义联表顺序
  • 将外连接转化成内连接，内连接的效率要高于外连接
  • 使用等价变换规则，mysql可以用一些等价变化来简化并规划表达式
  • 优化count,min max 索引和列是否为空通常可以帮助mysql优化这类表达式，例如找最小值，只需要找索引最左端的记录
  • 预估并转化为常数表达式，当mysql检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数进行处理
  • 索引fu盖扫描，当索引 中的列包含所有查询中需要使用的列旱，可以使用覆盖索引
  • 子查询优化 在某些情况下可以将子查询转换成效率更高的形式，从而减少多个查询多次对数据进行访问
  • 等值传播
    1. #等值传播
    2. explain select film.film_id from film inner join film_actor using(film_id
    3. ) where film.film_id > 500;
    4. # 这里使用film_id字段进行等值关联，film_id这个列不仅适用于film表而且适用于film_actor表
    5. explain select film.film_id from film inner join film_actor using(film_id
    6. ) where film.film_id > 500 and film_actor.film_id > 500;

关联查询

  • join的实现方式原理

image.png

image.png

image.png

案例演示

查看不同的顺序执行方式对查询性能的影响：
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’;

特定类型的优化

优化count

总有人认为myisam的count函数比较快，这是有前提条件的，只有没有任何where条件的count(*)才是比较快的

  • 使用近似值

在某些应用场景中，不需要完全精确的值，可以参考使用近似值来代替，比如可以使用explain来获取近似的值
其实在很多OLAP的应用中，需要计算某一个列值的基数，有一个计算近似值的算法叫hyperloglog。

  • 更复杂的优化

一般情况下，count()需要扫描大量的行才能获取精确的数据，其实很难优化，在实际操作的时候可以考虑使用索引覆盖扫描，或者增加汇总表，或者增加外部缓存系统。

优化关联查询

确保on或者using子句中的列上有索引，在创建索引的时候就要考虑到关联的顺序
当表A和表B使用列C关联的时候，如果优化器的关联顺序是B、A，那么就不需要再B表的对应列上建上索引，没有用到的索引只会带来额外的负担， 一般情况下来说，只需要在关联顺序中的第二个表的相应列上创建索引
确保任何的groupby和order by中的表达式只涉及到一个表中的列，这样mysql才有可能使用索引来优化这个过程

优化子查询

子查询的优化最重要的优化建议是尽可能使用关联查询代替
子查询打开临时表过多，改内连接的方式

优化limit分页

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

  • 优化此类查询的最简单的办法就是尽可能地使用覆盖索引，而不是查询所有的列 ```sql select film_id,description from film order by title limit 50,5

explain select film.film_id,film.description from film inner join (select film_id from film order by title limit 50,5) as lim using(film_id);

  2. <a name="CZcrO"></a>
  3. #### 优化join查询
  4. mysql总是通过创建并填充临时表的方式来执行union查询，因此很多优化策略在union查询中都没法很好的使用。经常需要手工的将where、limit、order by等子句下推到各个子查询中，以便优化器可以充分利用这些条件进行优化。<br />除非确实需要服务器消除重复的行，否则一定要使用union all，因此没有all关键字，mysql会在查询的时候给临时表加上distinct的关键字，这个操作的代价很高。
  6. <a name="Nh1Wk"></a>
  7. #### 推荐使用自定义变量
  9. - 自定义变量的使用
  10. ```sql
  11. set @one:=1
  12. set @min_actor:=(select min(actor_id))
  13. set @last_week:=current_date-interval 1 week;

  • 自定义变量的限制

    • 无法使用查询缓存
    • 不能在使用常量或标识符的地方使用自定义变量 例如表名、列名或Limit子句
    • 用户自定义变量的生命周期在一个连接内有效，所以不用它们来做连接间的通信
    • 不能显式声明自定义变量的类型
    • mysql优化器在某些场景下可能会将这些变量优化掉，这可能导致代码不按预想地方式运行
    • 赋值序号：=的优先级非常低，所以在使用赋值表达式的时候应该明确的使用括号
    • 使用未定义变量不会产生任何语法错误

  • 自定义变量的使用案例

    • 优化排名语句
      1. select actor_id,@rownum:=@rownum+1 as rownum from actor limit 10;
      2. select actor_id,count(*) as cnt from film_actor group by actor_id order by cnt desc limit 10;

  • 避免重新查询刚刚更新的数据 

    1. update t1 set  lastUpdated=now() where id =1;
    2. select lastUpdated from t1 where id =1;
    3. #优化后
    4. update t1 set lastupdated = now() where id = 1 and @now:=now();
    5. select @now;

  • 确定取值的顺序

在赋值和读取变量的时候可能是在查询的不同阶段

  1. set @rownum:=0;
  2. select actor_id,@rownum:=@rownum+1 as cnt from actor where @rownum<=1;
  3. 因为where和select在查询的不同阶段执行，所以看到查询到两条记录，这不符合预期
  5. set @rownum:=0;
  6. select actor_id,@rownum:=@rownum+1 as cnt from actor where @rownum<=1 order by first_name
  7. 当引入了orde;r by之后，发现打印出了全部结果，这是因为order by引入了文件排序，而where条件是在文件排序操作之前取值的  
  9. 解决这个问题的关键在于让变量的赋值和取值发生在执行查询的同一阶段：
  10. set @rownum:=0;
  11. select actor_id,@rownum as cnt from actor where (@rownum:=@rownum+1)<=1;