MySQL - 显示索引

MySQL：如何显示和分析表索引

索引是数据库搜索引擎可用于加速数据检索的特殊查找表。数据库不是扫描整个表来查找你所需的行（‘全表扫描’），而是可以使用索引直接定位数据，这类似于使用书后的索引。正确建立索引的表对于数据库性能至关重要。

MySQL 支持多种类型的索引，包括 PRIMARY KEY（主键）、UNIQUE（唯一索引）、INDEX（非唯一索引）和 FULLTEXT（全文索引）。为了管理性能，你需要一种方法来查看表上定义了哪些索引。本教程涵盖两种主要方法。

方法 1：`SHOW INDEX` 语句

SHOW INDEX 语句是 MySQL 特有的命令，它提供有关表索引的详细信息。

语法

SHOW INDEX FROM table_name;
-- 替代语法：
SHOW INDEXES IN table_name FROM database_name;

提示：在 MySQL 命令行客户端中，在查询后附加 \G 而不是 ; 将使输出垂直格式化，这对于像这样的宽结果集来说更具可读性。

示例

让我们创建一个 products 表，其中包含多种不同的索引。

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(50) NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    category_id INT NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    UNIQUE KEY `uk_sku` (sku),
    INDEX `idx_category_active` (category_id, is_active)
);

现在，让我们显示此表上的索引。

SHOW INDEX FROM products\G

输出

*************************** 1. row ***************************
        Table: products
   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: products
   Non_unique: 0
     Key_name: uk_sku
 Seq_in_index: 1
  Column_name: sku
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 3. row ***************************
        Table: products
   Non_unique: 1
     Key_name: idx_category_active
 Seq_in_index: 1
  Column_name: category_id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 4. row ***************************
        Table: products
   Non_unique: 1
     Key_name: idx_category_active
 Seq_in_index: 2
  Column_name: is_active
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL

理解 `SHOW INDEX` 输出

以下是输出中最重要的列：

Key_name：索引的名称。对于 PRIMARY KEY（主键），名称始终是 PRIMARY。
Non_unique：如果索引必须具有唯一值（如 PRIMARY KEY 或 UNIQUE 索引），则为 0；否则为 1。
Seq_in_index：列在索引中的序号，从 1 开始。这对于复合索引（多列上的索引）很重要。我们的 idx_category_active 有两行，索引中的每个列对应一行。
Column_name：已索引列的名称。
Cardinality：索引中唯一值的估计数量。相对于行数，更高的基数表示更具选择性和更有效的索引。此值会定期更新。
Index_type：使用的索引方法，对于大多数存储引擎通常是 BTREE。

方法 2（现代）：查询 `INFORMATION_SCHEMA`

检索数据库对象元数据的一种更标准、更灵活的方法是查询 INFORMATION_SCHEMA。STATISTICS 表包含有关表索引的信息。

语法

SELECT
    INDEX_NAME,
    COLUMN_NAME,
    NON_UNIQUE,
    SEQ_IN_INDEX
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

示例

让我们获取 products 表的索引信息，假设它位于名为 store 的数据库中。

SELECT
    INDEX_NAME,
    COLUMN_NAME,
    SEQ_IN_INDEX,
    NON_UNIQUE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'store' AND TABLE_NAME = 'products'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

输出

INDEX_NAME	COLUMN_NAME	SEQ_IN_INDEX	NON_UNIQUE
PRIMARY	id	1	0
idx_category_active	category_id	1	1
idx_category_active	is_active	2	1
uk_sku	sku	1	0

`SHOW INDEX` vs. `INFORMATION_SCHEMA`

SHOW INDEX：在 MySQL 客户端中进行交互式使用的快速、简单命令。其输出格式是 MySQL 特有的。
INFORMATION_SCHEMA：查询元数据的 SQL 标准方式。它更强大、更灵活，因为你可以使用标准的 SELECT 语法，包括 WHERE 子句、JOIN（例如，从 INFORMATION_SCHEMA.COLUMNS 获取列数据类型）和 ORDER BY。强烈建议将此方法用于程序化访问。

在应用程序代码中显示索引信息

对于需要检查数据库模式的脚本或应用程序，查询 INFORMATION_SCHEMA 是最佳实践。以下是如何在各种语言中实现这一点。

Python (mysql-connector)
Node.js (async/await)
Java (JDBC)
PHP (PDO)



此 Python 脚本以结构化方式检索并打印索引信息。

