数据库深入学习笔记

------------------------------------------- 分割线 -------------------------------------------
数据库深入学习笔记-SQL执行引擎

以下面的几个问题，来开始今天的讨论：
1，什么是SQL引擎？
2，什么是SQL存储引擎？
3，SQL引擎的工作原理（工作流程）？
4，SQL语句的执行顺序？

一，SQL语句的预编译
    预编译语句PreparedStatement是java.sql包中的一个接口，PreparedStatement继承了Statement接口。通过Statement执行SQL时，应用
程序首先将SQL语句发送给DBMS，由DBMS进行编译后执行。PreparedStatement与Statement的不同之处在于，PreparedStatement在创建
PreparedStatement对象时，就指定了SQL。该语句立即发送给DBMS进行编译。当PreparedStatement执行时，DBMS直接运行编译后的SQL。也就是
说，这个过程中省去了编译的过程，所以PreparedStatement的执行速度要高于Statement。
    java.sql.PreparedStatement PreparedStatementSql = null;
    com.mysql.jdbc.PreparedStatement PreparedStatementMysql = null;

1，mysql引擎
    mysql引擎取决于mysql在安装时是如何被编译的。要添加一个新的引擎，就必须重新编译mysql。默认情况下，mysql支持3个引擎：ISAM/MYISAM/
HEAP。另外2种类型是：INNODB/BERKLEY。下面介绍一下这几种mysql引擎：

ISAM：ISAM是一个定义明确的，且经历过时间考验的数据表管理方法，在设计之初，ISAM就考虑到数据库被查询的次数
要远大于修改的次数。因此，ISAM查询速度快，且不占用大量的内存和硬盘。ISAM的两个不足之处在于：ISAM不支持
事务，且ISAM不能够容错。如果硬盘崩溃了，那么数据文件就无法恢复了。
MyISAM：MyISAM是MySQL的ISAM的扩展格式和缺省的数据库引擎。MyISAM提供了ISAM没有的索引和字段管理的大量
功能，MyISAM还具有表锁定功能。MyISAM的表锁定功能可以优化多个并发的读写操作，代价是需要运行OPTIMIZE TABLE
命令，来恢复被更新机制所浪费的空间。MyISAM的查询速度快，这可能是MySQL收到web青睐的原因之一。MyISAM的一个
去缺点就是不能再损坏后不能进行数据恢复。
HEAP：HEAP允许只驻留在内存里的临时表。内存的速度远远高于硬盘，所以HEAP比ISAM/MyISAM都要快。但是HEAP不稳
定，容易丢失数据。
INNODB：INNODB引擎支持事务，支持外键

MyISAM与INNODB的区别：
  * MyISAM不支持事务，INNODB支持事务；
  * MyISAM不支持外键，INNODB支持外键；
  * MyISAM不支持行级锁，INNODB支持行级锁；
  * MyISAM的执行速度快于INNODB；
  * INNODB中不保存表的具体行数，也就是说，执行SELECT COUNT(*) FROM TABLE时，INNODB要扫描全表。而MyISAM中
保存有表的总行数，直接读取即可。但是，如果包含WHERE语句，那么INNODB和MyISAM都需要扫描全表。
  * 对于AUTO_INCREMENT类型的字段，INNODB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起
建立联合索引。
  * DELETE FROM TABLE时，INNODB不会重新建立表，而是一行一行的删除。

概括来说，MyISAM适合以下场景：
  * 插入操作少，查询操作多；
  * 没有事务；

INNODB适合以下场景：
  * 可靠性要求比较高；
  * 要求事务；

    MySQL支持表级别的数据库引擎设置，也就是说，对于MySQL中的每一个表格，都可以设置单独的数据库引擎。这也是MySQL灵活性的一种体现。
因为我们可以针对应用场景，为不同的表格设置不同的数据库引擎。比如对于有事务操作的表格，我们需要配置INNODB，对于没有事务的表格，我
们可以使用MyISAM来提高性能。

