Mysql - 持续更新

常见引擎

InnoDB

• InnoDB是MySQL的默认存储引擎，它支持事务、行级锁定和外键约束等功能。
• 支持 MVCC（多版本并发控制）机制，提高了读写并发性。
• 支持行级锁定，提高并发读写性能。
• 数据和索引存储在同一个表空间中，占用空间较大。
• 不支持全文索引。

MyISAM

• 不支持事务和外键约束。
• 使用表级锁定，不支持行级锁，并发性较低。
• 数据和索引分开存储，占用空间较小。
• 支持全文索引。

Memory

• 所有数据存储在内存中，访问速度快。
• 不支持 BLOB 和 TEXT 等大字段类型。
• 只支持表级锁定。
• 服务重启或宕机后数据会丢失。

Archive

• 专门用于存储和压缩大量历史数据。
• 只支持 INSERT 和 SELECT 操作，不支持 UPDATE 和 DELETE。
• 数据以压缩格式存储，节省存储空间。
• 不支持索引，查询效率较低。

=>总结：

InnoDB 是 MySQL 中最常用的存储引擎，它提供了事务支持、外键约束等高级特性。MyISAM 则更适合于读密集型应用。Memory 适合于临时数据存储，而 Archive 则适合于存储和压缩大量历史数据。

B树与B+树

B树

• 为了解决降低树的高度的问题，后面就出来了 B 树，它不再限制一个节点就只能有 2 个子节点，而是允许 M 个子节点 (M>2，M 称为 B 树的阶)，从而降低树的高度
• 每个节点最多有M-1个数据
  
  在上树中若需查找叶子节点s数据需要3次磁盘I/O操作
• B 树的每个节点都包含数据（索引+记录）
• 在我们查询位于底层的 A 记录的过程中（如索引1），「非 A 记录节点」里的记录数据（索引4、2里的数据）会从磁盘加载到内存，但是这些记录数据是没用的，我们只是想读取这些节点的索引数据来做比较查询，而「非 A 记录节点」里的记录数据对我们是没用的，这样不仅增多磁盘 I/O 操作次数，也占用内存资源。
• 使用 B 树来做范围查询的话，需要使用中序遍历，这会涉及多个节点的磁盘 I/O 问题，从而导致整体速度下降。

B+树

• 叶子节点才会存放实际数据，非叶子节点只会存放索引
• 所有索引都会在叶子节点出现，叶子节点之间构成一个有序链表，所以对于范围查询很方便，先找到最小的数再向右遍历即可

Innodb 里的 B+ 树

• B+树的叶子节点之间是用双向链表进行连接，这样的好处是既能向右遍历，也能向左遍历
• B+树点节点内容是数据页，数据页里存放了用户的记录以及各种信息，每个数据页默认大小是 16 KB

• 根据索引类型不同，分为聚集和二级索引。聚集索引的叶子节点存放的是实际数据，所有完整的用户记录都存放在聚集索引的叶子节点，而二级索引的叶子节点存放的是主键值（二级索引的 B+ 树的叶子节点包含「索引值+主键值」），而不是实际数据。聚簇索引只能有一个，而二级索引可以创建多个。

MyISAM中的B+树

B+树主键索引的叶子节点并不存储数据，而是存储数据的地址；二级索引也使用 B+ 树存储,叶子节点存储的是主键值

=>总结：

B 树每个节点都存储键和数据，B+ 树只有叶子节点存储数据，叶子节点之间通过指针连接，形成一个链表，方便进行范围查询而 B 树要实现范围查询，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 I/O 操作，范围查询效率不如 B+ 树。B+ 树通常比 B 树更矮（B+树的非叶子节点只存储索引键值信息,使得每个节点能够存储更多的索引项,从而降低了树的高度），查询时IO次数更少。

事务

事务是数据库管理系统执行的一个程序执行单元。它具有以下四个重要特性（ACID）：原子性、一致性、隔离性、持久性。

事务可能会出现的问题

1. 脏读：读到了另一个未提交事务修改过的数据
2. 不可重复读：前后两次读到的数据是不一致
3. 幻读：前后两次查询到的记录数量不一样的情况

隔离级别

1. 读未提交：事务还没提交时，它做的变更就能被其他事务看到
2. 读已提交：一个事务提交之后，它做的变更才能被其他事务看到
3. 可重复读：一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的（InnoDB的默认隔离级别）
4. 串行化

读已提交隔离级别是在每个语句执行前都会重新生成一个 Read View，而可重复读隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View。

read view：
，

聚簇索引记录中都包含下面两个隐藏列：

• trx_id，当一个事务对某条聚簇索引记录进行改动时，就会把该事务的事务 id 记录在 trx_id 隐藏列里；
• roll_pointer，每次对某条聚簇索引记录进行改动时，都会把旧版本的记录写入到 undo 日志中，然后这个隐藏列是个指针，指向每一个旧版本记录，于是就可以通过它找到修改前的记录。
  

