MySQL 地基基础：事务和锁的面纱

什么是事务，为什么需要事务

在 MySQL 中，事务是由一条或多条 SQL 组成的单位，在这个单位中所有的 SQL 共存亡，有点有福同享，有难同当的意思。要么全部成功，事务顺利完成；要么只要有一个 SQL 失败就会导致整个事务失败，所有已经做过的操作回退到原始数据状态。

用日常细说事务的特性

首先我们先说一下事务的四个特性：ACID。

  • A：原子性（atomicity），一个事务要么全都成功，要么全都失败
  • C：一致性（consistency），在事务的整个生命周期里，查询的数据是一致的，保证数据库不会返回未提交的事务的数据
  • I：隔离性（isolation），一个事务所做的操作，在最终提交前，对其他事务是不可见的，保证事务与事务之间不会冲突
  • D：持久性（durability），只要事务提交，数据就不会丢失，即使系统崩溃，事务也已经完成

在日常生活中有很多的事情就能体现为数据库中的事务。比如“转账”，下面我们就具体展开，你就可以很清晰的认识事务的四个特性。

  • 时间：2020 年 1 月 1 日
  • 地点：某银行 ATM
  • 人物：A 和 B
  • 起因：B 向 A 借 1000 元人民币
  • 经过：A 转账给 B
  • 结果：转账成功或失败

这么一个转账我们想一下底层基本的技术支撑与实现，B 向 A 借钱，A 要转账给 B，首先 A 必须有大于 1000 元的余额，然后从 A 的账户减 1000 元，在 B 的账户里加 1000 元。

我们定义一个事务：

获取 A 账户余额
select balance from account where username='A';
在 A 账户里减 1000 元
update account set balance=balance-1000 where username='A';
获取 B 账户余额
select balance from account where username='B';
在 B 账户里加 1000 元
update account set balance=balance+1000 where username='B';

好了，一个简单事务基本就这样，我们开始分析分析这个事务是如何保证事务的 ACID 的。

  • 原子性：这个事务要么全成功，要么全失败。事务成功则 1000 元转账到了 B 账户，事务失败回滚则 1000 元还在 A 账户里，就是说 1000 元不能凭空消失。
  • 一致性：在这个事务中，所有的查询都是一致的，我们先查询 A 账户余额是否大于 1000，如果小于 1000，事务失败回滚；如果获取不到 B 账户余额，事务失败回滚。
  • 隔离性：在这个事务发生的同时，发生了另一个事务（A 通过手机银行将钱全部转移到另外的账户，比如一共有 1500 元），第一个事务转 1000 元，第二个事务转 1500 元，我们仔细想想，如果都成功，那岂不是凭空获取了 1000 元，这是不合理的，每个事务在执行前都应查一下余额是否够本次转账的。这两个事务应该是隔离的，不能有冲突。
  • 持久性：转账成功了（即事务完成），这代表钱已经发生了转移，这个时候发生 ATM 吞卡、ATM 断电、手机银行无法登陆等等一切故障，反正钱已经转走了，钱没有丢（即数据没有丢）

MySQL 并发控制技术

并发控制技术可以说是数据库的底层基础技术，并发控制技术可以拆分来看，一是并发，一是控制。并发也就是说大量请求连接到数据库，控制就是数据库要控制好这些连接，保证资源的可用性、安全性，解决资源的挣用的问题。

那么如何实现并控制呢？主要通过两个方面：

  • Lock
  • MVCC

先分别简单说一下 Lock 和 MVCC，具体的后面再聊。

  • Lock，并发连接到数据库，操作有读和读、读和写、写和写，锁来保证并发连接使得数据可以保持一致性。
  • MVCC（Multiversion Concurrency Control），多版本并发控制，是数据库的多版本，可以提高并发过程中的读和写操作，有效的避免写请求阻塞读请求。

面试再也不怕被问到的 MVCC

前面我们已经大致了解了 MVCC 是什么，以及他做什么事情，现在我们具体看看 MVCC 是如何工作的？

我们知道数据的一致性，可以通过锁来保证，在并发连接中，锁机制在读和读的并发请求中不会锁数据，但是在读和写的并发请求中，写请求会加锁，读请求会被写请求阻塞，基于此，MVCC 发挥其作用。

MVCC 控制两类操作：

  • 快照读：读取的是历史可见版本的数据，无锁
  • 当前读：读取的是当前最新版本的数据，加锁

我们举个例子说一下吧，比如:

mysql> create table tab1(id decimal,name varchar(10),address varchar(10),status decimal,primary key(id));
mysql> insert into tab1 values(1,'a','beijing',1);

表中数据为：

现在有一个请求，将数据 a 的地址改为 shanghai，这个数据更新的过程，我们细化一下，将历史数据置为失效，将新的数据插入：

mysql> update tab1 set status=0 where name='a';
mysql> insert into tab1 value(2,'a','shanghai',1);

表中数据为：

MVCC 的原理就类似是这样的，address='beijing' 就是历史数据，更新前保存了下来，address='shanghai' 就是当前数据，新插入数据，这样并发连接来了，既可以读取历史数据，也可以修改当前数据。比如，现在有三个事务：

  • T1 -> 要执行 update address
  • T2 -> 要执行 update address
  • T3 -> 要执行 update address

T1 先获取了表中这一行数据，执行了 update，未提交；T2 获取表中这一行数据，由于 T1 未提交，address=‘beijing’,这个 beijing 就来源历史数据；T3 也获取表中这一行数据，由于 T1 未提交，address='beijing'，这个 beijing 也来源历史数据。这样是不是好理解了。

以此类推，如果只对 name='a' 这一行数据有 N 个并发连接要做 M 个操作，这些历史数据都保存在表中，这个表的数据量无法预估，势必会造成压力与瓶颈。多版本数据到底如何保存，这就不是本节考虑的问题了，是数据库 undo 帮你做的工作。这里就不展开了。（后期可能会做 undo 相关的 chat，大家可以关注我）

简单易懂的实例帮你理解 MySQL 事务隔离级别

事务隔离级别，拆分来看，事务、隔离、级别，故是三个概念的集合，是保证事务之间相互隔离互不影响的，有多个级别。事务在执行过程中可能会出现脏读、不可重复读、幻读，那么 MySQL 的事务隔离级别到底有怎样的表现呢？

那么到底什么是脏读、不可重复读、幻读呢？

  • 脏读：一个事务读取了另一个未提交事务操作的数据。
  • 不可重复读：一个事务重新读取前面读取过的数据时，发现该数据已经被修改了或者不见了，其实已被另一个已提交的事务操作了。解决了脏读的问题。
  • 幻读：一个事务，需要更新数据，于是重新提交了一个查询，返回符合查询条件行，发现这些行因为其他提交的事务发生了改变，这些数据像“幻影”一样出现了。解决了不可重复读。

接下来我们用具体实例分析各个事务隔离级别。

创建测试表 t_account：

mysql> create table t_account(name varchar(10),balance decimal);
mysql> insert into t_account values('A',100);
mysql> insert into t_account values('B',0);

读未提交

设置事务隔离级别：

mysql> set global tx_isolation='read-uncommitted';

查询事务隔离级别：

mysql>  SELECT @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)

当前事务可以读取另一个未提交事务操作的数据。

环境：用户 A 有 100 元钱，给用户 A 增加 100 元，然后用户 A 转账给用户 B。

现在我们查询一下用户 A 和用户 B 的余额：

mysql> select * from t_account;
+------+---------+
| name | balance |
+------+---------+
| A    |    -100 |
| B    |     200 |
+------+---------+
2 rows in set (0.00 sec)

问题来了，这个结果不符合预期，用户 A 竟然是 -100 元，用户 B 增加了 200 元，这是因为事务 2 读取了事务 1 未提交的数据。

读提交

设置事务隔离级别：

mysql> set global tx_isolation='read-committed';

查询事务隔离级别：

mysql>  SELECT @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-COMMITTED    |
+------------------+
1 row in set (0.00 sec)

