MySQL篇面试题
一、介绍
这是由小龙同学自己总结领悟的mysql面试题的解析，也是面试宝典
二、题目
1.数据库三大范式：
–作用：
使表结构清晰，减少数据冗余（简单讲就是重复），提高查询和存储效率
–三大范式：
①第一范式：保证每个列的原子性既不可拆分，举例说明：比如说，我最近项目中的保存客户信息列中，不能存在客户信息列，因为它可以拆分为客户姓名列，客户电话列
②第二范式：保证每个列直接依赖于主键，举例说明：比如说，我最近项目中的商品订单表，里面开始设计为：商品编号，商品名称，商品价格，店铺名称，店主名称
这是有问题的，店铺名称，和店主名称，都不依赖商品id
且：数据容易冗余–假如店铺A有可乐商品，店铺B也有，那么可乐商品要重复
如果雪碧商品都属于两个店铺有的，那么店铺A、B都又一次重复
数据更新异常：店铺A名称改成，店铺C，那么行内所有店铺A都需要更新
等等问题
修改需要拆分为店铺信息表：店铺名称，店主名称
然后商品订单表设计就是：商品编号，商品名称，商品价格
③第三范式：非主键字段之间不能出现传递依赖的关系
拿这张表来说：商品ID，商品名称，所在店铺，店铺名称，店主名称
店铺名称，和店主名称都不直接依赖商品ID，而是直接依赖所在店铺，那么就可以靠店铺名称和店主名称来传递数据，所以就存在传递依赖关系
可以拆分为：
商品订单表：商品ID，商品名称，所在店铺
店铺信息表：店铺ID，店铺名称，店主名称
这样就符合三大范式了
2.MySQL数据库引擎有哪些
–MYISAM：全表锁
①优势：执行速度快，占用空间小
②缺点：不支持事务，不支持外键，并发性能差
③业务：多适用于主要依赖Select、Insert的业务中
–InnoDB：行级锁
①优势：支持事务，具有提交回滚功能，支持外键，自动增长列，并发能力强
②缺点：执行速度相对MYISAM较差一点，占用空间是MYISAM的2.5倍
③业务：相对更适合业务复杂的场景，比如银行转账，崩溃时能够回滚
–Memory：全表锁
①优势：存储在内存中，执行速度快，默认使用HASH索引检索数据，检索效率高
②缺点：因为存在在内存中数据安全性低，容易丢失，不适用范围查询
③业务：主要适用于那些内容变化不繁琐的代码表
–MERGE：全表锁
是一组MYISAM表的组合
3.聚集索引和非聚集索引
–聚集索引：
拿没有目录的字典来说，我们只有abc字母来查询，当我们要查询 ‘’安’‘ 字时，得先根据读音 an 的a先开始找，我们要翻到a的部分，然后在根据a区里面的n区查找（遵循了最左前缀的匹配原则：即不是从最左开始查询，就无法找到数据） ，找到后我们能看到 ’‘安‘’ 字的所有信息。
即：
--聚集索引的索引是有顺序的
--聚集索引的索引和数据是在一起的
--所以找到文件后不需要再根据索引去回表查询
–非聚集索引：
继续拿字典来说，不过现在的字典是有目录的，当我们要查询 ‘’张‘’ 字时 我们可以根据他的部首 弓 在目录里面找到这个字，然后我们就能得知这个字的具体内容在哪个页码我们就能去查找到具体内容了
即：
--非聚集索引是没有顺序的
--非聚集索引和数据是分离的
--所以找到文件后需要进行回表查询
4.InnoDB与MYISAM的区别
--InnoDB支持事务，MYISAM不支持，而对于InnoDB的每条SQL都作为一个事务自动提交，这样很影响效率，所以最好就是将多条SQL语句放在begin和commit之间，组成一个事务一起提交
--InnoDB支持外键，MYISAM不支持，所以一个包含外键关系的InnoDB表无法转换为MYISAM引擎的表
--InnoDB是聚集索引，数据文件和索引绑定在一起，必须有主键，通过主键查找数据效率很高。不过辅助索引（聚集索引=辅助索引，二级索引）是通过查询到主键，再通过主键查询到数据，需要二次查询，所以主键不应过大。MYISAM非聚集索引，数据文件是分离的，主键索引和辅助索引都是分离的
--InnoDB不保存具体行数，执行查询行数的SQL时，会进行全表扫描，而MYISAM保存了表的行数，查询总行数的时候，只需要读取该变量出来即可，速度很快
5.hash索引
–适用于等值查询：
hash索引在范围查询时，因为hash函数的转换的哈希码作为键，然后数据作为值，的键值存入一个确定的存储桶中。这样我们可以根据hash码快速定位到桶的位置
–检索效率高：
通过哈希函数将关键字转换成哈希码，可以直接定位到数据的存储位置，避免了逐条比较的过程，因此查找速度非常快。
–不适合范围查询：
这是因为相邻的数据可能会被存入不相邻位置的桶中，这样数据的位置是无序的，范围查询时想要找到多个数据就得重复进行多次全表扫
6.MySQL索引分类
–按数据结构分类
| 结构/引擎 | InnoDB | MYISAM | Memory |
|---|---|---|---|
| B+Tree索引 | √ | √ | √ |
| Hash索引 | × | × | √ |
| Full-text索引 | √（MySQL5.6.4后） | √ | × |
–按物理存储分类
分为：聚集索引，非聚集索引（也叫辅助索引，二级索引）
–按字段特性分类
分为：主键索引，唯一索引，全文索引，普通索引
7.数据库的事务
–什么是事务？
多条SQL一起执行，要么同时成功，要么同时失败
–四种特性：
原子性：组成一个事务的多个数据库操作，就是不可拆分的原子单元，只有所有操作成功，才会提交事务。任意一个操作失败，该事务都需要回滚，回到初始状态。
一致性：数据的总量，与他的业务规则保持一致，即数据不会被破坏。比如转账操作，A转账给B，无论操作是否成功，A和B的账户总额不变
隔离性：在多个事务并发时，里面的操作互不干扰。
持久性：事务一旦提交成功，就会将事务所有操作，持久化到数据库中
8.Where 和 Having 的区别？
–Where
where中不能写聚合函数，在分组之前筛选出符合条件的行数据
–Having
having就是在分组好的数据中进行筛选出符合条件的组，也可以使用多个分组标准分组
–执行顺序：
on->join->where->group by->having
9.一个SQL慢，怎么优化？
--限制行数
有些查询数据量极大，如果查全部的话，很久都不出来，我们可以查询部分数据来验证SQL是否正确
--通过查看SQL的执行计划
oracle的查看执行计划命令
EXPLAIN PLAN FOR SELECT count(1) FROM AA_TEST where rownum <= 10; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
--检查SQL语法
select * 操作改成具体的列，减少性能开销
减少使用子查询，减少使用！= 和 <>操作符，否则就将放弃索引进行全表扫描
避免对null值进行判断
10.MVCC详细理解
--当前读需要加锁实现。快照读就是基于MVCC实现，不加锁的SQL操作，不过可能读到的是之前版本的历史数据，mvcc目的就是不去竞争锁，从而减少系统性能开销
--四种特性：
原子性：利用undo log实现
持久性：利用redo log实现
隔离性：通过加锁以及MVCC共同实现
串行化：
--MVCC实现原理：
通过undo log、版本链、Read View实现
Read View能让你知道你要在这些版本中去选择哪一个版本
--Read View
①ReadView有的东西
| m_ids | 表示生成‘Read View’时当前系统中活跃的读写事务的‘事务id’列表 | 未提交的事务【90 100 200】 |
|---|---|---|
| min_trx_id | 表示生成‘Read View’时当前系统中活跃的读写事务中最小的‘事务id’，也就是‘m_ids’中的最小值 | 也就版本链尾的事务【90】 |
| max_trx_id | 表示系统中应该分配给下一个事务的‘id’值 | 【201】 |
| creator_trx_id | 表示生成该‘ReadView’的事务的‘事务id’ | 最新的事务创建ReadView所以id为【201】 |
②ReadView任何判断版本呢链中哪个版本可用？
1-当前事务id == 生产该事务的事务id（trx_id==creator_trx_id）其实就是自己事务，肯定可以看的
2-当前事务id < undo log中最小活跃事务id（trx_id 3-当前事务id > 表示下一个undo log记录的数据的事务id（trx_id > max_trx_id）: 不可以访问这个版本 4-（min_trx_id <= trx_id <= max_trx_id） : 如果trx_id在m_ids中是不可以访问这个版本的，反之可以 --按照顺序走判断，只要有一个判断成功就进行返回这个数据，可以实现查询到的是已经提交的数据，解决了脏读 --同时呢，只要查询一次就产生一个ReadView就能解决不可重复读 当一个事务正在修改数据，而这个数据还未提交时，另一个事务读取了数据，这些数据就是脏数据，所进行的操作可能不正确 A修改了T数据，B也来修改了T数据，并且覆盖了A的操作。所以A的修改丢失了 B需要多次读取T数据，然后这时A修改了T数据，导致B再次读取的T数据，是不一致的情况。 B需要多次读取T数据，然后这时A新增了T数据，导致B再次读取的T数据，发现凭空出现了内容，就像发现了幻觉 这是最低的隔离级别。在该级别下，一个事务可以读取另一个事务尚未提交的修改。这可能会导致脏读（读取到未提交的数据）和不可重复读（同一查询在不同时间点得到不同结果）问题。 大多数数据库的默认隔离级别。在该级别下，一个事务只能读取已经提交的数据。这可以避免脏读，但依然可能会出现不可重复读和幻读（同一范围内查询得到不同的行数）问题。 在可重复读级别下，事务在读取数据时会创建一个快照，确保后续读取的数据与快照一致，不受其他事务的影响。这可以避免不可重复读，但仍存在幻读问题。 这是最高的隔离级别。在该级别下，事务是按顺序执行的，因此可以避免所有并发问题。然而，串行化级别可能会降低数据库的并发性能。 在MySQL中，默认的隔离级别是“可重复读”（Repeatable Read）。这意味着在MySQL中启动的每个事务可以看到之前事务的结果，而不受其他事务的干扰，并且可以避免不可重复读的问题。 总结来说，DROP用于删除整张表，包括结构和数据；DELETE用于删除表中的数据记录，保留表结构；TRUNCATE用于删除表中的数据记录，保留表结构，效率高。在选择使用时，需根据具体情况和需求来决定使用哪种操作。11.事务并发带来的问题
–脏读：
–丢失修改：
–不可重复读：
–幻读：
12.事务的隔离级别有哪些？
–读未提交（Read Uncommitted）：
–读已提交（Read Committed）：
–可重复读（Repeatable Read）：
–串行化（Serializable）：
–MySQL的默认级别
13.drop、delete、truncate的区别
–drop：
–delete:
–truncate：