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;