网络知识 娱乐 SQL Server内部运行机制浅析

SQL Server内部运行机制浅析

发布: 2023年2月14日 10:17:50

引言:

SQL Server是由微软开发和推广的关系数据库管理系统（DBMS），它最初是由Microsoft、Sybase和Ashton-Tate三家公司共同开发的，并于1988年推出了第一个OS/2版本。Microsoft SQL Server近年来不断更新版本，1996年，Microsoft 推出了SQL Server 6.5版本；1998年，SQL Server 7.0版本和用户见面；SQL Server 2000是Microsoft公司于2000年推出，目前最新版本是SQL SERVER 2017，以出色的性能和事务处理能力，与Oracle、Mysql构成了当前三大主流的关系数据库。

对于已经很熟悉T-SQL的读者，或者对于较专业的DBA来说，逻辑的增删改查，或者较复杂的SQL语句，都是非常简单的，不存在任何挑战，不值得一提，那么，SQL的哪些方面是他们的挑战 或者软肋呢？

那就是sql优化。然而，要向成为一个好的Sql优化高手，首先要做的一件事无疑就是了解sql语句在SQL Server中是如何执行的。在这一系列中，我们将开始sqlserver优化系列讲解，本 讲为优化系列的开篇文章，

在本篇文章中，我们将重点讲解SQL Server体系结构

在正式讲解之前，我们先来看看如下问题，你是否遇到过，若你遇到过且成功解决，那么这篇文章，你可以跳过。

为了测试需要，我们先模拟插入5亿3千多万条数据。

1

SELECT COUNT(1) FROM BigDataTest

SQL Server内部运行机制浅析

（一）查询缓慢问题

*，临时表，表连接，子查询等造成的查询缓慢问题，你能解决吗？

（二）内存泄漏

如下查询了8分2秒，然后内存溢出，你知道问题吗？

1

SELECT * FROM BigDataTest

SQL Server内部运行机制浅析

（三）经常听说如下概念，你都能解决吗？

事务与锁(请参考我另一篇文章：浅谈SQL Server事务与锁（上篇）)，ACID，隔离级别，脏读，分表分库，水平拆分，垂直拆分，高并发等

一 SQL Server体系结构抽象

SQL Server内部运行机制浅析

二 SQL Server体系结构概述

SQL Server核心体系结构，大致包括六大部分：客户端访问工具、SQL Server 网络接口(SQL Server Network Interface,SNI)、关系引擎、存储引擎、

磁盘和缓冲池。下图为SQL Server核心体系大致轮廓图。

SQL Server内部运行机制浅析

（一）SQL Server客户端访问工具

SQL Server内部运行机制浅析

SQL Server客户端访问工具，提供了远程访问技术，它与SQL Server服务端基于一定的协议，使其能够远程访问数据库，就像在本地操作数据库一样，如我们经常用的

Microsoft SQL Server Management Studio。

SQL Server客户端访问工具是比较多的，其中比较流行的要数Microsoft SQL Server Management Studio 和Navicat(Navicat在MySQL中也是比较常用的)了，至于其他工具，

本篇文章就不列举了，感兴趣的读者朋友，可以查询一下。

（二）SQL Server网络协议

SQL Server内部运行机制浅析

SQL Server网络协议，又叫SQL Server网络接口(SNI)，它是构成客户端和服务端通信的桥梁，它与SQL Server服务端基于一定协议，方可通信，

如我们在客户端输入一条查询语句SELECT * FROM BigDataTest，这条语句，只有客户端和服务端基于一定协议，方可被服务端解析，否则，被视为无

效语句。

SQL Server网络协议，由一组API构成，这些API供SQL Server数据库引擎和SQL Server本地客户端调用，如实现最基本的CRUD通信。

SQL Server 网络接口(SQL Server Network Interface,SNI)只需要在客户端和服务端配置网络协议即可，它支持一下协议：

（1）共享内存

（2）TCP/IP

（3）命名管道

（4）VIA

（三）关系引擎

SQL Server内部运行机制浅析

关系引擎，也叫查询引擎，其主要功能是负责处理SQL语句，其核心组件由三部分组成：命令分析器、查询优化器和查询执行器。

（1）命令分析器：负责解析客户端传递过来的T-SQL语句，如客户端传递一条SQL语句：SELECT * FROM BigDataTest，它会检查该语句的语法结构，若语法

