面试官都说这一篇数据库够了

数据库复习，新手绕道

存储引擎

MyISAM

该引擎不支持外键，不支持事务，不支持行锁，只支持表锁，不适合高并发的数据库场景，但是它执行读取操作的速度很快，而且不占用大量的内存和存储资源，所以更多的关注性能。

InnoDB

InnoDB 底层存储结构为B+树，该引擎支持主外键，支持事务操作，使用的是行锁适用于高并发场景，它还会缓存真实数据，对内存性能要求高，表空间较大，关注的是事务。

索引

索引是一种帮助mysql高效获取数据的数据结构。

索引的设计的原则

1. 选择唯一性索引：可以快速的定位到某条记录
2. 为经常需要排序、分组和联合操作的字段建立索引
3. 为常作为查询条件的字段建立索引
4. 限制索引的数目：索引过多对于数据的更新不利
5. 尽量使用数据量少的索引
6. 尽量使用前缀来索引
7. 删除不再使用的索引或者很少使用的索引
8. 符合最左匹配原则设计索引
9. 选择分别度高的字段作为索引
10. 索引列不能参与计算，带函数的查询不参与索引
11. 尽量扩展索引不要新建索引

索引的好处

• 通过创建索引可以提高数据查找的性能
• 通过创建唯一性索引，可以保证数据库表中一行数据的唯一性
• 在使用分组和排序的字句进行数据检索时，可以减少查询的时间

索引的缺点

• 创建爱你索引和维护索引都要消耗一部分时间，而且数据量越大时间消耗越大
• 索引需要占据一定的物理空间，如果建立聚簇索引，空间消耗更大
• 对表中的数据进行更新的时候，索引也会随之变动，会花费一定的资源去维护他

索引的种类

聚集索引：

索引中键值的逻辑顺序决定了表中相应行的物理顺序（索引中的数据物理存放地址和索引的顺序是一致的）

MySQL中主键索引就是属于聚集索引。

非聚集索引

非聚集索引的键值在逻辑上也是连续的，但是表中的数据在存储介质上的物理顺序是不一致的，即记录的逻辑顺序和实际存储的物理顺序没有任何联系。索引的记录节点有一个数据指针指向真正的数据存储位置。

MySQL中其他字段的索引就是非聚集的索引，如果给表中多个字段加上索引 ， 那么就会出现多个独立的索引结构，每个索引互相之间不存在关联。

非聚集索引和聚集索引的区别在于， 通过聚集索引可以查到需要查找的数据， 而通过非聚集索引可以查到记录对应的主键值 ， 再使用主键的值通过聚集索引查找到需要的数据

覆盖索引

如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。

非聚集索引只能查询到主键值，然后再通过主键值查询到数据，然而覆盖索引是一种不需要聚集索引的索引方式。覆盖索引直接就能获取到想要的数据。

什么时候不会选择覆盖查询

• select选择的字段中含有不在索引中的字段 ，即索引没有覆盖全部的列。

• where条件中不能含有对索引进行like的操作。

主键中的索引

• 如果一个主键被定义了，那么这个主键就是作为聚集索引
• 如果没有主键被定义，那么该表的第一个唯一非空索引被作为聚集索引
• 如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6个字节的列，改列的值会随着数据的插入自增。

什么时候索引会失效？

• 不遵循最左前缀法则
• 使用范围查询的时候，范围查询条件的右侧列的索引会失效
• 使用运算函数会导致索引失效
• 字符串不加单引号会导致索引失效
• or前面的条件有索引，后面的条件没有索引，那么就会导致整体索引试下
• 模糊查询前置%会导致索引失效
• in查询索引有效，not in查询索引无效
• 全表扫描比索引更快的时候会使用全表扫描
• 存储过程，存储函数，触发器中执行的查询
• 执行不确定的函数，如now（）；

如何避免索引失效

1. 查询的时候将所有的索引字段都带上
2. 遵循最左前缀法则进行查询

数据库的三范式

1. 第一范式（列不可再分）

第一范式的目标是确保每列的原子性：如果每列都是不可再分的最小数据，那么就满足第一范式；

例如：对一个人地址信息的统计，不能将所在地址设置用一个字段来表示（xxx省xx市xxx），而是应该用多个段来表示xxx省字段，xxx市字段,…；

2. 第二范式（每个表只表述一件事）

在满足第一范式的前提下，表中的非主键不存在对主键的部分依赖；第二范式要求每个表只描述一件事情，所有属性完全依赖于主键，不会出现数据冗余。

例如：在统计用户订单消息的设计中，我们不能将用户信息和订单信息放在一个表，而是将其分开，并用用户表的主键作为外键来获取用户信息，保证一个表描述的是订单信息这一件事。

3. 第三范式（不存在对非主键列的传递依赖）

满足第二范式，属性不依赖于其它非主属性 属性直接依赖于主键。

例如：简单的学生信息表，我们统计学生信息，学生的导员，导员的电话；

由此知道，学生的导员关联到学生，而导员的电话关联到导员，存在了导员电话依赖导员，间接依赖学生主键。所以应当分开学生相关信息和导员相关信息，可以用外键关联。

存储过程

一组为了完成特定功能的SQL语句集合，存储在数据库中，只经过一次编译即可。

优点

1）存储过程是预编译过的，执行效率高。

2）存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。

3）安全性高，执行存储过程需要有一定权限的用户。

4）存储过程可以重复使用，可减少数据库开发人员的工作量。

缺点：可移植性差。

存储过程的优化：

1. 利用SQL语句代替小循环
2. 中间结果存放于临时表，并且加索引
3. 少使用游标，游标是一种过程运算，不利于数据查询
4. 事务越短越好
5. 使用try catch处理异常
6. 查询语句尽量不要放在循环内

