数据库常见面试题（更新中）

B+树叶子节点存储的是索引还是数据？

• mysql的默认存储引擎是innoDB，innoDB使用聚集索引，也就是索引和数据在同一个文件，因此叶子节点存储的是数据。
  如果是myisam存储引擎的话，叶子节点存储的是索引。

说说InnoDB

• InnoDB存储引擎是mysql的默认存储引擎。底层采用B+树组织数据，所有数据都在叶子节点上。默认隔离级别是可重复读，支持事务，支持外键，使用主键索引+辅助索引的组织方式（主键索引和数据放在同一个文件，其他索引只存索引和主键值），锁粒度是行锁。采用MVCC+行锁的特性，让InnoDB适用于高并发读写的情况，并且InnoDB具备故障恢复的能力。InnoDB的事务默认开启，默认提交，也就是每一条记录就是一个事务。

说说Myisam和InnoDB的区别。

• InnoDB使用的是行锁，Myisam使用的是表锁
• InnoDB支持事务，Myisam不支持事务
• InnoDB支持外键，Myisam不支持外键
• InnoDB数据和主键索引在一起存储，而Myisam索引和数据分开存储，所以存储InnoDB一个表只有两个文件，而存储myisam一个表要三个文件。
• Myisam表可以被压缩，节省空间，而InnoDB不可以。

为什么推荐使用整型作为主键，并且主键自增？

• 首先，innoDB存储引擎在进行组织的时候，必须要有一个主键，如果没有指定，则会自动选择一个合适的不包含null的唯一索引作为主键，如果找不到，就会创建一个虚拟列作为该innoDB表的主键。
• 整型在进行比较的时候，效率高于字符串的比较，并且整型所占用的内存也小于字符串类型（因为二级索引会存储主键索引的主键），因此选择用整型。
• 而推荐主键自增是因为inboDB存储引擎在存储数据的时候使用B+树作为索引进行数据组织。往B+数添加数据的时候，可能需要进行页分裂，对B+树进行调整。如果主键非自增，在插入B+树的时候需要去计算将要插入的位置，而且在页中间插入数据需要重新分配空间，以及移动旁边的数据，这样会导致频繁的页分裂操作同时会产生碎片。频繁复制数据到新的页，使性能下降。而如果使用自增，则每次加入的位置都是在B+树的最后，这样页分裂的操作会减少，也就不用频繁复制数据。

说说有哪些索引失效场景？

• 不符合最左前缀原则的时候。
• 使用模糊查询，前面是通配符%或者_的时候。
• 使用!=，<>的时候
• 查询类型和数据库类型不一致的时候，需要隐式转换
• 对字段进行了运算
• 查询条件使用了某些函数（如concat），now函数还是可以走索引的

说说B树，B+树的区别

• B树每个节点都是索引+数据，因此没有索引冗余。而B+树非叶子节点不存数据，只在叶子节点存数据。因此B+树的非叶子节点可以存储更多的索引，树的高度相对会比较矮，进行数据读取的时候磁盘IO的次数较少。
• 因为数据都在叶子节点，B+树的查询复制度固定为 O(log n)，而B树的复杂度不稳定，最好可以达到O(1)。
• B+树叶子页之间有指针互相指向，因此可以进行范围查询，而B树没有该指针。

如何查看是否使用索引？

• 通过explain命令，explain后面跟着sql语句。就会打印执行该sql的一些信息。
• 里面有一个key字段，如果该字段不为空，就代表使用了索引。type字段代表该索引的类型。

现在有一个五百万条记录的数据库，读写效率低，如何优化？