错误，它会将错误返回给协议层，然后协议层将错误返回给客户端；如果语法结构正确，它会根据查询命令生成查询计划或寻找一个已存在的查询计划(先在缓冲池计划缓

存中查找，若找到，则直接给查询执行器执行，若未找到，则会生成基于T-SQL的查询树，然后交给查询优化器优化)

（2）查询优化器：负责优化命令解析器生成的T-SQL查询树(基于资源的优化，而非基于时间的优化)，然后将最终优化结果传递给查询执行器执行。查询优化器是基于

“资源开销”的优化器，这种算法评估多种可执行的查询方式，并从中选择开销最低的方案作为优化结果，然后将该结果生成查询计划输出给查询执行器。注意，查询优化器是

“基于资源开销最优”而非“基于方案最优”，也就是，查询优化器的最终优化结果未必是最好的方案，但一定是资源开销最低的方案。

（3）查询执行器：负责执行查询。假若查询执行器接收到命令解析器或查询优化器传递过来的SQL语句：SELECT * FROM BigDataTest，它通过OLE DB接口传递到存储

引擎，再传递到存储引擎的访问方法。

（四）存储引擎

SQL Server内部运行机制浅析

存储引擎，本质就是管理资源存储的，它的核心组件包括三部分：访问方法、事务管理器和缓冲区管理器。

（1）访问方法：访问方法本质是一个接口，供查询执行器调用(该接口提供了所有检索数据的代码，接口的实际执行是由缓冲区管理器来执行的)，假若查询执行器传递一条SQL语句：

SELECT * FROM BigDataTest，访问方法接收到该请求命令后，就会调用缓冲区管理器，缓冲区管理器就会调用缓冲池的计划缓存，在计划缓存中寻找到相应的结果集，然后返回给关系

引擎。

（2）缓冲区管理器：供访问方法调用，管理缓冲池，在缓冲池中查询相应资源并返回结果集，供访问方法返回给关系引擎。

（3）事务管理器：主要负责事务的管理(ACID管理)和高并发管理(锁)，它包括两个核心组件(日志管理器和锁管理器)，锁管理器负责提供并发数据访问，设置隔离级别等；日志管理器负责

记录所有访问方法操作动作，如基本的CRUD。

（五）缓冲池

SQL Server内部运行机制浅析

缓冲池驻于内存中，是磁盘和缓冲区管理器的桥梁SQL Server中，所有资源的查询都是在内存中进行的，即在缓冲池中进行的，假若缓冲池

接收到缓冲区管理器传递过来的的一条SQL语句：SELECT * FROM BigDataTest，缓冲区管理器数据缓存先从磁盘数据库中取满足条件的结果集，

然后放在缓冲池数据缓冲中，然后以结果集的形式返回给缓冲区管理器，供访问方法返回给关系引擎的查询执行器，然后返回给协议层，协议层再

返回给客户端。注意，这里操作的是缓冲池中数据，而不是磁盘DB中的数据，并且操作的缓冲池数据不会立即写入磁盘，因此就会造成查询到结果

与BD中的结果不一致，这就是所谓的脏读。

缓冲池主要包括两部分：计划缓存(生成执行计划是非常耗时耗资源的，计划缓存主要用来存储执行计划，以备后续使用)和数据缓存(通常是缓存池

中容量最大的，消耗内存最大，从磁盘中读取的数据页只要放在这里，方可调用)

（六）磁盘

SQL Server内部运行机制浅析

磁盘主要是用来存储持久化资源的，如日志资源，数据库资源和缓存池持久化支援等。

三 一个查询的完整流程

如下为一个比较完善的查询过程，即第二部分查询语句：SELECT * FROM BigDataTest 整个过程。

SQL Server内部运行机制浅析

四 参考文献

【01】《SQL Server 2012 深入解析与性能优化 第3版》Christian Bolton,Justin Langford,Glenn Berry,Gavin Payne,Amit Banerjee,Rob Farley著

来源：
http://www.cnblogs.com/wangjiming/p/10098061.html

分享数据库基础知识

数据库是“按照数据结构来组织、存储和管理数据的仓库”。是一个长期存储在计算机内的、有组织的、有共享的、统一管理的数据集合。数据库是以一定方式储存在一起、能与多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的数据集合，可视为电子化的文件柜——存储电子文件的处所，用户可以对文件中的数据进行新增、查询、更新、删除等操作。

数据库就是一种特殊的文件，其中存储着需要的数据

SQL Server内部运行机制浅析

ubuntu中mysql数据库

当前主要使用两种类型的数据库：关系型数据库RDBMS（Relational Database Management System）、非关系型数据库。

