02-04 19:28 已编辑中南大学计算机类

关注

分享我整理的Mysql八股笔记

前言：笔记整理自javaguide,程序员大彬，CT-NOTES等博客。之所以自己整理笔记是想让知识内容更简洁，条理更清晰。笔记内容较简单，大家可以拿走然后在此基础上自己增删修改。另外，如果大家有面试中遇到了没被此笔记覆盖的知识点，欢迎在此文章下留言，我可以添加上去。最后，本文章收录在我的专栏里面，我会不断在专栏里更新我整理的八股笔记，欢迎大家点点订阅，谢谢喵。

0.sql刷题笔记

sql刷题笔记 - 掘金 (juejin.cn)

1.Mysql基础

1.什么是关系型数据库？

关系型数据库（RDB，Relational Database）就是一种建立在关系模型的基础上的数据库。关系模型表明了数据库中所存储的数据之间的联系（一对一、一对多、多对多）。

关系型数据库中，我们的数据都被存放在了各种表中（比如用户表），表中的每一行就存放着一条数据（比如一个用户的信息）。

大部分关系型数据库都支持事务的四大特性(ACID)。

2.什么是MySQL

MySQL是一个关系型数据库，它采用表的形式来存储数据,有表结构（行和列）。行代表每一行数据，列代表该行中的每个值。列上的值是有数据类型的，比如：整数、字符串、日期等等。

3.数据库的三大范式

第一范式1NF

确保数据库表字段的原子性。

第二范式2NF

首先要满足第一范式，另外包含两部分内容，一是表必须有一个主键；二是非主键列必须完全依赖于主键，而不能只依赖于主键的一部分。

第三范式3NF

首先要满足第二范式，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。

2NF和3NF的区别？

2NF依据是非主键列是否完全依赖于主键，还是依赖于主键的一部分。
3NF依据是非主键列是直接依赖于主键，还是直接依赖于非主键。

4.SQL语句完整的执行顺序

FROM 子句组装来自不同数据源的数据；WHERE 子句基于指定的条件对记录行进行筛选；GROUP BY 子句将数据划分为多个分组；使用聚集函数进行计算；使用 HAVING 子句筛选分组；计算所有的表达式；SELECT 的字段；使用 ORDER BY 对结果集进行排序。

5.having和where区别？

二者作用的对象不同，where子句作用于表和视图，having作用于组。
where在数据分组前进行过滤，having在数据分组后进行过滤。

5.Mysql的架构/组成

MySQL主要分为 Server 层和存储引擎层：

Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块。
存储引擎：主要负责数据的存储和读取。server 层通过api与存储引擎进行通信。

Server 层基本组件

连接器： 当客户端连接 MySQL 时，server层会对其进行身份认证和权限校验。
查询缓存: 执行查询语句的时候，会先查询缓存，先校验这个 sql 是否执行过，如果有缓存这个 sql，就会直接返回给客户端，如果没有命中，就会执行后续的操作。
分析器: 没有命中缓存的话，SQL 语句就会经过分析器，主要分为两步，词法分析和语法分析，先看 SQL 语句要做什么，再检查 SQL 语句语法是否正确。
优化器： 优化器对查询进行优化，包括重写查询、决定表的读写顺序以及选择合适的索引等，生成执行计划。
执行器： 首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会根据执行计划去调用引擎的接口，返回结果。

6.sql语句在Mysql中的执行过程

其实我们的 SQL 可以分为两种，一种是查询，一种是更新（增加，修改，删除）

1.查询语句执行流程

大概有4步：

先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限，会先查询缓存，以这条 SQL 语句为 key 在内存中查询是否有结果，如果有直接缓存，如果没有，执行下一步。
通过分析器进行词法分析，提取 SQL 语句的关键元素。然后判断这个 SQL 语句是否有语法错误，比如关键词是否正确等等，如果检查没问题就执行下一步。
接下来就是优化器进行确定执行方案，优化器根据自己的优化算法进行选择执行效率最好的一个方案
进行权限校验，如果没有权限就会返回错误信息，如果有权限就会调用数据库引擎接口，返回引擎的执行结果。

2.更新语句执行流程

举个例子，更新语句是这样的：

update user set name = '张三' where id = 1;

1.先查询到 id 为1的记录，有缓存会使用缓存。

2.拿到查询结果，将 name 更新为张三，然后调用引擎接口，写入更新数据，innodb 引擎将数据保存在内存中，同时记录redo log，此时redo log进入 准备状态。

3.执行器收到通知后记录binlog，然后调用引擎接口，提交redo log为提交状态。

4.更新完成。

为什么记录完redo log，不直接提交，而是先进入准备状态？

假设先写redo log直接提交，然后写binlog，写完redo log后，机器挂了，binlog日志没有被写入，那么机器重启后，这台机器会通过redo log恢复数据，但是这个时候binlog并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。

7.inner join,left join和right join的区别

类型作用1 Join(Inner Join)内连接查出两表完全匹配的部分。（交集）2 Left Join左连接返回左表所有的行，右表返回匹配行，不匹配的返回NULL3 Right Join右连接返回由表所有的行，左表返回匹配行，不匹配的返回NULL4 Full Join全连接只要其中一个表存在匹配，则返回行

2.MySQL优化

0.如何分析sql的性能(`EXPLAIN` 命令)

我们可以使用 EXPLAIN 命令来分析 SQL 的 执行计划 。

1.什么是执行计划

执行计划 是指一条 SQL 语句在经过 MySQL 查询优化器 的优化会后，具体的执行方式。

通过 EXPLAIN 的结果，可以了解到如数据表的查询顺序、数据查询操作的操作类型、哪些索引可以被命中、哪些索引实际会命中、每个数据表有多少行记录被查询等信息。

