【上海大数据培训】MySQL性能调优查询优化

时间:2018-04-02 15:13:49   来源:上海尚学堂   阅读:

一、Mysql调优前言

MYSQL 应该是最流行了 WEB 后端数据库。WEB 开发语言近期发展非常快,PHP, Ruby, Python, Java 各有特点,尽管 NOSQL 近期越來越多的被提到,可是相信大部分架构师还是会选择 MYSQL 来做数据存储。MySQL 数据库的使用是非常的广泛,稳定性和安全性也非常好,经历了无数大小公司的验证。仅能够安装使用是远远不够的,MySQL 在使用中需要进行不断的调整参数或优化设置,才能够发挥 MySQL 的最大作用。
 

二、MySQL逻辑架构

如果能在头脑中构建一幅MySQL各组件之间如何协同工作的架构图,有助于深入理解MySQL服务器。下图展示了MySQL的逻辑架构图。
 
 
 
MySQL逻辑架构,来自:《高性能MySQL》
 
 
MySQL逻辑架构整体分为三层,最上层为客户端层,并非MySQL所独有,诸如:连接处理、授权认证、安全等功能均在这一层处理。
 
MySQL大多数核心服务均在中间这一层,包括查询解析、分析、优化、缓存、内置函数(比如:时间、数学、加密等函数)。所有的跨存储引擎的功能也在这一层实现:存储过程、触发器、视图等。
 
最下层为存储引擎,其负责MySQL中的数据存储和提取。和Linux下的文件系统类似,每种存储引擎都有其优势和劣势。中间的服务层通过API与存储引擎通信,这些API接口屏蔽了不同存储引擎间的差异。
 
 

三、MySQL查询过程

我们总是希望MySQL能够获得更高的查询性能,最好的办法是弄清楚MySQL是如何优化和执行查询的。一旦理解了这一点,就会发现:很多的查询优化工作实际上就是遵循一些原则让MySQL的优化器能够按照预想的合理方式运行而已。
 
当向MySQL发送一个请求的时候,MySQL到底做了些什么呢?
 
 
 
 

3.1 MySQL查询过程

 
客户端/服务端通信协议
MySQL客户端/服务端通信协议是“半双工”的:在任一时刻,要么是服务器向客户端发送数据,要么是客户端向服务器发送数据,这两个动作不能同时发生。一旦一端开始发送消息,另一端要接收完整个消息才能响应它,所以我们无法也无须将一个消息切成小块独立发送,也没有办法进行流量控制。
 
客户端用一个单独的数据包将查询请求发送给服务器,所以当查询语句很长的时候,需要设置max_allowed_packet参数。但是需要注意的是,如果查询实在是太大,服务端会拒绝接收更多数据并抛出异常。
 
与之相反的是,服务器响应给用户的数据通常会很多,由多个数据包组成。但是当服务器响应客户端请求时,客户端必须完整的接收整个返回结果,而不能简单的只取前面几条结果,然后让服务器停止发送。因而在实际开发中,尽量保持查询简单且只返回必需的数据,减小通信间数据包的大小和数量是一个非常好的习惯,这也是查询中尽量避免使用SELECT *以及加上LIMIT限制的原因之一。
 

3.2 查询缓存

在解析一个查询语句前,如果查询缓存是打开的,那么MySQL会检查这个查询语句是否命中查询缓存中的数据。如果当前查询恰好命中查询缓存,在检查一次用户权限后直接返回缓存中的结果。这种情况下,查询不会被解析,也不会生成执行计划,更不会执行。
 
MySQL将缓存存放在一个引用表(不要理解成table,可以认为是类似于HashMap的数据结构),通过一个哈希值索引,这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同(例如:空格、注释),都会导致缓存不会命中。
 
如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表,其查询结果
都不会被缓存。比如函数NOW()或者CURRENT_DATE()会因为不同的查询时间,返回不同的查询结果,再比如包含CURRENT_USER或者CONNECION_ID()的查询语句会因为不同的用户而返回不同的结果,将这样的查询结果缓存起来没有任何的意义。
 
