MySQL查询性能优化

为什么查询速度会慢

• 如果把查询看作是一个任务，那么它由一系列子任务组成，每个子任务都会消耗一定时间。
• 如果想优化查询，实际上要优化其子任务，要么消除其中一些任务，要么减少任务的执行次数，要么让子任务运行得更快。
• 查询的生命周期
  • 从客户端到服务器，然后在服务器进行解析，生成执行计划，执行，执行可以是整个生命周期中最重要的阶段，这其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组等。

慢查询基础：优化数据访问

查询性能低下最基本的原因是访问的数据太多

• 确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行，但有时候也可能是访问了太多的列。
• 确认MySQL服务器层是否在分析大量超过需要的数据行。

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

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

• 只查询只要的记录，加上limit
• 多表关联选择需要的列
• 总是取出全部列，有可能是用来做缓存，相比多个独立的只取部分列的查询可能就更有好处
• 重复查询相同的数据，如果同一个页面多个地方使用同样的记录，最好将数据缓存起来

MySQL是否在扫描额外的记录

在确认查询只返回需要的数据后，接下来应该看看查询为了返回结果是否扫描了过多的数据。对于MySQL,最简单的衡量查询开销的三个指标如下：

• 响应时间
• 扫描的行数
• 返回的行数

响应时间

• 服务时间
  • 数据库处理查询的时间
• 排队时间
  • 等待锁
  • 等待I/O

扫描的行数和返回的行数

扫描的行数和访问类型

在explain语句的type列反应了访问类型，访问类型有很多种，从全表扫描、索引扫描、范围扫描、唯一索引查询、常数引用等。这里列的速度是从慢到快，扫描的行数也是从多到少。

重构查询的方式

在优化有问题的查询时，目标应该是找到一个更优的方法获得实际需要的结果-而不一定总是需要从MySQL获取一模一样的结果集。

一个复杂查询还是多个简单查询

• 现在网络通信成本降低，MySQL即使在一个通用服务器上，也能够运行每秒超过10万的查询，所以运行多个小查询是没有问题的。
• MySQL内部每秒能够扫描内存中上百万行数据，相比之下，MySQL响应数据给客户端就慢得多了。

切分查询

有时候对于一个大查询我们需要“分而治之”，将大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果。

• 删除旧数据就是一个好的例子，定期删除大量的数据，比如每个月删除一次旧数据，如果一个大的语句一次性完成的话，则可能需要一次锁住很多数据，占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。将一个大的DELETE语句切分成多个较小的查询可以尽可能小地影响MySQL性能，同时还可以减少MySQL复制的延迟。

分解关联查询

可以对一个表进行一次单表查询，然后将结果在应用程序中进行关联

• 让缓存的效率更高
• 执行单个查询可以减少锁的竞争
• 在应用层做关联，可以容易对数据库进行拆分，更容易做到高性能和可扩展。
• 查询本身效率也可能会有所提升。
• 可以减少冗余记录的查询。
• 在应用中关联，在某些场景比在MySQL的嵌套循环关联要高效

查询执行的基础

MySQL执行一个查询的过程

MySQL客户端/服务器通信协议

• MySQL客户端和服务器之间的通信协议是“半双工“的,这意味着，在任何一个时刻，要么是由服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。所以，我们无法也无须将一个消息切成小块独立来发送-指的是客户端发送。
• 这种协议让MySQL通信简单快速，但是也从很多地方限制了MySQL。一个明显的限制是，这意味着没法进行流量控制，一旦一端开始发生消息，另一端要接收完整个消息才能响应它。
• 客户端用一个单独的数据包将查询传给服务器，这也是为什么当查询语句很长的时候，参数max_allowed_packed就特别重要了。一旦客户端发送了请求，它能做的事情就只有等待了。
• 相反的，一般服务器响应给用户的数据通常很多，由多个数据包组成。当服务器开始响应客户端请求时，客户端必须完整地接收整个返回结果，而不能简单地只取前面几条结果，然后让服务器停止发送数据。
• 有一些连接MySQL的库函数，可以获得全部结果集并缓存到内存里，可以让查询尽快结束，早点释放响应的服务器资源。

查询状态

使用 show full processlist命令查看连接的状态，一个查询的生命周期中，状态会变化很多次。

• Sleep
  线程正在等待客户端发送新的请求
• Query
  线程正在执行查询或者正在将结果发送给客户端