当前事务只能读取另一个提交事务操作的数据。

环境：用户 A 有 100 元钱，给用户 A 增加 100 元。

一个事务重新读取前面读取过的数据时，发现该数据已经被修改了，其实已被另一个已提交的事务操作了。

可重复读

设置事务隔离级别：

mysql> set global tx_isolation='repeatable-read';

查询事务隔离级别：

mysql>  SELECT @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| REPEATABLE-READ  |
+------------------+
1 row in set (0.00 sec)

当前事务读取通过第一次读取建立的快照是一致的，即使另外一个事务提交了该数据。除非自己这个事务可以读取在自身事务中修改的数据。

可重复读隔离级别是 MySQL 的默认隔离级别。

环境：用户 A 有 100 元钱，给用户 A 增加 100 元。

这就能看出来，事务 2 开启后读取了用户 A 的余额，即使事务 1 修改了数据，不管提交与否，事务 2 读取的数据一直是之前第一次读取的数据。继续操作。

为什么现在变成了 200 元了，因为事务 2 已经 commit，再次 select 是一个新的事务，读取数据当然又变为第一次获取数据（此时的数据是最新的数据）。

思考一下：上述这个举例是可重复读的 select 相关验证，如果是 DML 操作，会不会是同样的结果呢？

思考三分钟……

答案是：其他事物即使查询不到的数据，DML 操作也可能会影响那些提交的数据。好，让我验证一下。

update 操作：

delete 操作：

通过这两个例子你是不是了解了一个事务的 update 和 delete 操作了另外一个事务提交的数据，会使得这些数据在当前事务变得可见。就像幻影一下出现了！

序列化

设置事务隔离级别：

mysql> set global tx_isolation='serializable';

查询事务隔离级别：

mysql>  SELECT @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| SERIALIZABLE     |
+------------------+
1 row in set (0.00 sec)

当前事务 select 和 DML 操作的数据都会加行锁，其他事务访问同样的数据需要等锁释放。

环境：用户 A 有 100 元钱，给用户 A 增加 100 元。

好了，实例讲解到此结束，是否已经帮你理解了 MySQL 事务隔离级别。

另外，结合前面说的 MVCC，Read-Committed 和 Repeatable-Read，支持 MVCC；Read-Uncommitted 由于可以读取未提交的数据，不支持 MVCC；Serializable 会对所有读取的数据加行锁，不支持 MVCC。

MySQL 锁机制（机智）

锁是可以协调并发连接访问 MySQL 数据库资源的一种技术，可以保证数据的一致性。锁有两个阶段：加锁和解锁，InnoDB 引擎的锁主要有两类。

共享锁（S）

允许一个事务读取数据，阻塞其他事务想要获取相同数据。共享锁之间不互斥，读和读操作可以并行。代码展示：

select * from table where ... lock in share mode

排它锁（X）

持有排他锁的事务可以更新数据，阻塞其他事务获取数据的排他锁和共享锁。排它锁之间互斥，读和写、写和写操作不可以并行。代码展示：

select * from table where ... for update;

从 MySQL 数据库的内外区分锁，有两种锁。

内部锁

MySQL 在数据库内部自动管理，协调并发连接的资源争用。内部锁再具体来看分为：

  • 行锁：会话事务将访问的行数据加锁
  • 表锁：会话事务将访问的表整体加锁

外部锁

会话层使用特殊的手段显示获取锁，阻塞其他会话对数据的操作。我们通过外部操作命令实现外部锁，比如使用 lock table 和 unlock tables。

我们举个例子来描述一下这个过程吧，比如有事务 1 和事务 2，事务 1 锁定了一行数据，加了一个 S 锁；事务 2 想要对整个表加锁，需要判断这个表是否被加了表锁，表中的每一行是否有行锁。仔细想想这个过程是很快呢？还是非常的慢？如果表很小无所谓了，如果表是海量级数据，那糟了，事务 2 势必耗费很多资源。

如何解决事务 2 这种检索资源消耗的问题呢？事务意向锁帮你先获取意向，先一步问问情况，然后再获取我们想要的 S 和 X 锁，具体分为：

意向共享锁（IS）

事务 1 说：我要加一个行锁，我有这个意向，你们其他人有没有意见，如果没有我就先拿这个 IS 锁了。

意向排它锁（IX）

事务 2 说：我要加一个表锁，这个可是排他锁，我拿了你们就等我用完再说吧，我有这个意向，你们其他人有没有意见，如果没有我就先拿这个 IX 锁了。

前面这个举例，其过程升级优化为：

  • 事务 1 先申请获取 IS 锁，成功后，获取 S 锁
  • 事务 2 发现表中有 IS 锁了，事务 2 获取表锁会被阻塞

那么这四个锁之间兼容性如何呢？

聊几个经典死锁案例

在实际应用中经常发生数据库死锁的情况，那么什么是死锁呢？说白了就是事务 1 锁事务 2，事务 2 锁事务 1，这两个事务都在等着对方释放锁资源，陷入了死循环。

接下来我们介绍几个经典死锁案例，MySQL 默认级别使用的是 REPEATABLE-READ。

场景 1：insert 死锁

创建一个测试表：

mysql> create table t_insert(id decimal,no decimal,primary key(id),unique key(no));

session1：

mysql> begin;
mysql> insert into t_insert values(1,101);

session2：

mysql> begin;
mysql> insert into t_insert values(2,101);

此时会话一直等待无响应。

session1：

mysql> insert into t_insert values(3,100);

结果如下。

此时 session2 立马报出来死锁：

ERROR 1213 (40001): ==Deadlock== found when trying to get lock; try restarting transaction

数据库中 insert 作为最简单的 SQL，为什么会导致死锁呢？

session1 在插入(1,101) 的时候会加一个 X 锁；session2 插入(2,101)，no 字段有着唯一性，故 session2 在插入时数据库会做 duplicate 冲突检测，由于事务冲突先加 S 锁；然后 session1 又插入了 (3,100)，此时 session1 会加 insert intention X 锁（插入意向锁），之前 session1 已经有了 X 锁，故进入等待队列，结局就是 session1 和 session2 都在等待，陷入了僵局，MySQL 很机智，牺牲一方事务解决这个尴尬的局面，所以 session2 被干掉了，报错死锁。

场景 2：自增列死锁

自增列死锁问题和场景 1 的类似，比如将场景 1 的主键属性改为自增长属性，主键自增仍唯一，场景模拟类似，加锁的过程也类似，产生死锁的过程也类似，这里就不详细模拟了。

场景 3：rollback 死锁

创建一个测试表：

mysql> create table t_rollback(id decimal,no decimal,primary key(id),unique key(no));

session1：

mysql> begin;
mysql> insert into t_rollback values(1,100);

session2：

mysql> begin;
mysql> insert into t_rollback values(2,100);

此时会话一直等待无响应。

session3

mysql> begin;
mysql> insert into t_rollback values(3,100);

此时会话一直等待无响应。

session1

mysql> rollback;

结果如下： 此时 session1 执行了 rollback 成功返回，session2 的 insert 返回成功，session3 立马报出来死锁。

ERROR 1213 (40001): ==Deadlock== found when trying to get lock; try restarting transaction

为什么我回滚了事务，还要报死锁，难道我需要全部回滚吗？

session1 在插入 (1,100) 的时候会加一个 X 锁；session2 插入 (2,100)，no 字段有着唯一性，故 session2 在插入时数据库会做 duplicate 冲突检测，由于事务冲突先加 S 锁；session3 插入 (3,100)，no 字段有着唯一性，故 session3 在插入时数据库会做 duplicate 冲突检测，由于事务冲突先加 S 锁；session1 回滚，session2 申请 insert intention X 锁，等 session3;session3 申请 insert intention X 锁，等 session2，结局就是 session2 和 session3 都在等待，陷入了僵局，MySQL 很机智，牺牲一方事务解决这个尴尬的局面，所以 session3 被干掉了，报错死锁。

场景 4：commit 死锁