• 首先查看sql语句是否合理，比如查询的时候只查询需要的字段，尽量避免select *，这样可能只进行一次查询就可以获得数据，而不用回表。
• 用explain+sql语句来查看sql语句是否有走索引，以及走的是否是预想中的索引，如果没有走索引，想办法优化sql语句，让sql走索引。如果没有索引就根据sqll语句后面where部分建立索引，如果需要查询的是多个字段，那就建立联合索引，这样可以减少回表操作。
• 根据alibaba java开发手册里面推荐，数据库表记录达到500万或者表大小达到2G，就可以进行分库分表了。
• 分库可以按照水平或者垂直维度进行划分，按照项目模块，或者id大小，时间等来进行分库。
• 分表的话，可以根据某些规则对表进行切分。可以按照取模来进行划分，这样单个记录的查询效率可以大大提升，不过不能进行范围查找；也可以按照主键范围进行均分，这样分表之后还可以进行范围查询。查询的时候，一条sql语句可能会分为多条sql语句在多个表进行查询。

B+树一页可以存放多少条记录？

• 在B+树中，一个页的大小是16K。非叶子节点一个记录就是主键+指针。
• 主键一般是bigint，大小是8字节；mysql指针大小是6字节，所以一个节点大小为14字节。
• 一页可存节点数=16*1024/14≈1170个。

sql执行时，底层发送了什么？

• 首先连接器建立到数据库的连接。
• 查询缓存，如果select语句查询的数据在缓存里，就直接返回。如果没有就执行后续操作，查询完成之后再写入缓存。（一旦遇到经常更新的，缓存就会经常被清空；8.0开始没有该功能）
• 如果没有命中缓存，mysql就开始执行sql语句，首先分析器会对sql语句进行解析。首先词法分析，分析哪些词代表哪些意思。接着做语法分析，分析该sql语句是否符合语法规范。不符合规范就会报错，提示语法错误的地方。
• 经过了分析器之后，mysql知道了sql语句所要执行的操作。但是开始执行之前，会先进行优化器优化，比如多个索引的时候，选择走哪个索引；多个表连接的时候先连接哪两个表。
• 接着到了执行器阶段。执行之前，会先判断用户对当前表是否有该操作的权限，没有权限就会报错。如果有权限，就打开表继续执行。打开表的时候，优化器就会根据表的引擎定义，去使用这个引擎提供的接口。
• 例如查询name=‘lisi’，如果没有索引会一条一条地查询记录，如果记录对应，就将记录放入结果集，一直查询完整个表，然后返回结果集。如果有索引就会去查询索引，如果索引能直接命中需要的字段就返回结果集，否则就执行回表操作，回到主表进行查询，并返回结果集。至此sql语句执行完毕。

mysql MVCC是什么？如何实现？

MVCC即多版本并发控制，是 MySQL 数据库中实现事务隔离级别的一种方式，在innoDB存储引擎中使用。它的主要目的是为了实现高并发环境下的数据一致性和可靠性。
mysql中，MVCC的实现主要通过以下两种机制：
1.为每个数据行保存多个版本记录。
MVCC在数据库中维护着多个数据版本，靠事务ID和时间戳来唯一确定一个版本。当事务更新一个行记录的时候，mysql会为这个新记录生成一个新的版本，并将该版本与事务ID和时间戳相关联。同时mysql会将该版本写入一个新的页中，并把该页标记为活跃状态。这样每个事务都能访问到该行记录的不同版本，从而实现数据的多版本控制。
2.通过快照读实现读的一致性。
mysql通过快照读来实现读一致性。当执行 SELECT 查询时，MySQL 会根据该查询所处的事务 ID，读取该事务 ID 第一次查询时的数据库状态，这个版本就是快照。（底层是用到了undo日志）
在实现 MVCC 时，MySQL 还需要用到 Undo 日志，它记录了每个事务所做的修改，以及修改前的数据版本。当一个事务需要回滚时，MySQL 就会根据 Undo 日志中的信息，将数据回滚到之前的版本。

如何实现快照读？

1.当一个事务开始时，MySQL会为该事务分配一个唯一的事务ID。
2.当一个事务执行快照读时，MySQL会将当前事务的ID作为查询条件，只查询小于等于该事务ID的记录。这样可以保证查询结果是在该事务开始之前已经提交的数据。
3.在执行查询时，如果发现某一行数据的最后修改事务ID大于当前事务的ID，说明该行数据已经被其他事务修改过了，当前事务无法读取该行数据。此时，MySQL会从通过版本链往前一直到事务ID小于等于当前事务的ID，如果一直都没有小于等于当前事务id的，则代表该条记录不可见。

