数据库知识

概要¶

一个数据库的组成主要可以分为以下几个模块：

• 存储（文件系统）
• 程序实例
  • 存储管理
  • 缓存机制（淘汰机制）
  • SQL解析
  • 日志管理
  • 权限划分
  • 容灾机制
  • 索引管理
  • 锁管理

ACID四原则¶

事务具有四个特性：原子性、一致性、隔离性和持久性。

• 原子性：事务是数据库的逻辑工作单位，不可分割，事务中包含的各操作要么都做，要么都不做。
• 一致性：事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是 不一致的状态。
• 隔离性：一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。
• 持久性：指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的，不能回滚。接下来的其它操作或故障不应该对其执行结果有任何影响。

SQL¶

SQL书写规则：

• 以分号结尾
• SQL不区分关键词的大小写，插入到表中的数据区分大小写
• 推荐关键字大写，表名的首字母大写，其余(列名等)小写
• 常数用单引号进行包裹
• 单词之间需要使用半角空格或者换行符进行分隔

数据库管理¶

创建数据库：CREATE DATABASE <数据库名称>;

创建表：CREATE TABLE <表名> (<列名> <数据类型> <该类所需约束>,...,<该表的约束1>，<该表的约束2>);

删除表：DROP TABLE <表名>;

表定义的更新：

• 添加列：ALTER TABLE <表名> ADD COLUMN <列名> <列类型>; Oracle和SQL Server中不用写COLUMN。，Oracle可以ALTER TABLE <表名> ADD （ <列名>， <列名>，……）；
• 删除列：ALTER TABLE <表名> DROP COLUMN <列名>;Oracle的特殊同上。

只能使用半角英文字母（开头）、数字、下划线作为数据库、表、列的名称。

同一数据库表不能重名，同一个表里列也不能重名。

对于Mysql来说，时间类型如下：

时间类型	占用空间	日期格式	最小值	最大值	零值表示
DATETIME	8 bytes	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00	9999-12-31 23:59:59	0000-00-00 00:00:00
TIMESTAMP	4 bytes	YYYY-MM-DD HH:MM:SS	19700101080001	2038 年的某个时刻	00000000000000
DATE	4 bytes	YYYY-MM-DD	1000-01-01	9999-12-31	0000-00-00
TIME	3 bytes	HH:MM:SS	-838:59:59	838:59:59	00:00:00
YEAR	1 bytes	YYYY	1901	2155	0000

一般建表时候，创建时间用datetime，更新时间用timestamp。

数据表重命名：

• Oracle、PostgreSQL：ALTER TABLE <表名> RENAME TO <新的表名>
• DB2：RENAME TABLE <表名> TO <新的表名>
• SQL SERVER:sp_rename '表名','新的表名'
• Mysql:RENAME TABLE <表名> TO <新的表名>

SELECT语句¶

基本语法：SELECT <列名>,... FROM <表名>。列名之间用逗号分割。

使用*进行select无法设定列的显示顺序，按照CREATE TABLE语句的定义顺序。

插入空行会导致执行错误。

使用AS为列设置别名，格式如下：SELECT <列名> AS <别名>...。别名可以使用中文，但是要用双引号包裹。

可以在SELECT语句中书写常量，如下所示。这些常量会显示在每条查询出的结果的前面。

SELECT '商品' AS string, 38 AS number, '2009-02-24' AS date,
product_id, product_name
FROM Product;

在SELECT语句中删除重复行时，使用关键词DISTINCT。DISTINCT 关键字只能用在第一个列名之前。SELECT DISTINCT <列名> FROM <表名>。NULL也会被合并。

SELECT语句通过WHERE子句指定查询条件。WHERE子句要紧跟在FROM子句之后。

单行注释--，Mysql需要在--之后加上一个半角空格。多行注释使用/* */。

**所有包含NULL的运算，其结果都是NULL。**即使除零也还是NULL。

FROM子句在SELECT中并不是必不可少，例如SELECT (100 + 200) * 3 AS calculation;Oracle不允许省略FROM。可以使用DUAL临时表。

不等于<>，!=不被标准SQL承认。

