MySQL原理与实践（一）：一条select语句引出Server层和存储引擎层

前言：

       在这篇博文之前，博主也曾写过一些入门级别的MySQL相关文章，但是鉴于当时的技术水平和知识沉淀，部分概念和原理并没有理解清楚，仅仅是做为笔记在供大家参考学习。这段时间有时间和精力重新系统学习MySQL相关知识，极客时间 -《MySQL实战45讲》，感觉在深度和广度上都有了些许精进，特此开启了MySQL原理与实践的系列文章的攥写，希望帮助更多的人理清基本原理和概念。

 

正文：

     我们先来写一条最普通的SQL查询语句：

mysql> select * from T where ID=10；

没问题，这个语句只要我们稍微有点数据库基础应该都可以看懂，那就是从表T中找到ID=10的数据，并且输出该行所有字段。那么问题来了，这个语句在MySQL内部是如何执行的？下边通过介绍MySQL的基本逻辑架构图来详细阐述。

MySQL基本逻辑架构图：

                           

        从上图可以看出，MySQL内部逻辑架构包括Server层以及下边的存储引擎层。Server层又包括连接器，查询缓存，分析器，优化器和执行器。存储引擎层则包括当前数据库所使用的存储引擎，常见的包括：InnoDB，MyISAIM以及Memory等。接下来我们依次介绍逻辑架构图中出现的各个组件。

Server层：

连接器：

     由图中可以看到，每一个客户端都是和Server层的连接器建立连接，连接器负责客户端与数据库建立连接，获取权限，维持和管理连接。通过以下命令来建立连接：

mysql -h$ip -P$port -u$user -p

在这行命令之后，我们在交互命令中输入密码即可建立连接。（强烈不建议直接将密码附在-p后边，会导致密码泄漏）。

连接命令中的 mysql 是客户端工具，用来跟服务端建立连接。在完成经典的 TCP 握手后，连接器就要开始认证你的身份，这个时候用的就是你输入的用户名和密码。

  • 如果用户名或密码不对，你就会收到一个"Access denied for user"的错误，然后客户端程序结束执行。
  • 如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。

这就意味着，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。

       建立连接之后，我们可以通过show processlist来查看已经建立的连接。如果客户端一段时间内没有活跃行为，那么连接器在默认的8个小时后主动断开连接。如果在连接被断开之后，客户端再次发送请求的话，就会收到一个错误提醒： Lost connection to MySQL server during query。这时候如果你要继续，就需要重连，然后再执行请求了。

短连接：每次查询几次之后会断开，再次查询需要重新建立连接。（成本较高）

长连接：长连接会导致内存OOM，导致MySQL异常重启。

那么如何解决长连接导致的OOM问题？

  • 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。

  • 如果你用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

查询缓存：

        这个查询缓存比较好理解，再每一次的查询时，我们都先去看看是否命中缓存，命中则直接返回，提高了系统的响应速度。但是这个功能有一个相当大的弊病，那就是一旦这个表中数据发生更改，那么这张表对应的所有缓存都会失效。

        对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。

        在MySQL 8.0 版本之前，我们可以通过将 参数query_cache_type 设置成 DEMAND，来关闭查询缓存的功能；在MySQL8.0版本之后直接完全删掉了这部分功能。

分析器：

       系统在真正执行你输入的语句之前，必须分析出你的语句想要干嘛？首先通过select关键字得知这是一条查询命令，还包括分析你要查询的是哪张表以及查询条件是什么？同时，分析器必须分析你输入语句的语法正确性。相信我们都遇到过这个错误吧？  “You have an error in your SQL syntax” 

优化器：

        优化器是MySQL用来对你输入的语句在真正执行之前所做的最后一步优化。优化内容包括：选择哪个索引？是否选择索引？多表查询的联合顺序等。 每一种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。

执行器：

       MySQL 通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误，如下所示 (在工程实现上，如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证。查询也会在优化器之前调用 precheck 验证权限)。

  1.  
    mysql> select * from T where ID=10;
  2.  
     
  3.  
    ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

      如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。比如我们这个例子中的表 T 中，ID 字段没有索引，那么执行器的执行流程是这样的：

  • 调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；
  • 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
  • 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

至此，这个语句就执行完成了。

       对于有索引的表，执行的逻辑也差不多。第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的。

     在数据库的慢查询日志中看到一个 rows_examined 的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟 rows_examined 并不是完全相同的。

