说起MySQL的查询优化，相信大家收藏了一堆奇技淫巧：不能使用SELECT *、不使用NULL字段、合理创建索引、为字段选择合适的数据类型.....

你是否真的理解这些优化技巧？是否理解其背后的工作原理？在实际场景下性能真有提升吗？

我想未必，因而理解这些优化建议背后的原理就尤为重要，希望本文能让你重新审视这些优化建议，并在实际业务场景下合理的运用。

 

MySQL客户端/服务端通信协议是“半双工”的。在任一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据。

这两个动作不能同时发生，一端开始发送消息，另一端要接收完整个消息才能响应它。所以我们无法也无须将一个消息切成小块独立发送，也没有办法进行流量控制。

服务器响应给用户的数据通常会很多，由多个数据包组成。当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。

所以在实际开发中我们需要保持一个良好的习惯

保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量，查询中尽量避免使用SELECT * 以及加上 LIMIT 限制。

在解析一个查询语句前，如果查询缓存是打开的，那么MySQL会检查这个查询语句是否命中查询缓存中的数据。

如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。

这种情况下，查询不会被解析，也不会生成执行计划，更不会执行。

MySQL将缓存存放在一个引用表（不要理解成table，可以认为是类似于HashMap的数据结构）

通过一个哈希值索引，这个哈希值通过查询本身、查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同（例如：空格、注释），都会导致缓存不会命中。

如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、MySQL库中的系统表，其查询结果都不会被缓存。

比如函数 NOW() 或者 CURRENT_DATE() 会因为不同的查询时间，返回不同的查询结果

比如包含 CURRENT_USER 或者 CONNECION_ID() 的查询语句会因为不同的用户而返回不同的结果，将这样的查询结果缓存起来没有任何的意义。

MySQL的查询缓存系统会跟踪查询中涉及的每个表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。

所以，在任何的写操作时，MySQL必须将对应表的所有缓存都设置为失效。

如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗，甚至导致系统僵死一会儿。

而且查询缓存对系统的额外消耗也不仅仅在写操作，读操作也不例外：

　　任何的查询语句在开始之前都必须经过检查，即使这条SQL语句永远不会命中缓存

　　如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗

所以，并不是什么情况下查询缓存都会提高系统性能，缓存和失效都会带来额外消耗，只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升。

查询缓存系统本身是非常复杂的，所以不要轻易打开查询缓存，特别是写密集型应用。其他更深入的话题，比如：缓存是如何使用内存的？如何控制内存的碎片化？事务对查询缓存有何影响等等。

MySQL通过关键字将SQL语句进行解析，并生成对应的解析树。这个过程解析器主要通过语法规则来验证和解析。

比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等。预处理则会根据MySQL规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等。

经过前面的步骤生成的语法树被认为是合法的了，并且由优化器将其转化成查询计划。

多数情况下，一条查询可以有很多种执行方式，最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划。

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

MySQL可以通过查询当前会话的 last_query_cost 的值来得到其计算当前查询的成本。

例如：

结果表示优化器认为大概需要做6391个数据页的随机查找才能完成上面的查询。

这个结果是根据一些列的统计信息计算得来的，这些统计信息包括：每张表或者索引的页面个数、索引的基数、索引和数据行的长度、索引的分布情况等等。

有非常多的原因会导致MySQL选择错误的执行计划，比如统计信息不准确、不会考虑不受其控制的操作成本（用户自定义函数、存储过程）。

MySQL认为的最优跟我们想的不一样（我们希望执行时间尽可能短，但MySQL值选择它认为成本小，但成本小并不意味着执行时间短）。

MySQL的查询优化器是一个非常复杂的部件，它使用了非常多的优化策略来生成一个最优的执行计划。

随着MySQL的不断发展，优化器使用的优化策略也在不断的进化。

在完成解析和优化阶段以后，MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令逐步执行得出结果。

整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成，这些接口被称为 handler API。查询过程中的每一张表由一个 handler 实例表示。

MySQL在查询优化阶段就为每一张表创建了一个 handler 实例，优化器可以根据这些实例的接口来获取表的相关信息，包括表的所有列名、索引统计信息等。

存储引擎接口提供了非常丰富的功能，但其底层仅有几十个接口，这些接口像搭积木一样完成了一次查询的大部分操作。

查询执行的最后一个阶段就是将结果返回给客户端。即使查询不到数据，MySQL仍然会返回这个查询的相关信息，比如该查询影响到的行数以及执行时间等。

如果查询缓存被打开且这个查询可以被缓存，MySQL也会将结果存放到缓存中。

 

总结一下MySQL整个查询执行过程，总的来说分为6个步骤：

看了这么多，你可能会期待给出一些优化手段。但是，不要听信你看到的关于优化的“绝对真理”。

