查询数据库索引

晓夜残歌

已于 2024-01-08 13:36:20 修改

阅读量1.6k

点赞数 44

文章标签： oracle 数据库

于 2024-01-07 15:45:48 首次发布

本文链接：https://blog.csdn.net/weixin_57025326/article/details/135440371

版权

引言

查询数据库索引的意义在于优化和调整数据库的性能。通过查询数据库的索引，我们可以了解每张表包含的索引及个数、每张表的唯一索引和经常使用的索引、哪些索引可以删除、哪些表缺少索引等信息。通过了解这些信息，我们可以有针对性地添加或删除索引，调整索引的类型或顺序等来提高数据库的性能，加快数据查询速度和响应时间。

查询所有库的索引

SELECT t.table_schema, t.table_name, s.index_name, s.column_name, s.non_unique, s.seq_in_index, s.index_type 
FROM information_schema.tables t 
JOIN information_schema.statistics s 
ON t.table_schema = s.table_schema AND t.table_name = s.table_name;

通过使用JOIN子句，将tables表和statistics表连接起来。连接条件是table_schema和table_name必须匹配。这样就可以得到一个包含了表模式、表名、索引名、列名、索引是否为非唯一索引、列在索引中的位置和索引类型的结果集。

查询指定库的索引

SELECT t.table_schema, t.table_name, s.index_name, s.column_name, s.non_unique, s.seq_in_index, s.index_type 
FROM information_schema.tables t 
JOIN information_schema.statistics s 
ON t.table_schema = s.table_schema AND t.table_name = s.table_name
WHERE t.table_schema = '指定的库名';

这个WHERE子句的作用是筛选出具有指定数据库名称的表。通过在JOIN子句中使用"ON t.table_schema = s.table_schema AND t.table_name = s.table_name"，我们将表tables和表statistics连接在一起，然后使用WHERE子句筛选出所需数据库的表。

统计单表内所有索引

SELECT 
    t.table_schema,
    t.table_name,
    COUNT(s.index_name) AS index_count
FROM
    information_schema.tables t
        LEFT JOIN
    information_schema.statistics s ON t.table_schema = s.table_schema
        AND t.table_name = s.table_name
WHERE
    t.table_schema = '指定的库名'
GROUP BY t.table_schema , t.table_name
ORDER BY index_count DESC;

这个SQL语句从当前数据库中的information_schema.tables表中选择了表模式（table_schema）和表名（table_name），然后从information_schema.statistics表中使用LEFT JOIN联接了这个查询结果和表的索引信息。这里使用了LEFT JOIN，是因为并不是每张表都有对应的索引，使用LEFT JOIN可以保证即使不存在索引，这张表依然会被计数。然后使用WHERE子句，将表信息中选择具有指定的库名。之后使用GROUP BY子句，按照表模式和表名分组，统计每张表的索引数量（count(s.index_name)），并且命名为index_count。最后使用ORDER BY子句，按照索引数量降序排列结果集，可以看到有多少张表拥有多少个索引以及最具有索引的表。

排除主键外的索引数量

SELECT 
    t.table_schema,
    t.table_name,
    COUNT(s.index_name) AS index_count
FROM
    information_schema.tables t
        INNER JOIN
    information_schema.statistics s ON t.table_schema = s.table_schema
        AND t.table_name = s.table_name
WHERE
    t.table_schema = '指定的库名'
    AND s.index_name != 'PRIMARY'
GROUP BY t.table_schema , t.table_name
ORDER BY index_count DESC;

在WHERE子句中，我们使用了两个条件：t.table_schema = '指定的库名’和s.index_name != ‘PRIMARY’。其中’指定的库名’是一个占位符，需要替换成你所需查询的具体数据库的名称。
第一个条件t.table_schema = '指定的库名’筛选出具有指定数据库名称的表，这与之前的语句相同。第二个条件s.index_name != 'PRIMARY’排除了名为’PRIMARY’的主键索引。通过这个条件，我们可以获得排除主键索引后的表的索引数量。最终的结果集将包含指定数据库中的表模式、表名以及不包括主键索引在内的其他索引的数量。结果集按索引数量降序排列，以展示具有最多索引的表首先展示。
###############################################################