mysqlのinformation schema小結
3723 ワード
mysql 5.6のinformation schemaは、多くのデータベースを格納するメタデータであり、実際には多くの使用が可能である.
データベースのパフォーマンス・クエリーに使用するには、次の節でいくつかの項目を説明します.
1) Information_Schema.Tables
ここには、データベースのすべてのテーブルのメタデータ情報が格納されます.たとえば、次の文では、どのテーブルが最もスペースを占めているかを計算できます.
次に例を示します.
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;
データベースのパフォーマンス・クエリーに使用するには、次の節でいくつかの項目を説明します.
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;