MySQL学习笔记-CSDN博客

本文链接：https://blog.csdn.net/PaulHoldOn/article/details/128968804

文章目录

1. MySQL中，如何定位慢查询?
2. 定位到慢查询后，如何优化呢？
3. 什么是索引？
- 3.1 索引的底层数据结构了解过嘛？
- 3.2 B+ 树和 B 树的区别？
4. 什么是聚簇索引（聚集索引）和非聚簇索引（二级索引）？
- 4.1 什么是回表查询？
- 4.2 索引有哪些？
5. 什么是覆盖索引？
- 5.1 MySQL 超大分页怎么处理？
6. 创建索引需要注意什么？
7. 什么情况下索引会失效？（使用索引需要注意什么？答：要避免出现索引失效的情况）
8. SQL 优化的经验
- 8.1 建表的时候，如何优化？
- 8.2 用索引的时候，如何优化？
- 8.3 SQL 语句，如何优化？
9. 什么是事务？事务的四大特性ACID是什么？
10. 并发事务所引发的问题
- 10.1 怎么解决并发事务所引发的问题？(事务隔离级别)
- 10.2 InnoDB 有哪几类行锁？如何解决幻读？
11. 三大日志，解决事务什么问题？
12. 什么是 MVCC，用来做什么？（事务的隔离性是如何保证的？)
13. MySQL 主从同步原理
14. MySQL 的分库分表
15. InnoDB 存储引擎与 MyISAM 的区别 ?
16. 三大范式是什么？
17. MySQL 执行一条更新语句是怎么执行的？
18. 什么是 ER 图？
19. 数据库设计通常分为哪几步?
20. MySQL 的内连接、外连接有什么区别？
21. char 与 varchar 的区别？
22. MySQL 怎么存储 emoji😊?

1. MySQL中，如何定位慢查询?

慢查询就是接口响应时间比较长。如果是 SQL 的问题，就要去定位这些 SQL。
第一种方式：MySQL 中提供了慢查询日志的功能。可以在 MySQL 配置文件中开启慢查询日志，设置 SQL 执行超过多少秒，就记录到慢查询日志中。
第二种方式：可以借助一些运维工具：如 Skywalking 和 Prometheus 。图形化的界面比较直观，可以看到是哪个接口比较慢，SQL 的执行时间，定位是哪个 SQL 出了问题。

2. 定位到慢查询后，如何优化呢？

通过 explain 命令查看 SQL 的执行计划。
- 可以通过 key 和 key_len 看有没有用到索引，判断索引是否失效。
- 可以通过 type 属性查看 SQL 的执行效率，看下是否走了全表扫描，如果 type 属性是 all 的话，可以加索引。
- 可以通过 extra 属性判断是否出现了回表，如果出现了，可以加索引。

3. 什么是索引？

索引是一种用来高效查询数据的有序的数据结构，索引的作用相当于书的目录。
没有使用索引查询就比较慢，因为没有用到索引走的是全表扫描，查一条数据从表的第一行开始遍历，遍历到最后一行，效率就比较低。
用了索引，就会形成一个索引的数据结构，底层是 B+树，效率比较高。
但是使用索引也会带来一些问题，所以索引不是越多越好的。使用索引会占用一定的磁盘空间，虽然使用索引让查询变快了，但是对增删改的效率会有影响。

3.1 索引的底层数据结构了解过嘛？

MySQL 默认的存储引擎 InnoDB 采用的是 B+ 树作为索引。

为什么使用B+树？

选择 B+ 树的主要原因是：
- B+ 树查询磁盘 I/O 次数更少，非叶子节点只存储索引，叶子节点存储数据。
- B+ 树支持范围查询，叶子节点是一个双向链表。

为什么不选择二叉树？

有一种特殊的二叉树，叫二叉查找树，它有一个特点：父节点左边的都比它小，右边的都比它大，可能会出现单支树的情况，只有左子树或者只有右子树，这时候二叉树就退化成链表了，查询的时候又变成了全表扫描。所以为了避免这种情况的发生，后面就有了平衡二叉树。

