mysql面试知识点

1 引擎-MyISAM和InnoDB的区别

　　a 是否支持行级锁 :

　　MyISAM 只有表级锁(table-level locking)，

　　而InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。

　　b 是否支持事务和崩溃后的安全恢复：

　　MyISAM 强调的是性能，每次查询具有原子性,其执行速度比InnoDB类型更快，但是不提供事务支持。

　　但是InnoDB 提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。

　　c 是否支持外键： 

　　MyISAM不支持，而InnoDB支持。

　　d 是否支持MVCC ：

　　仅 InnoDB 支持。应对高并发事务, MVCC比单纯的加锁更高效;MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作;MVCC可以使用 乐观(optimistic)锁 和 悲观(pessimistic)锁来实现;各数据库中MVCC实现并不统一。

　　e 文件：

　　Innodb 创建表后生成的文件有：【聚簇索引】--【只在主键索引树的叶子节点存储了具体数据：节省磁盘】

• frm:创建表的语句
• idb:表里面的数据+索引文件

 

 

　　Myisam 创建表后生成的文件有：【非聚簇索引】---【查询性能高】

• frm:创建表的语句
• MYD:表里面的数据文件（myisam data）
• MYI:表里面的索引文件（myisam index）　　

　　f 树节点值：

　　MyISAM: B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。

　　InnoDB: 其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的。InnoDB表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”。而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。 因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

2 索引

　　MySQL索引使用的数据结构主要有BTree索引 和 哈希索引 。

　　对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。

　　BTree索引使用的是B树中的B+Tree，但对于主要的两种存储引擎的实现方式是不同的。

　索引结构：【B+树】

　　1.二叉查找树也叫二叉排序树，时间复杂度取决于树高，为O(lgn)。但是树可能不平衡，退化为链表，时间复杂度为O(n)

　　2.平衡二叉树，做旋转操作，所有节点的左右子树高度不能超过1，时间复杂度O(lgn)。当插入数据时，需要1次旋转维持平衡。删除数据时，需要O(lgn)次维持平衡。

　　3.红黑树，根到叶子的最长的可能路径不多于最短的可能路径的两倍长。插入或者删除，O(1)次的旋转或者变色。但是查询慢。

　　4.B（B-）树,每个非叶节点有多个子树。矮胖子。局部性原理。缓存命中率高。每个节点存储真实的数据。

　　5.B+树，只有叶子节点存储真实的数据，非叶子节点只存储键。真实数据包括行的全部数据、主键、地址等。叶子节点之间通过双向链表链接。

　　与B树相比，优势：

　　　　更少的IO次数。每个节点存储的记录数多，访问时所需要的IO次数更少。这样访问局部性原理的利用更好，缓存命中率更高。

　　　　更适用范围查询。只对叶节点的指针链表进行遍历即可。

　　　　更稳定的查询效率：复杂度稳定为树高，因为所有数据都在叶节点。

　　劣势：

　　　　键重复时，占用空间大。

 　　因此，最大优势在于树高更小。B+树高度一般在2-4层。树高由阶数决定，阶数越大树越矮；而阶数的大小又取决于每个节点可以存储多少条记录。

　　Innodb中每个节点使用一个页(page)，页的大小为16KB，其中元数据只占大约128字节左右(包括文件管理头信息、页面头信息等等)，大多数空间都用来存储数据。

• 对于非叶节点，记录只包含索引的键和指向下一层节点的指针。假设每个非叶节点页面存储1000条记录，则每条记录大约占用16字节；当索引是整型或较短的字符串时，这个假设是合理的。延伸一下，我们经常听到建议说索引列长度不应过大，原因就在这里：索引列太长，每个节点包含的记录数太少，会导致树太高，索引的效果会大打折扣，而且索引还会浪费更多的空间。
• 对于叶节点，记录包含了索引的键和值(值可能是行的主键、一行完整数据等，具体见前文)，数据量更大。这里假设每个叶节点页面存储100条记录(实际上，当索引为聚簇索引时，这个数字可能不足100；当索引为辅助索引时，这个数字可能远大于100；可以根据实际情况进行估算)。

