Mysql 面试题整理(上)

Mysql 面试题整理(上)

1. 常见的关系型数据库 和 非关系型数据库有哪些?
   关系型数据库 Mysql , Oracle ,SQL Server
   非关系型数据库 Redis

2. 它们之间的区别是什么?
   关系型数据库支持SQL语句的查询,增删改查非常方便,并且支持多表的join操作.
   非关系型数据库查询速度比关系型数据库慢,因为非关系型数据存在内存中,关系型的数据库的数据是存在磁盘中的.

3. Mysql中的关键字 where 和 having 的主要区别是什么?
   二者相同点 : 都可以用于数据的筛选 ;
   区别 : where 是数据分组前的筛选,所筛选的对象是原始表中的数据；而having是对分组后的数据进行筛选，所筛选的表对象是分组后的表数据。
   4.MySQL中字符串类型**
   在MySQL中是没有字符类型的，全部都是字符串类型。主要有char（定长字符串，默认长度是1），char（M）长度为M的字符串，varchar（M）变长字符串以及text（长文本）。char的好处是：不用计算字符数，固定几个几个一读，虽然会浪费一些空间，但是速度快，适用于那些频繁；而varchar可以节省空间，但每次读写都要计算字符数。

4. MySQL中delete from和truncate的主要区别是什么？
   参考答案：（1）truncate只能用于清空表，不能只删除部分数据，而delete可以加条件，只删除部分数据（2）truncate清空表的速度很快，而delete清空表很慢，原因在于truncate是把整个表drop掉，然后新建一张新表，表结构和原来一样。 delete是逐条删除记录。（3）truncate语句是无法回滚的，delete可以（当然前提是事务没提交时可以回滚）

5. MySQL中主键约束的主要特点是什么？
   参考答案：每张表都应该有主键约束，并且一个表只能有一个主键约束；通过主键约束，来唯一确定一行，主要特点为值是非空并且唯一的。

6. 谈谈你对MySQL中事务特性的认识？
   参考答案：①原子性：不可分割的操作单元，事务中所有的操作，要么全部成功；要么都要撤回到执行事务之前的状态②一致性：如果在执行事务之前数据库是一致的，那么在执行事务之后数据库也还是一致的③隔离性：事务操作之间彼此独立和透明不受影响，事务独立运行。这通常使用锁来实现。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。因此事务的100%隔离，需要牺牲速度④持久性：事务一旦提交，其结果就是永久的。即便发生系统故障，也能恢复。

7. MySQL中InnoDB引擎使用什么来作为索引结构？
   参考答案：InnoDB也是使用B+Tree作为索引结构的，InnoDB的数据文件本身就是索引文件，叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这种索引叫做“聚焦索引”。InnoDB的辅助索引的data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。InnoDB的索引实现后，不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。在Innodb中也不建议使用非单调的字段作为主键，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，建议使用自增字段作为主键。

8. 存储引擎 InnoDB 和 MyISAM 的主要区别？
   ①InnoDB支持事务，MyISAM不支持；
   ②InnoDB适合频繁修改以及涉及到安全性较高的应用,MyISAM适合查询以及插入为主的应用;
   ③InnoDB是支持外键 , MyISAM不支持外键
   ④InnoDB不支持全文索引 , MyISAM支持全文类型索引
   ⑤InnoDB不保存表的总行数，select count(*) from table时，InnoDB需要扫描整个表计算有多少行，但MyISAM只需简单读出保存好的总行数即可。
   小结：现在一般都选用InnoDB，主要是MyISAM的全表锁，读写串行问题，并发效率锁表，效率低，因此MyISAM对于读写密集型应用一般是不会去选用的。
   10.Mysql 索引的调优
   创建索引的时候,首先可以考虑覆盖索引,覆盖 索引可以减少回表 的次数 , mysql 5.6 对覆盖 索引做了进一步的优化, 支持 索引 下推的功能 ,把覆盖索引所 覆盖 的 一个字段,进一步的进行筛选 ,尽量减少回表的次数.

