MySQL 查詢索引的選擇性、索引欄位、註釋等基本資訊的SQL

maohaiqing0304發表於2015-10-30


標題: MySQL 查詢索引的選擇性、索引欄位、註釋等基本資訊的SQL

作者:lōττéry©版權所有[文章允許轉載,但必須以連結方式註明源地址,否則追究法律責任.]



如下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部落格 ” ,連結:http://blog.itpub.net/28602568/viewspace-1819474/,如需轉載,請註明出處,否則將追究法律責任。

相關文章