如下sql 用于統計mysql資料庫非系統db的全部表/索引資訊
(包括：資料庫、表名、表注釋、表行數、表大小、索引名、索引字段、字段注釋、基數、選擇性比、索引類型..)
<b>sql：</b>
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
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
t.table_rows
end );
<b>官網注釋</b>
information_schema 表
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.
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.
<b>表/視圖 字段介紹</b>
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> 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 | | | |
【源于本人筆記】 若有書寫錯誤，表達錯誤，請指正...
![SQL優化SQL語句優化的目的[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)