MySQL index使用率查询

最新推荐文章于 2022-10-17 16:51:17 发布

转载最新推荐文章于 2022-10-17 16:51:17 发布 · 205 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/xibuhaohao/p/11038576.html

文章标签：

#数据库

博客展示了一段数据库查询语句，该语句从 information_schema 相关表中查询数据，通过多表连接和条件筛选，获取数据库、表、索引等信息，并按特定条件排序，取前 10 条结果，还标注了待验证。

SELECT
t.table_schema AS db,
t.table_name   AS tab_name,
s.index_name   AS index_name,
s.column_name AS field_name,
s.seq_in_index AS seq_in_index,
s2.max_columns AS max_col,
s.cardinality AS cardinality,
t.table_rows   AS table_rows,
ROUND(((s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) * 100), 2) AS sel
FROM information_schema.statistics s
INNER JOIN information_schema.tables t
    ON s.table_schema = t.table_schema
      AND s.table_name = t.table_name
INNER JOIN (SELECT
                table_schema,
                table_name,
                index_name,
                MAX(seq_in_index) AS max_columns
              FROM information_schema.statistics
              WHERE table_schema != 'mysql'
              GROUP BY table_schema, table_name, index_name) AS s2
    ON s.table_schema = s2.table_schema
      AND s.table_name = s2.table_name
      AND s.index_name = s2.index_name
WHERE t.table_schema != 'mysql'
    AND t.table_rows > 100
    AND s.CARDINALITY IS NOT NULL
    AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) < 1.00
ORDER BY sel, s.table_schema, s.table_name
LIMIT 10