聊一聊 MySQL

最新推荐文章于 2024-07-19 16:12:43 发布

Java-之父

最新推荐文章于 2024-07-19 16:12:43 发布

阅读量410

点赞数 3

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/CTZL123456/article/details/139501319

版权

什么是 MySQL

数据库本质上就是一个软件，提供对应的读写能力。根据数据模型的不同，可分为 KV 数据库、关系型数据库、图数据库、文档数据库。在关系型数据库中，最常用的就是 MySQL。即数据会被抽象为一个个的关系，存储在二位表格中。既然数据库是一种提供读写能力的软件，我们就需要学习它是如何存储数据，如何保证读性能。例如：支持的数据类型、对事务的支持、复杂查询的支持、如何保证高可用以及如何对其进行优化等一些概念。

数据类型

MySQL :: MySQL 8.0 Reference Manual :: 13 Data Types。数据类型可以理解为对存储数据的抽象，针对不同的问题场景抽象出数值类型、时间类型、字符串类型、空间类型、JSON 类型。提供不同的方法，方便开发者直接使用。

数值

在数值类型中，可以分为整型、浮点型、十进制、位值类型。在声明字段类型时可以标记为 UNSIGNED 和 SINGED。此外，对于整型和浮点型还可以使用 AUTO_INCREMENT 属性。

整型

在 MySQL 中支持的整型包括 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，区别在于存储使用的空间大小不同，表示的范围也不同。

浮点型

浮点型表示的是近似值，它可以表示的数据范围非常大，缺点就是会有精度丢失。FLOAT 使用四个字节、DOUBLE 使用八个字节来存储数据。

十进制数

DECIMAL: 利用字符串处理，存储精确的小数。相较于浮点数， DECIMAL 能够避免浮点数计算时的舍入问题。缺点就是计算效率低、占用空间大。DECIMAL(5,2)表示支持存储的最大位数是 5 位，小数点后的位数是 2 位，则存储范围为 -999.99 - 999.99

位值类型

存储二进制位数据。BIT(M) 表示可以存储 M 个二进制位，M 的范围是[1,64]。例如：BIT(3)，表示存储时使用的是三个二进制位。数字 8 实际存储的是 100。使用 BIT 类型的优势在于可以精确控制存储数据使用的二进制位数，同时也可以进行一些位运算。

时间

用于表示时间的类型包括 DATE、TIME、DATETIME、TIMESTAMP 和 YEAR。当时间类型的列声明为 NOT NULL 时，默认会使用零值。

对于 DATIME 和 TIMESTAMP 类型，可以设置初始值或更新时设置当前时间。例如：

sql

复制代码

CREATE TABLE t1 ( -- 插入行时，如果不指定 ts 和 dt 则时间当前时间；更新行时，如果不指定 ts 和 dt 则使用当前时间 ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );

DATE

DATE 类型，表示的范围为 0000-01-01 到 9999-12-31，只能精确到日。可以应用于不需要精确到具体时间的业务场景。

TIME [( fsp )]

TIME 类型，表示范围 -838:59:59.000000' to '838:59:59.000000'。fsp 是一个可选参数，范围是 0-6，表示秒的小数位，如果不指定则为 0，即没有秒的小数位。例如：time(3)可以精确到毫秒，可以存储 11:45:17.001。

这里需要注意的是 TIME 的存储范围较大，它不仅可以表示 24h 制，还可以表示两个时间发生的时间间隔。

DATETIME[( fsp )]

DATE 类型和 TIME 类型的组合，表示范围为 0000-01-01 00:00:00.000000 to 9999-12-31 23:59:59.499999。fsp 是一个可选参数，范围是 0-6，表示秒的小数位，如果不指定则为 0，即没有秒的小数位。例如：datetime(3)可以精确到毫秒，可以存储 2024-05-30 11:45:17.001。

在具体使用中需要注意时区问题，在数据库的存储中尽可能都适用 UTC 0，业务层再赋予具体的时区信息。

TIMESTAMP[( fsp )]

时间戳，表示的范围是 1970-01-01 00:00:01.000000 UTC to 2038-01-19 03:14:07.499999 UTC。时间戳存储的是距离(1970-01-01 00:00:00 UTC) 的秒数，因此无法表示 1970-01-01 00:00:00 UTC，因此这个会用来表示零值。fsp 是一个可选参数，范围是 0-6，表示秒的小数位，如果不指定则为 0，即没有秒的小数位。

