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

开头还是介绍一下群，如果感兴趣polardb ,mongodb ,mysql ,postgresql ,redis 等有问题，有需求都可以加群群内有各大数据库行业大咖，CTO，可以解决你的问题。加群请联系 liuaustin3 ，在新加的朋友会分到2群（共600人左右 1 + 2）。

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

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

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

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

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

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

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

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

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

程序的调用部分

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