示例中的结果表示优化器认为大概需要做6391个数据页的随机查找才能完成上面的查询。这个结果是根据一些列的统计信息计算得来的,这些统计信息包括:每张表或者索引的页面个数、索引的基数、索引和数据行的长度、索引的分布情况等等。
有非常多的原因会导致MySQL选择错误的执行计划,比如统计信息不准确、不会考虑不受其控制的操作成本(用户自定义函数、存储过程)、MySQL认为的最优跟我们想的不一样(我们希望执行时间尽可能短,但MySQL值选择它认为成本小的,但成本小并不意味着执行时间短)等等。
MySQL的查询优化器是一个非常复杂的部件,它使用了非常多的优化策略来生成一个最优的执行计划:
- 重新定义表的关联顺序(多张表关联查询时,并不一定按照SQL中指定的顺序进行,但有一些技巧可以指定关联顺序)
- 优化
MIN()
和MAX()
函数(找某列的最小值,如果该列有索引,只需要查找B+Tree索引最左端,反之则可以找到最大值,具体原理见下文) - 提前终止查询(比如:使用Limit时,查找到满足数量的结果集后会立即终止查询)
- 优化排序(在老版本MySQL会使用两次传输排序,即先读取行指针和需要排序的字段在内存中对其排序,然后再根据排序结果去读取数据行,而新版本采用的是单次传输排序,也就是一次读取所有的数据行,然后根据给定的列排序。对于I/O密集型应用,效率会高很多)
随着MySQL的不断发展,优化器使用的优化策略也在不断的进化,这里仅仅介绍几个非常常用且容易理解的优化策略,其他的优化策略,大家自行查阅吧。
查询执行引擎
在完成解析和优化阶段以后,MySQL会生成对应的执行计划,查询执行引擎根据执行计划给出的指令逐步执行得出结果。整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成,这些接口被称为handler API
。查询过程中的每一张表由一个handler
实例表示。实际上,MySQL在查询优化阶段就为每一张表创建了一个handler
实例,优化器可以根据这些实例的接口来获取表的相关信息,包括表的所有列名、索引统计信息等。存储引擎接口提供了非常丰富的功能,但其底层仅有几十个接口,这些接口像搭积木一样完成了一次查询的大部分操作。
返回结果给客户端
查询执行的最后一个阶段就是将结果返回给客户端。即使查询不到数据,MySQL仍然会返回这个查询的相关信息,比如改查询影响到的行数以及执行时间等等。
如果查询缓存被打开且这个查询可以被缓存,MySQL也会将结果存放到缓存中。
结果集返回客户端是一个增量且逐步返回的过程。有可能MySQL在生成第一条结果时,就开始向客户端逐步返回结果集了。这样服务端就无须存储太多结果而消耗过多内存,也可以让客户端第一时间获得返回结果。需要注意的是,结果集中的每一行都会以一个满足①中所描述的通信协议的数据包发送,再通过TCP协议进行传输,在传输过程中,可能对MySQL的数据包进行缓存然后批量发送。
回头总结一下MySQL整个查询执行过程,总的来说分为6个步骤:
- 客户端向MySQL服务器发送一条查询请求
- 服务器首先检查查询缓存,如果命中缓存,则立刻返回存储在缓存中的结果。否则进入下一阶段
- 服务器进行SQL解析、预处理、再由优化器生成对应的执行计划
- MySQL根据执行计划,调用存储引擎的API来执行查询
- 将结果返回给客户端,同时缓存查询结果
性能优化建议
看了这么多,你可能会期待给出一些优化手段,是的,下面会从3个不同方面给出一些优化建议。但请等等,还有一句忠告要先送给你:不要听信你看到的关于优化的“绝对真理”,包括本文所讨论的内容,而应该是在实际的业务场景下通过测试来验证你关于执行计划以及响应时间的假设。
Scheme设计与数据类型优化
选择数据类型只要遵循小而简单的原则就好,越小的数据类型通常会更快,占用更少的磁盘、内存,处理时需要的CPU周期也更少。越简单的数据类型在计算时需要更少的CPU周期,比如,整型就比字符操作代价低,因而会使用整型来存储ip地址,使用DATETIME
来存储时间,而不是使用字符串。
这里总结几个可能容易理解错误的技巧:
- 通常来说把可为
NULL
的列改为NOT NULL
不会对性能提升有多少帮助,只是如果计划在列上创建索引,就应该将该列设置为NOT NULL
。 - 对整数类型指定宽度,比如
INT(11)
,没有任何卵用。INT
使用16为存储空间,那么它的表示范围已经确定,所以INT(1)
和INT(20)
对于存储和计算是相同的。 UNSIGNED
表示不允许负值,大致可以使正数的上限提高一倍。比如TINYINT
存储范围是-128 ~ 127,而UNSIGNED TINYINT
存储的范围却是0 – 255。- 通常来讲,没有太大的必要使用
DECIMAL
数据类型。即使是在需要存储财务数据时,仍然可以使用BIGINT
。比如需要精确到万分之一,那么可以将数据乘以一百万然后使用BIGINT
存储。这样可以避免浮点数计算不准确和DECIMAL
精确计算代价高的问题。 TIMESTAMP
使用4个字节存储空间,DATETIME
使用8个字节存储空间。因而,TIMESTAMP
只能表示1970 – 2038年,比DATETIME
表示的范围小得多,而且TIMESTAMP
的值因时区不同而不同。- 大多数情况下没有使用枚举类型的必要,其中一个缺点是枚举的字符串列表是固定的,添加和删除字符串(枚举选项)必须使用
ALTER TABLE
(如果只只是在列表末尾追加元素,不需要重建表)。 - schema的列不要太多。原因是存储引擎的API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据,然后在服务器层将缓冲内容解码成各个列,这个转换过程的代价是非常高的。如果列太多而实际使用的列又很少的话,有可能会导致CPU占用过高。
- 大表
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次,因为大多数情况下二叉查找树的平均查找速度比顺序查找要快。