mysql 查询变量_[玩转MySQL之九]MySQL实现ACID之原子性

一、 原子性(Atomicity)

MySQL InnoDB引擎通过以下几个方面实现事务的原子性:

• 事务自动提交(Autocommit)配置
• commit 和rollback语句
• 从元数据库INFORMATION_SCHEMA的 表中获取操作数据

1.1 MySQL 事务自动提交(Autocommit)配置

MySQL默认操作模式就是autocommit自动提交模式。这就表示除非显式地开始一个事务，否则每个查询都被当做一个单独的事务自动执行。

• 查看autocommit模式

变量autocommit分会话系统变量与全局系统变量，所以查询的时候，最好区别是会话系统变量还是全局系统变量。

mysql> show session variables like 'autocommit';+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit | ON |+---------------+-------+1 row in set (0.00 sec) mysql> show global variables like 'autocommit';+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit | ON |+---------------+-------+1 row in set (0.00 sec)

Value的值为ON，表示autocommit开启。OFF表示autocommit关闭。

• 修改autocommit模式

方法1: 通过命令行修改

mysql> set session autocommit=0;Query OK, 0 rows affected (0.00 sec) mysql> show session variables like 'autocommit';+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit | OFF |+---------------+-------+1 row in set (0.00 sec) mysql> show global variables like 'autocommit';+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit | ON |+---------------+-------+1 row in set (0.00 sec) mysql>  mysql> set global autocommit=0;Query OK, 0 rows affected (0.00 sec) mysql> show global variables like 'autocommit';+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit | OFF |+---------------+-------+1 row in set (0.01 sec)

上述SQL修改会话系统变量或全局系统变量，只对当前实例有效，如果MySQL服务重启的话，这些设置就会丢失，如果要永久生效，就必须在配置文件中修改系统变量。

方法2: 修改配置文件

[mysqld]autocommit=0

• autocommit与显性事务的关系

对于显性事务start transaction或begin， 在自动提交模式关闭(关闭隐式提交)的情况下，开启一个事务上下文。首先数据库会隐式提交之前的还未被提交的操作，同时开启一个新事务。具体实验如下：

mysql> select connection_id();+-----------------+| connection_id() |+-----------------+| 15 |+-----------------+1 row in set (0.00 sec)mysql> show variables like 'autocommit';+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit | ON |+---------------+-------+1 row in set (0.00 sec) mysql> set autocommit=0;Query OK, 0 rows affected (0.00 sec)mysql> delete from tester.test where name='张三';Query OK, 1 row affected (0.00 sec)

此时重新打开一个终端，登陆MySQL开启一个新的绘画，此时可以查询到会话ID为15的事务信息， 详情如下:

mysql> select connection_id();+-----------------+| connection_id() |+-----------------+| 16 |+-----------------+1 row in set (0.00 sec) mysql> SELECT a.trx_state,  -> b.event_name,  -> a.trx_started,  -> b.timer_wait / 1000000000000 timer_wait,  -> a.trx_mysql_thread_id blocking_trx_id,  -> b.sql_text  -> FROM information_schema.innodb_trx a,  -> performance_schema.events_statements_current b,  -> performance_schema.threads c  -> WHERE a.trx_mysql_thread_id = c.processlist_id  -> AND b.thread_id = c.thread_id; +-----------+----------------------+---------------------+------------+-----------------+------------------------------------------+| trx_state | event_name | trx_started | timer_wait | blocking_trx_id | sql_text |+-----------+----------------------+---------------------+------------+-----------------+------------------------------------------+| RUNNING | statement/sql/delete | 2019-02-25 15:45:00 | 0.0010 | 1 | delete from tester.test where name='张三' |+-----------+----------------------+---------------------+------------+-----------------+------------------------------------------+1 row in set (0.00 sec)

如果在会话15中开启显性事务，那么之前挂起的事务会自动提交，然后，你再去会话16当中查询，就发现之前的DELETE操作已经提交。

在会话15中开启显示事务:start transaction;

mysql> select connection_id();+-----------------+| connection_id() |+-----------------+| 15 |+-----------------+1 row in set (0.00 sec)mysql> start transaction;Query OK, 0 rows affected (0.00 sec)

在会话16中继续查询事务