2.执行计划常用字段

MySQL 为我们提供了 EXPLAIN 命令，来获取执行计划的相关信息。

需要注意的是，EXPLAIN 语句并不会真的去执行相关的语句，而是通过查询优化器对语句进行分析，找出最优的查询方案，并显示对应的信息。

执行计划有12个字段组成，常用的有：

1.type:查询执行的类型，描述了查询是如何执行的。以下性能从好到坏依次是：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

2.possible_keys:表示 MySQL 执行查询时可能用到的索引。如果这一列为 NULL ，则表示没有可能用到的索引；这种情况下，需要检查 WHERE 语句中所使用的的列，看是否可以通过给这些列中某个或多个添加索引的方法来提高查询性能。

3.key:表示 MySQL 实际使用到的索引。如果为 NULL，则表示未用到索引。

4.key_len:表示 MySQL 实际使用的索引的最大长度；当使用到联合索引时，有可能是多个列的长度和。在满足需求的前提下越短越好。如果 key 列显示 NULL ，则 key_len 列也显示 NULL 。

5.rows:估算要找到所需的记录，需要读取的行数，这是一个估计值。

**6.extra:**这列包含了 MySQL 解析查询的额外信息，通过这些信息，可以更准确的理解 MySQL 到底是如何执行查询的。常见的值如下：

Using filesort：在排序时使用了外部的索引排序，没有用到表内索引进行排序。
Using temporary：MySQL 需要创建临时表来存储查询的结果，常见于 ORDER BY 和 GROUP BY。
Using index：表明查询使用了覆盖索引，不用回表，查询效率非常高。
Using index condition：表示查询优化器选择使用了索引条件下推这个特性。
Using where：表明查询使用了 WHERE 子句进行条件过滤。一般在没有使用到索引的时候会出现。
Using join buffer (Block Nested Loop)：连表查询的方式，表示当被驱动表的没有使用索引的时候，MySQL 会先将驱动表读出来放到 join buffer 中，再遍历被驱动表与驱动表进行查询。

3.explain具体怎么分析一条慢sql?

如果一条sql执行很慢的话，我们通常会使用mysql自动的执行计划explain来去查看这条sql的执行情况，

比如在这里面可以通过key和key_len检查是否命中了索引，如果本身已经添加了索引，也可以判断索引是否有失效的情况，

第二个，可以通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描，

第三个可以通过extra建议来判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复

1.慢查询问题

1.怎么定位慢查询

嗯~，我们当时做压测的时候有的接口非常的慢，接口的响应时间超过了2秒以上，因为我们当时的系统部署了运维的监控系统Skywalking ，在展示的报表中可以看到是哪一个接口比较慢，并且可以分析这个接口哪部分比较慢，这里可以看到SQL的具体的执行时间，所以可以定位是哪个sql出了问题

2.导致MySQL慢查询有哪些原因？

没有索引，或者索引失效。
单表数据量太大
查询使用了临时表
join 或者子查询过多
in元素过多。如果使用了in，即使后面的条件加了索引，还是要注意in后面的元素不要过多哈。in元素一般建议不要超过500个，如果超过了，建议分组，每次500一组进行。
limit深度分页问题

3.大表查询慢常见优化措施？

当MySQL单表记录数过大时，数据库的性能会明显下降，一些常见的优化措施如下：

合理建立索引。在合适的字段上建立索引，例如在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描
对SQL优化。
建立分区。对关键字段建立水平分区，比如时间字段，若查询条件往往通过时间范围来进行查询，能提升不少性能
利用缓存。利用Redis等缓存热点数据，提高查询效率
限定数据的范围。比如：用户在查询历史信息的时候，可以控制在一个月的时间范围内
读写分离。经典的数据库拆分方案，主库负责写，从库负责读
通过分库分表的方式进行优化，主要有垂直拆分和水平拆分

4.慢sql的优化分析思路？

步骤如下：

1.查看慢查询日志记录，分析慢SQL

通过慢查询日志slow log，定位那些执行效率较低的SQL语句，重点关注分析

2.explain查看分析SQL的执行计划

当定位出查询效率低的SQL后，可以使用explain查看SQL的执行计划。

比如在这里面可以通过key和key_len检查是否命中了索引，如果本身已经添加了索引，也可以判断索引是否有失效的情况，

第二个，可以通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描，

第三个可以通过extra建议来判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复

3.profile 分析执行耗时

explain只是看到SQL的预估执行计划，如果要了解SQL真正的执行线程状态及消耗的时间，需要使用profiling。开启profiling参数后，后续执行的SQL语句都会记录其资源开销，包括IO，上下文切换，CPU，内存等等，我们可以根据这些开销进一步分析当前慢SQL的瓶颈再进一步进行优化。

4.Optimizer Trace分析详情

profile只能查看到SQL的执行耗时，但是无法看到SQL真正执行的过程信息，即不知道MySQL优化器是如何选择执行计划。这时候，我们可以使用Optimizer Trace，它可以跟踪执行语句的解析优化执行的全过程。

大家可以查看分析其执行树，会包括三个阶段：

join_preparation：准备阶段
join_optimization：分析阶段
join_execution：执行阶段

5.确定问题并采用相应的措施

最后确认问题，就采取对应的措施。

多数慢SQL都跟索引有关，比如不加索引，索引不生效、不合理等，这时候，我们可以优化索引。
我们还可以优化SQL语句，比如一些in元素过多问题（分批），深分页问题（基于上一次数据过滤等），进行时间分段查询
SQl没办法很好优化，可以改用ES的方式，或者数仓。
如果单表数据量过大导致慢查询，则可以考虑分库分表
如果数据库在刷脏页导致慢查询，考虑是否可以优化一些参数，跟DBA讨论优化方案
如果存量数据量太大，考虑是否可以让部分数据归档

