ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > MySQL > MySQL 查询索引的选择性、索引字段、注释等基本信息的SQL

MySQL 查询索引的选择性、索引字段、注释等基本信息的SQL

原创 MySQL 作者：maohaiqing0304 时间：2015-10-30 23:51:39 0 删除 编辑

标题： MySQL 查询索引的选择性、索引字段、注释等基本信息的SQL

作者：ｌōττéｒｙ©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]


如下sql 用于统计mysql数据库非系统db的全部表/索引信息 
(包括：数据库、表名、表注释、表行数、表大小、索引名、索引字段、字段注释、基数、选择性比、索引类型..)


SQL：
     SELECT t.table_schema DB_NAME,
           t.table_name,
           t.TABLE_COMMENT 表注释,
           t.TABLE_ROWS 表行数,
           round (sum(DATA_LENGTH / 1024 / 1024 ), 2 ) 表大小MB,
           -- st.table_id,
           -- si.index_id,
           s.index_schema,
           s.index_name,
           s.column_name,
           c.COLUMN_COMMENT 列注释,
           s.Cardinality,
           concat (round (( CASE
                          WHEN s.Cardinality = 0 THEN
                           1
                          ELSE
                           s.Cardinality
                        END ) / (CASE
                          WHEN t.TABLE_ROWS = 0 THEN
                           1
                          ELSE
                           t.TABLE_ROWS
                        END ) * 100 ,
                        2 ),
                  "%") 选择性,
           s.index_type
      FROM information_schema.TABLES t
      JOIN information_schema.INNODB_SYS_TABLESTATS st
        ON concat (t.table_schema, "/", t.table_name) = st.NAME
      JOIN information_schema.INNODB_SYS_INDEXES si
        ON si.table_id = st.table_id
      JOIN information_schema.STATISTICS s
        ON si.NAME = s.index_name
       AND s.table_name = t.table_name
       AND t.table_schema = s.table_schema
      join information_schema.COLUMNS c
        on c.COLUMN_NAME = s.column_name
       and c.table_name = t.table_name
       and c.table_schema = s.table_schema
       and t.table_schema not in ( 'test' ,
                                  'mysql' ,
                                  'zabbix' ,
                                  'information_schema' ,
                                  'performance_schema' )
     GROUP BY t.table_schema,
              t.table_name,
              t.TABLE_COMMENT,
              t.TABLE_ROWS,
              s.index_schema,
              s.index_name,
              s.column_name,
              c.column_COMMENT,
              s.Cardinality,
              s.index_type
     ORDER BY ( CASE
                WHEN s.Cardinality = 0 THEN
                 1
                ELSE
                 s.Cardinality
              END ) / (CASE
                WHEN t.TABLE_ROWS = 0 THEN
                 1
                ELSE
                 t.TABLE_ROWS
              END );


官网注释
information_schema 表

information_schema.TABLES     http://dev.mysql.com/doc/refman/5.6/en/tables-table.html  

information_schema.INNODB_SYS_TABLESTATS http://dev.mysql.com/doc/refman/5.6/en/innodb-sys-tablestats-table.html   
The INNODB_SYS_TABLESTATS provides a view of low-level status information about InnoDB tables. 
This data is used by the MySQL optimizer to calculate which index to use when querying an InnoDB table. 
This information is derived from in-memory data structures rather than corresponding to data stored on disk. 
There is no corresponding internal InnoDB system table.

information_schema.INNODB_SYS_INDEXES   http://dev.mysql.com/doc/refman/5.6/en/innodb-sys-indexes-table.html   
The INNODB_SYS_INDEXES table provides metadata about InnoDB indexes, equivalent to the information in the internal SYS_INDEXES table in the InnoDB data dictionary.

information_schema.STATISTICS  http://dev.mysql.com/doc/refman/5.6/en/statistics-table.html  
The STATISTICS table provides information about table indexes. 

information_schema.COLUMNS      http://dev.mysql.com/doc/refman/5.6/en/columns-table.html   
The COLUMNS table provides information about columns in tables.



表/视图 字段介绍
mysql> desc STATISTICS;
+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512)  | NO   |     |         |       |
| TABLE_SCHEMA  | varchar(64)   | NO   |     |         |       |
| TABLE_NAME    | varchar(64)   | NO   |     |         |       |
| NON_UNIQUE    | bigint(1)     | NO   |     | 0       |       |
| INDEX_SCHEMA  | varchar(64)   | NO   |     |         |       |
| INDEX_NAME    | varchar(64)   | NO   |     |         |       |
| SEQ_IN_INDEX  | bigint(2)     | NO   |     | 0       |       |
| COLUMN_NAME   | varchar(64)   | NO   |     |         |       |
| COLLATION     | varchar(1)    | YES  |     | NULL    |       |
| CARDINALITY   | bigint(21)    | YES  |     | NULL    |       |
| SUB_PART      | bigint(3)     | YES  |     | NULL    |       |
| PACKED        | varchar(10)   | YES  |     | NULL    |       |
| NULLABLE      | varchar(3)    | NO   |     |         |       |
| INDEX_TYPE    | varchar(16)   | NO   |     |         |       |
| COMMENT       | varchar(16)   | YES  |     | NULL    |       |
| INDEX_COMMENT | varchar(1024) | NO   |     |         |       |
+---------------+---------------+------+-----+---------+-------+

