【引言】

今天中午項目組來一需求，欲在MySQL環境的某張表下創建幾個BTREE索引。要創建索引，首先需要了解基表的表結構，以及已經包含的索引。Oracle的表結構大家都很熟悉，但MySQL表結構和已創建索引的查看怎么操作，本文將一一講述。

文章大綱

1.Oracle如何查看表結構和索引

2.MySQL如何查看表結構和索引

3.結語

一、Oracle如何查看表結構和索引

先來回顧下Oracle是如何查看表結構及表索引

首先查看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 = 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