【面试复习】数据库 - mysql（上）
考点：1、索引
（1）索引底层结构：b+树 和 hash索引优缺点；
（2）主键索引在innodb与myisam中有什么区别（一个是聚集性索引，另一个不是）；
（3）非主键索引的data域内放的是什么值（主键的key值）；
（4）索引b树和b+树之间的区别？为什么用b+树而不是b树；
（5）联合索引的建立准则？什么是最左匹配原则？
（6）讲讲索引覆盖
(7) 你们系统当前使用的mysql版本？（5.7） mysql 5.6 对索引做了哪些优化？ （索引下推）
(8) 举例讲讲你们系统中索引失效的场景和解决的方法
2、事务 & 锁
（1）锁的类型有哪些？死锁产生的条件？如何避免？
（2）事务的隔离级别？ 你们系统中采用的隔离级别是？为什么采用这个级别？有什么问题？如何解决的？
（3）讲讲MVCC吧？innodb是如何实现的？
（4）讲一下innodb是如何实现可重复读的？事务的回退如何实现？ MVCC+ReadView
（5）当前读和快照读区别？
（6）mysql 5.6、mysql 5.7 优化点
3、存储引擎
myisam和innodb之间的区别
4、delete drop truncate区别
参考回答-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1、索引
（1）索引的底层结构：b+树 / hash表；现实中绝大多数都使用b+树结构，原因在于： 1、hash索引在范围查找和数据排序方面，效果很差；2、hash的联合索引，没有最左匹配原则；3、hash索引对于大量重复值插入，容易引起交多的hash冲突，性能不好
（2）innodb内部的存储结构分别是表描述文件+索引数据文件；myisam内部存储结构则为：表描述文件+索引文件+数据存储文件。这是因为innodb是将索引和数据存储在一起的，在主键索引中，b+树的叶子节点内的data域内直接存储了数据；而在myisam则存储着指向数据域的指针；上述innodb的主键索引也被称为聚集性索引，好处在于1、大部分情况下减少了一次回表的过程；2、同样在范围查找时，可以一次性载入多个相连的数据，优于非聚集性索引。
（3）非主键索引的data域内放的是什么值？innodb存储的是主键key，myisam存储的是指向数据的指针
（4）索引b树和b+树之间的区别？为什么用b+树而不是b树？ b树和b+树的区别在于：1、非叶子节点的数据域存不存值，b树存值，b+树不存；2、b+树的叶子节点之间有链表相连；使用b+树的好处在于：1、非叶子节点不存数据，这样一个节点就可以存储更多node数据，使得树的高度进一步被压缩；2、叶子节点之间用指针相连，在范围查找时，只要一次查找树，比b树更高效
（5）联合索引的建立准则？什么是最左匹配原则？最左匹配原则：对于联合索引（`a`，`b`，`c`）的排序顺序是，先按照`a`排序，对于`a`相同的，按照`b`排序，对于`b`相同的，按照`c`排序，所以当查找的where语句为 a 或者 a、b 或者 a、b、c时都是有序的，可以利用索引，其余情况下则无法利用索引。所以在建立联合索引时：1、将where语句中最常使用到的，往前排列；2、在1的原则下，将where语句中字段比较短的往前排列。
（6）讲讲索引覆盖 ：当要查找的数据，刚好在索引值的范围内时，就可以不必回表，直接返回。
（7）你们系统当前使用的mysql版本？（5.7） mysql 5.6 对索引做了哪些优化？ （索引下推）
索引下推优化：对于一次索引取回来的主键值（或者是数据指针），先不要着急去磁盘取数据，然后再做二次筛选，可以选对索引值再次下推筛选，使需要取的数据行数，再次缩小，然后再去取值；innodb下仅仅支持二次下推优化。
（8）举例讲讲你们系统中索引失效的场景和解决的方法
索引失效场景：1、where语句中使用了 or，or的两边条件其中一个不是索引，就会导致全文索引，解决方案是：先用索引查出数据，再在这些数据中全文查找
2、where语句中使用的联合索引，不符合最左前缀原则
3、where语句中使用了like
4、where语句中索引自动类型转换，比如说varchar类型没有加 '' 引号，如果可以的话，会自动转换成数值类型，索引会失效
5、where语句中使用了 NOT IN
6、where语句中索引使用了公式计算
2、事务 & 锁
（1）锁的类型有哪些？死锁产生的条件？如何避免？
粒度分：行锁、表锁；是否共享分：共享锁、排他锁；表锁里面还有一个意向共享锁和意向排他锁；死锁产生的条件是：两方各自持有对方需要的锁，且不放弃自己所持有的锁，或者多人之间相互请求，构成一个封闭的环；如何避免：1、一次性获取所有资源需要的锁；2、每个程序按顺序获取需要的锁资源；3、当获取不到资源时，暂时释放目前已经占用的资源
（2）事务的隔离级别？ 你们系统中采用的隔离级别是？为什么采用这个级别？有什么问题？如何解决的？
事务的隔离级别：未提交读、提交读、可重复读、序列化；我们系统采用了可重复读；这个级别保证了事务的隔离性，解决了脏读，不可重复读的问题，并且和MVCC配合还可以解决幻读的问题，与序列化这个级别相比服务的响应速度，并发性能都很高。
（3）讲讲MVCC吧？innodb是如何实现的？
MVCC通过保存每个时间节点的数据，每一个事务开启，代表一个新的时间节点的开启，事务id即为时间节点id，在本事务内的读取修改工作只对之前的时间节点开启的事务数据起作用，在提交时，再比较数据是否有变化，做出提交或者回退的决定，在并发读写时，可以保证无锁，且没有不可重复读，幻读的问题。
innodb的内部实现：innodb是通过在每一行数据后面增加三个隐藏列：数据的创建时间节点（事务id），数据的删除时间节点（事务id），数据回退指针；
innodb内部有两个日志文件，分别为 redo log 和undo log；redo log又分为 redo log buf 和 redo log file，当一个事务开启时，会自动先将数据写到 redo log buf，当事务提交时，再写入到 redo log file中保证了事务的永久性；undo log 则是数据的各个时间点的记录，当对一个数据做出修改（增，删，改）时，innodb就会讲磁盘中的数据保存到undo log 中，磁盘中写入新的数据，同时数据的回退指针指向undo log中的原始数据。
数据回退指针可以保证很方便的查找到各个时间节点的数据，为MVCC提供了方便。事务在读取数据时，要保证数据的创建时间节点要早于该事务的开启节点，事务的开始节点要晚于数据的删除时间节点。
（4）讲一下innodb是如何实现可重复读的？事务的回退如何实现？ MVCC+ReadView
上面的描述中可以看到还存在问题的，考虑到下面这种场景：
虽然事务B遵循了只读取事务B时间节点以前的数据，但是由于事务A提交的满，还是会产生不可重复读，幻读，脏读之类的问题。对依旧活跃的事务id的不同处理态度，其实就决定了不同的事务级别。
在innodb中所有事务的开启之后会被放入到一个 trx_sys的事务链表中。事务链表里面保存着尚未提交的所有事务，如果事务被提交就会从链表中删去。
MVCC实现的一个关键：ReadView
ReadView说白了就是一个数据结构：{up_trx_id, low_trx_id, trx_ids}，在sql开始前被创建，内部包含了 up_trx_id：当前系统中尚未提交的最小事务id，low_trx_id：当前系统中除了自身事务id以外，最大的尚未提交的事务id，trx_ids：当前up_trx_id~low_trx_id之间所有尚未提交的事务id集合。
则所有在up_trx_id之前的事件id操作的数据，对当前sql都可见；所有在 low_trx_id之后的数据，对当前sql都不可见；对于中间的活跃的事务id，提交读是指对中间提交了的数据，sql可见；对于不可重复读级别，中间事务集合都是不可读的。（参考：https://zhuanlan.zhihu.com/p/40208895）
（5）当前读和快照读区别？
基于MVCC读取数据，读取到的很多都是历史数据；当前读是指只读取磁盘中最新的数据，而不去undo log里面拿历史数据；当前读可能会有幻读的风险，可以考虑用间隙锁来解决。
（6）mysql 5.6、mysql 5.7 优化点
3、存储引擎
（1）myisam、innodb的区别
4、delete drop truncate区别
delete truncate：不删除表结构只删除数据；drop：直接删除表结构和所有数据；
delete是DML语言，事务管理的，commit之后生效；drop truncate是DDL语言，删除立即生效；
1、索引是什么
索引是表的一列或多列的值进行排序的数据结构，这个数据结构通常为b+树
b树与b+树
B树也称B-树,它是一颗多路平衡查找树。我们描述一颗B树时需要指定它的阶数，阶数表示了一个结点最多有多少个孩子结点，一般用字母m表示阶数。当m取2时，就是我们常见的二叉搜索树。
一颗m阶的B树定义如下：
1）每个结点最多有m-1个关键字。
2）根结点最少可以只有1个关键字。
3）非根结点至少有Math.ceil(m/2)-1个关键字。
4）每个结点中的关键字都按照从小到大的顺序排列，每个关键字的左子树中的所有关键字都小于它，而右子树中的所有关键字都大于它。
5）所有叶子结点都位于同一层，或者说根结点到每个叶子结点的长度都相同。
b+树：
1）B+树包含2种类型的结点：内部结点（也称索引结点）和叶子结点。根结点本身即可以是内部结点，也可以是叶子结点。根结点的关键字个数最少可以只有1个。
2）B+树与B树最大的不同是内部结点不保存数据，只用于索引，所有数据（或者说记录）都保存在叶子结点中。
3） m阶B+树表示了内部结点最多有m-1个关键字（或者说内部结点最多有m个子树），阶数m同时限制了叶子结点最多存储m-1个记录。
4）内部结点中的key都按照从小到大的顺序排列，对于内部结点中的一个key，左树中的所有key都小于它，右子树中的key都大于等于它。叶子结点中的记录也按照key的大小排列。
5）每个叶子结点都存有相邻叶子结点的指针，叶子结点本身依关键字的大小自小而大顺序链接。
b树和b+树存在的目的？为啥数据库会选择b+树？
一切都是为了更便捷的查找，查找数据一般可以考虑使用树型结构或者hash表查找；考虑使用树形结构查找，每访问一个节点的过程就是访问节点对应的磁盘位置，将磁盘数据复制到内存，进行比较，总结就是一次磁盘io + 多次内存比较；所以树的层级应该保持越低越好（同时考虑到内存拷贝的成本，不可能一次性把所有数据到拷到内存里面作比较的），此时使用b树存储就很有比较。一个高阶（例如m>100)的b树可以存储的数量级，已三层为例就可以达到百万级。
b+树比b树的优势又在与：（1）b+树的中间节点没有存储数据的功能，只是索引节点，这样所占用的内存就更少，一个节点可以存储的关键字就更多，树的层级可以进一步变短；（2）b+树的叶子节点之间有指针指向下一个叶子节点的地址，整个呈现一个链表的形式，这种结构对范围查找更具有优势。
2、数据库表的存储引擎
myisam vs innodb
myisam：存储在磁盘中的文件有： xxx.frm(存储表定义，表结构)，xxx.MYD(存储数据)，xxx.MYI(存储索引)
可以看到主键索引为非聚集性索引，访问数据需要先访问索引，索引内存储了指向数据位置的指针，事实上，普通索引也是非聚集性索引。
innodb：存储在磁盘中的文件有： xxx.frm(存储表定义，表结构)，xxx.ibdata(索引+数据)
可以看到 主键索引的叶子节点上存储着完整的数据记录，不必再像myisam一样需要二次到数据文件内取数据，普通索引的data部分则存储了主键的值。
myisam与innodb还有一个很大的区别在于：myisam不支持事务，而innodb支持
3、什么是数据库事务？事务的特性是什么？
事务：一系列sql命令，要么全部执行，要么全部不执行
特性：原子性，一致性（事务将数据库从一种一致状态转变为下一种一致状态。也就是说，事务在完成时，必须使所有的数据都保持一致状态（各种 constraint 不被破坏）），隔离性，持久性 （ACID）
4、什么是脏读？幻读？不可重复读？什么是事务的隔离级别？Mysql的默认隔离级别是？
脏读：一个事务在读取过程中，另一个事务回退了，读到了脏数据
不可重复读：一个事务重复读取一个数据，期间这个数据被update了，导致重复读取的数据不一致
幻读：一个事务读取表格所有数据过程中，数据表新插入了数据，导致幻读。
事物的隔离级别：Read Uncommited 读未提交 ，可以读取事务未提交的数据；Read Commited 读提交，只能读取事务提交后的数据，可以克服脏读的问题；Repeatable read 可重复读，就是在开始读取数据（事务开启）时，不再允许修改操作，可以解决 不可重复读 问题；Serializable 序列化， Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。
Mysql的默认隔离级别是Repeatable read。
5、事务隔离的原理是
锁
锁又分为：
行锁（innodb引擎的）：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
表锁（myisam引擎的）：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。
页锁：上面两者的中和，一次锁定相邻的一组记录。开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般
6、死锁？如何避免？
死锁：多个事务之间占有着各自的资源的同时，请求对方的资源
如何避免：（1）每个事务按顺序请求各个资源；（2）对于单个事务可以考虑一次性请求完所有资源；（3）升级锁的返回，从行锁变成表锁；（4）使用乐观锁
7、乐观锁和悲观锁
悲观锁：互斥锁，Repeatable read的事务都属于悲观锁
乐观锁：read-copy-update:先读取，拷贝出来修改，等到更新时，观察数据与原来的数据是否相同，相同则更新，不同重复上述步骤。
8、大数据情况下如何做分页查询
首先需要知道mysql并不是跳过offset，而是先取offset+N，然后返回时去掉offset之前的数据，返回N的部分，当数据量巨大的，每次分页查询，工作量都是巨大的。
解决方案：先定位到需要截取的id字段，然后再关联
select a.* from 表 a, (select * from 表 limit 100000, 20) b where a.id = b.id;
9、数据库连接池
提前建立一堆tcp链接，省去了mysql接收请求，建立连接，销毁链接的步骤
