数据库性能调优,不止是加索引,索引建多了,会导致索引数据过去庞大,也会严重影响性能。了解索引的利用率可以帮助我们更好处理垃圾索引。
1、mysql查看索引利用率:
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 ;
-- 如果很慢把排序去掉,加上limit 并且在where条件中限定表名。cardinality越接近0,利用率越低
-- 上述 SQL 语句并不能工作在 MySQL 5.6 版本下(即使最新的 MySQL 5.6.28 版本),因为目前 5.6 的 -- STATISTICS 表中关于 Cardinality 的统计是错误的
2、postgresql查看索引利用率:
SELECT pg_stat_all_indexes.relid,
pg_stat_all_indexes.indexrelid,
pg_stat_all_indexes.schemaname,
pg_stat_all_indexes.relname,
pg_stat_all_indexes.indexrelname,
pg_stat_all_indexes.idx_scan,
pg_stat_all_indexes.idx_tup_read,
pg_stat_all_indexes.idx_tup_fetch
FROM pg_stat_all_indexes
WHERE (pg_stat_all_indexes.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND pg_stat_all_indexes.schemaname !~ '^pg_toast'::text;
-- 可以在where条件中限定表名
总结:这个真的非常方遍,特别是定位大数据量多时候,pgsql还会出现一些包括内存溢出之类的错误