总结：

MySQL的Server层主要包括：连接器，查询缓存，分析器，优化器，执行器。多个组件的共同配合，我们的SQL命令才可以执行成功。

存储引擎层：

        MySQL的存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

我们来创建一张简单的表：

mysql> create table T(c int) engine=InnoDB

通过engine来指定当前表所使用的数据存储引擎，通俗的说就是这一张表的类型。不同存储引擎的表数据存取方式不同，支持的功能也不同。接下来我们主要介绍 InnoDB、MyISAM、Memory 这三种存储引擎。

InnoDB存储引擎：

      InnoDB是当前MySQL的默认的存储引擎，也是互联网等公司数据库存储引擎的不二选择。

InnoDB的特性如下：

  • 支持数据库事务，在可重复读的隔离级别下，通过MVCC解决了不可重复读的问题，通过间隙锁的引入解决了幻读的问题
  • 支持行级锁和表级锁，默认是行级锁，更小的锁粒度意味着更高的并发度。
  • 支持外键
  • 为处理巨大数据量时的最大性能设计，它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。
  • InnoDB中不保存表的行数（eg:select count(*) from table时，InnoDB需要扫描一遍整个表来计算有多少行）；清空整个表时，InnoDB是一行一行的删除，效率非常慢。
  • InnoDB使用B+ Tree来做索引，查询效率高，支持索引上的范围查询

关于InnoDB的特性，我们在之后的文章中详细阐述。

MyISAM存储引擎：

        在MySQL5.1版本之前，其默认的存储引擎是MyISAM。MyISAM 管理非事务表、是ISAM 的扩展格式。除了提供ISAM里所没有的索引的字段管理等的大量功能、MyISAM 还使用一种表格锁定的机制、来优化多个并发的读写操作。MyISAM 提供高速存储和检索、以及全文搜索能力。

MyIASM存储引擎的特性如下：

  • 不支持事务、不具备AICD特性(原子性、一致性、分离性、永久性)
  • 表级别锁定形式(更新数据时锁定整个表、这样虽然可以让锁定的实现成本很小但是同时大大降低了其并发的性能)
  • 读写相互阻塞(不仅会在写入的时候阻塞读取、还会在读取的时候阻塞写入、但是读取不会阻塞读取)
  • 只会缓存索引(myisam通过key_buffer_size来设置缓存索引，提高访问性能较少磁盘IO的压力、但是只缓存索引、不缓存数据)
  • 读取速度快、占用资源比较少
  • 不支持外键约束、只支持全文检索

 MyIASM存储引擎的应用场景：

  • 不需要事务支持的场景
  • 读多或者写多的单一业务场景、读写频繁的则不适合、会阻塞
  • 读写并发访问较低的业务
  • 数据修改相对较少的业务
  • 以读为主的业务
  • 对数据的一致性要求不是很高的业务
  • 服务器硬件资源相对比较差的机器

Memory存储引擎：

       数据库中的表如果使用了Memory存储引擎，那么也可以将这张表称为内存表。为了说明内存表的相关特性，我们先来创建两张表：

  1.  
    create table t1(id int primary key, c int) engine=Memory;
  2.  
    create table t2(id int primary key, c int) engine=innodb;
  3.  
    insert into t1 values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0);
  4.  
    insert into t2 values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0);

t1是一张内存表，t2则是一张InnoDB表，我们分别插入了同样的数据。并且执行 select * from t1 和 select * from t2

       可以看到，内存表 t1 的返回结果里面 0 在最后一行，而 InnoDB 表 t2 的返回结果里 0 在第一行。这就是内存表的一个特性，内存表的索引使用了hash索引，InnoDB表则使用了B+ Tree索引。

       如果你熟悉InnoDB的B+ Tree索引（不熟悉也没关系，后边文章会介绍），则肯定知道t2表的数据组织方式为一颗B+ Tree，并且其主键索引树上的子节点包含了所有的数据，并且是有序的。所以从t2表中查询所有数据，结果是有序的。

       内存表的数据和索引是分开的，数据部分以数组的方式单独存放。主键id是一个hash索引，索引上的 key 并不是有序的。在内存表 t1 中，当我执行 select * 的时候，走的是全表扫描，也就是顺序扫描这个数组。因此，0 就是最后一个被读到，并放入结果集的数据。

