数据库知识点

1.数据库有哪几种范式并举例

第一范式(1NF)：数据库表中的每一个字段都是单一属性的，这个属性由基本数据类型组成，如为地址这个属性可以分为XX省XX市XX区XX详细地址
**第二范式(2NF)：**在第一范式的基本上，数据库表中不存在对非关键字段对候选码关键词的部分依赖，如学号，课程号，成绩，和性别；主键为学号和课程号，但是性别这个字段完全依赖于学号，存在着非关键字段对候选码的部分依靠；
第三范式(3NF)：在第二范式的基本上，数据库表中不存在非关键字段对任一候选码字段的传递依赖；
如在学生表中，学号，系别号，和系主任，则学号–>系别号 --> 系主任，存在着非关键词段对任一候选码的传递依赖，不符合3NF;

数据冗余、更新异常、插入异常和删除异常。

2、索引是什么，有什么作用和优缺点

是什么?
索引是数据库表中由一列或者多列的值进行排序的数据结构，使用索引可以快速访问数据库表中的特定性别，如果我们商品的价格作为索引，当以商品的价格为查询条件是可以更快的获得查询结构。
(索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中，索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。)
索引的实现通常使用B树及其变种B+树；
在数据之外，数据库系统还维护着满足特定查询算法的数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以让这些数据结构实现高级的查询算法。这种数据结构，叫做索引。
为表设置索引要付出代价的:一是增加了数据库的存储空间，二是在插入和修改数据时要花费更多的时间(因为索引是随之变动的)

上图 展示了一种可能的索引方式。左边是数据库表，右边的一个二叉查找树。左边的表最右边的为物理存储地址(注意逻辑上相邻在磁盘上的物理地址不一定是相邻，除了聚集索引)，为了加快col2的查询，可以维护一个右边所示的 的二叉树，每个节点分别包含着索引值及其对应的数据记录物理地址的指针，这样我们可以运用二叉树查找在O(log2n)的复杂度内获得相应的数据。

底层数据结构是B+数:

在数据结构中，我们最为常见的搜索结构就是二叉搜索树和AVL（高度平衡二叉搜索树，为了提高二叉搜索树的效率，减少数据搜索长度)。然而，无论是二叉搜索树还是AVL树，当数据量比较大时，都会由于树的深度过大而造成I/O读写过于频繁，进而导致查询效率低下，因此对于索引而言，多叉结构成为不二选择。特别的是，B-Tree的各种操作都能使B树保持较低的高度，从而保证高效的查询效率。

使用B+树的原因：
查询速度快、效率高，在查找的过程中，每次都能抛弃掉一部分的节点，减少遍历个数。

作用（优点）
创建索引可以大大的提交我们的查询效率，提高我们系统的性能。
1、可以大大加快数据的查询效率，这也是创建索引的主要原因；
2、在使用分组和排序语句进行数据检索时，同样也可以显著减少查询中的分组和排序的时间，提交效率
3、可以加速表与表之间的连接，特别是在实现数据的参照性完整性时特别有意义；
4、可以创建唯一索引，可以保证数据库表中每一条数据的唯一性；
5、通过使用索引，可以在查询的过程中 ，使用优化隐藏器，提高系统的性能。

缺点：
1.创建索引需要加大数据库存储空间，除了基本的数据库表要占据空间之外，每一个索引都为一个数据结构需要占据一定得物理空间，如果要建立聚簇索引，那么占据的物理空间会更大；
2.当对表中的数据进行增加、修改和删除时，索引也会进行动态的维护，进行重构索引，这样就会降低了我们数据的维护效率，降低了系统的性能；
3.创建索引和维护索引都需要时间，这种都是随数据量的增加而增加。

应该在那些键上：
1.经常作为查询条件的列，可以加快搜索的速度；
2.经常作为连接的列，这些列一般都为外键，可以加快连接的速度；
3.在经常需要排序的列创建索引，因为索引已经是排序了，这样可以利用索引的排序，加快排序的查询时间；
4.在经常需要进行分组的列上

不应该在那些键上：
1.当修改性能远大于查询性能的列不适合建立索引 ，这是因为修改和检索的性能是相互矛盾的，当添加索引时，会提高查询的速度但会减低增删改的效率；当减少索引时，会减低查询的速度但会提高增删改的效率；
2.对于在查询中很少使用作为条件的列或者参照列不应该建立索引；因为这些没有使用到的列建立索引，也不能提高查询的效率；但是增加了索引，增大了空间的需求和减低了系统的维护速度；
3.对于那些只有很少数据值的列不应该增加索引，因为这些列取值很少，例如人事表中的性别，在查询的结果中，结果集的数据行占了表中数据行很大的比例，增加索引，并不能明显加快检索速度；
4.对于那些定义为text，image和bit数据类型的列不应该创建索引，因为这些列的数据量要不相当大，要么取值很少。

