一． 认识MySQL

MySQL作为一款轻量级，功能强大、开源的关系型数据库，目前已经成为互联网公司后台数据库的标配。京东当前的业务系统，也基本都是使用MySQL数据库。那么作为MySQL数据库的深度使用者，不能仅仅会写SQL语句就可以，还需要深入了解MySQL的功能和特性，清楚SQL语句在数据库里面是如何执行的，是不是合理的，只有这样才能写出真正高性能的应用程序。

1.1 MySQL的版本

当前MySQL的版本已经到8.0版，常用的版本是5.7和5.6版本，京东线上还有部分5.5的版本在使用。MySQL 8.0版本虽然已经GA，但是稳定性上还有待验证，因此我们推荐生产系统使用稳定版MySQL 5.7，该版本无论是从性能还是稳定性上相比老版都有很大的提升。

1.2 MySQL的逻辑架构

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

逻辑架构.png

  • 客户端说明：主要是应用程序通过专有驱动访问数据库，比如JDBC、ODBC、python等等，不管是哪种，最后都会转换为SQL语句访问MySQL。
  • 连接线程处理层：主要功能是连接与线程的处理，这一层并不是MySQL独有，一般的基于C/S架构的都有类似组件，比如连接处理、授权认证、安全等。
  • SQL处理层：这一部分是MySQL核心功能，包括缓存查询、解析器、优化器，内置函数（日期、时间、加密等函数）等的实现.
  • 存储引擎层：负责数据存储，存储引擎的不同，存储方式、数据格式、提取方式等都不相同，这一部分也是很大影响数据存储与提取的性能的。SQL处理层是通过API与存储引擎通信的，API屏蔽了下层的差异，下层提供对外接口，上层负责调用即可，不必清楚下层是怎么实现的。
  • 目前业内较为常用的存储引擎主要有三个，MyISAM，Innodb，Tokudb。
  1. MyISAM是mysql早期常用的一款存储引擎，缺点是不支持行锁和事务，出现异常宕机后也比较容易出现数据损坏，不建议使用。
  2. Innodb是目前实际上的存储引擎标准，特点是支持行锁，ACID事务，并且对于异常恢复的处理机制比较完善。
  3. Tokudb是一款高压缩比的存储引擎，基于我们自己的测试可以达到1:10左右的压缩比，对于数据归档类型的应用比较适用。

二． SQL语句的生命周期

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

2.1 链接的生命周期

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

  1. 首先客户端发送请求连接到MySQL服务器时，服务器会对这个连接进行认证，认证基于用户名、主机信息和密码。如果客户端连接成功，服务器会继续验证该客户端是否具有相关权限（比如是否对某个库的某个表进行select操作）。
  2. 权限校验通过后，如果配置了查询缓存（Query Cache），会检查缓存是否命中，如果命中则立即返回缓存中的数据给客户端，如果没有命中则进入下一阶段。但是目前MySQL版本查询缓存的Bug较多，一般都不建议配置。
  3. MySQL服务器对SQL语句进行解析，主要是通过解析器和预处理器进行工作的。
  • 解析器： 也叫语法解析器，SQL语句经过解析器解析之后，会生成一颗对应的解析数，在解析器中，MySQL会使用语法规则验证和解析查询，例如是否使用错误的关键字、使用关键字的顺序是否正确、验证引号是否能正确匹配等。
  • 预处理器： 在预处理器中，进一步分析解析数是否合法并生成一颗新的解析数。例如：检查数据表和数据列是否存在，SQL语句的名字和别名是否有歧义，之后预处理器还会校验用户权限（权限校验一般会很快，除非服务器上有非常多的权限设置）。
  1. 以上过程处理完毕，SQL解析树可以认为是合法的，进入到查询优化器这一层。在查询优化器中将解析树转化为执行计划，假设一条查询语句有两种执行计划，返回的结果也是相同的，但是一种是走全表扫描，一种是走索引，优化器的工作就是通过预算每个执行计划的成本，选出其中成本最小的一个进行执行。
  2. MySQL服务器根据优化器生成的执行计划，调用存储引擎API来执行查询，最后把结果返回给客户端，如果配置了查询缓存，则会把数据缓存在查询缓存中。