比较运算符可以对字符、数值和日期等几乎所有数据类型的列和值进行比较。

使用比较运算符时一定要注意不等号和等号的位置。

对字符串类型的数据进行大小比较时，规则是按照字典顺序。以相同字符开头的单词比不同字符开头的单词更相近。该规则对定长字符串和可变长字符串都适用。

**不能对NULL使用比较运算符。**专门用来判断是否为 NULL 的 IS NULL或 IS NOT NULL 运算符。

NOT运算符用来否定某一条件，但是不能滥用。

多个查询条件进行组合时，需要使用AND运算符或者OR运算符。

AND 运算符优先于 OR 运算符，可以使用()，改变优先级。

使用 AND 运算符进行的逻辑运算称为逻辑积，使用 OR 运算符进行的逻辑运算称为逻辑和。

SQL中除了真值和假值以外，存在第三种，即不确定NULL。即三值逻辑。

聚合和排序¶

通常，聚合函数会对NULL以外的对象进行汇总。但是只有COUNT函数例外，使用COUNT（*）可以查出包含NULL在内的全部数据的行数。

函数	用处
COUNT	计算表中的记录数(行数)
SUM	计算表中数值列中数据的合计值
AVG	计算表中数值列中数据的平均值
MAX	计算表中数值列中数据的最大值
MIN	计算表中数值列中数据的最小值

*是COUNT函数特有的参数，其它函数不能使用其作为参数。COUNT(*)返回包含NULL的数据行数，COUNT(列名)则返回不包含NULL的数据行数。

使用聚合函数时，NULL的数据会被排除在外，不能简单的理解为等价于0。

SUM和AVG函数只对数值类型的列使用，MAX和MIN函数适用于任何数据类型的列。

去掉重复值的聚合函数，格式形如SELECT COUNT (DISTINCT product_type) FROM Product。

DISTINCT关键字必须写在括号中。写在外面会优先执行聚合函数，然后才是DISTINCT。

使用GROUP BY子句进行汇总：

SELECT <列名1>,<列名2>,<列名3>,...FROM <表名> GROUP BY <列名1>,<列名2>,<列名3>,...；

在GROUP BY子句中指定的列称为聚合键或分组列。

GROUP BY子句必须写在FROM子句之后。如果有WHERE子句，需要卸载WHERE子句之后。

聚合键中包含NULL时，在结果中会以“不确定”行（空行）的形式表现出来。

使用 WHERE 子句进行汇总处理时，会先根据 WHERE 子句指定的条件进行过滤，然后再进行汇总处理。

子句书写顺序：SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> OREDER BY

子句执行顺序：FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> OREDER BY

与聚合函数和GROUP BY子句有关的常见错误：

• 在SELECT子句中书写了多余的列。使用聚合函数时，SELECT子句只能存在以下三种元素：常数，聚合函数，GROUP BY子句中指定的列名(也就是聚合键)，MYSQL支持在多列候补中只要有一列满足要求就可以，其他RDBMS都不支持。因为聚合键和另外的列名不一定是一对一关系。
• 在GROUP BY子句中写了列的别名。在MYSQL中不算错误，但是不标准，因为标准SQL的执行顺序的影响，GROUP BY 时还未执行SELECT，系统也就不知道列的别名。
• GROUP BY子句结果的显示是无序的。
• 只有在SELECT子句、HAVING子句和ORDER BY子句中能够使用COUNT等聚合函数。

使用COUNT函数等对表中数据进行汇总操作时，为其指定条件的不是WHERE子句，而是HAVING子句。

HAVING子句要写在GROUP BY子句之后。

WHERE子句用来指定数据行的条件， HAVING子句用来指定分组的条件。

SELECT <列名1>, <列名2>, <列名3>, ……
FROM <表名>
GROUP BY <列名1>, <列名2>, <列名3>, ……
HAVING <分组结果对应的条件>

HAVING 子句中能够使用的 3 种要素:常数、聚合函数、GROUP BY子句中指定的列名。

在 WHERE 子句和 HAVING 子句中都可以使用的条件，最好写在 WHERE 子句中。WHERE在排序之前就删去了部分数据，且便于建立索引，执行速度也会更快。

