脚本

#!/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\n";
    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\n";
    print "\n";
    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."\n" 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.\n"  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} .= ",\n    DROP INDEX `".$row->{'INDEX_NAME'}.'`';
        }
    }
    print "# Index $tbl (".$row->{'INDEX_NAME'}.") not used.\n";
}

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

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,"\n";

    print '# Unused Tables:  '.$n_tbls."\n" 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/