Mysql高性能查询原理

这一小节胖虎为大家介绍一下Mysql查询的基本原理，首先从一个简单的问题开始入手：

1.为什么查询速度会慢

查询的生命周期可以按照顺序分为：客户端输出查询指令(SQL)->服务器进行解析->生成执行计划—>执行引擎执行计划->返回结果给客户端。

生命周期不同的阶段所消耗的时间不同，包括网络，CPU计算，统计信息，执行计划，锁等待以及底层引擎对检索数据的的调用操作，这些操作是发生在内存中的，内存不足的时候会涉及到大量的I/O，以及操作系统的上下文切换时间。

不合理的计划会产生大量的无效查询，会导致查询速度变慢，优化查询目的是将这些无效查询以及无效I/O转换为有效。

2.优化数据访问

大部分数据查询低效是因为查询了太多非必须的行数据，要优化数据访问首先要确认Mysql服务器是否分析大量非必需要的数据行。

比如在做分页查询的时候，无需首先查询全部数据，再丢弃部分数据。比如新闻网站上有100条记录，在切换页面的时候每次都会把100条数据全部查出，选10条数据展示当前页， 其余的丢掉。其实，简单有效的方法是在后面加上LIMIT。

• 响应时间

• 返回行数

  explain select  *  from order_info where count >3;

1）在索引中使用WHERE条件过滤不匹配的记录(索引列包含了where的查询条件列)。

2）使用索引覆盖（覆盖索引）需要返回的列（在Extra列中出现了Using index），查询过程无需回表。

3）在数据表中查询数据，然后过滤不满足条件的记录（在Extra列中Using Where）。

大部分优化技巧：

2）可以使用单独的汇总表。

3）重新优化复杂的查询结构。

下部分重点讲讲优化复杂的查询。

3. 重构复杂查询方式

（1）切分大的查询

传统阶段总是写一个复杂的查询尽可能让数据库完成，这样做的目的是减轻网络的通信，解析和优化的成本。

而现阶段网络速度比传统阶段要快的多，Mysql5.6以上版本数据库连接和断开连接都非常轻量级，返回一个小的查询结果非常高效。

相比之下，Mysql首先查询百万数据，然后再响应数据给客户端就慢多了。将一个大的查询分解成小的查询非常有必要。将一个大查询需要分而治之，大查询切分成小查询，每个查询功能完全一样。

比如要删除最近三个月之外的历史记录时，如果用一个大的delete语句执行删除的话，会一次锁住很多数据，沾满整个实物日志，耗尽系统资源，阻塞很多小但重要的查询。将一个大的Delete语句切分为多个较小的查询尽可能小地影响Mysql性能，同时还可以减少Mysql复制的延迟。

（2）分解关联查询 

很多高性能的引用都会将关联查询进行分解，比如查询订单为10086商品的供应商信息：

select  c.*  from order_info as o left join product as p on o.product_id=p.id left join company as c on p.conpany_id=c.id where o.id=10086;

分解为：

//查询10086订单的商品信息
select product_id from  order_info where id =10086;
//查询商品为132,134的供应商信息
select  company_id  from product where id in (132,134);
select  * from company where id =189;

看上去分解后的多个查询比单一查询更为复杂，其实它具有的优势如下：

1）让缓存的效率更高。

例如，在查询第一句时，已经有相应的id=10086的结果查询进行了缓存。

2）将查询结果分解后，执行单个查询可以减少锁的竞争。

3）查询效率提升，使用in代替关联查询，可以让Mysql按照ID进行顺序查询，查询效率更高。

4. 查询执行的基础

Mysql执行的总体逻辑如下图：

（1）客户端发送一条查询指令给服务器。

（2）服务端先检查缓存中是否存在查询的数据，如果有则立即返回。

（3）服务端进行SQL语句解析、预处理，再由优化器生成对应的执行计划。

（4）Mysql根据优化器生成的执行计划，存储引擎通过API调用执行查询。

（5）将结果返回给客户端。

4.1 查询指令

Mysql客户端和服务器之间的通信协议是“半双工”的，这意味着，在任何一个时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据。

在一个Mysql查询语句执行过程中，任何时刻输入SHOW FULL PROCESSLIST 都会返回一个状态，表示Mysql当前正在做什么。

具体指令如下： 

4.2 查询缓存

在解析一个查询语句之前，如果查询缓存是打开的，那么Mysql会先检查这个查询是否命名查询缓存中的数据。这个检查是对大小写敏感的，如果没有没有命中查询缓存，则进入到下一个阶段。

4.3 查询优化处理

将一个SQL语句转成一个执行计划，在依照这个执行计划和存储引擎进行交互。包括：解析SQL，预处理，优化SQL执行计划。

（1）语法解析器和预处理

Mysql通过关键字对SQL语句进行进行，形成一个解析树。Mysql解析器将使用Mysql语法规则验证和解析查询sql语句的标点符号和关键字以及顺序是否正确。预处理器会根据语法规则进一步分析解析树是否合法，并且分析树中的别名含义。

