数据库编程（1）：SQL基础篇 一

一、主流DBMS

1、数据库基础概念

        DB (DataBase ): 数据库，是存储数据的集合，也可理解为多个数据表。

        DBMS（DataBase Management System)：数据库管理系统，是位于用户与操作系统之间的一层数据管理软件，可以对多个数据库进行管理。和操作系统一样是计算机的基础软件。

        DBS（DataBase System)：数据库系统。可理解为DBS = DBMS + DB + DBA(数据库管理员) + 应用程序。

2、主流DBMS及其数据库模式

        下图是2019年5月DB-Engines公布的DBMS排名：

图1

        其中可以看出，关系型数据库是DBMS的主流。下面对各个数据库模式概念进行说明。

（1）关系型数据库

        建立在关系模型上，SQL就是关系数据库的查询语言。

（2）键值型数据库

        通过Key-Value键值的方式来存储数据，其中Key和Value可以是简单和复杂的对象。Key是唯一标识符，Redis是最流行的键值型数据库。

        优点：查找速度明显优于关系型数据库。

        缺点：无法像关系型数据库一样自由使用条件过滤（比如WHERE),如果不知道去哪里找数据的时候就要遍历所有数据。键值型数据库典型的使用场景是作为内容缓存。

（3）文档型数据库

        专门用来管理文档，文档是处理信息的基本单位，一个文档相当于一条记录，MongoDB是最流行的文档型数据库。

（4）搜索引擎

        通常指的是收集了万维网上几千万到几十亿个网页并对网页中的每一个词（即关键词）进行索引，建立索引数据库的全文搜索引擎。

是数据库检索的重要应用。而关系型数据库针对全文索引的效率较低。全文索引的核心原理是“倒排索引”（什么是倒排索引？）

（5）列式数据库

        是相对于行式存储的数据库，将数据按照列存储到数据库中，这样做的好处是可以大量降低系统的I/O（因为一列都是相同数据类型，方便压缩），适用于分布式系统，不足在于功能相对有限。代表是HBase。（什么是列式数据库？）

（6）图形数据库

        利用了图这种数据结构存储了实体（对象）之间的关系。最典型的例子就是社交网络中人与人的关系，数据模型主要是以结点和编来实现的，特点在于能高效地解决复杂的关系问题。

3.SQL阵营中的主流DBMS

（1）Oracle

        1979年，Oracle2诞生，它是第一个商用的RDBMS（关系型DBMS），后来越来越火，其性能和安全性最好，也最贵，适用于大型跨国企业。

（2）MySQL

        是1995年诞生的开源数据库管理系统，因为免费开源的特性，用户量迅速增长。但后面先后被SUN和Oracle公司收购，MySQL创造者担心其有闭源的风险，因此创建了MySQL的分支项目MariaDB,后者大部分情况下都是与MySQL兼容，并且增加了许多新的特性，比如支持更多的存储引擎类型。因此许多企业纷纷转向了MariaDB。

（3）SQL Server

        是1989年微软开发的商业数据库，受到了许多互联网公司，尤其是创业公司的青睐。

 

 

二、SQL在主流DBMS中的执行过程

1、Oracle中的SQL是如何执行的

        以下是整体流程图：

图2

• 六步骤

    （1）语法检查

        检查SQL拼写是否正确，如果不正确，Oracle会报语法错误。

    （2）语义检查

        检查SQL中的访问对象是否存在。比如SELECT的列名错误，系统就会报错。语法检查和语义检查的作用是保证SQL语句没有错误。

    （3）权限检查

        看用户是否有访问该数据的权限。

    （4）共享池检查

        共享池是一块内存池，最主要的作用是缓存SQL语句和该语句的执行计划。Oracle通过检查共享池是否存在SQL语句的执行计划，来判断是软解析还是硬解析。

        软解析：在共享池中，Oracle首先对SQL语句进行Hash运算，然后根据Hash值在库缓存中查找，如果存在SQL的执行计划，就直接拿来执行，直接进入“执行器”的环节，这就是软解析。

        硬解析：如果没有找到SQL语句和执行计划，Oracle就需要创建解析树进行解析，生成执行计划，进入“优化器”这个步骤，这就是硬解析。

    （5）优化器

        优化器就是要进行硬解析，也就是决定怎么做，比如创建解析树，生成执行计划。

    （6）执行器

        当有了解析树和执行计划之后，就知道SQL该怎么被执行了，这样就可以在执行器中执行语句了。

 

        共享池是Oracle中的术语，包括了库缓存、数据字典缓冲区等。上文也提到了库缓存，它主要缓存SQL语句和执行计划。而数据字典缓冲区存储的是Oracle中的对象定义，比如表、视图、索引等对象。当对SQL语句进行解析的时候，如果需要相关的数据，会从数据字典缓冲区中提取。

        库缓存这一步骤决定了SQL语句是否需要硬解析，而硬解析生成解析树是很消耗资源的，因此应该尽量避免硬解析。在Oracle中，绑定变量是它的一大特色。绑定变量就是在SQL语句中使用变量，通过不同的变量取值来改变SQL的执行结果。这样做的好处是，提升软解析的可能性，坏处是可能会导致生成的执行计划不够优化，因此是否绑定变量应视情况而定。

        举个例子：