在具体的使用中需要注意，该类型表示的范围有限，且能表示的最大值为2038-01-19 03:14:07.499999 UTC。

YEAR

表示具体的年，格式为 YYYY，表示的范围为 0000-9999。

字符串

字符串类型包括 CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET。

CHAR 和 VARCHAER

CHAR 和 VARCHAR 在功能上相同，区别在于存储占用的空间以及是否保留尾部的空格。CHAR 类型不会保留尾部的空格。「感觉设计的不太好」

下面展示了CHAR(4) 和 VARCAHR(4) 的实际存储区别。对于 VARCHAR，如果字符长度小于 255 会额外使用一个字节存储实际的字符长度，如果大于 255 则使用两个字节存储实际的字符长度。需要注意的是 CHAR 最多只能存储 255 个字符，VARCHAR 的存储数量则受限于数据行存储的空间。

BLOB 和 TEXT

对于 CHAR 和 VARCHAR 存储的数据长度有限。对于需要存储大量的字符串或二进制数据的场景，可以使用 BLOB 和 TEXT。TEXT 分为 TINYTEXT, TEXT, MEDIUMTEXT 和 LONGTEXT，BLOB 与之对应。下面是不同类型支持的最大存储空间

ENUM

枚举类型，即只允许插入提前预设好的候选集。使用枚举类型的好处在于可以节省存储空间「存储的时候会将候选列表编码为数字」，可以避免非预期的数据写入。使用示范：

sql

复制代码

CREATE TABLE shirts ( name VARCHAR(40), size ENUM('x-small', 'small', 'medium', 'large', 'x-large') ); INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'); SELECT name, size FROM shirts WHERE size = 'medium'; +---------+--------+ | name | size | +---------+--------+ | t-shirt | medium | +---------+--------+ UPDATE shirts SET size = 'small' WHERE size = 'large';

SET

枚举类型中，取值只能为候选集中的一个，而在 SET 中，可以为候选集的多个。使用示范：

sql

复制代码

CREATE TABLE myset (col SET('a', 'b', 'c', 'd')); INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d'); SELECT col FROM myset; +------+ | col | +------+ | a,d | | a,d | | a,d | | a,d | | a,d | +------+

空间

空间类型的数据主要应用在需要使用地理位置的场景。例如：查询所在位置指定范围的景点，不同景点的距离。使用空间类型可以很好的解决这一问题。相关的数据类型有 POINT、LINESTRING、POLYGON、GEOMETRY、MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION。底层实现使用的是 R 树。

POINT

点，存储坐标系中的一个点。即经度和纬度。如下是一个使用的例子：

sql

复制代码

CREATE TABLE places ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), location POINT ); -- 插入数据 INSERT INTO places (name, location) VALUES ('BJ', ST_GeomFromText('POINT(10 20)')); -- 查询位置，使用的 SRID，X 和 Y 坐标，具体查询单位是 m SELECT name, ST_AsText(location), ST_SRID(location) , ST_X(location), ST_Y(location) FROM places WHERE ST_Distance_Sphere(location, ST_GeomFromText('POINT(10 20)')) <= 1000;

LINESTRING

两个或多个点组成的线段。

sql

复制代码

CREATE TABLE paths ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), path LINESTRING ); INSERT INTO paths (name, path) VALUES ('path_a', ST_GeomFromText('LINESTRING(0 0, 10 1, 20 25, 50 60)')); SELECT name, ST_Length(path) as path_len, ST_AsText(path) as str, ST_AsText(ST_StartPoint(path)) as start_point, ST_AsText(ST_EndPoint(path)) as end_point FROM paths;

POLYGON

POLYGON是一种几何类型，用于表示一个多边形区域。在MySQL中，POLYGON是一个由至少四个点（其中第一个和最后一个点必须是相同的点）组成的闭环。

sql

复制代码

CREATE TABLE places ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), location POLYGON ); INSERT INTO places (name, location) VALUES ( 'Park', ST_GeomFromText('POLYGON((10 10, 10 20, 20 20, 20 10, 10 10))') ); SELECT name, ST_Area(location) as area FROM places WHERE ST_Contains(location, ST_GeomFromText('POINT(15 15)')); -- 点是否在区域内

