检测不再使用的索引--check-unused-keys-白红宇

脚本

#!/usr/bin/perl################################################################################# check-unused-keys - Perl Script to check unused indexes# # @author Ryan Lowe 
    
     ################################################################################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 optsmy $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 versionsif ($OPTIONS{'version'}) {    print "$VERSION\n";    exit 0;}# Help if asked for or no check givenpod2usage(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 connectionmy $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 rowswhile (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 NAMEcheck-unused-keys - Perl Script to check unused indexes using Percona userstat=head1 SYNOPSIS check-unused-keys [OPTIONS] Options:   -d, --databases=
     
        Comma-separated list of databases to check   -h, --help                Display this message and exit   -H, --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=
       
         The password of the MySQL user   -i, --port=
        
               The port MySQL is listening on   -s, --socket=
         
           Use the specified mysql unix socket to connect -t, --tables=
          
            Comma-separated list of tables to evaluate db_name.tbl_name --[no]summary Display summary information -u, --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 REQUIREMENTScheck-unused-keys requires the following Perl modules: Pod::Usage Getopt::Long DBI DBD::mysql=head1 BUGSPlease report all bugs and feature requests tohttp://code.google.com/p/check-unused-keys=head1 LICENSETHIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIEDWARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OFMERCHANTIBILITY AND FITNESS FOR A PARTICULAR PURPOSE.This program is free software; you can redistribute it and/or modify it underthe terms of the GNU General Public License as published by the Free SoftwareFoundation, version 2; OR the Perl Artistic License. On UNIX and similarsystems, you can issue `man perlgpl' or `man perlartistic' to read theselicenses.You should have received a copy of the GNU General Public License along withthis program; if not, write to the Free Software Foundation, Inc., 59 TemplePlace, Suite 330, Boston, MA 02111-1307 USA.=head1 AUTHORRyan Lowe (ryan.a.lowe@percona.com)=head1 VERSIONThis 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################################################################################