mysql 查询优化

2020-09-18  本文已影响0人  蜀山_竹君子

一、查询为什么会变慢？

需要明确的是编写快速的slq，真正重要的是响应时间
可以把查询看做是一个任务，其中包含一系列子任务，要优化查询，实际上是要优化子任务：

通常来讲，msql查询生命周期大致可以按照：从客户端到服务器，然后服务器解析、生成执行计划、然后调用存储引擎API执行查询，存储引擎检索、排序、分组处理，最后返回客户端。在完成查询任务时，查询任务需要在不同地方花费时间：

二、优化数据访问

查询性能最基本的原因是访问的数据过多。大部分性能地下的查询都可以通过减少数据访问量的方式进行优化。通过下面2种方法分析宗师很有效：

  1. 确认程序是否存在检索大量超过需要的数据。这通常意味着访问了太多行或列。
  2. mysql服务器是否存在分析大量超过需要的行。

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

有些查询会会请求超过需要的数据，然后这些多余的数据会被应用丢掉。这会给mysql服务器带来额外负担，并增加网络开销，同时也会消耗内存和CPU资源。

典型案例

查询不必要记录
常见的错误就是会误以为mysql只会返回需要的数据，需要开发设计人员习惯使用这样的设计，先使用select语句查询大量数据，然后获取前面的N行后关闭结果集（例如查询1000比商品信息，然后前端页面只展示前面10条）。开发人员会任务msql只会查询需要的10笔记录，然后停止查询。实际mysql会检索出全部的结果集，客户端程序会接收全部结果集然后抛弃其中大部分数据。最简单的优化手段就是在这样的查询加上LIMIT。
多表关联时返回全部的列
在设计中会经常看到类似这样的sql，查询电影的演员表：

 explain select * from actor inner join film_actor using(actor_id)
inner join film using(film_id) where film.title='ACE GOLDFINGER';

该查询将返回三个表全部的数据，在开发中应该避免出现这样的sql。正确的查询应该是：

 explain select actor.* from actor inner join film_actor using(actor_id)
inner join film using(film_id) where film.title='ACE GOLDFINGER';

总是取出所有列
每次看到select * 时都应该审视，是否需要返回全部的列？实际应该中大部分场景是不需要的。取出全部列，会让优化器无法使用覆盖索引扫描这类优化，同时还会对服务器带来额外I/O、CPU、内存网络消耗。尤其是应用和数据库服务器不是不是部署在一个节点，网络开销就别人明显了。
重复查询相同数据
在应用中经常会出现重复执行相同查询并返回相同结果。比如一个投票应用，投票者访问参赛者个人简介的时候可能会反复查询这个数据，比较好的方案就是初次查询时将该数据缓存起来，需要时从缓存取出，避免重复执行数据库访问。
分析是否在扫描额外的记录
确定查询只返回需要的数据后，接下来可以分析查询是否扫描了不需要的记录。最简单的衡量查询开销的三个指标：

没有那个指标能够完美衡量查询开销，但它们大致反映了在数据库中内部执行查询需要扫描多少数据，并大致推算出查询运行时间。这三个指标会记录在慢查询日志中。

这些扫描方式速度是从慢到快，扫描的行数也是从大到小。
explain语句的Extra列反映where条件使用，mysql提供三种方式应用where条件：

 explain select last_name from actor;--使用覆盖索引，虽然扫描了全部索引，但是无需回表查询直接命中结果
    id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
    1   SIMPLE  actor       index       idx_actor_last_name 137     200 100.00  Using index

 explain select * from actor where last_name like 'A%';--使用索引过滤
    id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
    1   SIMPLE  actor       range   idx_actor_last_name idx_actor_last_name 137     7   100.00  Using index condition

 explain select * from actor where first_name like 'A%';-- 使用where在服务器层过滤
    id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
    1   SIMPLE  actor       ALL                 200 11.11   Using where

常见的优化查询需要扫描大量数据的技巧和方法:

三、重构查询方式

mysql在设计上让连接和断开连接很轻量级，在返回一个很小的查询结果方面很高效。mysql内部每秒能够扫描百万级数据，相比之下mysql响应数据给客户端就慢的多。在其他情况都相同时，使用尽可能少的查询是非常好的策略。

切分查询

有时候对于一个大查询我们需要分而治之，将大查询拆分为多个相同功能的小查询，每个小查询只完成一分部任务。例如数据库表复制、清理数据等。如果用一个大查询一次性完成的话，可能会一次性锁定大量行，赞满整个事务日志、耗尽系统资源、阻塞很小但很重要的查询等。
例如：每个月定期清理消息表数据
优化前