既然是缓存,就会失效,那查询缓存何时失效呢?MySQL的查询缓存系统会跟踪查询中涉及的每个表,如果这些表(数据或结构)发生变化,那么和这张表相关的所有缓存数据都将失效。正因为如此,在任何的写操作时,MySQL必须将对应表的所有缓存都设置为失效。如果查询缓存非常大或者碎片很多,这个操作就可能带来很大的系统消耗,甚至导致系统僵死一会儿。而且查询缓存对系统的额外消耗也不仅仅在写操作,读操作也不例外:
 
任何的查询语句在开始之前都必须经过检查,即使这条SQL语句永远不会命中缓存。
如果查询结果可以被缓存,那么执行完成后,会将结果存入缓存,也会带来额外的系统消耗。
 
基于此,我们要知道并不是什么情况下查询缓存都会提高系统性能,缓存和失效都会带来额外消耗,只有当缓存带来的资源节约大于其本身消耗的资源时,才会给系统带来性能提升。但要如何评估打开缓存是否能够带来性能提升是一件非常困难的事情,也不在本文讨论的范畴内。如果系统确实存在一些性能问题,可以尝试打开查询缓存,并在数据库设计上做一些优化。

比如:
用多个小表代替一个大表,注意不要过度设计;
批量插入代替循环单条插入;
合理控制缓存空间大小,一般来说其大小设置为几十兆比较合适;
可以通过SQL_CACHE和SQL_NO_CACHE来控制某个查询语句是否需要进行缓存;
最后的忠告是不要轻易打开查询缓存,特别是写密集型应用。如果你实在是忍不住,可以将query_cache_type设置为DEMAND,这时只有加入SQL_CACHE的查询才会走缓存,其他查询则不会,这样可以非常自由地控制哪些查询需要被缓存。
 
当然查询缓存系统本身是非常复杂的,这里讨论的也只是很小的一部分,其他更深入的话题,比如:缓存是如何使用内存的?如何控制内存的碎片化?事务对查询缓存有何影响等等,读者可以自行阅读相关资料,这里权当抛砖引玉吧。
 

3.3 语法解析和预处理

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

四、查询优化

经过前面的步骤生成的语法树被认为是合法的了,并且由优化器将其转化成查询计划。多数情况下,一条查询可以有很多种执行方式,最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划。
 
MySQL使用基于成本的优化器,它尝试预测一个查询使用某种执行计划时的成本,并选择其中成本最小的一个。在MySQL可以通过查询当前会话的last_query_cost的值来得到其计算当前查询的成本。
Mysql代码
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会生成对应的执行计划,查询执行引擎根据执行计划给出的指令逐步执行得出结果。整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成,这些接口被称为handlerAPI。查询过程中的每一张表由一个handler实例表示。实际上,MySQL在查询优化阶段就为每一张表创建了一个handler实例,优化器可以根据这些实例的接口来获取表的相关信息,包括表的所有列名、索引统计信息等。存储引擎接口提供了非常丰富的功能,但其底层仅有几十个接口,这些接口像搭积木一样完成了一次查询的大部分操作。
 

 5.1 返回结果给客户端

查询执行的最后一个阶段就是将结果返回给客户端。即使查询不到数据,MySQL仍然会返回这个查询的相关信息,比如改查询影响到的行数以及执行时间等等。
 
如果查询缓存被打开且这个查询可以被缓存,MySQL也会将结果存放到缓存中。
 
结果集返回客户端是一个增量且逐步返回的过程。有可能MySQL在生成第一条结果时,就开始向客户端逐步返回结果集了。这样服务端就无须存储太多结果而消耗过多内存,也可以让客户端第一时间获得返回结果。需要注意的是,结果集中的每一行都会以一个满足①中所描述的通信协议的数据包发送,再通过TCP协议进行传输,在传输过程中,可能对MySQL的数据包进行缓存然后批量发送。
 

5.2  MySQL查询执行过程总结

回头总结一下MySQL整个查询执行过程,总的来说分为6个步骤:
 
1、客户端向MySQL服务器发送一条查询请求。
2、服务器首先检查查询缓存,如果命中缓存,则立刻返回存储在缓存中的结果。否则进入下一阶段。
3、服务器进行SQL解析、预处理、再由优化器生成对应的执行计划。
4、MySQL根据执行计划,调用存储引擎的API来执行查询。
5、将结果返回给客户端,同时缓存查询结果。
 