创建一个测试表：

mysql> create table t_commit(id decimal,no decimal,primary key(id),unique key(no));
mysql> insert into t_commit values(1,100);

session1：

mysql> begin;
mysql> delete from t_commit where id=1;

session2：

mysql> begin;
mysql> insert into t_commit values(1,100);

此时会话一直等待无响应。

session3：

mysql> begin;
mysql> insert into t_commit values(1,100);

此时会话一直等待无响应。

session1：

mysql> commit;

结果如下：此时 session1 执行了 commit 成功返回，session3 的 insert 返回成功，session2 立马报出来死锁。

ERROR 1213 (40001): ==Deadlock== found when trying to get lock; try restarting transaction

为什么我提交了事务，还要报死锁，难道我需要全部提交吗？

这个产生死锁的过程和场景 3rollback 死锁类似，大家可以和之前的 rollback 死锁产生过程对应来看。

小技巧——事务保存点帮你读档重闯关

玩游戏你是不是有过存档、读档的经历，过某一个比较难的关卡，先存档，过不了，就读档重新过。数据库中我们也可以如此，MySQL 事务保存点可以回滚到事务的某时间点，并且不用中止事务。下面举例说明一下。

用户 B 和用户 C 向用户 A 借钱，用户 A 转账给用户 B 和用户 C，转账的过程中发生了用户 C 账户不存在，那么我们也要把转给用户 B 的钱也取消吗？我们可以不取消，使用一个保存点即可。

查询用户 A 有 1000 元：

mysql> select balance from t_account where name='A';

转账 100 元给用户 B：

mysql> update t_account set balance=balance-100 where name='A';
mysql> update t_account set balance=balance+100 where name='B';

设置事务保存点

mysql> savepoint T_A_TO_B;

转账 200 元给用户 C：

mysql> update t_account set balance=balance-200 where name='A';
mysql> update t_account set balance=balance+200 where name='C';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

发现转账给 C 返回有 0 条受影响的行，转账给 C 未成功，此时用户 A 已经少了 200 元了，先退 200 元再排查吧，转账给用户 B 的不需要重新操作了。

mysql> rollback to T_A_TO_B;
mysql> commit；

根据提示 0 条受影响的行，也就是说用户 C 不存在呀，我们查询一下个用户信息：

mysql> select * from t_account where name='A';
+------+---------+
| name | balance |
+------+---------+
| A    |     900 |
+------+---------+
1 row in set (0.00 sec)

mysql> select * from t_account where name='B';
+------+---------+
| name | balance |
+------+---------+
| B    |     200 |
+------+---------+
1 row in set (0.00 sec)

mysql> select * from t_account where name='C';
Empty set (0.00 sec)

结果：用户 A 成功转 100 元给用户 B，用户 C 果然不存才，设置了保存点，帮我们省了很多工作，中途不用取消全部操作。

小技巧——一个死锁的具体分析方法

前面我们学习了事务、锁，以及介绍了几个经典死锁案例，当遇到死锁，我们怎样具体分析呢？

分析死锁，我们就需要看死锁的日志信息，通过日志具体找到死锁的原因及执行的语句。

首先，我们用前面的场景 1 模拟一个死锁。

然后，执行如下命令获取死锁信息：

mysql> show engine innodb status;

在打印的日志中，先看事务 1 的日志：

*** (1) TRANSACTION:
TRANSACTION 2179, ACTIVE 8 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 32, OS thread handle 140317789804288, query id 823 localhost root update
insert into t_insert values(2,101)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 37 page no 4 n bits 72 index no of table `test`.`t_insert` trx id 2179 lock mode S waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 5; hex 8000000065; asc     e;;
 1: len 5; hex 8000000001; asc      ;;
TRANSACTION 2179, ACTIVE ==8 sec== inserting

事务 1 持续了 8 秒：

mysql ==tables in use 1==, locked 1  涉及一张表  
LOCK WAIT 2 lock struct(s) 有两个锁  
insert into t_insert values(2,101) 这是 SQL 语句  
WAITING FOR THIS LOCK TO BE GRANTED 唯一行锁处于等待  
RECORD LOCKS space id 37 page no 4 n bits 72 index no 加锁的是索引字段 no  
lock mode S waiting 锁等待为 S 锁

事务 2 的日志：

*** (2) TRANSACTION:
TRANSACTION 2178, ACTIVE 17 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2
MySQL thread id 33, OS thread handle 140317663659776, query id 824 localhost root update
insert into t_insert values(3,100)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 37 page no 4 n bits 72 index no of table `test`.`t_insert` trx id 2178 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 5; hex 8000000065; asc     e;;
 1: len 5; hex 8000000001; asc      ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 37 page no 4 n bits 72 index no of table `test`.`t_insert` trx id 2178 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 5; hex 8000000065; asc     e;;
 1: len 5; hex 8000000001; asc      ;;
  • HOLDS THE LOCK(S)持有锁的内容
  • lock_mode X locks持有锁的锁等待内容是一个 x 锁
  • WAITING FOR THIS LOCK TO BE GRANTED等待锁的内容
  • lock_mode X locks gap before rec insert intention waiting等待锁的锁等待内容也是一个 x 锁

通过这些日志，我们发现日志中的事务 1，持有 S 锁，S 锁的出现是因为需要检查数据唯一性，我们的 no 字段确实有唯一索引，这一点也正好验证了。日志中的事务 1，持有一个 X 锁，又等待一个 X 锁。所以场景 1 中的两个事务都在锁等，造成了死锁。

小技巧——换种思路提高事务能力

在数据中如果是单一事务，那没的说，一个一个的事务来执行，毫无压力。现实是不允许这样的，肯定是有大量的并发连接，并发事务在所难免。如果高并发的环境中，事务处理效率肯定大幅下降，这个时候我们有没有方法提高并发事务能力呢？

我们解决技术处理问题的限制，这次我们换一种思路来提高事务能力。比如：

合理的在线、离线数据库

比如我们的系统数据量日益增加，还有一些业务需要查询大量的数据，我们可以改造系统为在线、离线数据库，在线表提供高效事务能力，离线表提供数据查询服务，互不影响。

提高 delete 操作效率的思考

如果你对表有大量数据的 delete 操作，比如定期的按日、月、年删除数据，可以设计表为日表、月表、年表亦或是相对应的分区表，这样清理数据会由大事务降低为小事务。

MySQL 地基基础：数据字典

数据字典是什么

MySQL 数据字典的发展史：

  • MySQL 4 提供了 information_schema 数据字典，可以简单的使用 SQL 来检索系统元数据。
  • MySQL 5.5 提供了 performa nce_schema 性能引擎，可以查看 MySQL 性能问题，但是这个有一定难度。
  • MySQL 5.7 提供了 sys 系统数据库，其包含的表、视图、函数、存储过程、触发器可以帮我们快速了解数据库的情况。

数据字典的作用

数据字典我们用一句话来概括，就是数据的数据，用于查询数据库中数据的信息内容。

MySQL information_schema 详解（崭露头角)

我们部署完 MySQL 后发现会自动生成一个 information_schema 库，这个库提供了访问 MySQL 元数据的访问方式。

那么什么是元数据呢？元数据就是数据字典，就是数据的数据，前者这个数据就是我们所知道，所用的数据，后者这个数据就是解释前者的数据。比如，数据库名、表名、列名、列类型、权限等等。

这个库中存在这大量的视图，我们只能查看其视图内容，不能修改。

我们看看这个库中到底有什么内容。

mysql> use sys
mysql> select * from schema_object_overview where db='information_schema';
+--------------------+-------------+-------+
| db                 | object_type | count |
+--------------------+-------------+-------+
| information_schema | SYSTEM VIEW |    61 |
+--------------------+-------------+-------+
1 row in set (0.10 sec)

结果显示，有 61 张系统视图。既然是视图，那就没有相关的数据文件了，我们去验证一下。

到 MySQL 的 data 目录中看看是否有 information_schema 相关的数据文件。经过查询根本找不到这个库的目录。

