对于已经很熟悉T-SQL的读者,或者对于较专业的DBA来说,逻辑的增删改查,或者较复杂的SQL语句,都是非常简单的,不存在任何挑战,不值得一提,那么,SQL的哪些方面是他们的挑战 或者软肋呢?
那就是sql优化。然而,要想成为一个好的Sql优化高手,首先要做的一件事无疑就是了解sql语句在SQL Server中是如何执行的。在这一系列中,我们将开始sqlserver优化系列讲解,本讲为优化系列的开篇文章,在本篇文章中,我们将重点讲解SQL Server体系结构。
在正式讲解之前,我们先来看看如下问题,你是否遇到过,若你遇到过且成功解决,那么这篇文章,你可以跳过。
为了测试需要,我们先模拟插入5亿3千多万条数据。
- SELECT COUNT(1) FROM BigDataTest
(一)查询缓慢问题
*,临时表,表连接,子查询等造成的查询缓慢问题,你能解决吗?
(二)内存泄漏
如下查询了8分2秒,然后内存溢出,你知道问题吗?
- SELECT * FROM BigDataTest
(三)经常听说如下概念,你都能解决吗?
事务与锁(请参考我另一篇文章:浅谈SQL Server事务与锁(上篇)https://www.cnblogs.com/wangjiming/p/8396986.html,ACID,隔离级别,脏读,分表分库,水平拆分,垂直拆分,高并发等
一、SQL Server体系结构抽象
二、SQL Server体系结构概述
SQL Server核心体系结构,大致包括六大部分:客户端访问工具、SQL Server 网络接口(SQL Server Network Interface,SNI)、关系引擎、存储引擎、
磁盘和缓冲池。下图为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网络接口(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语句,其核心组件由三部分组成:命令分析器、查询优化器和查询执行器。
(1)命令分析器:负责解析客户端传递过来的T-SQL语句,如客户端传递一条SQL语句:SELECT * FROM BigDataTest,它会检查该语句的语法结构,若语法
错误,它会将错误返回给协议层,然后协议层将错误返回给客户端;如果语法结构正确,它会根据查询命令生成查询计划或寻找一个已存在的查询计划(先在缓冲池计划缓存中查找,若找到,则直接给查询执行器执行,若未找到,则会生成基于T-SQL的查询树,然后交给查询优化器优化)
(2)查询优化器:负责优化命令解析器生成的T-SQL查询树(基于资源的优化,而非基于时间的优化),然后将最终优化结果传递给查询执行器执行。查询优化器是基于
“资源开销”的优化器,这种算法评估多种可执行的查询方式,并从中选择开销***的方案作为优化结果,然后将该结果生成查询计划输出给查询执行器。注意,查询优化器是“基于资源开销***”而非“基于方案***”,也就是,查询优化器的最终优化结果未必是***的方案,但一定是资源开销***的方案。
(3)查询执行器:负责执行查询。假若查询执行器接收到命令解析器或查询优化器传递过来的SQL语句:SELECT * FROM BigDataTest,它通过OLE DB接口传递到存储引擎,再传递到存储引擎的访问方法。
(四)存储引擎
存储引擎,本质就是管理资源存储的,它的核心组件包括三部分:访问方法、事务管理器和缓冲区管理器。
(1)访问方法:访问方法本质是一个接口,供查询执行器调用(该接口提供了所有检索数据的代码,接口的实际执行是由缓冲区管理器来执行的),假若查询执行器传递一条SQL语句:
SELECT * FROM BigDataTest,访问方法接收到该请求命令后,就会调用缓冲区管理器,缓冲区管理器就会调用缓冲池的计划缓存,在计划缓存中寻找到相应的结果集,然后返回给关系引擎。
(2)缓冲区管理器:供访问方法调用,管理缓冲池,在缓冲池中查询相应资源并返回结果集,供访问方法返回给关系引擎。
(3)事务管理器:主要负责事务的管理(ACID管理)和高并发管理(锁),它包括两个核心组件(日志管理器和锁管理器),锁管理器负责提供并发数据访问,设置隔离级别等;日志管理器负责记录所有访问方法操作动作,如基本的CRUD。
(五)缓冲池
缓冲池驻于内存中,是磁盘和缓冲区管理器的桥梁SQL Server中,所有资源的查询都是在内存中进行的,即在缓冲池中进行的,假若缓冲池接收到缓冲区管理器传递过来的的一条SQL语句:SELECT * FROM BigDataTest,缓冲区管理器数据缓存先从磁盘数据库中取满足条件的结果集,然后放在缓冲池数据缓冲中,然后以结果集的形式返回给缓冲区管理器,供访问方法返回给关系引擎的查询执行器,然后返回给协议层,协议层再返回给客户端。注意,这里操作的是缓冲池中数据,而不是磁盘DB中的数据,并且操作的缓冲池数据不会立即写入磁盘,因此就会造成查询到结果与BD中的结果不一致,这就是所谓的脏读。
缓冲池主要包括两部分:计划缓存(生成执行计划是非常耗时耗资源的,计划缓存主要用来存储执行计划,以备后续使用)和数据缓存(通常是缓存池中容量***的,消耗内存***,从磁盘中读取的数据页只要放在这里,方可调用)
(六)磁盘
磁盘主要是用来存储持久化资源的,如日志资源,数据库资源和缓存池持久化支援等。
三、一个查询的完整流程
如下为一个比较完善的查询过程,即第二部分查询语句:SELECT * FROM BigDataTest 整个过程。
四、参考文献
【01】《SQL Server 2012 深入解析与性能优化 第3版》Christian Bolton,Justin Langford,Glenn Berry,Gavin Payne,Amit Banerjee,Rob Farley著