檢測不再使用的索引–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/

Oracle 索引的使用情況檢視
2017-05-10
Oracle索引
物化檢視上使用bitmap索引
2009-07-17
索引
使用index_stats檢視檢視索引效率
2015-11-10
Index索引
DB2檢視索引的使用情況
2018-09-21
DB2索引
使用treedump事件檢視索引結構
2013-02-19
事件索引
蘋果電池新政策：不再需要進行額外的電池檢測
2018-01-04
蘋果
Laravel 使用 xunsearch（迅搜）全文檢索引擎
2021-10-30
Laravel索引
【Oracle】如何查詢檢視時使用索引
2012-06-12
Oracle索引
MSSQL索引檢視（indexed view）之簡述及使用
2018-01-11
SQL索引IndexView
【Mongo】MongoDB索引管理－索引的建立、檢視、刪除
2018-05-01
MongoDB索引
mysql 檢視索引
2010-07-14
MySql索引
讓音訊實時檢測不再那麼難網易易盾音訊檢測助力音訊業健康有序發展
2019-07-02
音訊
[NLP]OpenNLP語言檢測器的使用
2020-12-15
mysql檢視錶建立的索引
2017-05-27
MySql索引
【索引】oracle查詢使用索引和不使用索引的比較
2012-04-25
索引Oracle
【Oracle】-【ROWNUM與索引】-索引對ROWNUM檢索的影響
2013-08-02
Oracle索引
物體檢測實戰：使用 OpenCV 進行 YOLO 物件檢測
2022-01-10
OpenCVYOLO物件
【索引】使用索引分析快速得到索引的基本資訊
2009-11-27
索引
php程式碼檢測工具使用
2022-04-02
PHP
使用dbverify檢測物理損壞
2009-02-25
使用深度學習的交通標誌檢測
2019-07-02
深度學習
微信qq域名檢測系統的使用教程
2021-03-24
mongodb 如何檢視索引
2021-09-11
MongoDB索引
mysql 建立索引的方法--建立檢視
2014-04-09
MySql索引
使用python和xapian構建高速檢索引擎
2014-01-17
PythonAPI索引
人臉檢測的harr檢測函式
2013-07-17
函式
使用Dice loss實現清晰的邊界檢測
2021-07-08
使用深度學習檢測瘧疾
2019-05-24
深度學習
Oracle 定期檢查意義不大的索引
2015-01-08
Oracle索引
檢查是否存在truncate或者rebuild的索引
2010-07-10
Rebuild索引
Oracle 以月為單位檢查索引的使用情況(郵件反饋)
2015-01-17
Oracle索引
mysql建立索引和檢視
2018-05-25
MySql索引
Oracle索引HINT的使用
2011-11-08
Oracle索引
JavaScript 使用者代理檢測（瀏覽器型別檢測，執行平臺檢測等）規範程式碼
2020-11-03
JavaScript瀏覽器型別
頁面流暢度不再是謎！除錯神器開箱即用，Flutter FPS檢測工具
2021-04-06
除錯Flutter
使用netstat檢測及監測網路連線
2014-09-12
使用Elasticsearch的動態索引和索引優化
2019-03-28
Elasticsearch索引優化
使用聚集索引和非聚集索引的區別
2009-12-25
索引

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

指令碼

資料庫設定

語法

使用

輸出

相關文章