数据库综述

1、为什么需要
数据库数据保存在内存里面，存取方便，可以永久保存，集合了内存和文件存取数据的优点，查询效率使用sql 语句高
2、SQL : Structured Query Language 结构化查询语言。

SQL 基础：

增删改查：

SELECT name,country FROM Websites；
INSERT INTO Websites (name, url, alexa, country) VALUES ('百度','https://www.baidu.com/','4','CN');
UPDATE Websites  SET alexa='5000', country='USA' WHERE name='菜鸟教程';
DELETE FROM Websites WHERE name='Facebook' AND country='USA';

查有许多种形式：

1、数据有重复  SELECT DISTINCT country FROM Websites;
2、子句过滤： SELECT * FROM Websites WHERE country='USA' OR country='CN';  //这个应该是查询用的最多

运算符	描述
=	等于
<>	不等于。注释：在 SQL 的一些版本中，该操作符可被写成 !=
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEEN	在某个范围内
LIKE	搜索某种模式  可以用作模糊查找
IN	指定针对某个列的多个可能值

3、排序 SELECT * FROM Websites ORDER BY alexa;  //结果按照 aleax 排序  ASC 逆序

4、关联查询：

• 交叉连接（CROSS JOIN）
• 内连接（INNER JOIN）
• 外连接（LEFT JOIN/RIGHT JOIN）
• 联合查询（UNION与UNION ALL）
• 全连接（FULL JOIN）
• 交叉连接（CROSS JOIN）

外连接：

• 左外连接：LEFT OUTER JOIN, 以左表为主，先查询出左表，按照ON后的关联条件匹配右表，没有匹配到的用NULL填充，可以简写成LEFT JOIN
• 右外连接：RIGHT OUTER JOIN, 以右表为主，先查询出右表，按照ON后的关联条件匹配左表，没有匹配到的用NULL填充，可以简写成RIGHT JOIN

联合查询：

• 就是把多个结果集集中在一起，UNION前的结果为基准，需要注意的是联合查询的列数要相等，相同的记录行会合并
• 如果使用UNION ALL，不会合并重复的记录行
• 效率 UNION 高于 UNION ALL

全连接：

• MySQL不支持全连接
• 可以使用LEFT JOIN 和UNION和RIGHT JOIN联合使用

子查询：

1. 条件：一条SQL语句的查询结果做为另一条查询语句的条件或查询结果

2. 嵌套：多条SQL语句嵌套使用，内部的SQL查询语句称为子查询。

不同的删除：

	Delete	Truncate	drop
类型	属于DML	属于DDL	属于DDL
回滚	可回滚	不可回滚	不可回滚
删除内容	表结构还在，删除表的全部或者一部分数据行	表结构还在，删除表中的所有数据	从数据库中删除表，所有的数据行，索引和权限也会被删除
删除速度	删除速度慢，需要逐行删除	删除速度快	删除速度最快

 在不再需要一张表的时候，用drop；在想删除部分数据行时候，用delete；在保留表而删除所有数据的时候用truncate。

 

 

三大范式：

第一范式：每个列都不可以再拆分。

第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。

第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。

在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。比如性能。事实上我们经常会为了性能而妥协数据库的设计。

• 主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。
• 外键：在一个表中存在的另一个表的主键称此表的外键。

约束：

• NOT NULL: 用于控制字段的内容一定不能为空（NULL）。
• UNIQUE: 控件字段内容不能重复，一个表允许有多个 Unique 约束。
• PRIMARY KEY: 也是用于控件字段内容不能重复，但它在一个表只允许出现一个。
• FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
• CHECK: 用于控制字段的值范围。

MYSQL 权限表格：

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。这些权限表分别user，db，table_priv，columns_priv和host。下面分别介绍一下这些表的结构和内容：

• user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。
• db权限表：记录各个帐号在各个数据库上的操作权限。
• table_priv权限表：记录数据表级的操作权限。
• columns_priv权限表：记录数据列级的操作权限。
• host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

mysql有哪些数据类型

1、整数类型，包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别表示1字节、2字节、3字节、4字节、8字节整数。任何整数类型都可以加上UNSIGNED属性，表示数据是无符号的，即非负整数。
长度：整数类型可以被指定长度，例如：INT(11)表示长度为11的INT类型。长度在大多数场景是没有意义的，它不会限制值的合法范围，只会影响显示字符的个数，而且需要和UNSIGNED ZEROFILL属性配合使用才有意义。
例子，假定类型设定为INT(5)，属性为UNSIGNED ZEROFILL，如果用户插入的数据为12的话，那么数据库实际存储数据为00012。

2、实数类型，包括FLOAT、DOUBLE、DECIMAL。
DECIMAL可以用于存储比BIGINT还大的整型，能存储精确的小数。
而FLOAT和DOUBLE是有取值范围的，并支持使用标准的浮点进行近似计算。
计算时FLOAT和DOUBLE相比DECIMAL效率更高一些，DECIMAL你可以理解成是用字符串进行处理。

