information_schema

 

 

 

 

 

server层统计

 

权限问题：

1、所有用户都有访问information_schema下的表权限(但只能看到这些表中用户具有访问权限的对象相对应的数据行)，但只能访问Server层的部分数据字典表.
2、Server层中的部分数据字典表以及InnoDB层的数据字典表需要额外授权才能访问，如果用户权限不足，当查询Server层数据字典表时将不会返回任何数据，或者某个列没有权限访问时，该列返回NULL值。
3、当查询InnoDB数据字典表时将直接拒绝访问(要访问这些表需要有process权限，注意不是select权限)
4、从information_schema中查询相关数据需要的权限也适用于SHOW语句。无论使用哪种查询方式，都必须拥有某个对象的权限才能看到相关的数据。 
####################
process和show

 

 所有数据库详情

mysql> select * from SCHEMATA;
+--------------+--------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+--------------------+----------------------------+------------------------+----------+
| def          | information_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | apple              | utf8mb4                    | utf8mb4_general_ci     | NULL     |
| def          | mysql              | utf8mb4                    | utf8mb4_general_ci     | NULL     |
| def          | performance_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | sys                | utf8                       | utf8_general_ci        | NULL     |
+--------------+--------------------+----------------------------+------------------------+----------+
5 rows in set (0.05 sec)

mysql>

 

 

 tables表：

ser@hostname (information_schema) > desc tables;
+-----------------+---------------------+------+-----+---------+-------+
| field           | type                | null | key | default | extra |
+-----------------+---------------------+------+-----+---------+-------+
| table_catalog   | varchar(512)        | no   |     |         |       |
| table_schema    | varchar(64)         | no   |     |         |       | 数据库名称
| table_name      | varchar(64)         | no   |     |         |       | 表名称
| table_type      | varchar(64)         | no   |     |         |       | 表类型
| engine          | varchar(64)         | yes  |     | null    |       | 存储引擎
| version         | bigint(21) unsigned | yes  |     | null    |       |
| row_format      | varchar(20)         | yes  |     | null    |       |
| table_rows      | bigint(21) unsigned | yes  |     | null    |       | 行数
| avg_row_length  | bigint(21) unsigned | yes  |     | null    |       |
| data_length     | bigint(21) unsigned | yes  |     | null    |       | 数据大小
| max_data_length | bigint(21) unsigned | yes  |     | null    |       |
| index_length    | bigint(21) unsigned | yes  |     | null    |       | 索引大小
| data_free       | bigint(21) unsigned | yes  |     | null    |       |
| auto_increment  | bigint(21) unsigned | yes  |     | null    |       | 自增长度
| create_time     | datetime            | yes  |     | null    |       | 创建时间
| update_time     | datetime            | yes  |     | null    |       | 更新时间
| check_time      | datetime            | yes  |     | null    |       |
| table_collation | varchar(32)         | yes  |     | null    |       |
| checksum        | bigint(21) unsigned | yes  |     | null    |       |
| create_options  | varchar(255)        | yes  |     | null    |       |
| table_comment   | varchar(2048)       | no   |     |         |       | 注释
+-----------------+---------------------+------+-----+---------+-------+
21 rows in set (0.00 sec)

 

 

 

*************************** 19. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: statistics
     TABLE_NAME: user_2019_08
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Compressed
     TABLE_ROWS: 351716565
 AVG_ROW_LENGTH: 37
    DATA_LENGTH: 13293322240
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 10047455232
      DATA_FREE: 3670016
 AUTO_INCREMENT: 381762867
    CREATE_TIME: 2019-09-10 22:59:33
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: row_format=COMPRESSED
  TABLE_COMMENT: 原来的表已经很大了，新指标做在这张表里

 

 

 

 

 

 

 

 

所有存储引擎详情

 

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

mysql>

 

 

 

 

 

 

 

innodb层

 

锁、事务

 

mysql> show create table innodb_locks\G;
*************************** 1. row ***************************
       Table: INNODB_LOCKS
Create Table: CREATE TEMPORARY TABLE `INNODB_LOCKS` (
  `lock_id` varchar(81) NOT NULL DEFAULT '',
  `lock_trx_id` varchar(18) NOT NULL DEFAULT '',
  `lock_mode` varchar(32) NOT NULL DEFAULT '',
  `lock_type` varchar(32) NOT NULL DEFAULT '',
  `lock_table` varchar(1024) NOT NULL DEFAULT '',
  `lock_index` varchar(1024) DEFAULT NULL,
  `lock_space` bigint(21) unsigned DEFAULT NULL,
  `lock_page` bigint(21) unsigned DEFAULT NULL,
  `lock_rec` bigint(21) unsigned DEFAULT NULL,
  `lock_data` varchar(8192) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>