mysql> select connection_id();+-----------------+| connection_id() |+-----------------+| 16 |+-----------------+1 row in set (0.00 sec) mysql> SELECT a.trx_state,  -> b.event_name,  -> a.trx_started,  -> b.timer_wait / 1000000000000 timer_wait,  -> a.trx_mysql_thread_id blocking_trx_id,  -> b.sql_text  -> FROM information_schema.innodb_trx a,  -> performance_schema.events_statements_current b,  -> performance_schema.threads c  -> WHERE a.trx_mysql_thread_id = c.processlist_id  -> AND b.thread_id = c.thread_id; Empty set (0.00 sec)

使用START TRANSACTION，自动提交将保持禁用状态，直到你使用COMMIT或ROLLBACK结束事务。 自动提交模式然后恢复到之前的状态(如果start transaction 前 autocommit = 1，则完成本次事务后 autocommit 还是 1。如果 start transaction 前 autocommit = 0，则完成本次事务后 autocommit 还是 0)

1.2 COMMIT

一般的MySQL语句都是直接针对数据库表执行和编写的。这就是所谓的隐含提交(implicit commit)，即提交(写或保存)操作是自动进行的。但是，在事务处理块中，提交不会隐含地进行。为进行明确的提交，使用 COMMIT 语句，如下所示：

mysql> start transaction;mysql> delete from persons where id = 200;mysql> delete from students where person_id = 200;mysql> commit;

当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭

1.3 ROLLBACK

ROLLBACK 只能在一个事务处理内使用，即: 在执行一条 START TRANSACTION 命令之后。

mysql> begin transaction; # 开始事务Query OK, 0 rows affected (0.00 sec) mysql> insert into aaaa values(7);Query OK, 1 rows affected (0.00 sec) mysql> rollback; # 回滚Query OK, 0 rows affected (0.00 sec)

1.4 从元数据库INFORMATION_SCHEMA获取和操作数据

元数据就是描述数据的数据，如数据库名，表名，表大小，字段名，字段类型等。而MySQL中的INFORMATION_SCHEMA就是一个元数据库，存储着数据库实例中的所有元数据信息。

关于INFORMATION_SCHEMA的官方定义:

INFORMATION_SCHEMA provides access to database metadata, information about the MySQL server such as the name of a database or table, the data type of a column, or access privileges

information_schema数据库是MySQL自带的， 确切说information_schema是MySQL的信息数据库，保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名，数据库的表，字段的数据类型与访问权限等。

nformation_schema数据库是MySQL系统自带的数据库，它提供了数据库元数据的访问方式。感觉information_schema就像是MySQL实例的一个百科全书，记录了数据库当中大部分我们需要了结的信息，比如字符集，权限相关，数据库实体对象信息，外检约束，分区，压缩表，表信息，索引信息，参数，优化，锁和事物等等。通过information_schema我们可以窥透整个MySQL实例的运行情况，可以了结MySQL实例的基本信息，甚至优化调优，维护数据库等，

下面对information_schema中的表进行简单的分类:

1、 关于字符集和排序规则相关的系统表

• CHARACTER_SETS : 保存所有Mysql可用的字符集。相当于命令：SHOW CHARACTER SET
• COLLATIONS: 字符集对应的排序规则
• COLLATION_CHARACTER_SET_APPLICABILITY: 显示了哪种字符集适用于哪种排序方式。这些列相当于我们从SHOW COLLATION获得的前两个字段。

2、 权限相关的表

• SCHEMA_PRIVILEGES:保存数据库的权限信息，该表是个内存表， 里面的数据从mysql.db中加载。
• TABLE_PRIVILEGES: 保存表的权限信息。该表是个内存表， 里面的数据从mysql.tables_priv中加载。
• COLUMN_PRIVILEGES: 保存表中列的权限信息。该表是个内存表， 里面的数据从mysql.columns_priv中加载。
• USER_PRIVILEGES:存储用户的权限。该表是个内存表， 里面的数据从mysql.user中加载。

3、 存储数据库系统实体对象的表

