MySQL

索引

帮助MySQL高效获取数据的数据结构，主要是用来提高数据检索的效率，降低数据库的IO成本，同时通过索引列对数据进行排序，降低数据排序的成本，也能降低了CPU的消耗

索引的底层数据结构

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

第一、阶数更多，路径更短

第二、磁盘读写代价B+树更低，非叶子节点只存储指针，叶子节点存储数据

第三、B+树便于扫库和区间查询，叶子节点是一个双向链表

B树和B+树的区别

第一：在B树中，非叶子节点和叶子节点都会存放数据，而B+树的所有的数据都会出现在叶子节点，在查询的时候，B+树查找效率更加稳定

第二：在进行范围查询的时候，B+树效率更高，因为B+树都在叶子节点存储，并且叶子节点是一个双向链表

聚簇索引与非聚簇索引

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

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

回表查询

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

• 当你使用二级索引进行查询时，是否需要回表查询取决于几个因素：
  • 如果查询只需要二级索引中包含的数据（例如，索引覆盖了查询所需的所有列），那么无需回表查询。
  • 如果查询所需的某些列不在二级索引中，那么数据库就需要进行回表查询来获取完整数据。

覆盖索引

覆盖索引是指select查询语句使用了索引，在返回的列，必须在索引中全部能够找到，如果我们使用id查询，它会直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。

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

索引创建原则

以下原则还需要根据具体情况调整

1. 选择性原则：
   • 创建索引的列应该是那些在查询条件（如 WHERE 子句）中出现频率较高的列，并且这些列的值应当尽可能地独特。这样可以提高索引的筛选效果，减少搜索范围。
2. 频繁访问原则：
   • 对于经常出现在 SELECT 查询中的列，尤其是那些在 JOIN 或 GROUP BY 子句中使用的列，考虑创建索引以加速查询速度。
3. 联合索引原则：
   • 当一个查询涉及到多个列时，可以考虑创建一个包含这些列的联合索引。联合索引的设计应该考虑最常用查询的条件顺序，即最左边的列应该是最常用于过滤的列。
4. 最左前缀规则：
   • 联合索引的使用遵循最左前缀规则，即查询条件必须从索引的最左边开始连续匹配，才能利用索引。
5. 索引覆盖原则：
   • 创建索引时考虑查询所需的所有列是否都被索引覆盖。如果查询所需的数据全部包含在索引中，则可以避免回表查询（即直接从索引中读取数据而无需访问表中的数据行）。
6. 避免表达式使用原则：
   • 避免在索引列上使用函数或表达式，因为这样做会使索引失效，从而无法利用索引加速查询。
7. 更新频率原则：
   • 如果某列频繁被修改，那么为该列创建索引可能不是一个好主意，因为每次更新都会带来额外的索引维护成本。
8. 索引数量原则：
   • 尽管索引能提高查询性能，但过多的索引也会增加插入、更新和删除操作的开销。因此，需要权衡索引的数量与性能的关系。

索引失效常见原因

1. 不满足最左前缀原则
2. 范围索引没有放最后
3. 索引列上有计算
4. 索引列上使用了函数
5. 字符类型没加引号
6. 模糊（like）查询左边有%
7. 使用了select *

事务

事务的特性

ACID，分别指的是：原子性、一致性、隔离性、持久性；举个例子：

A向B转账500，转账成功，A扣除500元，B增加500元，原子操作体现在要么都成功，要么都失败

在转账的过程中，数据要一致，A扣除了500，B必须增加500

在转账的过程中，隔离性体现在A像B转账，不能受其他事务干扰

在转账的过程中，持久性体现在事务提交后，要把数据持久化（可以说是落盘操作）

并发事务带来的问题

第一是脏读， 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

​ 第二是不可重复读：比如在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

​ 第三是幻读（Phantom read）：幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

事务中的隔离性（MVCC）

事务的隔离性是由锁和mvcc实现的。

mvcc的意思是多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，它的底层实现主要是分为了三个部分：

​ 第一个是隐藏字段

​ 第二个是undo log日志

​ 第三个是readView读视图

隐藏字段：在mysql中给每个表都设置了隐藏字段，有一个是trx_id(事务id)，记录每一次操作的事务id，是自增的；另一个字段是roll_pointer(回滚指针)，指向上一个版本的事务版本记录地址

undo log 日志：主要的作用是记录回滚日志，存储老版本数据，在内部会形成一个版本链，在多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过roll_pointer指针形成一个链表

readView：解决的是一个事务查询选择版本的问题，在内部定义了一些匹配规则和当前的一些事务id判断该访问那个版本的数据，不同的隔离级别快照读是不一样的，最终的访问的结果不一样。如果是rc（读已提交）隔离级别，每一次执行快照读时生成ReadView，如果是rr（可重复读）隔离级别仅在事务中第一次执行快照读时生成ReadView，后续复用

MySQL的隔离级别

MySQL支持四种隔离级别，分别有：

​ 第一个是未提交读（read uncommitted）它解决不了刚才提出的所有问题，一般项目中也不用这个。

​ 第二个是读已提交（read committed）它能解决脏读的问题的，但是解决不了不可重复读和幻读。

​ 第三个是可重复读（repeatable read）它能解决脏读和不可重复读，但是解决不了幻读，这个也是mysql默认的隔离级别。

​ 第四个是串行化（serializable）它可以解决刚才提出来的所有问题，但是由于让是事务串行执行的，性能比较低。所以，我们一般使用的都是mysql默认的隔离级别:可重复读

undo log和redo log的区别

redo log日志记录的是数据页的物理变化，服务宕机可用来同步数据

undo log 不同，它主要记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据

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

MySQL主从同步原理

MySQL主从复制的核心就是二进制日志(DDL（数据定义语言）语句和 DML（数据操纵语言）语句)，它的步骤是这样的：

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

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

第三：从库重做中继日志中的事件，将改变反映它自己的数据

分库分表

分库分表的目的

1. 提高性能：通过将数据分散到多个数据库或表中，可以减少单个数据库的负载，从而提高查询和写入操作的速度。
2. 增强可扩展性：随着业务的增长，单一数据库难以支撑大规模的数据存储需求，分库分表可以让系统更容易地水平扩展。
3. 提高可用性：即使其中一个数据库出现问题，其它数据库仍然可以正常工作，从而提高系统的容错能力和可用性。

分库分表的方式

1. 水平分表（Horizontal Sharding）

• 按数据范围划分：例如，可以根据用户的ID或者订单的ID来划分数据，将不同的范围的数据存放在不同的表中。
• 按模运算划分：比如，可以将用户ID对某个数取模后，根据结果分配到不同的表中。
• 按时间段划分：例如，根据数据产生的日期，将不同时间段的数据存储在不同的表中。

2. 垂直分表（Vertical Sharding）

非常常见

• 按功能模块划分：将不同的业务逻辑对应的数据存储在不同的表中，比如用户信息和订单信息分别存储在不同的表或库中。
• 按字段划分：将一个表中的字段拆分到多个表中，每个表存储一部分字段，这样可以减少单个表的宽度，提高查询效率。

3. 水平分库（Horizontal Partitioning）

• 按数据范围划分：类似于水平分表，但这里是将数据分配到不同的数据库实例中。
• 按模运算划分：根据某种算法（如取模运算）将数据分散到不同的数据库中。
• 按时间段划分：根据数据产生的日期将数据存储在不同的数据库中。