mysql数据库题库和答案

mysql数据库题库和答案一、单项选择题1.下列关于MyISAM和InnoDB存储引擎的描述，错误的是（）A.MyISAM不支持事务，InnoDB支持事务B.MyISAM支持表级锁，InnoDB支持行级锁和表级锁C.MyISAM的非聚簇索引叶子节点存储数据地址，InnoDB二级索引叶子节点存储主键值D.MyISAM支持外键约束，InnoDB不支持外键约束答案：D解析：MyISAM不支持外键约束，InnoDB支持外键约束，可通过外键保证关联表的数据一致性。其余选项描述均正确：MyISAM是MySQL5.5之前的默认存储引擎，不支持事务、行锁，仅支持表级锁，读写并发性能差，采用非聚簇索引结构，索引与数据文件分离，内置表行数计数器，无WHERE条件的COUNT(*)查询性能极高；InnoDB是MySQL5.5及之后的默认存储引擎，支持ACID事务、行级锁、外键，采用聚簇索引结构，数据可靠性和并发性能更高，适合高并发事务场景。2.事务的四大特性中，指事务一旦提交，对数据的修改就是永久的，即便系统宕机也不会丢失的是（）A.原子性（Atomicity）B.一致性（Consistency）C.隔离性（Isolation）D.持久性（Durability）答案：D解析：原子性指事务的所有操作要么全部执行成功，要么全部失败回滚；一致性指事务执行前后数据始终处于一致状态，不存在中间状态；隔离性指并发事务之间互相隔离，互不干扰；持久性指提交后的修改永久生效，符合题干描述。3.下列SQL命令中，用于删除整张表结构和所有数据的是（）A.DELETEFROMtable_nameB.TRUNCATETABLEtable_nameC.DROPTABLEtable_nameD.ALTERTABLEtable_nameDROP答案：C解析：DELETE用于删除表中指定行的数据，可加WHERE条件过滤，支持事务回滚；TRUNCATE用于清空整张表的数据，保留表结构，执行速度比DELETE快，不支持事务回滚；DROP用于删除整张表的结构、数据、索引、约束等所有内容；ALTERTABLE用于修改表结构，无整体删除表的能力。4.现有联合索引idx(a,b,c)，下列查询语句中无法用到该索引的是（）A.SELECT*FROMtWHEREa=1ANDb=2B.SELECT*FROMtWHEREa=1ANDb>2ANDc=3C.SELECT*FROMtWHEREb=2ANDc=3D.SELECT*FROMtWHEREa=1ORDERBYb答案：C解析：联合索引遵循最左前缀匹配原则，查询必须从索引的最左列开始匹配，遇到范围查询就停止后续列的匹配。选项C的查询条件跳过了最左列a，无法触发索引匹配；选项A匹配前两列，选项B匹配a、b两列（b为范围查询，c无法匹配），选项D匹配a列且排序用到b列，均可以用到该联合索引。5.MySQLInnoDB引擎默认的事务隔离级别是（）A.读未提交（READUNCOMMITTED）B.读已提交（READCOMMITTED）C.可重复读（REPEATABLEREAD）D.串行化（SERIALIZABLE）答案：C解析：读未提交是最低隔离级别，存在脏读、不可重复读、幻读问题；读已提交是Oracle、PostgreSQL等数据库的默认隔离级别，解决了脏读问题，存在不可重复读、幻读问题；可重复读是MySQLInnoDB的默认隔离级别，通过MVCC和临键锁解决了脏读、不可重复读、幻读问题；串行化是最高隔离级别，通过强制事务串行执行解决所有并发问题，但并发性能极差，极少使用。6.InnoDB行级锁的实现依赖的是（）A.表结构B.索引C.事务D.日志答案：B解析：InnoDB行级锁是通过锁索引项实现的，如果查询条件未命中任何索引，行锁会升级为表锁，锁住整张表的所有数据，导致并发性能大幅下降。二、填空题1.MySQL中用于保证表中某列数据唯一且非空的约束是____。答案：主键约束（PRIMARYKEY）解析：唯一约束（UNIQUE）仅保证数据唯一，允许存在NULL值；主键约束天然具备唯一和非空两个特性，每张表只能有一个主键。2.InnoDB实现MVCC（多版本并发控制）依赖的三个核心组件是隐藏列、____、____。答案：undo日志、ReadView（读视图）解析：隐藏列包括每行数据的事务ID（DB_TRX_ID）、回滚指针（DB_ROLL_PTR）、隐藏主键（DB_ROW_ID）；undo日志存储数据的历史版本，形成版本链；ReadView是数据快照，通过版本匹配规则判断事务可见的数据版本。3.MySQL二进制日志（binlog）的三种格式分别是STATEMENT、____、____。答案：ROW、MIXED解析：STATEMENT格式存储实际执行的SQL语句，日志量小，但存在主从不一致风险；ROW格式存储每行数据的修改前后值，日志量大，但数据一致性高，是MySQL8.0的默认binlog格式；MIXED格式会自动根据SQL语句选择合适的日志格式，兼顾性能和一致性。4.SQL语句中，用于分组前过滤数据的关键字是____，用于分组后过滤聚合结果的关键字是____。答案：WHERE、HAVING解析：WHERE执行优先级高于GROUPBY，不允许使用聚合函数；HAVING执行优先级低于GROUPBY，仅能用于过滤分组后的聚合结果，支持使用聚合函数。5.InnoDB的三大核心特性分别是插入缓冲（ChangeBuffer）、____、____。答案：二次写（DoubleWrite）、自适应哈希索引（AdaptiveHashIndex）解析：插入缓冲用于优化非唯一二级索引的写入性能，减少随机IO；二次写用于避免页写入时发生部分页损坏，保证数据可靠性；自适应哈希索引由InnoDB自动生成，用于加速热点数据的查询性能。三、简答题1.请列举索引的优缺点，以及适合建索引和不适合建索引的场景。答案：索引是数据库中对一列或多列数据排序的存储结构，类似图书目录，用于加速查询效率。优点：①大幅降低查询的IO成本，提升查询速度；②加速多表关联查询的匹配效率；③降低GROUPBY、ORDERBY的CPU成本，避免生成临时表和文件排序；④唯一索引可保证表中数据的唯一性。缺点：①占用额外的磁盘存储空间，聚簇索引的存储开销更高；②插入、更新、删除数据时需要同步维护索引结构，降低DML操作性能；③不合理的索引设计会提升优化器的索引选择成本，反而降低查询效率。适合建索引的场景：①经常作为WHERE查询条件的列；②多表关联的外键列；③经常用于GROUPBY、ORDERBY的列；④高基数列（列值重复率低，如用户ID、手机号）。不适合建索引的场景：①数据量极小的表（如百级行表），全表扫描成本更低；②写入操作远多于查询操作的表，索引会大幅降低写入性能；③低基数列（列值重复率极高，如性别、状态字段），索引筛选效率极低；④大文本、二进制字段，索引存储开销大，查询效率低，适合用全文索引或搜索引擎实现检索。2.请简述事务ACID四大特性的实现原理。答案：①原子性：依赖undo日志实现，事务执行过程中，所有修改都会先写入undo日志记录数据的历史版本，当事务执行失败或调用ROLLBACK时，通过undo日志回滚已经执行的操作，恢复到事务执行前的状态，保证所有操作要么全部成功要么全部失败。②一致性：由数据库层约束和业务层共同实现，数据库层通过主键约束、唯一约束、外键约束、非空约束保证数据的合法性，业务层通过逻辑校验保证数据符合业务规则，确保事务执行前后数据始终处于一致状态。③隔离性：依赖锁机制和MVCC实现，不同隔离级别对应不同的锁策略和MVCC规则：读未提交级别不加锁，直接读取最新数据；读已提交和可重复读级别通过MVCC实现非阻塞一致性读，写操作加行锁；串行化级别所有操作加表级锁，强制事务串行执行，保证不同事务之间互不干扰。④持久性：依赖redo日志和WAL（预写日志）机制实现，事务提交前，会先将所有修改记录写入redo日志并刷盘，再修改内存中的数据，后续系统宕机重启时，可通过redo日志恢复已经提交但未刷入磁盘的数据，保证提交后的修改永久生效。3.请简述MVCC的实现原理，以及不同隔离级别下MVCC的差异。答案：MVCC全称多版本并发控制，是InnoDB用于实现读已提交、可重复读隔离级别的核心机制，可实现非阻塞的一致性读，大幅提升并发读写性能。实现原理如下：①隐藏列：每行数据默认添加三个隐藏列，分别是DB_TRX_ID（最近一次修改该行的事务ID）、DB_ROLL_PTR（回滚指针，指向undo日志中的历史版本）、DB_ROW_ID（无主键时自动生成的隐藏主键）。②undo日志：事务修改数据时，会将原数据拷贝到undo日志中，通过回滚指针将多个历史版本串联成版本链。③ReadView：事务执行一致性读时生成的数据快照，包含四个核心字段：m_ids（当前活跃未提交的事务ID集合）、min_trx_id（活跃事务的最小ID）、max_trx_id（下一个待分配的事务ID）、creator_trx_id（当前事务的ID）。数据可见性规则：若数据版本的DB_TRX_ID小于min_trx_id，说明对应事务已提交，数据可见；若DB_TRX_ID大于等于max_trx_id，说明对应事务是当前事务启动后生成的，数据不可见；若DB_TRX_ID在min_trx_id和max_trx_id之间，若不在m_ids中说明已提交，数据可见，若在m_ids中说明未提交，数据不可见，需顺着版本链找下一个历史版本，直到找到可见版本。不同隔离级别的差异：读已提交级别下，每次执行SELECT查询都会生成新的ReadView，因此可以读到其他事务已提交的最新数据，解决了脏读问题，但存在不可重复读问题；可重复读级别下，仅在事务第一次执行SELECT时生成ReadView，后续所有查询复用同一个ReadView，因此整个事务中读到的数据始终一致，解决了不可重复读问题。4.请简述慢查询优化的完整步骤和思路。答案：慢查询优化分为问题定位、原因分析、优化落地三个阶段，具体步骤如下：①问题定位：开启慢查询日志，设置合理的long_query_time阈值（常规场景设为1s，高并发场景可设为0.5s），采集运行过程中的慢SQL，使用mysqldumpslow或pt-query-digest工具分析慢查询日志，按执行时间、扫描行数、调用频率排序，定位TopN核心慢SQL。②原因分析：对每条慢SQL执行EXPLAIN分析执行计划，重点关注四个字段：type字段（访问类型，出现index、ALL说明存在全表/全索引扫描，需要优化）、key字段（实际命中的索引，为NULL说明未走索引）、rows字段（扫描的行数，行数越大性能越差）、Extra字段（出现Usingfilesort、Usingtemporary说明存在文件排序或临时表，需要优化）。③优化落地：索引优化：为WHERE条件、JOIN关联条件、ORDERBY、GROUPBY的列建立合适的索引，联合索引遵循最左前缀原则，需要避免回表时建立覆盖索引，定期清理重复、冗余、长期未使用的索引。SQL优化：避免使用SELECT*，仅查询需要的字段，减少回表开销；避免在索引列上使用函数、算术运算、隐式类型转换，防止索引失效；大分页查询优化为子查询加主键过滤，如将`SELECT*FROMorderLIMIT100000,10`优化为`SELECT*FROMorderWHEREid>(SELECTidFROMorderLIMIT100000,1)LIMIT10`，减少扫描行数；大事务拆分为小事务，减少锁持有时间。表结构优化：单表数据量超过千万时进行水平分表，冷热数据分离，历史数据归档到冷存储；字段类型选择尽量小而合适，如能用TINYINT就不用INT，能用VARCHAR(20)就不用VARCHAR(255)，避免使用冗余字段和大字段。架构优化：读写分离，读请求走从库，写请求走主库，分散主库压力；热点数据存入Redis等缓存，减少数据库查询压力；全文检索类请求迁移到Elasticsearch，避免数据库LIKE全表扫描。5.什么是幻读？InnoDB是如何解决幻读问题的？答案：幻读是指同一个事务中，两次相同的范围查询得到的行数不一致的问题，如事务A第一次查询id>10的用户有5条，第二次查询得到6条，因为事务B在两次查询之间插入了一条id>10的用户并提交。InnoDB在可重复读隔离级别下通过两种机制解决幻读：①快照读（普通SELECT查询）：通过MVCC的版本链实现一致性读，事务全程复用同一个ReadView，只会读到事务启动前已提交的数据，不会读到其他事务新插入的数据，因此不会出现幻读。②当前读（如SELECT...FORUPDATE、INSERT、UPDATE、DELETE操作）：通过临键锁（Next-KeyLock）解决幻读，临键锁是行锁和间隙锁的组合，行锁锁住已经存在的行记录，间隙锁锁住两个索引之间的空闲区间，执行范围查询时会锁住查询条件覆盖的所有行和对应的间隙，禁止其他事务在该区间插入新数据，从根本上避免幻读的发生。四、实操题1.现有两张表：用户表user（idINT主键，nameVARCHAR(20)，ageTINYINT，dept_idINT，create_timeDATETIME），部门表dept（idINT主键，dept_nameVARCHAR(50)）。要求1：查询每个部门的人数，按人数降序排序，仅显示人数≥10的部门。答案：SELECTd.dept_name,COUNT(u.id)ASuser_countFROMdeptdLEFTJOINuseruONd.id=u.dept_idGROUPBYd.id,d.dept_nameHAVINGuser_count>=10ORDERBYuser_countDESC;解析：使用LEFTJOIN保证无用户的部门也能被统计，COUNT(u.id)避免统计到NULL值，GROUPBY需包含所有非聚合查询字段，HAVING过滤分组后的聚合结果。要求2：删除user表中2020年1月1日之前创建的用户，若数据量较大请给出优化方案。答案：DELETEFROMuserWHEREcreate_time<'2020-01-0100:00:00';WHILEEXISTS(SELECT1FROMuserWHEREcreate_time<'2020-01-0100:00:00')DODELETEFROMuserWHEREcreate_time