2，SQL引擎是如何编译应用程序发送过来的SQL语句的
    当应用程序，把SQL语句发送给DBMS时，SQL引擎会对这个SQL进行加工处理，然后执行。这里我们提一个问题：如果一个SQL需要执行多次，SQL引擎
是如何做的呢？好，我们先来看SQL引擎对SQL的处理过程，大致可以分为三步：
第一步：解析SQL语句；
第二步：检查语法和语义；
第三步：生成DB可以执行的SQL；
这个过程还是有些抽象，我们以Oracle数据库为例，来进行说明，Oracle数据库的SQL引擎对SQL的处理过程如下：
第一步：语法检查；
第二步：语义检查，查看对象是否存在，判断类型是否正确；
第三步：检查用户权限；
第四步：从许多可能的执行路径中选择一条作为执行计划；
第五步：生成语句的编译版本；
    从这里我们可以看出，解析是一个相当耗费系统开销的操作。尤其是对于数据库而言。
    预编译的SQL，完成了前2步的操作，当需要执行时，SQL引擎只需要进行第三步操作即可。那么，问题来了，预编译的SQL语句存放在哪里，这个系统
开销有多大？（Oracle有SQL缓存，在执行SQL时，先将SQL语句的字符串通过哈希算法得出一个值，然后检查共享池中是否存在这个值。如果存在，则缓存
命中，直接用已经缓存的执行计划来执行这个语句。如果不存在，则需要对SQL进行处理）

二，SQL语句的执行顺序

    一般来说，SQL语句根据底层操作顺序和操作类型，可以分为7大类：
  * SELECT
  * DISTINCT
  * FROM
  * WHERE
  * GROUP BY
  * HAVING
  * ORDER BY

    这7种类型的关键字的执行顺序如下：
  * FROM
  * WHERE
  * GROUP BY
  * HAVING
  * SELECT
  * DISTINCT
  * ORDER BY

三，MySQL的语法分析过程详解

    语法分析的作用是将一个输入的字符串，变换成一个描述这个字符串的结构体。也就是把SQL语言翻译成更加接近机器语言的过程。
语法分析包含三个过程：词法分析、语法分析、输出抽象语法树。

1，词法分析
    词法分析阶段，会用到词法分析器。词法分析器是一个确定有限自动机（DFA）。词法分析器可以按照我们定义好的词法，将输入的字符
集转换为“单词”。如下：
abc     --> Identifier（标识符）
‘abc’   --> StringLiteral（字符串）
123     --> Number（数字）
SELECT  --> Keyword（关键字）

例如，当输入一个SQL语句在控制台，回车后，词法分析器会完成以下过程：
SELECT ID FROM PAYMENT;
--> (Keyword:SELECT)(Identifier:ID)(Keyword:FROM)(Identifier:PAYMENT)

2，语法分析
    词法分析之后，进入语法分析阶段。词法分析的输出结果，会作为语法分析的输入。语法分析主要来判断用户输入的单词是否符合语法逻辑。

3，输出抽象语法树
    语法分析的最后一步，就是输出抽象语法树。抽象语法树，全称ADT，Abstract Syntax Tree。抽象语法树以一种树形结构来表示用户输入。
抽象语法树上的每一个节点都是一个单词。至此，用户输入的SQL语句，变成了一个“树状结构体”。

四，MySQL的语义分析过程详解

    语义分析阶段是SQL解析过程中，最复杂的一环。语义分析涉及SQL标准、SQL优化、MapReduce等相关理论。语法分析阶段生成的抽象语法树，
经过语义分析之后，会生成一个查询计划。查询计划会指导着物理执行算子一步步的运行在我们的分布式系统上，去读取表的内容。
    语义分析可以分为2个阶段：逻辑分析和物理分析。

1，逻辑分析
    逻辑分析基本上是纯代数的分析过程。逻辑分析与底层分布式环境无关。逻辑分析就是要分析一下用户输入的SQL语句到底要干什么，需要执行
哪些操作。一般来讲，一个SQL语句总有一个输入，一个输出，输入数据经过SQL加工后得到输出数据。

