MySQL中冗余和重復(fù)索引的區(qū)別說明
MySQL允許在單個(gè)列上創(chuàng)建多個(gè)索引，無論是有意還是無意，MySQL需要單獨(dú)維護(hù)這些重復(fù)索引，優(yōu)化器在優(yōu)化查詢時(shí)也需要逐個(gè)考慮這會(huì)影響MySQL的性能
概念闡述
重復(fù)索引： 在相同的列上按照相同的順序創(chuàng)建的相同類型的索引。應(yīng)該避免創(chuàng)建這樣的重復(fù)索引，發(fā)現(xiàn)之后也應(yīng)該立即移除。
冗余索引： 兩個(gè)索引按照相同的順序覆蓋了相同的列。
創(chuàng)建的原因
一般來說，我們有時(shí)候會(huì)在不經(jīng)意間創(chuàng)建了重復(fù)索引，例如下面的例子：
CREATE TABLE test( ID INT NOT NULL PRIMARY KEY, A INT NOT NULL, B INT NOT NULL, UNIQUE(ID), INDEX(ID) )ENGINE=InnoDB;
因?yàn)镸ySQL的唯一限制和主鍵限制都是通過索引實(shí)現(xiàn)的，所以事實(shí)上使用如上代碼創(chuàng)建的表實(shí)際上會(huì)在ID列上創(chuàng)建3個(gè)索引。通常沒有理由這樣做，除非是在同一列上為了滿足不同的查詢需求創(chuàng)建不同類型的索引。比如KEY(col)和FULLTEXT KEY(col)兩種索引。
冗余索引和重復(fù)索引有些不同。如果創(chuàng)建了索引（A,B），再創(chuàng)建索引（A）就是冗余索引，因?yàn)檫@只是前一個(gè)索引的前綴索引。但是如果創(chuàng)建了索引（B,A），則不是冗余索引，索引（B）也不是冗余索引，因?yàn)樗皇撬饕ˋ,B）的最左前綴列。除此之外，不同類型的索引也不會(huì)是B樹索引的冗余索引，無論覆蓋的索引列是什么。
在大多數(shù)情況下都不需要使用冗余索引，應(yīng)該盡可能拓展已有的索引而不是創(chuàng)建新的索引。但有時(shí)候出于性能的考慮，比如拓展已有的索引會(huì)使得其變得太大，從而影響其他使用該索引的查詢的性能。
影響
創(chuàng)建冗余索引作為覆蓋索引可以提高我們對(duì)于部分查詢的QPS，但是存在兩個(gè)索引也有缺點(diǎn)，即索引成本更高。
當(dāng)表中的索引越來越多時(shí)，表的插入速度會(huì)變慢。一般而言，增加新索引將會(huì)導(dǎo)致INSERT、UPDATE、DELETE等操作的速度變慢，特別是當(dāng)新增索引之后導(dǎo)致達(dá)到了內(nèi)存的瓶頸的時(shí)候。
解決冗余索引和重復(fù)索引的方法很簡單，刪除這些索引就可以 ，但是首先要做的就是找出這樣的索引。
補(bǔ)充：MySQL中重復(fù)索引和重復(fù)外鍵清理
MySQL允許在相同列上創(chuàng)建重復(fù)的索引，但這樣做對(duì)數(shù)據(jù)庫卻是有害而無利的，需要定期檢查此類重復(fù)索引以改善數(shù)據(jù)庫性能。
可減少磁盤空間占用、減少磁盤IO、減少優(yōu)化器優(yōu)化查詢時(shí)需要比較的索引個(gè)數(shù)、減少數(shù)據(jù)庫維護(hù)冗余索引的各類開銷、提高數(shù)據(jù)庫性能（插入、更新、刪除）
重復(fù)索引檢測
pt-duplicate-key-checker：通過SHOW CREATE TABLE輸出的表定義檢測MySQL表中重復(fù)或者冗余的索引或外鍵
可以檢測到的冗余/重復(fù)索引類型：若某個(gè)索引和另外某個(gè)索引以同樣的順序包含同樣的列，或者該索引包含的列是另外某個(gè)索引的最左前綴列，則被認(rèn)為是重復(fù)/冗余的索引。
默認(rèn)情況下只在同類型的索引間（如BTREE索引）進(jìn)行比較，不同類型的索引即使符合上述描述也不會(huì)被認(rèn)為是重復(fù)/冗余,但這一行為可以通過參數(shù)改變。
除此之外，還可檢測重復(fù)的外鍵，即引用的表和列均相同的外鍵。對(duì)于聚簇索引的表，在輔助索引后添加主鍵列的索引也被認(rèn)為是冗余的，因?yàn)檫@種情況下，輔助索引末尾本身就包含有主鍵信息。
基本用法以及樣例輸出如下
[root@VM_8_180_centos packages]# pt-duplicate-key-checker A=utf8, F=/etc/my.cnf, h=localhost, u=root, P=3306 –ask-pass
樣例輸出：
# ######################################################################## # dcf.privilege # ######################################################################## # Uniqueness of UQI_IDX_1 ignored because PRIMARY is a duplicate constraint # UQI_IDX_1 is a duplicate of PRIMARY # Key definitions: # UNIQUE KEY `UQI_IDX_1` (`privilege_id`), # PRIMARY KEY (`privilege_id`), # Column types: # `privilege_id` varchar(50) collate utf8_bin not null comment '權(quán)限id' # To remove this duplicate index, execute: ALTER TABLE `dcf`.`privilege` DROP INDEX `UQI_IDX_1`; # ######################################################################## # dcf.t_game_config # ######################################################################## # Uniqueness of pkey ignored because PRIMARY is a duplicate constraint # pkey is a duplicate of PRIMARY # Key definitions: # UNIQUE KEY `pkey` (`pkey`) # PRIMARY KEY (`pkey`), # Column types: # `pkey` bigint(20) not null auto_increment # To remove this duplicate index, execute: ALTER TABLE `dcf`.`t_game_config` DROP INDEX `pkey`; # ######################################################################## # dcf.t_project_institution # ######################################################################## # index_1 is a left-prefix of index_2 # Key definitions: # KEY `index_1` (`project_id`), # KEY `index_2` (`project_id`,`institution_id`,`delete_flag`) # Column types: # `project_id` bigint(20) not null comment '項(xiàng)目id' # `institution_id` varchar(20) not null comment '機(jī)構(gòu)id' # `delete_flag` tinyint(4) not null # To remove this duplicate index, execute: ALTER TABLE `dcf`.`t_project_institution` DROP INDEX `index_1`; # ######################################################################## # dcf_commons.bank_cnaps # ######################################################################## # idx is a duplicate of PRIMARY # Key definitions: # KEY `idx` (`cnaps`) # PRIMARY KEY (`cnaps`), # Column types: # `cnaps` varchar(255) not null comment '電子聯(lián)行號(hào)' # To remove this duplicate index, execute: ALTER TABLE `dcf_commons`.`bank_cnaps` DROP INDEX `idx`; # ######################################################################## # dcf_contract.customer_bank_account # ######################################################################## # IDX_CUSTOMER_ID is a left-prefix of UQI_IDX_1 # Key definitions: # KEY `IDX_CUSTOMER_ID` (`customer_id`) # UNIQUE KEY `UQI_IDX_1` (`customer_id`,`account_no`,`branch_bank`,`account_type`,`account_name`) USING BTREE, # Column types: # `customer_id` varchar(20) collate utf8_bin not null comment '客戶id' # `account_no` varchar(40) collate utf8_bin default null comment '銀行賬號(hào)' # `branch_bank` varchar(100) collate utf8_bin default null comment '開戶支行' # `account_type` tinyint(4) default null comment '賬戶類型：比如收款賬戶，還款賬戶等\n0-收款賬戶\n1-還款賬戶' # `account_name` varchar(100) collate utf8_bin default null comment '銀行賬戶戶名' # To remove this duplicate index, execute: ALTER TABLE `dcf_contract`.`customer_bank_account` DROP INDEX `IDX_CUSTOMER_ID`; # ######################################################################## # dcf_contract.t_contract_account # ######################################################################## # IDX_CONTRACT_ID is a left-prefix of t_contract_account_uq1 # Key definitions: # KEY `IDX_CONTRACT_ID` (`contract_id`) # UNIQUE KEY `t_contract_account_uq1` (`contract_id`,`account_type`), # Column types: # `contract_id` bigint(20) not null comment '合同id' # `account_type` tinyint(4) not null comment '賬戶類 型：globalconstant.bankaccounttypec常數(shù) \n0-收款賬戶\n1-還款賬戶 等' # To remove this duplicate index, execute: ALTER TABLE `dcf_contract`.`t_contract_account` DROP INDEX `IDX_CONTRACT_ID`; ...... ...... # ######################################################################## # Summary of indexes # ######################################################################## # Size Duplicate Indexes 173317386 # Total Duplicate Indexes 18 # Total Indexes 562
會(huì)給出重復(fù)/冗余類型、索引/外鍵定義、索引包含的列類型、移除重復(fù)/冗余索引/外鍵的SQL、最后會(huì)給出有關(guān)索引的統(tǒng)計(jì)信息。
重復(fù)索引刪除
直接執(zhí)行工具輸出結(jié)果中的ALTER TABLE語句即可，但是執(zhí)行前一定要仔細(xì)評(píng)估可能造成的影響。比如，表非常非常大的情況下可能造成主從復(fù)制延遲，又比如SQL中若包含索引提示的話直接刪除索引可能導(dǎo)致報(bào)SQL語法錯(cuò)誤，最好事先查一下是不是包含此類SQL（可通過general log或者tcpdump工具獲取SQL并加以分析）
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。
相關(guān)文章
MySQL生產(chǎn)環(huán)境CPU使用率過高的排查與解決方案
在生產(chǎn)環(huán)境中,MySQL作為一個(gè)關(guān)鍵的數(shù)據(jù)庫組件,其性能對(duì)整個(gè)系統(tǒng)的穩(wěn)定性至關(guān)重要,有時(shí)候我們可能會(huì)遇到MySQL CPU使用率過高的問題,本文將詳細(xì)介紹如何排查和解決MySQL CPU過高的問題,幫助您迅速恢復(fù)正常的數(shù)據(jù)庫性能,需要的朋友可以參考下2024-03-03
在本篇內(nèi)容中小編給大家整理了關(guān)于mysql存儲(chǔ)emoji表情的詳細(xì)步驟以及知識(shí)點(diǎn)，需要的朋友們學(xué)習(xí)下。2019-03-03
史上最全的MySQL備份方法，需要的朋友可以參考下。2010-03-03
這篇文章主要介紹了MySQL如何快速導(dǎo)入數(shù)據(jù)，幫助大家更好的理解和學(xué)習(xí)MySQL，感興趣的朋友可以了解下2020-08-08
mysql重裝后出現(xiàn)亂碼設(shè)置為utf8可解決
mysql重裝后出現(xiàn)亂碼解決辦法：只能在配置文件中將database 和 server 字符集 設(shè)置為utf8 ，否則不起作用，具體如下感興趣的朋友可以參考下哈，希望對(duì)大家有所幫助2013-07-07
這篇文章主要為大家詳細(xì)介紹了Mysql下載安裝、部署與圖形化詳細(xì)操作教程，感興趣的小伙伴們可以參考一下2016-06-06
mysql中的視圖，視圖與表有很多相似的地方，視圖也是由若干個(gè)字段以及若干條記錄構(gòu)成，視圖也可以作為select語句的數(shù)據(jù)源2017-04-04
這篇文章主要介紹了MySQL中的group by，MySQL的group by用于對(duì)查詢的數(shù)據(jù)進(jìn)行分組；此外MySQL提供having子句對(duì)分組內(nèi)的數(shù)據(jù)進(jìn)行過濾。下面來看看文章對(duì)此的具體介紹,需要的朋友可以參考一下，希望對(duì)你有所幫助2021-11-11
mysql經(jīng)常會(huì)遇到某些中文插入異常,最近有同學(xué)反饋了這樣一個(gè)問題,所以下面這篇文章主要給大家介紹了關(guān)于如何永久解決mysql插入中文失敗問題的相關(guān)資料,需要的朋友可以參考下2021-11-11
datediff是SQL SERVER里面的用法，ORACLE沒有，主要作用是返回兩個(gè)日期之間的時(shí)間間隔，本文通過實(shí)例代碼給大家詳細(xì)講解，對(duì)datediff用法感興趣的朋友跟隨小編一起看看吧2022-11-11