数据库的特点:

  • 持久化存储
  • 读写速度极高
  • 保证数据的有效性
  • 对程序支持性非常好，容易扩展

所谓的关系型数据库RDBMS，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。关系型数据库模型是把复杂的数据结构归结为简单的二元关系（即二维表格形式）。在关系型数据库中，对数据的操作几乎全部建立在一个或多个关系表格上，通过这些关联的表格分类、合并、连接或选取等运算来实现数据的管理。就像Excel中的：

SQL Server内部运行机制浅析

关系型数据库的主要产品：

  • oracle：在以前的大型项目中使用,银行,电信等项目
  • mysql：web时代使用最广泛的关系型数据库
  • ms sql server：在微软的项目中使用
  • sqlite：轻量级数据库，主要应用在移动平台

非关系型数据库也被称为NoSQL数据库，NoSQL的本意是“Not Only SQL”，指的是非关系型数据库，而不是“NO SQL”的意思，因此，NoSQL的产生并不是要彻底否定关系型数据库，而是作为传统数据库的一个有效补充。NoSQL数据库在特定的场景下可以发挥难以想象的高效率和高性能。

非关系型数据库的主要产品：

  • Memcached（key-value）：开源的、高性能的、具有分布式内存对象的缓存系统
  • redis（key-value）：支持内存缓存、持久化储存、主从集群、分布式等功能
  • Cassandra（Column-oriented）：最初由Facebook开发，用于储存特别大的数据
  • MongoDB （Document-oriented）：一个介于关系数据库和非关系数据库之间的产品

OceanBase是关系型数据库，全兼容MySQL，也就是说学好MySQL就是学好它。看看OceanBase如今在世界上的地位：

SQL Server内部运行机制浅析

接下来我们来看看MySQL数据库：

MySQL数据库入门

MySQL 简介

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，后来被Sun公司收购，Sun公司后来又被Oracle公司收购，目前属于Oracle旗下产品。

