MySQL

基本概念

四个基本数据库 - schame

(4条消息) MySQL中自带的数据库表相关介绍（持续更新）_mysql的mysql库里的表都是干嘛的_啊荻～的博客-CSDN博客

information_schema
INFORMATION_SCHEMA提供对数据库元数据的访问 ，有关MySQL服务器的信息，例如数据库或表的名称，列的数据类型或访问权限。有时用于此信息的其他术语是数据字典和系统目录。
在INFORMATION_SCHEMA中，有数个只读表。它们实际上是视图，而不是基本表，因此，你将无法看到与之相关的任何文件

performance_schema
MySQL 5.5开始新增一个数据库：PERFORMANCE_SCHEMA，主要用于收集数据库服务器性能参数。并且库里表的存储引擎均为PERFORMANCE_SCHEMA，而用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表。

mysql
mysql库是核心数据库，类似于sql server中的master表，主要负责存储数据库的用户（账户）信息、权限设置、关键字等mysql自己需要使用的控制和管理信息。不可以删除，如果对mysql不是很了解，也不要轻易修改这个数据库里面的表信息。
常用举例：在mysql.user表中修改root用户的密码

sys
sys库是MySQL 5.7增加的系统数据库，这个库是通过视图的形式把information_schema和performance_schema结合起来，查询出更加令人容易理解的数据。
sys数据库里面包含了一系列的存储过程、自定义函数以及视图来帮助我们快速的了解系统的元数据信息。
sys库所有的数据源来自：performance_schema。目标是把performance_schema的把复杂度降低，让DBA能更好的阅读这个库里的内容。让DBA更快的了解DB的运行情况。

** test
早先版本系统自动创建的测试数据库，是一个空数据库，没有任何表，可以删除。5.7版官方已经删除了test数据库。

执行顺序

select * from table where “” group by having order by limit page, pagesize;

selct * from table1 join table2 on table1.x == table2.x

​	where xx == “” , group by xxx having () order by xxx limit ;

from .. on.. join.. where .. group by .. having ..select distinct ...order by ..  limit..

0. from
1.执行where xx对全表数据做筛选，返回第1个结果集。  where
2.针对第1个结果集使用group by分组，返回第2个结果集。 group by
3.针对第2个结果集中的每1组数据执行select xx，有几组就执行几次，返回第3个结果集。  select
4.针对第3个结集执行having xx进行筛选，返回第4个结果集。  having
5.针对第4个结果集排序。 order by
6.

select xx from xx的记录集合用where进行筛选，然后再使用group by 对筛选后的结果进行分组。

having字句对分组后的结果进行筛选

连接

直接连接

select * from table1, table2 - 笛卡尔积

等值连接 / 自然连接

区别：

自然连接去重，等值连接不去重
自然连接 一定是 等值连接
等值连接的相等属性不一定是公有的， 自然连接相等属性一定是公有的

select * from table1, table2 where table.x == table2.x

内连接

select * from table1 (inner) join table2 table1.x == table2.x

外连接

左外连

select * from table1 left join table2 table1.x == table2.x

右外连

select * from table1 right join table2 table1.x == table2.x

UNIQUE

假设我们有一个 user 表，其中包含以下字段：

```
CREATE TABLE user (
  id INT PRIMARY KEY,
  username VARCHAR(50),
  password VARCHAR(50),
  email VARCHAR(50),
  ...
);
```

现在我们需要为这个表添加一个索引，以保证 username、password 和 email 这三个字段的唯一性。我们可以通过以下语句在这个表上创建一个唯一索引：

```
CREATE UNIQUE INDEX idx_username_password_email ON user (username, password, email);
```

基于这个索引，每行数据的组合值 (username, password, email) 都必须是唯一的。也就是说，假如有两行数据 (A,B,C) 和 (A,B,D)，那它们就是合法的；而如果有一行数据 (A,B,C) 和另一行数据 (A,B,C)，那么这两行数据就会违反唯一性约束，并导致数据库抛出唯一性错误。

因此，这个索引保证了 user 表中的每一个数据行，在 (username, password, email) 这三个字段上的取值都必须是唯一的。索引的查找效率也更高，因为可以使用索引直接查找这三个字段来获取数据，在数据量较大的情况下可以极大提高查询效率。

执行流程

Update

执行一条update 语句执行过程。
第一步：

建立链接，并验证用户权限

第二步：

查询缓存，如果缓存中存在数据，则直接返回，缓存中不纯在数据，则访问数据库（select）

第三步：

分析器：词语解析语法解析

第四步：

优化器：执行索引

第五步：

加载缓存数据id=N的记录所在页数据

第六步：

写入数据的旧值，位于undo日志中，便于事务回滚

第七步：

更新内存中数据（缓存池）

第八步：

写入redo日志

第九步：

写入redo日志

第十步：

准备提交事务，redo日志写入磁盘

第十一步：

准备提交事务，redo日志写入磁盘

第十二步：

准备提交事务，binlog日志写入磁盘,binlog日志属于server层，主要用于回复数据库磁盘里的数据

第十三步：

写入commit标记的redo日志，提交事务完成后，该标记为了保证redo与binlog日志里数据保持一直

如果事务提交，redo日志还没有写入磁盘，数据库就发生了宕机，可以通过redo日志回复缓存中的数据

第十四步：

更新数据：已page为单位的随机写入到磁盘

事务

事务看起来感觉简单，但是要实现事务必须要遵守 4 个特性，分别如下：

• 原子性（Atomicity）：一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节，而且事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样，就好比买一件商品，购买成功时，则给商家付了钱，商品到手；购买失败时，则商品在商家手中，消费者的钱也没花出去。
• 一致性（Consistency）：是指事务操作前和操作后，数据满足完整性约束，数据库保持一致性状态。比如，用户 A 和用户 B 在银行分别有 800 元和 600 元，总共 1400 元，用户 A 给用户 B 转账 200 元，分为两个步骤，从 A 的账户扣除 200 元和对 B 的账户增加 200 元。一致性就是要求上述步骤操作后，最后的结果是用户 A 还有 600 元，用户 B 有 800 元，总共 1400 元，而不会出现用户 A 扣除了 200 元，但用户 B 未增加的情况（该情况，用户 A 和 B 均为 600 元，总共 1200 元）。
• 隔离性（Isolation）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致，因为多个事务同时使用相同的数据时，不会相互干扰，每个事务都有一个完整的数据空间，对其他并发事务是隔离的。也就是说，消费者购买商品这个事务，是不影响其他消费者购买的。
• 持久性（Durability）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

InnoDB 引擎通过什么技术来保证事务的这四个特性的呢？

• 持久性是通过 redo log （重做日志）来保证的；
• 原子性是通过 undo log（回滚日志） 来保证的；
• 隔离性是通过 MVCC（多版本并发控制） 或锁机制来保证的；
• 一致性则是通过持久性+原子性+隔离性来保证；

问题

SQL 标准提出了四种隔离级别来规避这些现象，隔离级别越高，性能效率就越低，这四个隔离级别如下：

• 读未提交（*read uncommitted*），指一个事务还没提交时，它做的变更就能被其他事务看到；
• 读提交（*read committed*），指一个事务提交之后，它做的变更才能被其他事务看到；
• 可重复读（*repeatable read*），指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL InnoDB 引擎的默认隔离级别；
• 串行化（*serializable* ）；会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行；

Explain - 执行计划

EXPLAIN是MySQL的一个关键字，用于解释查询语句的执行计划。它可以提供有关查询执行的详细信息，包括使用的索引、连接方式、数据访问方式等。EXPLAIN的作用是帮助开发人员和数据库管理员分析查询语句的性能，并进行优化。

使用EXPLAIN可以通过以下方式：

1. 在查询语句前加上EXPLAIN关键字，例如：EXPLAIN SELECT * FROM table_name WHERE condition;

2. 执行查询语句后，查看EXPLAIN的结果。

EXPLAIN的结果包含了查询的执行计划和相关的统计信息，如下所示：

• id：查询的标识符，用于标识不同的查询步骤。
• select_type：查询的类型，如SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）等。
• table：查询涉及的表名。
• partitions：查询涉及的分区。
• type：数据访问的方式，如ALL（全表扫描）、INDEX（索引扫描）、RANGE（范围扫描）等。
• possible_keys：可能使用的索引。
• key：实际使用的索引。
• key_len：使用的索引长度。
• ref：与索引比较的列或常量。
• rows：估计的扫描行数。
• filtered：过滤的行数百分比。
• Extra：额外的信息，如Using index（使用索引覆盖查询）、Using where（使用WHERE条件过滤）等。

通过分析EXPLAIN的结果，可以得到以下信息：

1. 查询的数据访问方式：根据type字段，可以判断查询是使用全表扫描还是索引扫描。全表扫描通常效率较低，而索引扫描可以提高查询性能。

2. 索引的使用情况：根据possible_keys和key字段，可以判断查询是否使用了索引。如果possible_keys中包含了查询涉及的列的索引，而key中显示使用了索引，则表示索引被有效地使用。

3. 查询的优化空间：根据EXPLAIN的结果，可以判断查询是否存在潜在的优化空间。例如，如果type字段显示为ALL，表示使用了全表扫描，可能需要考虑创建索引来提高查询性能。

4. 查询的连接方式：根据select_type字段，可以判断查询的连接方式，如简单查询、子查询、联接查询等。不同的连接方式可能对查询性能产生影响。

通过分析EXPLAIN的结果，可以根据查询的特点和需求，进行索引优化、查询重写、表结构调整等操作，以提高查询性能和优化数据库的设计。

—————《存储引擎》——–

MySQL引擎介绍 - 知乎 (zhihu.com)

存储引擎

1、MyISAM

使用这个存储引擎，每个MyISAM在磁盘上存储成三个文件。

（1）frm文件：存储表的定义数据

（2）MYD文件：存放表具体记录的数据

（3）MYI文件：存储索引

frm和MYI可以存放在不同的目录下。MYI文件用来存储索引，但仅保存记录所在页的指针，索引的结构是B+树结构。下面这张图就是MYI文件保存的机制：

从这张图可以发现，这个存储引擎通过MYI的B+树结构来查找记录页，再根据记录页查找记录。并且支持全文索引、B树索引和数据压缩。

支持数据的类型也有三种：

（1）静态固定长度表

这种方式的优点在于存储速度非常快，容易发生缓存，而且表发生损坏后也容易修复。缺点是占空间。这也是默认的存储格式。

（2）动态可变长表

优点是节省空间，但是一旦出错恢复起来比较麻烦。

（3）压缩表

上面说到支持数据压缩，说明肯定也支持这个格式。在数据文件发生错误时候，可以使用check table工具来检查，而且还可以使用repair table工具来恢复。

有一个重要的特点那就是不支持事务，但是这也意味着他的存储速度更快，如果你的读写操作允许有错误数据的话，只是追求速度，可以选择这个存储引擎。

2、InnoDB

InnoDB是默认的数据库存储引擎，他的主要特点有：

