mysql函数返回表_一文说尽MySQL优化原理

作者&＃xff1a;CHEN川
链接&＃xff1a;https://www.jianshu.com/p/d7665192aaaf

说起MySQL的查询优化&＃xff0c;相信大家收藏了一堆奇技淫巧&＃xff1a;不能使用SELECT *、不使用NULL字段、合理创建索引、为字段选择合适的数据类型..... 你是否真的理解这些优化技巧&＃xff1f;是否理解其背后的工作原理&＃xff1f;在实际场景下性能真有提升吗&＃xff1f;我想未必。因而理解这些优化建议背后的原理就尤为重要&＃xff0c;希望本文能让你重新审视这些优化建议&＃xff0c;并在实际业务场景下合理的运用。

MySQL逻辑架构

如果能在头脑中构建一幅MySQL各组件之间如何协同工作的架构图&＃xff0c;有助于深入理解MySQL服务器。下图展示了MySQL的逻辑架构图。

MySQL逻辑架构&＃xff0c;来自&＃xff1a;高性能MySQL

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

MySQL大多数核心服务均在中间这一层&＃xff0c;包括查询解析、分析、优化、缓存、内置函数(比如&＃xff1a;时间、数学、加密等函数)。所有的跨存储引擎的功能也在这一层实现&＃xff1a;存储过程、触发器、视图等。

最下层为存储引擎&＃xff0c;其负责MySQL中的数据存储和提取。和Linux下的文件系统类似&＃xff0c;每种存储引擎都有其优势和劣势。中间的服务层通过API与存储引擎通信&＃xff0c;这些API接口屏蔽了不同存储引擎间的差异。

MySQL查询过程

我们总是希望MySQL能够获得更高的查询性能&＃xff0c;最好的办法是弄清楚MySQL是如何优化和执行查询的。一旦理解了这一点&＃xff0c;就会发现&＃xff1a;很多的查询优化工作实际上就是遵循一些原则让MySQL的优化器能够按照预想的合理方式运行而已。

当向MySQL发送一个请求的时候&＃xff0c;MySQL到底做了些什么呢&＃xff1f;

MySQL查询过程

客户端/服务端通信协议

MySQL客户端/服务端通信协议是“半双工”的&＃xff1a;在任一时刻&＃xff0c;要么是服务器向客户端发送数据&＃xff0c;要么是客户端向服务器发送数据&＃xff0c;这两个动作不能同时发生。一旦一端开始发送消息&＃xff0c;另一端要接收完整个消息才能响应它&＃xff0c;所以我们无法也无须将一个消息切成小块独立发送&＃xff0c;也没有办法进行流量控制。

客户端用一个单独的数据包将查询请求发送给服务器&＃xff0c;所以当查询语句很长的时候&＃xff0c;需要设置max_allowed_packet参数。但是需要注意的是&＃xff0c;如果查询实在是太大&＃xff0c;服务端会拒绝接收更多数据并抛出异常。

与之相反的是&＃xff0c;服务器响应给用户的数据通常会很多&＃xff0c;由多个数据包组成。但是当服务器响应客户端请求时&＃xff0c;客户端必须完整的接收整个返回结果&＃xff0c;而不能简单的只取前面几条结果&＃xff0c;然后让服务器停止发送。因而在实际开发中&＃xff0c;尽量保持查询简单且只返回必需的数据&＃xff0c;减小通信间数据包的大小和数量是一个非常好的习惯&＃xff0c;这也是查询中尽量避免使用SELECT *以及加上LIMIT限制的原因之一。

查询缓存

在解析一个查询语句前&＃xff0c;如果查询缓存是打开的&＃xff0c;那么MySQL会检查这个查询语句是否命中查询缓存中的数据。如果当前查询恰好命中查询缓存&＃xff0c;在检查一次用户权限后直接返回缓存中的结果。这种情况下&＃xff0c;查询不会被解析&＃xff0c;也不会生成执行计划&＃xff0c;更不会执行。

MySQL将缓存存放在一个引用表(不要理解成table&＃xff0c;可以认为是类似于HashMap的数据结构)&＃xff0c;通过一个哈希值索引&＃xff0c;这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同(例如&＃xff1a;空格、注释)&＃xff0c;都会导致缓存不会命中。

如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表&＃xff0c;其查询结果
都不会被缓存。比如函数NOW()或者CURRENT_DATE()会因为不同的查询时间&＃xff0c;返回不同的查询结果&＃xff0c;再比如包含CURRENT_USER或者CONNECION_ID()的查询语句会因为不同的用户而返回不同的结果&＃xff0c;将这样的查询结果缓存起来没有任何的意义。

既然是缓存&＃xff0c;就会失效&＃xff0c;那查询缓存何时失效呢&＃xff1f;MySQL的查询缓存系统会跟踪查询中涉及的每个表&＃xff0c;如果这些表(数据或结构)发生变化&＃xff0c;那么和这张表相关的所有缓存数据都将失效。正因为如此&＃xff0c;在任何的写操作时&＃xff0c;MySQL必须将对应表的所有缓存都设置为失效。如果查询缓存非常大或者碎片很多&＃xff0c;这个操作就可能带来很大的系统消耗&＃xff0c;甚至导致系统僵死一会儿。而且查询缓存对系统的额外消耗也不仅仅在写操作&＃xff0c;读操作也不例外&＃xff1a;

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

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

