MySQL查詢?nèi)哂嗨饕臀词褂眠^的索引操作
MySQL5.7及以上版本提供直接查詢?nèi)哂嗨饕?、重?fù)索引和未使用過索引的視圖，直接查詢即可。
查詢?nèi)哂嗨饕?、重?fù)索引
select * sys.from schema_redundant_indexes;
查詢未使用過的索引
select * from sys.schema_unused_indexes;
如果想在5.6和5.5版本使用，將視圖轉(zhuǎn)換成SQL語句查詢即可
查詢?nèi)哂嗨饕?、重?fù)索引
select a.`table_schema`,a.`table_name`,a.`index_name`,a.`index_columns`,b.`index_name`,b.`index_columns`,concat('ALTER TABLE `',a.`table_schema`,'`.`',a.`table_name`,'` DROP INDEX `',a.`index_name`,'`') from ((select `information_schema`.`statistics`.`TABLE_SCHEMA` AS `table_schema`,`information_schema`.`statistics`.`TABLE_NAME` AS `table_name`,`information_schema`.`statistics`.`INDEX_NAME` AS `index_name`,max(`information_schema`.`statistics`.`NON_UNIQUE`) AS `non_unique`,max(if(isnull(`information_schema`.`statistics`.`SUB_PART`),0,1)) AS `subpart_exists`,group_concat(`information_schema`.`statistics`.`COLUMN_NAME` order by `information_schema`.`statistics`.`SEQ_IN_INDEX` ASC separator ',') AS `index_columns` from `information_schema`.`statistics` where ((`information_schema`.`statistics`.`INDEX_TYPE` = 'BTREE') and (`information_schema`.`statistics`.`TABLE_SCHEMA` not in ('mysql','sys','INFORMATION_SCHEMA','PERFORMANCE_SCHEMA'))) group by `information_schema`.`statistics`.`TABLE_SCHEMA`,`information_schema`.`statistics`.`TABLE_NAME`,`information_schema`.`statistics`.`INDEX_NAME`) a join (select `information_schema`.`statistics`.`TABLE_SCHEMA` AS `table_schema`,`information_schema`.`statistics`.`TABLE_NAME` AS `table_name`,`information_schema`.`statistics`.`INDEX_NAME` AS `index_name`,max(`information_schema`.`statistics`.`NON_UNIQUE`) AS `non_unique`,max(if(isnull(`information_schema`.`statistics`.`SUB_PART`),0,1)) AS `subpart_exists`,group_concat(`information_schema`.`statistics`.`COLUMN_NAME` order by `information_schema`.`statistics`.`SEQ_IN_INDEX` ASC separator ',') AS `index_columns` from `information_schema`.`statistics` where ((`information_schema`.`statistics`.`INDEX_TYPE` = 'BTREE') and (`information_schema`.`statistics`.`TABLE_SCHEMA` not in ('mysql','sys','INFORMATION_SCHEMA','PERFORMANCE_SCHEMA'))) group by `information_schema`.`statistics`.`TABLE_SCHEMA`,`information_schema`.`statistics`.`TABLE_NAME`,`information_schema`.`statistics`.`INDEX_NAME`) b on(((a.`table_schema` = b.`table_schema`) and (a.`table_name` = b.`table_name`)))) where ((a.`index_name` <> b.`index_name`) and (((a.`index_columns` = b.`index_columns`) and ((a.`non_unique` > b.`non_unique`) or ((a.`non_unique` = b.`non_unique`) and (if((a.`index_name` = 'PRIMARY'),'',a.`index_name`) > if((b.`index_name` = 'PRIMARY'),'',b.`index_name`))))) or ((locate(concat(a.`index_columns`,','),b.`index_columns`) = 1) and (a.`non_unique` = 1)) or ((locate(concat(b.`index_columns`,','),a.`index_columns`) = 1) and (b.`non_unique` = 0))));
查詢未使用過的索引
select `information_schema`.`statistics`.`TABLE_SCHEMA` AS `table_schema`,`information_schema`.`statistics`.`TABLE_NAME` AS `table_name`,`information_schema`.`statistics`.`INDEX_NAME` AS `index_name`,max(`information_schema`.`statistics`.`NON_UNIQUE`) AS `non_unique`,max(if(isnull(`information_schema`.`statistics`.`SUB_PART`),0,1)) AS `subpart_exists`,group_concat(`information_schema`.`statistics`.`COLUMN_NAME` order by `information_schema`.`statistics`.`SEQ_IN_INDEX` ASC separator ',') AS `index_columns` from `information_schema`.`statistics` where ((`information_schema`.`statistics`.`INDEX_TYPE` = 'BTREE') and (`information_schema`.`statistics`.`TABLE_SCHEMA` not in ('mysql','sys','INFORMATION_SCHEMA','PERFORMANCE_SCHEMA'))) group by `information_schema`.`statistics`.`TABLE_SCHEMA`,`information_schema`.`statistics`.`TABLE_NAME`,`information_schema`.`statistics`.`INDEX_NAME`
補(bǔ)充：mysql ID 取余索引_mysql重復(fù)索引、冗余索引、未使用索引的定義和查找
1.冗余和重復(fù)索引
mysql允許在相同列上創(chuàng)建多個索引，無論是有意還是無意，mysql需要單獨(dú)維護(hù)重復(fù)的索引，并且優(yōu)化器在優(yōu)化查詢的時候也需要逐個地進(jìn)行考慮，這會影響性能。重復(fù)索引是指的在相同的列上按照相同的順序創(chuàng)建的相同類型的索引，應(yīng)該避免這樣創(chuàng)建重復(fù)所以，發(fā)現(xiàn)以后也應(yīng)該立即刪除。但，在相同的列上創(chuàng)建不同類型的索引來滿足不同的查詢需求是可以的。
冗余索引和重復(fù)索引有一些不同，如果創(chuàng)建了索引(a,b)，再創(chuàng)建索引(a)就是冗余索引，因為這只是前面一個索引的前綴索引，因此(a,b)也可以當(dāng)作(a)來使用，但是(b,a)就不是冗余索引，索引(b)也不是，因為b不是索引(a,b)的最左前綴列，另外，其他不同類型的索引在相同列上創(chuàng)建(如哈希索引和全文索引)不會是btree索引的冗余索引。
另外：對于二級索引(a,id)，id是主鍵，對于innodb來說，主鍵列已經(jīng)包含在二級索引中了，所以這個也是冗余索引。大多數(shù)情況下都不需要冗余索引，應(yīng)該盡量擴(kuò)展已有的索引而不是創(chuàng)建新索引，但也有時候處于性能方面的考慮需要冗余索引，因為擴(kuò)展已有的索引會導(dǎo)致其變得太大，從而影響其他使用該索引的查詢性能。如：如果在整數(shù)列上有一個索引，現(xiàn)在需要額外增加一個很長的varchar列來擴(kuò)展該索引，那么性可能會急劇下降，特別是有查詢把這個索引當(dāng)作覆蓋索引，或者這是myisam表并且有很多范圍查詢的時候(由于myisam的前綴壓縮)。
如：表userinfo，myisam引擎，有100W行記錄，每個state_id值大概2W行，在state_id列有一個索引對下面的查詢有用：如：select count(*) from userinfo where state_id=5;測試每秒115次QPS
對于下面的查詢這個state_id列的索引就不太頂用了，每秒QPS是10次
select state_id,city,address from userinfo where state_id=5;
如果把state_id索引擴(kuò)展為(state_id,city,address)，那么第二個查詢的性能更快了，但是第一個查詢卻變慢了，如果要兩個查詢都快，那么就必須要把state_id列索引進(jìn)行冗余了。但如果是innodb表，不冗余state_id列索引對第一個查詢的影響并不明顯，因為innodb沒有使用索引壓縮，myisam和innmodb表使用不同的索引策略的select查詢的qps測試結(jié)果(以下測試數(shù)據(jù)僅供參考)：
只有state_id列索引 只有state_id_2索引 同時有兩個索引
myisam,第一個查詢 114.96 25.40 112.19
myisam,第二個查詢 9.97 16.34 16.37
innodb,第一個查詢 108.55 100.33 107.97
innodb,第二個查詢 12.12 28.04 28.06
從上圖中可以看出，兩個索引都有的時候，缺點(diǎn)是成本更高，下面是在不同的索引策略時插入innodb和myisam表100W行數(shù)據(jù)的速度(以下測試數(shù)據(jù)僅供參考)：
只有state_id列索引 同時有兩個索引
innodb,對有兩個索引都有足夠的內(nèi)容的時候 80秒 136秒
myisam,只有一個索引有足夠的內(nèi)容的時候 72秒 470秒
可以看到，不論什么引擎，索引越多，插入速度越慢，特別是新增索引后導(dǎo)致達(dá)到了內(nèi)存瓶頸的時候。解決冗余索引和重復(fù)索引的方法很簡單，刪除這些索引就可以了，但首先要做的是找出這樣的索引，可以通過一些復(fù)雜的訪問information_schema表的查詢來找，不過還有兩個更簡單的方法，使用：shlomi noach的common_schema中的一些視圖來定位，也可以使用percona toolkit中的pt-dupulicate-key-checker工具，該工具通過分析表結(jié)構(gòu)來找出冗余和重復(fù)的索引，對于大型服務(wù)器來說，使用外部的工具更合適，如果服務(wù)器上有大量的數(shù)據(jù)或者大量的表，查詢information_schema表可能會導(dǎo)致性能問題。建議使用pt-dupulicate-key-checker工具。
在刪除索引的時候要非常小心：
如果在innodb引擎表上有where a=5 order by id這樣的查詢，那么索引(a)就會很有用，索引(a,b)實(shí)際上是(a,b,id)索引，這個索引對于where a=5 order by id這樣的查詢就無法使用索引做排序，而只能使用文件排序了。所以，建議使用percona工具箱中的pt-upgrade工具來仔細(xì)檢查計劃中的索引變更。
2. 未使用的索引
除了冗余索引和重復(fù)索引，可能還會有一些服務(wù)器永遠(yuǎn)不使用的索引，這樣的索引完全是累贅，建議考慮刪除，有兩個工具可以幫助定位未使用的索引：
A：在percona server或者mariadb中先打開userstat=ON服務(wù)器變量，默認(rèn)是關(guān)閉的，然后讓服務(wù)器運(yùn)行一段時間，再通過查詢information_schema.index_statistics就能查到每個索引的使用頻率。
B：使用percona toolkit中的pt-index-usage工具，該工具可以讀取查詢?nèi)罩荆θ罩局械拿總€查詢進(jìn)行explain操作，然后打印出關(guān)羽索引和查詢的報告，這個工具不僅可以找出哪些索引是未使用的，還可以了解查詢的執(zhí)行計劃，如：在某些情況下有些類似的查詢的執(zhí)行方式不一樣，這可以幫助定位到那些偶爾服務(wù)器質(zhì)量差的查詢，該工具也可以將結(jié)果寫入到mysql的表中，方便查詢結(jié)果。
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。
相關(guān)文章
這篇文章主要介紹了MySQL存儲過程概念、原理與常見用法,結(jié)合實(shí)例形式詳細(xì)分析了mysql存儲過程的概念、原理、創(chuàng)建、刪除、調(diào)用等各種常用技巧與相關(guān)注意事項,需要的朋友可以參考下2019-07-07
mysql學(xué)習(xí)之引擎、Explain和權(quán)限的深入講解
這篇文章主要給大家介紹了關(guān)于mysql學(xué)習(xí)之引擎、Explain和權(quán)限的相關(guān)資料，文中通過示例代碼將引擎、Explain和權(quán)限介紹的非常詳細(xì)，對大家學(xué)習(xí)或者使用mysql具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2018-06-06
MySQL利用AES_ENCRYPT()與AES_DECRYPT()加解密的正確方法示例
MySQL中AES_ENCRYPT('密碼','鑰匙')函數(shù)可以對字段值做加密處理，AES_DECRYPT(表的字段名字,'鑰匙')函數(shù)解密處理，下面這篇文章主要給大家介紹了關(guān)于MySQL利用AES_ENCRYPT()與AES_DECRYPT()加解密的正確方法，文中給出了詳細(xì)的示例代碼，需要的朋友可以參考下。2017-08-08
今天小編就為大家分享一篇關(guān)于MySQL優(yōu)化方案參考，小編覺得內(nèi)容挺不錯的，現(xiàn)在分享給大家，具有很好的參考價值，需要的朋友一起跟隨小編來看看吧2019-03-03
外鍵約束是用于建立兩個表之間關(guān)系的一種約束，本文主要介紹了MySQL外鍵約束詳解，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2022-07-07
MySQL提供了一些函數(shù)來對JSON數(shù)據(jù)進(jìn)行操作,下面這篇文章主要給大家介紹了關(guān)于mysql中json基礎(chǔ)查詢的相關(guān)資料,文中通過圖文以及實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-10-10
MySQL數(shù)據(jù)庫中提供了很豐富的函數(shù)。MySQL函數(shù)包括數(shù)學(xué)函數(shù)、字符串函數(shù)、日期和時間函數(shù)、條件判斷函數(shù)、系統(tǒng)信息函數(shù)、加密函數(shù)、格式化函數(shù)等。通過這些函數(shù)，可以簡化用戶的操作。本期將帶你總結(jié)常用函數(shù)都有哪些2021-11-11
這篇文章主要介紹了mysql合并字符串的實(shí)現(xiàn)方式，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-08-08
MySQL中LIKE運(yùn)算符的多種使用方式及示例演示
無論是簡單的模式匹配還是復(fù)雜的模式匹配，LIKE運(yùn)算符都提供了強(qiáng)大的功能來滿足不同的匹配需求，通過本文的介紹，我們詳細(xì)了解了在MySQL數(shù)據(jù)庫中使用LIKE運(yùn)算符進(jìn)行模糊匹配的多種方式，感興趣的朋友跟隨小編一起看看吧2023-07-07
mysql數(shù)據(jù)庫和oracle數(shù)據(jù)庫之間互相導(dǎo)入備份
今天小編就為大家分享一篇關(guān)于mysql數(shù)據(jù)庫和oracle數(shù)據(jù)庫之間互相導(dǎo)入備份，小編覺得內(nèi)容挺不錯的，現(xiàn)在分享給大家，具有很好的參考價值，需要的朋友一起跟隨小編來看看吧2019-04-04