概述

information_schema 数据库跟 performance_schema 一样，都是 MySQL 自带的信息数据库。其中 performance_schema 用于性能分析，而 information_schema 用于存储数据库元数据(关于数据的数据)，例如数据库名、表名、列的数据类型、访问权限等。

information_schema 中的表实际上是视图，而不是基本表，因此，文件系统上没有与之相关的文件。

今天主要分享一些跟information_schema息息相关的一些统计命令。

1、没有使用索引的表统计

SELECT
	t.TABLE_SCHEMA,
	t.TABLE_NAME,
	t.TABLE_ROWS 
FROM
	information_schema.TABLES AS t
	LEFT JOIN ( SELECT DISTINCT table_schema, table_name FROM information_schema.`KEY_COLUMN_USAGE` ) AS kt 
 ON kt.table_schema = t.table_schema 
	AND kt.table_name = t.table_name 
WHERE
	t.table_schema NOT IN ( 'mysql', 'information_schema', 'performance_schema', 'test' ) 
	AND kt.table_name IS NULL;

2、查看存储过程和函数

select routine_name,ROUTINE_SCHEMA,ROUTINE_TYPE from information_schema.routines;

3、查看使用myisam的表

SELECT
	table_schema,
	table_name 
FROM
	information_schema.TABLES 
WHERE
	ENGINE = 'MyISAM' 
	AND table_schema NOT IN ( 'information_schema', 'performance_schema', 'mysql' )

4、查看没有使用主键的表统计

SELECT
	t.table_schema,
	t.table_name,
	t.ENGINE,
IF
	( ISNULL( c.constraint_name ), 'NOPK', '' ) AS nopk,
IF
	( s.index_type = 'FULLTEXT', 'FULLTEXT', '' ) AS ftidx,
IF
	( s.index_type = 'SPATIAL', 'SPATIAL', '' ) AS gisidx 
FROM
	information_schema.TABLES AS t
	LEFT JOIN information_schema.key_column_usage AS c ON ( t.table_schema = c.constraint_schema AND t.table_name = c.table_name AND c.constraint_name = 'PRIMARY' )
	LEFT JOIN information_schema.statistics AS s ON ( t.table_schema = s.table_schema AND t.table_name = s.table_name AND s.index_type IN ( 'FULLTEXT', 'SPATIAL' ) ) 
WHERE
	t.table_schema NOT IN ( 'information_schema', 'performance_schema', 'mysql' ) 
	AND t.table_type = 'BASE TABLE' 
	AND c.constraint_name IS NULL 
ORDER BY
	t.table_schema,
	t.table_name;

5、行数据前10统计的表

SELECT
	table_schema,
	table_name,
	table_rows,
	data_length,
	index_length,
	CONCAT( ROUND( ( data_length + index_length ) / ( 1024 * 1024 ), 2 ), 'M' ) AS 'Total',
	CONCAT( ROUND( DATA_FREE / ( 1024 * 1024 ), 2 ), 'M' ) 
FROM
	information_schema.TABLES 
WHERE
	table_schema NOT IN ( 'information_schema', 'mysql', 'performance_schema', 'test' ) 
ORDER BY
	table_rows DESC 
	LIMIT 10;

6、查看主外键约束

SELECT
	C.TABLE_SCHEMA,
	C.REFERENCED_TABLE_NAME,
	C.REFERENCED_COLUMN_NAME,
	C.TABLE_NAME,
	C.COLUMN_NAME,
	C.CONSTRAINT_NAME,
	T.TABLE_COMMENT,
	R.UPDATE_RULE,
	R.DELETE_RULE 
FROM
	INFORMATION_SCHEMA.KEY_COLUMN_USAGE C
	JOIN INFORMATION_SCHEMA.TABLES T ON T.TABLE_NAME = C.TABLE_NAME
	JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON R.TABLE_NAME = C.TABLE_NAME 
	AND R.CONSTRAINT_NAME = C.CONSTRAINT_NAME 
	AND R.REFERENCED_TABLE_NAME = C.REFERENCED_TABLE_NAME 
WHERE
	C.REFERENCED_TABLE_NAME IS NOT NULL;

7、查看触发器

select TRIGGER_SCHEMA,TRIGGER_NAME,DEFINER,ACTION_STATEMENT from information_schema.TRIGGERS 

8、查看视图表

select table_schema,table_name,definer,VIEW_DEFINITION,COLLATION_CONNECTION from information_schema.views 
where table_schema not in ('sys')
group by table_name,table_schema,VIEW_DEFINITION,VIEW_DEFINITION,COLLATION_CONNECTION

说明：

• sql1语句收集没有任何索引的表(包括主键)，这种表是非常危险的，应该避免出现
• sql2语句收集存储过程和函数，有利于线上的规范
• sql3 语句收集myisam表引擎的表，这种表会引起大量的表级锁，应该避免出现
• sql4 语句收集没有主键的表,这种表会引起性能问题，应该避免出现
• sql5 语句收集大表前10，大表对于mysql的性能影响很关键，应该尽量减少大表的出现
• sql6 语句收集主外键约束的表,拥有主外键约束的表可以影响手动DML操作，DBA应该注意
• sql7 语句收集触发器的表，用户触发器的表是无法应用pt工具的，DBA应该注意
• sql8 视图语句收集的表，通过对 views 表的查询可以查看数据库中所有视图的详细信息。

后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~

如果你觉得这篇文章对你有帮助, 请小小打赏下~