526互联

面试题9.18

发布时间 2023-09-20 10:36:16作者: John_Ran

来自于：B站，尚硅谷MySQL数据库面试题宝典，mysql面试必考！mysql工作必用！

MYSQL索引

  1. InnoDB和MyISAM的实现区别是什么？

    MySQL的索引分三类：B+树索引，Hash索引，全文索引。第一点：InnoDB数据和索引放在一个文件，MYISAM索引与原来的文件分开。第二点：在索引的B+树，InnoDB的叶子结点是数据本身，MYISAM放的是数据的地址。

  2. 一个表中没有创建索引，还会创建B+树吗？

    会的，有默认主键。又一个隐式的row id。

  3. B+树的原理。

    B+树的每一个节点都是按页来存储的。一页能存储16KB，对于非叶子结点来说，record_type=1，recor_type=2，代表页开始，record_type=3代表页结束，record_type=0代表是叶子结点。每一个页的表示区间都是[)。

  4. 聚簇索引和非聚簇索引在B+树实现有什么区别？

    聚簇索引：索引和数据都在同一个B+树里，页内的记录是按照主键大小顺序形成一个单向链表。页和页之间按照页中记录的主键大小顺序排成一个双向链表。非叶子结点存储的是记录的主键+页号。叶子节点存储的是完整的用户记录。

    优点：数据访问快，对于主键的排序查找和范围查找更快。节省了大量I/O操作。

    缺点：插入速度依赖于插入顺序，按照主键的顺序进行插入比较快。否则会出现页分裂，影响性能。一般是自增ID为主键。猪油InnoDB支持聚簇索引，MYISAM不支持。MYSQL的表只能有一个聚簇索引。如果没有定义主键，那么就会找唯一索引，实在没有会定义一个隐式的主键。不建议使用UUID，MD5，HASH，字符串作为主键。

    非聚簇索引：只有搜索条件是主键的时候才能有作用。如果想以别的列作为搜索条件，那么需要创建非聚簇索引（额外的B+树）。

    由于叶子结点定义的是主键。想要拿到该主键的完整记录，要回表。

  5. B+树中聚簇索引的匹配逻辑？太简单，不说了。

  6. 非聚簇索引的匹配逻辑： 最大的问题就是没有唯一性约束了。一个值的记录可能分布在多个数据页中。

  7. 组合索引的最左前缀匹配原则？ https://juejin.cn/post/7253389022939791421

  8. 模糊匹配，例如字符串，前缀不固定。用like匹配可能就会导致匹配失效。

  9. 平衡二叉树，红黑树，b树和b+树区别？平衡二叉树，左右平衡，叶子的高度差不会超过1，超过1就自旋。树太高，会导致自旋的数量大。高度高查找效率慢。红黑树：是一个非严格的平衡树，两次旋转平衡，分为红黑节点。

  10. 一个B+树中大概可以放多少索引记录？千万级别的数据，可以三层放下。一个数据块4k，四个4k就是一个page。根结点（根结点）只存键值+指针。算10字节，那根节点可以存1600条。下一层类似。叶子结点类似。叶子结点要存数据，不止10字节，算1kb吧。1600*1600*16就是千万级别的。

  11. 对于自增常主键来说，删除其中一些int型自增长主键。再插入一些，问键值是多少？分为重启不重启，重启就是重新开始，不重启就从最大的开始。

  12. 索引的优缺点是什么？

    聚簇索引：顺序读写，范围快速查找，范围自带顺序。非聚簇索引：避免全局scan，范围，排序，分组查询，再回表。覆盖索引不需要回表操作。

  13. 索引的代价？

    每一个索引都是一颗b+树，一颗b+树的每一个结点都是一个数据页（16KB）。一颗b+树由很多数据页组成。造成了空间上的代价。

    每次增删改，都会修改所有b+树。

  14. 什么时候建立索引？在查询操作很多，很频繁的时候一定要建立索引。如果不怎么用查询，只用来储存，那就不需要索引。

  15. 使用索引一定能提升效率吗？内容不多不用，频繁的修改也不会。唯一索引作为主键其实也不会（比如电话号码）。

  16. CRUD的时候，聚簇索引与非聚簇索引的区别是啥？

    聚簇索引插入的时候比非聚簇索引插入慢，因为要保证主键不重复。聚簇索引，有序，查找范围快。非聚簇索引常常需要回表。

  17. 什么时候需要创建索引。

    频繁查询where条件字段。关联字段需要建立。排序字段可以建立索引，分组字段可以建立索引。统计字段可以建立索引。

  18. 什么时候不需要创建索引。

    频繁更新的index不适合。where，分组，排序，统计上用不到的不用。表太小了不用。参与mysql函数计算的不用。

  19. 什么是索引下推？多个筛选条件的时候，回表多次。开启索引下推就可以一下查出来。

  20. 什么时候一定会导致索引失效？计算，左模糊，全模糊。!=, <>会导致索引失效 NOT NULL和IS NOT NULL。

  21. 如何查看一个表的索引？show index from t_temp；或者explain select * from t_temp where id = 1;

  22. 多个索引的优先级是如何匹配的？用optimizer_trace是可以看到索引选择的逻辑的。

    主键索引最快，全值匹配（单值匹配）高，最左前缀匹配。范围匹配，索引匹配，全表扫描。

    一般来说，对于单键索引，尽量选择过滤性更好的索引。组合索引，过滤性好的放前面。范围查询放后面，避免索引失效的情况。

  23. order by的时候，能否通过索引提升查询效率？ 没有过滤条件，索引不生效。通过索引排序的内部流程是什么？覆盖所用会直接排序。如果不是覆盖索引，那就是考虑是不是要用双路排序了。

