数据库原理知识点 数据库原理知识点思维导图 收藏 0

数据库系统原理

数据库系统原理

【课程代码：04735】

模拟测试

《数据库系统原理》模拟测试一

一、单项选择题:本大题共 15 小题，每小题 2 分，共 30 分。

1.随着数据管理规模的扩大，数据量急剧增加，下面不属于文件系统缺陷的是（）

A.数据冗余

B.数据不一致

C.读脏数据

D.数据联系弱

2.关于逻辑模型，叙述正确的是（ ）

A.逻辑模型独立于硬件和软件

B.逻辑模型表达了 DB 的局部逻辑结构

C.逻辑模型是从数据库实现的观点出发，对数据建模

D.逻辑模型主要有实体联系模型

3.对于数据库系统生存期，下面不属于物理设计阶段工作的是（ ）

A.存储记录结构设计

B.设计外模型

C.确定数据存储位置

D.存取方法的设计

4.在关系模型中，元组个数称为（ ）

A.元数

B.基数

C.度数

D.目数

5.对于函数依赖 W→A，如果存在 X  W 有 X→A 成立，那么称 W→A 是（ ）

A.平凡依赖

B.传递依赖

C.局部依赖

D.完全依赖

6.若事务 T1 已经给数据 A 加上了排他锁，则事务 T2（ ）

A.只能再对数据 A 加共享锁

B.不能再给数据 A 加任何锁

C.只能再对数据 A 加排他锁

D.可以对数据 A 加任何锁

7.关系代数中投影运算是对关系进行的( )

A.垂直分割

B.水平分割

C.结合

D.先垂直分割后水平分割

8.设有关系 R(ABCD)和关系 S(BCD),则 RXS 结果集的元数为( )

A.3

B.4

C.6

D.7

9.已知 SN 是一个字符型字段，下列 SQL 查询语句 SELECT SN FROM S WHERE SN LIKE \’AB%\’;

其执行结果为（ ）

A.找出含有 3 个字符’AB%’的所有 SN 字段

B.找出仅含 3 个字符且前两个字符为\’AB’的 SN 字段

C.找出含有字符\’AB’的所有 SN 字段

D.找出以字符\’AB’开头的所有 SN 字段

10.设有学生表 STUDENT(学号，姓名，性别，年龄)，则向 STUDENT 表插入一条新记录的正确的

SQL 语句是( )

A.APPEND INTO STUDENT VALUES(‘d001’,’王明’,’女’,18)

B.INSERT INTO STUDENT VALUES(‘d001\’,‘王明’,’女’’,18)

C.INSERT STUDENT VALUES (‘d001’,’王明’,’女’,18)

D.APPEND STUDENT VALUES (‘d001’,’王明’,’女’,18)

11.DBS 运行的最小逻辑工作单位是（）

A.数据

B.事务

C.记录

D.函数

12.用户只能使用视图定义中的数据，而不能使用视图定义外的其它数据，从而保证了数据（ ）

A.安全性

B.完整性

C.恢复性

D.并发性

13.下列关系代数操作中，要求两个运算对象其属性结构完全相同的是（ ）

A.笛卡尔积、连接

B.自然连接、除法

C.并、交、差

D.投影、选择

14.DBMS 的恢复子系统采取一系列措施保证在任何情况下保持事务的原子性和（

）

A.隔离性

B.持久性

C.一致性

D.安全性

15.如果关系模式 R 的每个关系 r 的属性值都是不可分的原子值，那么称 R 属于（

）

A.1NF

B.2NF

C.3NF

D.BCNF

二、填空题:本大题共 10 空，每空 1 分，共 10 分。

16.DB 是长期存储在计算机内、有组织的、统一管理的相关__________的集合。

17.数据库试运行也称为联合调试，其主要工作包括功能调试和__________调试。

18.关系模式的设计尽可能使得相应关系中不出现插入、删除和__________等操作异常现象。

19.数据库系统的英文缩写是__________。

20.SQL 语言中，撤消基本表的语句是__________。

21.数据库完整性一词是指数据的正确性、有效性和相容性，防止__________进入数据库。

