Mysql元数据分析

一、information_schema库

information_schema库中的表，保存的是Mysql的元数据。

库中有表：

+---------------------------------------+| Tables_in_information_schema          |+---------------------------------------+| CHARACTER_SETS                        || COLLATIONS                            || COLLATION_CHARACTER_SET_APPLICABILITY || COLUMNS                               || COLUMN_PRIVILEGES                     || ENGINES                               || EVENTS                                || FILES                                 || GLOBAL_STATUS                         || GLOBAL_VARIABLES                      || KEY_COLUMN_USAGE                      || PARAMETERS                            || PARTITIONS                            || PLUGINS                               || PROCESSLIST                           || PROFILING                             || REFERENTIAL_CONSTRAINTS               || ROUTINES                              || SCHEMATA                              || SCHEMA_PRIVILEGES                     || SESSION_STATUS                        || SESSION_VARIABLES                     || STATISTICS                            || TABLES                                || TABLESPACES                           || TABLE_CONSTRAINTS                     || TABLE_PRIVILEGES                      || TRIGGERS                              || USER_PRIVILEGES                       || VIEWS                                 || INNODB_CMP_RESET                      || INNODB_TRX                            || INNODB_CMPMEM_RESET                   || INNODB_LOCK_WAITS                     || INNODB_CMPMEM                         || INNODB_CMP                            || INNODB_LOCKS                          |+---------------------------------------+

1. CHARACTER_SETS

保存所有Mysql可用的字符集。相当于命令：SHOW CHARACTER SET

2. COLLATIONS

提供了关于各字符集的对照信息

3. COLLATION_CHARACTER_SET_APPLICABILITY

4. COLUMNS

这个表保存的是所有数据库的列信息

TABLE_CATALOG

TABLE_SCHEMA 库名

TABLE_NAME 表名

COLUMN_NAME 列名

ORDINAL_POSITION 应该是该列在该表中的顺序

COLUMN_DEFAULT 列的默认值

IS_NULLABLE 是否可以为NULL

DATA_TYPE 数据类型

CHARACTER_MAXIMUM_LENGTH 数据的长度

CHARACTER_OCTET_LENGTH 数据的存储长度

NUMERIC_PRECISION

NUMERIC_SCALE

CHARACTER_SET_NAME 列的字符编码

COLLATION_NAME

COLUMN_TYPE 列的类型，例如varchar(20)

COLUMN_KEY 如果等于PRI，表示是主键

EXTRA 定义列的时候的其他信息，例如自增，主键

PRIVILEGES 操作权限有：select,insert,update,references （ ）

COLUMN_COMMENT 列的备注

例子：

***************************[ 1. row ]***************************TABLE_CATALOG            | defTABLE_SCHEMA             | testTABLE_NAME               | sleep_accountCOLUMN_NAME              | keyORDINAL_POSITION         | 1COLUMN_DEFAULT           | NoneIS_NULLABLE              | NODATA_TYPE                | intCHARACTER_MAXIMUM_LENGTH | NoneCHARACTER_OCTET_LENGTH   | NoneNUMERIC_PRECISION        | 10NUMERIC_SCALE            | 0CHARACTER_SET_NAME       | NoneCOLLATION_NAME           | NoneCOLUMN_TYPE              | int(11)COLUMN_KEY               | PRIEXTRA                    | auto_incrementPRIVILEGES               | select,insert,update,referencesCOLUMN_COMMENT           |

5. COLUMN_PRIVILEGES

列的特权信息，应该和COLUMN表的PRIVILEGES的功能差不多的。

6. ENGINES

存储Mysql支持的数据库引擎类型，相当于命令SHOW ENGINES

ENGINE 引擎名

SUPPORT 是否支持，Yes，No，Default(默认)

COMMENT 引擎的说明

TRANSACTIONS 是否支持事务

XA

SAVEPOINTS

***************************[ 1. row ]***************************ENGINE       | InnoDBSUPPORT      | DEFAULTCOMMENT      | Supports transactions, row-level locking, and foreign keysTRANSACTIONS | YESXA           | YESSAVEPOINTS   | YES

7. EVENTS

保存计划事件（scheduled events）的信息，相当于命令 show events

8. FILES

保存数据库文件的存储信息，当使用Mysql集群的时候有用，也就是NDB。

9.GLOBAL_STATUS

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

10.GLOBAL_VARIABLES

保存Mysql的全局参数。