（1）可以通过自动增长列，方法是auto_increment。

（2）支持事务。默认的事务隔离级别为可重复度，通过MVCC（并发版本控制）来实现的。

（3）使用的锁粒度为行级锁，可以支持更高的并发；

（4）支持外键约束；外键约束其实降低了表的查询速度，但是增加了表之间的耦合度。

（5）配合一些热备工具可以支持在线热备份；

（6）在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度；

（7）对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位于B+数的叶子节点上；

当然InnoDB的存储表和索引也有下面两种形式：

（1）使用共享表空间存储：所有的表和索引存放在同一个表空间中。

（2）使用多表空间存储：表结构放在frm文件，数据和索引放在IBD文件中。分区表的话，每个分区对应单独的IBD文件，分区表的定义可以查看我的其他文章。使用分区表的好处在于提升查询效率。

对于InnoDB来说，最大的特点在于支持事务。但是这是以损失效率来换取的。

3、Memory

将数据存在内存，为了提高数据的访问速度，每一个表实际上和一个磁盘文件关联。文件是frm。

（1）支持的数据类型有限制，比如：不支持TEXT和BLOB类型，对于字符串类型的数据，只支持固定长度的行，VARCHAR会被自动存储为CHAR类型；

（2）支持的锁粒度为表级锁。所以，在访问量比较大时，表级锁会成为MEMORY存储引擎的瓶颈；

（3）由于数据是存放在内存中，一旦服务器出现故障，数据都会丢失；

（4）查询的时候，如果有用到临时表，而且临时表中有BLOB，TEXT类型的字段，那么这个临时表就会转化为MyISAM类型的表，性能会急剧降低；

（5）默认使用hash索引。

（6）如果一个内部表很大，会转化为磁盘表。

B+树结构

MySQL索引底层：B+树详解 - 知乎 (zhihu.com)

B+树经典面试题

• InnoDB一棵B+树可以存放多少行数据?
• 为什么索引结构默认使用B+树，而不是hash，二叉树，红黑树，B-树?
• B-树和B+树的区别

InnoDB一棵B+树可以存放多少行数据?
这个问题的简单回答是：约2千万行。

• 在计算机中，磁盘存储数据最小单元是扇区，一个扇区的大小是512字节。
• 文件系统中，最小单位是块，一个块大小就是4k;
• InnoDB存储引擎最小储存单元是页，一页大小就是16k。

因为B+树叶子存的是数据，内部节点存的是键值+指针。索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而再去数据页中找到需要的数据;

假设B+树的高度为2的话，即有一个根结点和若干个叶子结点。这棵B+树的存放总记录数为=根结点指针数*单个叶子节点记录行数。

• 如果一行记录的数据大小为1k，那么单个叶子节点可以存的记录数 =16k/1k =16.
• 非叶子节点内存放多少指针呢?我们假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，所以就是8+6=14字节，16k/14B =16*1024B/14B = 1170

因此，一棵高度为2的B+树，能存放1170 * 16=18720条这样的数据记录。同理一棵高度为3的B+树，能存放1170 *1170 *16 =21902400，也就是说，可以存放两千万左右的记录。B+树高度一般为1-3层，已经满足千万级别的数据存储。
为什么索引结构默认使用B+树，而不是B-Tree，Hash哈希，二叉树，红黑树?
简单版回答如下：

• Hash哈希，只适合等值查询，不适合范围查询。
• 一般二叉树，可能会特殊化为一个链表，相当于全表扫描。
• 红黑树，是一种特化的平衡二叉树，MySQL 数据量很大的时候，索引的体积也会很大，内存放不下的而从磁盘读取，树的层次太高的话，读取磁盘的次数就多了。
• B-Tree，叶子节点和非叶子节点都保存数据，相同的数据量，B+树更矮壮，也是就说，相同的数据量，B+树数据结构，查询磁盘的次数会更少。

B-树和B+树的区别

• B-树内部节点是保存数据的;而B+树内部节点是不保存数据的，只作索引作用，它的叶子节点才保存数据。
• B+树相邻的叶子节点之间是通过链表指针连起来的，B-树却不是。
• 查找过程中，B-树在找到具体的数值以后就结束，而B+树则需要通过索引找到叶子结点中的数据才结束
• B-树中任何一个关键字出现且只出现在一个结点中，而B+树可以出现多次。

———《索引使用 / 建立》———

MYSQL索引使用技巧及注意事项

MYSQL索引使用技巧及注意事项_mysql 索引使用技巧及注意事项_柴神的博客-CSDN博客

一、索引的作用

​ 一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，所以查询语句的优化显然是重中之重。

​ 在数据量和访问量不大的情况下，mysql访问是非常快的，是否加索引对访问影响不大。但是当数据量和访问量剧增的时候，就会发现mysql变慢，甚至down掉，这就必须要考虑优化sql了，给数据库建立正确合理的索引，是mysql优化的一个重要手段。

​ 索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的。除了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。

​ 在创建索引时，需要考虑哪些列会用于 SQL 查询，然后为这些列创建一个或多个索引。事实上，索引也是一种表，保存着主键或索引字段，以及一个能将每个记录指向实际表的指针。数据库用户是看不到索引的，它们只是用来加速查询的。数据库搜索引擎使用索引来快速定位记录。

​ INSERT 与 UPDATE 语句在拥有索引的表中执行会花费更多的时间，而SELECT 语句却会执行得更快。这是因为，在进行插入或更新时，数据库也需要插入或更新索引值。

二、索引的创建、删除

索引的类型：

1. UNIQUE(唯一索引)：不可以出现相同的值，可以有NULL值
2. INDEX(普通索引)：允许出现相同的索引内容
3. PROMARY KEY(主键索引)：不允许出现相同的值
4. fulltext index(全文索引)：可以针对值中的某个单词，但效率确实不敢恭维

组合索引：实质上是将多个字段建到一个索引里，列值的组合必须唯一

（1）使用ALTER TABLE语句创建索引，应用于表创建完毕之后再添加。

ALTER TABLE 表名 ADD 索引类型 （unique,primary key,fulltext,index）[索引名]（字段名）

//普通索引



alter table table_name add index index_name (column_list) ;



//唯一索引



alter table table_name add unique (column_list) ;



//主键索引



alter table table_name add primary key (column_list) ;

​ ALTER TABLE可用于创建普通索引、UNIQUE索引和PRIMARY KEY索引3种索引格式，table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。

​ 另外，ALTER TABLE允许在单个语句中更改多个表，因此可以同时创建多个索引。

（2）使用CREATE INDEX语句对表增加索引

​ CREATE INDEX可用于对表增加普通索引或UNIQUE索引，可用于建表时创建索引。

CREATE INDEX index_name ON table_name(username(length)); 

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；

如果是BLOB和TEXT类型，必须指定 length。

//create只能添加这两种索引;



CREATE INDEX index_name ON table_name (column_list)



CREATE UNIQUE INDEX index_name ON table_name (column_list)

​ table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义，索引名不可选。

​ 另外，不能用CREATE INDEX语句创建PRIMARY KEY索引。

（3）删除索引
删除索引可以使用ALTER TABLE或DROP INDEX语句来实现。DROP INDEX可以在ALTER TABLE内部作为一条语句处理，其格式如下：

drop index index_name on table_name ;



alter table table_name drop index index_name ;



alter table table_name drop primary key ;

　　其中，在前面的两条语句中，都删除了table_name中的索引index_name。而在最后一条语句中，只在删除PRIMARY KEY索引中使用，因为一个表只可能有一个PRIMARY KEY索引。因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。

如果从表中删除某列，则索引会受影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

（4）组合索引与前缀索引

​ 在这里要指出，组合索引和前缀索引是对建立索引技巧的一种称呼，并不是索引的类型。为了更好的表述清楚，建立一个demo表如下。

create table USER_DEMO
(
   ID                   int not null auto_increment comment '主键',
   LOGIN_NAME           varchar(100) not null comment '登录名',
   PASSWORD             varchar(100) not null comment '密码',
   CITY                 varchar(30) not null comment '城市',
   AGE                  int not null comment '年龄',
   SEX                  int not null comment '性别(0:女 1：男)',
   primary key (ID)

);

　　为了进一步榨取mysql的效率，就可以考虑建立组合索引，即将LOGIN_NAME,CITY,AGE建到一个索引里：

ALTER TABLE USER_DEMO ADD INDEX name_city_age (LOGIN_NAME(16),CITY,AGE); 

　　 建表时，LOGIN_NAME长度为100，这里用16，是因为一般情况下名字的长度不会超过16，这样会加快索引查询速度，还会减少索引文件的大小，提高INSERT，UPDATE的更新速度。

​ 如果分别给LOGIN_NAME,CITY,AGE建立单列索引，让该表有3个单列索引，查询时和组合索引的效率是大不一样的，甚至远远低于我们的组合索引。虽然此时有三个索引，但mysql只能用到其中的那个它认为似乎是最有效率的单列索引，另外两个是用不到的，也就是说还是一个全表扫描的过程。

​ 建立这样的组合索引，就相当于分别建立如下三种组合索引：

LOGIN_NAME,CITY,AGE
LOGIN_NAME,CITY
LOGIN_NAME

　　为什么没有CITY,AGE等这样的组合索引呢？这是因为mysql组合索引“最左前缀”的结果。简单的理解就是只从最左边的开始组合，并不是只要包含这三列的查询都会用到该组合索引。也就是说name_city_age(LOGIN_NAME(16),CITY,AGE)从左到右进行索引，如果没有左前索引，mysql不会执行索引查询。

​ 如果索引列长度过长，这种列索引时将会产生很大的索引文件，不便于操作，可以使用前缀索引方式进行索引，前缀索引应该控制在一个合适的点，控制在0.31黄金值即可（大于这个值就可以创建）。

SELECT COUNT(DISTINCT(LEFT(`title`,10)))/COUNT(*) FROM Arctic; 
-- 这个值大于0.31就可以创建前缀索引，Distinct去重复

ALTER TABLE `user` ADD INDEX `uname`(title(10)); 
-- 增加前缀索引SQL，将人名的索引建立在10，这样可以减少索引文件大小,加快索引查询速度

（5）重建索引：

repair table tablename quick

三、索引的使用及注意事项

​ EXPLAIN可以帮助开发人员分析SQL问题，explain显示了mysql如何使用索引来处理select语句以及连接表，可以帮助选择更好的索引和写出更优化的查询语句。使用方法，在select语句前加上Explain就可以了：

Explain select * from user where id=1;

尽量避免这些不走索引的sql：

SELECT `sname` FROM `stu` WHERE `age`+10=30;
-- 不会使用索引,因为所有索引列参与了计算

SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990;
-- 不会使用索引,因为使用了函数运算,原理与上面相同

SELECT * FROM `houdunwang` WHERE `uname` LIKE'后盾%'
-- 走索引

SELECT * FROM `houdunwang` WHERE `uname` LIKE "%后盾%"
-- 不走索引
-- 正则表达式不使用索引，这应该很好理解，所以为什么在SQL中很难看到regexp关键字的原因