GEOMETRY

GEOMETRY是一个数据类型，用于表示任何类型的二维空间对象，包括点（POINT），线（LINESTRING）和多边形（POLYGON）。

JSON

JSON（JavaScript Object Notation）是一种轻量级的数据交换格式，易于人阅读和编写，也易于机器解析和生成。在 MySQL 中使用 JSON 类型会校验插入的数据是否符合 JSON 规范、会对 JSON 数据进行存储优化，方便检索和查询、使用 JSON 可以动态扩展属性，无需新增列。

sql

复制代码

CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), info JSON ); INSERT INTO users (name, info) VALUES ( 'John', '{"age": 30, "city": "New York", "hobbies": ["reading", "swimming", "hiking"]}' ); SELECT name, info->'$.hobbies' FROM users WHERE JSON_CONTAINS(info->'$.hobbies', '"reading"');

需要注意的是 JSON 字段无法作为索引而存在，但可以为 JSON 中的某个字段创建索引。

sql

复制代码

ALTER TABLE users ADD COLUMN city VARCHAR(255) GENERATED ALWAYS AS (json_extract(info, '$.city')); CREATE INDEX idx_city ON users(city);

索引

索引是从主数据衍生出的额外数据结构，通过衍生出的额外元数据作为路标来快速查询数据，提升查询效率。常见的索引类型有哈希索引、B 树、LSM 树、R 树、全文索引等。在 MySQL 中，使用的是 B+ 树，其优点是可以支持范围查询，查询使用 IO 次数少；对于空间类型的数据，其索引实现使用的是 R 树。

什么是 B+ 树

B+ 数是 B 树的一个变种，主要有以下特点：

叶子结点才会存储实际的数据，非叶子结点只会存放索引
所有索引都会在叶子结点中出现，叶子结点之间构成一个有序链表
非叶子结点的索引也会同时存在在叶子结点中，并且是在子结点中所有索引的最大值或最小值
非叶子结点中有多少个索引，就有多少个子结点

下面使用具体的例子说明 B+ 树索引的实现，创建一个 product 表，并往里加入一些数据

sql

复制代码

