MySQL SQL 高频面试题及详细答案

MySQLSQL高频面试题及详细答案说明：本套试题整理互联网后端、测试、数据岗位高频MySQL面试考点，涵盖基础语法、数据库设计、索引原理、事务机制、锁机制、SQL优化等核心内容，题目贴合真实面试场景，答案详细易懂，适合面试刷题、技术复盘。一、基础概念面试题（10题）1、简述MySQL中数据库、数据表、字段的关系？答案：三者是层级包含关系。数据库是存储数据表的容器，一个数据库可以包含多张数据表；数据表是结构化数据的载体，用于存储具体业务数据；字段是数据表中的列，代表数据的属性，每一行数据对应一条完整的业务记录。简单来说：数据库>数据表>字段，层层嵌套，构成MySQL的数据存储结构。2、MySQL常用的数据类型有哪些？各自适用场景是什么？答案：主要分为数值类型、字符串类型、日期时间类型三大类：1.数值类型：int（整型，适用于ID、数量、年龄等整数数据）、bigint（长整型，适用于大数值ID、流水号）、decimal（定点小数，适用于金额、汇率，精度可控，无浮点误差）、float/double（浮点型，适用于非高精度小数数据）；2.字符串类型：varchar（可变长度字符串，适用于用户名、地址、备注，节省存储空间）、char（固定长度字符串，适用于手机号、身份证号等长度固定数据）、text（长文本，适用于文章内容、详细备注）；3.日期时间类型：date（仅日期，年月日）、time（仅时间，时分秒）、datetime（日期+时间，常用存储业务时间）、timestamp（时间戳，自动更新，适用于记录数据修改时间）。3、char和varchar的核心区别是什么？答案：1.存储方式：char是固定长度，定义多长就占用多少存储空间；varchar是可变长度，仅存储实际数据长度，额外占用1-2字节记录长度；2.性能差异：char存取速度更快，无需计算长度，但容易浪费存储空间；varchar节省空间，存取效率略低；3.尾部空格：char会自动截断尾部空格，varchar会保留尾部空格；4.使用场景：固定长度数据用char，可变长度数据用varchar。4、MySQL中null和空字符串''的区别？答案：1.含义不同：null代表无数据、未知数据；空字符串''代表存在数据，只是内容为空；2.存储不同：null需要额外占用存储空间标记空值；空字符串仅占用本身长度空间；3.查询判断不同：null不能用=、!=判断，只能用isnull、isnotnull；空字符串可以直接用=''、!=''判断；4.索引影响：null值可以被索引，空字符串也可被索引，但业务中尽量避免大量null值，容易导致查询逻辑出错。5、简述MySQL约束类型及作用？答案：MySQL共有5种常用约束，用于保证数据完整性和一致性：1.主键约束（primarykey）：唯一且非空，一张表只能有一个主键，用于唯一标识每条数据；2.唯一约束（unique）：数据唯一，但允许为空，一张表可设置多个唯一约束；3.非空约束（notnull）：字段数据不能为空，必须赋值；4.默认约束（default）：字段未赋值时，自动填充默认值；5.外键约束（foreignkey）：关联两张表，保证关联数据的一致性，防止出现脏数据。6、主键和唯一索引的区别？答案：1.约束差异：主键默认非空+唯一，唯一索引仅保证唯一，允许为空；2.数量限制：一张表只能有一个主键，可创建多个唯一索引；3.索引类型：主键默认创建聚簇索引，唯一索引默认创建二级索引；4.业务作用：主键用于唯一标识数据行，唯一索引用于限制字段数据重复。7、MySQL中delete、truncate、drop的区别？答案：1.delete：删除表中部分或全部数据，保留表结构，可加where条件，支持事务回滚，速度最慢；2.truncate：清空整张表所有数据，保留表结构，无where条件，不支持事务回滚，重置自增主键，速度快；3.drop：直接删除整张表，包括数据和表结构，释放所有存储空间，不可恢复，速度最快；总结：删数据用delete/truncate，删表用drop。8、什么是自增主键？自增主键的优缺点？答案：自增主键是通过auto_increment设置的整型主键，新增数据时自动递增生成唯一ID。优点：ID有序、索引紧凑、查询效率高，无需手动赋值；缺点：分库分表场景下会出现ID冲突，高并发场景可能存在主键空洞。9、MySQL存储引擎有哪些？默认引擎是什么？答案：常用存储引擎有InnoDB、MyISAM、Memory等，MySQL5.5及以上版本默认引擎为InnoDB。1.InnoDB：支持事务、行锁、外键、崩溃恢复，数据安全性高，适用于绝大多数业务场景；2.MyISAM：不支持事务和行锁，仅支持表锁，查询速度快，适用于只读、查询多修改少的场景；3.Memory：数据存储在内存中，速度极快，断电数据丢失，适用于临时数据缓存。10、InnoDB和MyISAM的核心区别？答案：1.事务支持：InnoDB支持事务，MyISAM不支持；2.锁机制：InnoDB支持行级锁+表级锁，并发性能高；MyISAM仅支持表级锁，并发差；3.外键：InnoDB支持外键，MyISAM不支持；4.崩溃恢复：InnoDB支持故障自动恢复，MyISAM容易数据损坏；5.适用场景：InnoDB用于增删改查频繁、需要事务的业务；MyISAM用于纯查询静态数据。二、SQL查询高频面试题（8题，含实操语句）前提数据表：学生表student（id主键，name姓名，age年龄，gender性别，class_id班级ID）班级表class（id主键，class_name班级名称）成绩表score（id主键，student_id学生ID，subject科目，score分数）1、查询所有年龄大于18岁的男生学生信息答案语句：SELECT*FROMstudentWHEREage>18ANDgender='男';2、查询每个班级的学生人数答案语句：SELECTc.class_name,COUNT(s.id)ASstudent_num