22.如果多个事务依次执行，则称为事务的__________。

23.存储过程的优点主要是提供了安全机制、改进了执行性能、减少了网络流量和增强了__________。

24.DBMS 总是基于某种数据模型，可以分为层次型、网状型、__________和面向对象型等。

25.如果 A 是关系模式 R 的候选键的属性，那么 A 是 R 的__________属性。

三、简答题:本大题共 10 小题，每小题了分，共 30 分。

26.使用 DBS 的用户有哪几类?

27.数据库系统投入运行后，有哪些维护工作?

28.简述事务的 COMMIT 和 ROLLBACK 语句的功能。

29.简述嵌入式 SQL 中引入游标的原因。

30.简述数据库的可恢复性的定义。

31.数据库的并发操作会带来哪些问题?学员专用 请勿外泄

32.什么是封锁的粒度?

33.简述 T-SQL 语言中全局变量的定义。

34.使用存储过程有哪些好处。

35.简述需求分析阶段的主要工作。

四、设计题：本大题共 5 小题，每小题 4 分，共 20 分。

某职工管理系统的数据库包含如下关系表：

职工(职工号，姓名，性别，年龄)

工程(工程号，工程名称，预算)

报酬(职工号，工程号，工资)

实现下列操作：

36.使用关系代数查询年龄不在 30 至 50 岁之间的职工姓名和性别。

37.使用 SQL 语句查询王琦的工资信息。信息包括：姓名、工程名称、工资。

38.使用 SQL 语句按照职工号统计每名职工的总收入。

39.使用 SQL 语句将预算额达到 l0000 元及以上工程的职工工资提高 10％。

40.创建一个职工参加工程项目的视图 VPS，视图包括职工号，姓名，工程名称和工资。

五、综合题：本大题共 2 小题，每小题 5 分，共 10 分。

41.设有关系模式 R(读者编号，姓名，性别，图书号，图书名，借书日期，还书日期)。

其中，每个读者可以借阅多本图书，每本图书可以由多名读者借阅，读者可以重复借阅图书，但每

本图书每个读者每天最多允许借一次。根据上述条件：

（1）写出关系模式 R 的关键码。

（2）R 最高属于第几范式，为什么?

（3）将 R 规范到 3NF。

42.某运输公司管理系统信息如下：

有三个实体集，一是“车队”实体集，属性有车队号、车队名等；二是“司机”实体集，属性有司

机编号、姓名、电话等；三是“车辆”实体集，属性有汽车牌照、厂家、出厂日期等。

车队与司机之间存在“聘用”联系，每个车队可聘用若干司机，但每个司机只能应聘于一个车队，

车队聘用司机有个聘期；司机与车辆之间存在“使用”联系，司机使用车辆时记录使用日期和公里

数两个属性，每个司机可使用多辆汽车，每个汽车可被多个司机使用。

（1）试画出反映上述实体关系的 E—R 图(不必画实体的属性)。

（2）转换成关系模式，并指出每个关系模式的主码和外码(如果有外码指出来)，在主码下画直线

_____，在外码下画波浪线

从原理到优化，深入浅出数据库索引

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。数据库查询是数据库的最主要功能之一，我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化，这篇文章对索引做一个系统的梳理，希望对大家有帮助。

索引的分类可以从多个角度进行，下面分别从数据结构，物理存储和业务逻辑三个维度进行划分。

关于B+树索引，后面会深入解析

• 仅仅能满足\”=\”,\”IN\”和\”<=>\”查询，不能使用范围查询
• 其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引
• 只有Memory存储引擎显示支持hash索引

现在MyISAM和InnoDB引擎都支持了

用于对GIS数据类型创建SPATIAL索引

• 正文内容按照一个特定维度排序存储，这个特定的维度就是聚集索引；
• Innodb存储引擎中行记录就是按照聚集索引维度顺序存储的，Innodb的表也称为索引表；因为行记录只能按照一个维度进行排序，所以一张表只能有一个聚集索引。

• 索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。
• 非聚集索引索引项顺序存储，但索引项对应的内容却是随机存储的；

举个例子说明下：