• Locked
  在MySQL服务器层，该线程正在等待锁。Innodb的锁冰不会体现在线程状态中
• Analyzing and statistics
  线程正在收集存储引擎的统计信息，并生成查询的执行计划
• Copying to tmp table [on disk]
  线程正在执行查询，并且将其结果集都复制到一个临时表中，这种状态一般要么是在做GROUP BY操作，要么是文件排序操作，或者是UNION操作。后面有 on disk表示MySQL正在将一个内存临时表放到磁盘上。
• Sorting result
  线程正在对结果集进行排序

查询缓存

在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命中查询缓存中的数据。如果当前查询恰好命中了查询缓存，那么在返回查询结果之前MySQL会检查一次用户权限。这仍然是无须解析查询SQL语句的。

查询优化处理

查询的生命周期的下一步是将一个SQL转换成一个执行计划，MySQL再依照这个执行计划和存储引擎进行交互。这包括多个子阶段：解析SQL、预处理、优化SQL执行计划

语法解析器和预处理

• MySQL通过关键字将SQL语句进行解析，并生成一颗对应的“解析树”，MySQL解析器将使用MySQL语法规则验证和解析语句。它将验证是否使用错误的关键字，或者使用关键字的顺序是否正确等，再或它还会验证引号是否能前后正确匹配。
• 预处理器则根据一些MySQL规则进一步检查解析树是否合法，例如，这里将检查数据表和数据列是否存在，还会解析名字和别名，看看他们是否还有歧义。

查询优化器

现在语法树被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果，优化器的作用就是找到其中最好的执行计划。

• MySQL使用基于成本的优化器，他将尝试预测一个查询使用某种执行计划时的成本，冰选择其中成本最小的一个。

• 可以通过查询当前会话的Last_query_cost的值来得知MySQL计算的当前查询的成本。

  •  select * from t2 where a in (1,2) and b=1;
     show status like 'Last_query_cost';
    

  

  • 优化器认为大概需要做1个数据页的随机查找才能完成上面的查询。
  • 这是根据一系列的统计信息计算得来的: 每个表和索引的页面个数、索引的基数、索引和数据行的长度、索引分布情况。
  • 优化器在评估成本的时候并不考虑任何层面的缓存，它假设读取任务数据都需要一次磁盘I/O.

• 有很多种原因会导致MySQL优化器选择错误的执行计划

  • 统计信息不准确。MySQL依赖存储引擎提供的统计信息来评估成本，但是有的存储引擎提供的信息是准确的，有的偏差可能非诚打。例如，InnoDB因为其MVCC的架构，并不能维护一个数据表的行数的精确统计信息。
  • 执行计划中的成本估算不等于实际执行的成本。所以即使统计信息精确，优化器给出的执行计划也可能不是最优的。如果某个执行计划读取的更多的页面，但它的成本却更小。因为这些页面都是顺序读或者这些页面都已经在内存中，那么它的的访问成本将很小。MySQL层面并不知道哪些页面在内存中，哪些在磁盘上，所以查询实际执行过程中到底需要多少次物理I/O是无法得知的。
  • MySQL的最优可能和你想的最优不一样。
  • MySQL从不考虑其他的并发执行的查询，这可能会影响到当前查询的速度。

• MySQL分静态优化和动态优化

  • 静态优化可以直接对解析树进行分析，并完成优化。例如，优化器可以通过一些简单的代数变化将WHERE条件转换成另一种等价形式。静态优化在第一次完成后就一直有效，即使使用不同的参数重复执行查询也不会发生变化，可以认为这是一种 编译时优化。
    = 动态优化在每次执行时都需要重新评估，有时候甚至在查询的执行过程中也会重新变化，比如索引统计信息

• MySQL能够处理的优化类型

  • 重新定义关联表的顺序

  • 将外连接转化成内连接

  • 使用等价变换规则 ，例如 5=5 AND a>5 被改写为 a>5

  • 优化COUNT(),MIN(),MAX()

    • 索引和列是否为空通常可以帮助MySQL优化这类表达式，例如要找到某一列的最小值，只需要查询对应B-TREE索引最左端的记录，MySQL可以直接获取索引的第一行记录。

  • 预估并转化为常数表达式

  • 覆盖索引扫描

  • 子查询优化

  • 提前终止查询

  • 等值传播

    • 如果两个列的值通过等式关联，那么MySQL能够把其中一个列的WHERE条件传递到另一列上。

    •    select film.film_id from film inner join flim_actor using(film_id)
         where film.film_id>500
      

    • MySQL知道这里的WHERE子句不仅使用于film表，而且对于film_actor表同样适用。

  • 列表IN()的比较

    • 在很多数据库系统中，IN()完全等同于多个OR条件的子句，因为这两者完全等价的。在MySQL中这点是不成立的，MySQL将IN（）列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，这是一个O（logn）复杂度的操作，等价地转换成OR查询的复杂度为O（n），对于IN()列表中有大量取值的时候，MySQL的处理速度将会更快。

