MySQL - 显示索引
MySQL 索引是一种特殊的查找表，用于简化数据库中的数据检索。它指向数据库中的实际数据。
MySQL 允许在表中的一个或多个列上创建各种类型的索引。它们分别是:
主键索引
唯一索引
简单索引
复合索引
隐式索引
为了检查表上是否定义了这些索引，MySQL 提供了 SHOW INDEX 语句。
MySQL SHOW INDEX 语句
MySQL 的 SHOW INDEX 语句用于列出表索引的信息。
MySQL 中，垂直格式输出(由 \G 指定)通常与此语句一起使用，以避免长行环绕。
语法
以下是SHOW INDEX 语句 -
SHOW INDEX FROM table_name;
示例
在此示例中，我们创建一个新表 CUSTOMERS，并使用以下 CREATE TABLE 查询为其其中一列添加 PRIMARY KEY 索引 -
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 表上的现有索引 -
SHOW INDEX FROM CUSTOMERS\G
输出
vertical-output 将显示为 -
*************************** 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)
使用 IN 子句
在本例中，我们首先使用以下 CREATE INDEX 查询在 CUSTOMERS 表的 AGE 列上创建索引 -
CREATE INDEX AGE_INDEX ON CUSTOMERS (AGE);
您也可以通过指定数据库名称来检索信息 -
SHOW INDEX IN CUSTOMERS FROM sample\G
输出
输出与上述相同 -
*************************** 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)
使用 WHERE 子句
由于索引以表格式显示，我们可以使用带有 SHOW INDEX 语句的 WHERE 子句来检索符合给定条件的指定索引。
SHOW INDEX IN CUSTOMERS WHERE Column_name = 'NAME'\G
输出
显示在 NAME 列上创建的索引 -
*************************** 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)
使用客户端程序显示索引
我们还可以使用客户端程序显示 MySQL 表上的索引信息。
语法
以下是使用各种编程语言显示 MySQL 表上索引的语法 -
要通过 PHP 程序显示 MySQL 表中的索引，我们需要使用 mysqli 连接器提供的 query() 函数执行 SHOW INDEX 语句，如下所示 -
$sql = "SHOW INDEX FROM tutorials_table"; $mysqli->query($sql);
要通过 JavaScript 程序显示 MySQL 表中的索引，我们需要使用 mysql2 库的 query() 函数执行 SHOW INDEX 语句，如下所示 -
sql = "SHOW INDEXES FROM temp"; con.query(sql);
要通过 Java 程序显示 MySQL 表中的索引，我们需要使用 JDBC 的 executeQuery() 函数执行 SHOW INDEX 语句，如下所示 -
String sql = "SHOW INDEXES FROM tutorials_tbl"; st.executeQuery(sql);
要通过 Python 程序显示 MySQL 表中的索引，我们需要使用 MySQL Connector/Python 的 execute() 函数执行 SHOW INDEX 语句，如下所示 -
rename_view_query = "SHOW INDEXES FROM tutorials_tbl" cursorObj.execute(rename_view_query)
示例
以下是程序 -
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
printf("Connect failed: %s
", $mysqli->connect_error);
exit();
}
// printf('Connected successfully.
');
// SHOW INDEX
$sql = "SHOW INDEX FROM tutorials_table";
if ($index = $mysqli->query($sql)) {
printf("Index shown successfully!.
");
while ($indx = mysqli_fetch_row($index)) {
print_r($indx);
}
}
if ($mysqli->errno) {
printf("Index could not be shown!.
", $mysqli->error);
}
$mysqli->close();
输出
获得的输出如下 -
Index shown successfully!. Array ( [0] => tutorials_tbl [1] => 0 [2] => PRIMARY [3] => 1 [4] => tutorial_id [5] => A [6] => 3 [7] => [8] => [9] => [10] => BTREE [11] => [12] => [13] => YES [14] => ) Array ( [0] => tutorials_tbl [1] => 0 [2] => UIID [3] => 1 [4] => tutorial_id [5] => A [6] => 3 [7] => [8] => [9] => [10] => BTREE [11] => [12] => [13] => YES [14] => )
var mysql = require('mysql2');
var con = mysql.createConnection({
host: "localhost",
user: "root",
password: "Nr5a0204@123"
});
//连接到 MySQL
con.connect(function (err) {
if (err) throw err;
console.log("Connected!");
console.log("--------------------------");
sql = "create database TUTORIALS"
con.query(sql);
sql = "USE TUTORIALS"
con.query(sql);
sql = "CREATE TABLE temp (ID INT, Name VARCHAR(100), Age INT, City VARCHAR(100));"
con.query(sql);
sql = "INSERT INTO temp values(1, 'Radha', 29, 'Vishakhapatnam'), (2, 'Dev', 30, 'Hyderabad');"
con.query(sql);
//Creating Indexes
sql = "CREATE INDEX sample_index ON temp (name) USING BTREE;"
con.query(sql);
sql = "CREATE INDEX composite_index on temp (ID, Name);"
con.query(sql);
//Displaying Indexes
sql = "SHOW INDEXES FROM temp;"
con.query(sql, function(err, result){
if (err) throw err
console.log(result);
});
});
输出
生成的输出如下 -
Connected!
--------------------------
[
{
Table: 'temp',
Non_unique: 1,
Key_name: 'sample_index',
Seq_in_index: 1,
Column_name: 'Name',
Collation: 'A',
Cardinality: 2,
Sub_part: null,
Packed: null,
Null: 'YES',
Index_type: 'BTREE',
Comment: '',
Index_comment: '',
Visible: 'YES',
Expression: null
},
{
Table: 'temp',
Non_unique: 1,
Key_name: 'composite_index',
Seq_in_index: 1,
Column_name: 'ID',
Collation: 'A',
Cardinality: 2,
Sub_part: null,
Packed: null,
Null: 'YES',
Index_type: 'BTREE',
Comment: '',
Index_comment: '',
Visible: 'YES',
Expression: null
},
{
Table: 'temp',
Non_unique: 1,
Key_name: 'composite_index',
Seq_in_index: 2,
Column_name: 'Name',
Collation: 'A',
Cardinality: 2,
Sub_part: null,
Packed: null,
Null: 'YES',
Index_type: 'BTREE',
Comment: '',
Index_comment: '',
Visible: 'YES',
Expression: null
}
]
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class ShowIndex {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/TUTORIALS";
String username = "root";
String password = "password";
try {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection connection = DriverManager.getConnection(url, username, password);
Statement statement = connection.createStatement();
System.out.println("Connected successfully...!");
//Show index...!;
String sql = "SHOW INDEXES FROM tutorials_tbl";
ResultSet resultSet = statement.executeQuery(sql);
System.out.println("Following are the indexes in tutorials_tbl");
while (resultSet.next()){
System.out.println(resultSet.getString(1)+ " "+ resultSet.getString(2)
+" "+resultSet.getString(3)+" " + resultSet.getString(4));
}
connection.close();
} catch (Exception e) {
System.out.println(e);
}
}
}
输出
获得的输出如下所示 -
Connected successfully...! Following are the indexes in tutorials_tbl tutorials_tbl 0 PRIMARY 1 tutorials_tbl 1 tid 1
import mysql.connector
#建立连接
connection = mysql.connector.connect(
host='localhost',
user='root',
password='password',
database='tut'
)
table_name = 'tutorials_tbl'
cursorObj = connection.cursor()
show_indexes_query = f"SHOW INDEXES FROM {table_name}"
cursorObj.execute(show_indexes_query)
indexes = cursorObj.fetchall()
for index in indexes:
print(f"Table: {index[2]}, Index Name: {index[3]}, Column Name: {index[4]}, Non-unique: {index[1]}")
cursorObj.close()
connection.close()
输出
以下是上述代码的输出 -
Table: PRIMARY, Index Name: 1, Column Name: tutorial_id, Non-unique: 0 Table: idx_submission_date, Index Name: 1, Column Name: submission_date, Non-unique: 1
mysql_statements_reference.html