基于此&＃xff0c;我们要知道并不是什么情况下查询缓存都会提高系统性能&＃xff0c;缓存和失效都会带来额外消耗&＃xff0c;只有当缓存带来的资源节约大于其本身消耗的资源时&＃xff0c;才会给系统带来性能提升。但要如何评估打开缓存是否能够带来性能提升是一件非常困难的事情&＃xff0c;也不在本文讨论的范畴内。如果系统确实存在一些性能问题&＃xff0c;可以尝试打开查询缓存&＃xff0c;并在数据库设计上做一些优化&＃xff0c;比如&＃xff1a;

1. 用多个小表代替一个大表&＃xff0c;注意不要过度设计

2. 批量插入代替循环单条插入

3. 合理控制缓存空间大小&＃xff0c;一般来说其大小设置为几十兆比较合适

4. 可以通过SQL_CACHE和SQL_NO_CACHE来控制某个查询语句是否需要进行缓存

最后的忠告是不要轻易打开查询缓存&＃xff0c;特别是写密集型应用。如果你实在是忍不住&＃xff0c;可以将query_cache_type设置为DEMAND&＃xff0c;这时只有加入SQL_CACHE的查询才会走缓存&＃xff0c;其他查询则不会&＃xff0c;这样可以非常自由地控制哪些查询需要被缓存。

当然查询缓存系统本身是非常复杂的&＃xff0c;这里讨论的也只是很小的一部分&＃xff0c;其他更深入的话题&＃xff0c;比如&＃xff1a;缓存是如何使用内存的&＃xff1f;如何控制内存的碎片化&＃xff1f;事务对查询缓存有何影响等等&＃xff0c;读者可以自行阅读相关资料&＃xff0c;这里权当抛砖引玉吧。

语法解析和预处理

MySQL通过关键字将SQL语句进行解析&＃xff0c;并生成一颗对应的解析树。这个过程解析器主要通过语法规则来验证和解析。比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。预处理则会根据MySQL规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等等。

查询优化

经过前面的步骤生成的语法树被认为是合法的了&＃xff0c;并且由优化器将其转化成查询计划。多数情况下&＃xff0c;一条查询可以有很多种执行方式&＃xff0c;最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划。

MySQL使用基于成本的优化器&＃xff0c;它尝试预测一个查询使用某种执行计划时的成本&＃xff0c;并选择其中成本最小的一个。在MySQL可以通过查询当前会话的last_query_cost的值来得到其计算当前查询的成本。

mysql> select * from t_message limit 10;
...省略结果集

mysql> show status like &＃39;last_query_cost&＃39;;
&＃43;-----------------&＃43;-------------&＃43;
| Variable_name   | Value       |
&＃43;-----------------&＃43;-------------&＃43;
| Last_query_cost | 6391.799000 |
&＃43;-----------------&＃43;-------------&＃43;


示例中的结果表示优化器认为大概需要做6391个数据页的随机查找才能完成上面的查询。这个结果是根据一些列的统计信息计算得来的&＃xff0c;这些统计信息包括&＃xff1a;每张表或者索引的页面个数、索引的基数、索引和数据行的长度、索引的分布情况等等。

有非常多的原因会导致MySQL选择错误的执行计划&＃xff0c;比如统计信息不准确、不会考虑不受其控制的操作成本(用户自定义函数、存储过程)、MySQL认为的最优跟我们想的不一样(我们希望执行时间尽可能短&＃xff0c;但MySQL值选择它认为成本小的&＃xff0c;但成本小并不意味着执行时间短)等等。

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

• 重新定义表的关联顺序(多张表关联查询时&＃xff0c;并不一定按照SQL中指定的顺序进行&＃xff0c;但有一些技巧可以指定关联顺序)

• 优化MIN()和MAX()函数(找某列的最小值&＃xff0c;如果该列有索引&＃xff0c;只需要查找B&＃43;Tree索引最左端&＃xff0c;反之则可以找到最大值&＃xff0c;具体原理见下文)

• 提前终止查询(比如&＃xff1a;使用Limit时&＃xff0c;查找到满足数量的结果集后会立即终止查询)

• 优化排序(在老版本MySQL会使用两次传输排序&＃xff0c;即先读取行指针和需要排序的字段在内存中对其排序&＃xff0c;然后再根据排序结果去读取数据行&＃xff0c;而新版本采用的是单次传输排序&＃xff0c;也就是一次读取所有的数据行&＃xff0c;然后根据给定的列排序。对于I/O密集型应用&＃xff0c;效率会高很多)

随着MySQL的不断发展&＃xff0c;优化器使用的优化策略也在不断的进化&＃xff0c;这里仅仅介绍几个非常常用且容易理解的优化策略&＃xff0c;其他的优化策略&＃xff0c;大家自行查阅吧。

查询执行引擎

在完成解析和优化阶段以后&＃xff0c;MySQL会生成对应的执行计划&＃xff0c;查询执行引擎根据执行计划给出的指令逐步执行得出结果。整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成&＃xff0c;这些接口被称为handler API。查询过程中的每一张表由一个handler实例表示。实际上&＃xff0c;MySQL在查询优化阶段就为每一张表创建了一个handler实例&＃xff0c;优化器可以根据这些实例的接口来获取表的相关信息&＃xff0c;包括表的所有列名、索引统计信息等。存储引擎接口提供了非常丰富的功能&＃xff0c;但其底层仅有几十个接口&＃xff0c;这些接口像搭积木一样完成了一次查询的大部分操作。

返回结果给客户端

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

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