mysql> 		mysql> desc columns;
+--------------------------+---------------------+------+-----+---------+-------+
| Field                    | Type                | Null | Key | Default | Extra |
+--------------------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG            | varchar(512)        | NO   |     |         |       |
| TABLE_SCHEMA             | varchar(64)         | NO   |     |         |       |
| TABLE_NAME               | varchar(64)         | NO   |     |         |       |
| COLUMN_NAME              | varchar(64)         | NO   |     |         |       |
| ORDINAL_POSITION         | bigint(21) unsigned | NO   |     | 0       |       |
| COLUMN_DEFAULT           | longtext            | YES  |     | NULL    |       |
| IS_NULLABLE              | varchar(3)          | NO   |     |         |       |
| DATA_TYPE                | varchar(64)         | NO   |     |         |       |
| CHARACTER_MAXIMUM_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |
| CHARACTER_OCTET_LENGTH   | bigint(21) unsigned | YES  |     | NULL    |       |
| NUMERIC_PRECISION        | bigint(21) unsigned | YES  |     | NULL    |       |
| NUMERIC_SCALE            | bigint(21) unsigned | YES  |     | NULL    |       |
| DATETIME_PRECISION       | bigint(21) unsigned | YES  |     | NULL    |       |
| CHARACTER_SET_NAME       | varchar(32)         | YES  |     | NULL    |       |
| COLLATION_NAME           | varchar(32)         | YES  |     | NULL    |       |
| COLUMN_TYPE              | longtext            | NO   |     | NULL    |       |
| COLUMN_KEY               | varchar(3)          | NO   |     |         |       |
| EXTRA                    | varchar(30)         | NO   |     |         |       |
| PRIVILEGES               | varchar(80)         | NO   |     |         |       |
| COLUMN_COMMENT           | varchar(1024)       | NO   |     |         |       |
+--------------------------+---------------------+------+-----+---------+-------+

mysql>  
mysql> desc innodb_sys_indexes  ;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| INDEX_ID | bigint(21) unsigned | NO   |     | 0       |       |
| NAME     | varchar(193)        | NO   |     |         |       |
| TABLE_ID | bigint(21) unsigned | NO   |     | 0       |       |
| TYPE     | int(11)             | NO   |     | 0       |       |
| N_FIELDS | int(11)             | NO   |     | 0       |       |
| PAGE_NO  | int(11)             | NO   |     | 0       |       |
| SPACE    | int(11)             | NO   |     | 0       |       |
+----------+---------------------+------+-----+---------+-------+

mysql>  		mysql> desc innodb_sys_tablestats ;
+-------------------+---------------------+------+-----+---------+-------+
| Field             | Type                | Null | Key | Default | Extra |
+-------------------+---------------------+------+-----+---------+-------+
| TABLE_ID          | bigint(21) unsigned | NO   |     | 0       |       |
| NAME              | varchar(193)        | NO   |     |         |       |
| STATS_INITIALIZED | varchar(193)        | NO   |     |         |       |
| NUM_ROWS          | bigint(21) unsigned | NO   |     | 0       |       |
| CLUST_INDEX_SIZE  | bigint(21) unsigned | NO   |     | 0       |       |
| OTHER_INDEX_SIZE  | bigint(21) unsigned | NO   |     | 0       |       |
| MODIFIED_COUNTER  | bigint(21) unsigned | NO   |     | 0       |       |
| AUTOINC           | bigint(21) unsigned | NO   |     | 0       |       |
| REF_COUNT         | int(11)             | NO   |     | 0       |       |
+-------------------+---------------------+------+-----+---------+-------+
mysql> 
mysql> desc tables;
+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(512)        | NO   |     |         |       |
| TABLE_SCHEMA    | varchar(64)         | NO   |     |         |       |
| TABLE_NAME      | varchar(64)         | NO   |     |         |       |
| TABLE_TYPE      | varchar(64)         | NO   |     |         |       |
| ENGINE          | varchar(64)         | YES  |     | NULL    |       |
| VERSION         | bigint(21) unsigned | YES  |     | NULL    |       |
| ROW_FORMAT      | varchar(10)         | YES  |     | NULL    |       |
| TABLE_ROWS      | bigint(21) unsigned | YES  |     | NULL    |       |
| AVG_ROW_LENGTH  | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_LENGTH     | bigint(21) unsigned | YES  |     | NULL    |       |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |
| INDEX_LENGTH    | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_FREE       | bigint(21) unsigned | YES  |     | NULL    |       |
| AUTO_INCREMENT  | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_TIME     | datetime            | YES  |     | NULL    |       |
| UPDATE_TIME     | datetime            | YES  |     | NULL    |       |
| CHECK_TIME      | datetime            | YES  |     | NULL    |       |
| TABLE_COLLATION | varchar(32)         | YES  |     | NULL    |       |
| CHECKSUM        | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_OPTIONS  | varchar(255)        | YES  |     | NULL    |       |
| TABLE_COMMENT   | varchar(2048)       | NO   |     |         |       |
+-----------------+---------------------+------+-----+---------+-------+
mysql> 

【源于本人笔记】 若有书写错误，表达错误，请指正... 

此条目发表在   SQL、SQL优化篇  分类目录。将固定连接加入收藏夹。

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/28602568/viewspace-1819474/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： Oracle 如何选择update、merge into
下一篇： MySQL 如何快速复制用户权限到其他环境
请登录后发表评论 登录
全部评论
maohaiqing0304
专家姓名：毛海晴
.

注册时间：2013-03-13

  • 博文量
    117
  • 访问量
    2626249

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593