mysql优化原理_万字干货总结：MySQL优化原理学习，这一篇就够了！

最新推荐文章于 2024-04-11 06:41:58 发布

Ger Young

最新推荐文章于 2024-04-11 06:41:58 发布

阅读量122

点赞数

文章标签： mysql优化原理

本文链接：https://blog.csdn.net/weixin_42400518/article/details/113152922

版权

开辟者大年夜赛路演 | 12月16日，技巧立异，北京不见不散

索引若何组织数据存储，来本身高机能 MySQL

9d83ac3b32103f43681c283042952cc3.jpg-wh_651x-s_3586833508.jpg

说起 MySQL 的萌芽优化，信赖大年夜家收藏了一堆事业淫巧：不克不及应用 SELECT *、不应用 NULL 字段、合理创建索引、为字段选择合适的数据类型….. 你是否真的懂得这些优化技能？是否懂得其背后的工作道理？在实际场景下机能真有晋升吗？我想未必。因而懂得这些优化建议背后的道理就尤为重要，欲望本文能让你从新核阅这些优化建议，并在实际营业场景下合理的应用。

MySQL 逻辑架构

如不雅能在脑筋中构建一幅 MySQL 各组件之间若何协同工作的架构图，有助于深刻懂得 MySQL 办事器。下图展示了 MySQL 的逻辑架构图。

MySQL 逻辑架构整体分为三层，最上层为客户端层，并非 MySQL 所独有，诸如：连接处理、授权认证、安然等功能均在这一层处理。

MySQL 大年夜多半核心办事均在中心┞封一层，包含萌芽解析、分析、优化、缓存、内置函数 (比如：时光、数学、加密等函数)。所有的跨存储引擎的功能也在这一层实现：存储过程、触发器、视图等。

最基层为存储引擎，其负责 MySQL 中的数据存储和提取。和 Linux 下的文件体系类似，每种存储引擎都有其优势和劣势。中心的办事层经由过程 API 与存储引擎通信，这些 API 接口樊篱了不合存储引擎间的差别。

MySQL 萌芽过程

我们老是欲望 MySQL 可以或许获得更高的萌芽机能，最好的办法是弄清跋扈 MySQL 是若何优化和履行萌芽的。一旦懂得了这一点，就会发明：很多的萌芽优化工作实际上就是遵守一些原则让 MySQL 的优化器可以或许按照预想的合理方法运行罢了。当向 MySQL 发送一个请求的时刻，MySQL 到底做了些什么呢？

MySQL 萌芽过程

1. 客户端 / 办事端通信协定

MySQL 客户端 / 办事端通信协定是 “半双工” 的：在任一时刻，要么是办事器向客户端发送数据，要么是客户端向办事器发送数据，这两个动作不克不及同时产生。一旦一端开端发送消息，另一端要接收完全个消息才能响应它，所以我们无法也无须将一个消息切成小块自力发送，也没有办法进行流量控制。

客户端用一个零丁的数据包将萌芽请求发送给办事器，所以当萌芽语句很长的时刻，须要设置 max_allowed_packet 参数。然则须要留意的是，如不雅萌芽实袈溱是太大年夜，办事端会拒绝接收更多半据并抛出异常。

与之相反的是，办事器响应给用户的数据平日会很多，由多个数据包构成。然则当办事器响应客户端请求时，客户端必须完全的接收全部返回结不雅，而不克不及简单的只取前面几条结不雅，然后让办事器停止发送。因而在实际开辟中，尽量保持萌芽简单且只返回必须的数据，减小通信间数据包的大年夜小和数量是一个异常好的习惯，这也是萌芽中尽量避免应用 SELECT * 以及加上 LIMIT 限制的原因之一。

2. 萌芽缓存

在解析一个萌芽语句前，如不雅萌芽缓存是打开的，那么 MySQL 会检查这个萌芽语句是否射中萌芽缓存中的数据。如不雅当前萌芽正好射中萌芽缓存，在检查一次用户权限后直接返回缓存中的结不雅。这种情况下，萌芽不会被解析，也不会生成履行筹划，更不会履行。

MySQL 精华存存放在一个引用表(不要懂得成 table，可以认为是类似于 HashMap 的数据构造)，经由过程一个哈希值索引，这个哈希值经由过程萌芽本身、当前要萌芽的数据库、客户端协定版本号等一些可能影响结不雅的信息计算得来。所以两个萌芽在任何字符上的不合(例如：空格、注释)，都邑导致缓存不会射中。

如不雅萌芽中包含任何用户自定义函数、存储函数、用户变量、临时表、MySQL 库中的体系表，其萌芽结不雅都不会被缓存。比如函数 NOW() 或者 CURRENT_DATE() 会因为不合的萌芽时光，返回不合的萌芽结不雅，再比如包含 CURRENT_USER 或者 CONNECION_ID() 的萌芽语句会因为不合的用户而返回不合的结不雅，将如许的萌芽结不雅缓存起来没有任何的意义。

既然是缓存，就会掉效，那萌芽缓存何时掉效呢？

MySQL 的萌芽缓存体系会跟踪萌芽中涉及的每个表，如不雅这些表(数据或构造)产生变更，那么和这张表相干的所有缓存数据都将掉效。正因为如斯，在任何的写操作时，MySQL 必须将对应表的所有缓存都设置为掉效。如不雅萌芽缓存异常大年夜或者碎片很多，这个操作就可能带来很大年夜的体系消费，甚至导致体系僵逝世一会儿。并且萌芽缓存对体系的额外消费也不仅仅在写操作，读操作也不例外：任何的萌芽语句在开端之前都必须经由检查，即使这条 SQL 语句永远不会射中缓存

如不雅萌芽结不雅可以被缓存，那么履行完成后，会将结不雅存入缓存，也会带来额外的体系消费

最后的忠言是不要随便马虎打开萌芽缓存，特别是写密集型应用。如不雅你实袈溱是不由得，可以将 query_cache_type 设置为 DEMAND，这时只有参加 SQL_CACHE 的萌芽才会走缓存，其他萌芽则不会，如许可以异常自由地控制哪些萌芽须要被缓存。

当然萌芽缓存体系本身是异常复杂的，这里评论辩论的也执偾很小的一部分，其他更深刻的话题，比如：缓存是若何应用内存的？若何控制内存的碎片化？事务对萌芽缓存有何影响等等，读者可以自行浏览相干材料，这里权当抛砖引玉吧。

3. 语法解析和预处理