结果集返回客户端是一个增量且逐步返回的过程。有可能MySQL在生成第一条结果时&＃xff0c;就开始向客户端逐步返回结果集了。这样服务端就无须存储太多结果而消耗过多内存&＃xff0c;也可以让客户端第一时间获得返回结果。需要注意的是&＃xff0c;结果集中的每一行都会以一个满足①中所描述的通信协议的数据包发送&＃xff0c;再通过TCP协议进行传输&＃xff0c;在传输过程中&＃xff0c;可能对MySQL的数据包进行缓存然后批量发送。

回头总结一下MySQL整个查询执行过程&＃xff0c;总的来说分为6个步骤&＃xff1a;

1. 客户端向MySQL服务器发送一条查询请求

2. 服务器首先检查查询缓存&＃xff0c;如果命中缓存&＃xff0c;则立刻返回存储在缓存中的结果。否则进入下一阶段

3. 服务器进行SQL解析、预处理、再由优化器生成对应的执行计划

4. MySQL根据执行计划&＃xff0c;调用存储引擎的API来执行查询

5. 将结果返回给客户端&＃xff0c;同时缓存查询结果

性能优化建议

看了这么多&＃xff0c;你可能会期待给出一些优化手段&＃xff0c;是的&＃xff0c;下面会从3个不同方面给出一些优化建议。但请等等&＃xff0c;还有一句忠告要先送给你&＃xff1a;不要听信你看到的关于优化的“绝对真理”&＃xff0c;包括本文所讨论的内容&＃xff0c;而应该是在实际的业务场景下通过测试来验证你关于执行计划以及响应时间的假设。

Scheme设计与数据类型优化

选择数据类型只要遵循小而简单的原则就好&＃xff0c;越小的数据类型通常会更快&＃xff0c;占用更少的磁盘、内存&＃xff0c;处理时需要的CPU周期也更少。越简单的数据类型在计算时需要更少的CPU周期&＃xff0c;比如&＃xff0c;整型就比字符操作代价低&＃xff0c;因而会使用整型来存储ip地址&＃xff0c;使用DATETIME来存储时间&＃xff0c;而不是使用字符串。

这里总结几个可能容易理解错误的技巧&＃xff1a;

1. 通常来说把可为NULL的列改为NOT NULL不会对性能提升有多少帮助&＃xff0c;只是如果计划在列上创建索引&＃xff0c;就应该将该列设置为NOT NULL。

2. 对整数类型指定宽度&＃xff0c;比如INT(11)&＃xff0c;没有任何卵用。INT使用32位(4个字节)存储空间&＃xff0c;那么它的表示范围已经确定&＃xff0c;所以INT(1)和INT(20)对于存储和计算是相同的。

3. UNSIGNED表示不允许负值&＃xff0c;大致可以使正数的上限提高一倍。比如TINYINT存储范围是-128 ~ 127&＃xff0c;而UNSIGNED TINYINT存储的范围却是0 - 255。

4. 通常来讲&＃xff0c;没有太大的必要使用DECIMAL数据类型。即使是在需要存储财务数据时&＃xff0c;仍然可以使用BIGINT。比如需要精确到万分之一&＃xff0c;那么可以将数据乘以一百万然后使用BIGINT存储。这样可以避免浮点数计算不准确和DECIMAL精确计算代价高的问题。

5. TIMESTAMP使用4个字节存储空间&＃xff0c;DATETIME使用8个字节存储空间。因而&＃xff0c;TIMESTAMP只能表示1970 - 2038年&＃xff0c;比DATETIME表示的范围小得多&＃xff0c;而且TIMESTAMP的值因时区不同而不同。

6. 大多数情况下没有使用枚举类型的必要&＃xff0c;其中一个缺点是枚举的字符串列表是固定的&＃xff0c;添加和删除字符串(枚举选项)必须使用ALTER TABLE(如果只只是在列表末尾追加元素&＃xff0c;不需要重建表)。

7. schema的列不要太多。原因是存储引擎的API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据&＃xff0c;然后在服务器层将缓冲内容解码成各个列&＃xff0c;这个转换过程的代价是非常高的。如果列太多而实际使用的列又很少的话&＃xff0c;有可能会导致CPU占用过高。

8. 大表ALTER TABLE非常耗时&＃xff0c;MySQL执行大部分修改表结果操作的方法是用新的结构创建一个张空表&＃xff0c;从旧表中查出所有的数据插入新表&＃xff0c;然后再删除旧表。尤其当内存不足而表又很大&＃xff0c;而且还有很大索引的情况下&＃xff0c;耗时更久。当然有一些奇技淫巧可以解决这个问题&＃xff0c;有兴趣可自行查阅。

创建高性能索引

索引是提高MySQL查询性能的一个重要途径&＃xff0c;但过多的索引可能会导致过高的磁盘使用率以及过高的内存占用&＃xff0c;从而影响应用程序的整体性能。应当尽量避免事后才想起添加索引&＃xff0c;因为事后可能需要监控大量的SQL才能定位到问题所在&＃xff0c;而且添加索引的时间肯定是远大于初始添加索引所需要的时间&＃xff0c;可见索引的添加也是非常有技术含量的。

接下来将向你展示一系列创建高性能索引的策略&＃xff0c;以及每条策略其背后的工作原理。但在此之前&＃xff0c;先了解与索引相关的一些算法和数据结构&＃xff0c;将有助于更好的理解后文的内容。

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

通常我们所说的索引是指B-Tree索引&＃xff0c;它是目前关系型数据库中查找数据最为常用和有效的索引&＃xff0c;大多数存储引擎都支持这种索引。使用B-Tree这个术语&＃xff0c;是因为MySQL在CREATE TABLE或其它语句中使用了这个关键字&＃xff0c;但实际上不同的存储引擎可能使用不同的数据结构&＃xff0c;比如InnoDB就是使用的B&＃43;Tree。

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