3、字符串类型，包括VARCHAR、CHAR、TEXT、BLOB
VARCHAR用于存储可变长字符串，它比定长类型更节省空间。
VARCHAR使用额外1或2个字节存储字符串长度。列长度小于255字节时，使用1字节表示，否则使用2字节表示。
VARCHAR存储的内容超出设置的长度时，内容会被截断。
CHAR是定长的，根据定义的字符串长度分配足够的空间。
CHAR会根据需要使用空格进行填充方便比较。
CHAR适合存储很短的字符串，或者所有值都接近同一个长度。
CHAR存储的内容超出设置的长度时，内容同样会被截断。

使用策略：
对于经常变更的数据来说，CHAR比VARCHAR更好，因为CHAR不容易产生碎片。
对于非常短的列，CHAR比VARCHAR在存储空间上更有效率。
使用时要注意只分配需要的空间，更长的列排序时会消耗更多内存。
尽量避免使用TEXT/BLOB类型，查询时会使用临时表，导致严重的性能开销。

4、枚举类型（ENUM），把不重复的数据存储为一个预定义的集合。
有时可以使用ENUM代替常用的字符串类型。
ENUM存储非常紧凑，会把列表值压缩到一个或两个字节。
ENUM在内部存储时，其实存的是整数。
尽量避免使用数字作为ENUM枚举的常量，因为容易混乱。
排序是按照内部存储的整数

5、日期和时间类型，尽量使用timestamp，空间效率高于datetime，
用整数保存时间戳通常不方便处理。
如果需要存储微妙，可以使用bigint存储。

shuj

CHAR 和varchar  区别：

1、char 是不变的定长，varchar是变长的

2、插入数据小于char 则用空格填充，varchar有多少数据就用多少
3、char 最多存放255  varchar 最多65532
4、varchar 存取慢，不占用多的空间，时间换空间的做法

varchar(50)  50 主要看版本  4.0以下是50个字节，一个汉字是三个字节，5.0以上是50个字符  不管汉字还是字母

int(20) : 显示的最大长度，但是还是4个字节的类型，只占四个字节，不影响内部存储，只影响fillzero

FLOAT和DOUBLE的区别是什么？

• FLOAT类型数据可以存储至多8位十进制数，并在内存中占4字节。
• DOUBLE类型数据可以存储至多18位十进制数，并在内存中占8字节。

MyISAM索引与InnoDB索引的区别？

InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。

InnoDB引擎的4大特性

• 插入缓冲（insert buffer)

• 二次写(double write)

• 自适应哈希索引(ahi)

• 预读(read ahead)

引擎：

MySQL存储引擎MyISAM与InnoDB区别
存储引擎Storage engine：MySQL中的数据、索引以及其他对象是如何存储的，是一套文件系统的实现。

常用的存储引擎有以下：

Innodb引擎：Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。
MyIASM引擎(原本Mysql的默认引擎)：不提供事务的支持，也不支持行级锁和外键。
MEMORY引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高。
 

存储引擎选择

如果没有特别的需求，使用默认的Innodb即可。

MyISAM：以读写插入为主的应用程序，比如博客系统、新闻门户网站。

Innodb：更新（删除）操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键。比如OA自动化办公系统。

索引：

1、为什么用？有何优势？

        索引包含对数据记录的指针，是一个特殊的文件，索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树，索引相当于目录，通过目录查找相应的数据

        主要优点，大大提高检索的效率，缺点就是 创建和维护索引需要时间、索引需要占用一定的物理空间

2、索引类型？

主要分四种： 唯一索引 、主键索引、普通索引、全文索引

• 主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。 很多数据的ID 就是这种
• 唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引
• 普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引
• 全文索引： 是目前搜索引擎使用的一种关键技术。可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引

3、索引数据结构？

B+树，HASH 

InnoDB 引擎默认是B+树

在单条记录时候，使用hash 来查找，效率快，其他场景应该使用B+树

先给下B树的定义： 多路平衡查找树

一颗m阶的B树定义如下：

1）每个结点最多有m-1个关键字。

2）根结点最少可以只有1个关键字。

3）非根结点至少有Math.ceil(m/2)-1个关键字。

4）每个结点中的关键字都按照从小到大的顺序排列，每个关键字的左子树中的所有关键字都小于它，而右子树中的所有关键字都大于它。

5）所有叶子结点都位于同一层，或者说根结点到每个叶子结点的长度都相同。

1、使用B+树，非叶子节点全部储存键值，而不是数据，数据库中页的大小是固定的，innodb中页的默认大小是16KB。如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的IO次数有会再次减少，数据查询的效率也会更快。

2、所有数据在叶子节点，数据按照顺序排列，。那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单

聚集索引和非聚集索引：

本质点是键值的存储，是用主键还是非主键

区别：

非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。 明白了聚集索引和非聚集索引的定义，我们应该明白这样一句话：数据即索引，索引即数据

索引设计的原则？

适合索引的列是出现在where子句中的列，或者连接子句中指定的列

• 基数较小的类，索引效果较差，没有必要在此列建立索引
• 使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间
• 不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。

