高级MySQL与大表优化

16 分钟 读完 (约 2361 字)

本文主要在学习了 MySQL 基础的前提下帮助深入理解 MySQL 以及大数据量情况下提高数据库性能的优化。

MySQL架构

mysql-architecture.png

MySQL的设计体现了分层设计的思想，从上至下分为：

  1. 连接层：包括各种Connector和MySQL Shell
  2. 服务层：SQL接口、解析器Parser、优化器Optimizer、缓存等
  3. 引擎层：包括InnoDB、MyISAM等各种存储引擎
  4. 存储层：和磁盘和日志文件打交道

引擎层

InnoDB 和 MyISAM 的区别：

  1. InnoDB 主要为了大数据量和抗高并发设计，MyISAM为了性能设计
  2. InnoDB 支持行级锁、事务、外键，MyISAM 只支持表级锁
  3. InnoDB 使用聚簇索引， MyISAM 使用非聚促索引，在下文会进行阐述
  4. MyISAM 存储引擎支持空间数据索引（R-Tree），可以用于地理数据存储

MySQL缓存

MySQL 与磁盘交互数据的最小单位是页（默认1页=16kB）
内存池（Buffer Pool）默认大小=128MB
由此可以计算出内存池中的默认页数为 128M / 16kB = 8172

bin.log、redo.log和undo.log

1、bin log是MySQL Server层记录的日志，redo log和undo log是InnoDB存储引擎的日志。
2、他们的内容有些重复，实际格式多为二进制。其中binlog主要记录所有对数据库的操作事件（Event），以便支持主从复制和数据恢复，常见的事件类型包括：Query Event、Write Rows Event、Update Rows Event、Delete Rows Event
3、redo log是InnoDB存储引擎层的日志，记录数据库物理页的修改操作，用于在MySQL崩溃/重启后对丢失数据进行恢复；undo log也是InnoDB存储引擎层日志，在需要执行事务回滚的时候用到，也可用于MVCC。
4、举例：
比如我们有一张表employees，初始数据如下：

1
2
3
4
5
+----+----------+
| id | salary   |
+----+----------+
| 1  | 5000     |
+----+----------+

现在要执行以下操作：

1
UPDATE employees SET salary = 6000 WHERE id = 1;

bin log示例：

1
2
3
4
5
6
7
8
9
# at 307
#230301 10:00:15 server id 1 end_log_pos 429 CRC32 0x9abcdef0 Update_rows_event_v1
### UPDATE `employees`
### WHERE
### @1=1
### @2=5000
### SET
### @1=1
### @2=6000

解析后的事件说明：

  • 时间戳: #230301 10:00:15
  • 事件：Update Rows Event
  • 操作: 更新一行数据。
  • SQL: UPDATE employees SET salary = 6000 WHERE id = 1;
  • 旧值: @1=1, @2=5000
  • 新值: @1=1, @2=6000
    redo log示例：
    1
    2
    3
    4
    5
    6
    7
    8
    9
    {
      "transaction_id": 12345,
      "operation": "UPDATE",
      "table_name": "employees",
      "row_id": 1,
      "new_values": {
        "salary": 6000
      }
    }

undo log示例：

1
2
3
4
5
6
7
8
9
{
  "transaction_id": 12345,
  "operation": "UPDATE",
  "table_name": "employees",
  "row_id": 1,
  "old_values": {
    "salary": 5000
  }
}

一条 Update 语句执行过程

  1. 修改 buffer（产生脏页）
  2. 生成对应 redo.log 和 bin.log （日志先行原则）
  3. redo.log 和 bin.log 的持久化，
  4. 返回修改成功

事务

事务指的是满足 ACID（Atomicity原子性、Consistency 一致性、Isolation 隔离性、Durability 持久性） 特性的一组操作，可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚。

MySQL事务的原子性、一致性和持久性是通过redo.log、undo.log和force log at commit机制实现的，而隔离性则是通过MVCC机制和锁实现的。force log at commit是指当事务commit时必须先将事务的所有日志写入redo log中进行持久化后，commit才算完成。

MySQL 事务执行过程

todo: 使用流程图+结构示意图表示


举例：
1.事务开始
2.申请锁资源，对id=2这行数据上排他锁
3.将需要修改的data pages读取到innodb_buffer_cache
4.记录id=2的数据到undo log
5.记录id=2修改后的数据到redo log buffer
6.将buffer cache中id=2得name改为test
7.commit，触发二阶段提交2pc
8.事务结束

redo日志的两阶段提交

redo log 的写入拆成了两个步骤：prepare 和 commit，这就是”两阶段提交”
主要是保证redo log事务写入顺序和binlog 事务顺序一致(通过事务id保证一致)
prepare阶段：redo持久化到磁盘（redo group commit），并将回滚段置为prepared状态，此时binlog不做操作
commit阶段：innodb释放锁，释放回滚段，设置redo log提交状态，binlog持久化到磁盘，然后存储引擎层提交
说白了就是执行完更新语句后，先将redo log状态设置为prepared，等更新内容写入binlog后，在将redo log状态设置为commited