应该在实际的业务场景下通过测试来验证你关于执行计划以及响应时间的假设。

1、Scheme设计与数据类型优化

2、创建高性能索引

3、索引相关的数据结构和算法

选择数据类型只要遵循小而简单的原则就好，越小的数据类型通常会更快，占用更少的磁盘、内存，处理时需要的CPU周期也更少。

越简单的数据类型在计算时需要更少的CPU周期，比如，整型就比字符操作代价低，因而会使用整型来存储ip地址，使用DATETIME来存储时间，而不是使用字符串。

几个可能容易理解错误的技巧：

索引是提高MySQL查询性能的一个重要途径，但过多的索引可能会导致过高的磁盘使用率以及过高的内存占用，从而影响应用程序的整体性能。

应当尽量避免事后才想起添加索引，因为事后可能需要监控大量的SQL才能定位到问题所在，而且添加索引的时间肯定是远大于初始添加索引所需要的时间，可见索引的添加也是非常有技术含量的。

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

我们知道，数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。

最基本的查询算法当然是顺序查找（linear search），这种复杂度为O(n)的算法在数据量很大时显然是糟糕的。

好在计算机科学的发展提供了很多更优秀的查找算法，例如二分查找（binary search）、二叉树查找（binary tree search）等。

如果稍微分析一下会发现，每种查找算法都只能应用于特定的数据结构之上，例如二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树上。

但是数据本身的组织结构不可能完全满足各种数据结构（例如，理论上不可能同时将两列都按顺序进行组织）。

所以在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构。

通常我们所说的索引是指 B-Tree 索引，它是目前关系型数据库中查找数据最为常用和有效的索引，大多数存储引擎都支持这种索引。

B-Tree 中的 B 是指 balance，意为平衡。需要注意的是，B-Tree 索引并不能找到一个给定键值的具体行，它找到的只是被查找数据行所在的页，接着数据库会把页读入到内存，再在内存中进行查找，最后得到要查找的数据。

B-Tree 是一种多路自平衡的搜索树 

它类似普通的平衡二叉树，不同的一点是 B-Tree 允许每个节点有更多的子节点。下图是 B-Tree 的简化图

特点：

1. 所有键值分布在整颗树中；

2. 任何一个关键字出现且只出现在一个结点中；

3. 搜索有可能在非叶子结点结束；

4. 在关键字全集内做一次查找,性能逼近二分查找；

B+Tree 是 B-Tree 的变体，也是一种多路搜索树, 它与 B-Tree 的不同之处在于:

1. 所有关键字存储在叶子节点出现,内部节点(非叶子节点并不存储真正的 data)

2. 为所有叶子结点增加了一个链指针

简化 B+Tree 如下图

红黑树等数据结构也可以用来实现索引，但是文件系统及数据库系统普遍采用B-/+Tree作为索引结构。

MySQL是基于磁盘的数据库系统，索引往往以索引文件的形式存储的磁盘上。

索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。为什么使用B-/+Tree，还跟磁盘存取原理有关。

下面我们来看一下磁盘局部性原理和预读。

由于磁盘的存取速度与内存之间鸿沟，为了提高效率，要尽量减少磁盘I/O，磁盘往往不是严格按需读取，而是每次都会预读。

磁盘读取完需要的数据，会顺序向后读一定长度的数据放入内存。

而这样做的理论依据是计算机科学中的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用，程序运行期间所需要的数据通常比较集中。

由于磁盘顺序读取的效率很高(不需要寻道时间，只需很少的旋转时间)，因此对于具有局部性的程序来说，预读可以提高I/O效率，预读的长度一般为页(page)的整倍数。

MySQL(默认使用InnoDB引擎)，将记录按照页的方式进行管理，每页大小默认为16K(这个值可以修改)，linux 默认页大小为4K。

我们一般以使用磁盘 I/O 次数评价索引结构的优劣。根据B-Tree的定义，可知检索一次最多需要访问h个节点。

数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。

B-Tree 每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个 node 只需一次I/O。

B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为 

可以在 num上设置默认值 0,确保表中 num 列没有 null 值，然后这样查询：

优化后：

对于连续的数值，能用 between 就不要用 in 了

若要提高效率，可以考虑全文检索。条件：在MySQL5.6以下，只有MyISAM表支持全文检索，在MySQL5.6以上Innodb引擎表也提供支持全文检索。相应字段建立FULLTEXT索引。

因为 SQL 只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时，它必须在编译时进行选择。如果在编译时建立访问计划变量的值还是未知的，所以无法作为索引选择的输入项将进行全表扫描。

可以改为强制查询使用索引：

不要在 where 子句中的 = 左边进行函数、算术运算或其他表达式运算，否则全表扫描。

优化后：

优化后：

参考：

https://www.cnblogs.com/liujiacai/p/7605612.html