博客
关于我
强烈建议你试试无所不能的chatGPT,快点击我
MySQL INFORMATION_SCHEMA 使用
阅读量:6566 次
发布时间:2019-06-24

本文共 7127 字,大约阅读时间需要 23 分钟。

一、INFORMATION_SCHEMA介绍

第一个查询看看库里有多少个表,表名等

select * from INFORMATION_SCHEMA.TABLES

information_schema这张数据表保存了服务器所有的信息。如数据库名,数据库的表,表栏的数据类型与访问权限等。再简单点,这台服务器上,到底有哪些数据库、各个数据库有哪些表,每张表的字段类型是什么,各个数据库要什么权限才能访问,等等信息都保存在information_schema表里面。

Mysql的INFORMATION_SCHEMA数据库包含了一些表和视图,提供了访问数据库元数据的方式。

元数据是关于数据的数据,如数据库名或表名,列的数据类型,或访问权限等。有些时候用于表述该信息的其他术语包括“数据词典”和“系统目录”。

 

下面对一些重要的数据字典表做一些说明:

SCHEMATA表:提供了关于数据库的信息。

TABLES表:给出了关于数据库中的表的信息。

COLUMNS表:给出了表中的列信息。

STATISTICS表:给出了关于表索引的信息。

USER_PRIVILEGES表:给出了关于全程权限的信息。该信息源自mysql.user授权表。

SCHEMA_PRIVILEGES表:给出了关于方案(数据库)权限的信息。该信息来自mysql.db授权表。

TABLE_PRIVILEGES表:给出了关于表权限的信息。该信息源自mysql.tables_priv授权表。

COLUMN_PRIVILEGES表:给出了关于列权限的信息。该信息源自mysql.columns_priv授权表。

CHARACTER_SETS表:提供了关于可用字符集的信息。

COLLATIONS表:提供了关于各字符集的对照信息。

COLLATION_CHARACTER_SET_APPLICABILITY表:指明了可用于校对的字符集。

TABLE_CONSTRAINTS表:描述了存在约束的表。

KEY_COLUMN_USAGE表:描述了具有约束的键列。

ROUTINES表:提供了关于存储子程序(存储程序和函数)的信息。此时,ROUTINES表不包含自定义函数(UDF)。

VIEWS表:给出了关于数据库中的视图的信息。

TRIGGERS表:提供了关于触发程序的信息。

二、使用示例

--查看创建的索引的CARDINALITY比率

--通常cardinality达到表数据的10%左右建索引会有意义 --如果是一个组合索引,索引第一位的cardinality表示第一个列的cardinality大小,第二列表示第一列和第二列共同的cardinality值 SELECT     T1.TABLE_SCHEMA,    T1.TABLE_NAME,    T2.INDEX_NAME,    ROUND(T2.CARDINALITY / T1.TABLE_ROWS * 100, 2) AS RATEFROM    INFORMATION_SCHEMA.TABLES T1,    INFORMATION_SCHEMA.STATISTICS T2WHERE    T1.TABLE_SCHEMA = T2.TABLE_SCHEMA        AND T1.TABLE_NAME = T2.TABLE_NAME        AND T2.SEQ_IN_INDEX = (SELECT             MIN(T3.SEQ_IN_INDEX)        FROM            INFORMATION_SCHEMA.STATISTICS T3        WHERE                T2.TABLE_NAME = T3.TABLE_NAME                AND T2.TABLE_SCHEMA = T3.TABLE_SCHEMA                AND T2.INDEX_NAME = T3.INDEX_NAME)AND T1.TABLE_SCHEMA NOT IN ('MYSQL','PERFORMANCE_SCHEMA','INFORMATION_SCHEMA','SYS')AND T1.TABLE_ROWS >=100 ORDER BY RATE;

--查看锁阻塞

