mysql 中的information schema小结

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;