该表中主键id是该表的聚集索引、name为非聚集索引；表中的每行数据都是按照聚集索引id排序存储的；比如要查找name=\’Arla\’和name=\’Arle\’的两个同学，他们在name索引表中位置可能是相邻的，但是实际存储位置可能差的很远。name索引表节点按照name排序，检索的是每一行数据的主键。聚集索引表按照主键id排序，检索的是每一行数据的真实内容。

主键索引是一种特殊的唯一索引，不允许有空值

复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合

空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。

MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建.

• unique|fulltext|spatial为可选参数，分别表示唯一索引、全文索引和空间索引；
• index和key为同义词，两者作用相同，用来指定创建索引
• col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择；
• index_name指定索引的名称，为可选参数，如果不指定，MYSQL默认col_name为索引值；length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
• asc或desc指定升序或降序的索引值存储

查看表结构

show create table table_name;可以使 EXPLAIN 语句查看索引是否被使用

全文索引只能在char，varchar或者text 类型的字段上。而且，只有MyISAM 储存引擎支持全文索引。

这里需要注意的，subject 的长度为255，但是index4_st索引只有10。这样做的目的还是为了提高查询速度。对于字符型的数据，可以不用查询全部信息，只查询其前面的若干字符信息。

这是我们可以看到，name 和sex字段上已经创建了index_ns索引。

在example0() 表中的id 创建名为index7_id 的索引。

在name字段上创建名为indx_name 的索引

目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构，那么索引树是如何维护的？

查找是数据结构和算法中一个非常重要的概念。

• 线性查找：一个个找；实现简单；太慢
• 二分查找：有序；简单；要求是有序的，插入特别慢
• HASH查找：查询快；占用空间；不太适合存储大规模数据
• 二叉查找树：插入和查询很快(log(n))；无法存大规模数据，复杂度退化
• 平衡树：解决 BST 退化问题，树是平衡的；节点非常多的时候，依然树高很高
• 多路查找树：一个父亲多个孩子节点（度）；节点过多树高不会特别深
• 多路平衡查找树：B-Tree

B-Tree是一种多路搜索树（并不是二叉的）：

1. 定义任意非叶子结点最多只有M个儿子；且M>2；
2. 根结点的儿子数为[2, M]；
3. 除根结点以外的非叶子结点的儿子数为[M/2, M]；
4. 每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（至少2个关键字）
5. 非叶子结点的关键字个数=指向儿子的指针个数-1；
6. 非叶子结点的关键字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；
7. 非叶子结点的指针：P[1], P[2], …, P[M]；其中P[1]指向关键字小于K[1]的子树，P[M]指向关键字大于K[M-1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])的子树；
8. 所有叶子结点位于同一层；
9. 每个k对应一个data。如：（M=3）相当于一个2–3树，2–3树是一个这样的一棵树， 它的每个节点要么有2个孩子和1个数据元素，要么有3个孩子和2个数据元素，叶子节点没有孩子，并且有1个或2个数据元素。

B-树的搜索，从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点；重复，直到所对应的儿子指针为空，或已经是叶子结点；B-Tree上查找算法的伪代码如下：

1. 关键字集合分布在整颗树中；
2. 任何一个关键字出现且只出现在一个结点中；
3. 搜索有可能在非叶子结点结束；
4. 其搜索性能等价于在关键字全集内做一次二分查找；
5. 自动层次控制；

B树中每一个内部节点会包含一定数量的键值。通常，键值的数量被选定在d和2d之间。在实际中，键值占用了节点中大部分的空间。因数2将保证节点可以被拆分或组合。如果一个内部节点有2d个键值，那么添加一个键值给此节点的过程，将会拆分2d键值为2个d键值的节点，并把此键值添加给父节点。每一个拆分的节点需要最小数目的键值。相似地，如果一个内部节点和他的邻居两者都有d个键值，那么将通过它与邻居的合并来删除一个键值。删除此键值将导致此节点拥有d-1个键值;与邻居的合并则加上d个键值，再加上从邻居节点的父节点移来的一个键值。结果为完全填充的2d个键值。

下面是往B树中依次插入

6 10 4 14 5 11 15 3 2 12 1 7 8 8 6 3 6 21 5 15 15 6 32 23 45 65 7 8 6 5 4