delete from message where create_time < date_sub(now(),INTERVAL 3 MONTH);

优化后

rows_affected = 0
do {
 rows_affected = do_query(
"delete from message where create_time < date_sub(now(),INTERVAL 3 MONTH) limit 1000"
)} while rows_affected > 0

分解关联查询

很多高性能应用都会对关联查询进行分解。简单的就是多每个表进行一次查询，然后在应用程序中进行关联。用分解关联的方式拆分查询有如下优势：

四、查询执行的基础

当希望mysql能够以更高性能运行查询时，最好的办法是弄清楚mysql是如何优化和执行查询的。



向mysql发起一个查询请求，mysql执行路径以上如所示：

  1. 客户端发起一条查询给服务器。
  2. 服务器先检查缓存，如果缓存命中，则立即返回缓存中结果，否则进行下一步。
  3. 服务器进行sql解析、预处理，在由优化器生成执行计划。
  4. mysql根据优化器生成的执行计划调用存储引擎API执行查询。
  5. 将结果返回客户端。

mysql客户端/服务端通讯协议

mysql客户端/服务端通讯协议是“半双工”的，在任一时刻，要么是服务端向客户端发送数据，要么是客户端向服务端发送数据，两个动作不能同时发生。也无法将一个消息切成小块独立来发送。这种协议方式使得mysql通讯简单快速，也从很多地方限制了mysql，一个明显的限制就是mysql无法做流量控制：一端开始发生消息，另一端必须接收整个完整消息才能相应它。
客户端请求
客户端使用一个单独的包将查询传输给服务端。当查询特别长时，参数max_allowed_packet（5.7版本默认4M）非常重要，如果查询过大，服务端会拒绝接收更多数据并抛出相应错误。

 show variables like 'max_allowed_packet';
    Variable_name   Value
    max_allowed_packet  4194304
 4*1024*1024

服务端响应
一般服务端响应给客户端数据通常非常多，由多个数据包组成。当服务端向客户端响应数据时，客户端必须完整的接收整个返回结果，而不能被简单的只取前几条记录就让服务端停止传输数据。这是为什么在必要的时候必须在查询中加上LIMIT的原因。mysql服务端在向客户端传输数据时，实际是向客户端推送数据的过程，客户端没法使服务端停止下来。
查询状态
对于一个sql连接或者一个线程，任何时刻都有一个状态，该状态表示了mysql目前正在做什么。可以使用SHOW FULL PROCESSLIST 命令查看当前连接的状态。

show full processlist;
   Id  User    Host    db  Command Time    State   Info
   5   root    localhost:10310     Sleep   271     
   6   root    localhost:10311 sakila  Query   0   starting    show full processlist

mysql查询状态解释：

了解这些状态非常重要，可以通过这些状态判断当前线程谁在“持球”。
查询缓存
在解析查询语句前，如果查询缓存是打开的，mysql会优先检查这个查询是否命中查询缓存中的数据，如果命中直接返回查询缓存中的结果。检查规则是根据大小敏感的哈希查找实现。
查询优化处理
查询在未命中缓存后，进入服务器sql转换为执行计划过程：包括解析sql、预处理、生成执行计划。过程出现任何错误都可能终止查询。
语法解析器和预处理器

查询优化器
sql语法检查通过后，由优化器将其转换为sql执行计划，一个查询可以有多种执行方式，最后都返回相同结果，优化器的作用就是找到最合适的执行计划。
mysql使用基于成本的优化器。将尝试预测一个查询使用某种计划的成本，并选择一个成本最小的一个。优化器基于存储引擎的统计信息:每个表或索引的页面个数、索引的基数（索引中不同值得数量）、索引与数据行的长度、索引的分布情况等来评估成本。优化器在评估成本是不会考虑任何层面的缓存。
导致优化器选择错误执行计划原因：

mysql优化策略简单可以划分为两种，静态优化和动态优化。静态优化直接对解析树进行分析并完成优化。动态优化和查询的上下文有关。在执行语句或者存储过程的时候，静态优化和动态优化的区别非常重要，mysql对查询的静态优化在整个过程只执行一次，而动态优化则需要每次执行时都重新评估。
mysql能够处理以下优化类型：
重- 新定义关联表的顺序：数据关联表查询并不是总按sql指定的关联顺序进行。决定关联顺序是优化器很重要的一个功能。

