檢測不再使用的索引–check-unused-keys
指令碼
#!/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/
相關文章
- Oracle 索引的使用情況檢視Oracle索引
- 物化檢視上使用bitmap索引索引
- 使用index_stats檢視檢視索引效率Index索引
- DB2檢視索引的使用情況DB2索引
- 使用treedump事件檢視索引結構事件索引
- 蘋果電池新政策:不再需要進行額外的電池檢測蘋果
- Laravel 使用 xunsearch(迅搜)全文檢索引擎Laravel索引
- 【Oracle】如何查詢檢視時使用索引Oracle索引
- MSSQL索引檢視(indexed view)之簡述及使用SQL索引IndexView
- 【Mongo】MongoDB索引管理-索引的建立、檢視、刪除MongoDB索引
- mysql 檢視索引MySql索引
- 索引的使用索引
- 讓音訊實時檢測不再那麼難 網易易盾音訊檢測助力音訊業健康有序發展音訊
- mysql檢視錶建立的索引MySql索引
- [NLP]OpenNLP語言檢測器的使用
- 【索引】oracle查詢使用索引和不使用索引的比較索引Oracle
- 【Oracle】-【ROWNUM與索引】-索引對ROWNUM檢索的影響Oracle索引
- 【索引】使用索引分析快速得到索引的基本資訊索引
- 物體檢測實戰:使用 OpenCV 進行 YOLO 物件檢測OpenCVYOLO物件
- php程式碼檢測工具使用PHP
- 使用dbverify檢測物理損壞
- mongodb 如何檢視索引MongoDB索引
- 使用深度學習的交通標誌檢測深度學習
- 微信qq域名檢測系統的使用教程
- mysql 建立索引的方法--建立檢視MySql索引
- 使用python和xapian構建高速檢索引擎PythonAPI索引
- 人臉檢測的harr檢測函式函式
- 使用Dice loss實現清晰的邊界檢測
- 使用深度學習檢測瘧疾深度學習
- Oracle 定期檢查意義不大的索引Oracle索引
- 檢查是否存在truncate或者rebuild的索引Rebuild索引
- Oracle 以月為單位檢查索引的使用情況(郵件反饋)Oracle索引
- mysql建立索引和檢視MySql索引
- Oracle索引HINT的使用Oracle索引
- JavaScript 使用者代理檢測(瀏覽器型別檢測,執行平臺檢測等) 規範程式碼JavaScript瀏覽器型別
- 頁面流暢度不再是謎!除錯神器開箱即用,Flutter FPS檢測工具除錯Flutter
- 使用Elasticsearch的動態索引和索引優化Elasticsearch索引優化
- 使用聚集索引和非聚集索引的區別索引