【高性能MySQL】查询性能优化

2017-05-15  本文已影响0人  ezreally

合理的表结构、索引对于高性能查询来说是必不可少的。但是还需要合理的设计查询。如果查询写得很糟糕，那么表结构再合理、索引再合适，也无法实现高性能。
因此高性能的查询依赖于这三点：查询优化、索引优化、表结构优化。

为什么查询速度会慢

如果把查询看作是一个任务，那么它由一系列子任务组成，每个子任务都会消耗一定的时间。如果要优化查询，实际上要优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数，要么让子任务运行得更快。
查询的生命周期：从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行（包括调用存储引擎及调用后的排序、分组等数据处理），并返回结果给客户端。
在完成这些任务时，查询需要在不同地方花费时间，包括网络，CPU 计算，生成统计信息和执行计划、锁等待等操作。

优化数据访问

查询性能低下的原因可能是以下两种：

  1. 应用程序可能检索了超过需要的数据（访问了太多的行或者太多的列）。
  2. MySQL 服务器层可能在分析大量超过需要的数据行。
请求了不需要的数据
  1. 应用程序中做分页查询。
  2. 只查找确实需要的列，不用 SELECT *。
  3. 不要做重复查询。
MySQL 在扫描额外的记录

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

一般 MySQL 使用如下三种方式应用 WHERE 条件，从好到坏依次为：

如果发现查询需要扫描大量的数据但只返回少数的行，可以这么做：

重构查询的方式

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

一般情况下，能用一个查询解决的事就不要用多个查询。因为每次查询都有网络通信、查询解析和优化的过程。但是也有例外。因为 MySQL 从设计上让连接和断开连接都很轻量级，在返回一个小的查询结果方面很高效。

切分查询

比如需要将某张表的全部数据做DML操作，可以分多次进行。防止一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。

分解关联查询

将一个关联查询分解为多个查询的优势：

查询执行的基础

MySQL 执行一个查询的过程，到底做了些什么：

Screen Shot 2017-05-15 at 10.45.27.png
  1. 客户端发送一条查询给服务器。
  2. 服务器检查查询是否可以命中缓存，命中则直接返回。否则进入下一阶段。
  3. 服务器进行 SQL 解析、预处理，再由优化器生成对应的执行计划。
  4. 调用存储引擎的 API 来执行查询
  5. 返回结果给客户端
客户端／服务器通信协议

通过抓包软件分析，可以看到 MySQL 客户端和服务器之间是通过 TCP 和 mysql 协议进行通信的。具体细节这里就不讲了，我也没仔细看。目前我们只要大致理解通信协议是如何工作的就够了。

查询缓存

在解析查询语句之前，如果查询缓存是打开的，那么 MySQL 会优先检查这个查询是否命中查询缓存中的数据。没命中则走下一个阶段。命中了，在返回结果之前还会检查用户权限。如果权限没问题，则直接返回数据给客户端。这种情况下，查询不会被解析，不用生成执行计划，不会被执行。

查询优化处理

查询的生命周期下一步是将 SQL 转化成执行计划。然后再按照执行计划和存储引擎交互。这包括多个子阶段：解析 SQL，预处理，优化执行计划。一条查询可能有多种执行方式，最后都返回相同的结果。优化器的作用就是找到其中最好的执行计划。

查询执行引擎

存储引擎根据查询计划来完成查询。

返回结果给客户端

查询执行的最后一个阶段是返回结果给客户端。即使客户端不需要结果，也会返回这个查询的一些信息，如该查询影响到的行数。MySQL 将结果集返回给客户端是一个增量、逐步返回的过程。好处是服务器不需要缓存太多的结果，客户端也能第一时间获得返回的结果。

优化特定类型的查询

上一篇下一篇

猜你喜欢

热点阅读