MySQL知识点覆盖索引、MVCC、存储引擎、事务锁、性能优化等核心点一、索引专题最高频1. 什么是索引有什么作用本质加速数据检索的有序辅助数据结构相当于字典的目录核心作用减少磁盘IO次数把全表扫描变成索引查找千万级表查询速度提升几个数量级额外作用利用索引有序性避免文件排序Using filesort、通过唯一索引保证数据完整性缺点增加写入开销需维护索引结构、占用额外磁盘空间、过多索引会增加优化器选择成本2. MySQL索引为什么用B树不用二叉树、红黑树、B树、哈希表数据结构不适合做索引的原因二叉查找树可能退化成链表高度极高IO次数暴增红黑树仍是二叉树百万数据约20层IO次数太多B树非叶子节点也存数据一个磁盘页存的索引少树更高无链表范围查询差哈希表仅支持等值查询不支持范围、排序、模糊查询哈希冲突严重B树核心优势IO次数最少非叶子节点只存索引不存数据一个页能存更多索引树高仅3-4层可存千万级数据查询效率稳定所有查询都要到叶子节点时间复杂度都是O(logn)范围查询极快叶子节点用双向链表串联只需找到首尾节点遍历即可缓存友好非叶子节点小内存能缓存更多索引3. 聚簇索引和非聚簇索引有什么区别特性聚簇索引非聚簇索引二级索引叶子节点内容完整的整行数据索引值 主键值数量限制一张表只能有1个一张表可以有多个查询效率极高无需回表较低需回表覆盖索引除外物理存储数据按索引顺序排列索引顺序与物理数据无关典型代表主键索引普通索引、唯一索引、联合索引核心结论InnoDB必须有聚簇索引无主键则自动生成6字节rowid优先用自增主键做聚簇索引4. 什么是回表如何避免回表定义通过二级索引查到主键值后再去聚簇索引查完整行数据的过程本质是两次B树查找避免方法使用覆盖索引查询的所有字段都包含在索引中尽量直接用主键查询只查询需要的字段避免SELECT *5. 什么是覆盖索引有什么好处定义查询需要的所有字段都能在某个索引中找到不需要回表核心好处消除回表的二次IO查询性能提升数倍示例给order表建idx_userid_createtime(user_id, create_time)索引SELECT order_id, create_time FROM order WHERE user_id123→ 覆盖索引不回表SELECT * FROM order WHERE user_id123→ 需回表查其他字段6. 什么是最左匹配原则定义联合索引查询时必须从索引最左侧字段开始匹配中间不能跳过字段底层原因联合索引的B树先按第一个字段排序再按第二个排序以此类推示例联合索引idx_abc(a,b,c)WHERE a1 AND b2 AND c3→ 用全部3个字段WHERE a1 AND b2→ 用前2个字段WHERE a1 AND c3→ 只用a字段c字段用不到WHERE b2 AND c3→ 完全用不到索引注意MySQL优化器会自动调整where条件顺序WHERE c3 AND a1 AND b2也能用到索引7. 什么是索引下推ICPIndex Condition Pushdown定义MySQL 5.6引入的优化技术把原本在Server层的条件过滤下推到存储引擎层优化前存储引擎只按最左字段筛选数据返回所有符合条件的主键Server层再过滤其他条件优化后存储引擎在遍历索引时同时判断所有索引字段的条件只返回符合条件的主键效果大幅减少回表次数尤其当最左字段筛选性差、后续字段筛选性好时示例索引idx_age_name(age,name)WHERE age20 AND name LIKE li%无ICP查所有age20的记录回表后再过滤name有ICP在索引中同时过滤age和name只回表符合条件的记录8. 索引有哪些设计原则索引不是越多越好单表索引不超过5个只给高频查询字段建索引低频查询字段没必要优先给高基数字段建索引基数80%的字段如手机号、用户名性别、状态等低基数字段不建索引字段尽量小用int不用bigint用varchar(20)不用varchar(255)避免给允许NULL的字段建索引NULL值会导致索引效率降低频繁更新的字段不建索引会导致大量页分裂和页合并尽量用联合索引代替多个单列索引能更多触发覆盖索引联合索引遵循左高右低高频字段、等值字段、高基数字段放左边主键尽量用自增ID避免随机插入导致的页分裂9. 哪些情况会导致索引失效索引字段用了函数或表达式WHERE YEAR(create_time)2026、WHERE age126隐式类型转换varchar类型字段用数字查询WHERE phone13800138000模糊查询%在左边WHERE name LIKE %lili%不会失效违反最左匹配原则联合索引跳过左侧字段用OR连接非索引字段WHERE namezhangsan OR age20age无索引则全表扫描低基数字段建索引优化器认为全表扫描更快查询结果集过大返回超过表数据30%时优化器放弃索引表数据量太小几十行的表全表扫描更快表统计信息过时执行ANALYZE TABLE 表名更新统计信息10. 什么是前缀索引什么时候用定义只对字符串字段的前N个字符建索引语法CREATE INDEX idx_name ON user(name(10))适用场景长字符串字段如邮箱、URL前N个字符已经有足够的区分度优点减少索引占用空间提高索引效率缺点无法用于ORDER BY和GROUP BY也无法做覆盖索引二、MVCC专题必考题11. 什么是MVCC解决了什么问题定义多版本并发控制通过维护数据的多个历史版本实现读写互不阻塞解决的核心问题没有MVCC时读写会互斥读加共享锁写加排他锁高并发下性能极差核心优势读不加锁写不阻塞读大幅提升数据库并发性能12. MVCC是如何实现的全称Multi-Version Concurrency Control中文多版本并发控制作用MySQL InnoDB 实现读不加锁、读写不阻塞提升并发性能核心原理每行数据存多个版本通过undo log 行隐藏字段生成数据快照生效隔离级别主要用于RC读已提交、RR可重复读和锁区别乐观并发机制不靠锁而是靠数据版本控制并发InnoDB 三个隐藏字段trx_id事务 ID、roll_pointer回滚指针、row_id无主键时使用RR 隔离级别下MVCC 间隙锁 解决幻读问题依赖三个核心组件隐藏字段每行数据隐含两个字段DB_TRX_ID最后修改该行的事务ID事务ID递增DB_ROLL_PTR回滚指针指向undo日志中的旧版本Undo日志存储数据的历史版本同一行数据的多次修改会形成一条版本链Read View读视图事务启动时生成的快照判断哪个版本对当前事务可见13. MVCC如何实现可重复读可重复读隔离级别下事务启动时生成一个Read View整个事务期间都用这个Read View可见性规则只能看到创建版本≤当前事务ID的数据只能看到删除版本当前事务ID或未删除的数据效果事务期间看到的数据是一致的其他事务的修改对当前事务不可见14. MVCC和锁的关系MVCC只适用于普通SELECT查询快照读不需要加锁对于写操作INSERT/UPDATE/DELETE和加锁读SELECT ... FOR UPDATE仍然需要加锁当前读两者结合InnoDB实现了读写互不阻塞读操作不影响写操作写操作也不影响读操作三、存储引擎专题15. InnoDB和MyISAM的核心区别特性InnoDBMyISAM事务支持支持ACID事务不支持锁机制行级锁基于索引 表级锁仅表级锁索引类型聚簇索引 非聚簇索引仅非聚簇索引外键支持不支持崩溃恢复支持通过redo/undo日志不支持数据易丢失MVCC支持不支持适用场景绝大多数业务电商、金融、社交仅只读/归档场景已逐步淘汰核心结论非特殊场景一律用InnoDBMySQL 5.5之后默认引擎就是InnoDB16. InnoDB为什么推荐用自增主键避免页分裂自增主键是顺序插入不会在数据页中间插入数据减少页分裂和页合并索引体积小int类型比UUID等字符串类型小很多一个页能存更多索引树高更低减少回表开销二级索引的叶子节点存主键值主键越小二级索引体积越小四、事务与锁专题17. 事务的ACID特性是什么原子性Atomicity事务是不可分割的最小单位要么全部成功要么全部失败由undo日志保证一致性Consistency事务执行前后数据的完整性约束不被破坏由其他三个特性共同保证隔离性Isolation多个事务并发执行时互不干扰由锁和MVCC保证持久性Durability事务提交后修改永久生效即使数据库崩溃也不会丢失由redo日志保证18. 事务的四个隔离级别分别解决了什么问题隔离级别脏读不可重复读幻读读未提交Read Uncommitted可能可能可能读已提交Read Committed解决可能可能可重复读Repeatable Read解决解决可能InnoDB通过MVCC间隙锁解决串行化Serializable解决解决解决脏读读到其他事务未提交的数据不可重复读同一事务内两次查询同一行数据结果不同其他事务修改了数据幻读同一事务内两次查询同一范围结果行数不同其他事务插入了数据MySQL默认隔离级别可重复读RR19. InnoDB如何解决幻读InnoDB在可重复读隔离级别下通过MVCC 间隙锁Gap Lock解决幻读快照读普通SELECT通过MVCC读取历史版本看不到其他事务的插入当前读加锁读/写操作通过间隙锁锁住记录之间的间隙防止其他事务在间隙中插入数据20. 什么是死锁如何避免死锁定义多个事务互相持有对方需要的锁且都不释放形成循环等待互斥资源同一时间只允许一个事务持有请求并保持事务已持有部分资源又去申请新资源不释放已有资源不可剥夺已被占有的资源不能被其他事务强行抢占循环等待多个事务形成环形资源等待链避免方法所有事务按相同顺序获取锁比如都按ID从小到大的顺序更新尽量用小事务减少持有锁的时间避免大范围加锁尽量用行级锁避免表级锁设置合理的锁超时时间innodb_lock_wait_timeout默认50秒用乐观锁代替悲观锁通过版本号或时间戳实现五、日志专题21. redo日志和undo日志的区别redo log重做日志崩溃恢复用保证事务持久性宕机后靠它把未刷盘的数据恢复。MySQL崩溃恢复核心先写日志再写磁盘宕机后依据它重做未落地的数据保障事务提交后数据不丢失。undo log回滚日志事务回滚 MVCC 用保存数据旧版本实现回滚和读写不阻塞。记录数据修改前的旧版本一是供事务回滚二是为 MVCC 提供历史快照实现无锁读。特性redo日志undo日志类型物理日志记录数据页的修改逻辑日志记录反向操作核心作用保证事务持久性崩溃恢复保证事务原子性回滚 支撑MVCC写入时机事务执行中写入提交时刷盘事务修改数据前立即生成生命周期环形覆盖刷盘后可复用提交后保留由purge线程异步清理22. 什么是WAL写前日志机制全称Write-Ahead Logging中文预写日志 / 写前日志定义事务提交时先写redo日志再异步刷盘数据核心优势把随机写磁盘变成顺序写redo日志大幅提升写入性能崩溃恢复数据库宕机后重启时通过redo重做日志 记录 所有已提交但未刷盘的修改保证数据不丢失WAL 规则改数据前先把修改记录写到 redo 日志修改内存数据页同时把「这次做了什么修改」记录到redo logredo log 是固定、连续的磁盘文件所有事务的修改日志从头到尾依次追加写入 这就是顺序写几乎没有寻址开销速度飞快。数据页不会立刻刷盘MySQL 后台线程慢慢、批量把内存脏页刷回磁盘异步刷盘六、执行计划与性能优化专题23. EXPLAIN执行计划中最重要的四个字段是什么怎么看type key rows Extratype查询效率从好到坏system const eq_ref ref range index ALL至少要达到ref或range出现ALL就是全表扫描必须优化const根据主键/唯一索引查询只返回 1 行 →极快ref普通索引匹配多行 →良好range索引范围查询between、in、→一般可用index遍历整个索引树 →慢key实际使用的索引为NULL表示没用到索引possible_keyskey_len 可以用来判断是否用到了联合索引的多个字段。rows预估扫描的行数越小越好Extra额外信息Using index覆盖索引极好Using where使用 where 条件过滤MySQL 在服务器层对数据进行过滤而不是在存储引擎层通过索引过滤Using filesort文件排序严重性能问题Using temporary使用临时表性能差Using index condition索引下推优化NULL正常no matching row in const table查询里用到了const类型的常量表单表唯一匹配但该表没有找到任何符合条件的数据属于无数据匹配Using join buffer (hash join)MySQL 无法使用索引嵌套循环Nested Loop Join只能用哈希连接Hash Join哈希连接需要把一张表的数据读入join buffer内存构建哈希表再和另一张表做匹配只有当连接字段没有可用索引时才会触发这个行为较好的总结type别出现 ALLkey别为 NULLrows越小越好Extra别出现 Using filesort / Using temporary所有字段id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extraid执行顺序select_type查询类型table表名type查询效率最重要possible_keys可能用的索引key实际用的索引key_len索引长度ref匹配条件rows预估扫描行数Extra额外关键信息性能坑都在这idid 查询执行的顺序编号数字越大越先执行子查询、联合查询会出现多个 id 看执行顺序是否合理。单表简单查询永远是1select_type含义查询的类型最重要的字段之一常见值SIMPLE简单查询无子查询/union→最好SUBQUERY子查询DERIVED派生表from 里的子查询主表为PRIMARYUNIONunion 查询DEPENDENT SUBQUERY相关子查询 →性能差好坏判断能 SIMPLE 就别 SUBQUERY、DERIVED。derive 英 / dɪˈraɪv / 美 / dɪˈraɪv / 简明柯林斯 v. 获得取得起源于来自提取衍生化学物质ref含义与索引比较的列或常量常见const和常量匹配where id1库.表.列与另一张表的列关联24. 什么是慢查询如何分析慢查询定义执行时间超过long_query_time默认10秒建议设为1秒的SQL开启慢查询日志SETGLOBALslow_query_logON;SETGLOBALlong_query_time1;分析工具mysqldumpslow、pt-query-digest优化步骤用EXPLAIN分析执行计划 → 检查是否用到索引 → 优化SQL语句或添加索引25. 如何优化大量数据插入的性能批量插入合并多条INSERT为一条每批不超过1000条关闭自动提交手动控制事务提交减少事务次数临时关闭索引插入前删除索引插入完成后重建调整参数innodb_flush_log_at_trx_commit0、bulk_insert_buffer_size使用LOAD DATA比INSERT快10倍以上26. 数据库性能优化的金字塔是什么从易到难收益从高到低架构优化读写分离、分库分表、缓存、搜索引擎SQL与索引优化优化慢SQL、合理设计索引MySQL配置优化缓冲池、连接数、日志参数硬件与OS优化SSD、RAID、文件系统、内核参数RAIDRedundant Array of Independent Disks中文独立磁盘冗余阵列早期也叫廉价磁盘冗余阵列将多块物理硬盘通过硬件 / 软件组合为一个逻辑磁盘核心解决两个问题提升读写性能数据冗余容错硬盘坏了不丢数据、不宕机是服务器、数据库存储的标准方案。七、其他高频题27. 什么是行锁和表锁表锁锁住整个表粒度大冲突概率高并发性能差MyISAM仅支持表锁行锁锁住具体的行粒度小冲突概率低并发性能好InnoDB支持行锁注意InnoDB的行锁是基于索引的没有索引的查询会升级为表锁28. 什么是乐观锁和悲观锁悲观锁假设一定会发生并发冲突操作前先加锁实现SELECT ... FOR UPDATE适用写多的场景乐观锁假设不会发生并发冲突提交时检查是否有冲突实现版本号机制UPDATE table SET ..., versionversion1 WHERE id1 AND versionold_version适用读多的场景29. 为什么不推荐用SELECT *增加IO开销读取不需要的字段尤其是大字段TEXT、BLOB无法使用覆盖索引必须回表查询增加网络传输量返回多余数据占用带宽降低代码可读性不知道具体查询了哪些字段30. 什么是主从延迟如何解决主从延迟定义主库写入数据后从库同步数据的时间差原因主库并发写入量大从库单线程重放binlog解决方法优化主库写入减少大事务升级从库硬件采用多线程复制MySQL 5.7支持读写分离时关键业务读主库分库分表分散压力MySQL binlog全称Binary Log二进制日志作用记录数据库所有修改类SQL增/删/改、DDL查询不记录。核心用途主从复制主库把binlog同步给从库实现数据同步数据恢复误删/误改后用binlog回滚找回数据三种格式statement记录SQL语句体积小可能有数据不一致row记录行数据变更精准默认推荐mixed混合前两种一句话总结MySQL的数据变更流水账用来做主从同步和数据恢复。
MySQL知识点 覆盖索引、MVCC、存储引擎、事务锁、性能优化等核心点
发布时间：2026/6/11 3:41:36
MySQL知识点覆盖索引、MVCC、存储引擎、事务锁、性能优化等核心点一、索引专题最高频1. 什么是索引有什么作用本质加速数据检索的有序辅助数据结构相当于字典的目录核心作用减少磁盘IO次数把全表扫描变成索引查找千万级表查询速度提升几个数量级额外作用利用索引有序性避免文件排序Using filesort、通过唯一索引保证数据完整性缺点增加写入开销需维护索引结构、占用额外磁盘空间、过多索引会增加优化器选择成本2. MySQL索引为什么用B树不用二叉树、红黑树、B树、哈希表数据结构不适合做索引的原因二叉查找树可能退化成链表高度极高IO次数暴增红黑树仍是二叉树百万数据约20层IO次数太多B树非叶子节点也存数据一个磁盘页存的索引少树更高无链表范围查询差哈希表仅支持等值查询不支持范围、排序、模糊查询哈希冲突严重B树核心优势IO次数最少非叶子节点只存索引不存数据一个页能存更多索引树高仅3-4层可存千万级数据查询效率稳定所有查询都要到叶子节点时间复杂度都是O(logn)范围查询极快叶子节点用双向链表串联只需找到首尾节点遍历即可缓存友好非叶子节点小内存能缓存更多索引3. 聚簇索引和非聚簇索引有什么区别特性聚簇索引非聚簇索引二级索引叶子节点内容完整的整行数据索引值 主键值数量限制一张表只能有1个一张表可以有多个查询效率极高无需回表较低需回表覆盖索引除外物理存储数据按索引顺序排列索引顺序与物理数据无关典型代表主键索引普通索引、唯一索引、联合索引核心结论InnoDB必须有聚簇索引无主键则自动生成6字节rowid优先用自增主键做聚簇索引4. 什么是回表如何避免回表定义通过二级索引查到主键值后再去聚簇索引查完整行数据的过程本质是两次B树查找避免方法使用覆盖索引查询的所有字段都包含在索引中尽量直接用主键查询只查询需要的字段避免SELECT *5. 什么是覆盖索引有什么好处定义查询需要的所有字段都能在某个索引中找到不需要回表核心好处消除回表的二次IO查询性能提升数倍示例给order表建idx_userid_createtime(user_id, create_time)索引SELECT order_id, create_time FROM order WHERE user_id123→ 覆盖索引不回表SELECT * FROM order WHERE user_id123→ 需回表查其他字段6. 什么是最左匹配原则定义联合索引查询时必须从索引最左侧字段开始匹配中间不能跳过字段底层原因联合索引的B树先按第一个字段排序再按第二个排序以此类推示例联合索引idx_abc(a,b,c)WHERE a1 AND b2 AND c3→ 用全部3个字段WHERE a1 AND b2→ 用前2个字段WHERE a1 AND c3→ 只用a字段c字段用不到WHERE b2 AND c3→ 完全用不到索引注意MySQL优化器会自动调整where条件顺序WHERE c3 AND a1 AND b2也能用到索引7. 什么是索引下推ICPIndex Condition Pushdown定义MySQL 5.6引入的优化技术把原本在Server层的条件过滤下推到存储引擎层优化前存储引擎只按最左字段筛选数据返回所有符合条件的主键Server层再过滤其他条件优化后存储引擎在遍历索引时同时判断所有索引字段的条件只返回符合条件的主键效果大幅减少回表次数尤其当最左字段筛选性差、后续字段筛选性好时示例索引idx_age_name(age,name)WHERE age20 AND name LIKE li%无ICP查所有age20的记录回表后再过滤name有ICP在索引中同时过滤age和name只回表符合条件的记录8. 索引有哪些设计原则索引不是越多越好单表索引不超过5个只给高频查询字段建索引低频查询字段没必要优先给高基数字段建索引基数80%的字段如手机号、用户名性别、状态等低基数字段不建索引字段尽量小用int不用bigint用varchar(20)不用varchar(255)避免给允许NULL的字段建索引NULL值会导致索引效率降低频繁更新的字段不建索引会导致大量页分裂和页合并尽量用联合索引代替多个单列索引能更多触发覆盖索引联合索引遵循左高右低高频字段、等值字段、高基数字段放左边主键尽量用自增ID避免随机插入导致的页分裂9. 哪些情况会导致索引失效索引字段用了函数或表达式WHERE YEAR(create_time)2026、WHERE age126隐式类型转换varchar类型字段用数字查询WHERE phone13800138000模糊查询%在左边WHERE name LIKE %lili%不会失效违反最左匹配原则联合索引跳过左侧字段用OR连接非索引字段WHERE namezhangsan OR age20age无索引则全表扫描低基数字段建索引优化器认为全表扫描更快查询结果集过大返回超过表数据30%时优化器放弃索引表数据量太小几十行的表全表扫描更快表统计信息过时执行ANALYZE TABLE 表名更新统计信息10. 什么是前缀索引什么时候用定义只对字符串字段的前N个字符建索引语法CREATE INDEX idx_name ON user(name(10))适用场景长字符串字段如邮箱、URL前N个字符已经有足够的区分度优点减少索引占用空间提高索引效率缺点无法用于ORDER BY和GROUP BY也无法做覆盖索引二、MVCC专题必考题11. 什么是MVCC解决了什么问题定义多版本并发控制通过维护数据的多个历史版本实现读写互不阻塞解决的核心问题没有MVCC时读写会互斥读加共享锁写加排他锁高并发下性能极差核心优势读不加锁写不阻塞读大幅提升数据库并发性能12. MVCC是如何实现的全称Multi-Version Concurrency Control中文多版本并发控制作用MySQL InnoDB 实现读不加锁、读写不阻塞提升并发性能核心原理每行数据存多个版本通过undo log 行隐藏字段生成数据快照生效隔离级别主要用于RC读已提交、RR可重复读和锁区别乐观并发机制不靠锁而是靠数据版本控制并发InnoDB 三个隐藏字段trx_id事务 ID、roll_pointer回滚指针、row_id无主键时使用RR 隔离级别下MVCC 间隙锁 解决幻读问题依赖三个核心组件隐藏字段每行数据隐含两个字段DB_TRX_ID最后修改该行的事务ID事务ID递增DB_ROLL_PTR回滚指针指向undo日志中的旧版本Undo日志存储数据的历史版本同一行数据的多次修改会形成一条版本链Read View读视图事务启动时生成的快照判断哪个版本对当前事务可见13. MVCC如何实现可重复读可重复读隔离级别下事务启动时生成一个Read View整个事务期间都用这个Read View可见性规则只能看到创建版本≤当前事务ID的数据只能看到删除版本当前事务ID或未删除的数据效果事务期间看到的数据是一致的其他事务的修改对当前事务不可见14. MVCC和锁的关系MVCC只适用于普通SELECT查询快照读不需要加锁对于写操作INSERT/UPDATE/DELETE和加锁读SELECT ... FOR UPDATE仍然需要加锁当前读两者结合InnoDB实现了读写互不阻塞读操作不影响写操作写操作也不影响读操作三、存储引擎专题15. InnoDB和MyISAM的核心区别特性InnoDBMyISAM事务支持支持ACID事务不支持锁机制行级锁基于索引 表级锁仅表级锁索引类型聚簇索引 非聚簇索引仅非聚簇索引外键支持不支持崩溃恢复支持通过redo/undo日志不支持数据易丢失MVCC支持不支持适用场景绝大多数业务电商、金融、社交仅只读/归档场景已逐步淘汰核心结论非特殊场景一律用InnoDBMySQL 5.5之后默认引擎就是InnoDB16. InnoDB为什么推荐用自增主键避免页分裂自增主键是顺序插入不会在数据页中间插入数据减少页分裂和页合并索引体积小int类型比UUID等字符串类型小很多一个页能存更多索引树高更低减少回表开销二级索引的叶子节点存主键值主键越小二级索引体积越小四、事务与锁专题17. 事务的ACID特性是什么原子性Atomicity事务是不可分割的最小单位要么全部成功要么全部失败由undo日志保证一致性Consistency事务执行前后数据的完整性约束不被破坏由其他三个特性共同保证隔离性Isolation多个事务并发执行时互不干扰由锁和MVCC保证持久性Durability事务提交后修改永久生效即使数据库崩溃也不会丢失由redo日志保证18. 事务的四个隔离级别分别解决了什么问题隔离级别脏读不可重复读幻读读未提交Read Uncommitted可能可能可能读已提交Read Committed解决可能可能可重复读Repeatable Read解决解决可能InnoDB通过MVCC间隙锁解决串行化Serializable解决解决解决脏读读到其他事务未提交的数据不可重复读同一事务内两次查询同一行数据结果不同其他事务修改了数据幻读同一事务内两次查询同一范围结果行数不同其他事务插入了数据MySQL默认隔离级别可重复读RR19. InnoDB如何解决幻读InnoDB在可重复读隔离级别下通过MVCC 间隙锁Gap Lock解决幻读快照读普通SELECT通过MVCC读取历史版本看不到其他事务的插入当前读加锁读/写操作通过间隙锁锁住记录之间的间隙防止其他事务在间隙中插入数据20. 什么是死锁如何避免死锁定义多个事务互相持有对方需要的锁且都不释放形成循环等待互斥资源同一时间只允许一个事务持有请求并保持事务已持有部分资源又去申请新资源不释放已有资源不可剥夺已被占有的资源不能被其他事务强行抢占循环等待多个事务形成环形资源等待链避免方法所有事务按相同顺序获取锁比如都按ID从小到大的顺序更新尽量用小事务减少持有锁的时间避免大范围加锁尽量用行级锁避免表级锁设置合理的锁超时时间innodb_lock_wait_timeout默认50秒用乐观锁代替悲观锁通过版本号或时间戳实现五、日志专题21. redo日志和undo日志的区别redo log重做日志崩溃恢复用保证事务持久性宕机后靠它把未刷盘的数据恢复。MySQL崩溃恢复核心先写日志再写磁盘宕机后依据它重做未落地的数据保障事务提交后数据不丢失。undo log回滚日志事务回滚 MVCC 用保存数据旧版本实现回滚和读写不阻塞。记录数据修改前的旧版本一是供事务回滚二是为 MVCC 提供历史快照实现无锁读。特性redo日志undo日志类型物理日志记录数据页的修改逻辑日志记录反向操作核心作用保证事务持久性崩溃恢复保证事务原子性回滚 支撑MVCC写入时机事务执行中写入提交时刷盘事务修改数据前立即生成生命周期环形覆盖刷盘后可复用提交后保留由purge线程异步清理22. 什么是WAL写前日志机制全称Write-Ahead Logging中文预写日志 / 写前日志定义事务提交时先写redo日志再异步刷盘数据核心优势把随机写磁盘变成顺序写redo日志大幅提升写入性能崩溃恢复数据库宕机后重启时通过redo重做日志 记录 所有已提交但未刷盘的修改保证数据不丢失WAL 规则改数据前先把修改记录写到 redo 日志修改内存数据页同时把「这次做了什么修改」记录到redo logredo log 是固定、连续的磁盘文件所有事务的修改日志从头到尾依次追加写入 这就是顺序写几乎没有寻址开销速度飞快。数据页不会立刻刷盘MySQL 后台线程慢慢、批量把内存脏页刷回磁盘异步刷盘六、执行计划与性能优化专题23. EXPLAIN执行计划中最重要的四个字段是什么怎么看type key rows Extratype查询效率从好到坏system const eq_ref ref range index ALL至少要达到ref或range出现ALL就是全表扫描必须优化const根据主键/唯一索引查询只返回 1 行 →极快ref普通索引匹配多行 →良好range索引范围查询between、in、→一般可用index遍历整个索引树 →慢key实际使用的索引为NULL表示没用到索引possible_keyskey_len 可以用来判断是否用到了联合索引的多个字段。rows预估扫描的行数越小越好Extra额外信息Using index覆盖索引极好Using where使用 where 条件过滤MySQL 在服务器层对数据进行过滤而不是在存储引擎层通过索引过滤Using filesort文件排序严重性能问题Using temporary使用临时表性能差Using index condition索引下推优化NULL正常no matching row in const table查询里用到了const类型的常量表单表唯一匹配但该表没有找到任何符合条件的数据属于无数据匹配Using join buffer (hash join)MySQL 无法使用索引嵌套循环Nested Loop Join只能用哈希连接Hash Join哈希连接需要把一张表的数据读入join buffer内存构建哈希表再和另一张表做匹配只有当连接字段没有可用索引时才会触发这个行为较好的总结type别出现 ALLkey别为 NULLrows越小越好Extra别出现 Using filesort / Using temporary所有字段id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extraid执行顺序select_type查询类型table表名type查询效率最重要possible_keys可能用的索引key实际用的索引key_len索引长度ref匹配条件rows预估扫描行数Extra额外关键信息性能坑都在这idid 查询执行的顺序编号数字越大越先执行子查询、联合查询会出现多个 id 看执行顺序是否合理。单表简单查询永远是1select_type含义查询的类型最重要的字段之一常见值SIMPLE简单查询无子查询/union→最好SUBQUERY子查询DERIVED派生表from 里的子查询主表为PRIMARYUNIONunion 查询DEPENDENT SUBQUERY相关子查询 →性能差好坏判断能 SIMPLE 就别 SUBQUERY、DERIVED。derive 英 / dɪˈraɪv / 美 / dɪˈraɪv / 简明柯林斯 v. 获得取得起源于来自提取衍生化学物质ref含义与索引比较的列或常量常见const和常量匹配where id1库.表.列与另一张表的列关联24. 什么是慢查询如何分析慢查询定义执行时间超过long_query_time默认10秒建议设为1秒的SQL开启慢查询日志SETGLOBALslow_query_logON;SETGLOBALlong_query_time1;分析工具mysqldumpslow、pt-query-digest优化步骤用EXPLAIN分析执行计划 → 检查是否用到索引 → 优化SQL语句或添加索引25. 如何优化大量数据插入的性能批量插入合并多条INSERT为一条每批不超过1000条关闭自动提交手动控制事务提交减少事务次数临时关闭索引插入前删除索引插入完成后重建调整参数innodb_flush_log_at_trx_commit0、bulk_insert_buffer_size使用LOAD DATA比INSERT快10倍以上26. 数据库性能优化的金字塔是什么从易到难收益从高到低架构优化读写分离、分库分表、缓存、搜索引擎SQL与索引优化优化慢SQL、合理设计索引MySQL配置优化缓冲池、连接数、日志参数硬件与OS优化SSD、RAID、文件系统、内核参数RAIDRedundant Array of Independent Disks中文独立磁盘冗余阵列早期也叫廉价磁盘冗余阵列将多块物理硬盘通过硬件 / 软件组合为一个逻辑磁盘核心解决两个问题提升读写性能数据冗余容错硬盘坏了不丢数据、不宕机是服务器、数据库存储的标准方案。七、其他高频题27. 什么是行锁和表锁表锁锁住整个表粒度大冲突概率高并发性能差MyISAM仅支持表锁行锁锁住具体的行粒度小冲突概率低并发性能好InnoDB支持行锁注意InnoDB的行锁是基于索引的没有索引的查询会升级为表锁28. 什么是乐观锁和悲观锁悲观锁假设一定会发生并发冲突操作前先加锁实现SELECT ... FOR UPDATE适用写多的场景乐观锁假设不会发生并发冲突提交时检查是否有冲突实现版本号机制UPDATE table SET ..., versionversion1 WHERE id1 AND versionold_version适用读多的场景29. 为什么不推荐用SELECT *增加IO开销读取不需要的字段尤其是大字段TEXT、BLOB无法使用覆盖索引必须回表查询增加网络传输量返回多余数据占用带宽降低代码可读性不知道具体查询了哪些字段30. 什么是主从延迟如何解决主从延迟定义主库写入数据后从库同步数据的时间差原因主库并发写入量大从库单线程重放binlog解决方法优化主库写入减少大事务升级从库硬件采用多线程复制MySQL 5.7支持读写分离时关键业务读主库分库分表分散压力MySQL binlog全称Binary Log二进制日志作用记录数据库所有修改类SQL增/删/改、DDL查询不记录。核心用途主从复制主库把binlog同步给从库实现数据同步数据恢复误删/误改后用binlog回滚找回数据三种格式statement记录SQL语句体积小可能有数据不一致row记录行数据变更精准默认推荐mixed混合前两种一句话总结MySQL的数据变更流水账用来做主从同步和数据恢复。
