mysql⾃定义函数返回结果集_MySQL优化原理
说起MySQL的查询优化，相信⼤家收藏了⼀堆奇技淫巧：不能使⽤SELECT *、不使⽤NULL字段、合理创建索引、为字段选择合适的数据类型..... 你是否真的理解这些优化技巧?是否理解其背后的⼯作原理?在实际场景下性能真有提升吗?我想未必。因⽽理解这些优化建议背后的原理就尤为重要，希望本⽂能让你重新审视这些优化建议，并在实际业务场景下合理的运⽤。
MySQL逻辑架构
如果能在头脑中构建⼀幅MySQL各组件之间如何协同⼯作的架构图，有助于深⼊理解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. 如果查询结果可以被缓存，那么执⾏完成后，会将结果存⼊缓存，也会带来额外的系统消耗
基于此，我们要知道并不是什么情况下查询缓存都会提⾼系统性能，缓存和失效都会带来额外消耗，只有当缓存带来的资源节约⼤于其本⾝消耗的资源时，才会给系统带来性能提升。但要如何评估打开缓存是否能够带来性能提升是⼀件⾮常困难的事情，也不在本⽂讨论的范畴内。如果系统确实存在⼀些性能问题，可以尝试打开查询缓存，并在数据库设计上做⼀些优化，⽐如：
1. ⽤多个⼩表代替⼀个⼤表，注意不要过度设计
2. 批量插⼊代替循环单条插⼊
3. 合理控制缓存空间⼤⼩，⼀般来说其⼤⼩设置为⼏⼗兆⽐较合适
4. 可以通过SQL_CACHE和SQL_NO_CACHE来控制某个查询语句是否需要进⾏缓存
最后的忠告是不要轻易打开查询缓存，特别是写密集型应⽤。如果你实在是忍不住，可以将query_cac
he_type设置为DEMAND，这时只有加⼊SQL_CACHE的查询才会⾛缓存，其他查询则不会，这样可以⾮常⾃由地控制哪些查询需要被缓存。
当然查询缓存系统本⾝是⾮常复杂的，这⾥讨论的也只是很⼩的⼀部分，其他更深⼊的话题，⽐如：缓存是如何使⽤内存的?如何控制内存的碎⽚化?事务对查询缓存有何影响等等，读者可以⾃⾏阅读相关资料，这⾥权当抛砖引⽟吧。
语法解析和预处理
MySQL通过关键字将SQL语句进⾏解析，并⽣成⼀颗对应的解析树。这个过程解析器主要通过语法规则来验证和解析。⽐如SQL中是否使⽤了错误的关键字或者关键字的顺序是否正确等等。预处理则会根据MySQL规则进⼀步检查解析树是否合法。⽐如检查要查询的数据表和数据列是否存在等等。
查询优化
经过前⾯的步骤⽣成的语法树被认为是合法的了，并且由优化器将其转化成查询计划。多数情况下，⼀条查询可以有很多种执⾏⽅式，最后都返回相应的结果。优化器的作⽤就是到这其中最好的执⾏计划。
MySQL使⽤基于成本的优化器，它尝试预测⼀个查询使⽤某种执⾏计划时的成本，并选择其中成本最
⼩的⼀个。在MySQL可以通过查询当前会话的last_query_cost的值来得到其计算当前查询的成本。
mysql> select * from t_message limit 10;
...省略结果集
mysql> show status like 'last_query_cost';
+-----------------+-------------+
| Variable_name  | Value      |
+-----------------+-------------+
| Last_query_cost | 6391.799000 |
+-----------------+-------------+
⽰例中的结果表⽰优化器认为⼤概需要做6391个数据页的随机查才能完成上⾯的查询。这个结果是根据⼀些列的统计信息计算得来的，这些统计信息包括：每张表或者索引的页⾯个数、索引的基数、索引和数据⾏的长度、索引的分布情况等等。
有⾮常多的原因会导致MySQL选择错误的执⾏计划，⽐如统计信息不准确、不会考虑不受其控制的操作成本(⽤户⾃定义函数、存储过程)、MySQL认为的最优跟我们想的不⼀样(我们希望执⾏时间尽可能短，但MySQL值选择它认为成本⼩的，但成本⼩并不意味着执⾏时间短)等等。
MySQL的查询优化器是⼀个⾮常复杂的部件，它使⽤了⾮常多的优化策略来⽣成⼀个最优的执⾏计划：
重新定义表的关联顺序(多张表关联查询时，并不⼀定按照SQL中指定的顺序进⾏，但有⼀些技巧可以指定关联顺序)
优化MIN()和MAX()函数(某列的最⼩值，如果该列有索引，只需要查B+Tree索引最左端，反之则可以到最⼤值，具体原理见下⽂)
提前终⽌查询(⽐如：使⽤Limit时，查到满⾜数量的结果集后会⽴即终⽌查询)
优化排序(在⽼版本MySQL会使⽤两次传输排序，即先读取⾏指针和需要排序的字段在内存中对其排序，然后再根据排序结果去读取数据⾏，⽽新版本采⽤的是单次传输排序，也就是⼀次读取所有的数据⾏，然后根据给定的列排序。对于I/O密集型应⽤，效率会⾼很多)
随着MySQL的不断发展，优化器使⽤的优化策略也在不断的进化，这⾥仅仅介绍⼏个⾮常常⽤且容易
理解的优化策略，其他的优化策略，⼤家⾃⾏查阅吧。
查询执⾏引擎
在完成解析和优化阶段以后，MySQL会⽣成对应的执⾏计划，查询执⾏引擎根据执⾏计划给出的指令逐步执⾏得出结果。整个执⾏过程的⼤部分操作均是通过调⽤存储引擎实现的接⼝来完成，这些接⼝被称为handler API。查询过程中的每⼀张表由⼀个handler实例表⽰。实际上，MySQL在查询优化阶段就为每⼀张表创建了⼀个handler实例，优化器可以根据这些实例的接⼝来获取表的相关信息，包括表的所有列名、索引统计信息等。存储引擎接⼝提供了⾮常丰富的功能，但其底层仅有⼏⼗个接⼝，这些接⼝像搭积⽊⼀样完成了⼀次查询的⼤部分操作。
返回结果给客户端
查询执⾏的最后⼀个阶段就是将结果返回给客户端。即使查询不到数据，MySQL仍然会返回这个查询的相关信息，⽐如该查询影响到的⾏数以及执⾏时间等等。
如果查询缓存被打开且这个查询可以被缓存，MySQL也会将结果存放到缓存中。
mysql下载32位结果集返回客户端是⼀个增量且逐步返回的过程。有可能MySQL在⽣成第⼀条结果时，就开始向客户端逐步返回结果集了。这样服务端就⽆须存储太多结果⽽消耗过多内存，也可以让客户端第⼀时间获
得返回结果。需要注意的是，结果集中的每⼀⾏都会以⼀个满⾜①中所描述的通信协议的数据包发送，再通过TCP协议进⾏传输，在传输过程中，可能对MySQL的数据包进⾏缓存然后批量发送。
回头总结⼀下MySQL整个查询执⾏过程，总的来说分为6个步骤：
1. 客户端向MySQL服务器发送⼀条查询请求
2. 服务器⾸先检查查询缓存，如果命中缓存，则⽴刻返回存储在缓存中的结果。否则进⼊下⼀阶段
3. 服务器进⾏SQL解析、预处理、再由优化器⽣成对应的执⾏计划
4. MySQL根据执⾏计划，调⽤存储引擎的API来执⾏查询
5. 将结果返回给客户端，同时缓存查询结果
性能优化建议
看了这么多，你可能会期待给出⼀些优化⼿段，是的，下⾯会从3个不同⽅⾯给出⼀些优化建议。但请等等，还有⼀句忠告要先送给你：不要听信你看到的关于优化的“绝对真理”，包括本⽂所讨论的内容，⽽应该是在实际的业务场景下通过测试来验证你关于执⾏计划以及响应时间的假设。
Scheme设计与数据类型优化
选择数据类型只要遵循⼩⽽简单的原则就好，越⼩的数据类型通常会更快，占⽤更少的磁盘、内存，处理时需要的CPU周期也更少。越简单的数据类型在计算时需要更少的CPU周期，⽐如，整型就⽐字符操作代价低，因⽽会使⽤整型来存储ip地址，使⽤DATETIME来存储时间，⽽不是使⽤字符串。
这⾥总结⼏个可能容易理解错误的技巧：
1. 通常来说把可为NULL的列改为NOT NULL不会对性能提升有多少帮助，只是如果计划在列上创建索引，就应该将该列设置为NOT
NULL。
2. 对整数类型指定宽度，⽐如INT(11)，没有任何卵⽤。INT使⽤32位(4个字节)存储空间，那么它的表⽰范围已经确定，所以INT(1)和
INT(20)对于存储和计算是相同的。
3. UNSIGNED表⽰不允许负值，⼤致可以使正数的上限提⾼⼀倍。⽐如TINYINT存储范围是-128 ~ 127，⽽UNSIGNED TINYINT存储
的范围却是0 - 255。
4. 通常来讲，没有太⼤的必要使⽤DECIMAL数据类型。即使是在需要存储财务数据时，仍然可以使⽤BIGINT。⽐如需要精确到万分之
⼀，那么可以将数据乘以⼀百万然后使⽤BIGINT存储。这样可以避免浮点数计算不准确和DECIMAL精确计算代价⾼的问题。
5. TIMESTAMP使⽤4个字节存储空间，DATETIME使⽤8个字节存储空间。因⽽，TIMESTAMP只能表⽰1970 - 2038年，⽐
DATETIME表⽰的范围⼩得多，⽽且TIMESTAMP的值因时区不同⽽不同。
6. ⼤多数情况下没有使⽤枚举类型的必要，其中⼀个缺点是枚举的字符串列表是固定的，添加和删除字符串(枚举选项)必须使⽤ALTER
TABLE(如果只只是在列表末尾追加元素，不需要重建表)。
7. schema的列不要太多。原因是存储引擎的API⼯作时需要在服务器层和存储引擎层之间通过⾏缓冲格式拷贝数据，然后在服务器层将
缓冲内容解码成各个列，这个转换过程的代价是⾮常⾼的。如果列太多⽽实际使⽤的列⼜很少的话，有可能会导致CPU占⽤过⾼。
8. ⼤表ALTER TABLE⾮常耗时，MySQL执⾏⼤部分修改表结果操作的⽅法是⽤新的结构创建⼀个张空表，从旧表中查出所有的数据插
⼊新表，然后再删除旧表。尤其当内存不⾜⽽表⼜很⼤，⽽且还有很⼤索引的情况下，耗时更久。当然有⼀些奇技淫巧可以解决这个问题，有兴趣可⾃⾏查阅。
创建⾼性能索引
索引是提⾼MySQL查询性能的⼀个重要途径，但过多的索引可能会导致过⾼的磁盘使⽤率以及过⾼的内存占⽤，从⽽影响应⽤程序的整体性能。应当尽量避免事后才想起添加索引，因为事后可能需要监控⼤量的SQL才能定位到问题所在，⽽且添加索引的时间肯定是远⼤于初始添加索引所需要的时间，可见索引的添加也是⾮常有技术含量的。
接下来将向你展⽰⼀系列创建⾼性能索引的策略，以及每条策略其背后的⼯作原理。但在此之前，先了解与索引相关的⼀些算法和数据结构，将有助于更好的理解后⽂的内容。
索引相关的数据结构和算法
通常我们所说的索引是指B-Tree索引，它是⽬前关系型数据库中查数据最为常⽤和有效的索引，⼤多数存储引擎都⽀持这种索引。使⽤B-Tree这个术语，是因为MySQL在CREATE TABLE或其它语句中使⽤了这个关键字，但实际上不同的存储引擎可能使⽤不同的数据结构，⽐如InnoDB就是使⽤的B+Tree。
B+Tree中的B是指balance，意为平衡。需要注意的是，B+树索引并不能到⼀个给定键值的具体⾏，它到的只是被查数据⾏所在的页，接着数据库会把页读⼊到内存，再在内存中进⾏查，最后得到要查的数据。
在介绍B+Tree前，先了解⼀下⼆叉查树，它是⼀种经典的数据结构，其左⼦树的值总是⼩于根的值，右⼦树的值总是⼤于根的值，如下图①。如果要在这课树中查值为5的记录，其⼤致流程：先到根，其值为6，⼤于5，所以查左⼦树，到3，⽽5⼤于3，接着3的右⼦树，总共了3次。同样的⽅法，如果查值为8的记录，也需要查3次。所以⼆叉查树的平均查次数为(3 + 3 + 3 + 2 + 2 + 1) / 6 = 2.3次，⽽顺序查的话，查值为2的记录，仅需要1次，但查值为8的记录则需要6次，所以顺序查的平均查次数为：(1 + 2 + 3 + 4 + 5 + 6) / 6 = 3.3次，因此⼤多数情况下⼆叉查树的平均查速度⽐顺序查要快。
⼆叉查树和平衡⼆叉树
由于⼆叉查树可以任意构造，同样的值，可以构造出如图②的⼆叉查树，显然这棵⼆叉树的查询效率和顺序查差不多。若想⼆叉查数的查询性能最⾼，需要这棵⼆叉查树是平衡的，也即平衡⼆叉树(AVL树)。
平衡⼆叉树⾸先需要符合⼆叉查树的定义，其次必须满⾜任何节点的两个⼦树的⾼度差不能⼤于1。显然图②不满⾜平衡⼆叉树的定义，⽽图①是⼀课平衡⼆叉树。平衡⼆叉树的查性能是⽐较⾼的(性能最好的是最优⼆叉树)，查询性能越好，维护的成本就越⼤。⽐如图①的平衡⼆叉树，当⽤户需要插⼊⼀个新的值9的节点时，就需要做出如下变动。
通过⼀次左旋操作就将插⼊后的树重新变为平衡⼆叉树是最简单的情况了，实际应⽤场景中可能需要旋转多次。⾄此我们可以考虑⼀个问题，平衡⼆叉树的查效率还不错，实现也⾮常简单，相应的维护成本还能接受，为什么MySQL索引不直接使⽤平衡⼆叉树?
随着数据库中数据的增加，索引本⾝⼤⼩随之增加，不可能全部存储在内存中，因此索引往往以索引⽂件的形式存储的磁盘上。这样的话，索引查过程中就要产⽣磁盘I/O消耗，相对于内存存取，I/O存取的消耗要⾼⼏个数量级。可以想象⼀下⼀棵⼏百万节点的⼆叉树的深度是多少?如果将这么⼤深度的⼀颗⼆叉树放磁盘上，每读取⼀个节点，需要⼀次磁盘的I/O读取，整个查的耗时显然是不能够接受的。那么如何减少查过程中的I/O存取次数?
⼀种⾏之有效的解决⽅法是减少树的深度，将⼆叉树变为m叉树(多路搜索树)，⽽B+Tree就是⼀种多路搜索树。理解B+Tree时，只需要理解其最重要的两个特征即可：第⼀，所有的关键字(可以理解为数据)都存储在叶⼦节点(Leaf Page)，⾮叶⼦节点(Index Page)并不存储真正的数据，所有记录节点都是按键值⼤⼩顺序存放在同⼀层叶⼦节点上。其次，所有的叶⼦节点由指针连接。如下图为⾼度为2的简化了的B+Tree。
简化B+Tree
怎么理解这两个特征?MySQL将每个节点的⼤⼩设置为⼀个页的整数倍(原因下⽂会介绍)，也就是在节
点空间⼤⼩⼀定的情况下，每个节点可以存储更多的内结点，这样每个结点能索引的范围更⼤更精确。所有的叶⼦节点使⽤指针链接的好处是可以进⾏区间访问，⽐如上图中，如果查⼤于20⽽⼩于30的记录，只需要到节点20，就可以遍历指针依次到25、30。如果没有链接指针的话，就⽆法进⾏区间查。这也是MySQL使⽤B+Tree作为索引存储结构的重要原因。
MySQL为何将节点⼤⼩设置为页的整数倍，这就需要理解磁盘的存储原理。磁盘本⾝存取就⽐主存慢很多，在加上机械运动损耗(特别是普通的机械硬盘)，磁盘的存取速度往往是主存的⼏百万分之⼀，为了尽量减少磁盘I/O，磁盘往往不是严格按需读取，⽽是每次都会预读，即使只需要⼀个字节，磁盘也会从这个位置开始，顺序向后读取⼀定长度的数据放⼊内存，预读的长度⼀般为页的整数倍。
页是计算机管理存储器的逻辑块，硬件及OS往往将主存和磁盘存储区分割为连续的⼤⼩相等的块，每个存储块称为⼀页(许多OS中，页的⼤⼩通常为4K)。主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发⼀个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会到数据的起始位置并向后连续读取⼀页或⼏页载⼊内存中，然后⼀起返回，程序继续运⾏。
MySQL巧妙利⽤了磁盘预读原理，将⼀个节点的⼤⼩设为等于⼀个页，这样每个节点只需要⼀次I/O就可以完全载⼊。为了达到这个⽬的，每次新建节点时，直接申请⼀个页的空间，这样就保证⼀个节点物理上也存储在⼀个页⾥，加之计算机存储分配都是按页对齐的，就实现了读取⼀个节点只需⼀次I/O。
假设B+Tree的⾼度为h，⼀次检索最多需要h-1次I/O(根节点常驻内存)，复杂度O(h) = O(logmN)。实际应⽤场景中，M通常较⼤，常常超过100，因此树的⾼度⼀般都⽐较⼩，通常不超过3。
最后简单了解下B+Tree节点的操作，在整体上对索引的维护有⼀个⼤概的了解，虽然索引可以⼤⼤提⾼查询效率，但维护索引仍要花费很⼤的代价，因此合理的创建索引也就尤为重要。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。