说起MySQL的查询优化，相信不管是初级的小菜鸟还是浸淫多年的大神都能信口说出来个一二三。例如不能使用SELECT *、合理创建索引、使用explain命令检查sql语句..... 只是你知其然，是否真的知其所以然？是否理解其背后的工作原理？在实际场景下性能真有提升吗？我想可能很多人心里就要打一个问号了。因此理解这些优化建议背后的原理就显得尤为重要了。因为它可以帮助我们从整体出发去考虑问题的解决方案，而不是零零碎碎的做一些边边角的工作。
如果能在头脑中构建一幅MySQL各组件之间如何协同工作的架构图，那将非常有助于深入理解MySQL服务器。下图展示了MySQL的逻辑架构图。
MySQL逻辑架构整体分为三层，最上层为客户端层，负责mysql数据库与外界的交互。诸如：连接处理、授权认证、安全过滤等。
MySQL大多数核心服务均在中间这一层，包括查询解析、分析、优化、缓存、内置函数(比如：时间、数学、加密等函数)。所有的跨存储引擎的功能也在这一层实现：存储过程、触发器、视图等。
最下层为存储引擎，其负责MySQL中的数据存储和提取。每种存储引擎都有其优势和劣势。（一般来讲，除非是整张表的数据只用来读的操作，否则全部推荐使用innodb引擎。因为innodb会支持一些关系数据库的高级功能，如事务功能和行级锁，而myisam不支持。）
中间的服务层通过API与存储引擎通信，这些API接口屏蔽了不同存储引擎间的差异。
MySQL查询过程
客户端/服务端通信协议
MySQL客户端/服务端通信协议是“单通”的：在任一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。一旦一端开始发送消息，另一端要接收完整个消息才能响应它，所以我们无法也无须将一个消息切成小块独立发送，也没有办法进行流量控制。客户端用一个单独的数据包将查询请求发送给服务器，所以当查询语句很长的时候，需要设置max_allowed_packet参数。但是需要注意的是，如果查询实在是太大，服务端会拒绝接收更多数据并抛出异常。与之相反的是，服务器响应给用户的数据通常会很多，由多个数据包组成。但是当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。因而在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用SELECT *以及加上LIMIT限制的原因之一。
查询缓存
在解析一个查询语句前，如果查询缓存是打开的，那么MySQL会检查这个查询语句是否命中查询缓存中的数据。如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。这种情况下，查询不会被解析，也不会生成执行计划，更不会执行。
MySQL将缓存存放在一个引用表（不要理解成table，可以认为是类似于HashMap的数据结构），通过一个哈希值索引，这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同（例如：空格、注释），都会导致缓存不会命中。
如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、MySQL库中的系统表，其查询结果都不会被缓存。比如函数NOW()或者CURRENT_DATE()会因为不同的查询时间，返回不同的查询结果，再比如包含CURRENT_USER或者CONNECION_ID()的查询语句会因为不同的用户而返回不同的结果，将这样的查询结果缓存起来没有任何的意义。
既然是缓存，就会存在失效机制。MySQL的查询缓存系统会跟踪查询中涉及的每个表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。正因为如此，在任何的写操作时，MySQL必须将对应表的所有缓存都设置为失效。如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗，甚至导致系统僵死。所以查询缓存是一把双刃剑，它的使用与否是需要先进行论证的。如果是写密集的应用，更要慎重。
语法解析和预处理
MySQL通过关键字将SQL语句进行解析，并生成一颗对应的解析树。这个过程解析器主要通过语法规则来验证和解析。比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。预处理则会根据MySQL规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等。经过前面的步骤生成的语法树被认为是合法的了，并且由优化器将其转化成查询计划。多数情况下，一条查询可以有很多种执行方式，最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划。
查询执行引擎
在完成解析和优化阶段以后，MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令逐步执行得出结果。整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成，这些接口被称为handler API。查询过程中的每一张表由一个handler实例表示。实际上，MySQL在查询优化阶段就为每一张表创建了一个handler实例，优化器可以根据这些实例的接口来获取表的相关信息，包括表的所有列名、索引统计信息等。存储引擎接口提供了非常丰富的功能，但其底层仅有几十个接口，这些接口像搭积木一样完成了一次查询的大部分操作。
返回结果给客户端
查询执行的最后一个阶段就是将结果返回给客户端。即使查询不到数据，MySQL仍然会返回这个查询的相关信息，比如该查询影响到的行数以及执行时间等。如果查询缓存被打开且这个查询可以被缓存，MySQL也会将结果存放到缓存中。
总结一下MySQL整个查询执行过程，总的来说分为5个步骤：
一、客户端向MySQL服务器发送一条查询请求
二、服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段
三、服务器进行SQL解析、预处理、再由优化器生成对应的执行计划
四、MySQL根据执行计划，调用存储引擎的API来执行查询
五、将结果返回给客户端，同时缓存查询结果
性能优化建议
下面会从3个不同方面给出一些优化建议。但是要明白，关于性能的优化，没有绝对的真理。一切都应该是在实际的业务场景下通过测试来验证你关于执行计划以及响应时间的假设。
1、Scheme设计与数据类型优化
选择数据类型只要遵循小而简单的原则就好，越小的数据类型通常会更快，占用更少的磁盘、内存，处理时需要的CPU周期也更少。越简单的数据类型在计算时需要更少的CPU周期，比如，整型就比字符操作代价低，因而会使用整型来存储ip地址，使用DATETIME来存储时间，而不是使用字符串。
2创建高性能索引
索引是提高MySQL查询性能的一个重要途径，但过多的索引可能会导致过高的磁盘使用率以及过高的内存占用，从而影响应用程序的整体性能。应当尽量避免事后才想起添加索引，因为事后可能需要监控大量的SQL才能定位到问题所在，而且添加索引的时间肯定是远大于初始添加索引所需要的时间，可见索引的添加也是非常有技术含量的。
3使用explain检查sql语句的执行情况
Explain语法：explain select … from … [where …]
例如：explain select * from news;
输出：
+----+-------------+-------+-------+-------------------+---------+---------+-------+------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------
下面对各个属性进行了解：
1、id：这是SELECT的查询序列号
2、select_type：select_type就是select的类型，可以有以下几种：
3、table：显示这一行的数据是关于哪张表的
4、type：这列最重要，显示了连接使用了哪种类别,有无使用索引，是使用Explain命令分析性能瓶颈的关键项之一。一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题。
5、possible_keys：列指出MySQL能使用哪个索引在该表中找到行
6、key：显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL
7、key_len：显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好
8、ref：显示使用哪个列或常数与key一起从表中选择行。
9、rows：显示MySQL认为它执行查询时必须检查的行数。
10、Extra：包含MySQL解决查询的详细信息，也是关键参考项之一。
