2025年mysql数据库笔试试题及答案全

2025年mysql数据库笔试试题及答案全一、单项选择题（共10题，每题2分）1.MySQL8.0版本默认的字符集是（）A.utf8B.utf8mb4C.gbkD.latin1答案：B解析：MySQL5.7及之前版本默认字符集为latin1，部分定制版本默认的utf8实际为utf8mb3，仅支持最多3字节的UTF-8字符，无法存储emoji等4字节字符；MySQL8.0开始默认字符集调整为utf8mb4，支持完整的UTF-8字符，无需额外配置即可适配多语言、emoji等存储需求。2.InnoDB存储引擎中，事务提交时redolog的刷盘策略由innodb_flush_log_at_trx_commit参数控制，当该参数设置为1时代表的含义是（）A.每秒将redologbuffer写入oscache并刷盘B.每次事务提交将redologbuffer写入oscache，由操作系统决定刷盘时机C.每次事务提交将redologbuffer写入oscache并强制刷盘D.事务提交时不操作redolog，由后台线程统一处理答案：C解析：innodb_flush_log_at_trx_commit三个核心取值的含义：0为每秒刷盘一次，宕机最多丢失1秒数据；1为每次提交强制刷盘，符合ACID的持久性要求，数据零丢失，生产环境高安全要求场景必须设置为1；2为每次提交写入oscache，每秒刷盘一次，宕机最多丢失1秒数据但性能高于取值1的场景。3.InnoDB存储引擎中，唯一可以存储完整行数据的索引类型是（）A.普通二级索引B.唯一索引C.聚簇索引D.覆盖索引答案：C解析：聚簇索引的核心特点是索引与行数据存储在一起，叶子节点存储完整的行记录，每张表仅能存在一个聚簇索引，默认以主键作为聚簇索引，无主键时选择第一个非空唯一索引，无符合条件的索引则自动生成隐式rowid作为聚簇索引。4.下列关于MVCC的描述中，符合MySQL8.0RC隔离级别特性的是（）A.事务启动时生成全局唯一的ReadView，整个事务生命周期内复用B.每次执行查询语句时都会生成新的ReadViewC.可以解决幻读问题D.读取的数据一定是最新提交的答案：B解析：RC（读已提交）隔离级别下，每次执行查询都会重新生成ReadView，因此同一个事务内的多次查询可以读取到其他事务最新提交的内容；RR（可重复读）级别下ReadView在事务首次查询时生成，整个事务复用；仅RR级别下的next-keylock可以解决幻读问题；RC级别仅保证读取的内容是已提交的，不一定是最新的（可能存在查询过程中其他事务提交的情况）。5.MySQL8.0中，binlog默认的格式是（）A.STATEMENTB.ROWC.MIXEDD.TEXT答案：B解析：MySQL5.7及之前版本binlog默认格式为STATEMENT，基于SQL语句记录日志，存在主从不一致的风险；MySQL8.0开始默认binlog格式为ROW，基于行数据变更记录日志，仅记录行的修改前后内容，避免主从不一致问题，生产环境推荐使用ROW格式。6.下列锁类型中，不属于InnoDB行级锁算法的是（）A.记录锁B.间隙锁C.意向锁D.临键锁答案：C解析：意向锁属于表级锁，用于快速判断表级锁与行级锁的冲突，避免逐行检查锁状态；行级锁算法包括记录锁（锁定单行记录）、间隙锁（锁定索引间隙，防止插入）、临键锁（记录锁+间隙锁的组合，解决幻读问题）。7.下列参数中，用于控制慢查询阈值的是（）A.slow_query_logB.long_query_timeC.log_queries_not_using_indexesD.slow_query_log_file答案：B解析：slow_query_log为慢查询日志开关，long_query_time为慢查询阈值，单位为秒，执行时间超过该值的SQL会被记录到慢查询日志；log_queries_not_using_indexes用于开启未走索引的SQL记录；slow_query_log_file为慢查询日志存储路径。8.联合索引（a,b,c）无法适用的查询条件是（）A.wherea=1andb=2andc=3B.wherea=1andb>2andc=3C.wherea=1orderbyb,cD.whereb=2andc=3答案：D解析：联合索引遵循最左匹配原则，查询条件必须包含索引左侧的前缀字段才能命中索引，D选项未包含最左侧的a字段，无法命中该联合索引；B选项中范围查询后的c字段无法使用索引，但a和b字段可以命中索引。9.InnoDB默认的事务隔离级别是（）A.读未提交（READUNCOMMITTED）B.读已提交（READCOMMITTED）C.可重复读（REPEATABLEREAD）D.串行化（SERIALIZABLE）答案：C解析：Oracle、PostgreSQL等数据库默认隔离级别为读已提交，InnoDB默认隔离级别为可重复读，通过MVCC和next-keylock实现了RR级别下无幻读的特性。10.下列高可用方案中，数据一致性最高的是（）A.异步主从复制B.半同步主从复制C.MGR单主模式D.MGR多主模式答案：C解析：MGR基于Paxos算法实现，需要多数节点写入成功才返回事务提交，RPO=0，数据零丢失；半同步复制仅保证至少一个从节点收到binlog，存在数据丢失的风险；MGR多主模式存在写入冲突的概率，一致性低于单主模式。二、多项选择题（共5题，每题4分，多选、少选、错选均不得分）1.下列场景中，会导致索引失效的有（）A.wherenamelike'%张三'B.wheredate(create_time)='2025-01-01'C.wherephonephone字段为varchar类型）D.wherea=1orb=2（a有索引，b无索引）答案：ABCD解析：A选项左模糊匹配不符合B+树前缀匹配规则；B选项对索引字段使用函数，无法命中索引；C选项触发隐式类型转换，索引失效；D选项OR条件一侧无索引，优化器会选择全表扫描。2.关于undolog的作用，下列描述正确的有（）A.实现事务回滚B.用于MVCC多版本读C.实现crash-safeD.用于主从复制答案：AB解析：undolog存储数据修改前的历史版本，用于事务回滚和MVCC多版本读；redolog用于实现crash-safe；binlog用于主从复制。3.下列关于InnoDB页的描述，正确的有（）A.是InnoDB最小的磁盘存储单元B.默认大小为16KBC.每个页中至少存储2条行记录D.页大小可以自定义配置为8KB答案：ABD解析：InnoDB以页为最小的磁盘读写单元，默认大小为16KB，支持自定义配置为4KB、8KB、32KB等；当行记录大小超过页的一半时，会触发行溢出存储，单个页可能仅存储1条记录的前缀数据，因此C错误。4.下列属于MySQL8.0新增特性的有（）A.窗口函数B.原子DDLC.通用表表达式（CTE）D.JSON字段支持答案：ABC解析：JSON字段支持是MySQL5.7新增的特性，其余选项均为MySQL8.0新增的核心特性。5.分库分表场景下，适合作为全局唯一主键的方案有（）A.UUIDB.雪花算法C.号段模式D.数据库自增序列答案：BCD解析：UUID为无序字符串，写入InnoDB会导致索引分裂，性能极低，不推荐作为主键；其余方案均为生产环境常用的全局唯一主键方案。三、填空题（共10题，每题2分）1.InnoDB最小的磁盘存储单元是____，默认大小为____KB。答案：页（Page）、162.MySQL中，用于查看SQL执行计划的命令是____。答案：explain3.InnoDB中，____锁是记录锁和间隙锁的组合，用于解决RR隔离级别下的幻读问题。答案：next-keylock（临键锁）4.binlog的三种格式分别是STATEMENT、____、MIXED。答案：ROW5.事务的四大特性ACID分别是原子性、一致性、____、持久性。答案：隔离性6.联合索引的匹配原则是____。答案：最左前缀匹配7.MySQL8.0中，用于实现递归查询的语法是____。答案：递归CTE（WITHRECURSIVE）8.InnoDB中，两个事务互相持有对方需要的锁，导致事务无法继续执行的现象称为____。答案：死锁9.慢查询日志分析常用的Percona工具是____。答案：pt-query-digest10.MGR的中文全称是____，基于Paxos算法实现多副本数据一致。答案：MySQL组复制（MySQLGroupReplication）四、简答题（共7题，每题10分）1.请简述MySQL8.0相对于5.7版本的核心特性升级（至少列出6项）答案：1.默认字符集改为utf8mb4，支持全量UTF-8字符，无需额外配置即可存储emoji等4字节字符；2.支持原子DDL操作，不会出现DDL中途失败导致的元数据不一致问题；3.支持窗口函数，可高效实现排名、分组内排序等复杂统计需求，无需编写嵌套子查询；4.支持通用表表达式（CTE），包括递归CTE，可简化树形结构查询、连续数值生成等复杂逻辑；5.InnoDB性能大幅提升，包括缓冲池碎片整理优化、DDL操作并行度提升、高并发场景下的锁竞争优化，官方基准测试性能比5.7高2倍以上；6.原生支持不可见索引、降序索引，不可见索引可在不删除索引的前提下验证索引的必要性，降序索引可优化包含DESC排序的查询性能；7.原生支持JSON字段的大量操作函数，以及JSON字段的部分更新，性能比5.7的JSON存储提升3倍以上；8.高可用方案原生支持InnoDBCluster、MGR，无需依赖第三方组件即可实现多副本高可用、自动故障转移。2.请详细描述InnoDBMVCC的实现原理，以及RR和RC隔离级别下MVCC的差异答案：MVCC（多版本并发控制）是InnoDB实现非锁定读的核心机制，通过读取数据的历史版本实现读写不阻塞，大幅提升并发场景下的性能。实现核心依赖三个组件：1.隐式字段：InnoDB每行数据除了用户定义的字段外，会新增三个隐式字段：DB_TRX_ID（最近一次修改该行的事务ID）、DB_ROLL_PTR（回滚指针，指向undolog中该行的历史版本）、DB_ROW_ID（隐式主键，无自定义主键时生成）；2.undolog：存储数据的历史版本，当事务修改数据时，会先将修改前的版本写入undolog，回滚指针指向该历史版本，多版本的数据通过回滚指针组成版本链；3.ReadView：一致性读视图，由四个核心字段组成：m_ids（当前活跃的未提交事务ID集合）、min_trx_id（活跃事务的最小ID）、max_trx_id（下一个要分配的事务ID）、creator_trx_id（当前生成ReadView的事务ID）。可见性判断规则：当查询某行数据时，遍历版本链的每个版本的DB_TRX_ID：①如果等于creator_trx_id，说明是当前事务自己修改的，可见；②如果小于min_trx_id，说明该版本的事务已经提交，可见；③如果大于等于max_trx_id，说明该版本的事务是在ReadView生成之后才启动的，不可见；④如果在min_trx_id和max_trx_id之间，判断是否在m_ids集合中，如果在则说明事务未提交，不可见，如果不在则说明已提交，可见。RR和RC级别下的MVCC差异：RR（可重复读）级别下，ReadView在事务启动后执行第一条查询语句时生成，整个事务生命周期内复用该ReadView，因此同一个事务内的多次查询结果一致，实现可重复读；RC（读已提交）级别下，每次执行查询语句都会重新生成新的ReadView，因此同一个事务内的多次查询可以读取到其他事务已提交的最新数据，仅保证读取到的数据都是已提交的。3.请列出至少8种常见的索引失效场景，并简述对应的优化思路答案：1.左模糊匹配查询：如`wherenamelike'%张三'`，B+树的前缀匹配特性无法生效，优化思路：尽量使用右模糊匹配`like'张三%'`，如果必须全模糊匹配，可引入Elasticsearch等全文检索引擎实现；2.对索引字段使用函数、算术运算、类型转换：如`wheredate(create_time)='2025-01-01'`、`whereid+1=10`、`wherephone=138xxxxxxx`（phone为varchar类型，传入数值会触发隐式转换），优化思路：将运算转移到条件值一侧，改为`wherecreate_timebetween'2025-01-0100:00:00'and'2025-01-0123:59:59'`、`whereid=9`、`wherephone='138xxxxxxx'`；3.联合索引不满足最左匹配原则：如联合索引(a,b,c)，查询条件仅包含b、c，或仅包含a和c且中间跳过b，索引无法完全生效，优化思路：调整查询条件顺序，或调整联合索引的字段顺序，让查询条件覆盖最左前缀；4.OR条件两侧的字段不全有索引：如`wherea=1orb=2`，如果a有索引b无索引，会触发全表扫描，优化思路：给OR两侧的字段都建索引，或改为UNION拆分两个查询；5.索引列存在NULL值，且查询条件使用isnull/isnotnull：部分场景下会导致索引失效，优化思路：字段定义时设置非空默认值，避免NULL值的存储和查询；6.数据量过小，优化器判断全表扫描性能高于索引扫描：如仅几百条数据的表，优化器会选择全表扫描，无需优化，数据量上涨后会自动走索引；7.范围查询之后的索引字段失效：如联合索引(a,b,c)，查询条件为`wherea>10andb=2andc=3`，仅a字段的索引生效，b和c的索引无法使用，优化思路：将等值查询字段放在联合索引左侧，范围查询字段放在右侧；8.使用select*查询，无法覆盖索引：如果查询的字段不在索引中，需要回表查询，当回表数据量过大时优化器会选择全表扫描，优化思路：只查询需要的字段，建立覆盖索引，让查询的所有字段都包含在索引中，避免回表。4.请对比redolog、undolog、binlog三类日志的核心差异，并简述InnoDB两阶段提交的流程答案：三类日志的差异：1.所属层面：redolog和undolog是InnoDB存储引擎层的日志，binlog是MySQLServer层的日志，所有存储引擎都可以生成；2.日志类型：redolog是物理日志，记录的是数据页的物理修改；undolog是逻辑日志，记录的是数据修改前的逻辑状态，用于回滚和MVCC；binlog是逻辑日志，记录的是SQL语句的原始逻辑或行数据的变更内容；3.写入时机：redolog在事务执行过程中持续写入redologbuffer，事务提交时刷盘；undolog在事务修改数据之前写入；binlog在事务提交完成前写入binlogcache，事务提交时刷盘；4.生命周期：redolog是循环写入的，大小固定，写满后会覆盖旧的已持久化到磁盘的日志；undolog会在事务提交且没有活跃事务引用其历史版本时，由purge线程清理；binlog是追加写入的，不会覆盖旧日志，可保留指定时长用于数据备份、主从同步；5.作用：redolog用于实现crash-safe，数据库宕机重启后可通过redolog恢复未持久化到磁盘的已提交事务数据，保证数据不丢失；undolog用于事务回滚和MVCC多版本读；binlog用于数据备份、主从复制、数据恢复。两阶段提交流程：为了保证redolog和binlog的一致性，InnoDB采用两阶段提交：1.第一阶段（prepare阶段）：事务执行完成，将redolog写入buffer并刷盘，标记redolog为prepare状态；2.第二阶段（commit阶段）：将binlog写入并刷盘，然后将redolog的状态改为commit状态，事务正式提交。宕机恢复逻辑：如果redolog是commit状态，直接提交事务；如果redolog是prepare状态，检查对应的binlog是否存在且完整，如果存在则提交事务，否则回滚事务，保证两类日志的数据一致。5.请简述MySQL慢查询优化的全流程答案：1.开启慢查询日志：配置参数slow_query_log=ON，long_query_time根据业务需求设置（一般生产环境设置为1s，高并发场景可设置为0.5s），slow_query_log_file指定日志存储路径，可开启log_queries_not_using_indexes记录未走索引的SQL；2.慢查询日志分析：使用pt-query-digest或mysqldumpslow工具分析慢查询日志，按照执行次数、累计耗时、锁等待时间等维度排序，优先优化TopN的慢SQL；3.执行计划分析：对筛选出的慢SQL执行explain命令，重点关注type字段（是否为range、ref、eq_ref、const，避免all全表扫描）、key字段（是否命中了预期的索引）、rows字段（扫描的行数是否过高）、Extra字段（是否出现Usingfilesort、Usingtemporary等需要优化的标识）；4.索引优化：根据执行计划判断是否需要新增索引、调整联合索引顺序、删除冗余索引，优先建立覆盖索引避免回表，避免重复索引和无用索引，索引总数建议不超过单表6个；5.SQL改写优化：拆分复杂的多表关联查询（关联表不建议超过3张）、避免大事务、避免select*、调整关联顺序、使用批量操作代替单条循环操作、避免相关子查询改为join关联；6.表结构优化：调整字段类型避免隐式转换、拆分大字段（如text、blob）到单独的扩展表、避免使用过多的NULL字段、对于冷热分离的场景拆分冷热数据到不同的表；7.架构优化：如果单表数据量过大（超过5000万行）且查询性能无法通过索引优化满足，考虑分库分表；对于读多写少的场景引入读写分离、缓存层（Redis等）分担读压力；对于复杂统计查询引入OLAP引擎（如ClickHouse）处理。6.请简述InnoDB的锁机制，以及next-keylock如何解决幻读问题答案：InnoDB的锁按照粒度分为表级锁和行级锁：1.表级锁：包括元数据锁（MDL锁，用于保护表结构的一致性，执行DDL时会加排他MDL锁，执行DML和查询时会加共享MDL锁，避免DDL和DML冲突）、意向锁（意向共享锁IS、意向排他锁IX，用于表级锁和行级锁的冲突快速判断，避免逐行检查锁状态）；2.行级锁：包括共享锁（S锁，读锁，加锁后其他事务只能加S锁不能加X锁）、排他锁（X锁，写锁，加锁后其他事务不能加任何锁），还有三种行锁算法：记录锁（lockrec，锁定单行索引记录）、间隙锁（gaplock，锁定两个索引记录之间的间隙，不包含记录本身，防止其他事务在间隙中插入数据）、next-keylock（临键锁，是记录锁+间隙锁的组合，锁定范围包含记录本身和前面的间隙，为左开右闭区间）。幻读是指同一个事务内两次相同的范围查询，第二次查询出现了第一次没有的新数据，属于隔离性问题。InnoDB在RR隔离级别下通过next-keylock解决幻读：当执行范围查询（如select*fromtwhereid>10forupdate）时，会对查询涉及的所有索引范围加next-keylock，不仅锁定已存在的符合条件的记录，还会锁定记录之间的所有间隙，防止其他事务在该范围内插入新的记录，因此同一个事务内的多次范围查询结果一致，不会出现幻读。需要注意的是，当查询条件命中唯一索引且是等值查询时，next-keylock会退化为记录锁，不需要加间隙锁。7.请对比常见的MySQL高可用方案的优缺点和适用场景答案：常见高可用方案包括：1.一主多从半同步复制：架构为1个主节点负责写，多个从节点负责读，半同步复制保证至少一个从节点收到binlog，故障转移需要依赖第三方组件（MHA、Keepalived等）；优点：架构简单、部署成本低、读写分离扩展方便、性能损耗低；缺点：故障转移存在秒级延迟、极端场景下可能丢失少量数据、从节点复制延迟问题无法完全避免；适用场景：数据一致性要求不极高、读压力大的中小型业务；2.MGR（MySQLGroupReplication）：原生支持的多副本同步复制方案，基于Paxos算法，多数节点写入成功才返回提交，支持单主模式和多主模式，自动故障转移；优点：数据一致性高（RPO=0）、自动故障转移速度快（秒级）、无需第三方组件；缺点：最多支持9个节点、写入性能比半同步低10%-20%、对网络延迟要求高（建议跨节点延迟低于2ms）；适用场景：数据一致性要求高、需要高可用的中大型业务；3.InnoDBCluster：基于MGR+MySQLRouter的官方高可用方案，封装了节点管理、路由转发能力，对外提供统一的访问入口，故障转移对业务透明；优点：官方原生支持、运维成本低、自动路由、内置故障探测；缺点：和MGR一致存在写入性能损耗；适用场景：不想维护第三方高可用组件的业务场景；4.云厂商托管RDS：云服务商提供的托管MySQL服务，默认支持多可用区部署、自动备份、自动故障转移、弹性扩展；优点：无需运维、可用性高达99.99%、支持弹性扩缩容、配套监控备份能力完善；缺点：成本比自建高、定制化能力受限；适用场景：大部分互联网业务，尤其是不想投入大量运维成本的创业团队。五、实操题（共2题，每题15分）1.现有业务订单表t_order（InnoDB引擎），表结构如下：```sqlCREATETABLE`t_order`(`order_id`bigintunsignedNOTNULLAUTO_INCREMENTCOMMENT'订单ID',`user_id`bigintunsignedNOTNULLCOMMENT'用户ID',`store_id`bigintunsignedNOTNULLCOMMENT'门店ID',`order_amount`decimal(10,2)NOTNULLCOMMENT'订单金额',`order_status`tinyintNOTNULLCOMMENT'订单状态：1待支付2已支付3已取消4已完成',`create_time`datetimeNOTNULLCOMMENT'创建时间',PRIMARYKEY(`order_id`),KEY`idx_user_ctime`(`user_id`,`create_time`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;```现有三个高频查询SQL：SQL1:`selectorder_id,order_amount,order_statusfromt_orderwhereuser_id=?andcreate_timebetween?and?;`SQL2:`select*fromt_orderwherestore_id=?andorder_status=?orderbycreate_timedesclimit10;`SQL3:`selectsum(order_amount)fromt_orderwhereuser_id=?andorder_status=2andcreate_time>='2025-01-01';`请回答以下问题：（1）SQL1是否需要优化？如果需要请给出优化方案答案：SQL1当前的联合索引idx_user_ctime(user_id,create_time)已经覆盖了查询条件，但查询的字段order_amount、order_status不在索引中，需要回表查询，当查询的数据量较大时性能较差。优化方案为调整联合索引为覆盖索引：`idx_user_ctime(user_id,create_time,order_amount,order_status)`，因为order_id是主键，已经默认包含在二级索引中，不需要额外添加，调整后SQL1的所有查询字段都包含在索引中，无需回表，性能可提升30%以上。（2）请为SQL2创建最优的索引，说明原因答案：创建联合索引`idx_store_status_ctime(store_id,order_status,create_timedesc)`。原因：SQL2的查询条件是store_id和order_status等值查询，然后按照create_time降序排序，将等值查询的字段放在联合索引左侧，排序字段放在右侧且创建为降序索引，查询时可以直接通过索引获取排序后的结果，避免filesort，且limit10只需要读取索引的前10条记录即可返回，性能最优。（3）SQL3当前命中idx_user_ctime索引，但执行耗时较高，请问是什么原因，怎么优化？答案：原因：现有索引仅包含user_id和create_time，需要根据索引扫描的结果回表判断order_status是否为2，当用户的订单量较大时，回表的IO开销高，且需要过滤不符合order_status条件的记录，性能损耗大。优化方案为调整联合索引为`idx_user_status_ctime(user_id,order_status,create_time,order_amount)`，等值查询条件user_id和order_status放在最左侧，然后是范围查询条件create_time，最后是需要统计的order_amount字段，形成覆盖索引，无需回表即可完成sum统计，性能可提升数倍。2.生产环境出现主从复制延迟过高的问题，请简述排查流程和解决方案答案：排查流程：1.首先登录从节点执行`showslavestatus\G`，重点关注Seconds_Behind_Master参数的延迟数值，以及Relay_Master_Log_File、Exec_Master_Log_Pos和主节点`showmasterstatus`输出的当前binlog位置的差距，判断是IO线程延迟还是SQL线程延迟：如果Master_Log_File和Relay_Master_Log_File差距大，说明是IO线程延迟；如果Master_Log_File和Relay_Master_Log_File一致，但Exec_Master_Log_Pos和主节点的Position差距大，说明是SQL线程延迟。2.如果是IO线程延迟：检查主从节点之间的网络延迟（ping、mtr测试）、带宽占用（iftop查看流量），主节点的binlog刷盘速度，从节点的磁盘IO写入速度，是否存在网络拥塞、带宽不足、从节点磁盘性能过低的问题。3.如果是SQL线程延迟：查看主节点的写入QPS，是否有大事务、批量DDL操作，从节点的硬件配置是否低于主节点，是否开启了并行复制，业务是否有大量的小事务提交。解决方案：1.网络层面：将主从节点部署在同一可用区，提升网络带宽，降低网络延迟，避免跨地域部署主从节点；2.配置层面：从节点关闭不必要的日志（如log_slave_updates、审计日志、慢查询日志等），开启MySQL8.0的逻辑时钟并行复制（参数设置为`sl