2025年mysql考试题及答案

2025年mysql考试题及答案一、单项选择题（每题2分，共20分）1.MySQL8.0中，关于InnoDB存储引擎的默认配置，以下描述错误的是？A.默认事务隔离级别为可重复读（REPEATABLEREAD）B.支持行级锁和表级锁C.自动增长列的锁模式为AUTO-INC锁（5.7及以下版本特性）D.索引类型同时支持B+树索引和哈希索引答案：C解析：MySQL8.0中InnoDB自动增长列的锁模式已优化为互斥锁（innodb_autoinc_lock_mode=2），取代了传统的AUTO-INC锁（innodb_autoinc_lock_mode=1），仅在需要严格顺序时使用。2.以下哪种索引类型无法通过EXPLAIN的type字段显示为"ref"？A.普通二级索引B.唯一索引（非主键）C.主键索引D.全文索引答案：D解析：全文索引用于全文搜索（MATCH...AGAINST），其查询方式与B+树索引不同，EXPLAIN的type字段不会显示为"ref"；其他索引类型在等值查询时可能显示为"ref"或"eq_ref"。3.执行以下SQL语句后，变量@result的值是？```sqlSET@a=10;SET@b=20;SELECTLEAST(@a,@b,NULL)INTO@result;```A.10B.20C.NULLD.报错答案：C解析：LEAST函数在参数包含NULL时，返回NULL（除非所有参数均为NULL）。4.关于MySQL慢查询日志（slowquerylog），以下说法正确的是？A.仅记录执行时间超过long_query_time的查询B.使用索引但扫描行数超过min_examined_row_limit的查询会被记录C.临时表的创建不会被记录到慢查询日志D.慢查询日志默认存储在系统临时目录答案：B解析：慢查询日志记录两种情况：执行时间超过long_query_time的查询，或使用索引但扫描行数超过min_examined_row_limit的查询（需开启log_queries_not_using_indexes）。5.某表结构为`CREATETABLEt(idINTPRIMARYKEY,nameVARCHAR(50),ageINT,dept_idINT)`，若需频繁查询"各部门年龄大于30岁的员工姓名"，最合理的索引设计是？A.(dept_id,age)B.(dept_id,age,name)C.(age,dept_id,name)D.(name,dept_id,age)答案：B解析：覆盖索引（dept_id,age,name）可避免回表，直接通过索引获取name字段，满足WHEREdept_id和age过滤条件，以及SELECTname的需求。6.以下关于MySQL事务的说法，错误的是？A.隐式提交会终止当前事务B.可重复读隔离级别下，可能出现幻读C.串行化隔离级别会完全禁止脏读、不可重复读和幻读D.InnoDB的事务日志先写入redologbuffer，再按innodb_flush_log_at_trx_commit参数控制刷盘答案：B解析：MySQL8.0的InnoDB通过间隙锁（GapLock）在可重复读隔离级别下解决了幻读问题（仅针对普通DML语句），但需注意当前读（SELECT...FORUPDATE）仍可能触发幻读。7.执行`EXPLAINFORMAT=JSONSELECTFROMtWHEREidIN(10,20,30)`后，输出中的"access_type"字段值最可能是？A.rangeB.refC.eq_refD.const答案：A解析：IN子句对主键的查询属于范围扫描（range），当id为主键时，会使用主键索引进行范围查找。8.关于MySQL主从复制（基于二进制日志），以下描述错误的是？A.从库通过IO线程读取主库的binlog并写入relaylogB.主库需开启binlog，且binlog_format建议设置为ROWC.从库的SQL线程负责执行relaylog中的事件D.半同步复制（semi-sync）可保证主从数据完全一致答案：D解析：半同步复制仅保证主库提交事务前至少有一个从库确认接收binlog，但网络延迟或从库故障时仍可能出现数据不一致（如主库提交后未发送binlog到从库即崩溃）。9.某InnoDB表数据量为1000万行，执行`ALTERTABLEtDROPCOLUMNextra`，以下优化措施最有效的是？A.使用pt-online-schema-change工具B.直接执行ALTER语句（MySQL8.0）C.先创建新表，再导入数据，最后重命名D.开启innodb_file_per_table答案：B解析：MySQL8.0对ALTERTABLE的元数据操作进行了优化，DROPCOLUMN操作变为“即时操作”（instantALTER），仅修改元数据，无需重建表，执行时间接近O(1)。10.以下关于MySQL索引失效的场景，解释错误的是？A.对索引列使用函数（如WHEREYEAR(create_time)=2024）——索引无法使用，需改为范围查询（create_time>='2024-01-01'ANDcreate_time<'2025-01-01'）B.索引列参与计算（如WHEREid+1=100）——MySQL无法使用索引，需改写为WHEREid=99C.联合索引(a,b,c)，查询条件为WHEREa=10ANDc=20——仅a列使用索引，c列无法使用D.索引列使用ISNULL查询——InnoDB不支持对NULL值的索引优化答案：D解析：InnoDB支持对NULL值的索引，索引列可以存储NULL，且ISNULL查询可以使用索引（需注意索引列允许NULL时，索引树会包含NULL值节点）。二、填空题（每空2分，共20分）1.InnoDB的缓冲池（BufferPool）通过________算法管理内存页面，默认大小为________（填写配置参数）。答案：LRU（最近最少使用）、innodb_buffer_pool_size2.事务的ACID特性中，I代表________（英文缩写），D代表________（中文全称）。答案：Isolation（隔离性）、持久性3.联合索引的最左匹配原则指索引的匹配顺序必须从________开始，且________（填“支持”或“不支持”）跳过中间列。答案：最左列、不支持4.MySQL8.0引入的隐藏索引（InvisibleIndex）默认不会被查询优化器使用，但可以通过设置________系统变量强制使用。答案：optimizer_switch='use_invisible_indexes=on'5.死锁的必要条件包括互斥条件、请求与保持条件、不可剥夺条件和________。答案：循环等待条件6.二进制日志（binlog）的三种格式中，________格式对存储过程、触发器的复制更友好。答案：STATEMENT（语句）7.InnoDB的undolog用于实现________（填“原子性”或“持久性”），redolog用于实现________（填“原子性”或“持久性”）。答案：原子性、持久性三、简答题（每题8分，共40分）1.简述InnoDB行锁的类型及加锁规则。答案：InnoDB行锁类型包括共享锁（S锁）和排他锁（X锁）。加锁规则：普通SELECT使用一致性非锁定读（快照读），不加锁；SELECT...FORUPDATE加X锁，SELECT...LOCKINSHAREMODE加S锁；对于索引条件，若使用唯一索引等值查询，仅锁定匹配的行；若使用非唯一索引或范围查询，会锁定索引记录及间隙（间隙锁），防止幻读；锁定的行包括所有满足WHERE条件的索引记录，即使数据行不存在（如插入意向锁）。2.如何分析MySQL慢查询？请列出具体步骤。答案：分析步骤：（1）开启慢查询日志（设置slow_query_log=ON，long_query_time=1，log_queries_not_using_indexes=ON）；（2）使用pt-query-digest工具解析慢查询日志，统计高频、高耗时、高扫描行数的查询；（3）对目标查询执行EXPLAIN（或EXPLAINANALYZE），查看执行计划中的type（访问类型）、key（使用的索引）、rows（预估扫描行数）、Extra（额外信息如Usingfilesort/Usingtemporary）；（4）检查是否存在全表扫描（type=ALL）、文件排序（Usingfilesort）、临时表（Usingtemporary）；（5）优化方向：添加合适索引（覆盖索引、联合索引）、重写查询（避免SELECT、拆分复杂子查询）、调整数据分布（分库分表）。3.对比MySQL8.0与5.7在索引优化方面的主要改进。答案：主要改进：（1）隐藏索引（InvisibleIndex）：允许创建不被优化器使用的索引，用于测试索引效果而不影响当前性能；（2）降序索引（DescendingIndex）：支持显式创建降序索引（如INDEXidx(aDESC,bASC)），优化ORDERBYDESC查询；（3）函数索引（FunctionalIndex）：支持对表达式或函数结果创建索引（如INDEXidx((YEAR(create_time)))）；（4）即时ALTER（InstantALTER）：部分ALTERTABLE操作（如ADD/DROPCOLUMN）仅修改元数据，无需重建表；（5）IN子句优化：对IN列表中的值进行排序和去重，减少索引扫描次数。4.说明MySQL主从复制延迟的常见原因及解决方法。答案：常见原因及解决方法：（1）从库硬件性能不足（CPU/内存/磁盘慢）：升级从库硬件，或使用级联复制（多级从库分担压力）；（2）主库写入压力大，binlog事件过多：优化主库写入（批量插入、减少事务大小），使用并行复制（设置slave_parallel_type=LOGICAL_CLOCK，slave_parallel_workers=N）；（3）从库SQL线程单线程执行relaylog：开启多线程复制（MySQL5.7+支持），按库或按事务组并行回放；（4）大事务导致延迟：避免大事务（如一次性插入10万行），拆分为小事务；（5）网络延迟：检查主从之间的网络带宽，使用专线或就近部署从库。5.设计一个电商订单表（order），包含字段：订单ID（主键）、用户ID、下单时间、支付状态（0未支付，1已支付）、总金额。要求：（1）支持快速查询某用户最近30天内已支付的订单（按下单时间倒序）；（2）支持快速统计某用户当月已支付订单的总金额。请给出表结构（含索引设计）并说明理由。答案：表结构：```sqlCREATETABLE`order`(`order_id`BIGINTUNSIGNEDNOTNULLAUTO_INCREMENTCOMMENT'订单ID',`user_id`BIGINTUNSIGNEDNOTNULLCOMMENT'用户ID',`create_time`DATETIMENOTNULLCOMMENT'下单时间',`pay_status`TINYINTNOTNULLDEFAULT0COMMENT'支付状态（0未支付，1已支付）',`total_amount`DECIMAL(10,2)NOTNULLCOMMENT'总金额',PRIMARYKEY(`order_id`),KEY`idx_user_pay_time`(`user_id`,`pay_status`,`create_time`)COMMENT'用户-支付状态-时间索引',KEY`idx_user_pay_month`(`user_id`,`pay_status`,(YEAR(create_time)),(MONTH(create_time)))COMMENT'用户-支付状态-年月函数索引')ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COMMENT='订单表';```设计理由：（1）查询需求1（某用户最近30天已支付订单）：使用联合索引idx_user_pay_time（user_id,pay_status,create_time），满足WHEREuser_id=?ANDpay_status=1ANDcreate_time>=NOW()-INTERVAL30DAY的过滤条件，且按create_time倒序排序时索引可直接利用（需确保索引顺序与查询条件顺序一致）；（2）统计需求2（某用户当月已支付订单总金额）：由于需要按年月统计，创建函数索引idx_user_pay_month，存储YEAR(create_time)和MONTH(create_time)的计算结果，优化WHEREuser_id=?ANDpay_status=1ANDYEAR(create_time)=?ANDMONTH(create_time)=?的查询，避免全表扫描。四、操作题（每题10分，共20分）1.现有表`employee`结构如下：```sqlCREATETABLEemployee(idINTPRIMARYKEY,nameVARCHAR(50),dept_idINT,salaryDECIMAL(10,2),hire_dateDATE);```要求：（1）为dept_id字段添加外键，关联department表的dept_id（假设department表已存在）；（2）查询各部门中薪资最高的员工姓名、部门ID及薪资（要求使用窗口函数）；（3）将2023年1月1日前入职的员工薪资上调5%。答案：（1）添加外键：```sqlALTERTABLEemployeeADDCONSTRAINTfk_deptFOREIGNKEY(dept_id)REFERENCESdepartment(dept_id)ONDELETESETNULLONUPDATECASCADE;```（2）查询各部门薪资最高员工（窗口函数）：```sqlWITHranked_empAS(SELECTname,dept_id,salary,RANK()OVER(PARTITIONBYdept_idORDERBYsalaryDESC)ASrkFROMemployee)SELECTname,dept_id,salaryFROMranked_empWHERErk=1;```（3）上调2023年1月1日前入职员工薪资：```sqlUPDATEemployeeSETsalary=salary1.05WHEREhire_date<'2023-01-01';```2.使用EXPLAIN分析以下查询的执行计划，并说明索引优化建议：```sqlSELECTu.username,o.order_id,o.create_timeFROMuseruLEFTJOINorderoONu.user_id=o.user_idWHEREo.pay_status=1ANDu.register_time>'2024-01-01';```答案：执行计划分析（假设user表主键为user_id，order表有索引idx_user_pay（user_id,pay_status））：驱动表：可能为user表（LEFTJOIN时通常左表为驱动表），但WHERE条件包含o.pay_status=1（非NULL），实际会转换为INNERJOIN，驱动表可能变为order表；访问类型：order表通过idx_user_pay索引过滤pay_status=1的记录（type=ref）；连接类型：user表通过user_id与order表连接（type=eq_ref，使用主键索引）；额外信息（Extra）：可能出现Usingwhere（过滤u.register_time条件）、Usingindex（若user表有覆盖索引）。优化建议：（1）在order表上创建覆盖索引idx_user_pay_time（user_id,pay_status,create_time），避免回表获取create_time；（2）在user表上创建索引idx_register（register_time,user_id,username），覆盖WHEREu.register_time>'2024-01-01'条件及SELECT的username字段，避免全表扫描；（3）若数据量极大，考虑将user表的register_time字段按时间范围分区，减少扫描数据量。五、综合题（20分）某电商平台的商品评论表（comment）结构如下，数据量约5000万条：```sqlCREATETABLEcomment(comment_idBIGINTUNSIGNEDNOTNULLAUTO_INCREMENT,product_idBIGINTUNSIGNEDNOTNULL,user_idBIGINTUNSIGNEDNOTNULL,contentTEXTNOTNULL,scoreTINYINTNOTNULLCOMMENT'评分（1-5分）',create_timeDATETIMENOTNULL,is_deletedTINYINTNOTNULLDEFAULT0COMMENT'是否删除（0未删除，1已删除）',PRIMARYKEY(comment_id),KEY`idx_product`(product_id),KEY`idx_user`(user_id))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;```业务需求：（1）查询某商品（product_id=1001）最近30天未删除的评论，按create_time倒序，每页20条（分页查询）；（2）统计某商品近1年各评分（1-5分）的评论数量；（3）定期删除1年前的已删除评论（is_deleted=1且create_time<'2023-01-01'）。请设计优化方案（包括索引调整、表结构优化、查询优化、数据归档等），并说明理由。答案：优化方案设计1.索引调整（1）为查询需求1（商品最近30天未删除评论）创建联合索引：`INDEXidx_product_time_deleted(product_id,create_timeDESC,is_deleted)`理由：覆盖WHEREproduct_id=1001、is_deleted=0、create_time>=NOW()-30天的条件，且按create_time倒序排序，避免文件排序（Usingfilesort），索引顺序与查询条件完全匹配。（2）为统计需求2（商品近1年各评分数量）创建覆盖索引：`INDEXidx_product_time_score(product_id,create_time,score)`理由：过滤product_id和create_time范围（近1年），同时包含score字段，可直接通过索引统计各评分数量（COUNT()或SUM(IF(score=?,1,0))），避免回表读取content等无关字段。2.表结构优化（1）拆分大字段：将content字段单独存储到另一张表（comment_content），通过comment_id关联。```sqlCREATETABLEcomment_content(comment_idBIGINTUNSIGNEDNOTNULLPRIMARYKEY,contentTEXTNOTNULL)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;```理由：TEXT类型占用空间大，拆分后主表comment仅保留小字段，减少IO消耗，提升索引效率。（2）添加分区：按create_time做范围分区（RANGE），例如按月份分区。```sqlALTERTABLEcommentPARTITIONBYRANGE(TO_DAYS(create_time))(PARTITIONp202301VALUESLESSTHAN(TO_DAYS('2023-02-01')),PARTITIONp202302VALUESLESSTHAN(TO_DAYS('2023-03-01')),...);```理由：分区后，查询最近30天数据时仅扫描最新分区，删除1年前数据时直接删除旧分区（DROPPARTITION），比逐条删除更高效。3.查询优化（1）分页查询优化：避免使用`LIMIToffset,20`（offset过大时扫描行数多），改用覆盖索引+书签记录上次查询的最大create_time和comment_id。优化后查询：```sqlSELECTment_id,c.user_id,c.score,c.create_time,cc.contentFROMcommentcJOINcomment_contentccONment_id=ment_idWHEREduct_id=1001ANDc.is_deleted=0ANDc.create_time<='2024-10-1012:00:00'-上次查询的最小时间AND(c.create_time<'2024-10-1012:00:00'OR(c.create_time='2024-10-1012:00:00'AND