• COLUMNS: 保存所有数据库中表的列信息
• INNODB_SYS_COLUMNS: 存放的是INNODB的元数据， 依赖于SYS_COLUMNS这个统计表而存在的。
• ENGINES: 存储Mysql支持的数据库引擎类型，相当于命令SHOW ENGINES
• EVENTS: 保存计划事件(scheduled events)的信息，类似于定时作业
• FILES: 这张表提供了有关在MySQL的表空间中的数据存储的文件的信息，文件存储的位置
• PARAMETERS: 参数表存储了一些存储过程和方法的参数，以及存储过程的返回值信息。存储和方法在ROUTINES里面存储。
• PLUGINS: MySQL的插件信息。其实SHOW PLUGINS本身就是通过这张表来获取数据。
• ROUTINES: 关于存储过程和方法function的一些信息，不过这个信息是不包括用户自定义的，只是系统的一些信息。
• SCHEMATA: 供了实例下有多少个数据库，以及数据库默认的字符集
• TABLES: 保存数据表信息。类似show tables。
• TRIGGERS: 记录的就是触发器的信息，包括所有的相关的信息。包括系统的和自己用户创建的触发器。
• VIEWS: 视图的信息，包括系统的和用户的基本视图信息

4、 约束外键等相关的表

• REFERENTIAL_CONSTRAINTS: 这个表提供的外键相关的信息，而且只提供外键相关信息
• TABLE_CONSTRAINTS: 保存表的约束信息
• INNODB_SYS_FOREIGN_COLS: 这个表也是存储的INNODB关于外键的元数据信息和INNODB_SYS_FOREIGN 存储的信息是一致的
• INNODB_SYS_FOREIGN: 存储的INNODB关于外键的元数据信息和SYS_FOREIGN_COLS 存储的信息是一致的，只不过是单独对于INNODB来说的
• KEY_COLUMN_USAGE: 数据库中所有有约束的列都会存下下来，也会记录下约束的名字和类别

5、 关于数据库管理的表

• GLOBAL_STATUS: 保存MySQL的全局状态。全局是相对于Session而言的，Session是指单个Mysql连接，全局可以理解为自从Mysql启动以来，所有的连接，产生的状态。
• GLOBAL_VARIABLES: 保存MySQL的全局参数。

状态(status)是随着MySQL的运行，发生变化的。参数(variable)只有数据库管理员主动修改，才会变化的。

• SESSION_STATUS: 保存SESSION时的数据库状态，类似于GLOBAL_STATUS
• SESSION_VARIABLES: 保存SESSION的数据库变量，类似于GLOBAL_BARIABLES
• PARTITIONS: MySQL分区表相关的信息，通过这张表我们可以查询到分区的相关信息(数据库中已分区的表，以及分区表的分区和每个分区的数据信息)
• PROCESSLIST：show processlist其实就是从这个表拉取数据。由于是一个内存表，所以我们相当于在内存中查询一样，这些操作都是很快的。
• INNODB_CMP_PER_INDEX，INNODB_CMP_PER_INDEX_RESET:这两个表存储的是关于压缩INNODB信息表的时候的相关信息,有关整个表和索引信息都有
• INNODB_CMPMEM，INNODB_CMPMEM_RESET: 这两个表是存放关于MySQL INNODB的压缩页的buffer pool信息，但是要注意一点的就是,用这两个表来收集所有信息的表的时候,是会对性能造成严重的影响的,所以说默认是关闭状态的。
• INNODB_BUFFER_POOL_STATS: 表提供有关INNODB 的buffer pool相关信息，和show engine innodb status提供的信息是相同的。也是show engine innodb status的信息来源。
• INNODB_BUFFER_PAGE_LRU，INNODB_BUFFER_PAGE: 维护了INNODB LRU LIST的相关信息
• INNODB_SYS_DATAFILES: 这张表就是记录的表的文件存储的位置和表空间的一个对应关系(INNODB)
• INNODB_TEMP_TABLE_INFO:
• INNODB_METRICS: 提供INNODB的各种的性能指数，收集的是MySQL的系统统计信息。这些统计信息都是可以手动配置打开还是关闭的。有以下参数都是可以控制的：innodb_monitor_enable, innodb_monitor_disable, innodb_monitor_reset, innodb_monitor_reset_all。
• INNODB_SYS_VIRTUAL:表存储的是INNODB表的虚拟列的信息，
• INNODB_CMP，INNODB_CMP_RESET: 存储的是关于压缩INNODB信息表的时候的相关信息。