　　对于一颗3层B+树，第一层(根节点)有1个页面，可以存储1000条记录；第二层有1000个页面，可以存储1000*1000条记录；第三层(叶节点)有1000*1000个页面，每个页面可以存储100条记录，因此可以存储1000*1000*100条记录，即1亿条。而对于二叉树，存储1亿条记录则需要26层左右。

　　那，为什么使用B+树，不用hash呢？

　　1.hash需要把全部数据加载到内存中，如果数据量大，将非常消耗内存。采用B+树，按照节点分段加载，会减少内存消耗。

　　2.唯一查找一个值时，hash更快。但是数据库中经常查询多条数据，由于B+树数据的有序性，叶子节点有链表相连，查询效率会快很多。

3 事务

　　事务是逻辑上的一组操作，要么都执行，要么都不执行。

　　事务最经典也经常被拿出来说例子就是转账了。假如小明要给小红转账1000元，这个转账会涉及到两个关键操作就是：将小明的余额减少1000元，将小红的余额增加1000元。万一在这两个操作之间突然出现错误比如银行系统崩溃，导致小明余额减少而小红的余额没有增加，这样就不对了。事务就是保证这两个关键操作要么都成功，要么都要失败。

　　四大特性：

1. 原子性（Atomicity）： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
2. 一致性（Consistency）： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
3. 隔离性（Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
4. 持久性（Durability）： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

4 并发事务的问题：

　　在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对同一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题。

• 脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
• 丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
• 不可重复读（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
• 幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

　　不可重复读和幻读区别：

　　不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除比如多次读取一条记录发现记录增多或减少了。

5 事务隔离级别

• READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
• READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
• REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
• SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

　　默认隔离级别：

　　MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。使用的是Next-Key Lock 锁算法，因此可以避免幻读的产生。

　　InnoDB 存储引擎在 分布式事务 的情况下一般会用到 SERIALIZABLE(可串行化) 隔离级别。

6 锁机制与InnoDB锁算法

　　MyISAM和InnoDB存储引擎使用的锁：

• MyISAM采用表级锁(table-level locking)。
• InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁

　　表级锁和行级锁对比：

• 表级锁： MySQL中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁。
• 行级锁： MySQL中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

　　InnoDB存储引擎的锁的算法有三种：

• Record lock：单个行记录上的锁
• Gap lock：间隙锁，锁定一个范围，不包括记录本身。目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生。有两种方式显式关闭gap锁：（除了外键约束和唯一性检查外，其余情况仅使用record lock） A. 将事务隔离级别设置为RC B. 将参数innodb_locks_unsafe_for_binlog设置为1
• Next-key lock：record+gap 锁定一个范围，包含记录本身。为了解决Phantom Problem幻读问题。Innodb对于行的查询使用next-key lock。当查询的索引含有唯一属性时，将next-key lock降级为record key。

7 表优化

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

　　a 限定数据的范围

　　务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内；

　　b 读写分离

　　经典的数据库拆分方案，主库负责写，从库负责读；

　　c 垂直分区

　　根据数据库里面数据表的相关性进行拆分。 例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。

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

　　优点： 可以使得列数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。

　　缺点： 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂；

　　d 水平分区

　　保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。 水平拆分可以支撑非常大的数据量。

　　水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。

　　水平拆分可以支持非常大的数据量。需要注意的一点是：分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以 水平拆分最好分库 。

　　水平拆分能够 支持非常大的数据量存储，应用端改造也少，但 分片事务难以解决 ，跨节点Join性能较差，逻辑复杂。一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。如果实在要分片，尽量选择客户端分片架构，这样可以减少一次和中间件的网络I/O。

　　下面补充一下数据库分片的两种常见方案：

• 客户端代理： 分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。 当当网的 Sharding-JDBC 、阿里的TDDL是两种比较常用的实现。
• 中间件代理： 在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。 我们现在谈的 Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现。

8 池化思想

