执行以下SQL。我们看到的只是输入一条语句,返回一个结果,而不知道这条语句在MySQL内部的执行过程。select*fromwhereid='1';上图是MySQL的基本架构图,从中可以清楚的看到MySQL各个功能模块中SQL语句的执行过程。一般来说,MySQL可以分为两部分:服务器层和存储引擎层。Server层包括连接器、查询缓存、分析器、优化器、执行器等,涵盖了MySQL的大部分核心服务功能,以及所有内置功能(如日期、时间、数学和加密功能等),所有跨存储引擎的功能都在这一层实现,比如存储过程、触发器、视图等,存储引擎层负责数据的存储和检索。其架构模型为插件式,支持InnoDB、MyISAM、Memory等多种存储引擎。现在最常用的存储引擎是InnoDB,它从MySQL5.5.5开始成为了默认的存储引擎。也可以通过指定存储引擎的类型来选择其他引擎,比如在createtable语句中使用engine=memory来指定使用内存引擎建表。一个SQL查询的完整执行流程如上图所示。服务器服务层连接器必须是一开始连接数据库的连接器。连接器负责与客户端建立连接、获取权限、维护和管理连接。连接命令一般是这样写的:mysql-h$ip-P$port-u$user-p输入命令后,需要在交互对话框中输入密码。虽然也可以直接在-p后面的命令行写密码,但是这样可能会导致你的密码泄露。如果您连接到生产服务器,强烈建议您不要这样做。连接命令中的mysql是一个客户端工具,用来与服务器端建立连接。完成经典的TCP握手后,连接器将开始验证您的身份,这次使用您输入的用户名和密码。如果用户名或密码不正确,您将收到“拒绝用户访问”错误,客户端程序将终止执行。如果用户名和密码验证通过,连接器将检查您在权限表中拥有的权限。之后,这个连接中的权限判断逻辑就会依赖于此时读取的权限。这意味着用户成功建立连接后,即使你使用管理员账户修改用户的权限,也不会影响已有的连接权限。修改完成后,只有新连接才会使用新的权限设置。连接完成后,如果没有后续操作,则连接空闲,在showprocesslist命令中可以看到。文中图片是showprocesslist的结果,其中的Command栏显示“Sleep”这一行,表示系统中有空闲连接。如果客户端太长时间没有移动,连接器将自动断开连接。这个时间由参数wait_timeout控制,默认值为8小时。如果客户端断开连接后再次发送请求,会收到错误提示:LostconnectiontoMySQLserverduringquery。这时候如果想继续,就需要重新连接,然后执行请求。在数据库中,长连接是指连接成功后,如果客户端继续请求,将一直使用同一个连接。短连接是指每次执行几次查询后断开连接,重新建立一个新的连接用于下一次查询。建立连接的过程通常比较复杂,所以我建议大家在使用过程中尽量减少建立连接的动作,即尽量使用长连接。但是全部使用长连接之后,你可能会发现有时候MySQL占用的内存增加的非常快。这是因为MySQL在执行过程中临时使用的内存是在connection对象中管理的。这些资源只有在连接断开时才会被释放。因此,如果长连接堆积起来,可能会导致内存占用过多而被系统强行杀掉(OOM)。从现象可以看出MySQL重启异常。如何解决这个问题呢?您可以考虑以下两种选择。定期断开长连接。使用一段时间后,或者程序判断执行了占用内存大的查询,断开连接,需要重新连接查询。如果您使用的是MySQL5.7或更新版本,则可以在每次大型操作后执行mysql_reset_connection来重新初始化连接资源。这个过程不需要重新连接和重新认证,但是会将连接恢复到刚刚创建的状态。查询缓存连接建立后,就可以执行select语句了。执行逻辑会走到第二步:查询缓存。MySQL得到一个查询请求后,首先会去查询缓存中查看这条语句之前是否执行过。之前执行的语句及其结果可以以键值对的形式直接缓存在内存中。键是查询语句,值是查询结果。如果你的查询可以直接在这个缓存中找到键,那么这个值就会直接返回给客户端。如果该语句不在查询缓存中,则继续执行后续阶段。执行完成后,执行结果会保存在查询缓存中。可以看到如果查询命中了缓存,MySQL可以直接返回结果,不需要进行后续的复杂操作,非常高效。但在大多数情况下,我会建议您不要使用查询缓存,为什么?因为查询缓存往往弊大于利。查询缓存失效非常频繁。只要有一个表的更新,这个表上的所有查询缓存都会被清除。因此,很有可能你辛辛苦苦保存的结果,还没等你用到,就被一次更新彻底清空了。对于更新压力大的数据库,查询缓存的命中率会很低。除非你的业务是要静态表,否则会隔很长时间更新一次。比如一个系统配置表,那么对这个表的查询就适合做查询缓存。幸运的是,MySQL也提供了这种“按需使用”的方式。您可以将参数query_cache_type设置为DEMAND,这样查询缓存就不会被默认的SQL语句使用。对于确定要使用查询缓存的语句,可以使用SQL_CACHE显式指定,像下面的语句:mysql>selectSQL_CACHE*fromTwhereID=10;需要注意的是,MySQL8.0版本直接将整个querycache的saveblock功能删除,也就是说8.0版本完全没有这个功能。如果分析器没有命中查询缓存,它将开始实际执行语句。首先,MySQL需要知道你要做什么,所以它需要解析SQL语句。分析器首先进行“词法分析”。你输入的是一个由多个字符串和空格组成的SQL语句。MySQL需要识别其中的字符串是什么以及它们代表什么。MySQL是从你输入的关键字“select”中识别出来的,这是一条查询语句。它还将字符串“T”识别为“表名T”,将字符串“ID”识别为“列ID”。完成这些识别之后,就要进行“语法分析”了。语法分析器根据词法分析的结果,根据语法规则判断你输入的SQL语句是否满足MySQL语法。如果你的语句有误,你会收到“你的SQL语法有错误”的错误提示。例如,下面的语句select缺少首字母“s”。mysql>elect*fromtwhereID=1;ERROR1064(42000):你的SQL语法有错误;查看你的MySQL服务器版本对应的手册第1行near'elect*fromtwhereID=1'的正确使用语法一般语法错误会提示第一个错误位置,所以要注意后面的内容“在附近使用”。优化器通过了分析器,MySQL知道你要做什么。在开始执行之前,必须经过优化器的处理。当表中有多个索引时,优化器决定使用哪个索引;或者当一个语句有多个表关联(join)时,决定每个表的连接顺序。通常两种执行方式的逻辑结果是一样的,但是执行效率会有所不同,优化器的作用就是决定采用哪种方案。优化器阶段完成后,确定这条语句的执行计划,然后进入执行器阶段。在执行SQL查询时,优化器主要完成以下任务:选择最合适的索引;选择是扫描表还是使用索引;选择表关联顺序;优化where子句;排除管理中无用的表;决定orderby和groupby是否使用索引;尝试用innerjoin代替outerjoin;简化子查询,确定结果缓存;MySQL查询优化器有几个目标,但主要目标是尽可能使用索引,并且使用最严格的索引来消除尽可能多的数据行。优化器尝试排除行的原因是它排除行的速度越快,它找到符合条件的行的速度就越快。如果首先运行最严格的测试,查询可以执行得更快。执行者MySQL通过分析器知道你要做什么,通过优化器知道你要做什么,于是进入执行者阶段,开始执行语句。开始执行的时候,首先要判断自己是否有权限对这张表T执行查询,如果没有,会返回一个没有权限的错误,如下图(工程实现中,如果命中了查询缓存,会在querycache返回结果的时候做权限校验,query也会调用precheck在optimizer之前校验权限)。mysql>select*fromTwhereID=10;ERROR1142(42000):SELECTcommanddeniedtouser'b'@'localhost'fortable'T'如果您有权限,请打开表并继续执行。打开表时,执行器会根据表的引擎定义使用引擎提供的接口。比如我们例子中的表T,ID字段是没有索引的,所以执行器的执行过程是这样的:调用InnoDB引擎接口取这张表的第一行,判断ID值是否为10,如果没有则跳过它。如果是,将这一行存入结果集中;调用引擎接口取“下一行”,重复相同的判断逻辑,直到取到表的最后一行。执行器将上述遍历过程中满足条件的所有行组成的记录集作为结果集返回给客户端。至此,这条语句执行完毕。对于有索引的表,执行逻辑类似。首先调用的是“获取满足条件的第一行”接口,然后循环获取“满足条件的下一行”接口。这些接口已经在引擎中定义。你会在数据库的慢查询日志中看到一个rows_examined字段,表示语句执行过程中扫描了多少行。每次执行程序调用引擎获取数据行时都会累积此值。在某些场景下,执行器被调用一次,在引擎内部扫描多行,因此引擎扫描的行数与rows_examined并不完全相同。存储引擎只能使用InnoDB引擎类型通过showengines查看引擎类型来使用事务;InnoDB存储引擎InnoDB是MySQL默认的事务引擎,是最重要、应用最广泛的存储引擎,具有行级锁和外键约束。它旨在处理大量短期交易。大多数短暂的事务通常会提交并且很少回滚。InnoDB的性能和自动崩溃恢复功能使其在非事务性存储需求方面也很受欢迎。除非有非常具体的原因需要使用其他存储引擎,否则应该首选InnoDB引擎。InnoDB的适用场景/特点如下:频繁更新的表适合处理多个并发的更新请求。支持交易。可以从灾难中恢复(通过bin-log等)。外键约束。只有他支持外键。支持自增列属性auto_increment。MyISAM存储引擎MyISAM提供了大量的特性,包括全文搜索、压缩等,但不支持事务和行级锁,但支持表级锁。对于只读数据,或者表比较小,可以容忍修复操作的场景,MyISAM还是可以用的。MyISAM的适用场景/特点如下:不支持事务的设计,但不代表有事务操作的项目不能使用MyISAM存储引擎,可以根据自身业务需求在程序层进行控制.不支持外键的表设计。查询速度非常快,如果对数据库的insert和update操作比较多的话比较适合。整天锁表的场景。MyISAM非常强调快速读取操作。表的行数存储在MyIASM中,所以在SELECTCOUNT(*)FROMTABLE时只需要直接读取保存的值即可,无需进行全表扫描。如果表的读操作远多于写操作,并且不需要数据库事务的支持,MyIASM也是一个不错的选择。MySQL内置的其他存储引擎MySQL也有一些专用的存储引擎,在一些特殊的场景下用起来非常爽。在新版本的MySQL中,有的可能因为某些原因不再支持,有的会继续支持,但需要明确启用后才能使用。Archive存储引擎Archive引擎只支持insert和select操作,在MySQL5.1之前,甚至不支持索引。Archive引擎缓存所有写入并使用zlib压缩插入的行,因此它的磁盘I/O比MyISAM引擎少。但是每次select查询都需要全表扫描,所以Archive更适合日志和数据收集类应用,而这类应用往往需要全表扫描进行数据分析。Archive引擎支持行级锁和专用缓冲区,因此可以实现高并发插入。在查询开始并返回表中存在的所有行之前,存档引擎会阻止其他选择执行以实现一致读取。此外,这使得批量插入在完成之前对读取不可见。黑洞存储引擎黑洞引擎没有实现任何存储机制,它会在没有任何存储的情况下丢失所有插入的数据。奇怪,这不是没用吗?但是服务器端会记录Blackhole的日志,所以可以用来复制数据到备库,或者干脆记录到日志中。这种特殊的存储引擎可以在一些特殊的复制架构和日志审计中发挥作用。不过,这个存储引擎的存在还是有些难以理解。CSV存储引擎CSV引擎可以把普通的CSV文件当作MySQL表来处理,但是这样的表不支持索引。CSV引擎可以在数据库运行时将文件复制进或复制出。它可以将数据以CSV文件形式存储在Excel等电子表格软件中,然后复制到MySQL数据目录下,即可在MySQL中打开使用。同样,如果将数据写入CSV引擎表,其他外部程序可以立即从表的数据文件中读取CSV格式的数据。因此,CSV引擎作为一种数据交换机制非常有用。Memory存储引擎如果你需要快速访问数据,而且数据不会被修改,重启后丢失也无所谓,那么使用Memory引擎就非常有用了。Memory引擎至少比MyISAM引擎快一个数量级,因为所有数据都存储在内存中,不需要磁盘I/O。Memory引擎的表结构重启后会保留,但数据会丢失。内存引擎在很多场景下都能发挥很好的作用:用于查找或映射表,比如将邮箱映射到州名的表。用于缓存周期聚合数据的结果。用于保存数据分析中产生的中间数据。内存引擎支持哈希索引,因此查找非常快。尽管内存速度非常快,但它仍然无法取代传统的基于磁盘的表。Memory引擎是表级锁,并发摄取性能低。如果MySQL在查询执行过程中需要使用临时表来保存中间结果,那么内部临时表就是Memory引擎。如果中间结果太大,超过Memory的限制,或者包含BLOB或TEXT字段,临时表将被转换为MyISAM引擎。看了上面的描述,大家会经常把Memory和temporarytables混淆。临时表是指使用CREATETEMPORARYTABLE语句创建的表。它可以使用任何存储引擎,所以它和Memory不是一回事。临时表仅在单个连接内可见,并在连接断开时不复存在。关于临时表和内存引擎的信息,请参考MySQL·引擎特性·临时表。MySQL存储引擎和第三方存储引擎有很多,这里就不一一介绍了。如有必要,我将进一步讨论。如何选择合适的存储引擎?这么多的存储引擎真是让人眼花缭乱,我们应该如何选择呢?大多数情况下都会选择默认的存储引擎——InnoDB,这也是最正确的选择,所以Oracle最终在MySQL5.5中使用了InnoDB作为默认的存储引擎。如何选择合适的存储引擎可以用一句话简单概括:“除非你需要使用一些InnoDB没有的特性,又没有其他选择,否则你应该首先选择InnoDB引擎。”比如要使用全文搜索,建议优先使用InnoDB和Sphinx的组合,而不是使用支持全文搜索的MyISAM。当然,如果你不需要使用InnoDB的特性,而其他引擎的特性更能满足你的需求,你可以考虑其他存储引擎。除非绝对必要,否则建议不要混用多个存储引擎,否则可能会带来一系列复杂的问题,以及一些潜在的bug和边界问题。如果需要使用不同的存储引擎,建议考虑以下因素进行考量。事务备份和恢复的独特功能其他搜索引擎SQL通过以下命令检查默认存储引擎。mysql>showvariableslike'%storage_engine%';--查看表的存储引擎showtablestatuslike"table_name";