数据和索引的统计信息

服务器层没有任何统计信息，所以MySQL查询优化器在生成查询的执行计划是，需要向存储引擎获取响应的统计信息。

• 每个表或者索引有多少个页面
• 每个表的每个索引的基数是多少
• 数据行
• 索引长度
• 索引的分布信息等
  优化器根据这些信息来选择一个最优的执行计划

MySQL如何执行关联查询

• MySQL认为任何一个查询都是一次“关联”，并不仅仅是一个查询需要到两个表匹配才叫关联。
• MySQL关联执行的策略很简单
  • MySQL对任何归案立案都执行嵌套循环关联操作
  • MySQL先在一个表中循环取出单条数据
  • 再嵌套循环到下一个表中寻找匹配的行
  • 依次下去，直到知道所有表中匹配的行为止
  • 然后根据各个表匹配的行，返回查询中需要的各个列。
  • 

执行计划

关联查询优化器

MySQL优化器最重要的一部分就是关联查询优化，它决定了多个表关联时的顺序。通常多表关联的时候，可以有多种不同的关联顺序来获得相同的执行结果。关联查询优化器则通过评估不同的顺序时的成本来选择一个代价最小的关联顺序。

• 多表关联会一般会选择行数少的作为第一个驱动表

排序优化

无论如何排序都是一个成本很高的操作，所以从新能角度考虑，应尽可能避免排序或者尽可能避免对大量数据进行排序。

• 当不能使用索引生成排序结果的时候，MySQL需要自己进行排序，如果数据量小则在内存中进行，如果数据量大则需要使用磁盘，不过MySQL将这个过程统一称为文件排序，即使完全是内存排序不需要任何磁盘文件时也是如此。
• 如果需要排序的数据量小于“排序缓冲区”，MySQL使用内存进行“快速排序”操作。如果内存不够排序，那么MySQL会先将数据分块，对每个独立的块使用“快速排序”进行排序，将各个块的排序结果存放在磁盘，然后将各个排好序的块进行合并，最后返回排序结果。
• MySQL在进行文件排序的时候需要使用的临时存储空间可能回避想象的要大很多。原因在于MySQL在排序时，对每一个排序记录都会分配一个足够长的定长空间来存放。这个定长空间必须足够长以容纳其中最长的字符串，例如，如果是VARCHAR列则需要分配其完整长度。
• 在关联查询的排序时，如果排序的列全部在第一个表，那么MySQL在关联处理第一个表的时候就进行文件排序。反之，全部关联完再使用临时表排序。

查询执行引擎

无

返回结果给客户端

查询执行的最后一个阶段是将结果返回给客户端。即使查询不需要返回结果集给客户端，MySQL仍然会返回这个查询的一些信息，如该查询影响到的行数。

• MySQL将结果集返回客户端是一个增量、逐步返回的过程。比如关联操作，一旦服务器处理完最后一个关联表，开始生成第一条结果时，MySQL就可以开始向客户端逐步返回结果集了。
  • 服务端无需存储太多的结果，也就不会因为要返回太多结果而消耗太多内存。
  • 让MySQL客户端第一时间获得返回的结果

MySQL查询优化器的局限性

MySQL的万能“嵌套循环”并不是对每种查询都是最优的。不过还好，MySQL查询优化器只对少部分查询不适用，而且我们往往可以通过改写查询让MySQL高效地完成工作。MySQL5.6版本正式发布后，会消除很多MySQL原本的限制。

关联子查询

MySQL的子查询实现得非常糟糕。最糟糕的一类查询是WHERE条件中包含IN()的子查询语句。

如何利用好关联子查询

并不是所有关联子查询的性能都会很差。

• 用连接还是子查询 自行测试对比

UNION的限制