B-Tree有许多变种，其中最常见的是B+Tree，MySQL就普遍使用B+Tree实现其索引结构。与B-Tree相比，B+Tree有以下不同点：

1. 非叶子结点的子树指针与关键字个数相同；
2. 非叶子结点的子树指针P[i]，指向关键字值属于[K[i], K[i+1])的子树（B-树是开区间）；
3. 为所有叶子结点增加一个链指针；
4. 所有关键字都在叶子结点出现；
5. 内节点不存储data，只存储key如：（M=3）

B+的搜索与B-树也基本相同，区别是B+树只有达到叶子结点才命中（B-树可以在非叶子结点命中），其性能也等价于在关键字全集做一次二分查找；

1. 所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的；
2. 不可能在非叶子结点命中；
3. 非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层；
4. 更适合文件索引系统；

下面是往B+树中依次插入

6 10 4 14 5 11 15 3 2 12 1 7 8 8 6 3 6 21 5 15 15 6 32 23 45 65 7 8 6 5 4

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。

这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

假如每个盘块可以正好存放一个B树的结点（正好存放2个文件名）。那么一个BTNODE结点就代表一个盘块，而子树指针就是存放另外一个盘块的地址。

下面，咱们来模拟下查找文件29的过程：

1. 根据根结点指针找到文件目录的根磁盘块1，将其中的信息导入内存。【磁盘IO操作 1次】
2. 此时内存中有两个文件名17、35和三个存储其他磁盘页面地址的数据。根据算法我们发现：17<29<35，因此我们找到指针p2。
3. 根据p2指针，我们定位到磁盘块3，并将其中的信息导入内存。【磁盘IO操作 2次】
4. 此时内存中有两个文件名26，30和三个存储其他磁盘页面地址的数据。根据算法我们发现：26<29<30，因此我们找到指针p2。
5. 根据p2指针，我们定位到磁盘块8，并将其中的信息导入内存。【磁盘IO操作 3次】
6. 此时内存中有两个文件名28，29。根据算法我们查找到文件名29，并定位了该文件内存的磁盘地址。分析上面的过程，发现需要3次磁盘IO操作和3次内存查找操作。关于内存中的文件名查找，由于是一个有序表结构，可以利用折半查找提高效率。至于IO操作是影响整个B树查找效率的决定因素。当然，如果我们使用平衡二叉树的磁盘存储结构来进行查找，磁盘4次，最多5次，而且文件越多，B树比平衡二叉树所用的磁盘IO操作次数将越少，效率也越高。

B+-tree的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。举个例子，假设磁盘中的一个盘块容纳16bytes，而一个关键字2bytes，一个关键字具体信息指针2bytes。一棵9阶B-tree(一个结点最多8个关键字)的内部结点需要2个盘快。而B+树内部结点只需要1个盘快。当需要把内部结点读入内存中的时候，B 树就比B+树多一次盘块查找时间(在磁盘中就是盘片旋转的时间)。

由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

索引查询是数据库中重要的记录查询方法，要不要进入索引以及在那些字段上建立索引都要和实际数据库系统的查询要求结合来考虑，下面给出实际中的一些通用的原则：

1. 在经常用作过滤器的字段上建立索引；
2. 在SQL语句中经常进行GROUP BY、ORDER BY的字段上建立索引；
3. 在不同值较少的字段上不必要建立索引，如性别字段；
4. 对于经常存取的列避免建立索引；
5. 用于联接的列（主健/外健）上建立索引；
6. 在经常存取的多个列上建立复合索引，但要注意复合索引的建立顺序要按照使用的频度来确定；
7. 缺省情况下建立的是非簇集索引，但在以下情况下最好考虑簇集索引，如：含有有限数目（不是很少）唯一的列；进行大范围的查询；充分的利用索引可以减少表扫描I/0的次数，有效的避免对整表的搜索。
8. 经常用在WHERE子句中的数据列；
9. 经常出现在关键字order by、group by、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用；
10. 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引；
11. 对于定义为text、image和bit的数据类型的列不要建立索引；
12. 对于经常存取的列避免建立索引；
13. 限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。
14. 对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用,因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。

