精华内容
下载资源
问答
  • (尊重劳动成果,转载请注明出处:https://yangwenqiang.blog.csdn.net/article/details/90544530冷血...MySQL原理与实践(一):一条select语句引出Server层和存储引擎层 MySQL原理与实践(二):一条update语句引...

    (尊重劳动成果,转载请注明出处:https://yangwenqiang.blog.csdn.net/article/details/90544530冷血之心的博客)

    关注微信公众号(文强的技术小屋),学习更多技术知识,一起遨游知识海洋~

    快速导航:

     MySQL原理与实践(一):一条select语句引出Server层和存储引擎层

    MySQL原理与实践(二):一条update语句引出MySQL日志系统

    MySQL原理与实践(三):由三种数据结构引入MySQL索引及其特性

    MySQL原理与实践(四):由数据库事务引出数据库隔离级别

    MySQL原理与实践(五):数据库的锁机制

    MySQL原理与实践(六):自增主键的使用

    目录

    前言:

    正文:

    MySQL基本逻辑架构图:

    Server层:

    连接器:

    查询缓存:

    分析器:

    优化器:

    执行器:

    总结:

    存储引擎层:

    InnoDB存储引擎:

    InnoDB的特性如下:

    MyISAM存储引擎:

    MyIASM存储引擎的特性如下:

    MyIASM存储引擎的应用场景:

    Memory存储引擎:

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

    InnoDB和Memory的不同:

    总结:

    结束语:


    前言:

           在这篇博文之前,博主也曾写过一些入门级别的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 验证权限)。

    mysql> select * from T where ID=10;
     
    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存储引擎,那么也可以将这张表称为内存表。为了说明内存表的相关特性,我们先来创建两张表:

    create table t1(id int primary key, c int) engine=Memory;
    create table t2(id int primary key, c int) engine=innodb;
    insert into t1 values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0);
    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语句如下:

    delete from t1 where id=5;
    insert into t1 values(10,10);
    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内存表的相关特点和使用场景。总之,不同的存储引擎提供了不同的数据读写接口。

     

    结束语:

     这篇文章是MySQL原理与实践系列文章的第一篇,文中内容整理总结于博主的学习与实践中,参考了网上各位同学的成果。之后会继续更新该系列文章,希望对大家有帮助。

     

    如果对你有帮助,记得点赞哦~欢迎大家关注我的博客,可以进群366533258一起交流学习哦~

    本群给大家提供一个学习交流的平台,内设菜鸟Java管理员一枚、精通算法的金牌讲师一枚、Android管理员一枚、蓝牙BlueTooth管理员一枚、Web前端管理一枚以及C#管理一枚。欢迎大家进来交流技术。
     

    关注微信公众号(文强的技术小屋),学习更多技术知识,一起遨游知识海洋~

    展开全文
  • 设计MySQL的大叔人为的把MySQL分为server层和存储引擎层,但是什么操作是在server层做的,什么操作是在存储引擎层做的大家可能有些迷糊。本文将以一个实例来展示它们二者各自负责的事情。 准备工作 为了故事的...

    SQL的全称是Structured Query Language,翻译成中国话就是结构化查询语言。这是一种声明式的语法,何为声明式?可以联想一下我们生活中的老板,老板在布置任务的时候会告诉你:小王啊,今天把这些砖从A地搬到B地啊,然后就没然后了。老板并不关心你是用手抬,还是用车拉,老板只关心结果:你把砖搬过去就好了。我们之于数据库而言,就是一个老板,SQL语句就是我们给数据库下达的任务,至于具体数据库怎么执行我们并不关心,我们只关心最后数据库给我们返回的结果。

    对于设计数据库的人而言,语句怎么执行就得好好考虑了,老板不操心,事儿总还得干。设计MySQL的大叔人为的把MySQL分为server层和存储引擎层,但是什么操作是在server层做的,什么操作是在存储引擎层做的大家可能有些迷糊。本文将以一个实例来展示它们二者各自负责的事情。

    准备工作

    为了故事的顺利发展,我们先创建一个表:

    CREATE TABLE hero (
        id INT,
        name VARCHAR(100),
        country varchar(100),
        PRIMARY KEY (id),
        KEY idx_name (name)
    ) Engine=InnoDB CHARSET=utf8;
    

    我们为hero表的id列创建了聚簇索引,为name列创建了一个二级索引。这个hero表主要是为了存储三国时的一些英雄,我们向表中插入一些记录:

    INSERT INTO hero VALUES
        (1, 'l刘备', '蜀'),
        (3, 'z诸葛亮', '蜀'),
        (8, 'c曹操', '魏'),
        (15, 'x荀彧', '魏'),
        (20, 's孙权', '吴');
    

    现在表中的数据就是这样的:

    mysql> SELECT * FROM hero;
    +----+------------+---------+
    | id | name       | country |
    +----+------------+---------+
    |  1 | l刘备      | 蜀      |
    |  3 | z诸葛亮    | 蜀      |
    |  8 | c曹操      | 魏      |
    | 15 | x荀彧      | 魏      |
    | 20 | s孙权      | 吴      |
    +----+------------+---------+
    5 rows in set (0.00 sec)
    

    准备工作就做完了。

    正文

    一条语句在执行之前需要生成所谓的执行计划,也就是该语句将采用什么方式来执行(使用什么索引,采用什么连接顺序等等),我们可以通过Explain语句来查看这个执行计划,比方说对于下边语句来说:

    mysql> EXPLAIN SELECT * FROM hero WHERE name < 's孙权' AND country = '蜀';
    +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
    | id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                              |
    +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
    |  1 | SIMPLE      | hero  | NULL       | range | idx_name      | idx_name | 303     | NULL |    2 |    20.00 | Using index condition; Using where |
    +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
    1 row in set, 1 warning (0.03 sec)
    

    输出结果的key列值为idx_name,type列的值为range,表明会针对idx_name二级索引进行一个范围查询。很多同学在这里有一个疑惑:到底是一次性把所有符合条件的二级索引都取出来之后再统一进行回表操作,还是每从二级索引中取出一条符合条件的记录就进行回表一次?其实server层和存储引擎层的交互是以记录为单位的,上边这个语句的完整执行过程就是这样的:

    1. server层第一次开始执行查询,把条件name < 's孙权’交给存储引擎,让存储引擎定位符合条件的第一条记录。

    2. 存储引擎在二级索引idx_name中定位name < ‘s孙权’的第一条记录,很显然符合该条件的二级索引记录的name列的值为’c曹操’。然后需要注意,我们看到EXPLAIN语句的输出结果的Extra列有一个Using index condition的提示,这表明会将有关idx_name二级索引的查询条件放在存储引擎层判断一下,这个特性就是所谓的索引条件下推(Index Condition Pushdown,简称ICP)。很显然这里的ICP条件就是name < ‘s孙权’。有的同学可能会问这不就是脱了裤子放屁么,name值为’c曹操’的这条记录就是通过name < 's孙权’这个条件定位的,为啥还要再判断一次?这就是设计MySQL 的大叔的粗暴设计,十分简单,没有为啥~

    小贴士

    对于使用二级索引进行等值查询的情况有些许不同,比方说上边的条件换成name = 's孙权',对于等值查询的这种情况,设计MySQL的大叔在InnoDB存储引擎层有特殊的处理方案,是不作为ICP条件进行处理的。

    然后拿着该二级索引记录中的主键值去回表,把完整的用户记录都取到之后返回给server层(也就是说得到一条二级索引记录后立即去回表,而不是把所有的二级索引记录都拿到后统一去回表)。

    1. 我们的执行计划输出的Extra列有一个Using Where的提示,意味着server层在接收到存储引擎层返回的记录之后,接着就要判断其余的WHERE条件是否成立(就是再判断一下country = '蜀’是否成立)。如果成立的话,就直接发送给客户端。

    小贴士:

    什么?发现一条记录符合条件就发送给了客户端?那为什么我的客户端不是一条一条的显示查询结果,而是一下子全部展示呢?这是客户端软件的鬼,人家规定在接收完全部的记录之后再展示而已。

    如果不成立的话,就跳过该条记录。

    1. 接着server层向存储引擎层要求继续读刚才那条记录的下一条记录。

    2. 因为每条记录的头信息中都有next_record的这个属性,所以可以快速定位到下一条记录的位置,然后继续判断ICP条件,存储引擎把下一条记录取出后就将其返回给server层。

    3. 然后重复第3步的过程,直到存储引擎层遇到了不符合name < 's孙权’的记录,然后向server层返回了读取完毕的信息,这是server层将结束查询。

    这个过程用语言描述还是有点儿啰嗦,我们写一个超级简化版的伪代码来瞅瞅(注意,是超级简化版):

    first_read = true;  //是否是第一次读取
    while (true) {
    
        if (first_read) {
            first_read = false;
            err = index_read(...);  //调用存储引擎接口,定位到第一条符合条件的记录;
        } else {
            err = index_next(...); //调用存储引擎接口,读取下一条记录
        }
        
        if (err = 存储引擎的查询完毕信息) {
            break;  //结束查询
        }
        
        if (是否符合WHERE条件) {
            send_data();    //将该记录发送给客户端;
        } else {
            //跳过本记录
        }
    }
    

    上述的伪代码虽然很粗糙,但也基本表明了意思哈~ 之后有机会我们再唠叨唠叨使用临时表的情况已经使用filesort的情况是怎么执行的。

    (想自学习编程的小伙伴请搜索圈T社区,更多行业相关资讯更有行业相关免费视频教程。完全免费哦!)

    展开全文
  • 的记录,然后向server层返回了读取完毕的信息,这时server层将结束查询。 这个过程用语言描述还是有点儿啰嗦,我们写一个超级简化版的伪代码来瞅瞅(注意,是超级简化版): first_read = true ; //是否是第一次...

    点击上方石杉的架构笔记,右上选择“设为星标

    每日早8点半,精品技术文章准时送上


    640?wx_fmt=png

    往期文章

    BAT 面试官是如何360°无死角考察候选人的(上篇)

    每秒上万并发下的Spring Cloud参数优化实战

    分布式事务如何保障实际生产中99.99%高可用

    记一位朋友斩获 BAT 技术专家Offer的面试经历

    亿级流量架构系列之如何支撑百亿级数据的存储与计算

    640?wx_fmt=png


    本文来源:我们都是小青蛙


    SQL的全称是Structured Query Language,翻译成中国话就是结构化查询语言。这是一种声明式的语法。

    何为声明式?

    可以联想一下我们生活中的老板,老板在布置任务的时候会告诉你:小王啊,今天把这些砖从A地搬到B地啊,然后就没然后了。

    老板并不关心你是用手抬,还是用车拉,老板只关心结果:你把砖搬过去就好了。

    我们之于数据库而言,就是一个老板,SQL语句就是我们给数据库下达的任务,至于具体数据库怎么执行我们并不关心,我们只关心最后数据库给我们返回的结果。

    对于设计数据库的人而言,语句怎么执行就得好好考虑了,老板不操心,事儿总还得干。

    设计MySQL的大叔人为的把MySQL分为server层和存储引擎层,但是什么操作是在server层做的,什么操作是在存储引擎层做的大家可能有些迷糊。

    本文将以一个实例来展示它们二者各自负责的事情。

    准备工作

    为了故事的顺利发展,我们先创建一个表:

    CREATE TABLE hero (
    id INT,
    name VARCHAR(100),
    country varchar(100),
    PRIMARY KEY (id),
    KEY idx_name (name)
    ) Engine=InnoDB CHARSET=utf8;

    我们为hero表的id列创建了聚簇索引,为name列创建了一个二级索引。

    这个hero表主要是为了存储三国时的一些英雄,我们向表中插入一些记录:

    INSERT INTO hero VALUES
    (1, 'l刘备', '蜀'),
    (3, 'z诸葛亮', '蜀'),
    (8, 'c曹操', '魏'),
    (15, 'x荀彧', '魏'),
    (20, 's孙权', '吴');

    现在表中的数据就是这样的:

    mysql> SELECT * FROM hero;
    +----+------------+---------+
    | id | name | country |
    +----+------------+---------+
    | 1 | l刘备 | 蜀 |
    | 3 | z诸葛亮 | 蜀 |
    | 8 | c曹操 | 魏 |
    | 15 | x荀彧 | 魏 |
    | 20 | s孙权 | 吴 |
    +----+------------+---------+
    5 rows in set (0.00 sec)

    准备工作就做完了。

    正文

    一条语句在执行之前需要生成所谓的执行计划,也就是该语句将采用什么方式来执行(使用什么索引,采用什么连接顺序等等)

    我们可以通过Explain语句来查看这个执行计划,比方说对于下边语句来说:

    mysql> EXPLAIN SELECT * FROM hero WHERE name < 's孙权' AND country = '蜀';
    +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
    | 1 | SIMPLE | hero | NULL | range | idx_name | idx_name | 303 | NULL | 2 | 20.00 | Using index condition; Using where |
    +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
    1 row in set, 1 warning (0.03 sec)

    输出结果的key列值为idx_nametype列的值为range,表明会针对idx_name二级索引进行一个范围查询。

    很多同学在这里有一个疑惑:到底是一次性把所有符合条件的二级索引都取出来之后再统一进行回表操作,还是每从二级索引中取出一条符合条件的记录就进行回表一次?

    其实server层和存储引擎层的交互是以记录为单位的,上边这个语句的完整执行过程就是这样的:

    server层第一次开始执行查询,把条件name < 's孙权'交给存储引擎,让存储引擎定位符合条件的第一条记录。

    存储引擎在二级索引idx_name中定位name < 's孙权'的第一条记录,很显然第一条符合该条件的二级索引记录的name列的值为'c曹操'

    然后需要注意,我们看到EXPLAIN语句的输出结果的Extra列有一个Using index condition的提示,这表明会将有关idx_name二级索引的查询条件放在存储引擎层判断一下。

    这个特性就是所谓的索引条件下推(Index Condition Pushdown,简称ICP)。很显然这里的ICP条件就是name < 's孙权'

    有的同学可能会问这不就是脱了裤子放屁么,name值为'c曹操'的这条记录就是通过name < 's孙权'这个条件定位的,为啥还要再判断一次?这就是设计MySQL 的大叔的粗暴设计,十分简单,没有为啥~

    小贴士: 对于使用二级索引进行等值查询的情况有些许不同,比方说上边的条件换成`name = 's孙权'`,对于等值查询的这种情况,设计MySQL的大叔在InnoDB存储引擎层有特殊的处理方案,是不作为ICP条件进行处理的。

    然后拿着该二级索引记录中的主键值去回表,把完整的用户记录都取到之后返回给server层(也就是说得到一条二级索引记录后立即去回表,而不是把所有的二级索引记录都拿到后统一去回表)。

    我们的执行计划输出的Extra列有一个Using Where的提示,意味着server层在接收到存储引擎层返回的记录之后

    接着就要判断其余的WHERE条件是否成立(就是再判断一下country = '蜀'是否成立)。如果成立的话,就直接发送给客户端。

    小贴士: 什么?发现一条记录符合条件就发送给了客户端?那为什么我的客户端不是一条一条的显示查询结果,而是一下子全部展示呢?这是客户端软件的鬼,人家规定在接收完全部的记录之后再展示而已。

    如果不成立的话,就跳过该条记录。

    接着server层向存储引擎层要求继续读刚才那条记录的下一条记录。

    因为每条记录的头信息中都有next_record的这个属性,所以可以快速定位到下一条记录的位置,然后继续判断ICP条件,然后进行回表操作,存储引擎把下一条记录取出后就将其返回给server层。

    然后重复第3步的过程,直到存储引擎层遇到了不符合name < 's孙权'的记录,然后向server层返回了读取完毕的信息,这时server层将结束查询。

    这个过程用语言描述还是有点儿啰嗦,我们写一个超级简化版的伪代码来瞅瞅(注意,是超级简化版):

    first_read = true;  //是否是第一次读取
    while (true) {

    if (first_read) {
    first_read = false;
    err = index_read(...); //调用存储引擎接口,定位到第一条符合条件的记录;
    } else {
    err = index_next(...); //调用存储引擎接口,读取下一条记录
    }

    if (err = 存储引擎的查询完毕信息) {
    break; //结束查询
    }

    if (是否符合WHERE条件) {
    send_data(); //将该记录发送给客户端;
    } else {
    //跳过本记录
    }
    }

    上述的伪代码虽然很粗糙,但也基本表明了意思哈~ 之后有机会我们再唠叨唠叨使用临时表的情况以及使用filesort的情况是怎么执行的。


    END


    划至底部,点击“在看”,是你来过的仪式感!

    640?wx_fmt=png



    推荐阅读





    更多文章:

    640?wx_fmt=png


    欢迎长按下图关注公众号石杉的架构笔记

    640?wx_fmt=jpeg

    BAT架构经验倾囊相授

    640?wx_fmt=gif

    展开全文
  • (1)各简介 Dao(mapper) service(业务) controller(控制) Dao 即数据持久,对数据做持久化操作。也被称为mapper。声明为接口。 dao的作用为访问数据库,向数据库发送sql语句,完成数据...

    (1)各层简介
    Dao层(mapper层)
    service层(业务层)
    controller层(控制层)
    Dao层
    即数据持久层,对数据做持久化操作。也被称为mapper层。声明为接口。
    dao层的作用为访问数据库,向数据库发送sql语句,完成数据的增删改查任务。
    Service层
    业务层,service层的作用为完成功能设计。存放业务逻辑处理,不直接对数据库进行操作,有接口和接口实现类,提供controller层调用的方法。
    调用dao层接口,接收dao层返回的数据,完成项目的基本功能设计。(也就是说对于项目中的功能的需求就是在这里完成的)(对Dao层接口的实现)
    Controller层
    控制器层,controller层的功能为请求和响应控制。
    controller层负责前后端交互,接受前端请求,调用service层,接收service层返回的数据,最后返回具体的页面和数据到客户端。
    (2)各层理解
    在这里插入图片描述
    Controller层像是一个服务员,他把客人(前端)点的菜(数据、请求的类型等)进行汇总什么口味、咸淡、量的多少,交给厨师长(Service层),厨师长则告诉沾板厨师(Dao 1)、汤料房(Dao 2)、配菜厨师(Dao 3)等(统称Dao层)我需要什么样的半成品,副厨们(Dao层)就负责完成厨师长(Service)交代的任务。

    展开全文
  • 在Java中,结合ssh建构,这是现在最基本的分层方式了 modle就是对应的数据库表的实体类。  ...Action:引用对应的Service,在这里结合Struts的配置文件,跳转到指定的页面,当然也能接受页
  • Server 及整个MySQL体系图: InnoDB 存储引擎体系结构图:
  • 快速生成C# SQL server架构代码,项目源码,可自行修改
  • MTK sensorServer层到HAL层、驱动层解析

    千次阅读 2017-06-07 17:21:32
    我们这里首先从SensorServer.cpp开始分析: (本人项目文件的路径:.\frameworks\native\services\sensorservice\SensorServer.cpp) 1.enable() 在这里我们可以先去找enable()函数,在这里我们可以看出来其实...
  • thinkphp5.1抽离出server逻辑

    千次阅读 2019-06-10 10:36:22
    (目前只找到这样一个方法,有更方便简单的可以留言) 1.在application中创建common目录, 以及创建service目录。 也就是application/common/service 2.(选) 在controller文件目录新建Base文件,加载所有...
  • 用于解决SQLSERVER连接问题驱动程序无法通过使用安全套接字(SSL)加密与 SQL Server 建立安全连接问题JAR包。
  • Windows_server_2008R2安装金蝶K3 WISE中间安装配置的详细教程讲解。
  • 要想通过面试,MySQL的Limit子句底层原理你不可不知

    万次阅读 多人点赞 2021-10-12 17:02:22
    MySQL的Limit子句底层原理如何分析,Limit子句在哪一步骤才执行?这一篇,我们得从从server层和存储引擎层进行分析...
  • 解决Could not generate DH keypair 和 Unsupported curveId :29问题
  • Windows Server 2016,它可以理解为服务器版的 Windows 10,宣告整个核心架构定型稳定。Windows Server 2016 是微软推出的第六个 Windows server 版本,也是 Windows 10 的服务器版本。据了解,与前代不同的是,...
  • 解决SQLSERVER数据库驱动程序无法通过使用安全套接字(SSL)加密与 SQL Server 建立安全连接问题JAR包
  • 我们在做递归查询的时候,有时候需要查询特定某一级的数据,如果我们的数据上没有标注该数据的层级数,那么我们可以在递归的时候自己加一个,并在读取的时候作为查询条件来用,测试数据:--测试数据 ...
  • sqlserver实现层级树形查询

    千次阅读 2017-08-09 08:13:43
    本sql已经经过试验,请放心使用(无毒) WITH T AS ( SELECT *,CAST(dept_id AS VARBINARY(MAX)) AS px FROM comm_dept AS A WHERE NOT EXISTS(SELECT * FROM comm_dept WHERE dept_id=A.[parent_dept_...
  • SQL Server卸载不干净和重新安装问题

    万次阅读 多人点赞 2019-05-06 17:41:25
    SQL Server卸载不干净和重新安装问题 经历了半天的摸索和尝试,结合前辈们的经历,终于重新装好了SQL Server2017,因为我发现网上 的博客基本没有此类的问题总结,那就我遇到的各种问题,把卸载及安装的步骤在这篇...
  • Proxy-Server

    千次阅读 2018-05-06 22:20:31
    VPS部署在国外,Virtual Private Server虚拟专用服务器 )技术,将一台服务器分割成多个虚拟专享服务器的优质服务,简单理解VPS就是一台拥有公网的IP的服务器,进行翻墙。 (3)封锁HTTP代理 对于没有办法搭建...
  • 为什么dao和service要写接口和实现类

    万次阅读 多人点赞 2019-05-11 15:23:16
    是让表示不依赖于业务的具体实现。为什么要用DAO接口?是让业务不依赖于持久的具体实现。举个例子,用DAO接口,那么持久用Hibernate,还是用iBatis,还是 JDBC,随时可以替换,不用修改业务Service类的...
  • SpringBoot注入Server失败==>>解决办法

    千次阅读 2018-07-05 10:28:20
    在Text测试上面调用Server就能调用到如下       但是在另外一个类上调用Server就调用不到,如下     我的server实现也没错,如果有错的话Text测试上的Server不可能有值接下来我们来看下Server如下  ...
  • 大家都知道 能在Controller/action获取 HttpServletRequest , 但是这里给大家备份的 是 从代码内部 service获取HttpServletRequest 工具类。 具体如下: package com.base.common.sessionutils; import ...
  • SSM是sping+springMVC+...存放业务逻辑处理,也是一些关于数据库处理的操作,但不是直接和数据库打交道,他有接口还有接口的实现方法,在接口的实现方法中需要导入mapper,mapper是直接跟数据库打交道的,他也是...
  • com.microsoft.sqlserver.jdbc.SQLServerException: 驱动程序无法通过使用安全套接字(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client ...
  • 1 本人新手,刚开始学习SringMVC框架,需要各个之间的注入。 举个例子: 最初,在dao实现service的方式是,new出来一个(每个方法都要new一次)。 IServiceImpl service =new IServiceImpl(); service.fill...
  • 根据项目的需要,查询树形的数据并且需要按照主键id字段进行降序排列。 查阅了几篇博客,大多是按照默认的排序方式进行排序,无奈只有自己去写了,本人是比较懒的 直接附上sql的案例: WITH T AS ...
  • 解决com.microsoft.sqlserver.jdbc.SQLServerException: 驱动程序无法通过使用安全套接字(SSL)加密与 SQL Server 建立安全连接。错误Caused by: java.io.IOException: SQL Server 未返回响应。连接已关闭。
  • 此方法适用于JDK1.8,应用场景常见于window server操作系统 ...驱动程序无法通过使用安全套接字(SSL)加密与 SQL Server 建立安全连接。 java.security.cert.CertificateException: Certificates does not confor...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 626,728
精华内容 250,691
关键字:

server层