MySQL笔记

发表于 2019-11-21 更新于 2021-07-01 分类于 mysql 阅读次数：

部署

安装

ubuntu 或者 centos, 更新过仓库之后, 直接用 apt 或者 yum 进行安装即可

安装官方文档

更新

数据库备份, 备份所有数据库配置、系统数据库、自建数据库
确保更新路径是正确的
只能在 GA 版本之间升级
不能跨版本升级, 如: 5.5 —> 5.7 是不支持的, 5.6 —> 5.7 是可以的
跨版本升级最好先把当前版本升级到最新版本, 如 5.6 —> 5.7, 需要先升级到最新的 5.6 版本, 然后再升级到 5.7
同一个大版本可以跨小版本升级, 如 5.7.x —> 5.7.y
升级之前需要对比要升级的版本和当前版本之间的差异, 配置、系统表、server、innodb、sql
其他详细的信息参见 MySQL 官网

遇到的问题

问题: 安装完之后怎么更改 root 用户密码都可以直接登录

版本: 5.7.26

原因:

root 用户的认证插件默认是: auth_socket, 不是 mysql_native_password,
而 auth_socket 插件不关心, 也不需要密码, 它只检查用户是否使用 UNIX 套接字进行连接, 然后比较用户名
如果要配置密码, 需要在同一命令中同时更改插件并设置密码, 首先更改插件然后设置密码将不起作用, 它将再次回退到 auth_socket

修改密码命令:

SHOW DATABASES;
USE mysql;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';
FLUSH PRIVILEGES;
quit;

// 修改密码还可以用下面两条, 推荐第一个, 第二个会有 warning, 但是认证插件是 auth_socket 的时候必须用上面那个指定认证插件的修改语句
// ALTER USER 'root'@'localhost' IDENTIFIED BY '密码';
// UPDATE USER SET authentication_string=PASSWORD('密码') WHERE USER='root';

// 还可以修改 /etc/my.cnf 文件, 添加 skip-grant-tables 或 skip-grant-tables=1,
// 或者直接以 mysqld  --skip-grant-tables & 方式启动
// 当时没有以这样忽略密码验证的方式启动, 仍然能修改成功
// 该参数一般的使用情况是当 root 用户密码丢失时以此参数作为启动项

命令

用户相关命令

查看用户