一个单独的索引扫描只能用于这样的条件子句：使用被索引字段和索引操作符类中的操作符， 并且这些条件以AND连接。

假设在(a, b)上有一个索引， 那么类似WHERE a = 5 AND b = 6的条件可以使用索引，但是像WHERE a = 5 OR b = 6的条件就不能直接使用索引。

一个类似WHERE x =42 OR x = 47 OR x = 53 OR x = 99 这样的查询可以分解成四个在x上的独立扫描，每个扫描使用一个条件， 最后将这些扫描的结果OR 在一起，生成最终结果。

另外一个例子是，如果我们在x 和y上有独立的索引，一个类似WHERE x = 5 AND y = 6 这样的查询可以分解为几个使用独立索引的子句，然后把这几个结果AND 在一起，生成最终结果。

1. 如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
2. 对于多列索引，不是使用的第一部分(第一个)，则不会使用索引
3. like查询是以%开头
4. 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
5. 如果mysql估计使用全表扫描要比使用索引快,则不使用索引

联合索引又叫复合索引。两个或更多个列上的索引被称作复合索引。

对于复合索引：Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index （a,b,c）。 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。

所以说创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处。

这里记录两种方式，分别是

大家可以注意：

• handler_read_key:这个值越高越好，越高表示使用索引查询到的次数
• handler_read_rnd_next:这个值越高，说明查询低效

分析这几个值，我们可以查看当前索引的使用情况：

• Handler_read_first：索引中第一条被读的次数。如果较高，它表示服务器正执行大量全索引扫描；例如，SELECT col1 FROM foo，假定col1有索引（这个值越低越好）。
• Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的性能改善不高，因为索引不经常使用（这个值越高越好）。
• Handler_read_next ：按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列，该值增加。
• Handler_read_prev：按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY … DESC。
• Handler_read_rnd ：根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高，意味着运行效率低，应该建立索引来补救。
• Handler_read_rnd_next：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。

查询 schema_unused_indexes库。

explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。

新建一张表，

执行查询，

响应数据如下，

type 是 ALL,即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。

• table：显示这一行的数据是关于哪张表的
• type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL
• possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
• key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引
• key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
• ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
• rows：MYSQL认为必须检查的用来返回请求数据的行数
• Extra：关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢

MySQL 在表里找到所需行的方式。包括（由左至右，由最差到最好）：| All | index | range | ref | eq_ref | const,system | null |

• system 表只有一行：system表。这是const连接类型的特殊情况
• const:表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待
• eq_ref:在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用
• ref:这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好
• range:这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况
• index: 这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）
• ALL:这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免

• Distinct:一旦MYSQL找到了与行相联合匹配的行，就不再搜索了
• Not exists: MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了
• Range checked for each Record（index map:#）:没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一
• Using filesort: 看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
• Using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候
• Using temporary 看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上
• Using where 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题不同连接类型的解释（按照效率高低的顺序排序）

数据库原理

做应用开发的同学常常觉得数据库由DBA运维，自己会写SQL就可以了，数据库原理不需要学习。其实即使是写SQL也需要了解数据库原理，比如我们都知道，SQL的查询条件尽量包含索引字段，但是为什么呢？这样做有什么好处呢？你也许会说，使用索引进行查询速度快，但是为什么速度快呢？

此外，我们在Java程序中访问数据库的时候，有两种提交SQL语句的方式，一种是通过Statement直接提交SQL；另一种是先通过PrepareStatement预编译SQL，然后设置可变参数再提交执行。

Statement直接提交的方式如下：

PrepareStatement预编译的方式如下：

看代码，似乎第一种方式更加简单，但是编程实践中，主要用第二种。使用MyBatis等ORM框架时，这些框架内部也是用第二种方式提交SQL。那为什么要舍简单而求复杂呢？

要回答上面这些问题，都需要了解数据库的原理，包括数据库的架构原理与数据库文件的存储原理。

我们先看看数据库架构原理与SQL执行过程。

