526互联

KingbaseESV8R6中查看索引常用sql

发布时间 2023-09-18 13:41:11作者: KINGBASE研究院

前言

KingbaseES具有丰富的索引功能，对于运行一段时间的数据库，经常需要查看索引的使用大小，使用状态等。
尤其重复索引的存在，有时会因为索引过多而造成维护成本加大和减慢数据库的运行速度。
下面是经常使用的查看索引的sql。

1.查看表上索引个数,是否唯一,表与索引大小。

SELECT CONCAT(n.nspname,'.', c.relname) AS table,
 i.relname AS index_name
,indisunique is_unique
,sys_size_pretty(sys_relation_size(x.indrelid)) AS table_size,
          sys_size_pretty(sys_relation_size(x.indexrelid)) AS index_size,
          sys_size_pretty(sys_total_relation_size(x.indrelid)) AS total_size 
 FROM sys_class c
 JOIN sys_index x ON c.oid = x.indrelid
 JOIN sys_class i ON i.oid = x.indexrelid 
 LEFT JOIN sys_namespace n ON n.oid = c.relnamespace
 WHERE c.relkind = ANY (ARRAY['r', 't']) AND c.relname like 'test1';

    table     | index_name | is_unique | table_size | index_size | total_size
--------------+------------+-----------+------------+------------+------------
 public.test1 | idx_id1    | f         | 8192 bytes | 16 kB      | 40 kB
 public.test1 | idx_id2    | f         | 8192 bytes | 16 kB      | 40 kB
(2 行记录)

2.索引的创建语句。

SELECT sys_get_indexdef(indexrelid) AS index_query
FROM sys_index 
WHERE indrelid = 'test1'::regclass;
                      index_query
-------------------------------------------------------
 CREATE INDEX idx_id1 ON public.test1 USING btree (id)
 CREATE INDEX idx_id2 ON public.test1 USING btree (id)
(2 行记录)

3.获取支持的索引方法。
KingbaseES有许多索引方法，如BTree，Hash，GIST和GIN等。

TEST=# select distinct amname from sys_am;
 amname
--------
 bitmap
 btree
 brin
 heap
 spgist
 gist
 gin
 hash
(8 rows)

4.查询未使用的索引。
如果index_scans始终为0 或接近0，可以理解为该索引未使用unsed。
如果有些索引长期未被使用，这些索引不会发挥任何作用，而且会占用不必要的空间，让数据增删改的成本变大，增加备份的时间开销。考虑将其删除。

SELECT s.relname AS table_name,
       indexrelname AS index_name,
       i.indisunique,
       idx_scan AS index_scans
FROM   sys_catalog.pg_stat_user_indexes s,
       sys_index i
WHERE  i.indexrelid = s.indexrelid and idx_scan=0;

table_name |  index_name  | indisunique | index_scans
------------+--------------+-------------+-------------
 company    | company_pkey | t           |           0
 brand      | brand_pkey   | t           |           0
 t          | t_pkey       | t           |           0
 test1      | idx_id1      | f           |           0
 test1      | idx_id2      | f           |           0

4.1查询没有使用过的大于1MB的索引 top 10 (注意, PK、UK如果只是用于约束, 可能不会被统计计数,但不能删掉)。

select sys_size_pretty(sys_relation_size(indexrelid)),* from sys_stat_all_indexes where sys_relation_size(indexrelid)>=1024000 and (idx_scan=0 or idx_tup_read=0 or idx_tup_fetch=0)    
and schemaname not in ('sys_toast','sys_catalog') order by sys_relation_size(indexrelid) desc limit 10;

5.查询重复的索引。
在KingbaseES中同一列可以重复创建索引，然而没有必要在表上有多个具有不同名称的相同索引，同样浪费空间。

SELECT   indrelid::regclass table_name,
         att.attname column_name,
         amname index_method,
         indkey
FROM     sys_index i,
         sys_class c,
         sys_opclass o,
         sys_am a,
         sys_attribute att
WHERE    o.oid = ALL (indclass) 
AND      att.attnum = ANY(i.indkey)
AND      a.oid = o.opcmethod
AND      att.attrelid = c.oid
AND      c.oid = i.indrelid
GROUP BY table_name, 
         att.attname,
         indclass,
         amname, indkey
HAVING count(*) > 1;
 table_name | column_name | index_method | indkey
------------+-------------+--------------+--------
 tmp        | a           | btree        | 1
(1 row)

SELECT relname,(array_agg(idx))[1] idx1,
    sys_get_indexdef((array_agg(idx))[1]) idx1_def,
    (array_agg(idx))[2] idx2,
    sys_get_indexdef((array_agg(idx))[2]) idx2_def,
    (array_agg(idx))[3] idx3,
    sys_get_indexdef((array_agg(idx))[3]) idx3_def 
FROM (
    SELECT indrelid::regclass AS relname,
    indexrelid::regclass AS idx,
    (indrelid::text || indclass::text || indkey::text || COALESCE(indexprs::text,'') || COALESCE(indpred::text,'')) AS KEY 
    FROM sys_index) sub 
GROUP BY relname, KEY 
HAVING count(*) > 1 \gx
-[ RECORD 1 ]-----------------------------------------------
relname  | tmp
idx1     | ind_01
idx1_def | CREATE INDEX ind_01 ON public.tmp USING btree (a)
idx2     | ind_02
idx2_def | CREATE INDEX ind_02 ON public.tmp USING btree (a)
idx3     |
idx3_def |

6.查看无效的索引。

如果 create concurrently index创建索引失败, 索引将处于invalid状态, 需要drop索引重建。

select indisvalid, indexrelid::regclass, indrelid::regclass, sys_get_indexdef(indexrelid) from sys_index where not indisvalid;

7.占用空间top 10的索引。

select schemaname,tablename,indexname,sys_size_pretty(sys_relation_size((quote_ident(schemaname)||'.'||quote_ident(indexname))::regclass)) from sys_indexes 
order by sys_relation_size((quote_ident(schemaname)||'.'||quote_ident(indexname))::regclass) desc limit 10;