- A+
所属分类：MySQL
mysqlindexcheck 工具是用来识别潜在的冗余表索引的。读取一个或多个表的索引，识别出重复的和潜在冗余的索引。
如果只指定数据库名，那么检查数据库中的所有表。如果要检测特定的某个表索引，以这个格式定义表名db.table。
除了这些库的表mysql, INFORMATION_SCHEMA, performance_schema，可以扫描所有其他库。
根据不同的索引类型，适用于以下规则进行索引比较（指定为idx_a和idx_b）：
- 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) 等效。
为了显示每个表最好或最坏的非主键索引，使用--best 或 --worst选项。
格式
- grid (default)
- csv
- tab
- sql
- vertical
不解释了，前面有，不清楚看前面的内容。
注意：--best 和 --worst 列表选项不显示SQL 语句。
选项
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
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 |
实例
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 |
# 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权限。
微信公众号
扫一扫关注运维生存时间公众号，获取最新技术文章~