若查询没有指定顺序，则返回的结果往往是随机的。

OREDER BY：

SELECT <列名1>, <列名2>, <列名3>, ……
FROM <表名>
ORDER BY <排序基准列1>, <排序基准列2>, ……

不论何种情况， ORDER BY 子句都需要写在 SELECT 语句的末尾。这是因为对数据行进行排序的操作必须在结果即将返回时执行。

OREDER BY默认是升序，也可以显式声明ASC，使用关键词DESC降序。

OREDER BY指定多个排序键时，优先使用左侧的键，相同情况下在参考右侧的键。

排序键中包含NULL时，会在开头或末尾进行汇总。

在ORDER BY子句中可以使用SELECT子句中定义的别名。但是GROUP BY不可以。因为 SELECT 子句的执行顺序在 GROUP BY 子句之后， ORDER BY 子句之前。

在ORDER BY子句中可以使用SELECT子句中未使用的列和聚合函数。

索引¶

当数据量巨大时，通过索引可以大幅提升查询数据的速度。而数据量很少的时候，直接读入内存进行全表扫描会更快。

索引类型¶

主键、唯一键以及普通键等。

索引的数据结构¶

• 建立二叉查找树进行二分查找
• 建立B-Tree结构进行查找
• 建立B±Tree结构进行查找（MYSQl）
• 建立Hash结构进行查找。

二叉查找树¶

定义¶

二叉查找数，是在二叉树的基础之上，约束其根节点的左节点小于根节点，右节点大于根节点。这样查找的时间复杂度为。但是由于这样每个节点至多只能有两个节点，在面对大量数据时，整个树的层级会很深且由于数据变动可能变成线性的导致查询效果变差。因此，引入B-Tree。

B-Tree¶

m阶B-Tree即子节点最多m个的B-Tree。

定义¶

• 根节点至少包含两个子节点
• 树中每个节点最多含有m个子节点（m>=2），m取决与节点的容量与相关配置。
• 除根节点和叶节点外，其他每个节点至少有ceil(m/2)个子节点（ceil为取上限）
• 所有叶子节点都在同一层
• 假设每个非终端节点中包含有n个关键字信息，其中
  • 为关键字，且关键字按顺序升序排序。
  • 关键字的个数n必须满足：。
  • 非叶子节点的指针：p[1],p[2],…,p[M]；其中p[1]指向关键字小于k[1]的子树，p[M]指向关键字大于k[M-1]的子树，其中p[i]指向关键字属于的子树。

B±Tree¶

定义¶

B±Tree是B-Tree的变体，其定义基本与B树相同，除了

• 非叶子节点的子树指针与关键字个数相同
• 非叶子节点的子树指针p[i]，指向关键字值[k[i],k[i+1]]的子树
• 非叶子节点仅用来索引，数据都保存在叶子节点中
• 所有叶子节点均有一个链指针指向下一个叶子节点，这样便于进行范围计算。例如大于某一个数的记录

优点¶

• B+树的磁盘读写代价更低，其非叶子节点因其不存储信息相较于B树大小更小，一次性读入内存的节点数更多，可以减少读写
• B+树的查询效率更加稳定，都是从根节点到叶子节点
• B+树更有利于对数据库的扫描，其叶子节点之间有着链指针，遍历链指针就能实现对全部数据的扫描，范围查询效果更好

Hash索引¶

hash索引也能可以作为索引，但是有以下缺陷，主要体现为SQL查询指令上的范围查找：

• 仅仅能满足“=”，“in”，不能使用范围查询
• 无法被用来避免数据的排序操作
• 不能利用部分索引键查询
• 不能避免表扫描
• 遇到大量hash值相等的情况后性能并不一定就会比B-Tree索引高

BitMap索引¶

定义¶

位图索引可以看作是存储了大量bit位的bit序列，并且通过这些bit序列上的按位操作来响应查询请求，同时每个bit序列中的位数与数据表中的行数是一致的。

索引模块¶

密集索引与稀疏索引¶

• 密集索引文件中的每个搜索码值都对应一个索引值

• 稀疏索引文件只为索引码的某些值建立索引项

