SQL必知必会1

【个人学习记录】

不同的人编写出来的SQL的效率是不同的，比如说一份好的SQL执行计划就会尽量减少I/O操作，因为I/O是DBMS最容易出现瓶颈的地方，可以说数据库操作中有大量的时间都花在了I/O上。此外，还需要考虑如何降低CPU的计算量，在SQL语句中使用GROUP BY、ORDER BY等这些语句会消耗大量的CPU计算资源，因此我们需要从全局出发，不仅需要考虑数据库的I/O性能，还需要考虑CPU计算、内存使用情况等。

DBMS

DBMS的英文全称是DataBase Management System，数据库管理系统，实际上它可以对多个数据库进行管理，所以可以理解为DBMS = 多个数据库（DB） + 管理程序.

这里需要注意的是，虽然我们有时候把Oracle、MySQL等称之为数据库，但确切讲，它们应该是数据库管理系统，即DBMS.

NoSql分类

键值型数据库典型的使用场景是作为内容缓存。Redis是最流行的键值型数据库。

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

搜索引擎也是数据库检索中的重要应用，常见的全文搜索引擎有Elasticsearch、Splunk和Solr。虽然关系型数据库采用了索引提升检索效率，但是针对全文索引效率却较低。搜索引擎的优势在于采用了全文搜索的技术，核心原理是“倒排索引”。

列式数据库是相对于行式存储的数据库，Oracle、MySQL、SQL Server等数据库都是采用的行式存储 （Row-based），而列式数据库(如kudu)是将数据按照列存储到数据库中，这样做的好处是可以大量降低系统的I/O，适合于分布式文件系统，不足在于功能相对有限。

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

SQL在Oracle中的执行过程

1.语法检查：检查SQL拼写是否正确

2.语义检查：检查SQL中的访问对象是否存在

3.权限检查：看用户是否具备访问该数据的权限

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

   在共享池中，Oracle首先对SQL语句进行Hash运算，然后根据Hash值在库缓存（Library Cache）中查找，如果存在SQL语句的执行计划，就直接拿来执行，直接进入“执行器”的环节，这就是软解析。如果没有找到SQL语句和执行计划，Oracle就需要创建解析树进行解析，生成执行计划，进入“优化器”这个步骤，这就是硬解析。

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

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

共享池

包括库缓存，数据字典缓冲区等。

库缓存区，它主要缓存 SQL语句和执行计划。

而数据字典缓冲区存储的是Oracle中的对象定义，比如表、视图、索引等对象。当对SQL语句进行解析的时候，如果需要相关的数据，会从数据字典缓冲区中提取。

库缓存这一个步骤，决定了SQL语句是否需要进行硬解析。为了提升SQL的执行效率，我们应该尽量避免硬解析，因为在SQL的执行过程中，创建解析树，生成执行计划是很消耗资源的。

绑定变量就是能提升软解析的可能性，不足之处在于可能会导致生成的执行计划不够优化，因此是否需要绑定变量还需要视情况而定。

SQL在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和Oracle执行SQL的原理是一样的。

与Oracle不同的是，MySQL的存储引擎采用了插件的形式，每个存储引擎都面向一种特定的数据库应用环境。

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

还有常见的存储引擎如：MyISAM存储引擎、Memory存储引擎、NDB存储引擎、Archive存储引擎。

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

不同的DBMS的SQL的执行原理是相通的，只是在不同的软件中，各有各的实现路径。

 

事务并发处理时的异常问题和事务隔离级别