关系数据库系统RDBMS有很多种，但是这些关系数据库的架构基本上差不多，包括支持SQL语法的Hadoop大数据仓库，也基本上都是相似的架构。一个SQL提交到数据库，经过连接器将SQL语句交给语法分析器，生成一个抽象语法树AST；AST经过语义分析与优化器，进行语义优化，使计算过程和需要获取的中间数据尽可能少，然后得到数据库执行计划；执行计划提交给具体的执行引擎进行计算，将结果通过连接器再返回给应用程序。

应用程序提交SQL到数据库执行，首先需要建立与数据库的连接，数据库 连接器 会为每个连接请求分配一块专用的内存空间用于会话上下文管理。建立连接对数据库而言相对比较重，需要花费一定的时间，因此应用程序启动的时候，通常会初始化建立一些数据库连接放在连接池里，这样当处理外部请求执行SQL操作的时候，就不需要花费时间建立连接了。

这些连接一旦建立，不管是否有SQL执行，都会消耗一定的数据库内存资源，所以对于一个大规模互联网应用集群来说，如果启动了很多应用程序实例，这些程序每个都会和数据库建立若干个连接，即使不提交SQL到数据库执行，也就会对数据库产生很大的压力。

所以应用程序需要对数据库连接进行管理，一方面通过连接池对连接进行管理，空闲连接会被及时释放；另一方面微服务架构可以大大减少数据库连接，比如对于用户数据库来说，所有应用都需要连接到用户数据库，而如果划分一个用户微服务并独立部署一个比较小的集群，那么就只有这几个用户微服务实例需要连接用户数据库，需要建立的连接数量大大减少。

连接器收到SQL以后，会将SQL交给 语法分析器 进行处理，语法分析器工作比较简单机械，就是根据SQL语法规则生成对应的抽象语法树。

如果SQL语句中存在语法错误，那么在生成语法树的时候就会报错，比如，下面这个例子中SQL语句里的where拼写错误，MySQL就会报错。

因为语法错误是在构建抽象语法树的时候发现的，所以能够知道，错误是发生在哪里。上面例子中，虽然语法分析器不能知道whee是一个语法拼写错误，因为这个whee可能是表名users的别名，但是语法分析器在构建语法树到了 id=1 这里的时候就出错了，所以返回的报错信息可以提示，在 \’id = 1\’ 附近有语法错误。

语法分析器生成的抽象语法树并不仅仅可以用来做语法校验，它也是下一步处理的基础。语义分析与优化器会对抽象语法树进一步做语义优化，也就是在保证SQL语义不变的前提下，进行语义等价转换，使最后的计算量和中间过程数据量尽可能小。

比如对于这样一个SQL语句，其语义是表示从users表中取出每一个id和order表当前记录比较，是否相等。

事实上，这个SQL语句在语义上等价于下面这条SQL语句，表间计算关系更加清晰。

SQL语义分析与优化器就是要将各种复杂嵌套的SQL进行语义等价转化，得到有限几种关系代数计算结构，并利用索引等信息进一步进行优化。可以说，各个数据库最黑科技的部分就是在优化这里了。

语义分析与优化器最后会输出一个执行计划，由执行引擎完成数据查询或者更新。MySQL执行计划的例子如下：

执行引擎是可替换的，只要能够执行这个执行计划就可以了。所以MySQL有多种执行引擎（也叫存储引擎）可以选择，缺省的是InnoDB，此外还有MyISAM、Memory等，我们可以在创建表的时候指定存储引擎。大数据仓库Hive也是这样的架构，Hive输出的执行计划可以在Hadoop上执行。

好了，了解了数据库架构与SQL执行过程之后，让我们回到开头的问题，应用程序为什么应该使用PrepareStatement执行SQL？

这样做主要有两个好处。

一个是PrepareStatement会预先提交带占位符的SQL到数据库进行预处理，提前生成执行计划，当给定占位符参数，真正执行SQL的时候，执行引擎可以直接执行，效率更好一点。

另一个好处则更为重要，PrepareStatement可以防止SQL注入攻击。假设我们允许用户通过App输入一个名字到数据中心查找用户信息，如果用户输入的字符串是Frank，那么生成的SQL是这样的：

