mysql查看索引利用率

本文主要是介绍mysql查看索引利用率，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

-- mysql查看索引利用率
-- 如果很慢把排序去掉，加上limit  并且在where条件中限定表名。
-- cardinality越接近0，利用率越低

SELECT 
  t.TABLE_SCHEMA,
  t.TABLE_NAME,
  INDEX_NAME,
  CARDINALITY,
  TABLE_ROWS,
  CARDINALITY / TABLE_ROWS AS SELECTIVITY 
FROM
  information_schema.TABLES t,
  (SELECT 
    table_schema,
    table_name,
    index_name,
    cardinality 
  FROM
    information_schema.STATISTICS 
  WHERE (
      table_schema,
      table_name,
      index_name,
      seq_in_index
    ) IN 
    (SELECT 
      table_schema,
      table_name,
      index_name,
      MAX(seq_in_index) 
    FROM
      information_schema.STATISTICS 
    GROUP BY table_schema,
      table_name,
      index_name)) s 
WHERE t.table_schema = s.table_schema 
  AND t.table_name = s.table_name 
  AND t.table_rows != 0 
  AND t.table_schema NOT IN (
    'mysql',
    'performance_schema',
    'information_schema'
  ) 
ORDER BY SELECTIVITY ;

这篇关于mysql查看索引利用率的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！