如何在 MySQL 中查看数据库或表的索引？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5213339/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to see indexes for a database or table in MySQL?
提问by Blankman
How do I see if my database has any indexes on it?
如何查看我的数据库是否有任何索引？
How about for a specific table?
对于特定的表呢？
回答by Mark Byers
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 子句将显示所有模式中的所有索引。
回答by RolandoMySQLDBA
If you want to see all indexes across all databases all at once:
如果您想一次查看所有数据库中的所有索引：
use information_schema;
SELECT * FROM statistics;
回答by LiorK
回答by adeviloper
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;
回答by user2065095
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 onlyindex.
您找到了所有仅索引的索引。
Regard.
看待。
回答by Janak
To get all indexed columns per index in one column in the sequence order.
按序列顺序获取一列中每个索引的所有索引列。
SELECT table_name AS `Table`,
index_name AS `Index`,
GROUP_CONCAT(column_name ORDER BY seq_in_index) AS `Columns`
FROM information_schema.statistics
WHERE table_schema = 'sakila'
GROUP BY 1,2;
Ref: http://blog.9minutesnooze.com/mysql-information-schema-indexes/
参考：http: //blog.9minutesnooze.com/mysql-information-schema-indexes/
回答by Ganesh Giri
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;
回答by Digital87
To check all disabled indexes on db
检查数据库上所有禁用的索引
SELECT INDEX_SCHEMA, COLUMN_NAME, COMMENT
FROM information_schema.statistics
WHERE table_schema = 'mydb'
AND COMMENT = 'disabled'
回答by asim
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';