都有什么视图内容呢？我们可以通过 show tables 命令查看。

mysql> use information_schema
mysql> show tables;

接下来我们讲几个 information_schema 中重要常用视图。

  • SCHEMATA：查看 MySQL 实例中所有数据库信息
  • TABLES：查询数据库中的表、视图信息
  • COLUMNS：查询表中的列信息
  • STATISTICS：查询表中的索引信息
  • USER_PRIVILEGES：查询用户权限信息
  • SCHEMA_PRIVILEGES：查询数据库权限信息
  • TABLE_PRIVILEGES：查询表权限信息
  • COLUMN_PRIVILEGES：查询列权限信息
  • CHARACTER_SETS：查询字符集信息

好了，information_schema 不做过多的介绍了，查询的内容比较全面，也比较简单，大家可以自行去探索。

MySQL performance_schema 详解（成长升级）

MySQL 在 5.7 开始，对数据字典的使用有了很大的改进，使用上更加的方便，提供的能力也更高。它可以查询事务信息、获取元数据锁、跟踪事件、统计内存使用情况等等。

我们先说一个你想不到的事情，MySQL 的 performance_schema 其实是一个引擎。

mysql> select * from information_schema.engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ENGINE             | SUPPORT | COMMENT                                                        | TRANSACTIONS | XA   | SAVEPOINTS |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

看到了吗，在 MySQL 支持的存储引擎中发现了 PERFORMANCE_SCHEMA，是不是很神奇。

在 MySQL 配置中可以配置启用这个引擎，默认是启动的。

在 my.cnf 中配置如下：

[mysqld]  
performance_schema=ON

验证一下参数是否启动：

mysql> show variables like 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+
1 row in set (0.01 sec)

虽然它是一个引擎，但是我们可以像使用数据库那样使用 use 来使用它。这个库里到底有什么内容呢？

mysql> use sys;
mysql> select * from schema_object_overview where db='performance_schema';
+--------------------+-------------+-------+
| db                 | object_type | count |
+--------------------+-------------+-------+
| performance_schema | BASE TABLE  |    87 |
+--------------------+-------------+-------+
1 row in set (0.06 sec)

结果显示，有 87 张表。我们知道 MySQL 有很多需要监控和统计的内容，而 performance_schema 将这些监控、统计信息之类的内容通过库中的表统计出来，都展现在这些表中。那么这些表都是做什么用的呢？我们就去研究一下。

总体分类：

  • setup 表
  • instance 表
  • wait event 表
  • stage event 表
  • statement event 表
  • transaction event 表
  • summary 表
  • other 表

setup 表

mysql> use performance_schema
mysql> show tables like '%setup%';
+----------------------------------------+
| Tables_in_performance_schema (%setup%) |
+----------------------------------------+
| setup_actors                           |
| setup_consumers                        |
| setup_instruments                      |
| setup_objects                          |
| setup_timers                           |
+----------------------------------------+
5 rows in set (0.00 sec)

setup_actors

作用：配置用户维度的监控，默认监控所有用户。

mysql> select * from setup_actors;
+------+------+------+---------+---------+
| HOST | USER | ROLE | ENABLED | HISTORY |
+------+------+------+---------+---------+
| %    | %    | %    | YES     | YES     |
+------+------+------+---------+---------+
1 row in set (0.00 sec)

% 表示默认是对所有的用户监控。

setup_consumers

作用：配置事件的消费者类型，管理将收集的监控内容保存在哪些表中。

mysql> select * from setup_consumers;
+----------------------------------+---------+
| NAME                             | ENABLED |
+----------------------------------+---------+
| events_stages_current            | NO      |
| events_stages_history            | NO      |
| events_stages_history_long       | NO      |
| events_statements_current        | YES     |
| events_statements_history        | YES     |
| events_statements_history_long   | NO      |
| events_transactions_current      | NO      |
| events_transactions_history      | NO      |
| events_transactions_history_long | NO      |
| events_waits_current             | NO      |
| events_waits_history             | NO      |
| events_waits_history_long        | NO      |
| global_instrumentation           | YES     |
| thread_instrumentation           | YES     |
| statements_digest                | YES     |
+----------------------------------+---------+
15 rows in set (0.00 sec)

有 15 条记录，这些配置呢存在着上下级关系，原则是当上级监控生效，下级监控才起作用。上下级对应关系如下：

global_instrumentation
|----thread_instrumentation
|         |----events_waits_current
|         |           |----events_waits_history
|         |           |----events_waits_history_long
|         |----events_stages_current
|         |           |----events_stages_history
|         |           |----events_stages_history_long
|         |----events_statements_current
|                     |----events_statements_history
|                     |----events_statements_history_long
|-----statements_digest
  • 第一级：global_instrumentation 是全局统计，只有它生效其余的才生效。如果设置它生效，其余都设置未生效，则只收集全局统计信息，不收集用户级统计信息。
  • 第二级：thread_instrumentation 是用户线程统计，statements_digest 是全局 SQL 统计。
  • 第三级：events_waits_current、events_stages_current、events_statements_current，分别是事件的 wait、stage、statement 的统计。
  • 第四级：分别是对应的历史统计内容了。

setup_instruments

作用：配置仪器，这个表中内容非常丰富，包含了统计 SQL 执行阶段情况、统计等待事件情况、IO 情况、内存情况、锁情况等。

配置内容很多，我们分组看一下几大类。

mysql> select name,count(*) from setup_instruments group by LEFT(name,5);
+-------------------------------------------+----------+
| name                                      | count(*) |
+-------------------------------------------+----------+
| idle                                      |        1 |
| memory/performance_schema/mutex_instances |      377 |
| stage/sql/After create                    |      129 |
| statement/sql/select                      |      193 |
| transaction                               |        1 |
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_tc |      319 |
+-------------------------------------------+----------+
6 rows in set (0.00 sec)

如果你执行上面的这个分组报错如下：

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'performance_schema.setup_instruments.NAME' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

解决方法：这是由于 sql_mode=only_full_group_by 导致。

mysql> SELECT @@SESSION.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@SESSION.sql_mode                                                                                                                        |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> set @@session.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

通过修改会话级的 sql_mode 可以解决问题。

  • idle：表示空闲
  • memory：表示内存的统计
  • stage：表示 SQL 语句每个阶段的统计
  • statement：表示 SQL 语句的统计
  • transaction：表示事务的统计
  • wait：表示各种等待的统计

setup_objects

作用：配置监控对象。

mysql> select * from setup_objects;
+-------------+--------------------+-------------+---------+-------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME | ENABLED | TIMED |
+-------------+--------------------+-------------+---------+-------+
| EVENT       | mysql              | %           | NO      | NO    |
| EVENT       | performance_schema | %           | NO      | NO    |
| EVENT       | information_schema | %           | NO      | NO    |
| EVENT       | %                  | %           | YES     | YES   |
| FUNCTION    | mysql              | %           | NO      | NO    |
| FUNCTION    | performance_schema | %           | NO      | NO    |
| FUNCTION    | information_schema | %           | NO      | NO    |
| FUNCTION    | %                  | %           | YES     | YES   |
| PROCEDURE   | mysql              | %           | NO      | NO    |
| PROCEDURE   | performance_schema | %           | NO      | NO    |
| PROCEDURE   | information_schema | %           | NO      | NO    |
| PROCEDURE   | %                  | %           | YES     | YES   |
| TABLE       | mysql              | %           | NO      | NO    |
| TABLE       | performance_schema | %           | NO      | NO    |
| TABLE       | information_schema | %           | NO      | NO    |
| TABLE       | %                  | %           | YES     | YES   |
| TRIGGER     | mysql              | %           | NO      | NO    |
| TRIGGER     | performance_schema | %           | NO      | NO    |
| TRIGGER     | information_schema | %           | NO      | NO    |
| TRIGGER     | %                  | %           | YES     | YES   |
+-------------+--------------------+-------------+---------+-------+
20 rows in set (0.00 sec)

