MySql高级——MySql逻辑架构

MySql 逻辑架构

1、整体架构图

和其它数据库相比， MySQL 有点与众不同， 它的架构可以在多种不同场景中应用并发挥良好作用。 主要体现在存储引擎的架构上， 插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。 这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

1.1、连接层

最上层是一些客户端和连接服务， 包含本地 sock 通信和大多数基于客户端/服务端工具实现的类似于 tcp/ip 的通信。 主要完成一些类似于连接处理、 授权认证、 及相关的安全方案。 在该层上引入了线程池的概念， 为通过认证安全接入的客户端提供线程。 同样在该层上可以实现基于 SSL 的安全链接。 服务器也会为安全接入的每个客户端验证它所具有的操作权限。

1.2、服务

Management Serveices & Utilities	系统管理和控制工具
SQL Interface	SQL 接口。 接受用户的 SQL 命令， 并且返回用户需要查询的结果。 比如 select from 就是调用 SQL Interface
Parser	解析器。 SQL 命令传递到解析器的时候会被解析器验证和解析
Optimizer	查询优化器。 SQL 语句在查询之前会使用查询优化器对查询进行优化， 比如有 where 条件时， 优化器来决定先投影还是先过滤。
Cache 和 Buffer	查询缓存。 如果查询缓存有命中的查询结果， 查询语句就可以直接去查询缓存中取 数据。 这个缓存机制是由一系列小缓存组成的。 比如表缓存， 记录缓存， key 缓存， 权限缓存等

1.3、引擎层

存储引擎层， 存储引擎真正的负责了 MySQL 中数据的存储和提取， 服务器通过 API 与存储引擎进行通信。 不同的存储引擎具有的功能不同， 这样我们可以根据自己的实际需要进行选取。

1.4、存储层

数据存储层， 主要是将数据存储在运行于裸设备的文件系统之上， 并完成与存储引擎的交互。

---

2、sql 执行周期

2.1、show profile

利用 show profile 可以查看 sql 的执行周期！

2.2、开启 profile

查看 profile 是否开启： show variables like '%profiling%'

mysql> show variables like '%profiling%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | OFF   |
| profiling_history_size | 15    |
+------------------------+-------+
3 rows in set (0.20 sec)

如果没有开启， 可以执行 set profiling=1 开启！

2.3、使用 profile

执行 show profiles 命令， 可以查看最近的几次查询。

mysql> show profiles;
+----------+------------+-----------------------------------+
| Query_ID | Duration   | Query                             |
+----------+------------+-----------------------------------+
|        1 | 0.00100600 | show variables like '%profiling%' |
|        2 | 0.00008850 | show prifiles                     |
|        3 | 0.00008400 | show profile cpu,block io for 2   |
|        4 | 0.00003775 | show tables                       |
|        5 | 0.00003400 | shows databases                   |
|        6 | 0.00003300 | shows database                    |
|        7 | 0.22814675 | show databases                    |
|        8 | 0.00003375 | user db1                          |
|        9 | 0.00012700 | SELECT DATABASE()                 |
|       10 | 0.00013325 | show databases                    |
|       11 | 0.00013775 | show tables                       |
|       12 | 0.00016175 | show tables                       |
|       13 | 0.00028600 | select * from db1.emp             |
+----------+------------+-----------------------------------+
13 rows in set, 1 warning (0.00 sec)

根据 Query_ID，可以进一步执行 show profile cpu,block io for query Query_id 来查看 sql 的具体执行步骤。

mysql> show profile cpu,block io for query 13;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000113 | 0.000024 |   0.000072 |            0 |             0 |
| checking permissions | 0.000005 | 0.000001 |   0.000003 |            0 |             0 |
| Opening tables       | 0.000012 | 0.000003 |   0.000009 |            0 |             0 |
| init                 | 0.000024 | 0.000006 |   0.000018 |            0 |             0 |
| System lock          | 0.000006 | 0.000001 |   0.000004 |            0 |             0 |
| optimizing           | 0.000002 | 0.000001 |   0.000002 |            0 |             0 |
| statistics           | 0.000006 | 0.000002 |   0.000005 |            0 |             0 |
| preparing            | 0.000006 | 0.000001 |   0.000004 |            0 |             0 |
| executing            | 0.000001 | 0.000000 |   0.000001 |            0 |             0 |
| Sending data         | 0.000087 | 0.000023 |   0.000065 |            0 |             0 |
| end                  | 0.000004 | 0.000000 |   0.000002 |            0 |             0 |
| query end            | 0.000003 | 0.000001 |   0.000003 |            0 |             0 |
| closing tables       | 0.000004 | 0.000001 |   0.000003 |            0 |             0 |
| freeing items        | 0.000006 | 0.000001 |   0.000004 |            0 |             0 |
| cleaning up          | 0.000007 | 0.000002 |   0.000005 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+
15 rows in set, 1 warning (0.00 sec)

2.4、大致的查询流程

mysql 的查询流程大致是：

1. mysql 客户端通过协议与 mysql 服务器建连接， 发送查询语句， 先检查查询缓存， 如果命中， 直接返回结果，否则进行语句解析，也就是说， 在解析查询之前， 服务器会先访问查询缓存（query cache）——它存储 SELECT 语句以及相应的查询结果集。

   如果某个查询结果已经位于缓存中， 服务器就不会再对查询进行解析、 优化、 以及执行。 它仅仅将缓存中的结果返回给用户即可， 这将大大提高系统的性能。

2. 语法解析器和预处理： 首先 mysql 通过关键字将 SQL 语句进行解析， 并生成一颗对应的 “解析树”。

   mysql 解析器将使用 mysql 语法规则验证和解析查询； 预处理器则根据一些 mysql 规则进一步检查解析数是否合法。

3. 查询优化器当解析树被认为是合法的了，并且由优化器将其转化成执行计划。

   一条查询可以有很多种执行方式，最后都返回相同的结果。 优化器的作用就是找到这其中最好的执行计划。

4. 然后， mysql 默认使用的 BTREE 索引， 并且一个大致方向是：无论怎么折腾 sql， 至少在目前来说， mysql 最多只用到表中的一个索引。

---

3、SQL 的执行顺序

3.1、手写顺序

3.2、真正执行的顺序

随着 Mysql 版本的更新换代， 其优化器也在不断的升级， 优化器会分析不同执行顺序产生的性能消耗不同而动
态调整执行顺序。 下面是经常出现的查询顺序：

---

4、MyISAM 和 InnoDB

对比项	MyISAM	InnoDB
外键	不支持	支持
事务	不支持	支持
行表锁	表锁， 即使操作一条记录也会锁住整个表， 不适合高并发的操作	行锁,操作时只锁某一行， 不对其它行有影响， 适合**高并发**的操作
缓存	只缓存索引， 不缓存真实数据	不仅缓存索引还要缓存真实数据， 对内存要求较高， 而且内 存大小对性能有决定性的影响
关注点	读性能	并发写、 事务、 资源
默认安装	Y	Y
默认使用	N	Y
自 带 系 统 表 使用	Y	N

show engines：查看所有的数据库引擎

show variables like '%storage_engine%' ：查看默认的数据库引擎

mysql> show variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| default_storage_engine           | InnoDB |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set (0.00 sec)