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>
|
相關文章
- SQL Server 查詢表註釋和欄位SQLServer
- Oracle 計算欄位選擇性 判別列的索引潛力Oracle索引
- MySQL進階【五】—— MySQL查詢優化器是如何選擇索引的MySql優化索引
- [20180408]那些函式索引適合欄位的查詢.txt函式索引
- PostgreSQL資料庫多列複合索引的欄位順序選擇原理SQL資料庫索引
- MySql 查詢某一天日期格式欄位走索引問題MySql索引
- 表資料量影響MySQL索引選擇MySql索引
- 唯一索引和普通索引的選擇索引
- 【TUNE_ORACLE】列出走了低選擇性索引的SQL參考Oracle索引SQL
- MySQL null值欄位是否使用索引的總結MySqlNull索引
- MySQL索引與查詢優化MySql索引優化
- 這個大表走索引欄位查詢的 SQL 怎麼就成全掃描了，我TM人傻了索引SQL
- oracle 使用sql查詢表註釋和列註釋及資料型別等OracleSQL資料型別
- MySQL索引選擇及規則整理MySql索引
- MySQL欄位新增註釋，但不改變欄位的型別MySql型別
- PG裡常見的欄位有索引但未使用索引的原因索引
- MySQL 覆蓋索引、回表查詢MySql索引
- [20181020]lob欄位的索引段.txt索引
- MySQL資料庫索引選擇使用B+樹MySql資料庫索引
- 談談MYSQL索引是如何提高查詢效率的MySql索引
- 《MySQL慢查詢優化》之SQL語句及索引優化MySql優化索引
- oracle複合索引介紹(多欄位索引)Oracle索引
- MySQL 選錯索引的原因？MySql索引
- 如何選擇普通索引和唯一索引《死磕MySQL系列 五》索引MySql
- 多表關聯查詢中，關聯欄位都應該建立索引嗎？索引
- 資料庫索引選擇策略資料庫索引
- pandas索引和選擇資料索引
- mysql sql同一個欄位多個行轉成一個欄位查詢MySql
- OGG複製程式延遲高，優化方法二(存在索引),SQL選擇不好的索引優化索引SQL
- MySQL 學習之索引篇和查詢MySql索引
- MySQL 索引及查詢優化總結MySql索引優化
- TableStore多元索引，大資料查詢的利器索引大資料
- SQL Server中獲取資料庫名、表名、欄位名和欄位註釋的SQL語句SQLServer資料庫
- Mysql建表、索引、函式、查詢使用中的坑！！！MySql索引函式
- AppBoxFuture: 二級索引及索引掃描查詢資料APP索引
- Laravel 對於 Mysql 欄位string型別查詢，當使用數字對這個欄位進行查詢，PHP弱型別語言導致索引失效LaravelMySql型別PHP索引
- 理解索引（中）：MySQL查詢過程和高階查詢索引MySql
- MySQL如何檢視新增修改表以及欄位註釋資訊MySql
- MySQL：查詢欄位數量多少對查詢效率的影響MySql