檢測不再使用的索引–check-unused-keys

笱局長發表於2017-12-25

指令碼

#!/usr/bin/perl

################################################################################
# check-unused-keys - Perl Script to check unused indexes
# 
# @author Ryan Lowe <ryan.a.lowe@percona.com>
################################################################################

use strict;
use warnings FATAL => `all`;
use Pod::Usage;
use Getopt::Long;
use English qw(-no_match_vars);
use DBI;

my $VERSION = `0.0.2`;
my %OPTIONS;
$OPTIONS{`summary`} = 1;

################################################################################
# Get configuration information
################################################################################

# Parse command line opts
my $gop=new Getopt::Long::Parser;
$gop->configure(`no_ignore_case`,`bundling`);
if (!$gop->getoptions(
    `create-alter!`        => $OPTIONS{`createalter`},
    `databases|d=s`        => $OPTIONS{`database` },
    `help|h`               => $OPTIONS{`help`     },
    `hostname|H=s`         => $OPTIONS{`host`     },
    `ignore-databases=s`   => $OPTIONS{`ignoredb` },
    `ignore-indexes=s`     => $OPTIONS{`ignoreidx`},
    `ignore-primary-key!`  => $OPTIONS{`ignorepk`},   
    `ignore-tables=s`      => $OPTIONS{`ignoretbl`},
    `ignore-unique-index!` => $OPTIONS{`ignoreuniq`},
    `print-unused-tables!` => $OPTIONS{`printunusedtbl`},
    `options-file=s`       => $OPTIONS{`def`      },
    `password|p=s`         => $OPTIONS{`password` },
    `port=i`               => $OPTIONS{`port`     },
    `socket|s=s`           => $OPTIONS{`socket`   },
    `summary!`             => $OPTIONS{`summary`  },
    `tables|t=s`           => $OPTIONS{`tables`   },
    `username|u=s`         => $OPTIONS{`user`     },
    `verbose|v+`           => $OPTIONS{`verbose`  },
    `version|V`            => $OPTIONS{`version`  } ) ) {

    pod2usage(2);
}

# Yay for versions
if ($OPTIONS{`version`}) {
    print "$VERSION
";
    exit 0;
}

# Help if asked for or no check given
pod2usage(2) if     ($OPTIONS{`help`});

# Set global defaults/validate options
$OPTIONS{`timeout`} = $OPTIONS{`timeout`} ? $OPTIONS{`timeout`} : 10;
$OPTIONS{`verbose`} = $OPTIONS{`verbose`} ? $OPTIONS{`verbose`} : 0;

################################################################################
# Begin the main program
################################################################################

# Set db defaults/validate options
$OPTIONS{`host`} = $OPTIONS{`host`} ? $OPTIONS{`host`} : `localhost`;
$OPTIONS{`port`} = $OPTIONS{`port`} ? $OPTIONS{`port`} : `3306`;
$OPTIONS{`def` } = $OPTIONS{`def` } ? $OPTIONS{`def` } : $ENV{`HOME`}.`/.my.cnf`;

# Set some default behaviour
$OPTIONS{`createalter`}     = defined($OPTIONS{`createalter`})     ? $OPTIONS{`createalter`} : 0;
$OPTIONS{`ignorepk`}        = defined($OPTIONS{`ignorepk`})        ? $OPTIONS{`ignorepk`} : 1;
$OPTIONS{`ignoreuniq`}      = defined($OPTIONS{`ignoreuniq`})      ? $OPTIONS{`ignoreuniq`} : 1;
$OPTIONS{`printunusedtbl`}  = defined($OPTIONS{`printunusedtbl`})  ? $OPTIONS{`printunusedtbl`} : 0;


# Attempt db connection
my $connection_string  = `DBI:mysql:`;
$connection_string    .= "host=$OPTIONS{`host`};";
$connection_string    .= "database=$OPTIONS{`database`};"
    if $OPTIONS{`database`};
$connection_string    .= "mysql_socket=$OPTIONS{`socket`};"
    if $OPTIONS{`socket`} and $OPTIONS{`host`} eq `localhost`;