在介绍B&＃43;Tree前&＃xff0c;先了解一下二叉查找树&＃xff0c;它是一种经典的数据结构&＃xff0c;其左子树的值总是小于根的值&＃xff0c;右子树的值总是大于根的值&＃xff0c;如下图①。如果要在这课树中查找值为5的记录&＃xff0c;其大致流程&＃xff1a;先找到根&＃xff0c;其值为6&＃xff0c;大于5&＃xff0c;所以查找左子树&＃xff0c;找到3&＃xff0c;而5大于3&＃xff0c;接着找3的右子树&＃xff0c;总共找了3次。同样的方法&＃xff0c;如果查找值为8的记录&＃xff0c;也需要查找3次。所以二叉查找树的平均查找次数为(3 &＃43; 3 &＃43; 3 &＃43; 2 &＃43; 2 &＃43; 1) / 6 &＃61; 2.3次&＃xff0c;而顺序查找的话&＃xff0c;查找值为2的记录&＃xff0c;仅需要1次&＃xff0c;但查找值为8的记录则需要6次&＃xff0c;所以顺序查找的平均查找次数为&＃xff1a;(1 &＃43; 2 &＃43; 3 &＃43; 4 &＃43; 5 &＃43; 6) / 6 &＃61; 3.3次&＃xff0c;因此大多数情况下二叉查找树的平均查找速度比顺序查找要快。

二叉查找树和平衡二叉树

由于二叉查找树可以任意构造&＃xff0c;同样的值&＃xff0c;可以构造出如图②的二叉查找树&＃xff0c;显然这棵二叉树的查询效率和顺序查找差不多。若想二叉查找数的查询性能最高&＃xff0c;需要这棵二叉查找树是平衡的&＃xff0c;也即平衡二叉树(AVL树)。

平衡二叉树首先需要符合二叉查找树的定义&＃xff0c;其次必须满足任何节点的两个子树的高度差不能大于1。显然图②不满足平衡二叉树的定义&＃xff0c;而图①是一课平衡二叉树。平衡二叉树的查找性能是比较高的(性能最好的是最优二叉树)&＃xff0c;查询性能越好&＃xff0c;维护的成本就越大。比如图①的平衡二叉树&＃xff0c;当用户需要插入一个新的值9的节点时&＃xff0c;就需要做出如下变动。

平衡二叉树旋转

通过一次左旋操作就将插入后的树重新变为平衡二叉树是最简单的情况了&＃xff0c;实际应用场景中可能需要旋转多次。至此我们可以考虑一个问题&＃xff0c;平衡二叉树的查找效率还不错&＃xff0c;实现也非常简单&＃xff0c;相应的维护成本还能接受&＃xff0c;为什么MySQL索引不直接使用平衡二叉树&＃xff1f;

随着数据库中数据的增加&＃xff0c;索引本身大小随之增加&＃xff0c;不可能全部存储在内存中&＃xff0c;因此索引往往以索引文件的形式存储的磁盘上。这样的话&＃xff0c;索引查找过程中就要产生磁盘I/O消耗&＃xff0c;相对于内存存取&＃xff0c;I/O存取的消耗要高几个数量级。可以想象一下一棵几百万节点的二叉树的深度是多少&＃xff1f;如果将这么大深度的一颗二叉树放磁盘上&＃xff0c;每读取一个节点&＃xff0c;需要一次磁盘的I/O读取&＃xff0c;整个查找的耗时显然是不能够接受的。那么如何减少查找过程中的I/O存取次数&＃xff1f;

一种行之有效的解决方法是减少树的深度&＃xff0c;将二叉树变为m叉树(多路搜索树)&＃xff0c;而B&＃43;Tree就是一种多路搜索树。理解B&＃43;Tree时&＃xff0c;只需要理解其最重要的两个特征即可&＃xff1a;第一&＃xff0c;所有的关键字(可以理解为数据)都存储在叶子节点(Leaf Page)&＃xff0c;非叶子节点(Index Page)并不存储真正的数据&＃xff0c;所有记录节点都是按键值大小顺序存放在同一层叶子节点上。其次&＃xff0c;所有的叶子节点由指针连接。如下图为高度为2的简化了的B&＃43;Tree。

简化B&＃43;Tree

怎么理解这两个特征&＃xff1f;MySQL将每个节点的大小设置为一个页的整数倍(原因下文会介绍)&＃xff0c;也就是在节点空间大小一定的情况下&＃xff0c;每个节点可以存储更多的内结点&＃xff0c;这样每个结点能索引的范围更大更精确。所有的叶子节点使用指针链接的好处是可以进行区间访问&＃xff0c;比如上图中&＃xff0c;如果查找大于20而小于30的记录&＃xff0c;只需要找到节点20&＃xff0c;就可以遍历指针依次找到25、30。如果没有链接指针的话&＃xff0c;就无法进行区间查找。这也是MySQL使用B&＃43;Tree作为索引存储结构的重要原因。

MySQL为何将节点大小设置为页的整数倍&＃xff0c;这就需要理解磁盘的存储原理。磁盘本身存取就比主存慢很多&＃xff0c;在加上机械运动损耗(特别是普通的机械硬盘)&＃xff0c;磁盘的存取速度往往是主存的几百万分之一&＃xff0c;为了尽量减少磁盘I/O&＃xff0c;磁盘往往不是严格按需读取&＃xff0c;而是每次都会预读&＃xff0c;即使只需要一个字节&＃xff0c;磁盘也会从这个位置开始&＃xff0c;顺序向后读取一定长度的数据放入内存&＃xff0c;预读的长度一般为页的整数倍。