9. MySQL遇到过死锁问题吗，你是如何解决的？
   我排查死锁的一般步骤是酱紫的：
   ①查看死锁日志show engine innodb status;
   ②找出死锁Sql
   ③分析sql加锁情况
   ④模拟死锁案发
   ⑤分析死锁日志
   ⑥分析死锁结果
   12 日常工作中你是怎么优化SQL的？
   可以从这几个维度回答这个问题：
   ①加索引
   ②避免返回不必要的数据
   ③适当分批量进行
   ④优化sql结构
   ⑤分库分表
   ⑥读写分离

10. 数据库索引的原理，为什么要用B+树，为什么不用二叉树？
    为什么不是一般二叉树？

如果二叉树特殊化为一个链表，相当于全表扫描。平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。

为什么不是平衡二叉树呢？

我们知道，在内存比在磁盘的数据，查询效率快得多。如果树这种数据结构作为索引，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块，但是平衡二叉树可是每个节点只存储一个键值和数据的，如果是B树，可以存储更多的节点数据，树的高度也会降低，因此读取磁盘的次数就降下来啦，查询效率就快啦。

那为什么不是B树而是B+树呢？

1）B+树非叶子节点上是不存储数据的，仅存储键值，而B树节点中不仅存储键值，也会存储数据。innodb中页的默认大小是16KB，如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的IO次数有会再次减少，数据查询的效率也会更快。

2）B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的，链表连着的。那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。
14. 什么是幻读，脏读，不可重复读呢？
事务A、B交替执行，事务A被事务B干扰到了，因为事务A读取到事务B未提交的数据,这就是脏读

在一个事务范围内，两个相同的查询，读取同一条记录，却返回了不同的数据，这就是不可重复读。

事务A查询一个范围的结果集，另一个并发事务B往这个范围中插入/删除了数据，并静悄悄地提交，然后事务A再次查询相同的范围，两次读取得到的结果集不一样了，这就是幻读。
15. 在高并发情况下，如何做到安全的修改同一行数据？
有悲观锁和乐观锁两种方案
悲观锁：
悲观锁她专一且缺乏安全感了，她的心只属于当前事务，每时每刻都担心着它心爱的数据可能被别的事务修改，所以一个事务拥有（获得）悲观锁后，其他任何事务都不能对数据进行修改啦，只能等待锁被释放才可以执行
乐观锁：
乐观锁的“乐观情绪”体现在，它认为数据的变动不会太频繁。因此，它允许多个事务同时对数据进行变动。实现方式：乐观锁一般会使用版本号机制或CAS算法实现。
16 SQL优化的一般步骤是什么，怎么看执行计划（explain），如何理解其中各个字段的含义。
show status 命令了解各种 sql 的执行频率

通过慢查询日志定位那些执行效率较低的 sql 语句

explain 分析低效 sql 的执行计划（这点非常重要，日常开发中用它分析Sql，会大大降低Sql导致的线上事故）
17. MySQL事务得四大特性以及实现原理
原子性：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。

一致性：指在事务开始之前和事务结束以后，数据不会被破坏，假如A账户给B账户转10块钱，不管成功与否，A和B的总金额是不变的。

隔离性：多个事务并发访问时，事务之间是相互隔离的，即一个事务不影响其它事务运行效果。简言之，就是事务之间是进水不犯河水的。

持久性：表示事务完成以后，该事务对数据库所作的操作更改，将持久地保存在数据库之中。
小结 : 事务ACID特性的实现思想

原子性：是使用 undo log来实现的，如果事务执行过程中出错或者用户执行了rollback，系统通过undo log日志返回事务开始的状态。

持久性：使用 redo log来实现，只要redo log日志持久化了，当系统崩溃，即可通过redo log把数据恢复。

隔离性：通过锁以及MVCC,使事务相互隔离开。

一致性：通过回滚、恢复，以及并发情况下的隔离性，从而实现一致性。
18.数据库的三大范式
第一范式：数据表中的每一列（每个字段）都不可以再拆分。

第二范式：在第一范式的基础上，分主键列完全依赖于主键，而不能是依赖于主键的一部分。

第三范式：在满足第二范式的基础上，表中的非主键只依赖于主键，而不依赖于其他非主键。