状态（status）是随着Mysql的运行，会变化的，

参数（variable）只有主动修改，才会变化的。

可以使用show status 语法查看

11.KEY_COLUMN_USAGE

保存所有约束（CONSTRAINT）

CONSTRAINT_CATALOG

CONSTRAINT_SCHEMA 约束的数据库

CONSTRAINT_NAME 约束名

TABLE_CATALOG

TABLE_SCHEMA 约束属于哪个数据库

TABLE_NAME 约束属于哪个数据表

COLUMN_NAME 约束的列名

ORDINAL_POSITION 排序权重

POSITION_IN_UNIQUE_CONSTRAINT

REFERENCED_TABLE_SCHEMA

REFERENCED_TABLE_NAME

REFERENCED_COLUMN_NAME

例如：

test库的account表中，主键是key，就会有下面一行记录

CONSTRAINT_CATALOG            | defCONSTRAINT_SCHEMA             | testCONSTRAINT_NAME               | PRIMARYTABLE_CATALOG                 | defTABLE_SCHEMA                  | testTABLE_NAME                    | accountCOLUMN_NAME                   | keyORDINAL_POSITION              | 1POSITION_IN_UNIQUE_CONSTRAINT | NoneREFERENCED_TABLE_SCHEMA       | NoneREFERENCED_TABLE_NAME         | NoneREFERENCED_COLUMN_NAME        | None

12.PARAMETERS

保存了所有已定义的PARAMETERS 信息

13.PARTITIONS

保存所有分区表信息

14.PLUGINS

保存所有Mysql已装载的插件信息

15. PROCESSLIST

保存Mysql的连接信息，一行记录代表一个数据库连接，代表一个Mysql服务线程。相当于SHOW PROCESSLIST

查看该表会带来一定的性能影响，因为需要一个锁，查看Threads表却不会。

而且查看Threads表会显示后台线程，PROCESSLIST缺不会显示。

这里的线程可以使用KILL语法来杀掉

ID 连接ID，根据这个ID来执行KILL命令

USER 连接的用户名

HOST 连接的客户端的IP，格式是IP:PORT。如果想查看一个连接对应的是哪个客户端进程，就可以这样：假如HOST='192.168.1.1:23501'，去到192.168.1.1这台机，通过命令netstat -apn|grep 23501,看到这样的结果：

192.168.1.1:23501           192.168.1.10:3306            ESTABLISHED 14599/python2.7

就可以知道这个Mysql的连接的客户端是14599/python2.7这个进程

DB 连接的数据库

COMMAND 线程在执行的命令，。常用的命令有：SLeep（等待客户端发送SQL），Query（正在执行一个SQL）

TIME 单位是秒，表示这个连接处于现在这个命令多久了

STATE 线程执行的命令的细节描述，。一般这个状态持续的时间是很短的，如果持续了很久，就表明有问题了。常见的描述：Updating（正在更新数据），executing（正在执行），Sending data（发送数据给客户端）。这里的描述和show profile for query 1;里的执行步骤是对应的

INFO 正在执行的SQL语句，如果没有执行SQL，为空。

例子：

ID      | 51USER    | rootHOST    | localhost:59487DB      | information_schemaCOMMAND | QueryTIME    | 0STATE   | executingINFO    | select * from PROCESSLIST limit 1

16. PROFILING

保存性能分析的数据，相当于 SHOW PROFILES。只有当session的profiling 参数设置为1，这个表才有数据。

17.REFERENTIAL_CONSTRAINTS

保存外键的数据。

18.ROUTINES

保存routines 信息，包括procedures 和 functions，但是不包含用户定于的functions。

19.SCHEMATA

保存数据库的信息，一行记录是一个数据库（database），类似命令show databases;

20.SCHEMA_PRIVILEGES

保存数据库的权限信息。

GRANTEE 权限拥有者，格式是'user_name'@'host_name'，例如root'@'192.168.137.1

TABLE_CATALOG

TABLE_SCHEMA 权限对应的数据库

PRIVILEGE_TYPE 权限类型

IS_GRANTABLE 是否可以分配权限给其他拥有者，一般为NO

GRANTEE: 'root'@'192.168.137.1' TABLE_CATALOG: def  TABLE_SCHEMA: ggy_wrdPRIVILEGE_TYPE: SELECT  IS_GRANTABLE: NO

这个表示拥有者'root'@'192.168.137.1'有权限对数据库ggy_wrd执行SELECT的操作

