数据库系统工程师专项练习（索引优化+事务管理）

数据库系统工程师专项练习（索引优化+事务管理）一、单项选择题（总共10题，每题2分，共20分）1.在数据库索引优化中，以下哪种索引结构最适合频繁更新的小表？A.B+树索引B.哈希索引C.全文索引D.位图索引解析：小表频繁更新时，哈希索引的插入删除效率最高，因为其基于哈希函数直接定位数据位置，无需维护平衡树结构。B+树索引在更新时需要调整树结构，开销较大；全文索引适用于文本搜索；位图索引适合低基数列的聚合查询。选项B正确。2.某数据库表中有100万条数据，查询条件为"年龄BETWEEN25AND35AND职位='工程师'"。若表中"年龄"列无索引，"职位"列有索引，以下哪种优化方案最能提升查询性能？A.对"年龄"列创建索引B.对"职位"列创建复合索引（年龄,职位）C.对"年龄"和"职位"列分别创建单列索引D.使用临时表存储中间结果解析：复合索引（年龄,职位）能同时利用两列的索引，且索引顺序需与查询条件匹配。因为查询先筛选"职位"（已索引），再筛选"年龄"（无索引），所以应创建（年龄,职位）索引。若顺序反了，B树索引会失效。选项B正确。3.在SQLServer中，执行"CREATEINDEXidx_user_idONUsers(UserID)"后，若发现查询"SELECTFROMUsersWHEREUserID=100"仍慢，可能的原因是？A.索引未生效B.数据页未分配索引页C.填充因子设置不当导致页分裂D.硬件瓶颈解析：此查询应直接使用索引，但若仍慢，最可能原因是填充因子（fillfactor）设置过高，导致数据插入时频繁页分裂。选项C正确。4.以下哪种场景最适合使用覆盖索引（CoveringIndex）？A.查询条件涉及多列，但返回少量列B.查询条件仅涉及单列，但返回多列C.经常更新的列作为索引键D.大量JOIN操作的关联列解析：覆盖索引包含查询所需的所有列，无需回表取数据，最适合返回多列的查询。选项B正确。5.在MySQL中，执行"EXPLAINSELECTFROMOrdersWHEREOrderDate='2023-01-01'"时，若发现Type为"ALL"，但OrderDate列有索引，可能的原因是？A.索引损坏B.等价类优化失效C.索引统计信息过时D.查询缓存未命中解析：Type为"ALL"表示全表扫描，若索引存在但未使用，可能是MySQL统计信息（cardinality等）过时，导致优化器误判索引选择性不足。选项C正确。6.在PostgreSQL中，执行"CREATEINDEXidx_nameONUsers(Name)USINGbtree;"后，为何对"LIKE'张%'查询效果不佳？A.B树不支持前缀匹配B.前缀长度超过索引页大小C.索引未包含前缀数据D.模糊匹配默认使用全表扫描解析：B树索引支持前缀匹配，但必须从首字符开始（即"张%"），"张_%"或"%"开头的模糊查询无法利用索引。选项D正确。7.若某数据库表频繁执行"UPDATEUsersSETAge=Age+1WHEREUserIDIN(SELECTIDFROMActiveUsers)"，以下哪种索引策略最有效？A.对UserID列创建索引B.对ActiveUsers表中的ID列创建索引C.对Users表的Age列创建索引D.使用触发器避免频繁更新解析：内连接子查询的执行依赖于被引用表（ActiveUsers）的ID索引，因此应优化该表的索引。选项B正确。8.在Oracle中，执行"ALTERINDEXidx_usersREBUILDONLINE;"后，以下哪个说法正确？A.索引重建期间无法访问表数据B.索引重建会占用额外空间C.索引重建不影响表DML操作D.索引重建会自动更新统计信息解析：ONLINE重建允许表在重建期间正常访问，但会降低性能。选项C正确。9.若某查询执行计划显示"IndexSeek"优先使用索引，但实际性能仍差，可能的原因是？A.索引列选择性过低B.索引列存在函数计算C.索引未包含查询所需所有列D.硬件IOPS不足解析：IndexSeek已使用索引，但若查询返回大量数据，仍可能因I/O瓶颈导致性能差。选项D正确。10.在SQLServer中，执行"CREATEUNIQUECLUSTEREDINDEXidx_user_emailONUsers(Email)"后，插入重复Email会报错，但更新Email列会怎样？A.自动重建索引B.报错"违反唯一约束"C.允许更新，但索引页需分裂D.索引自动失效解析：更新唯一列仍需满足唯一约束，若新值已存在，会报错。选项B正确。二、判断题（总共10题，每题2分，共20分）1.索引页分裂（SplitPage）只会发生在插入数据时，不会出现在更新操作中。（×）解析：更新若导致行长度变化，也可能触发页分裂。2.聚集索引的B+树根节点一定指向数据页。（√）解析：聚集索引的B+树非叶子节点也包含数据行指针。3.使用覆盖索引能完全避免回表操作。（√）解析：若查询列不在索引中，仍需回表取数据。4.哈希索引适合排序操作，因为其数据存储顺序与索引顺序一致。（×）解析：哈希索引无顺序性，排序仍需全表扫描。5.索引选择性越高，其覆盖范围越大。（√）解析：高选择性意味着索引列唯一值多，能覆盖更多查询场景。6.使用"WITH(NOLOCK)"提示能完全避免死锁。（×）解析：该提示仅跳过锁，但事务仍可能阻塞。7.事务的隔离级别越高，并发性能越差。（√）解析：读未提交最低，但写提交最高，开销最大。8.乐观锁通过版本号实现，适合写少读多的场景。（√）解析：版本号冲突概率低，适合高并发读。9.SQLServer的索引压缩能减少所有数据页的存储空间。（×）解析：压缩页仍需存储非压缩页的备份。10.临时表会自动创建聚集索引。（×）解析：临时表索引策略由系统决定，未必聚集。三、填空题（总共10题，每题2分，共20分）1.索引的维护操作包括______、重建和重新组织。参考答案：重建解析：重建会创建新索引并删除旧索引，重新组织仅重写数据。2.在SQLServer中，使用______语句可以查看索引的填充因子设置。参考答案：sp_helpindex解析：该存储过程返回索引详细属性，包括fillfactor。3.覆盖索引的核心优势是______，无需回表取数据。参考答案：包含查询所需所有列解析：减少I/O开销，提升查询效率。4.MySQL的EXPLAIN输出中，Type为"ref"表示______。参考答案：索引查找解析：使用非全键匹配的索引列。5.事务的ACID特性中，______确保了数据一致性。参考答案：原子性解析：原子性要求事务要么全部执行，要么全部回滚。6.在PostgreSQL中，使用______命令可以强制刷新索引缓存。参考答案：REINDEXCONCURRENTLY解析：该命令在重建索引时允许并发操作。7.索引选择性计算公式为______，值越接近1越好。参考答案：唯一值数量/总行数解析：高选择性意味着列值分布均匀。8.Oracle中，使用______参数控制索引压缩的级别。参考答案：COMPRESSION解析：该参数支持ALL、ROW、UNCOMPRESSED等选项。9.乐观锁通常通过______字段实现版本控制。参考答案：版本号解析：记录更新时的版本，冲突时拒绝操作。10.SQLServer中，使用______语句可以删除无用的索引碎片。参考答案：DBCCINDEXDEFRAG解析：该命令在线优化索引页顺序。四、简答题（总共8题，每题2分，共16分）1.简述B+树索引与哈希索引的区别及其适用场景。参考答案：-B+树索引：支持范围查询（BETWEEN、LIKE'a%'），数据按顺序存储，适合排序和范围操作。-哈希索引：基于哈希函数定位数据，仅支持等值查询（=、IN），无顺序性。适用场景：-B+树：主键索引、频繁范围查询的列。-哈希：频繁等值查询的列。2.解释什么是索引碎片及其两种类型。参考答案：索引碎片指索引页数据顺序与索引顺序不一致，分为：-物理碎片：索引页数据被分散在不同位置。-逻辑碎片：索引页顺序正确，但数据页顺序错误。3.索引选择性的计算方法是什么？如何提高选择性？参考答案：计算方法：唯一值数量/总行数。提高方法：-去除冗余列（如去重后创建索引）。-使用精确数据类型（如INT而非VARCHAR）。4.事务的四个基本特性（ACID）分别是什么？参考答案：-原子性（Atomicity）：事务不可分割。-一致性（Consistency）：保证数据完整性。-隔离性（Isolation）：并发事务互不干扰。-持久性（Durability）：提交后永久保存。5.简述乐观锁和悲观锁的区别及其适用场景。参考答案：-乐观锁：假设冲突概率低，通过版本号检查冲突，冲突时重试。-悲观锁：假设冲突概率高，直接锁定资源，如SELECTFORUPDATE。适用场景：-乐观锁：写少读多的场景（如电商库存）。-悲观锁：写多冲突高的场景（如秒杀）。6.什么是覆盖索引？其优缺点是什么？参考答案：覆盖索引：包含查询所需所有列的索引。优点：-减少I/O，提升性能。-无需回表。缺点：-维护成本高（需更新所有列）。-索引大小可能过大。7.索引失效的常见原因有哪些？参考答案：-查询条件不匹配（如函数计算）。-索引统计信息过时。-隐藏列（如NULL值未索引）。-复杂查询（如子查询未优化）。8.简述索引重建与重新组织的区别。参考答案：-重建：删除旧索引，创建新索引，耗时较长但彻底。-重新组织：重写数据页顺序，保留原索引，耗时较短。五、应用题（总共8题，每题4分，共24分）1.某电商订单表（Orders）结构如下：```sqlCREATETABLEOrders(OrderIDINTPRIMARYKEY,UserIDINT,OrderDateDATE,TotalAmountDECIMAL(10,2),StatusVARCHAR(20));```假设表中有100万条数据，查询"2023年1月状态为'已完成'的订单，按金额排序"频繁执行，请设计索引优化方案。参考答案：-创建复合索引（OrderDate,Status,TotalAmount）。-索引顺序：先按OrderDate过滤时间范围，再按Status筛选状态，最后按TotalAmount排序。解析：-时间范围查询通常放在最前，过滤数据量最大。-状态筛选次之，进一步缩小结果集。-排序列放最后，避免排序时扫描过多数据。2.某数据库表（Products）结构如下：```sqlCREATETABLEProducts(ProductIDINTPRIMARYKEY,CategoryVARCHAR(20),PriceDECIMAL(8,2),StockINT);```假设"Category"列有30个分类，"Price"列数据分布均匀，查询"价格在100-200元之间的电子产品"频繁执行，请设计索引方案。参考答案：-创建复合索引（Category,Price）。-索引顺序：先按Category过滤分类，再按Price范围查询。解析：-分类数量少，先过滤分类能快速缩小结果集。-价格范围查询放在次位，利用索引范围扫描。3.某数据库表（Employees）结构如下：```sqlCREATETABLEEmployees(EmployeeIDINTPRIMARYKEY,DepartmentVARCHAR(20),SalaryDECIMAL(8,2),HireDateDATE);```假设"Department"列有20个部门，"Salary"列数据分布均匀，查询"工资在5000-8000元之间的IT部门员工"频繁执行，请设计索引方案。参考答案：-创建复合索引（Department,Salary）。-索引顺序：先按Department过滤部门，再按Salary范围查询。解析：-部门数量少，先过滤部门能快速缩小结果集。-薪资范围查询放在次位，利用索引范围扫描。4.某数据库表（Sales）结构如下：```sqlCREATETABLESales(SaleIDINTPRIMARYKEY,ProductIDINT,SaleDateDATE,QuantityINT);```假设"ProductID"列有1000个产品，"SaleDate"列数据按月统计，查询"2023年2月销售量最多的前10个产品"频繁执行，请设计索引方案。参考答案：-创建复合索引（SaleDate,ProductID,QuantityDESC）。-索引顺序：先按SaleDate过滤时间，再按ProductID排序，最后按Quantity降序。解析：-时间范围查询放在最前，过滤数据量最大。-产品ID排序次之，确保统计准确性。-量级排序放最后，避免全表排序。5.某数据库表（Customers）结构如下：```sqlCREATETABLECustomers(CustomerIDINTPRIMARYKEY,NameVARCHAR(50),CityVARCHAR(20),RegistrationDateDATE);```假设"City"列有50个城市，"RegistrationDate"列数据按年统计，查询"2023年注册的北京客户"频繁执行，请设计索引方案。参考答案：-创建复合索引（City,RegistrationDate）。-索引顺序：先按City过滤城市，再按RegistrationDate过滤时间。解析：-城市数量少，先过滤城市能快速缩小结果集。-时间过滤放在次位，利用索引范围扫描。6.某数据库表（Orders）结构如下：```sqlCREATETABLEOrders(OrderIDINTPRIMARYKEY,UserIDINT,OrderDateDATE,TotalAmountDECIMAL(10,2));```假设"UserID"列有10万用户，"OrderDate"列数据按月统计，查询"2023年1月所有用户的订单总额"频繁执行，请设计索引方案。参考答案：-创建复合索引（UserID,OrderDate）。-索引顺序：先按UserID分组，再按OrderDate过滤时间。解析：-用户数量适中，先分组能快速聚合。-时间过滤放在次位，利用索引范围扫描。7.某数据库表（Products）结构如下：```sqlCREATETABLEProducts(ProductIDINTPRIMARYKEY,CategoryVARCHAR(20),PriceDECIMAL(8,2),StockINT);```假设"Category"列有30个分类，"Price"列数据分布均匀，查询"价格在100-200元之间的电子产品"频繁执行，请设计索引方案。参考答案：-创建复合索引（Category,Price）。-索引顺序：先按Category过滤分类，再按Price范围查询。解析：-分类数量少，先过滤分类能快速缩小结果集。-价格范围查询放在次位，利用索引范围扫描。8.某数据库表（Employees）结构如下：```sqlCREATETABLEEmployees(EmployeeIDINTPRIMARYKEY,DepartmentVARCHAR(20),SalaryDECIMAL(8,2),HireDateDATE);```假设"Department"列有20个部门，"Salary"列数据分布均匀，查询"工资在5000-8000元之间的IT部门员工"频繁执行，请设计索引方案。参考答案：-创建复合索引（Department,Salary）。-索引顺序：先按Department过滤部门，再按Salary范围查询。解析：-部门数量少，先过滤部门能快速缩小结果集。-薪资范围查询放在次位，利用索引范围扫描。【标准答案及解析】一、单项选择题1.B2.B3.C4.B5.C6.D7.B8.C9.D10.B二、判断题1.×2.√3.√4.×5.√6.×7.√8.√9.×10.×三、填空题1.重建2.sp_helpindex3.包含查询所需所有列4.索引查找2.原子性6.REINDEXCONCURRENTLY7.唯一值数量/总行数3.COMPRESSION9.版本号10.DBCCINDEXDEFRAG四、简答题1.B+树索引支持范围查询（BETWEEN、LIKE'a%'），数据按顺序存储，适合排序和范围操作。哈希索引基于哈希函数定位数据，仅支持等值查询（=、IN），无顺序性。适用场景：-B+树：主键索引、频繁范围查询的列。-哈希：频繁等值查询的列。2.索引碎片指索引页数据顺序与索引顺序不一致，分为：-物理碎片：索引页数据被分散在不同位置。-逻辑碎片：索引页顺序正确，但数据页顺序错误。3.索引选择性的计算方法为：唯一值数量/总行数。提高选择性的方法：-去除冗余列（如去重后创建索引）。-使用精确数据类型（如INT而非VARCHAR）。4.事务的四个基本特性（ACID）分别是什么？-原子性（Atomicity）：事务不可分割。-一致性（Consistency）：保证数据完整性。-隔离性（Isolation）：并发事务互不干扰。-持久性（Durability）：提交后永久保存。5.乐观锁和悲观锁的区别及其适用场景：-乐观锁：假设冲突概率低，通过版本号检查冲突，冲突时重试。-悲观锁：假设冲突概率高，直接锁定资源，如SELECTFORUPDATE。适用场景：-乐观锁：写少读多的场景（如电商库存）。-悲观锁：写多冲突高的场景（如秒杀）。6.覆盖索引：包含查询所需所有列的索引。优缺点：优点：-减少I/O，提升性能。-无需回表。缺点：-维护成本高（需更新所有列）。-索引大小可能过大。7.索引失效的常见原因：-查询条件不匹配（如函数计算）。-索引统计信息过时。-隐藏列（如NULL值未索引）。-复杂查询（如子查询未优化）。8.索引重建与重新组织的区别：-重建：删除旧索引，创建新索引，耗时较长但彻底。-重新组织：重写数据页顺序，保留原索引，耗时较短。五、应用题1.索引优化方案：```sqlCREATEINDEXidx_order_date_status_amountONOrders(OrderDate,Status,TotalAmount);```解析：-时间范围查询放在最前，过滤数据量最大。-状态筛选次之，进一步缩小结果集。-排序列放最后，避免排序时扫描过多数据。2.索引优化方案：```sqlCREATEINDEXidx_ca