查询数据库索引

2024-01-09 08:34:26

引言

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

查询所有库的索引

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’的主键索引。通过这个条件,我们可以获得排除主键索引后的表的索引数量。最终的结果集将包含指定数据库中的表模式、表名以及不包括主键索引在内的其他索引的数量。结果集按索引数量降序排列,以展示具有最多索引的表首先展示。
###############################################################

今日份小说推荐

《这游戏也太真实了》
推荐理由:这游戏也太真实了是我看的第一部种田+科幻类的网文小说,作者以独特的视角进行创作,达到了较为完美的游戏和现实的结合,并把现实与废土的链接作为先进文明对于现实世界的对接,构建了一个废土和现实世界相互链接的世界。梗多、真实、科幻风十足。
在这里插入图片描述

文章来源:https://blog.csdn.net/weixin_57025326/article/details/135440371
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。