MySQL面试题（150题）

• 001.MySQL如何实现的索引机制

  MySQL索引有B+树索引、B树索引、Hash索引，InnoDB 和 MyISAM 都支持 B+ 树索引、Memory使用的是Hash索引，MySQL默认使用的是InnoDB存储引擎、InnoDB的B+树非叶子节点存放的是主键ID，聚簇索引叶子节点会存放数据本身，非聚簇索引的非叶子节点存放索引字段数据和主键ID。MyISam由于不支持聚簇索引，所以它的叶子节点存放的是数据物理地址。数据结构类型层面：B+树索引、B树索引、Hash索引，存储方式层面：聚簇索引、非聚簇索引，字段数层面：单值索引、联合索引，字段特性层面：主键索引、唯一索引、全文索引

• 002.InnoDB索引与MyISAM索引实现的区别是什么？

  InnoDB索引与MyISAM索引都使用的是B+树，它们的非叶子节点存放的都是主键ID，但叶子节点InnoDB索引存放的是真实的数据，而MyISAM索引存放的是数据地址

• 003.一个表中如果没有创建索引，那么还会创建B+树吗？

  如果没有创建任何非空唯一索引和没有指定主键那么，系统会使用隐式字段row_id作为隐式主键并且为它建立聚簇索引

• 004.说一下B+树索引实现原理（数据结构）

  B+树是一种多路查找数据结构，一个节点对应多个子节点，分为叶子节点和非叶子节点，大小默认为16kb即一个数据页，非叶子节点存放建值，叶子节点如果是聚簇索引会存放具体数据，非叶子节点存放主键值 每个节点左边的子节点都要小于当前节点右边大于当前节点，叶子节点之间使用双向链表，叶子节点内数据使用单向链表

• 005.聚簇索引与非聚簇索引B+树实现有什么区别？

  聚簇索引，索引和数据保存在同一个B+树中，页内的记录是按照主键的大小顺序排成一个单向链表 。页和页之间也是根据页中记录的主键的大小顺序排成一个双向链表 。非叶子节点存储的是记录的主键+页号。叶子节点存储的是完整的用户记录。聚簇索引的B+数据叶子节点存放具体数据，而非聚簇索引叶子节点存放索引字段值和主键ID 他们的非叶子节点存放的都是页号和主键值或者索引字段值

• 006.说一下B+树中聚簇索引的查找（匹配）逻辑

  先从B+树的根节点查询，如果查询值小于当前节点中最小值，则沿着当前节点最小值左边指向的页继续查找；如果查询值大于当前节点中最大值，则沿着当前节点最大值右边指向的页继续查找否则找到满足当前值所在范围的指针继续向下查找比如当前是要查询主键为20的数据，先会使用二分法获取到主键为20所在范围的非叶子节点在确定数据所在叶子节点的数据页，再遍历数据列表找到主键为20的数据

• 007.说一下B+树中非聚簇索引的查找（匹配）逻辑

  先从B+树的根节点查询，如果查询值小于当前节点中最小值，则沿着当前节点最小值左边指向的页继续查找；如果查询值大于当前节点中最大值，则沿着当前节点最大值右边指向的页继续查找 找到对应叶子节点后取到主键ID从聚簇索引查询数据或回表查找数据

• 008.平衡二叉树、红黑树、B树和B+树的区别是什么？都有哪些应用场景？

  • 二叉树：最多两个节点
  • 平衡二叉树：最多两个子节点、任何一个节点左右树的高度差绝对值不超过1(防止二叉树退化成链表)
  • B树：可以有多个节点、可以根据范围进行多路查找、叶子节点、非叶子节点都会存放数据
  • 红黑树：自平衡二叉树
  • B+树：可以有多个节点、可以根据范围进行多路查找，叶子节点存放数据

• 009.一个B+树中大概能存放多少条索引记录？

  非叶子节点按索引键是bigInt类型8bit,一个数据页是16kB，那么可以存放16 X 1024/(8+6指针大小)=1170 叶子节点由于存放数据，如果一行数据1kb，那么叶子节点一页可以存放16行数据，三层B+树的话总数据量为：1170 X 1170 X 16=21902400如果是四层就能存放2000W X 1000约等于100亿的数据，算上根节点加载只需要四次IO