六、 性能优化建议

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

6.1 Scheme设计与数据类型优化

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

 

6.2 总结几个可能容易理解错误的技巧:

通常来说把可为NULL的列改为NOT NULL不会对性能提升有多少帮助,只是如果计划在列上创建索引,就应该将该列设置为NOT NULL。
 
对整数类型指定宽度,比如INT(11),没有任何卵用。INT使用16为存储空间,那么它的表示范围已经确定,所以INT(1)和INT(20)对于存储和计算是相同的。
 
UNSIGNED表示不允许负值,大致可以使正数的上限提高一倍。比如TINYINT存储范围是通常来讲,没有太大的必要使用DECIMAL数据类型。即使是在需要存储财务数据时,仍然可以使用BIGINT。比如需要精确到万分之一,那么可以将数据乘以一百万然后使用TIMESTAMP使用4个字节存储空间,DATETIME使用8个字节存储空间。因而,TIMESTAMP只能表示1970 - 2038年,比DATETIME表示的范围小得多,而且TIMESTAMP的值因时区不同而不同。
大多数情况下没有使用枚举类型的必要,其中一个缺点是枚举的字符串列表是固定的,添加和删除字符串(枚举选项)必须使用ALTER TABLE(如果只只是在列表末尾追加元素,不需要重建表)。
 
schema的列不要太多。原因是存储引擎的API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据,然后在服务器层将缓冲内容解码成各个列,这个转换过程的代价是非常高的。如果列太多而实际使用的列又很少的话,有可能会导致CPU占用过高。
 
大表ALTER TABLE非常耗时,MySQL执行大部分修改表结果操作的方法是用新的结构创建一个张空表,从旧表中查出所有的数据插入新表,然后再删除旧表。尤其当内存不足而表又很大,而且还有很大索引的情况下,耗时更久。当然有一些奇淫技巧可以解决这个问题,有兴趣可自行查阅。
 

6.3 创建高性能索引

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

 

七、MySQL数据库Schema设计的性能优化


7.1 高效的模型设计

适度冗余-让Query尽两减少Join

大字段垂直分拆-summary表优化

大表水平分拆-基于类型的分拆优化

统计表-准实时优化
 

7.2 合适的数据类型


时间存储格式总类并不是太多,我们常用的主要就是DATETIME,DATE和TIMESTAMP这三种了。从存储空间来看TIMESTAMP最少,四个字节,而其他两种数据类型都是八个字节,多了一倍。而TIMESTAMP的缺点在于他只能存储从1970年之后的时间,而另外两种时间类型可以存放最早从1001年开始的时间。如果有需要存放早于1970年之前的时间的需求,我们必须放弃TIMESTAMP类型,但是只要我们不需要使用1970年之前的时间,最好尽量使用TIMESTAMP来减少存储空间的占用。
 

7.3字符存储类型


CHAR[(M)]类型属于静态长度类型,存放长度完全以字符数来计算,所以最终的存储长度是基于字符集的,如latin1则最大存储长度为255字节,但是如果使用gbk则最大存储长度为510字节。CHAR类型的存储特点是不管我们实际存放多长数据,在数据库中都会存放M个字符,不够的通过空格补上,M默认为1。虽然CHAR会通过空格补齐存放的空间,但是在访问数据的时候,MySQL会忽略最后的所有空格,所以如果我们的实际数据中如果在最后确实需要空格,则不能使用CHAR类型来存放。

VARCHAR[(M)]属于动态存储长度类型,仅存占用实际存储数据的长度。TINYTEXT,TEXT,MEDIUMTEXT和LONGTEXT这四种类型同属于一种存储方式,都是动态存储长度类型,不同的仅仅是最大长度的限制。


参考文章:http://www.jianshu.com/p/d7665192aaaf;https://www.cnblogs.com/luxiaoxun/p/4694144.html

感谢阅读上海尚学堂大数据文章,获取更多大数据内容或支持请点击  上海大数据培训

分享:0

电话咨询

客服热线服务时间

周一至周五 9:00-21:00

周六至周日 9:00-18:00

咨询电话

021-67690939
15201841284

微信扫一扫