Mysql 简明教程
MySQL - Show Indexes
MySQL 索引是一种特殊查找表类型，用于简化数据库中的数据检索。它指向数据库中的实际数据。
A MySQL Index is a type of special lookup table that is used to make data retrieval easier in a database. It points to the actual data in the database.
MySQL 允许在表中的一个或多个列上创建不同类型的索引。它们有：
MySQL allows various types of indexes to be created on one or more columns in a table. They are:
-
Primary Key Index
-
Unique Index
-
Simple Index
-
Composite Index
-
Implicit Index
为了检查表中是否定义了这些索引，MySQL 提供了 SHOW INDEX 语句。
To check if any of these indexes are defined on a table or not, MySQL provides the SHOW INDEX statement.
The MySQL SHOW INDEX Statement
MySQL 的 SHOW INDEX 语句用于列出表索引信息。
The SHOW INDEX Statement of MySQL is used to list out the information about table index.
Syntax
以下是 SHOW INDEX 语句的基本语法 −
Following is the basic syntax of the SHOW INDEX Statement −
SHOW INDEX FROM table_name;Example
在此示例中，我们使用以下 CREATE TABLE 查询创建一个新表 CUSTOMERS ，并向其某一列添加一个 PRIMARY KEY 索引 −
In this example, we are create a new table CUSTOMERS and adding a PRIMARY KEY index to one of its columns using the following CREATE TABLE query −
CREATE TABLE CUSTOMERS (
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY(ID),
INDEX(NAME)
);现在，我们可以使用以下 SHOW INDEX 查询显示 CUSTOMERS 表上存在的索引 −
Now, we can display the indexes present on the CUSTOMERS table using the following SHOW INDEX query −
SHOW INDEX FROM CUSTOMERS\GOutput
垂直输出将显示为 −
The vertical-output will be displayed as −
*************************** 1. row ************************
Table: customers
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: ID
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 2. row ************************
Table: customers
Non_unique: 1
Key_name: NAME
Seq_in_index: 1
Column_name: NAME
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
2 rows in set (0.01 sec)With IN Clause
在此示例中，让我们首先使用以下 CREATE INDEX 查询对 CUSTOMERS 表的 AGE 列创建索引 −
In this example, let us first create an index on the AGE column of CUSTOMERS table using the following CREATE INDEX query −
CREATE INDEX AGE_INDEX ON CUSTOMERS (AGE);你还可以通过指定数据库名称来检索信息 −
You can also retrieve the information by specifying the database name as −
SHOW INDEX IN CUSTOMERS FROM sample\GOutput
输出将与以上相同 −
The output will be the same as above −
*************************** 1. row ***************************
Table: customers
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: ID
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 2. row ***************************
Table: customers
Non_unique: 1
Key_name: NAME
Seq_in_index: 1
Column_name: NAME
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
2 rows in set (0.01 sec)With WHERE Clause
由于索引以表格格式显示，因此我们可以将 WHERE 子句与 SHOW INDEX 语句配合使用，以检索与给定条件匹配的指定索引。
As the indexes are displayed in a table format, we can use a WHERE clause with SHOW INDEX statement to retrieve specified indexes matching a given condition.
SHOW INDEX IN CUSTOMERS WHERE Column_name = 'NAME'\GOutput
将显示 NAME 列上创建的索引 −
The index created on NAME column is displayed −
*************************** 1. row ************************
Table: customers
Non_unique: 1
Key_name: NAME
Seq_in_index: 1
Column_name: NAME
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
1 row in set (0.00 sec)