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 表
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.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.
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>
|
相關文章
- MySQL 查詢索引的選擇性、索引欄位、註釋等基本資訊的SQLMySql索引
- 查詢oracle表的資訊（表，欄位，約束，索引）Oracle索引
- MySQL字首索引和索引選擇性MySql索引
- mysql聯合索引的選擇性MySql索引
- SQL Server 查詢表註釋和欄位SQLServer
- Oracle 計算欄位選擇性 判別列的索引潛力Oracle索引
- 查詢外來鍵約束、子表欄位等資訊的SQLSQL
- 查詢表上的索引及對應的欄位索引
- MySQL進階【五】—— MySQL查詢優化器是如何選擇索引的MySql優化索引
- mysql 用sql語句查詢一個表中的所有欄位型別、註釋MySql型別
- 分割槽表全域性索引與本地索引的選擇索引
- 查詢索引 常用SQL索引SQL
- 查詢某個表的索引資訊索引
- oracle查詢表資訊（索引，外來鍵，列等）Oracle索引
- 【索引】使用索引分析快速得到索引的基本資訊索引
- MySql 查詢某一天日期格式欄位走索引問題MySql索引
- mysql查詢索引結構MySql索引
- 根據表查詢索引資訊索引
- 從Sql server 2008獲取表欄位屬性資訊，註釋資訊SQLServer
- MySQL null值欄位是否使用索引的總結MySqlNull索引
- PostgreSQL資料庫多列複合索引的欄位順序選擇原理SQL資料庫索引
- 【TUNE_ORACLE】列出走了低選擇性索引的SQL參考Oracle索引SQL
- [Mysql 查詢語句]——查詢欄位MySql
- Mysql表關聯欄位未建索引導致查詢慢,優化後查詢效率顯著提升MySql索引優化
- 索引的選擇原則索引
- MySQL索引與查詢優化MySql索引優化
- 【索引】Oracle查詢指定索引提高查詢效率索引Oracle
- MySQL欄位新增註釋，但不改變欄位的型別MySql型別
- 這個大表走索引欄位查詢的 SQL 怎麼就成全掃描了，我TM人傻了索引SQL
- MySQL索引選擇及規則整理MySql索引
- 在標準MySQL 5.6上查詢沒有使用過的索引的SQLMySql索引
- 查詢表或索引增長的歷史資訊索引
- 【索引】oracle查詢使用索引和不使用索引的比較索引Oracle
- [20180408]那些函式索引適合欄位的查詢.txt函式索引
- sqlserver新增查詢 表、欄位註釋，組合查詢所有的使用者、表名、表註釋SQLServer
- 【Oracle】修改indexed 欄位是否影響索引的有效性OracleIndex索引
- 記一次 T-SQL 查詢優化 索引的重要性SQL優化索引
- Oracle 查詢欄位詳細資訊Oracle