FROMclasscLEFTJOINstudentsONc.id=s.class_id

GROUPBYc.id,c.class_name;3、查询每门科目的平均分、最高分、最低分答案语句：SELECTsubject,AVG(score)ASavg_score,MAX(score)ASmax_score,MIN(score)ASmin_score

FROMscore

GROUPBYsubject;4、查询平均分大于80分的学生ID和平均分答案语句：SELECTstudent_id,AVG(score)ASavg_score

FROMscore

GROUPBYstudent_id

HAVINGAVG(score)>80;解析：分组后条件过滤必须使用having，where只能用于分组前过滤。5、查询所有学生的姓名、班级名称、各科成绩（多表联查）答案语句：SELECT,c.class_name,sc.subject,sc.score

FROMstudents

JOINclasscONs.class_id=c.id

JOINscorescONs.id=sc.student_id;6、查询没有考试成绩的学生姓名答案语句：SELECT

FROMstudents

LEFTJOINscorescONs.id=sc.student_id

WHEREsc.idISNULL;7、查询排名前三的学生总分（分数相同并列）答案语句：SELECTstudent_id,SUM(score)AStotal_score

FROMscore

GROUPBYstudent_id

ORDERBYtotal_scoreDESC

LIMIT3;8、去重查询所有选修的科目名称答案语句：SELECTDISTINCTsubjectFROMscore;三、索引核心面试题（8题）1、什么是索引？索引的作用？答案：索引是数据库为数据表字段建立的排序数据结构，类似于书籍的目录。核心作用是大幅提升数据查询效率，快速定位数据行，避免全表扫描。同时也存在弊端：会占用磁盘空间，增删改数据时需要维护索引，降低写入效率。2、MySQL索引的分类有哪些？答案：1.按结构分：聚簇索引、二级索引（普通索引）；2.按功能分：主键索引、唯一索引、普通索引、联合索引、全文索引；3.按存储分：B+树索引、哈希索引。InnoDB默认使用B+树索引。3、聚簇索引和非聚簇索引的区别？答案：1.聚簇索引：叶子节点直接存储整行数据，主键索引就是聚簇索引，一张表只有一个聚簇索引；2.非聚簇索引（二级索引）：叶子节点仅存储索引字段值和主键ID，查询数据需要通过主键回表查询完整数据；3.查询效率：聚簇索引查询速度更快，无需回表；二级索引可能产生回表操作。4、什么是联合索引？最左前缀原则是什么？答案：联合索引是对多个字段建立的复合索引。最左前缀原则是联合索引的核心规则：查询条件必须匹配索引的最左侧字段，才能命中索引。例如：建立索引(a,b,c)，查询wherea=?、wherea=?andb=?、wherea=?andb=?andc=?可以命中索引；仅查询b=?、c=?无法命中索引。5、什么是回表查询？如何避免回表？答案：通过二级索引查询到主键ID后，需要再次通过聚簇索引查询完整行数据，这个过程就是回表，会增加查询耗时。避免方式：使用覆盖索引，让查询的所有字段都包含在二级索引中，无需回表查询。6、什么是覆盖索引？有什么优点？答案：覆盖索引是指查询所需的全部字段都存在于索引中，数据库直接通过索引获取所有数据，无需回表。优点是减少IO操作，大幅提升查询性能，是日常优化中常用的手段。7、哪些情况会导致索引失效？答案：高频失效场景：1.索引字段使用函数、运算操作（如left(name)、age+1）；2.索引字段隐式类型转换（字符串字段传入数字、数字字段传入字符串）；3.使用!=、<>、notin、isnotnull查询；4.like查询以%开头（如%张三）；5.or连接的条件中，有字段无索引；6.违反联合索引最左前缀原则。8、索引是不是越多越好？为什么？答案：不是。索引有利有弊，过多索引会带来严重问题：1.占用大量磁盘存储空间；2.数据表增、删、改数据时，需要同步更新所有索引，大幅降低写入性能；3.优化器选择索引耗时增加，可能导致查询效率下降。业务中只需要对高频查询、筛选、排序、分组字段建立索引即可。四、事务与锁机制面试题（6题）1、什么是数据库事务？事务的四大特性（ACID）？答案：事务是一组不可分割的SQL操作单元，要么全部执行成功，要么全部执行失败回滚。四大特性：1.原子性（Atomicity）：事务整体不可拆分，全部成功或全部失败；2.一致性（Consistency）：事务执行前后，数据库数据状态保持合法一致；3.隔离性（Isolation）：多个事务并发执行时，相互隔离、互不干扰；4.持久性（Durability）：事务提交成功后，数据永久保存，故障不丢失。2、MySQL事务的四大隔离级别？默认级别是什么？答案：隔离级别由低到高：1.读未提交：可读取未提交数据，存在脏读、不可重复读、幻读问题；2.读已提交：只能读取已提交数据，解决脏读，存在不可重复读、幻读；3.可重复读：同一事务多次读取数据结果一致，解决脏读、不可重复读，存在幻读（MySQL默认隔离级别）；4.串行化：最高隔离级别，事务串行执行，解决所有并发问题，性能最低。3、脏读、不可重复读、幻读的区别？答案：1.脏读：一个事务读取到另一个事务未提交的修改数据，数据无效；2.不可重复读：同一事务内，两次读取同一数据，结果不一致（被其他事务修改并提交）；3.幻读：同一事务内，两次查询数据行数不一致（被其他事务新增/删除数据）。4、InnoDB的锁机制有哪些？答案：1.按粒度分：行级锁、表级锁、意向锁；2.按类型分：共享锁（S锁，读锁，可读不可写，共享兼容）、排他锁（X锁，写锁，独占，读写互斥）；InnoDB默认使用行级锁，仅锁定操作数据行，并发性能高；无索引条件下会升级为表锁。5、什么是死锁？如何避免死锁？答案：死锁是两个或多个事务互相持有对方需要的锁，同时等待对方释放锁，导致事务永久阻塞。避免方式：统一事务锁获取顺序、缩短事务执行时间、避免大事务、合理设置超时时间。6、行锁什么时候会升级为表锁？答案：核心场景：SQL语句无索引或索引失效时，InnoDB无法精准锁定数据行，会锁定整张数据表，行锁升级为表锁，严重影响并发性能。五、SQL优化面试题（4题）1、日常SQL优化的核心思路是什么？答案：1.优先优化索引，避免索引失效，合理建立联合索引、覆盖索引；2.避免全表扫描，精准使用where条件过滤数据；3.避免select*，只查询需要的字段；4.大表分页优化、避免深分页；5.拆分大事务，减少锁等待；6.避免in、or、like%前缀等低效语法。2、如何优化深分页问题（limit100000,10）？答案：深分页会扫描大量无效数据，效率极低。优化方案：1.主键分页：通过whereid>偏移值+limit10，利用主键索引快速定位；2.延迟关联：先通过索引查询主键，再关联查询完整数据；3.业务层面限制，禁止