正常：

        select * from player where player_id = 10001;

        select * from player where player_id = 10002;

绑定变量：

        select * from player where player_id = :player_id;

        第一个每次都要硬解析，因为在计算机看来属于两个完全不同的SQL语句。第二个只需要硬解析一次，因为用了变量名作为赋值，所以虽然值可能变了，但是语句看上去是一样的。当然，使用动态变量也有缺点，因为参数不同，会导致SQL的执行效率不同，同时SQL优化也会比较困难。

2、MySQL中的SQL是如何执行的

        MySQL是典型的C/S架构，即Client/Server架构，服务端程序使用的mysqld。整体MySQL流程如下图所示：

        可以看到MySQL由三层组成：

        1.连接层：客户端和服务器端建立连接，客户端发送SQL至服务器端；

        2.SQL层：对SQL语句进行查询处理；

        3. 存储引擎层：与数据库文件打交道，负责数据的存储和读取。

        由于有存储引擎层，所以SQL层与数据库文件的存储方式无关，下面来看下SQL层的结构：

        1.查询缓存：Server如果在查询缓存时发现了该SQL语句，将直接把结果返回给客户端；如果没有就进入到解析器阶段。但由于查询 缓存往往效率不高，所以在MySQL8.0之后就抛弃了这个功能。

        2.解析器：在解析器中对SQL语句进行语法分析，语义分析。

        3.优化器：在优化器中会确定SQL语句的执行路径，比如是根据全表来检索，还是根据索引来检索等。

        4.执行器：在执行之前需要判断该用户是否具备权限，如果具备权限就执行SQL查询并返回结果。在MySQL8.0以下的版本，如果设置了查询缓存，这时会将查询结果进行缓存。

        因此可以看到SQL语句在MySQL中的流程是：SQL语句→缓存查询→解析器→优化器→执行器。这一部分中，MySQL和Oracle执行SQL的原理是一样的。

        与Oracle不同的是，MySQL的存储引擎采用了插件的形式，每个存储引擎都面向一种特定的数据库应用环境。同时开源的MySQL还允许开发人员设置自己的存储引擎，下面是一些常用的存储引擎。

        1.InnoDB存储引擎：它是MySQL5.5版本之后默认的存储引擎，最大的特点是支持事务、行级锁定、外键约束等。

        2.MyISAM存储引擎：在MySQL5.5版本之前是默认的存储引擎，不支持事务、不支持外键，最大的特点是速度快，占用资源少。

       （以读为主的数据表用MyISAM)。

        3.Memory存储引擎：使用系统内存作为存储介质，以便得到更快的响应速度。不过如果mysqld进程崩溃，则会导致所有的数据丢失，因此我们只有当数据是临时的情况下才使用Memory存储引擎。

        4.NDB存储引擎：也叫做NDB Cluster存储引擎，主要用于MySQL Cluster分布式集群环境，类似于Oracle的RAC集群。

        5.Archive存储引擎：它有很好的压缩机制，用于文件归档，在请求写入时会进行压缩，所以也经常用来做仓库。

        需要注意的是，数据库的设计在于表的设计，而在MySQL中每个表的设计都可以采用不同的存储引擎，我们可以根据需要选择存储引擎，这也正是MySQL的强大之处。

        当然完整的Oracle和MySQL结构图要复杂得多，但它们最核心的部分——SQL的执行原理是大同小异的。既然一条SQL语句会经历不同的模块，那么我们来看下，在不同的模块中，SQL执行所使用的资源（时间）是怎样的。

        首先，我们需要看下profiling是否开启，开启它可以让MySQL收集在SQL执行时所使用的资源情况。

mysql> select @@profiling;

profiling=0表示关闭，我们需要把profiling打开，即设置为1：

mysql> set profiling=1;

然后我们执行一个SQL查询：

mysql> select * from wucai.heros;

查看当前会话产生的所有profiles：

mysql> show profiles;

查看上一次查询的具体每一步执行时间，可以使用：

mysql> show profile;

当然也可以指定查询某个指定的profile通过Query ID，比如：

mysql> show profile for query 2;

三、参考文献

1.陈旸，极客时间《SQL必知必会》1-3讲

2.Oracle 绑定变量详解