高频sql数据库测试面试题及答案

高频sql数据库测试面试题及答案1.请说明SQL语句的主要分类及典型场景SQL语句主要分为数据定义语言（DDL）、数据操作语言（DML）、数据控制语言（DCL）和事务控制语言（TCL）四类。DDL用于定义数据库对象结构，如CREATE（创建表/索引）、ALTER（修改表结构）、DROP（删除表），典型场景是初始化数据库表结构或调整字段类型。DML用于操作数据内容，包括SELECT（查询）、INSERT（插入）、UPDATE（修改）、DELETE（删除），例如从用户表中筛选月消费超过1000元的记录。DCL用于权限管理，如GRANT（授予权限）、REVOKE（回收权限），常见于控制开发人员仅能查询测试库但无法删除数据。TCL用于事务管理，如BEGINTRANSACTION（开启事务）、COMMIT（提交事务）、ROLLBACK（回滚事务），典型场景是电商订单支付时，同时扣减库存和提供订单的原子性操作。2.简述INNERJOIN、LEFTJOIN、RIGHTJOIN、FULLOUTERJOIN的区别及使用场景INNERJOIN（内连接）仅返回两张表中满足连接条件的交集数据，例如用户表（user）和订单表（order）通过user_id连接，只返回有对应订单的用户。LEFTJOIN（左连接）返回左表所有记录，右表无匹配时用NULL填充，适用于统计所有用户的订单数（包括未下单用户）。RIGHTJOIN（右连接）与左连接相反，返回右表所有记录，左表无匹配时用NULL，例如统计所有订单对应的用户信息（包括无用户记录的异常订单）。FULLOUTERJOIN（全外连接）返回左右表所有记录，无匹配时用NULL填充，实际中较少使用，常见于需要合并两个独立数据源的全量比对场景（如会员系统与积分系统的全量用户匹配）。需注意MySQL不支持FULLOUTERJOIN，可通过UNIONLEFTJOIN和RIGHTJOIN实现。3.子查询与CTE（公共表表达式）的核心差异及各自适用场景子查询是嵌套在主查询中的SELECT语句，结果作为主查询的条件或数据源，例如SELECTnameFROMuserWHEREage>(SELECTAVG(age)FROMuser)。CTE通过WITH子句定义临时结果集，可在后续查询中多次引用，语法为WITHcte_nameAS(SELECT...)SELECT...FROMcte_name。核心差异体现在：①作用域：子查询仅能在当前SQL中使用一次，CTE可被后续多个查询复用；②可读性：复杂嵌套子查询会降低代码可维护性，CTE通过命名临时结果集提升可读性；③性能：部分数据库（如PostgreSQL）对CTE做了优化，可能将其转换为子查询执行，但SQLServer中CTE默认不存储结果（除非显式使用MATERIALIZED）。适用场景：子查询适合简单过滤条件（如单字段比较），CTE适合多步骤分析（如分层统计：先计算各部门销售额，再计算部门占比）或需要多次引用同一中间结果的场景（如递归查询部门层级关系）。4.视图（VIEW）和表（TABLE）的本质区别是什么？何时选择创建视图？视图是虚拟表，不存储实际数据，其内容由定义的SELECT语句动态提供；表是物理存储结构，数据直接存储在磁盘中。本质区别在于数据存储方式：视图是查询结果的“快照定义”，表是数据的物理存储。选择创建视图的场景包括：①简化复杂查询：将多表连接、过滤条件封装为视图，供前端直接调用（如用户信息视图=用户表+地址表+联系方式表的JOIN）；②权限控制：通过视图限制用户只能访问部分字段（如只暴露员工表的姓名和岗位，隐藏薪资）；③兼容旧系统：当表结构变更时，通过视图保持旧查询接口不变（如原表新增字段，视图仍返回原字段列表）；④临时结果复用：避免重复编写相同查询逻辑（如每月需要的销售统计视图）。需注意视图本身不提升查询性能，若频繁访问视图，可考虑物化视图（部分数据库支持，如Oracle），其会物理存储视图结果并定期刷新。5.解释窗口函数（WindowFunction）的核心作用，举例说明RANK()、DENSE_RANK()、ROW_NUMBER()的区别窗口函数用于对查询结果的特定分区（PARTITIONBY）进行计算，不改变原有行数，常见于排名、累加、移动平均等场景。核心作用是在不分组的情况下对数据进行聚合计算，例如统计每个部门内员工的薪资排名。RANK()：相同值赋予相同排名，下一个排名跳过重复数（如薪资8000、8000、7000，排名为1、1、3）；DENSE_RANK()：相同值赋予相同排名，下一个排名连续（如上述情况排名为1、1、2）；ROW_NUMBER()：为每个行分配唯一序号，即使值相同也不重复（如上述情况排名为1、2、3）。示例：员工表（emp_id,dept_id,salary），计算各部门薪资排名：WITHemp_salaryAS(SELECTdept_id,salary,RANK()OVER(PARTITIONBYdept_idORDERBYsalaryDESC)ASrk,DENSE_RANK()OVER(PARTITIONBYdept_idORDERBYsalaryDESC)ASdrk,ROW_NUMBER()OVER(PARTITIONBYdept_idORDERBYsalaryDESC)ASrnFROMemployee)SELECTFROMemp_salaryWHEREdept_id=10;若部门10有薪资10000、10000、9000，则rk为1、1、3；drk为1、1、2；rn为1、2、3。6.存储过程（StoredProcedure）和函数（Function）的主要区别及使用场景存储过程和函数均是预编译的SQL代码块，但核心区别在于：①返回值：存储过程可通过OUT参数返回多个值或无返回，函数必须返回单一值；②调用方式：存储过程用CALL调用，函数可在SELECT语句中直接使用；③用途：存储过程侧重执行一系列操作（如事务性操作），函数侧重计算并返回结果。使用场景示例：存储过程适用于订单提供（同时插入订单表、扣减库存、记录日志），通过参数传入用户ID和商品ID，通过OUT参数返回订单号和错误码；函数适用于计算年龄（输入生日，返回当前年龄），可在SELECTuser_id,calc_age(birthday)FROMuser中调用。需注意存储过程在复杂逻辑中可能导致数据库负载增加，应避免过度使用；函数若包含复杂逻辑（如循环）可能影响查询性能，需结合具体数据库优化策略（如PostgreSQL的LANGUAGEPL/pgSQL）。7.列举索引失效的5种常见场景，并说明如何避免（1）对索引列使用函数或表达式：如WHERESUBSTRING(username,1,2)='AB'，会导致索引无法使用。应改为WHEREusernameLIKE'AB%'（若索引是username）。（2）索引列类型不匹配：如字段是VARCHAR(20)，查询时用数字（如WHEREuser_id=123），数据库会隐式转换为字符串'123'，可能导致索引失效。应保持查询条件与字段类型一致（如WHEREuser_id='123'）。（3）复合索引未遵循左前缀法则：复合索引（col1,col2,col3）支持col1、col1+col2、col1+col2+col3的查询，但无法支持仅col2或col2+col3的查询。设计索引时需按查询频率高的字段顺序排列（如高频查询条件为col1和col1+col2，则索引顺序为col1,col2）。（4）使用OR条件连接不同索引列：如WHEREcol1='A'ORcol2='B'，若col1和col2分别有独立索引，数据库可能无法同时使用两个索引（需看执行计划）。可改为UNION（如SELECTFROMtableWHEREcol1='A'UNIONSELECTFROMtableWHEREcol2='B'），但需注意UNION去重，UNIONALL会保留重复。（5）LIKE查询以通配符开头：如WHEREusernameLIKE'%AB'，索引无法有效利用（B+树按前缀排序）。若业务需要前缀模糊查询，可调整为LIKE'AB%'；若必须后缀或中间匹配，可考虑全文索引（如MySQL的FULLTEXT）或使用搜索引擎（如Elasticsearch）。其他场景包括：索引列使用ISNULL（部分数据库支持NULL索引，如PostgreSQL）、数据分布不均（如索引列90%为同一值，数据库可能放弃索引）、事务未提交导致索引锁（如长时间未提交的写事务阻塞读索引）。8.事务的ACID特性分别指什么？如何通过日志机制保证原子性和持久性？ACID特性：原子性（Atomicity）：事务中的操作要么全部成功，要么全部回滚；一致性（Consistency）：事务执行前后数据库状态保持合法（如转账后总金额不变）；隔离性（Isolation）：多个事务并发执行时互不干扰；持久性（Durability）：事务提交后数据永久保存，即使数据库崩溃也可恢复。原子性通过undolog（回滚日志）保证：执行事务时，先记录旧值到undolog，若事务失败，根据undolog回滚到事务前状态。持久性通过redolog（重做日志）保证：事务提交时，先将redolog写入磁盘（WAL，Write-AheadLogging），再更新数据页；若数据库崩溃，重启时通过redolog重新执行已提交但未写入数据页的操作。例如MySQL的InnoDB引擎，redolog是ib_logfile文件，undolog存储在回滚段（rollbacksegment）中。9.简述数据库隔离级别及各自解决的并发问题SQL标准定义了4种隔离级别，从低到高：（1）读未提交（READUNCOMMITTED）：允许事务读取其他事务未提交的数据（脏读）。例如事务A更新余额为1000但未提交，事务B读取到1000，随后事务A回滚，事务B读取的是脏数据。（2）读已提交（READCOMMITTED，RC）：只读取已提交的数据，解决脏读，但可能出现不可重复读（同一事务两次查询结果不同）。例如事务A第一次查询余额为1000，事务B提交更新余额为2000，事务A第二次查询得到2000，两次结果不一致。（3）可重复读（REPEATABLEREAD，RR）：同一事务内多次查询结果一致，解决不可重复读，但可能出现幻读（查询范围新增/删除记录）。例如事务A查询ID=1-10的订单（10条），事务B插入ID=11的订单并提交，事务A再次查询ID=1-11的订单得到11条，出现幻读。（4）串行化（SERIALIZABLE）：事务串行执行，解决所有并发问题（脏读、不可重复读、幻读），但性能最低。MySQLInnoDB默认隔离级别是可重复读（RR），通过MVCC（多版本并发控制）实现：为每行记录维护多个版本，读操作访问历史版本（快照），写操作提供新版本。InnoDB在RR级别通过间隙锁（GapLock）防止幻读，例如查询IDBETWEEN10AND20时，会锁定(10,20)的间隙，阻止其他事务插入该区间的记录。10.如何定位并优化慢查询？请描述具体步骤步骤1：开启慢查询日志。通过SETGLOBALslow_query_log='ON'；设置长查询时间（如超过2秒）SETGLOBALlong_query_time=2；指定日志文件路径SETGLOBALslow_query_log_file='/var/log/mysql/slow.log'。步骤2：分析慢查询日志。使用工具（如pt-query-digest）解析日志，识别高频、高耗时的SQL语句，关注执行次数（Count）、平均执行时间（AvgTime）、锁等待时间（LockTime）、扫描行数（RowsExamined）与返回行数（RowsSent）的比值（比值大说明全表扫描）。步骤3：查看执行计划。对慢SQL执行EXPLAIN，重点关注：type：表示访问类型，从优到劣为system>const>eq_ref>ref>range>index>ALL（ALL表示全表扫描，需优化）；key：实际使用的索引，若为NULL表示未使用索引；rows：数据库估计扫描的行数，值越大性能越差；Extra：包含额外信息，如Usingfilesort（文件排序，需添加索引优化排序）、Usingtemporary（使用临时表，可能需调整GROUPBY或SELECT字段）。步骤4：优化策略。索引优化：对WHERE、JOIN、ORDERBY、GROUPBY涉及的字段添加索引（注意复合索引顺序）；避免全表扫描：检查是否有索引失效场景（如函数操作、类型转换），调整查询条件；减少扫描行数：通过缩小查询范围（如添加更严格的WHERE条件）、使用覆盖索引（索引包含所有查询字段，避免回表）；优化排序和分组：确保ORDERBY和GROUPBY使用索引，避免Usingfilesort和Usingtemporary；拆分复杂查询：将多表连接拆分为多个单表查询（利用应用层缓存），或使用子查询替代连接；升级硬件或分库分表：若数据量极大（如亿级），考虑水平分表（按时间或ID哈希）、垂直分表（拆分大字段）或分库（按业务模块拆分）。示例：慢查询为SELECT,o.amountFROMuseruJOINorderoONu.id=o.user_idWHEREu.regist_time>'2023-01-01'ORDERBYo.create_timeDESCLIMIT100;执行计划显示type=ALL（全表扫描），key=NULL（未用索引）。优化方案：为user表的regist_time添加索引（加速WHERE条件），为order表的user_id和create_time添加复合索引（user_id,create_timeDESC），覆盖JOIN条件和排序，避免回表。11.悲观锁和乐观锁的区别及适用场景悲观锁假设并发冲突概率高，通过加锁强制独占资源，常见实现是SELECT...FORUPDATE（行锁）。例如电商库存扣减：BEGIN;SELECTstockFROMproductWHEREid=123FORUPDATE;UPDATEproductSETstock=stock-1WHEREid=123;COMMIT;此过程其他事务需等待锁释放才能修改同一行。乐观锁假设冲突概率低，通过版本号或时间戳实现无锁并发控制。例如添加version字段：UPDATEproductSETstock=stock-1,version=version+1WHEREid=123ANDversion=5;若更新行数为0，说明版本已变，需重试。适用场景：悲观锁适用于冲突频繁（如热门商品库存）、数据一致性要求高的场景；乐观锁适用于冲突较少（如用户资料修改）、性能要求高的场景。需注意悲观锁可能导致死锁（如事务A锁行1，事务B锁行2，互相等待对方锁），需通过优化锁顺序（按ID升序加锁）、设置锁超时（innodb_lock_wait_timeout）避免；乐观锁需处理重试逻辑（如前端提示“数据已修改，请刷新后重试”）。12.如何处理数据库主从同步延迟问题？主从同步延迟指主库提交事务后，从库未及时同步，导致查询从库时读取到旧数据。常见原因及解决方法：（1）主库写入压力大：主库执行大量写操作（如批量插入），从库SQL线程（负责重放二进制日志）处理速度跟不上。解决：优化主库SQL（如批量插入改为分批），增加从库数量（分担读压力），或升级从库硬件（如更快的CPU、SSD）。（2）从库硬件性能差：从库磁盘IO或CPU性能低于主库，导致重放日志慢。解决：确保从库与主库硬件配置一致，或使用半同步复制（主库等待至少一个从库确认后再提交，牺牲部分性能换取一致性）。（3）大事务或长事务：主库执行长事务（如一次性更新10万条记录），二进制日志事件大，从库重放耗时。解决：拆分大事务为多个小事务（如每1000条提交一次），避免在主库执行长时间运行的查询。（4）网络延迟：主从库跨机房部署，网络延迟高导致日志传输慢。解决：使用低延迟网络（如专线），或调整复制方式（如使用物理复制替代逻辑复制，减少日志量）。（5）从库执行额外查询：从库被用于业务查询，影响SQL线程优先级。解决：限制从库的查询负载（如通过读写分离中间件控制），或使用独立从库用于查询（级联复制：主→从1→从2，从2仅用于查询）。监控同步延迟的方法：在从库执行SHOWSLAVESTATUS\G，查看Seconds_Behind_Master（主从延迟秒数），若持续大于0需排查原因。13.数据库连接数满的原因及应急处理原因：（1）应用程序未正确释放连接：如忘记关闭Connection或使用后未调用close()，导致连接池耗尽（如Tomcat的DBCP连接池默认最大连接数100）。（2）短连接过多：应用频繁创建/销毁连接（如每次请求都新建连接），超过最大连接数限制（max_connections，MySQL默认151）。（3）长事务未提交：事务长时间未提交，连接被占用（如SELECT...FORUPDATE后未COMMIT）。（4）连接池配置不合理：最大连接数设置过低（小于业务峰值需求），或超时时间（wait_timeout）设置过长（空闲连接未及时回收）。应急处理：（1）杀死空闲连接：通过SHOWPROCESSLIST查看状态为Sleep的连接，执行KILL[thread_id]释放（注意避免杀死正在执行的事务）。（2）临时调整最大连接数：SETGLOBALmax_connections=500（需数据库有足够内存支持）。（3）重启应用释放连接：若应用连接泄漏，重启可回收连接（临时方案，需修复代码）。长期优化：（1）优化应用代码：确保连接使用后关闭（如try-with-resources自动关闭），使用连接池（如HikariCP）管理连接。（2）调整连接池参数：设置合理的最大连接数（根据业务峰值，一般为CPU核心数×2）、最小空闲连接数、超时时间（wait_timeout设为600秒，避免空闲连接过多）。（3）监控连接使用：通过Prometheus+Grafana监控连接池状态（活跃连接数、空闲连接数），设置告警（如活跃连接数超过80%时预警）。14.简述数据库备份与恢复的常见策略备份策略需结合业务需求（RPO，恢复点目标；RTO，恢复时间目标）：（1）全量备份：定期（如每天凌晨）备份整个数据库（如MySQL的mysqldump或物理备份工具PerconaXtraBackup），优点是恢复简单（直接还原全量备份），缺点是文件大、备份时间长。（2）增量备份：基于全量备份，备份自上次备份以来的变更（如MySQL的二进制日志binlog），优点是文件小、备份频率高（每分钟或每秒），缺点是恢复时需按顺序应用全量备份+增量日志。（3）差异备份：备份自上次全量备份以来的所有变更，介于全量和增量之间（如每天做差异备份，每周做全量），恢复时只需全量+最后一次差异备份，无需应用所有增量。恢复步骤（以MySQL为例）：①停止数据库服务，防止新写入；②还原最近的全量备份（如使用XtraBackup的--copy-back参数）；③应用二进制日志（通过mysqlbinlog工具重放binlog到故障时间点）；④启动数据库，验证数据一致性。注意事项：备份文件需存储在异地（如云存储），防止机房故障；定期测试恢复流程（如每月模拟恢复一次），确保备份可用；对于关键业务，可结合主从复制（从库作为实时备份）和备份文件，缩短RTO。15.CHAR和VARCHAR的区别及选择依据CHAR是固定长度字符串类型（如CHAR(10)），存储时自动填充空格（尾部空格），检索时去除填充空格；VARCHAR是可变长度字符串类型（如VARCHAR(255)），存储实际长度+内容（MySQL5.0.3+后，VARCHAR(255)存储需1字节长度前缀，超过255需2字节）。选择依据：（1）固定长度场景选CHAR：如性别（'M'/'F'）、状态码（'0'/'1'），固定长度5的字段用CHAR(5)比VARCHAR(5)更节省空间（无需存储长度前缀），且访问速度更快（定长记录便于分页）。（2）可变长度场景选VARCHAR：如用户名（长度1-20）、地址（长度变化大），避免固定长度造成的空间浪费（如CHAR(20)存储"abc"会浪费17字节）。（3）需注意MySQL的ROW_FORMAT：在COMPACT行格式下，VARCHAR的NULL值不占用空间（用NULL标志位表示），而CHAR的NULL值会占用空间（如CHAR(10)的NULL实际存储10字节+NULL标志位）；另外，VARCHAR的最大长度受限于行最大长度（InnoDB默认65535字节，需考虑字符集：UTF-8每个字符3字节，VARCHAR(21844)是上限）。16.主键（PRIMARYKEY）和唯一索引（UNIQUEINDEX）的核心区别（1）约束性：主键自动添加NOTNULL约束（不允许NULL值），唯一索引允许NULL值（但NULL值不唯一，多个NULL视为不同）；（2）数量限制：一个表只能有一个主键，可有多条唯一索引；（3）存储方式：主键默认是聚簇索引（InnoDB），数据按主键顺序存储在B+树中；唯一索引通常是非聚簇索引（存储主键值作为回表指针）；（4）性能影响：主键作为聚簇索引，查询时效率更高（无需回表）；唯一索引若为覆盖索引（包含所有查询字段），性能与主键接近。示例：用户表中，user_id作为主键（NOTNULL且唯一），email字段添加唯一索引（允许NULL，但每个非NULL的email必须唯一）。17.外键（FOREIGNKEY）的优缺点及替代方案优点：强制数据一致性：子表插入记录时，外键字段值必须存在于主表的主键中（如订单表的user_id必须存在于用户表的user_id）；级联操作：可定义级联删除（CASCADE）或级联更新（SETNULL），例如主表删除用户时，子表订单自动删除（ONDELETECASCADE）。缺点：性能开销：插入/更新/删除子表时需检查主表，高并发场景可能成为瓶颈；死锁风险：跨表操作可能导致循环依赖（如A表外键引用B表，B表外键引用A表），引发死锁；扩展性差：分库分表时，外键无法跨库约束（因主表和子表可能分布在不同数据库）。替代方案：应用层校验：在插入订单时，先查询用户表是否存在该user_id（需保证原子性，可通过事务+锁实现）；触发器（Trigger）：在子表插入时触发检查（如BEFOREINSERT触发器查询主表是否存在），但触发器可能影响性能；定期数据清洗：通过定时任务（如每日凌晨）检查子表外键字段是否存在主表记录，删除无效数据（适用于一致性要求不高的场景）。实际中，互联网高并发系统通常不使用外键，而是通过应用层逻辑和异步任务保证一致性；传统企业系统（如ERP）因数据一致性要求高，可能使用外键。18.如何防范SQL注入攻击？SQL注入是由于应用程序未正确过滤用户输入，导致恶意SQL被执行（如输入"1'OR'1'='1"导致WHEREid=1'OR'1'='1，返回所有记录）。防范措施：（1）使用预编译语句（PreparedStatement）：参数通过占位符（?）传递，数据库将SQL语句和参数分开处理，避免字符串拼接。例如Java中：Stringsql="SELECTFROMuserWHEREusername=?ANDpassword=?";PreparedStatementpstmt=conn.prepareStatement(sql);pstmt.setString(1,username);pstmt.setString(2,password);（2）输入校验：对用户输入进行类型、长度、正则表达式校验（如邮箱必须符合xxx@xxx.xxx格式），拒绝非法字符（如单引号、分号）。（3）最小权限原则：数据库用户仅授予必要权限（如应用连接数据库使用只读用户，禁止DROP、DELETE等危险操作）。（4）ORM框架防护：使用Hibernate、MyBatis等ORM框架，其默认使用预编译语句，降低注入风险（需避免手动拼接SQL）。（5）转义特殊字符：若必须拼接SQL（如动态查询），对单引号（'）转义为两个单引号（''），分号（;）转义为其他字符，或使用数据库提供的转义函数（如MySQL的QUOTE()）。示例：用户输入username为"admin'--"，拼接SQL为"SELECTFROMuserWHEREusername='admin'--'ANDpassword='xxx'"，--表示注释，导致密码校验被跳过。使用预编译语句可避免此问题。19.大数据量下，如何优化分页查询（LIMITNOFFSETM）？传统分页使用LIMITMOFFSETN，当N很大时（如OFFSET100000），数据库需扫描前100000条记录，效率极低。优化方法：（1）覆盖索引+记录上次位置：利用覆盖索引（包含排序字段和主键），记录上一页最后一条的主键值，通过WHERE主键>上次值LIMIT页数。例如按id升序分页：第一页：SELECTid,nameFROMu