MySQL数据库面试题完整版目录1. 三大范式2. 数据存储类型2.1 varchar与char的区别2.2 blob和text有什么区别2.3 DATETIME和TIMESTAMP的异同2.4 IN和EXISTS区别3. Select语句完整的执行顺序4. MySQL事务4.1 事务的基本要素ACID4.2 ACID靠什么保证4.3 事务的并发问题4.4 MySQL事务隔离级别4.5 MVCC4.5.1 MVCC是什么4.5.2 MVCC组成4.5.3 MVCC工作原理4.5.4 MVCC解决什么问题5. MySQL索引5.1 索引的定义、类型及使用5.2 创建索引的注意点5.3 索引失效的情况5.4 建立索引的时机5.5 MySQL索引数据结构5.5.1 聚簇索引和非聚簇索引5.6 B树和B树的区别5.7 为什么MySQL索引常用B树5.8 索引数据结构选择依据5.9 MyISAM和InnoDB的区别5.10 最左前缀原则/最左匹配原则6. MySQL锁6.1 乐观锁和悲观锁6.2 死锁问题及排查6.3 死锁解决办法7. MySQL日志8. MySQL高可用/高性能8.1 MySQL高可用方案8.2 主从同步延迟处理9. SQL优化9.1 慢SQL定位与排查9.2 MySQL优化原则10. 实际工作问题10.1 百万级数据删除10.2 大表添加字段10.3 MySQL CPU飙升处理11. 常见面试SQL1. 三大范式第一范式数据表中的每一列每个字段都不可以再拆分。第二范式在第一范式的基础上非主键列完全依赖于主键而不能依赖于主键的一部分。例如订单表里存储商品信息时需将商品ID和订单ID作为联合主键。第三范式在满足第二范式的基础上表中的非主键只依赖于主键而不依赖于其他非主键。例如订单表不能存储用户姓名、地址等用户信息。2. 数据存储类型2.1 varchar与char的区别类型长度特性存储方式性能特点空间利用最大字符数char定长插入数据长度小于设定长度时用空格填充-存取速度快比varchar快约50%可能占多余空间空间换时间255varchar可变长-按插入数据实际长度存储存取速度慢不占多余空间时间换空间655322.2 blob和text有什么区别对比维度BLOBTEXT用途存储二进制数据图片、音频、视频二进制编码存储大字符串数据文章内容、长描述字符集无字符集按字节存储无编码问题有字符集需进行字符编码处理排序/比较依据基于二进制字节值根据字符集校对规则2.3 DATETIME和TIMESTAMP的异同对比维度DATETIMETIMESTAMP表现格式YYYY-MM-DD HH:MM:SSYYYY-MM-DD HH:MM:SS数据组成包含日期时间包含日期时间微秒支持支持秒后6位小数秒支持秒后6位小数秒存储范围1000-01-01 00:00:00 至 9999-12-31 23:59:591970-01-01 00:00:01 UTC 至 2038-01-19 03:14:07 UTC存储空间8字节4字节无微秒/5-8字节有微秒时区影响不受时区影响存储读取值不变受时区影响存储转UTC读取转当前时区默认值无默认值默认当前时间CURRENT_TIMESTAMP2.4 IN和EXISTS区别对比项INEXISTS实现方式外表和内表作hash连接外表loop循环每次循环查询内表效率比较两表相当差别不大子查询表小用IN两表相当差别不大子查询表大用EXISTSNOT IN/NOT EXISTSNOT IN全表扫描不使用索引NOT EXISTS能用到索引通常比NOT IN快3. Select语句完整的执行顺序SELECT语句完整执行顺序FROM-JOIN-WHERE-GROUP BY-HAVING-SELECT-DISTINCT-ORDER BY-LIMIT/OFFSET分步说明FROM确定查询数据来源表JOIN多表时按条件连接表WHERE筛选符合条件的行GROUP BY按指定列分组HAVING筛选分组后的结果SELECT指定返回的列DISTINCT去除重复行ORDER BY对结果集排序LIMIT/OFFSET限制返回行数和偏移量4. MySQL事务4.1 事务的基本要素ACID原子性Atomicity事务操作要么全部成功要么全部失败回滚一致性Consistency事务执行前后数据库数据保持一致状态隔离性Isolation多个事务并发执行时互不干扰持久性Durability事务提交后数据修改永久生效4.2 ACID靠什么保证A原子性undo log日志记录回滚信息事务回滚时撤销已执行的SQLC一致性由其他三大特性业务代码保证I隔离性锁机制共享锁、排他锁 MVCC多版本并发控制D持久性内存redo log修改数据时同时记录到内存和redo log宕机可恢复4.3 事务的并发问题问题描述解决方式脏读读取到另一个未提交事务修改的数据1. 隔离级别设为读已提交2. 读数据加共享锁3. MVCC读取已提交版本不可重复读同一事务两次读取同一数据结果不一致1. 隔离级别提升到可重复读2. MVCC保证事务内数据一致性3. 共享锁至事务结束幻读同一条件查询结果集因其他事务增删记录而变化1. 隔离级别设为串行化2. InnoDB可重复读级别用间隙锁3. 应用层乐观锁丢失更新两个事务同时更新一个覆盖另一个结果1. 更新前加排他锁2. 乐观锁版本号/时间戳检查后更新4.4 MySQL事务隔离级别隔离级别含义解决的并发问题仍存在的问题读未提交可读取未提交事务的数据无脏读、不可重复读、幻读读已提交只能读取已提交事务的数据脏读不可重复读、幻读可重复读同一事务多次读取数据结果一致脏读、不可重复读幻读InnoDB间隙锁缓解串行化事务依次顺序执行脏读、不可重复读、幻读性能差锁竞争多4.5 MVCC4.5.1 MVCC是什么MVCC多版本并发控制是数据库的版本控制机制通过维护数据多个版本支持不同事务并发执行解决脏读、幻读、不可重复读问题。4.5.2 MVCC组成数据版本新版本、旧版本事务ID每个事务唯一ID回滚段存储数据旧版本4.5.3 MVCC工作原理读未提交MVCC无作用读已提交事务开始创建一致性视图查询取最新已提交版本避免脏读但存在不可重复读/幻读可重复读事务开始创建视图且全程不变解决脏读、不可重复读幻读通过MVCC间隙锁缓解串行化MVCC无作用4.5.4 MVCC解决什么问题解决脏读、不可重复读、幻读读写可同时进行提高并发量减少锁竞争读不加锁读写不冲突保证事务隔离性5. MySQL索引5.1 索引的定义、类型及使用定义索引是提高查询速度的数据结构类型主键索引唯一非空每个表仅一个唯一索引值唯一可含NULL表可多个普通索引无限制基础索引类型全文索引用于文本数据全文搜索组合索引多列组合遵循最左前缀原则5.2 创建索引的注意点建在查询频繁的字段上索引数量适量占用空间更新需维护频繁更新的值不做主键/索引小表不建索引离散度低的字段如性别不建索引5.3 索引失效的情况查询条件含or、like、内置函数、运算/-/*/、!/、not in、is null/is not null字符串字段查询未加引号隐式类型转换联合索引不遵循最左前缀原则左右连接时关联字段编码不一致5.4 建立索引的时机表数据量大查询慢多表关联的字段GROUP BY/ORDER BY的字段5.5 MySQL索引数据结构B树B树InnoDB/MyISAM默认哈希HashMemory引擎5.5.1 聚簇索引和非聚簇索引聚簇索引InnoDBB树叶子节点包含完整数据行非聚簇索引MyISAMB树叶子节点存储索引键值主键值查询需回表5.6 B树和B树的区别B树平衡多路搜索树键值分布在整棵树非叶子节点可能找到目标B树B树变形数据仅存于叶子节点非叶子节点仅索引叶子节点通过指针形成有序链表5.7 为什么MySQL索引常用B树磁盘I/O少非叶子节点仅存索引节点存储更多索引树高度低范围查询优叶子节点链表相连便于范围查询查询稳定每次查询都到叶子节点效率稳定红黑树劣势数据量大时树高磁盘I/O多且有变色旋转开销5.8 索引数据结构选择依据精确查找为主哈希索引存储引擎支持时范围查询/排序多B树索引通用场景B树MySQL默认5.9 MyISAM和InnoDB的区别对比维度MyISAMInnoDB事务支持不支持支持ACID外键支持不支持支持索引存储索引与数据分离索引指向物理位置聚簇索引存数据辅助索引指向主键锁机制表级锁并发写差行级锁表级锁并发好崩溃恢复恢复困难易丢数据日志崩溃恢复机制自动恢复存储统计保存总行数查询快不精确需实时计算适用场景读多写少无事务/外键需求写多需数据完整性和高并发5.10 最左前缀原则/最左匹配原则InnoDB联合索引中需匹配前一个/左边的值才能匹配下一个。如创建组合索引(a1,a2,a3)等价于创建(a1)(a1,a2)(a1,a2,a3)6. MySQL锁6.1 乐观锁和悲观锁悲观锁认为数据随时会被修改事务拿到锁后其他事务无法修改只能等待数据库行锁、表锁、读锁、写锁均为悲观锁乐观锁认为数据变动不频繁通过版本号version或时间戳timestamp实现版本号为最常用方式6.2 死锁问题及排查行锁争用导致的死锁原因事务持有锁时间过长行锁争用激烈解决思路减少事务持有锁时间移出不必要操作缩短执行时间示例-- 事务1STARTTRANSACTION;UPDATEusersSETage30WHEREid1;-- 对id1加排他锁-- 事务2STARTTRANSACTION;SELECT*FROMusersWHEREid2FORUPDATE;-- 对id2加排他锁UPDATEusersSETage25WHEREid1;-- 等待事务1释放锁-- 事务1UPDATEusersSETnameJohnWHEREid2;-- 等待事务2释放锁形成死锁事务顺序不一致导致的死锁原因不同事务操作表的顺序不一致解决思路统一事务操作表的顺序示例-- 事务1STARTTRANSACTION;UPDATEtable1SETname1AWHEREid11;UPDATEtable2SETname2XWHEREid21;-- 事务2STARTTRANSACTION;UPDATEtable2SETname2YWHEREid21;UPDATEtable1SETname1BWHEREid11;-- 顺序相反易死锁死锁排查步骤查看死锁日志show engine innodb status;找出死锁SQL分析SQL加锁情况模拟死锁场景分析死锁日志得出解决方案6.3 死锁解决办法统一操作顺序所有事务对表的操作顺序一致如先操作questions→answers→feedback拆分事务大事务拆分为小事务减少锁持有时间设置超时时间通过innodb_lock_wait_timeout参数设置锁等待阈值超时自动回滚7. MySQL日志错误日志error log记录启动、运行、关闭过程定位MySQL问题慢查询日志slow query log记录执行时间超过long_query_time的SQL用于优化一般查询日志general log记录所有数据库请求无论是否执行成功二进制日志bin log记录DDL/DML语句不含select/show事件形式存储InnoDB特有日志重做日志redo log记录事务日志保证持久性回滚日志undo log记录数据回滚信息保证原子性8. MySQL高可用/高性能8.1 MySQL高可用方案读写分离原理将读写操作分散到不同节点搭建主从集群一主一从/一主多从主机负责读写操作从机仅负责读操作主机通过复制同步数据到从机业务端写操作发主机读操作发从机实现方式程序代码封装抽象数据访问层实现读写分离和连接管理分库分表类型说明垂直分库按业务归属将不同表拆分到不同库水平分库按字段策略hash/range将一个库数据拆分到多个库垂直分表按字段活跃性将字段拆到主表/扩展表水平分表按字段策略hash/range将一个表数据拆分到多个表水平分表路由方式范围路由按有序列整形/时间戳分段Hash路由字段Hash运算后分散常用中间件sharding-jdbcMycat分库分表问题事务问题需分布式事务跨库JOIN业务代码中关联查询主从复制过程master写入数据更新binlogmaster创建dump线程向slave推送binlogslave创建IO线程接收binlog写入relay logslave创建SQL线程读取relay log并执行完成同步slave记录自身binlog8.2 主从同步延迟处理原因从库仅一个线程读取binlog主库大并发更新导致SQL积压解决办法写操作后的读操作指定主库读从机失败后重试主机关键业务读写走主机非关键业务读写分离9. SQL优化9.1 慢SQL定位与排查发现慢SQL开启慢查询日志# my.cnf/my.ini配置 slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 2 # 阈值2秒重启MySQL生效SHOW PROCESSLIST;查看Time列值大的为慢SQL分析慢SQLEXPLAIN分析执行计划关注typeALL全表扫描差、possible_keys、key索引检查验证过滤/排序/连接字段是否有索引示例CREATEINDEXidx_ageONusers(age);锁等待查看查询information_schema.innodb_trx/innodb_locksSQL优化减少复杂子查询/连接添加过滤条件工具辅助pt-query-digest分析慢查询日志pt-query-digest /var/log/mysql/mysql-slow.log9.2 MySQL优化原则分析表适当建立索引单表数据不超200W适时分表用explain分析慢查询FROM子句后表为基础表选记录少的表尽量缩小子查询结果10. 实际工作问题10.1 百万级数据删除删除索引删除无用数据重新创建索引10.2 大表添加字段方案1中间表转换创建临时表复制旧表结构添加新字段复制旧表数据到临时表删除旧表临时表重命名为旧表名缺点可能丢失部分数据方案2主从切换先在从库添加字段主从切换其他节点添加字段10.3 MySQL CPU飙升处理排查步骤top命令确认是否mysqld导致show processlist查看消耗资源的SQL分析SQL执行计划、索引、数据量处理方式kill消耗高的线程观察CPU是否下降调整加索引、改SQL、调内存参数重新执行SQL11. 常见面试SQL例1查询每门课都大于80分的学生姓名数据表namekechengfenshu张三语文81张三数学75李四语文76李四数学90王五语文81王五数学100王五英语90解法1SELECTDISTINCTnameFROMtableWHEREnameNOTIN(SELECTDISTINCTnameFROMtableWHEREfenshu80);解法2SELECTnameFROMtableGROUPBYnameHAVINGMIN(fenshu)80;例2删除学生冗余信息数据表自动编号学号姓名课程编号课程名称分数12005001张三0001数学6922005002李四0001数学8932005001张三0001数学61SQLDELETEtablenameWHERE自动编号NOTIN(SELECTMIN(自动编号)FROMtablenameGROUPBY学号,姓名,课程编号,课程名称,分数);例3查询球队所有比赛组合数据表teamname字段a,b,c,dSQLSELECTa.name,b.nameFROMteam a,team bWHEREa.nameb.name;例4行转列查询原表yearmonthamount199111.1199121.2199131.3199141.4199212.1199222.2199232.3199242.4目标结果yearm1m2m3m419911.11.21.31.419922.12.22.32.4SQLSELECTyear,(SELECTamountFROMaaa mWHEREmonth1ANDm.yearaaa.year)ASm1,(SELECTamountFROMaaa mWHEREmonth2ANDm.yearaaa.year)ASm2,(SELECTamountFROMaaa mWHEREmonth3ANDm.yearaaa.year)ASm3,(SELECTamountFROMaaa mWHEREmonth4ANDm.yearaaa.year)ASm4FROMaaaGROUPBYyear;例5复制表结构源表a新表bMySQLCREATETABLEbASSELECT*FROMaWHERE12;SQL ServerSELECT*INTObFROMaWHERE11;-- 11复制结构数据例6添加及格标记原表courseidcoursenamescore1java702oracle903xml404jsp305servlet80目标结果courseidcoursenamescoremark1java70pass2oracle90pass3xml40fail4jsp30fail5servlet80passSQLSELECTcourseid,coursename,score,IF(score60,pass,fail)ASmarkFROMcourse;例7查询购入2种以上商品的购物人数据表购物人商品名称数量A甲2B乙4C丙1A丁2B丙5SQLSELECT*FROM购物信息WHERE购物人IN(SELECT购物人FROM购物信息GROUPBY购物人HAVINGCOUNT(*)2);例8统计每日胜负次数数据表dateresult2005-05-09win2005-05-09lose2005-05-09lose2005-05-09lose2005-05-10win2005-05-10lose2005-05-10lose目标结果datewinlose2005-05-09132005-05-1012解法1SELECTdate,SUM(CASEWHENresultwinTHEN1ELSE0END)ASwin,SUM(CASEWHENresultloseTHEN1ELSE0END)ASloseFROMinfoGROUPBYdate;解法2SELECTa.date,a.resultASwin,b.resultASloseFROM(SELECTdate,COUNT(result)ASresultFROMinfoWHEREresultwinGROUPBYdate)ASaJOIN(SELECTdate,COUNT(result)ASresultFROMinfoWHEREresultloseGROUPBYdate)ASbONa.dateb.date;例9联合索引生效判断联合索引(a,b,c)WHERE a1 AND b1 AND c1→ 生效WHERE a1 AND c1→ 生效a匹配最左前缀WHERE b1 AND c1→ 失效无aWHERE b1 AND a1 AND c1→ 生效顺序不影响### 总结 1. 文档完整整合了所有MySQL面试题内容修正了原始格式错误如表格排版、代码缩进、标点符号补充了缺失的逻辑内容 2. 按照目录层级结构化梳理每个知识点独立成节关键内容用表格/代码块/列表呈现可读性大幅提升 3. 保留了所有核心考点三大范式、事务、索引、锁、优化、面试SQL等并补充了完整的SQL示例和配置示例可直接用于复习和面试准备。
Java面试-06-数据库MySql
发布时间：2026/6/27 6:13:06
MySQL数据库面试题完整版目录1. 三大范式2. 数据存储类型2.1 varchar与char的区别2.2 blob和text有什么区别2.3 DATETIME和TIMESTAMP的异同2.4 IN和EXISTS区别3. Select语句完整的执行顺序4. MySQL事务4.1 事务的基本要素ACID4.2 ACID靠什么保证4.3 事务的并发问题4.4 MySQL事务隔离级别4.5 MVCC4.5.1 MVCC是什么4.5.2 MVCC组成4.5.3 MVCC工作原理4.5.4 MVCC解决什么问题5. MySQL索引5.1 索引的定义、类型及使用5.2 创建索引的注意点5.3 索引失效的情况5.4 建立索引的时机5.5 MySQL索引数据结构5.5.1 聚簇索引和非聚簇索引5.6 B树和B树的区别5.7 为什么MySQL索引常用B树5.8 索引数据结构选择依据5.9 MyISAM和InnoDB的区别5.10 最左前缀原则/最左匹配原则6. MySQL锁6.1 乐观锁和悲观锁6.2 死锁问题及排查6.3 死锁解决办法7. MySQL日志8. MySQL高可用/高性能8.1 MySQL高可用方案8.2 主从同步延迟处理9. SQL优化9.1 慢SQL定位与排查9.2 MySQL优化原则10. 实际工作问题10.1 百万级数据删除10.2 大表添加字段10.3 MySQL CPU飙升处理11. 常见面试SQL1. 三大范式第一范式数据表中的每一列每个字段都不可以再拆分。第二范式在第一范式的基础上非主键列完全依赖于主键而不能依赖于主键的一部分。例如订单表里存储商品信息时需将商品ID和订单ID作为联合主键。第三范式在满足第二范式的基础上表中的非主键只依赖于主键而不依赖于其他非主键。例如订单表不能存储用户姓名、地址等用户信息。2. 数据存储类型2.1 varchar与char的区别类型长度特性存储方式性能特点空间利用最大字符数char定长插入数据长度小于设定长度时用空格填充-存取速度快比varchar快约50%可能占多余空间空间换时间255varchar可变长-按插入数据实际长度存储存取速度慢不占多余空间时间换空间655322.2 blob和text有什么区别对比维度BLOBTEXT用途存储二进制数据图片、音频、视频二进制编码存储大字符串数据文章内容、长描述字符集无字符集按字节存储无编码问题有字符集需进行字符编码处理排序/比较依据基于二进制字节值根据字符集校对规则2.3 DATETIME和TIMESTAMP的异同对比维度DATETIMETIMESTAMP表现格式YYYY-MM-DD HH:MM:SSYYYY-MM-DD HH:MM:SS数据组成包含日期时间包含日期时间微秒支持支持秒后6位小数秒支持秒后6位小数秒存储范围1000-01-01 00:00:00 至 9999-12-31 23:59:591970-01-01 00:00:01 UTC 至 2038-01-19 03:14:07 UTC存储空间8字节4字节无微秒/5-8字节有微秒时区影响不受时区影响存储读取值不变受时区影响存储转UTC读取转当前时区默认值无默认值默认当前时间CURRENT_TIMESTAMP2.4 IN和EXISTS区别对比项INEXISTS实现方式外表和内表作hash连接外表loop循环每次循环查询内表效率比较两表相当差别不大子查询表小用IN两表相当差别不大子查询表大用EXISTSNOT IN/NOT EXISTSNOT IN全表扫描不使用索引NOT EXISTS能用到索引通常比NOT IN快3. Select语句完整的执行顺序SELECT语句完整执行顺序FROM-JOIN-WHERE-GROUP BY-HAVING-SELECT-DISTINCT-ORDER BY-LIMIT/OFFSET分步说明FROM确定查询数据来源表JOIN多表时按条件连接表WHERE筛选符合条件的行GROUP BY按指定列分组HAVING筛选分组后的结果SELECT指定返回的列DISTINCT去除重复行ORDER BY对结果集排序LIMIT/OFFSET限制返回行数和偏移量4. MySQL事务4.1 事务的基本要素ACID原子性Atomicity事务操作要么全部成功要么全部失败回滚一致性Consistency事务执行前后数据库数据保持一致状态隔离性Isolation多个事务并发执行时互不干扰持久性Durability事务提交后数据修改永久生效4.2 ACID靠什么保证A原子性undo log日志记录回滚信息事务回滚时撤销已执行的SQLC一致性由其他三大特性业务代码保证I隔离性锁机制共享锁、排他锁 MVCC多版本并发控制D持久性内存redo log修改数据时同时记录到内存和redo log宕机可恢复4.3 事务的并发问题问题描述解决方式脏读读取到另一个未提交事务修改的数据1. 隔离级别设为读已提交2. 读数据加共享锁3. MVCC读取已提交版本不可重复读同一事务两次读取同一数据结果不一致1. 隔离级别提升到可重复读2. MVCC保证事务内数据一致性3. 共享锁至事务结束幻读同一条件查询结果集因其他事务增删记录而变化1. 隔离级别设为串行化2. InnoDB可重复读级别用间隙锁3. 应用层乐观锁丢失更新两个事务同时更新一个覆盖另一个结果1. 更新前加排他锁2. 乐观锁版本号/时间戳检查后更新4.4 MySQL事务隔离级别隔离级别含义解决的并发问题仍存在的问题读未提交可读取未提交事务的数据无脏读、不可重复读、幻读读已提交只能读取已提交事务的数据脏读不可重复读、幻读可重复读同一事务多次读取数据结果一致脏读、不可重复读幻读InnoDB间隙锁缓解串行化事务依次顺序执行脏读、不可重复读、幻读性能差锁竞争多4.5 MVCC4.5.1 MVCC是什么MVCC多版本并发控制是数据库的版本控制机制通过维护数据多个版本支持不同事务并发执行解决脏读、幻读、不可重复读问题。4.5.2 MVCC组成数据版本新版本、旧版本事务ID每个事务唯一ID回滚段存储数据旧版本4.5.3 MVCC工作原理读未提交MVCC无作用读已提交事务开始创建一致性视图查询取最新已提交版本避免脏读但存在不可重复读/幻读可重复读事务开始创建视图且全程不变解决脏读、不可重复读幻读通过MVCC间隙锁缓解串行化MVCC无作用4.5.4 MVCC解决什么问题解决脏读、不可重复读、幻读读写可同时进行提高并发量减少锁竞争读不加锁读写不冲突保证事务隔离性5. MySQL索引5.1 索引的定义、类型及使用定义索引是提高查询速度的数据结构类型主键索引唯一非空每个表仅一个唯一索引值唯一可含NULL表可多个普通索引无限制基础索引类型全文索引用于文本数据全文搜索组合索引多列组合遵循最左前缀原则5.2 创建索引的注意点建在查询频繁的字段上索引数量适量占用空间更新需维护频繁更新的值不做主键/索引小表不建索引离散度低的字段如性别不建索引5.3 索引失效的情况查询条件含or、like、内置函数、运算/-/*/、!/、not in、is null/is not null字符串字段查询未加引号隐式类型转换联合索引不遵循最左前缀原则左右连接时关联字段编码不一致5.4 建立索引的时机表数据量大查询慢多表关联的字段GROUP BY/ORDER BY的字段5.5 MySQL索引数据结构B树B树InnoDB/MyISAM默认哈希HashMemory引擎5.5.1 聚簇索引和非聚簇索引聚簇索引InnoDBB树叶子节点包含完整数据行非聚簇索引MyISAMB树叶子节点存储索引键值主键值查询需回表5.6 B树和B树的区别B树平衡多路搜索树键值分布在整棵树非叶子节点可能找到目标B树B树变形数据仅存于叶子节点非叶子节点仅索引叶子节点通过指针形成有序链表5.7 为什么MySQL索引常用B树磁盘I/O少非叶子节点仅存索引节点存储更多索引树高度低范围查询优叶子节点链表相连便于范围查询查询稳定每次查询都到叶子节点效率稳定红黑树劣势数据量大时树高磁盘I/O多且有变色旋转开销5.8 索引数据结构选择依据精确查找为主哈希索引存储引擎支持时范围查询/排序多B树索引通用场景B树MySQL默认5.9 MyISAM和InnoDB的区别对比维度MyISAMInnoDB事务支持不支持支持ACID外键支持不支持支持索引存储索引与数据分离索引指向物理位置聚簇索引存数据辅助索引指向主键锁机制表级锁并发写差行级锁表级锁并发好崩溃恢复恢复困难易丢数据日志崩溃恢复机制自动恢复存储统计保存总行数查询快不精确需实时计算适用场景读多写少无事务/外键需求写多需数据完整性和高并发5.10 最左前缀原则/最左匹配原则InnoDB联合索引中需匹配前一个/左边的值才能匹配下一个。如创建组合索引(a1,a2,a3)等价于创建(a1)(a1,a2)(a1,a2,a3)6. MySQL锁6.1 乐观锁和悲观锁悲观锁认为数据随时会被修改事务拿到锁后其他事务无法修改只能等待数据库行锁、表锁、读锁、写锁均为悲观锁乐观锁认为数据变动不频繁通过版本号version或时间戳timestamp实现版本号为最常用方式6.2 死锁问题及排查行锁争用导致的死锁原因事务持有锁时间过长行锁争用激烈解决思路减少事务持有锁时间移出不必要操作缩短执行时间示例-- 事务1STARTTRANSACTION;UPDATEusersSETage30WHEREid1;-- 对id1加排他锁-- 事务2STARTTRANSACTION;SELECT*FROMusersWHEREid2FORUPDATE;-- 对id2加排他锁UPDATEusersSETage25WHEREid1;-- 等待事务1释放锁-- 事务1UPDATEusersSETnameJohnWHEREid2;-- 等待事务2释放锁形成死锁事务顺序不一致导致的死锁原因不同事务操作表的顺序不一致解决思路统一事务操作表的顺序示例-- 事务1STARTTRANSACTION;UPDATEtable1SETname1AWHEREid11;UPDATEtable2SETname2XWHEREid21;-- 事务2STARTTRANSACTION;UPDATEtable2SETname2YWHEREid21;UPDATEtable1SETname1BWHEREid11;-- 顺序相反易死锁死锁排查步骤查看死锁日志show engine innodb status;找出死锁SQL分析SQL加锁情况模拟死锁场景分析死锁日志得出解决方案6.3 死锁解决办法统一操作顺序所有事务对表的操作顺序一致如先操作questions→answers→feedback拆分事务大事务拆分为小事务减少锁持有时间设置超时时间通过innodb_lock_wait_timeout参数设置锁等待阈值超时自动回滚7. MySQL日志错误日志error log记录启动、运行、关闭过程定位MySQL问题慢查询日志slow query log记录执行时间超过long_query_time的SQL用于优化一般查询日志general log记录所有数据库请求无论是否执行成功二进制日志bin log记录DDL/DML语句不含select/show事件形式存储InnoDB特有日志重做日志redo log记录事务日志保证持久性回滚日志undo log记录数据回滚信息保证原子性8. MySQL高可用/高性能8.1 MySQL高可用方案读写分离原理将读写操作分散到不同节点搭建主从集群一主一从/一主多从主机负责读写操作从机仅负责读操作主机通过复制同步数据到从机业务端写操作发主机读操作发从机实现方式程序代码封装抽象数据访问层实现读写分离和连接管理分库分表类型说明垂直分库按业务归属将不同表拆分到不同库水平分库按字段策略hash/range将一个库数据拆分到多个库垂直分表按字段活跃性将字段拆到主表/扩展表水平分表按字段策略hash/range将一个表数据拆分到多个表水平分表路由方式范围路由按有序列整形/时间戳分段Hash路由字段Hash运算后分散常用中间件sharding-jdbcMycat分库分表问题事务问题需分布式事务跨库JOIN业务代码中关联查询主从复制过程master写入数据更新binlogmaster创建dump线程向slave推送binlogslave创建IO线程接收binlog写入relay logslave创建SQL线程读取relay log并执行完成同步slave记录自身binlog8.2 主从同步延迟处理原因从库仅一个线程读取binlog主库大并发更新导致SQL积压解决办法写操作后的读操作指定主库读从机失败后重试主机关键业务读写走主机非关键业务读写分离9. SQL优化9.1 慢SQL定位与排查发现慢SQL开启慢查询日志# my.cnf/my.ini配置 slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 2 # 阈值2秒重启MySQL生效SHOW PROCESSLIST;查看Time列值大的为慢SQL分析慢SQLEXPLAIN分析执行计划关注typeALL全表扫描差、possible_keys、key索引检查验证过滤/排序/连接字段是否有索引示例CREATEINDEXidx_ageONusers(age);锁等待查看查询information_schema.innodb_trx/innodb_locksSQL优化减少复杂子查询/连接添加过滤条件工具辅助pt-query-digest分析慢查询日志pt-query-digest /var/log/mysql/mysql-slow.log9.2 MySQL优化原则分析表适当建立索引单表数据不超200W适时分表用explain分析慢查询FROM子句后表为基础表选记录少的表尽量缩小子查询结果10. 实际工作问题10.1 百万级数据删除删除索引删除无用数据重新创建索引10.2 大表添加字段方案1中间表转换创建临时表复制旧表结构添加新字段复制旧表数据到临时表删除旧表临时表重命名为旧表名缺点可能丢失部分数据方案2主从切换先在从库添加字段主从切换其他节点添加字段10.3 MySQL CPU飙升处理排查步骤top命令确认是否mysqld导致show processlist查看消耗资源的SQL分析SQL执行计划、索引、数据量处理方式kill消耗高的线程观察CPU是否下降调整加索引、改SQL、调内存参数重新执行SQL11. 常见面试SQL例1查询每门课都大于80分的学生姓名数据表namekechengfenshu张三语文81张三数学75李四语文76李四数学90王五语文81王五数学100王五英语90解法1SELECTDISTINCTnameFROMtableWHEREnameNOTIN(SELECTDISTINCTnameFROMtableWHEREfenshu80);解法2SELECTnameFROMtableGROUPBYnameHAVINGMIN(fenshu)80;例2删除学生冗余信息数据表自动编号学号姓名课程编号课程名称分数12005001张三0001数学6922005002李四0001数学8932005001张三0001数学61SQLDELETEtablenameWHERE自动编号NOTIN(SELECTMIN(自动编号)FROMtablenameGROUPBY学号,姓名,课程编号,课程名称,分数);例3查询球队所有比赛组合数据表teamname字段a,b,c,dSQLSELECTa.name,b.nameFROMteam a,team bWHEREa.nameb.name;例4行转列查询原表yearmonthamount199111.1199121.2199131.3199141.4199212.1199222.2199232.3199242.4目标结果yearm1m2m3m419911.11.21.31.419922.12.22.32.4SQLSELECTyear,(SELECTamountFROMaaa mWHEREmonth1ANDm.yearaaa.year)ASm1,(SELECTamountFROMaaa mWHEREmonth2ANDm.yearaaa.year)ASm2,(SELECTamountFROMaaa mWHEREmonth3ANDm.yearaaa.year)ASm3,(SELECTamountFROMaaa mWHEREmonth4ANDm.yearaaa.year)ASm4FROMaaaGROUPBYyear;例5复制表结构源表a新表bMySQLCREATETABLEbASSELECT*FROMaWHERE12;SQL ServerSELECT*INTObFROMaWHERE11;-- 11复制结构数据例6添加及格标记原表courseidcoursenamescore1java702oracle903xml404jsp305servlet80目标结果courseidcoursenamescoremark1java70pass2oracle90pass3xml40fail4jsp30fail5servlet80passSQLSELECTcourseid,coursename,score,IF(score60,pass,fail)ASmarkFROMcourse;例7查询购入2种以上商品的购物人数据表购物人商品名称数量A甲2B乙4C丙1A丁2B丙5SQLSELECT*FROM购物信息WHERE购物人IN(SELECT购物人FROM购物信息GROUPBY购物人HAVINGCOUNT(*)2);例8统计每日胜负次数数据表dateresult2005-05-09win2005-05-09lose2005-05-09lose2005-05-09lose2005-05-10win2005-05-10lose2005-05-10lose目标结果datewinlose2005-05-09132005-05-1012解法1SELECTdate,SUM(CASEWHENresultwinTHEN1ELSE0END)ASwin,SUM(CASEWHENresultloseTHEN1ELSE0END)ASloseFROMinfoGROUPBYdate;解法2SELECTa.date,a.resultASwin,b.resultASloseFROM(SELECTdate,COUNT(result)ASresultFROMinfoWHEREresultwinGROUPBYdate)ASaJOIN(SELECTdate,COUNT(result)ASresultFROMinfoWHEREresultloseGROUPBYdate)ASbONa.dateb.date;例9联合索引生效判断联合索引(a,b,c)WHERE a1 AND b1 AND c1→ 生效WHERE a1 AND c1→ 生效a匹配最左前缀WHERE b1 AND c1→ 失效无aWHERE b1 AND a1 AND c1→ 生效顺序不影响### 总结 1. 文档完整整合了所有MySQL面试题内容修正了原始格式错误如表格排版、代码缩进、标点符号补充了缺失的逻辑内容 2. 按照目录层级结构化梳理每个知识点独立成节关键内容用表格/代码块/列表呈现可读性大幅提升 3. 保留了所有核心考点三大范式、事务、索引、锁、优化、面试SQL等并补充了完整的SQL示例和配置示例可直接用于复习和面试准备。
相关文章
AI安全攻防升级，OpenClaw主动防御体系，筑牢智能体自主安全壁垒
AI安全攻防升级，OpenClaw主动防御体系，筑牢智能体自主安全壁垒2026年AI安全攻防对抗进入白热化阶段，针对AI智能体的恶意注入、指令劫持、 prompt攻击、插件漏洞利用、越权操控等新型攻击手段层出不穷。大量开源智能体因缺少主动防御机制&…
关于grep -v
grep -v 中的 v 是 invert 的缩写，和拉丁语 vice/versa 没有关系。明确区分表格复制来源含义例子invert（英语）反转、取反grep -v → 反向匹配vice versa（拉丁语）反之亦然表示双向关系互换为什么容易混淆？因…
8GB 仿真结果只能甩两张截图?HOOPS Envision 让评审会上当场调云图
关键词：CAE 可视化、CFD 后处理、仿真结果 Web 发布、WebGL 渲染、HOOPS Envision、SDK 仿真的价值不在结果文件本身，而在它能不能被看见、被理解、被决策。一、一个 CAE 工程师都遇到过的尴尬 李工是一名 CAE 仿真工程师，负责新一代电控散热…
旋进漩涡流量计如何选型？
旋进漩涡流量计属于速度式气体专用计量仪表，自带一体化温压补偿，可自动将工况气体流量换算标况流量，想要选型精准、不出现计量偏差、设备过早损坏，核心抓介质、口径量程、温压工况、现场安装环境、信号通讯、厂家实力六大维度&…
珠三角阻尼转轴避坑：3 招识别厂家有没有真・扭矩自研调校能力
「3C 结构件硬核拆解」专栏做 3C 结构件采购的朋友，大概率都踩过这个坑：打样时扭力精准、手感丝滑，一上量产就原形毕露 —— 同批次扭力公差超 30%，几千次开合后扭力跳水，异响、松旷接踵而至，售后返修率直接…
2026必看：AI编程工具免费付费全攻略（附真实踩坑）
这篇文章源于一个实际需求：我们的CI流水线需要和AI编程工具集成，5款工具的CI/CD支持情况对比。作为维护祖传项目管理工具（项目代号：TaskFlow-Pro）的老员工，2026年4月22日上线新版本时，我踩过AI生…
高压电缆局放在线监测中的抗干扰实战：从脉冲分组到动态阈值
现场噪声比放电信号大两个数量级？这套组合拳让2pC局放无所遁形做电缆局放监测的工程师都有一个共识：现场干扰永远比信号来得猛烈。在电缆接头处，高频脉冲电流传感器（HFCT）耦合到的信号里，真正反映绝缘缺陷的…
跨境电商的热门数码消费品怎样？亚马逊消费电子品类攻略
跨境 3C 数码一直是亚马逊平台流量与销售额双高的核心赛道，近期亚马逊官方推出专属消费电子运营指导手册，完整拆解海外消费者选购偏好、细分赛道增长机遇，同时给出合规落地、供应链匹配、品牌打造全套实操方案。本文结合美、欧、日三大核心站…
密码学完整理论知识详解｜古典密码 / 对称 / 非对称 / PKI 全体系梳理
文章摘要 本文完整梳理密码学全套核心理论知识，覆盖古典密码、对称分组密码、非对称公钥密码、密钥交换、CRT、数字签名、PKI 数字证书、Shamir 门限秘密共享全模块，适合计算机专业学习、网络安全面试、密码学入门阅读。 密码体制基础分类 对称密码与…
千问AI眼镜：阿里AI战略急先锋，能否在激烈竞争中突围？
千问眼镜：销量第一背后的挑战不久前，不少第三方统计机构给千问背书，甚至给出全国销量第一的统计结果。这个第一的含金量有多高？暂且先打个问号。但这些榜单至少说明，千问眼镜延续了阿里AI战略整体偏激进的风格…
Tomcat中X-Frame-Options配置实战：防御点击劫持的四种方法与最佳实践
1. 项目概述：为什么X-Frame-Options是Web安全的“防盗门”？最近在排查一个老项目的安全审计报告时，又被提到了“点击劫持”风险，矛头直指缺失的X-Frame-Options响应头。这已经不是第一次了，很多开发团队，尤…
10分钟AI语音克隆与实时变声：Retrieval-based-Voice-Conversion-WebUI完整指南
10分钟AI语音克隆与实时变声：Retrieval-based-Voice-Conversion-WebUI完整指南 【免费下载链接】Retrieval-based-Voice-Conversion-WebUI Easily train a good VC model with voice data < 10 mins! 项目地址: https://gitcode.com/GitHub_Trending/re/Retrie…
Google AI Studio 300美元额度的真相与实战指南
1. 这300美金不是“送钱”，而是Google埋下的第一道技术门槛 你看到标题里那个醒目的“$300美金”时，第一反应可能是：又一个免费额度？领完就完事？我亲手试过——这300美金根本不是红包，而是一张入场券&…
PDF对比终极指南：用diff-pdf轻松识别文档差异的完整教程
PDF对比终极指南：用diff-pdf轻松识别文档差异的完整教程 【免费下载链接】diff-pdf A simple tool for visually comparing two PDF files 项目地址: https://gitcode.com/gh_mirrors/di/diff-pdf 还在为PDF文档的版本对比而烦恼吗？diff-pdf这款开…
嵌入式GUI控件实战：ROTARY、SCROLLBAR、SLIDER原理与应用
1. 嵌入式GUI控件：从原理到实战的深度解析在嵌入式系统开发中，图形用户界面（GUI）的设计与实现往往是项目从“能用”到“好用”的关键一跃。不同于资源充沛的PC或移动平台，嵌入式设备的GUI需要在有限的CPU性能、内存空间…
Zotero Duplicates Merger：5步彻底清理文献库重复条目
Zotero Duplicates Merger：5步彻底清理文献库重复条目 【免费下载链接】ZoteroDuplicatesMerger A zotero plugin to automatically merge duplicate items 项目地址: https://gitcode.com/gh_mirrors/zo/ZoteroDuplicatesMerger 还在为文献库中堆积如山的重…
利用随机有限集理论对蜂群的ILQR和MPC控制研究附Matlab代码
✅作者简介：热爱科研的Matlab仿真开发者，擅长数据处理、建模仿真、程序设计、完整代码获取、论文复现及科研仿真。🍎 往期回顾关注个人主页：Matlab科研工作室🍊个人信条：格物致知,完整Matlab代码及仿真咨询…
为什么你的Gemini邮件CTE低于行业均值2.8倍？：从Prompt架构到发送时序的深度归因
更多请点击： https://intelliparadigm.com 第一章：为什么你的Gemini邮件CTE低于行业均值2.8倍？：从Prompt架构到发送时序的深度归因 Gemini邮件的客户转化效率（CTE）显著偏低，根本原因常被误判为…