MySQL 查询索引的选择性、索引字段、注释等基本信息的SQL

链接：http://blog.itpub.net/28602568/viewspace-1819474/

标题： 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   

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> 

【源于本人笔记】 若有书写错误，表达错误，请指正... 

时间： 2024-10-24 19:22:28

MySQL 查询索引的选择性、索引字段、注释等基本信息的SQL的相关文章

Mysql查询结果中加入序号字段

只要下面简单的一句话即可在mysql中检索结果中加入序号. SELECT @ROW := @ROW + 1 AS ROW, t.*  FROM group_post t, (SELECT @ROW := 0) r    Mysql中@ROW默认从0开始统计. 上图红框就是我们生成了有多少条记录生成多少个序号了,

MySQL表设计优化与索引

  设计好MySql的索引可以让你的数据库飞起来,大大的提高数据库效率.设计MySql索引的时候有一下几点注意: 1.创建索引 对于查询占主要的应用来说,索引显得尤为重要.很多时候性能问题很简单的就是因为我们忘了添加索引而造成的,或者说没有添加更为有效的索引导致.如果不加 索引的话,那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描,如果一张表的数据量很大而符合条件的结果又很少,那么不加索引会引起致命的性能下 降.但是也不是什么情况都非得建索引不可,比如性别可能就只有两个值,建索引不仅没什么

详解MySQL数据库索引的选择性

在MySQL中,对于索引的使用并是一直都采用正确的决定. 简单表的示例: create TABLE `r2` ( ID` int(11) DEFAULT NULL, ID1` int(11) DEFAULT NULL, CNAME` varchar(32) DEFAULT NULL, KEY `ID1` (`ID1`) ) ENGINE=MyISAM DEFAULT charSET=latin1 select count(*) FROM r2; 250001 (V1) select count(

MySQL 第六篇：索引与子查询

我把MySQL的内容整理成9篇博客,学完这9篇博客虽不能说能成为大神,但是应付一般中小企业的开发已经足够了,有疑问或建议的欢迎留言讨论. 子查询 子查询,从原有的查询语句中 嵌入新的查询 来得到我们想要的结果,也可称为嵌套查询. 一.where 型 1.查询课程名为"Java"的学生信息 -- 使用关联查询实现 SELECT s.* FROM students s, class c WHERE s.class_id = c.class_id AND c.class_name = 'JA

mysql性能优化-慢查询分析、优化索引和配置

目录 一.优化概述 二.查询与索引优化分析 1性能瓶颈定位 Show命令 慢查询日志 explain分析查询 profiling分析查询   2索引及查询优化 三.配置优化 1)      max_connections 2)      back_log 3)      interactive_timeout 4)      key_buffer_size 5)      query_cache_size 6)      record_buffer_size 7)      read_rnd_b

MySQL中IN子查询会导致无法使用索引

原文:MySQL中IN子查询会导致无法使用索引   今天看到一个博客园的一篇关于MySQL的IN子查询优化的案例,一开始感觉有点半信半疑(如果是换做在SQL Server中,这种情况是绝对不可能的,后面会做一个简单的测试.)随后动手按照他说的做了一个表来测试验证,发现MySQL的IN子查询做的不好,确实会导致无法使用索引的情况(IN子查询无法使用所以,场景是MySQL,截止的版本是5.7.18) MySQL的测试环境 测试表如下 create table test_table2 ( id int

MySQL索引操作命令（创建索引、重建索引、查询索引、删除索引）

1.创建索引 索引的创建可以在CREATE TABLE语句中进行,也可以单独用CREATE INDEX或ALTER TABLE来给表增加索引.以下命令语句分别展示了如何创建主键索引(PRIMARY KEY),联合索引(UNIQUE)和普通索引(INDEX)的方法. mysql>ALTER TABLE `table_name` ADD INDEX `index_name` (column list); mysql>ALTER TABLE `table_name` ADD UNIQUE `inde

索引-mysql查询问题，优化性能，提高查询时间

问题描述 mysql查询问题,优化性能,提高查询时间 我有一个数据采集表,一个月一张表,每天不停新增数据,一个月百万条,现在查询一页数据在3s左右,发现sql 中order by消耗不少时间,建立索引可方便查询,可新增数据的效率肯定受影响,怎么平衡 经验不足,现在只能想到这里,故而前来请教 解决方案 通过时间分区会不会好点呢 解决方案二: order by是通过什么来排序的呢 如果表中的id是自增的,order by的时候,你根据id来排序,主键会被默认创建索引,这样应该能快些.

mysql 查询数据量过大时，索引失效,怎么强制使用索引

问题描述 mysql 查询数据量过大时,索引失效,怎么强制使用索引 解决方案 MYSQL强制使用索引和禁止使用索引mysql 强制索引mysql强制索引 解决方案二: 你的sql语句是否有合法的应用到索引,是不是导致索引失效