2，物理分析
    物理分析是将逻辑分析的结果做变换。物理分析与底层的执行环境密切相关。一般来说，物理分析时需要确定在MapReduce时如何将数据分区、
排序、读取数据量的大小、启动多少个进程来执行任务等等。

五，MySQL的逻辑算子

   下面的SQL操作，其实都对应了一种逻辑算子，逻辑算子可以理解为MySQL内部的一些操作。这些操作时原子性的，不可拆分的。逻辑算子可以
看做MySQL内部操作的最小执行单元。
  * SELECT
  * DISTINCT
  * FROM
  * WHERE
  * GROUP BY
  * HAVING
  * ORDER BY

    这些SQL操作对应的MySQL内部操作的逻辑算子如下：
  * SELECT               -->  SelectOperator(SEL)
  * DISTINCT / GROUP BY  -->  GBYOperator(GBY)
  * FROM                 -->  TableScanOperator(TS)
  * WHERE / HAVING       -->  FilterOperator(FIL)
  * ORDER BY / LIMIT     -->  OrderByOperator(ORDER)
  * JOIN                 -->  JoinOperator(JOIN)
  * UNION / UNION ALL    -->  UnionAllOperator(UNION)

1，逻辑算子的作用
    逻辑查询计划是由逻辑算子组成的。逻辑计划是一个有向无环图（DAG）。一般的逻辑算子都有一个输入数据集和输出数据集。JoinOperator
和UnionAllOperator比较特殊，拥有两个或者两个以上的输入数据集，因为这两个逻辑算子的功能就是关联多个数据集。
    我们将逻辑算子的输入数据集和输出数据集称为虚表。用户是看不到虚表的，虚表是用来做内部分析的，是逻辑算子之间的桥梁。

六，生成逻辑查询计划

    有了逻辑算子的概念以后，我们就可以生成我们的查询计划了。根据用户输入的SQL语句的执行顺序，遍历编译阶段生成的抽象语法树，用
逻辑算子替换树的节点对应的“单词”。
    生成逻辑查询计划以后，需要进行一次优化。这些优化包括以下内容：
  * 计算常量表达式的结果
  * 列裁剪
  * Predict Push Down

    这里我们一下列裁剪和Predict Push Down。生成查询计划时，默认会把表中的所有列都读出来，但是之后可能只需要对其中的几个列进行
计算，其他的列就没有必要查询出来了。这就是列裁剪。Predict Push Down指的是先过滤再连接，还是先连接再过滤的问题。Predict Push Down
会进行逻辑判断，如果过滤操作（FilterOperator）可以放在连接操作（JoinOperator）之前，那么先过滤再连接。Predict Push Down优化对于提
升性能很有帮助。

七，生成物理查询计划

    逻辑查询计划是物理查询计划的输入。我们按照拓扑序去遍历逻辑查询计划上的每一个逻辑算子，生成对应的物理算子。生成物理查询计划时
也需要进行优化。


上面的语法分析、语义分析参考了博文《SQL解析过程详解》






------------------------------------------- 分割线 -------------------------------------------
数据库深入学习笔记-应用程序与数据库如何通信

一，应用程序服务器与数据库服务器是如何通信的

    个人认为，应用程序服务器与数据库服务器是通过Socket进行通信的。


------------------------------------------- 分割线 -------------------------------------------
数据库深入学习笔记-MySQL体系结构

    MySQL是一个可移植的数据库，几乎能在当前所有的操作系统上运行。各种系统在底层实现方面各有不同，但是MySQL基本上能够保证在各个操作系统
上的物理体系结构的一致性。

一，MySQL的组成部分

1，连接池组件（Connections Pools）
2，管理服务和工具组件（Management Services and Utilities）
3，SQL接口组件（SQL Interface）
4，查询分析器组件（Parse）
5，优化器组件（Optimize）
6，缓冲（Cache）组件/缓存组件（Cache and Buffer）
7，插件式存储引擎（Pluggable Storage Engine）
8，物理文件

    MySQL区别于其他关系数据库的一个重要特点就是：MySQL支持插件式的存储引擎。MySQL5.6版本默认存储引擎为INNODB。