索引分类
1、主键索引
设定为主键后的数据库会自动建立索引，innodb为聚簇索引；
2、唯一索引
索引列的值必须唯一，但允许有空值
3、单值索引
即一个索引只包含单个列，一个表中可以有多个单列索引；
4、复合索引
即一个索引包含了多个列
5、聚集索引
在聚集索引中，表中行的物理顺序与键值得逻辑位置顺序相同，一个表中只能包含一个聚集索引；
如果某索引不是聚集索引，则表中行的物理顺序与逻辑顺序不相同；与非聚集索引相比，聚集索引通常提供更快的数据查询速度。

3.数据库中的乐观锁和悲观锁是什么？

数据库管理系统(DBMS)中的并发控制的任务是保证多个事务同时操作数据库中的同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。

乐观并发管理(乐观锁)和悲观并发管理(悲观锁)是并发控制主要采用的方式。
1、悲观锁：假定会发生冲突，屏蔽一切可能会违反数据完整性的操作；
2、乐观锁：假定不会发生冲突，只要提交操作时检查是否违法数据完整性操作

数据库管理系统概述：
数据库管理系统(DBMS)是一种操纵和管理数据库的大型软件（MYSQL,ORACLE,SQL SERVER）,用户创建。使用和维护数据库。它对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。用户可以通过DBMS访问数据库中的数据，数据库管理员也通过数据库进行维护工作。他可以支持多个应用程序和用户用不同的方法和同时或不同时去建立、修改和访问数据库。大部分的DBMS都提供了DDL和DML(数据操作语言)，供用户定义数据库的模式结构与权限约束，实现对数据的增删改查。

数据完整性： 是指数据的精确性和可靠性
1、实体完整性：是保证表中所有行的唯一。实体完整性要求表中的所有行都有自己的唯一标识。这个标识可以是一列或者多列，称为主键；
2、参照完整性：是保证主键表(被引用表)和外部关键表(引用表)之间的参照关系；它涉及到了2个以上表数据的一致性维护。外键值将引用表的包含此外键的记录和被引用的表相匹配的记录关联起来；
3、自定义完整性(域完整性):
非空 (not null) 唯一（unique） 默认值(default) 约束(check)

4、数据库的性能优化

实践中，MySQL的优化主要涉及SQL语句及索引的优化、数据表结构的优化、系统配置的优化和硬件的优化四个方面
1、数据库结构设计层面，主要是表结构、还有数据库分库分表
（1）、索引，在经常作为查询条件的列和其他适合索引的列建立索引，提高查询的效率；
（2）、建表是选择为字段选择最适当的字段长度(宽度)，一般来说，数据库的表越小，在它上面执行的查询效率就会越快；因此，在创建表的时候，为了获得更好的性能，我们可以为字段设置更小的宽度；
（3）分库分表，当数据量特别大时，特别是微服务不同的模块时，可以为不同的模块建立不同的数据库和表，这样可以减少需要查询的数据量，提交查询的速度；
（4）分表分区，对数量很大的表(如超过一亿条数据)，可以按时间归类，将表进行水平拆分为多张小表、

2、sql语句的优化：优化Sql语句，可以提高查询数据的性能；
（1）尽量不使用*进行返回查询结果，用具体的值进行替代，不要返回用不到的字段；
（2）对查询进行优化，应尽量避免全表扫描，首先应考虑在where、order by 和group by涉及的字段建立索引；
（3）应尽量避免使用在where子句中使用!=或者<>操作符，MYSQL只有对以下的操作符才会使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE。
（4）In 和not in也要慎用，否则会导致全表扫描，对于连续的数值，能用BETWEEN就不要用in；
（5）尽量避免中使用or进行连接条件，否则会导致引擎放弃使用索引而进行全表扫描，可以使用union进行合并查询；
（6）很多时候用 exists 代替 in 是一个好的选择： select num from a where num in(select num from b).用下面的语句替换： select num from a where exists(select 1 from b where num=a.num)
（7）笛卡尔积 使用join被驱动表 和驱动表进行查询，这样会减少笛卡尔积的记录数，减少查询的记录总数
（8）当有一批 批量操作插入或更新时，要用批量更新或批量插入，不要用一条条数据区更新。

3、使用缓存 redis、mongoDB
4、读写分离
就是将数据库分为主从库，一个主库用于写数据，多个从库用于完成读数据的操作，主从库之间通过某种机制进行数据同步，是一种常见的数据库架构。

5、Mysql的存储引擎

定义：数据库引擎用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务，从而满足企业大量数据的应用程序要求，使用数据库引擎创建用于联机事务处理或联机分析处理数据的关系数据库。这包括创建用于存储数据的表和用于查看、管理和保护数据安全的数据库对象(如索引、视图和存储过程)。
为什么要合理的选择数据库存储引擎(不同的解决方案)

MYSQL中的数据使用不同的技术存储在文件(或者内存)中。这些技术中的每一个技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同功能。通过选择不同的技术你能够获得额外的速度或者功能，从而改善你的应用整体功能。

常用的四种存储引擎介绍：

