MySQL(六)查询优化

「 MYSQL 」 十二月 06, 2021

MySQL(六)查询优化

文章字数 7.1k 阅读约需 6 mins. 阅读次数 0

查询优化

查询慢的原因

  • 网络

  • CPU

  • IO

  • 上下文切换

  • 系统调用

  • 生成统计信息

  • 锁等待时间

优化数据访问

  • 减少访问数据量的方式进行优化
    • 确认应用程序是否在检索大量超过需要的数据
    • 确认mysql服务器层是否在分析大量超过需要的数据行
  • 是否向数据库请求了不需要的数据
    • 查询不需要的记录,优化:使用limit限制查询的列.
    • 多表关联时返回全部列,优化:取用到的字段
    • 总是取出全部列,优化:取用到的字段
    • 重复查询相同的数据,优化:在外部缓存起来.

执行过程的优化

查询缓存

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

注:由于命中率较低,在mysql8后,查询缓存被淘汰.

查询优化处理

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

语法解析器和预处理

mysql通过关键字将SQL语句进行解析，并生成一颗解析树

​ mysql解析器将使用mysql语法规则验证和解析查询，例如验证使用使用了错误的关键字或者顺序是否正确等等，

​ 预处理器会进一步检查解析树是否合法，例如表名和列名是否存在，是否有歧义，还会验证权限等等

查询优化器

当语法树没有问题之后，相应的要由优化器将其转成执行计划，一条查询语句可以使用非常多的执行方式，最后都可以得到对应的结果，但是不同的执行方式带来的效率是不同的，优化器的最主要目的就是要选择最有效的执行计划

mysql使用的是基于成本的优化器，在优化的时候会尝试预测一个查询使用某种查询计划时候的成本，并选择其中成本最小的一个

在很多情况下mysql会选择错误的执行计划，原因如下
  • 统计信息不准确
  • 执行计划的成本估算不等同于实际执行的成本
  • mysql的优化是基于成本模型的优化，但是有可能不是最快的优化
  • mysql不考虑其他并发执行的查询
  • 执行存储过程或者用户自定义函数的成本,不会被考虑.
优化器的优化策略
  • 静态优化: 直接对解析树进行分析，并完成优化
  • 动态优化: 动态优化与查询的上下文有关，也可能跟取值、索引对应的行数有关
  • mysql对查询的静态优化只需要一次，但对动态优化在每次执行时都需要重新评估
优化器的优化类型
  • 数据表的关联并不总是按照在查询中指定的顺序进行，决定关联顺序时优化器很重要的功能,
  • 将外连接转化成内连接，内连接的效率要高于外连接
  • 使用等价变换规则，mysql可以使用一些等价变化来简化并规划表达式,比如a<>4的写法可以写成a!=4来替换a<4 and a>4.
  • 优化count(),min(),max()等聚合函数,使用索引来分组可以增加性能.
  • 预估并转化为常数表达式，当mysql检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数进行处理
  • 索引覆盖扫描，当索引中的列包含所有查询中需要使用的列的时候，可以使用覆盖索引
  • 子查询优化,将经常查询的数据放入到缓存中.
  • 等值传播,如果两个列的值通过等式关联，那么mysql能够把其中一个列的where条件传递到另一个上.
排序优化

​ 当不能使用索引的时候，mysql就需要自己进行排序，如果数据量小则再内存中进行，如果数据量大就需要使用磁盘，mysql中称之为filesort。

​ 如果需要排序的数据量小于排序缓冲区(show variables like ‘%sort_buffer_size%’;),mysql使用内存进行快速排序操作，如果内存不够排序，那么mysql就会先将树分块，对每个独立的块使用快速排序进行排序，并将各个块的排序结果存放再磁盘上，然后将各个排好序的块进行合并，最后返回排序结果.

  • 两次传输排序

    第一次数据读取是将需要排序的字段读取出来，然后进行排序，第二次是将排好序的结果按照需要去读取数据行。
    这种方式效率比较低，原因是第二次读取数据的时候因为已经排好序，需要去读取所有记录而此时更多的是随机IO，读取数据成本会比较高
    两次传输的优势，在排序的时候存储尽可能少的数据，让排序缓冲区可以尽可能多的容纳行数来进行排序操作

  • 单次传输排序

    先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果，此方式只需要一次顺序IO读取所有的数据，而无须任何的随机IO，问题在于查询的列特别多的时候，会占用大量的存储空间，无法存储大量的数据

当需要排序的列的总大小超过max_length_for_sort_data定义的字节，mysql会选择双次排序，反之使用单次排序，当然，用户可以设置此参数的值来选择排序的方式

优化特定类型的查询

优化count()查询

count(1),count(id),count(*) 的查询速率是一样的,type都是index.但若是非索引字段则为all,且不会统计该字段为null的记录.

myisam的count在没有条件的时候比较快.

  • 使用近似值

  • 更复杂的优化

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

优化关联查询

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

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

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

优化子查询

子查询的优化最重要的优化建议是尽可能使用关联查询代替.因为子查询会产生临时表,增加io负担.与join的临时表不同.

优化limit分页

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

使用技巧:可以通过关联查询,先用覆盖索引查出需要的行记录id.再获取需要的记录字段

eg: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);

优化union

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

使用用户自定义变量

变量的使用

添加序号: 虽然mysql8推出了row_number()函数,但早些版本只能通过自定义变量实现.

set @index :=1; @index := @index +1;

获取最值,待之后的sql使用

set @min_actor :=(select min(actor_id) from actor)

计算日期

set @last_week :=current_date-interval 1 week;

自定义变量的限制

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

使用案例(以官方sakila库为例)

  • 优化排名语句

    1. 在给一个变量赋值的同时使用这个变量 

      set @rownum:=0;
select actor_id,@rownum:=@rownum +1 as rownum from actor limit 10;

    2. 查询获取演过最多电影的前10名演员，然后根据出演电影次数做一个排名 

      set @actor_num:=0;
SELECT t.*,@actor_num:=@actor_num +1 as rownum FROM (
select actor_id, count(*) cnt from film_actor group by actor_id order by cnt DESC limit 10) t

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

    当需要高效的更新一条记录的时间戳，同时希望查询当前记录中存放的时间戳是什么

    update t1 set lastupdated = now() where id = 1 and @now:=now();
-- select lastUpdated from t1 where id =1;
select @now;

  • 确定取值的顺序

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

    -- 获取一条数据
set @rownum:=0;
select actor_id,@rownum as cnt from actor where (@rownum:=@rownum+1)<=1;

-- 错误例子

set @rownum:=0;
select actor_id,@rownum:=@rownum+1 as cnt from actor where @rownum<=1;
-- 因为where和select在查询的不同阶段执行，所以看到查询到两条记录.

set @rownum:=0;
select actor_id,@rownum:=@rownum+1 as cnt from actor where @rownum<=1 order by first_name
-- 当引入了orde;r by之后，发现打印出了全部结果，这是因为order by引入了文件排序，而where条件是在文件排序操作之前取值的

空痕影

学而不思则罔,思而不学则殆.
0%