尧图网站建设 
目录Day 1SQL基础1. 什么是SQL(Structured Query Language)2. SQL语句有哪些分类区分为什么update/delete一定要加where3. 增删改查CRUD什么是CRUD4. SELECT查询执行顺序重点5. Where和Having区别面试常问6. GROUP BY是什么⭐⭐⭐7. JOIN是什么⭐⭐⭐⭐Day 2MySQL索引 ⭐1. 什么是索引必背2. 为什么需要索引3. 索引的底层结构是什么⭐⭐⭐⭐为什么使用BTree4. 为什么不用二叉树为什么不用Hash索引⭐⭐⭐⭐5. BTree是什么⭐⭐⭐⭐⭐6. 索引的优缺点 ⭐⭐⭐⭐7. 什么字段适合建立索引⭐⭐⭐⭐什么字段不适合8、为什么BTree特别适合范围查询9、那BTree和B树相比有什么区别为什么数据库更喜欢BTreeDay 3MySQL 索引进阶 1. 什么是聚簇索引2. 什么是二级索引3. 什么是回表什么是覆盖索引高频4. 什么是联合索引什么是最左匹配原则5. 哪些情况可能导致索引失效高频6. 为什么不建议 select * 7、为什么①通常可以正常利用索引而②对索引列使用函数后可能无法正常利用这个索引Day 4MySQL 事务篇 1. 什么是事务为什么需要事务3. 事务MySQL中怎么使用4. 什么是 ACID5. 并发事务会出现什么问题① 脏读 ②不可重复读 ③幻读 6. MySQL事务隔离级别 7. MySQL InnoDB 默认隔离级别 8. 四种隔离级别解决什么问题Day 5MVCC MySQL 锁 ️1、什么是 MVCC2 、为什么需要 MVCC3、undo log的两大作用4、Read View为什么 InnoDB 的 Repeatable Read可重复读能够解决不可重复读问题请从undo log、MVCC、版本链和 Read View 的角度解释。5、什么是快照读和当前读6、什么是共享锁和排他锁表锁 vs 行锁记录锁Record Lockvs 间隙锁Gap Lock为啥临键锁 Next-Key Lock 能帮助防止幻读7、InnoDB 在 RR 隔离级别下是怎么处理幻读问题的8、为什么说 InnoDB 的查询条件如果没有合适的索引可能导致“锁范围扩大”甚至表现得像锁表什么情况下行锁可能退化成类似“锁表”的效果快照读 当前读9、为什么 SELECT ... FOR UPDATE 不能像普通 SELECT 一样只读取历史快照而要读取当前最新的数据并加锁事务 A COMMIT 之后事务 B 会发生什么如果事务 A 还是持有 X Lock但事务 B 执行select事务 B 会不会被阻塞为什么Day6SQL 优化与 EXPLAIN 第一题 什么是慢 SQL第二题 EXPLAIN 是干什么的第三题 为什么不能看到 key ! NULL、SQL 使用了索引就直接判断“这条 SQL 性能很好”为什么 Extra Using index 通常是一个比较好的信号第四题SQL优化---深分页Day7InnoDB 底层 MySQL 日志体系1、什么是 Buffer PoolInnoDB 为什么需要 Buffer Pool2、是不是每执行一次 UPDATEMySQL 都要立刻把修改后的数据写回磁盘3、既然有了 redo log为什么还需要 binlogredo log 和 binlog 有什么区别4、为什么 MySQL 需要两阶段提交5、redo log 为什么叫“循环写” binlog叫“追加写”6、一个update的背后流程图Day 1SQL基础1. 什么是SQL(Structured Query Language)回答是一种用于操作关系型数据库的语言。主要用于对数据库中的数据进行定义、操作、查询、控制。比如MySQL、Oracle/ˈɔːrəkəl/、PostgreSQL波斯格瑞 等关系型数据库都支持SQL2. SQL语句有哪些分类MySQL中常见分为① DDLData Definition Language是数据定义语言主要用来 定义数据库对象结构比如数据库、表、字段等常见操作包括create、alter、drop、truncate。create用于创建数据库对象比如创建表alter用于修改数据库对象结构如增加字段、修改字段类型、删除字段等。区分drop删除整个表对象表结构和数据都会被删除。truncate清空表中的所有数据但是保留表的结构和字段之后仍然可以继续使用这个表。② DMLData Manipulation Language是数据操作语言主要用于操作表中的数据包括insert、update、delete为什么update/delete一定要加whereupdate和delete加where条件是为了指定操作的数据范围。如果不加where条件会默认操作整张表的数据可能导致大量数据被错误修改或删除③ DQLData Query Language是数据查询语言最常用的命令是select用于 查询指定字段、筛选数据以及 进行排序、分组统计等操作④ DCLData Control Language是数据控制语言。主要用于管理数据库用户的权限常见命令有 grant用于授权revoke用于撤销权限3. 增删改查CRUD英文中文SQLCreate创建INSERTRead读取SELECTUpdate更新UPDATEDelete删除DELETE什么是CRUDCRUD是数据库中最基本的数据操作包括创建、查询、修改、删除对应SQL中的 insert、select、update、delete。4. SELECT查询执行顺序重点先找表from再过滤where再分组group by再过滤组having再选字段select再排序order by再分页limitselect name, count(*) -- 选择最终要返回的字段并使用count(*)统计数量 from user -- 从user表中获取数据确定数据来源 where age 18 -- 过滤原始数据只保留年龄大于18的数据 group by name -- 根据name字段进行分组 having count(*) 5 -- 对分组后的结果进行过滤只保留数量大于5的组 order by count(*) desc -- 按统计数量进行降序排序从大到小 limit 10; -- 最后只返回10条数据分页5. Where和Having区别面试常问where 和 having都是用于过滤数据区别在于where过滤的是原始数据在group by之前执行having过滤的是分组后的结果在group by之后执行。where不能使用聚合函数而having可以结合count、max、min等聚合函数。6. GROUP BY是什么⭐⭐⭐对查询结果进行分组统计例如统计每个城市用户数量。user表namecity张三上海李四上海王五北京分组结果citycount上海2北京1GROUP BY通常和聚合函数一起使用例如count()、sum()、avg()、max()、min()7. JOIN是什么⭐⭐⭐⭐用于多张表之间进行关联查询。常见JOIN类型有inner join内连接只返回两个表都有的数据left join左连接返回左表全部数据以及右表匹配数据Day 2MySQL索引 ⭐1. 什么是索引必背索引是一种帮助数据库快速查询数据的数据结构主要用于提高数据库的查询效率减少全表扫描2. 为什么需要索引当数据量较大时如果没有索引数据库需要进行全表扫描查询效率较低使用索引可以快速定位数据提高查询效率。3. 索引的底层结构是什么⭐⭐⭐⭐MySQL InnoDB存储引擎默认使用BTree作为索引结构。为什么使用BTree查询效率稳定、支持范围查询、树高度较低4. 为什么不用二叉树为什么不用Hash索引⭐⭐⭐⭐普通二叉树如果使用普通二叉树、数据在有序的情况下、可能退化成链表。时间复杂度变成O(n)、效率下降Hash索引优点等值查询快。缺点①不支持范围查询②不支持排序5. BTree是什么⭐⭐⭐⭐⭐BTree是一种多叉平衡搜索树MySQL利用它来组织索引数据。特点① 多叉一个节点可以存储多个关键字、因此树的高度比较低可以减少磁盘I/O次数② 所有数据存储在叶子节点③ 叶子节点通过链表连接方便范围查询6. 索引的优缺点 ⭐⭐⭐⭐优点① 提高查询效率减少扫描数据。② 提高排序效率缺点① 占用额外空间因为索引本身需要存储。② 降低增删改效率因为修改数据时需要同时维护索引7. 什么字段适合建立索引⭐⭐⭐⭐① 经常作为查询条件② 经常排序③ 经常用于JOIN查询什么字段不适合① 区分度比较低、重复值很多的字段② 经常修改的字段因为维护成本高。8、为什么BTree特别适合范围查询做范围查询时可以先通过BTree快速定位到范围起点比如100然后从这个叶子节点开始沿着链表顺序向后扫描直到200为止所以范围查询效率比较高9、那BTree和B树相比有什么区别为什么数据库更喜欢BTreeB树的非叶子节点也可以存储数据而B树的数据集中在叶子节点非叶子节点主要用于索引。这样B树的非叶子节点可以容纳更多索引项使树的高度更低同时B树的叶子节点有序连接因此更适合范围查询Day 3MySQL 索引进阶 1. 什么是聚簇索引①聚簇索引是数据和索引存储在一起的索引。②比如在InnoDB中聚簇索引的叶子节点 存储完整的数据行通常主键索引就是聚簇索引2. 什么是二级索引又叫非聚簇索引 / Secondary Index二级索引的叶子节点一般不存完整数据行而是存索引字段 主键值。3. 什么是回表什么是覆盖索引高频回表是指通过二级索引查询时其中没有查询所需要的全部字段就需要先通过二级索引找到主键值再根据主键到聚簇索引中查询完整数据这个过程叫回表。查询所需要的字段全部能够从索引中直接获得不需要再回表查询这种情况叫覆盖索引4. 什么是联合索引什么是最左匹配原则联合索引同时基于多个字段建立的索引查询条件要尽量从联合索引最左边的字段开始匹配。遇到范围查询后范围字段可以使用索引但其后的字段通常不能继续用于缩小索引扫描范围5. 哪些情况可能导致索引失效高频① 违反联合索引最左匹配② 对索引列进行函数操作③ 对索引列进行计算④ LIKE 以%开头6. 为什么不建议select *①可能增加不必要的数据读取②并可能导致无法利用覆盖索引③增加回表查询成本7、为什么①通常可以正常利用索引而②对索引列使用函数后可能无法正常利用这个索引① where name Felix; ② where lower(name) felix;索引存的是原始值的有序结构但查询条件用的是计算后的值因此数据库可能无法直接根据原索引定位数据后%开头确定知道从哪找。前%开头不确定不知道从哪找。where name Felix and age 20 and city 上海;最左匹配①中间缺字段会断②遇到范围查询范围列能用但后面的列不能继续用于缩小联合索引的扫描范围。Day 4MySQL 事务篇 1. 什么是事务为什么需要事务事务是一组数据库操作的集合这些操作要么全部成功要么全部失败用来保证数据的一致性和可靠性防止执行过程中出现异常导致数据处于错误状态3. 事务MySQL中怎么使用start transaction 开始事务 commit 提交事务 rollback 回滚事务4. 什么是 ACID事务有四大特性原子性Atomicity一个事务中的操作要么全部成功要么全部失败。一致性Consistency事务执行前后数据都应该保持合法、一致的状态总金额隔离性Isolation多个事务并发执行时事务之间应该尽量互不干扰持久性Durability事务一旦commit提交成功修改的数据就应该被持久保存。5. 并发事务会出现什么问题① 脏读 一个事务读到了另一个事务还没有提交的数据。②不可重复读 同一个事务中对同一条数据读取两次结果不一样。③幻读 在同一个事务中使用相同的查询条件进行多次查询得到的记录集合不一致。6. MySQL事务隔离级别 隔离级别中文简单理解Read Uncommitted读未提交别人没提交我也能读Read Committed读已提交只能读别人已提交的数据Repeatable Read可重复读同一事务中重复读取保持一致Serializable串行化事务高度串行执行7. MySQL InnoDB 默认隔离级别 InnoDB 默认事务隔离级别是 Repeatable ReadRR可重复读8. 四种隔离级别解决什么问题隔离级别脏读不可重复读幻读Read Uncommitted❌可能❌可能❌可能Read Committed✅解决❌可能❌可能Repeatable Read✅解决✅解决⚠️标准定义下仍需关注幻读InnoDB有额外机制处理许多幻读场景Serializable✅✅✅Day 5MVCC MySQL 锁 ️1、什么是 MVCCMulti-Version Concurrency Control多版本并发控制。MVCC是一种并发控制机制一条数据维护多个版本让不同事务读取适合自己的版本提高并发性能2 、为什么需要 MVCC你改你的新版本我读我的旧版本大家尽量别互相等。3、undo log的两大作用undo log①是回滚日志主要用于事务回滚。②帮助构建历史版本链。MVCC 可以基于这些版本链让不同事务读取自己想要的版本。4、Read ViewRead View 相当于一套可见性规则用来判断版本链中的哪些数据版本能被当前事务读取为什么 InnoDB 的 Repeatable Read可重复读能够解决不可重复读问题请从undo log、MVCC、版本链和 Read View 的角度解释。undo log 记录数据修改前的相关信息帮助构建版本链。Read View 是一套可见性规则用于判断版本链中哪些数据能被当前事务读取。RR 下同一个事务会保持一致的Read View所以即使其他事务修改并提交了数据我还是可以读取之前对我可见的版本从而实现可重复读。5、什么是快照读和当前读快照读读取的是 MVCC 中对当前事务可见的数据版本当前读读取的是当前最新的数据通常需要加锁。6、什么是共享锁和排他锁共享锁Shared LockS锁允许多个事务同时读取同一份数据但会限制其他事务对该数据进行修改排他锁Exclusive LockX锁加了X锁后其他事务不能再获取其它S锁或X锁需要等待当前事务释放锁。表锁 vs 行锁表锁是锁住整张表锁粒度比较大并发性能相对低行锁是锁定具体的索引记录锁粒度更小并发性能更好。锁粒度 锁的范围大小记录锁Record Lockvs 间隙锁Gap LockRecord Lock锁住某一条具体的索引记录。Gap Lock 锁住两条索引记录之间的间隙为啥临键锁 Next-Key Lock 能帮助防止幻读Next-Key Lock 是 Record Lock 和 Gap Lock 的结合。它既可以锁住已有的索引记录又可以锁住记录之间的间隙从而阻止其他事务在相应范围内插入新的记录因此可以帮助防止幻读。7、InnoDB 在 RR 隔离级别下是怎么处理幻读问题的InnoDB 在 RR 隔离级别下主要通过MVCC和锁机制处理幻读场景。①对于普通快照读通过 MVCC、版本链和 Read View 保持 数据的一致②对于当前读则通过 Gap Lock、Next-Key Lock 等锁机制锁住相应的索引范围和间隙防止其他事务插入新的记录从而处理幻读问题。8、为什么说 InnoDB 的查询条件如果没有合适的索引可能导致“锁范围扩大”甚至表现得像锁表如果查询条件没有合适的索引可能需要进行全表扫描并锁住大量扫描到的索引记录导致锁范围扩大影响并发性能。什么情况下行锁可能退化成类似“锁表”的效果查询/更新条件中的字段没有合适的索引。快照读 当前读普通 SELECT 一般属于快照读通过 MVCC 和 Read View 读取当前事务可见的数据版本SELECT ... FOR UPDATE 属于当前读会读取最新的数据版本并对相关记录加锁。9、为什么SELECT ... FOR UPDATE不能像普通 SELECT 一样只读取历史快照而要读取当前最新的数据并加锁因为SELECT ... FOR UPDATE通常是为了后续修改数据所以需要读取当前最新的数据并对相关记录加锁防止其他事务同时进行冲突修改。事务 ACOMMIT之后事务 B 会发生什么COMMIT 不仅提交事务的数据修改还会释放该事务持有的锁。如果事务 A 还是持有X Lock但事务 B 执行select事务 B 会不会被阻塞为什么事务 B 通常不会被阻塞因为普通SELECT属于快照读它通过 MVCC根据 Read View 的可见性规则从版本链中读取当前事务可见的数据版本。Day6SQL 优化与 EXPLAIN 第一题 什么是慢 SQL慢 SQL执行时间较长、执行效率较低的 SQL → 慢查询日志定位具体SQL → EXPLAIN 分析执行计划 → 重点看四个字段 → 针对性优化比如建立或调整合适的索引、优化联合索引、避免索引失效、减少SELECT *、优化深分页等。优化之后再重新通过 EXPLAIN 和实际执行情况验证优化效果。第二题 EXPLAIN 是干什么的MySQL 打算怎么执行这条 SQLtype → 怎么找数据的key → 本次执行实际使用的索引rows →预计扫描的行数Extra → 还有什么额外执行信息第三题 type ALL扫描整张表type indexMySQL 需要扫描整棵索引type range只扫描符合条件的一段索引范围而不是扫描整棵索引type ref通过普通索引进行等值匹配可能匹配到多条记录type const通过主键索引或唯一索引进行等值查询并且最多只匹配一条记录一般至少希望查询达到 range 级别最好能达到 ref出现 index、ALL 时需要重点关注但不是看到 ALL 就一定有问题。为什么不能看到key ! NULL、SQL 使用了索引就直接判断“这条 SQL 性能很好”结合rows为什么Extra Using index通常是一个比较好的信号Using index → 使用了覆盖索引 ⭐Using where → 还需要根据 WHERE 条件进行过滤Using filesort→ 需要额外进行排序 Using temporary→ 使用了临时表 第四题SQL优化---深分页SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;LIMIT offset, size在 offset 很大时会产生深分页问题因为数据库需要扫描并丢弃前面大量数据最终只返回少量记录造成不必要的扫描开销。优化后SELECT * FROM orders WHERE id 1000000 !!! ORDER BY id LIMIT 10;Day7InnoDB 底层 MySQL 日志体系1、什么是 Buffer PoolInnoDB 为什么需要 Buffer PoolBuffer Pool 是 InnoDB 在内存中的缓冲区域用于缓存数据页等内容减少磁盘 I/O提高数据库性能。2、是不是每执行一次UPDATEMySQL 都要立刻把修改后的数据写回磁盘InnoDB 更新数据时通常先修改 Buffer Pool并记录 redo log之后再把脏页刷回磁盘而不是每次 UPDATE 都立即写磁盘。① 脏页这种已经被修改、但还没有刷回磁盘的数据页叫脏页。② redo log记录“这次修改做了什么”用于在数据库异常恢复时重新执行这些修改。3、既然有了 redo log为什么还需要 binlogredo log 和 binlog 有什么区别redo log 主要用于InnoDB 的崩溃恢复binlog 主要用于主从复制和数据恢复redo log 是 InnoDB 的日志而 binlog 是 MySQL Server 层面的日志因此 MySQL 需要同时维护这两套日志。4、为什么 MySQL 需要两阶段提交MySQL 使用两阶段提交是为了保证 redo log 和 binlog 的一致性避免出现一个日志已经记录事务提交、另一个日志却没有记录的情况。5、redo log 为什么叫“循环写” binlog叫“追加写”redo log 空间有限通过循环写实现空间复用。而 binlog 需要记录数据库的历史变更用于主从复制和数据恢复因此通常采用追加写的方式保存日志。6、一个update的背后流程图UPDATE ↓ ┌──────────────┐ │ Buffer Pool │ │ 修改数据页 │ └──────┬───────┘ ↓ 脏页 │ ┌──────┴──────┐ ↓ ↓ redo log binlog 崩溃恢复 复制/恢复 ↘ ↙ 两阶段提交 保证日志一致 ↓ 后续脏页刷盘 MySQL重点梳理
目录Day 1SQL基础1. 什么是SQL(Structured Query Language)2. SQL语句有哪些分类区分为什么update/delete一定要加where3. 增删改查CRUD什么是CRUD4. SELECT查询执行顺序重点5. Where和Having区别面试常问6. GROUP BY是什么⭐⭐⭐7. JOIN是什么⭐⭐⭐⭐Day 2MySQL索引 ⭐1. 什么是索引必背2. 为什么需要索引3. 索引的底层结构是什么⭐⭐⭐⭐为什么使用BTree4. 为什么不用二叉树为什么不用Hash索引⭐⭐⭐⭐5. BTree是什么⭐⭐⭐⭐⭐6. 索引的优缺点 ⭐⭐⭐⭐7. 什么字段适合建立索引⭐⭐⭐⭐什么字段不适合8、为什么BTree特别适合范围查询9、那BTree和B树相比有什么区别为什么数据库更喜欢BTreeDay 3MySQL 索引进阶 1. 什么是聚簇索引2. 什么是二级索引3. 什么是回表什么是覆盖索引高频4. 什么是联合索引什么是最左匹配原则5. 哪些情况可能导致索引失效高频6. 为什么不建议 select * 7、为什么①通常可以正常利用索引而②对索引列使用函数后可能无法正常利用这个索引Day 4MySQL 事务篇 1. 什么是事务为什么需要事务3. 事务MySQL中怎么使用4. 什么是 ACID5. 并发事务会出现什么问题① 脏读 ②不可重复读 ③幻读 6. MySQL事务隔离级别 7. MySQL InnoDB 默认隔离级别 8. 四种隔离级别解决什么问题Day 5MVCC MySQL 锁 ️1、什么是 MVCC2 、为什么需要 MVCC3、undo log的两大作用4、Read View为什么 InnoDB 的 Repeatable Read可重复读能够解决不可重复读问题请从undo log、MVCC、版本链和 Read View 的角度解释。5、什么是快照读和当前读6、什么是共享锁和排他锁表锁 vs 行锁记录锁Record Lockvs 间隙锁Gap Lock为啥临键锁 Next-Key Lock 能帮助防止幻读7、InnoDB 在 RR 隔离级别下是怎么处理幻读问题的8、为什么说 InnoDB 的查询条件如果没有合适的索引可能导致“锁范围扩大”甚至表现得像锁表什么情况下行锁可能退化成类似“锁表”的效果快照读 当前读9、为什么 SELECT ... FOR UPDATE 不能像普通 SELECT 一样只读取历史快照而要读取当前最新的数据并加锁事务 A COMMIT 之后事务 B 会发生什么如果事务 A 还是持有 X Lock但事务 B 执行select事务 B 会不会被阻塞为什么Day6SQL 优化与 EXPLAIN 第一题 什么是慢 SQL第二题 EXPLAIN 是干什么的第三题 为什么不能看到 key ! NULL、SQL 使用了索引就直接判断“这条 SQL 性能很好”为什么 Extra Using index 通常是一个比较好的信号第四题SQL优化---深分页Day7InnoDB 底层 MySQL 日志体系1、什么是 Buffer PoolInnoDB 为什么需要 Buffer Pool2、是不是每执行一次 UPDATEMySQL 都要立刻把修改后的数据写回磁盘3、既然有了 redo log为什么还需要 binlogredo log 和 binlog 有什么区别4、为什么 MySQL 需要两阶段提交5、redo log 为什么叫“循环写” binlog叫“追加写”6、一个update的背后流程图Day 1SQL基础1. 什么是SQL(Structured Query Language)回答是一种用于操作关系型数据库的语言。主要用于对数据库中的数据进行定义、操作、查询、控制。比如MySQL、Oracle/ˈɔːrəkəl/、PostgreSQL波斯格瑞 等关系型数据库都支持SQL2. SQL语句有哪些分类MySQL中常见分为① DDLData Definition Language是数据定义语言主要用来 定义数据库对象结构比如数据库、表、字段等常见操作包括create、alter、drop、truncate。create用于创建数据库对象比如创建表alter用于修改数据库对象结构如增加字段、修改字段类型、删除字段等。区分drop删除整个表对象表结构和数据都会被删除。truncate清空表中的所有数据但是保留表的结构和字段之后仍然可以继续使用这个表。② DMLData Manipulation Language是数据操作语言主要用于操作表中的数据包括insert、update、delete为什么update/delete一定要加whereupdate和delete加where条件是为了指定操作的数据范围。如果不加where条件会默认操作整张表的数据可能导致大量数据被错误修改或删除③ DQLData Query Language是数据查询语言最常用的命令是select用于 查询指定字段、筛选数据以及 进行排序、分组统计等操作④ DCLData Control Language是数据控制语言。主要用于管理数据库用户的权限常见命令有 grant用于授权revoke用于撤销权限3. 增删改查CRUD英文中文SQLCreate创建INSERTRead读取SELECTUpdate更新UPDATEDelete删除DELETE什么是CRUDCRUD是数据库中最基本的数据操作包括创建、查询、修改、删除对应SQL中的 insert、select、update、delete。4. SELECT查询执行顺序重点先找表from再过滤where再分组group by再过滤组having再选字段select再排序order by再分页limitselect name, count(*) -- 选择最终要返回的字段并使用count(*)统计数量 from user -- 从user表中获取数据确定数据来源 where age 18 -- 过滤原始数据只保留年龄大于18的数据 group by name -- 根据name字段进行分组 having count(*) 5 -- 对分组后的结果进行过滤只保留数量大于5的组 order by count(*) desc -- 按统计数量进行降序排序从大到小 limit 10; -- 最后只返回10条数据分页5. Where和Having区别面试常问where 和 having都是用于过滤数据区别在于where过滤的是原始数据在group by之前执行having过滤的是分组后的结果在group by之后执行。where不能使用聚合函数而having可以结合count、max、min等聚合函数。6. GROUP BY是什么⭐⭐⭐对查询结果进行分组统计例如统计每个城市用户数量。user表namecity张三上海李四上海王五北京分组结果citycount上海2北京1GROUP BY通常和聚合函数一起使用例如count()、sum()、avg()、max()、min()7. JOIN是什么⭐⭐⭐⭐用于多张表之间进行关联查询。常见JOIN类型有inner join内连接只返回两个表都有的数据left join左连接返回左表全部数据以及右表匹配数据Day 2MySQL索引 ⭐1. 什么是索引必背索引是一种帮助数据库快速查询数据的数据结构主要用于提高数据库的查询效率减少全表扫描2. 为什么需要索引当数据量较大时如果没有索引数据库需要进行全表扫描查询效率较低使用索引可以快速定位数据提高查询效率。3. 索引的底层结构是什么⭐⭐⭐⭐MySQL InnoDB存储引擎默认使用BTree作为索引结构。为什么使用BTree查询效率稳定、支持范围查询、树高度较低4. 为什么不用二叉树为什么不用Hash索引⭐⭐⭐⭐普通二叉树如果使用普通二叉树、数据在有序的情况下、可能退化成链表。时间复杂度变成O(n)、效率下降Hash索引优点等值查询快。缺点①不支持范围查询②不支持排序5. BTree是什么⭐⭐⭐⭐⭐BTree是一种多叉平衡搜索树MySQL利用它来组织索引数据。特点① 多叉一个节点可以存储多个关键字、因此树的高度比较低可以减少磁盘I/O次数② 所有数据存储在叶子节点③ 叶子节点通过链表连接方便范围查询6. 索引的优缺点 ⭐⭐⭐⭐优点① 提高查询效率减少扫描数据。② 提高排序效率缺点① 占用额外空间因为索引本身需要存储。② 降低增删改效率因为修改数据时需要同时维护索引7. 什么字段适合建立索引⭐⭐⭐⭐① 经常作为查询条件② 经常排序③ 经常用于JOIN查询什么字段不适合① 区分度比较低、重复值很多的字段② 经常修改的字段因为维护成本高。8、为什么BTree特别适合范围查询做范围查询时可以先通过BTree快速定位到范围起点比如100然后从这个叶子节点开始沿着链表顺序向后扫描直到200为止所以范围查询效率比较高9、那BTree和B树相比有什么区别为什么数据库更喜欢BTreeB树的非叶子节点也可以存储数据而B树的数据集中在叶子节点非叶子节点主要用于索引。这样B树的非叶子节点可以容纳更多索引项使树的高度更低同时B树的叶子节点有序连接因此更适合范围查询Day 3MySQL 索引进阶 1. 什么是聚簇索引①聚簇索引是数据和索引存储在一起的索引。②比如在InnoDB中聚簇索引的叶子节点 存储完整的数据行通常主键索引就是聚簇索引2. 什么是二级索引又叫非聚簇索引 / Secondary Index二级索引的叶子节点一般不存完整数据行而是存索引字段 主键值。3. 什么是回表什么是覆盖索引高频回表是指通过二级索引查询时其中没有查询所需要的全部字段就需要先通过二级索引找到主键值再根据主键到聚簇索引中查询完整数据这个过程叫回表。查询所需要的字段全部能够从索引中直接获得不需要再回表查询这种情况叫覆盖索引4. 什么是联合索引什么是最左匹配原则联合索引同时基于多个字段建立的索引查询条件要尽量从联合索引最左边的字段开始匹配。遇到范围查询后范围字段可以使用索引但其后的字段通常不能继续用于缩小索引扫描范围5. 哪些情况可能导致索引失效高频① 违反联合索引最左匹配② 对索引列进行函数操作③ 对索引列进行计算④ LIKE 以%开头6. 为什么不建议select *①可能增加不必要的数据读取②并可能导致无法利用覆盖索引③增加回表查询成本7、为什么①通常可以正常利用索引而②对索引列使用函数后可能无法正常利用这个索引① where name Felix; ② where lower(name) felix;索引存的是原始值的有序结构但查询条件用的是计算后的值因此数据库可能无法直接根据原索引定位数据后%开头确定知道从哪找。前%开头不确定不知道从哪找。where name Felix and age 20 and city 上海;最左匹配①中间缺字段会断②遇到范围查询范围列能用但后面的列不能继续用于缩小联合索引的扫描范围。Day 4MySQL 事务篇 1. 什么是事务为什么需要事务事务是一组数据库操作的集合这些操作要么全部成功要么全部失败用来保证数据的一致性和可靠性防止执行过程中出现异常导致数据处于错误状态3. 事务MySQL中怎么使用start transaction 开始事务 commit 提交事务 rollback 回滚事务4. 什么是 ACID事务有四大特性原子性Atomicity一个事务中的操作要么全部成功要么全部失败。一致性Consistency事务执行前后数据都应该保持合法、一致的状态总金额隔离性Isolation多个事务并发执行时事务之间应该尽量互不干扰持久性Durability事务一旦commit提交成功修改的数据就应该被持久保存。5. 并发事务会出现什么问题① 脏读 一个事务读到了另一个事务还没有提交的数据。②不可重复读 同一个事务中对同一条数据读取两次结果不一样。③幻读 在同一个事务中使用相同的查询条件进行多次查询得到的记录集合不一致。6. MySQL事务隔离级别 隔离级别中文简单理解Read Uncommitted读未提交别人没提交我也能读Read Committed读已提交只能读别人已提交的数据Repeatable Read可重复读同一事务中重复读取保持一致Serializable串行化事务高度串行执行7. MySQL InnoDB 默认隔离级别 InnoDB 默认事务隔离级别是 Repeatable ReadRR可重复读8. 四种隔离级别解决什么问题隔离级别脏读不可重复读幻读Read Uncommitted❌可能❌可能❌可能Read Committed✅解决❌可能❌可能Repeatable Read✅解决✅解决⚠️标准定义下仍需关注幻读InnoDB有额外机制处理许多幻读场景Serializable✅✅✅Day 5MVCC MySQL 锁 ️1、什么是 MVCCMulti-Version Concurrency Control多版本并发控制。MVCC是一种并发控制机制一条数据维护多个版本让不同事务读取适合自己的版本提高并发性能2 、为什么需要 MVCC你改你的新版本我读我的旧版本大家尽量别互相等。3、undo log的两大作用undo log①是回滚日志主要用于事务回滚。②帮助构建历史版本链。MVCC 可以基于这些版本链让不同事务读取自己想要的版本。4、Read ViewRead View 相当于一套可见性规则用来判断版本链中的哪些数据版本能被当前事务读取为什么 InnoDB 的 Repeatable Read可重复读能够解决不可重复读问题请从undo log、MVCC、版本链和 Read View 的角度解释。undo log 记录数据修改前的相关信息帮助构建版本链。Read View 是一套可见性规则用于判断版本链中哪些数据能被当前事务读取。RR 下同一个事务会保持一致的Read View所以即使其他事务修改并提交了数据我还是可以读取之前对我可见的版本从而实现可重复读。5、什么是快照读和当前读快照读读取的是 MVCC 中对当前事务可见的数据版本当前读读取的是当前最新的数据通常需要加锁。6、什么是共享锁和排他锁共享锁Shared LockS锁允许多个事务同时读取同一份数据但会限制其他事务对该数据进行修改排他锁Exclusive LockX锁加了X锁后其他事务不能再获取其它S锁或X锁需要等待当前事务释放锁。表锁 vs 行锁表锁是锁住整张表锁粒度比较大并发性能相对低行锁是锁定具体的索引记录锁粒度更小并发性能更好。锁粒度 锁的范围大小记录锁Record Lockvs 间隙锁Gap LockRecord Lock锁住某一条具体的索引记录。Gap Lock 锁住两条索引记录之间的间隙为啥临键锁 Next-Key Lock 能帮助防止幻读Next-Key Lock 是 Record Lock 和 Gap Lock 的结合。它既可以锁住已有的索引记录又可以锁住记录之间的间隙从而阻止其他事务在相应范围内插入新的记录因此可以帮助防止幻读。7、InnoDB 在 RR 隔离级别下是怎么处理幻读问题的InnoDB 在 RR 隔离级别下主要通过MVCC和锁机制处理幻读场景。①对于普通快照读通过 MVCC、版本链和 Read View 保持 数据的一致②对于当前读则通过 Gap Lock、Next-Key Lock 等锁机制锁住相应的索引范围和间隙防止其他事务插入新的记录从而处理幻读问题。8、为什么说 InnoDB 的查询条件如果没有合适的索引可能导致“锁范围扩大”甚至表现得像锁表如果查询条件没有合适的索引可能需要进行全表扫描并锁住大量扫描到的索引记录导致锁范围扩大影响并发性能。什么情况下行锁可能退化成类似“锁表”的效果查询/更新条件中的字段没有合适的索引。快照读 当前读普通 SELECT 一般属于快照读通过 MVCC 和 Read View 读取当前事务可见的数据版本SELECT ... FOR UPDATE 属于当前读会读取最新的数据版本并对相关记录加锁。9、为什么SELECT ... FOR UPDATE不能像普通 SELECT 一样只读取历史快照而要读取当前最新的数据并加锁因为SELECT ... FOR UPDATE通常是为了后续修改数据所以需要读取当前最新的数据并对相关记录加锁防止其他事务同时进行冲突修改。事务 ACOMMIT之后事务 B 会发生什么COMMIT 不仅提交事务的数据修改还会释放该事务持有的锁。如果事务 A 还是持有X Lock但事务 B 执行select事务 B 会不会被阻塞为什么事务 B 通常不会被阻塞因为普通SELECT属于快照读它通过 MVCC根据 Read View 的可见性规则从版本链中读取当前事务可见的数据版本。Day6SQL 优化与 EXPLAIN 第一题 什么是慢 SQL慢 SQL执行时间较长、执行效率较低的 SQL → 慢查询日志定位具体SQL → EXPLAIN 分析执行计划 → 重点看四个字段 → 针对性优化比如建立或调整合适的索引、优化联合索引、避免索引失效、减少SELECT *、优化深分页等。优化之后再重新通过 EXPLAIN 和实际执行情况验证优化效果。第二题 EXPLAIN 是干什么的MySQL 打算怎么执行这条 SQLtype → 怎么找数据的key → 本次执行实际使用的索引rows →预计扫描的行数Extra → 还有什么额外执行信息第三题 type ALL扫描整张表type indexMySQL 需要扫描整棵索引type range只扫描符合条件的一段索引范围而不是扫描整棵索引type ref通过普通索引进行等值匹配可能匹配到多条记录type const通过主键索引或唯一索引进行等值查询并且最多只匹配一条记录一般至少希望查询达到 range 级别最好能达到 ref出现 index、ALL 时需要重点关注但不是看到 ALL 就一定有问题。为什么不能看到key ! NULL、SQL 使用了索引就直接判断“这条 SQL 性能很好”结合rows为什么Extra Using index通常是一个比较好的信号Using index → 使用了覆盖索引 ⭐Using where → 还需要根据 WHERE 条件进行过滤Using filesort→ 需要额外进行排序 Using temporary→ 使用了临时表 第四题SQL优化---深分页SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;LIMIT offset, size在 offset 很大时会产生深分页问题因为数据库需要扫描并丢弃前面大量数据最终只返回少量记录造成不必要的扫描开销。优化后SELECT * FROM orders WHERE id 1000000 !!! ORDER BY id LIMIT 10;Day7InnoDB 底层 MySQL 日志体系1、什么是 Buffer PoolInnoDB 为什么需要 Buffer PoolBuffer Pool 是 InnoDB 在内存中的缓冲区域用于缓存数据页等内容减少磁盘 I/O提高数据库性能。2、是不是每执行一次UPDATEMySQL 都要立刻把修改后的数据写回磁盘InnoDB 更新数据时通常先修改 Buffer Pool并记录 redo log之后再把脏页刷回磁盘而不是每次 UPDATE 都立即写磁盘。① 脏页这种已经被修改、但还没有刷回磁盘的数据页叫脏页。② redo log记录“这次修改做了什么”用于在数据库异常恢复时重新执行这些修改。3、既然有了 redo log为什么还需要 binlogredo log 和 binlog 有什么区别redo log 主要用于InnoDB 的崩溃恢复binlog 主要用于主从复制和数据恢复redo log 是 InnoDB 的日志而 binlog 是 MySQL Server 层面的日志因此 MySQL 需要同时维护这两套日志。4、为什么 MySQL 需要两阶段提交MySQL 使用两阶段提交是为了保证 redo log 和 binlog 的一致性避免出现一个日志已经记录事务提交、另一个日志却没有记录的情况。5、redo log 为什么叫“循环写” binlog叫“追加写”redo log 空间有限通过循环写实现空间复用。而 binlog 需要记录数据库的历史变更用于主从复制和数据恢复因此通常采用追加写的方式保存日志。6、一个update的背后流程图UPDATE ↓ ┌──────────────┐ │ Buffer Pool │ │ 修改数据页 │ └──────┬───────┘ ↓ 脏页 │ ┌──────┴──────┐ ↓ ↓ redo log binlog 崩溃恢复 复制/恢复 ↘ ↙ 两阶段提交 保证日志一致 ↓ 后续脏页刷盘