页是计算机管理存储器的逻辑块&＃xff0c;硬件及OS往往将主存和磁盘存储区分割为连续的大小相等的块&＃xff0c;每个存储块称为一页(许多OS中&＃xff0c;页的大小通常为4K)。主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时&＃xff0c;会触发一个缺页异常&＃xff0c;此时系统会向磁盘发出读盘信号&＃xff0c;磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中&＃xff0c;然后一起返回&＃xff0c;程序继续运行。

MySQL巧妙利用了磁盘预读原理&＃xff0c;将一个节点的大小设为等于一个页&＃xff0c;这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的&＃xff0c;每次新建节点时&＃xff0c;直接申请一个页的空间&＃xff0c;这样就保证一个节点物理上也存储在一个页里&＃xff0c;加之计算机存储分配都是按页对齐的&＃xff0c;就实现了读取一个节点只需一次I/O。假设B&＃43;Tree的高度为h&＃xff0c;一次检索最多需要h-1次I/O(根节点常驻内存)&＃xff0c;复杂度O(h) &＃61; O(logmN)。实际应用场景中&＃xff0c;M通常较大&＃xff0c;常常超过100&＃xff0c;因此树的高度一般都比较小&＃xff0c;通常不超过3。

最后简单了解下B&＃43;Tree节点的操作&＃xff0c;在整体上对索引的维护有一个大概的了解&＃xff0c;虽然索引可以大大提高查询效率&＃xff0c;但维护索引仍要花费很大的代价&＃xff0c;因此合理的创建索引也就尤为重要。

仍以上面的树为例&＃xff0c;我们假设每个节点只能存储4个内节点。首先要插入第一个节点28&＃xff0c;如下图所示。

leaf page和index page都没有满

接着插入下一个节点70&＃xff0c;在Index Page中查询后得知应该插入到50 - 70之间的叶子节点&＃xff0c;但叶子节点已满&＃xff0c;这时候就需要进行也分裂的操作&＃xff0c;当前的叶子节点起点为50&＃xff0c;所以根据中间值来拆分叶子节点&＃xff0c;如下图所示。

Leaf Page拆分

最后插入一个节点95&＃xff0c;这时候Index Page和Leaf Page都满了&＃xff0c;就需要做两次拆分&＃xff0c;如下图所示。

Leaf Page与Index Page拆分

拆分后最终形成了这样一颗树。

最终树

B&＃43;Tree为了保持平衡&＃xff0c;对于新插入的值需要做大量的拆分页操作&＃xff0c;而页的拆分需要I/O操作&＃xff0c;为了尽可能的减少页的拆分操作&＃xff0c;B&＃43;Tree也提供了类似于平衡二叉树的旋转功能。当Leaf Page已满但其左右兄弟节点没有满的情况下&＃xff0c;B&＃43;Tree并不急于去做拆分操作&＃xff0c;而是将记录移到当前所在页的兄弟节点上。通常情况下&＃xff0c;左兄弟会被先检查用来做旋转操作。就比如上面第二个示例&＃xff0c;当插入70的时候&＃xff0c;并不会去做页拆分&＃xff0c;而是左旋操作。

左旋操作

通过旋转操作可以最大限度的减少页分裂&＃xff0c;从而减少索引维护过程中的磁盘的I/O操作&＃xff0c;也提高索引维护效率。需要注意的是&＃xff0c;删除节点跟插入节点类似&＃xff0c;仍然需要旋转和拆分操作&＃xff0c;这里就不再说明。

高性能策略

通过上文&＃xff0c;相信你对B&＃43;Tree的数据结构已经有了大致的了解&＃xff0c;但MySQL中索引是如何组织数据的存储呢&＃xff1f;以一个简单的示例来说明&＃xff0c;假如有如下数据表&＃xff1a;

CREATE TABLE People(
   last_name varchar(50) not null,
   first_name varchar(50) not null,
   dob date not null,
   gender enum(&＃96;m&＃96;,&＃96;f&＃96;) not null,
   key(last_name,first_name,dob)
);

对于表中每一行数据&＃xff0c;索引中包含了last_name、first_name、dob列的值&＃xff0c;下图展示了索引是如何组织数据存储的。

索引如何组织数据存储&＃xff0c;来自&＃xff1a;高性能MySQL

可以看到&＃xff0c;索引首先根据第一个字段来排列顺序&＃xff0c;当名字相同时&＃xff0c;则根据第三个字段&＃xff0c;即出生日期来排序&＃xff0c;正是因为这个原因&＃xff0c;才有了索引的“最左原则”。

1、MySQL不会使用索引的情况&＃xff1a;非独立的列

“独立的列”是指索引列不能是表达式的一部分&＃xff0c;也不能是函数的参数。比如&＃xff1a;

select * from where id &＃43; 1 &＃61; 5

我们很容易看出其等价于 id &＃61; 4&＃xff0c;但是MySQL无法自动解析这个表达式&＃xff0c;使用函数是同样的道理。

2、前缀索引

如果列很长&＃xff0c;通常可以索引开始的部分字符&＃xff0c;这样可以有效节约索引空间&＃xff0c;从而提高索引效率。

3、多列索引和索引顺序

