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

最新推荐文章于 2022-12-28 22:52:22 发布
最新推荐文章于 2022-12-28 22:52:22 发布
阅读量179 收藏
点赞数
文章标签： 数据库
原文链接：https://yq.aliyun.com/articles/52233
版权

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

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>  

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

weixin_34228617
关注
sql mysql创建 视图索引_SQL语句-创建索引
weixin_35801556的博客
02-04 1318
语法：CREATE [索引类型] INDEX 索引名称ON 表名(列名)WITH FILLFACTOR = 填充因子值0~100GOUSE 库名GOIF EXISTS (SELECT * FROM SYSINDEXES WHERE NAME='IX_TEST_TNAME')--检测是否已经存在IX_TEST_TNAME索引DROP INDEX TEST.IX_TEST_TNAME--如果存...
PHP获取mysql数据表的字段名称和详细信息的方法
12-19
在PHP中，获取MySQL数据表的字段名称和详细信息是常见的需求，这有助于开发者了解数据库结构，以便进行数据...记得在处理SQL查询时始终确保数据的安全性，避免SQL注入攻击，并根据实际情况选择合适的数据库连接方式。
mysql前缀索引的索引选择性
qq_38852289的博客
08-09 538
前言：前缀索引一般是用在字符串型（varchar）的数据列创建索引时考虑。 一. 基础概念  在mysql中建立前缀索引的意义在于相对于整列建立索引，前缀索引仅仅是选择该列的部分字符作为索引，减少索引的字符可以节约索引空间，从而提高索引效率，但这样也会降低索引的选择性 。 关于索引的选择性，它是指不重复的索引值（也称为基数cardinality)和数据表的记录总数的比值，范围从1/(数据表记录...
mysql选择性_Mysql索引的选择性
weixin_39830020的博客
01-28 271
对于索引的使用，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=latin1SE...
mysql 索引选择性_由浅入深讲解MySQL数据库索引的选择性
weixin_39905816的博客
01-19 184
数据库索引就好比是一本书的目录部分，便于大家查找数据库中数据，方便快捷，给数据库管理员的工作带来了很多的便利。在MySQL数据库中，对于索引的使用并是一直都采用正确的决定。简单表的示例：以下是代码片段： CREATE TABLE `r2` (ID` int(11) DEFAULT NULL,ID1` int(11) DEFAULT NULL,CNAME` varchar(32) DEFAUL...
Mysql 索引基数与选择性
papaya的博客
12-28 1362
索引的可选择性好与坏，和索引基数关系非常密切。基数值越高，索引的可选择性越好；相反，基数越低，索引的可选择性越差。优化器优先使用的索引一般选择性都不差，除非没得选，才会走选择性稍差点的索引或者走全表扫描。MYSQL 如何使用索引？之所周知，MYSQL 优化器使用的是基于成本的模型。而衡量成本的主要指标就是一个查询需要扫描多少行。如果表没有索引统计信息，或者索引统计信息不准确，优化器就很有可能做出错误的决定。统计信息不准确的问题可以通过 ANALYZE TABLE 来重新生成统计信息解决。
高性能的Mysql读书笔记系列之五（创建高性能的索引）
jdk_wangtaida的博客
09-21 1936
前言： 正文： 总结：
MySQL创建全文索引分享
12-16
此外，合理设计索引字段，避免频繁更新已建立全文索引的字段，因为这会影响索引的效率。 除了全文索引，MySQL还提供了其他类型的索引，如主键索引（唯一且不允许为空）、普通索引（非唯一）和唯一索引（不允许重复...
mysql中向表中某字段追加字符串的方法
12-15
- **创建带解释的表和字段注释**：在MySQL中，可以使用`COMMENT`关键字为表和字段添加注释，以便于理解和维护，如`ALTER TABLE `表名` COMMENT '表描述';`，`ALTER TABLE `表名` MODIFY COLUMN 新字段名 数据类型 ...
建表规范丶SQL规范丶索引规范-word文档
最新发布
03-28
通过遵循以上建表规范、SQL规范及索引规范，可以帮助数据库管理员和开发人员更高效地进行数据库设计、SQL语句编写及索引优化，从而提升数据库的整体性能和可维护性。这些规范不仅有助于确保数据库的稳定运行，还能...
java技术--表结构及索引查看命令
qq591009234的博客
06-04 1103
有时候创建一张表以及一些索引，需要查看表结构字段或者索引是否有效，可以通过一些命令实现！ 1.针对于表结构字段查看（2种方式） 1.1.可以使用：desc命令 语法格式：desc table_name 举例：加入有一张user表，需要查看user表字段和类型 sql命令： desc user 1.2. 可以使用 show columns命令 sq...
mysql查看字段选择性_mysql查询优化2：列选择原则
weixin_35647812的博客
02-04 368
1、字段类型选择优先级：整型 > date,time > enum ,char >varchar >blob,text2、列的特点分析：整型：定长，没有国家/地区之分，没有字符集的差异如：tinyint 1,2,3,4,5 char(1) a,b,c,d,e从空间上都是占1个字节，但是order by排序时，tinyint快，原因时是：因为char类型需要考虑字符集和校...
mysql 索引注释_Mysql系列（六）—— MySQL索引介绍
weixin_31342203的博客
02-07 521
前言索引种类索引维护如何使用索引一.索引索引种类MySQL中索引主要包含以下几种：普通索引唯一索引主键索引联合索引全文索引二.索引维护在简述了索引的类型后，再来了解下如何维护索引。创建语法格式：CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name[index_type]ON tbl_name (index_col_name,...)[index_opt...
sqlserver 2008 查看表描述，和表结构
weixin_33686714的博客
12-23 361
sp_help sys_user sp_columns   sys_user --表结构 SELECT CASE WHEN col.colorder = 1 THEN obj.name ELSE '' END AS 表名, col.colorder AS 序号 , ...
数据库索引的选择性
晨曦的专栏
12-05 3472
为数据表创建索引是提升数据库查询性能的一个有效方式，如何去创建索引，我们应该在哪些列上创建索引呢？在这之前我们有一个必须要理解的概念：索引的选择性。 索引的选择性是指索引列中不同值的数目与表中记录总数的比值。举个例子：假设一个表中有100条数据，该列的值中有80个不同的值，那么该列索引的选择性就是0.8，对于索引的选择性，值越高那么该列索引的效率也就越高。         如何判断索引的选择性
linux mysql 字符集 索引，主键等
qq_39109226的博客
12-02 282
1.字符集mysql默认使用字符集 Latin1 * show variables like “%char%”; //查看默认字符编码 * set character_set_database=utf8; //临时修改字符编码 * vim /etc/my.cnf character_set_server=utf8 //永久修改字符编码 * alter table 表名 character set=utf8; //修改表的字符编码 * 现在默认使用utf8mb4格式，因为utf8有bug
mysql中的各种索引大总结
风团团
09-12 6573
文章目录为啥不用二叉搜索树？为啥不用平衡二叉（avl）树？为啥不用b-树？为啥用b+树？（重点）索引聚簇索引聚簇索引的缺点 大家都知道mysql索引底层使用的是b+树的数据结构，树有这么多种，那为啥就选择b+树呢？那就从为啥使用b+树开始，到分析其原理的思路一步步分析吧。 为啥不用二叉搜索树？ 定义： 1：非空左子树的所有键值小于其根结点的键值。 2：非空右子树的所有键值大于其根结点的键值。 3：左、右子树都是二叉搜索树。就是左节点<根节点<右节点。 看下图树太高了。查询效率太低，故不推荐。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值