MySQL的几个常见面试问题

1、数据库三范式

第一范式：列不可再分

第二范式：行可以唯一区分，主键约束

第三范式：表的非主属性不能依赖于其他表的非主属性，外键约束

三大范式是一级一级依赖的，第二范式建议在第一范式上，第三范式建立在第一第二范式上

2、数据库引擎

查看mysql提供的所有存储引擎：

show engines;

常用引擎包括：MYISAM、Innod、Memory、MERGE

• MYISAM：全表锁，不支持事务，不支持外键，并发性能差，占用空间小，以select、insert为主的应用可以使用该引擎；
• Innodb：行级锁，提供了具有提交、回滚和崩溃回复能力的事务安全，支持自动增长列，支持
  外键约束，并发能力强，占用空间是MYISAM的2.5倍，处理效率相对会差一些
• Memory：全表锁，存储在内容中，速度快，但会占用和数据量成正比的内存空间且数据在
  mysql重启时会丢失，默认使用HASH索引，检索效率非常高，但不适用于精确查找，主要用于
  那些内容变化不频繁的代码表
• MERGE：是一组MYISAM表的组合

3、比较InnoDB和MyISAM

1. 事务：InnoDB支持，MyISAM不支持；
2. 外键：InnoDB支持，MyISAM不支持。对一个包含外键的InnoDB表转化成MyISAM会失败；
3. InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。
   但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该
   过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，
   索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
4. InnoDB不保存表的具体行数，查询行数时需要全表扫描，而MyISAM用一个变量保存表的行数，查询速度很快；
5. InnoDB不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；

4、事务

ACID：原子性、一致性、隔离性、持久性

5、索引是什么？

索引是帮助高效获取数据的数据结构，类比一本书的目录，能加快数据库的查询速度。

特点：索引本身也很大，不可能全部存储在内存中，因此索引往往存储在磁盘上（可能存在单独的索引文件，也可能与数据一起存储在数据文件中）

分类：聚集索引、覆盖索引、组合索引、前缀索引、唯一索引

默认都是使用B+树（多路搜索）

6、B-树和B+树

B-树

多路自平衡的搜索树，B-树也是常说的B树

m阶B-树的特点：

1. 所有键值分布在整棵树中
2. 搜索可能在非叶子节点结束
3. 每个节点最多拥有m个子树
4. 根节点至少有2个子树
5. 分支节点至少拥有m/2棵子树
6. 所有叶子节点在同一层，并且以升序排列

B-树存在的问题：

• 每个节点有key，也有data，如果data较大时导致每个节点存储的key的数量很小
• 当存储的数据量很大时会导致B-树深度太大，增加磁盘IO次数

B+树

B+是在B-的基础上的优化，变化点：

1. B+树每个节点包含更多的节点。一是降低树的高度，二是将数据范围变为多个区间
2. 非叶子节点存储key，叶子节点存储key和data
3. 叶子节点两两指针相互连接，顺序查询性能更高

MySQL的InnoDB在设计时将根节点常驻内存，并且力求树的深度不超过3，即磁盘IO次数不超过3次。

B-和B+区别

• B+非叶子节点不存储数据，因此所有数据的查询时间复杂度固定为log n，B-查询时间复杂度不固定，最好的是O(1)。
• B+树更适合外部存储。由于非叶子节点无data域，每个节点能索引的范围更大更准确。

7、SQL优化手段

1. 查询语句不要使用select *
2. 减少子查询，使用关联查询（left join right join inner join）替代
3. 减少使用in或not in，使用exists或not exists替代
4. or的查询尽量用union或union all
5. 避免在where子句里使用!=或<>操作符，否则引擎会放弃使用索引而进行全表扫描
6. 避免在where子句中进行null判断，否则引擎会放弃使用索引而进行全表扫描，如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： select id from t where num=0

8、大表如何优化？

当MySQL单表记录数过大时，CRUD的性能明显下降，常见的优化措施：

1. 限定数据的范围：

   禁止不带任何限制数据范围条件的查询语句。如查询订单时，我们可以控制在一个月范围内。

2. 读写分离

   数据库拆分，主库负责写，从库负责读。

3. 垂直分区

   根据数据表的相关性进行拆分。

   垂直拆分，是指数据表列的拆分，把一张列比较多的表拆分为多张表。

4. 水平分区：

   保持表结构不变，通过某种策略存储数据分片，达到分布式的目的。