万字复习总结：学习MySQL优化原理-这一篇就够了!.pdf

万字总结 ：学习MySQL优化原理 ，这一篇就够 了 ！ Java开源分享 说起MySQL的查询优化 ，相信大家收藏了一堆奇技淫巧 ：不能使用SELECT *、不使用NULL字段、 合理创建索引、为字段选择合适的数据类型 你是否真的理解这些优化技巧 ？是否理解其背后的工 作原理 ？在实际场景下性能真有提升吗 ？我想未必。因而理解这些优化建议背后的原理就尤为重 要 ，希望本文能让你重新审视这些优化建议 ，并在实际业务场景下合理的运用。 MySQL逻辑架构 如果能在头脑中构建一幅MySQL各组件之间如何协同工作的架构图 ，有助于深入理解MySQL服务 器。下图展示了MySQL的逻辑架构图。 MySQL逻辑架构整体分为三层 ，最上层为客户端层 ，并非MySQL所独有 ，诸如 ：连接处理、授权认 证、安全等功能均在这一层处理。 MySQL大多数核心服务均在中间这一层 ，包括查询解析、分析、优化、缓存、内置函数(比如 ：时 间、数学、加密等函数)。所有的跨存储引擎的功能也在这一层实现 ：存储过程、触发器、视图等。 最下层为存储引擎 ，其负责MySQL中的数据存储和提取。和Linux下的文件系统类似 ，每种存储引 擎都有其优势和劣势。中间的服务层通过API与存储引擎通信 ，这些API接口屏蔽了不同存储引擎间 的差异。 MySQL查询过程 我们总是希望MySQL能够获得更高的查询性能 ，最好的办法是弄清楚MySQL是如何优化和执行查询 的。一旦理解了这一点 ，就会发现 ：很多的查询优化工作实际上就是遵循一些原则让MySQL的优化 器能够按照预想的合理方式运行而已。 当向MySQL发送一个请求的时候 ，MySQL到底做了些什么呢 ？ MySQL查询过程 客户端/服务端通信协议 MySQL客户端/服务端通信协议是 “半双工”的 ：在任一时刻 ，要么是服务器向客户端发送数据 ，要 么是客户端向服务器发送数据 ，这两个动作不能同时发生。一旦一端开始发送消息 ，另一端要接收 完整个消息才能响应它 ，所以我们无法也无须将一个消息切成小块独立发送 ，也没有办法进行流量 控制。 客户端用一个单独的数据包将查询请求发送给服务器 ，所以当查询语句很长的时候 ，需要设置 max_allowed_packet参数。但是需要注意的是 ，如果查询实在是太大 ，服务端会拒绝接收更多数据 并抛出异常。 与之相反的是 ，服务器响应给用户的数据通常会很多 ，由多个数据包组成。但是当服务器响应客户 端请求时 ，客户端必须完整的接收整个返回结果 ，而不能简单的只取前面几条结果 ，然后让服务器 停止发送。因而在实际开发中 ，尽量保持查询简单且只返回必需的数据 ，减小通信间数据包的大小 和数量是一个非常好的习惯 ，这也是查询中尽量避免使用SELECT *以及加上LIMIT限制的原因之 一。 查询缓存 在解析一个查询语句前 ，如果查询缓存是打开的 ，那么MySQL会检查这个查询语句是否命中查询缓 存中的数据。如果当前查询恰好命中查询缓存 ，在检查一次用户权限后直接返回缓存中的结果。这 种情况下 ，查询不会被解析 ，也不会生成执行计划 ，更不会执行。 MySQL将缓存存放在一个引用表 （不要理解成table ，可以认为是类似于HashMap的数据结构 ）， 通过一个哈希值索引 ，这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些 可能影响结果的信息计算得来。所以两个查询在任何字符上的不同 （例如 ：空格、注释 ），都会导 致缓存不会命中。 如果查询中包含任何用户 自定义函数、存储函数、用户变量、临时表、MySQL库中的系统表 ，其查 询结果都不会被缓存。比如函数NOW()或者CURRENT_DATE()会因为不同的查询时间 ，返回不同的 查询结果 ，再比如包含CURRENT_USER或者CONNECION_ID()的查询语句会因为不同的用户而返 回不同的结果 ，将这样的查询结果缓存起来没有任何的意义。 既然是缓存 ，就会失效 ，那查询缓存何时失效呢 ？MySQL的查询缓存系统会跟踪查询中涉及的每个 表 ，如果这些表 （数据或结构 ）发生变化 ，那么和这张表相关的所有缓存数据都将失效。正因为如 此 ，在任何的写操作时 ，MySQL必须将对应表的所有缓存都设置为失效。如果查询缓存非常大或者 碎片很多 ，这个操作就可能带来很大的系统消耗 ，甚至导致系统僵死一会儿。而且查询缓存对系统 的额外消耗也不仅仅在写操作 ，读操作也不例外 ： 1. 任何的查询语句在开始之前都必须经过检查 ，即使这条SQL语句永远不会命中缓存 2. 如果查询结果可以被缓存 ，那么