mysql关联查询执行方式
在mysql中，任何一次查询都是一次“关联”—这不仅仅是一个查询需要两张表匹配才叫关联。在处理关联查询时，mysql会先将一系列单个查询的结果放到一个临时表中，然后在重新读取临时表中数据进行关联操作。
mysql执行关联策略很简单：对任何关联都执行嵌套循环关联操作，即msyql先在一个表中循环取出单挑数据，然后在嵌套循环到下一张表匹配数据，依次下去直到找到表中所有匹配为止。然后根据匹配的行，返回查询中需要的列。
执行计划
和大对数数据库系统不同，mysql不会生成查询字节码来执行查询。msyql服务器生成一颗查询指令树，然后通过存储引擎执行完成这颗指令树并返回结果。
排序优化
排序是成本很高的操作，从性能角度考虑，应该尽量避免排序或者避免大数据的排序。当不能使用索引排序时，mysql需要自己排序：数据量小的在内存中排序，数据大的在磁盘中排序，mysql将这过程统一称为文件排序。
如果需要排序的数据量小于排序缓存区大小mysql则直接在内存中快速排序。如果大于排序缓冲区，mysql会将数据分块，然后对每个独立的块进行排序，然后将各个块排序结果存储到磁盘，再将各个块的排序结果进行合并，最后返回排序结果。
查询执行引擎
在解析和优化器阶段，mysql服务器将sql生成对应的执行计划，而存储引擎则根据执行计划完成整个查询。这里的执行计划是一种数据结构（指令树）而不是字节码。

五、msyql查询优化器的局限

msyql查询优化器不是万能，对少部分查询不适用。对于不适合的场景我们可以重构改写sql让mysql高效的完成查询。
UNION限制
有时候mysql优化器无法将限制条件从外层“下推”到内层，这使得原本能够限制内层的条件无法应该到内层查询的优化中。
如果希望在UNION个子查询能够根据LIMIT只取部分结果集或者希望能够先排好序然后在合并结果集的话，需要在每个个子句分别使用LIMIT或者排序。
比如

 explain (select first_name,last_name from actor order by last_name)
union all
(select first_name,last_name from customer order by last_name) limit 20;
    id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
    1   PRIMARY actor       ALL                 200 100.00  
    2   UNION   customer        ALL                 599 100.00

这个查询会将actor的200条记录和customer的599条记录放到临时表中，然后在LIMIT 20条记录返回结果。
对这个查询的优化是分别在两个子查询使用LIMIT 20减少临时表的数据

 explain (select first_name,last_name from actor order by last_name limit 20)
union all
(select first_name,last_name from customer order by last_name limit 20) limit 20;
    id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
    1   PRIMARY actor       ALL                 200 100.00  Using filesort
    2   UNION   customer        ALL                 599 100.00  Using filesort

索引合并优化
mysql能够访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行，在合并过程需要消耗大量CPU计算和内存资源。
等值传递
某些时候等值传递会出现意想不到的额外消耗，比如当有一个非常大的in()列表，而mysql优化器发现存在where、using、on的子句，将这个列表的值和另一个表的某个列关联。优化器会将in()列表的值复制应该到关联的表中。通常如果in()列表小的时候，增加了过滤条件会提高效率，但是当in列表过大时，反而会导致查询变慢。
无法并行执行
mysql无法利用多核特性来并行执行查询任务。
最大值和最小值优化
mysql对min()、max()的优化做的并不好，可以看一个例子：

 explain select min(actor_id) from actor where first_name like 'A%';
    id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
    1   SIMPLE  actor       ALL                 200 11.11   Using where

因为first_name上没有索引，因此会执行一次全表扫描。actor_id是actor表主键，理论上mysql读到的第一个值就是我们需要的最小值了，因为主键是严格按照大小排序的。但是实际mysql只会做全表扫描。比较曲线的优化就是去掉min使用LIMIT

 explain select actor_id from actor where first_name like 'A%' order by actor_id asc limit 1;
    id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
    1   SIMPLE  actor       index       PRIMARY 2       1   11.11   Using where

不允许在同一个表上查询和更新
mysql不允许同一时刻在同一张表上同时进行查询和更新。

六、查询优化器的提示

