一、事务的四大特性ACID事务有四大特性原子性、一致性、隔离性、持久性。原子性Atomicity事务中的所有操作要么全部成功要么全部失败。如果执行过程中出错事务会回滚到开始前的状态。主要通过 undo log 日志实现。一致性Consistency事务执行前后数据库的数据要保持合理合法的状态。硬性规则数据类型匹配、主键唯一性、外键约束等业务规则比如转账场景A 和 B 各有 1000 元A 转给 B 500 元后A 剩 500B 有 1500总金额不变隔离性Isolation保证并发状态下数据的一致性多个事务之间彼此隔离体现在四种隔离级别读未提交、读已提交、可重复读、串行化。通过锁和 MVCC多版本并发控制实现。持久性Durability事务提交后对数据的修改是永久的即使系统故障也不会丢失。主要通过 redo log 日志实现。二、Undo Log 的作用undo log 是回滚日志记录数据被修改前的原始版本。主要作用1. 执行数据回滚保证事务的原子性2. 实现 MVCC 多版本并发控制分为两种类型insert undo log执行 insert 时产生只记录主键信息回滚时根据主键删除。事务提交后立即删除。update undo log执行 delete、update 时产生记录修改前的完整数据用于回滚和 MVCC。事务提交后不会立即删除要等所有读事务都不再需要该版本才删除。三、并发状态下的三大问题脏读事务 A 读到了事务 B 还未提交的数据。不可重复读事务 A 两次查询同一条数据中间事务 B 修改并提交了这条数据导致 A 两次读到的结果不一样。幻读事务 A 多次查询符合条件的记录数量中间事务 B 插入或删除了符合条件的记录导致 A 前后查到的记录数不一样。这些问题通过设置不同的隔离级别来解决。四、事务的四种隔离级别隔离级别脏读不可重复读幻读说明读未提交✓✓✓并发性能最高但数据一致性最差基本不用读已提交✗✓✓避免脏读可重复读✗✗✓MySQL 默认级别避免脏读和不可重复读串行化✗✗✗完全避免并发问题但性能极低基本不用五、四种隔离级别的实现原理读未提交直接读内存中的最新数据不做任何控制所以会出现各种并发问题。串行化通过加锁实现所有操作都加临键锁Next-Key Lock强制事务串行执行。select 加读锁update/delete/insert 加写锁。解决了所有并发问题但并发性能几乎为零。读已提交和可重复读通过 MVCC 实现区别在于生成 ReadView 的时机读已提交每次查询都生成新的 ReadView可重复读事务中第一次查询时生成 ReadView后续查询复用这个 ReadView所以能保证多次读取结果一致六按理说RC每一次快照读都要生成ReadView而RR只用生成一次为什么反而是RC的性能更好了1.在RR隔离级别下每一次查询都是第一次查询的版本也就意味着Undo log版本链必须一直保持直到事务结束Undo log版本链冗余而RC隔离级别下每一次查询都会是新的ReadView 一旦查询结束之前的旧版本就可能不再被需要了Purge 线程可以立即清理掉过时的 Undo Log2.RC锁粒度更小并发性能更高RR为了避免幻读问题大量使用了临键锁锁的范围大了就更容易导致锁等待和死锁并发性能下降。RC绝大多数情况下只锁存在的记录Record Lock不锁间隙锁的范围小冲突概率低并发吞吐量自然更高。当更新age20的数据时凡是扫过的数据就算不符合条件也会加锁直到事务提交了。RC不会锁住不符合条件的数据3. RC 能更好地利用 “索引下推ICP” 在RR隔离级别下为了保证一致性某些场景下会禁用索引下推。 在RC隔离级别下索引下推可以肆无忌惮地使用。减少回表查询次数提高性能。七、可重复读隔离级别下A 事务提交的数据B 事务能看见吗要看 B 事务第一次执行查询的时机如果 B 的第一次查询在 A 提交之前那么看不见 A 提交的数据如果 B 的第一次查询在 A 提交之后能看见 A 提交的数据因为在可重复读级别下ReadView 在第一次查询时生成后续查询都用这个 ReadView 做可见性判断。八、MVCC 的实现原理MVCC多版本并发控制允许多个事务同时读取同一行数据而不互相阻塞因为每个事务看到的是 undo log 版本链中的历史版本。解决了并发状态下的读写冲突问题。三大组件1.两个隐藏字段trx_id最后修改这行记录的事务 IDroll_pointer指向修改前历史版本的指针2. undo log版本链每次修改数据时旧版本写入 undo log多个历史版本通过 roll_pointer 连接成链。3. ReadView读视图用于可见性判断包含四个关键信息creator_trx_id创建该 ReadView 的事务 IDm_ids创建 ReadView 时所有活跃未提交的事务 ID 列表min_trx_id活跃事务中最小的事务 IDmax_trx_id系统下一个要分配的事务 ID当前最大事务 ID 1可见性判断规则1. 如果trx_id creator_trx_id说明是当前事务修改的数据可见2. 如果trx_id min_trx_id说明该版本在 ReadView 创建前就已提交可见3. 如果trx_id max_trx_id说明该版本在 ReadView 创建后才启动不可见4. 如果min_trx_id trx_id max_trx_id如果 trx_id 在 m_ids 中说明该事务还活跃着不可见如果 trx_id 不在 m_ids 中说明该事务已提交可见如果当前版本不可见就沿着 roll_pointer 找上一个版本继续判断直到找到可见版本或到达链尾。九、MySQL 可重复读隔离级别下如何避免幻读分两种场景快照读普通 select通过 MVCC 避免幻读因为读的是历史版本不受其他事务插入的影响。当前读加锁查询、update、delete通过临键锁Next-Key Lock避免幻读锁住查询范围和间隙防止其他事务插入新记录。InnDB引擎很大程度上解决了幻读问题但是当快照读和当前都混用时还有可能出现幻读问题事务A读取id5的数据发现不存在事务B此时插入一条Id 5的数据然后提交事务A直接对id5的数据执行update操作当前读此时id5这行数据的事务id隐藏的字段会记录为事务A的id事务A下一次查询id 5可见性判断能查出来的。解决方法尽量在开启事务之后马上执行 select ... for update 这类锁定读的语句因为它会对记录加 next-key lock从而避免其他事务插入一条新记录就避免了幻读的问题。十、一条 update 语句是原子性的吗为什么是原子性的通过锁 undo log保证锁保证执行期间没有其他事务修改这行数据避免并发冲突。undo log记录修改前的数据如果操作失败可以及时回滚到原始状态。十一、Redo Log 的作用及实现原理redo log重做日志保证了事务的持久性实现了 WALWrite-Ahead Logging先写日志机制。工作流程1. 修改数据时内存中的数据页变成脏页2. 不是立即刷盘而是先在 redo log buffer 中写入 redo log记录数据修改后的信息3. redo log 先刷入磁盘4. 脏页数据等数据库空闲时由后台线程批量异步刷入磁盘为什么 redo log 更快redo log 写入速度比脏页快几个数量级因为redo log是顺序写入在磁盘上有固定的写入位置没有寻址开销脏页是随机写入需要找到数据页在磁盘上的位置有大量寻址开销这样即使系统崩溃也能通过 redo log 恢复已提交的事务保证数据不丢失。十二. 讲一下MySQL里的锁有哪些MySQL里的锁按照粒度可以分为全局锁表级锁行级锁。表级锁又分为表锁元数据锁意向锁行级锁又分为记录所间隙锁临键锁按锁的性质分可以分为读锁和写锁。按用法来分可以分为悲观锁和乐观锁全局锁对整个数据库加锁每张表都处于只读状态用于数据库的全局逻辑备份。加锁时机执行FLUSH TABLES WITH READ LOCK (FTWRL)解锁时机执行UNLOCK TABLES或会话断开时表锁分为读类型的表锁和写类型的表锁对整张表加读类型表锁那么所有事物包括当前 事务对这张表都不能写对整张表加写锁那么只有当前事务对这张表有读写权限 其他事务没有读写权限。加锁时机执行LOCK TABLES 表名 READ/WRITE时或加行锁时没有使用到索引升级为 表锁。释放时机UNLOCK TABLES或会话断开。元数据锁用于保护数据库对象表、库、视图等的元数据结构不被并发修改避免 DML 和 DDL 操作冲突。加锁时机执行DML语句时加元数据读锁执行DDL语句时加元数据写锁。释放时机事务提交事务过长极易引发阻塞导致全表不可用。意向锁当执行插入、更新、删除操作需要先对表加上「意向独占锁」然后对该记录加独占 锁。意向锁的目的是为了快速判断表里是否有记录被加锁。行锁InnoDB 引擎是支持行级锁的而 MyISAM 引擎并不支持行级锁。基于索引实现如果没 有索引那么就会变为表锁。与事务隔离级别强相关RC支持记录锁RR全部支持记录锁只锁记录不锁间隙加锁时机通过SELECT ... FOR UPDATE排他锁或SELECT ... LOCK IN SHARE MODE共享锁触发执行INSERT/UPDATE/DELETE等 DML 语句时InnoDB 会自动给操作行加排他 型记录锁间隙锁只存在于可重复读隔离级别目的是为了解决可重复读隔离级别下幻读的现象。Next-Key Lock 称为临键锁是 Record Lock Gap Lock 的组合锁定一个范围并且锁定记录本身。十三. MySQL两个线程的update语句同时处理一条数据会不会有阻塞会有阻塞执行update时会给该记录加写类型的记录锁行锁第二条update语句就会阻塞。十四. 两条update语句处理一张表的不同的主键范围的记录一个10一个15会不会遇到阻塞底层是为什么的不会因为锁住的范围不一样不会形成冲突。第一条 update sql 的话 id10锁住的范围是-♾️10第二条 update sql 的话id 15锁住的范围是15♾️十五. 索引是什么有什么好处索引就是指利用B树哈希等数据结构来优化查询速度提高查询效率的方法。他就像书的目录一样可以减少扫描的数据量。十六. 索引的分类按数据结构分可以分为B树索引Hash索引Full-text索引按物理结构分可以分为聚簇索引二级索引按字段特性分可以分为主键索引唯一索引普通索引前缀索引按字段个数分分为单列索引联合索引B树索引Hash索引Full-Text索引InnoDB引擎不支持Hash索引但内存中有一个自适应哈希索引MyIsam不支持Hash索引Memory不支持Full-Text索引。1. Hash索引的优点是什么适合哪些场景Hash索引的优点是等值查询的时间复杂度能做到O(1)比B树索引快而短板是不支持范围查询、排序和前缀匹配所以使用场景是业务中绝大部分查询都是或IN这类等值匹配几乎没有范围查询/BETWEEN、排序ORDER BY需求。用户登录时根据user_id查询用户信息SELECT * FROM user WHERE user_id 10086电商系统根据order_id查询订单详情2. MySQL索引是如何实现的MySQL的InnoDB引擎使用的是B树实现索引的具体就是一张表在创建时就会根据主键生 成一张聚簇索引。B树的特点1. 他是一种多叉树将树的高度变矮并且所有叶子节点都在同一层确保了 所有数据项的检索都具有相同的I/O延迟提高了搜索效率。2. 只在叶子节点存放数据非叶子节点存放索引只起到查找的作用。B 树在插入、删除的效率都更高比如删除根节点的时候不会像 B 树那样 会发生复杂的树的变化3. 父节点的索引值会出现在子结点中所以B树的叶子节点存放了所有的索 引信息每个索引信息之间有两个指针指向相邻的索引信息形成一个双 向链表这就意味着它非常适合作排序和范围查询4. 自平衡B树在插入、删除和更新操作后会自动重新平衡确保树的高度 保持相对稳定从而保持良好的搜索性能。每个节点最多可以有M个子节 点最少可以有ceil(M/2)个子节点除了根节点这里的M是树的阶数。3. 说说B树和B树的区别B树所有的只在叶子节点存放数据信息非叶子节点只放索引而B树在非叶子节点上也存放数据信息。这样做首先是在相同数据量的情况下B树可能会更矮胖查询底层节点的磁盘 I/O次数会更少。其次是B 树插入、删除的效率都更高比如删除根节点的时候不会像B树那样会发生复杂的树的变化。B树的叶子节点之间存在双向链表便于排序和范围查询而B树没有只能遍历完成范围查询产生大量的磁盘IO。B树的每一次查询都会到查到叶子节点而B树可能会在非叶子节点中查找查找稳定性比B 树好4. 为什么不用跳表相比于B树面对相同的千万级数据量时B树只需维护3-4曾即可而调表需要维护层数很多造成查询时产生大量磁盘IO。聚簇索引二级索引1. 聚簇索引和非聚簇索引的区别区别一 聚簇索引的叶子节点上存储着实际的数据行这意味着索引结构本身就是数据的物理存储结构。而非聚簇索引的叶子节点不包含完整的数据行而是包含指向数据行的指针或主键值。数据行本身存储在聚簇索引中。区别二在一张表中聚簇索引有且只有一个非聚簇索引可以有多个。2. 如果聚簇索引的数据更新它的存储要不要变化如果更新的数据是非索引数据那么就没有变化。如果是更新的数据是索引数据那么会发生变化因为要维护B树的有序性3. 聚簇索引索引键的选取规则如果有主键那么主键就是聚簇索引的索引列如果没有主键那么就会选取第一个唯一非空字段为索引键如果都没有那么会生成一个隐藏主键为索引键。4. 什么字段适合当做主键首先是字段要满足唯一非空的特性字段最好是自增的趋势随机的话可能会产生页分裂。最好不要用业务字段因为可能会由于业务变动字段值也会改变。单机系统可以用自增字段单多台服务器可能会进行数据合并所以推荐用分布式id方案。5. 性别字段能加索引么为啥不推荐。6. 表中十个字段你主键用自增ID还是UUID为什么用自增id。原因1.InnoDB 的聚簇索引叶子节点是按主键顺序物理存储数据的自增id会直接追加到聚簇索引的最后一个数据页不会打乱已有数据的物理顺序插入效率极高而UUID随机生成的插入效率低还有可能造成页分裂。2. 自增id存储开销小自增id8个字节UUID36个字节。3. 有序就意味着排序和范围查询效率更高。联合索引1. 联合索引的实现原理联合索引就是将多个字段组合成一个索引在B树的非叶子节点上同时存放了多个字段的值比如我将字段AB创建联合索引那么在B树中字段A是全局有序的字段B是局部有序当字段A相等时字段B有序因此查询时就要遵守最左前缀法则。也就是按照最左优先的方式进行索引的匹配。在使用联合索引进行查询的时候如果不遵循「最左匹配原则」联合索引会失效这样就无法利用到索引快速查询的特性了。最左前缀法则索引生效的前提是从最左列开始且前导列是等值匹配2. 创建联合索引要注意什么建立联合索引时要把区分度大的字段排在前面这样区分度大的字段越有可能被更多的 SQL 使用到。区分度就是某个字段 column 不同值的个数「除以」表的总行数计算公式如下性别的区分度很小UUID区分度很大。3. 联合索引ABC现在有个执行语句是A XXX and C XXX索引怎么走a走索引c可以走索引下推4. 联合索引(a,b,c) 查询条件 where b xxx and a x 会生效吗会生效与ax and bxxx是等效的满足最左前缀法则5.联合索引 (a, bc)where条件是 a2 and c 1能用到联合索引吗a能走联合索引c可以走索引下推。6.什么是索引下推定义索引下推是将过滤逻辑从服务器层下推到存储引擎层利用索引列提前过滤数据的优化技术核心价值减少回表次数和数据传输降低磁盘 IO 开销生效标志EXPLAIN 的Extra字段显示Using index condition适用场景联合索引的二级索引扫描、需要回表的查询限制条件聚簇索引、覆盖索引、函数 / 表达式过滤等场景不支持与最左前缀的关系ICP 不改变最左前缀原则仅在索引生效的基础上优化。十七. 索引失效有哪些情况6 种会发生索引失效的情况当我们使用左或者左右模糊匹配的时候也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效当我们在查询条件中对索引列使用函数就会导致索引失效。当我们在查询条件中对索引列进行表达式计算也是无法走索引的。MySQL 在遇到字符串和数字比较的时候会自动把字符串转为数字然后再进行比较。如果字符串是索引列而条件语句中的输入参数是数字的话那么索引列会发生隐式类型转换由于隐式类型转换是通过 CAST 函数实现的等同于对索引列使用了函数所以就会导致索引失效。联合索引要能正确使用需要遵循最左匹配原则也就是按照最左优先的方式进行索引的匹配否则就会导致索引失效。在 WHERE 子句中如果在 OR 前的条件列是索引列而在 OR 后的条件列不是索引列那么索引会失效
MySQL八股——进阶篇（持续更新）
一、事务的四大特性ACID事务有四大特性原子性、一致性、隔离性、持久性。原子性Atomicity事务中的所有操作要么全部成功要么全部失败。如果执行过程中出错事务会回滚到开始前的状态。主要通过 undo log 日志实现。一致性Consistency事务执行前后数据库的数据要保持合理合法的状态。硬性规则数据类型匹配、主键唯一性、外键约束等业务规则比如转账场景A 和 B 各有 1000 元A 转给 B 500 元后A 剩 500B 有 1500总金额不变隔离性Isolation保证并发状态下数据的一致性多个事务之间彼此隔离体现在四种隔离级别读未提交、读已提交、可重复读、串行化。通过锁和 MVCC多版本并发控制实现。持久性Durability事务提交后对数据的修改是永久的即使系统故障也不会丢失。主要通过 redo log 日志实现。二、Undo Log 的作用undo log 是回滚日志记录数据被修改前的原始版本。主要作用1. 执行数据回滚保证事务的原子性2. 实现 MVCC 多版本并发控制分为两种类型insert undo log执行 insert 时产生只记录主键信息回滚时根据主键删除。事务提交后立即删除。update undo log执行 delete、update 时产生记录修改前的完整数据用于回滚和 MVCC。事务提交后不会立即删除要等所有读事务都不再需要该版本才删除。三、并发状态下的三大问题脏读事务 A 读到了事务 B 还未提交的数据。不可重复读事务 A 两次查询同一条数据中间事务 B 修改并提交了这条数据导致 A 两次读到的结果不一样。幻读事务 A 多次查询符合条件的记录数量中间事务 B 插入或删除了符合条件的记录导致 A 前后查到的记录数不一样。这些问题通过设置不同的隔离级别来解决。四、事务的四种隔离级别隔离级别脏读不可重复读幻读说明读未提交✓✓✓并发性能最高但数据一致性最差基本不用读已提交✗✓✓避免脏读可重复读✗✗✓MySQL 默认级别避免脏读和不可重复读串行化✗✗✗完全避免并发问题但性能极低基本不用五、四种隔离级别的实现原理读未提交直接读内存中的最新数据不做任何控制所以会出现各种并发问题。串行化通过加锁实现所有操作都加临键锁Next-Key Lock强制事务串行执行。select 加读锁update/delete/insert 加写锁。解决了所有并发问题但并发性能几乎为零。读已提交和可重复读通过 MVCC 实现区别在于生成 ReadView 的时机读已提交每次查询都生成新的 ReadView可重复读事务中第一次查询时生成 ReadView后续查询复用这个 ReadView所以能保证多次读取结果一致六按理说RC每一次快照读都要生成ReadView而RR只用生成一次为什么反而是RC的性能更好了1.在RR隔离级别下每一次查询都是第一次查询的版本也就意味着Undo log版本链必须一直保持直到事务结束Undo log版本链冗余而RC隔离级别下每一次查询都会是新的ReadView 一旦查询结束之前的旧版本就可能不再被需要了Purge 线程可以立即清理掉过时的 Undo Log2.RC锁粒度更小并发性能更高RR为了避免幻读问题大量使用了临键锁锁的范围大了就更容易导致锁等待和死锁并发性能下降。RC绝大多数情况下只锁存在的记录Record Lock不锁间隙锁的范围小冲突概率低并发吞吐量自然更高。当更新age20的数据时凡是扫过的数据就算不符合条件也会加锁直到事务提交了。RC不会锁住不符合条件的数据3. RC 能更好地利用 “索引下推ICP” 在RR隔离级别下为了保证一致性某些场景下会禁用索引下推。 在RC隔离级别下索引下推可以肆无忌惮地使用。减少回表查询次数提高性能。七、可重复读隔离级别下A 事务提交的数据B 事务能看见吗要看 B 事务第一次执行查询的时机如果 B 的第一次查询在 A 提交之前那么看不见 A 提交的数据如果 B 的第一次查询在 A 提交之后能看见 A 提交的数据因为在可重复读级别下ReadView 在第一次查询时生成后续查询都用这个 ReadView 做可见性判断。八、MVCC 的实现原理MVCC多版本并发控制允许多个事务同时读取同一行数据而不互相阻塞因为每个事务看到的是 undo log 版本链中的历史版本。解决了并发状态下的读写冲突问题。三大组件1.两个隐藏字段trx_id最后修改这行记录的事务 IDroll_pointer指向修改前历史版本的指针2. undo log版本链每次修改数据时旧版本写入 undo log多个历史版本通过 roll_pointer 连接成链。3. ReadView读视图用于可见性判断包含四个关键信息creator_trx_id创建该 ReadView 的事务 IDm_ids创建 ReadView 时所有活跃未提交的事务 ID 列表min_trx_id活跃事务中最小的事务 IDmax_trx_id系统下一个要分配的事务 ID当前最大事务 ID 1可见性判断规则1. 如果trx_id creator_trx_id说明是当前事务修改的数据可见2. 如果trx_id min_trx_id说明该版本在 ReadView 创建前就已提交可见3. 如果trx_id max_trx_id说明该版本在 ReadView 创建后才启动不可见4. 如果min_trx_id trx_id max_trx_id如果 trx_id 在 m_ids 中说明该事务还活跃着不可见如果 trx_id 不在 m_ids 中说明该事务已提交可见如果当前版本不可见就沿着 roll_pointer 找上一个版本继续判断直到找到可见版本或到达链尾。九、MySQL 可重复读隔离级别下如何避免幻读分两种场景快照读普通 select通过 MVCC 避免幻读因为读的是历史版本不受其他事务插入的影响。当前读加锁查询、update、delete通过临键锁Next-Key Lock避免幻读锁住查询范围和间隙防止其他事务插入新记录。InnDB引擎很大程度上解决了幻读问题但是当快照读和当前都混用时还有可能出现幻读问题事务A读取id5的数据发现不存在事务B此时插入一条Id 5的数据然后提交事务A直接对id5的数据执行update操作当前读此时id5这行数据的事务id隐藏的字段会记录为事务A的id事务A下一次查询id 5可见性判断能查出来的。解决方法尽量在开启事务之后马上执行 select ... for update 这类锁定读的语句因为它会对记录加 next-key lock从而避免其他事务插入一条新记录就避免了幻读的问题。十、一条 update 语句是原子性的吗为什么是原子性的通过锁 undo log保证锁保证执行期间没有其他事务修改这行数据避免并发冲突。undo log记录修改前的数据如果操作失败可以及时回滚到原始状态。十一、Redo Log 的作用及实现原理redo log重做日志保证了事务的持久性实现了 WALWrite-Ahead Logging先写日志机制。工作流程1. 修改数据时内存中的数据页变成脏页2. 不是立即刷盘而是先在 redo log buffer 中写入 redo log记录数据修改后的信息3. redo log 先刷入磁盘4. 脏页数据等数据库空闲时由后台线程批量异步刷入磁盘为什么 redo log 更快redo log 写入速度比脏页快几个数量级因为redo log是顺序写入在磁盘上有固定的写入位置没有寻址开销脏页是随机写入需要找到数据页在磁盘上的位置有大量寻址开销这样即使系统崩溃也能通过 redo log 恢复已提交的事务保证数据不丢失。十二. 讲一下MySQL里的锁有哪些MySQL里的锁按照粒度可以分为全局锁表级锁行级锁。表级锁又分为表锁元数据锁意向锁行级锁又分为记录所间隙锁临键锁按锁的性质分可以分为读锁和写锁。按用法来分可以分为悲观锁和乐观锁全局锁对整个数据库加锁每张表都处于只读状态用于数据库的全局逻辑备份。加锁时机执行FLUSH TABLES WITH READ LOCK (FTWRL)解锁时机执行UNLOCK TABLES或会话断开时表锁分为读类型的表锁和写类型的表锁对整张表加读类型表锁那么所有事物包括当前 事务对这张表都不能写对整张表加写锁那么只有当前事务对这张表有读写权限 其他事务没有读写权限。加锁时机执行LOCK TABLES 表名 READ/WRITE时或加行锁时没有使用到索引升级为 表锁。释放时机UNLOCK TABLES或会话断开。元数据锁用于保护数据库对象表、库、视图等的元数据结构不被并发修改避免 DML 和 DDL 操作冲突。加锁时机执行DML语句时加元数据读锁执行DDL语句时加元数据写锁。释放时机事务提交事务过长极易引发阻塞导致全表不可用。意向锁当执行插入、更新、删除操作需要先对表加上「意向独占锁」然后对该记录加独占 锁。意向锁的目的是为了快速判断表里是否有记录被加锁。行锁InnoDB 引擎是支持行级锁的而 MyISAM 引擎并不支持行级锁。基于索引实现如果没 有索引那么就会变为表锁。与事务隔离级别强相关RC支持记录锁RR全部支持记录锁只锁记录不锁间隙加锁时机通过SELECT ... FOR UPDATE排他锁或SELECT ... LOCK IN SHARE MODE共享锁触发执行INSERT/UPDATE/DELETE等 DML 语句时InnoDB 会自动给操作行加排他 型记录锁间隙锁只存在于可重复读隔离级别目的是为了解决可重复读隔离级别下幻读的现象。Next-Key Lock 称为临键锁是 Record Lock Gap Lock 的组合锁定一个范围并且锁定记录本身。十三. MySQL两个线程的update语句同时处理一条数据会不会有阻塞会有阻塞执行update时会给该记录加写类型的记录锁行锁第二条update语句就会阻塞。十四. 两条update语句处理一张表的不同的主键范围的记录一个10一个15会不会遇到阻塞底层是为什么的不会因为锁住的范围不一样不会形成冲突。第一条 update sql 的话 id10锁住的范围是-♾️10第二条 update sql 的话id 15锁住的范围是15♾️十五. 索引是什么有什么好处索引就是指利用B树哈希等数据结构来优化查询速度提高查询效率的方法。他就像书的目录一样可以减少扫描的数据量。十六. 索引的分类按数据结构分可以分为B树索引Hash索引Full-text索引按物理结构分可以分为聚簇索引二级索引按字段特性分可以分为主键索引唯一索引普通索引前缀索引按字段个数分分为单列索引联合索引B树索引Hash索引Full-Text索引InnoDB引擎不支持Hash索引但内存中有一个自适应哈希索引MyIsam不支持Hash索引Memory不支持Full-Text索引。1. Hash索引的优点是什么适合哪些场景Hash索引的优点是等值查询的时间复杂度能做到O(1)比B树索引快而短板是不支持范围查询、排序和前缀匹配所以使用场景是业务中绝大部分查询都是或IN这类等值匹配几乎没有范围查询/BETWEEN、排序ORDER BY需求。用户登录时根据user_id查询用户信息SELECT * FROM user WHERE user_id 10086电商系统根据order_id查询订单详情2. MySQL索引是如何实现的MySQL的InnoDB引擎使用的是B树实现索引的具体就是一张表在创建时就会根据主键生 成一张聚簇索引。B树的特点1. 他是一种多叉树将树的高度变矮并且所有叶子节点都在同一层确保了 所有数据项的检索都具有相同的I/O延迟提高了搜索效率。2. 只在叶子节点存放数据非叶子节点存放索引只起到查找的作用。B 树在插入、删除的效率都更高比如删除根节点的时候不会像 B 树那样 会发生复杂的树的变化3. 父节点的索引值会出现在子结点中所以B树的叶子节点存放了所有的索 引信息每个索引信息之间有两个指针指向相邻的索引信息形成一个双 向链表这就意味着它非常适合作排序和范围查询4. 自平衡B树在插入、删除和更新操作后会自动重新平衡确保树的高度 保持相对稳定从而保持良好的搜索性能。每个节点最多可以有M个子节 点最少可以有ceil(M/2)个子节点除了根节点这里的M是树的阶数。3. 说说B树和B树的区别B树所有的只在叶子节点存放数据信息非叶子节点只放索引而B树在非叶子节点上也存放数据信息。这样做首先是在相同数据量的情况下B树可能会更矮胖查询底层节点的磁盘 I/O次数会更少。其次是B 树插入、删除的效率都更高比如删除根节点的时候不会像B树那样会发生复杂的树的变化。B树的叶子节点之间存在双向链表便于排序和范围查询而B树没有只能遍历完成范围查询产生大量的磁盘IO。B树的每一次查询都会到查到叶子节点而B树可能会在非叶子节点中查找查找稳定性比B 树好4. 为什么不用跳表相比于B树面对相同的千万级数据量时B树只需维护3-4曾即可而调表需要维护层数很多造成查询时产生大量磁盘IO。聚簇索引二级索引1. 聚簇索引和非聚簇索引的区别区别一 聚簇索引的叶子节点上存储着实际的数据行这意味着索引结构本身就是数据的物理存储结构。而非聚簇索引的叶子节点不包含完整的数据行而是包含指向数据行的指针或主键值。数据行本身存储在聚簇索引中。区别二在一张表中聚簇索引有且只有一个非聚簇索引可以有多个。2. 如果聚簇索引的数据更新它的存储要不要变化如果更新的数据是非索引数据那么就没有变化。如果是更新的数据是索引数据那么会发生变化因为要维护B树的有序性3. 聚簇索引索引键的选取规则如果有主键那么主键就是聚簇索引的索引列如果没有主键那么就会选取第一个唯一非空字段为索引键如果都没有那么会生成一个隐藏主键为索引键。4. 什么字段适合当做主键首先是字段要满足唯一非空的特性字段最好是自增的趋势随机的话可能会产生页分裂。最好不要用业务字段因为可能会由于业务变动字段值也会改变。单机系统可以用自增字段单多台服务器可能会进行数据合并所以推荐用分布式id方案。5. 性别字段能加索引么为啥不推荐。6. 表中十个字段你主键用自增ID还是UUID为什么用自增id。原因1.InnoDB 的聚簇索引叶子节点是按主键顺序物理存储数据的自增id会直接追加到聚簇索引的最后一个数据页不会打乱已有数据的物理顺序插入效率极高而UUID随机生成的插入效率低还有可能造成页分裂。2. 自增id存储开销小自增id8个字节UUID36个字节。3. 有序就意味着排序和范围查询效率更高。联合索引1. 联合索引的实现原理联合索引就是将多个字段组合成一个索引在B树的非叶子节点上同时存放了多个字段的值比如我将字段AB创建联合索引那么在B树中字段A是全局有序的字段B是局部有序当字段A相等时字段B有序因此查询时就要遵守最左前缀法则。也就是按照最左优先的方式进行索引的匹配。在使用联合索引进行查询的时候如果不遵循「最左匹配原则」联合索引会失效这样就无法利用到索引快速查询的特性了。最左前缀法则索引生效的前提是从最左列开始且前导列是等值匹配2. 创建联合索引要注意什么建立联合索引时要把区分度大的字段排在前面这样区分度大的字段越有可能被更多的 SQL 使用到。区分度就是某个字段 column 不同值的个数「除以」表的总行数计算公式如下性别的区分度很小UUID区分度很大。3. 联合索引ABC现在有个执行语句是A XXX and C XXX索引怎么走a走索引c可以走索引下推4. 联合索引(a,b,c) 查询条件 where b xxx and a x 会生效吗会生效与ax and bxxx是等效的满足最左前缀法则5.联合索引 (a, bc)where条件是 a2 and c 1能用到联合索引吗a能走联合索引c可以走索引下推。6.什么是索引下推定义索引下推是将过滤逻辑从服务器层下推到存储引擎层利用索引列提前过滤数据的优化技术核心价值减少回表次数和数据传输降低磁盘 IO 开销生效标志EXPLAIN 的Extra字段显示Using index condition适用场景联合索引的二级索引扫描、需要回表的查询限制条件聚簇索引、覆盖索引、函数 / 表达式过滤等场景不支持与最左前缀的关系ICP 不改变最左前缀原则仅在索引生效的基础上优化。十七. 索引失效有哪些情况6 种会发生索引失效的情况当我们使用左或者左右模糊匹配的时候也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效当我们在查询条件中对索引列使用函数就会导致索引失效。当我们在查询条件中对索引列进行表达式计算也是无法走索引的。MySQL 在遇到字符串和数字比较的时候会自动把字符串转为数字然后再进行比较。如果字符串是索引列而条件语句中的输入参数是数字的话那么索引列会发生隐式类型转换由于隐式类型转换是通过 CAST 函数实现的等同于对索引列使用了函数所以就会导致索引失效。联合索引要能正确使用需要遵循最左匹配原则也就是按照最左优先的方式进行索引的匹配否则就会导致索引失效。在 WHERE 子句中如果在 OR 前的条件列是索引列而在 OR 后的条件列不是索引列那么索引会失效
