2025年计算机二级MySQL分区表设计技巧试题及答案

2025年计算机二级MySQL分区表设计技巧试题及答案一、单项选择题（共10题，每题2分，共20分）1.下列关于MySQL分区表适用场景的描述，正确的是（）A.单表数据量小于10万行的业务表适合使用分区表B.需要定期批量归档历史数据的日志表、订单表适合使用分区表C.存在大量非分区键更新操作的表适合使用分区表D.依赖外键约束保证数据一致性的表适合使用分区表答案：B解析：选项A错误，小表使用分区会增加元数据管理开销，查询性能反而低于普通表，通常单表数据量超过100万行才考虑分区；选项B正确，分区表支持直接DROP分区归档历史数据，效率远高于DELETE批量删除；选项C错误，更新操作如果导致分区键值变更，会触发数据在不同分区之间迁移，产生大量IO，性能极差；选项D错误，InnoDB分区表不支持外键约束，无法通过外键保证关联表的数据一致性。2.某业务系统的日志表年新增数据量超过2亿行，核心查询场景为按日志生成时间范围检索，且每季度需要归档上一年度的日志数据，最适合的分区类型是（）A.LIST分区B.HASH分区C.RANGE分区D.KEY分区答案：C解析：RANGE分区基于连续的区间值划分分区，完美匹配时间维度的范围查询和批量归档需求，仅需删除对应时间区间的分区即可完成归档，无需扫描全表。LIST分区适用于枚举值类型的分区键，HASH/KEY分区适用于打散热点数据、无固定范围归档需求的场景。3.某政务系统的居民信息表需要按户籍所在省份分区，核心查询场景为按省份过滤居民信息，省份列表固定且不会频繁新增，最适合的分区类型是（）A.RANGE分区B.LIST分区C.HASH分区D.KEY分区答案：B解析：LIST分区基于离散的枚举值划分分区，适合分区键为固定枚举值的场景，可针对单个枚举值对应的分区做单独查询或维护，符合本题按省份分区的需求。4.某社交平台的用户表总数据量5000万行，核心查询场景为按用户ID等值查询，无定期归档需求，为避免单分区读写热点，最适合的分区类型是（）A.RANGE分区B.LIST分区C.HASH分区D.以上都不对答案：C解析：HASH分区基于用户指定的表达式计算哈希值，将数据均匀分配到不同分区，可有效打散读写压力，避免单分区热点，完美匹配按用户ID等值查询的场景。5.某RANGE分区表的分区键为create_timeDATETIME，分区定义为PARTITIONp1VALUESLESSTHAN('2024-01-01')，PARTITIONp2VALUESLESSTHAN('2025-01-01')，插入一条create_time为NULL的记录，该记录会被分到哪个分区（）A.p1B.p2C.报错，无法插入D.分到默认分区答案：A解析：MySQL中NULL值被视为小于任何非NULL值，因此RANGE分区中插入分区键为NULL的记录时，会被分配到第一个分区。如果是LIST分区且未显式指定NULL对应的枚举值，插入时才会报错。6.某RANGE分区表的分区键为create_timeDATETIME，下列查询语句中能触发分区修剪的是（）A.SELECT*FROMt_orderWHEREYEAR(create_time)=2024B.SELECT*FROMt_orderWHEREcreate_timeBETWEEN'2024-01-01'AND'2024-12-31'C.SELECT*FROMt_orderWHEREDATE_FORMAT(create_time,'%Y')='2024'D.SELECT*FROMt_orderWHEREcreate_time+INTERVAL1DAY<'2025-01-01'答案：B解析：分区修剪要求WHERE子句中分区键以原生字段参与比较，不能使用函数、运算或类型转换。选项A、C、D均对create_time做了函数运算或算术运算，优化器无法识别分区边界，无法触发分区修剪；选项B直接用原生字段做范围比较，可触发分区修剪，仅扫描2024年对应的分区。7.下列关于MySQL分区表限制的描述，错误的是（）A.分区表的所有主键和唯一索引必须包含分区键字段B.同一分区表的不同分区可以使用不同的存储引擎C.分区表不支持全文索引、空间索引之外的外键约束D.单个分区表最多支持8192个分区答案：B解析：MySQL分区表的存储引擎是表级属性，同一表的所有分区必须使用相同的存储引擎，因此选项B错误；其余选项均为MySQL8.0版本中分区表的官方限制，描述正确。8.某电商订单表需要按季度归档历史数据，同时按用户ID打散单分区的读写压力，最适合的分区方案是（）A.仅使用RANGE分区按下单时间分区B.仅使用HASH分区按用户ID分区C.复合分区：一级RANGE分区按下单时间，二级HASH分区按用户IDD.复合分区：一级LIST分区按订单状态，二级RANGE分区按下单时间答案：C解析：复合分区（子分区）可同时满足两类分区需求，一级RANGE分区满足按时间归档的需求，二级HASH分区按用户ID打散单分区的数据，避免单分区读写热点，符合本题业务需求。9.某RANGE分区表存储了2020-2024年的订单数据，需要删除2020年的所有数据，效率最高的操作是（）A.执行DELETEFROMt_orderWHEREcreate_time<'2021-01-01'B.执行TRUNCATETABLEt_orderC.执行ALTERTABLEt_orderDROPPARTITIONp2020q1,p2020q2,p2020q3,p2020q4D.执行DROPTABLEt_order答案：C解析：DROPPARTITION是DDL操作，直接删除对应分区的数据文件，无需生成redo/undo日志，执行效率远高于DELETE批量删除；TRUNCATE和DROP会删除全表数据，不符合需求。10.下列关于HASH分区和KEY分区的区别，描述正确的是（）A.HASH分区支持非整数类型的分区键，KEY分区不支持B.HASH分区的哈希函数由用户指定，KEY分区的哈希函数由MySQL内部提供C.KEY分区不支持多列作为分区键，HASH分区支持D.HASH分区的数据分布更均匀，KEY分区容易出现数据倾斜答案：B解析：选项A错误，KEY分区支持非整数类型的分区键，HASH分区要求表达式返回值为整数；选项B正确，HASH分区用户需自定义返回整数的表达式，KEY分区使用MySQL内置的哈希函数，无需用户编写表达式；选项C错误，KEY分区支持多列作为分区键；选项D错误，两种分区的均匀度差异不大，只要分区键离散度足够，都可实现均匀分布。二、填空题（共5题，每题3分，共15分）1.MySQL分区表要求同一表的所有分区必须使用____存储引擎。答案：相同解析：MySQL分区表的存储引擎是表级属性，所有分区必须使用同一种存储引擎，不能混合使用InnoDB、MyISAM等不同引擎。2.针对非整数类型的时间、字符串字段直接做范围分区，应使用____分区类型替代传统RANGE分区。答案：RANGECOLUMNS解析：传统RANGE分区仅支持整数类型的分区表达式，RANGECOLUMNS分区支持直接使用DATETIME、DATE、VARCHAR等非整数类型作为分区键，无需额外做类型转换。3.分区表实现查询性能提升的核心机制是____，即仅扫描符合条件的分区，跳过无关分区。答案：分区修剪（PartitionPruning）解析：分区修剪是MySQL优化器针对分区表的核心优化逻辑，通过解析WHERE子句的分区键过滤条件，匹配分区边界定义，缩小扫描范围，大幅减少IO开销。4.LIST分区插入数据时，如果分区键的值不在预定义的枚举列表中，默认会抛出错误码为1526的____错误。答案：Tablehasnopartitionforvaluexxx解析：LIST分区需提前预定义所有可能的分区键枚举值，插入未定义的枚举值时会触发该错误，可通过新增对应枚举值的分区、或新增存储未知值的默认分区解决。5.为了让HASH分区的数据分布尽可能均匀，分区数量通常建议设置为____。答案：2的N次幂解析：MySQL的HASH分区采用取模算法，当分区数量为2的N次幂时，取模运算的结果分布最均匀，可避免出现数据倾斜的问题。三、简答题（共3题，每题10分，共30分）1.请简述RANGE、LIST、HASH、KEY四种常用分区类型的适用场景及设计核心注意事项。参考答案：（1）RANGE分区：适用场景为存在时间维度的范围查询、需要定期批量归档历史数据的业务表，如订单表、日志表、监控数据表。注意事项：①分区键优先选择时间类型字段，使用RANGECOLUMNS避免类型转换；②提前规划1-2年的分区边界，预留默认分区避免新增数据无分区可放；③分区粒度不宜过细，单分区数据量建议控制在500万-2000万行。（2）LIST分区：适用场景为分区键为固定枚举值、按枚举值过滤查询频繁的业务表，如按区域、订单状态、业务线分区的表。注意事项：①提前枚举所有可能的分区键值，避免插入失败；②如果允许分区键为NULL，需显式指定NULL对应的分区；③不适合枚举值频繁新增的场景，新增枚举值需要修改分区定义。（3）HASH分区：适用场景为需要打散读写热点、无定期归档需求、核心查询为分区键等值查询的业务表，如用户表、商品表。注意事项：①分区键选择离散度高的字段，如用户ID、订单ID，避免使用低离散度字段导致数据倾斜；②分区数量设置为2的N次幂，保证数据分布均匀；③避免频繁调整分区数量，否则会触发全表数据重分布，影响业务可用性。（4）KEY分区：适用场景与HASH分区类似，适合分区键为非整数类型、不想自定义哈希表达式的场景。注意事项：①哈希函数由MySQL内部提供，跨版本迁移时需注意哈希规则可能变化，提前验证数据分布；②支持多列作为分区键，适合无单独高离散度字段的场景。2.请列举分区表设计的3个常见误区及对应的优化方案。参考答案：（1）误区：不分数据量大小盲目使用分区表。优化方案：单表数据量低于100万行时无需使用分区表，小表分区会增加元数据管理开销，查询性能反而低于普通表；仅当单表数据量超过100万行、且存在明显的冷热数据分离或热点打散需求时，才考虑使用分区表。（2）误区：分区键选择随意，不参与高频查询的过滤条件。优化方案：分区键必须是业务高频查询的过滤字段，否则无法触发分区修剪，会扫描所有分区，性能甚至低于普通表；设计阶段需梳理业务所有核心查询场景，选择覆盖率最高的过滤字段作为分区键。（3）误区：忽略分区键与主键/唯一键的约束关系。优化方案：MySQL要求分区表的所有主键和唯一索引必须包含分区键字段，否则表创建失败；如果需要按非主键字段分区，需调整主键为（原主键字段+分区键字段）的联合主键，不会影响原主键的唯一性。（补充误区）误区：对分区键使用函数运算作为查询条件。优化方案：查询时避免对分区键使用函数、算术运算、类型转换，直接用原生字段做等值或范围比较，保证触发分区修剪。3.请简述分区修剪的实现原理及触发的必要条件。参考答案：实现原理：MySQL优化器在解析SQL的WHERE子句时，会提取分区键的过滤条件，与分区元数据中存储的各分区边界值做匹配，计算出查询需要扫描的分区范围，直接跳过不符合条件的分区，大幅减少扫描的数据量和IO开销。触发必要条件：①WHERE子句中包含分区键的过滤条件；②分区键以原生字段参与比较，未被函数、算术运算、隐式类型转换修改；③过滤运算符匹配分区类型的支持范围：RANGE分区支持>、<、BETWEEN、=，LIST分区支持=、IN，HASH/KEY分区支持=、IN；④未使用无WHERE条件的全表扫描、全表排序等语法。四、实操设计题（共1题，35分）某电商平台订单表`t_order`的核心字段如下：字段名类型非空说明order_idBIGINTUNSIGNED是订单ID，全局唯一user_idBIGINTUNSIGNED是用户IDorder_statusTINYINTUNSIGNED是订单状态：1待支付、2已支付、3已发货、4已完成、5已取消create_timeDATETIME是下单时间pay_amountDECIMAL(10,2)是支付金额要求完成以下任务：1.设计合理的分区方案，说明设计思路（10分）2.写出创建该分区表的完整SQL语句，要求主键为`(order_id,create_time)`，存储引擎为InnoDB，字符集为utf8mb4（10分）3.写出2025年12月31日归档2023年及之前所有订单数据的SQL语句（5分）4.写出查询2024年11月所有已完成订单（`order_status=4`）的SQL语句，要求必须触发分区修剪（5分）5.若业务反馈上述查询执行缓慢，经排查未触发分区修剪，请列出2种可能的原因及排查方法（5分）参考答案：1.分区方案设计思路：采用RANGECOLUMNS+LIST复合分区方案。①一级分区使用RANGECOLUMNS(create_time)按自然季度划分：匹配高频时间范围查询和定期归档需求，RANGECOLUMNS直接支持DATETIME类型，无需额外转换，删除旧分区即可完成归档，效率极高；单季度数据量约200万行，符合单分区数据量最优区间。②二级分区使用LIST(order_status)按订单状态划分：匹配次高频按订单状态查询/统计的需求，可在一级分区修剪的基础上进一步修剪二级分区，减少扫描数据量。③提前预留2年的分区和默认分区，避免业务增长后无分区可放的问题。2.创建表的完整SQL语句：```sqlCREATETABLEt_order(order_idBIGINTUNSIGNEDNOTNULLCOMMENT'订单ID',user_idBIGINTUNSIGNEDNOTNULLCOMMENT'用户ID',order_statusTINYINTUNSIGNEDNOTNULLCOMMENT'订单状态：1待支付2已支付3已发货4已完成5已取消',create_timeDATETIMENOTNULLCOMMENT'下单时间',pay_amountDECIMAL(10,2)NOTNULLCOMMENT'支付金额',PRIMARYKEY(order_id,create_time))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4PARTITIONBYRANGECOLUMNS(create_time)SUBPARTITIONBYLIST(order_status)(PARTITIONp2023q1VALUESLESSTHAN('2023-04-01')(SUBPARTITIONs2023q1_1VALUESIN(1),SUBPARTITIONs2023q1_2VALUESIN(2),SUBPARTITIONs2023q1_3VALUESIN(3),SUBPARTITIONs2023q1_4VALUESIN(4),SUBPARTITIONs2023q1_5VALUESIN(5)),PARTITIONp2023q2VALUESLESSTHAN('2023-07-01')(SUBPARTITIONs2023q2_1VALUESIN(1),SUBPARTITIONs2023q2_2VALUESIN(2),SUBPARTITIONs2023q2_3VALUESIN(3),SUBPARTITIONs2023q2_4VALUESIN(4),SUBPARTITIONs2023q2_5VALUESIN(5)),PARTITIONp2023q3VALUESLESSTHAN('2023-10-01')(SUBPARTITIONs2023q3_1VALUESIN(1),SUBPARTITIONs2023q3_2VALUESIN(2),SUBPARTITIONs2023q3_3VALUESIN(3),SUBPARTITIONs2023q3_4VALUESIN(4),SUBPARTITIONs2023q3_5VALUESIN(5)),PARTITIONp2023q4VALUESLESSTHAN('2024-01-01')(SUBPARTITIONs2023q4_1VALUESIN(1),SUBPARTITIONs2023q4_2VALUESIN(2),SUBPARTITIONs2023q4_3VALUESIN(3),SUBPARTITIONs2023q4_4VALUESIN(4),SUBPARTITIONs2023q4_5VALUESIN(5)),-2024-2026年分区按相同规则定义，此处省略PARTITIONp_futureVALUESLESSTHAN(MAXVALUE)(SUBPARTITIONsf_1VALUESIN(1),SUBPARTITIONsf_2VALUESIN(2),SUBPARTITIONsf_3VALUESIN(3),SUBPARTITIONsf_4VALUESIN(4),SUBPARTITIONsf_5VALUESIN(5)));```3.归档2023年及之前数据的SQL语句：```sql-归档前需确认2023年数据已备份到冷存储ALTERTABLEt_orderDROPPARTITIONp2023q1,p2023q2,p2023q3,p2023q4;```说明：该操作为DDL操作，执行速度极快，不会产生大量undo日志，对业务影响极小。4.符合分区修剪要求的查询语句：```sqlSELECTorder_id