在多数情况下&＃xff0c;在多个列上建立独立的索引并不能提高查询性能。理由非常简单&＃xff0c;MySQL不知道选择哪个索引的查询效率更好&＃xff0c;所以在老版本&＃xff0c;比如MySQL5.0之前就会随便选择一个列的索引&＃xff0c;而新的版本会采用合并索引的策略。举个简单的例子&＃xff0c;在一张电影演员表中&＃xff0c;在actor_id和film_id两个列上都建立了独立的索引&＃xff0c;然后有如下查询&＃xff1a;

select film_id,actor_id from film_actor where actor_id &＃61; 1 or film_id &＃61; 1

老版本的MySQL会随机选择一个索引&＃xff0c;但新版本做如下的优化&＃xff1a;

select film_id,actor_id from film_actor where actor_id &＃61; 1  
union all
select film_id,actor_id from film_actor where film_id &＃61; 1 and actor_id <> 1

• 当出现多个索引做相交操作时(多个AND条件)&＃xff0c;通常来说一个包含所有相关列的索引要优于多个独立索引。

• 当出现多个索引做联合操作时(多个OR条件)&＃xff0c;对结果集的合并、排序等操作需要耗费大量的CPU和内存资源&＃xff0c;特别是当其中的某些索引的选择性不高&＃xff0c;需要返回合并大量数据时&＃xff0c;查询成本更高。所以这种情况下还不如走全表扫描。

因此explain时如果发现有索引合并(Extra字段出现Using union)&＃xff0c;应该好好检查一下查询和表结构是不是已经是最优的&＃xff0c;如果查询和表都没有问题&＃xff0c;那只能说明索引建的非常糟糕&＃xff0c;应当慎重考虑索引是否合适&＃xff0c;有可能一个包含所有相关列的多列索引更适合。

前面我们提到过索引如何组织数据存储的&＃xff0c;从图中可以看到多列索引时&＃xff0c;索引的顺序对于查询是至关重要的&＃xff0c;很明显应该把选择性更高的字段放到索引的前面&＃xff0c;这样通过第一个字段就可以过滤掉大多数不符合条件的数据。

索引选择性是指不重复的索引值和数据表的总记录数的比值&＃xff0c;选择性越高查询效率越高&＃xff0c;因为选择性越高的索引可以让MySQL在查询时过滤掉更多的行。唯一索引的选择性是1&＃xff0c;这是最好的索引选择性&＃xff0c;性能也是最好的。

理解索引选择性的概念后&＃xff0c;就不难确定哪个字段的选择性较高了&＃xff0c;查一下就知道了&＃xff0c;比如&＃xff1a;

SELECT * FROM payment where staff_id &＃61; 2 and customer_id &＃61; 584

是应该创建(staff_id,customer_id)的索引还是应该颠倒一下顺序&＃xff1f;执行下面的查询&＃xff0c;哪个字段的选择性更接近1就把哪个字段索引前面就好。

select count(distinct staff_id)/count(*) as staff_id_selectivity,
      count(distinct customer_id)/count(*) as customer_id_selectivity,
      count(*) from payment

多数情况下使用这个原则没有任何问题&＃xff0c;但仍然注意你的数据中是否存在一些特殊情况。举个简单的例子&＃xff0c;比如要查询某个用户组下有过交易的用户信息&＃xff1a;

select user_id from trade where user_group_id &＃61; 1 and trade_amount > 0

MySQL为这个查询选择了索引(user_group_id,trade_amount)&＃xff0c;如果不考虑特殊情况&＃xff0c;这看起来没有任何问题&＃xff0c;但实际情况是这张表的大多数数据都是从老系统中迁移过来的&＃xff0c;由于新老系统的数据不兼容&＃xff0c;所以就给老系统迁移过来的数据赋予了一个默认的用户组。这种情况下&＃xff0c;通过索引扫描的行数跟全表扫描基本没什么区别&＃xff0c;索引也就起不到任何作用。

推广开来说&＃xff0c;经验法则和推论在多数情况下是有用的&＃xff0c;可以指导我们开发和设计&＃xff0c;但实际情况往往会更复杂&＃xff0c;实际业务场景下的某些特殊情况可能会摧毁你的整个设计。

4、避免多个范围条件

实际开发中&＃xff0c;我们会经常使用多个范围条件&＃xff0c;比如想查询某个时间段内登录过的用户&＃xff1a;

select user.* from user where login_time > &＃39;2017-04-01&＃39; and age between 18 and 30;

这个查询有一个问题&＃xff1a;它有两个范围条件&＃xff0c;login_time列和age列&＃xff0c;MySQL可以使用login_time列的索引或者age列的索引&＃xff0c;但无法同时使用它们。

5、覆盖索引

如果一个索引包含或者说覆盖所有需要查询的字段的值&＃xff0c;那么就没有必要再回表查询&＃xff0c;这就称为覆盖索引。覆盖索引是非常有用的工具&＃xff0c;可以极大的提高性能&＃xff0c;因为查询只需要扫描索引会带来许多好处&＃xff1a;

• 索引条目远小于数据行大小&＃xff0c;如果只读取索引&＃xff0c;极大减少数据访问量

• 索引是有按照列值顺序存储的&＃xff0c;对于I/O密集型的范围查询要比随机从磁盘读取每一行数据的IO要少的多

6、使用索引扫描来排序

MySQL有两种方式可以生产有序的结果集&＃xff0c;其一是对结果集进行排序的操作&＃xff0c;其二是按照索引顺序扫描得出的结果自然是有序的。如果explain的结果中type列的值为index表示使用了索引扫描来做排序。

