MySQL基础面试题及答案（实操接地气版）

MySQL基础面试题及答案（实操接地气版）一、基础概念类（高频必问）1、MySQL中InnoDB和MyISAM的核心区别？答：日常开发最常用的就是这两个引擎，核心区别集中在事务、锁、索引和数据存储上：1）事务与日志：InnoDB支持事务、ACID特性、支持崩溃恢复；MyISAM不支持事务，崩溃后数据可能丢失、损坏。2）锁机制：InnoDB是行级锁（默认），并发性能高；MyISAM是表级锁，增删改都会锁整张表，并发差。3）外键：InnoDB支持外键约束，MyISAM不支持。4）存储文件：InnoDB数据和索引存在同一个文件；MyISAM数据、索引、表结构分三个文件存储。5）场景：日常业务、需要事务、高并发读写一律用InnoDB；只有纯静态、极少修改、只查数据的场景才用MyISAM。2、什么是事务？事务的四大特性（ACID）是什么？答：事务是数据库执行的一组SQL操作，要么全部执行成功，要么全部失败回滚，用来保证数据可靠。四大特性：原子性：事务内的操作不可分割，要么全成、要么全败。一致性：事务执行前后，数据库的数据完整性、约束规则不变。隔离性：多个事务并发执行时，互相之间不会干扰。持久性：事务提交成功后，数据永久保存，宕机也不会丢失。3、MySQL有哪些事务隔离级别？默认级别是什么？答：一共四级，从低到高：1）读未提交：能读到其他事务未提交的数据，基本不用，会出现脏读。2）读已提交：只能读到其他事务提交后的数据，解决脏读，会出现不可重复读。3）可重复读：同一个事务内，多次读取同一数据结果一致，解决脏读、不可重复读，会出现幻读。MySQLInnoDB默认隔离级别。4）串行化：最高级别，事务串行执行，无并发问题，但是性能极低，基本不用。4、脏读、不可重复读、幻读分别是什么？答：脏读：一个事务读到了另一个事务未提交的数据，一旦对方回滚，读到的数据就是无效脏数据。不可重复读：同一个事务内，两次查询同一条数据，期间被其他事务修改并提交，导致两次查询结果不一样。侧重数据修改。幻读：同一个事务内，两次范围查询，期间其他事务新增/删除数据，导致两次查询条数不一致。侧重数据新增、删除。5、InnoDB如何解决幻读问题？答：InnoDB的可重复读级别下，通过间隙锁+临键锁解决大部分幻读问题，不是完全杜绝，但是能满足绝大多数业务场景。真正彻底解决幻读需要串行化隔离级别。二、索引相关高频题1、什么是索引？索引的优缺点？答：索引是数据库给数据表建立的“目录”，用来快速定位数据。优点：大幅提升查询速度，减少数据库IO开销。缺点：占用磁盘空间；增、删、改数据时，需要同步更新索引，会降低写入性能。所以不是字段越多建索引越好。2、InnoDB主键索引和普通索引的区别？答：主键索引（聚簇索引）：叶子节点直接存整行数据，一张表只有一个聚簇索引，查询速度最快。普通索引（二级索引）：叶子节点只存索引列值+主键ID，查到主键后，需要再回表查整行数据，也就是回表查询。3、什么是回表查询？什么是覆盖索引？答：回表：通过普通索引查到主键ID后，需要再走一次主键索引获取完整数据，多一次IO，性能更低。覆盖索引：查询的所有字段，全部包含在索引中，不需要回表，直接从索引就能拿到全部数据，查询效率极高。日常优化优先做覆盖索引。4、最左前缀原则是什么？答：针对联合索引，查询条件必须匹配索引的最左侧连续字段，索引才会生效。比如联合索引(a,b,c)：wherea=1、wherea=1andb=2、wherea=1andb=2andc=3索引生效；whereb=2、wherec=3、whereb=2andc=3索引失效。5、哪些情况会导致索引失效？答：工作中最常见的几种：1）违背最左前缀原则；2）索引列使用函数、运算、类型转换，比如whereleft(name,2)='xx'、whereage+1=18；3）like以%开头，比如like'%张三'；4）or连接的字段有未建索引的列；5）MySQL判断全表扫描比索引更快时，会自动放弃索引（数据量少、重复度极高的字段）。三、SQL实操与优化基础1、limit分页深度大为什么慢？怎么优化？答：limit100000,10会先扫描前10万条数据，舍弃后再取10条，扫描数据量极大，所以慢。优化方案：1）用主键分页：whereid>100000limit10，利用主键索引快速定位；2）业务层面限制，不允许深度分页；3）延迟关联，先查主键再关联查询完整数据。2、union和unionall的区别？答：union：会自动去重、排序，性能差；unionall：直接拼接结果，不去重、不排序，性能远高于union。业务中能使用unionall就不用union，需要去重再用union。3、count(*)、count(1)、count(字段)区别？答：count(*)：统计所有行数，包含null、空行，InnoDB做了优化，效率很高；count(1)：和count(*)性能几乎一致，无区别；count(字段)：统计该字段非null的数据，会忽略null值，性能比前两者差。日常统计行数优先用count(*)。4、explain关键字是干嘛的？重点看哪些字段？答：explain用来查看SQL的执行计划，判断SQL是否走索引、查询效率如何，是SQL优化最常用的命令。重点看：type：访问类型，优先级system>const>eq_ref>ref>range>index>all，避免all（全表扫描）；key：实际命中的索引；rows：预估扫描行数，越小越好；Extra：是否出现Usingfilesort（文件排序）、Usingtemporary（临时表），出现就需要优化。四、锁与日志基础（入门高频）1、InnoDB行锁和表锁的使用场景？答：InnoDB行锁的前提是精准命中索引，且索引有效。精准通过索引锁定单条/多条数据，走行锁，并发高；如果没有索引、索引失效、范围查询过大，InnoDB会退化成表锁，并发性能骤降。2、MySQL常见日志有哪些？各自作用？答：1）redolog（重做日志）：保证事务持久性，宕机重启后，根据redolog恢复已提交事务的数据；2）undolog（回滚日志）：保证事务原子性，事务失败时回滚数据，同时实现MVCC；3）binlog（二进制日志）：记录所有修改数据的SQL，用于主从复制、数据恢复、日志审计。3、MVCC是什么？作用是什么？答：MVCC多版本并发控制，是InnoDB实现高并发的核心机制。作用：读写不加锁、读不加写锁，实现事务并发隔离，提升数据库并发性能，也是可重复读隔离级别的实现基础。五、日常实操易错面试题1、主键为什么建议用自增ID，不用UUID？答：1）自增ID有序，索引写入时不会频繁分裂、移位，写入性能高；2）UUID无序、字符串长，占用索引空间大，索引碎片多，查询和写入性能都差；3）自增ID占用空间更小，检索效率更高。2、为什么不建议用null做字段默认值？答：1）nul