```python
# main.py
import mysql.connector

config = { 'user': 'root', 'password': 'password' }

db_name = 'store'
tbl_name = 'products'

sql = """
SELECT INDEX_NAME, COLUMN_NAME, NON_UNIQUE, SEQ_IN_INDEX
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s
ORDER BY INDEX_NAME, SEQ_IN_INDEX
"""

with mysql.connector.connect(**config) as cnx:
    with cnx.cursor(dictionary=True) as cursor:
        cursor.execute(sql, (db_name, tbl_name))
        print(f"Indexes on table '{db_name}.{tbl_name}':") # 表 '{db_name}.{tbl_name}' 上的索引：
        for index in cursor:
            print(f" - Index: {index['INDEX_NAME']}, Column: {index['COLUMN_NAME']}, " # - 索引：{index['INDEX_NAME']}，列：{index['COLUMN_NAME']}，
                  f"Seq: {index['SEQ_IN_INDEX']}, Unique: {not index['NON_UNIQUE']}") # 序号：{index['SEQ_IN_INDEX']}，唯一：{not index['NON_UNIQUE']}

此 mysql2/promise 示例获取并显示索引元数据。

import mysql from 'mysql2/promise';

async function showIndexes() {
  let connection;
  const dbName = 'store';
  const tableName = 'products';
  const sql = `
    SELECT INDEX_NAME, COLUMN_NAME, NON_UNIQUE, SEQ_IN_INDEX
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
    ORDER BY INDEX_NAME, SEQ_IN_INDEX;
  `;

  try {
    connection = await mysql.createConnection({ /* connection config */ }); // 连接配置
    const [indexes] = await connection.execute(sql, [dbName, tableName]);

    console.log(`Indexes on table '${dbName}.${tableName}':`); // 表 '${dbName}.${tableName}' 上的索引：
    indexes.forEach(idx => {
      console.log(
        ` - Index: ${idx.INDEX_NAME}, Column: ${idx.COLUMN_NAME}, ` + // - 索引：${idx.INDEX_NAME}，列：${idx.COLUMN_NAME}，
        `Seq: ${idx.SEQ_IN_INDEX}, Unique: ${!idx.NON_UNIQUE}` // 序号：${idx.SEQ_IN_INDEX}，唯一：${!idx.NON_UNIQUE}
      );
    });
  } finally {
    if (connection) await connection.end();
  }
}
showIndexes();

Java 的 JDBC 为此提供了专用的 DatabaseMetaData API，这是一个更高级别的抽象。

import java.sql.*;

public class ShowIndexExample {
    // ... connection details ... (连接详情)
    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
            DatabaseMetaData metaData = conn.getMetaData();
            // Params: catalog, schema, table, unique, approximate (参数：目录、模式、表、唯一、近似)
            try (ResultSet rs = metaData.getIndexInfo("store", null, "products", false, false)) {
                System.out.println("Indexes on table 'store.products':"); // 表 'store.products' 上的索引：
                while (rs.next()) {
                    System.out.printf(" - Index: %s, Column: %s, Seq: %d, Unique: %b\n", // - 索引：%s，列：%s，序号：%d，唯一：%b
                            rs.getString("INDEX_NAME"),
                            rs.getString("COLUMN_NAME"),
                            rs.getShort("ORDINAL_POSITION"),
                            !rs.getBoolean("NON_UNIQUE"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

此 PHP 脚本使用 PDO 预处理语句查询 INFORMATION_SCHEMA。

<?php
require 'config.php'; // Assumes a file with PDO connection $pdo (假设文件包含 PDO 连接 $pdo)

$db_name = 'store';
$table_name = 'products';

$sql = "
    SELECT INDEX_NAME, COLUMN_NAME, NON_UNIQUE, SEQ_IN_INDEX
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA = :db_name AND TABLE_NAME = :table_name
    ORDER BY INDEX_NAME, SEQ_IN_INDEX
";

$stmt = $pdo->prepare($sql);
$stmt->execute(['db_name' => $db_name, 'table_name' => $table_name]);

$indexes = $stmt->fetchAll();

echo "Indexes on table '$db_name.$table_name':\n"; // 表 '$db_name.$table_name' 上的索引：
foreach ($indexes as $index) {
    $is_unique = !$index['NON_UNIQUE'] ? 'true' : 'false';
    echo sprintf(" - Index: %s, Column: %s, Seq: %d, Unique: %s\n", // - 索引：%s，列：%s，序号：%d，唯一：%s
        $index['INDEX_NAME'],
        $index['COLUMN_NAME'],
        $index['SEQ_IN_INDEX'],
        $is_unique
    );
}
?>

MySQL - 显示索引

MySQL：如何显示和分析表索引

方法 1：SHOW INDEX 语句

语法

示例

输出

理解 SHOW INDEX 输出

方法 2（现代）：查询 INFORMATION_SCHEMA

语法

示例

输出

SHOW INDEX vs. INFORMATION_SCHEMA

在应用程序代码中显示索引信息

方法 1：`SHOW INDEX` 语句

理解 `SHOW INDEX` 输出

方法 2（现代）：查询 `INFORMATION_SCHEMA`

`SHOW INDEX` vs. `INFORMATION_SCHEMA`