MySQL 索引之問題反饋

本文主要是關於MySQL 索引之問題反饋，我們一起去看看吧！

問：我們團隊中的一人想要使用 bigint 字段類型來代替 25-30 長度的 varchar 類型來存儲 CRC64 數據，然後將索引也改成 bigint 的索引，這會節省索引的空間。請問這否是合理的性能優化方法？（需要注意到是自適應前綴哈希在這裡是無效的，因為前 10 個字符重復率很高，因此 UNIQUE 索引不適合用在這裡）

答：在很多時候，這是一個很好的優化方法。考慮到哈希碰撞的問題，你可以使用形如 SELECT * FROM TBL WHERE hash=crc32(‘string’) AND string=’string’這樣的查詢。另外還需要注意的是 MySQL 裡的字符串比較默認是不區分大小寫的，對於哈希字符串也是如此。另外與 25~30 字節長度的字符串來說，使用 8 位的 bigint 在索引存儲上的差異並不大。你所采用的這項技術對超過 100 直接的字符串是最好的

問：ORDER BY 的優化問題：select * from table where A=xxx and B between 100 and 200 order by B 是很常見的日期范圍的查詢，並且需要對結果進行排序。問題是這樣的查詢如何設置最佳的索引？

答：實際上這種情況在 (A,B) 建索引就很好了，但如果你需要使用第三個列(C)作排序，那麼復合索引 (A,B,C) 就會導致排序無法使用索引，因此你可以我在PPT裡介紹的將排序轉成小范圍數據的聯合來處理。

問：使用關聯表時，是否應該在 (foreignkey1,foreignkey2) 和 (foreignkey2,foreignkey1) 上建索引？

答：是的，這是一個好的實踐方法。正常情況下如果我需要使用不同查詢做雙向遍歷時，我會這樣創建表：CREATE TABLE LINK (id1 int unsigned not null ,id2 int unsigned not null, PRIMARY KEY(id1,id2), KEY K(id2,id1)) engine=INNODB; 這個將使用更快的主鍵來做一些查詢，然後使用索引 K 來做其他的查詢。

問: 技巧#1“WHERE a IN (2-4)”的效率比“WHERE a IN (2,3,4)”低嗎?換句話說，在范圍查詢中IN比BETWEEN效率高嗎?

A: IN(2-4) 並不是你想要的那樣。2-4被看做是一個數學公式，結果是IN(-2)，而不是你想要的結果.

Q: 有一個主索引在int(ID)，還有另外一些復合索引在idx1(X,A,B,C) idx2(Y,A,B,C)等等(一共有五個).是不是把A,B,C,ID設置為主索引，把另外的索引設置在一列,像這樣idx(X),idx2(Y)，這樣是不是更好?

A: 相較於設置5個復合索引和僅僅設置第一列為索引,那一個才是最好的配置. 關於改變主鍵來包含這麼多的列前綴取決你准備搜索什麼. 通過這些列的數據分組有利於你很多方面的搜索，但是也會減慢插入操作以及使主鍵顯得很碎，很分散. 並且在處理把主鍵追加到索引的時候MYSQL也有很多的限制。尤其是你說的這種情況.最後我們要注意的是這種獨特的設置是不是對系統性能有很大的提升.

問：Table1有一主鍵，Table2 用Table1的當前這個主鍵來關聯Table1，Table2上兩個表存在關聯的那個字段有必要建立索引嗎？

答：這個問題涉及到MySQL執行join的問題。如果首先是用別的索引字段來檢索Table2，然後再到Table1通過Table1的主鍵索引來查找行數據，這樣就沒有必要在Table2關聯的那個字段上建一個索引。

Q: 關於擴展原有的索引是否比新添加一個索引更好：我有張名叫PO的表，擁有一個主鍵以及兩個額外的字段vendor_id 和order_id。如果我有一個索引基於vendor_id和order_id，但是我的查詢語句僅選擇了vendor，這個索引是否會影響到該查詢的速度？

A: 如果你將該索引從 (vendor_id) 擴展至 (vendor_id,order_id)，卻只用到vendor_id，你將會因為增加了4個字節的長度(假設order_id是int類型)而影響你的查詢，但可能並不明顯。不過除了單獨基於(vendor_id)的索引，比起對(vendor_id,order_id)建立另一個索引，它似乎能極大的減少系統花費。這個例子裡你真正需要擔心的會因擴展索引而造成影響的是當你大幅度增加他的長度，例如增加long varchar類型列。類似的情況下它確實會比增加一個索引好。

Q: 我們有個數據庫大概有400G的索引,這麼多索引不可能全部讀取到內存了.這樣會影響系統性能嗎？

