Mysql查询过程

Mysql的课程是在极客时间上学习的不错的课程，准备二刷，二刷是因为讲的不错，数据库是基础之一吧，再熟悉下很有必要。本篇算是读书笔记类的。这系列讲的好原因我觉得作者设计篇幅设计的挺好的，从浅入深，从宏观到微观。

一 Mysql整体逻辑架构

我们在执行Sql语句的时候，想没想过sql到底是如何执行，而又如何把数据返回的，了解整个过程，对数据库有个整体认识很有必要，这篇就以select的查询语句为例来说明数据库的各个组件在查询语句中的作用。

整个Mysql大体上分为两个部分，服务层和存储引擎层，如下：

如上图所示，Mysql的服务层包括连接器，分析器，优化器，缓存。包含了Mysql的核心服务，比如一些日期函数，加密函数等执行，比如存储过程支撑，触发器，视图都是在这个层次实现的。

存储层是可以替换的存储引擎，存储引擎包括常用的InnoDB，MyISAM，memory这种内存式的存储引擎。 **InnoDB：**提供了事务安全表，支持外键引用完整性约束。它支持提交、回滚和紧急恢复功能来保护数据。它还支持行级锁定，在Mysql5.5后成为默认引擎； 将数据表分为.frm和.idb两类文件存储，.frm存储表的结构，ibd存储表数据和索引，聚簇索引里面包含了数据。 支持热备份，采用MVCC 多版本支持高并发，实现了四个隔离级别；锁支持行锁。 MyISAM： 采用索引和数据分离的方式，数据保存在三类文件中：.frm .MYD .MYI 分别对应表结构，表数据和表索引。 缺点：不支持事务，不支持行锁，读取数据加共享锁，写入加排他锁。 优点：支持全文索引，压缩等。

二 连接器

作用是连接建立，管理，权限判断。客户端连接mysql数据库的时候，连接器通过判断用户名和密码来认证用户，认证通过后，会查询权限表，以后此连接的操作权限判断，按照初始连接时候获取的权限信息来判断。即使管理员更改权限，已经有的连接不受到影响。 测试下，发现其实结果并不是这样：版本【8.0.3-rc-log】

mysql> grant select on test.* to 'tlink'@localhost;
Query OK, 0 rows affected (0.01 sec)

mysql> grant delete on test.t_user to 'tlink'@localhost;
Query OK, 0 rows affected (0.01 sec)

另一个客户端没有断开连接的情况下，却具有删除权限了：

mysql> delete from t_user where user_name='apple';
ERROR 1142 (42000): DELETE command denied to user 'tlink'@'localhost' for table 't_user'
mysql> delete from t_user where user_name='apple';
Query OK, 1 row affected (0.01 sec)

可能是新版本升级了，连接后，如果没有到mysql的默认超时时间，默认的超时时间是8个小时，超过8个小时的空闲连接会被干掉。

mysql> show processlist;
+-----+-------+-----------------+------+---------+------+----------+------------------+
| Id  | User  | Host            | db   | Command | Time | State    | Info             |
+-----+-------+-----------------+------+---------+------+----------+------------------+
| 210 | tlink | localhost:49538 | test | Query   |    0 | starting | show processlist |
+-----+-------+-----------------+------+---------+------+----------+------------------+
1 row in set (0.00 sec)

mysql>  show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set, 1 warning (0.01 sec)

连接尽量使用长连接，因为建立连接的过程比较复杂，但是如果长连接比较多，Mysql之下过程中使用的内存式管理在连接对象里面，这些资源会在连接后才释放，长期积累容易导致Mysql被系统杀掉。 解决办法：

执行比较占内存的sql后，主动断开连接，这样释放内存。

mysql5.7版本后，可以通过mysql_reset_connection来初始化连接，不需要权限认证和权限验证

二 缓存

客户端连接后，执行查询语句，会以语句为key去缓存中查找，如果查找到直接返回，可以节省查询时间。 但是如果在这个表上发生更新语句，会造成缓存失效，如果频繁更新的表，缓存失效会很快，所以一般不建议使用缓存，Mysql8.0以后版本取消了缓存。

三 分析器

查询缓存查询不到，就需要对sql语句进行分析，进行语法分析和词法分析，提取表名，字段名信息，关键字等，如果分析错误，会报错：You have an error in your SQL syntax，分析成功后会得到一个语法树， 如果要自己实现sql的语法分析，网上有不少代码可以参考：presto，drill，calcite 推荐presto。

四 优化器

优化器的作用是优化sql的执行，比如要不要做下推，选择哪个索引更好。SQL优化器分为两项：逻辑优化和物理优化；两个工作都是修改分析器生成的语法分析树。比如查询中多个条件的时候，哪个条件先执行，比如两个表在连接的时候，哪个表作为主表进行连接。 我们可以用explain来分析sql语句的优化结果，查看sql的执行瓶颈。

mysql> explain select * from t_user;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    9 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

五 执行器

开始执行的时候，需要先判断用户是否有执行查询权限，如果没有权限报错返回；如果权限没有问题，执行器会根据用的存储引擎接口：

1. 来打开表，获取一条数据判断是否满足条件；
2. 如果不满足条件，则放弃，满足放在结果集中。
3. 继续取下一条数据，判断是否满足，如此往复直到结束。
4. 最终执行器将结果集返回。

 

第一 Select查询过程.png