-- 字符串与数字比较不使用索引;



EXPLAIN SELECT * FROM `a` WHERE `a`="1"
-- 走索引



EXPLAIN SELECT * FROM `a` WHERE `a`=1
-- 不走索引

select * from dept where dname='xxx' or loc='xx' or deptno=45
-- 如果条件中有or，即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段，都必须建立索引，我们建议大家尽量避免使用or 关键字
-- 如果mysql估计使用全表扫描要比使用索引快，则不使用索引

索引虽然好处很多，但过多的使用索引可能带来相反的问题，索引也是有缺点的：

​ （1）虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT,UPDATE和DELETE。因为更新表时，mysql不仅要保存数据，还要保存一下索引文件。如果要给大表上建了多种组合索引，索引文件会膨胀很快。
​ （2）索引只是提高效率的一个方式，如果mysql有大数据量的表，就要花时间研究建立最优的索引，或优化查询语句。

四、使用索引的一些技巧：

​ （1）索引不会包含有NULL的列

​ 只要列中包含有NULL值，都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此符合索引就是无效的。

​ （2）使用短索引

​ 对串列进行索引，如果可以就应该指定一个前缀长度。例如，如果有一个char（255）的列，如果在前10个或20个字符内，多数值是唯一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

​ （3）索引列排序

​ mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作，尽量不要包含多个列的排序，如果需要最好给这些列建复合索引。

​ （4）like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，注意正确的使用方式。like ‘%aaa%’不会使用索引，而like ‘aaa%’可以使用索引。

​ （5）不要在列上进行运算

​ （6）不使用NOT IN 、<>、！=操作，但<,<=，=，>,>=,BETWEEN,IN是可以用到索引的

​ （7）索引要建立在经常进行select操作的字段上。

​ 因为如果这些列很少用到，那么有无索引并不能明显改变查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

​ （8）索引要建立在值比较唯一的字段上。

​ （9）对于那些定义为text、image和bit数据类型的列不应该增加索引。因为这些列的数据量要么相当大，要么取值很少。

​ （10）在where和join中出现的列需要建立索引。

​ （11）where的查询条件里有不等号(where column != …),mysql将无法使用索引。

​ （12）如果where字句的查询条件里使用了函数(如：where DAY(column)=…),mysql将无法使用索引。

​ （13）在join操作中(需要从多个数据表提取数据时)，mysql只有在主键和外键的数据类型相同时才能使用索引，否则及时建立了索引也不会使用。

五、注意点

​ （1）自然语言全文索引创建索引时的字段需与查询的字段保持一致，即MATCH里的字段必须和FULLTEXT里的一模一样；

​ （2）自然语言检索时，检索的关键字在所有数据中不能超过50%（即常见词），则不会检索出结果。可以通过布尔检索查询；

​ （3）在mysql的stopword中的单词检索不出结果。可通过SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD查询所有的stopword。遇到这种情况，有两种解决办法：

​ A、stopword一般是mysql自建的，但可以通过设置ft_stopword_file变量为自定义文件，从而自己设置stopword，设置完成后需要重新创建索引。但不建议使用这种方法；

​ B、使用布尔索引查询。

​ （4）小于最短长度和大于最长长度的关键词无法查出结果。可以通过设置对应的变量来改变长度限制，修改后需要重新创建索引。

myisam引擎下对应的变量名为ft_min_word_len和ft_max_word_len

innodb引擎下对应的变量名为innodb_ft_min_token_size和innodb_ft_max_token_size

​ （5）MySQL 5.7.6 之前的版本不支持中文，需使用第三方插件

​ （6）全文索引只能在 InnoDB（MySQL 5.6以后） 或 MyISAM 的表上使用，并且只能用于创建 char,varchar,text 类型的列。

———《索引概念》———

原理

(6条消息) Mysql索引原理_深夜面包的博客-CSDN博客

索引有： hash 索引、B+树索引、全文索引
hash 索引：key value 结构缩影
B+树索引：按照索引 顺序 ABC，建立树形结构，可以通过查询优化：索引下推、覆盖索引、联合索引
（特别：B+树也是 innoDB 的存储文件结构，可以减少 IO次数，每一个节点16k，可以通过）

索引下推（Index Pushdown）是指在查询过程中，将过滤条件下推到存储引擎层级执行，减少数据的读取和传输量，提高查询性能。具体来说，当查询语句包含索引列的过滤条件时，存储引擎可以利用索引来过滤掉不符合条件的数据，减少了从磁盘读取的数据量。

覆盖索引（Covering Index）是指一个索引包含了查询所需的所有列，包括查询的列和过滤条件的列。当查询语句只需要索引中的列数据时，可以直接使用索引进行查询，而不需要再去访问数据行。这样可以减少磁盘I/O操作，提高查询性能。

联合索引（Composite Index）是指一个索引包含多个列。联合索引可以用于多列的查询条件，同时也可以用于单列的查询条件。当查询语句中的列与联合索引的前缀列匹配时，可以利用联合索引进行查询。联合索引的选择需要根据查询的列和查询条件的情况来确定，合理的联合索引可以提高查询性能。

联合索引

联合索引（Composite Index）是指一个索引包含多个列。最左匹配原则（Leftmost Prefix Rule）是指在联合索引中，如果查询条件只涉及到联合索引的前缀列，那么索引可以被有效地使用。

具体来说，当查询语句中的列与联合索引的前缀列匹配时，可以利用联合索引进行查询。例如，如果有一个联合索引包含列（A, B, C），那么以下查询可以使用该索引进行优化：
- 查询条件为 A = 1
- 查询条件为 A = 1 AND B = 2
- 查询条件为 A = 1 AND B = 2 AND C = 3

但是，如果查询条件只涉及到联合索引的后续列，那么索引无法被有效地使用。例如，如果只有以下查询条件，将无法使用联合索引进行优化：
- 查询条件为 B = 2
- 查询条件为 B = 2 AND C = 3

最左匹配原则的意义在于，如果你需要使用联合索引进行查询优化，应该将最常用的列放在联合索引的前面，以便能够最大程度地利用索引。如果查询条件涉及到的列不符合最左匹配原则，那么数据库可能无法使用联合索引进行优化，而需要进行全表扫描，导致查询性能下降。

因此，在设计联合索引时，需要根据查询的列和查询条件的情况来确定索引的顺序，以满足最左匹配原则，提高查询性能。

覆盖索引

索引下推

Mysql性能优化：什么是索引下推？ - 知乎 (zhihu.com)

• 在开始之前先先准备一张用户表(user)，其中主要几个字段有：id、name、age、address。建立联合索引（name，age）。
• 假设有一个需求，要求匹配姓名第一个为陈的所有用户，sql语句如下：

SELECT * from user where  name like '陈%'

• 根据 “最佳左前缀” 的原则，这里使用了联合索引（name，age）进行了查询，性能要比全表扫描肯定要高。
• 问题来了，如果有其他的条件呢？假设又有一个需求，要求匹配姓名第一个字为陈，年龄为20岁的用户，此时的sql语句如下：

SELECT * from user where  name like '陈%' and age=20

• 这条sql语句应该如何执行呢？下面对Mysql5.6之前版本和之后版本进行分析。

Mysql5.6之前的版本

• 5.6之前的版本是没有索引下推这个优化的，因此执行的过程如下图：

• 会忽略age这个字段，直接通过name进行查询，在(name,age)这课树上查找到了两个结果，id分别为2,1，然后拿着取到的id值一次次的回表查询，因此这个过程需要回表两次。

Mysql5.6及之后版本

• 5.6版本添加了索引下推这个优化，执行的过程如下图：

• InnoDB并没有忽略age这个字段，而是在索引内部就判断了age是否等于20，对于不等于20的记录直接跳过，因此在(name,age)这棵索引树中只匹配到了一个记录，此时拿着这个id去主键索引树中回表查询全部数据，这个过程只需要回表一次。

实践

• 当然上述的分析只是原理上的，我们可以实战分析一下，因此陈某装了Mysql5.6版本的Mysql，解析了上述的语句，如下图：

• 根据explain解析结果可以看出Extra的值为Using index condition，表示已经使用了索引下推。

总结

• 索引下推在非主键索引上的优化，可以有效减少回表的次数，大大提升了查询的效率。
• 关闭索引下推可以使用如下命令，配置文件的修改不再讲述了，毕竟这么优秀的功能干嘛关闭呢：

set optimizer_switch='index_condition_pushdown=off';

锁

锁种类

MySQL中提供了多种锁机制，可以控制并发访问数据库的数据操作、保证数据的一致性和完整性，避免数据在事务或并发操作下出现不同步或冲突的问题。常用的MySQL锁包括：

1. 共享锁（Shared Locks）：也称读锁。多个事务同时获取该锁，可以同时读取同一资源，并进行读操作，但不能进行写操作，直到所有共享锁被释放。

2. 排它锁（Exclusive Locks）：也称写锁。只允许一个事务获取该锁，可以进行读写操作，其他事务无法对该资源进行任何操作，直到排它锁被释放。

3. 行锁（Row Locks）：在对一行进行操作时，仅对该行进行锁定，相对于表级锁或页级锁，减少了锁的粒度，提高并发性能。

4. 间隙锁（Gap Locks）：使用间隙锁可以锁定索引但不存在的键值，防止其他事务插入相应的键值，在某些情况下可以用于保证数据的完整性。

5. 共享意向锁（Shared Intent Locks）和排它意向锁（Exclusive Intent Locks）：当事务准备获取一定数量的共享锁或排它锁时，可以先获取对应的意向锁，以提高锁的效率和并发性。

6. Auto-In – 主键锁

7. 表锁

MySQL还支持多种锁策略，如锁等待、锁超时、悲观锁、乐观锁等，可以根据不同的业务需求和场景选择不同的锁策略。

总之，在使用MySQL数据库时，需要理解各种锁的特点和使用方法，合理选取锁策略，确保数据一致性和并发安全性。

意向锁

详解 MySql InnoDB 中意向锁的作用 - 知乎 (zhihu.com)

MySQL的意向锁是一种用于控制表级别锁定的机制，其主要目的是提高并发操作时的性能和效率。它是一种辅助锁机制，用于协调多个事务之间的并发读写操作，并且不会阻止其他事务对同一表进行读取或修改。

其功能介绍如下：

1. 提高并发操作时的性能和效率：通过使用意向锁，可以避免因为排他锁而导致的资源争用和等待，从而提高并发操作时的性能和效率。
2. 控制事务的锁定状态：在多个事务同时对一个表进行读写操作时，可以使用意向锁来控制每个事务的锁定状态，以保证数据的一致性和完整性。
3. 减少死锁的风险：通过使用意向锁，可以减少死锁的风险，从而提高系统的可靠性和稳定性。

使用场景包括但不限于：

