Mysql - 持续更新

头像
桃瑾
     香港
    阅读 6 分钟

    常见引擎

    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 模式；
    mysql
    桃瑾
    1 声望1 粉丝

    常常播种，有时收获

    « 上一篇
    go-协程、GMP

    引用和评论

    推荐阅读
    头像
    redis -- 持续更新

    桃瑾阅读 208

    头像
    MySQL 回收表碎片实践教程

    MySQL技术赞 2阅读 543

    头像
    Mysql梳理1

    cashew赞 1阅读 2.2k

    头像
    MySQL 内存异常增长分析

    zbdba赞 1阅读 478

    头像
    mysql梳理2

    cashew赞 1阅读 1.6k

    头像
    MySQL使用内存的计算方法

    zbdba赞 1阅读 406

    头像
    如何高效使用索引——10个索引优化最佳指南

    Chat2DB阅读 980

    0 条评论
    得票最新