暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 【生产篇】_MySQL环境下如何查看基于表的索引定义

【生产篇】_MySQL环境下如何查看基于表的索引定义

一森咖记 2019-11-12
657
【此为"一森咖记"公众号——第72篇文章】

本文预计阅读10分钟

【引言】
今天中午项目组来一需求，欲在MySQL环境的某张表下创建几个BTREE索引。要创建索引，首先需要了解基表的表结构，以及已经包含的索引。Oracle的表结构大家都很熟悉，但MySQL表结构和已创建索引的查看怎么操作，本文将一一讲述。
 
文章大纲
1. Oracle如何查看表结构和索引
2. MySQL如何查看表结构和索引
3. 结语

一、Oracle如何查看表结构和索引

先来回顾下Oracle是如何查看表结构及表索引
1. 首先查看oracle数据库的单个表结构
使用Oracle的package包进行查看，其语法如下：
DBMS_METADATA.GET_DDL (
object_type     IN VARCHAR2,
name            IN VARCHAR2,
schema          IN VARCHAR2 DEFAULT NULL,
version         IN VARCHAR2 DEFAULT 'COMPATIBLE',
model           IN VARCHAR2 DEFAULT 'ORACLE',
transform       IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;
 
官方示例如下：
Example: Fetch the DDL for all Complete Tables in the Current Schema, Filter Out Nested Tables and Overflow Segments
This example fetches the DDL for all "complete" tables in the current schema, filtering out nested tables and overflow segments. The example uses SET_TRANSFORM_PARAM (with the handle value = DBMS_METADATA.SESSION_TRANSFORM meaning "for the current session") to specify that storage clauses are not to be returned in the SQL DDL. Afterwards, the example resets the session-level parameters to their defaults.
 
To generate complete, uninterrupted output, set the PAGESIZE to 0 and set LONG to some large number, as shown, before executing your query.
 
SET LONG 2000000
SET PAGESIZE 0
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
     FROM USER_ALL_TABLES u
     WHERE u.nested='NO'
     AND (u.iot_type is null or u.iot_type='IOT');
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT');
 
这里我们使用简单操作，命令如下
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE',TABLE_NAME,’SCHEMA’) FROM DUAL;
 
注意：
参数'TABLE'是要查询的对象，因要查的是表结构，所以是'TABLE'；
参数TABLE_NAME是要查的表名，注意表名必须大写；
参数’SCHEMA’为表所属的属主，也需要大写
 
注意：
执行完上面这条语句你可能只是看到整个建表语句的一部分；要生成完整的、不间断的输出，在执行查询之前，将PAGESIZE设置为0并将LONG设置为较大的数字，如下所示。


    SQL> SET LONG 2000000
    SQL> SET PAGESIZE 0
    SQL> SELECT DBMS_METADATA.GET_DDL\
    ('TABLE',TABLE_NAME,’SCHEMA’) \
    FROM DUAL;


    2. 查看Oracle的表所包含的索引
    如使用用户账号登陆，则查询user_indexes
      SQL> select * from user_indexes where table_name=upper('table_name');
      如果是使用sys账号登陆，则查询dba_indexes


        SQL> select * from user_indexes where table_name=upper('table_name');
        注意：upper函数将实现小写转换成大写。


         
        3. 根据上述索引名查看索引基于的字段/属性列
        SQL> select * from dba_ind_columns where index_name = 'INDEXS_NAME';
        SQL> select * from dba_ind_columns where index_name = upper('index_name');
        索引名大写或者通过upper函数转换成大写。
         
        至此，Oracle环境下如何查看表结构和其所基于的索引介绍完毕。


        接下来，再看MySQL环境下如何查看表结构和索引。


        二、MySQL环境下如何查看表结构和索引

        MySQL环境下，你会发现查询语句简单的令人发指；简单如下：
        mysql> use schema_name;
        --查看表结构定义
        mysql> SHOW create table table_name \G;
        --查看表上的索引有哪些
        mysql> SHOW INDEX FROM table_name \G
        或者再简单点，用如下一天命令一次查清楚基于某表的索引
        mysql> SHOW INDEX FROM mydb.mytable;
        即结束了。
        很惊喜，很意外，很舒爽。
         
        为避免Oracle介绍重，MySQL轻的嫌疑，这里具体介绍下官网中MySQL的索引部分。
        MySQL官网查看索引的语法：

        SHOW INDEX Syntax

        SHOW [EXTENDED] {INDEX | INDEXES | KEYS}
            {FROM | IN} tbl_name
            [{FROM | IN} db_name]
            [WHERE expr]
        示例如下：
        mysql> show indexes IN  my_schema.`ethan_table` \G
        *************************** 1. row ***************************
                Table: ethan_table
           Non_unique: 1
             Key_name: idx_ethan_table
         Seq_in_index: 1
          Column_name: order_id
            Collation: A
          Cardinality: 368831
             Sub_part: NULL
               Packed: NULL
                 Null:
           Index_type: BTREE
              Comment:
        Index_comment:
              Visible: YES
           Expression: NULL
        2 rows in set (0.00 sec)
        该图可以看出，key_name“ idx_ethan_table”即是索引的名称，是基于表ethan_table的属性列order_id创建，类型BTREE索引。
        SHOW INDEX返回以下主要字段介绍如下：
        Non_unique
        如果索引不能包含重复项，则为0;如果可以，则为1。
        Key_name
        索引的名称。如果索引是主键，那么名称总是主键。Seq_in_index索引中的列序列号，从1开始。Column_name
        列名。Collation列在索引中的排序方式。它可以有值A(升序)、D(降序)或NULL(未排序)。
        Cardinality
        对索引中惟一值数目的估计。要更新这个数字，运行ANALYZE TABLE或(对于MyISAM表)myisamchk -a。基数是基于存储为整数的统计数据进行计数的，因此即使对于小表，这个值也不一定是精确的。基数越高，MySQL在执行连接时使用索引的机会越大。
        Sub_part
        该指数前缀。也就是说，如果只对列进行部分索引，则索引字符的数量;如果对整个列进行索引，则为NULL。
        注意前缀限制以字节为单位度量。但是，CREATE TABLE、ALTER TABLE和CREATE index语句中索引规范的前缀长度被解释为非二进制字符串类型(CHAR、VARCHAR、TEXT)的字符数和二进制字符串类型(binary、VARBINARY、BLOB)的字节数。在为使用多字节字符集的非二进制字符串列指定前缀长度时，要考虑到这一点。

        Packed
        指示如何包装密钥。如果不是，则为空。Null如果列可能包含空值，则包含“是”;如果不包含空值，则包含“否”。
        Index_type
        使用的索引方法(BTREE、FULLTEXT、HASH、RTREE)。
        Comment
        在它自己的列中没有描述的关于索引的信息，例如，如果索引被禁用，则禁用索引。
        Index_comment
        在创建索引时使用comment属性为索引提供的任何注释。
        Visible
        索引是否对优化器可见。
        Expression
        MySQL 8.0.13及更高版本支持函数关键部分(参见函数关键部分)，它同时影响Column_name和表达式列:对于非功能性键部件，Column_name表示由键部件索引的列，表达式为NULL。对于功能性键部件，Column_name列为NULL，而Expression表示键部件的表达式。
         
        关于表索引的信息也可以从INFORMATION_SCHEMA统计表中获得。隐藏索引的扩展信息只能通过显示扩展索引来实现;它不能从统计表中获得。可以使用shell> mysqlshow [options] [db_name [tbl_name [col_name]]]命令列出表的索引。
         
        示例如下：
        shell> mysqlshow -k -uroot -p my_schema ethan_table order_id
         
        至此，MySQL环境下如何查看表结构和其所基于的索引介绍完毕。
         
        【结语】
        1. 本文回顾了Oracle环境下如何查看表结构和其所基于的索引，以及MySQL下的更为人性、简单的查看语句；
        2. MySQL 8.0.13以后，可以使用
        shell> mysqlshow [options] [db_name [tbl_name [col_name]]]命令列出表的索引；
        3. 感悟：熟悉Oracle和MySQL的亲可能已经深有体会，MySQL在语句的简洁性、易用性上下了很大的工夫，不同于Oracle的语句那么复杂，之前自己在“我的DBA之路”中也说过一段话：“Oracle自治带来的门槛并不意味着成为高级Oracle DBA的拿督降低”；但学好Oracle，对其他数据库的设计理念和原理会帮助甚多。

        【参考】
        https://dev.mysql.com/doc/refman/8.0/en/show-index.html
        【参考】
        https://www.cnblogs.com/JokerShi/p/8087112.html
        以下为个人公众号“一森咖记”，欢迎关注。
        往期精彩文章
        =====================================

        1. MySQL：主从同步延迟Seconds_Behind_Master越来越大，什么鬼？

        2. 浅谈MySQL三种锁：全局锁、表锁和行锁

        3. LINUX环境：MySQL和Oracle开机自启动，咋搞？

        4. 生产环境：mysqlbackup逻辑备份的一种shell脚本实现

        5. 生产环境：mysqlbackup物理备份的一种shell脚本实现

        6. MySql 8.0.16 客户端连接失败

        7. Oracle如何访问MySql：透明网关

        8. 一款好的数据库监控工具：天兔数据库监控系统V3.8搭建

        9. MySQL主从架构搭建+GTID同步方式部署

        10. 用户：单台服务器部署多MySQL实例，咋弄？

        11. MySQL服务器一次异常掉电的恢复


         


        mysql
        最后修改时间：2019-11-13 09:22:59
        文章转载自一森咖记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

        评论