有时，MySQL无法将限制条件从外层“下推”到内层，这使得原本能够限制部分返回的条件无法应用到内层查询的优化上。

   select * from (
    select name from a
    union all
    select name form b
   ) limit 20

这条查询会把a表和b表的所以记录放在一个临时表中，然后再从临时表取出前20条。可以在union子查询中分别加上一个limit 20来减少临时表中的数据。

   select * from (
    select name from a
    limit 20
    union all
    select name form b
    limit 20
   ) limit 20

等值传递

某些时候，等值传递会带来一些意想不到的额外消耗。例如，有一个非常大的IN()列表，而MySQL优化器发现存在WHERE、ON或者USING的子句，将这个列表的值和另一个表的某个列相关联。

并行执行

MySQL无法利用多核特性来并行执行查询。很多其他的关系型数据库能够提供这个特性，但是MySQL做不到。

哈希关联

松散索引扫描

索引(a,b)，可以越过a扫描b, 5.6以前不支持，现在不知道

最大值和最小值优化

对于MIN()和MAX()查询，MySQL的优化做得并不好

   select min(id) from a where name='xiaobai'

这里的name没有索引，会做全表扫描，因为id是自增的，在扫描第一个值的时候缺没有停下来，可以重写为以下-但是测试不起作用

   select id from a  use index(PRIMARY) where name='xiaobai' limit 1

在同一个表上查询和更新

MySQL不允许对同一张表同时进行查询和更新。

查询优化器的提示(hint)

如果对优化器选择的执行计划不满意，可以使用优化器提供的几个提示(hint)来控制最终的执行计划。

• HIGH_PRIORITY 加到锁列队前后 , 只对标所的存储引擎有效
• LOW_PRIORITY 加到锁队列后面 , 只对标所的存储引擎有效
• DELAYED
  • 这个提示对INSERT和REPLACE有效。MySQL会使用该提示的语句立即返回给客户端，并将插入的行数据放入到缓冲区，然后在表空闲时批量将数据写入。日志系统使用这样的提示非常有效，或者是其他需要写入大量数据但是客户端缺不需要等待单条语句完成I/O的应用。可能会导致LAST_INSERT_ID()无法正常工作。
• STRAIGHT_JOIN
  • 这个提示可以放置在SELECT语句的SELECT关键字之后，也可以放置在任何两个关联表的名字之间。
  • 第一个用法是让查询中所有表按照在语句中出现的顺序进行关联
  • 第二个用法则是固定其前后两个表的关联顺序。
    

MySQL升级后的验证

可能会失效

优化特定类型的查询

优化COUNT()查询

• COUNT()是一个特殊的函数，有两种不同的作用
  • 统计某个列值的数量 count(name)
  • 统计行数 count(*)
• 使用COUNT(*) ,这种情况下通配符 *并不会像我们猜想的那样扩展成所有的列，实际上，它会忽略所有的列而直接统计所有的行数。

优化关联查询

• 确保ON或者USING子句中的列上有索引。
  • 当表A和表B用列c关联的时候，如果优化器的关联顺序时B、A，那么久不需要再B表的对应列上建上索引。没有用到的索引只会带来额外的负担。
  • 确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化这个过程。
  • 当升级MySQL的时候需要注意:关联语法、运算符优先级等其他可能会发生变化的地方。因为以前是普通关联的地方可能会变成笛卡尔积，不同类型的关联可能会生成不同的结果等。

优化子查询

关于子查询优化我们给出的最重要的优化建议就是尽可能使用关联查询。
如果使用的是MySQL5.6或更新的版本或者MariaDB，那么久可以直接忽略关于子查询的这些建议了。

优化GROUP BY 和 DISTINCT

• 使用索引
• 无法使用索引
  • GROUP BY使用两种策略完成: 使用临时表或者文件排序来做分组，这两种策略的新能都有提升的地方。可以通过使用提示SQL_BIG_RESULT和SQL_SMALL_RESULT来让优化器按照你希望的方式运行。
• 可以将MySQL的SQL_MODE设置为包含ONLY_FULL_GROUP_BY，当分组select后面包含非分组列，会返回一个错误，提醒你需要重写这个查询。

优化LIMIT分页

• 尽可能使用覆盖索引，而不是查询所有的列
• 如果表很大，使用 延迟关联
• 计算位置，使用到下一次查询中

优化UNION查询

• UNION会去重重复行，MySQL会给临时表加上DISTINCT选项
• 如果没必要去重重复行 请使用UNITON ALL