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

如下sql 用于统计mysql数据库非系统db的全部表/索引信息  (包括：数据库、表名、表注释、表行数、表大小、索引名、索引字段、字段注释、基数、选择性比、索引类型..)      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,
                        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 );
http://dev.mysql.com/doc/refman/5.6/en/information-schema.html 
 21.29 INFORMATION_SCHEMA Tables for InnoDB    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