21.SESSION_STATUS

保存SESSION的状态，类似于GLOBAL_STATUS

22.SESSION_VARIABLES

保存SESSION的变量，类似于GLOBAL_BARIABLES

23.STATISTICS

保存索引信息。相当于show index from tbl_name

TABLE_CATALOG

TABLE_SCHEMA 数据库名

TABLE_NAME 表名

NON_UNIQUE 是否唯一

INDEX_SCHEMA

INDEX_NAME

SEQ_IN_INDEX

COLUMN_NAME 列名

COLLATION

CARDINALITY

SUB_PART

PACKED

NULLABLE

INDEX_TYPE 索引类型，一般是BTREE

COMMENT

INDEX_COMMENT

例子：

TABLE_CATALOG: def TABLE_SCHEMA: db_kklauncher   TABLE_NAME: sleep_local_account   NON_UNIQUE: 0 INDEX_SCHEMA: db_kklauncher   INDEX_NAME: PRIMARY SEQ_IN_INDEX: 1  COLUMN_NAME: key    COLLATION: A  CARDINALITY: 10673     SUB_PART: NULL       PACKED: NULL     NULLABLE:    INDEX_TYPE: BTREE      COMMENT: INDEX_COMMENT:

24.TABLES

保存数据表信息。类似show tables。

TABLE_CATALOG

TABLE_SCHEMA

TABLE_NAME 表名

TABLE_TYPE 表的类型

ENGINE 表的存储引擎

VERSION 表的版本

ROW_FORMAT

TABLE_ROWS 表的行数

AVG_ROW_LENGTH 平均一行的长度

DATA_LENGTH 数据长度

MAX_DATA_LENGTH 最大一行的数据长度

INDEX_LENGTH 索引的长度

DATA_FREE

AUTO_INCREMENT 自增到哪个数

CREATE_TIME 创建时间

UPDATE_TIME 最后修改表结构的时间

CHECK_TIME

TABLE_COLLATION 表的编码

CHECKSUM

CREATE_OPTIONS

TABLE_COMMENT

TABLE_CATALOG   | defTABLE_SCHEMA    | db_kklauncherTABLE_NAME      | sleep_local_accountTABLE_TYPE      | BASE TABLEENGINE          | InnoDBVERSION         | 10ROW_FORMAT      | CompactTABLE_ROWS      | 10095AVG_ROW_LENGTH  | 365DATA_LENGTH     | 3686400MAX_DATA_LENGTH | 0INDEX_LENGTH    | 327680DATA_FREE       | 415236096AUTO_INCREMENT  | 24342CREATE_TIME     | 2016-12-27 16:31:56UPDATE_TIME     | NoneCHECK_TIME      | NoneTABLE_COLLATION | utf8_general_ciCHECKSUM        | NoneCREATE_OPTIONS  | TABLE_COMMENT   |

25.TABLESPACES

保存数据表占用的空间，如果表引擎是InnoDB，需要去查 INNODB_SYS_TABLESPACES 和INNODB_SYS_DATAFILES

26. TABLE_CONSTRAINTS

保存表的约束信息。

27.TABLE_PRIVILEGES

保存表的权限信息。如果赋予拥有者一个表的权限，TABLE_PRIVILEGES表就会有数据。如果是赋予拥有者一个库的权限，这里就不会有数据，只会在SCHEMA_PRIVILEGES表里面有数据。

这里的数据和SCHEMA_PRIVILEGES的数据意义是一样的，只不过多了TABLE_SCHEMA这列。

28.TRIGGERS

保存触发器的信息

29.USER_PRIVILEGES

这里会存储用户的权限。

30.VIEWS

保存视图信息

31.INNODB_CMP_RESET和INNODB_CMP

保存被压缩的InnoDB表的信息

32.INNODB_TRX

保存InnoDB的事务信息（不会包含只读的事务）。

trx_id 事务ID，唯一的，只读事务没有生成ID

trx_state 当前的状态，取值：RUNNING（正在执行）, LOCK WAIT（等待锁）, ROLLING BACK（回滚）, and COMMITTING（提交中）

trx_started 事务启动的时间

trx_requested_lock_id 如果状态是LOCK WAIT，这里显示的是正在等待的锁的ID，对应INNODB_LOCKS表的LOCK_ID列

trx_wait_started 如果状态是LOCK WAIT，这里显示的是该事务等待锁等待了多久

