如何在MySQL中查看数据库或表的索引？

How do I see if my database has any indexes on it?

如何查看我的数据库是否有任何索引?

How about for a specific table?

对于特定的桌子怎么样?

8 个解决方案

#1

554

To see the index for a specific table use SHOW INDEX:

要查看特定表的索引,请使用SHOW INDEX:

SHOW INDEX FROM yourtable;

To see indexes for all tables within a specific schema you can use the STATISTICS table from INFORMATION_SCHEMA:

要查看特定模式中所有表的索引,可以使用INFORMATION_SCHEMA中的STATISTICS表:

SELECT DISTINCT
    TABLE_NAME,
    INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_schema';

Removing the where clause will show you all indexes in all schemas.

删除where子句将显示所有模式中的所有索引。

#2

If you want to see all indexes across all databases all at once:

如果要同时查看所有数据库中的所有索引:

use information_schema;
SELECT * FROM statistics;

#3

SHOW INDEX FROM mytable FROM mydb;

SHOW INDEX FROM mydb.mytable;

See documentation.

#4

You could use this query to get the no of indexes as well as the index names of each table in specified database.

您可以使用此查询来获取索引的数量以及指定数据库中每个表的索引名称。

SELECT TABLE_NAME,
       COUNT(1) index_count,
       GROUP_CONCAT(DISTINCT(index_name) SEPARATOR ',\n ') indexes
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'mydb'
      AND INDEX_NAME != 'primary'
GROUP BY TABLE_NAME
ORDER BY COUNT(1) DESC;

#5

I propose this query:

我建议这个查询:

SELECT DISTINCT s.*
FROM INFORMATION_SCHEMA.STATISTICS s
LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS t 
    ON t.TABLE_SCHEMA = s.TABLE_SCHEMA 
       AND t.TABLE_NAME = s.TABLE_NAME
       AND s.INDEX_NAME = t.CONSTRAINT_NAME 
WHERE 0 = 0
      AND t.CONSTRAINT_NAME IS NULL
      AND s.TABLE_SCHEMA = 'YOUR_SCHEMA_SAMPLE';

You found all Index only index.

您找到了所有仅索引索引。

Regard.

#6

To check all disabled indexes on db

检查db上的所有已禁用索引

SELECT INDEX_SCHEMA, COLUMN_NAME, COMMENT 
FROM information_schema.statistics
WHERE table_schema = 'mydb'
AND COMMENT = 'disabled'

#7

This works in my case for getting table name and column name in the corresponding table for indexed fields.

这在我的情况下适用于在索引字段的相应表中获取表名和列名。

SELECT TABLE_NAME , COLUMN_NAME, COMMENT 
FROM information_schema.statistics
WHERE table_schema = 'database_name';

#8

You can check your indexes in MySQL workbench.under the performance reports tabs you can see all used indexes and unused indexes on the system. or you can fire the query.

您可以在MySQL工作台中检查索引。在性能报告选项卡下,您可以看到系统上所有使用的索引和未使用的索引。或者您可以触发查询。

select * from sys.schema_index_statistics;

#1

554