1. 高并发读写操作：在需要进行高并发读写操作时，可以通过使用意向锁来提高系统的性能和效率。
2. 多线程操作：在需要进行多线程操作时，可以使用意向锁来协调各个线程之间的读写操作，防止出现数据冲突和死锁问题。

使用方式：

1. 在进行数据读取或修改操作前，先对表进行意向锁定，表明该事务有可能需要读写该表。
2. 如果其他事务对同一表进行了排他锁定，则当前事务可以等待，或者选择放弃锁定操作。
3. 当锁被释放后，该事务再次检查是否需要对该表进行读写操作。如果需要，则进行相应的操作，并在完成后释放意向锁。

使用

以下是举例说明MySQL锁机制在具体应用场景中的应用：

1. 共享锁

例如，在发起一次普通的查询时，可以使用共享锁(SHARE MODE)来锁定表或行，防止其他事务对表或行进行修改或删除。例如：

SELECT * FROM orders WHERE client_id=1 LOCK IN SHARE MODE;

在执行查询操作时，获取共享锁来锁定表或行，其他事务可以同时读取该资源，但不能进行任何修改或删除。

2. 排他锁

例如，在新增或修改数据时，使用排它锁来保证只有一个事务可以修改或写入数据。例如：

BEGIN;
SELECT * FROM orders WHERE client_id=1 FOR UPDATE;

...进行相关数据修改操作...

COMMIT;

在执行修改或写入操作时，获取排它锁来锁定表或行，其他事务将被阻塞，直到当前事务回滚或提交操作。

3. 行锁

例如，在高并发订单系统中，使用行锁来避免资金冻结和出错的情况。例如：

BEGIN;
SELECT account_balance FROM accounts WHERE account_id=1000 FOR UPDATE;

UPDATE accounts SET account_balance = account_balance - 100.00
WHERE account_id = 1000;

COMMIT;

在执行订单操作时，通过获取行锁来锁定对应的账户信息行，保证其他订单事务无法修改该行信息，避免同一账户出现资金不足或超额等问题。

4. 间隙锁

例如，在高并发修改大量订单状态的场景中，使用间隙锁来避免其他事务在修改过程中对订单进行插入或修改。例如：

BEGIN;
SELECT * FROM orders WHERE order_status=0 FOR UPDATE;

UPDATE orders SET order_status=1 WHERE order_status=0;

COMMIT;

在执行订单状态修改时，通过获取间隙锁来锁定订单状态为0的区间范围，防止其他事务在该范围内插入或删除订单记录，保证修改的数据完整和安全性。

以上是MySQL锁机制在具体应用场景中的示例，不同的业务需求和数据操作类型需要针对选取合适的锁机制和锁级别，以提高数据的一致性和并发性能。

基础SQL / 对象

SQL语言：DDL、DML、DQL、DCL详解 - 知乎 (zhihu.com)

DDL ：Data Definite Language

DDL全称是Data Definition Language，即数据定义语言，定义语言就是定义关系模式、删除关系、修改关系模式以及创建数据库中的各种对象，比如表、聚簇、索引、视图、函数、存储过程和触发器等等。

DML：Data Manipulation Language

数据操纵语言全程是Data Manipulation Language，主要是进行插入元组、删除元组、修改元组的操作。主要有insert、update、delete语法组成。

DQL：Data Query Language

数据查询语言全称是Data Query Language，所以是用来进行数据库中数据的查询的，即最常用的select语句

DCL：Data Controller Language

数据控制语言：Data Control Language。用来授权或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，能够对数据库进行监视。

数据库中创建过程、函数、包、触发器分别如何调用

在中函数编写和调用。下面分别给出实例代码和调用示例：

创建过程并调用：

创建一个简单的示例过程，实现将传入的两个整数相加并返回结果：

创建过程：

CREATE OR REPLACE PROCEDURE add_numbers (num1 IN NUMBER, num2 IN NUMBER, result OUT NUMBER)
IS
BEGIN
    result := num1 + num2;
END;
/

调用过程：

DECLARE
    res NUMBER;
BEGIN
    add_numbers(3, 4, res);
    DBMS_OUTPUT.PUT_LINE('Result: ' || res);
END;
/

在上述代码中，我们定义了一个过程 add_numbers，该过程接受两个整数参数，将它们相加并将结果存储在输出参数 result 中。然后我们调用该过程，并传递参数 3 和 4。最后，我们使用 DBMS_OUTPUT 语句将结果打印到控制台上。

创建函数并调用：

创建一个简单的示例函数，实现将传入的两个整数相加并返回结果：

创建函数：

CREATE OR REPLACE FUNCTION add_numbersnum1 IN NUMBER, num2 IN NUMBER) RETURN NUMBER
IS
BEGIN
    RETURN num1 + num2;
END;
/

调用函数：

DECLARE
    res NUMBER;
BEGIN
    res := add_numbers(3, 4);
    DBMS_OUTPUT.PUT_LINE('Result: ' || res);
END;
/

在上述代码中，我们定义了一个函数 add_numbers，该函数接受两个整数参数，将它们相加并返回结果。我们调用该函数，并传递参数 3 和 4，然后将返回值存储在变量 res 中。最后，我们使用 DBMS_OUTPUT 语句将结果打印到控制台上。

创建包并调用：

创建一个示例包，并调用该包中的过程和函数：

创建包：

CREATE OR REPLACE PACKAGE my_package IS
    PROCEDURE add_numbers (num1 IN NUMBER, num2 IN NUMBER, result OUT NUMBER);
    FUNCTION multiply_numbers (num1 IN NUMBER, num2 IN NUMBER) RETURN NUMBER;
END my_package;
/

CREATE OR REPLACE PACKAGE BODY my_package IS
    PROCEDURE add_numbers (num1 IN NUMBER, num2 IN NUMBER, result OUT NUMBER)
    IS
    BEGIN
        result := num1 + num2;
    END;

    FUNCTION multiply_numbers (num1 IN NUMBER, num2 IN NUMBER) RETURN NUMBER
    IS
    BEGIN
        RETURN num1
    END;