2.sql优化经验

sql语句优化小技巧

SQL语句优化

查询时只返回必要的列，用具体的字段列表代替 select * 语句,因为要尽量用聚集索引防止回表查询
SQL语句要避免造成索引失效的写法
尽量用union all代替union union会多一次过滤，效率低
避免在where子句中对字段进行表达式操作
Join优化能用inner join 就不用left join right join，如必须使用一定要以小表为驱动，（内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。left join 或 right join，不会重新调整顺序）
使用varchar代替char.(因为可变常字段存储空间小，可节省空间)
将多次插入换成批量Insert插入

sql优化的真实场景

1.正确建立索引（联合索引）

应对场景：在实际工作中，对于一个承载着业务核心的表，它除了数据条数多之外，而且会有很多列。对于这样核心的表，我们又频繁的有查询修改操作。查询的时候往往是多个列在一起构成筛选条件,所以针对这种情况我们就需要创建合理的联合索引。

这里通过创建一个用户信息表来讲解，表名为userbase 表信息如下：创建一个联合索引，由 age,score,sex 组成注意他们的前后顺序：age 是1 ，score是2 ，sex是3

执行不同条件查询及结果：语句：select * from userbase where age =10 and score=98 and sex=‘男’ (顺序：1、2、3)结果：使用了索引

语句：select * from userbase where age =10 and score=98 (顺序：1、2)结果：使用了索引语句：select * from userbase where score=98 and sex=‘男’ (顺序：2、3)结果：未使用索引

语句：select * from userbase where score=98 (顺序：2)结果：未使用索引

联合索引的最左原则： 以最左边的那列为中心，只要它参与了，就可以让索引起到作用，否则就不起作用。

我们知道了最左原则，就可以避免创建过多而无用的索引。索引的创建，会占用硬盘大量的空间，所以合理的创建索引，不但可以让我们查询效率提高，也能减少硬盘空间。

2.建表时选择合适的数据类型

使用varchar代替char.(因为可变常字段存储空间小，可节省空间)

3.不滥用事务修改事务级别

不同的数据库，有自己默认的事务类型。我们在做开发时，因为某些关键业务比如涉及到金钱的转账等，必须用事务防止出现脏数据(或脏读现象)，保证数据的准确性。但是这样的情况下，必定有损执行性能，在一些不必要的业务中，建议取消事务。如我要查询某个结果，这个结果即使出现脏读，也不影响我们业务。就可以取消事务，来提高整体效率。

4.避免不必要的数据库连接

如果要执行100行sql语句，如果可以一次执行，那就在一次数据库连接，一起执行100条。而不是通过100次数据库连接，每次连接执行一条。较少数据库连接时产生的性能消耗。

在Java中，我们可以使用JDBC（Java Database Connectivity）来连接和操作数据库。为了一次性执行多条SQL语句，我们可以使用Statement或PreparedStatement对象的executeBatch()方

   import java.sql.*;

