本文深入探讨了MySQL中的索引类型、数据结构及其应用场景，重点介绍了B+树在InnoDB引擎中的应用。同时，阐述了锁的分类，包括全局锁、表级锁和行级锁，以及不同类型的锁如何协同工作。此外，还讨论了SQL优化的步骤和方法，如索引优化、LIMIT优化和查询语句优化。最后，概述了INSERT语句的执行流程和事务回滚与数据恢复的底层原理，强调了MVCC在事务隔离中的作用。
一. 说一下对mysql索引的了解
索引是什么
索引是一个为了提高查询性能而创建的独立数据结构，类似书的目录。
索引的分类
- 主键索引: 基于主键创建的索引，只要表有主键，就有主键索引。
- 非主键索引: 除了主键之外普通字段创建的索引。主要有: 复合索引、全文索引、唯一索引、普通索引、外键索引。
索引的数据结构
-
应用场景(
等值查询、范围查询): -
select * from t_user where id=10; 等值查询，
最好的数据结构是hash -
select * from t_user where id >10 and id <30;
-
数据结构的选择
-
hash: 仅满足等值查询，时间复杂度为 O(1)。
-
二叉排序树: 大于根节点的数据排在右子树上，小于根节点的数据排在左子树上，时间复杂度为O(log2n)。
-
平衡二叉树: 时刻保持二叉树左右子树的高度小于等于1，如果大于1就进行左旋或右旋来保持平衡，时间复杂度为O(log2n)。
-
b-tree(B树): 每个节点中保持多个元素，每个节点保持的元素的上限叫度，每个元素的左右两侧各有一个指针指向下一个节点，左边元素小于根节点，右边元素大于根节点。
-
b+tree(B+树): 中间节点不保存数据，
只包含主键信息，所有的数据都放到叶子节点中，每个叶子结点通过双向指针连接。 -
InnoDB引擎的索引
-
主键索引: 索引都是B+树结构，叶子结点就是记录行(
单向链表)，根据主键索引查询的效率更高，主键索引和数据行聚集存储在一起，就是聚族索引/聚集索引 -
辅助索引: 叶子节点中保存的是主键，如果查询数据需要辅助索引找到主键信息，然后根据主键索引找到对应的记录，
这个查询主键索引的过程就叫做回表。 -
常用的索引
-
主键索引: 主键 -
组合索引:
组合索引是指一个索引中包含多个字段，使用时要保证最左前缀原则；查询条件中的第一个字段必须存在，如果不存在就不能应用组合索引，如果中间字段丢失，会导致后面的字段无法应用索引。 -
覆盖索引
在select条件中，如果字段都包含辅助索引，就不需要回表，可以提高查询速度，这就叫覆盖索引。引出一个SQL优化点： 在使用select查询的时候，尽量使用具体的字段，避免使用 select *。
回答话术:
-
Mysql的索引就是为了提高查询性能而创建的独立数据结构，可以把它看作书的目录，索引分为主键索引和非主键索引。
-
索引的底层数据结构是B+树，子节点不保存数据，只保存主键信息，所有的数据都放到叶子节点中，叶子节点之间通过双向指针连接。所以根据主键索引查询效率更高，非主键索引查询时会找到对应的主键信息，然后根据主键索引找到对应的记录，这个查询主索引的过程就叫做回表。
-
常用的索引有主键索引、组合索引和覆盖索引，
组合索引使用时要保证最左前缀原则，也就是说查询条件中的第一个字段必须存在，如果不存在就不能应用组合索引，如果中间字段丢失，会导致后面的字段无法应用索引。覆盖索引指的是在select条件中，如果都包含辅助索引，就不需要回表，可以提高查询速度。
二. 说一下mysql中的锁
按照粒度分类: 全局锁、表级锁、行级锁。
按照功能分类: 共享锁、排他锁。
全局锁：
锁定了整个mysql实例，加锁: flush tables with lock; 解锁: unlock tables 会话会自动解锁
表级锁(锁定了一个表):
共享锁(S锁)：sessionA 加锁之后，表处于只读状态，其他session可以读取表中数据，不能修改；sessionA 也只能读，不能修改。排他锁(X锁):加锁后，当前session会独占用资源，加锁的session可读可写，其他session不可读也不可写。元数据锁:当开启事务并执行一个表的查询操作之后，会对表元数据加锁，此时不允许对表结构进行修改操作。自增锁:mysql支持自增主键，生成的主键有序而且唯一不重复，就是通过自增锁实现的。
行级锁(锁定了一行数据, 只有InnoDB支持)
记录锁:只锁定了表中一行数据，它也有共享锁和排他锁两种类型间隙锁:表中记录和记录之间的缝隙，当加间隙锁后不允许间隙值的插入；
举例: id ->1,5,10; 1和5 之间的间隙值就是2，3，4；加锁的时机就是通过主键更新(查询)数据没有命中记录时加间隙锁。解决了什么问题? (幻读的问题) 间隙锁存在于唯一索引中，锁定的是开区间范围内的一段间隙。临建锁(记录锁+间隙锁):指的是加在某条记录一级这条记录前面间隙的锁；左开右闭区间，临建锁只与非唯一索引列有关，在唯一索引列上不存在临建锁。如果查询条件等值查询命中了一条记录会退化为记录锁，若没有命中任何记录会退化为间隙锁。意向锁:1. 当对表 加表级锁时，需要判断表中是否有行级锁的存在，若存在就无法加锁成功；2.需要快速的知道是否有行锁的存在，所以引入了意向锁，在表中加行锁后，会在表中增加一个标志位，标识表中有行锁； 3. 解决了什么问题？ 就是为了让InnoDB中行锁和表锁更加高效的共存。插入意向锁:1. 执行insert操作后，事务没有提交之前，需要锁定插入行的主键的值，是行级锁，为了防止其他事务，也是用此主键；2. 解决了什么问题？ 来控制和解决并发插入。
回答话术:
- Mysql中的锁按粒度分为全局锁、表级锁和行级锁，按功能又可分为共享锁和排他锁，
- 共享锁指的是加锁后表处于可读状态，其他会话不能修改，而排他锁是只有加锁后其他会话不能读也不能写。还有自增锁是mysql自增主键的保证措施，生成的主键有序且唯一。
- InnoDB支持行级锁，有间隙锁、意向锁、插入意向锁等，间隙锁是指表中记录和记录的缝隙，当加间隙锁后不允许缝隙值的插入，主要解决了幻读的问题。意向锁是为了让InnoDB中行锁和表锁更加高效的共存，当对表加表级锁是，需要判断表中是否有行级锁的存在，如果存在就无法加锁成功。插入意向锁是解决并发插入的问题，执行insert操作后，事务没有提交之前，需要锁定插入行的主键的值，防止其他事务也使用此主键。
三. 关于mysql优化了解多少？
首先对于mysql优化，分三个步骤:
- 定位需要优化sql, 根据慢查询日志，找到查询时间比较长的sql语句
开启慢查询功能:slow_query_log=ON, long_query_time=1 查询阀值，单位是秒
- 分析需要优化的sql,explain执行计划，分析定位sql性能问题
- 主要看几个参数:
select_type查询类型、type(ref级别以上才可以，非唯一索引扫描，返回匹配某个单独的值的所有行)、prossible_keys(可能使用到的索引)、key(真正使用索引)、rows(预估扫描的行数，不是精确的值)
- 主要看几个参数:
- 真正的去优化sql语句
- 索引优化
- 尽量建立组合索引，并且注意组合索引创建顺序，符合最左前缀原则
- 尽量使用覆盖索引，不是使用select *
- order by、group by语句，也尽量使用索引
- 使用force强制生效索引
- 使用唯一索引
- limit优化
- 分页时用到limit，翻页到最后会因偏移量大导致分页效率差，
可以使用 between and 替换，也可以使用 where id > offset, 后面加上 limit
- 分页时用到limit，翻页到最后会因偏移量大导致分页效率差，
- 查询语句优化
- 小表驱动大表，使用join连接时，使用小表关联大表去查
- 避免扫描全表
- 尽量少使用count(*)，使用count(主键)
- join时两表关联字段都要有索引
- where条件不要使用not in (建议使用 not exists)
- 索引优化
回答话术:
- 要想优化SQL，首先我们可以通过慢查询日志定位到查询时间比较长的sql语句，然后使用explain执行计划对sql语句进行分析，查看sql是否使用索引，以及索引级别，和预估的扫描行数等，再根据情况去具体优化sql。
- 一般来说常见的优化sql就是
索引优化，尽量使用组合索引，并且注意要符合最左前缀原则，尽量使用覆盖索引，减少回表，也可以使用force强制生效索引。还有就是Limit优化，大数据量下翻页到最后会因为偏移量大导致分页效率变差，可以使用where id > offset limit 10来提高性能，也可以使用between and替换。还有sql语句上的优化就是用小表驱动大表，join时关联字段都要有索引等等。前面说的都是使用上的优化， - 还有一些服务器层面的优化就是设置足够大的innodb_buffer_pool_size，标识缓冲池字节大小，推荐内存的50%～80%，还有就是降低磁盘写入次数，可以设置 innodb_flush_log_at_trx_commit控制redo log刷新到磁盘的策略，默认是1，innodb_max_dirty_pages_pct，设置脏页占比比例触发刷新脏页到磁盘中，推荐值25%～50%。
四. MVCC到底做了什么？
- MVCC其实就是一个数据快照，不同的事务访问的是不同版本的数据库快照，从而实现了不同事务之间的隔离级别。隔离级别分为读未提交、读已提交、可重复读和串行化，可重复读是InnoDB的默认隔离级别。
- MVCC的底层是依赖了undo log和read view。undo log中插入和删除的不同点就是插入操作的undo log只对本事务可见，所以可以在事务提交后直接删除，不需要purge操作，而更新操作产生的undo log可能会被用于后续的MVCC中，所以不能在事务提交时删除，而会放在undo log的链表中，等待异步的purge清除线程进行最后的删除。Read view是判断版本链中哪个版本是当前事务可见的，读提交和可重复读的一个很大区别就是生产ReadView的时间不同。
- MVCC通过每行记录后面保存了两个隐藏的列来实现，一个保存的是行的事务ID，另一个保存了行的回滚指针。MVCC分为当前读和快照读，当前读读的是最新数据，比如插入/删除/更新等操作需要加行级锁，快照读读的是历史数据，查询操作不用加锁。
四.你知道一条insert语句的执行流程吗？
- 首先由server层的连接器建立连接，通过解析器解析sql语句，通过优化器进行sql优化，在使用执行器把sql语句交给引擎层去执行，
- 在InnoDB引擎中，会开启事务，然后加插入意向锁锁定主键保证并发插入，接着记录undo log的redo log缓冲区，然后记录undo log缓冲区，再去记录redo log缓冲区，（这里undo log是保证事务回滚的关键，redo log是保证数据不丢失的关键），更新数据内存页缓冲区，然后进行提交事务，此时redo log会进行落盘，redo log落盘成功则事务提交成功，否则事务回滚，释放插入意向锁，执行结束。
- 这里有个关键点就是所有日志和数据在事务提交前都保存在了缓冲区中，只有事务提交并且redo log落盘成功时，才算真正的执行成功。
五. 事务回滚和数据恢复的底层原理
-
事务的隔离性由多版本并发机制和锁实现的，原子性和一致性主要通过redo log、undo log和force log at commit机制来实现的。
-
redo log用于在崩溃时恢复数据，undo log用于在对事务的影响进行撤销回滚，也可以用于多版本并发控制，force log at commit机制保证了事务提交后redo log。redo log原理就是记录数据页的物理操作，如果数据丢失，可以根据redo log进行恢复。当事务的各种sql操作执行时，会先在缓冲区中修改信息，也会将对应的redo log写入它所属的缓存，当事务执行commit时，与该事务相关的redo log缓冲必须要刷新到磁盘中之后，才算commit成功，也就是这样的操作保证了数据的准确性。
-
undo log原理时记录了数据在每个操作前的状态，如果事务需要回滚，就可以根据undo log进行回滚操作，undo log存储不同的redo log，它存在数据库内部的回滚段中，回滚段位于共享表空间中，以undo page为最小的组织单位，和数据和索引的页类似。因为redo log是物理日志，所以undo log得写入也会产生redo
log，undo log也需要持久性的保护。 -
事务的整个流程：事务开始，查询带更新的数据到内存，并加写锁，然后记录undo log相关的redo log到缓冲，记录undo log到缓冲，记录数据变更相关的redo log到缓冲，内存更新数据，形成脏页，此时进行commit出发redo log刷盘，innoDB每秒也会触发redo log刷盘，在commit之前如果服务崩溃，则恢复后会进行回滚，在commit之后如果服务崩溃，则恢复后会使用redo log进行数据恢复。undo log页和脏页会依据checkPoint机制进行刷盘，事务结束。
170万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
