mysqlindexcheck

mysqlindexcheck是来检查库中是否存在冗余索引,并生成删除冗余索引sql,支持BTRR、HASH、SPATIAL、FULLTEXT

$ ./mysqlindexcheck --help
Usage: mysqlindexcheck --server=user:pass@host:port:socket db1.table1 db2 db3.table2
 
mysqlindexcheck - check for duplicate or redundant indexes
 
Options:
  --version             show program's version number and exit
  --help                display a help message and exit
  --server=SERVER       connection information for the server in the form:
                        <user>[:<password>]@<host>[:<port>][:<socket>] or
                        <login-path>[:<port>][:<socket>].
  -d, --show-drops      display DROP statements for dropping indexes
  -i, --show-indexes    display indexes for each table
  -s, --skip            skip tables that do not exist
  -f FORMAT, --format=FORMAT
                        display the list of indexes per table in either sql,
                        grid (default), tab, csv, or vertical format
  --stats               show index performance statistics
  --best=BEST           limit index statistics to the best N indexes
  --worst=WORST         limit index statistics to the worst N indexes
  -v, --verbose         control how much information is displayed. e.g., -v =
                        verbose, -vv = more verbose, -vvv = debug
 
root@luoxuan 02:50:35>show create table test_index \G;
*************************** 1. row ***************************
       Table: test_index
