2026年数据库性能优化与SQL调优笔试题目

2026年数据库性能优化与SQL调优笔试题目一、单选题（共10题，每题2分，共20分）1.在MySQL中，以下哪种索引类型最适合用于经常需要全文搜索的场景？A.普通索引B.唯一索引C.全文索引D.组合索引2.在Oracle数据库中，以下哪个参数主要用于控制内存中PL/SQL缓存的命中率？A.PGA_AGGREGATE_TARGETB.DB_CACHE_SIZEC.SQLnet.service_nameD.LOG_BUFFER3.当数据库中出现锁等待问题时，以下哪种方法最能有效识别锁的持有者？A.`EXPLAIN`语句B.`SHOWPROCESSLIST`（MySQL）C.`DBCCSQLPERF`（SQLServer）D.`ANALYZE`命令4.在SQLServer中，以下哪种索引优化策略适用于大量插入、删除操作的场景？A.超大表使用堆表B.创建覆盖索引C.使用索引提示D.索引分页5.当查询中存在多个JOIN操作时，以下哪种优化方法最能有效减少数据扫描量？A.增加索引B.使用子查询C.优化JOIN顺序D.使用临时表6.在PostgreSQL中，以下哪种方法可以用于临时提升特定查询的并行度？A.设置`work_mem`参数B.使用`EXPLAINANALYZE`C.调整`max_parallel_workers_per_query`D.创建并行索引7.在数据库分区表中，以下哪种场景最适合使用范围分区？A.按主键分区B.按时间范围分区C.按哈希分区D.按列表分区8.当查询中频繁使用LIKE'%keyword%'时，以下哪种优化方法最有效？A.创建全文索引B.使用普通索引C.将LIKE条件改为LIKE'keyword%'D.使用OR条件拆分查询9.在MySQL中，以下哪种方法可以用于减少事务日志的写入量？A.开启InnoDB_flush_log_at_trx_commit=2B.增加innodb_buffer_pool_sizeC.使用批量插入D.创建更复杂的索引10.在SQLServer中，以下哪种索引类型最适合用于高基数列（唯一值多）？A.B-Tree索引B.Hash索引C.Clustered索引D.Filtered索引二、多选题（共5题，每题3分，共15分）1.在数据库性能优化中，以下哪些方法可以有效减少慢查询？A.增加数据库缓存B.优化查询逻辑C.使用分区表D.增加硬件资源E.忽略低频查询2.当数据库出现死锁时，以下哪些方法可以用于排查？A.查看系统表（如MySQL的`INNODB_TRX`）B.使用`DBCCSQLLOCK`（SQLServer）C.调整事务隔离级别D.禁用索引E.增加事务超时时间3.在SQLServer中，以下哪些参数与内存优化相关？A.`maxservermemory`B.`tempdb`文件设置C.`querygovernor`D.`bufferpoolextension`E.`maxdegreeofparallelism`4.在PostgreSQL中，以下哪些方法可以提高查询性能？A.使用CTE（公用表表达式）B.创建物化视图C.调整`work_mem`参数D.使用索引覆盖E.禁用统计信息收集5.在Oracle数据库中，以下哪些操作可能导致索引失效？A.表结构变更B.大量INSERT操作C.使用函数计算列值D.索引重建E.更新索引列的值三、简答题（共5题，每题5分，共25分）1.简述数据库索引失效的常见原因，并举例说明。2.解释什么是数据库分区，并列举至少三种分区类型及其适用场景。3.在SQLServer中，如何使用`SETSTATISTICSIO`命令分析查询的I/O消耗？4.简述事务隔离级别及其对性能的影响，并说明SQLServer的默认隔离级别。5.当数据库出现全表扫描时，如何通过SQL语句定位问题？四、计算题（共2题，每题10分，共20分）1.假设一个SQL查询的执行计划如下：sqlSELECTa.id,FROMtable_aASaJOINtable_bASbONa.id=b.a_idWHEREa.status='active'ANDb.type='type1';-table_a有10万行数据，索引在`status`列上；-table_b有5万行数据，索引在`a_id`和`type`列上；-查询中`status='active'`的占比为20%，`type='type1'`的占比为30%；-JOIN操作假设为嵌套循环，不考虑索引合并。请计算：a.若`status`和`type`的索引未被有效利用，预估扫描行数。b.若`table_b`的`a_id`和`type`索引被合并使用，预估扫描行数。2.假设一个表`orders`有500万行数据，其中`order_date`列无索引，查询如下：sqlSELECTCOUNT()FROMordersWHEREorder_dateBETWEEN'2023-01-01'AND'2023-12-31';-数据库为MySQL，假设表扫描每次IO读取1MB数据，服务器每秒IO能力为100MB。请计算：a.若表扫描无索引，预估执行时间（秒）。b.若`order_date`列添加索引，预估执行时间（假设索引扫描效率为表扫描的10%）。五、综合分析题（共2题，每题10分，共20分）1.某公司数据库出现频繁的锁等待，日志显示以下SQL语句长时间阻塞：sqlSELECTFROMordersWHEREorder_id=1000FORUPDATE;-表`orders`有大量并发写入操作；-查询涉及索引`order_id`，但存在锁升级问题。请分析可能的原因，并提出至少三种优化建议。2.某电商平台的订单表`orders`查询性能下降，主要表现为：-查询`SELECTFROMordersWHEREuser_id=?`响应时间增加；-`user_id`列有索引，但执行计划显示全表扫描。请分析可能的原因，并提出优化方案。答案与解析一、单选题答案与解析1.C-解析：全文索引专为文本搜索设计，如MySQL的`FULLTEXT`索引、Oracle的`INDEXTYPEISFULLTEXT`等。普通索引和唯一索引不支持全文搜索，组合索引用于多列组合查询。2.B-解析：`DB_CACHE_SIZE`控制Oracle的数据库缓冲区大小，用于缓存数据块和PL/SQL代码，直接影响缓存命中率。PGA_AGGREGATE_TARGET控制PGA大小，SQLnet.service_name用于网络配置，LOG_BUFFER控制日志缓冲区。3.B-解析：`SHOWPROCESSLIST`（MySQL）和`sp_who2`（SQLServer）可实时查看锁等待和阻塞进程。`EXPLAIN`分析查询计划，`ANALYZE`更新统计信息。4.A-解析：堆表（无主键索引）适用于高并发写入场景，避免索引维护开销。覆盖索引、索引提示和索引分页更适用于读优化。5.C-解析：优化JOIN顺序（如先连接小表）可减少中间结果集大小，降低扫描量。其他选项效果有限或错误。6.C-解析：`max_parallel_workers_per_query`控制并行查询线程数，临时提升并行度。`work_mem`用于内存临时表，`EXPLAINANALYZE`分析执行计划，并行索引需先创建。7.B-解析：范围分区适用于时间、ID等有序数据，如按月份、年份分区。其他分区类型如列表分区（固定值）、哈希分区（随机分布）适用场景不同。8.C-解析：LIKE'%keyword%'无法利用索引，而`LIKE'keyword%'`可索引。全文索引和普通索引对前缀匹配效果较好。9.A-解析：`InnoDB_flush_log_at_trx_commit=2`延迟写入日志，减少写入量但可能丢失数据。其他选项与日志写入无关。10.B-解析：Hash索引适用于高基数列（唯一值多），如`PRIMARYKEY`列。B-Tree索引适用于范围查询，Clustered索引为物理排序，Filtered索引为条件过滤。二、多选题答案与解析1.A,B,C,D-解析：增加缓存、优化逻辑、分区表和硬件资源都是有效方法。忽略低频查询可能导致问题被掩盖。2.A,B,C,E-解析：系统表、锁诊断工具、隔离级别调整和超时设置可排查死锁。禁用索引会加剧问题。3.A,B,D,E-解析：内存参数包括服务器总内存、临时表文件、缓冲池扩展和并行度。`querygovernor`是SQLServer的查询超时设置。4.A,B,C,D-解析：CTE、物化视图、`work_mem`和索引覆盖可优化查询。禁用统计信息会导致查询计划错误。5.A,C,E-解析：表结构变更、函数计算列值和更新索引列值会触发索引失效。重建索引会临时失效但最终生效。三、简答题答案与解析1.索引失效原因及示例-原因：1.范围查询（如`>`、`<`）不利用索引；2.函数计算列值（如`UPPER(column)`）；3.OR条件拆分索引（如`WHEREa=1ORb=2`）；4.表结构变更（如删除索引列）。-示例：sql--范围查询失效SELECTFROMusersWHEREage>30;--age无索引--函数计算失效SELECTFROMproductsWHEREUPPER(name)='APPLE';--name无索引2.数据库分区及类型-分区：按规则将表数据拆分到多个物理部分，提高管理性和性能。-类型：1.范围分区：按列值范围（如时间、ID）；2.列表分区：按列值固定列表（如地区）；3.哈希分区：按列值哈希值（随机分布）；4.复合分区：结合多种规则。3.`SETSTATISTICSIO`分析-命令：sqlSETSTATISTICSIOON;SELECTFROMordersWHEREuser_id=1;-输出：显示`logicalreads`（逻辑读取次数）和`physicalreads`（物理读取次数），帮助定位I/O瓶颈。4.事务隔离级别及影响-级别：1.读未提交：最低，可见未提交数据（脏读）；2.读已提交：防止脏读，但仍可见未提交写（不可重复读）；3.可重复读：防止脏读和不可重复读，但可见幻读；4.串行化：最高，完全隔离（但性能最低）。-SQLServer默认：`READCOMMITTED`（读已提交）。5.全表扫描定位方法-方法：1.`EXPLAIN`分析执行计划；2.查看慢查询日志（如MySQL的`slow_query_log`）；3.监控工具（如SQLServerProfiler）抓取执行计划。四、计算题答案与解析1.执行计划预估a.无索引扫描行数：-table_a：10万行（20%被过滤，扫描8万行）；-table_b：5万行（30%被过滤，扫描3.5万行）；-JOIN：8万×3.5万=28亿行（实际不可能，需考虑索引）。b.索引合并扫描行数：-table_a：10万×30%=3万行（按`a_id`过滤）；-JOIN：3万行（直接匹配）。2.表扫描时间预估a.无索引：-扫描量：500万×1MB=500GB；-时间：500GB/100MB/s=50秒。b.有索引：-扫描量：500万×0.1MB=50GB；-时间：50GB/100MB/s=5秒。五、综合分析题答案与解析1.锁等待优化-原因：1.`FORUPDATE`锁定大量行；2.索引选择不当（如非主键）；3.事务