• MylSAM引擎：主键索引、唯一键索引、普通索引其索引都属于稀疏索引

• InnoDB引擎：有且仅有一个密集索引

  • 若一个主键被定义，该主键则作为密集索引

  • 若没有主键被定义，该表的第一个唯一非空索引则作为密集索引

  • 若不满足以上条件，InnoDB内部会生成一个隐藏主键（密集索引），该列是一个6字节的列

  • 非主键索引存储相关键位和其对应的主键值，包括两次查找

    

连接¶

性能优化¶

在SQL在数据库中执行花费很长时间的时候需要考虑相应的性能优化问题。

在优化的过程中大致可以分成两个部分：即程序在执行sql的过程中可否优化以及sql语句本身可否优化。针对第一点明显的就是在大量数据需要插入的时候，能否将多条数据整合进入一条sql中，以此加快执行的速度。或给表加上索引，mysql可以使用explain查看sql执行计划来检查查询是否命中索引。

explain¶

explain执行计划中包含的信息如下：

• id: 查询序列号

  select查询的序列号(一组数字)，表示查询中执行select子句或者操作表的顺序。

  id列分为三种情况：

  • 如果id相同，那么执行顺序从上到下
  • 如果id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
  • id相同和不同的，同时存在：相同的可以认为是一组，从上往下顺序执行，在所有组中，id值越大，优先级越高，越先执行

• select_type: 查询类型

  主要用来分辨查询的类型，是普通查询还是联合查询还是子查询

  • simple: 简单的查询，不包含子查询和union
  • primary: 查询中若包含任何复杂的子查询，最外层查询则被标记为Primary
  • union: 在union，union all和子查询中的第二个和随后的select被标记为union
  • dependent union: 在包含UNION或者UNION ALL的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小查询之外，其余的小查询的select_type的值就是DEPENDENT UNION。
  • union result: 从union表获取结果的select。
  • subquery: 在select或者where列表中包含子查询（不在from子句中）
  • dependent subquery: 子查询中的第一个select（不在from子句中），而且取决于外面的查询。
  • derived: 在FROM列表中包含的子查询被标记为DERIVED，也叫做派生类
  • UNCACHEABLE SUBQUERY：一个子查询的结果不能被缓存，必须重新评估外链接的第一行对于外层的主表，子查询不可被物化，每次都需要计算（耗时操作）
  • uncacheable union: 表示union的查询结果不能被缓存

• table: 表名或者别名

  对应行正在访问哪一个表，表名或者别名，可能是临时表或者union合并结果集.

  • 如果是具体的表名，则表明从实际的物理表中获取数据，当然也可以是表的别名.
  • 表名是derivedN的形式，表示使用了id为N的查询产生的衍生表.
  • 当有union result的时候，表名是union n1,n2等的形式，n1,n2表示参与union的id.

• partitions: 匹配的分区

• type: 访问类型

  type显示的是访问类型，访问类型表示我是以何种方式去访问我们的数据，最容易想的是全表扫描，直接暴力的遍历一张表去寻找需要的数据，效率非常低下。

  访问的类型有很多，效率从最好到最坏依次是：

  system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

  一般情况下，要保证查询至少达到range级别，最好能达到ref

  • all: 全表扫描，需要扫描整张表，从头到尾找到需要的数据行。一般情况下出现这样的sql语句而且数据量比较大的话那么就需要进行优化。
  • index：全索引扫描这个比all的效率要好，主要有两种情况，一种是当前的查询时覆盖索引，即我们需要的数据在索引中就可以索取，或者是使用了索引进行排序，这样就避免数据的重排序
  • range：表示利用索引查询的时候限制了范围，在指定范围内进行查询，这样避免了index的全索引扫描，适用的操作符：=, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN()
  • index_subquery：利用索引来关联子查询，不再扫描全表

• possible_keys: 可能用到的索引

• key: 实际用到的索引

• key_len: 索引长度

• ref: 与索引比较的列

• rows: 估算的行数

• filtered: 按表条件筛选的行百分比

• Extra: 额外信息

Tip¶

mysql中的sql语句中表名、列名可以用`进行包裹。comment之后的文字用单引号进行包裹。