mysqlindexcheck 工具是用来识别潜在的冗余表索引的。读取一个或多个表的索引，识别出重复的和潜在冗余的索引。
如果只指定数据库名，那么检查数据库中的所有表。如果要检测特定的某个表索引，以这个格式定义表名db.table。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlindexcheck/
除了这些库的表mysql, INFORMATION_SCHEMA, performance_schema，可以扫描所有其他库。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlindexcheck/
根据不同的索引类型，适用于以下规则进行索引比较（指定为idx_a和idx_b）：文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlindexcheck/
- BTREE 当且仅当idx_b中的第一个n列也出现在idx_a，那么idx_b是idx_a的重复的。顺序和唯一的。
- HASH 当且仅当它们包含相同的列相同一顺序，那么idx_a和idx_b是重复的。唯一的。
- SPATIAL 当且仅当它们包含相同的列，那么idx_a和idx_b是重复的。
- FULLTEXT 当且仅当idx_b包含的所有列，也idx_a也包含，那么idx_b与idx_a是冗余的。顺序。
如果要看到DROP语句来删除冗余索引，可以指定 --show-drops 选项。检查现有的索引，使用--verbose 来打印出与CREATE INDEX (或者 ALTER TABLE) 等效。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlindexcheck/
为了显示每个表最好或最坏的非主键索引，使用--best 或 --worst选项。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlindexcheck/
格式
- grid (default)
- csv
- tab
- sql
- vertical
不解释了，前面有，不清楚看前面的内容。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlindexcheck/
注意：--best 和 --worst 列表选项不显示SQL 语句。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlindexcheck/
选项
MySQL Utilities mysqlindexcheck version 1.5.3
License type: GPLv2
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
--license display program's license and exit
--server=SERVER connection information for the server in the form:
<user>[:<password>]@<host>[:<port>][:<socket>] or
<login-path>[:<port>][:<socket>] or <config-
path>[<[group]>].
--ssl-ca=SSL_CA The path to a file that contains a list of trusted SSL
CAs.
--ssl-cert=SSL_CERT The name of the SSL certificate file to use for
establishing a secure connection.
--ssl-key=SSL_KEY The name of the SSL key file to use for establishing a
secure connection.
-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。默认是5条。
--worst=WORST limit index statistics to the worst N indexes。默认是5条。
-r, --report-indexes reports if a table has neither UNIQUE indexes nor a
PRIMARY key
-v, --verbose control how much information is displayed. e.g., -v =
verbose, -vv = more verbose, -vvv = debug
实例
# mysqlindexcheck --server=instance_3306 --show-drops --show-indexes --stats --report-indexes -vvv ttlsa_com # Source on localhost: ... connected. # Checking indexes... # Getting indexes for ttlsa_com.bbs_categories # Showing indexes from ttlsa_com.bbs_categories: # +------------+-----------------+----------+--------+---------+----------------+-----------+ | database | table | name | type | unique | accepts nulls | columns | +------------+-----------------+----------+--------+---------+----------------+-----------+ | ttlsa_com | bbs_categories | PRIMARY | BTREE | True | False | cid, pid | +------------+-----------------+----------+--------+---------+----------------+-----------+ # # Table ttlsa_com.bbs_categories has no duplicate nor redundant indexes. # WARNING: Not enough data to calculate best/worst indexes. # # Getting indexes for ttlsa_com.bbs_comments # Showing indexes from ttlsa_com.bbs_comments: # +------------+---------------+----------+--------+---------+----------------+---------------+ | database | table | name | type | unique | accepts nulls | columns | +------------+---------------+----------+--------+---------+----------------+---------------+ | ttlsa_com | bbs_comments | PRIMARY | BTREE | True | False | id, fid, uid | +------------+---------------+----------+--------+---------+----------------+---------------+ # # Table ttlsa_com.bbs_comments has no duplicate nor redundant indexes. # WARNING: Not enough data to calculate best/worst indexes. # # Getting indexes for ttlsa_com.bbs_favorites # Showing indexes from ttlsa_com.bbs_favorites: # +------------+----------------+----------+--------+---------+----------------+----------+ | database | table | name | type | unique | accepts nulls | columns | +------------+----------------+----------+--------+---------+----------------+----------+ | ttlsa_com | bbs_favorites | PRIMARY | BTREE | True | False | id, uid | | ttlsa_com | bbs_favorites | uid | BTREE | False | False | uid | +------------+----------------+----------+--------+---------+----------------+----------+ # # Table ttlsa_com.bbs_favorites has no duplicate nor redundant indexes. # WARNING: Not enough data to calculate best/worst indexes. # # Getting indexes for ttlsa_com.bbs_forums # Showing indexes from ttlsa_com.bbs_forums: # +------------+-------------+-------------+--------+---------+----------------+----------------+ | database | table | name | type | unique | accepts nulls | columns | +------------+-------------+-------------+--------+---------+----------------+----------------+ | ttlsa_com | bbs_forums | PRIMARY | BTREE | True | False | fid, cid, uid | | ttlsa_com | bbs_forums | updatetime | BTREE | False | True | updatetime | | ttlsa_com | bbs_forums | ord | BTREE | False | False | ord | +------------+-------------+-------------+--------+---------+----------------+----------------+ # # Table ttlsa_com.bbs_forums has no duplicate nor redundant indexes. # WARNING: Not enough data to calculate best/worst indexes. # # Getting indexes for ttlsa_com.bbs_links # Showing indexes from ttlsa_com.bbs_links: # +------------+------------+----------+--------+---------+----------------+----------+ | database | table | name | type | unique | accepts nulls | columns | +------------+------------+----------+--------+---------+----------------+----------+ | ttlsa_com | bbs_links | PRIMARY | BTREE | True | False | id | +------------+------------+----------+--------+---------+----------------+----------+ # # Table ttlsa_com.bbs_links has no duplicate nor redundant indexes. # WARNING: Not enough data to calculate best/worst indexes. # # Getting indexes for ttlsa_com.bbs_notifications # Showing indexes from ttlsa_com.bbs_notifications: # +------------+--------------------+----------+--------+---------+----------------+------------+ | database | table | name | type | unique | accepts nulls | columns | +------------+--------------------+----------+--------+---------+----------------+------------+ | ttlsa_com | bbs_notifications | PRIMARY | BTREE | True | False | nid, nuid | +------------+--------------------+----------+--------+---------+----------------+------------+ # # Table ttlsa_com.bbs_notifications has no duplicate nor redundant indexes. # WARNING: Not enough data to calculate best/worst indexes. # # Getting indexes for ttlsa_com.bbs_page # Showing indexes from ttlsa_com.bbs_page: # +------------+-----------+----------+--------+---------+----------------+----------+ | database | table | name | type | unique | accepts nulls | columns | +------------+-----------+----------+--------+---------+----------------+----------+ | ttlsa_com | bbs_page | PRIMARY | BTREE | True | False | pid | +------------+-----------+----------+--------+---------+----------------+----------+ # # Table ttlsa_com.bbs_page has no duplicate nor redundant indexes. # WARNING: Not enough data to calculate best/worst indexes. # # Getting indexes for ttlsa_com.bbs_settings # Showing indexes from ttlsa_com.bbs_settings: # +------------+---------------+----------+--------+---------+----------------+------------------+ | database | table | name | type | unique | accepts nulls | columns | +------------+---------------+----------+--------+---------+----------------+------------------+ | ttlsa_com | bbs_settings | PRIMARY | BTREE | True | False | id, title, type | +------------+---------------+----------+--------+---------+----------------+------------------+ # # Table ttlsa_com.bbs_settings has no duplicate nor redundant indexes. # WARNING: Not enough data to calculate best/worst indexes. # # Getting indexes for ttlsa_com.bbs_tags # Showing indexes from ttlsa_com.bbs_tags: # +------------+-----------+------------+--------+---------+----------------+------------+ | database | table | name | type | unique | accepts nulls | columns | +------------+-----------+------------+--------+---------+----------------+------------+ | ttlsa_com | bbs_tags | PRIMARY | BTREE | True | False | tag_id | | ttlsa_com | bbs_tags | tag_title | BTREE | True | False | tag_title | +------------+-----------+------------+--------+---------+----------------+------------+ # # Table ttlsa_com.bbs_tags has no duplicate nor redundant indexes. # WARNING: Not enough data to calculate best/worst indexes. # # Getting indexes for ttlsa_com.bbs_tags_relation # Showing indexes from ttlsa_com.bbs_tags_relation: # +------------+--------------------+---------+--------+---------+----------------+----------+ | database | table | name | type | unique | accepts nulls | columns | +------------+--------------------+---------+--------+---------+----------------+----------+ | ttlsa_com | bbs_tags_relation | tag_id | BTREE | False | False | tag_id | | ttlsa_com | bbs_tags_relation | fid | BTREE | False | True | fid | +------------+--------------------+---------+--------+---------+----------------+----------+ # # Table `ttlsa_com`.`bbs_tags_relation` does not contain neither a PRIMARY nor UNIQUE key. # The following indexes for table ttlsa_com.bbs_tags_relation contain the clustered index and might be redundant: # CREATE INDEX `tag_id` ON `ttlsa_com`.`bbs_tags_relation` (`tag_id`) USING BTREE # CREATE INDEX `fid` ON `ttlsa_com`.`bbs_tags_relation` (`fid`) USING BTREE # # DROP/ADD statements: # ALTER TABLE `ttlsa_com`.`bbs_tags_relation` DROP INDEX `tag_id`, ADD INDEX `tag_id` (tag_id); ALTER TABLE `ttlsa_com`.`bbs_tags_relation` DROP INDEX `fid`, ADD INDEX `fid` (fid); # # WARNING: Not enough data to calculate best/worst indexes. # # Getting indexes for ttlsa_com.bbs_users # Showing indexes from ttlsa_com.bbs_users: # +------------+------------+----------+--------+---------+----------------+------------------+ | database | table | name | type | unique | accepts nulls | columns | +------------+------------+----------+--------+---------+----------------+------------------+ | ttlsa_com | bbs_users | PRIMARY | BTREE | True | False | uid, group_type | +------------+------------+----------+--------+---------+----------------+------------------+ # # Table ttlsa_com.bbs_users has no duplicate nor redundant indexes. # WARNING: Not enough data to calculate best/worst indexes. # # Getting indexes for ttlsa_com.bbs_user_follow # Showing indexes from ttlsa_com.bbs_user_follow: # +------------+------------------+----------+--------+---------+----------------+-----------------------------+ | database | table | name | type | unique | accepts nulls | columns | +------------+------------------+----------+--------+---------+----------------+-----------------------------+ | ttlsa_com | bbs_user_follow | PRIMARY | BTREE | True | False | follow_id, uid, follow_uid | +------------+------------------+----------+--------+---------+----------------+-----------------------------+ # # Table ttlsa_com.bbs_user_follow has no duplicate nor redundant indexes. # WARNING: Not enough data to calculate best/worst indexes. # # Getting indexes for ttlsa_com.bbs_user_groups # Showing indexes from ttlsa_com.bbs_user_groups: # +------------+------------------+----------+--------+---------+----------------+------------------+ | database | table | name | type | unique | accepts nulls | columns | +------------+------------------+----------+--------+---------+----------------+------------------+ | ttlsa_com | bbs_user_groups | PRIMARY | BTREE | True | False | gid, group_type | +------------+------------------+----------+--------+---------+----------------+------------------+ # # Table ttlsa_com.bbs_user_groups has no duplicate nor redundant indexes. # WARNING: Not enough data to calculate best/worst indexes. # # Getting indexes for ttlsa_com.data # Showing indexes from ttlsa_com.data: # +------------+--------+----------+--------+---------+----------------+----------+ | database | table | name | type | unique | accepts nulls | columns | +------------+--------+----------+--------+---------+----------------+----------+ | ttlsa_com | data | PRIMARY | BTREE | True | False | value | | ttlsa_com | data | id | BTREE | False | False | id | +------------+--------+----------+--------+---------+----------------+----------+ # # Table ttlsa_com.data has no duplicate nor redundant indexes. # WARNING: Not enough data to calculate best/worst indexes. # # Getting indexes for ttlsa_com.t_data # Showing indexes from ttlsa_com.t_data: # +------------+---------+----------+--------+---------+----------------+----------+ | database | table | name | type | unique | accepts nulls | columns | +------------+---------+----------+--------+---------+----------------+----------+ | ttlsa_com | t_data | PRIMARY | BTREE | True | False | value | | ttlsa_com | t_data | id | BTREE | False | False | id | +------------+---------+----------+--------+---------+----------------+----------+ # # Table ttlsa_com.t_data has no duplicate nor redundant indexes. # WARNING: Not enough data to calculate best/worst indexes. # # ...done.
权限
需要对mysql数据库和需要检测的库(表)的SELECT权限。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlindexcheck/ 文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlindexcheck/
我的微信
微信公众号
扫一扫关注运维生存时间公众号，获取最新技术文章~
评论