因實際環境上，可能開發因為需求不斷增加，但卻沒有仔細分析檢測過是否重複，此時可以利用percona-toolkit工具內的pt-duplicate-key-checker功能，來找出重複索引與冗餘索引。
安裝方法可參考↓
pt-duplicate-key-checker的使用方法
pt-duplicate-key-checker [OPTION...] [DSN]
使用說明：檢查重複或多餘(冗餘)的索引和外鍵，並打印出信息。
參數說明：加粗的為常用的。
--all-structs：
結構（B樹，哈希等）的索引，默認情況下是禁止的。因為BTREE索引可能會覆蓋作為FULLTEXT索引的列，其實他們不是真正的重複，因為是不同的索引結構。
--ask-pass
提示輸入密碼。
--charset
縮寫-A，字符集設置：utf8，gbk，latin1 …
--[no] clustered
主鍵和第二索引一起是重複的，默認開啟。檢測時的非主鍵索引的多列索引的後綴是一個主鍵最左邊的前綴，則作為一個重複鍵。如：
PRIMARY KEY (`a`)
KEY `b` (`b`,`a`)
SELECT ... WHERE b=1 ORDER BY a;
如果按照這個工具去掉b索引，則會出現filesort，他給的意見是留下b（b）單列索引，把order by a去掉，因為他們的結果是一樣的，a是主鍵。
--config
讀取該配置文件的列表。
--databases
只檢查該列表中的數據庫。
--defaults-file
縮寫-F，讀取Mysql的配置文件，需要絕對路徑。
--engines
縮寫-e，只檢查該列表中指定的表的存儲引擎。
--tables
縮寫-t，只檢查列表中指定的表。
--help
顯示幫助
--host
縮寫-h，連接到主機地址。
--ignore-databases
跳過檢查的某些數據庫。
--ignore-engines
跳過檢查的某些存儲引擎。
--ignore-tables
跳過檢查某些表。
--ignore-order
加了這個參數會報： KEY(a,b)和KEY(b,a)是重複索引，默認關閉。
--key-types
檢查索引的類型：f=foreignkeys, k=keys or fk=both，默認是fk。
--password
縮寫-p，連接mysql時候的密碼。
--pid
連接的PID
--port
縮寫-P，連接時候的端口。
--socket
縮寫-S，連接時候的套接字。
--[no]sql
打印出sql，如果有重複會答應出刪除重複索引的sql。默認開啟。
--[no]summary
打印出索引的統計信息。默認開啟。
--verbose
縮寫-v，打印出所有的索引信息，包括重複索引。
--version
打印版本信息。
#創建一個test_index表，並加入多個索引
mysql >
CREATE TABLE test_index(
ID INT NOT NULL PRIMARY KEY,
A INT NOT NULL,
B INT NOT NULL,
C INT NOT NULL,
D INT NOT NULL,
UNIQUE(ID),
INDEX(ID),
KEY B (`B`),
KEY A (`A`),
KEY ABC (`A`,`B`,`C`),
KEY BCD (`B`,`C`,`D`),
KEY ID_C_D (`ID`,`C`,`D`)
) ENGINE=InnoDB;
#可以看一下表的結構
mysql> DESC test_index;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| ID | int(11) | NO | PRI | NULL | |
| A | int(11) | NO | MUL | NULL | |
| B | int(11) | NO | MUL | NULL | |
| C | int(11) | NO | | NULL | |
| D | int(11) | NO | | NULL | |
+-------+---------+------+-----+---------+-------+
5 rows in set (0.00 sec)
#開始分析剛創建的那張test_index的索引是否重覆
[root@rosalie-mysql02 mysql]# pt-duplicate-key-checker -u root -p1234 --database=tttt
# A software update is available:
# * The current version for Percona::Toolkit is 3.0.5
# ########################################################################
# tttt.test_index
# ########################################################################
# ID_2 is a duplicate of PRIMARY
# Key definitions:
# KEY `ID_2` (`ID`),
# PRIMARY KEY (`ID`),
# Column types:
# `id` int(11) not null
# To remove this duplicate index, execute:
ALTER TABLE `tttt`.`test_index` DROP INDEX `ID_2`;
##紫色的是工具分析出,可以直接在DB執行
#索引ID_2是多餘的,因為ID原本就是主鍵了,又多設一個INDEX變ID_2
# Uniqueness of ID ignored because PRIMARY is a duplicate constraint
# ID is a duplicate of PRIMARY
# Key definitions:
# UNIQUE KEY `ID` (`ID`),
# PRIMARY KEY (`ID`),
# Column types:
# `id` int(11) not null
# To remove this duplicate index, execute:
ALTER TABLE `tttt`.`test_index` DROP INDEX `ID`;
#索引ID是多餘的,因為ID原本就是主鍵了,又多設一個唯一索引ID這欄索引就再重複
# B is a left-prefix of BCD
# Key definitions:
# KEY `B` (`B`),
# KEY `BCD` (`B`,`C`,`D`),
# Column types:
# `b` int(11) not null
# `c` int(11) not null
# `d` int(11) not null
# To remove this duplicate index, execute:
ALTER TABLE `tttt`.`test_index` DROP INDEX `B`;
#索引B是多餘的,因為已經有一個組合索引，同單左前綴索引效用
# A is a left-prefix of ABC
# Key definitions:
# KEY `A` (`A`),
# KEY `ABC` (`A`,`B`,`C`),
# Column types:
# `a` int(11) not null
# `b` int(11) not null
# `c` int(11) not null
# To remove this duplicate index, execute:
ALTER TABLE `tttt`.`test_index` DROP INDEX `A`;
#索引A是多餘的,因為已經有一個組合索引，同單左前綴索引效用
# ########################################################################
# Summary of indexes
# ########################################################################
# Size Duplicate Indexes 16 #Size重複索引16
# Total Duplicate Indexes 4 #重複索引總數
# Total Indexes 9
#這是在未刪除多餘索引前查看表創建的內容
mysql> show create table tttt.test_index;
| test_index | CREATE TABLE `test_index` (
`ID` int(11) NOT NULL,
`A` int(11) NOT NULL,
`B` int(11) NOT NULL,
`C` int(11) NOT NULL,
`D` int(11) NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `ID` (`ID`),
KEY `ID_2` (`ID`),
KEY `B` (`B`),
KEY `A` (`A`),
KEY `ABC` (`A`,`B`,`C`),
KEY `BCD` (`B`,`C`,`D`),
KEY `ID_C_D` (`ID`,`C`,`D`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
#依上面工具分析的結果，將多餘的INDEX刪除後，再重新查看
mysql> show create table tttt.test_index;
| test_index | CREATE TABLE `test_index` (
`ID` int(11) NOT NULL,
`A` int(11) NOT NULL,
`B` int(11) NOT NULL,
`C` int(11) NOT NULL,
`D` int(11) NOT NULL,
PRIMARY KEY (`ID`),
KEY `ABC` (`A`,`B`,`C`),
KEY `BCD` (`B`,`C`,`D`),
KEY `ID_C_D` (`ID`,`C`,`D`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
沒有留言:
張貼留言