-- 查看锁的SQLSELECT     t3.trx_id waiting_trx_id,    t3.trx_mysql_thread_id waiting_thread,    t3.trx_query waiting_query,    t2.trx_id blocking_trx_id,    t2.trx_mysql_thread_id blocking_thread,    t2.trx_query blocking_queryFROM    information_schema.innodb_lock_waits t1,    information_schema.innodb_trx t2,    information_schema.innodb_trx t3WHERE    t1.blocking_trx_id = t2.trx_id        AND t1.requesting_trx_id = t3.trx_id;

--查询出哪些表不是InnoDB引擎的

SELECT     TABLE_SCHEMA,    TABLE_NAME,    TABLE_TYPE,    ENGINE,    CREATE_TIME,    UPDATE_TIME,    TABLE_COLLATIONFROM    INFORMATION_SCHEMA.TABLESWHERE    TABLE_SCHEMA NOT IN ('information_schema' , 'mysql', 'performance_schema', 'sys')        AND ENGINE <> 'InnoDB';

--生成修改存储引擎的语句

SELECT     -- TABLE_SCHEMA,    -- TABLE_NAME,    -- TABLE_TYPE,    -- ENGINE,    -- CREATE_TIME,    -- UPDATE_TIME,    -- TABLE_COLLATION,     CONCAT('alter table ', TABLE_SCHEMA,'.',TABLE_NAME, ' engine=InnoDB;') AS alter_sql  FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN       ('information_schema', 'mysql', 'performance_schema', 'sys')   AND ENGINE <> 'InnoDB';

--  查看指定数据库的表信息

SET @table_schema='employees';SELECT     table_name,    table_type,    engine,    table_rows,    avg_row_length,    data_length,    index_length,    table_collation,    create_timeFROM    information_schema.tablesWHERE    table_schema = @table_schemaORDER BY table_name;

-- 查看会话连接信息

SELECT     THREAD_ID,    name,    type,    PROCESSLIST_ID,    PROCESSLIST_USER AS user,    PROCESSLIST_HOST AS host,    PROCESSLIST_DB AS db,    PROCESSLIST_COMMAND AS cmd,    PROCESSLIST_TIME AS time,    PROCESSLIST_STATE AS state,    PROCESSLIST_INFO AS info,    CONNECTION_TYPE AS type,    THREAD_OS_ID AS os_idFROM    performance_schema.threadsWHERE    type = 'FOREGROUND'ORDER BY THREAD_ID;

-- CHARACTER_SETS 查看数据库支持的字符集

SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETSWHERE CHARACTER_SET_NAME LIKE 'utf%';SHOW CHARACTER SET LIKE 'utf%';

-- COLLATIONS  字符序

-- 用于指定数据集如何排序,以及字符串的比对规则SELECT * FROM INFORMATION_SCHEMA.COLLATIONSWHERE COLLATION_NAME LIKE 'utf%';SHOW COLLATION LIKE 'utf%';

-- 查看表结构定义信息

SELECT     table_name,    COLUMN_NAME,    ordinal_position,    DATA_TYPE,    IS_NULLABLE,    COLUMN_DEFAULT,    column_type,    column_key,    character_set_name,    collation_nameFROM    INFORMATION_SCHEMA.COLUMNSWHERE    table_name = 'employees'        AND table_schema = 'employees';

show columns from employees from employees;

desc employeees.employees;

-- 查看支持的引擎

SELECT *  FROM INFORMATION_SCHEMA.ENGINES;show ENGINES;

-- 查看数据库的数据文件信息

SELECT     FILE_ID,    FILE_NAME,    FILE_TYPE,    TABLESPACE_NAME,    FREE_EXTENTS,    TOTAL_EXTENTS,    ((TOTAL_EXTENTS - FREE_EXTENTS) * EXTENT_SIZE) / 1024 / 1024 AS MB_used,    EXTENT_SIZE,    INITIAL_SIZE,    MAXIMUM_SIZE,    AUTOEXTEND_SIZE,    DATA_FREE,    STATUS,    ENGINEFROM    INFORMATION_SCHEMA.FILES;

-- 查看指定表的约束

