mysql 中的information schema小结ITeye - 乐橙lc8

mysql 中的information schema小结ITeye

2019年03月30日10时59分27秒 | 作者: 振翱 | 标签: 数据库,寄存,比方 | 浏览: 120

  mysql 5.6中的information schema,便是存储了许多数据库的元数据,其实许多能够用来
做数据库的功能查询用,下面小结介绍其间几个:


1) Information_Schema.Tables
  这儿寄存的是数据库的一切表的元数据信息,比方下面的句子能够核算哪些表是最占空间的:
 
Select
 Concat(table_schema, ., table_name) As "Name"
 ,Concat(Round(table_rows / 1000000, 2), M) As "Rows"
 ,Concat(Round(data_length / ( 1024 * 1024 * 1024 ), 2), G) As "Row Size"
 ,Concat(Round(index_length / ( 1024 * 1024 * 1024 ), 2), G) As "Index Size"
 ,Concat(Round(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), G) As "Total"
 ,Round(index_length / data_length, 2) "Row / Index Ratio"
From information_schema.TABLES
Order By data_length + index_length DESC
Limit 10;



  又例如:
  Select Table_Name
  - From Information_Schema.Tables
  - Where Engine innodb and Table_Schema = employees;

2) Information_Schema.Columns
  这个是寄存办理列的信息了,比方:
  mysql Select Table_Name, Column_Name From Information_schema.Columns
  - Where Column_Name Like %name% And Table_Schema = employees;

3) Information_Schema.Referential_Constraints
  这儿是寄存什么表用到什么样的外键
  mysql Select
  -   Table_Name
  -   ,Constraint_Name
  -   ,Referenced_Table_Name Ref_Tbl
  -   ,Unique_Constraint_Name As Ref_Cnstr
  - From Information_schema.Referential_Constraints
  - Where Constraint_Schema = employees;
++-+-+-+
| Table_Name  | Constraint_Name  | Ref_Tbl  | Ref_Cnstr |
++-+-+-+
| dept_emp  | dept_emp_ibfk_1  | employees  | PRIMARY  |
| dept_emp  | dept_emp_ibfk_2  | departments | PRIMARY  |
| dept_manager | dept_manager_ibfk_1 | employees  | PRIMARY  |
| dept_manager | dept_manager_ibfk_2 | departments | PRIMARY  |
| salaries  | salaries_ibfk_1  | employees  | PRIMARY  |
| titles  | titles_ibfk_1  | employees  | PRIMARY  |
++-+-+-+
6 rows in set (0.00 sec)

4) Information_Schema.Key_Column_Usage
  这个是用来显现组成索引的有哪些列,比方:
使用mysql 的INFORMATION_SCHEMA.KEY_COLUMN_USAGE表查找某个表的外键(primary, unique, and foreign key constraints)
句子:select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME =guestbook and CONSTRAINT_NAME LIKE fk_%;


5)
Information_Schema.Processlist
看进程状况
  mysql Select User, Command, Time From Information_schema.processlist;
++-++
| User | Command | Time |
++-++
| root | Sleep  |  86 |
| root | Sleep  |  439 |
| root | Query  |  0 |
++-++
3 rows in set (0.01 sec)

6) Information_Schema.InnoDb_Lock_Waits
  看等候事情
  Select
  r.trx_id waiting_trx_id
  ,r.trx_mysql_thread_id waiting_thread
  ,r.trx_query waiting_query
  ,b.trx_id blocking_trx_id
  ,b.trx_mysql_thread_id blocking_thread
  ,b.trx_query blocking_query
From information_schema.innodb_lock_waits w
Join information_schema.innodb_trx b On b.trx_id = w.blocking_trx_id
Join information_schema.innodb_trx r On r.trx_id = w.requesting_trx_id;
版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表乐橙lc8立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章