（转）数据库重要知识点

**
原创：https://blog.51cto.com/aiilive/2309361

基础概念

**
1. 什么是数据库事务？事务有什么特点？
数据库事务：是指作为单个逻辑工作单元执行的一系列操作，这些操作要么全做，要么全不做，是一个不可分割的工作单元。

事务特点：

• 原子性
• 一致性
• 隔离性
• 持久性特

2. 主键和外键的区别？
主键所在的列的值是唯一的，不可为空的
外键所在的列的值可以重复，可以为空的，不能创建对应表中不存在的外键值

3. truncate与delete的区别？
比较 truncate delete
相同点 删除表中的全部行 不带where字句删除表中的全部行
相同点 删除记录，不删除表定义 删除记录，不删除表定义
不同点 truncate比delete速度快，且使用系统和事务日志资源少 delete每删除一行，并在事务日志中为所删除的每行记录一项
不同点 truncate正常情况返回0，中断什么都不返回 delete返回删除的行记录数

4. 索引的作用和优缺点？
作用：

索引可以大大提高系统检索的性能
优点：

1. 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
2. 可以大大加快 数据的检索速度，这也是创建索引的最主要的原因
3. 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义
4. 在使用分组和排序 子句进行数据检索时，同样可以显著减少查询中分组和排序的时间
5. 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能
   缺点：

1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大
当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度
5. 创建索引都有哪些注意的事项
适合创建索引：

1. 在经常需要搜索的列上，可以加快搜索的速度。
2. 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构。
3. 在经常用在连接的列上，这 些列主要是一些外键，可以加快连接的速度。
4. 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的。
5. 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
6. 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
   不适合创建索引：

1.对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

2.对于那些只有很少数据值（唯一性差）的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

3.对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。

4.当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。
6. SQL语句分为哪几类？
DDL(Data Definition Language)：数据定义语言，定义对数据库对象(库、表、列、索引)的操作。代表指令：CREATE、DROP、ALTER、RENAME、 TRUNCATE等

DML(Data Manipulation Language)：数据操作语言，定义对数据库记录的操作。代表指令：INSERT、DELETE、UPDATE、SELECT等

DCL(Data Control Language)：数据控制语言，定义对数据库、表、字段、用户的访问权限和安全级别。代表指令：GRANT、REVOKE，COMMIT、ROLLBACK、SAVEPOINT等
7. MySQL存储引擎有哪些，各自有什么特点？

1. 存储引擎是：数据库管理系统如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。MySQL的核心就是插件式存储引擎，支持多种存储引擎。

主要回答下表中MyISAM,InnoDB,Memory三个引擎即可，区别就是各自的Feature不一样，标✔的标识该引擎支持对应的特性。
校招数据库笔试面试题汇总详解

8.MySQL都支持那些索引？
支持的索引如下：

主键索引(primary key)
唯一索引(unique)
普通索引(index)
全文索引(fulltext) （InnoDB引擎不支持全文索引，MyISAM引擎支持全文索引）
9. 什么是存储过程？
存储过程是一个预编译的SQL 语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL ，使用存储过程比单纯SQL 语句执行要快。可以用一个命令对象（call）来调用存储过程。

10. 视图的优缺点？
优点：

对数据库的访问，因为视图可以有选择性的选取数据库里的一部分。
用户通过简单的查询可以从复杂查询中得到结果。
维护数据的独立性，试图可从多个表检索数据。
对于相同的数据可产生不同的视图。
视图可以提高安全性，必须具有足够的访问权限
缺点：

性能影响，查询视图时，必须把视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么就无法更改数据
11. 列举几种表连接方式以及区别？
连接方式：

内连接
自连接
外连接（左、右、全）
交叉连接
连接方式说明：

内连接：只有两个元素表相匹配的才能在结果集中显示。
外连接：
左外连接：左边为驱动表，驱动表的数据全部显示，匹配表的不匹配的不会显示。
右外连接：右边为驱动表，驱动表的数据全部显示，匹配表的不匹配的不会显示。
全外连接：连接的表中不匹配的数据全部会显示出来。
交叉连接： 笛卡尔效应，显示的结果是链接表数的乘积。
12. 数据库的三范式
范式：

第一范式：当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。
第二范式：如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。
第三范式：设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF. 数据不能存在传递关系，即每个属性都跟主键有直接关系而不是间接关系。
参考资料：

数据库设计三大范式
实际使用：三大范式只是一般设计数据库的基本理念，可以建立冗余较小、结构合理的数据库。如果有特殊情况，当然要特殊对待，数据库设计最重要的是看需求和性能，需求>性能>表结构。所以不能一味的去追求范式建立数据库

13.SQL的关键字执行顺序
SQL查询中各个关键字的执行先后顺序：

from > on> join > where > group by > with > having > select > distinct > order by > limit
参考资料：《MySQL技术内幕：SQL编程 第三章查询处理》

场景分析

1. 如果有1000万条数据，执行库查询时如何提高查询效率？
   问题分析：从1000万这个数字可以定位到考察数据量特别大的情况下如何提升查询效率。

数据库设计方面

建立索引
数据表分区
尽量使用固定长度的字段
限制字段长度
分表，分库
数据库I/O方面

增加缓冲器
涉及表的级联，不同的表存储在不同的磁盘上，以增加I/O速度
改善物理设备，比如更换固态硬盘
在SQL语句方面

优化SQL语句，较少比较次数
限制返回的记录数，进行分页查询
在Java方面
反复查询，使用preparedStatement减少查询次数
考虑数据缓存，较少数据库访问