（2）查询优化器

优化器将解析好的解析树转换为执行计划，一条查询可以很多种执行计划返回相同结果。优化器的作用是找到其中最高的执行计划。

Mysql使用的是基于成本的优化器，它可以预测一个查询使用某种执行计划的成本，并选择其中成本最小的一个。可以通过查询当前会话的Last_query_cost的值来查询Mysql计算的当前的查询成本。

show status like 'last_query_cost' //上一次查询语句优化的成本

例如以上例子，Mysql优化器认为需要10个数据页的随机查询才能完成上面的数据查询，这是根据一系列的统计信息计算的得来的每个表或者索引的页面个数。

查询优化器并不一定总是能找到最优的执行计划。索引基数，索引和数据行的长度以及索引的分布情况，有很多种原因会导致Mysql优化器执行错误的执行计划。Mysql依赖存储引擎提供的统计信息来评估执行成本，有的存储引擎提供的信息是准确的，但有的偏差可能非常大。

执行计划中成本估算不等同于实际执行的成本，所以即使统计信息非常精准，优化器给出的执行计划也可能不是最优。Mysql给出的执行计划并不一定是执行时间最短的，Mysql只是基于其成本模型选择最优的执行计划，而有些时候这不是最快的执行方式。

Mysql的查询是一个非常复杂的结构，包含了许多优化策略来生成一个最优的执行计划。这些策略可以分为两种，一种是静态优化，另一种是动态优化。

静态优化可以直接对解析数进行分析，并完成优化。例如可以通过一些简单的代数变换将WHER条件转换为另一种等价形式。

动态优化则与上下文有关，例如WHERE条件中的取值，索引条目对应的数据行数等，这需要在每次查询的时候都进行重新评估。

下面是Myqsl能够处理的优化类型：

1）重新定义关联表的顺序 

数据表的查询并不是按照在查询中指定的顺序进行，决定关联查询的顺序是优化器非常重要的一部分，内容会在后续章节中继续讲到。

2）将外连接转换为内连接

并不是所有的out join都是外连接的执行方式执行，在某些where 条件和库表结构会让外连接等价与一个内连接。

3）使用等价变换规则

Mysql可以使用一些等价变换来简化并规范表达式。例如，（5=5 AND A>5）会写成a>5。

4）优化COUNT(),MIN()和MAX() 

例如要找到某一列的最小值，只需要查找对应的B-Tree索引最左的记录，Mysql可以直接获取索引的第一行记录，优化器会将这个查询表达式当做常数来对待。

当Mysql检测到一个表达式可以转换为常数的时候，就会一直把该表达式作为常数进行优化处理。

举个例子：

explain select o.id, o.order_num ,p.name from order_info o LEFT JOIN products p  on   o.product_id=p.id where p.id =1

在执行计划的第二行中，Mysql将第一步中返回的product_id列当做一个已知取值的列来处理，因为优化器清楚在第一步执行完成后，该值是一个常数值。所以第二段访问类型也是const。

5）Mysql执行关联查询

Mysql中认为任何一次查询都是一次关联，例如在union查询 ，Mysql先将一系列单个查询结果存储到一个临时表中，然后再重新查询临时表数据来完成union查询。

Mysql对任何关联都执行嵌套循环关联操作，即Mysql先在一个表中循环去查询单条数据，然后再嵌套循环到下一个表中循环寻找匹配的行，依次查询，直到找到所有表中匹配的行位置，最后返回需要查询的列。如果最后一个关联表中无法找到更多匹配的行，Mysql会返回上一层关联表，查询其它匹配的结果，依次迭代执行。

和其它关系数据库不同，Mysql并不会生成查询字节码来执行查询，Mysql会生成查询的一颗指令树，然后通过存储引擎来执行这颗指令树并返回结果。Mysql查询总是从一个表开始一直嵌套循环，回溯完成所有表的关联，执行过程是一个深度优先搜索树。

6）排序优化

排序是一个耗费大量成本的操作，如果数据量小则在内存汇总进行排序，如果数据量大则需要使用外部磁盘，Mysql需要将数据进行分块，然后每个独立的块使用“快速排序”进行排序，再将排序结果放在磁盘上，并将各个排序好的块进行合并，最后返回排序结果。

4.4 查询执行引擎

在解析和优化阶段，Mysql将生成查询对应的执行计划，Mysql查询执行引擎则根据执行计划来完成整个查询，在执行执行计划过程中， 有大量的操作需要通过调用存储引擎接口来完成，这些接口称为“handler API接口” 在执行计划阶段就为每个表创建了一个handler实例，通过这个接口可以获得表中的列名以及索引信息，来估算成本模型生成最优执行计划。

4.5 返回结果给客户端

参考书籍：《高性能Mysql》

我是胖虎，用心写好每篇文，下期再见