MySQL优化原理

1、认识MySQL

　　　　MySQL作为一款轻量级，功能强大、开源的关系型数据库，目前已成为互联网公司后台数据库的标配。那么作为MySQL数据库的深度使用者，不能仅仅会写SQL语句就可以，还需深入了解MySQL的功能和特性，清楚SQL语句在数据库里面试如何执行

　　的，是不是合理的，只有这样才能写出真正高性能的应用程序。

　　1.1 MySQL的版本

　　　　当前MySQL的版本已经到8.0版，常用的版本是5.6和5.7，很多5.5版本仍在使用。MySQL8.0版本虽然已经GA,但稳定性还有待验证，因此推荐生产系统使用稳定的版本MySQL5.7，该版本无论是从性能还是稳定性上相比于老版本都有很大的提升。

　　1.2 MySQL的逻辑架构

　　　　工欲善其事必先利其器，首先我们需要了解一下MySQL的逻辑架构，以及每个组件的功能，如下图所示：

　　　　

• 客户端说明：主要是应用通过通过专有的驱动访问数据库，比如JDBC、ODBC等。不管是哪种，最后都会转化为SQL语句访问MySQL。
• 连接线程处理层：主要功能是连接线程与处理，这一层并不是MySQL独有，一般基于C\S架构的都有类似组件，比如连接处理，授权认证，安全等。
• SQL处理层：这一层是MySQL的核心功能，包括缓存查询，解析器，优化器，内置函数等实现。
• 存储引擎层：负责存储数据，存储引擎不同，存储方式，数据格式，提取方式等都不相同，这一部分也是很影响数据存储与提取性能的。SQL处理层是通过API与存储引擎通信的，API屏蔽了下层差异，下层提供对外接口，上层调用即可，不必关心下层如何实现。
• 目前业内较为常用的存储引擎有三个，MyISAM，InnoDB,TokuDB

　　　　1.  MyISAM是MySQL早起常用的一款存储引擎，缺点是不支持锁和事务，出现异常宕机后也也比较容易出现数据损坏，不建议使用。

　　　　2. InnoDB是目前实际上的存储引擎标准，特点是支持行锁，ACID事务，并且对异常恢复的处理机制比较完善。

　　　　3. TokuDB是一款高压缩比的存储引擎，据测试可达1:10左右的压缩比，对于数据归档类型的应用比较适用。

2、SQL语句的生命周期

　　　　上一节我们介绍了MySQL的体系架构，现在我们通过一个应用连接到MySQL，从发出一条SQL语句，到返回数据结束整个生命周期来说明每个组件的功能。另外从程序员的角度，我们也需要清晰的理解SQL在数据库中是如何执行的。

　　2.1 连接的生命周期

　　　　当应用程序建立一个连接并发出一条SQL语句的时候，MySQL是按照如下图的流程进行工作的：

　　　　

　　　　1. 首先客户端发送请求连接到MySQL的服务器时，服务器会对这个连接进行认证，认证基于用户名，主机信息密码。如果客户端连接成功，服务器会继续验证该客户端是否具有相关权限。（比如是否对某个库某个表进行选择操作）

　　　　2. 权限校验通过后，如果配置了查询缓存，会检查缓存中是否命中，如果命中则立即返回缓存中的数据给客户端，如果没有命中则进入下一个阶段。但是目前版本MySQL查询缓存的错误较多，一般都不建议配置。

　　　　3. MySQL服务器对SQL语句进行解析，主要是通过解析器和预处理器进行工作的。

　　　　　　。解析器：也叫语法解析器，SQL语句经过解析器解析之后，会生成一棵对应的解析树，在解析器中，MySQL会使用语法规则验证和解析查询，例如是否使用错误的关键字，使用关键字的顺序是否正确，验证引号是否能正确匹配等。

　　　　　　。预处理器：在预处理器中，进一步分析解析树是否合法，并生成一棵新的解析树，例如检查数据表和数据列是否存在，SQL语句的名字和别名是否有歧义，之后预处理器还会校验权限。（校验权限一般会很快，除非服务器上有非常多的权限设置）

　　　　4. 以上过程处理完毕，SQL解析树可以认为是合法的，进入到查询优化器这一层。在查询优化器中将解析树转化为执行计划，假设一条语句有两种执行计划，返回的结果也是相同的，但是一种走全表扫描，一种走索引，优化器的工作就是通过预算每个执

　　　　　行计划的成本，选择其中成本最小的一个执行。

　　　　5. MySQL的服务器根据优化器生成的执行计划，调用存储引擎API来执行查询，最后把结果返回给客户端，如果配置了查询缓存，会把数据缓存在查询缓存中。

　　2.2 SQL的执行顺序

　　　　我们已经了解一条SQL在数据库中的执行过程，那么一条SQL语句的执行顺序如何呢？我们通过下面这条SQL进行全面的介绍：

　　　　