为什么不选择平衡二叉树？

平衡二叉树本身是二叉查找树，左右子树高度绝对值之差不超过1，就是为了避免退化成链表的情况。但是平衡二叉树每个节点只存放一个数据，B+ 树可以存放更多的数据，树高也更低，磁盘 I/O 的次数更少，所以B+树更合适。

3.2 B+ 树和 B 树的区别？

B+ 树是对 B 树的升级。B+ 树与 B 树的区别，主要是以下这几点：
B+ 树非叶子节点存放索引，这些索引也会同时会出现在叶子节点中，并且叶子节点会存放实际的数据，叶子节点之间构成一个有序链表。
B+ 树查询磁盘 I/O次数更少。B+ 树的非叶子节点不存放数据，仅存放索引，因此相比 B 树，B+ 树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更矮胖。
B+ 树插入和删除效率更高。B+ 树删除一个节点的时候，可以直接从叶子节点中删除。
B+ 树支持范围查询。比如我们想知道 12 月 1 日和 12 月 12 日之间的订单，这个时候可以先查找到 12 月 1 日所在的叶子节点，然后利用链表向右遍历，直到找到 12 月12 日的节点。
MySQL 中索引的数据结构就是采用了 B+ 树，B+ 树结构如下图：

4. 什么是聚簇索引（聚集索引）和非聚簇索引（二级索引）？

聚集索引 (Clustered Index)：叶子节点保存行数据。一般主键索引就是聚集索引。
二级索引 (Secondary Index)：叶子节点关联的是对应的主键值。

4.1 什么是回表查询？

先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，最终拿到这一行的数据。

4.2 索引有哪些？

主键索引：主键默认就是主键索引，一个表只能有一个主键。
唯一索引 (UNIQUE)：数据列不可以重复，一个表可以多个列创建唯一主键。
普通索引 (INDEX)
单列索引：一个索引只包含单个列。
联合索引：一个索引包含了多个列。
聚集索引 (Clustered Index)：叶子节点保存行数据。
二级索引 (Secondary Index)：叶子节点关联的是对应的主键。

5. 什么是覆盖索引？

覆盖索引是指 select 查询语句中使用了索引，查询的列必须在索引中要能找得到。如果查询的有的列没有索引，就会出现回表查询。所以一般查什么就 select 什么，不要使用select *。
在这里插入图片描述

5.1 MySQL 超大分页怎么处理？

超大分页就是数据量比较大的时候作分页。因为 limit 分页查询，要对数据做排序，效率会比较低。我们可以用覆盖索引和子查询来作超大分页。

先用覆盖索引，查 id
再用子查询，通过 id 来过滤

6. 创建索引需要注意什么？

对数据和查询量比较大的表建索引。
建索引的列，是查询比较频繁的字段，一般是对 order by， group by 、where 操作的字段建索引。
尽量使用联合索引，减少单列索引，避免回表。
索引也不能建太多，索引越多，维护索引的成本就越大。对磁盘占用、虽然提高了查询效率，但是对增删改效率有影响。

7. 什么情况下索引会失效？（使用索引需要注意什么？答：要避免出现索引失效的情况）

使用索引要遵守最左前缀法则。最左前缀法则说的是联合索引，就是最左边的列必须要存在，不能跳过其中一列，如果跳过了某一列，后面的字段索引会失效。
范围查询时：联合索引中，出现 > 或者 < 的范围查询，右侧的列索引失效。
- 如何解决：用 >= 或者 <= 。
在索引列上进行函数运算操作，索引失效。
对于字符串，不加引号，索引失效。
模糊查询，如果是头部模糊匹配，索引失效。
or 连接的条件，如果 or 前的条件中的列有索引，而后面的列中没有索引，索引失效
如果使用索引比全表扫描更慢，则不使用索引。

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

