脚本
#!/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/