CREATE TABLE `product` ( `id` int(11) NOT NULL, `product_no` varchar(20) DEFAULT NULL, `name` varchar(255) DEFAULT NULL, `price` decimal(10, 2) DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

在 B+ 树中，非叶子结点值存放了索引，即表中的 id 字段，叶子结点中存放了索引数据和行数据，且每个叶子结点会有一个指针，指向上一个和下一个叶子结点，形成一个双向链表。

索引的分类

在上面的例子中，由于表中使用主键字段 id，所以会使用 id 创建一个 B+ 树。如果不存在主键，会如何创建索引呢？其逻辑如下：

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

按照叶子结点是否存储全部的数据，可以将索引分为聚簇索引「主键索引」和非聚簇索引「二级索引、辅助索引、次级索引」。在 InnoDB 引擎中，一个表只能有一个聚簇索引，它决定了数据的物理存储顺序。

在 proudct 表中，我们可以通过聚簇索引快速查询到 id 为某个值的数据。那如果快速通过 product_no 查询数据呢？可以创建一个非聚簇索引，实现对 product_no 的快速查询。

sql

复制代码

alter table product add index idx_product_no(product_no);

相比于聚簇索引，非聚簇索引的叶子结点存储的主键值，而不是实际的数据。当我们使用下面的 sql 进行查询时，需要先通过非聚簇索引 product_no 查询到对应的主键值 2；然后去主键索引中查找 id=2 的数据行；这一过程我们叫做回表。

csharp

复制代码

select * from product where product_no = '0002';

当然，如果我们不需要查询到行的所有字段，而是使用下面的查询。则不需要进行回表，因为非聚集索引的叶子结点已经存储了主键值。这一过程我们叫做覆盖索引，即索引中即覆盖了全部的查询数据。

csharp

复制代码

select id from product where product_no = '0002';

此外，按照索引字段个数的不同，又分为单列索引、联合索引。按照字段特性的不同，可以分为：

主键索引（Primary Key） ：每个表只能有一个主键索引。它必须包含唯一的值，且不能含有 NULL 值。
唯一索引（Unique Index） ：唯一索引要求索引的所有值都唯一，但它可以有一个 NULL 值。
普通索引（Index） ：这是最基本的索引，它没有任何限制。
全文索引（Fulltext Index） ：全文索引用于全文搜索。与其他索引不同，全文索引并不存储值的位置，而是存储值的存在。
空间索引（Spatial Index） ：空间索引用于地理空间数据，比如经纬度坐标。
外键索引（Foreign Key） ：在 InnoDB 表中，当你定义一个外键约束时，MySQL 自动在外键列上创建一个普通索引（如果它还不存在的话）。
复合索引（Composite Index） ：复合索引是由两个或更多的列构成的索引，也被称为多列索引。
前缀索引（Prefix Index）： 对字符类型的前几个字符创建索引，可以减少索引占用的存储的空间。

事务

定义

事务是将应用程序的多个读写操作合并为一个逻辑操作单元的一种方式。在这个逻辑单元中，要么这个事务全部成功提交，要么全部回滚。事务的引入，可以让应用程序忽略多个操作部分成功、部分失败的场景，简化了错误的处理逻辑。

特性

提起事务，大家第一时间想到的就是 ACID，这是事务提供安全保证特性的缩写。ACID 代表原子性「Atomicity」、一致性「Consistency」、隔离性「Isolation」、持久性「Durability」。它由 Theo Härder 和 Andreas Reuter 于 1983 年提出，旨在为数据库中的容错机制建立精确的术语。

原子性「Atomicity」

ACID 的原子性是指：能够在错误时终止事务，丢弃该事务进行的所有写入变更的能力。

例如：一个客户端执行转账操作，会先从账户 A 执行扣款操作，再从账户 B 执行增款操作。在这两个操作执行期间会发生各种故障而导致转账失败。如果这些操作被分配到一个事务中，数据库会保证这两个操作要么全部成功，要么全部失败。

一致性「Consistency」

在数据库复制的章节中，介绍了最终一致性，即在操作完成一段时间后，不同节点最终会达到数据一致的状态。在 ACID 中，一致性是指：对数据的一组特定约束必须始终成立。例如，外键约束、唯一约束、主键自增等。

隔离性「Isolation」

为了提升数据库的读写性能，可以将多个事务并行运行。如果不同的事务读写不同的数据那是没有问题的。然而，如果多个事务读写相同的数据，则会遇到并发问题。即：多个事务读写操作执行顺序的不确定性，导致读到的数据不符合预期。

持久性「Durability」

数据的目的是提供一个地方存储数据。持久性是数据库提供的一个保障，即一旦事务执行成功，即使数据库发生崩溃、机器断电等，写入数据也不会丢失。

隔离级别

如果多个事务之间处理的数据并不重合，那它们可以并行的执行，不会造成任何问题。当一个事务读取另一个事务修改的数据，或两个事务修改同一个数据时，将会产生并发问题。需要使用对应的同步策略来解决这一问题。

最简单的策略就是让有并发的事务串行执行。然而，可串行化带来的性能开销是很多数据库不能容忍的。因此，数据库会采用更弱的隔离级别来保证其性能。

读未提交

最简单的隔离级别就是读未提交，即事务可以读到其他事务未提交的数据。这种方式的优势在实现简单，无需过多的并发控制，具有极高的读写性能。最大的问题也是可以读到其他事务未提交的数据「脏读」，如果其他事务进行了回滚，从而导致异常的数据逻辑。

另一个需要考虑的问题是脏写：即一个事务可以直接覆盖另一个事务未提交的写入。目前，所有的隔离级别均避免了脏写。即如果多个事务同时更新一条记录，则事务等待另一个事务提交或回滚之后再执行。

读已提交

在读已提交的隔离级别中可以解决脏读问题。即保证事务只能读到其他事务已经提交的数据。读已提交带来的问题是不可重复读，即对于同一条记录，在同一个事物中两次读取的结果不同。

可重复读

在大多数场景中，读已提交似乎已经可以解决它们的问题。但是，在此隔离场景下仍然会产生一些并发问题。例如：Alice 有两个账户，各有 500。现在有两个事务，一个是查看两个账户的余额，一个是从 account1 转账 100 到 account2。读取 account 1 时转账事务未开始，看到了 account1: 500 元，读取 account2 时转账事务已经提交，看到了 account2: 400。account1 + account2 < 1000，从而造成困惑。

针对这种场景可以使用可重复读/快照隔离的隔离级别。它可以保证同一个事务中，读取到的数据不会发生变更。即每个事务从自己的快照中读取数据。

串行化

在上面介绍了事务的隔离级别，读未提交、读已提交、可重复读，其隔离级别越来越严格。依次解决了脏写、脏读、不可重复读的问题，其实现难度也越来越复杂。在可重复读中，常见的一个问题就是丢失更新。例如，下面的计数器的例子：

此时，不得不考虑串行化。

实现

原子性保证

在事务的实现中，是如何保证原子性呢？即在一个事务执行的过程中，还没提交事务之前发生了崩溃，如何回滚到事务之前的数据？

在每次事务执行过程中，都记录下回滚时需要的信息到一个日志里，那么在事务执行中发生了崩溃，就可以通过这个日志回滚到事务之前的数据。这一机制就是 undo log「回滚日志」，保证了事务的原子性。此外，undo log 还有一个作用就是通过 ReadView + undo log 来实现 MVCC，后续会介绍。

持久性保证

持久性保证，是指将数据存储到非易失的设备中，也就是硬盘。可以保证断电重启后数据不会丢失。这里使用的是 redo log。即物理日志，记录了某个数据做了什么修改，每当执行一个事务就会产生这样的一条或者多条物理日志。

在事务提交时，只要先讲 redo log 持久化到磁盘即可。当系统崩溃时，虽然数据还没有持久化到叶子结点中，但是通过 redo log 可以将数据恢复到最新的状态。

隔离性保证

隔离性主要是通过锁机制和 MVCC「多版本控制」实现的，已提交读、可重复读可以通过 MVCC 实现，串行化可以通过锁机制来实现。

MVCC

MVCC 的基本思想就是对于同一行数据会存在多个版本。在数据行中会存在两个隐藏列：trx_id 和 roll_point。

trx_id: 当一个事务对某条聚簇索引记录进行更改时，就会把该事务的事务 id 记录在 trx_id 里。
roll_point: 每次对某条聚簇索引记录进行改动时，都是把旧版本记录写入到 unlog log，然后 roll_point 是一个指针，指向每一个旧版本记录，可以通过它找到修改前的记录。

每次事务开始时，会创建一个 Read View，其结构如下：

在可重复读中，整个事务都使用事务启动时生成的 Read View。当事务读取某一行记录时，则根据 Read View 和 trx_id 来确定读取的版本：

如果 trx_id 小于 min_trx_id，则表示 Read View 创建时该行就已经提交，直接返回。
如果 trx_id 大于等于 max_trx_id，表明该记录在 ReadView 生成前才开始，则通过 undo log 返回上个版本重复 1。
如果 trx_id 在 min_trx_id 和 max_trx_id 之间，则需要判断 trx_id 是否在 m_ids 中，如果存在，即说明这条记录还没有被提交，则需要顺着 ungo log 链往下找旧版本记录，重复 1。如果不在，说明创建 ReadView 时该版本已经被提交，可以访问。

两阶段锁

在 MySQL 中，串行化实现采用的是两阶段锁实现的。基本思想是将事务执行过程分为两个阶段：加锁阶段和解锁阶段。

加锁阶段（Growing Phase） ：在这个阶段，事务可以获取需要的锁，但不能释放任何锁。这意味着事务开始时可以逐步获取更多的锁，以便访问需要的资源。这个阶段会持续到事务找到了它所需的所有锁位置。
解锁阶段（Shrinking Phase） ：一旦事务释放了其第一个锁，它就进入了解锁阶段。在这个阶段，事务不能再请求新的锁，只能逐步释放已经获取的锁。当事务释放了所有的锁，这个阶段就结束了。

查询优化

使用索引可以避免扫描全表，从而提升查询效率。那如何判断 SQL 语句是否使用了索引呢？可以通过 EXPLAIN 关键字来展示 MySQL 执行语句时的详细信息。EXPLAIN 可以和 SELECT、DELETE、INSERT、REPLACE 和 UPDATE 一起使用。

看一个简单的例子：

sql

复制代码

-- auto-generated definition create table product ( id int not null primary key, product_no varchar(20) null, name varchar(255) null, price decimal(10, 2) null ) charset = utf8 row_format = DYNAMIC; -- 使用 EXPLAIN 进行查询分析, 在这个查询中，使用了主键索引，只有一行数据匹配 explain select id from product where id = 0

EXPLAIN 输出的列

id

查询标识符。SELECT 查询语句的序列号。id 越大越先被执行，id 相同则靠前的先执行。当查询的结果是 union 时，则 id 为 NULL。在这种场景下， table 列展示的是 <union M, N>，M 和 N 是查询对应的 id。

sql

复制代码

explain select * from product where id = 1 union select * from product where id = 1

例如上面的查询，id:1 和 id:2 用于标识两次 SELECT 查询。对于 id 为 NULL 的行，是一次 union 操作，union 的是 id:1 和 id:2 的查询结果。

select_type

SELECT 的查询类型，其枚举值如下图所示。

table

table 列输出查询表的名称，也可以是下列值：

<union M,N> 返回的结果是查询 id: M 和 id:N 的聚合
id 为 N 的查询结果的派生表。

sql

复制代码

explain select * from (select AVG(product.price) as avg_price from product) as t join product p on p.price > t.avg_price

id 为 N 的物化子查询结果，可以认为是一个内存临时表。

partitions

返回查询匹配的分区。如果没有分区表，则返回 NULL。

type

重要的参考字段，用于判断查询是否使用了索引。其枚举值如下：

system:

查询的表只有一行，即系统表。

const:

该表最多只有一个匹配行。通常是指指定主键查询、唯一列索引查询。例如：

sql

复制代码

explain select * from product where id = 5

eq_ref

对于前表返回的行，在表中都唯一的行与之对应。与 system 和 const 不同的是，eq_ref 出现在表的关联查询中，且关联的字段为主键或唯一键。例如：

sql

复制代码

-- 先全量扫表，查询 product.id，对于 account 表的 jion 类型是 eq_ref explain select * from product,account where product.id = account.`id(11)`

与 eq_ref 不同的是，eq_ref 关联表查询使用的主键或者唯一索引，而 ref 使用的非主键索引或者唯一索引，即有多个行与之对应。

sql

复制代码

-- product_no 为普通索引 explain select * from product where product.product_no = '0002'

fulltext

使用全文索引

ref_or_null

与 ref 类似，只是查询红需要额外检索包含 NULL 值的列。之所以单独作为一个枚举值，是因为对于 NULL 值的查询需要额外的操作。

sql

复制代码

explain select * from product where product.product_no = '0002' or product_no is null

index_merge

即表示此次查询使用了多个索引，将多个索引的查询结果进行了合并。

range

范围查询，并且使用了索引字段。常见于 <>，>，>=，<，<=，<=>，is NULL，BETWEEN，IN 等。

sql

复制代码

explain select * from product where product.id > 0

index

全索引扫描，与 ALL 的区别在于一个遍历 index 树，一个直接全部扫描叶子结点。

sql

复制代码

-- product_no 为索引，返回全部的 product_no 只需遍历全部的 product_no 索引树 explain select product_no from product

性能最差的查询，需要扫表。

possible_keys

返回 MySQL 可能会使用索引字段。这就意味着具体查询的时候并不一定会用到返回的索引。如果返回的是 NULL，意味着没有相关的索引。

Key

实际使用的索引，如果为 NULL，则表示没有使用任何索引。

key_len

表示 MySQL 决定使用键的长度。通过 key_len 的值来确定使用键的多少部分。

ref

表示哪些列或常量被用来与 key 列中提到的索引进行比较。

rows

在查询中，分为需要检查的行，最终返回的行。例如：对于一个没有索引字段的查询，需要检查的行就是表的全部数据，而最终返回的行就是满足 where 条件的行。这里的 rows 就是需要检查的行。

filtered

最终返回的行与需要检查的行的比值。结果从 100 开始递减，结果越大越好。结果 100 即表示需要检查的行就是需要返回的行。

Extra

返回查询额外的冗余信息，这里不再一一介绍。

高可用

高可用是指系统无中断的执行其功能的能力，代表系统的可用性程度。高可用通常通过提升系统的容错能力来实现。核心思路就是将数据冗余存储，并且能够进行故障检测，进行切流。MySQL 有哪些常见的高可用方案呢？

主从复制

主从复制是指将一个 MySQL 的实例数据同步到另一个实例中。可以使用两个或以上的实例搭建一个主从复制集群，提供单点写入，多点读取的服务。

在下面的例子中，有一个主库，三个从库。即所有写入操作都请求主库，主库将数据变更差生的 bin log 同步到从库。读请求可以请求到从库，从而降低主库的负载。如果主库发生故障，可以将一个从库升级为主库。

按照复制时延的不同，又分为异步复制、半同步复制、全同步复制。

异步复制

MySQL 中默认的复制是异步的，主库在执行完客户端提交的事务后会立即将结果返回给客户端，并不关心从库是否已经接收并且处理。存在问题就是，如果主库的日志没有及时同步到从库，然后主库宕机了，这时候执行故障转移，在从库冲选主，可能会存在选出的主库中数据不完整。

全同步复制

指当主库执行完一个事务，并且等到所有从库也执行完成这个事务的时候，主库在提交事务，并且返回数据给客户端。因为要等待所有从库都同步到主库中的数据才返回数据，所以能够保证主从数据的一致性，但是数据库的性能必然受到影响。

半同步复制

是介于全同步和全异步同步的一种，主库至少需要等待一个从库接收并写入到 Relay Log 文件即可，主库不需要等待所有从库给主库返回 ACK。主库收到 ACK ，标识这个事务完成，返回数据给客户端。

在半同步复制中，主库写数据到 binlog，并且执行 commit 提交事务后，会一直等待一个从库的 ACK。从库会在写入 Relay Log 后，将数据落盘，然后回复给主库 ACK，主库收到这个 ACK 才能给客户端事务完成的确认。

这样会存在问题就是，主库已经将该事务的 commit 存储到了引擎层，应用已经可以看到数据的变化了，只是在等待从库的返回，如果此时主库宕机，可能从库还没有写入 Relay Log，就会发生主从库数据不一致。

为了解决这个问题，MySQL 5.7 引入了增强半同步复制。主库写入数据到 binlog 后，就开始等待从库的应答 ACK，直到至少一个从库写入 Relay Log 后，并将数据落盘，然后返回给主库 ACK，通知主库可以进行 commit 操作，然后主库再将事务提交到事务引擎，应用此时才能看到数据的变化。

组复制

组复制，MySQL Group Replication，又叫 MGR。主要为了解决异步复制和半同步复制产生的数据不一致的问题。

在组复制中，每个MySQL服务器都是一个复制组的成员，所有的成员都存储一份相同的数据集。在复制组中，事务在提交到一个成员后，会被复制并在其他所有在线成员上执行，以保持数据的一致性。

组复制的主要特性包括：

具有强一致性：组复制使用了 Paxos 分布式共识算法，确保在分布式环境中数据的强一致性。
提供冲突检测和处理：在并发提交事务时，如果出现冲突，组复制可以自动检测并解决这些冲突。
成员可以自动处理故障和重新配置：如果组复制中的一个成员出现故障，其他成员可以自动重新配置并继续运行。
支持多主模式：在组复制中，任何一个服务器都可以作为主服务器，允许在任何服务器上执行写操作。
保证零数据丢失：只有当事务在所有在线组成员上都成功提交后，才会向客户端报告提交成功。

InnoDB Cluster

MySQL Shell 是 MySQL 的高级管理客户端。MySQL Router 是一种轻量级中间件，主要进行路由处理，将客户端发送的请求路由到不同 MySQL 节点。可以提供自动成员管理、容错、自动故障转移等功能。

MHA

Master High Availability Manager and Tools for MySQL, MHA。MHA 由两部分组成：

MHA Manager: 单独部署，定时探测集群中的 master 节点，当 master 发生故障时，它可以自动将最新数据的 slave 提升为新的 master，同时将所有的其他 slave 指向新的 master。
MHA Node: 运行在每台 MySQL 服务器上，主要作用是收集 binlog 信息，当发生故障时，能够选取数据较为完备的 Slave 节点切换为主节点。

Java-之父

关注

3
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
聊一聊 MySQL

叶子结点才会存储实际的数据，非叶子结点只会存放索引所有索引都会在叶子结点中出现，叶子结点之间构成一个有序链表非叶子结点的索引也会同时存在在叶子结点中，并且是在子结点中所有索引的最大值或最小值非叶子结点中有多少个索引，就有多少个子结点下面使用具体的例子说明 B+ 树索引的实现，创建一个 product 表，并往里加入一些数据sql复制代码。
复制链接

扫一扫