A: 通常你不必把所有的索引都放入內存，只需要讓一部分頻繁進出內存. 這部分工作區的大小取決於應用程序,范圍大小是總內存的5%至100%. 合適的工作區大小和不合適的工作區大小會讓系統性能相差10倍以上.

問：在什麼情況下使用自增字段為主鍵?

答：自增字段是一個很好的默認主鍵。在以下幾種情況下你應該選擇別的——如果一個字段有助於你在各個方面的數據分組或者這個字段被頻繁的在查詢中使用.

問：索引數量最大值是多少？

答：在最新的MYSQL版本裡,每個表最多可以有64個索引.這已經太多了。相比於考慮索引數量的最大值，我更傾向於考慮添加索引是否有利於提高系統性能;添加索引增加的收益是否小於系統性能的降低

Q: "id"=5 和 "id" in (5)在性能上有什麼不同？

A: 最近的 MySQ版本已經很智能的將in(5)轉換為ID=5的形式(in的列表中是單值的情況下). 過去他們是有所區別的.

Q: 你推不推薦為你創建的每一個表都創建一個索引呢？例如：CREATE TABLE user_competition_entry user_id (INT), competition_id(INT); 這張表僅僅用於記錄user_id和competition_id，並沒有別的東西。若直接使用SELECT competition_id, COUNT(user_id) AS user_count FROM user_competition_entry GROUP BY competition_id;會不會比沒有索引慢呢？

A: 我會定義(competition_id,user_id)作為此類表的主鍵。它還能對你提到的無臨時表或外部排序時進行分組查詢有幫助。

問：我們如何管理服務器的數據庫索引？有怎樣的管理要求或者服務器自己完成索引的管理。特別是在使用數據庫結構已經預定義的CMS（內容管理系統）

答：MYSQL服務器不會自動的為您定義任何的索引。只能期望您的CMS已經配置了合理的索引，如果沒有的話，需要你手動的添加需要的索引。

問：在一個表中我運行了11M的數據之後的，主鍵的就從19到了19,000，那麼對於基數這麼大的主鍵有什麼好的解決方法？

基數是數據的一個特性，我覺得沒必要為了這個特性而專門去克服它。最好的還是研究下數據量存的大的那些，然後從執行情況入手，可以通過重新設計模式或者執行次數來達到優化的目的。

問：如何使用那種有一列的索引並且在另一列的使用order by 語句。我是否需要使用order by子句添加索引列呢。

答：如果索引的使用和order by 相同的索引，必須是在查詢同一張表，不能有另外的索引，而且你只可以有平等的比較，例如查詢條件是：WHERE A=5 ORDER BY B 將使用索引(A,B)來實現排序優化，對於更復雜的條件，可能需要使用類似"Unionizing Order by"的手法。

Q: UUID字段類型為VARCHAR(36),使用它代替自增列做為索引會產生什麼影響?

A: 為了系統性能你最好把UUID轉換為二進制形式並存儲為VARBINARY(16).不管怎樣你可能得到的table會比使用自增列要大. 雖然這樣還是有很多人在應用中使用UUID想當成功,而這個應用不是必需優化為最佳性能或是這種情況不會成為系統瓶頸. 想看更詳細的可以查一下我關於這個主題的其它文章.

Q: mysql如何在group by上使用索引？

A: 如果為group by上的字段加索引的話，mysql可以避免臨時表或filesort(譯注：這兩種處理類型性能很低) .這是因為mysql通過掃描索引已經對數據進行了排序。

問: 這裡有關於使用日期范圍進行查詢的相關方法或技巧嗎? 比如2個日期之間的范圍? 或者在一個日期之後的范圍?

答: 日期比較和其他的數據比較非常相似所以一些技巧也是想通的, 例如在某些條件下你可以將BETWEEN的寫法轉換成IN這種范圍寫法以更好的利用索引.

問: 對於innodb型的b+樹索引在葉子節點是是鏈表還是雙向鏈表? 你的幻燈片裡展示的是單鏈表但是事實上你可以使用"order by desc"子句來暗示索引使用雙向鏈表.

Innodb 支持雙向鏈表 – 每一個葉子都包含指向前驅和後繼節點的指針. 請注意使用“ORDER BY DESC”這個優化選項並不是真正的需求 – 即使沒有葉子指向其他的Page,你仍然可以向任意的方向遍歷BTREE. 如若如此,這僅僅只是讓搜索變得開銷相對昂貴罷了.

以上就是精品為大家整理的MySQL 索引之問題反饋，希望對大家有所幫助。