POSTGRESQL 自动搜索所有逻辑库中的无用索引自动化脚本实现

时间:2023-03-16 15:14:09


前两天腾出点时间,打算整理一下POSTGRESQL 公司的数据库的无用的索引的问题,写了一个SQL 通过SQL 来获取这些数据库的无用索引,但头疼的是,我们整个TEAM 到来的时候,很多坏习惯已经养成了,所以我们目前就是在一个治标的过程,看上去我们的工作有点,“幼稚”,但谁让那些开发的部分必须让我们先改变他们的幼稚。

然后我们的一个同事,刚刚,发现了工作中的难点,并进行了超级改进,将整体的工作自动化,而且还是一个成本很低的方案,SHELL ,我知道,说起SHELL 很多人不屑,现在都是PYTHON, GO 的天下,谁还用SHELL。下面我就展示一下这个SHELL 的 功底,以及设计结构。

POSTGRESQL 的基础,这里是几十个POSTGRESQL 的实例,每个实例下面有不固定的数据库,每个数据库有几百张表,同时每张表里面有众多的没有被使用过的索引。

结果如下,会根据每个命令的执行时间,以及数据库的名字建立文件夹,然后开始针对每一个数据库进行扫描,并产生无用索引的记录以及清理和回滚的语句。

POSTGRESQL 自动搜索所有逻辑库中的无用索引自动化脚本实现

下图信息字符已经替换或更改

POSTGRESQL 自动搜索所有逻辑库中的无用索引自动化脚本实现

整体的工作量大幅度削减。

下面是整体的数据库中获取无用索引的层次图

POSTGRESQL 自动搜索所有逻辑库中的无用索引自动化脚本实现

整体的SHELL 的设计中,大致的结构如下图


POSTGRESQL 自动搜索所有逻辑库中的无用索引自动化脚本实现


POSTGRESQL 自动搜索所有逻辑库中的无用索引自动化脚本实现

POSTGRESQL 自动搜索所有逻辑库中的无用索引自动化脚本实现

POSTGRESQL 自动搜索所有逻辑库中的无用索引自动化脚本实现

SELECT s.schemaname,
       s.relname AS tablename,
       s.indexrelname AS indexname,
       s.idx_scan,
       s.idx_tup_fetch,
       x.indexdef || ' ;' as index_create_statement,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
       'drop index ' || s.indexrelname || ' on ' || s.relname || ' ;' as del_statement
  FROM pg_catalog.pg_stat_user_indexes s
  JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid
  JOIN pg_indexes as x on x.indexname = s.indexrelname
 WHERE s.idx_scan = 0
   and s.idx_tup_fetch = 0
   AND 0 <> ALL(i.indkey)
   AND NOT i.indisunique
   AND NOT EXISTS (SELECT 1
          FROM pg_catalog.pg_constraint c
         WHERE c.conindid = s.indexrelid)
   AND NOT EXISTS (SELECT 1
          FROM pg_catalog.pg_inherits AS inh
         WHERE inh.inhrelid = s.indexrelid)
 ORDER BY pg_relation_size(s.indexrelid) DESC;

——————————————————————————————
#!/bin/bash

hosts=/data/pg_batch_script/hosts_cy
xjsql=/data/pg_batch_script/pgindex.sql
dt=$(date '+%Y%m%d')

xudir=/data/pg_batch_script/${dt}_pgindex
if [ ! -d  $xudir ]; then
    mkdir -p $xudir
fi

log=/data/pg_batch_script/${dt}_pgindex/${dt}.log
#sqlFile=$3


function getDbs(){
  local res=$(echo $sql |psql -h $host -p $port -U $user -d $defaultDb -At -v -w)
  echo $res
}

function getTime(){
  local res=$(date '+%Y%m%d_%H:%M:%S')
  echo $res
}

function printLog(){
  local res="$(getTime)\t$*"
  echo -e $res
  echo -e $res >> $log
}

function getDbInfo(){
  #echo enter getDbInfo:$1
  host=$(jq ".${1}.host" $hosts | sed 's/\"//g')
  #echo host:$host
  port=$(jq ".${1}.port" $hosts)
  user=$(jq ".${1}.user" $hosts | sed 's/\"//g')
  pass=$(jq ".${1}.pass" $hosts | sed 's/\"//g')
  defaultDb=$(jq ".${1}.defaultdb" $hosts | sed 's/\"//g')
  export PGPASSWORD=$pass
  #echo getDbInfo:$host,$port,$user,$pass
}

function getSingleDefault(){
   instanceName=$1
   getDbInfo $instanceName
   psql -h $host -p $port -U $user -d $defaultDb -w   -f $xjsql  >  ${xudir}/${instanceName}_${defaultDb}.log
   printLog $instanceName $defaultDb $res
}

function getAllInstances(){
function getDbs(){
  local sql="select datname from pg_database where datname not like 'test%' and datname not like 'backup%' and datname not in ('template0','template1','template2','postgres','template_db','cy7SaasCenter','cy7SaasCenterTest','cy7server','tcposroot','rdsadmin') order by datname;"
  local res=$(echo $sql |psql -h $host -p $port -U $user -d $defaultDb -At -v -w)
  echo $res
}

function getTime(){
  local res=$(date '+%Y%m%d_%H:%M:%S')
  echo $res
}

function printLog(){
  local res="$(getTime)\t$*"
  echo -e $res
  echo -e $res >> $log
}

function getDbInfo(){
  #echo enter getDbInfo:$1
  host=$(jq ".${1}.host" $hosts | sed 's/\"//g')
  #echo host:$host
  port=$(jq ".${1}.port" $hosts)
  user=$(jq ".${1}.user" $hosts | sed 's/\"//g')
  pass=$(jq ".${1}.pass" $hosts | sed 's/\"//g')
  defaultDb=$(jq ".${1}.defaultdb" $hosts | sed 's/\"//g')
  export PGPASSWORD=$pass
  #echo getDbInfo:$host,$port,$user,$pass
}

function getSingleDefault(){
   instanceName=$1
   getDbInfo $instanceName
   psql -h $host -p $port -U $user -d $defaultDb -w   -f $xjsql  >  ${xudir}/${instanceName}_${defaultDb}.log
   printLog $instanceName $defaultDb $res
}


function getAllDbs(){
  instanceName=$1
  getDbInfo $instanceName
  local dbs=$(getDbs)
  for db in $dbs
  do

程序的调用部分


需要具体咨询脚本问题的,可以加群。

POSTGRESQL 自动搜索所有逻辑库中的无用索引自动化脚本实现