SQL DBA 面试题及详细答案（实战完整版）

SQLDBA面试题及详细答案（实战完整版）一、SQL基础与语法实战1、DELETE、TRUNCATE、DROP的区别？生产环境如何选型？参考答案：三者核心区别集中在日志、事务、数据结构、资源占用四个维度：1）DELETE：DML数据操作语句，用于删除表中部分或全部数据。支持事务回滚，会记录详细redo/undo日志，删除大量数据时会产生事务日志堆积、锁表/锁行，性能较慢。仅删除数据，保留表结构、索引、约束。2）TRUNCATE：DDL数据定义语句，清空整张表所有数据。不支持事务回滚（部分数据库新版本支持快照回滚，但原生无事务特性），日志记录极少，执行速度极快。只清空数据，保留表结构、字段、约束，但会重置自增主键。3）DROP：DDL语句，直接删除整张表，包含数据、表结构、索引、约束、权限，所有关联资源全部销毁，无法恢复（无简易回滚方式），仅可通过备份恢复。生产选型原则：需要删除部分数据、可回滚、业务可控→用DELETE；清空全表海量数据、无需回滚、保留表结构→用TRUNCATE；废弃无用表、彻底释放资源→用DROP。生产严禁随意执行DROP，操作前必须备份。2、WHERE、GROUPBY、HAVING、ORDERBY的执行顺序？参考答案：真实执行顺序（区别于书写顺序）：FROM/JOIN→WHERE→GROUPBY→聚合函数→HAVING→ORDERBY→LIMIT核心关键点：1）WHERE过滤分组前的数据，无法过滤聚合结果；2）GROUPBY完成数据分组后，才会执行SUM、COUNT、MAX等聚合函数；3）HAVING专门过滤分组后的聚合结果，是唯一可搭配聚合函数做条件过滤的子句；4）ORDERBY最后执行，对最终结果集排序，因此排序字段可以使用别名。3、UNION和UNIONALL的区别？生产如何选择？参考答案：1）UNION：合并结果集后，自动去重、排序，资源消耗大、执行效率低；2）UNIONALL：直接拼接结果集，不去重、不排序，性能远高于UNION，占用资源少。生产选型：业务允许重复数据、追求查询性能，一律使用UNIONALL；仅业务必须强制去重时，才使用UNION。绝大多数生产场景优先UNIONALL。二、索引原理与优化（DBA核心考点）1、MySQL常用索引类型及适用场景？参考答案：1）主键索引（聚簇索引）：每张表唯一，叶子节点存储整行数据，查询主键速度最快，用于唯一标识数据行；2）普通索引（二级索引）：叶子节点存储主键值，适用于普通字段查询、筛选、排序；3）唯一索引：字段值唯一、允许为空，用于手机号、身份证、订单号等唯一约束字段；4）联合索引：多个字段组合索引，遵循最左匹配原则，适用于多字段联合查询、筛选、排序场景；5）全文索引：针对大文本字段，用于模糊检索文章、内容详情，替代低效的%xxx%模糊查询。2、什么是联合索引最左匹配原则？失效场景有哪些？参考答案：联合索引(a,b,c)生效规则：查询条件必须包含最左侧的首个字段a，索引才会触发，后续字段按需匹配。常见索引失效场景：1）跳过最左字段，直接查询b、c字段，索引完全失效；2）最左字段使用范围查询（>、<、between），后续所有字段索引失效；3）索引字段参与运算、函数调用、隐式类型转换，索引失效；4）使用SELECT*、冗余字段过多，导致回表开销大于全表扫描，优化器放弃索引；5）OR连接非索引字段、like以%开头，索引失效。3、覆盖索引是什么？有什么作用？参考答案：覆盖索引指查询所需的所有字段，全部包含在索引中，查询时无需回表查询原始数据，直接通过索引即可拿到全部结果。核心作用：避免回表操作，大幅减少IO开销，提升查询性能，是日常SQL优化最常用的手段。执行计划中可通过Extra字段的Usingindex标识覆盖索引生效。4、索引不是越多越好，为什么？参考答案：索引可以提升查询效率，但会严重降低写入、更新、删除效率：1）数据表执行INSERT/UPDATE/DELETE时，不仅要修改数据，还要同步更新所有关联索引，索引越多，写入开销越大；2）索引会占用磁盘空间，海量数据场景下，冗余索引会大幅增加存储成本；3）优化器选择执行计划时，会遍历所有索引，冗余索引会增加解析耗时，可能导致选错索引。生产原则：优先保障高频查询，低频查询不建索引，定期清理冗余、失效索引。三、事务、锁与MVCC1、数据库事务四大特性ACID分别是什么？参考答案：1）原子性（Atomicity）：事务要么全部执行成功，要么全部回滚，不存在部分执行；2）一致性（Consistency）：事务执行前后，数据库数据完整性、约束规则保持一致，无非法数据；3）隔离性（Isolation）：多个事务并发执行时，相互隔离、互不干扰；4）持久性（Durability）：事务提交成功后，数据永久落地，宕机、重启不会丢失。2、MySQL四种事务隔离级别及并发问题参考答案：1）读未提交：可读取其他事务未提交数据，存在脏读、不可重复读、幻读，生产完全不用；2）读已提交（RC）：只能读取已提交数据，解决脏读，存在不可重复读、幻读，互联网业务常用；3）可重复读（RR，MySQL默认）：同一事务内多次读取数据一致，解决脏读、不可重复读，存在幻读；4）串行化：事务串行执行，完全解决三类并发问题，但并发性能极差，仅金融核心场景使用。核心问题区分：脏读：读取到未提交的脏数据；不可重复读：同一事务两次读取同一数据，结果不一致；幻读：同一事务范围内，其他事务新增/删除数据，导致前后查询行数不一致。3、InnoDB行锁、表锁、意向锁区别？什么场景触发全表锁？参考答案：1）表锁：锁定整张表，并发极低，MyISAM默认表锁；2）行锁：InnoDB独有，精准锁定数据行，并发性能高，基于索引生效；3）意向锁：表级辅助锁，分为意向共享、意向排他，用于快速判断表内是否有行锁，避免逐行检测。行锁变表锁核心场景：更新、删除条件无索引、索引失效、条件为范围查询匹配全部数据，InnoDB无法精准锁定行，会升级为全表锁，阻塞所有读写操作，是生产死锁、阻塞的高频原因。4、什么是MVCC？工作原理是什么？参考答案：MVCC即多版本并发控制，是InnoDB实现读写无锁、提升并发性能的核心机制，主要作用是让读操作不加锁，解决并发读写冲突。工作原理：1）数据表每行数据会存储隐藏字段：事务ID、回滚指针；2）数据更新时，会将旧数据存入undo日志，通过回滚指针形成数据版本链；3）事务启动时生成ReadView视图，通过视图规则匹配版本链，读取当前事务可见的最新数据版本；4）RC、RR隔离级别的差异，本质是ReadView生成时机不同，RC每次查询生成新视图，RR事务首次查询生成视图并复用。四、数据库备份与恢复1、MySQL常用备份方式及优缺点、适用场景参考答案：1）mysqldump（逻辑备份）：优点：跨版本、跨平台、压缩比高、备份文件小、操作简单；缺点：备份恢复速度慢，大库耗时久，备份期间会产生事务日志堆积；适用：中小库、日常定时备份、跨环境迁移。2）xtrabackup（物理备份）：优点：热备份、不锁表、速度快、支持增量备份，适配大库；缺点：仅支持InnoDB，备份文件大，跨环境兼容性一般；适用：几十G、上百G大库、生产7*24不间断业务。3）快照备份：基于磁盘快照，速度最快，依赖云服务或存储设备，适合超大型数据库。2、什么是全量备份、增量备份、差异备份？生产如何搭配？参考答案：1）全量备份：备份数据库所有数据，完整可靠，耗时久、占用空间大；2）增量备份：仅备份上一次任意备份后新增/修改数据，体积最小、速度最快，恢复需要叠加全量+所有增量；3）差异备份：仅备份上一次全量备份后的数据，体积和速度居中，恢复只需全量+最新差异。生产通用策略：每周1次全量备份，每日1次增量/差异备份，保留7天备份文件，同时开启binlog日志，支持任意时间点恢复。3、binlog作用是什么？三种日志格式区别？参考答案：binlog即二进制日志，记录数据库所有增删改操作，核心作用：数据恢复、主从复制、审计追溯。三种格式：1）STATEMENT：记录执行的SQL语句，日志体积小，部分函数、场景会导致主从数据不一致，基本淘汰；2）ROW：记录数据行变更前后明细，精准无误差，主从一致性高，日志体积大，生产主流使用；3）MIXED：混合模式，自动择优使用前两种格式，兼容性适中。五、主从复制与高可用1、MySQL主从复制原理？核心流程？参考答案：核心基于binlog日志同步数据，分为三步：1）主库：执行增删改操作，写入binlog日志；2）从库IO线程：连接主库，拉取主库binlog日志，写入本地relay中继日志；3）从库SQL线程：读取中继日志，回放执行SQL，同步主库数据。最终实现主库写、从库读，读写分离，分担主库压力。2、主从延迟的原因及解决方案？参考答案：常见原因：1）主库写入并发量大，从库单线程SQL线程回放速度跟不上主库写入速度；2）从库硬件配置低于主库，IO、CPU性能瓶颈；3）主库存在大批量DELETE、UPDATE、ALTER大事务，binlog日志量大，回放耗时久；4）从库存在慢查询、锁等待，阻塞日志回放。解决方案：1）开启从库并行复制，提升回放效率；2）优化大事务，拆分批量操作，避免一次性生成海量日志；3）统一主从硬件配置，优化磁盘IO；4）规避从库慢查询，定期优化低效SQL；5）核心业务读写分离路由优化，延迟敏感业务走主库查询。3、MySQL常见高可用方案及优缺点参考答案：1）主从单主架构：部署简单、维护成本低，故障需手动切换，无自动容灾，适合非核心业务；2）MHA高可用：自动故障检测、自动主从切换、秒级容灾，开源免费，适配中小集群，依赖手动补全日志，超大集群稳定性一般；3）MySQLInnoDBCluster：官方高可用方案，自动切换、数据强一致，部署复杂、学习成本高；4）云数据库高可用：云端一主多从、自动容灾、秒级切换，无需底层运维，成本较高，适合线上核心业务。六、慢查询优化与故障排查1、慢查询优化完整流程？参考答案：1）开启慢查询日志，设置阈值（默认1秒），捕获低效SQL；2）通过explain分析执行计划，定位问题：索引失效、全表扫描、临时表、文件排序；3）针对性优化：新建/调整索引、优化查询条件、避免函数运算、拆分大SQL、限制返回行数；4）优化大事务、避免锁等待，减少执行阻塞；5）优化后压测验证，监控执行耗时、QPS、IO指标，确认优化生效。2、explain核心字段含义（重点）参考答案：1）type：访问类型，性能优先级：system>const>eq_ref>ref>range>index>all，生产必须杜绝all全表扫描；2）key：本次查询实际使用的索引，为空则索引失效；3）rows：预估扫描行数，数值越小性能越好；4）Extra：Usingindex覆盖索引、Usingwhere条件过滤、Usingfilesort文件排序（需优化）、Usingtemporary临时表（需优化）。3、生产数据库CPU飙升、IO打满如何排查？参考答案：CPU飙升排查步骤：1）查看慢查询日志、showprocesslist，定位高频执行、耗时极高的SQL；2）排查是否存在大量全表扫描、索引失效、笛卡尔积查询；3）检查是否有大量锁等待、死锁、事务未提交；4）临时kill异常会话，紧急降载，后续优化SQL、新增索引。IO打满排查步骤：1）排查是否存在大批量写入、更新、删除大事务；2）查看binlog、redo、undo日志写入量，是否日志堆积；3）定位大量回表查询、全表扫描SQL，产生海量磁盘IO；4）优化大事务、拆分批量操作、优化索引、开启缓存，降低磁盘读写压力。七、日常运维与规范1、生产数据库禁止操作有哪些？参考答案：1）业务高峰期执行ALTER表结构、DROP、TRUNCATE高危操作；2）线上直接执行不带LIMIT的DELETE、UPDATE，防止误删全表数据；3）随意修改数据库参数、字符集、隔离级别；4）大表一次性批量更新、删除，引发大事务