------------------------------------------- 分割线 -------------------------------------------


数据库深入学习笔记-数据库索引底层实现原理

一，背景知识

1，B-Tree和B+Tree

2，Binary Search（折半查找）

3，数据库的性能问题
  * 磁盘IO性能非常低，严重的影响了数据库的性能；
  * 磁盘顺序读写比随机读写的性能高很多；

4，数据的基本存储结构
  * 磁盘空间被划分为许多大小相同的块（Block）或者页（Page）
  * 一个表的数据块以链表的方式连接在一起
  * 数据是以行为单位存放在磁盘上的块（Block）中
  * 在访问数据时，一次从磁盘中读出或者写入至少一个完整的块（Block）

5，如何定位数据
    对数据操作之前，需要先进行定位。Mysql是如何定位数据的呢？简单来说，就是表扫描。表扫描就是从磁盘中依次读出所有的数据块，逐行
进行匹配。表扫描的时间复杂度是O(n)。

6，如何提高数据定位的效率
    主要有以下几种方式：减少数据占用的磁盘空间，也就是需要对数据进行压缩，优化数据存储结构；减少数据访问总量，减少无效数据的访问。
索引的出现，极大的提高了数据定位的效率。

>>>>>思考一个问题：联合索引和单列索引的区别？

7，索引产生的背景
    如果我们只匹配一行记录中的一个字段，那么效率会得到很大的提升，这样做有效的减少了无效数据的访问；如果我们把一个字段单独放到一个
块（Block）中，那么效率也会得到很大的提升，这样做有效减少了占用的磁盘空间。索引就是在这种情况下产生的。索引是以牺牲空间为代价来换
时间的。凡事都有两面性，所以合理使用索引就显得尤为重要了。MySQL的索引以B树格式保存。

8，Binary Search（折半查找）
    折半查找，是对索引进行排序，使用一个数组顺序存储索引块地址。这个数组也存放在磁盘上。折半查找的时间复杂度为O(log(n))。在此基础
上，继续进行抽象，直到最上层块中只有一个记录，这样就成了一棵B+Tree。树的高度就是需要查找的次数，索引树越高，效率也就越低。

二，MySQL索引的分类

1，普通索引：最基本的索引类型，没有唯一性限制。

2，唯一索引：唯一索引和普通索引的区别就是唯一索引必须保证索引列的值都是不重复的，也就是索引列的值都是唯一的。

3，主键：主键是一种索引，并且是唯一性索引。主键相当于聚合索引，是查询最快的索引。

4，全文索引：一种特殊的索引。全文索引会把某个数据表的某个字段出现过的所有单词生成一份清单。全文索引只能在MyISAM数据表中创建。少于
3个字符的单词通常会被忽略。可以通过修改MySQL配置文件my.cnf来修改。ft_min_word_len = 3。

5，单列索引：单列索引就是一个字段的索引。

6，多列索引：多列索引就是多个字段的索引，多列索引也叫联合索引。多列索引使用时需要注意，只有在where条件中含有索引中的首列字段时，才
有效。举例说明如下：
首先创建一个索引：ALTER TABLE PAYMENT ADD INDEX UNION_INDEX(ID,AMOUNT,DATE);
然后看以下几个查询操作：
SELECT * FROM PAYMENT WHERE ID = '10000001' AND AMOUNT = '100.00';    --联合索引有效
SELECT * FROM PAYMENT WHERE ID = '10000001' AND DATE = '20161208';    --联合索引有效
SELECT * FROM PAYMENT WHERE ID = '10000001';                          --联合索引有效
SELECT * FROM PAYMENT WHERE AMOUNT = '100.00' AND DATE = '20161208';  --联合索引无效

7，最左索引：可以这样理解吗，MySQL的组合索引必须符合最左索引的规则。也就是上面所说的，只有在where条件中含有索引中的首列字段时，
索引才有效。