1、mysql架构组成

71 阅读 0 评论 47 点赞

我是靠谱客的博主任性项链，最近开发中收集的这篇文章主要介绍1、mysql架构组成，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

mysql想必大家都不陌生，是当前最流行的一款关系型数据库产品之一。其实在早期，大部分公司还是用oracle居多，mysql还是一个比较冷门的数据库，自从阿里提出了去IOE这个概念之后，mysql才逐渐被广泛使用。与其它数据库相比更加灵活，能够适应更多的应用场景。举个例子，存储引擎是mysql最特别的特性，它提供了多种存储引擎可供我们选择，同时它的存储引擎架构将查询处理、数据存储/提取以及其它系统任务相分离，接下来让我们一起进入mysql的世界探索吧。

mysql架构

在这里插入图片描述上图摘自《高性能mysql第三版》一书，可以看出，mysql的组成总体分为两层：server层和存储引擎层，而server层又可以细分为多个组件：连接处理、查询缓存、分析器、解析器、优化器、执行器等，该层是存储引擎共享；而存储引擎层则是mysql特有，负责数据的存储和提取，支持多种存储引擎，采用了插件方式提高了灵活性。
为了更直观地理解各个组件之间的关系，我将上图进行细化为下图：
在这里插入图片描述
1、server层
（1）连接器
连接器主要处理来自客户端的连接请求，其实就是一个socket server端，同时需要进行授权认证，如校验用户名、密码等，校验通过会查询出该用户所有的操作权限（注意这些权限的作用域是当前连接），当前连接内需要权限判断的地方都是基于这里查出来的权限进行的。

（2）查询缓存（query cache）
当客户端发送一条select语句到server层的时候，首先会去查询缓存里查询是否存在，查询缓存里的数据结构是K(select语句)-V(结果)结构，由上次同样的select语句查询结果缓存进去的。如果查询缓存中能匹配到key，则直接返回结果，如果匹配不到，再到后面的阶段。

由此可见，查询缓存的设计是为了提高查询效率，提高吞吐率，可是很遗憾，查询缓存在MySQL 5.7.20版本已经弃用了（虽然还存在这个概念以及相关配置项，但是已经没用了），mysql 8.0版本就彻底移除了查询缓存以及相关配置项，可参见官网相关描述（mysql 8.0）：

The query cache was removed. Removal includes these items:

    The FLUSH QUERY CACHE and RESET QUERY CACHE statements.

    These system variables: query_cache_limit, query_cache_min_res_unit, query_cache_size, query_cache_type, query_cache_wlock_invalidate.

    These status variables: Qcache_free_blocks, Qcache_free_memory, Qcache_hits, Qcache_inserts, Qcache_lowmem_prunes, Qcache_not_cached, Qcache_queries_in_cache, Qcache_total_blocks.

    These thread states: checking privileges on cached query, checking query cache for query, invalidating query cache entries, sending cached result to client, storing result in query cache, Waiting for query cache lock.

    The SQL_CACHE SELECT modifier.

These deprecated query cache items remain deprecated, but have no effect; expect them to be removed in a future MySQL release:

    The SQL_NO_CACHE SELECT modifier.

    The ndb_cache_check_time system variable. 

The have_query_cache system variable remains deprecated, and always has a value of NO; expect it to be removed in a future MySQL release.

既然查询缓存能提高查询效率，那么为什么还要被移除呢？我想其中一个很重要的原因就是它的适用场景太少，举个例子：如果一张表更新非常频繁，只要更新一次，就要清空一次查询缓存，这就会大大降低缓存命中率，同时也增加了mysql内部复杂性。对于不怎么更新的表，会比较适用查询缓存，可以在my.cnf文件中进行如下设置：
query_cache_type=2 （0：关闭查询缓存；1:开启查询缓存，针对所有查询都生效；2：当sql语句中有SQL_CACHE时才生效），如：select SQL_CACHE * from test where id = 5；

查询缓存之所以适用场景太少，另外一个原因就是我们基本上会在应用层设计缓存机制，如redis、ehcache等，很少去使用mysql的查询缓存。

（3）分析器
第2步的查询缓存中如果没有查到数据或者关闭查询缓存的情况下，就会来到这一步。分析器主要是对sql语句进行词法分析、语法分析，词法分析是分析sql是要干啥，会根据一条语句中各个字符串生成一颗语法树；语法分析是在词法分析的基础上，根据特定语法判断sql语法是否合法，如果不合法就会报错。

（4）优化器
经过分析器之后，接下来优化器就会对sql进行优化，目的是为了提升sql执行效率。以select语句为例，往往一条查询语句的执行可能会有多种方案，而优化器会选择出一条更优的方案来执行，比如会判断是否有索引，如果有多个索引，选择哪个索引，甚至是走索引是否会比不走索引效率来的更快，等等，最终会根据这跟分析生成执行计划。需要注意的是，mysql生成的执行计划并不一定说绝对性能最高的方案，mysql选择一种执行方案是基于对成本的分析来决定的，这个分析结果不一定最优，但是大部分情况下这个结果都是最优方案。
如果想要知道mysql是最终怎么选择索引的，它的分析过程是啥样的，可以使用trace工具（mysql 5.6版本开始新增的功能）来查看，登录mysql后：