数据库为什么使用B+树而不是B树

• B树只适合随机检索，而B+树同时支持随机检索和顺序检索；
• B+树空间利用率更高，可减少I/O次数，磁盘读写代价更低。一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗。B+树的内部结点并没有指向关键字具体信息的指针，只是作为索引使用，其内部结点比B树小，盘块能容纳的结点中关键字数量更多，一次性读入内存中可以查找的关键字也就越多，相对的，IO读写次数也就降低了。而IO读写次数是影响索引检索效率的最大因素；
• B+树的查询效率更加稳定。B树搜索有可能会在非叶子结点结束，越靠近根节点的记录查找时间越短，只要找到关键字即可确定记录的存在，其性能等价于在关键字全集内做一次二分查找。而在B+树中，顺序检索比较明显，随机检索时，任何关键字的查找都必须走一条从根节点到叶节点的路，所有关键字的查找路径长度相同，导致每一个关键字的查询效率相当。
• B-树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。B+树的叶子节点使用指针顺序连接在一起，只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作。
• 增删文件（节点）时，效率更高。因为B+树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率。

事务：

数据库的事务，ACID四大特性：

• 原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
• 一致性： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
• 隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
• 持久性： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

脏读、不可重复读、幻读

事务的隔离级别：

• READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
• READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
• REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
• SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读

Mysql 默认采用的 REPEATABLE_READ隔离级别 Oracle 默认采用的 READ_COMMITTED隔离级别

锁的问题：

因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED(读取提交内容):，但是你要知道的是InnoDB 存储引擎默认使用 **REPEATABLE-READ（可重读）**并不会有任何性能损失

隔离级别与锁的关系:  读取时候加锁  修改数据也加锁，就会导致，一个数据只能一个事务被占用

• 在Read Uncommitted级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突
• 在Read Committed级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁；
• 在Repeatable Read级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁。
• SERIALIZABLE 是限制性最强的隔离级别，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。

锁分颗粒度：行级锁(INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎 )

• MyISAM采用表级锁(table-level locking)。
• InnoDB支持行级锁(row-level locking)和表级锁，默认为行级锁

锁的类型： 读写锁

• 共享锁: 又叫做读锁。 当用户要进行数据的读取时，对数据加上共享锁。共享锁可以同时加上多个。
• 排他锁: 又叫做写锁。 当用户要进行数据的写入时，对数据加上排他锁。排他锁只可以加一个，他和其他的排他锁，共享锁都相斥。

InnoDB存储引擎的锁的算法有三种

• Record lock：单个行记录上的锁
• Gap lock：间隙锁，锁定一个范围，不包括记录本身
• Next-key lock：record+gap 锁定一个范围，包含记录本身

相关知识点：

• innodb对于行的查询使用next-key lock
• Next-locking keying为了解决Phantom Problem幻读问题
• 当查询的索引含有唯一属性时，将next-key lock降级为record key
• Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生
• 有两种方式显式关闭gap锁：（除了外键约束和唯一性检查外，其余情况仅使用record lock） A. 将事务隔离级别设置为RC B. 将参数innodb_locks_unsafe_for_binlog设置为1

并发处理手段： 乐观锁，悲观锁

• 悲观锁：假设会发送冲突，屏蔽一切违反数据完整性的操作，在查询完数据的时候就把事务锁起来，直到提交事务，使用数据库的锁机制
• 乐观锁： 假设不会发生冲突，只有提交数据才检查数据完整性，修改数据将事务上锁，通过版本锁定，一般实现方法是版本号管理和csa算法

几个基本概念和应用：

视图：

游标：游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果，每个游标区都有一个名字。用户可以通过游标逐一获取记录并赋给主变量，交由主语言进一步处理

函数：

存储过程：

优点：

• 1）存储过程是预编译过的，执行效率高。
• 2）存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。
• 3）安全性高，执行存储过程需要有一定权限的用户。
• 4）存储过程可以重复使用，减少数据库开发人员的工作量。

缺点：

• 1）调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。
• 2）移植问题，数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。
• 3）重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。
• 4）如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦

触发器：触发器是用户定义在关系表上的一类由事件驱动的特殊的存储过程。触发器是指一段代码，当触发某个事件时，自动执行这些代码。

使用场景

• 可以通过数据库中的相关表实现级联更改。
• 实时监控某张表中的某个字段的更改而需要做出相应的处理。例如可以生成某些业务的编号。
• 注意不要滥用，否则会造成数据库及应用程序的维护困难。

在MySQL数据库中有如下六种触发器：

• Before Insert
• After Insert
• Before Update
• After Update
• Before Delete
• After Delete

数据库优化策略：

1、优化where子句，尽量避免全表扫描：

• where 子句避免出现 != <>  in  /  not in  / like 等关键字
• where 子句尽量避免  对字段进行表达式操作
• where 子句尽量避免函数操作，可以使用通配符操作来替代函数

参考文献：

1、SQL 基础教程
2、B+树 
3、mysql 数据库