通过结果我们看到默认对 EVENT、FUNCTION、PROCEDURE、TABLE、TRIGGER 的配置：

  • mysql 库都不监控
  • performance_schema 都不监控
  • information_schema 都不监控
  • 其余库都监控

setup_timers

作用：配置每种类型统计的时间单位。

mysql> select * from setup_timers;
+-------------+-------------+
| NAME        | TIMER_NAME  |
+-------------+-------------+
| idle        | MICROSECOND |
| wait        | CYCLE       |
| stage       | NANOSECOND  |
| statement   | NANOSECOND  |
| transaction | NANOSECOND  |
+-------------+-------------+
5 rows in set (0.00 sec)
  • idle：微妙
  • wait：CPU 时钟
  • stage：纳秒
  • statement：纳秒
  • transaction：纳秒

那么为什么使用这样的时间呢，这个时间定义来源于 MySQL 的基础定义。

mysql> select * from performance_timers;
+-------------+-----------------+------------------+----------------+
| TIMER_NAME  | TIMER_FREQUENCY | TIMER_RESOLUTION | TIMER_OVERHEAD |
+-------------+-----------------+------------------+----------------+
| CYCLE       |      2193855721 |                1 |             18 |
| NANOSECOND  |      1000000000 |                1 |            152 |
| MICROSECOND |         1000000 |                1 |            155 |
| MILLISECOND |            1037 |                1 |            155 |
| TICK        |             108 |                1 |            561 |
+-------------+-----------------+------------------+----------------+
5 rows in set (0.00 sec)

TICK：是系统的相对时间单位，也称为系统的时基，来源于定时器的周期性中断。

instance 表

mysql> use performance_schema
mysql> show tables like '%instances';
+-------------------------------------------+
| Tables_in_performance_schema (%instances) |
+-------------------------------------------+
| cond_instances                            |
| file_instances                            |
| mutex_instances                           |
| prepared_statements_instances             |
| rwlock_instances                          |
| socket_instances                          |
+-------------------------------------------+
6 rows in set (0.00 sec)
  • cond_instances：保存系统中使用的条件变量的对象
  • file_instances：保存系统中打开文件的对象
  • mutex_instances：保存系统中使用互斥变量的对象
  • prepared_statements_instances：保存预处理 SQL 语句的 statements 的对象
  • rwlock_instances：保存系统中使用读写锁的对象
  • socket_instances：保存系统中使用的 socket 的对象

wait event 表

mysql> use performance_schema
mysql> show tables like '%waits%';
+-----------------------------------------------+
| Tables_in_performance_schema (%waits%)        |
+-----------------------------------------------+
| events_waits_current                          |
| events_waits_history                          |
| events_waits_history_long                     |
| events_waits_summary_by_account_by_event_name |
| events_waits_summary_by_host_by_event_name    |
| events_waits_summary_by_instance              |
| events_waits_summary_by_thread_by_event_name  |
| events_waits_summary_by_user_by_event_name    |
| events_waits_summary_global_by_event_name     |
| table_io_waits_summary_by_index_usage         |
| table_io_waits_summary_by_table               |
| table_lock_waits_summary_by_table             |
+-----------------------------------------------+
12 rows in set (0.00 sec)

这里是说前三个，其他的后面介绍。

  • events_waits_current：保存当前线程的等待事件
  • events_waits_history：保存每个线程的最近 N 个等待事件
  • events_waits_history_long：保存所有线程的最近 N 个等待事件

stage event 表

mysql> use performance_schema
mysql> show tables like '%stage%';
+------------------------------------------------+
| Tables_in_performance_schema (%stage%)         |
+------------------------------------------------+
| events_stages_current                          |
| events_stages_history                          |
| events_stages_history_long                     |
| events_stages_summary_by_account_by_event_name |
| events_stages_summary_by_host_by_event_name    |
| events_stages_summary_by_thread_by_event_name  |
| events_stages_summary_by_user_by_event_name    |
| events_stages_summary_global_by_event_name     |
+------------------------------------------------+
8 rows in set (0.00 sec)

这里是说前三个，其他的后面介绍。

  • events_stages_current：保存当前线程所处的执行阶段
  • events_stages_history：保存当前线程最新的 N 个执行阶段
  • events_stages_history_long：保存当前线程最新的 N 个执行阶段

statement event 表

mysql> use performance_schema
mysql> show tables like '%statement%';
+----------------------------------------------------+
| Tables_in_performance_schema (%statement%)         |
+----------------------------------------------------+
| events_statements_current                          |
| events_statements_history                          |
| events_statements_history_long                     |
| events_statements_summary_by_account_by_event_name |
| events_statements_summary_by_digest                |
| events_statements_summary_by_host_by_event_name    |
| events_statements_summary_by_program               |
| events_statements_summary_by_thread_by_event_name  |
| events_statements_summary_by_user_by_event_name    |
| events_statements_summary_global_by_event_name     |
| prepared_statements_instances                      |
+----------------------------------------------------+
11 rows in set (0.00 sec)

这里是说前三个，其他的后面介绍。

  • events_statements_current：保存当前线程执行的语句
  • events_statements_history：保存每个线程最新的 N 个执行的语句
  • events_statements_history_long：保存每个线程最新的 N 个执行的语句

transaction event 表

mysql> use performance_schema
mysql> show tables like '%transactions%';
+------------------------------------------------------+
| Tables_in_performance_schema (%transactions%)        |
+------------------------------------------------------+
| events_transactions_current                          |
| events_transactions_history                          |
| events_transactions_history_long                     |
| events_transactions_summary_by_account_by_event_name |
| events_transactions_summary_by_host_by_event_name    |
| events_transactions_summary_by_thread_by_event_name  |
| events_transactions_summary_by_user_by_event_name    |
| events_transactions_summary_global_by_event_name     |
+------------------------------------------------------+
8 rows in set (0.00 sec)

这里是说前三个，其他的后面介绍。

  • events_transactions_current：保存每个线程当前事务事件
  • events_transactions_history：保存每个线程最近的 N 个事务事件
  • events_transactions_history_long：保存每个线程最近的 N 个事务事件

summary 表

mysql> use performance_schema
mysql> show tables like '%summary%';
+------------------------------------------------------+
| Tables_in_performance_schema (%summary%)             |
+------------------------------------------------------+
| events_stages_summary_by_account_by_event_name       |
| events_stages_summary_by_host_by_event_name          |
| events_stages_summary_by_thread_by_event_name        |
| events_stages_summary_by_user_by_event_name          |
| events_stages_summary_global_by_event_name           |
| events_statements_summary_by_account_by_event_name   |
| events_statements_summary_by_digest                  |
| events_statements_summary_by_host_by_event_name      |
| events_statements_summary_by_program                 |
| events_statements_summary_by_thread_by_event_name    |
| events_statements_summary_by_user_by_event_name      |
| events_statements_summary_global_by_event_name       |
| events_transactions_summary_by_account_by_event_name |
| events_transactions_summary_by_host_by_event_name    |
| events_transactions_summary_by_thread_by_event_name  |
| events_transactions_summary_by_user_by_event_name    |
| events_transactions_summary_global_by_event_name     |
| events_waits_summary_by_account_by_event_name        |
| events_waits_summary_by_host_by_event_name           |
| events_waits_summary_by_instance                     |
| events_waits_summary_by_thread_by_event_name         |
| events_waits_summary_by_user_by_event_name           |
| events_waits_summary_global_by_event_name            |
| file_summary_by_event_name                           |
| file_summary_by_instance                             |
| memory_summary_by_account_by_event_name              |
| memory_summary_by_host_by_event_name                 |
| memory_summary_by_thread_by_event_name               |
| memory_summary_by_user_by_event_name                 |
| memory_summary_global_by_event_name                  |
| objects_summary_global_by_type                       |
| socket_summary_by_event_name                         |
| socket_summary_by_instance                           |
| table_io_waits_summary_by_index_usage                |
| table_io_waits_summary_by_table                      |
| table_lock_waits_summary_by_table                    |
+------------------------------------------------------+
36 rows in set (0.00 sec)