如果记录的 trx_id 值大于等于 Read View 中的 max_trx_id 值，表示这个版本的记录是在创建 Read View 后才启动的事务生成的，所以该版本的记录对当前事务不可见。如果记录的 trx_id 值在 Read View 的 min_trx_id 和 max_trx_id 之间，需要判断 trx_id 是否在 m_ids 列表中：若在则不可见（如上图事务B无法看到修改后的数据：2000000）

索引

索引分类

• 按物理存储分类

  • 聚簇索引（主键索引）
  • 二级索引（辅助索引）

• 按字段特性分类

  • 主键索引
  • 唯一索引——唯一索引建立在 UNIQUE 字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值。
  • 普通索引
  • 前缀索引——是指对字符类型字段的前几个字符建立，可以建立在字段类型为 char、 varchar、binary、varbinary 的列上

• 按字段个数分

  • 单列索引
  • 联合索引

索引创建

在创建表时，InnoDB 存储引擎会根据不同的场景选择不同的列作为索引：

• 如果有主键，默认会使用主键作为聚簇索引的索引键；
• 如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键；
• 在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键；

覆盖索引

什么是回表？

• 先在二级索引的 B+ 树找到对应的叶子节点，获取主键值；

• 然后用上一步获取的主键值，在聚簇索引中的 B+ 树检索到对应的叶子节点，然后获取要查询的数据。

  在二级索引的 B+Tree 就能查询到结果的过程就叫作覆盖索引，要求查询语句中的列必须完全包含在索引中，这样就不需要回表操作

• 使用覆盖索引的关键是要注意 SELECT 列表中只取出需要的列，不要使用 SELECT *
• 不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引的列，而哈希索引、空间索引和全文索引等都不存储索引列的值

索引下推

• 索引下推能够减少二级索引在查询时的回表操作，提高查询的效率

• 使用 ICP 后,查询过程变为:

  1. 存储引擎读取索引记录
  2. 判断 WHERE 条件中能否用索引列进行过滤,如果可以则在索引层进行过滤
  3. 对满足索引层过滤条件的记录,再进行回表读取完整行记录
  4. MySQL 服务层对读取的行记录进行剩余 WHERE 条件过滤

什么时候需要 / 不需要创建索引

索引的缺点：

• 需要占用物理空间，数量越大，占用空间越大；
• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增大；
• 会降低表的增删改的效率，因为每次增删改索引，B+ 树为了维护索引有序性，都需要进行动态维护。

1. where条件里用不到的字段：如果某个字段在查询条件中从未使用过，那么就没有必要为它创建索引。
2. 表记录太少：如果表中的记录数量很少，创建索引可能反而会降低查询性能。
3. 经常增删改的表：对于频繁进行增删改操作的表，创建索引会降低这些操作的速度。
4. 数据重复且分布平均的字段：对于区分度不高的字段，比如"性别"这种字段，创建索引意义不大。
5. 负条件查询：使用 !=、<>、not in、not exists、not like 等负条件进行查询时，索引无法发挥作用。
6. 前导模糊查询：使用 like '%xxx' 这种前置模糊查询时，索引也无法生效。【如果数据库表中的字段只有主键+二级索引，那么即使使用了左模糊匹配，也不会走全表扫描（type=all），而是走全扫描二级索引树(type=index)。见小林coding%E3%80%82)】

索引失效

1. 对索引使用左或者左右模糊匹配：因为索引 B+ 树是按照索引值有序排列存储的，只能根据前缀进行比较。
2. 对索引使用函数
3. 对索引进行表达式计算：索引保存的是索引字段的原始值，表达式计算后的值，所以无法走索引，只能通过把索引字段的取值都取出来，然后依次进行表达式的计算来进行条件判断，因此采用的就是全表扫描的方式
4. 对索引隐式类型转换

5. 联合索引非最左匹配（对于联合索引来说，MySQL 会优先使用最左边的索引列进行匹配）：

   • 如果创建了一个 (a, b, c) 联合索引，如果查询条件是以下这几种，就可以匹配上联合索引（所以 a 字段在 where 子句的顺序并不重要）：

     • where a=1；
     • where a=1 and b=2 and c=3；
     • where a=1 and b=2
     • 当遇到范围查询(>、<、between、like)就会停止匹配

   • 如果查询条件是以下这几种，联合索引就会失效:

     • where b=2；
     • where c=3；
     • where b=2 and c=3；
6. WHERE 子句中的 OR：若左右有一个不为索引列，则索引失效

优化索引