END my_package调```
DECLARE
    res1 NUMBER;
    res2 NUMBER;
BEGIN
    my_package.add_numbers(3, 4, res1);
    my_package.multiply_numbers(3, 4, res2);
    DBMS_OUTPUT.PUT_LINE('Result of add: ' || res1);
    DBMS_OUTPUT.PUT_LINE('Result of multiply: ' || res2);
END;
/

在上述代码中，我们定义了一个名为 “my_package” 的，并在包中创建了一个 “add_numbers” 过程和一个 “multiply_numbers” 函数。我们在声明部分声明了两个变量“res1”和“res2”，然后在开始部分分别调用了包中的过程和函数，并将结果存储在这两个变量中。最后，我们使用 DBMS_OUTPUT 语句打印了两个结果值。

创建触发器并调用：

创建一个触发器，当一个新的行插入到测试表中时，自动将当前日期插入到另一个指定的日期列中。示例代码：

创建触发器：

CREATE OR REPLACE TRIGGER my_trigger
BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
    :NEW.date_column := SYSDATE;
END;
/

在上述代码中，我们创建了一个名为 “my_trigger” 的触发器，它会在新记录插入my 的表中时被调用。当触发器被激活时，它系统日期填充到名为 “date_column” 的列中。

由于触发器不是通过显式调用来执行的，因此在此示例中无法提供对触发器的调用代码。触发器是在满足其定义条件时自动触发的，具体条件在示例代码中显示。

总之，创建过程、函数、包和触发器可以提高编程效率，使数据库应用程序更加强大和灵活。具体的调用方式取决于它们各自的特性和定义条件。

一

题目：正确认识全球能源安全形势

内容摘要：
在当今世界中，能源是一个非常重要的话题。随着全球人口的不断增加和城市化的快速发展，相应地对能源的需求也日益增加。能源是现代社会的支柱，它涉及各个领域，包括交通、制造业、医疗、教育、采矿等等。能源的供求关系和安全问题越来越被人们所重视。本文将对全球能源安全形势进行分析，旨在正确认识能源安全的重要性、特点以及对全球经济、政治和社会的影响。

关键词：能源安全、全球、供需关系、风险、政策推动

一、全球能源安全的重要性
能源安全是指能源的供应、储备和利用不会因战争、短缺、涨价、灾害等原因出现中断和不安全等情况的状态。全球能源安全的保障直接涉及到企业、国家、政府以及个人的利益。能源是世界上最基本的生产要素，没有能源，先进的制造技术和高水平的创新都没有用武之地。能源是调动社会生产力和维护社会稳定的重要支撑。在能源紧缺的情况下，工业生产无法正常进行，交通运输无法保持正常运营，社会大众生活状况也将受到影响。

二、全球能源安全的挑战和风险
能源供需的严峻形势是当前全球能源安全面临的主要挑战。全球经济和能源消费的增长率显然超过了能源产量和供应的速度，这导致了全球上涨的油价、能源储备的短缺，以及难以控制的能源消费增长速度。例如，中国和印度的经济增长已经超过30年，人口快速增加且消费的能源不断增加，这导致了全球能源市场受到巨大的压力。此外，政治和地缘策略的因素也对全球能源安全产生了不小的影响，例如石油和天然气的管道或出口国家之间的紧张关系、恶劣气候和能源设施遭受的自然灾害等。

三、政策推动全球能源安全
全球能源安全不仅在政治、经济和社会层面上有着重要的影响，在国际公共卫生、环保和海洋保护等方面也有着巨大的挑战。为此，面对严峻的能源安全形势，政府和各利益相关方应该通过一系列政策措施来推动全球能源安全。对于各国政府而言，应该优先考虑保障国民的能源安全需求，同时也要打造绿色的能源体系，争取在全球新能源资源的开发方面取得领先地位。另外，应该加大对能源互联网和能源储存技术的支持力度，促进能源生产和消费之间的平衡，推动全球能源合作与创新。

在企业层面上，应该通过技术创新和发展，实现能源的可持续利用，保证生产运转的流畅和创新发展的推动。此外，还应该加强对员工的培训和意识教育，强化能源安全的意识并有效控制企业能源消耗。

四、未来展望
随着能源需求的不断增加，全球能源安全问题将会更加突出且复杂。为了确保全球能源的供应、储备和利用的可持续性和安全性，各国政府和利益相关方将不断采取政策措施来推动全球能源安全。此外，未来还会出现更多的新能源技术，例如太阳能、风能、地热能和水能等，这将为全球能源安全问题的解决提供更好的选择和可能性。同时，在应对全球气候变化问题的过程中，可再生能源的使用和推广也将成为全球能源安全的主要方向。

总之，全球能源安全问题是一个真正的全球性挑战，需要政府和各利益相关方共同参与，推动能源的绿色、可持续发展，并加强能源安全意识，积极应对全球能源安全面临的复杂形势。

二

题目：正确认识全球能源安全形势

内容摘要： 随着全球经济的快速发展，能源安全已经成为了各国政府高度关注的问题。本文将从以下几个方面来分析全球能源安全形势：第一，全球能源安全的概念与意义；第二，全球能源资源的供应与需求现状；第三，全球能源市场的竞争格局；第四，面临的挑战与解决途径；最后，未来的走向和建议。

关键词：能源安全，全球能源，供需现状，竞争格局，挑战与解决途径，未来走向

一：全球能源安全的概念与意义 能源安全是指保障一个国家或地区的能源可靠供应，以满足经济和社会发展所需的能量的安全状态。能源安全对于一个国家的经济、社会、政治稳定和国际地位都具有重要影响。如果缺乏能源安全，将会带来诸多不利的后果，如经济衰退、社会动荡、国际竞争力下降等。

二：全球能源资源的供应与需求现状 随着全球人口的持续增长和经济的快速发展，对能源的需求也在不断增加。同时，能源的供给却面临着许多挑战，如能源资源的有限性、环境污染和气候变化等。目前，全球主要能源消耗国家之一的中国，对于能源的需求已经超过了自身生产能力，需要依赖进口。而在全球范围内，化石能源（如石油、天然气和煤炭）仍是主要的能源来源，但随着可再生能源（如太阳能、风能和水能）的广泛应用，未来能源需求结构将会发生重大变化。

三：全球能源市场的竞争格局 在争夺全球能源市场上，各个国家都在积极推动能源行业的发展。尤其是在中东地区，拥有丰富的石油资源，成为全球石油市场的主要供应国。而在欧洲、美洲以及亚洲地区，都有强大的能源需求国家，这些国家也在积极争夺全球能源市场的话语权。此外，可再生能源的崛起也给全球能源市场带来了新的竞争格局。

四：面临的挑战与解决途径 全球能源面临的主要挑战包括：一是能源的高消耗和高浪费；二是化石能源的有限性和环境问题；三是国际政治环境和供应风险等。为了应对这些挑战，各个国家需要采取一系列措施，如加强能源技术创新、促进能源节约与效率提升、发展可再生能源等。此外，应加强国际合作，共同应对全球能源安全的挑战。

五：未来的走向和建议 未来全球能源发展将会面临更多的变数，需要各个国家共同应对。在未来，可再生能源将会成为全球能源供给体系中的重要组成部分。同时，各个国家也应该在制定相关政策时注重环保、节约、高效等方面的考虑。建议各个国家加强合作，共同研究新能源技术，推动能源领域的创新与发展，实现全球能源安全的可持续发展。

总结： 本文从全球能源安全的概念与意义、全球能源资源的供应与需求现状、全球能源市场的竞争格局、面临的挑战与解决途径和未来的走向和建议等方面进行了分析。全球能源安全是全球各个国家必须共同关注的问题，需要采取综合措施应对。只有真正实现全球能源安全，才能保障全球的经济和社会发展，并且让人们享受到更加美好的生活。

三

题目：正确认识全球能源安全形势

内容摘要：
随着全球经济的快速发展和人口的不断增加，能源已成为现代社会的基础源求关系和越来越引府和企业的重视。为了深入认识全球能源安全形势，本文从能源安全的概念和特点、全球能源安全的挑战，以及推动全球能源安全的政策措施等方面进行了研究分析，旨在推动全球能源安全的发展。

关键词：能源安全、全球、供给与需求、挑战、政策推动

一、能源安全的概念与特点
能源安全是指能源的供应、储备和利用不受威胁和干扰的状态。能源安全是现代社会发展的重要基础，它是人类经济社会持续发展和全球可持续发展的键要素。能源主要特点包括：

1. 稳定安全性：保证能源的供应和储备的稳定性，不受短期风险和威胁的干扰。

2. 经济性源，济发展和社会稳定。

3. 环保性：能源开采的过程环境、人类健康造成的负面影响得到有效控制。

4. 可持续性：充分考虑能源储备的可持续性，推广和发展绿色、清洁、可再生能源。

二、全球能源安全的挑战和问题
全球能源安全问题与发展阶段、经济体系、国家和地缘政治等因素有关。随着全球人口的增加和城市化的快速发展、新兴市场和发展起能源供应压力持续大。下面将探讨全球能源安全面临的挑战。

1. 供需矛盾日益加剧：济的飞速增长推动了对能源的需求迅速增加，但能源供依旧难以满足需致全球能源市场的矛盾日益加剧。

2. 能源气候变化：人类活动导致的二氧化碳排放和全球气温上升，加剧了能源气候变化的问题，增加了全球能源供应的不稳定性。

3. 地缘政治的影响：能源生产国、中转国和消费国之间的竞争和关系对全球能源安全产生了不小的影响，能源市场的稳定也使全球能源安全面临风险。

4. 能源安全的技术挑战：能源生产和储存技术的发展是关键，然而搞气化、油页岩开采以及天然气水合物等技术也面临着技术成本高和环境污染等问题。

三、推动全球能源安全的政策措施
为了应对全球能源安全面临的挑战和问题，采取有效的政策措施是非常必要的。在此过程中，需要各国政府、企业和各利益相关方共同努力、推动全球能源安全发展。具体的措施如下：

1. 推广可持续能源，促进绿色和清洁能源的发展，开发和利用可再生能源，减少对传统能源的依赖。

2. 采用新的技术手段，例如提高能源效率，降低能源消耗；并加强新的技术研发，例如能源互联网、先进燃气轮机等，以提高能源生产、储存和供应的效率和稳定性。

3. 改善能源市场的定价机制，实行市场化改革，提高市场透明度和公平性，降低能源市场的风险和波动性。

4. 发挥国际合作的重要作用，国际合作既可以促进全球能源稳定供应，也可以实现能源安全性和环境保护的目标。

四、推动全球能源安全的政策措施
为了应对全球能源安全面临的挑战和问题，采取有效的政策措施是非常必要的。在此过程中，需要各国政府、企业和各利益相关方共同努力、推动全球能源安全发展。具体的措施如下：

1. 推广可持续能源，促进绿色和清洁能源的发展，开发和利用可再生能源，减少对传统能源的依赖。

2. 采用新的技术手段，例如提高能源效率，降低能源消耗；并加强新的技术研发，例如能源互联网、先进燃气轮机等，以提高能源生产、储存和供应的效率和稳定性。

3. 改善能源市场的定价机制，实行市场化改革，提高市场透明度和公平性，降低能源市场的风险和波动性。

4. 发挥国际合作的重要作用，国际合作既可以促进全球能源稳供应，也可以实现能源安全性和环境保护的目标。

五、未来展望
展望未来，应在政策层面上加强对能源安全的重视，进一步推动全球能源可持续发展。可以通过采用包括采取节能、降低能耗、加强管理和利用可再生能源等在内的多种策略，非减空气污染和控制二氧化碳排放等，推动全球能源生产、消费和供应之间的平衡，实现能源持续、稳定、可靠的发展。

在技术创新方面，可以引进更多的科学技术，推广先进技术，完善技术体系并加强能源安全领域的科研投入，实现能源生产和消费的高效和可持续。

此外，国际合作能够促进国际贸易、本地产业以及全球性的政策措施变革。各国可以加强能源之间的合作，共享技术、人才和经验，共同应对全球能源安全问题。例如，可以加强国际能源协会等的发展，共同制定行业标准和规范，增强国际能源界组织以及企业之间的信息交流等。

结语：
本文回顾了全球能源安全的挑战和相关政策措施，并提出了全球能源安全的解决方案。随着全球化的深入发展和全球能源需求的不断增加，各国政府、企业和个人都应高度重视全球能源安全形势，加强合作，创新发展，实现能源可持续发展，保障经济社会的持续稳定发展。

CDC（Change Data Capture）

MySQL到CDC（Change Data Capture）是一种数据库管理技术，它允许在数据库发生变化时捕获并传输变更数据。CDC可以用于许多用例，例如数据同步、数据复制、实时分析和实时报表生成等。

在MySQL中，要实现CDC，可以使用以下方法：

1. 二进制日志（Binary Log）：MySQL的二进制日志是记录数据库变更的事务日志。通过启用二进制日志，并使用MySQL提供的工具（如mysqlbinlog），可以将更改数据解析为易于处理的格式。

2. MySQL复制：MySQL复制机制通过将变更数据从主数据库实时传输到一个或多个从数据库来实现数据复制。可以使用主从复制来实现CDC，其中主数据库记录所有变更，并将其复制到从数据库。

3. 存储过程/触发器：MySQL的存储过程和触发器功能可以用于捕获和处理数据库变更。可以编写自定义的存储过程或触发器，以在发生变更时执行特定的操作，例如将变更数据存储在其他表中或发送到其他系统。

4. 第三方工具：还有许多第三方工具可用于实现MySQL到CDC，如Debezium、Maxwell、Tungsten Replicator等。这些工具提供了更高级的功能，如实时数据捕获、变更数据解析和传输等。

无论使用哪种方法，MySQL到CDC可以帮助实现实时数据同步和实时分析等用例，提高数据库的灵活性和可扩展性。

MVCC （multi-version-concurrency-control）

MVCC–多版本并发控制机制 - 数据是宝 - 博客园 (cnblogs.com)

看一遍就理解：MVCC原理详解

捡田螺的小男孩

2021-10-07 11:4818083

前言

MVCC实现原理是一道非常高频的面试题，最近技术讨论群的小伙伴一直在讨论，趁着国庆节有空，我们一起来聊聊。

• 公众号：捡田螺的小男孩

1. 相关数据库知识点回顾

1.1 什么是数据库事务，为什么要有事务

事务，由一个有限的数据库操作序列构成，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。

假如A转账给B 100 元，先从A的账户里扣除 100 元，再在 B 的账户上加上 100 元。如果扣完A的100元后，还没来得及给B加上，银行系统异常了，最后导致A的余额减少了，B的余额却没有增加。所以就需要事务，将A的钱回滚回去，就是这么简单。

为什么要有事务呢？ 就是为了保证数据的最终一致性。

1.2 事务包括哪几个特性？

事务四个典型特性，即ACID，原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

• 原子性： 事务作为一个整体被执行，包含在其中的对数据库的操作要么全部都执行，要么都不执行。
• 一致性： 指在事务开始之前和事务结束以后，数据不会被破坏，假如A账户给B账户转10块钱，不管成功与否，A和B的总金额是不变的。
• 隔离性： 多个事务并发访问时，事务之间是相互隔离的，一个事务不应该被其他事务干扰，多个并发事务之间要相互隔离。。
• 持久性： 表示事务完成提交后，该事务对数据库所作的操作更改，将持久地保存在数据库之中。

1.3 事务并发存在的问题

事务并发会引起脏读、不可重复读、幻读问题。

1.3.1 脏读

如果一个事务读取到了另一个未提交事务修改过的数据，我们就称发生了脏读现象。

假设现在有两个事务A、B：

• 假设现在A的余额是100，事务A正在准备查询Jay的余额
• 事务B先扣减Jay的余额，扣了10，但是还没提交
• 最后A读到的余额是90，即扣减后的余额

因为事务A读取到事务B未提交的数据,这就是脏读。

1.3.2 不可重复读

同一个事务内，前后多次读取，读取到的数据内容不一致

假设现在有两个事务A和B：

• 事务A先查询Jay的余额，查到结果是100
• 这时候事务B 对Jay的账户余额进行扣减，扣去10后，提交事务
• 事务A再去查询Jay的账户余额发现变成了90

事务A被事务B干扰到了！在事务A范围内，两个相同的查询，读取同一条记录，却返回了不同的数据，这就是不可重复读。

1.3.3 幻读

如果一个事务先根据某些搜索条件查询出一些记录，在该事务未提交时，另一个事务写入了一些符合那些搜索条件的记录（如insert、delete、update），就意味着发生了幻读。

假设现在有两个事务A、B：

• 事务A先查询id大于2的账户记录，得到记录id=2和id=3的两条记录
• 这时候，事务B开启，插入一条id=4的记录，并且提交了
• 事务A再去执行相同的查询，却得到了id=2,3,4的3条记录了。

事务A查询一个范围的结果集，另一个并发事务B往这个范围中插入新的数据，并提交事务，然后事务A再次查询相同的范围，两次读取到的结果集却不一样了，这就是幻读。

1.4 四大隔离级别

为了解决并发事务存在的脏读、不可重复读、幻读等问题，数据库大叔设计了四种隔离级别。分别是读未提交，读已提交，可重复读，串行化（Serializable）。

1.4.1 读未提交

读未提交隔离级别，只限制了两个数据不能同时修改，但是修改数据的时候，即使事务未提交，都是可以被别的事务读取到的，这级别的事务隔离有脏读、重复读、幻读的问题；

1.4.2 读已提交

读已提交隔离级别，当前事务只能读取到其他事务提交的数据，所以这种事务的隔离级别解决了脏读问题，但还是会存在重复读、幻读问题；

1.4 3 可重复读

可重复读隔离级别，限制了读取数据的时候，不可以进行修改，所以解决了重复读的问题，但是读取范围数据的时候，是可以插入数据，所以还会存在幻读问题；

1.4.4 串行化

事务最高的隔离级别，在该级别下，所有事务都是进行串行化顺序执行的。可以避免脏读、不可重复读与幻读所有并发问题。但是这种事务隔离级别下，事务执行很耗性能。

1.4.5 四大隔离级别，都会存在哪些并发问题呢

隔离级别	脏读	不可重复读	幻读
读未提交	√	√	√
读已提交	×	√	√
可重复读	×	×	√
串行化	×	×	×

1.5 数据库是如何保证事务的隔离性的呢？

数据库是通过加锁，来实现事务的隔离性的。这就好像，如果你想一个人静静，不被别人打扰，你就可以在房门上加上一把锁。

加锁确实好使，可以保证隔离性。比如串行化隔离级别就是加锁实现的。但是频繁的加锁，导致读数据时，没办法修改，修改数据时，没办法读取，大大降低了数据库性能。

那么，如何解决加锁后的性能问题的？

答案就是,MVCC多版本并发控制！它实现读取数据不用加锁，可以让读取数据同时修改。修改数据时同时可读取。

2. 什么是 MVCC？

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

通俗的讲，数据库中同时存在多个版本的数据，并不是整个数据库的多个版本，而是某一条记录的多个版本同时存在，在某个事务对其进行操作的时候，需要查看这一条记录的隐藏列事务版本id，比对事务id并根据事物隔离级别去判断读取哪个版本的数据。

数据库隔离级别读已提交、可重复读 都是基于MVCC实现的，相对于加锁简单粗暴的方式，它用更好的方式去处理读写冲突，能有效提高数据库并发性能。

3. MVCC实现的关键知识点

3.1 事务版本号

事务每次开启前，都会从数据库获得一个自增长的事务ID，可以从事务ID判断事务的执行先后顺序。这就是事务版本号。

3.2 隐式字段

对于InnoDB存储引擎，每一行记录都有两个隐藏列trx_id、roll_pointer，如果表中没有主键和非NULL唯一键时，则还会有第三个隐藏的主键列row_id。

列名	是否必须	描述
row_id	否	单调递增的行ID，不是必需的，占用6个字节。
trx_id	是	记录操作该数据事务的事务ID
roll_pointer	是	这个隐藏列就相当于一个指针，指向回滚段的undo日志

3.3 undo log

undo log，回滚日志，用于记录数据被修改前的信息。在表记录修改之前，会先把数据拷贝到undo log里，如果事务回滚，即可以通过undo log来还原数据。

可以这样认为，当delete一条记录时，undo log 中会记录一条对应的insert记录，当update一条记录时，它记录一条对应相反的update记录。

undo log有什么用途呢？

1. 事务回滚时，保证原子性和一致性。
2. 用于MVCC快照读。

3.4 版本链

多个事务并行操作某一行数据时，不同事务对该行数据的修改会产生多个版本，然后通过回滚指针（roll_pointer），连成一个链表，这个链表就称为版本链。如下：

其实，通过版本链，我们就可以看出事务版本号、表格隐藏的列和undo log它们之间的关系。我们再来小分析一下。

1. 假设现在有一张core_user表，表里面有一条数据,id为1，名字为孙权：

1. 现在开启一个事务A： 对core_user表执行update core_user set name ="曹操" where id=1,会进行如下流程操作

• 首先获得一个事务ID=100
• 把core_user表修改前的数据,拷贝到undo log
• 修改core_user表中，id=1的数据，名字改为曹操
• 把修改后的数据事务Id=101改成当前事务版本号，并把roll_pointer指向undo log数据地址。

3.5 快照读和当前读

快照读： 读取的是记录数据的可见版本（有旧的版本）。不加锁,普通的select语句都是快照读,如：

csharp
复制代码select * from core_user where id > 2;

当前读：读取的是记录数据的最新版本，显式加锁的都是当前读

csharp
复制代码select * from core_user where id > 2 for update;
select * from account where id>2 lock in share mode;

3.6 Read View

• Read View是什么呢？ 它就是事务执行SQL语句时，产生的读视图。实际上在innodb中，每个SQL语句执行前都会得到一个Read View。
• Read View有什么用呢？ 它主要是用来做可见性判断的，即判断当前事务可见哪个版本的数据~

Read View是如何保证可见性判断的呢？我们先看看Read view 的几个重要属性

• m_ids:当前系统中那些活跃(未提交)的读写事务ID, 它数据结构为一个List。
• min_limit_id:表示在生成ReadView时，当前系统中活跃的读写事务中最小的事务id，即m_ids中的最小值。
• max_limit_id:表示生成ReadView时，系统中应该分配给下一个事务的id值。
• creator_trx_id: 创建当前read view的事务ID

Read view 匹配条件规则如下：

1. 如果数据事务ID trx_id < min_limit_id，表明生成该版本的事务在生成Read View前，已经提交(因为事务ID是递增的)，所以该版本可以被当前事务访问。
2. 如果trx_id>= max_limit_id，表明生成该版本的事务在生成ReadView后才生成，所以该版本不可以被当前事务访问。
3. 如果 min_limit_id =<trx_id< max_limit_id,需腰分3种情况讨论

• （1）.如果m_ids包含trx_id,则代表Read View生成时刻，这个事务还未提交，但是如果数据的trx_id等于creator_trx_id的话，表明数据是自己生成的，因此是可见的。
• （2）如果m_ids包含trx_id，并且trx_id不等于creator_trx_id，则Read View生成时，事务未提交，并且不是自己生产的，所以当前事务也是看不见的；
• （3）.如果m_ids不包含trx_id，则说明你这个事务在Read View生成之前就已经提交了，修改的结果，当前事务是能看见的。

4. MVCC实现原理分析

4.1 查询一条记录，基于MVCC，是怎样的流程

1. 获取事务自己的版本号，即事务ID
2. 获取Read View
3. 查询得到的数据，然后Read View中的事务版本号进行比较。
4. 如果不符合Read View的可见性规则， 即就需要Undo log中历史快照;
5. 最后返回符合规则的数据

InnoDB 实现MVCC，是通过 Read View+ Undo Log 实现的，Undo Log 保存了历史快照，Read View可见性规则帮助判断当前版本的数据是否可见。

4.2 读已提交（RC）隔离级别，存在不可重复读问题的分析历程

1. 创建core_user表，插入一条初始化数据,如下：

1. 隔离级别设置为读已提交（RC），事务A和事务B同时对core_user表进行查询和修改操作。

ini
复制代码事务A: select * fom core_user where id=1
事务B: update core_user set name =”曹操”

执行流程如下：

最后事务A查询到的结果是，name=曹操的记录，我们基于MVCC，来分析一下执行流程：

(1). A开启事务，首先得到一个事务ID为100

(2).B开启事务，得到事务ID为101

(3).事务A生成一个Read View，read view对应的值如下

变量	值
m_ids	100，101
max_limit_id	102
min_limit_id	100
creator_trx_id	100

然后回到版本链：开始从版本链中挑选可见的记录：

由图可以看出，最新版本的列name的内容是孙权，该版本的trx_id值为100。开始执行read view可见性规则校验：

ini
复制代码min_limit_id(100)=<trx_id（100）<102;
creator_trx_id = trx_id =100;

由此可得，trx_id=100的这个记录，当前事务是可见的。所以查到是name为孙权的记录。

（4). 事务B进行修改操作，把名字改为曹操。把原数据拷贝到undo log,然后对数据进行修改，标记事务ID和上一个数据版本在undo log的地址。

(5) 提交事务

(6) 事务A再次执行查询操作，新生成一个Read View，Read View对应的值如下

变量	值
m_ids	100
max_limit_id	102
min_limit_id	100
creator_trx_id	100

然后再次回到版本链：从版本链中挑选可见的记录：

从图可得，最新版本的列name的内容是曹操，该版本的trx_id值为101。开始执行Read View可见性规则校验：

ini
复制代码min_limit_id(100)=<trx_id（101）<max_limit_id（102);
但是,trx_id=101，不属于m_ids集合

因此，trx_id=101这个记录，对于当前事务是可见的。所以SQL查询到的是name为曹操的记录。

综上所述，在读已提交（RC）隔离级别下，同一个事务里，两个相同的查询，读取同一条记录（id=1），却返回了不同的数据（第一次查出来是孙权，第二次查出来是曹操那条记录），因此RC隔离级别，存在不可重复读并发问题。

4.3 可重复读（RR）隔离级别，解决不可重复读问题的分析

在RR隔离级别下，是如何解决不可重复读问题的呢？我们一起再来看下，

还是4.2小节那个流程，还是这个事务A和事务B，如下：

4.3.1 不同隔离级别下，Read view的工作方式不同

实际上，各种事务隔离级别下的Read view工作方式，是不一样的，RR可以解决不可重复读问题，就是跟Read view工作方式有关。

• 在读已提交（RC）隔离级别下，同一个事务里面，每一次查询都会产生一个新的Read View副本，这样就可能造成同一个事务里前后读取数据可能不一致的问题（不可重复读并发问题）。

begin
select * from core_user where id =1	生成一个Read View
/	/
/	/
select * from core_user where id =1	生成一个Read View

• 在可重复读（RR）隔离级别下，一个事务里只会获取一次read view，都是副本共用的，从而保证每次查询的数据都是一样的。

begin
select * from core_user where id =1	生成一个Read View
/
/
select * from core_user where id =1	共用一个Read View副本

4.3.2 实例分析

我们穿越下，回到刚4.2的例子，然后执行第2个查询的时候：

事务A再次执行查询操作，复用老的Read View副本，Read View对应的值如下

变量	值
m_ids	100，101
max_limit_id	102
min_limit_id	100
creator_trx_id	100

然后再次回到版本链：从版本链中挑选可见的记录：

从图可得，最新版本的列name的内容是曹操，该版本的trx_id值为101。开始执行read view可见性规则校验：

scss
复制代码min_limit_id(100)=<trx_id（101）<max_limit_id（102);
因为m_ids{100,101}包含trx_id（101），
并且creator_trx_id (100) 不等于trx_id（101）

所以，trx_id=101这个记录，对于当前事务是不可见的。这时候呢，版本链roll_pointer跳到下一个版本，trx_id=100这个记录，再次校验是否可见：

scss
复制代码min_limit_id(100)=<trx_id（100）< max_limit_id（102);
因为m_ids{100,101}包含trx_id（100），
并且creator_trx_id (100) 等于trx_id（100）

所以，trx_id=100这个记录，对于当前事务是可见的。即在可重复读（RR）隔离级别下，复用老的Read View副本，解决了不可重复读的问题。

4.4 网络江湖传说，MVCC是否解决了幻读问题呢？

网络江湖有个传说，说MVCC的RR隔离级别，解决了幻读问题，我们来一起分析一下。

4.4.1 RR级别下，一个快照读的例子，不存在幻读问题

由图可得，步骤2和步骤6查询结果集没有变化，看起来RR级别是已经解决幻读问题啦~

4.4.2 RR级别下，一个当前读的例子

假设现在有个account表，表中有4条数据，RR级别。

• 开启事务A，执行当前读，查询id>2的所有记录。
• 再开启事务B，插入id=5的一条数据。

流程如下：

显然，事务B执行插入操作时，阻塞了~因为事务A在执行select ... lock in share mode（当前读）的时候，不仅在id = 3,4 这2条记录上加了锁，而且在id > 2 这个范围上也加了间隙锁。

因此，我们可以发现，RR隔离级别下，加锁的select, update, delete等语句，会使用间隙锁+ 临键锁，锁住索引记录之间的范围，避免范围间插入记录，以避免产生幻影行记录，那就是说RR隔离级别解决了幻读问题？？？

4.4.3 这种特殊场景，似乎有幻读问题

其实，上图事务A中，多加了update account set balance=200 where id=5;这步操作，同一个事务，相同的sql，查出的结果集不同了，这个结果，就符合了幻读的定义~

参考与感谢

• 数据库基础（四）Innodb MVCC实现原理

问题

连接包超过MySQL服务器限制

Packet for query is too large (5,526,600 > 65,535). You can change this value on the server by setting the ‘max_allowed_packet’ variable.

个错误提示是由于MySQL服务器接收到的查询数据包大小超过了默认的最大限制（65,535字节）。要解决这个问题，你可以通过设置MySQL服务器的'max_allowed_packet'变量来增加允许的最大数据包大小。

你可以按照以下步骤进行设置：

1. 登录到MySQL服务器的命令行界面或使用MySQL客户端工具。

2. 运行以下命令来查看当前的'max_allowed_packet'值：

SHOW VARIABLES LIKE ‘max_allowed_packet’;

3. 如果当前值较小（例如默认的65,535），你可以使用以下命令来修改'max_allowed_packet'的值（以1MB为例）：

SET GLOBAL max_allowed_packet=1048576;

   注意：这个命令会立即生效，但只对当前会话有效。如果你希望永久修改该值，你需要修改MySQL服务器的配置文件（如my.cnf或my.ini）并重新启动MySQL服务。

4. 重新启动MySQL服务器，以使新的'max_allowed_packet'值生效。

请注意，增加'max_allowed_packet'的值可能会增加服务器的内存消耗。因此，你应该根据实际需求和服务器资源来选择合适的值。

如果你没有权限修改服务器的配置文件或无法解决该问题，请联系你的系统管理员或数据库管理员寻求进一步的帮助。

如果你需要更多关于'max_allowed_packet'变量的信息，可以使用以下查询词进行搜索：MySQL max_allowed_packet variable。

——————《日志》——————

binlog 日志

需要注意的是：

• binlog 是一种逻辑日志，他里边所记录的是一条 SQL 语句的原始逻辑，例如给某一个字段 +1，注意这个区别于 redo log 的物理日志（在某个数据页上做了什么修改）。
• binlog 文件写满后，会自动切换到下一个日志文件继续写，而不会覆盖以前的日志，这个也区别于 redo log，redo log 是循环写入的，即后面写入的可能会覆盖前面写入的。
• 一般来说，我们在配置 binlog 的时候，可以指定 binlog 文件的有效期，这样在到期后，日志文件会自动删除，这样避免占用较多存储空间。

binlog 的格式

binlog 有三种格式：

• Statement（Statement-Based Replication,SBR）：每一条会修改数据的 SQL 都会记录在 binlog 中。
• Row（Row-Based Replication,RBR）：不记录 SQL 语句上下文信息，仅保存哪条记录被修改。
• Mixed（Mixed-Based Replication,MBR）：Statement 和 Row 的混合体。

2.1.1 Statement

Statement 模式只记录执行的 SQL，不需要记录每一行数据的变化，因此极大的减少了 binlog 的日志量，避免了大量的 IO 操作，提升了系统的性能。

但是，正是由于 Statement 模式只记录 SQL，而如果一些 SQL 中包含了函数，那么可能会出现执行结果不一致的情况。比如说 uuid() 函数，每次执行的时候都会生成一个随机字符串，在 master 中记录了 uuid，当同步到 slave 之后，再次执行，就获取到另外一个结果了。

所以使用 Statement 格式会出现一些数据一致性问题。

2.2.2 Row

从 MySQL5.1.5 版本开始，binlog 引入了 Row 格式，Row 格式不记录 SQL 语句上下文相关信息，仅仅只需要记录某一条记录被修改成什么样子了。

Row 格式的日志内容会非常清楚的记录下每一行数据修改的细节，这样就不会出现 Statement 中存在的那种数据无法被正常复制的情况。

不过 Row 格式也有一个很大的问题，那就是日志量太大了，特别是批量 update、整表 delete、alter 表等操作，由于要记录每一行数据的变化，此时会产生大量的日志，大量的日志也会带来 IO 性能问题。

2.2.3 Mixed

从 MySQL5.1.8 版开始，MySQL 又推出了 Mixed 格式，这种格式实际上就是 Statement 与 Row 的结合。

在 Mixed 模式下，系统会自动判断该用 Statement 还是 Row：一般的语句修改使用 Statement 格式保存 binlog；对于一些 Statement 无法准确完成主从复制的操作，则采用 Row 格式保存 binlog。

Mixed 模式中，MySQL 会根据执行的每一条具体的 SQL 语句来区别对待记录的日志格式，也就是在 Statement 和 Row 之间选择一种。

relay log - 中继日志

图文结合带你搞懂MySQL日志之relay log（中继日志） - GreatSQL - 博客园 (cnblogs.com)

什么是中继日志（relay log）

中继日志（relay log）只在主从服务器架构的从服务器上存在。从服务器（slave）为了与主服务器(Master)保持一致，要从主服务器读取二进制日志的内容，并且把读取到的信息写入本地的日志文件中，这个从服务器本地的日志文件就叫中继日志。然后，从服务器读取中继日志，并根据中继日志的内容对从服务器的数据进行更新，完成主从服务器的数据同步。

搭建好主从服务器之后，中继日志默认会保存在从服务器的数据目录下。

文件名的格式是：从服务器名 - relay-bin.序号。中继日志还有一个索引文件：从服务器名 - relay-bin.index，用来定位当前正在使用的中继日志。

（主从复制原理图）

从服务器I/O线程将主服务器的二进制日志(binlog)读取过来记录到从服务器本地文件，然后从服务器SQL线程会读取中继日志的内容并应用到从服务器，从而使从服务器和主服务器的数据保持一致。

中继日志的作用

中继日志用于主从服务器架构中，从服务器用来存放主服务器二进制日志内容的一个中间文件。从服务器通过读取中继日志的内容，来同步主服务器上的操作。

中继日志是连接mastert(主服务器)和slave(从服务器)的信息，它是复制的核心，I/O线程将来自master的binlog存储到中继日志中，中继日志充当缓冲，这样master不必等待slave执行完成就可以发送下一个binlog。

查看中继日志

中继日志文件的格式与二进制日志文件相同，并且可以 使用 mysqlbinlog 进行读取

SET TIMESTAMP= 1615352328 /*!*/;
BEGIN
/*!*/;
# at 900
#211413 11:33:46 server id 1 end_log_pos 832 CRC32 0xcc16d651 Table_map:
`kaito`.`test` mapped to number 91
# at 950
#211413 11:33:46 server id 1 end_log_pos 872 CRC32 0x07e4047c Delete_rows: table id
91 flags: STMT_END_F -- server id 1 是主服务器，意思是主服务器删了一行数据
BINLOG '
CD95YBMBAAAAMgAAAEADAAAAAFsAAAAAAAEABGRlbW8ABHRlc3QAAQMAAQEBAFHWFsw=
CD95YCABAAAAKAAAAGgDAAAAAFsAAAAAAAEAAgAB/wABAAAAfATkBw==
'/*!*/;
# at 1000

这一段的意思是，主服务器（“server id 1”）对表 kaito.test 进行了 2 步操作：

• 定位到表 kaito.test 编号是 91 的记录，日志位置是 832
• 删除编号是 91 的记录，日志位置是 872

相关参数解析

通过语句：show variables like '%relay%' 查看先骨干的relay的所有相关参数如下：

mysql> show variables like '%relay%';
+---------------------------+---------------------------------------+
| Variable_name             | Value                                 |
+---------------------------+---------------------------------------+
| max_relay_log_size        | 0                                     |
| relay_log                 | kaito-relay-bin                       |
| relay_log_basename        | /var/lib/mysql/kaito-relay-bin        |
| relay_log_index           | /var/lib/mysql/kaito-relay-bin.index  |
| relay_log_info_file       | relay-log.info                        |
| relay_log_info_repository | TABLE                                 |
| relay_log_purge           | ON                                    |
| relay_log_recovery        | OFF                                   |
| relay_log_space_limit     | 0                                     |
| sync_relay_log            | 10000                                 |
| sync_relay_log_info       | 10000                                 |
+---------------------------+---------------------------------------+
11 rows in set (0.00 sec)

• max_relay_log_size：标记relay log 允许的最大值，如果该值为0，则默认值为max_binlog_size(1G)；如果不为0，则max_relay_log_size则为最大的relay_log文件大小；
• relay_log：定义relay_log的位置和名称，如果值为空，则默认位置在数据文件的目录（datadir），文件名默认为host_name-relay-bin.nnnnnn
• relay_log_index：同relay_log，定义relay_log的位置和名称；一般和relay-log在同一目录
• relay_log_info_file：设置relay-log.info的位置和名称（relay-log.info记录MASTER的binary_log的恢复位置和relay_log的位置）
• relay_log_purge：是否自动清空不再需要中继日志时。默认值为1(启用)。
• relay_log_recovery：当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性。默认情况下该功能是关闭的，将relay_log_recovery的值设置为 1时，可在slave从库上开启该功能，建议开启。
• relay_log_space_limit：防止中继日志写满磁盘，这里设置中继日志最大限额。
• • 注意！但此设置存在主库崩溃，从库中继日志不全的情况，不到万不得已，不推荐使用！
• sync_relay_log：这个参数和sync_binlog是一样的，当设置为1时，slave的I/O线程每次接收到master发送过来的binlog日志都要写入系统缓冲区，然后刷入relay log中继日志里，这样是最安全的，因为在崩溃的时候，你最多会丢失一个事务，但会造成磁盘的大量I/O
  当设置为0时，并不是马上就刷入中继日志里，而是由操作系统决定何时来写入，虽然安全性降低了，但减少了大量的磁盘I/O操作。这个值默认是0，可动态修改，建议采用默认值。
• sync_relay_log_info：这个参数和sync_relay_log参数一样，当设置为1时，slave的I/O线程每次接收到master发送过来的binlog日志都要写入系统缓冲区，然后刷入relay-log.info里，这样是最安全的，因为在崩溃的时候，你最多会丢失一个事务，但会造成磁盘的大量I/O。当设置为0时，并不是马上就刷入relay-log.info里，而是由操作系统决定何时来写入，虽然安全性降低了，但减少了大量的磁盘I/O操作。这个值默认是0，可动态修改，建议采用默认值。

以上只是简单的介绍了每个参数的作用，这些参数具体的设置还是需要根据每个用户的实际系统情况进行设置的；

范式

数据库设计过程中，范式（Normal Form）是一个用于衡量数据库表结构质量和规范的概念。常见的范式有一范式（1NF）、二范式（2NF）和三范式（3NF）。下面分别阐述它们的概念并举例说明。

1、第一范式（1NF）：

1NF是指数据库表中的每个属性都是不可再分的基本数据项。换句话说，1NF要求表中的每个字段都是原子性的，不能再拆分为更小的单位。

举例：
表1：用户信息表

字段	类型
ID	INT
姓名	VARCHAR(50)
年龄	INT
联系电话	VARCHAR(15)

在这个表中，每个字段都是不可再分的基本数据项，因此满足1NF。

2、第二范式（2NF）：

2NF要求数据库表中的非主属性完全依赖于主键，即非主属性只依赖于主键，不依赖于其他非主属性。

举例：
表2：订单信息表

字段	类型
订单ID	INT
订单日期	DATETIME
客户ID	INT
客户姓名	VARCHAR(50)
客户联系电话	VARCHAR(15)

在这个表中，订单ID是主键，但非主属性“客户姓名”和“客户联系电话”依赖于非主键“客户ID”，不符合2NF。为了满足2NF，可以将表2拆分为两个表：

表2-1：订单信息表

字段	类型
订单ID	INT
订单日期	DATETIME
客户ID	INT

表2-2：客户信息表

字段	类型
客户ID	INT
客户姓名	VARCHAR(50)
客户联系电话	VARCHAR(15)

通过拆分，每个表都只包含了与主键相关的非主属性，满足了2NF。

3、第三范式（3NF）：

3NF要求数据库表中的非主属性互不依赖，即非主属性之间没有直接的关系。

举例：
表3：员工信息表

字段	类型
员工ID	INT
员工姓名	VARCHAR(50)
职位	VARCHAR(50)
部门ID	INT
部门名称	VARCHAR(50)

在这个表中，非主属性“部门ID”依赖于主键“员工ID”，同时非主属性“部门名称”依赖于非主属性“部门ID”，造成了传递依赖。为了满足3NF，可以将表3拆分为两个表：

表3-1：员工信息表

字段	类型
员工ID	INT
员工姓名	VARCHAR(50)
职位	VARCHAR(50)
部门ID	INT

表3-2：部门信息表

字段	类型
部门ID	INT
部门名称	VARCHAR(50)

通过拆分，每个表中的非主属性之间没有依赖关系，满足了3NF。

LSN 和 checkpoints

MySQL中 LSN 和 Checkpoint 关系 - 知乎 (zhihu.com)

为什么需要Checkpoint技术？

考虑如下场景：假如redo-log重做日志可以无限增加，并且buffer-pool数据池足够大，那么就不需要将MySQL写入的缓冲池的数据页面刷回到磁盘。因为当主机crash时候，恢复数据库变得很容易，从无限容量的redo重做日志中对磁盘中的数据页面进行重放就可以了。

但这有必须有至少二个前提条件：

• 数据库的buffer-pool可以缓存所有的页面数据
• Redo-log重做日志可以无限增长

显然上面二个前提约束条件是不可能成立的。

为了解决如下问题，checkpoint检查点技术应运而生：

• 缩短数据库从crash中恢复的时间
• MySQL buffer-pool缓冲池有上限，脏页面需要定时刷新回磁盘
• 重做日志redo-log不能无限增长

有了checkpoint技术，当数据库从crash恢复时，数据库不需要重放执行所有的重做日志，因为checkpoint检查点（小于checkpoint_lsn的值）前面的脏页面已经安全刷回磁盘了。数据库只需要执行检查点之后的脏页面来进行恢复，从而大大减少了恢复的时间。

在讨论实现机制前，还需要先讨论一些关键概念。

MySQL在持久化与性能之间做了很多权衡与折衷。数据记录的修改首先写入buffer-pool内存中，但是为了将随机IO转换成顺序IO，会将数据记录对数据库修改所对应的物理日志写入redo-log缓存中。更进一步，或直接或间接的后台线程将数据写入OS buffer后，最后再同步磁盘中。

Buffer pool -> log Buffer -> os Buffer -> Log files on disk

LSN概述

LSN (log sequence number)是日志的逻辑序列号，在InnoDB存储引擎中，LSN的值会随着日志的写入而逐渐增大。新的日志LSN等于旧的LSN加上新增日志的大小。

LSN的分类？

每个页面都有 LSN，重做日志redo-log也有 LSN，检查点checkpoint也有 LSN。可以使用如下命令来观察:

mysql> show engine innodb status\G;

其中：

• log sequence number: 代表当前的重做日志redo log(in buffer)在内存中的LSN
• log flushed up to: 代表刷到redo log file on disk中的LSN
• pages flushed up to: 代表已经刷到磁盘数据页上的LSN
• last checkpoint at: 代表上一次检查点所在位置的LSN

LSN的存储位置？

LSN不仅存在于重做日志redo log，还存在于普通数据页面中，每个数据页面的头部有FIL-PAGE_LSN字段，它记录了当前数据页最后一次被修改时日志序列LSN值。

根据上图流程，更新一条记录时：

• 先写内存数据页面（内存的数据页面有LSN字段）
• 然后写缓冲内存的重做日志redo-log（内存的重做日志redo-log记录有LSN字段）
• 最后提交时innodb_flush_log_at_trx_commit写重做日志redo-log进磁盘文件（磁盘的redo-log日志记录有pageLSN字段）

简言之，LSN这个值在几个地方存储：

• 在数据页面(内存与磁盘）存储LSN
• 在重做日志redo-log(内存)存储LSN
• 在重做日志redo-log(磁盘)存储LSN
• Checkpoint对应的LSN也是存储在重做日志记录中

恰好对应上文:

mysql> show engine innodb status\G;

• log sequence number: 代表当前的重做日志redo log(in buffer)在内存中的LSN
• log flushed up to: 代表刷到redo log file on disk中的LSN
• pages flushed up to: 代表已经刷到磁盘数据页上的LSN
• last checkpoint at: 代表上一次检查点所在位置的LSN

问题1：MySQL怎么判断脏页？

基于数据页面的LSN，可以了解当前数据页面的版本。MySQL系统在更新记录时，会在对应的重做日志redo-log中更新LSN的值，与此同时记录的数据页面的头部也会记录redo-log中的LSN最新的值。当MySQL刷脏数据页面时，磁盘中数据页面文件的LSN的值也会增加。

回到我们的问题，MySQL怎么判断脏页？

只需要判断这个页面的LSN值，如果数据页面的LSN的值大于 Checkpoint的LSN值，说明这个数据页面接受了新的更新（所有数据页面的LSN更新到更大的值）。那么这个页面就是脏页。

问题2：MySQL基于Checkpoint如何从crash中恢复？

MySQL在崩溃恢复时，会从重做日志redo-log的Checkpoint处开始执行重放操作。 它从last Checkpoint对应的LSN开始扫描redo-log日志，并将其应用到buffer-pool中，直到last Checkpoint对应的LSN等于 log flushed up to 对应的 LSN （也就是redo-log磁盘上存储的LSN值)，则恢复完成 。

说白了，检查点要做的操作就是将缓冲池中的数据页刷到磁盘，最终达到内存与外存数据页的一致。检查点的作用是縮短当数据库发生crash时数据库恢复所需要的时间。

尽管从理论上来说，由于重做日志redo-log记录了所有的页操作，当数据库crash时，恢复重做日志redo-log即可。但考虑到数据库是一个需要保证高可用的应用环境，从crash环境中快速恢复回来对服务的可用性是一个关键指标。因此检查点checkpoint机制就是将内存页写加磁盘，这样发生crash的时候，已经刷到磁盘的数据页就不再需要恢复，只需要恢复检查点之后的操作。

——————《文件》——————

表结构文件：*.frm

framework == frm

在MYSQL中建立任何一张数据表，在其数据存放目录对应的数据库目录下都有对应表的.frm文件,

.frm文件是用来保存每个数据表的元数据(meta)信息，包括表结构的定义等，

在MySQL中，“.frm”文件跟数据库存储引擎无关，任何存储引擎的数据表结构文件都存储为.frm文件，

命名方式为“tablename.frm”，如user.frm，.frm文件可以用来在数据库崩溃时恢复表结构。

独占表空间文件：*.ibd

在innodb_file_per_table配置为ON时设置为独占表空间，此时，系统将为每一个表单独存储为“.ibd”文件，

（如user.ibd）在此文件中，存储与该表相关的数据、索引、表的内部数据字典信息。

MyISAM

使用这个存储引擎，每个MyISAM在磁盘上存储成三个文件。

（1）frm文件：存储表的定义数据

（2）MYD文件：存放表具体记录的数据 == My Data

（3）MYI文件：存储索引 == My Index