$connection_string    .= "port=$OPTIONS{`port`};";
$connection_string    .= "mysql_read_default_file=$OPTIONS{`def`};";
$connection_string    .= "mysql_read_default_group=client;";
$connection_string    .= "mysql_multi_statements=1";
my $dbh;
eval {
    $dbh = DBI->connect (
        $connection_string,
        $OPTIONS{`user`},
        $OPTIONS{`password`},
        { RaiseError => 1, PrintError => 0 }
    );
};

if ( $@ ) {
    print "Could not connect to MySQL
";
    print "
";
    print $@ if ($OPTIONS{`verbose`} > 0);
    exit 1;
}

# Check to make sure userstats is actually enabled:)

my $sanity_query = `SHOW GLOBAL VARIABLES LIKE "userstat_running"`;
my $sth = $dbh->prepare($sanity_query);
$sth->execute();

my $status = $sth->fetchrow_hashref();
die(`userstat is NOT running`) unless ($status->{`Value`} eq `ON`); 

################################################################################
# Build The Query
################################################################################

my $query = `
SELECT DISTINCT `s`.`TABLE_SCHEMA`, `s`.`TABLE_NAME`, `s`.`INDEX_NAME`,
      `s`.`NON_UNIQUE`, `s`.`INDEX_NAME`, `t`.`ROWS_READ` AS TBL_READ, `i`.`ROWS_READ` AS IDX_READ
  FROM `information_schema`.`statistics` AS `s` 
  LEFT JOIN `information_schema`.`index_statistics` AS `i`
    ON (`s`.`TABLE_SCHEMA` = `i`.`TABLE_SCHEMA` AND 
        `s`.`TABLE_NAME`   = `i`.`TABLE_NAME` AND
        `s`.`INDEX_NAME`   = `i`.`INDEX_NAME`)
  LEFT JOIN `information_schema`.`table_statistics` AS `t`
    ON (`s`.`TABLE_SCHEMA` = `t`.`TABLE_SCHEMA` AND 
        `s`.`TABLE_NAME`   = `t`.`TABLE_NAME`)
  WHERE `i`.`TABLE_SCHEMA` IS NULL
`;

if ($OPTIONS{`database`}) {
    my @dbs = split(`,`, $OPTIONS{`database`});
    $query .= `    AND `s`.`TABLE_SCHEMA` IN ("`.join(`","`,@dbs).`")
`;
}

if ($OPTIONS{`ignoredb`}) {
    my @dbs = split(`,`, $OPTIONS{`ignoredb`});
    $query .= `    AND `s`.`TABLE_SCHEMA` NOT IN ("`.join(`","`,@dbs).`")
`;
}

if ($OPTIONS{`ignoretbl`}) {
    my @tbls = split(`,`, $OPTIONS{`ignoretbl`});
    foreach (@tbls) {
        my @a = split(/./, $_); 
        $query .= `    AND (`s`.`TABLE_SCHEMA` != "`.$a[0].`" AND `s`.`TABLE_NAME` != "`.$a[1].`")
`;
    } 
}

if ($OPTIONS{`ignoreidx`}) {
    my @idxs = split(`,`, $OPTIONS{`ignoreidx`});
    foreach (@idxs) {
        my @a = split(/./, $_);
        $query .= `    AND (`s`.`TABLE_SCHEMA` != "`.$a[0].`" AND `s`.`TABLE_NAME` != "`.$a[1].`" AND `s`.`INDEX_NAME` != "`.$a[2].`")
`;
    }
}

if ($OPTIONS{`tables`}) {
    my @tbls = split(/,/, $OPTIONS{`tables`});
    foreach (@tbls) {
        my @a = split(/./, $_);
        $query .= `    AND (`s`.`TABLE_SCHEMA` = "`.$a[0].`" AND `s`.`TABLE_NAME` = "`.$a[1].`")
`;
    }
}