1. 前缀索引优化：减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。order by 无法使用前缀索引； 无法把前缀索引用作覆盖索引；
2. 覆盖索引：不需要查询出包含整行记录的所有信息，也就减少了大量的 I/O 操作
3. 主键索引最好是自增的：如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据
4. 区分度不高不建议作为索引
5. 表记录少时不需要创建索引
6. 索引最好有序
7. 频繁更新的最好不要作为索引（引发频繁的页分裂和页合并）
8. 尽量采用组合索引（把频繁使用的、区分度高的列放在前面）

锁

1. 全局锁:

   • 全局锁是对整个数据库实例加锁。
   • 使用 FLUSH TABLES WITH READ LOCK 命令可以获取全局读锁。
   • 全局锁主要用于数据库备份,可以确保备份时数据的一致性。

2. 表级锁:

   • 表级锁包括表锁和元数据锁(MDL)。
   • 表锁可以是共享锁(LOCK TABLES tbl_name READ)或排他锁(LOCK TABLES tbl_name WRITE)。
   • 元数据锁(MDL)在访问一个表时会被自动加上,读锁在查询时加上,写锁在执行 DDL 语句时加上。
   • MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更。MDL 是在事务提交后才会释放

3. 行级锁:

   • 行级锁是 InnoDB 存储引擎特有的锁定机制。
   • InnoDB 行级锁分为记录锁、间隙锁、临键锁（记录锁+间隙锁）、插入意向锁。
   • 行级锁的粒度最小,发生锁冲突的概率也最低,并发度最高。但加锁开销也最大。

4. 意向锁:

   • 意向锁是表级锁,分为意向共享锁(IS)和意向排他锁(IX)。
   • 意向锁用来表示事务想要获取表中某些行的共享锁或排他锁。

5. 间隙锁和临键锁:

   • 是行级锁
   • 间隙锁用于防止幻读,锁定索引记录之间的间隙。
   • 假设，表中有一个范围 id 为（3，5）间隙锁，那么其他事务就无法插入 id = 4 这条记录了，这样就有效的防止幻读现象的发生。
   • 临键锁是在间隙锁的基础上,额外锁定索引记录本身。
   • 假设，表中有一个范围 id 为（3，5] 的 next-key lock，那么其他事务即不能插入 id = 4 记录，也不能修改 id = 5 这条记录。

日志

• undo log（回滚日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC。
• redo log（重做日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复；
• binlog （归档日志）：是 Server 层生成的日志，主要用于数据备份和主从复制；

undo log

MySQL 会隐式开启事务来执行“增删改”语句，在事务没提交之前，MySQL 会先记录更新前的数据到 undo log 日志文件里面，当事务回滚时，可以利用 undo log 来进行回滚。

一条记录的每一次更新操作产生的 undo log 格式都有一个 roll_pointer 指针和一个 trx_id 事务id：

• 通过 trx_id 可以知道该记录是被哪个事务修改的；
• 通过 roll_pointer 指针可以将这些 undo log 串成一个链表，这个链表就被称为版本链；

通过 ReadView + undo log 实现 MVCC（多版本并发控制）

redo log

当有一条记录需要更新的时候，InnoDB 引擎就会先更新内存（同时标记为脏页），然后将本次对这个页的修改以 redo log 的形式记录下来，这个时候更新就算完成了。InnoDB 引擎会在适当的时候，由后台线程将缓存在 Buffer Pool 的脏页刷新到磁盘里，这就是 WAL （Write-Ahead Logging）技术（MySQL 的写操作并不是立刻写到磁盘上，而是先写日志，然后在合适的时间再写到磁盘上）

• redo log 记录了此次事务完成后的数据状态，记录的是更新之后的值；
• undo log 记录了此次事务开始前的数据状态，记录的是更新之前的值；

bin log

MySQL 在完成一条更新操作后，Server 层还会生成一条 binlog，等之后事务提交的时候，会将该事物执行过程中产生的所有 binlog 统一写 入 binlog 文件。记录了所有数据库表结构变更和表数据修改的日志

binlog 有 3 种格式类型，分别是 STATEMENT（默认格式）、ROW、 MIXED，区别如下：

• STATEMENT：每一条修改数据的 SQL 都会被记录到 binlog 中，主从复制中 slave 端再根据 SQL 语句重现。但 STATEMENT 有动态函数的问题，比如你用了 uuid 或者 now 这些函数，你在主库上执行的结果并不是你在从库执行的结果，这种随时在变的函数会导致复制的数据不一致；
• ROW：记录行数据最终被修改成什么样了，不会出现 STATEMENT 下动态函数的问题。但 ROW 的缺点是每行数据的变化结果都会被记录，比如执行批量 update 语句，更新多少行数据就会产生多少条记录，使 binlog 文件过大，
• MIXED：包含了 STATEMENT 和 ROW 模式，它会根据不同的情况自动使用 ROW 模式和 STATEMENT 模式；