　　　　1. FROM :对来自子句的前两个表执行笛卡尔积（交叉连接），生成虚拟表VT1

　　　　2. ON :对VT1应用ON筛选器，只有那些为真的行才被插入VT2

　　　　3. OUTER(JOIN)：如果指定了OUTER JOIN(相对于CORSS JOIN 或INNER JOIN)，保留表（保留表：左外部连接把左表标记为保留表，右外部连接把右表标记为保留表，完全外部连接把两个表都标记为保留表）中未找到匹配的行将作为外部行添加到

　　　　　VT2,生成VT3,如果FROM子句包含两个以上的的表，则对上一个连接生成的结果表和下一个表重复执行步骤1到步骤3，直到处理所有表为止。

　　　　4. WHERE : 对VT3应用WHERE筛选器，为真的行才被插入VT4

　　　　5. GROUP BY:按照GROUP BY子句中的列，列表对VT4进行分组，生成VT5

　　　　6. CUBE|ROLLUP：把超组（Suppergroups）插入VT5,VT6生成

　　　　7. HAVING : 对VT6应用HAVING筛选器，为真的组才会被插入VT7

　　　　8. SELECT : 处理SELECT列表，产生VT8。

　　　　9. DISTINCT:将重复的行从VT8中移除，产生VT9

　　　　10. ORDER BY:将VT9中的行按照ORDER BY子句中的列表排序，生成游标（VC10）

　　　　11. LIMIT: 从VC10的开始处选择指定数量的或比例的行，生成表VT11,并返回调用者

 

3、如何分析SQL优劣

　　　　当我们发布一条SQL语句之后，它的执行计划是怎么样的？如何判断这条SQL语句的优劣呢？如果程序员不对自己的SQL语句进行自测，不了解SQL语句可能产生的性能后果，很可能就会对线上系统带来风险和隐患。那么我们如何才能对自己的SQL进行

　　测试呢？这时候就需要使用MySQL自带的explain工具进行验证下面我们详细说明一下该工具的使用方法：

　　　　可以使用explain命令，后面带着SQL查询语句执行即可

　　　　

　　　　也可以使用desc命令快捷执行 

　　　　

　　　　展示出来的信息有12列（本次使用的是MySQL5.7，根据版本不同字段可能略有差别）分别是id，select_type，table，partipartitions，type，possible_keys ，key，key_len，ref，rows，filtered，extra，下面对这些列进行解释：

　　　　1. id是SQL执行的顺序的标识,如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行;如果id相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行。

　　　　2. select_type显示查询中每个select子句的类型：

　　　　　　   (1) simple(简单select,不使用union或子查询等)
　　　　　　(2) primary(查询中若包含任何复杂的子部分,最外层的select被标记为primary)
　　　　　　(3) union(若第二个select出现在union之后，则被标记为union。若包含在from字句的子查询中，外层select则被标记为derived)
　　　　　　(4) dependent union(union中的第二个或后面的select语句，取决于外面的查询)
　　　　　　(5) union result(union的结果)
　　　　　　(6) subquery(子查询中的第一个select)
　　　　　　(7) dependent subquery(子查询中的第一个select，取决于外面的查询)
　　　　　　(8) derived(在from列表包含子查询被标记为derived（衍生）mysql会递归执行这些子查询，把结果放在临时表里)
　　　　　　(9) uncacheable subquery(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

　　　　3. table这一列表示 explain 的一行正在访问哪个表。当 from 子句中有子查询时，table列是格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。当有 union 时，UNION RESULT 的 table 列的值为 ，1和2表示

　　　　　参与 union 的 select 。

　　　　4. partitions如果查询是基于分区表的话，会显示查询将访问的分区。

　　　　5. type表示MySQL在表中找到所需行的方式，又称“访问类型”。常用的类型有： ALL, index, range, ref, eq_ref, const, system, NULL(从左到右，性能从差到好)：

　　　　　　  (1) ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
　　　　　　  (2) index: Full Index Scan，index与ALL区别为index类型只遍历索引树
　　　　　　  (3) range:只检索给定范围的行，使用一个索引来选择行
　　　　　　  (4) ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
　　　　　　  (5) eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
　　　　　　  (6) const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system
　　　　　　  (7) NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

 　　　   6. possible_keys：指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表

　　　　　次序使用。如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询

　　　    7. key: 显示MySQL实际决定使用的键（索引）如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

　　　    8. key_len: 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的），不损失精确性的情况下，长度

　　　　    越短越好。

　　　     9. ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。

　　　     10. rows: 表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数。

　　　     11. filtered:它指返回结果的行占需要读到的行(rows列的值)的百分比。

　　　     12. extra: 该列包含MySQL解决查询的详细信息,有以下几种情况：

　　　　　　    (1) Using where:列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
　　　　　　    (2) Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询
　　　　　　    (3) Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”
　　　　　　    (4) Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
　　　　　　    (5) Impossible where：这个值强调了where语句会导致没有符合条件的行。
　　　　　　    (6) Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行