数据库 面试题

数据库的三范式是什么？

• 第一范式：
  1）每一列属性都是不可再分的属性值，确保每一列的原子性。
  2）两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据。
• 第二范式：
  1）每一行的数据只能与其中一列相关，即一行数据只做一件事。只要数据列中出现数据重复，就要把表拆分开来。
• 第三范式：
  1）数据不能存在传递关系，即没个属性都跟主键有直接关系而不是间接关系。像：a–>b–>c 属性之间含有这样的关系，是不符合第三范式的。

一张自增表里面总共有 7 条数据，删除了最后 2 条数据，重启 MySQL 数据库，又插入了一条数据，此时 id 是几？

• 一般情况下，我们创建的表的类型是InnoDB，如果新增一条记录（不重启mysql的情况下），这条记录的id是8；但是如果重启（文中提到的）MySQL的话，这条记录的ID是6。因为InnoDB表只把自增主键的最大ID记录到内存中，所以重启数据库或者对表OPTIMIZE操作，都会使最大ID丢失。
• 如果我们使用表的类型是MylSAM，那么这条记录的ID就是8。因为MylSAM表会把自增主键的最大ID记录到数据文件里面，重启MYSQL后，自增主键的最大ID也不会丢失。
• 如果在这7条记录里面删除的是中间的几个记录（比如删除的是3,4两条记录），重启MySQL数据库后，insert一条记录后，ID都是8。因为内存或者数据库文件存储都是自增主键最大ID

如何获取当前数据库版本？

• 第一种：使用SQL select version();
• 第二种：进入mysql中bin目录使用 mysql -V
• 第三种：进入mysql中bin目录使用 mysql -? 或者 mysql --help 或者 mysql -I

说一下 ACID 是什么？

• 原子性(Atomicity)： 指的是整个事务是一个独立的单元，要么操作成功，要么操作不成功。
• 一致性(Consistency)：事务必须要保持和系统处于一致的状态（如果不一致会导致系统其它的方出现bug）。
• 隔离性(Isolation)：事务是并发控制机制，他们的交错也需要一致性，隔离隐藏，一般通过悲观或者乐观锁实现。
• 耐久性(Durability)：一个成功的事务将永久性地改变系统的状态，所以在它结束之前，所有导致状态的变化都记录在一个持久的事务日志中

float 和 double 的区别是什么？

double 和 float 的区别是double精度高，有效数字16位，float精度7位。但double消耗内存是float的两倍，double的运算速度比float慢得多。

MySQL 的内连接、左连接、右连接有什么区别？

• 左连接：左边表显示全部数据，右边的只有符合条件的才有数据；
• 右连接：右边表显示全部数据，左边的只有符合条件的才有数据；
• 内连接：是只有符合条件的才显示；

索引的分类

• 主键索引：即主索引，根据主键pk_clolum（length）建立索引，不允许重复，不允许空值；
• 唯一索引：用来建立索引的列的值必须是唯一的，允许空值；
• 普通索引：用表中的普通列构建的索引，没有任何限制；
• 组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值；

MySQL 索引是怎么实现的？

• 数据库索引依赖的底层数据结构，B+树。它通过存储在磁盘的多叉树结构，做到了时间、空间的平衡，既保证了执行效率，又节省了内存。

怎么验证 MySQL 的索引是否满足需求？

• explain 关键字，加载select 前面，possible_keys会显示索引名。

说一下数据库的事务隔离？

数据库隔离级别有四种:

• 未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据。
• 提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)。
• 可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读。
• 串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞。

说一下 MySQL 常用的引擎？

Innodb和MyIASM。

• Innodb引擎，Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。它本身实际上是基于Mysql后台的完整的系统。Mysql运行的时候，Innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是，该引擎是不支持全文搜索的。同时，启动也比较的慢，它是不会保存表的行数的。当进行Select count(*) from table指令的时候，需要进行扫描全表。所以当需要使用数据库的事务时，该引擎就是首选。由于锁的粒度小，写操作是不会锁定全表的。所以在并发度较高的场景下使用会提升效率的。
• MyIASM引擎，它是MySql的默认引擎，但不提供事务的支持，也不支持行级锁和外键。因此当执行Insert插入和Update更新语句时，即执行写操作的时候需要锁定这个表。所以会导致效率会降低。不过和Innodb不同的是，MyIASM引擎是保存了表的行数，于是当进行Select count(*) from table语句时，可以直接的读取已经保存的值而不需要进行扫描全表。所以，如果表的读操作远远多于写操作时，并且不需要事务的支持的。可以将MyIASM作为数据库引擎的首先。 补充2点： c.大容量的数据集时趋向于选择Innodb。因为它支持事务处理和故障的恢复。Innodb可以利用数据日志来进行数据的恢复。主键的查询在Innodb也是比较快的。 d.大批量的插入语句时（这里是INSERT语句）在MyIASM引擎中执行的比较的快，但是UPDATE语句在Innodb下执行的会比较的快，尤其是在并发量大的时候。

说一下 MySQL 的行锁和表锁？

• 表级锁：每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
• 行级锁：每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高.   一般来说，如果where条件中只用到索引项，则加的是行锁；否则加的是表锁。比如说主键索引，唯一索引和聚簇索引等。如果sql的where是全表扫描的，想加行锁也爱莫能助。   共享锁（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE   排他锁（X）：SELECT * FROM table_name WHERE … FOR UPDATE   mysql 还提供了LOCK TABLES，UNLOCK TABLES，用于加表锁。

说一下乐观锁和悲观锁？

• 乐观锁（Optimistic Concurrency Control，OCC）：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。
• 悲观锁（Pessimistic Concurrency Control，PCC）：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。

如何做 SQL 的优化？

• 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
• 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。可以在num上设置默认值0，确保表中num列没有null值
• 应尽量避免在 where 子句中使用!=或<>操作符、or 来连接条件、对字段进行表达式操作、对字段进行函数操作，否则将导致引擎放弃使用索引而进行全表扫描。
• in 和 not in 也要慎用，否则会导致全表扫描，对于连续的数值，能用 between 就不要用 in 了。
• 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
• 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引， 否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
• 任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
• 并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引。
• 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。