mysql面试题

1、drop，truncate和delete的区别

drop直接删除表，truncate删除表中数据，再加入时自增id从1开始，delete删除表中数据，可以加where语句

• delete语句执行过程是每次删除一行，同时将该删除操作作为事务记录在日志中，可以回滚。truncate table是一次性删除白哦中所有数据，删除过程中不把单独的删除操作记录到日志中，删除行是不能恢复的，并且在删除过程中不会触发delete的触发器，删除速度快
• 当表被truncate后，表的索引所占用空间恢复到初始大小，而delete操作不会减少表和索引所占用的空间，drop会完全释放表和索引所占用的空间
• 一般而言，drop>truncate>delete
• truncate只能对table，delete可以是表和视图
• truncate和delete只删除数据，而delete删除整个表（结构和数据）及和该表相关联的约束，触发器，索引；依赖于该表的存储过程和函数会被保留，但其状态会变成invalid
• delete是dml，操作会被放到事务里，可以回滚，事务提交后生效。如果有相应的trigger，执行时会被触发。drop和truncate时ddl，操作立即生效。不能回滚

2、索引的工作原理和种类

在数据之外，数据库还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。通常是B树及其变种B+树

种类：普通索引、主键索引、唯一索引、组合索引、全文索引

3、数据库连接

外连接：left join、right join、full join

内连接：inner join

交叉连接：cross join（笛卡尔积）

4、数据库范式

• 第一范式（1NF）：关系型数据库的最低要求，在第一范式中一行即为一个实例
• 第二范式（2NF）：第二范式是在第一范式的基础上建立的，即满足第二范式则必然满足第一范式。第二范式要求每一个实例或行能够被唯一区分，为实现区分需要将该实体的某个属性或者新增一个属性作为唯一区分，这个唯一属性称作主键。第二范式要求实体的属性完全依赖于主关键字。
• 第三范式（3NF）：第三范式要求在一个表中不能包含其他表中非关键字的信息
• 巴斯-科德范式（BCNF）：在第三范式的基础上，任何非主属性不能对主属性子集依赖
• 第四范式（4NF）：消除非平凡且非函数依赖的多值依赖
• 第五范式（5NF）：消除不是由候选码所蕴含的连接依赖

5、数据库优化

• sql语句优化
  • 用变量代替函数，会使用数据库缓存，从而加快查询效率
  • 当只要一条数据时，使用limit 1
  • 不要用order by rand()
• 索引优化
• 数据库结构优化：范式、反范式、拆分表（水平和垂直拆分）
• 服务器硬件优化

6、MySQL 中 InnoDB 支持的四种事务隔离级别名称，以及逐级之间的区
别？

• Read Uncommitted（读取未提交内容） >> 在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）
• Read Committed（读取提交内容） >> 这是大多数数据库系统的默认隔离级别（但不是 MySQL 默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的 commit，所以同一 select 可能返回不同结果。
• Repeatable Read（可重读） >> 这是 MySQL 的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读（PhantomRead）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。 InnoDB 和 Falcon 存储引擎通过多版本并发控制（MVCC， Multiversion Concurrency Control 间隙锁）机制解决了该问题。注：其实多版本只是解决不可重复读问题，而加上间隙锁（也就是它这里所谓的并发控制）才解决了幻读问题。
•  Serializable（可串行化） >> 这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。