2.2 SQL的执行顺序

我们已经了解了一条SQL语句在数据库内部的执行过程，那么一条SQL语句本身的执行顺序如何呢？我们可以通过下面这条SQL进行全面的介绍。执行顺序.png

  1. FROM：对FROM子句中的前两个表执行笛卡尔积（Cartesian product)(交叉联接），生成虚拟表VT1
  2. ON：对VT1应用ON筛选器。只有那些使为真的行才被插入VT2。
  3. OUTER(JOIN)：如果指定了OUTER JOIN（相对于CROSS JOIN 或(INNER JOIN),保留表（preserved table：左外部联接把左表标记为保留表，右外部联接把右表标记为保留表，完全外部联接把两个表都标记为保留表）中未找到匹配的行将作为外部行添加到 VT2,生成VT3.如果FROM子句包含两个以上的表，则对上一个联接生成的结果表和下一个表重复执行步骤1到步骤3，直到处理完所有的表为止。
  4. WHERE：对VT3应用WHERE筛选器。只有使为true的行才被插入VT4.
  5. GROUP BY：按GROUP BY子句中的列列表对VT4中的行分组，生成VT5.
  6. CUBE|ROLLUP：把超组(Suppergroups)插入VT5,生成VT6.
  7. HAVING：对VT6应用HAVING筛选器。只有使为true的组才会被插入VT7.
  8. SELECT：处理SELECT列表，产生VT8.
  9. DISTINCT：将重复的行从VT8中移除，产生VT9.
  10. ORDER BY：将VT9中的行按ORDER BY 子句中的列表排序，生成游标（VC10).
  11. Limit：从VC10的开始处选择指定数量或比例的行，生成表VT11，并返回调用者。

三． 如何分析SQL优劣

当我们发布一条SQL语句之后，它的执行计划是怎样的？如何判断这条SQL语句的优劣呢？如果程序员不对自己的SQL语句进行自测，不了解SQL语句可能产生的性能后果，很可能就会对线上系统带来风险和隐患。那么我们如何才能对自己写的SQL进行测试呢，如果查看执行计划呢？这时候就需要使用MySQL自带的explain工具进行验证。下面我们详细说明一下该工具的使用方法：

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

ex1ex1.jpg

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

ex2ex2.jpg

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

  1. ID
    是SQL执行的顺序的标识,SQL从大到小的执行
     ID相同时，执行顺序由上至下
     如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
     如果ID相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行
  2. select_type
    显示查询中每个select子句的类型
    (1) SIMPLE(简单SELECT,不使用UNION或子查询等)
    (2) PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)
    (3) UNION(UNION中的第二个或后面的SELECT语句)
    (4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
    (5) UNION RESULT(UNION的结果)
    (6) SUBQUERY(子查询中的第一个SELECT)
    (7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，取决于外面的查询)
    (8) DERIVED(派生表的SELECT, FROM子句的子查询)
    (9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
  3. table
    这一列表示 explain 的一行正在访问哪个表。
    当 from 子句中有子查询时，table列是 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。当有 union 时，UNION RESULT 的 table 列的值为 ，1和2表示参与 union 的 select 行id。
  4. partitions
    如果查询是基于分区表的话，会显示查询将访问的分区。
  5. type
    表示MySQL在表中找到所需行的方式，又称“访问类型”。
    常用的类型有： ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）
    ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
    index: Full Index Scan，index与ALL区别为index类型只遍历索引树
    range:只检索给定范围的行，使用一个索引来选择行
    ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
    eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
    const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system
    NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
  6. possible_keys
    指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用
    该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
    如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询
  7. Key
    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解决查询的详细信息,有以下几种情况：
    Using where:列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
    Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询
    Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”
    Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
    Impossible where：这个值强调了where语句会导致没有符合条件的行。
    Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行