MySQL面试题大全（真实职场版）

MySQL面试题大全（真实职场版）本题库整理互联网后端、开发、运维岗位高频MySQL面试题，摒弃空洞理论，全部贴合实际工作场景，分为基础语法、索引原理、事务与锁、性能优化、高阶特性、实战问题六大模块，答案简洁精准，适合面试突击、日常复盘。一、MySQL基础语法高频面试题1、MySQL常用数据类型有哪些？各自适用场景？数值类型tinyint：1字节，取值范围小，适用于状态字段（0/1、启用/禁用）int：4字节，常规整数存储，适用于ID、数量等通用数值bigint：8字节，长整型，适用于大自增ID、海量数据主键decimal：定点数，精准小数，适用于金额、汇率等需要精准计算的场景，禁止用float/double存金额字符串类型char(n)：固定长度，查询速度快，适用于手机号、身份证号、固定编码等长度固定的数据varchar(n)：可变长度，节省存储空间，适用于用户名、地址、备注等长度不固定数据，最大支持65535字节text：长文本，适用于文章内容、详细备注，不支持索引前缀以外的索引时间类型date：仅日期（年月日）time：仅时间（时分秒）datetime：日期+时间，范围大，不受时区影响，工作最常用timestamp：时间戳，范围较小，会根据时区转换，默认自动更新2、char和varchar的核心区别？存储方式：char为固定长度，定义多少位就占用多少空间；varchar为可变长度，仅占用实际数据空间性能：char查询、检索速度更快，无字符截断、补齐开销；varchar需要动态计算长度，性能略低尾部空格：char会自动补齐尾部空格，查询时忽略尾部空格；varchar严格保留输入的空格适用场景：固定短文本用char，可变长文本用varchar3、datetime和timestamp的区别？取值范围：datetime范围1000-01-01~9999-12-31；timestamp范围1970-01-01~2038-01-19时区影响：datetime存储原始时间，不受数据库、系统时区影响；timestamp存储时间戳数值，查询时会根据时区转换默认值：timestamp支持自动赋值、自动更新；datetime默认值为null，需手动设置默认值存储空间：datetime占用8字节，timestamp占用4字节4、SQL常用约束有哪些？作用分别是什么？主键约束（primarykey）：唯一且非空，一张表只能有一个主键，用于唯一标识数据行唯一约束（unique）：数据唯一，允许为空，一张表可设置多个唯一约束非空约束（notnull）：字段不允许存储null值，保证数据完整性默认约束（default）：字段无赋值时使用默认值，避免null数据外键约束（foreignkey）：关联两张表数据，保证数据参照完整性，生产环境一般禁用，由代码控制关联关系5、limit分页偏移量过大为什么慢？如何优化？原因：limit100000,10会先扫描前100010条数据，丢弃前100000条，只返回后10条，偏移量越大，扫描数据越多，IO开销越高。优化方案：主键分页：通过whereid>上一页最后一条idlimit10，避免大偏移量扫描延迟关联：先通过索引查询主键，再关联原表查询完整数据，减少回表开销业务限制：前端禁止超大分页，只提供上一页、下一页，不提供跳页功能6、union和unionall的区别？去重：union会自动去除重复数据，unionall直接拼接所有数据，不去重性能：union需要排序、去重，性能远低于unionall排序：union结果默认有序，unionall结果无序工作中无去重需求，一律使用unionall。二、MySQL索引核心面试题（重中之重）1、MySQL索引类型有哪些？主键索引（聚簇索引）：叶子节点存储整行数据，一张表只有一个普通索引（二级索引/辅助索引）：叶子节点存储索引列+主键ID唯一索引：索引列数据唯一，允许为空复合索引：多个字段组合的索引，遵循最左匹配原则全文索引：针对大文本字段分词检索，替代模糊查询2、聚簇索引和非聚簇索引的区别？存储内容：聚簇索引叶子节点存储完整行数据；非聚簇索引叶子节点存储索引值+主键ID数量：每张表仅有一个聚簇索引，可存在多个非聚簇索引查询流程：主键查询直接命中数据；普通索引查询需要先查索引，再通过主键回表查询完整数据数据排序：表数据物理顺序与聚簇索引顺序一致3、什么是回表？什么是覆盖索引？回表：通过普通索引查询到主键ID后，需要再次通过主键索引查询整行数据，产生二次IO，就是回表操作，会降低查询性能。覆盖索引：查询的所有字段都包含在索引中，无需回表，直接通过索引获取全部数据，是最优索引优化方案。示例：建立索引(a,b)，查询selecta,bfromtablewherea=1，触发覆盖索引。4、最左匹配原则是什么？建立复合索引(a,b,c)时，查询条件必须匹配最左侧的索引字段，索引才会生效。生效场景：wherea=1、wherea=1andb=2、wherea=1andb=2andc=3失效场景：whereb=2、wherec=3、whereb=2andc=3（跳过最左a字段）补充：范围查询（>、<、between）右侧的字段索引全部失效，因此复合索引中，等值条件放前面，范围条件放最后。5、索引失效的常见场景？违反最左匹配原则，复合索引跳过前置字段索引列使用函数、运算：whereleft(name,2)='xx'、whereid+1=10隐式类型转换：字符串字段传数字、数字字段传字符串使用!=、<>、notin、notexistslike%xx左模糊查询，%%xx全模糊查询（xx%右模糊索引有效）or连接的字段有未建立索引的字段MySQL判断全表扫描比索引查询更快，自动放弃索引6、为什么索引结构选用B+树而不是B树、哈希、二叉树？二叉树：层级太深，数据量大时查询IO次数多，性能极差哈希索引：不支持范围查询、排序、模糊查询，仅支持精准匹配，且存在哈希冲突B树：每个节点都存储数据，页存储索引数量少，层级多，IO次数多；不适合全表遍历、范围查询B+树：非叶子节点只存索引，层级更低、查询更快；所有数据都在叶子节点，有序排列，支持高效范围查询、排序、分页；叶子节点链表结构，遍历数据效率极高7、索引是不是越多越好？为什么？不是，索引有利有弊。优点：提升查询、检索、排序、分组性能缺点：占用磁盘存储空间降低写入、更新、删除性能，数据变更时需要同步更新索引结构原则：高频查询字段建索引，低频查询、高频更新字段不建索引。三、事务与锁机制高频面试题1、MySQL事务四大特性（ACID）？原子性（Atomicity）：事务要么全部执行成功，要么全部回滚，不可分割一致性（Consistency）：事务执行前后，数据库数据状态保持合法一致，无非法数据隔离性（Isolation）：多个事务并发执行时，相互独立、互不干扰持久性（Durability）：事务提交成功后，数据永久落地，断电、重启不丢失2、MySQL四大事务隔离级别？默认级别是什么？隔离级别从低到高：读未提交：可读取其他事务未提交数据，存在脏读、不可重复读、幻读读已提交：只能读取已提交数据，解决脏读，存在不可重复读、幻读可重复读：同一事务内多次读取同一数据结果一致，解决脏读、不可重复读，存在幻读（MySQL默认隔离级别）串行化：事务串行执行，无并发问题，解决所有并发问题，性能最低3、脏读、不可重复读、幻读的区别？脏读：一个事务读取到另一个事务未提交的修改数据，数据无效不可重复读：同一事务内，两次读取同一数据，结果不一致（其他事务中途修改并提交）幻读：同一事务内，两次范围查询，数据条数不一致（其他事务新增/删除数据）4、InnoDB如何解决幻读问题？MySQL可重复读级别下，通过间隙锁+临键锁解决大部分幻读问题，无法完全杜绝，但能满足绝大多数业务场景；彻底解决幻读需使用串行化隔离级别。5、MySQL锁的分类？行锁和表锁的区别？按锁粒度分为：表级锁、行级锁、页级锁表锁（MyISAM引擎默认）锁定整张表，加锁快、开销小并发度低，写锁阻塞所有读写操作行锁（InnoDB引擎默认）锁定单行数据，仅锁定操作行并发度高，行之间互不影响加锁慢、开销大，容易产生死锁重点：行锁必须走索引，无索引会降级为表锁6、什么是死锁？如何避免和解决死锁？死锁成因：两个及以上事务，互相持有对方需要的锁，同时等待对方释放锁，无限阻塞。避免方案：统一事务锁获取顺序，所有事务按固定顺序操作数据精简事务逻辑，缩短事务执行时间，避免长事务避免事务中嵌套复杂查询、外部接口调用设置锁超时时间，自动释放阻塞锁解决方式：MySQL自带死锁检测机制，自动回滚代价最小的事务，解除死锁。四、MySQL存储引擎面试题1、InnoDB和MyISAM的核心区别？对比维度InnoDBMyISAM事务支持支持事务、ACID不支持事务锁机制行锁+表锁，并发高仅表锁，并发低崩溃恢复支持redo/undo日志，可恢复数据无日志，崩溃易丢数据、损坏表外键支持外键不支持外键全文索引5.6版本后支持原生支持适用场景读写均衡、高并发、需要事务的业务（主流默认）静态数据、多读少写、无事务场景2、InnoDB三大日志作用？redolog（重做日志）：保证事务持久性。事务提交前写入redolog，数据库崩溃重启后，根据日志重做未落地的数据，防止数据丢失undolog（回滚日志）：保证事务原子性、隔离性。存储数据修改前的快照，事务回滚时恢复原始数据，同时实现可重复读隔离级别binlog（二进制日志）：服务于主从复制、数据恢复。记录所有数据库增删改语句，主库同步给从库，可通过日志恢复指定时间点数据五、MySQL性能优化面试题1、MySQL慢查询优化整体思路？开启慢查询日志，定位慢SQL，记录执行时间、扫描行数、返回行数通过explain分析SQL执行计划，查看索引使用、扫描行数、连接方式优化索引：新增缺失索引、优化复合索引顺序、删除无效冗余索引优化SQL语句：避免索引失效场景、优化分页、减少关联查询、避免大事务表结构优化：合理选择数据类型、拆分大字段、冷热数据分离架构优化：读写分离、分库分表、缓存兜底2、explain关键字重点字段解读？type：核心字段，查询性能等级，从优到劣：system>const>eq_ref>ref>range>index>all（all为全表扫描，必须优化）key：实际生效的索引key_len：索引使用长度，判断复合索引是否完全生效rows：预估扫描行数，数值越小性能越好Extra：额外信息，Usingfilesort（文件排序，需优化）、Usingtemporary（临时表，需优化）、Usingindex（覆盖索引，最优）3、什么是SQL文件排序、临时表？如何优化？Usingfilesort：排序字段无索引，MySQL需要额外排序，消耗CPU和IO。优化：给orderby、groupby字段建立索引。Usingtemporary：查询需要建立临时表存储中间数据，常见于groupby、distinct、多表关联。优化：优化索引、精简查询字段、拆分复杂SQL。4、大表优化方案（千万级数据）？字段优化：禁用大文本、拆分冷热字段、精简数据类型索引优化：精准建索引，删除冗余索引，避免索引失效SQL优化：禁止全表扫描、禁止超大分页、避免大事务表优化：定时清理冗余数据、归档历史数据架构优化：水平分表、垂直分表、分库分表、读写分离5、读写分离的原理和适用场景？原理：主库负责处理新增、修改、删除写操作，从库同步主库binlog日志，保证数据一致；读操作分摊到多个从库，分担主库压力。适用场景：业务读多写少、数据库查询压力大、单库性能瓶颈的场景。问题：存在主从延迟，实时查询需要走主库。六、实战高频场景面试题1、如何解决MySQL主从延迟？优化主库：减少大事务、批量写入，降低主库写入压力优化从库：提升从库硬件配置、优化从库索引、避免从库大查询配置优化：开启并行复制、调整binlog日志格式为row模式业务兼容：核心实时接口查主库，非实时接口查从库2、为什么不建议用select*？查询多余字段，增加网络传输、磁盘IO开销无法触发覆盖索引，必然产生回表，降低查询性能表结构变更后，代码可能出现字段异常，兼容性差3、in和exists的区别？如何选择？in：先查询子表数据，再匹配主表，子表数据少用inexists：遍历主表，逐行匹配子表，主表数据少、子表数据多用exists新版本MySQL优化器会自动优化，无需手动区分，但超大批量数据仍需手动适配。4、重复数据如何去重？核心思路：保留一条最新/最早数据，删除重复数据。常用方案：通过分组查询重复ID，根据主键或唯一标识删除冗余数据；新建临时表存储唯一数据，清空原表后回写。5、MySQL如何防止超卖？数据库层：利用行锁+事务，更新库存时携带库存判断条件（updatestocksetnum=num-1whereid=1andnum>0）业务层：分布式锁、限流、库存预热兜底核心：基于数据库原子更新，避免先查后改的并发漏洞。七、高频简答压轴题MySQ