6、 关于表信息和索引信息的一些表

• TABLES: 记录的数据库中表的信息，其中包括系统数据库和用户创建的数据库。show table status like 'test1'G的来源就是这个表；
• TABLESPACES: 标注活跃表空间，这个表是不提供关于innodb的表空间信息。
• INNODB_SYS_TABLES: 这张表依赖的是SYS_TABLES数据字典中拉取出来的。此表提供了有关表格的格式和存储特性，包括行格式，压缩页面大小位级别的信息。提供的是关于INNODB的表空间信息，其实和SYS_TABLESPACES 中的INNODB信息是一致的。
• STATISTICS: 这个表提供的是关于表的索引信息，所有索引的相关信息。
• INNODB_SYS_INDEXES: 提供相关INNODB表的索引的相关信息，和SYS_INDEXES 这个表存储的信息基本是一样的，只不过后者提供的是所有存储引擎的索引信息，前者只提供INNODB表的索引信息。
• INNODB_SYS_TABLESTATS: 这个表非常重要，记录的是MySQL的INNODB表信息以及MySQL优化器会预估SQL选择合适的索引信息，其实就是MySQL数据库的统计信息。这个表的记录是记录在内存当中的，是一个内存表，每次重启后就会重新记录，所以只能记录从上次重启后的数据库统计信息。有了这个表，我们对于索引的维护就更加方便了，我们可以查询索引的使用次数，方便清理删除不常用的索引，提高表的更新插入等效率，节省磁盘空间。
• INNODB_SYS_FIELDS: 这个表记录的是INNODB的表索引字段信息，以及字段的排名
• INNODB_FT_CONFIG: 这张表存的是全文索引的信息
• INNODB_FT_DEFAULT_STOPWORD: 这个表存放的是stopword 的信息,是和全文索引匹配起来使用的，和innodb的 INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD 是相同的，这个STOPWORD必须是在创建索引之前创建，而且必须指定字段为varchar。stopword 也就是我们所说的停止词，全文检索时，停止词列表将会被读取和检索，在不同的字符集和排序方式下，会造成命中失败或者找不到此数据，这取决于停止词的不同的排序方式。我们可以使用这个功能筛选不必要字段。
• INNODB_FT_INDEX_TABLE: 这个表存储的是关于INNODB表有全文索引的索引使用信息的，同样这个表也是要设置innodb_ft_aux_table以后才能够使用的，一般情况下是空的
• INNODB_FT_INDEX_CACHE: 这张表存放的是插入前的记录信息，也是为了避免DML时候昂贵的索引重组

7、 关于MySQL优化相关的表

• OPTIMIZER_TRACE: 提供的是优化跟踪功能产生的信息.
• PROFILING: SHOW PROFILE可以深入的查看服务器执行语句的工作情况。以及也能帮助你理解执行语句消耗时间的情况。一些限制是它没有实现的功能，不能查看和剖析其他连接的语句，以及剖析时所引起的消耗。
• INNODB_FT_BEING_DELETED，INNODB_FT_DELETED: INNODB_FT_BEING_DELETED 这张表是INNODB_FT_DELETED的一个快照,只在OPTIMIZE TABLE 的时候才会使用。

8、 关于MySQL事物和锁的相关的一些表

• INNODB_LOCKS: 现在获取的锁，但是不含没有获取的锁，而且只是针对INNODB的。
• INNODB_LOCK_WAITS: 系统锁等待相关信息，包含了阻塞的一行或者多行的记录，而且还有锁请求和被阻塞改请求的锁信息等
• INNODB_TRX: 包含了所有正在执行的的事物相关信息(INNODB)，而且包含了事物是否被阻塞或者请求锁。

参考文献

MySQL ACID及四种隔离级别的解释

事务ACID特性及4种隔离级别详解

数据库事务的四大特性以及事务的隔离级别详解

InnoDB事务模型 (InnoDB Transaction Model)

MySQL的自动提交模式

Mysql元数据分析

MySQL information_schema 详解

MySQL 5.7 INFORMATION_SCHEMA 详解

information_schema系列二(列，列权限，事件，存储引擎)