Create Table: CREATE TABLE `test_index` (
  `a` bigint(20) NOT NULL AUTO_INCREMENT,
  `b` bigint(20) NOT NULL,
  `c` varchar(1000) DEFAULT NULL,
  `d` bigint(20) NOT NULL,
  PRIMARY KEY (`a`),
  KEY `ind_b` (`b`),
  KEY `ind_dc` (`b`,`d`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

eg1:展示luoxuan库中的冗余索引

$ ./mysqlindexcheck --server=root@localhost luoxuan
# Source on localhost: ... connected.
# The following indexes are duplicates or redundant for table luoxuan.test_index:
#
CREATE INDEX ind_b ON luoxuan.test_index (b) USING BTREE
#     may be redundant or duplicate of:
CREATE INDEX ind_dc ON luoxuan.test_index (b, d) USING BTREE

eg2:展示luoxuan库中的冗余索引,并打印drop索引语句、表索引情况、索引统计信息

$ ./mysqlindexcheck --server=root@localhost luoxuan -d -i --stats 
# Source on localhost: ... connected.
# Showing indexes from luoxuan.test_index:
#
+-----------+-------------+----------+--------+----------+
| database  | table       | name     | type   | columns  |
+-----------+-------------+----------+--------+----------+
| luoxuan   | test_index  | PRIMARY  | BTREE  | a        |
| luoxuan   | test_index  | ind_b    | BTREE  | b        |
| luoxuan   | test_index  | ind_dc   | BTREE  | b, d     |
+-----------+-------------+----------+--------+----------+
#
# The following indexes are duplicates or redundant for table luoxuan.test_index:
#
CREATE INDEX ind_b ON luoxuan.test_index (b) USING BTREE
#     may be redundant or duplicate of:
CREATE INDEX ind_dc ON luoxuan.test_index (b, d) USING BTREE
#
# DROP statements:
#
ALTER TABLE luoxuan.test_index DROP INDEX ind_b;
#
# Showing indexes from luoxuan.test_index2:
#
+-----------+--------------+----------+--------+----------+
| database  | table        | name     | type   | columns  |
+-----------+--------------+----------+--------+----------+
| luoxuan   | test_index2  | PRIMARY  | BTREE  | a        |
| luoxuan   | test_index2  | ind_bb   | BTREE  | b        |
+-----------+--------------+----------+--------+----------+

注意:如果表中没有数据,–stats不起作用

eg3:如果表中有索引冗余,只保留效率最好一个

root@luoxuan 03:23:11>show index from test_index;
+------------+------------+----------+--------------+-------------+-----------+-------------+------------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Index_type |
+------------+------------+----------+--------------+-------------+-----------+-------------+------------+
| test_index |          0 | PRIMARY  |            1 | a           | A         |      500124 | BTREE      |
| test_index |          1 | ind_b    |            1 | b           | A         |      500124 | BTREE      |
| test_index |          1 | ind_dc   |            1 | b           | A         |      500124 | BTREE      |
| test_index |          1 | ind_dc   |            2 | d           | A         |      500124 | BTREE      |
+------------+------------+----------+--------------+-------------+-----------+-------------+------------+
4 rows in set (0.00 sec)
 
root@luoxuan 03:23:13>show index from test_index7;
+-------------+------------+-----------+--------------+-------------+-----------+-------------+------------+
| Table       | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Index_type |
+-------------+------------+-----------+--------------+-------------+-----------+-------------+------------+
| test_index7 |          0 | PRIMARY   |            1 | a           | A         |      500124 | BTREE      |
| test_index7 |          1 | ind_b     |            1 | b           | A         |      500124 | BTREE      |
| test_index7 |          1 | index_bc  |            1 | b           | A         |      500124 | BTREE      |
| test_index7 |          1 | index_bc  |            2 | c           | A         |      500124 | BTREE      |
| test_index7 |          1 | index_bcd |            1 | b           | A         |      500124 | BTREE      |
| test_index7 |          1 | index_bcd |            2 | c           | A         |      500124 | BTREE      |
| test_index7 |          1 | index_bcd |            3 | d           | A         |      500124 | BTREE      |
+-------------+------------+-----------+--------------+-------------+-----------+-------------+------------+
7 rows in set (0.01 sec)
 
$ ./mysqlindexcheck --server=root@localhost luoxuan --stats --best=1 -d
# Source on localhost: ... connected.
# The following indexes are duplicates or redundant for table luoxuan.test_index:
#
CREATE INDEX ind_b ON luoxuan.test_index (b) USING BTREE
#     may be redundant or duplicate of:
CREATE INDEX ind_dc ON luoxuan.test_index (b, d) USING BTREE
#
# DROP statements:
#
ALTER TABLE luoxuan.test_index DROP INDEX ind_b;
#
#
# Showing the top best performing indexes from luoxuan.test_index:
#
+-----------+-------------+---------+---------+-----------+--------------+--------------+------------+----------+
| database  | table       | name    | column  | sequence  | num columns  | cardinality  | est. rows  | percent  |
+-----------+-------------+---------+---------+-----------+--------------+--------------+------------+----------+
| luoxuan   | test_index  | ind_dc  | d       | 2         | 2            | 500124       | 500124     | 100.00   |
+-----------+-------------+---------+---------+-----------+--------------+--------------+------------+----------+
# The following indexes are duplicates or redundant for table luoxuan.test_index7:
#
CREATE INDEX ind_b ON luoxuan.test_index7 (b) USING BTREE
#     may be redundant or duplicate of:
CREATE INDEX index_bc ON luoxuan.test_index7 (b, c(255)) USING BTREE
#
CREATE INDEX index_bc ON luoxuan.test_index7 (b, c(255)) USING BTREE
#     may be redundant or duplicate of:
CREATE INDEX index_bcd ON luoxuan.test_index7 (b, c(255), d) USING BTREE
#
# DROP statements:
#
ALTER TABLE luoxuan.test_index7 DROP INDEX ind_b;
ALTER TABLE luoxuan.test_index7 DROP INDEX index_bc;
#
#
# Showing the top best performing indexes from luoxuan.test_index7:
#
+-----------+--------------+------------+---------+-----------+--------------+--------------+------------+----------+
| database  | table        | name       | column  | sequence  | num columns  | cardinality  | est. rows  | percent  |
+-----------+--------------+------------+---------+-----------+--------------+--------------+------------+----------+
| luoxuan   | test_index7  | index_bcd  | b       | 1         | 3            | 500124       | 500124     | 100.00   |
+-----------+--------------+------------+---------+-----------+--------------+--------------+------------+----------+

Post a Comment