扫描索引本身很快&＃xff0c;因为只需要从一条索引记录移动到相邻的下一条记录。但如果索引本身不能覆盖所有需要查询的列&＃xff0c;那么就不得不每扫描一条索引记录就回表查询一次对应的行。这个读取操作基本上是随机I/O&＃xff0c;因此按照索引顺序读取数据的速度通常要比顺序地全表扫描要慢。

在设计索引时&＃xff0c;如果一个索引既能够满足排序&＃xff0c;又满足查询&＃xff0c;是最好的。

只有当索引的列顺序和ORDER BY子句的顺序完全一致&＃xff0c;并且所有列的排序方向也一样时&＃xff0c;才能够使用索引来对结果做排序。如果查询需要关联多张表&＃xff0c;则只有ORDER BY子句引用的字段全部为第一张表时&＃xff0c;才能使用索引做排序。ORDER BY子句和查询的限制是一样的&＃xff0c;都要满足最左前缀的要求(有一种情况例外&＃xff0c;就是最左的列被指定为常数&＃xff0c;下面是一个简单的示例)&＃xff0c;其他情况下都需要执行排序操作&＃xff0c;而无法利用索引排序。

// 最左列为常数&＃xff0c;索引&＃xff1a;(date,staff_id,customer_id)
select  staff_id,customer_id from demo where date &＃61; &＃39;2015-06-01&＃39; order by staff_id,customer_id

7、冗余和重复索引

冗余索引是指在相同的列上按照相同的顺序创建的相同类型的索引&＃xff0c;应当尽量避免这种索引&＃xff0c;发现后立即删除。比如有一个索引(A,B)&＃xff0c;再创建索引(A)就是冗余索引。冗余索引经常发生在为表添加新索引时&＃xff0c;比如有人新建了索引(A,B)&＃xff0c;但这个索引不是扩展已有的索引(A)。

大多数情况下都应该尽量扩展已有的索引而不是创建新索引。但有极少情况下出现性能方面的考虑需要冗余索引&＃xff0c;比如扩展已有索引而导致其变得过大&＃xff0c;从而影响到其他使用该索引的查询。

8、删除长期未使用的索引

定期删除一些长时间未使用过的索引是一个非常好的习惯。

关于索引这个话题打算就此打住&＃xff0c;最后要说一句&＃xff0c;索引并不总是最好的工具&＃xff0c;只有当索引帮助提高查询速度带来的好处大于其带来的额外工作时&＃xff0c;索引才是有效的。对于非常小的表&＃xff0c;简单的全表扫描更高效。对于中到大型的表&＃xff0c;索引就非常有效。对于超大型的表&＃xff0c;建立和维护索引的代价随之增长&＃xff0c;这时候其他技术也许更有效&＃xff0c;比如分区表。最后的最后&＃xff0c;explain后再提测是一种美德。

特定类型查询优化

优化COUNT()查询

COUNT()可能是被大家误解最多的函数了&＃xff0c;它有两种不同的作用&＃xff0c;其一是统计某个列值的数量&＃xff0c;其二是统计行数。统计列值时&＃xff0c;要求列值是非空的&＃xff0c;它不会统计NULL。如果确认括号中的表达式不可能为空时&＃xff0c;实际上就是在统计行数。最简单的就是当使用COUNT(*)时&＃xff0c;并不是我们所想象的那样扩展成所有的列&＃xff0c;实际上&＃xff0c;它会忽略所有的列而直接统计行数。

我们最常见的误解也就在这儿&＃xff0c;在括号内指定了一列却希望统计结果是行数&＃xff0c;而且还常常误以为前者的性能会更好。但实际并非这样&＃xff0c;如果要统计行数&＃xff0c;直接使用COUNT(*)&＃xff0c;意义清晰&＃xff0c;且性能更好。

有时候某些业务场景并不需要完全精确的COUNT值&＃xff0c;可以用近似值来代替&＃xff0c;EXPLAIN出来的行数就是一个不错的近似值&＃xff0c;而且执行EXPLAIN并不需要真正地去执行查询&＃xff0c;所以成本非常低。通常来说&＃xff0c;执行COUNT()都需要扫描大量的行才能获取到精确的数据&＃xff0c;因此很难优化&＃xff0c;MySQL层面还能做得也就只有覆盖索引了。如果不还能解决问题&＃xff0c;只有从架构层面解决了&＃xff0c;比如添加汇总表&＃xff0c;或者使用redis这样的外部缓存系统。

优化关联查询

在大数据场景下&＃xff0c;表与表之间通过一个冗余字段来关联&＃xff0c;要比直接使用JOIN有更好的性能。如果确实需要使用关联查询的情况下&＃xff0c;需要特别注意的是&＃xff1a;

• 确保ON和USING字句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A和表B用列c关联的时候&＃xff0c;如果优化器关联的顺序是A、B&＃xff0c;那么就不需要在A表的对应列上创建索引。没有用到的索引会带来额外的负担&＃xff0c;一般来说&＃xff0c;除非有其他理由&＃xff0c;只需要在关联顺序中的第二张表的相应列上创建索引(具体原因下文分析)。

• 确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列&＃xff0c;这样MySQL才有可能使用索引来优化。

要理解优化关联查询的第一个技巧&＃xff0c;就需要理解MySQL是如何执行关联查询的。当前MySQL关联执行的策略非常简单&＃xff0c;它对任何的关联都执行嵌套循环关联操作&＃xff0c;即先在一个表中循环取出单条数据&＃xff0c;然后在嵌套循环到下一个表中寻找匹配的行&＃xff0c;依次下去&＃xff0c;直到找到所有表中匹配的行为为止。然后根据各个表匹配的行&＃xff0c;返回查询中需要的各个列。