if ($OPTIONS{`ignorepk`}) {
    $query .= `    AND `s`.`INDEX_NAME` != "PRIMARY"
`;
}

if ($OPTIONS{`ignoreuniq`}) {
    $query .= `    AND `s`.`NON_UNIQUE` = 1
`;
}

#if ($OPTIONS{`ignoreunusedtbl`}) {
#    $query .= `    AND `t`.`ROWS_READ` > 0 AND `t`.`ROWS_READ` IS NOT NULL
#`;
#}


print $query."
" if ($OPTIONS{`verbose`} gt 1);

$sth = $dbh->prepare($query);
$sth->execute();

my $n_indexes = 0;
my $n_tbls = 0;
my $ignored_tbls = {};
my %alters;

## loop through all returned rows
while (my $row = $sth->fetchrow_hashref()) {
    my $tbl = ```.$row->{`TABLE_SCHEMA`}.``.``.$row->{`TABLE_NAME`}.```;

    ## if this table was never read from
    if (!defined($row->{`TBL_READ`}) or $row->{`TBL_READ`} eq 0) {
        ## skip if we already printed this table
        next if ($ignored_tbls->{$row->{`TABLE_NAME`}});

        $ignored_tbls->{$row->{`TABLE_NAME`}} = 1;
        $n_tbls++;

        print "# Table $tbl not used.
"  if ($OPTIONS{`printunusedtbl`} gt 0);
        
        ## dont bother doing check for unused indexes if table was never read
        next;
    }

    ## build the ALTER command
    $n_indexes++;
    if ($OPTIONS{`createalter`}) {
        if (!defined($alters{$tbl})) {
            $alters{$tbl} = `ALTER TABLE `.$tbl.` DROP INDEX ``.$row->{`INDEX_NAME`}.```;
        } else {
            $alters{$tbl} .= ",
    DROP INDEX `".$row->{`INDEX_NAME`}.```;
        }
    }
    print "# Index $tbl (".$row->{`INDEX_NAME`}.") not used.
";
}

if ($OPTIONS{`createalter`}) {
    foreach (sort keys %alters)  {
       print $alters{$_}.";
";
    }
}

if ($OPTIONS{`summary`} gt 0) {
    $sth = $dbh->prepare(`SHOW GLOBAL STATUS LIKE "Uptime"`);
    $sth->execute();
    my $ua = $sth->fetchrow_hashref();

    print `
################################################################################
# Unused Indexes: `.$n_indexes,"
";

    print `# Unused Tables:  `.$n_tbls."
" if $OPTIONS{`printunusedtbl`};
    print `# Uptime: `.$ua->{`Value`}.` seconds
################################################################################
`;
}

=pod

=head1 NAME

check-unused-keys - Perl Script to check unused indexes using Percona userstat

=head1 SYNOPSIS

 check-unused-keys [OPTIONS]

 Options:
   -d, --databases=<dbname>  Comma-separated list of databases to check
   -h, --help                Display this message and exit
   -H, --hostname=<hostname> The target MySQL server host
   --[no]create-alter        Print ALTER statements for each table
   --ignore-databases        Comma-separated list of databases to ignore
   --ignore-indexes          Comma-separated list of indexes to ignore
                                 db_name.tbl_name.index_name
   --ignore-tables           Comma-separated list of tables to ignore
                                 db_name.tbl_name
   --[no]ignore-primary      Whether or not to ignore PRIMARY KEY
   --[no]ignore-unique       Whether or not to ignore UNIQUE indexes
   --options-file            The options file to use
   --[no]print-unused-tables 
                             Whether or not to print a list of unused tables
                                 (indexes from unused tables are never shown)
   -p, --password=<password> The password of the MySQL user
   -i, --port=<portnum>      The port MySQL is listening on
   -s, --socket=<sockfile>   Use the specified mysql unix socket to connect
   -t, --tables=<tables>     Comma-separated list of tables to evaluate
                                 db_name.tbl_name
   --[no]summary             Display summary information
   -u, --username=<username> The MySQL user used to connect
   -v, --verbose             Increase verbosity level
   -V, --version             Display version information and exit

 Defaults are:

 ATTRIBUTE                  VALUE
 -------------------------- ------------------
 databases                  ALL databases 
 help                       FALSE
 hostname                   localhost
 create-alter               FALSE
 ignore-databases           No default value
 ignore-indexes             No default value
 ignore-primary             TRUE
 ignore-tables              No default value
 ignore-unique              TRUE
 options-file               ~/.my.cnf
 password                   No default value
 print-unused-tables        FALSE
 port                       3306
 socket                     No default value
 summary                    TRUE
 tables                     No Default Value
 username                   No default value
 verbose                    0 (out of 2)
 version                    FALSE