public class Main {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydatabase";
        String user = "username";
        String password = "password";
        try {
            Connection conn = DriverManager.getConnection(url, user, password);
            Statement stmt = conn.createStatement();
            for (int i = 1; i <= 100; i++) {
                stmt.addBatch("INSERT INTO mytable VALUES (" + i + ", 'value" + i + "')");
            }
            int[] results = stmt.executeBatch();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

在这个例子中，我们首先建立了一个到数据库的连接，然后创建了一个Statement对象。然后，我们在循环中添加了100个INSERT语句到批处理中。最后，我们调用executeBatch()方法一次执行所有的插入操作，然后关闭连接。

4.深度分页优化

(什么是深度分页问题？)

select * from xxx order by id limit 500000, 10;

当offset非常大时，server层会从引擎层获取到很多无用的数据，而当select后面是*号时，就需要拷贝完整的行信息，拷贝完整数据相比只拷贝行数据里的其中一两个列字段更耗费时间。这就是深度分页问题。

优化方法：

延迟关联法，就是把条件转移到主键索引树，然后减少回表。

先用子查询查出符合条件的主键，再用主键ID做条件查出所有字段。

select * from xxx  where id >=(select id from xxx order by id limit 500000, 1) order by id limit 10;

3.分页问题

1.MySQL查询 limit 1000,10 和limit 10 速度一样快吗？

结论：mysql查询中 limit 1000,10 会比 limit 10 更慢。原因是 limit 1000,10 会取出1000+10条数据，并抛弃前1000条，这部分耗时更大。

分析背后原理：

limit 1000,10 和limit 10,对应 limit offset, size 和 limit size 两种方式。

而其实 limit size ，相当于 limit 0, size。也就是从0开始取size条数据。

也就是说，两种方式的区别在于offset是否为0。

先来看下limit sql的内部执行逻辑。

MySQL内部分为server层和存储引擎层。一般情况下存储引擎都用innodb。

server层有很多模块，其中需要关注的是执行器是用于跟存储引擎打交道的组件。

执行器可以通过调用存储引擎提供的接口，将一行行数据取出，当这些数据完全符合要求（比如满足其他where条件），则会放到结果集中，最后返回给调用mysql的客户端。

以主键索引的limit执行过程为例：

执行select * from xxx order by id limit 0, 10;，select后面带的是星号，也就是要求获得行数据的所有字段信息。

server层会调用innodb的接口，在innodb里的主键索引中获取到第0到10条完整行数据，依次返回给server层，并放到server层的结果集中，返回给客户端。

把offset搞大点，比如执行的是：select * from xxx order by id limit 500000, 10;

server层会调用innodb的接口，由于这次的offset=500000，会在innodb里的主键索引中获取到第0到（500000 + 10）条完整行数据，返回给server层之后根据offset的值挨个抛弃，最后只留下最后面的size条，也就是10条数据，放到server层的结果集中，返回给客户端。

可以看出，当offset非0时，server层会从引擎层获取到很多无用的数据，而获取的这些无用数据都是要耗时的。

因此，mysql查询中 limit 1000,10 会比 limit 10 更慢。原因是 limit 1000,10 会取出1000+10条数据，并抛弃前1000条，这部分耗时更大。

2.深度分页怎么优化？

(什么是深度分页问题？)

select * from xxx order by id limit 500000, 10;

优化方法：

延迟关联法，就是把条件转移到主键索引树，然后减少回表。

先用子查询查出符合条件的主键，再用主键ID做条件查出所有字段。

select * from xxx  where id >=(select id from xxx order by id limit 500000, 1) order by id limit 10;

3.Mysql超大分页怎么处理？

超大分页一般都是在数据量比较大时，我们使用了limit分页查询，并且需要对数据进行排序，这个时候效率就很低，我们可以采用覆盖索引和子查询来解决

先分页查询数据的id字段，确定了id之后，再用子查询来过滤，只查询这个id列表中的数据就可以了

因为查询id的时候，走的覆盖索引，所以效率可以提升很多

4.列举一下，常用的数据库设计优化技巧？

字段尽量避免使用NULL
合理选择数据类型
字段选择合适的长度
正确使用索引
尽量少定义text类型
合理的数据表结构设计
适当的冗余设计
优化SQL查询语句
一张表的字段不宜过多

2.索引

1.什么是索引

**索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。**它可以比作一本字典的目录，可以帮你快速找到对应的记录。

2.索引的优缺点

优点：

加快数据查找的速度
为用来排序或者是分组的字段添加索引，可以加快分组和排序的速度
加快表与表之间的连接

缺点：

建立索引需要占用物理空间
会降低表的增删改的效率，因为每次对表记录进行增删改，需要进行动态维护索引，导致增删改时间变长

3.索引的作用/为什么用索引

数据是存储在磁盘上的，查询数据时，如果没有索引，会加载所有的数据到内存，依次进行检索，读取磁盘次数较多。有了索引，就不需要加载所有数据，因为B+树的高度一般在2-4层，最多只需要读取2-4次磁盘，查询速度大大提升。

4.索引数据结构

索引的数据结构主要有B+树和哈希表。MySQL的默认的存储引擎InnoDB采用的B+树的数据结构来存储索引。

1.为什么选择b+树当索引数据结构？

MySQL的默认的存储引擎InnoDB采用的B+树的数据结构来存储索引，选择B+树的主要的原因是：

1.阶数更多，路径更短；

2.磁盘读写代价B+树更低，非叶子节点只存储指针，叶子阶段存储数据

3.因为叶子节点组成一个双向链表，B+树便于扫库和区间查询

2.什么是b+树索引？

B+ 树是基于B 树和叶子节点顺序访问指针进行实现，它具有B树的平衡性，并且通过顺序访问指针来提高区间查询的性能。

在 B+ 树中，节点中的 key 从左到右递增排列。进行查找操作时，首先在根节点进行二分查找，找到key所在的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出key所对应的数据项。

3.为什么B+树比B树更适合实现数据库索引？

由于B+树的数据都存储在叶子结点中，叶子结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其非叶子结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，而在数据库中基于范围的查询是非常频繁的，所以通常B+树用于数据库索引。
B+树的节点只存储索引key值，具体信息的地址存在于叶子节点的地址中。这就使以页为单位的索引中可以存放更多的节点。减少更多的I/O支出。
B+树的查询效率更加稳定，任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

4.哈希索引

哈希索引是基于哈希表实现的，对于每一行数据，存储引擎会对索引列进行哈希计算得到哈希码，并且哈希算法要尽量保证不同的列值计算出的哈希码值是不同的，将哈希码的值作为哈希表的key值，将指向数据行的指针作为哈希表的value值。这样查找一个数据的时间复杂度就是O(1)，一般多用于精确查找。

5.Hash索引和B+树索引的区别

哈希索引不支持排序，因为哈希表是无序的。

哈希索引不支持范围查找。

哈希索引不支持模糊查询及多列索引的最左前缀匹配。

因为哈希表中会存在哈希冲突，所以哈希索引的性能是不稳定的，而B+树索引的性能是相对稳定的，每次查询都是从根节点到叶子节点。

5.什么情况下需要建索引？

经常用于查询的字段
经常用于连接的字段建立索引，可以加快连接的速度
经常需要排序的字段建立索引，因为索引已经排好序，可以加快排序查询速度

6.什么情况下不建索引？

where条件中用不到的字段不适合建立索引
表记录较少。比如只有几百条数据，没必要加索引。
需要经常增删改。需要评估是否适合加索引
参与列计算的列不适合建索引
区分度不高的字段不适合建立索引，如性别，只有男/女/未知三个值。加了索引，查询效率也不会提高。

7.索引类型

0.索引类型

按照底层存储方式角度划分：

聚簇索引（聚集索引）：索引结构和数据一起存放的索引，InnoDB 中的主键索引就属于聚簇索引。
非聚簇索引（非聚集索引）：索引结构和数据分开存放的索引，二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。

按照应用维度划分：

主键索引：加速查询 + 列值唯一（不可以有 NULL）+ 表中只有一个。
普通索引：仅加速查询。
唯一索引：加速查询 + 列值唯一（可以有 NULL）。
覆盖索引：一个索引包含（或者说覆盖）所有需要查询的字段的值。
联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。
全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。

1.聚簇索引和非聚簇索引(二级索引)

聚簇索引主要是指数据与索引放到一块，B+树的叶子节点保存了整行数据，有且只有一个，一般情况下主键在作为聚簇索引的

非聚簇索引值,又叫二级索引，数据与索引分开存储，B+树的叶子节点保存对应的主键，可以有多个，一般我们自己定义的索引都是非聚簇索引

回表查询

回表的意思就是通过二级索引找到对应的主键值，然后再通过主键值找到聚集索引中所对应的整行数据，这个过程就是回表。

如果按照二级索引查询数据的时候，返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用select *，尽量在返回的列中都包含添加索引的字段

【备注：如果面试官直接问回表，则需要先介绍聚簇索引和非聚簇索引】

2.覆盖索引和联合索引

覆盖索引：需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。

联合索引：使用表中的多个字段创建索引

8.索引的设计/创建原则？

对于经常作为查询条件的字段，应该建立索引，以提高查询速度
为经常需要排序、分组和联合操作的字段建立索引
索引列的区分度越高，索引的效果越好。比如使用性别这种区分度很低的列作为索引，效果就会很差。
避免给"大字段"建立索引。尽量使用数据量小的字段作为索引。因为MySQL在维护索引的时候是会将字段值一起维护的，那这样必然会导致索引占用更多的空间，另外在排序的时候需要花费更多的时间去对比。
尽量使用短索引，对于较长的字符串进行索引时应该指定一个较短的前缀长度，因为较小的索引涉及到的磁盘I/O较少，查询速度更快。
索引不是越多越好，每个索引都需要额外的物理空间，维护也需要花费时间。
频繁增删改的字段不要建立索引。假设某个字段频繁修改，那就意味着需要频繁的重建索引，这必然影响MySQL的性能
利用最左前缀原则。

9.什么情况索引会失效

SQL索引失效可能会导致查询速度变慢，因为数据库需要执行全表扫描而不是使用索引

导致索引失效的情况：

以%开头的like查询如%abc，无法使用索引
查询条件使用or连接，也会导致索引失效
索引在使用的时候没有遵循最左匹配法则(联合索引的匹配从where的最左边的字段开始，只有匹配成功才能继续匹配到右边的下一个字段。)
如果在添加索引的字段上进行了运算操作或者类型转换也都会导致索引失效。
当操作符左右两边的数据类型不一致时，会发生隐式转换让索引失效。

通常情况下，想要判断出这条sql是否有索引失效的情况，可以使用explain执行计划来分析

最左前缀匹配原则

最左前缀匹配原则指的是，在使用联合索引时，MySQL 会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配，如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用该字段过滤一批数据，直至联合索引中全部字段匹配完成，或者在执行过程中遇到范围查询（如 >、< ）才会停止匹配，但是对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配。所以，我们在使用联合索引时，可以将区分度高的字段放在最左边，这也可以过滤更多数据。

10.索引合并index merge

1.前言：单表查询MySQL只能利用一个索引

因为一般情况下，单表查询MySQL只能利用一个索引，比如下面这个查询，假设id是主键，a和b分别创建了索引，别天真的以为idx_a和idx_b都能发挥作用，其实不是的。

SELECT id,a,b FROM T WHERE a>100 AND b>200;

因为idx_a索引只存储了列a和id的值，无法判断b>200条件是否成立，所以只能拿着id去回表查询。同样idx_b索引只存储了列b和id的值，无法判断a>100条件是否成立，也只能拿着id去回表查询。可以看到，最大的开销其实是回表操作，通过二级索引匹配到的数据越少，回表的开销也就越低。所以理论上来说，a>100和b>200分别符合这两个条件的记录数越少，MySQL就会使用哪个索引。MySQL是如何判断符合这些条件的记录数量的呢？不也得老老实实的扫描全表吗？MySQL采用预估的方式，通过表的统计数据或访问表中少量的数据来进行预估，并分别计算使用这两个索引进行查询各自的成本是多少，最终选择执行成本更低的索引方案。

我们假设最终MySQL使用idx_a索引，那么这个查询过程其实是这样的：

InnoDB从idx_aB+树中获取到第一条a>100的记录，拿记录里的id值回表查询。
回表查询获取到完整的用户记录，判断b>200是否成立，成立则返回给客户端，否则丢弃该记录。
InnoDB继续从idx_aB+树中获取到下一条a>100的记录，重复前面的过程。

建立了这么多索引，每次查询只使用一个，太可惜了不是嘛。能不能同时利用多个索引来完成查询呢？可以的，但是条件有些严苛，这就是我们今天要介绍的索引合并Index Merge。

2.index merge索引合并作用

在MySQL中，当执行一个查询语句需要使用多个索引时，MySQL可以使用索引合并(Index Merge)来优化查询性能。具体来说，索引合并是将多个单列索引或多个联合索引合并使用，以满足查询语句的需要。

当使用索引合并时，MySQL会选择最优的索引组合来执行查询，从而避免了全表扫描和排序操作，提高了查询效率。而对于使用多个单列索引的查询语句，MySQL也可以使用索引合并来优化查询性能。

如何才能知道我们写的SQL语句使用了索引合并呢？通过EXPLAIN分析一下就知道了，如果使用了索引合并，对应的type列显示的值应该是index_merge，key列显示用的到所有索引名称，Extra列会显示具体使用了哪种类型的索引合并。

MySQL目前共支持三种类型的索引合并，分别是：

Intersection	对多个二级索引里符合条件的主键值取交集合并
Union	对多个二级索引里符合条件的主键值去重后取并集合并
Sort Union	对多个二级索引里符合条件的主键值去重并排序后，再取并集合并

3.事务相关

1.事务特性ACID

事务特性ACID：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚。
一致性是指一个事务执行之前和执行之后都必须处于一致性状态。比如a与b账户共有1000块，两人之间转账之后无论成功还是失败，它们的账户总和还是1000。
隔离性。跟隔离级别相关，如read committed，一个事务只能读到已经提交的修改。
持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

2.并发事务问题(脏读，幻读等)及4种隔离级别

事务隔离就是为了解决上面提到的脏读、不可重复读、幻读这几个问题。

3.MySQL 的隔离级别怎么实现的？

MySQL 的隔离级别基于锁和 MVCC 机制共同实现的。

可串行化隔离级别是通过锁来实现的，读已提交和可重复读隔离级别是基于 MVCC 实现的。不过，可串行化之外的其他隔离级别可能也需要用到锁机制，就比如 REPEATABLE-READ 在当前读情况下需要使用加锁读来保证不会出现幻读。

4.MySQL怎么保证事务原子性？(undo log)

在异常发生时，对已经执行的操作进行回滚，在 MySQL 中，恢复机制是通过 回滚日志undo log 实现的，所有事务进行的修改都会先记录到这个回滚日志中，然后再执行相关的操作。如果执行过程中遇到异常的话，我们直接利用 回滚日志 中的信息将数据回滚到修改之前的样子即可。

4.三大日志

bin log/redo log/undo log日志

bin log（二进制日志）和 redo log（重做日志）和 undo log（回滚日志）。

bin log二进制日志

Bin Log是MySQL的二进制日志，它记录了对数据库进行的所有更改操作，包括数据的增删改操作以及表结构的变更操作。Bin Log以二进制格式记录，可以用于主从复制、数据恢复以及数据备份等场景。

redo log重做日志

redo log是重做日志，记录的是数据修改之后的值，不管事务是否提交都会记录下来。redo log保证了事务的持久性，比如断电了，InnoDB存储引擎会使用redo log恢复到断电前的时刻，以此来保证数据的完整性。

undo log回滚日志

Undo Log是MySQL的回滚日志，它用于在事务回滚或者数据库崩溃时撤销已提交的事务对数据库的修改。Undo Log记录了每个事务对数据库所做的修改操作的逆操作，以便在需要回滚时可以恢复数据。

1.undo log 与redo log的区别

redo log日志记录的是数据页的物理变化，服务宕机可用来同步数据，而undo log 不同，它主要记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据；

redo log保证了事务的持久性，undo log保证了事务的原子性和一致性。

2.bin log和redo log有什么区别？

bin log会记录所有日志记录，包括InnoDB、MyISAM等存储引擎的日志；redo log只记录innoDB自身的事务日志。
bin log只在事务提交前写入到磁盘，一个事务只写一次；而在事务进行过程，会有redo log不断写入磁盘。
bin log是逻辑日志，记录的是SQL语句的原始逻辑；redo log是物理日志，记录的是在某个数据页上做了什么修改。

4.存储引擎

1.常见存储引擎

MySQL中常用的四种存储引擎分别是： MyISAM、InnoDB、MEMORY、ARCHIVE。

MySQL 当前默认的存储引擎是 InnoDB。并且，所有的存储引擎中只有 InnoDB 是事务性存储引擎，也就是说只有 InnoDB 支持事务。

InnoDB存储引擎

InnoDB是MySQL默认的事务型存储引擎，使用最广泛，基于聚簇索引建立的。InnoDB内部做了很多优化，如能够自动在内存中创建自适应hash索引，以加速读操作。

优点：支持事务和崩溃修复能力；引入了行级锁和外键约束。

缺点：占用的数据空间相对较大。

适用场景：需要事务支持，并且有较高的并发读写频率。

2.MyISAM 和 InnoDB 有什么区别？

可移植性、备份及恢复。MyISAM数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。对于InnoDB，可行的方案是拷贝数据文件、备份 binlog，或者用mysqldump，在数据量达到几十G的时候就相对麻烦了。
是否支持行级锁。MyISAM 只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。而InnoDB 支持行级锁和表级锁，默认为行级锁。行锁大幅度提高了多用户并发操作的性能。
是否支持事务。 MyISAM 不提供事务支持。而InnoDB 提供事务支持，具有事务、回滚和崩溃修复能力。
是否支持外键。MyISAM不支持，而InnoDB支持。
是否支持MVCC。MyISAM不支持，InnoDB支持。应对高并发事务，MVCC比单纯的加锁更高效。
是否支持聚集索引。MyISAM不支持聚集索引，InnoDB支持聚集索引。

5.Mysql的锁

1.行级锁、表级锁和页级锁

按锁粒度分类，有行级锁、表级锁和页级锁。

行级锁是mysql中锁定粒度最细的一种锁。是 针对索引字段加的锁 ，只针对当前操作的行记录进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。
表级锁是mysql中锁定粒度最大的一种锁，是针对非索引字段加的锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。不过，触发锁冲突的概率最高，高并发下效率极低。
页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。因此，采取了折衷的页级锁，一次锁定相邻的一组记录。

MyISAM 仅仅支持表级锁(table-level locking)，一锁就锁整张表，这在并发写的情况下性非常差。InnoDB 不光支持表级锁(table-level locking)，还支持行级锁(row-level locking)，默认为行级

2.行级锁使用的注意事项

InnoDB 的行锁是针对索引字段加的锁，表级锁是针对非索引字段加的锁。当我们执行 UPDATE、DELETE 语句时，如果 WHERE条件中字段没有命中唯一索引或者索引失效的话，就会导致扫描全表对表中的所有行记录进行加锁。这个在我们日常工作开发中经常会遇到，一定要多多注意！！！

3.行锁的类型

行锁的类型主要有三类：

Record Lock，记录锁，也就是仅仅把一条记录锁上；
Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；
Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

4.共享锁、排他锁、意向锁

按锁级别分类，有共享锁、排他锁和意向锁。

共享锁（S 锁）：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。

排他锁（X 锁）：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁（锁不兼容）。

5.意向锁是什么有什么用

如果需要用到表锁的话，我们需要用到做意向锁快速判断是否可以对某个表使用表锁。

意向锁是表级锁，共有两种：

意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些记录加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。
意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。

意向锁是由数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享/排他锁之前，InooDB 会先获取该数据行所在在数据表的对应意向锁。

6.当前读和快照读有什么区别？

表记录有两种读取方式。

快照读

快照读(一致性非锁定读)：读取的是快照版本，读写不冲突。普通的SELECT就是快照读。通过mvcc来进行并发控制的，不用加锁。

只有在事务隔离级别为读已提交和可重复读下，InnoDB 才会使用快照读锁定读：

在读已提交级别下，对于快照数据，快照读总是读取被锁定行的最新一份快照数据，和当前读读取的数据是一样的，都是最新的。
在可重复读级别下，对于快照数据，快照读总是读取本事务开始时的行数据版本，有可能读取的不是最新的数据。

mysql中的快照读是通过MVCC+undolog实现的。

当前读

当前读(一致性锁定读)：读取的是最新版本，但是读的时候不允许写，写的时候也不允许读。UPDATE、DELETE、INSERT`是当前读。就是给行记录加共享锁或排他锁。

Mysql实现当前读是通过共享锁+排他锁+Next-Key Lock(一种行锁)实现的。每次对行数据进行读取的时候，加共享锁。此时就不允许修改，但是允许其他事务读取，所以每次都可以读到最新的数据。每次对行数据进行修改的时候，加排他锁，不允许其他事务读取和修改。这种情况下其他事务读取的数据也一定是最新的数据。每次对范围行数据进行读取的时候，对这个范围加一个范围共享锁。每次对范围行数据进行修改的时候，读这个范围加一个范围排它锁。基于上述锁机制，实现当前读，确保每次读取的都是最新的数据。

7.MVCC

1.什么是MVCC

MVCC（Multiversion concurrency control多版本并发控制)一种并发控制的方法，用于在多个并发事务同时读写数据库时保持数据的一致性和隔离性。它是通过在每个数据行上维护多个版本的数据来实现的。当一个事务要对数据库中的数据进行修改时，MVCC 会为该事务创建一个数据快照，而不是直接修改实际的数据行。

2.MVCC原理

InnoDB 的MVCC是通过 read view 和版本链实现的，版本链保存有历史版本记录，通过read view 判断当前版本的数据是否可见，如果不可见，再从版本链中找到上一个版本，继续进行判断，直到找到一个可见的版本。

版本链

MVCC 的实现依赖于版本链，版本链是通过表的三个隐藏字段实现。

DB_TRX_ID：当前事务id，通过事务id的大小判断事务的时间顺序。
DB_ROLL_PTR：回滚指针，指向当前行记录的上一个版本，通过这个指针将数据的多个版本连接在一起构成undo log版本链。
DB_ROW_ID：主键，如果数据表没有主键，InnoDB会自动生成主键。

使用事务更新行记录的时候，就会生成版本链，执行过程如下：

用排他锁锁住该行；
将该行原本的值拷贝到undo log，作为旧版本用于回滚；
修改当前行的值，生成一个新版本，更新事务id，使回滚指针指向旧版本的记录，这样就形成一条版本链。

read view

read view可以理解成将数据在每个时刻的状态拍成“照片”记录下来。在获取某时刻t的数据时，到t时间点拍的“照片”上取数据。

在read view内部维护一个活跃事务链表，表示生成read view的时候还在活跃的事务。这个链表包含在创建read view之前还未提交的事务，不包含创建read view之后提交的事务。

不同隔离级别创建read view的时机不同。

读已提交级别：每次执行select都会创建新的read_view，保证能读取到其他事务已经提交的修改。
可重复读级别：在一个事务范围内，第一次select时更新这个read_view，以后不会再更新，后续所有的select都是复用之前的read_view。这样可以保证事务范围内每次读取的内容都一样，即可重复读。

read view的记录筛选方式

前提：DATA_TRX_ID 表示每个数据行的最新的事务ID；up_limit_id表示当前快照中的最先开始的事务；low_limit_id表示当前快照中的最慢开始的事务，即最后一个事务。

如果DATA_TRX_ID < up_limit_id：说明在创建read view时，修改该数据行的事务已提交，该版本的记录可被当前事务读取到。

如果DATA_TRX_ID >= low_limit_id：说明当前版本的记录的事务是在创建read view之后生成的，该版本的数据行不可以被当前事务访问。此时需要通过版本链找到上一个版本，然后重新判断该版本的记录对当前事务的可见性。

如果up_limit_id <= DATA_TRX_ID < low_limit_i：

需要在活跃事务链表中查找是否存在ID为DATA_TRX_ID的值的事务。
如果存在，因为在活跃事务链表中的事务是未提交的，所以该记录是不可见的。此时需要通过版本链找到上一个版本，然后重新判断该版本的可见性。
如果不存在，说明事务trx_id 已经提交了，这行记录是可见的。

5.读写分离与主从同步

1.什么是读写分离？

读写分离主要是为了将对数据库的读写操作分散到不同的数据库节点上。 这样的话，就能够小幅提升写性能，大幅提升读性能。

2.怎么读写分离？

不论是使用哪一种读写分离具体的实现方案，想要实现读写分离一般包含如下几步：

部署多台数据库，选择其中的一台作为主数据库，其他的一台或者多台作为从数据库。
保证主数据库和从数据库之间的数据是实时同步的，这个过程也就是我们常说的主从复制。
系统将写请求交给主数据库处理，读请求交给从数据库处理。

落实到项目本身的话，可以使用组件Sharding-JDBC。

Sharding-JDBC

Sharding-JDBC定位为轻量级java框架，在java的JDBC层提供的额外服务。它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。使用Sharding-JDBC可以在程序中轻松的实现数据库读写分离,优点在于数据源完全有Sharding托管，写操作自动执行master库，读操作自动执行slave库。不需要程序员在程序中关注这个实现了。

3.主从同步原理

主从同步:当master(主)库的数据发生变化的时候，变化会实时的同步到slave(从)库。

步骤如下：

第一：主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中。

第二：从库读取主库的二进制日志文件 Binlog ，写入到从库的中继日志 Relay Log 。

第三：从库重做中继日志中的事件，在slave库上做相应的更改。

6.分库分表

如果 MySQL 一张表的数据量过大怎么办?

换言之，我们该如何解决 MySQL 的存储压力呢？

答案之一就是 分库分表。

1.什么是分库和分表

分库就是将数据库中的数据分散到不同的数据库上，可以垂直分库，也可以水平分库。

分表就是对单表的数据进行拆分，可以是垂直拆分，也可以是水平拆分

2.两种数据拆分策略

数据切分可以分为两种方式：垂直划分和水平划分。

垂直划分

垂直划分数据库是根据业务进行划分，例如购物场景，可以将库中涉及商品、订单、用户的表分别划分出成一个库，通过降低单库的大小来提高性能。同样的，分表的情况就是将一个大表根据业务功能拆分成一个个子表，例如商品基本信息和商品描述，商品基本信息一般会展示在商品列表，商品描述在商品详情页，可以将商品基本信息和商品描述拆分成两张表。

优点：行记录变小，数据页可以存放更多记录，在查询时减少I/O次数。

缺点：

主键出现冗余，需要管理冗余列；
会引起表连接JOIN操作，可以通过在业务服务器上进行join来减少数据库压力；
依然存在单表数据量过大的问题。

水平划分

水平划分是根据一定规则，例如时间或id序列值等进行数据的拆分。比如根据年份来拆分不同的数据库。每个数据库结构一致，但是数据得以拆分，从而提升性能。

优点：单库（表）的数据量得以减少，提高性能；切分出的表结构相同，程序改动较少。

缺点：

分片事务一致性难以解决
跨节点join性能差，逻辑复杂
数据分片在扩容时需要迁移

2.分库分表的4种策略

垂直分库：以表为依据，根据业务将不同表拆分到不同库中。特点：按业务对数据分级管理、维护、监控、扩展在高并发下，提高磁盘IO和数据量连接数

垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中。特点：1，冷热数据分离2，减少IO过渡争抢，两表互不影响

水平分库：将一个库的数据拆分到多个库中。特点：解决了单库大数量，高并发的性能瓶颈问题提高了系统的稳定性和可用性

水平分表：将一个表的数据拆分到多个表中(可以在同一个库内)。特点：优化单一表数据量过大而产生的性能问题;避免IO争抢并减少锁表的几率;

3.什么情况下需要分库分表？

遇到下面几种场景可以考虑分库分表：

单表的数据达到千万级别以上，数据库读写速度比较缓慢。
数据库中的数据占用的空间越来越大，备份时间越来越长。
应用的并发量太大。

4.分库分表的使用方案

ShardingSphere 绝对可以说是当前分库分表的首选！ShardingSphere 的功能完善，除了支持读写分离和分库分表，还提供分布式事务、数据库治理、影子库、数据加密和脱敏等功能.

5.分库分表后，数据怎么迁移呢？

分库分表之后，我们如何将老库（单库单表）的数据迁移到新库（分库分表后的数据库系统）呢？

比较简单同时也是非常常用的方案就是停机迁移，写个脚本老库的数据写到新库中。比如你在凌晨 2 点，系统使用的人数非常少的时候，挂一个公告说系统要维护升级预计 1 小时。然后，你写一个脚本将老库的数据都同步到新库中。

如果你不想停机迁移数据的话，也可以考虑双写方案。双写方案是针对那种不能停机迁移的场景，实现起来要稍微麻烦一些。具体原理是这样的：

我们对老库的更新操作（增删改），同时也要写入新库（双写）。如果操作的数据不存在于新库的话，需要插入到新库中。这样就能保证，咱们新库里的数据是最新的。
在迁移过程，双写只会让被更新操作过的老库中的数据同步到新库，我们还需要自己写脚本将老库中的数据和新库的数据做比对。如果新库中没有，那咱们就把数据插入到新库。如果新库有，旧库没有，就把新库对应的数据删除（冗余数据清理）。
重复上一步的操作，直到老库和新库的数据一致为止。

想要在项目中实施双写还是比较麻烦的，很容易会出现问题。我们可以借助上面提到的数据库同步工具 Canal 做增量数据迁移（还是依赖 binlog，开发和维护成本较低）。

#晒一晒我的offer##华为开奖那些事##原神启动#

Java抽象带蓝子的笔记专栏文章被收录于专栏

我的笔记专栏，内有自己整理的八股知识笔记和算法刷题笔记，我会不断通过他人和自己的面经来更新和完善自己的八股笔记。专栏每增加一篇文章费用就会上涨一点，如果你喜欢的话建议你尽早订阅。