• 010.使用B+树存储的索引Crud执行效率如何？

  • 聚簇索引插入新值比采用非聚簇索引插入新值的速度要慢很多，因为插入要保证主键不重复(提前检查,非聚簇索引设置唯一同样效果)
  • 聚簇索引叶子节点存放数据，所以在查询的时候可以直接定位到数据行，而非聚簇索引查询非索引字段信息时需要回表查询
  • 聚簇索引插入主键的同时需要保存数据，而非聚簇索引只需要保存索引字段和主键值即可

• 011.什么是自适应哈希索引？

  MySQL数据库实现Hash索引的一种方式，可以动态调整散列通的数量维护一个合理的扩展因子，让每个桶内的数据量尽量一致，从而提高查询效率

• 012.什么是2-3树、2-3-4树？

  • 2树是指一个节点对应的子节点只能包含2个节点或不包含节点不能包含1个节点，然后遵循左边小于节点，右边大于节点
  • 3树是指一个节点存放最小、最大两个值，对应的子节点只能包含3个节点或不包含节点不能包含1个、2个节点，然后遵循左边小于最小，中间介于最小和最大之间，右边大于最大值

• 013.说一下自增主键和字符串类型主键的区别和影响

  首先主键默认是聚簇索引结构，使用的是B+树，非叶子节点存放主键ID和每个节点对应子节点的指针，叶子节点存放数据，节点和节点之前使用升序双向链表，节点内部使用升序单向列表连接 而自增主键由于插入是自增的，所以会在最大的记录有顺序插入，而字符串类型

• 014.使用int自增主键后，最大id是10，删除id 10和9，再添加一条记录，最后添加的id是几？

  如果没重启，会延续删除之前最大的id开始递增，如果重启，会从最大的id开始递增,重启会重置自增量

• 015.索引的优缺点是什么？

  • 优点：索引可以加快数据检索，减少I/O次数，大大提高查询效率
  • 缺点：增加空间占用，增删改数据会降低效率

• 016.使用索引一定能提升效率吗？

  不一定，如果在增删改很频繁且查询频率并不是很高的表建立索引反而降低了表使用效率，如果表数据量不大，加索引和不加索引可能没啥差别

• 017.如果是大段文本内容，如何创建（优化）索引？

• 018.什么是聚簇索引？

  聚簇索引就是，在建表时候构造的B+树结构，非叶子节点存放主键和指针，叶子节点存放具体的数据值，聚簇索引是非空唯一的，如果建表时候没有指定主键，存储引擎会找是否满足非空唯一的其他索引，如果没有会自动生成一个隐式ID作为主键

• 019.一个表中可以有多个（非）聚簇索引吗？

  可以，但是聚簇索引只能有一个

• 020.聚簇索引与非聚集索引的特点是什么？

  • 聚簇索引叶子节点存放具体数据、非聚簇索引叶子节点存放索引字段和主键ID，所以聚簇索引会占用更多的空间
  • 聚簇索引查询直接在叶子节点就可以找到数据，而非聚簇索引如果查询了非索引字段需要根据主键ID回表查询其他信息
  • 聚簇索引是非空唯一性的，而非聚簇索引是可以唯一也可以不唯一
  • 聚簇索引在插入和修改时代价比较高需要对所有重排序

• 021.CRUD时聚簇索引与非聚簇索引的区别是什么？

  • 聚簇索引插入新值比采用非聚簇索引插入新值的速度要慢很多，因为插入要保证主键不重复(提前检查,非聚簇索引设置唯一同样效果)
  • 聚簇索引叶子节点存放数据，所以在查询的时候可以直接定位到数据行，而非聚簇索引查询非索引字段信息时需要回表查询
  • 聚簇索引插入主键的同时需要保存数据，而非聚簇索引只需要保存索引字段和主键值即可

• 022.非聚簇索引为什么不存数据地址值而存储主键？

  因为对数据的操作有时会引发索引重排，数据有可能会移动重新分布，数据地址往往比主键更占用空间，无法更好的利用索引空间，由于数据存储在聚簇索引里且利用的还是主键，为了更快速的匹配到需要的数据，从而提高查询效率

• 023.什么是回表操作？

  回表指的是通过索引字段为条件查询时找到叶子节点对应的主键再通过主键去聚簇索引上查找需要的数据