InnoDB 和 Memory 引擎的数据组织方式是不同的：

  • InnoDB引擎把数据放在主键索引上，其他索引上保存的是主键 id。这种方式，我们称之为索引组织表（Index Organizied Table）
  • Memory引擎采用的是把数据单独存放，索引上保存数据位置的数据组织形式，我们称之为堆组织表（Heap Organizied Table）

InnoDB和Memory的不同：

  • InnoDB 表的数据总是有序存放的，而内存表的数据就是按照写入顺序存放的

  • 当数据文件有空洞的时候，InnoDB 表在插入新数据的时候，为了保证数据有序性，只能在固定的位置写入新值，而内存表找到空位就可以插入新值

  • 数据位置发生变化的时候，InnoDB 表只需要修改主键索引，而内存表需要修改所有索引

  • InnoDB 表用主键索引查询时需要走一次索引查找，用普通索引查询的时候，需要走两次索引查找。而内存表没有这个区别，所有索引的“地位”都是相同的

  • InnoDB 支持变长数据类型，不同记录的长度可能不同；内存表不支持 Blob 和 Text 字段，并且即使定义了 varchar(N)，实际也当作 char(N)，也就是固定长度字符串来存储，因此内存表的每行数据长度相同。

为了验证上边的不同点2，我们将t1表中的数据id=5删除，并且插入新的id=10，查询全部结果观察。执行的SQL语句如下：

  1.  
    delete from t1 where id=5;
  2.  
    insert into t1 values(10,10);
  3.  
    select * from t1;

结果如下：

由返回结果我们可以看出，id=10 这一行出现在 id=4 之后，也就是原来 id=5 这行数据的位置。

        Memory表的主键索引是哈希索引，缺点是执行范围查询如：select * from t1 where id<5; 会走全表扫描，效率低下。

解决办法：Memory表也支持B+ Tree的索引方式，通过如下的语句可以在id列上同时建立B+ Tree索引。

alter table t1 add index a_btree_index using btree (id);

由结果我们可以看出，当前查找出来的数据是有序的，证实了当前确实建立了B+ Tree的索引，并且优化器在范围查询的时候帮我们选择了B+ Tree索引。 我们可以强制使用默认的主键索引方式来验证：

      图中我们使用了force语句强行指定使用的主键默认索引（hash索引），再次走了全表扫描，返回了无序（和插入数据顺序有关）的数据。

        通过名字内存表，我们可以知道Memory存储引擎的表数据都存在内存中，通过参数max_heap_table_size控制Memory表的大小，读写速度很快。并且内存表支持hash索引和B+ Tree索引，但是我们为什么一般不会使用Memory做为表的存储引擎呢？主要考虑以下两点：

  • 锁的粒度问题
  • 数据持久化问题

（1）锁的粒度问题：

内存表不支持行锁，只支持表锁。因此，一张表只要有更新，就会堵住其他所有在这个表上的读写操作

（2）数据持久化问题：

数据放在内存中，是内存表的优势，但也是一个劣势。因为，数据库重启的时候，所有的内存表都会被清空。特别是在主从结构下，主库正常更新update数据，但是从库异常重启，导致数据丢失，从库重启后，同步主库的内容会由于找不到该数据行而报错，导致主从同步停止。

基于Memory表的特点和缺点，哪些场景适合使用Memory做为其存储引擎呢？

答：在数据量可控的情况下，我们所需的临时表可以使用内存表。

内存临时表刚好可以无视内存表的两个不足，主要是下面的三个原因：

  • 临时表不会被其他线程访问，没有并发性的问题

  • 临时表重启后也是需要删除的，清空数据这个问题不存在

  • 备库的临时表也不会影响主库的用户线程。

总结：

在存储引擎层我们介绍了InnoDB，MyIASAM以及Memory存储引擎，由于InnoDB和MyIASM比较常见和常用，我们后边改回继续介绍，所以这里着重介绍了Memory内存表的相关特点和使用场景。总之，不同的存储引擎提供了不同的数据读写接口。

 

文章来源：

https://blog.csdn.net/qq_25827845/article/details/90544530

posted @ 2020-11-15 17:48  小南的歌  阅读(427)  评论(0)  编辑  收藏  举报