　　池化设计应该不是一个新名词。我们常见的如java线程池、jdbc连接池、redis连接池等就是这类设计的代表实现。这种设计会初始预设资源，解决的问题就是抵消每次获取资源的消耗，如创建线程的开销，获取远程连接的开销等。就好比你去食堂打饭，打饭的大妈会先把饭盛好几份放那里，你来了就直接拿着饭盒加菜即可，不用再临时又盛饭又打菜，效率就高了。除了初始化资源，池化设计还包括如下这些特征：池子的初始值、池子的活跃值、池子的最大值等，这些特征可以直接映射到java线程池和数据库连接池的成员属性中。

　　在连接池中，创建连接后，将其放置在池中，并再次使用它，因此不必建立新的连接。如果使用了所有连接，则会建立一个新连接并将其添加到池中。连接池还减少了用户必须等待建立与数据库的连接的时间。

　　数据库连接本质就是一个 socket 的连接。数据库服务端还要维护一些缓存和用户权限信息之类的 所以占用了一些内存。我们可以把数据库连接池是看做是维护的数据库连接的缓存，以便将来需要对数据库的请求时可以重用这些连接。为每个用户打开和维护数据库连接，尤其是对动态数据库驱动的网站应用程序的请求，既昂贵又浪费资源。

9 如何做持久化

　　undo日志：

　　记录的是修改前的值，在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方称为Undo Log）。然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。

　　举例： 　　

　　事务A要将字段age的值由原来的1修改为2，要将name的值由原来的Alice修改为Bob，

　　假设现在数据库出现了宕机的问题，分为两种情况：

　　如果在上表第三步之前数据库挂了，则最终数据和日志均为原数据；

　　如果第三步及以后出现异常，则undo已经记录了原来的值，则可以利用undo日志将数据恢复为原数据。

 　　原理

　　当事务提交之后，undo不会被马上删除，而是放入待删除队列，由purge线程来判断是否删除和处理。在mysql5.6之前，undo只存在于共享表空间中，之后的版本中，则可以配置为独立的文件。

undo内部默认128个回滚段槽（rseg slot），每个rseg slot内部有1024个回滚段（rollback segment）。其中：

　　　　slot0——共1个，预留给系统表空间

　　　　slot1~slot31——共31个，预留给临时表空间

　　　　slot32~slot127——共96个，预留给undo独立表空间

　　缺点：

　　由于undo日志会被清理掉，不能保证事务的持久性，因此才需要引入redo日志来保证事务的持久性。

 　　redo日志：

　　记录的是修改后最新的数据和冗余的undo日志，和Undo Log相反，Redo Log记录的是新数据的备份。在事务提交前，只要将Redo Log持久化即可，不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，但是Redo Log已经持久化。系统可以根据Redo Log的内容，将所有数据恢复到最新的状态。

　　举例：

　　事务B要将字段age的值由原来的1修改为2，要将name的值由原来的Alice修改为Bob，redo日志记录的过程表如下：

 　　其中，redo日志必须先于数据写入磁盘（即步骤8和步骤9的顺序不能改变）。因为如果不这样，在数据提交之后再写redo日志，一旦redo日志的写入过程出现异常，将无法保证持久性。

 　　记录redo日志时，先记入redo log buffer，最后再一起写入磁盘，这样可以减少IO，提升性能。

 　　另外，未提交的事务和回滚了的事务也会计入redo日志。

　　mysql恢复的策略：

　　恢复时，先根据redo重做所有事务（包括未提交和回滚了的）。再根据undo回滚未提交的事务。

　　如上，如果事务B异常未提交事务就宕机，恢复时，先根据redo日志将数据恢复为age=2&name=Bob，然后再根据undo记录的age=1&name=Alice将数据恢复如初。

 　　如果事务C异常未提交事务就宕机，恢复时，根据redo日志，可以直接恢复至age=1&name=Alice的初始状态。

 　　redo日志会随着时间推移而越来越大，为了提升redo的恢复性能，引入了checkpoint机制，在恢复的时候，只需要从checkpoint的位置往后恢复即可。