1、InnoDB存储引擎(需要事务的表)
该存储引擎提供了提交，回滚和崩溃恢复能力的事务安全.但是相比于MYISAM引擎，写的效率较慢，并且会占用更多的物理空间去保留数据和索引。
特点：支持事务；支持自动增长索引；支持外键约束；行级锁；并发

2、MEMORY存储引擎
Memory存储结构使用存在内存的内容创建表。读的效率快，每一个memory都对应一个磁盘文件，对表的访问速度是非常的快；并且它的数据时存储在内存中，默认使用hash索引，但是一旦关闭服务，表中的数据就会丢失
特点：存储在内容中查询的速度非常快；关闭服务表的数据会丢失占据内存

3、myisam存储引擎(可用于只做查询的表)
不支持事务、也不支持外键，优势是访问的速度快，对事务完整性没有要求；
有三种不同的存储格式：静态表、动态表和压缩表
4、merge存储引擎
Merge存储引擎是一组MYISAM表的组合，这些MyISAM表必须结构完全相同，merge表本身并没有数据，对merge类型的表进行查询，更新，删除操作，这些操作实际是对内部的myisam表进行操作。
5、Berkeley DB ：支持事务

6、drop,delete与truncate的区别？

Drop直接删除表；Truncate是删除表然后再重建表；Delete是刪除表中的数据，也可以加上where条件

drop table
1、属于DDL语句；数据库定义语句；
2、不可回滚；
3、不可带where语句；
4、表内容和表结构删除；删除的速度快；
truncate table
1、属于DDL语句；数据库定义语句；
2、不可回滚；
3、不可带where语句；
4、表的内容和结构删除后，再重建表结构
5、删除的速度快
delete from
1、属于DML 数据库操作语言
2、可回滚
3、可带where语句
4、表结构在，表的内容要看where的执行情况
5、删除的速度慢；因为要逐行删除

使用简要说明
1、当不需要一张表且不要数据时，使用drop table；
2、当需要保留表但是不保存所有数据时，使用truncate table；
3、当要删除部分表数据时，使用delete

7、什么是存储过程？有哪些优缺点

存储过程就像是编程语言中的函数(方法)一样,封装了我们的代码。(PLSQL,T-SQL)

T-SQL 即 Transact-SQL，是 SQL 在 Microsoft SQL Server 上的增强版，它是用来让应用程序与 SQL Server 沟通的主要语言。T-SQL 提供标准 SQL 的 DDL 和 DML 功能，加上延伸的函数、系统预存程序以及程式设计结构(例如 IF 和 WHILE)让程式设计更有弹性。

-------------创建名为GetUserAccount的存储过程----------------

create Procedure GetUserAccount
as
select * from UserAccount
go
 
-------------执行上面的存储过程----------------
exec GetUserAccount

存储过程的优点：

1、安全性高，存储过程可以屏蔽对底层数据库对象的直接访问，使用EXECUTE权限调用存储过程，无需访问底层数据库对象的显式权限。
2、能够将代码封装起来；
3、保存在数据库之中；
4、让编程语言执行；
5、存储过程就是一个预编译的代码块，执行效率比较高；
6、一个存储过程可以代替大量的T-SQL，可以降低网络通信量，提高通信速率。

存储过程的缺点：
1、每一个存储过程语言几乎不一致，十分难以维护；(不通用)
2、业务代码放在数据库上，不能迭代。

8、触发器的作用

触发器是与表相关的数据库对象，在满足条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端保证数据库的完整性。但是还是慎用。

9、超键、候选键、主键和外键？

超键：在关系中能唯一标识元组的属性集称为该关系模式的超键。一个属性可以作为超键，多个属性组合在一起也可以作为超键。超键包括主键和候选键。
候选键(候选码)：是最小的超键；可以有多个，如身份证号码和学号等可以
主键(主码):数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合；一个数据列只有一个主键，一个主键可以是一个属性或多个属性，且不能为空，且主键唯一。
外键：在一个表中存在对另一个表的主键在这表中称为外键；也可说成参照列。

10、Sql中的完整性约束有

实体完整性：primary key 用于该字段唯一非空；
参照完整性：foreign key 用于预防表之间联系的动作，也能够防止非法数据插入外键列。
自定义完整性：
Not null ：用于控制某一字段的内容不能为空；
Unique：用于控制某一字段的内容不能重复，一个表中可以有多个unique约束；
Check 用于控制该字段的值范围。

11、Char 和 varchar 的区别

Char是一种固定长度的属性类型，而varhcar是一种可变长度的数据类型；
Char的存储速度还是比varchar的快，因为其长度固定，方便程度的存储；
但是char也付出了空间的代价，因为其长度固定，难免会浪费存储空间，可谓是以空间换时间。
Char的存储方式是，对英文字符(ASCII),占用一个字节，对汉字要占用两个字节；
Varchar的存储方式是，对英文字符和汉字都是占用两个字节。
两者的存储数据都是非unicode的字符数据。

12、实现中优化Mysql(重)

实践中，MySQL的优化主要涉及SQL语句及索引的优化、数据表结构的优化、系统配置的优化和硬件的优化四个方面