这些 summary 表有很多，提供了一段时间内已经执行完成的事件的汇总情况，我们从不同的维度整理如下：

  • 按阶段事件的汇总摘要：events_stages*
  • 按语句事件的汇总摘要：events_statements*
  • 按事务事件的汇总摘要：events_transactions*
  • 按等待事件的汇总摘要：events_waits*
  • 按文件事件的汇总摘要：file*
  • 按内存事件的汇总摘要：memory*
  • 按对象事件的汇总摘要：objects_summary_global_by_type
  • 按套接字事件的汇总摘要：socket*
  • 按表事件的汇总摘要：table*

other 表

其他的表还有很多，可以监控统计 accounts、file、status、hosts、memory、metadata_locks、replication、session、socket、table、threads 等。

好了，performance_schema 是数据库，是性能引擎，内部逻辑比较复杂，能做的事情也很多，这里就先介绍到这里，大家可以继续深入研究。

MySQL sys 详解（演变进化）

MySQL 在 5.7 版本引入了 sys Schema，这个 sys 可以理解为是一个 MySQL 系统库，这个库中提供了表、视图、函数、存储过程、触发器，这些就可以帮我们快捷、高效地知道 MySQL 数据库的元数据信息，比如我们可以了解：SQL 执行情况是否使用了索引，是否走了全表扫描，统计信息的情况、内存使用情况、IO 使用情况、会话连接等等。

前面我们学习了 information_schema 和 performance_schema，这个 sys 提供的视图其实就是前面这两个化繁为简的总结，降低复杂度，让你更快乐的了解 MySQL 的现状。可见 MySQL 在自我优化方面是多么的努力，它帮你做了很多的工作，我们可以更简单的获取更直观的数据，怎么样，MySQL 优秀吧。

说这么多了，这个 sys 库里到底有什么内容呢？好，赶紧一睹芳容。

mysql> use sys
mysql> select * from schema_object_overview where db='sys';
+-----+---------------+-------+
| db  | object_type   | count |
+-----+---------------+-------+
| sys | FUNCTION      |    22 |
| sys | PROCEDURE     |    26 |
| sys | VIEW          |   100 |
| sys | BASE TABLE    |     1 |
| sys | INDEX (BTREE) |     1 |
| sys | TRIGGER       |     2 |
+-----+---------------+-------+
6 rows in set (0.01 sec)

结果显示：

这些内容可以帮我们做什么呢？

  • 视图：获取更可读的 performance_schema 中的数据
  • 存储过程：调整 performance_schema 的配置信息，生成系统诊断报告等
  • 函数：查询 performance_schema 配置信息，提供格式化数据等

1 张表

在这些所有内容中，我们常用的就是这一张表和其他视图，我们先来看看这唯一一张表，它是 sys_config。

mysql> select * from sys_config;
+--------------------------------------+-------+---------------------+--------+
| variable                             | value | set_time            | set_by |
+--------------------------------------+-------+---------------------+--------+
| diagnostics.allow_i_s_tables         | OFF   | 2020-12-16 19:14:32 | NULL   |
| diagnostics.include_raw              | OFF   | 2020-12-16 19:14:32 | NULL   |
| ps_thread_trx_info.max_length        | 65535 | 2020-12-16 19:14:32 | NULL   |
| statement_performance_analyzer.limit | 100   | 2020-12-16 19:14:32 | NULL   |
| statement_performance_analyzer.view  | NULL  | 2020-12-16 19:14:32 | NULL   |
| statement_truncate_len               | 64    | 2020-12-16 19:14:32 | NULL   |
+--------------------------------------+-------+---------------------+--------+
6 rows in set (0.00 sec)

只有简单的 6 行数据，这张表保存的是基础参数的配置内容。内容级别是会话级。默认最后一列 set_by（配置修改者）为空，其保存的内容是最后一次修改配置时的用户名。

参数说明

  • diagnostics.allow_i_s_tables：默认 OFF，这参数控制调用 diagnostics() 存储过程时会扫描 information_schema.tables 找到所有的基表与 statistics 表关联查询，扫描每个表的统计信息。
  • diagnostics.include_raw：默认 OFF，这参数控制调用 diagnostics() 存储过程输出包含 metrics 视图的原始信息。
  • ps_thread_trx_info.max_length：默认 65535，保存的是 ps_thread_trx_info() 函数生成的 json 输出内容的最大长度。
  • statement_performance_analyzer.limit：默认 100，返回不具有内置限制的视图的行数。
  • statement_performance_analyzer.view：默认 NULL，给 statement_performance_analyzer() 存储过程当作入参使用的自定义查询或视图名称。
  • statement_truncate_len：默认 64，控制 format_statement() 函数返回的语句的最大长度。

接下来我们测试修改一下 statement_truncate\len 这个参数内容：

# statement_truncate_len，调用 format_statement()函数返回是 64 字节长度的值，在未被调用过任何涉及到该配置选项的函数之前，该参数的值是 NULL。
mysql> select @sys.statement_truncate_len;
+----------------------------------------------------------+
| @sys.statement_truncate_len                              |
+----------------------------------------------------------+
| NULL                                                     |
+----------------------------------------------------------+
1 row in set (0.00 sec)

# 调用一下 format_statement()函数
mysql> set @stmt='select variable,value,set_time,set_by from sys_config';
mysql> select format_statement(@stmt);
+----------------------------------------------------------+
| format_statement(@stmt)                                  |
+----------------------------------------------------------+
| select variable,value,set_time,set_by from sys_config |
+----------------------------------------------------------+
1 row in set (0.00 sec)
此时结果可以正常显示 SQL。

# 调用过 format_statement()函数之后，参数的值会更新为 64
mysql> select @sys.statement_truncate_len;
+-----------------------------+
| @sys.statement_truncate_len |
+-----------------------------+
| 64                          |
+-----------------------------+
1 row in set (0.00 sec)
此时看到 statement_truncate_len 值内容为 64 了

#修改一下 statement_truncate_len 的值为 32
mysql> set @sys.statement_truncate_len=32;
mysql> select @sys.statement_truncate_len;
+-----------------------------+
| @sys.statement_truncate_len |
+-----------------------------+
|                          32 |
+-----------------------------+
1 row in set (0.00 sec)

# 再次调用 format_statement()函数，可以看到返回的结果内容显示不全了，因为我们把 statement_truncate_len 改为了 32 导致。
mysql> select format_statement(@stmt);   
+-----------------------------------+
| format_statement(@stmt)           |
+-----------------------------------+
| select variabl ... rom sys_config |
+-----------------------------------+
1 row in set (0.00 sec)

上面这 6 行配置时默认自带的，sys_config 中还有一个 sys.debug 参数，这个参数默认没有，我们可以手工插入。

debug

默认是 NULL，调用 diagnostics() 和 execute_prepared_stmt() 存储过程，执行检查。这个参数默认不存在，是临时使用的。

# 会话级设置
set @sys.debug = NULL;
# 所有会话使用，需要插入到表中
mysql> insert into sys_config (variable, value) values('debug', 'ON');
mysql> select * from sys_config;
+--------------------------------------+-------+---------------------+--------+
| variable                             | value | set_time            | set_by |
+--------------------------------------+-------+---------------------+--------+
| debug                                | ON    | 2021-02-07 15:53:12 | NULL   |
| diagnostics.allow_i_s_tables         | OFF   | 2020-12-16 19:14:32 | NULL   |
| diagnostics.include_raw              | OFF   | 2020-12-16 19:14:32 | NULL   |
| ps_thread_trx_info.max_length        | 65535 | 2020-12-16 19:14:32 | NULL   |
| statement_performance_analyzer.limit | 100   | 2020-12-16 19:14:32 | NULL   |
| statement_performance_analyzer.view  | NULL  | 2020-12-16 19:14:32 | NULL   |
| statement_truncate_len               | 64    | 2020-12-16 19:14:32 | NULL   |
+--------------------------------------+-------+---------------------+--------+
7 rows in set (0.00 sec)
# 更新
mysql> update sys_config set value = 'OFF' where variable = 'debug';

