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

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

时间:2023-06-22 12:03:14浏览次数:41  
标签:function POSTGRESQL res echo 索引 host hosts pg 库中


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

开头还是介绍一下群,如果感兴趣polardb ,mongodb ,mysql ,postgresql ,redis 等有问题,有需求都可以加群群内有各大数据库行业大咖,CTO,可以解决你的问题。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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 自动搜索所有逻辑库中的无用索引自动化脚本实现_自动化_09

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

标签:function,POSTGRESQL,res,echo,索引,host,hosts,pg,库中
From: https://blog.51cto.com/u_14150796/6534566

相关文章

  • PostgreSQL 15 让多年被DISS的PG 安全画上圆满的句号
    开头还是介绍一下群,如果感兴趣polardb,mongodb,mysql,postgresql,redis等有问题,有需求都可以加群群内有各大数据库行业大咖,CTO,可以解决你的问题。提起POSTGRESQL中的安全问题其中最容易被人Diss的最大BUG并不是autovacuum 之类的部分,排在首位的被DISS的最大的问题是安全的......
  • POSTGRESQL postgresql 升级的需求来自哪里
    开头还是介绍一下群,如果感兴趣polardb,mongodb,mysql,postgresql,redis等有问题,有需求都可以加群群内有各大数据库行业大咖,CTO,可以解决你的问题(本篇的思路来自于,盘古云课堂PG152023年2月18日晚,PG15升级问题大讨论稿)说起POSTGRESQL的升级问题,很多同学会问,升级POSTGRESQL......
  • POSTGRESQL 再说 PGBOUNCER 如何部署的问题
    开头还是介绍一下群,如果感兴趣polardb,mongodb,mysql,postgresql,redis等有问题,有需求都可以加群群内有各大数据库行业大咖,CTO,可以解决你的问题。最近得到与PGBOUNCER的一个问题,问题大体上是这样描述的,一台POSTGRESQL的服务器,2000个maxconnection,同时安装了4个pgbouncer在......
  • PostgreSQL 16 三则 “新功能更新”
    开头还是介绍一下群,如果感兴趣polardb,mongodb,mysql,postgresql,redis等有问题,有需求都可以加群群内有各大数据库行业大咖,CTO,可以解决你的问题。POSTGRESQL15刚刚推出不久,而POSTGRESQL16的新功能也已经在路上了,下面说说PG16已经确认有的3个新功能。1PG_DUMP压缩相对......
  • POSTGRESQL SQL 优化,不建立索引,不调整参数,不修改SQL的另类方式
    开头还是介绍一下群,如果感兴趣polardb,mongodb,mysql,postgresql,redis等有问题,有需求都可以加群群内有各大数据库行业大咖,CTO,软件架构师,软件开发大佬,可以解决你的问题。在MYSQL中很少听说过自建统计信息,实际上在其他数据库中,创建统计信息的方式和需求都是有的,尤其处理复杂SQ......
  • 搜索引擎搜索技巧
    方法:  前言:以下用加号(+)表示空格,如A+B即A[空格]B;关键词+filetype:格式  指定文件类型搜索,较严格;平时也可以直接用“文件名+格式”搜索。    注意:只有搜索引擎支持的格式才可以使用filetype,如doc、ppt、pdf等;而epub、mobi等不行,它们可以使用“名称+格式......
  • 搜索引擎的搜索方法
    site:www.bing.com搜索指定的网站搜索指定的文件类型例如:防火墙使用手册filetype:ppt......
  • mysql索引及索引创建原则
    1.mysql索引及索引创建原则目录1.mysql索引及索引创建原则1.1.使用场景1.1.1.什么时候用索引1.1.2.索引的弱点1.1.3.MySQL会使用到索引的场景如下:1.2.查看表上的索引1.3.索引类型1.3.1.组合索引1.3.1.1.组合索引生效规则1.3.2.前缀索引1.3.3.函数索引1.3.4.唯一索......
  • mysql索引和基本概念
    1.mysql索引和基本概念目录1.mysql索引和基本概念1.1.声明1.2.什么是索引1.3.二分查找法(BinarySearch)1.4.二叉查找树(BST)1.4.1.二叉树的特点1.4.2.二叉树存在的问题1.5.平衡二叉树(AVLTree)1.5.1.平衡二叉树的特点1.6.索引需要存储什么1.7.B树的特点1.7.1.B树是......
  • PG-DBA培训03:Linux平台PostgreSQL安装配置与管理入门
    一、风哥PG-DBA培训03:Linux平台PostgreSQL安装配置与管理入门本课程由风哥发布的基于PostgreSQL数据库的系列课程,本课程属于PostgreSQL数据库实战入门与安装配置阶段之Linux平台PostgreSQL安装配置与管理入门课程,学完本课程可以掌握基于Linux平台的PostgreSQL项目规划,PostgreSQL......