Mysql5.6中的information schema,就是存储了很多数据库的元数据,很多可以用来做数据库的性能查询用,下面介绍其中几个:
这里存放的是数据库的所有表的元数据信息,比如下面的语句可以计算哪些表是最占空间的:
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';
这个是存放管理列的信息了,比如:
Select Table_Name, Column_Name From Information_schema.Columns Where Column_Name Like '%name%' And Table_Schema = 'employees';
这里是存放什么表用到什么样的外键
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)
这个是用来显示组成索引的有哪些列,比如:
利用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_%';
看进程情况
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)
看等待事件
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;