这篇MySQL优化原理分析比X光还清晰前言：MySQL架构体系先分享实验前的基础知识。MySQL主要分为Server层和存储引擎层。server层主要包括connectors、retrievalmemory、analyzers、optimizers、executors等。所有跨存储引擎的功能都建立在这一层之上，如存储过程、触发器、视图、函数等。有一个标准化的binglog日志模块.存储引擎负责数据的存储和访问。它采用可替换的插件架构，拥有InnoDB、MyISAM、Memory等多种存储引擎。InnoDB引擎有一个重做日志模块。如下图所示，实验环境操作系统内核版本：Tencenttlinuxrelease2.2MySQL数据库版本：5.7.10新建表tb_article并创建两个索引：index_title、index_author_id，表结构如下：我们尝试插入一些数据：现在执行SQL语句，select*fromtb_articlewhereauthor_id=20andtitle='b';分析SQL语句的执行过程和优化策略。MySQL执行SQL语句过程1.MySQL客户端与服务器通信客户端根据MySQL通信协议向服务器发送SQL。SQL到达服务器后，服务器会启动单线程执行SQL。MySQL客户端和服务器之间的通信协议是“半双工”。2、查询状态对于MySQL连接，随时都有一个状态，表示MySQL当前在做什么。运行showfullprocesslist命令查看当前状态。在一个查询生命周期中，状态会发生多次变化，以下是对这些状态的解释：1.睡眠：线程正在等待客户端发送新的请求；2.查询：线程正在执行查询或将结果发送给客户端；3．locked：在MySQL服务器层，线程正在等待一个表锁。在存储引擎级别实现的锁，例如InnoDB的行锁，不会反映在线程状态中。这是MyISAM的典型状态；4、分析统计：线程正在收集存储引擎的统计信息，生成查询的执行计划；5.copyingtotmptable：线程正在执行查询并将结果集复制到一个临时表中。这种状态一般要么是groupby操作，要么是文件排序操作，要么是union操作。如果这个状态后面有ondisk标记，说明MySQL在磁盘上放了一个内存临时表；6.排序结果：线程正在对结果集进行排序；7、发送数据：线程可能在多个状态之间传输数据，或者生成结果集，或者返回数据给客户端。3、查询缓存MySQL的缓存的主要作用是提高查询效率。缓存以键和值的哈希表的形式存储。键是具体的SQL语句，值是结果的集合。如果无法命中缓存，则继续分析器步骤，命中缓存则直接返回客户端。如果使用查询缓存，在执行读写操作时会带来额外的资源消耗。如果你处在一个写多读少的环境中，缓存会频繁的添加和失效。MySQL8.0版本开始取消查询缓存。4.查询优化处理查询的生命周期的下一步是将一条SQL转化为一个执行计划，MySQL根据这个执行计划与存储引擎进行交互。这包括多个子阶段：解析SQL、预处理和优化SQL执行计划。此过程中的任何错误都可能终止查询。1、语法解析器和预处理：首先，MySQL通过关键字对SQL语句进行解析，生成对应的“解析树”。MySQL解析器将使用mysql语法规则来验证和解析查询；预处理器会根据MySQL的一些规则进一步检查解析出的数据是否合法。2.查询优化器：当语法树被认为合法时，由优化器将其转化为执行计划。可以通过多种方式执行查询，所有这些方式都返回相同的结果。优化器的作用就是在其中寻找最佳的执行计划。3、执行计划：MySQL不会生成查询字节码来执行查询。MySQL为查询生成一棵指令树，然后通过存储引擎执行指令树并返回结果。最终的执行计划包含重构查询的所有信息。5、查询执行引擎在分析优化阶段，MySQL会生成查询对应的执行计划，MySQL会根据优化器生成的执行计划调用存储引擎的API执行查询。六、返回结果给客户端理解select*fromtb_articlewhereauthor_id=20andtitle='b';性能和优化策略，一般使用explain命令进行分析。MySQLexplainMySQLQueryOptimizer通过执行explain命令获取一个Query在当前状态数据库中的执行计划。explain的信息有10列，分别是id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra。这些字段的出现可能的解释如下：1、idid列的编号是select编号的顺序，有几个select，就有几个id，id的顺序依次递增选择出现。MySQL将select查询分为简单查询和复杂查询。复杂查询分为三类：简单子查询、派生表（from语句中的子查询）和联合查询。2.select_type(1)SIMPLE（简单SELECT，不使用UNION或子查询等）；(2)PRIMARY（如果查询包含任何复杂的子部分，则最外层的select被标记为PRIMARY）；(3)UNION（UNION中的第二个或后续SELECT语句）；(4)DEPENDENTUNION（UNION中的第二个或后续SELECT语句，取决于外部查询）；（5）UNIONRESULT（UNION的结果）；(6)SUBQUERY（子查询中的第一个SELECT）；(7)DEPENDENTSUBQUERY（子查询中第一个SELECT依赖于外层查询）；(8)DERIVED(派生表的SELECT，FROM子句子查询)；(9)UNCACHEABLESUBQUERY（子查询的结果不能缓存，必须重新计算外链的第一行）。3、table列表示一行explain访问的是哪个表。当from子句中有子查询时，表列为格式，说明当前查询依赖id=N的查询，所以先执行id=N的查询。有union时，UNIONRESULT表列的值为，1和2代表参与union的selectrowids。4.type表示MySQL在表中查找所需行的方式，也称为“访问类型”。常用的类型有：ALL,index,range,ref,eq_ref,const,system,NULL（从左到右，性能从差到好）ALL：FullTableScan，MySQL会遍历整个表寻找匹配的行索引：FullIndexScan，index和ALL的区别在于索引类型只遍历索引树范围：只检索给定范围内的行，并使用索引来选择行ref：表示以上表的连接匹配条件，即，哪些列或常量用于搜索索引列上的值。与eq_ref相比，没有使用唯一索引，而是使用了普通索引或唯一索引的一部分前缀。索引需要与某个值进行比较，可能会找到多个符合条件的行。eq_ref：和ref类似，不同的是使用的索引是唯一索引。对于每个索引键值，表中只有一条记录匹配。简单来说就是主键或者唯一键作为关联条件const，systeminmulti-tableconnection:这些类型的访问在MySQL优化查询的某一部分并转化为常量时使用。如果主键放在where列表中，MySQL可以将查询转换为常量。system是const类型的特例。当查询表只有一行时，使用系统NULL：MySQL在优化过程中对语句进行分解。执行时即使不访问表或索引，例如从索引列中选取最小值也可以通过单个索引查找来完成。5.possible_keys列显示查询可能使用哪些索引来查找。解释的时候可能会出现possible_keys有columns，但是key显示NULL。这是因为表中的数据不多。MySQL认为索引对本次查询没有帮助，所以选择了全表查询。如果该列为NULL，则没有关联索引。在这种情况下，可以通过检查where子句看是否可以创建合适的索引来提高查询性能，然后使用explain查看效果。6、keykey列显示的是MySQL实际决定使用的键（索引）。如果未选择索引，则该键为NULL。要强制MySQL使用或忽略possible_keys列上的索引，请在查询中使用FORCEINDEX、USEINDEX或IGNOREINDEX。7.key_len表示索引使用的字节数，通过该列可以计算出查询中使用的索引长度（key_len显示的值是索引字段的最大可能长度，不是实际使用的长度，即key_len是根据表定义计算的，不是通过表检索的）。长度越短越好，而且不会损失准确性。8.ref表示上表的连接匹配条件，即在索引列上使用哪些列或常量来查找值。9.rows表示MySQL根据表统计和索引选择估计要读取多少行才能找到需要的记录。这不是结果集中的行数。10.Extra该列包含了MySQL解决查询的详细信息，有以下几种情况：Usingindex：当请求的表的列都是同一个索引的一部分，返回的列数据只使用索引中的信息，而不是访问表中的行记录，是一种高性能的表现。Usingwhere：列数据从一个表中返回，只使用索引中的信息而不读取实际动作。当表的所有请求列都是同一索引的一部分时会发生这种情况，表明mysql服务器将在存储引擎检索行后进行过滤。Usingtemporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询。使用文件排序：MySQL中不能使用索引完成的排序操作称为“文件排序”，它使用外部索引对结果进行排序，而不是按索引顺序从表中读取行。此时mysql会根据连接类型浏览所有符合条件的记录，保存sortkey和rowpointer，然后按顺序排序key和检索row信息。这种情况一般需要考虑使用索引进行优化。Usingjoinbuffer：值的变化强调获取join条件时不使用索引，需要一个joinbuffer来存储中间结果。如果出现这个值，需要注意的是根据查询的具体情况，可能需要增加索引来提高性能。Impossiblewhere：该值强调where语句将导致没有符合条件的行。执行解释语句explainselect*fromtb_articlewhereauthor_id=20andtitle='b';可以发现，这条SQL语句的执行实际上并没有使用index_title索引，而是选择使用index_author_id索引。启用优化器跟踪功能：SEToptimizer_trace="enabled=on";选择*frominformation_schema.optimizer_trace\G;执行计划最终选择了index_author_id索引，因为index_author_id的开销比index_title小。这里需要介绍一下MySQL的成本计算模型。MySQL成本模型总成本模型：COST=CPUCost+IOCostMySQL在成本类型上分为IO、CPU和Memory。MySQL5.7的成本模型还在完善中。内存的成本虽然已经收齐了，但是还没有算在最终的价格里面。MySQL5.7在源码上对cost模型进行了大量重构，将cost分为server层和engine层。服务器层主要是CPU成本，而引擎层主要是IO成本。MySQL5.7引入了两个系统表mysql.server_cost和mysql.engine_cost分别配置这两层的cost。以下分析基于MySQL5.7.10server_cost1。row_evaluate_cost(default0.2)计算符合条件的行的成本，行数越多，成本越大；2.memory_temptable_create_cost(default2.0)创建临时内存表的开销；3.memory_temptable_row_cost(default0.2)内存临时表的行开销；4.key_compare_cost(default0.1)key比较的代价，比如排序；5.disk_temptable_create_cost(default40.0)内部myisam或innodb临时表的创建成本；6.disk_temptable_row_cost(default1.0)内部myisam或innodb临时表的行开销；可见创建临时表的开销是非常高的，尤其是内部的myisam或者innodb临时表。engine_cost1。io_block_read_cost(default1.0)从磁盘读取数据的开销，对于innodb来说，就是从磁盘读取一页的开销；2.memory_block_read_cost（默认1.0）；从内存中读取数据的开销，对于innodb来说，是指从缓冲池中读取一页的开销。目前io_block_read_cost和memory_block_read_cost的默认值都是1，实际生产中建议适当增加memory_block_read_cost，尤其是普通硬盘场景。为表tb_article创建复合索引index_title_authorALTERTABLEtb_articleADDKEYindex_title_author(`title`,`author_id`);select*fromtb_articlewhereauthor_id=20andtitle='b';index_author_id和index_title_author的代价相同，MySQL会优先选择叶子块少的索引。SQL语句：selecttitle,author_idfromtb_articlewhereauthor_id=20andtitle='b';MySQL会优先考虑复合索引index_title_author，因为index_title_author是索引覆盖扫描，不需要回表，性能高。