• 024.什么是覆盖索引？

  覆盖索引指的是查询的列只需要在索引上就可以找到，不需要回表查找

• 025.非聚集索引一定回表查询吗？

  不一定，使用非聚集索引查询数据时如果查询的字段在索引上就可以找到，就不需要回表查询了

• 026.为什么要回表查询？直接存储数据不可以吗？

  回表查询是因为需要查询的字段信息在索引B+树上不存在，需要先找到聚簇索引的主键再去聚餐索引的B+树上查找，聚簇索引只有叶子节点存放数据，非聚簇索引叶子节点存放聚簇索引主键ID,如果将所有数据直接存放到索引上，会多个，B+树的数据会冗余，且占用空间会非常大

• 027.如果把一个InnoDB表的主键删掉，是不是没有主键，就没办法进行回表查询？

  不是，如果把InnoDB表的主键删掉，引擎会从当前表找是否有非空唯一索引，如果没有就会生成一个隐式主键建立聚簇索引

• 028.什么是联合索引、组合索引、复合索引？

  首先这三种索引是一样的东西，只不过叫法不一样，都是通过多个字段组成索引的B+树，该索引在匹配时需要遵循最左匹配规则不然只能部分查询条件命中索引

• 029.复合索引创建时字段顺序不一样使用效果一样吗？

  不一样，创建顺序需要和查询条件顺序一致，不然不满足最左匹配原则就会部分使用索引或无法使用索引，对于联合索引在匹配的时候先根据最左边的索引字段从B+树上，查找找到满足条件的再依次匹配其他字段。所以创建索引字段的先后顺序会影响最先匹配数据的多少

• 030.什么是唯一索引？

  唯一索引就是，加索引的列不允许重复，但是是可以为null的并且可以多行为null

• 031.唯一索引是否影响性能？

• 032.什么时候使用唯一索引？

• 033.什么时候适合创建索引，什么时候不适合创建索引？

• 034.什么是索引下推？

• 035.有哪些情况会导致索引失效？

• 036.为什么LIKE以%开头索引会失效？

• 037.一个表有多个索引的时候，能否手动选择使用哪个索引？

• 038.如何查看一个表的索引？

• 039.能否查看到索引选择的逻辑？是否使用过optimizer_trace？

• 040.多个索引优先级是如何匹配的？

• 041.使用order by时能否通过索引排序？

• 042.通过索引排序内部流程是什么？

• 043.什么是双路排序和单路排序？

• 044.group by分组和order by在索引使用上有什么区别？

• 045.如果表中有字段为null，又被经常查询该不该给这个字段创建索引？

• 046.有字段为null索引是否会失效？

• 047.MySQL内部支持缓存查询吗？

• 048.MySQL8为何废弃掉查询缓存？

• 049.替代方案是什么？

• 050.MySQL内部有哪些核心模块组成，作用是什么？

• 051.说一下MySQL执行一条查询语句的内部执行过程？

• 052.MySQL提示“不存在此列”是执行到哪个节点报出的？

• 053.如果一张表创建了多个索引，在哪个阶段或模块进行的索引选择？

• 054.MySQL支持哪些存储引擎？默认使用哪个？

• 055.MySQL8.0自带哪些存储引擎？分别是做什么的？

• 056.MySQL存储引擎架构了解吗？

• 057.能否单独为一张表设置存储引擎？

• 058.阿里、京东等大厂都有自研的存储引擎，如何开发一套自己的？

• 059.MyISAM和InnoDB的区别是什么？

• 060.具体说一下如何做技术选型

• 061.什么是数据库事务？事务的特性是什么？

• 062.什么是ACID？

• 063.并发事务会有哪些问题？

• 064.并发事务会有哪些问题？脏读、丢失修改、不可重复读、幻读

• 069.MySQL是如何避免事物并发问题的？

• 074.MySQL事务隔离是如何实现的？

• 075.什么是一致性非锁定读和锁定读？