如果对查询优化器的优化结果不满意可以根据优化器的几个提示来控制最终执行计划。

  • delayed:这个提示对insert和replace有效，mysql会将使用该提示的语句立即返回给客户端，并将插入的行数据放入缓冲区，然后在闲时批量执行写入。日志系统或者客户端不需要等待单条记录完成I/O的应用非常适合这个提示。需要注意并不是所有存储引擎支持该提供，同时可能导致LAST_INSERT_ID()函数无法使用。
  • straight_jion:这个提示可以放到select语句的select关键字后面，也可以放到任何关联表的前面。该提示有两个作用：让查询中所有表安装sql语句中的关联顺序进行关联以及固定前后两个表的关联顺序。当mysql优化器无法正确选择关联顺序，或者关联太多优化器无法评估关联顺序时，straight_jion都很有用。可以使用explain查询关联查询顺序和执行效率，然后使用straight_jion提示固定查询顺序，在使用explain查询执行效率，选择最佳的顺序。
  • sql_mall_result和sql_big_result：该提示只有对select语句有效，它告诉优化器对group by和distinct查询如何使用临时表和排序。sql_mall_result告诉优化器这个结果集很小，可以直接使用排序缓冲区排序，sql_big_result告诉优化器这个结果集很大，直接使用磁盘临时表做排序操作。
  • sql_buffer_result：告诉优化器将查询放入临时表，然后尽可能释放锁。
  • sql_cache和sql_no_cache：告诉优化器是否将查询结果放入缓存。
  • for update和lock in share mode：这两个提示不是优化器提示，这两个提示控制了select 语句的锁机制，但只对行级锁存储引擎有效。

七、优化特定类型查询（优化案例汇总）

count()查询优化
count()聚合函数，是一个特殊函数，有两种不同作用：

 explain select first_name,last_name,count(*) from film_actor inner join actor using(actor_id) group by actor.first_name,actor.last_name;
    id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
    1   SIMPLE  actor       ALL PRIMARY             200 100.00  Using temporary; Using filesort
    1   SIMPLE  film_actor      ref PRIMARY PRIMARY 2   sakila.actor.actor_id   27  100.00  Using index
 explain select first_name,last_name,count(*) from film_actor inner join actor using(actor_id) group by film_actor.actor_id;
    id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
    1   SIMPLE  actor       ALL PRIMARY             200 100.00  Using temporary; Using filesort
    1   SIMPLE  film_actor      ref PRIMARY,idx_fk_film_id  PRIMARY 2   sakila.actor.actor_id   27  100.00  Using index

使用关联顺序第二张表效率会更高

 explain select first_name,last_name,count(*) from film_actor inner join actor using(actor_id) group by actor.actor_id;
    id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
    1   SIMPLE  actor       index   PRIMARY,idx_actor_last_name PRIMARY 2       200 100.00  
    1   SIMPLE  film_actor      ref PRIMARY PRIMARY 2   sakila.actor.actor_id   27  100.00  Using index

优化LIMIT分页
在系统中进行分页操作时，我们通常使用LIMIT加上偏移量实现，同时加上合适的order by子句。如果order by 可以实现索引，效率通常会不错。如果不能使用索引那么只能使用文件排序。同时当偏移量特别大时，即时使用了索引，最后查询效率也会变得很慢，比如limit 100000 10。对于这类场景要么是限制客户分页，要么是优化大便宜量性能。
优化分页查询最好的手段就是使用覆盖索引扫描。然后在根据需要做一次关联查询获取需要的记录。

 explain select * from film order by title asc limit 50, 5;
    id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
    1   SIMPLE  film        ALL                 1000    100.00  Using filesort

优化后：

 explain select * from film inner join (select film_id from film order by title asc limit 50, 5) t using(film_id) ;
    id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
    1   PRIMARY <derived2>      ALL                 55  100.00  
    1   PRIMARY film        eq_ref  PRIMARY PRIMARY 2   t.film_id   1   100.00  
    2   DERIVED film        index       idx_title   767     55  100.00  Using index

另外也可以通过计算边界值的方式优化LIMIT或者使用一些冗余表的方式。
优化UNION
mysql总是创建并填充临时表的方式来执行UNION查询，因为没有过多优化策略。最主要的就是mysql无法将外部条件下沉到内部，因为子查询需要冗余的各自实现自己的过滤和排序等规则。另外除非必须消除重复的行，否则应该使用UNION ALL。
静态查询分析
Percona Toolkit中的pt-query-advior能够解析查询日志、分析查询模式，然后给出所有可能存在潜在危险的查询，然后给出足够详细建议。

上一篇 下一篇

猜你喜欢

热点阅读