redo.log 的持久化策略配置：
=0： 事务提交时不立即持久化，而是交给后台进程做
=1：…立即持久化
=2：…立即交给操作系统缓存

并发一致性问题：

  1. 修改丢失
  2. 读脏数据
  3. 不可重复读
  4. 幻读
脏读 不可重复读 幻读 修改丢失
读未提交 ❌ ❌ ❌ ❌
读已提交 ✅ ❌ ❌ ❌
可重复读 ✅ ✅ ❌ ❌
可重复读+MVCC ✅ ✅ ✅ ❌
可序列化 ✅ ✅ ✅ ✅

上面的修改丢失问题也可通过行锁(SELECT … FOR UPDATE)，乐观锁字段来解决。

MVCC （Multi-Version Concurrency Control，多版本并发控制）

Read Uncommited模式下：事务连接的链表可以变化
Repeatable Read模式下：事务连接的链表指针固定

  • MVCC 通过版本控制和范围锁机制（如 Next-Key Lock）解决了幻读问题。

索引

解释：排好序的快速查找的数据结构

为数据表添加索引（index）有两种方式：

  1. create index
  2. alter table
    索引也是存在于磁盘中的，需要时加载到内存中进行检索。

索引的优缺点

优点：加快查询和排序的速度
缺点：每创建一个索引都会复制一份表数据指针，会导致插入、更新速度减慢

索引速度太慢的原因

  1. 查询语句写的烂
  2. 索引失效
  3. 关联查询太多

聚簇索引和非聚促索引

20160717221315263.jpeg
聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。
一般情况下主键会默认创建聚簇索引，且一张表只允许存在一个聚簇索引。

索引匹配

最佳左前缀原则

索引优化

大表优化

条件

数据量：千万级

优化对象：数据表

根据业务类型，一般将数据分为三种：

  1. 流水型数据
  2. 状态性数据：多笔业务数据之间依赖于有状态的数据，而且要保证改数据的正确性。比如缴费充值时必须要获取原来的余额，才能支付成功。
  3. 配置型数据

根据数据类型的不同，优化思路也不同，整理如下表所示：

数据量增长情况 数据表类型 业务特点 优化核心思想
千万级
是一个相对稳定的数据量		 状态表 OLTP业务方向 能不拆就不拆，读需求水平扩展
千万级
可能达到亿万级甚至更高		 流水表 OLTP业务历史数据 业务拆分，面向分布式存储设计
千万级
可能达到亿万级甚至更高		 流水表 OLTP业务统计数据源 设计数据统计需求存储的分布式扩展
千万级
不应该有这么多的数据		 配置表 通用业务 小而简，避免大一统

库、表的垂直拆分和水平拆分

垂直拆分是将一张列比较多的表拆分成多张表
一般拆分原则：

  1. 把不常用的字段单独放在一张表中
  2. 把text、blob等大字段拆分出来放在附表中
  3. 经常组合查询的列放在一张表中

水平拆分是指数据表行的拆分，当表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放
一些拆分原则：

  1. 取模拆分：比如一张有400W的用户表users，为提高其查询效率我们把其分成4张
  2. 部分业务逻辑也可以通过地区，年份等字段来进行归档拆分

分库分表带来的问题

  1. 事务一致性问题，会产生分布式事务
  2. 跨节点关联查询
  3. 跨节点分页、排序函数
  4. 主键重复：要考虑使用全局主键
  5. 公共表（参数表、数据字典表）需要在每个数据库都保存一份，所有对公共表的更新都要执行一遍。

参考文献：
1、MySQL官网结构图： https://dev.mysql.com/doc/refman/8.0/en/pluggable-storage-overview.html
2、MySQL千万级大表优化，看这一篇就忘不掉了!： https://database.51cto.com/art/202002/610289.htm
3、表的垂直拆分和水平拆分：https://www.kancloud.cn/thinkphp/mysql-design-optimalize/39326
4、MYSQL索引：对聚簇索引和非聚簇索引的认识：https://blog.csdn.net/alexdamiao/article/details/51934917#:~:text=%E8%81%9A%E7%B0%87%E7%B4%A2%E5%BC%95%E6%98%AF%E5%AF%B9,%E5%AD%98%E5%9C%A8%E4%B8%80%E4%B8%AA%E8%81%9A%E7%B0%87%E7%B4%A2%E5%BC%95%E3%80%82

#MySQL #SQL #数据库
从C++到Java —— C系列编程语言辨析总结 数据结构与算法笔记 | 动态规划

评论

You forgot to set the shortname for Disqus. Please set it in _config.yml.
Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×