特点

  • 使用C和C++编写，并使用了多种编译器进行测试，保证源代码的可移植性
  • 支持多种操作系统，如Linux、Windows、AIX、FreeBSD、HP-UX、MacOS、NovellNetware、OpenBSD、OS/2 Wrap、Solaris等
  • 为多种编程语言提供了API，如C、C++、Python、Java、Perl、PHP、Eiffel、Ruby等
  • 支持多线程，充分利用CPU资源
  • 优化的SQL查询算法，有效地提高查询速度
  • 提供多语言支持，常见的编码如GB2312、BIG5、UTF8
  • 提供TCP/IP、ODBC和JDBC等多种数据库连接途径
  • 提供用于管理、检查、优化数据库操作的管理工具
  • 大型的数据库。可以处理拥有上千万条记录的大型数据库
  • 支持多种存储引擎
  • MySQL 软件采用了双授权政策，它分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择MySQL作为网站数据库
  • MySQL使用标准的SQL数据语言形式
  • Mysql是可以定制的，采用了GPL协议，你可以修改源码来开发自己的Mysql系统
  • 在线DDL更改功能
  • 复制全局事务标识
  • 复制无崩溃从机
  • 复制多线程从机

    • 开源 免费 不要钱 使用范围广,跨平台支持性好,提供了多种语言调用的 API，是学习数据库开发的首选

    安装

    系统以Ubuntu为例，其他系统百度有很多教程，在终端中输入如下命令，回车后，然后按照提示输入：

    sudo apt-get install mysql-servernsudo apt-get install mysql-client

    服务器用于接收客户端的请求、执行sql语句、管理数据库，服务器端一般以服务方式管理，名称为mysql。

    sudo service mysql start

    配置

    配置文件目录为/etc/mysql/mysql.cnf

    SQL Server内部运行机制浅析

  • 进入conf.d目录，打开mysql.cnf，发现并没有配置
  • 进入mysql.conf.d目录，打开mysql.cnf，可以看到配置项
    • SQL Server内部运行机制浅析

    • 主要配置项如下

    bind-address表示服务器绑定的ip，默认为127.0.0.1nnport表示端口，默认为3306nndatadir表示数据库目录，默认为/var/lib/mysqlnngeneral_log_file表示普通日志，默认为/var/log/mysql/mysql.lognnlog_error表示错误日志，默认为/var/log/mysql/error.log

    登录

    输入mysql -u root -p

    提示输入之前第一条命令设置的密码，出现如下界面则安装成功

    SQL Server内部运行机制浅析

    常见定义

    • 主键 ：唯一标识一行记录的字段
    • 数据行(记录) : 一行记录某个事物的完整信息的数据
    • 数据列(字段) : 一列数据类型相同的数据
    • 数据表 : 有若干字段和记录组成
    • 数据库 : 由若干数据表组成

    常见数据类型：

    SQL Server内部运行机制浅析

    约束

    • 主键primary key：物理上存储的顺序
    • 非空not null：此字段不允许填写空值
    • 惟一unique：此字段的值不允许重复
    • 默认default：当不填写此值时会使用默认值，如果填写时以填写为准
    • 外键foreign key：对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常

    数据库操作

    数据库登录和退出:

    SQL Server内部运行机制浅析

    数据库的基本操作:

    SQL Server内部运行机制浅析

    数据表的基本操作:

    SQL Server内部运行机制浅析

    数据表数据的基本操作：

    • 增 insert into table_name values(),(),();
    • 删 delete from table_name where 条件;
    • 改 update table_name set key=value where 条件;
    • 查 select */查询的字段 from table_name where 条件;
    SQL Server内部运行机制浅析

    当然，MySQL查询是个大内容，有条件查询、排序、聚合函数、分组、分页、连接查询、自关联查询、子查询等等，如何写好SQL语句又是一门大学问，后面将会降到。查询的完整格式：

    SELECT select_expr [,select_expr,...] [ n FROM tb_namen [WHERE 条件判断]n [GROUP BY {col_name | postion} [ASC | DESC], ...] n [HAVING WHERE 条件判断]n [ORDER BY {col_name|expr|postion} [ASC | DESC], ...]n [ LIMIT {[offset,]rowcount | row_count OFFSET offset}]n]

    MySQL高级进阶

    视图

  • 视图就是一个能够把复杂SQL语句的功能封装起来的一个虚表
  • 视图不存储具体的数据基本表数据发生了改变，视图也会跟着改变
  • 视图可以方便操作，特别是查询操作，减少复杂的SQL语句，增强可读性，复用性；

    • # 视图的定义方式：ncreate view 视图名称(一般使用v开头) as select语句;n# 查看视图nshow tables; n# 使用视图nselect * from v_goods_info; n# 删除视图ndrop view 视图名字；

    事务

    1.事务四大特性ACID

    • 原子性(Atomicity)
    • 一致性(Consistency)
    • 隔离性(Isolation)
    • 持久性(Durability)

    2.开启事务

    开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中

    begin; 或者 start transaction;

    执行SQL语句

    3.提交事务

    将缓存中的数据变更维护到物理表中

    commit;

    4.回滚事务

    放弃缓存中变更的数据 表示事务执行失败 应该回到开始事务前的状态

    rollback;

    索引

    # 查看表中已有索引nshow index from 表名;n# 创建索引nalter table 表名 add index 索引名【可选】（字段名，…）;ncreate index 索引名称 on 表名(字段名称(长度))n# 删除索引ndrop index 索引名称 on 表名;

    优点：

    加快数据的查询速度

    缺点：

    创建索引会浪费时间和占用磁盘空间，并且随着数据量的增加所耗费的时间会越来越多

    使用原则：

    • 经常发生数据更新的表避免使用过多的索引
    • 数据量小的表没有必要使用索引
    • 数据量较大同时不会频发发生数据更改的表可以使用索引

    三范式

    设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，

    各种范式呈递次规范，越高的范式数据库冗余越小。

    数据冗余是指数据之间的重复，也可以说是同一数据存储在不同数据文件中的现象

    六种范式：

    • 第一范式（1NF）：强调的是字段的原子性，即一个字段不能够再分成其他几个字段。
    • 第二范式（2NF）：一是表必须有一个主键，二是非主键字段必须完全依赖于主键，而不能只依赖于主键的一部分
    • 第三范式（3NF）：不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。
    • 巴斯-科德范式（BCNF）：第三范式的基础上进一步消除主属性对于码的部分函数依赖和传递依赖
    • 第四范式 ( 4NF）限制关系模式的属性之间不允许有非平凡且非函数依赖的多值依赖
    • 第五范式（5NF，又称完美范式）：消除了4NF中的连接依赖

    E-R模型

    • E-R模型由 实体、属性、实体之间的关系构成，主要用来描述数据库中表之间的关系和表结构。
    • 开发流程是先画出E-R模型，然后根据三范式设计数据库中的表结构


    SQL Server内部运行机制浅析

    相关文章