如何查看MySQL中的索引和进行索引监控？

在MySQL数据库中，索引是一种用于提高查询性能的数据结构，通过查看索引，可以了解数据库表的索引情况，以及如何优化查询操作，以下是关于如何查看索引和监控索引使用情况的详细解答。

一、查看索引的方法

1、使用SHOW INDEX命令

SHOW INDEX命令是查看MySQL数据库中表索引的最直接和常用的方法，使用SHOW INDEX命令，可以获取表中所有索引的详细信息，包括索引的名称、类型、列名、唯一性等。

语法：SHOW INDEX FROM table_name;

示例：假设我们有一个名为employees的表，使用SHOW INDEX命令来查看其索引信息：

     SHOW INDEX FROM employees;

执行上述命令后，MySQL将返回一个结果集，其中包含表中所有索引的详细信息，结果集的列包括：

Table: 表名

Non_unique: 如果索引不能包含重复词，则为0；如果可以，则为1

Key_name: 索引的名称

Seq_in_index: 索引中的列序列号，从1开始

Column_name: 列名

Collation: 列的排序方式

Cardinality: 索引中唯一值的数目

Sub_part: 如果列只是被部分索引，则为被索引的字符数

Packed: 指示关键字如何被压缩

Null: 是否包含NULL

Index_type: 索引的类型（BTREE, FULLTEXT, HASH, RTREE）

Comment: 注释

2、使用DESCRIBE命令

DESCRIBE命令可以用来查看表的结构信息，包括索引信息。

语法：DESCRIBE table_name;

示例：要查看名为users的表的索引信息，可以执行以下命令：

     DESCRIBE users;

这将返回该表的结构信息，包括字段名称、字段类型、索引信息等，在索引列的Extra字段中，如果出现"PRI"表示该字段是主键，如果出现"UNI"表示该字段是唯一索引，如果出现"MUL"表示该字段是普通索引。

3、使用SHOW CREATE TABLE命令

SHOW CREATE TABLE命令可以用来查看创建表的SQL语句，其中包含了索引的定义信息。

语法：SHOW CREATE TABLE table_name;

示例：要查看名为users的表的索引定义，可以执行以下命令：

     SHOW CREATE TABLE users;

这将返回创建该表的SQL语句，其中包含了索引的定义信息，可以通过查看CREATE TABLE语句中的INDEX关键字来获取索引的定义。

4、使用INFORMATION_SCHEMA数据库

INFORMATION_SCHEMA是MySQL的系统数据库，包含了关于数据库对象（如表、列、索引等）的元数据，通过查询INFORMATION_SCHEMA数据库中的相关表，可以获取数据库中所有索引的详细信息。

语法：SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME = 'table_name';

示例：假设我们有一个名为employees的表，位于名为company的数据库中，使用以下查询来查看其索引信息：

     SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'company' AND TABLE_NAME = 'employees';

执行上述查询后，MySQL将返回一个结果集，其中包含表中所有索引的详细信息。

二、监控索引使用情况

在某个OLTP系统中某张表上建立了10个索引，由于不确定索引是否有在各程序中被使用，不能随意删除，因此需要监控已有的索引是否在使用，官方文档描述：Monitoring Index Usage Oracle Database provides a means of monitoring indexes to determine whether they are being used. If an index is not being used, then it can be dropped, eliminating unnecessary statement overhead. To start monitoring the usage of an index, issue this statement:

ALTER INDEX index MONITORING USAGE; //开启索引监控

Later, issue the following statement to stop the monitoring:

ALTER INDEX index NOMONITORING USAGE; //关闭索引监控

The view V$OBJECT_USAGE can be queried for the index being monitored to see if the index has been used. The view contains a USED column whose value is YES or NO, depending upon if the index has been used within the time period being monitored. The view also contains the start and stop times of the monitoring period, and a MONITORING column (YES/NO) to indicate if usage monitoring is currently active. Each time that you specify MONITORING USAGE, the V$OBJECT_USAGE view is reset for the specified index. The previous usage information is cleared or reset, and a new start time is recorded. When you specify NOMONITORING USAGE, no further monitoring is performed, and the end time is recorded for the monitoring period. Until the next ALTER INDEX…MONITORING USAGE statement is issued, the view information is left unchanged.

开启索引监控后，可以从v$object_usage中查询到数据（即使索引还没有被使用），START_MONITORING 字段会写入开始监控的时间；当某条SQL使用了相关的索引以后，其“USED”字段就会更新为’YES‘，监控索引结束后最好关闭监控，以提高效率，注意： v$object_usage 要去每个USERS 中查询，即使用SYS权限开启的其他USER索引的监控，也无法从SYS用户的V$OBJECT_USAGE 中插到相关的记录。

实验：测试一个索引

[oracle@tyger ~]$sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 Production on Fri Mar 28 16:19:01 2014

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Production

With the Partitioning, OLAP and Data Mining options

SYS@ORCL>create user tyger identified by tyger; // 创建测试用户 tyger

User created.

SYS@ORCL>grant connect,resource to tyger; // 赋予tyger connect，resource 权限

Grant succeeded.

SYS@ORCL>grant select on dba_objects to tyger; // 授予 dba_objects 查询权限给 tyger

Grant succeeded.

SYS@ORCL>conn tyger/tyger

Connected.

TYGER@ORCL>create table test_monitor as select * from dba_objects; // 创建测试表

Table created.

TYGER@ORCL>select * from user_indexes; // 查看当前用户下的索引

no rows selected

TYGER@ORCL>create index test_monitor_idx on test_monitor(object_id); // 基于 object_id 字段创建索引

Index created.

TYGER@ORCL>col INDEX_NAME for a20

TYGER@ORCL>col TABLE_NAME for a20

TYGER@ORCL>col TABLE_OWNER for a20

L

1 select index_name,table_name,table_owner

2* from user_indexes

3/ // 查看当前用户下的索引

INDEX_NAME TABLE_NAME TABLE_OWNER

TEST_MONITOR_IDX TEST_MONITOR TYGER

…

三、常见问题与解答

Q1: 如何查看某个特定表的所有索引？

A1: 要查看某个特定表的所有索引，可以使用SHOW INDEX命令或DESCRIBE命令，对于名为users的表，可以使用以下命令：

SHOW INDEX FROM users;

或

DESCRIBE users;

这些命令将返回该表的所有索引信息。

Q2: 如何查看数据库中所有表的索引？

A2: 要查看数据库中所有表的索引，可以通过查询INFORMATION_SCHEMA数据库中的相关表来实现，要查看名为company的数据库中所有表的索引，可以使用以下查询：

SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'company';

这条查询将返回company数据库中所有表的索引信息。

Q3: 如何监控索引的使用情况？

A3: 要监控索引的使用情况，可以使用ALTER INDEX命令来开启或关闭索引监控，要监控名为test_monitor_idx的索引，可以使用以下命令：

ALTER INDEX test_monitor_idx MONITORING USAGE; //开启索引监控

要停止监控，可以使用：

ALTER INDEX test_monitor_idx NOMONITORING USAGE; //关闭索引监控

可以查询V$OBJECT_USAGE视图来查看索引的使用情况。