• 076.说一下MVCC内部细节

  • MVCC内部细节:
    • MVCC是多版本并发控制，然后主要是解决并发导致的问题，读读由于不对数据做修改所以不会出现问题，写写呢会加排他锁，为串行执行也不会产生并发问题,而读、写操作会导致并发问题（脏读、不可重复读、幻读）,为了做到非阻塞并发读不加锁方式使用MVCC
      • 首先事务的四种隔离级别为：
        • 读未提交（脏读、不可重复读、幻读）：不同事务可以读到其他未提交的数据
        • 读已提交（不可重复读、幻读）：不同事务可以读到其他事务已经提交的数据产生不可重复读
        • 可重复读：在一个事务中多次读同样的数据结果是一致的导致幻读
        • 串行化：多个事务排序执行
      • 串行化就是通过加锁实现事务串行执行
      • 读未提交：没有解决任何并发问题 - 为了解决读写导致的并发问题提出两种解决方案：
    • 加锁：都是当前读，读取的是记录最新的版本
    • MVCC：使用快照读，不加锁的select 操作就是快照读（快照读的前提是隔离级别不为串行化）
      • 串行化：无法使用快照读，串行化是通过加锁实现的
      • 可重复读：一个事务中最开始查询的SQL会产生一个readView，接下来再读使用的还是同一个readView，并不会产生新的
      • 读已提交：每次读都会生成一个readView导致再不同事务中可以读到其他事务提交的数据，即当前读，读取的都是最新的数据
    • MVCC实现原理：
      • MVCC是通过：隐式字段、undoLog链表、readView这三种实现多版本并发控制的
        • 隐式字段：每行数据系统都会把：DB_TRX_ID、DB_ROW_ID、DB_ROLL_PTR加到行格式里
          • DB_TRX_ID：当前记录最后一次写的事务ID
          • DB_ROLL_PTR：会指向当前记录的上一个版本undoLog中
          • DB_ROW_ID：隐式主键，如果没有自定义主键和非空唯一索引，就会给行ID加一个聚簇索引
        • undoLog记录链：

• 077.MySQL事务一致性、原子性、持久性是如何实现的？

• 095.表级锁和行级锁相关面试题

• 086.什么是XA协议，MySQL XA事务与普通事务区别是什么？2pc和3pc的区别？

• 090.是否使用过select for update？会产生哪些操作？

• 091.说一下MySQL死锁的原因和处理方法

• 092.MySQL会产生几种日志？

• 093.undo log、redo log、bin log的作用是什么？

• 096.MySQL日志是否实时写入磁盘？bin log刷盘机制是如何实现的？

• 098.redo log、undo log刷盘机制是如何实现的？数据脏页

• 100.MySQL的binlog有几种录入格式？分别有什么区别？

• 101.MySQL集群同步时为什么使用binlog？优缺点是什么？

• 102.文件存储 emoji相关面试题

• 106.如何存储IP地址？

• 107.长文本如何存储，如何设计表结构，如何建立索引？

• 111.日期，时间如何存取TIMESTAMP、DATETIME的区别，为什么不使用字符串存储日期？时间戳 timestamp和int该如何选择？

• 115.char与varchar的区别？如何选择？

• 116.财务计算有没有出现过错乱？

• 117.decimal与float、double的区别是什么？如何选型？

• 119.预编译SQL是什么？好处是什么？

• 121.JOIN多表关联与优化相关面试题

• 126.是否有过MySQL调优经验？用过哪些调优工具？

• 128.如何监控线上环境中执行比较慢的SQL？如何分析一条慢SQL？

• 130.如何查看当前SQL使用了哪个索引？EXPLAIN关键字中的重要指标有哪些？

• 133.MySQL数据库CPU飙升你会如何分析？

• 134.什么是分库分表？什么时候进行分库分表？有没有配合ES使用经验？

• 137.说一下实现分库分表工具的实现思路及读写分离方案

• 141.视图、外键、存储过程、processlist相关面试题

• 145.某个表有数千万数据，查询比较慢，如何优化？说下你的思路

• 146.count（列名）和count（星号）有什么区别

• 147.如果有超大分页该怎么处理？

• 148.MySQL服务器毫无规律的异常重启如何排查问题？

• 149.MySQL线上修改表结构有哪些风险？

  • 1.修改表结构会产生表级锁：这时候有大量数据库操作都会阻塞
  • 3.线上修改表结构需要提前预估每个使用该表的应用在表结构改动之后是否满足如：java程序如果表结构修改之后同时需要对应修改实体类、涉及SQL；定时任务或采集任务涉及的SQL需要对应修改

• 150.什么是MySQL多实例部署？

  • 多实例部署就是在同一个服务器上同时部署多个mysql服务监听不同的端口