MySQL 查詢索引的選擇性、索引欄位、註釋等基本資訊的SQL
maohaiqing0304發表於2015-10-30
如下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 表
http://dev.mysql.com/doc/refman/5.6/en/information-schema.html
21.29 INFORMATION_SCHEMA Tables for InnoDB
21.29 INFORMATION_SCHEMA Tables for InnoDB
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.
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.
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.
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>
|
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28602568/viewspace-1819474/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL查詢索引的選擇性、索引欄位、註釋等基本資訊的SQL2016-05-24MySql索引
- 查詢oracle表的資訊（表，欄位，約束，索引）2014-02-24Oracle索引
- MySQL字首索引和索引選擇性2017-09-07MySql索引
- mysql聯合索引的選擇性2017-01-18MySql索引
- SQL Server 查詢表註釋和欄位2020-12-07SQLServer
- Oracle 計算欄位選擇性 判別列的索引潛力2019-04-11Oracle索引
- 查詢外來鍵約束、子表欄位等資訊的SQL2012-06-13SQL
- 查詢表上的索引及對應的欄位2011-07-16索引
- MySQL進階【五】—— MySQL查詢優化器是如何選擇索引的2020-09-27MySql優化索引
- mysql 用sql語句查詢一個表中的所有欄位型別、註釋2016-02-23MySql型別
- 分割槽表全域性索引與本地索引的選擇2016-05-04索引
- 查詢索引 常用SQL2015-04-24索引SQL
- 查詢某個表的索引資訊2015-04-27索引
- oracle查詢表資訊（索引，外來鍵，列等）2015-03-24Oracle索引
- 【索引】使用索引分析快速得到索引的基本資訊2009-11-27索引
- MySql 查詢某一天日期格式欄位走索引問題2024-03-28MySql索引
- mysql查詢索引結構2014-12-04MySql索引
- 根據表查詢索引資訊2017-01-20索引
- 從Sql server 2008獲取表欄位屬性資訊，註釋資訊2010-01-11SQLServer
- MySQL null值欄位是否使用索引的總結2018-11-30MySqlNull索引
- PostgreSQL資料庫多列複合索引的欄位順序選擇原理2018-04-18SQL資料庫索引
- 【TUNE_ORACLE】列出走了低選擇性索引的SQL參考2021-08-13Oracle索引SQL
- [Mysql 查詢語句]——查詢欄位2017-03-18MySql
- Mysql表關聯欄位未建索引導致查詢慢,優化後查詢效率顯著提升2017-09-06MySql索引優化
- 索引的選擇原則2016-08-19索引
- MySQL索引與查詢優化2019-04-13MySql索引優化
- 【索引】Oracle查詢指定索引提高查詢效率2012-04-25索引Oracle
- MySQL欄位新增註釋，但不改變欄位的型別2020-10-19MySql型別
- 這個大表走索引欄位查詢的 SQL 怎麼就成全掃描了，我TM人傻了2021-08-07索引SQL
- MySQL索引選擇及規則整理2018-09-13MySql索引
- 在標準MySQL 5.6上查詢沒有使用過的索引的SQL2015-01-15MySql索引
- 查詢表或索引增長的歷史資訊2012-04-07索引
- 【索引】oracle查詢使用索引和不使用索引的比較2012-04-25索引Oracle
- [20180408]那些函式索引適合欄位的查詢.txt2018-04-08函式索引
- sqlserver新增查詢 表、欄位註釋，組合查詢所有的使用者、表名、表註釋2017-12-04SQLServer
- 【Oracle】修改indexed 欄位是否影響索引的有效性2012-05-10OracleIndex索引
- 記一次 T-SQL 查詢優化 索引的重要性2015-10-27SQL優化索引
- Oracle 查詢欄位詳細資訊2011-06-20Oracle