基础概念1.MySQL存储引擎有哪些常用的InnoDB、MyISAM区别MySQL常见的存储引擎有InnoDB、MyISAM、Memory、Archive等。现在生产最常用的是InnoDB。InnoDB支持事务、行锁、MVCC、崩溃恢复、聚簇索引适合高并发和事务场景。MyISAM不支持事务不支持行锁主要是表级锁、崩溃恢复能力弱但结构简单2.InnoDB相比MyISAM优势在哪InnoDB的优势主要有支持事务满足ACID支持行级锁并发写能力更好支持崩溃恢复可靠性更高支持MVCC提高读写并发支持外键面试里可以直接说InnoDB更适合现代业务系统尤其是高并发、强一致、需要事务的场景3.什么是事务事务的四大特性ACID分别是什么事务是一组操作的集合要么全部成功要么全部失败ACID分别是A Atomicity 原子性事务内操作要么全部成功要么全部失败。主要靠undo logC Consistency 一致性事务执行前后数据从一个一致状态变成另一个一致状态I Isolation 隔离性多个事务并发执行时彼此影响受隔离级别控制D Durability 持久性事务提交后数据不会丢。主要靠redo log4.数据库三范式是什么日常开发一定要严格遵守吗第一范式字段不可再分保证原子性第二范式非主键字段必须完全依赖主键避免部分依赖第三范式非主键字段不能依赖其他非主键字段避免传递依赖日常开发不一定要严格遵守。范式能减少冗余提高一致性但有时为了查询性能会适当反范式比如冗余用户名、订单快照、统计字段。原则核心数据保持一致读多性能瓶颈场景可以有控制的冗余。5.执行一条SQL语句期间发生了哪些事情以更新语句为例客户端发送SQLServer层解析、优化、生成执行计划执行层调用InnoDBInnoDB找到数据页加载到Buffer Pool记录undo log便于回滚修改内存页产生脏页写redo log prepareServer层写binlogredo log commit后台线程择机把脏页刷盘查询语句主要涉及解析、优化、执行、走索引、回表、返回结果事务1.MySQL四大事务隔离级别分别是什么读未提交 Read Uncommitted允许读到别人未提交的数据读已提交 Read Committed只能读到其他事务已经提交的数据可重复读 Repeatable-Read同一个事务内多次读取读到事务启动那一刻的快照结果一致串性化 Serializable最高隔离级别串性执行并发低2.脏读、不可重复读、幻读分别是什么脏读读到了其他事务还没有提交的数据。如果对方回滚你读到的就是脏数据不可重复读同一个事务内两次读取同一行数据结果不一样。通常是其他事务提交了修改幻读同一个事务内两次范围查询别的事务插入或删除数据并提交前后返回行数不一样脏读读到未提交、不可重复读是读同一行变了幻读是结果集行数变了。3.四大隔离级别分别能解决哪些问题读未提交什么都解决不了读已提交解决脏读但可能不可重复读和幻读可重复读解决脏读、不可重复读。InnoDB通过MVCC和临键锁在很多场景下也解决幻读串性化基本都能解决但并发性能最低生产中MySQLInnoDB默认是可重复读4.InnoDB的默认级别是什么InnoDB的默认级别是可重复读它通过MVCC保证普通快照的可重复读通过next-key lock处理当前读下的幻读问题5.什么是幻读怎么解决幻读幻读指一个事务内两次范围查询第二次出现了第一次没有的记录像“幻影”一样。解决方式串行化隔离级别直接强约束并发InnoDB可重复读下普通快照读通过Read View避免幻读当前读通过next-key lock锁住范围阻止其他事务插入注意MVCC主要解决快照读的一致性临键锁主要解决当前读的幻读6.事务的实现原理MVCC是什么事务能力不是一个单独机制完成的而是多个机制配合原子性靠 undo log持久性靠 redo log隔离性靠锁和MVCC一致性由业务约束、数据库约束和上述机制共同保证MVCC是多版本并发控制。它让读操作可以读到某个时间点的数据版本而不是总被写操作阻塞通俗的说数据被修改后旧版本不会立即消失读事务可以根据规则找到自己应该看到的版本。7.可重复读隔离级别完成解决幻读了吗对于普通select快照读可重复读通过MVCC的Read View能保证同一事务内查询结果一致基本避免幻读对于select…for update、update、delete这类当前读InnoDB通过next-key lock锁范围防止其他事务插入从而避免幻读MVCC1.MVCC底层原理是什么MVCC全称Multi-Version Concurrency Control,多版本并发控制。InnoDB 每行记录都有隐藏字段配合undo log保持历史版本再通过Read View判断当前事务能看到哪个版本。它的核心目标是提高读写并发读不阻塞写写不阻塞普通读。2.什么是快照读当前读举例说明快照读读取的是某个时间点的数据版本不加锁。普通select通常是快照读当前读读取的是最新数据并且通常要加锁select * from user where id1 for update;update user set nametom where id1;delete from user where id1;快照读看历史版本当前读看最新版本3.隐藏字段DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID作用InnoDB行记录里有几个隐藏字段DB_TRX_ID最近一次修改这行记录的事务idDB_ROLL_PTR回滚指针指向undo log 中的旧版本DB_ROW_ID如果表没有主键和唯一非空索引InnoDB会生成隐藏行idMVCC主要依靠DB_TRX_ID、DB_ROLL_PTR找到可见版本。4.undo log日志作用、版本链是什么undo log记录数据被修改前的旧值用于事务回滚和给MVCC提供历史版本。当一行数据被多次修改时每次修改都会产生undo log记录之间通过回滚指针串起来就形成版本链。查询时如果当前版本对事务不可见InnoDB会沿着版本链往前找直到找到一个可见版本或者找不到。5.Read View视图四个字段作用、可见性规则Read View是MVCC判断版本是否可见的核心四个字段m_ids创建Read View时系统中活跃事务id列表min_trx_id活跃事务中最小idmax_trx_id下一个将要分配的事务idcreator_trx_id创建这个Read View的事务id可见性规则简化理解如果版本的事务id小于min_trx_id说明早就提高了可见如果版本的事务id大于等于max_trx_id说明是之后才出现的不可见如果事务id在m_ids里说明当时还没有提交不可见如果事务id不在m_ids里说明当时已经提交可见6.MVCC怎么实现可重复读、读已提交读已提交RC每次执行普通select都会生成新的Read View。所以同一事务里第二次查询能看到其他事务已经提交的数据可重复读RR事务中第一次普通select时生成Read View后续普通select复用这个Read View。所以同一事务里多次读取结果一致区别在于Read View的创建时机索引1.InnoDB索引结构为什么选B树不选二叉树、红黑树、B树数据库索引存在磁盘上核心目标是减少磁盘IO。二叉树和红黑树高度相对较高数据量大时查找层数多磁盘IO多B树每个节点既存key也存数据单页能放的key数量相对少树可能更高。B树非叶子节点只存key和指针叶子节点存完整数据或者主键单页能放更多key。树更矮IO更少。叶子节点还用链表连接范围查询更方便所以InnoDB选择B树是为了降低IO、提高范围查询效率2.B树和B树的区别B树的每个节点都可以存数据B树只有叶子节点存数据非叶子节点只做索引导航B树叶子节点之间有链表范围查询更快B树单个非叶子节点能存更多key树更矮磁盘IO更少3.聚簇索引和非聚簇索引二级索引区别聚簇索引的叶子节点存整行数据。在InnoDB中主键索引就是聚簇索引非聚簇索引也叫二级索引叶子节点存的是索引字段和主键值。通过二级索引查到主键后如果还需要其他字段就要再根据主键回到聚簇索引查整行这就是回表。4.主键索引、唯一索引、普通索引、联合索引区别主键索引唯一且不能为空一张表只能有一个主键唯一索引值不能重复但通常允许null具体行为和数据库规则有关普通索引没有唯一性限制只提升查询效率联合索引多个字段组合成一个索引比如a,b,c使用时要遵循最左前缀原则5.什么是回表查询怎么避免回表回表是指通过二级索引查到主键后在根据主键去聚簇索引查整行数据。假如有索引name:select age from user where nametom;如果age不在索引里就需要回表避免回表的方式是使用覆盖索引把查询需要的字段都放进索引creat index idx_name_age on user(name,age);6.覆盖索引是什么使用场景覆盖索引是指查询需要的字段都能从索引里拿到不需要回表例如联合索引name,ageselect name,age from user where nametom;这种情况下只查索引就够了覆盖索引适合高频查询、列表页、分页查询等场景可以减少回表IO7.最左前缀原则原理为什么要遵循联合索引按字段顺序排序比如a,b,c索引先按a排再按b排最后按c排所以查询必须从最左字段开始连续使用才能充分利用索引。可以走索引where a1 where a1 and b2 where a1 and b2 and c3不可以where b2 where c3因为缺少最左字段a索引整体顺序用不上8.索引下推原理和作用索引下推Index Condition Pushdown简称ICP是MySQL的一种优化没有索引下推时存储引擎根据索引找到记录后可能先回表再由Server层判断其他条件有索引下推时能在存储引擎层先用索引里的字段过滤一部分数据减少回表次数典型场景是联合索引中部分字段可以用于过滤但不完全用于定位。9.什么是索引失效哪些情况会导致索引失效索引失效是指SQL虽然有索引但优化器没有使用或者只能使用一部分。常见原因对索引列使用函数或表达式字段类型不一致导致隐式转换like %xxx前缀模糊联合索引不满足最左前缀使用or且部分条件没有索引范围查询后面的联合索引字段无法继续有序利用数据量太小或优化器判断全表扫描更划算优化时不要只看建没建索引要看explain里实际有没有用。10.模糊查询like %xxx、like xxx%哪个走索引like %xxx一般不能走普通B树索引因为开头不确定无法从索引树定位范围like xxx%可以走索引因为前缀确认B树可以按范围查询如果必须支持任意位置模糊搜索可以考虑全文索引、搜索引擎或者业务测倒排索引11.字段类型隐式转换为什么会导致索引失效如果字段类型和查询条件类型不一致MySQL可能对字段做隐式转换例如手机号字段是varchar却这样查where phone13456789001MySQL可能把phone转成数字比较相当于对索引列做函数处理索引就可能失效。正确写法where phone1345678900112.为什么不建议用select *原因查出不需要的字段增加网络和内存开销更容易回表无法利用覆盖索引表结构变更时结果字段不稳定大字段如text、blob会拖慢查询生产建议明确写出需要的字段13.联合索引创建顺序原则区分度高、长度小、经常查询联合索引字段顺序一般考虑经常用于查询条件的字段靠前区分度高的字段优先字段长度小的优先索引更紧凑等值查询字段通常放前面范围查询字段放后面还要兼顾order by、group by14.什么时候不适合建索引表数据量很小字段区分度很低比如性别、状态值很少字段很少用于查询条件写入非常频繁索引会增加维护成本大字段不适合直接建普通索引已有联合索引可以直接覆盖不需要重复建单列索引索引不是越多越好它能加快查询但会拖慢写入并占用磁盘空间锁1.InnoDB有哪些锁行锁、表锁、意向锁常见锁表锁锁整张表行锁锁某些记录粒度小并发度高意向锁表级锁用来表示事务接下来想锁某些行记录锁锁具体索引记录间隙锁锁两个索引记录之间的间隙临键锁记录锁间隙锁InnoDB的行锁是加在索引上的如果查询条件没有走索引可能导致锁范围变大2.行锁什么时候变表锁严格说InnoDB行锁不会真正升级为表锁但如果SQL没有走索引InnoDB可能扫描很多行并对大量记录加锁看起来像锁表常见原因where条件没有索引索引失效字段类型不一致导致隐式转换范围条件过大所以更新和删除时一定要确认条件走索引尤其是大表3.记录锁间隙锁临键锁分别是什么记录锁Record Lock锁住某一条索引记录间隙锁Gap Lock锁住索引记录之间的间隙不锁具体记录主要防止幻读临键锁Next-Key Lock记录锁间隙锁既锁记录也锁记录前的间隙例如索引里有10和20间隙锁可能锁住1020这个范围防止其他事务插入154.临键锁怎么解决幻读幻读是同一个事务内两次范围查询结果条数不一致。在可重复读隔离级别下InnoDB对范围查询加临键锁锁住已有记录和记录之间的间隙、这样其他事务就不能在这个范围里插入新记录所以临键锁通过“锁记录锁间隙”防止范围查询内新增数据从而解决当前读下的幻读问题5.什么是死锁产生条件、怎么排查和避免死锁死锁是多个事务互相等待对方持有的锁导致都无法继续产生条件:互斥持有并等待不可剥夺循环等待排查方式使用show engine innodb status查看最近一次死锁信息查看事务持有什么锁等待什么锁结合慢SQL、业务日志定位SQL顺序避免方式统一加锁顺序事务尽量短where条件走索引避免大范围更新必要时使用重试机制日志1.MySQL三大日志redo log、undo log、binlog各自作用redo log是InnoDB的重做日志主要保证事务的持久性。事务提交后即使数据库突然宕机也可以通过redo log把已经提交的数据恢复过来undo log是回滚日志主要保证事务原子性也用于MVCC。事务执行过程中会记录修改前的数据如果事务回滚就可以根据undo log恢复旧值binlog是MySQL Server层的二进制日志主要用于主从复制、数据恢复和审计。它记录的是数据库发生了哪些逻辑变更。redo log保证崩溃恢复偏物理undo log保证事务回滚和MVCC记录久版本binlog保证复制和归档偏逻辑2.redo log 为什么能保证事务崩溃恢复WAL机制是什么redo log 能保证事务崩溃恢复核心靠WAL机制也就是Write Ahead Logging先写日志再写数据页。InnoDB修改数据时不会每次都立刻把磁盘上的数据页改掉而是先修改内存中的Buffer Pool同时记录redo log。事务提交时只有redo log持久化成功就认为事务提交成功如果数据库宕机内存里的脏页可能还没有刷到磁盘但redo log已经在磁盘上了重启后InnoDB会根据redo log重放修改把数据恢复到提交后的状态3.binlog是什么statement、row、mixed格式区别binlog是MySQL Server层的二进制日志记录数据库变更常用于主从复制和数据恢复它有三种常见格式statement记录SQL语句。优点是日志量少缺点是某些SQL在主从执行结果可能不一致比如now()、uuid()不确定数据的更新row记录每一行数据的变化。优点是最准确主从一致性更好缺点是日志量可能很大mixed混合模式。MySQL会根据SQL是否安全自动选择statement或row生产环境更常用row因为复制更可靠也方便做数据订正和恢复4.redo log和binlog区别redo log和binlog经常一起问因为它们都记录修改但定位完全不同。redo log是InnoDB引擎层日志binlog是MySQL Server层日志redo log主要用于崩溃恢复binlog主要用于主从复制和数据恢复redo log是循环写空间固定会覆盖旧日志binlog是追加写一个文件写满后切换到另一个redo log记录偏物理变化比如某个页做了什么修改binlog记录偏逻辑变化比如执行了什么SQL或哪行变成什么样redo log管“宕机后自己怎么恢复”binlog管“别人怎么同步和回放”5.事务提交时redo log和binlog两阶段提交原理两阶段提交是为了保证redo log和binlog一致大致流程InnoDB写redo log状态prepareMySQL Server写binlogInnoDB把redo log改成commit状态事务提交涉及两个日志如果只写成功一个就宕机会出现主库和从库数据不一致恢复时会判断redo log有preparebinlog也完整提交事务redo log有preparebinlog不完整回滚事务这样可以保证主库崩溃恢复结果和binlog复制结果一致SQL优化1.一条SQL执行完整流程客户端发送SQL到MySQL Server连接器管理连接和权限解析器做词法、语法分析预处理器检查表、字段是否存在优化器选择执行计划比如用哪个索引、表连接顺序执行器调用存储引擎接口存储引擎读取或修改数据返回结果给客户端如果更新语句还会涉及undo log、redo log、log、binlog等日志2.explain执行计划每个字段含义explain用来查看SQL执行计划面试常问这些字段id查询执行顺序标识。id越大通常越先执行相同id从上往下执行select_type查询类型比如SIMPLE、PRIMARY、SUBQUERY、DERIVEDtype访问类型表示查表效率优化重点字段key实际使用的索引rows优化器预估要扫描的行数Extra额外信息比如Using index、Using where、Using filesort、Using temporary看explain时重点关注type、key、rows、Extra。3.type执行效率级别all、index、range、ref、eq_ref、const、systemtype表示MySQL怎么访问表常见效率从差到好大概是allindexrangerefeq_refconstsystemall:全表扫描通常最差index扫描整个索引树比全表扫描稍好但仍然扫很多range范围扫描比如between、、inref普通索引等值匹配可能匹配多行eq_ref唯一索引或主键关联查询每次最多匹配一行const主键或唯一索引等值查询结果最多一行system表只有一行是const的特殊情况实际优化目标一般是避免all尽量达到range、ref或更好4.怎么看慢查询日志慢查询日志用于记录执行时间超过阈值的SQL常用参数slow_query_log是否开启慢查询日志long_query_time超过多少秒算慢SQLslow_query_log_file慢查询文件路径排查时重点看SQL原文执行耗时扫描行数返回行数是否走索引常用分析工具有mysqldumpslow和pt-query-digest5.慢查询优化整体思路用慢查询日志定位问题SQL用explain看执行计划判断是否走了合适索引检查是否有回表、Using filesort、Using temporary优化SQL写法减少扫描行数必要时调整索引、拆表、缓存或改业务方案核心原则少扫行、少回表、少排序、少临时表6.limit分页深偏移量怎么优化例如limit 100000010limit 100000010慢是因为MySQL需要先扫描并丢弃前1000000行再返回10行常见优化方式第一种基于上一页最大id做游标分页select * from user where id1000000 order by id limit 10;第二种先用覆盖索引查出id再回表select u.* from user u join( select id from user order by id liimit 1000000,10 ) t on u.idt.id;第三种产品层面避免跳到特别深的页比如搜索引擎通常只展示前几十页。7.order by排序原理什么时候Using filesort怎么优化order by如果能直接利用索引顺序就不需要额外排序如果不能利用索引排序MySQL会使用filesort。这里的filesort不一定真的落磁盘它表示额外排序算法数据大时可能用临时文件常见触发原因排序字段没有合适索引联合索引顺序不符合最左前缀排序方向混乱索引无法完全利用where条件和order by字段不匹配优化方式给whereorder by建合适联合索引尽量使用覆盖索引控制返回数据量避免对排序字段使用函数或表达式8.group by原理与优化思路group by用于分组聚合。MySQL执行时通常需要按分组字段聚集数据可能用索引也可能用临时表和排序优化思路给分组字段建立索引where先过滤减少参与分组的数据量只查询必要字段避免大结果集分组能在业务或离线任务预聚合的不要每次实时算大表如果explain里出现Using temporary/Using filesort。说明可能存在额外临时表和排序成本9.join连接原理内连接、左连接、右连接join本质是把多张表按条件关联起来内连接inner join只返回两边都匹配的数据左连接left join返回左表全部数据右表匹配不到时右表字段为null右连接right join返回右表全部数据左表匹配不到时左表字段为null开发中更常用内连接和左连接。右连接通常可以改写成左连接保持阅读习惯统一10.大表join怎么优化大表join优化重点是减少驱动表数据量并让被驱动表能走索引常见做法小表驱动大表join字段建立索引类型保持一致先where过滤再join只查需要字段避免select *大分页、大排序、大分组尽量拆分复杂场景可以用冗余字段、宽表、缓存、离线计算让参与join的数据尽量少让匹配过程尽量走索引总结redo log保证崩溃恢复undo log支持回滚和MVCCbinlog用于复制和归档WAL是先写日志再写数据页保证宕机后能恢复两阶段提交解决redo log和binlog一致性问题explain重点看type、key、rows、Extra慢SQL优化核心是少扫行、少回表、少排序、少临时表InnoDB默认级别是可重复读MVCC依赖隐藏字段、undo log版本链和Read View快照读读历史版本当前读读最新版本并加锁InnoDB索引用B树是为了降低IO和优化范围查询联合索引要遵循最左前缀原则like xxx%通常可走索引like %xxx通常不走普通索引日志redo、undo、binlog分别解决恢复、回滚、复制SQL优化先定位慢SQL再看执行计划最后减少扫描和回表锁理解行锁、间隙锁、临键锁以及死锁排查事务抓住ACID、隔离级别、脏读、不可重复读、幻读MVCC抓住隐藏字段、undo版本链、Read View索引抓住B树、聚簇索引、回表、覆盖索引、最左前缀 热门八股-MySQL
基础概念1.MySQL存储引擎有哪些常用的InnoDB、MyISAM区别MySQL常见的存储引擎有InnoDB、MyISAM、Memory、Archive等。现在生产最常用的是InnoDB。InnoDB支持事务、行锁、MVCC、崩溃恢复、聚簇索引适合高并发和事务场景。MyISAM不支持事务不支持行锁主要是表级锁、崩溃恢复能力弱但结构简单2.InnoDB相比MyISAM优势在哪InnoDB的优势主要有支持事务满足ACID支持行级锁并发写能力更好支持崩溃恢复可靠性更高支持MVCC提高读写并发支持外键面试里可以直接说InnoDB更适合现代业务系统尤其是高并发、强一致、需要事务的场景3.什么是事务事务的四大特性ACID分别是什么事务是一组操作的集合要么全部成功要么全部失败ACID分别是A Atomicity 原子性事务内操作要么全部成功要么全部失败。主要靠undo logC Consistency 一致性事务执行前后数据从一个一致状态变成另一个一致状态I Isolation 隔离性多个事务并发执行时彼此影响受隔离级别控制D Durability 持久性事务提交后数据不会丢。主要靠redo log4.数据库三范式是什么日常开发一定要严格遵守吗第一范式字段不可再分保证原子性第二范式非主键字段必须完全依赖主键避免部分依赖第三范式非主键字段不能依赖其他非主键字段避免传递依赖日常开发不一定要严格遵守。范式能减少冗余提高一致性但有时为了查询性能会适当反范式比如冗余用户名、订单快照、统计字段。原则核心数据保持一致读多性能瓶颈场景可以有控制的冗余。5.执行一条SQL语句期间发生了哪些事情以更新语句为例客户端发送SQLServer层解析、优化、生成执行计划执行层调用InnoDBInnoDB找到数据页加载到Buffer Pool记录undo log便于回滚修改内存页产生脏页写redo log prepareServer层写binlogredo log commit后台线程择机把脏页刷盘查询语句主要涉及解析、优化、执行、走索引、回表、返回结果事务1.MySQL四大事务隔离级别分别是什么读未提交 Read Uncommitted允许读到别人未提交的数据读已提交 Read Committed只能读到其他事务已经提交的数据可重复读 Repeatable-Read同一个事务内多次读取读到事务启动那一刻的快照结果一致串性化 Serializable最高隔离级别串性执行并发低2.脏读、不可重复读、幻读分别是什么脏读读到了其他事务还没有提交的数据。如果对方回滚你读到的就是脏数据不可重复读同一个事务内两次读取同一行数据结果不一样。通常是其他事务提交了修改幻读同一个事务内两次范围查询别的事务插入或删除数据并提交前后返回行数不一样脏读读到未提交、不可重复读是读同一行变了幻读是结果集行数变了。3.四大隔离级别分别能解决哪些问题读未提交什么都解决不了读已提交解决脏读但可能不可重复读和幻读可重复读解决脏读、不可重复读。InnoDB通过MVCC和临键锁在很多场景下也解决幻读串性化基本都能解决但并发性能最低生产中MySQLInnoDB默认是可重复读4.InnoDB的默认级别是什么InnoDB的默认级别是可重复读它通过MVCC保证普通快照的可重复读通过next-key lock处理当前读下的幻读问题5.什么是幻读怎么解决幻读幻读指一个事务内两次范围查询第二次出现了第一次没有的记录像“幻影”一样。解决方式串行化隔离级别直接强约束并发InnoDB可重复读下普通快照读通过Read View避免幻读当前读通过next-key lock锁住范围阻止其他事务插入注意MVCC主要解决快照读的一致性临键锁主要解决当前读的幻读6.事务的实现原理MVCC是什么事务能力不是一个单独机制完成的而是多个机制配合原子性靠 undo log持久性靠 redo log隔离性靠锁和MVCC一致性由业务约束、数据库约束和上述机制共同保证MVCC是多版本并发控制。它让读操作可以读到某个时间点的数据版本而不是总被写操作阻塞通俗的说数据被修改后旧版本不会立即消失读事务可以根据规则找到自己应该看到的版本。7.可重复读隔离级别完成解决幻读了吗对于普通select快照读可重复读通过MVCC的Read View能保证同一事务内查询结果一致基本避免幻读对于select…for update、update、delete这类当前读InnoDB通过next-key lock锁范围防止其他事务插入从而避免幻读MVCC1.MVCC底层原理是什么MVCC全称Multi-Version Concurrency Control,多版本并发控制。InnoDB 每行记录都有隐藏字段配合undo log保持历史版本再通过Read View判断当前事务能看到哪个版本。它的核心目标是提高读写并发读不阻塞写写不阻塞普通读。2.什么是快照读当前读举例说明快照读读取的是某个时间点的数据版本不加锁。普通select通常是快照读当前读读取的是最新数据并且通常要加锁select * from user where id1 for update;update user set nametom where id1;delete from user where id1;快照读看历史版本当前读看最新版本3.隐藏字段DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID作用InnoDB行记录里有几个隐藏字段DB_TRX_ID最近一次修改这行记录的事务idDB_ROLL_PTR回滚指针指向undo log 中的旧版本DB_ROW_ID如果表没有主键和唯一非空索引InnoDB会生成隐藏行idMVCC主要依靠DB_TRX_ID、DB_ROLL_PTR找到可见版本。4.undo log日志作用、版本链是什么undo log记录数据被修改前的旧值用于事务回滚和给MVCC提供历史版本。当一行数据被多次修改时每次修改都会产生undo log记录之间通过回滚指针串起来就形成版本链。查询时如果当前版本对事务不可见InnoDB会沿着版本链往前找直到找到一个可见版本或者找不到。5.Read View视图四个字段作用、可见性规则Read View是MVCC判断版本是否可见的核心四个字段m_ids创建Read View时系统中活跃事务id列表min_trx_id活跃事务中最小idmax_trx_id下一个将要分配的事务idcreator_trx_id创建这个Read View的事务id可见性规则简化理解如果版本的事务id小于min_trx_id说明早就提高了可见如果版本的事务id大于等于max_trx_id说明是之后才出现的不可见如果事务id在m_ids里说明当时还没有提交不可见如果事务id不在m_ids里说明当时已经提交可见6.MVCC怎么实现可重复读、读已提交读已提交RC每次执行普通select都会生成新的Read View。所以同一事务里第二次查询能看到其他事务已经提交的数据可重复读RR事务中第一次普通select时生成Read View后续普通select复用这个Read View。所以同一事务里多次读取结果一致区别在于Read View的创建时机索引1.InnoDB索引结构为什么选B树不选二叉树、红黑树、B树数据库索引存在磁盘上核心目标是减少磁盘IO。二叉树和红黑树高度相对较高数据量大时查找层数多磁盘IO多B树每个节点既存key也存数据单页能放的key数量相对少树可能更高。B树非叶子节点只存key和指针叶子节点存完整数据或者主键单页能放更多key。树更矮IO更少。叶子节点还用链表连接范围查询更方便所以InnoDB选择B树是为了降低IO、提高范围查询效率2.B树和B树的区别B树的每个节点都可以存数据B树只有叶子节点存数据非叶子节点只做索引导航B树叶子节点之间有链表范围查询更快B树单个非叶子节点能存更多key树更矮磁盘IO更少3.聚簇索引和非聚簇索引二级索引区别聚簇索引的叶子节点存整行数据。在InnoDB中主键索引就是聚簇索引非聚簇索引也叫二级索引叶子节点存的是索引字段和主键值。通过二级索引查到主键后如果还需要其他字段就要再根据主键回到聚簇索引查整行这就是回表。4.主键索引、唯一索引、普通索引、联合索引区别主键索引唯一且不能为空一张表只能有一个主键唯一索引值不能重复但通常允许null具体行为和数据库规则有关普通索引没有唯一性限制只提升查询效率联合索引多个字段组合成一个索引比如a,b,c使用时要遵循最左前缀原则5.什么是回表查询怎么避免回表回表是指通过二级索引查到主键后在根据主键去聚簇索引查整行数据。假如有索引name:select age from user where nametom;如果age不在索引里就需要回表避免回表的方式是使用覆盖索引把查询需要的字段都放进索引creat index idx_name_age on user(name,age);6.覆盖索引是什么使用场景覆盖索引是指查询需要的字段都能从索引里拿到不需要回表例如联合索引name,ageselect name,age from user where nametom;这种情况下只查索引就够了覆盖索引适合高频查询、列表页、分页查询等场景可以减少回表IO7.最左前缀原则原理为什么要遵循联合索引按字段顺序排序比如a,b,c索引先按a排再按b排最后按c排所以查询必须从最左字段开始连续使用才能充分利用索引。可以走索引where a1 where a1 and b2 where a1 and b2 and c3不可以where b2 where c3因为缺少最左字段a索引整体顺序用不上8.索引下推原理和作用索引下推Index Condition Pushdown简称ICP是MySQL的一种优化没有索引下推时存储引擎根据索引找到记录后可能先回表再由Server层判断其他条件有索引下推时能在存储引擎层先用索引里的字段过滤一部分数据减少回表次数典型场景是联合索引中部分字段可以用于过滤但不完全用于定位。9.什么是索引失效哪些情况会导致索引失效索引失效是指SQL虽然有索引但优化器没有使用或者只能使用一部分。常见原因对索引列使用函数或表达式字段类型不一致导致隐式转换like %xxx前缀模糊联合索引不满足最左前缀使用or且部分条件没有索引范围查询后面的联合索引字段无法继续有序利用数据量太小或优化器判断全表扫描更划算优化时不要只看建没建索引要看explain里实际有没有用。10.模糊查询like %xxx、like xxx%哪个走索引like %xxx一般不能走普通B树索引因为开头不确定无法从索引树定位范围like xxx%可以走索引因为前缀确认B树可以按范围查询如果必须支持任意位置模糊搜索可以考虑全文索引、搜索引擎或者业务测倒排索引11.字段类型隐式转换为什么会导致索引失效如果字段类型和查询条件类型不一致MySQL可能对字段做隐式转换例如手机号字段是varchar却这样查where phone13456789001MySQL可能把phone转成数字比较相当于对索引列做函数处理索引就可能失效。正确写法where phone1345678900112.为什么不建议用select *原因查出不需要的字段增加网络和内存开销更容易回表无法利用覆盖索引表结构变更时结果字段不稳定大字段如text、blob会拖慢查询生产建议明确写出需要的字段13.联合索引创建顺序原则区分度高、长度小、经常查询联合索引字段顺序一般考虑经常用于查询条件的字段靠前区分度高的字段优先字段长度小的优先索引更紧凑等值查询字段通常放前面范围查询字段放后面还要兼顾order by、group by14.什么时候不适合建索引表数据量很小字段区分度很低比如性别、状态值很少字段很少用于查询条件写入非常频繁索引会增加维护成本大字段不适合直接建普通索引已有联合索引可以直接覆盖不需要重复建单列索引索引不是越多越好它能加快查询但会拖慢写入并占用磁盘空间锁1.InnoDB有哪些锁行锁、表锁、意向锁常见锁表锁锁整张表行锁锁某些记录粒度小并发度高意向锁表级锁用来表示事务接下来想锁某些行记录锁锁具体索引记录间隙锁锁两个索引记录之间的间隙临键锁记录锁间隙锁InnoDB的行锁是加在索引上的如果查询条件没有走索引可能导致锁范围变大2.行锁什么时候变表锁严格说InnoDB行锁不会真正升级为表锁但如果SQL没有走索引InnoDB可能扫描很多行并对大量记录加锁看起来像锁表常见原因where条件没有索引索引失效字段类型不一致导致隐式转换范围条件过大所以更新和删除时一定要确认条件走索引尤其是大表3.记录锁间隙锁临键锁分别是什么记录锁Record Lock锁住某一条索引记录间隙锁Gap Lock锁住索引记录之间的间隙不锁具体记录主要防止幻读临键锁Next-Key Lock记录锁间隙锁既锁记录也锁记录前的间隙例如索引里有10和20间隙锁可能锁住1020这个范围防止其他事务插入154.临键锁怎么解决幻读幻读是同一个事务内两次范围查询结果条数不一致。在可重复读隔离级别下InnoDB对范围查询加临键锁锁住已有记录和记录之间的间隙、这样其他事务就不能在这个范围里插入新记录所以临键锁通过“锁记录锁间隙”防止范围查询内新增数据从而解决当前读下的幻读问题5.什么是死锁产生条件、怎么排查和避免死锁死锁是多个事务互相等待对方持有的锁导致都无法继续产生条件:互斥持有并等待不可剥夺循环等待排查方式使用show engine innodb status查看最近一次死锁信息查看事务持有什么锁等待什么锁结合慢SQL、业务日志定位SQL顺序避免方式统一加锁顺序事务尽量短where条件走索引避免大范围更新必要时使用重试机制日志1.MySQL三大日志redo log、undo log、binlog各自作用redo log是InnoDB的重做日志主要保证事务的持久性。事务提交后即使数据库突然宕机也可以通过redo log把已经提交的数据恢复过来undo log是回滚日志主要保证事务原子性也用于MVCC。事务执行过程中会记录修改前的数据如果事务回滚就可以根据undo log恢复旧值binlog是MySQL Server层的二进制日志主要用于主从复制、数据恢复和审计。它记录的是数据库发生了哪些逻辑变更。redo log保证崩溃恢复偏物理undo log保证事务回滚和MVCC记录久版本binlog保证复制和归档偏逻辑2.redo log 为什么能保证事务崩溃恢复WAL机制是什么redo log 能保证事务崩溃恢复核心靠WAL机制也就是Write Ahead Logging先写日志再写数据页。InnoDB修改数据时不会每次都立刻把磁盘上的数据页改掉而是先修改内存中的Buffer Pool同时记录redo log。事务提交时只有redo log持久化成功就认为事务提交成功如果数据库宕机内存里的脏页可能还没有刷到磁盘但redo log已经在磁盘上了重启后InnoDB会根据redo log重放修改把数据恢复到提交后的状态3.binlog是什么statement、row、mixed格式区别binlog是MySQL Server层的二进制日志记录数据库变更常用于主从复制和数据恢复它有三种常见格式statement记录SQL语句。优点是日志量少缺点是某些SQL在主从执行结果可能不一致比如now()、uuid()不确定数据的更新row记录每一行数据的变化。优点是最准确主从一致性更好缺点是日志量可能很大mixed混合模式。MySQL会根据SQL是否安全自动选择statement或row生产环境更常用row因为复制更可靠也方便做数据订正和恢复4.redo log和binlog区别redo log和binlog经常一起问因为它们都记录修改但定位完全不同。redo log是InnoDB引擎层日志binlog是MySQL Server层日志redo log主要用于崩溃恢复binlog主要用于主从复制和数据恢复redo log是循环写空间固定会覆盖旧日志binlog是追加写一个文件写满后切换到另一个redo log记录偏物理变化比如某个页做了什么修改binlog记录偏逻辑变化比如执行了什么SQL或哪行变成什么样redo log管“宕机后自己怎么恢复”binlog管“别人怎么同步和回放”5.事务提交时redo log和binlog两阶段提交原理两阶段提交是为了保证redo log和binlog一致大致流程InnoDB写redo log状态prepareMySQL Server写binlogInnoDB把redo log改成commit状态事务提交涉及两个日志如果只写成功一个就宕机会出现主库和从库数据不一致恢复时会判断redo log有preparebinlog也完整提交事务redo log有preparebinlog不完整回滚事务这样可以保证主库崩溃恢复结果和binlog复制结果一致SQL优化1.一条SQL执行完整流程客户端发送SQL到MySQL Server连接器管理连接和权限解析器做词法、语法分析预处理器检查表、字段是否存在优化器选择执行计划比如用哪个索引、表连接顺序执行器调用存储引擎接口存储引擎读取或修改数据返回结果给客户端如果更新语句还会涉及undo log、redo log、log、binlog等日志2.explain执行计划每个字段含义explain用来查看SQL执行计划面试常问这些字段id查询执行顺序标识。id越大通常越先执行相同id从上往下执行select_type查询类型比如SIMPLE、PRIMARY、SUBQUERY、DERIVEDtype访问类型表示查表效率优化重点字段key实际使用的索引rows优化器预估要扫描的行数Extra额外信息比如Using index、Using where、Using filesort、Using temporary看explain时重点关注type、key、rows、Extra。3.type执行效率级别all、index、range、ref、eq_ref、const、systemtype表示MySQL怎么访问表常见效率从差到好大概是allindexrangerefeq_refconstsystemall:全表扫描通常最差index扫描整个索引树比全表扫描稍好但仍然扫很多range范围扫描比如between、、inref普通索引等值匹配可能匹配多行eq_ref唯一索引或主键关联查询每次最多匹配一行const主键或唯一索引等值查询结果最多一行system表只有一行是const的特殊情况实际优化目标一般是避免all尽量达到range、ref或更好4.怎么看慢查询日志慢查询日志用于记录执行时间超过阈值的SQL常用参数slow_query_log是否开启慢查询日志long_query_time超过多少秒算慢SQLslow_query_log_file慢查询文件路径排查时重点看SQL原文执行耗时扫描行数返回行数是否走索引常用分析工具有mysqldumpslow和pt-query-digest5.慢查询优化整体思路用慢查询日志定位问题SQL用explain看执行计划判断是否走了合适索引检查是否有回表、Using filesort、Using temporary优化SQL写法减少扫描行数必要时调整索引、拆表、缓存或改业务方案核心原则少扫行、少回表、少排序、少临时表6.limit分页深偏移量怎么优化例如limit 100000010limit 100000010慢是因为MySQL需要先扫描并丢弃前1000000行再返回10行常见优化方式第一种基于上一页最大id做游标分页select * from user where id1000000 order by id limit 10;第二种先用覆盖索引查出id再回表select u.* from user u join( select id from user order by id liimit 1000000,10 ) t on u.idt.id;第三种产品层面避免跳到特别深的页比如搜索引擎通常只展示前几十页。7.order by排序原理什么时候Using filesort怎么优化order by如果能直接利用索引顺序就不需要额外排序如果不能利用索引排序MySQL会使用filesort。这里的filesort不一定真的落磁盘它表示额外排序算法数据大时可能用临时文件常见触发原因排序字段没有合适索引联合索引顺序不符合最左前缀排序方向混乱索引无法完全利用where条件和order by字段不匹配优化方式给whereorder by建合适联合索引尽量使用覆盖索引控制返回数据量避免对排序字段使用函数或表达式8.group by原理与优化思路group by用于分组聚合。MySQL执行时通常需要按分组字段聚集数据可能用索引也可能用临时表和排序优化思路给分组字段建立索引where先过滤减少参与分组的数据量只查询必要字段避免大结果集分组能在业务或离线任务预聚合的不要每次实时算大表如果explain里出现Using temporary/Using filesort。说明可能存在额外临时表和排序成本9.join连接原理内连接、左连接、右连接join本质是把多张表按条件关联起来内连接inner join只返回两边都匹配的数据左连接left join返回左表全部数据右表匹配不到时右表字段为null右连接right join返回右表全部数据左表匹配不到时左表字段为null开发中更常用内连接和左连接。右连接通常可以改写成左连接保持阅读习惯统一10.大表join怎么优化大表join优化重点是减少驱动表数据量并让被驱动表能走索引常见做法小表驱动大表join字段建立索引类型保持一致先where过滤再join只查需要字段避免select *大分页、大排序、大分组尽量拆分复杂场景可以用冗余字段、宽表、缓存、离线计算让参与join的数据尽量少让匹配过程尽量走索引总结redo log保证崩溃恢复undo log支持回滚和MVCCbinlog用于复制和归档WAL是先写日志再写数据页保证宕机后能恢复两阶段提交解决redo log和binlog一致性问题explain重点看type、key、rows、Extra慢SQL优化核心是少扫行、少回表、少排序、少临时表InnoDB默认级别是可重复读MVCC依赖隐藏字段、undo log版本链和Read View快照读读历史版本当前读读最新版本并加锁InnoDB索引用B树是为了降低IO和优化范围查询联合索引要遵循最左前缀原则like xxx%通常可走索引like %xxx通常不走普通索引日志redo、undo、binlog分别解决恢复、回滚、复制SQL优化先定位慢SQL再看执行计划最后减少扫描和回表锁理解行锁、间隙锁、临键锁以及死锁排查事务抓住ACID、隔离级别、脏读、不可重复读、幻读MVCC抓住隐藏字段、undo版本链、Read View索引抓住B树、聚簇索引、回表、覆盖索引、最左前缀