SELECT     constraint_schema,    table_name,    constraint_name,    column_name,    ordinal_position,    CONCAT(table_name,            '.',            column_name,            ' -> ',            referenced_table_name,            '.',            referenced_column_name) AS list_of_fksFROM    information_schema.KEY_COLUMN_USAGEWHERE    REFERENCED_TABLE_SCHEMA = 'employees'        AND REFERENCED_TABLE_NAME IS NOT NULLORDER BY TABLE_NAME , COLUMN_NAME;

-- 查看指定分区表信息

SELECT     TABLE_SCHEMA,    table_name,    partition_name,    subpartition_name sub_par,    partition_ordinal_position par_position,    partition_method method,    partition_expression expression,    partition_description description,    table_rowsFROM    information_schema.PARTITIONSWHERE    table_schema = 'test'        AND table_name = 't';

-- 查看支持的插件

SELECT  PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_TYPE,  PLUGIN_LIBRARY, PLUGIN_LICENSEFROM INFORMATION_SCHEMA.PLUGINS;SHOW PLUGINS;

-- 查看数据库连接信息

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;SHOW FULL PROCESSLIST;

-- 查看数据库中的存储过程、函数等

SELECT     ROUTINE_SCHEMA,    routine_name,    ROUTINE_TYPE,    data_type,    routine_body,    routine_definition,    routine_commentFROM    INFORMATION_SCHEMA.ROUTINESWHERE    ROUTINE_TYPE = 'PROCEDURE'AND ROUTINE_SCHEMA="employees";

-- 查看存在的数据库及字符集信息

SELECT     SCHEMA_NAME,    DEFAULT_CHARACTER_SET_NAME,    DEFAULT_COLLATION_NAMEFROM    INFORMATION_SCHEMA.SCHEMATA;SHOW DATABASES;

-- 查看索引信息

SELECT     table_schema,    table_name,    index_name,    COLUMN_NAME,    COLLATION,    CARDINALITY,    index_typeFROM    INFORMATION_SCHEMA.STATISTICSWHERE    table_name = 'employees'        AND table_schema = 'employees'; SHOW INDEX FROM employees FROM employees;

-- 查看数据库大小

SELECT     table_schema 'database',    CONCAT(ROUND(SUM(data_length + index_length) / (1024 * 1024),                    2),            'M') sizeFROM    information_schema.TABLESWHERE    ENGINE in ('MyISAM','InnoDB')GROUP BY table_schema;

-- 查看表大小  

SELECT 

CONCAT(table_schema, '.', table_name) table_name,
CONCAT(ROUND(data_length / (1024 * 1024), 2),
'M') data_length,
CONCAT(ROUND(index_length / (1024 * 1024), 2),
'M') index_length,
CONCAT(ROUND(ROUND(data_length + index_length) / (1024 * 1024),
2),
'M') total_size,
engine
FROM
information_schema.TABLES
WHERE
table_schema NOT IN ('information_schema' , 'performance_schema', 'sys', 'mysql')
ORDER BY data_length DESC;

转自:http://www.cnblogs.com/zhenxing/p/5340592.html

你可能感兴趣的文章
【DeepLearning】Exercise:Sparse Autoencoder
查看>>
Util应用程序框架公共操作类(八):Lambda表达式公共操作类(二)
查看>>
android 设置布局横屏竖屏
查看>>
ThreadLocal
查看>>
FormsAuthentication详解
查看>>
Canvas createRadialGradient API
查看>>
什么是 Delta 文件
查看>>
windows下一个,OracleServiceXXX和Oracle 关系实例
查看>>
【LeetCode】241. Different Ways to Add Parentheses
查看>>
风清杨之Oracle的安装与说明
查看>>
thinkphp查询
查看>>
Eclipse使用技巧收集
查看>>
iOS开发-Protocol协议及委托代理(Delegate)传值
查看>>
【BZOJ】1105: [POI2007]石头花园SKA
查看>>
Import语句
查看>>
thinking in object pool
查看>>
MapReduce原理与设计思想
查看>>
极速调整页面的个别不兼容
查看>>
浅谈WebService的调用<转>
查看>>
Theano学习笔记(三)——图结构
查看>>