1	select distinct concat('User: ''',user,'''@''',host,''';') as query from mysql.user;

创建用户

1 2	grant all on . to 'test'@'%' identified by 'test'; grant all privileges on `test`.* to 'test'@'%' identified by 'test';

删除用户

1 2	drop user 'test'; drop user 'test'@'%';

显示权限

1 2	show grants for 'user'; show grants for 'user'@'%';

授予权限

1 2	grant all privileges on . to 'user'@'%'; grant all privileges on `test`.* to 'user'@'localhost';

回收权限

1 2	revoke create on . from 'user'@'%'; revoke drop on 'test'.* to 'user'@'localhost';

刷新权限

1	flush privileges;

SHOW DATABASES

查看所有的数据库, 等同于 SELECT schema_name FROM information_schema.schemata\G 用 \G 替换 “;” 以纵向报表的形式输出结果, 有利于阅读

STATUS

查看 mysql 数据库的运行状态

USE

命令选择数据库例如 USE information_schema, 当使用此命令后
SELECT schema_name FROM information_schema.schemata\G, 可以更改为
SELECT schema_name FROM schemata\G

SHOW TABLES

查看数据库中的表
同样也可以在 information_schema 中查看, SHOW 命令是方便使用的简短模式
SELECT table_name FROM tables WHERE table_schema=’jblog’;

DESC table_name

查看表结构

查看表状态

SHOW 条件或

SHOW STATUS like ‘%条件%’ 或

SHOW VARIABLES LIKE “%条件%” 等

可以查看 engine 数据库引擎, version, row, index 等信息

查询数据库连接:

SHOW FULL PROCESSLIST;

SHOW STATUS LIKE '%Max_used_connections%';

SHOW STATUS LIKE '%Threads_connected%'; #当前连接数

SHOW STATUS LIKE '%table_lock%'; #表锁定

SHOW STATUS LIKE 'innodb_row_lock%'; #行锁定

SHOW STATUS LIKE '%qcache%'; #查询缓存情况

SHOW STATUS LIKE 'Qcache%';

SHOW STATUS LIKE 'Aborted_clients'; #由于客户没有正确关闭连接已经死掉, 已经放弃的连接数量

SHOW VARIABLES LIKE "%query_cache%";

SHOW VARIABLES LIKE "%binlog%";

SHOW VARIABLES LIKE '%max_connections%'; //查看最大连接数量

SHOW VARIABLES LIKE '%timeout%'; #查看超时时间

SHOW VARIABLES LIKE 'log_%'; #查看日志是否启动

小技巧

当多行命令输入, 发现错误后, 用\c 结束

事务

特性

事务具有 ACID 特性, A: 原子性（atomicity, 或称不可分割性）、C: 一致性（consistency）、I: 隔离性（isolation, 又称独立性）、D: 持久性（durability）

原子性

一个事务必须被视为一个不可分割的最小工作单元

一致性

数据库总是从一个一致性的状态转换到另一个一致性的状态

隔离性

通常来说, 一个事务所做的修改在最终提交之前, 对其他事务是不可见的

持久性

一旦事务提交, 其所做的修改就会永久保存到数据库中

数据读取问题

脏读

读取未提交数据

不可重复读

前后多次读取, 数据内容不一致

幻读

前后多次读取, 数据总量不一致

当某个事务在读取某个范围内的记录时, 另外一个事务又在该范围内插入新的记录, 当之前的事务再次读取该范围的记录时, 会产生幻行

不可重复读和幻读的区别

不可重复读是读取了其他事务更改的数据, 针对 update 操作
幻读是读取了其他事务新增的数据, 针对 insert 和 delete 操作

隔离级别

数据库事务的隔离级别有 4 种, 由低到高分别为 Read uncommitted 、Read committed 、Repeatable read 、Serializable

Read uncommitted

读未提交, 顾名思义, 就是一个事务可以读取另一个未提交事务的数据

从性能上来说, 该级别不会比其他的级别好太多, 但是缺乏其他级别的很多好处, 所以除非真的有非常必要的理由, 在实际应用中一般很少使用

Read committed

读提交, 顾名思义, 就是一个事务要等另一个事务提交后才能读取数据

大多数数据库系统的默认隔离级别都是 Read committed（但 MySQL 不是）

Repeatable read

重复读, 就是在开始读取数据（事务开启）时, 不再允许修改操作, 保证了在同一个事务中多次读取同样记录的结果是一致的

Repeatable read 是 MySQL 的默认事务隔离级别

InnoDB 通过多版本并发控制（MVCC, Multiversion Concurrency Control）基本解决了幻读的问题, MVCC 以乐观锁为理论基础, MVCC 的实现没有固定的规范, 每个数据库都会有不同的实现方式

Serializable

Serializable 是最高的事务隔离级别, 在该级别下, 事务串行化顺序执行, 可以避免脏读、不可重复读与幻读

Shared Everthting

一般是针对单个主机, 完全透明共享 CPU/MEMORY/IO, 并行处理能力是最差的, 典型的代表 SQLServer

Shared Disk

各个处理单元使用自己的私有 CPU 和 Memory, 共享磁盘系统

典型的代表 Oracle Rac, 它是数据共享, 可通过增加节点来提高并行处理的能力, 扩展能力较好

其类似于 SMP（对称多处理）模式, 但是当存储器接口达到饱和的时候, 增加节点并不能获得更高的性能

Shared Nothing

各个处理单元都有自己私有的 CPU/内存/硬盘等, 不存在共享资源, 类似于 MPP（大规模并行处理）模式, 各处理单元之间通过协议通信, 并行处理和扩展能力更好

典型代表 DB2 DPF 和 hadoop, 各节点相互独立, 各自处理自己的数据, 处理后的结果可能向上层汇总或在节点间流转

我们常说的 Sharding 其实就是 Share Nothing 架构, 它是把某个表从物理存储上被水平分割, 并分配给多台服务器（或多个实例）, 每台服务器可以独立工作, 具备共同的 schema, 比如 MySQL Proxy 和 Google 的各种架构, 只需增加服务器数就可以增加处理能力和容量

mysql 存储结构

mysql-存储结构

mysql buffer pool

Buffer Pool 其实就是一块内存区域，用来缓存表数据与索引数据，把磁盘上的数据加载到缓冲池，避免每次访问都进行磁盘 IO，起到加速访问的作用。

mysql 日志

mysql 常用日志有以下几种：

错误日志
查询日志
慢查询日志
事务日志【redo log（重做日志）、undo log（回滚日志）】
二进制日志【bin log】

redo log

redo log 是用于崩溃恢复和事务持久性的机制之一，它是一个循环记录缓冲区，用于记录在事务提交时所做的更改。在事务提交之前，所有的更改都会先被写入 redo log，然后再由数据库引擎写入磁盘。

当系统崩溃或者意外断电时，redo log 会被用来恢复未写入磁盘的事务。MySQL 在启动时会自动检查 redo log 是否需要进行崩溃恢复，并将未完成的事务重做。通过这种方式，redo log 可以保证事务的持久性和数据的一致性。

redo log 主要有两部分文件组成，重做日志缓冲（redo log buffer）以及重做日志文件（redo log file），前者是在内存中，后者是在磁盘中。
redo log 是物理格式的日志，记录的是物理数据页面的修改的信息，其 redo log 是顺序写入 redo log file 的物理文件中去的。

如果 mysql 每次更新操作都要写进磁盘，那么整个过程的 io 成本、磁盘查找成本都会很高，进而影响 mysql 的性能。

针对以上这种情况 mysql 采用了 WAL 技术 (Write-Ahead Logging, 先写日志，再写磁盘) 。

redo log file 由多个物理文件组成，这些文件通常被称为 redo log 组。每个 redo log 组包含一个或多个 redo log 文件，每个文件的大小通常为几十兆到几百兆字节。

每个 redo log 文件都是一个循环缓冲区，它可以记录一定数量的事务更改。当 redo log 文件被写满时，MySQL 将其标记为不可用，然后开始写入下一个 redo log 文件。此时，MySQL 会将最早的 redo log 文件重用，覆盖其中的旧事务更改。

在每个 redo log 文件中，MySQL 将事务更改记录为一系列的日志条目 (log entry)，每个日志条目都包含了一个事务所做的更改。每个日志条目通常包含以下信息：

事务 ID：每个事务都有一个唯一的标识符，用于将事务的多个日志条目关联起来。
操作类型：插入、更新或删除等操作类型。
数据对象：被更改的表、行或索引等数据对象。
旧值：在更新或删除操作中，记录被更改前的值。
新值：在插入或更新操作中，记录被更改后的值。

当 MySQL 启动时，它会检查最后一个 redo log 文件中的最后一个日志条目，并根据其中的信息确定哪些事务未完成，然后尝试将这些事务恢复到正常状态。在 MySQL 运行期间，redo log 将持续记录所有已提交的事务更改，以确保在崩溃恢复时可以恢复所有未写入磁盘的事务更改。

binlog

binlog 记录的是数据库中所有的更新操作，包括更新、插入、删除等操作。binlog 可以被用于备份、复制、恢复数据等场景。

binlog 可以用于许多用途，其中最常见的用途是数据备份和复制。使用 binlog，可以将数据库的所有更新操作记录下来，并在需要时将它们应用到另一个 MySQL 实例上，从而实现数据备份和复制。此外，binlog 还可以用于崩溃恢复，当 MySQL 重启时，它会读取最后一个 binlog 文件，然后将其中记录的所有更改应用到数据库中，以恢复未写入磁盘的更改。

binlog 也是以循环缓冲区的形式实现的。每个 binlog 文件包含一定数量的事件 (event)，每个事件记录一个事务的更改。每个事件由一个事件头 (event header) 和一个事件体 (event body) 组成，事件头包含了一些元数据信息，例如事件类型、时间戳、事务 ID 等，事件体则包含了具体的更改信息，例如被修改的表、修改前后的值等。

注意与 redo log 不同，binlog 是一种逻辑日志，它记录的是数据库的逻辑更改，而不是物理更改。这意味着 binlog 中记录的操作并不是直接修改了数据文件，而是以 SQL 语句的形式记录下来的，因此在使用 binlog 进行数据恢复时，可能会受到一些限制，例如在恢复期间需要使用相同的表结构、索引等。

默认情况下 binlog 日志是二进制格式，无法直接查看，需要借助工具：

使用 mysqlbinlog 工具
使用 MySQL Workbench

日志模式

binlog 支持三种日志格式： Statement、Row 和 Mixed

Statement（基于 SQL 语句的复制）

优点：不需要记录每一行的变化，减少了 binlog 日志量，节约了 IO，提高性能。

由于记录的只是执行语句，为了这些语句能在 slave 上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在 slave 得到和在 master 端执行时候相同的结果。

Row（基于行的复制）

优点：可以不记录执行的 sql 语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以 rowlevel 的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，以及 trigger 的调用和触发无法被正确复制的问题。

缺点：所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。

Mixed（混合模式）

一般的语句修改使用 statment 格式保存 binlog，如一些函数，statement 无法完成主从复制的操作，则采用 row 格式保存 binlog,MySQL 会根据执行的每一条具体的 sql 语句来区分对待记录的日志形式，也就是在 Statement 和 Row 之间选择一种。

undo log

undo log（回滚日志）用来记录数据被修改前的信息。正好跟前面的重做日志进行相反操作。undo log 主要记录的是数据的逻辑变化，为了在发生错误时回滚之前的操作，需要将之前的操作都记录下来，然后在发生错误时才可以回滚。

undo log 保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读；

undo log 是逻辑格式的日志，在执行 undo 的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，这一点是不同于 redo log 的。

undo log 日志格式

以 InnoDB 存储引擎为例，介绍一下其 undo log 的存储格式。

InnoDB 存储引擎的 undo log 存储在系统表空间中，具体来说：

每个表都会有一个对应的 undo 表空间，其中存储了该表的 undo log 信息。
每个 undo 日志文件包含多个 undo 日志段（undo log segment）。
每个日志段又包含多个 undo 日志块（undo log block）。
undo 日志块的大小默认为 16KB，可以通过参数 innodb_undo_log_default_size 来修改。
每个 undo 日志块包含了多个 undo 记录（undo record），每个 undo 记录对应一个修改操作。

每个 undo 记录的格式如下：

Header（12 字节）：包含 undo 记录的一些基本信息，如事务 ID、数据页编号、undo 记录长度等。
Transaction Information（10 字节）：包含 undo 记录的事务信息，如最后一个修改该记录的事务 ID、上一个版本的 undo 段落编号等。
Rollback Segments Information（0~32 字节）：用于记录 undo 记录中的数据在哪些 undo 段落中被修改过，以及这些段落中的事务 ID。
Data：修改操作之前的数据。

undo log 日志类型

undo log 主要分为 3 种：

Insert undo log：插入一条记录时，至少要把这条记录的主键值记下来，之后回滚的时候只需要把这个主键值对应的记录删掉就好了。
Update undo log：修改一条记录时，至少要把修改这条记录前的旧值都记录下来，这样之后回滚时再把这条记录更新为旧值就好了。
Delete undo log：删除一条记录时，至少要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了。
- 删除操作都只是设置一下老记录的 DELETED_BIT，并不真正将过时的记录删除。
- 为了节省磁盘空间，InnoDB 有专门的 purge 线程来清理 DELETED_BIT 为 true 的记录。为了不影响 MVCC 的正常工作，purge 线程自己也维护了一个 read view（这个 read view 相当于系统中最老活跃事务的 read view）;如果某个记录的 DELETED_BIT 为 true，并且 DB_TRX_ID 相对于 purge 线程的 read view 可见，那么这条记录一定是可以被安全清除的。

对 MVCC 有帮助的实质是 update undo log ，undo log 实际上就是存在 rollback segment 中旧记录链

Update undo log 执行流程

一个事务向 persion 表插入了一条新记录，记录如下，name 为 Jerry, age 为 24 岁，隐式主键是 1，事务 ID 和回滚指针，我们假设为 NULL

mysql-undolog-update1

现在来了一个事务 1 对该记录的 name 做出了修改，改为 Tom
1. 在事务 1 修改该行(记录)数据时，数据库会先对该行加排他锁
2. 然后把该行数据拷贝到 undo log 中，作为旧记录，即在 undo log 中有当前行的拷贝副本
3. 拷贝完毕后，修改该行 name 为 Tom，并且修改隐藏字段的事务 ID 为当前事务 1 的 ID, 我们默认从 1 开始，之后递增，回滚指针指向拷贝到 undo log 的副本记录，即表示我的上一个版本就是它
4. 事务提交后，释放锁

mysql-undolog-update2

又来了个事务 2 修改 person 表的同一个记录，将 age 修改为 30 岁
1. 在事务 2 修改该行数据时，数据库也先为该行加锁
2. 然后把该行数据拷贝到 undo log 中，作为旧记录，发现该行记录已经有 undo log 了，那么最新的旧数据作为链表的表头，插在该行记录的 undo log 最前面
3. 修改该行 age 为 30 岁，并且修改隐藏字段的事务 ID 为当前事务 2 的 ID, 那就是 2，回滚指针指向刚刚拷贝到 undo log 的副本记录
4. 事务提交，释放锁

mysql-undolog-update3

从上面，我们就可以看出，不同事务或者相同事务的对同一记录的修改，会导致该记录的 undo log 成为一条记录版本线性表，即链表，undo log 的链首就是最新的旧记录，链尾就是最早的旧记录（当然就像之前说的该 undo log 的节点可能是会 purge 线程清除掉，向图中的第一条 insert undo log，其实在事务提交之后可能就被删除丢失了，不过这里为了演示，所以还放在这里）

redo log、binlog、undo log 区别与作用

在 MySQL 数据库中，redo log、binlog 和 undo log 都是用于数据恢复和事务回滚的重要机制，它们分别具有不同的作用和特点，具体区别如下：

Redo log：用于保证事务的持久性，记录了 InnoDB 存储引擎中的物理操作，如数据修改等，用于在数据库崩溃时重做未完成的事务。redo log 是在内存中操作的，具有高效性，通常会写入磁盘的方式是批量提交，降低了磁盘 I/O 的开销。在 MySQL 中，redo log 的大小通过参数 innodb_log_file_size 来配置。
Binlog：用于记录 MySQL 中的逻辑操作，如对表的增删改查等操作，也用于主从复制和数据恢复。binlog 是基于语句的，记录了 SQL 语句的执行情况，具有较好的可读性和可恢复性。在 MySQL 中，binlog 的大小通过参数 max_binlog_size 来配置。
Undo log：用于记录事务的 undo 信息，包含了事务执行期间的所有修改操作及其 undo 信息，用于支持事务的回滚和 MVCC 机制。undo log 的大小通过参数 innodb_undo_tablespaces 来配置。

综上所述，redo log 和 binlog 分别记录了不同的日志信息，具有不同的作用和特点，都是用于数据恢复和备份的关键机制；而 undo log 则是用于支持事务的回滚和 MVCC 机制，记录了事务执行期间的所有修改操作及其 undo 信息。在数据库系统中，这些机制都是非常重要的，能够有效地保障数据库的数据一致性和可靠性。

update 语句的内部流程

示例语句：给 ID=2 这一行的 c 字段加 1

执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

两阶段提交

两阶段提交，是为了 binlog 和 redolog 两分日志之间的逻辑一致。redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。

由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者采用反过来的顺序。可能造成的问题：

数据库的状态可能和用它的日志恢复出来的库的状态不一致。
导致使用 binlog 进行数据同步的主从数据库数据不一致。

示例： update 语句来做例子。假设当前 ID=2 的行，字段 c 的值是 0，再假设执行 update 语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了 crash，会出现什么情况呢？

先写 redo log 后写 binlog。
假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。
先写 binlog 后写 redo log。
如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了 “把 c 从 0 改成 1” 这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。

MVCC (Multi-Version Concurrency Control，即多版本并发控制)

MVCC，全称 Multi-Version Concurrency Control，即多版本并发控制。MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。

MVCC 多版本并发控制指的是 “维持一个数据的多个版本，使得读写操作没有冲突” 这么一个概念。仅仅是一个理想概念。

MVCC 在 MySQL InnoDB 中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。

为什么需要 MVCC

MVCC 是为了实现读-写冲突不加锁，而这个读指的是快照读, 而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现。

在学习 MVCC 多版本并发控制之前，我们先了解一下，什么是 MySQL InnoDB 下的当前读和快照读？

当前读和快照读

当前读：指的是读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。

像 select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁) 这些操作都是一种当前读。

快照读：指的是不加锁的非阻塞读。

快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即 MVCC。
可以认为 MVCC 是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。

像不加锁的 select 操作就是快照读。

实现这么一个 MVCC 理想概念，我们就需要 MySQL 提供具体的功能去实现它，而快照读就是 MySQL 为我们实现 MVCC 理想模型的其中一个具体非阻塞读功能。而相对而言，当前读就是悲观锁的具体功能实现

MVCC 能解决什么问题，好处是？

数据库并发场景有三种, 分别为：

读-读：不存在任何问题，也不需要并发控制
读-写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读
写-写：有线程安全问题，可能会存在更新丢失问题，比如第一类更新丢失，第二类更新丢失

MVCC 带来的好处：

多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。

所以 MVCC 可以为数据库解决以下问题：

在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能
同时还可以解决脏读，幻读，不可重复读等事务隔离问题
但不能解决更新丢失问题

总而言之，MVCC 就是因为大牛们，不满意只让数据库采用悲观锁这样性能不佳的形式去解决读-写冲突问题，而提出的解决方案

在数据库中有了 MVCC，我们可以形成两个组合：

MVCC + 悲观锁 MVCC 解决读写冲突，悲观锁解决写写冲突
MVCC + 乐观锁 MVCC 解决读写冲突，乐观锁解决写写冲突

这种组合的方式可以最大程度的提高数据库并发性能，并解决读写冲突，和写写冲突导致的问题

MVCC 实现原理

在 mysql 中，它的实现原理主要是依赖记录中的 4 个隐式字段，undo 日志，Read View 来实现的。

隐式字段

每行记录除了我们自定义的字段外，还有数据库隐式定义的 DB_ROW_ID, DB_TRX_ID, DB_ROLL_PTR, DELETED_BIT 等字段

DB_ROW_ID: 6byte, 隐含的自增 ID（隐藏主键），如果数据表没有主键，InnoDB 会自动以 DB_ROW_ID 产生一个聚簇索引
DB_TRX_ID: 6byte, 最近修改(修改/插入)事务 ID：记录创建这条记录/最后一次修改该记录的事务
IDDB_ROLL_PTR: 7byte, 回滚指针，指向这条记录的上一个版本（存储于 rollback segment 里）
DELETED_BIT: 1byte, 记录被更新或删除并不代表真的删除，而是删除 flag 变了

mysql-mvcc-1

如上图，DB_ROW_ID 是数据库默认为该行记录生成的唯一隐式主键；DB_TRX_ID 是当前操作该记录的事务 ID；而 DB_ROLL_PTR 是一个回滚指针，用于配合 undo 日志，指向上一个旧版本；delete flag 没有展示出来。

undo 日志

参见上面 undo log

Read View(读视图)

Read View 是事务进行快照读操作的时候生产的读视图(Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的 ID(当每个事务开启时，都会被分配一个 ID, 这个 ID 是递增的，所以最新的事务，ID 值越大)

Read View 主要是用来做可见性判断的, 即当我们某个事务执行快照读的时候，对该记录创建一个 Read View 读视图，把它比作条件用来判断当前事务能够看到哪个版本的数据，即可能是当前最新的数据，也有可能是该行记录的 undo log 里面的某个版本的数据。

Read View 遵循一个可见性算法，主要是将要被修改的数据的最新记录中的 DB_TRX_ID（即当前事务 ID）取出来，与系统当前其他活跃事务的 ID 去对比（由 Read View 维护），如果 DB_TRX_ID 跟 Read View 的属性做了某些比较，不符合可见性，那就通过 DB_ROLL_PTR 回滚指针去取出 Undo Log 中的 DB_TRX_ID 再比较，即遍历链表的 DB_TRX_ID（从链首到链尾，即从最近的一次修改查起），直到找到满足特定条件的 DB_TRX_ID, 那么这个 DB_TRX_ID 所在的旧记录就是当前事务能看见的最新老版本。

那么这个判断条件是什么呢？

我们可以把 Read View 简单的理解成有三个全局属性：

trx_list: 未提交事务 ID 列表，用来维护 Read View 生成时刻系统正活跃的事务 ID
up_limit_id: 记录 trx_list 列表中事务 ID 最小的 ID
low_limit_id: ReadView 生成时刻系统尚未分配的下一个事务 ID，也就是目前已出现过的事务 ID 的最大值 + 1

比较步骤：

首先比较 DB_TRX_ID < up_limit_id, 如果小于，则当前事务能看到 DB_TRX_ID 所在的记录，如果大于等于进入下一个判断
接下来判断 DB_TRX_ID 大于等于 low_limit_id , 如果大于等于则代表 DB_TRX_ID 所在的记录在 Read View 生成后才出现的，那对当前事务肯定不可见，如果小于则进入下一个判断
判断 DB_TRX_ID 是否在活跃事务之中，trx_list.contains(DB_TRX_ID)，如果在，则代表我 Read View 生成时刻，你这个事务还在活跃，还没有 Commit，你修改的数据，我当前事务也是看不见的；如果不在，则说明，你这个事务在 Read View 生成之前就已经 Commit 了，你修改的结果，我当前事务是能看见的

MVCC 整体流程

mysql-mvcc-2

索引

索引目的

索引的目的在于提高查询效率，可以类比字典，如果要查 “mysql” 这个单词，我们肯定需要定位到 m 字母，然后从上往下找到 y 字母，再找到剩下的 sql。如果没有索引，那么我们可能需要把所有单词看一遍才能找到想要的单词。

索引优点

大大减少了服务器需要扫描的数据行数。
帮助服务器避免进行排序和分组，也就不需要创建临时表(B+Tree 索引是有序的，可以用于 ORDER BY 和 GROUP BY 操作。临时表主要是在排序和分组过程中创建，因为不需要排序和分组，也就不需要创建临时表)。
将随机 I/O 变为顺序 I/O(B+Tree 索引是有序的，也就将相邻的数据都存储在一起)。

索引原理

除了字典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。它们的原理都是一样的： 通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是可以通过同一种查找方式来锁定数据。

数据库也是一样，但显然要复杂许多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果 1000 条数据，1 到 100 分成第一段，101 到 200 分成第二段，201 到 300 分成第三段……这样查第 250 条数据，只要找第三段就可以了，一下子去除了 90%的无效数据。但如果是 1 千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是 lgN，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的，数据库实现比较复杂，数据保存在磁盘上，而为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。

磁盘 IO 与预读

前面提到了访问磁盘，那么这里先简单介绍一下磁盘 IO 和预读，磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分。

寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在 5ms 以下；
旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘 7200 转，表示每分钟能转 7200 次，也就是说 1 秒钟能转 120 次，旋转延迟就是 1/120/2 = 4.17ms；
传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。

那么访问一次磁盘的时间，即一次磁盘 IO 的时间约等于 5 + 4.17 = 9ms 左右，听起来还挺不错的，但要知道一台 500 -MIPS 的机器每秒可以执行 5 亿条指令，因为指令依靠的是电的性质，换句话说执行一次 IO 的时间可以执行 40 万条指令，数据库动辄十万百万乃至千万级数据，每次 9 毫秒的时间，显然是个灾难。下图是计算机硬件延迟的对比图，供大家参考：

mysql-io-计算机硬件延迟对比

考虑到磁盘 IO 是非常高昂的操作，计算机操作系统做了一些优化，当一次 IO 时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次 IO 读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为 4k 或 8k，也就是我们读取一页内的数据时候，实际上才发生了一次 IO，这个理论对于索引的数据结构设计非常有帮助。

索引的类型和数据结构

索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。

B+Tree 索引

B+ 树是大多数 MySQL 存储引擎的默认索引类型。

因为不再需要进行全表扫描，只需要对树进行搜索即可，因此查找速度快很多。

除了用于查找，还可以用于排序和分组。可以指定多个列作为索引列，多个索引列共同组成键。

适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于最左前缀查找。如果不是按照索引列的顺序进行查找，则无法使用索引。

InnoDB 的 B+ 树索引分为主索引和辅助索引。

InnoDB 对 B+ 树做了优化，同级节点中加入了前向指针，这样可以支持小于等于的快速查询

主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

mysql-index-b+tree-1

辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。

mysql-index-b+tree-2

InnoDB 一棵 B+ 树可以存放多少行数据？

这个问题的简单回答是：约 2 千万。

这里我们假设 B+ 树高为 3，即存在一个根节点和若干个叶子节点，那么这棵 B+ 树的存放总记录数为：根节点指针数 * 单个叶子节点记录行数。

单个叶子节点（页）中的记录数： 16K/1K=16 。（这里假设一行记录的数据大小为 1k，实际上现在很多互联网业务数据记录大小通常就是 1K 左右）。

那么现在我们需要计算出非叶子节点能存放多少指针？

其实这也很好算，我们假设主键 ID 为 bigint 类型，长度为 8 字节，而指针大小在 InnoDB 源码中设置为 6 字节，这样一共 14 字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，即 16384/14=1170 。

那么可以算出一棵高度为 3 的 B+ 树，能存放 1170*1170*16=21902400 条这样的数据记录。

所以在 InnoDB 中 B+ 树高度一般为 1-3 层，它就能满足千万级的数据存储。

在查找数据时一次页的查找代表一次 IO，所以通过主键索引查询通常只需要 1-3 次 IO 操作即可查找到数据。

哈希索引

哈希索引能以 O(1) 时间进行查找，但是失去了有序性，它具有以下限制:

无法用于排序与分组；
只支持精确查找，无法用于部分查找和范围查找。

InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

全文索引

MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。查找条件使用 MATCH AGAINST，而不是普通的 WHERE。

全文索引一般使用倒排索引实现，它记录着关键词到其所在文档的映射。

InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。

空间数据索引

MyISAM 存储引擎支持空间数据索引(R-Tree)，可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。

必须使用 GIS 相关的函数来维护数据。

聚簇索引和非聚簇索引

聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据。表数据按照索引的顺序来存储的，也就是说索引项的顺序与表中记录的物理顺序一致。
非聚簇索引：将数据存储与索引分开，叶结点包含索引字段值及指向数据页数据行的逻辑指针，其行数量与数据表行数据量一致，非聚簇索引访问数据总是需要二次查找。

一个表只能有一个聚簇索引。

聚簇索引的优势

看上去聚簇索引的效率明显要低于非聚簇索引，因为每次使用辅助索引检索都要经过两次 B+ 树查找，这不是多此一举吗？聚簇索引的优势在哪？

由于行数据和叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了 Buffer 中，再次访问的时候，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键 id 来组织数据，获得数据更快。
辅助索引使用主键作为 “指针” 而不是使用地址值作为指针的好处是：减少了当出现行移动或者数据页分裂时辅助索引的维护工作，使用主键值当作指针会让辅助索引占用更多的空间，换来的好处是 InnoDB 在移动行时无须更新辅助索引中的这个”指针”。也就是说行的位置（实现中通过 16K 的 Page 来定位）会随着数据库里数据的修改而发生变化（前面的 B+树节点分裂以及 Page 的分裂），使用聚簇索引就可以保证不管这个主键 B+树的节点如何变化，辅助索引树都不受影响。
聚簇索引适合用在排序的场合，非聚簇索引不适合。
取出一定范围数据的时候，使用用聚簇索引。
二级索引需要两次索引查找，而不是一次才能取到数据，因为存储引擎第一次需要通过二级索引找到索引的叶子节点，从而找到数据的主键，然后在聚簇索引中用主键再次查找索引，再找到数据。
可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户 ID 来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘 I/O。

聚簇索引的劣势

维护索引很昂贵，特别是插入新行或者主键被更新导至要分页(page split)的时候。建议在大量插入新行后，选在负载较低的时间段，通过 OPTIMIZE TABLE 优化表，因为必须被移动的行数据可能造成碎片。使用独享表空间可以弱化碎片。
表因为使用 UUId（随机 ID）作为主键，使数据存储稀疏，这就会出现聚簇索引有可能有比全表扫面更慢，所以建议使用 int 的 auto_increment 作为主键
主键的值是顺序的，所以 InnoDB 把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时（InnoDB 默认的最大填充因子是页大小的 15/16，留出部分空间用于以后修改），下一条记录就会写入新的页中。一旦数据按照这种顺序的方式加载，主键页就会近似于被顺序的记录填满（二级索引页可能是不一样的）
如果主键比较大的话，那辅助索引将会变的更大，因为辅助索引的叶子存储的是主键值；过长的主键值，会导致非叶子节点占用占用更多的物理空间

一个误区：把主键自动设为聚簇索引

聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。InnoDB 只聚集在同一个页面中的记录。包含相邻键值的页面可能相距甚远。如果已经设置了主键为聚簇索引，必须先删除主键，然后添加想要的聚簇索引，最后恢复设置主键即可。

优化

分页

表结构如下：

CREATE TABLE `limit_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `column1` decimal(11,2) NOT NULL DEFAULT '0.00',
  `column2` decimal(11,2) NOT NULL DEFAULT '0.00',
  `column3` decimal(11,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

基础分页语句

1	SELECT * FROM limit_test LIMIT 0,20;

LIMIT X 表示: 读取 X 条数据
LIMIT X, Y 表示: 跳过 X 条数据，读取 Y 条数据
LIMIT Y OFFSET X 表示: 跳过 X 条数据，读取 Y 条数据

对于简单的小型应用程序和数据量不是很大的场景，这种方式还是没问题的。

LIMIT 和 OFFSET 有什么问题

OFFSET 和 LIMIT 对于数据量少的项目来说是没有问题的，但是，当数据库里的数据量超过服务器内存能够存储的能力，并且需要对所有数据进行分页，问题就会出现，为了实现分页，每次收到分页请求时，数据库都需要进行低效的全表遍历。

优化第一版

1	SELECT * FROM limit_test WHERE id>10 limit 20;

这是一种基于指针的分页。你要在本地保存上一次接收到的主键 (通常是一个 ID) 和 LIMIT，而不是 OFFSET 和 LIMIT，那么每一次的查询可能都与此类似。

此方法缺点

类似于查询 SELECT * FROM table_name WHERE id > 3400000 LIMIT 20; 这样的效率非常快, 因为主键上是有索引的, 但是这样有个缺点, 就是 ID 必须是连续的, 并且查询不能有 WHERE 语句, 因为 WHERE 语句会造成过滤数据。那使用场景就非常的局限了。

再优化（覆盖索引优化）

1
2
3

SELECT * FROM (SELECT id FROM table_name LIMIT 3400000,20) a LEFT JOIN table_name b ON a.id = b.id;
# 或者是
SELECT * FROM table_name a INNER JOIN (SELECT id FROM table_name LIMIT 3400000,20) b USING (id);

MySQL 的查询完全命中索引的时候，称为覆盖索引，是非常快的，因为查询只需要在索引上进行查找，之后可以直接返回，而不用再回数据表拿数据。因此我们可以先查出索引的 ID ，然后根据 ID 拿数据。

索引优化

MySQL 索引优化是为了提高数据库查询性能和减少查询时间，以下是一些常见的 MySQL 索引优化方法：

选择合适的索引类型：
MySQL 支持多种索引类型，如 B-tree 索引、哈希索引、全文索引等。根据具体的查询需求和数据特点，选择适合的索引类型。
确定索引字段：
索引字段的选择非常重要。通常，选择常被用于查询条件的字段、经常被用于连接表的字段、用于排序和分组的字段等作为索引字段。
唯一索引和组合索引：
对于具有唯一性要求的字段，可以创建唯一索引。而对于经常一起使用的多个字段，可以创建组合索引，以提高查询效率。
避免冗余索引：
避免在表中创建冗余的、重复的索引，因为冗余索引会增加数据库的维护成本，并降低插入、更新和删除操作的性能。
避免过长的索引：
索引字段的长度越长，索引的维护和查询所需的资源就越多。因此，避免创建过长的索引，只选择必要的字段长度。
使用覆盖索引：
如果查询语句只需要索引字段的数据，而不需要从数据表中读取其他字段，可以使用覆盖索引。覆盖索引可以减少查询的 IO 操作，提高查询效率。
定期维护和优化索引：
定期分析和优化数据库中的索引，删除不再需要的索引，重新构建或重新组织索引，以保持索引的高效性。
注意索引和数据的一致性：
确保索引和数据的一致性，当对数据表进行更新、插入或删除操作时，需要及时更新和维护相关的索引。
监控和调优查询性能：
使用 MySQL 提供的性能监控工具和查询分析工具，定位和优化查询性能较差的 SQL 语句，避免全表扫描和慢查询等问题。

以上是一些常见的 MySQL 索引优化方法，根据具体的数据库结构和查询需求，可以针对性地选择和应用这些方法，以提高数据库查询性能和优化索引效果。

水平分库

问题：为什么需要水平分库，水平分库是为了解决什么问题？

水平分库（Sharding）是一种数据库分片策略，用于解决大规模数据存储和处理的问题。它是将数据库中的数据按照某种规则分散存储在多个独立的数据库实例中，从而实现数据的分布式存储和处理。

水平分库的主要目的是解决以下问题：

数据规模的扩展性问题： 当数据库中的数据规模不断增大，单个数据库实例可能无法承受高并发的查询和更新操作。水平分库将数据分散存储在多个数据库实例中，每个实例只需要处理一部分数据，从而有效地扩展了数据规模的处理能力。
高并发访问问题： 单个数据库实例在面对高并发的读写请求时可能会出现性能瓶颈。水平分库将数据分散存储在多个实例中，可以平均分担并发请求的负载，提高了系统的并发处理能力。
地理位置和延迟问题： 当用户分布在不同的地理位置时，访问同一个数据库实例可能会受到网络延迟影响。水平分库可以将数据存储在靠近用户的地理位置的实例中，减少访问延迟。
热点数据问题： 在单个数据库实例中，某些数据可能会成为热点数据，导致性能不均衡。水平分库可以将热点数据分散到不同的实例中，减少了热点数据对性能的影响。
硬件资源利用问题： 某些数据库实例可能具有更好的硬件资源，而某些实例可能资源紧张。水平分库可以根据需求将数据分散到不同的实例中，充分利用不同实例的硬件资源。

需要注意的是，水平分库虽然能够有效解决上述问题，但也引入了一些新的挑战，如数据一致性、跨分片事务、查询跨分片等问题。因此，在设计和实施水平分库时，需要综合考虑系统的需求、架构和数据访问模式，以及针对挑战的解决方案。

垂直分库

问题：为什么需要垂直分库，垂直分库是为了解决什么问题？

垂直分库（Vertical Sharding）是一种数据库分片策略，用于解决数据库表中不同的数据属性和访问模式之间的差异问题。它是将数据库中的不同数据列（字段）分散存储在多个独立的数据库实例中，从而实现数据的分布式存储和处理。

垂直分库的主要目的是解决以下问题：

数据冗余问题： 数据库中的某些表可能包含了大量不同属性的数据，但某些属性只在特定的访问模式下被使用，导致了数据的冗余存储。通过垂直分库，可以将不同属性的数据分开存储在不同的实例中，减少了数据的冗余。
访问模式的差异问题： 数据库表中的不同属性可能会被不同的应用程序或访问模式使用。某些属性可能在频繁的查询中使用，而其他属性可能只在特定的分析任务中使用。通过垂直分库，可以将不同属性的数据存储在不同的实例中，从而根据不同的访问模式进行优化。
性能优化问题： 某些属性的查询可能会对性能产生负面影响，例如频繁的全表扫描。通过垂直分库，可以将这些可能影响性能的属性存储在独立的实例中，从而保护其他数据的查询性能。
硬件资源利用问题： 某些属性可能需要更高性能的硬件资源，而其他属性可能对性能要求较低。通过垂直分库，可以根据需求将数据分散到不同的实例中，充分利用不同实例的硬件资源。
安全性问题： 某些属性可能包含敏感信息，需要更高级别的访问控制和安全保护。通过垂直分库，可以将敏感属性存储在独立的实例中，加强数据的安全性。

需要注意的是，垂直分库也引入了一些新的挑战，如跨分片的查询、数据一致性等问题。在设计和实施垂直分库时，需要综合考虑系统的需求、架构和数据属性，以及针对挑战的解决方案。垂直分库通常与水平分库等其他分片策略结合使用，以达到更好的数据管理和性能优化效果。

水平分表

问题：为什么需要水平分表，水平分表是为了解决什么问题

水平分表（Table Sharding）是一种数据库分片策略，用于解决单个表中数据量过大导致查询性能下降的问题。它是将单个数据库表中的数据按照某种规则分散存储在多个独立的表中，从而实现数据的分布式存储和处理。

水平分表的主要目的是解决以下问题：

数据规模的扩展性问题： 当数据库表中的数据量逐渐增大，单个表可能会变得庞大，导致查询性能下降，例如查询时间延长。水平分表可以将表中的数据分散存储在多个表中，每个表只包含部分数据，从而有效地扩展了数据规模的处理能力。
高并发访问问题： 单个表在面对高并发的读写请求时可能会出现性能瓶颈。水平分表可以将并发请求分散到不同的表中，平均分担了并发请求的负载，提高了系统的并发处理能力。
数据维护和备份问题： 大表的维护和备份可能会变得困难和耗时。通过水平分表，可以将数据分散到多个表中，使数据的维护和备份变得更加高效。
查询性能问题： 针对特定查询，查询时间可能会随着数据量的增加而增加。通过水平分表，可以减小每个表的数据量，从而提高查询性能。
硬件资源利用问题： 某些表可能需要更高性能的硬件资源，而其他表可能对性能要求较低。通过水平分表，可以根据需求将数据分散到不同的表中，充分利用不同表的硬件资源。

需要注意的是，水平分表也引入了一些新的挑战，如跨分片的查询、数据一致性等问题。在设计和实施水平分表时，需要综合考虑系统的需求、架构和数据访问模式，以及针对挑战的解决方案。水平分表通常与水平分库等其他分片策略结合使用，以达到更好的数据管理和性能优化效果。

垂直分表

问题：为什么需要垂直分表，垂直分表是为了解决什么问题？

垂直分表（Vertical Sharding）是一种数据库分片策略，用于解决单个数据表中不同数据属性的差异问题。它是将一个大的数据表按照数据属性的差异，将不同的列（字段）分散存储在多个独立的表中，从而实现数据的分布式存储和处理。

垂直分表的主要目的是解决以下问题：

数据冗余问题： 在单个大表中，可能包含了大量不同属性的数据，但某些属性只在特定的查询或应用场景下使用。这导致了数据的冗余存储，浪费了存储空间和维护成本。通过垂直分表，可以将不同属性的数据分散存储在不同的表中，减少了数据的冗余。
查询性能优化： 单个大表在执行查询时可能会遇到性能瓶颈，尤其是在需要扫描全表的情况下。通过垂直分表，可以将常用的查询字段分散到多个表中，提高查询性能。
维护和备份问题： 大表的维护和备份可能会变得复杂和耗时。通过垂直分表，可以将不同属性的数据分散到多个表中，简化了维护和备份过程。
访问权限和安全性： 不同属性的数据可能拥有不同的访问权限和安全性需求。通过垂直分表，可以将敏感数据分散存储在独立的表中，加强了数据的安全性。
表的设计灵活性： 在某些情况下，大表的设计可能变得复杂和难以维护。通过垂直分表，可以将不同属性的数据分散到多个表中，提高了表的设计灵活性和可维护性。

需要注意的是，垂直分表也会引入一些新的挑战，如跨表查询、数据一致性等问题。在设计和实施垂直分表时，需要综合考虑系统的需求、架构和数据属性，以及针对挑战的解决方案。垂直分表通常与其他分片策略如水平分表或水平分库结合使用，以满足复杂的数据管理和性能优化需求。

分库和分表区别

什么时候水平分表，什么时候水平分库

水平分表和水平分库是解决不同问题的数据库分片策略，选择何时使用取决于系统的需求、性能目标和架构设计。下面是一些情况说明何时使用水平分表和水平分库：

水平分表：

数据表过大： 当单个数据表的数据量逐渐增大，导致查询性能下降时，可以考虑水平分表。分散数据到多个表中，每个表只包含部分数据，可以提高查询性能。
查询模式差异： 不同属性的数据在不同的查询模式下使用频率差异较大时，可以考虑水平分表。将不同属性的数据分散到不同的表中，根据查询模式进行优化。
硬件资源利用： 某些属性的数据需要更高性能的硬件资源，而其他属性可能对性能要求较低。在这种情况下，可以考虑将数据按照属性分散到不同的表中，以充分利用硬件资源。

水平分库：

数据规模过大： 当整个数据库的数据规模逐渐增大，单个数据库实例难以承受高并发和大规模数据存储时，可以考虑水平分库。将数据分散存储在多个独立的数据库实例中，提高了数据规模的扩展性。
地理分布和延迟问题： 当用户分布在不同地理位置时，访问同一个数据库实例可能会受到网络延迟的影响。通过水平分库，可以将数据存储在靠近用户的地理位置的实例中，减少访问延迟。
高并发访问问题： 单个数据库实例在面对高并发的读写请求时可能会出现性能瓶颈。水平分库可以将并发请求分散到不同实例中，提高系统的并发处理能力。

需要根据实际情况来选择水平分表还是水平分库，有时也可能需要结合两者一起使用，以满足复杂的数据管理和性能优化需求。在做出决策时，需要综合考虑数据访问模式、查询需求、数据规模、性能要求和系统架构等因素。

什么时候垂直分表，什么时候垂直分库

垂直分表和垂直分库是解决不同问题的数据库分片策略，选择何时使用取决于系统的需求、性能目标和架构设计。下面是一些情况说明何时使用垂直分表和垂直分库：

垂直分表：

数据表中存在属性差异： 当单个数据表中的数据属性差异很大，不同属性的数据被不同的查询模式或应用程序使用时，可以考虑垂直分表。将不同属性的数据分开存储在不同的表中，以优化不同的访问模式。
属性的访问模式不同： 数据库表中的某些属性可能在不同的访问模式下被使用，有些属性可能在频繁的查询中使用，而其他属性可能只在特定的分析任务中使用。通过垂直分表，可以将不同属性的数据存储在不同的表中，根据不同的访问模式进行优化。

垂直分库：

数据规模过大： 当整个数据库中的数据规模逐渐增大，单个数据库实例无法应对高并发和大规模数据存储需求时，可以考虑垂直分库。将数据库中不同的数据表分散存储在多个独立的数据库实例中，提高了数据规模的扩展性。
访问权限和安全性不同： 不同的数据表可能包含不同级别的敏感信息，需要不同级别的访问权限和安全保护。在这种情况下，可以考虑将敏感数据表分散存储在不同的数据库实例中，增强数据的安全性。

需要根据实际情况来选择垂直分表还是垂直分库，也可能需要结合两者一起使用，以满足复杂的数据管理和性能优化需求。在做出决策时，需要综合考虑数据访问模式、查询需求、数据规模、性能要求、安全性和系统架构等因素。

什么时候水平分库，什么时候垂直分库

水平分库和垂直分库是数据库分片策略，用于解决不同类型的问题。以下是一些情况说明何时使用水平分库和垂直分库：