触发器

当对数据库中的表执行增删改的时候会自动触发执行的一段程序，是一种特殊的存储过程；

作用

可以用来强化约束、维护数据的完整性和一致性，可以跟踪数据库中的更新操作，不允许非法的更新和变化。还可以做级联运算，通过一个表操作另一个表。

事务

事务的ACID属性：

1. 原子性：事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚。

2. 一致性：数据库在事务执行前后都保持一致性状态。在一致性状态下，所有事务对同一个数据的读取结果都是相同的。

3. 隔离性 ：一个事务所做的修改在最终提交以前，对其它事务是不可见的。

4. 永久性：一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。

数据库默认是开始自动提交策略的，如果不显示的手动开启一个事务，那么每个操作都会被当成一个事务

并发一致性问题

1. 脏读：不同的事务下，一个事务修改一个数据但是未提交，另一个事务随后读取到了这个未提交的数据，这个数据就叫做脏数据。

2. 不可重复读：一个事务多次读取一个数据，但是当第一次读取后另一个事务对这个数据进行了改变，这时候多次读取的结果不一样，就出现了不可重复读的问题

3. 幻读：幻读本质上也属于不可重复读的情况，一个事务读取某个范围的数据，另一个事务在这个范围内插入新的数据，再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。

数据库锁

行级锁

行级锁是一种排他锁，同一时间段只允许一个事务进行操作，当进行update 、delete、insert 、select for update的时候会触发触发行锁，只有当事务提交或者回滚后会释放锁。

注意：select for update 支持多条记录进行更新。

表级锁

就是在表中的数据被更新的时候就会对整张表进行加锁。消耗的资源少，被大部分MySQL引擎支持；表级锁分为表共享锁（读锁）和表独占锁（写锁）

读写锁

• 互斥锁：简写为 X 锁，又称写锁。
• 共享锁：简写为 S 锁，又称读锁。

有以下两个规定：

• 一个事务对数据对象 A 加了 X 锁，就可以对 A 进行读取和更新。加锁期间其它事务不能对 A 加任何锁。
• 一个事务对数据对象 A 加了 S 锁，可以对 A 进行读取操作，但是不能进行更新操作。加锁期间其它事务能对 A 加 S 锁，但是不能加 X 锁。

隔离级别

1. 读未提交：

   1. 事务中的修改，即使没有提交，对其它事务也是可见的。
   2. 不能解决事务一致性问题中的任何问题。

2. 读已提交：

   1. 一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。

   2. 可以解决脏读问题

3. 可重复读：

   1. 保证在同一个事务中多次读取同一数据的结果是一样的。
   2. 可以解决不可重复读问题和脏读问题。

4. 串行化：

   1. 强制事务串行执行，这样多个事务互不干扰，不会出现并发一致性问题（这个隔离级别是加锁实现的，保证同一时间只有一个事务执行，进而实现串行）。
   2. 可以解决脏读、幻读、不可重复读。

优化SQL

• 尽量手动提交事务
• 尽量在插入数据的时候主键有序插入
• 排序查询的时候可以使用覆盖索引优化
• 多个字段排序的时候尽量保持升降一致
• 排序的时候尽量让排序顺序和索引顺序一致
• 对于分组查询，我们尽可能让他不排序
• 尽量使用连接查询代替子查询
• 使用or条件查询的时候尽量让其条件都是索引字段或者使用联合查询来代替or查询

面试题

数据库删除的方式有几种？区别是什么？

• 使用delete语句
• 使用truncate语句
• 使用drop语句

区别：

1. 前两种删除的是数据库中的数据，drop删除的是整个数据库；也就是delete和truncate删除的是数据，drop删除的是结构。
2. 数据递增的情况下， delete删除后递增会从端点处开始，truncate删除后会从第一行开始。
3. delete删除后有返回值，truncate删除后没有返回值。
4. truncate删除后不能回滚，delete删除后可以回滚。

varchar和char的使用场景?

• char的长度是不可变的，如果值得长度不够会用空格补充，而varchar是变长的。
• char存取速度比varchar快，因为定长查询更快。但是varchar因为 长度固定，所以会付出更多的空间代价。
• char对英文字符占1个字节，中文占2个字节。
• varchar中英文都是占2个字节。

count（*）、count（1）、count（column）的区别

• count（1）的结果和count（*）一样；不同的是前者扫描的是主键，后者扫描整个表。

• count（column）：对特定列的值计数

场景：

• 如果表没有主键,那么count(1)比count(*)快。
• 如果有主键,那么count(主键,联合主键)比count(*)快。
• 如果表只有一个字段,count(*)最快。

为什么需要一个主键？

• InnoDB 的数据是按照主键顺序存放的，而聚集索引就是按照每张表的主键构造一颗 B+ 树，也就是索引；而主键就默认是聚集索引；

• 如果没有定义主键，聚集索引可能是第一个不允许为 null 的唯一索引

• 如果也没有这样的唯一索引，InnoDB 会选择内置 6 字节长的 ROWID 作为隐含的聚集索引。

为什么要用自增ID作为主键？

• 自增主键在每次插入新的记录的时候，记录会顺序的添加到当前索引节点的后续位置，当一页写满之后就会开辟新的一页。
• 如果是非递增的主键，那么每次插入主键的值近似随机，那么该索引就会被插入到当前所以页中间的某个位置，此时整个索引表就会出现较大的变动，从而增加额外的开销。

游标？

对查询的结果集作为一个单元来进行有效的处理，可以定义在结果集的任何一行，从这一行开始检索一行或者多行进行修改。一般情况下不会使用游标，只有在需要逐条处理数据的情况下回使用游标。