但是如果用户输入的是这样一个字符串：

那么生成的SQL就是这样的：

这条SQL提交到数据库以后，会被当做两条SQL执行，一条是正常的select查询SQL，一条是删除users表的SQL。黑客提交一个请求然后users表被删除了，系统崩溃了，这就是SQL注入攻击。

如果用Statement提交SQL就会出现这种情况。

但如果用PrepareStatement则可以避免SQL被注入攻击。因为一开始构造PrepareStatement的时候就已经提交了查询SQL，并被数据库预先生成好了执行计划，后面黑客不管提交什么样的字符串，都只能交给这个执行计划去执行，不可能再生成一个新的SQL了，也就不会被攻击了。

回到文章开头提出的另一个问题，数据库通过索引进行查询能加快查询速度，那么，为什么索引能加快查询速度呢？

数据库索引使用B+树，我们先看下B+树这种数据结构。B+树是一种N叉排序树，树的每个节点包含N个数据，这些数据按顺序排好，两个数据之间是一个指向子节点的指针，而子节点的数据则在这两个数据大小之间。

如下图。

B+树的节点存储在磁盘上，每个节点存储1000多个数据，这样树的深度最多只要4层，就可存储数亿的数据。如果将树的根节点缓存在内存中，则最多只需要三次磁盘访问就可以检索到需要的索引数据。

B+树只是加快了索引的检索速度，如何通过索引加快数据库记录的查询速度呢？

数据库索引有两种，一种是聚簇索引，聚簇索引的数据库记录和索引存储在一起，上面这张图就是聚簇索引的示意图，在叶子节点，索引1和记录行r1存储在一起，查找到索引就是查找到数据库记录。像MySQL数据库的主键就是聚簇索引，主键ID和所在的记录行存储在一起。MySQL的数据库文件实际上是以主键作为中间节点，行记录作为叶子节点的一颗B+树。

另一种数据库索引是非聚簇索引，非聚簇索引在叶子节点记录的就不是数据行记录，而是聚簇索引，也就是主键，如下图。

通过B+树在叶子节点找到非聚簇索引a，和索引a在一起存储的是主键1，再根据主键1通过主键（聚簇）索引就可以找到对应的记录r1，这种通过非聚簇索引找到主键索引，再通过主键索引找到行记录的过程也被称作回表。

所以通过索引，可以快速查询到需要的记录，而如果要查询的字段上没有建索引，就只能扫描整张表了，查询速度就会慢很多。

数据库除了索引的B+树文件，还有一些比较重要的文件，比如事务日志文件。

数据库可以支持事务，一个事务对多条记录进行更新，要么全部更新，要么全部不更新，不能部分更新，否则像转账这样的操作就会出现严重的数据不一致，可能会造成巨大的经济损失。数据库实现事务主要就是依靠事务日志文件。

在进行事务操作时，事务日志文件会记录更新前的数据记录，然后再更新数据库中的记录，如果全部记录都更新成功，那么事务正常结束，如果过程中某条记录更新失败，那么整个事务全部回滚，已经更新的记录根据事务日志中记录的数据进行恢复，这样全部数据都恢复到事务提交前的状态，仍然保持数据一致性。

此外，像MySQL数据库还有binlog日志文件，记录全部的数据更新操作记录，这样只要有了binlog就可以完整复现数据库的历史变更，还可以实现数据库的主从复制，构建高性能、高可用的数据库系统。

做应用开发需要了解RDBMS的架构原理，但是关系数据库系统非常庞大复杂，对于一般的应用开发者而言，全面掌握关系数据库的各种实现细节，代价高昂，也没有必要。我们只需要掌握数据库的架构原理与执行过程，数据库文件的存储原理与索引的实现方式，以及数据库事务与数据库复制的基本原理就可以了。然后，在开发工作中针对各种数据库问题去思考，其背后的原理是什么，应该如何处理。通过这样不断地思考学习，不但能够让使用数据库方面的能力不断提高，也能对数据库软件的设计理念也会有更深刻的认识，自己软件设计与架构的能力也会得到加强。

本文作者及来源:Renderbus瑞云渲染农场https://www.renderbus.com