=head1 SYSTEM REQUIREMENTS

check-unused-keys requires the following Perl modules:

  Pod::Usage
  Getopt::Long
  DBI
  DBD::mysql

=head1 BUGS

Please report all bugs and feature requests to
http://code.google.com/p/check-unused-keys

=head1 LICENSE

THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
MERCHANTIBILITY AND FITNESS FOR A PARTICULAR PURPOSE.

This program is free software; you can redistribute it and/or modify it under
the terms of the GNU General Public License as published by the Free Software
Foundation, version 2; OR the Perl Artistic License.  On UNIX and similar
systems, you can issue `man perlgpl` or `man perlartistic` to read these
licenses.

You should have received a copy of the GNU General Public License along with
this program; if not, write to the Free Software Foundation, Inc., 59 Temple
Place, Suite 330, Boston, MA  02111-1307 USA.

=head1 AUTHOR

Ryan Lowe (ryan.a.lowe@percona.com)

=head1 VERSION

This manual page documents 0.0.1 of check-unused-keys

=cut

資料庫設定

mysql> SHOW GLOBAL VARIABLES LIKE "userstat";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| userstat      | ON    |
+---------------+-------+
1 row in set (0.00 sec)

語法

./check-unused-keys --help

使用

./check-unused-keys --host=192.168.1.15 --username=root --password=zhujie1986 --port=3308 --create-alter 

輸出

# Index `medicine`.`inn` (idx_name) not used.
# Index `hrs_ebs`.`qa_theme_item` (idx_hospUuid_parentId) not used.
# Index `hrs_ebs`.`qa_theme_item` (idx_std_hosp_uuid) not used.
# Index `expert`.`doctor_course` (idx_state) not used.
# Index `settle`.`transfer_order` (idx_transfer_no) not used.
# Index `syreserve`.`channel_detail` (idx_module_type_module_business_id) not used.
…………
# Index `cca`.`ip_addr_pool` (idx_ipaddr_id) not used.
# Index `cca`.`ip_addr_pool` (idx_roleid_ipaddr) not used.
ALTER TABLE `area_family_doctor`.`t_agreement` DROP INDEX `idx_patient_idcard_no`;
ALTER TABLE `area_family_doctor`.`t_ops_org_hosp` DROP INDEX `idx_org_id`;
ALTER TABLE `area_family_doctor`.`t_ops_org` DROP INDEX `idx_name`;
ALTER TABLE `area_family_doctor`.`t_ops_sign_area_service` DROP INDEX `idx_org_id`;
ALTER TABLE `area_family_doctor`.`t_pay_record` DROP INDEX `idx_agreement_apply_id`;
…………
ALTER TABLE `working`.`user_profile_bak` DROP INDEX `up_mobile`,
    DROP INDEX `up_call_phone`,
    DROP INDEX `idx_user_name`,
    DROP INDEX `idx_cert_no`,
    DROP INDEX `idx_user_profile_nickname`;
ALTER TABLE `wxgops`.`wx_redpacket` DROP INDEX `idx_red_packet_starttime`,
    DROP INDEX `idx_red_packet_endtime`;

################################################################################
# Unused Indexes: 830
# Uptime: 5037588 seconds
################################################################################

https://www.percona.com/blog/2009/06/26/check-unused-keys-a-tool-to-interact-with-index_statistics/


相關文章