trx_weight 事务的权重，权重越低，Mysql越先执行一个事务，这个主要用于解决死锁

trx_mysql_thread_id 事务对应的线程ID，和PROCESSLIST表的ID列对应

trx_query 事务正在执行的SQL

trx_operation_state 事务当前的操作状态，如果没有，显示NULL

trx_tables_in_use 事务处理当前的SQL，也就是trx_query里的SQL，需要打开多少个表

trx_tables_locked 事务处理当前的SQL需要上锁多少个表的行锁

trx_lock_structs 有多少个锁会被该事务保留，也就是执行该事务需要锁住多少条行记录

trx_lock_memory_bytes 锁需要耗用的内存

trx_rows_locked 事务处理当前的SQL需要上锁多少个行锁，这只是个近似值

trx_rows_modified 事务需要修改或新增多少行内容

trx_concurrency_tickets 该线程被调度前，需要执行多少工作

trx_isolation_level 事务的隔离级别

trx_unique_checks 是否打开唯一检查（unique_checks）

trx_foreign_key_checks 是否打开外键唯一检查（ foreign key checks）

trx_last_foreign_key_error 上一次外键错误信息

trx_adaptive_hash_latched

trx_adaptive_hash_timeout

例子：

trx_id                     | 7B441trx_state                  | LOCK WAITtrx_started                | 2017-02-06 18:16:26trx_requested_lock_id      | 7B441:0:5172:49trx_wait_started           | 2017-02-06 18:16:26trx_weight                 | 2trx_mysql_thread_id        | 60trx_query                  | update account set nickname='aaabbb4' where `key`=11165trx_operation_state        | starting index readtrx_tables_in_use          | 1trx_tables_locked          | 1trx_lock_structs           | 2trx_lock_memory_bytes      | 376trx_rows_locked            | 1trx_rows_modified          | 0trx_concurrency_tickets    | 0trx_isolation_level        | REPEATABLE READtrx_unique_checks          | 1trx_foreign_key_checks     | 1trx_last_foreign_key_error | Nonetrx_adaptive_hash_latched  | 0trx_adaptive_hash_timeout  | 10000

33.NODB_LOCK_WAITS

保存等待锁的连接的信息

requesting_trx_id 正在请求的事务ID，也就是等待锁的事务ID

requested_lock_id 请求事务的ID获得锁成功后，会创建的锁ID

blocking_trx_id 已经获取锁的事务ID

blocking_lock_id 已经获取的锁ID

requesting_trx_id | 7B444requested_lock_id | 7B444:0:5172:49blocking_trx_id   | 7B43Fblocking_lock_id  | 7B43F:0:5172:49

表示事务7B43F获得了锁7B43F:0:5172:49，事务7B444在等待锁，获取锁后，会创建锁7B444:0:5172:49

34.INNODB_LOCKS

保存InnoDB的锁信息。只有当存在等待锁的时候，这个表才会有数据。例如一个线程获得了锁，但是没有commit，这个表是没有数据的，当另一个线程等待锁，这个表会有两条数据，一个是已获得的锁，一个是等待的锁。

lock_id 锁的ID，不要尝试解析ID的意义

lock_trx_id 已经获取该锁的事务ID，和INNODB_TRX 表的trx_id对应

lock_mode 锁的模式，

lock_type 锁的类型，RECORD （行锁），TABLE（表锁）

lock_table 被锁的表名

lock_index 如果是行锁，显示索引名

lock_space 如果是行锁，显示表空间（Tablespace ）的ID

lock_page 如果是行锁，显示被锁的行的页码

lock_rec 如果是行锁，显示被锁的行的页码里面的堆栈号

lock_data 被锁的行的主键的值，如果没有主键，显示InnoDB内部的行ID

例子：

lock_id     | 7B449:0:5172:49lock_trx_id | 7B449lock_mode   | Xlock_type   | RECORDlock_table  | `test`.`account`lock_index  | `PRIMARY`lock_space  | 0lock_page   | 5172lock_rec    | 49lock_data   | 10001

二、常用的语法

1.SHOW STATUS 语法

SHOW [GLOBAL | SESSION] STATUS    [LIKE 'pattern' | WHERE expr]

表中有Variable_name 和Value两个列。不区分大小写

LIKE是WHERE的快捷方式

例如这两个是等价的

show global status like '%thread%' show global status where Variable_name like '%thread%'

