MySQL查询性能的优化涉及多个方面,其中包括库表结构、建立合理的索引、设计合理的查询。库表结构包括如何设计表之间的关联、表字段的数据类型等。这需要依据具体的场景进行设计。如下我们从数据库的索引和查询语句的设计两个角度介绍如何提高MySQL查询性能。
索引是存储引擎中用于快速找到记录的一种数据结构。索引有多种分类方式,按照存储方式可以分为:聚簇索引和非聚簇索引;按照数据的唯一性可以分为:唯一索引和非唯一索引;按照列个数可以分为:单列索引和多列索引等。索引也有多种类型:B-Tree索引、Hash索引、空间数据索引(R-Tree)、全文索引等。
在利用B-Tree索引进行查询的过程中,有几点注意事项,我们以表A进行说明。其中表A的定义如下:
create table A(id int auto_increment primary key, name varchar(10), age tinyint, sex enum('男','女'), birth datatime, key(name,age,sex));id为主键,并在name,age,sex列上建立了索引。
索引的优点
高性能的索引策略
正确地创建和使用索引是实现高性能查询的基础。前面已经介绍了各种类型的索引以及对应的优缺点。高效地选择和使用索引有很多种方式,其中有些是针对特殊案例的优化,有些则是针对特定行为的优化。
如上是盗取的一个向InnoDB表中插入数据的时间和索引大小的图,其中userinfo表和userinfo_uuid表唯一的区别是userinfo表以id为主键,而userinfo_uuid表以uuid为主键,而插入100万和300万数据的顺序是按照id列的顺序插入的,由上图可知,当插入300万数据行时,userinfo_uuid表由于不是按照主键(uuid)的顺序插入的数据,会导致大量的页分裂,从而插入需要更多的时间、索引占用更大的空间。
当然覆盖查询还是有很多陷阱可能导致无法实现优化的。MySQL查询优化器会在执行查询前判断是否有一个索引能够进行覆盖,覆盖where条件中的字段和select的字段。如果不能覆盖,则还是需要扫描数据行。
因为InnoDB表中非聚簇索引中存储主键值,所以我们先根据条件获取主键值,然后再根据主键值进行查询,这种方式叫做延迟关联。
如上是分别使用主键id排序和name排序的查询,可以看出使用id排序的查询使用了索引排序,而name排序的查询使用的是filesort。
总结
总的来说编写查询语句时,应尽可能选择合适的索引以避免单行查找,尽可能的使用原生顺序从而避免额外的排序操作,并尽可能使用索引覆盖查询。我们通过响应时间来对查询进行分析,找出消耗时间最长的查询或者给服务器带来压力最大的查询,然后检查查询的schema、SQL和索引结构,判断是否有查询扫描了太多的行,是否做了很多额外的排序或者使用了临时表,是否使用了随机I/O访问数据,或者太多回表查询哪些不在索引中的列的操作。
在发现查询效率不高时,首先就需要考虑查询语句的设计是否合理。如下将会介绍一些查询优化技巧,然后在介绍一些MySQL优化器内部的机制,并展示MySQL是如何执行查询的。最后探索查询优化的模式,以帮助MySQL更有效地执行查询。
优化数据访问
查询性能低下的最基本原因是访问的数据太多了。因此大部分的性能低下查询都可以通过减少访问的数据量进行优化。减少数据访问量通常意味着访问了太多的行,但有时也可能是访问了太多的列。在查询时如果仅需要查询结果集中的前某些行,则最简单的方式是在查询语句的最后加上limit。在进行多表关联查询时应尽量避免使用select *,因为它返回表的所有列,但是这些列可能并不都是必须的。除了请求了不需要的数据,还需要查看MySQL是否在扫描额外的记录,其中可以通过扫描行数和返回行数进行衡量。如果发现查询中需要扫描大量的数据但是只返回少数的行,通常可以:
重构查询方式
设计查询的时候一个需要考虑的重要问题是,是否需要将一个复杂的查询分成多个简单的查询。在传统的实现中总是强调数据库层完成尽可能多的工作,这样的逻辑在于以前总是认为网络通信、查询解析和优化是一件代价很高的事情。但是这样的想法对于MySQL并不适用,MySQL从设计上连接和断开连接都很轻量级,在返回一个小的查询结果方面很高效。
分解关联查询:很多高性能的应用都会对关联查询进行分解,简单地说就是对每个表进行一次单表查询,然后将结果在应用程序中进行关联。如下图所示:
查询计算机1班学生的所有成绩,我们可以将上过程分解为三个子步骤,如下:
那么这么分解的好处又在哪里呢?首先是让缓存的效率更高。许多应用程序可以方便的缓存单表查询对应的结果对象。如已经缓存了计算机1班对应的id为1,tb_student表中1班的学生有1号和5号,从而可以从成绩表中查询1号和5号学生的成绩;其次查询分解后,执行单个查询可以减少锁竞争;再次查询本身效率也会有所提升。如上使用in()代替关联查询,可以让MySQL按照ID顺序进行查询,这可能比随机的关联更加高效;最后分解关联查询可以减少冗余记录的查询,在应用层做关联查询时,意味着对于某条记录应用只需要查询一次,而在数据库中做关联查询,则可能需要重复地访问一部分数据。
查询执行的基础
当希望MySQL能够以较高的性能运行查询时,最好的办法就是弄清楚MySQL是如何优化和执行查询的。如下图展示了向MySQL发送一个请求时MySQL具体的操作过程:
第一步是MySQL客户端/服务器通信,二者之间通信协议是“半双工”的,也就是说在某一时刻只能有一方在发送数据。在任何一个时刻MySQL连接都有一个状态,该状态表示MySQL当前的工作,通过SHOW FULL PROCESSLIST命令查询状态。其中状态有Sleep、Query、Locked、Analyzing and statistics、Coping to tmp table、Sorting result、Sending data.
第二步是查寻缓存。在解析一个查询语句之前,如果查询缓存是打开的,那么MySQL会优先检查这个查询是否命中查询缓存中的数据。通常是通过一个对大小写敏感的Hash查找实现。如果命中,那么在返回结果前MySQL会检查一次用户权限,该过程无须解析查询SQL语句。如果未命中,则解析SQL语句。
第三步是查询优化处理。包括解析SQL、预处理、优化SQL执行计划,其中出现任何错误都会终止查询。首先,MySQL通过关键字将SQL语句进行解析,并生成一棵对应的“解析树”。查询优化器负责将解析树转化成执行计划,优化器的作用就是找到查询的较优执行计划。MySQL使用基于成本的优化器,它将尝试预测一个查询使用某种执行计划时的成本(SHOW STATUS LIKE 'Last_query_cost'),并选择成本最小的一个。查询优化器是一个非常复杂的部件,它使用了很多优化策略来生成一个最优的执行计划。优化策略分为:静态优化和动态优化。静态优化可以直接对解析树进行分析,并完成优化。例如,优化器可以通过简单的代数变换将where条件转换成另一种等价形式,静态优化不依赖于特别的数值,如where中带入的常数。静态优化在第一次完成后就一直有效,即使使用不同的参数重复执行也不会发生变化,可以认为是一种“编译时优化”。动态优化是上下文相关的,如where条件中取值、索引条目对应的数据行数等,是一种“运行时优化”。如下是MySQL能够处理的优化类型:
(a<b and b=c) and a=10
则会改写为a=10 and b>10 and b=c
;
当MySQL需要对选择的数据进行排序时,如果无法使用索引进行排序,那么MySQL在数据量小则在内存中进行排序,如果数据量大则需要磁盘进行排序,不过MySQL将这一过程统一称为文件排序(filesort)。如果需要排序的数据量小于“排序缓冲区”,MySQL使用内存进行“快速排序”操作,如果内存不够排序,MySQL先对数据进行分块,然后对每个独立的块使用“快速排序”,并将各块排序结果放入磁盘,然后将各个排好序的块进行合并(merge)。在关联查询的时候如果需要排序,MySQL会分两种情况来处理这样的文件排序,如果order by子句中的所有列都来自关联的第一个表,那么MySQL在关联处理第一个表的时候就进行文件排序,则MySQL的EXPLAIN结果的extra字段就会有“using filesort”。除此之外的其他情况,MySQL都会先将关联结果放到一个临时表中,,然后在所有关联都结束后再进行文件排序,此时的MySQL的EXPLAIN结果的extra字段值为“Using temporary;Using filesort”。如果查询中有limit的话,limit也会在排序之后应用,所以即使返回较少的数据,临时表和需要排序的数量仍会非常大(MySQL5.6的limit子句在此处已经做了改进)。
第四步是查询执行引擎。MySQL根据执行计划给出的指令逐步执行,在该过程中,有大量的操作需要通过调用存储引擎实现的接口来完成,也就是“Handler API”。MySQL在优化阶段就为每个表创建一个handler实例,优化器根据这些实例的接口获取表的相关信息。
最后一步就是将查询的结果返回给客户端。MySQL将结果集返回客户端是一个增量、逐步返回的过程。一旦服务器处理完最后一个关联表,开始生成第一条结果时,MySQL就可以开始想客户端逐步返回结果。这样有两个好处:一是服务器端无须存储太多的结果;二是结果集中的每一行都会以一个满足MySQL客户端/服务器通信协议的封包发送,再通过TCP协议进行传输,从而是客户端可以在第一时间获得返回的结果。
优化特定类型的查询
总结
综上所有的内容可知,创建高性能应用程序要考虑schema、索引、查询语句以及查询优化等问题。理解查询是如何被执行的以及时间都消耗在哪些地方,从而针对耗时大的查询语句进行改进。