如何保证mysql原子性

靠undolog日志文件，执行事务的时候会将执行的写指令写入undolog日志文件，如果发生回滚，就将undolog的sql执行就可以恢复到事务前的状态。

如何保证mysql一致性

数据库通过原子性（A）、隔离性（I）、持久性（D）来保证一致性（C）。其中一致性是目的，原子性、隔离性、持久性是手段。因此数据库必须实现AID三大特性才有可能实现一致性。
mysql事务提交的时候有两个阶段。第一个阶段是将提交的事务操作写入redolog日志文件，并且redolog日志文件变为prepare状态。第二阶段将操作写入binlog日志文件，修改状态为commit，然后提交事务。
如果在第二阶段之前发生宕机，事务就会回滚。此时还没有写binlog，也就不会导致主从数据不一致。
如果在第二阶段执行过程中发生宕机，mysql会根据redolog里面的事务做判断，如果事务已经完整，有了commit标识，那么就提交事务。如果redolog还是处于prepare状态，就判断binlog日志里面对应的事务是否存在并且完整，是就提交事务；否就回滚。

为什么需要两个阶段呢？

是为了保证数据的一致性。在多库的情况下，如果事务提交过程中发生宕机，可能会导致主库和从库的数据不一致。

如何保证mysql持久性

mysql有一个redolog日志，是用来保证事务安全的。每次有写操作的时候都会先记录到redolog日志文件中，等到CPU不忙的时候再持久化到磁盘。如果持久化之前数据库宕机了，重启之后会执行redolog里面的sql指令，完成数据持久化。
Binlog是用来记录Mysql内部对数据库的改动（只记录对数据的修改操作），主要用于数据库的主从复制以及增量恢复。

binlog和redolog的区别

binlog是mysql server层实现的二进制日志，而redolog是引擎层的重做日志。
binlog记录的是逻辑日志，记录的是具体的写操作sql语句；而redolog记录的是物理日志，记录的是哪个物理页发生了什么改变。
binlog在使用完一页之后，会生成新的页，然后再新的页上面写；而redolog会用新的记录去覆盖旧的记录。
binlog可以作为恢复数据使用，主从复制搭建；而redo log作为异常宕机或者介质故障后的数据恢复使用。

mysql权限管理

基本语句格式：

//添加权限
grant 权限 on 数据库对象 to 用户

//撤销权限
revoke 权限 on 数据库对象 from 用户 

//给用户查询的权限,root是用户名，@后面是主机ip，数据库对象是testdb库的所有表
grant select on testdb.* to root@localhost;

//给用户查询的权限，同时运行用户把权限授予其他用户(在上面的基础上加上grant option)
grant select on testdb.* to root@localhost with grant option;

统计数量count(*)、count(字段名)、count(常量)

COUNT(常量) 和 COUNT(*) 表示的是直接查询符合条件的数据库表的行数。
而COUNT(列名)表示的是查询符合条件的列的值不为NULL的行数。

myisam对count()做了一些优化，把表的总记录条数缓存了下来，只要不包含where查询条件，查询起来就会很快；因为myisam使用的是表锁，因此查询到的数据是准确的。
innodb也对count()做了一些优化，比如通过低成本索引扫表；通过非主键索引，因为非主键索引不存记录值，因此扫描速度较快。MySQL会优先选择最小的非主键索引来扫表。（前提也是没有where筛选条件和group by）
innodb中，count（1）和count（）本质上是一样的，但依旧建议使用count()，因为这是SQL92定义的标准统计行数的语法。
count(字段)使用的是全表扫描，遇到非空记录就count加一，速度比count(1),count()要慢。
查询表记录条数建议使用count().