WHERE的功能更强大，例如：

show global status where Value  =0

2.status说明

常用的：

• Queries 执行的查询总数
• Threads_connected 服务器连接数，如果使用线程池，这个指标变化不大
• Threads_running 执行查询的线程数

行锁相关：

• Innodb_row_lock_current_waits 当前正在等待行锁的连接数
• Innodb_row_lock_time 等待行锁耗费的时间，单位MS
• Innodb_row_lock_time_avg 等待行锁耗费的平均时间
• Innodb_row_lock_time_max 等待行锁耗费的最大时间
• Innodb_row_lock_waits 一直以来等待行锁的连接数

Com开头的：

表示每个操作执行的次数，例如Com_select表示执行select操作的次数。

3. SHOW VARIABLES 语法

SHOW [GLOBAL | SESSION] VARIABLES  [LIKE 'pattern' | WHERE expr]

跟show status类似

4. variables说明

• innodb_lock_wait_timeout 等待行锁的超时时间

5. SHOW PROCESSLIST语法

SHOW [FULL] PROCESSLIST

如果没有FULL，只会显示SQL语句的前100个字符

内容同PROCESSLIST表是一样的

6. KILL语法

7.SHOW PROFILE语法

8. SHOW OPEN TABLES语法

这个语法显示正在缓存中被打开的表。

例子：

Database    | db_kklauncherTable       | sleep_local_accountIn_use      | 1Name_locked | 0

Database 数据库名

Table 数据表名

In_use 使用该表的客户端数量。一般是表示有多少个客户端在等待这个表的锁。

Name_locked 表名是否被锁，一般只有删除表或还原表的时候，这个会等于1

三、常用命令

1. 查看哪个事务获得了锁（只显示有连接在等待的锁）

   select * from INNODB_LOCKS,`INNODB_TRX` where INNODB_TRX.trx_id=INNODB_LOCKS.lock_trx_id and INNODB_TRX.trx_state ='RUNNING'\G;

2. 查看获得锁的连接情况

   select l.lock_id ,l.lock_index,l.lock_data,p.id,p.command,p.time,p.host from information_schema.innodb_trx as t,information_schema.innodb_locks as l ,information_schema.processlist as p where t.trx_id=l.lock_trx_id and p.id=t.trx_mysql_thread_id and l.lock_id in (select distinct blocking_lock_id from information_schema.INNODB_LOCK_WAITS)

四、Innodb的锁

假如有student表：

+----+------+------+-------+| id | name | age  | class |+----+------+------+-------+|  1 | 2    |   20 | A     ||  2 | 1    |   22 | A     ||  3 | 1    |   23 | A     ||  4 | NULL |   24 | B     ||  5 | NULL |   24 | B     |+----+------+------+-------+

1. 连接A执行SQLupdate student set name='1' where class='A' ;，但是没有commit

   这时如果连接B执行SQLupdate student set name='2' where id=1;，然后执行查看锁命令，结果是：

   lock_id: 7B600:0:40373:7               lock_trx_id: 7B600                 lock_type: RECORD                lock_table: `test`.`student`                lock_index: `PRIMARY`                 lock_data: 0               trx_started: 2017-02-07 17:19:33       trx_mysql_thread_id: 2          trx_lock_structs: 2           trx_rows_locked: 3         trx_rows_modified: 4

• 因为连接B等待id=0的行锁，所以这里只会显示lock_data=1，但是实际连接A是锁住了3条记录的（id 1-3），
• 事务ID7B600是连接A的事务ID
• lock_index和lock_data是关联的，如果lock_index是PRIMARY，lock_data就是行记录的主键。如果lock_index是其他索引例如是索引my_index，而my_index索引的列是class，lock_data就会是A，3，也就是前面是索引列的值，后面是主键。
• 现在还不知道lock_index的索引是连接A还是连接B定位数据时使用的索引

1. 连接A执行SQLupdate student set name='1' where class='B' ;,也就是更新id=4，5两条记录，但是没有commit
   这时如果连接B执行SQLupdate student set name='2' where age<24;，也就是更新全部记录，接着连接C执行SQLupdate student set name='1' where id=1;，更新id=1这条记录。这样的结果是：连接A获取了id=4，5的锁，连接B获取了id=1，2，3的锁，正在等待id=4的锁，事务状态是LOCK_WAIT，连接C等待id=1的锁，事务状态也是LOCK_WAIT。

未经许可，请不要转载。