8. SQL 优化的经验

SQL 优化可以在这几个方面进行优化：

建表的时候对表的优化。
可以使用索引。
对 SQL 语句的优化。
可以对数据库作主从复制，读写分离。
数据量比较大的时候，可以分库分表。

8.1 建表的时候，如何优化？

对字段要选择合适的类型，比如对字符串，可以根据实际情况使用 char 或者 varchar。

8.2 用索引的时候，如何优化？

参考6和7。

8.3 SQL 语句，如何优化？

不要直接使用 select * ，要使用 select + 具体字段，避免回表。
注意写 SQL 时索引失效的情况。

9. 什么是事务？事务的四大特性ACID是什么？

事务是一组操作的集合，这些操作要么同时成功，要么同时失败。（例子：银行转账）

事务的四大特性ACID：

原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
一致性（Consistency）：事务完成时，所有的数据都保持一致状态。
隔离性（Isolation）：有多个并发的事务，事务A和事务B都在操作数据库，事务A和事务B在操作的时候互不干扰。
持久性（Durability）：事务一旦提交或回滚，数据库中的数据的改变就是永久的。

10. 并发事务所引发的问题

脏读：一个事务读到另外一个事务还没有提交的数据。
不可重复读：一个事务先后读取同一条记录，但两次读取的数据不一样。
幻读：一个事务查询数据时，没有查到，但是在插入数据时，又发现数据已经存在。

10.1 怎么解决并发事务所引发的问题？(事务隔离级别)

隔离级别	脏读	不可重复读	幻读
读未提交	√	√	√
读已提交	×	√	√
可重复读(MySQL默认)	×	×	√
串行化	×	×	×

事务隔离级别越高，数据越安全，但是性能越低,一般采用默认的隔离级别。

10.2 InnoDB 有哪几类行锁？如何解决幻读？

InnoDB 支持三种行锁定方式：

记录锁（Record Lock）：锁一整行记录。
间隙锁（Gap Lock）：锁定一个范围，不包括记录本身。
临键锁（Next-Key Lock）：Record Lock+Gap Lock，锁定一个范围，包含记录本身，主要目的是为了解决幻读问题。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。也就是说执行 insert 操作的时候需要依赖 Next-Key Lock 临键锁（Record Lock+Gap Lock）进行加锁来保证不出现幻读。

如何解决幻读？

举个例子：执行 delete 和 update 操作的时候，可以直接对记录加锁，保证事务安全。而执行 insert 操作的时候，由于记录锁（Record Lock）只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁（Gap Lock）。也就是说执行 insert 操作的时候需要依赖 Next-Key Lock 临键锁（Record Lock+Gap Lock）进行加锁来保证不出现幻读。

11. 三大日志，解决事务什么问题？

MySQL 里面的三大日志是 binlog、 undolog、redolog。

binlog (二进制日志)，主要用于数据备份和主从复制，保证事务的一致性。
undo log (回滚日志)，记录的是数据的历史版本信息，相当于一个快照，保证事务的原子性。主要用于事务回滚和 MVCC。
redo log (重做日志) 用于故障恢复的时候恢复数据，保证的是事务的持久性。

12. 什么是 MVCC，用来做什么？（事务的隔离性是如何保证的？)

MVCC 是多版本并发控制，主要是用来提高数据库的读写性能。让数据库在读写的时候不用去加锁。
MVCC处理的是快照读。有2个概念，一个是快照读，一个是当前读。
- 快照读就是简单的 select 语句，读取的是数据的历史版本信息。
- 当前读指的是悲观锁，每次读取数据的时候都要加锁。
MVCC 底层是由三部分组成，隐藏字段，undolog 和 readview。
- 隐藏字段：用于不可见的字段，比如事务的id，回滚的指针。
- undolog：执行增删改操作时候产生的方便回滚的日志。
- readview（读视图）：有了 readview ，在访问某条记录时，根据可见性算法，判断事务能不能访问这条数据。