# Turn tracing on (it's off by default):
SET optimizer_trace="enabled=on"; //开启trace（默认是关闭的，建议使用完之后给关闭）
SELECT ...; # your query here
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; //查看优化器优化过程
# possibly more queries...
# When done with tracing, disable it:
SET optimizer_trace="enabled=off"; //关闭trace

OPTIMIZER_TRACE表提供优化器跟踪功能，保存了优化器跟踪语句生成的信息，我们来看一下这条sql语句返回的结果长啥样：

{
  "steps": [ //分析步骤
    {
      "join_preparation": { //步骤1：准备阶段
        "select#": 1,
        "steps": [
          {
            "IN_uses_bisection": true
          },
          {
              //这里我把具体的表字段隐去了，用xxx代替
            "expanded_query": "/* select#1 */ select xxx from `tv_user_subscriber` where (`tv_user_subscriber`.`user_id` in ('023160006161211','023160006161230'))"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": { //步骤2：优化阶段
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(`tv_user_subscriber`.`user_id` in ('023160006161211','023160006161230'))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`tv_user_subscriber`.`user_id` in ('023160006161211','023160006161230'))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`tv_user_subscriber`.`user_id` in ('023160006161211','023160006161230'))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`tv_user_subscriber`.`user_id` in ('023160006161211','023160006161230'))"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`tv_user_subscriber`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [ //成本估算，即将各种查询方案的成本进行估算
              {
                "table": "`tv_user_subscriber`",
                "range_analysis": {
                  "table_scan": { //全表扫描成本
                    "rows": 5884,
                    "cost": 1209.9
                  } /* table_scan */,
                  "potential_range_indexes": [//可能使用的索引
                    {
                      "index": "PRIMARY", //主键索引
                      "usable": false, //未使用
                      "cause": "not_applicable" //不使用原因
                    },
                    {
                      "index": "tv_user_s_u_a", 
                      "usable": true, //表示tv_user_s_u_a索引可用
                      "key_parts": [
                        "user_id"
                      ] /* key_parts */
                    },
                    {
                      "index": "partner_user_id",
                      "usable": false,
                      "cause": "not_applicable"
                    }
                  ] /* potential_range_indexes */,
                  "setup_range_conditions": [
                  ] /* setup_range_conditions */,
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  } /* group_index_range */,
                  "analyzing_range_alternatives": {//分析各个索引使用成本
                    "range_scan_alternatives": [
                      {
                        "index": "tv_user_s_u_a",
                        "ranges": [
                          "023160006161211 <= user_id <= 023160006161211",
                          "023160006161230 <= user_id <= 023160006161230"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 2,//扫描行数
                        "cost": 4.41,//成本
                        "chosen": true //选择使用此索引
                      }
                    ] /* range_scan_alternatives */,
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    } /* analyzing_roworder_intersect */
                  } /* analyzing_range_alternatives */,
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "tv_user_s_u_a",
                      "rows": 2,
                      "ranges": [
                        "023160006161211 <= user_id <= 023160006161211",
                        "023160006161230 <= user_id <= 023160006161230"
                      ] /* ranges */
                    } /* range_access_plan */,
                    "rows_for_plan": 2,
                    "cost_for_plan": 4.41,
                    "chosen": true
                  } /* chosen_range_access_summary */
                } /* range_analysis */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [//最终生成的执行计划
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`tv_user_subscriber`",
                "best_access_path": {//最优访问路径
                  "considered_access_paths": [//最终采用的访问路径
                    {
                      "rows_to_scan": 2,//扫描行数
                      "access_type": "range", //访问类型，对应于explain中的type字段
                      "range_details": {
                        "used_index": "tv_user_s_u_a"//使用的索引
                      } /* range_details */,
                      "resulting_rows": 2,
                      "cost": 4.81, //估算的成本
                      "chosen": true //是否选择此计划
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 2,
                "cost_for_plan": 4.81,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "(`tv_user_subscriber`.`user_id` in ('023160006161211','023160006161230'))",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`tv_user_subscriber`",
                  "attached": "(`tv_user_subscriber`.`user_id` in ('023160006161211','023160006161230'))"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [ //改进执行计划
              {
                "table": "`tv_user_subscriber`",
                "pushed_index_condition": "(`tv_user_subscriber`.`user_id` in ('023160006161211','023160006161230'))",
                "table_condition_attached": null
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": { //步骤3：执行阶段
        "select#": 1, //执行该sql（可以理解成sql的别名），对应于join_preparation阶段中的参数项： "expanded_query": "/* select#1 */ 
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}

从以上的分析结果来看，mysql最终选择哪个索引、选择哪个执行路径，取决于优化器对各种执行路径成本消耗的分析结果，选用成本最小的来生成执行计划。更多关于trace工具的介绍，可以在官网上了解，说的很详细：https://dev.mysql.com/doc/refman/5.7/en/information-schema-optimizer-trace-table.html

（5）执行器
优化器生成了执行计划之后，执行器就会按照这个执行计划来执行，执行器会调用存储引擎层的接口来对表进行存取。用过mysql的朋友应该都知道binlog日志，它就是在执行器执行过程中写入的，可见binlog是独立于存储引擎层的，也就是说不管使用哪种存储引擎，都会有binlog日志（前提是开启了binlog设置）。

2、存储引擎层
mysql提供了多种存储引擎，使用最多的应该就是InnoDB和MyIsam了，mysql从5.5版本之后，默认的存储引擎就就从MyIsam改为InnoDB了。相信大多数人使用过的也就是InnoDB和MyIsam这两种引擎，除了这两种，mysql还提供了其它一些引擎，我们看下官网介绍（mysql5.7版本）：
在这里插入图片描述官网中这里列的是除了InnoDB之外的其它存储引擎，InnoDB是单独的一个章节来介绍的，可见InnoDB的重要性，这也是官网建议使用的引擎。如果有想详细了解这些存储引擎的朋友，可以到官网自行了解：https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html

本文主要基于InnoDB和MyIsam两种引擎进行介绍，这两种引擎的比较网上有很多相关帖子，其实它们更全面更详细的特性官网也有介绍，可分别参考官网：
https://dev.mysql.com/doc/refman/5.7/en/innodb-introduction.html
https://dev.mysql.com/doc/refman/5.7/en/myisam-storage-engine.html

在这里插入图片描述

它们俩最重要的区别当属以下几点了：

InnoDB支持事务，Myisam不支持；
InnoDB锁粒度到行，而Myisam只到表；
InnoDB支持MVCC，Myisam不支持；

尤其是事务特性，我们的应用场景中随处可见，这也是InnoDB成为最常用的存储引擎其中一个很重要的原因。

3、mysql各组件间的工作流程
第1节中介绍了mysql的架构，它的内部各个组件是如何协调工作的呢？server层的工作流程前文已经做了结束，现在主要补充一下存储引擎层的工作流程，那么server层和存储引擎层流程串起来之后其实就是mysql执行一条sql的流程。还是基于第1节中的架构图，进一步完善如下（基于InnoDB引擎）：

在这里插入图片描述
其中redo log 和undo log是InnoDB特有的，因为InnoDB是事务安全的存储引擎，其事务安全性主要就是通过redo log和undo log来保证的。redo log中记录了InnoDB所做的所有物理变更和事务信息，通过redo log和undo log，InnnoDB保证了任何情况下的事务安全性。其实undo log还有一个作用，就是MVCC的实现也是基于undo log的，通过MVCC可以保证事务的隔离性，MVCC相关原理我后面会专门介绍。

mysql的增删改查都是直接操作buffer pool，并不直接读写磁盘，这是因为磁盘存取效率要远低于内存，buffer pool中的数据会有一个单独的线程不定期刷到磁盘上，是page为单位，因此buffer pool中的数据存储也是以page为单位进行存储的。

关于这几个log文件的读写，都是顺序读、写，所以性能很高，不会非常影响mysql的整体性能，而存储数据的数据文件则是随机读写，因为可能会对任意page中的任意行增删数据，无法保证顺序性，因此直接对磁盘上的数据文件进行存取的性能会非常低，这也是mysql执行sql时不会直接操作磁盘而是buffer pool的原因。

4、数据文件
在mysql中每一个数据库都会在数据目录（可以指定）下存在一个以数据库名字命名的文件夹，用来存放该数据库中各种表数据文件。不同的存储引擎的文件格式也会有不同，本文就只介绍InnoDB和Myisam两种存储引擎的数据文件格式。
（1）.frm文件
对于user表，必定会存在一个user.frm文件，该文件存放了表的元数据信息，包括表结构等。该文件独立于存储引擎，所有存储引擎都会有.frm文件，存放在所属数据库的文件夹下面。

（2）.ibd文件和ibdata文件
这两种文件是InnoDB存储引擎特有的，用来存储表数据的。InnoDB可以通过配置指定使用共享表空间还是独享表空间来存放数据。独享表空间就是用.ibd文件来存放数据，如user表，它的数据存放在user.ibd文件中，每一个表都会有一个以其名字命名的.ibd文件，与.frm文件存放在一个位置。如果选用共享表空间来存放数据，就会使用ibdata文件来存放，所有表共用一个（可以通过配置指定文件数）ibdata文件。我们一般都是使用独享表空间，至少我没用过共享表空间，也没遇到过相关场景，就不展开结束了。

（3）.MYD文件
.MYD文件是Myisam存储引擎特有的，用来存放Myisam表的数据。同.ibd文件类似，每一个表都会有一个以其名字命名的.MYD文件名，存放在.frm文件相同位置。

（4）.MYI文件
.MYI文件也是Myisam存储引擎特有的，用来存放Myisam表的索引信息，存放在.frm和.MYD文件相同的位置。

本文主要就是简单介绍了mysql的架构，以及各个组件之间的关系和工作流程，更多细节可以去官网上了解，mysql官网我个人觉得写的是比较清晰的，而且非常详细，没事可以上去遛遛~~