MYSQL原理

  1. MYSQL支持查询缓存吗？Mysql8废弃了。主要原因：没有灵活的管理缓存失效与生效机制，SQL完全一致才能cache命中。太太result不支持，分库分表不能用等等。替代方案：redis，ehcached。

  2. MySQL的核心模块？作用是什么？一条MySQL命令发送给MySQL之后是怎么执行的。

    连接层（也会有连接池）。解析与优化，语法解析，查询优化。存储引擎InnoDB和MYISAM。

  3. 默认引擎：InnoDB：支持事物，行锁，外键。

  4. 引擎结构。InnoDB：内存结构和磁盘结构。MYISAM，不支持外键，不支持事务，不支持行锁（几乎没有写操作，全是高频读）。MYISAM并发查询，节省资源，消耗少，简单业务。

MySQL的事务：

  1. ACID是什么。原子性（要么都做，要么都不做），一致性（从一致性到另一个一致性，一致性是指只包含成功事务提交的结果），隔离性（不能被其他事务干扰），持久性（改变应该是永久性的）。

  2. 什么是脏读，丢失修改，不可重复读，什么是幻读。脏读是读到了另一个事务未提交的数据。丢失修改，事务读取的时候，另一个事务也读了。两个事务都修改了，这导致了修改丢失。不可重复读，同一个事务读取同一数据的时候，读不到相同的结果。幻读：一个事务多次查询的结果集不一样。事务没结束，另一事务插入了数据。

  3. 事务的隔离级别。ru，rc，rr，serializable。InnoDB默认的是可重复读。隔离级别越低，锁少，性能高。反之亦然。

  4. 事务的隔离是如何实现的？串行化：表锁。rc和rr是行锁+MVCC来实现的。

  5. MVCC的内部细节：MVCC是一种并发控制的方法。依赖于隐藏字段，read view，undo log。DB_TRX_ID(最后一次修改本行的事务ID)，DB_ROLL_PTR指向本行的undo log。DB_ROW_ID如果没有设置主键且该表没有唯一非空索引。InnoDB会使用该id生成聚簇索引。Read View是记录和合理不同事务并发的时候哪些版本对当前事务可见。undo log是用来回滚的。

  6. MySQL的一致性，原子性，持久性是如何实现的。？还没懂

    mvcc和锁实现了执行过程中的一致性和原子性。灾备方面是Redo log，Redo log会把对数据库的修改都记录下来。Redo log保证食物的持久性，事务提交时，会把所有日志放在日志文件里，WAL（write adead log），断电重启可以从redo log里恢复，redolog写入失败也就是修改失败（必须先写日志），整个事务就回滚了。

  7. MySQL的表级锁和行级锁区别？串行化的时候，就是表锁。非串行化的时候，行级锁是mvcc来完成的。S锁共享所，读锁。X锁，写锁。意向锁IS和IX。

  8. InnoDB支持的锁，表锁，行锁，间隙锁，Next-Key锁。

  9. 什么是XA协议？

  10. 怎样避免死锁？

MySQL的日志

  1. bin log，redo log，undo log，介绍一下。

    一个事务执行的时候，先记一下undo log，记一下数据修改之前的值，用于失败后的回滚，也可以用于mvcc访问老版本的数据。写redo log，数据修改之后的值（期望的值），写在磁盘上。bin log记录的是SQL的二进制，这个阶段语句磁盘上已经修改结束了，用来恢复数据（所以不包含查询，只有增删改），或者用于主从同步，（用到time stamp的话，系统变量的话可能出现不一致的情况）。

  2. 这三种log的刷盘机制是如何实现的（磁盘写入时机）。

    首先不是实时写入。binlog的写入策略是可以选的。sync_binlog=0的时候，每次提交事务binlog不会马上写入磁盘，写到page cache，这快得多。但是有丢失日志的风险。sync_binlog=1的时候调用fsync直接写，sync_binlog大于1，先写到page cache，累计多少个之后，写到磁盘里。

    undo log和redo log在内存中有log buffer。这取决于innodb_flush_log_at_trx_commit。

  3. 为啥同步的时候用binlog，所有引擎都用binlog。直接刷二进制肯定没问题，但是中断了就不知道中断后游标在哪儿了。支持增量同步。binlog可以用中间件。

MySQL开发

  1. BLOB可以存储二进制大对象的字段类型。
  2. 存：需要高效查询和文件都很小。不存：文件比较大，数据多而且变更频繁。
  3. 储存的时候，问题：大sql执行失败。主从同步比较慢，应用程序阻塞，但用网络贷款。Emoji乱码，用utf-8mb4。
  4. 如何储存ip地址。只需要4个字节，INET_ATON()，INET_NTOA,ipv6用INET6_ATON, INET6_NTOA。