2 个触发器

前面 sys_config 这个表介绍的差不多了，接下来我们说一下这两个触发器，他们和这张表有紧密的关系。

在 MySQL 5.7 开始提供了一个新的用户 mysql.sys，这个用户可避免修改或删除 root 用户时发生的问题，但是该用户被锁定是无法连接客户端的。

接下来说的两个触发器，在定义时使用了 DEFINER=mysql.sys@localhost，就是说只能用 mysql.sys 调用触发器，从而对表 sys_config 的内容做修改，如果 mysql.sys 用户不存在会报错

ERROR 1449 (HY000): The user specified as a definer ('mysql.sys'@'localhost') does not exist

假如，我是说假如 mysql.sys 用户被你给误删除了，或者其他原因导致这个用户不存在了，我们如何补救呢？（建议：千万不要去动这个用户，以免造成不必要的麻烦）

# 首先创建用户，并赋予使用触发器权限
mysql> grant TRIGGER on sys.* to 'mysql.sys'@'localhost' identified by '123456';

mysql> INSERT INTO sys.sys_config (variable, value) VALUES('debug', 'ON');    
ERROR 1143 (42000): SELECT command denied to user 'mysql.sys'@'localhost' for column 'set_by' in table 'sys_config'

# 还需要赋予 select、insert、update 权限
mysql> grant select,insert,update on sys.sys_config to 'mysql.sys'@'localhost';

mysql> INSERT INTO sys.sys_config (variable, value) VALUES('debug', 'ON'); 
Query OK, 1 row affected (0.02 sec)
mysql> UPDATE sys.sys_config SET value = 'OFF' WHERE variable = 'debug';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

sysconfiginsertsetuser

当对 sys.sys_config 表做 insert 操作时，该触发器会将 sys_config 表的 set_by 列设置为当前用户名。

sysconfigupdatesetuser

当对 sys.sys_config 表做 insert 操作时，该触发器会将 sys_config 表的 set_by 列设置为当前用户名。

这两个触发器可以更新 set_by 字段都有一个前提条件：

mysql> set @sys.ignore_sys_config_triggers=0;

100 张视图

在 MySQL 的 sys 库中有 100 个视图，其中有 52 个是字母的，有 48 个是 x$开头的，有什么区别呢？前者是格式化的数据，更加适合人类阅读；后者是数据库原始数据，适合工具采集数据使用。

我们重点介绍一下字母开头的视图，重点分为几类：

  • host_summary：服务器层级，以 IP 分组，汇总 IO 信息。
  • innodb：InnoDB 层级，汇总 innodb 存储引擎信息和事务锁、等待等信息。
  • io：IO 层级，汇总 IO 使用情况、IO 等待情况等。
  • memory：内存使用情况。
  • metrics：数据库内部统计值。
  • processlist：线程情况。
  • ps_check_lost_instrumentation：发生监控丢失的信息情况。
  • schema：模式层级，汇总表统计信息等。
  • session：会话层级，汇总会话情况。
  • statement：执行语句层级，汇总统计信息等。
  • user_summary：用户层级，以用户分组，汇总用户使用文件 IO 信息，执行语句的统计信息等。
  • wait：汇总主机，等待事情等。

接下来我们重点介绍几个视图。

  • host_summary：这个视图我们可以查看连接数据库的主机情况，统计每个主机 SQL 执行次数、SQL 执行时长、表扫描次数、文件 IO 情况、连接情况、用户情况、内存分布情况。通过这些信息我们可以快速了解连接数据库的主机情况。
  • hostsummarybyfileio_type：查询连接数据库每个主机的文件 IO 使用情况。
  • hostsummarybyfileio：查询连接数据库主机的总 IO 使用情况。
  • innodbbufferstatsbyschema：扫描整个 buffer pool 来统计查看每个库的内存占用情况。如果生产环境 buffer pool 很大，扫描会占用很多资源，造成性能问题，慎用。
  • innodbbufferstatsbytable：扫描整个 buffer pool 来统计查看每个库的每个对象的内存占用情况。如果生产环境 buffer pool 很大，扫描会占用很多资源，造成性能问题，慎用。
  • ioglobalbyfileby_bytes：查询数据库的 IO 情况。
  • memorybyhostbycurrent_bytes：查询连接数据库的主机内存情况。
  • memorybythreadbycurrent_bytes：查询连接数据库的线程内存情况。
  • memorybyuserbycurrent_bytes：查询连接数据库的用户内存情况。
  • processlist：查询数据库连接情况。
  • session：查询连接数据库的会话情况。
  • schematablelock_waits：查询锁等待情况。
  • schematablestatistics：查询对表的 insert、update、delete、select 的 IO 情况。
  • schematableswithfulltable_scans：查询全表扫描情况。
  • schemaautoincrement_columns：查询自增列情况。
  • schemaobjectoverview：查询 MySQL 中每个数据库的对象情况（包括表、索引、视图、函数、存储过程、触发器）。
  • schemaredundantindexes：查询数据库的冗余索引情况。
  • schemaunusedindexes：查询数据库中没有使用索引的情况。
  • schemaindexstatistics：查询索引的 select、insert、update、delete 情况。
  • statement_analysis：查询执行语句总体的统计信息情况。
  • statementswitherrorsorwarnings：查询执行语句的错误和警告情况。
  • statementswithfulltablescans：查询全表扫描情况。
  • statementswithruntimesin95th_percentile：查询语句平均执行时间大于整体 95%平均分布的情况。
  • statementswithsorting：查询使用了文件排序的情况。
  • statementswithtemp_tables：查询使用了临时表的执行语句情况。
  • user_summary：查询连接的总体执行时间、平均执行时间、IO、内存等情况。
  • version：查询 sys schema 和 MySQL 版本情况。
  • waitclassesglobalbyavg_latency：查询等待事件的平均延迟时间情况。
  • waitclassesglobalbylatency：查询等待事件的总体延迟时间情况。

以上差不多就是 sys 库常用的视图了，基本满足我们的日常分析统计需求，大家可以通过官网继续深入学习，学好这一部分的内容，对 MySQL 的底层原理及性能分析有非常大的帮助。

MySQL 地基基础：数据库字符集

字符集简介

字符是各种文字和符号的总称，而字符集是多个字符的集合。

字符集分类与特点

字符包含有国家文字、标点符号、图形符号、数字等内容，字符集是多个字符的集合，字符集种类非常多，每种字符集包含的字符个数是不相同的。比如说，国家文字不同，就会使用各自国家通用字符集，这样是不是好理解一些。

常见字符集分类

  • ASCII（American Standard Code for Information Interchange，美国信息互换标准编码）：是基于罗马字母表的一套电脑编码系统，一个字节表示一个字符。
  • LATIN1：ASCII 字符集的扩充，仍然使用一个字节表示一个字符。
  • GB2312（信息交换用汉字编码字符集·基本集）：中国国家标准的简体中文字符集，基本满足了汉字的计算机处理需要，分区表示，双字节表示一个字符。
  • GB18030（信息交换用汉字编码字符集基本集的扩充）：是 GB2312 的扩充，更全面，兼容 Unicode 3.0 和 GB2312。
  • UTF-8（Unicode Tranformation Format）：是 Unicode 的其中一个使用方式，支持所有国家字符集，使用 1-4 个字节表示一个字符。

字符集有很多，这里不一一列举。想要获取全部支持字符集，可以在库中查看，详见下文。

字符集特点

  • 不同的编码方式，最终解释为不同的机器语言（二进制）
  • 不同的表示方式，使用 1 个或者多个字节表示一个字符

MySQL 与字符集之间亲密关系