太抽象了&＃xff1f;以上面的示例来说明&＃xff0c;比如有这样的一个查询&＃xff1a;

SELECT A.xx,B.yy
FROM A INNER JOIN B USING(c)
WHERE A.xx IN (5,6)

假设MySQL按照查询中的关联顺序A、B来进行关联操作&＃xff0c;那么可以用下面的伪代码表示MySQL如何完成这个查询&＃xff1a;

outer_iterator &＃61; SELECT A.xx,A.c FROM A WHERE A.xx IN (5,6);
outer_row &＃61; outer_iterator.next;
while(outer_row) {
   inner_iterator &＃61; SELECT B.yy FROM B WHERE B.c &＃61; outer_row.c;
   inner_row &＃61; inner_iterator.next;
   while(inner_row) {
       output[inner_row.yy,outer_row.xx];
       inner_row &＃61; inner_iterator.next;
   }
   outer_row &＃61; outer_iterator.next;
}

可以看到&＃xff0c;最外层的查询是根据A.xx列来查询的&＃xff0c;A.c上如果有索引的话&＃xff0c;整个关联查询也不会使用。再看内层的查询&＃xff0c;很明显B.c上如果有索引的话&＃xff0c;能够加速查询&＃xff0c;因此只需要在关联顺序中的第二张表的相应列上创建索引即可。

优化LIMIT分页

当需要分页操作时&＃xff0c;通常会使用LIMIT加上偏移量的办法实现&＃xff0c;同时加上合适的ORDER BY字句。如果有对应的索引&＃xff0c;通常效率会不错&＃xff0c;否则&＃xff0c;MySQL需要做大量的文件排序操作。

一个常见的问题是当偏移量非常大的时候&＃xff0c;比如&＃xff1a;LIMIT 10000 20这样的查询&＃xff0c;MySQL需要查询10020条记录然后只返回20条记录&＃xff0c;前面的10000条都将被抛弃&＃xff0c;这样的代价非常高。

优化这种查询一个最简单的办法就是尽可能的使用覆盖索引扫描&＃xff0c;而不是查询所有的列。然后根据需要做一次关联查询再返回所有的列。对于偏移量很大时&＃xff0c;这样做的效率会提升非常大。考虑下面的查询&＃xff1a;

SELECT film_id,description FROM film ORDER BY title LIMIT 50,5;

如果这张表非常大&＃xff0c;那么这个查询最好改成下面的样子&＃xff1a;

SELECT film.film_id,film.description
FROM film INNER JOIN (
   SELECT film_id FROM film ORDER BY title LIMIT 50,5
) AS tmp USING(film_id);

这里的延迟关联将大大提升查询效率&＃xff0c;让MySQL扫描尽可能少的页面&＃xff0c;获取需要访问的记录后在根据关联列回原表查询所需要的列。

有时候如果可以使用书签记录上次取数据的位置&＃xff0c;那么下次就可以直接从该书签记录的位置开始扫描&＃xff0c;这样就可以避免使用OFFSET&＃xff0c;比如下面的查询&＃xff1a;

SELECT id FROM t LIMIT 10000, 10;
改为&＃xff1a;
SELECT id FROM t WHERE id > 10000 LIMIT 10;

其他优化的办法还包括使用预先计算的汇总表&＃xff0c;或者关联到一个冗余表&＃xff0c;冗余表中只包含主键列和需要做排序的列。

优化UNION

MySQL处理UNION的策略是先创建临时表&＃xff0c;然后再把各个查询结果插入到临时表中&＃xff0c;最后再来做查询。因此很多优化策略在UNION查询中都没有办法很好的时候。经常需要手动将WHERE、LIMIT、ORDER BY等字句“下推”到各个子查询中&＃xff0c;以便优化器可以充分利用这些条件先优化。

除非确实需要服务器去重&＃xff0c;否则就一定要使用UNION ALL&＃xff0c;如果没有ALL关键字&＃xff0c;MySQL会给临时表加上DISTINCT选项&＃xff0c;这会导致整个临时表的数据做唯一性检查&＃xff0c;这样做的代价非常高。当然即使使用ALL关键字&＃xff0c;MySQL总是将结果放入临时表&＃xff0c;然后再读出&＃xff0c;再返回给客户端。虽然很多时候没有这个必要&＃xff0c;比如有时候可以直接把每个子查询的结果返回给客户端。

结语

理解查询是如何执行以及时间都消耗在哪些地方&＃xff0c;再加上一些优化过程的知识&＃xff0c;可以帮助大家更好的理解MySQL&＃xff0c;理解常见优化技巧背后的原理。希望本文中的原理、示例能够帮助大家更好的将理论和实践联系起来&＃xff0c;更多的将理论知识运用到实践中。

其他也没啥说的了&＃xff0c;给大家留两个思考题吧&＃xff0c;可以在脑袋里想想答案&＃xff0c;这也是大家经常挂在嘴边的&＃xff0c;但很少有人会思考为什么&＃xff1f;

1. 有非常多的程序员在分享时都会抛出这样一个观点&＃xff1a;尽可能不要使用存储过程&＃xff0c;存储过程非常不容易维护&＃xff0c;也会增加使用成本&＃xff0c;应该把业务逻辑放到客户端。既然客户端都能干这些事&＃xff0c;那为什么还要存储过程&＃xff1f;

2. JOIN本身也挺方便的&＃xff0c;直接查询就好了&＃xff0c;为什么还需要视图呢&＃xff1f;

---END---

更文不易&＃xff0c;点个“在看”支持一下?