首先我们想一下，数据库是做什么的？数据库是存放数据的环境。这些数据库是如何保存的？拍拍脑袋一想，肯定不是原封不动的存放的，数据库不同于我们的大脑，它的思维能力甚至不如阿猫阿狗，它只能读懂 0 和 1，那么数据库存储的数据，其实就是一串串的 0 和 1 的组合，那么按照什么规则定义这些 0 和 1 呢？重点来了，数据库需要在字符集中找到对应的数据编码，然后存储的是这些编码。

MySQL 字符集包括字符集（character）和校对规则（collation），字符集用来定义存储字符串的方式，校队规则定义比较字符串的方式。字符集和校对规则是一对多的关系，每个字符集至少对应一个校对规则。后面我们在具体说说校对规则。

MySQL 在底层存储上有四个级别的字符集设置，分别是：服务器级、数据库级、表级、列级。字符集优先级是：列字符集 > 表字符集 > 数据库字符集 > 服务器字符集，其中最常用的是表级和数据库级字符集。列级字符集最优先，但是一般不单独设置，使其继承表级字符集。

获取所有数据库字符集：

mysql> show character set;
或者
mysql> select * from information_schema.character_sets;

获取所有数据库校对规则：

mysql> show collation;
或者
mysql> select * from information_schema.collations;

字符集编码原理

在客户端和服务器端之期间的数据请求与反馈，会经历一系列的转换，从而保证数据的正确无误无乱码，下面具体说一下这个请求的过程。

\1. MySQL client 发送请求（字符集为 character_set_client） character_set_client 就是客户端字符集。

\2. MySQL Server 收到请求，将请求数据从 character_set_client 转换为 character_set_connection 通常情况下 character_set_connection 字符集同 character_set_client 字符集一致。

\3. MySQL Server 将请求数据从 character_set_connection 转换为内部操作字符集。

何为内部操作字符集，其实就是前面说的列字符集、表字符集、数据库字符集、服务器字符集这四个。这一步操作会使用每个数据字段的 CHARACTER SET 设定值：

  • 如果 CHARACTER SET 不存在，使用表字符集
  • 如果表字符集不存在，使用数据库字符集
  • 如果数据库字符集不存在，使用服务器字符集

\4. MySQL Server 将操作结果从内部操作字符集转换为 character_set_results character_set_results 就是结果内容的字符集。

字符集与校对规则分析

前面我们已经说了说字符集和校对规则的关系。接下来我们具体分析分析它们。

查看数据库字符集：

mysql> show variables like 'character%';

在这里插入图片描述

参数解释：

  • character_set_client：客户端使用的字符集
  • character_set_connection：客户端和服务端连接层字符集
  • character_set_database：默认数据库字符集 ，如没有默认数据库，会用 character_set_server 指定的字符集，建议由系统自动管理
  • character_set_filesystem：把 os 上文件名转化成此字符集，把 character_set_client 转换 character_set_filesystem， 默认 binary 是不做任何转换
  • character_set_results：结果字符集
  • character_set_server：数据库服务器字符集
  • character_set_system：操作系统字符集，无需设置，总是 utf8

DDL 字符集选择：

  • 建库操作，未指定数据库字符集，继承数据库服务器字符集
  • 建表操作，未指定表字符集，继承当前库字符集
  • 新增字段，未指定列字符集，继承表字符集
  • 修改字段，未指定列字符集，继承表字符集

DML 字符集选择：

  • 插入、更新数据，由 character_set_client 转换为 character_set_connection 再转换为表字符集

查看字符集校对规则：

mysql> show variables like 'collation%';

在这里插入图片描述

参数说明：

  • collation_connection：当前连接的字符集校对规则
  • collation_database：当前数据库默认校对规则
  • collation_server：当前数据库服务器默认校对规则

字符集和校对规则：

  • 每个字符集至少有一个校对规则
  • 每个字符集有一个默认的校对规则
  • 每个校对规则只能属于一个字符集

校对规则命名

字符集名称_语言_后缀，其中后缀有三种写法：

  • _ci：不区分大小写
  • _cs：区分大小写
  • _bin：二进制

如何更改 MySQL 字符集

更改数据库字符集：

alter database db1 default character set utf8;

更改数据库表的字符集：

alter table tab1 default character set utf8;

把表默认的字符集和所有字符列改为新的字符集（例如 utf8）：

alter table tab1 convert to character set utf8;

前面的操作转换了字符集之间的列类型。如果有一列使用一种字符集（如 latin1），但是存储的值实际上使用了其它的字符集（如 utf8），这种情况不是你想要的，进行如下操作就可以解决你的问题。

alter table tab1 change 字段 1 字段 2 类型；
alter table tab1 change 字段 2 字段 2 类型 character set utf8;

字符集最佳实践

MySQL 数据库字符集和校对规则有 4 个级别：服务器级、数据库级、表级、字段级，下面我们重点讲解及用实例操作来实践。

服务器级设置字符集

方式一

在 MySQL 配置文件 my.cnf 中进行配置设置：

[mysqld]
default-character-set=gbk

方式二

在启动 MySQL 时设置：

mysqld --default-character-set=gbk

方式三

在源码编译时指定，如果未指定，默认使用 latin1：

./configure --with-charcter=gbk

数据库级设置字符集

  • 创建数据库时指定字符集（create database … ）
  • 修改数据库时修改字符集（alter database …）

注：对于已经存在的数据修改字符集无效。

表级设置字符集

  • 创建表时指定字符集（create table …）
  • 修改表时修改字符集（alter table …）

注：对于已经存在的数据修改字符集无效。

列级设置字符集

  • 创建表时指定列字符集（create table …）
  • 修改表时修改列字符集（alter table …）

注：对于已经存在的数据修改字符集无效。

细心的朋友应该已经注意到了，以上这种修改是不适用于已经存在的数据的，如果表中已经存在数据了，那么我们怎样修改字符集呢？不要着急，后面会一步一步的教你如何实现。

首先我们先实例说一下如何设置字符集。

1. 为数据库设置字符集和校对规则

设置数据库字符集：

create database db1 default character set utf8;

设置数据库校对规则：

create database db1 default character set utf8 collate utf8_bin;

说明：

  • 如果指定了字符集和校对规则，则使用指定的
  • 如果指定了字符集未指定校对规则，则使用指定字符集和默认校对规则
  • 如果未指定字符集和校对规则，则使用服务器字符集和校对规则

2. 为表设置字符集和校对规则

设置表字符集：

create table tab1(column1 varchar(5)) default character set utf8;

设置表校对规则：

create table tab1(column1 varchar(5)) default character set utf8 collate utf8_bin;

说明：

  • 如果指定了字符集和校对规则，则使用指定的
  • 如果指定了字符集未指定校对规则，则使用指定字符集和默认校对规则
  • 如果未指定字符集和校对规则，则使用数据库字符集和校对规则

3. 为列设置字符集和校对规则

设置列字符集：

create table tab1(column1 varchar(5) character SET utf8);

设置列校对规则：

create table tab1(column1 varchar(5) character set utf8 collate utf8_bin);

说明：

  • 如果指定了字符集和校对规则，则使用指定的
  • 如果指定了字符集未指定校对规则，则使用指定字符集和默认校对规则
  • 如果未指定字符集和校对规则，则使用表字符集和校对规则

4. 如何处理带数据的字符集

当表中已经存在数据，直接更改字符集，不会更改既有的数据字符集，我们需要先将数据导出，调整字符集再导入。

第一步：导出表结构

mysqldump -uroot -p --default-character-set=gbk -d db1> createtab.sql

第二步：修改表字符集

编辑修改 createtab.sql 文件，将表结构定义中的字符集改为新的字符集。

第三步：导出所有数据

mysqldump -uroot -p --quick --no-create-info --extended-insert --default-character-set=latin1 db1> data.sql

第四步：修改数据字符集

编辑修改 data.sql，将 set names latin1 修改成 set names gbk。

第五步：创建数据库

create database db1 default charset gbk;

第六步：创建表

mysql -uroot -p db1 < createtab.sql

第七步：导入数据

mysql -uroot -p db1 < data.sql