索引的優點
-
1.加快資料的檢索速度，這是創建索引的最主要的原因;
-
2.通過創建唯一性索引，可以保證資料庫表中每一行資料的唯一性;
-
3.加速表和表之間的連接;
-
4.在使用分組和排序子句進行資料檢索時，可以顯著減少查詢中分組和排序的時間，
索引的缺點
-
1.創建索引和維護索引要耗費時間，這種時間隨著資料量的增加而增加，
-
2.索引需要占物理空間，除了資料表占資料空間之外，每一個索引還要占一定的物理空間，如果要建立聚簇索引，那么需要的空間就會更大，
-
3.當對表中的資料進行增加、洗掉和修改的時候，索引也要同步動態的維護，這樣就降低了資料的增刪改速度，
所以單表資料太少，索引反而會影響速度；更新非常頻繁的資料不適宜建索引
索引設計原則
根據資料庫的功能，可以在資料庫設計器中創建三種索引
- 唯一索引：唯一索引是不允許其中任何兩行具有相同索引值的索引
- 主鍵索引：表定義主鍵將自動創建主鍵索引，主鍵索引是唯一索引的特定型別，該索引要求主鍵中的每個值都唯一，當在查詢中使用主鍵索引時， 它允許對資料的快速訪問
- 聚集索引：表中行的物理順序與鍵值的邏輯（索引）順序相同，一個表 只能包含一個聚集索引
選擇索引的最終目的是為了使查詢的速度變快，下面給出的原則是最基本的準則，但不能拘泥于這些準則，應該根據應用的實際情況進行分析和判斷，選擇最合適的索引方式，
1.索引最左匹配原則
- 索引可以簡單如一個列(a)，也可以復雜如多個列(a, b, c, d)，即聯合索引，
- 如果是聯合索引，那么key也由多個列組成，同時，索引只能用于查找key是否存在（相等），遇到范圍查詢(>、<、between、like左匹配)等就不能進一步匹配了，后續退化為線性查找，因此，列的排列順序決定了可命中索引的列數，
例子：
如有索引(a, b, c, d)，查詢條件a = 1 and b = 2 and c > 3 and d = 4，則會在每個節點依次命中a、b、c，無法命中d，(很簡單：索引命中只能是相等的情況，不能是范圍匹配)
明白最左匹配原則，對我們設計索引和撰寫高效SQL陳述句非常有幫助
2.為經常需要排序、分組操作的欄位建立索引
經常需要ORDER BY、GROUP BY、DISTINCT等操作的欄位，排序操作會浪費很多時間，如果為其建立索引，可以有效地避免排序操作，
分組欄位或者排序欄位應該創建索引
3.為常作為查詢條件的欄位建立索引
如果某個欄位經常用來做查詢條件，那么該欄位的查詢速度會影響整個表的查詢速度，因此，為這樣的欄位建立索引，可以提高整個表的查詢速度，
Where 子句中經常使用的欄位應該創建索引
4.限制索引的數目
索引的數目不是越多越好，每個索引都需要占用磁盤空間，索引越多，需要的磁盤空間就越大，修改表時，對索引的重構和更新很麻煩，越多的索引，會使更新表變得很浪費時間，
5.盡量選擇區分度高的列作為索引
盡量選擇區分度高的列作為索引,區分度的公式是count(distinct col)/count(*)，表示欄位不重復的比例，比例越大我們掃描的記錄數越少，唯一鍵的區分度是1，而一些狀態、性別欄位可能在大資料面前區分度就是0，那可能有人會問，這個比例有什么經驗值嗎？使用場景不同，這個值也很難確定，一般需要join的欄位我們都要求是0.1以上，即平均1條掃描10條記錄
6.索引列不能參與計算
索引列不能參與計算，保持列“干凈”，比如from_unixtime(create_time) = ’2019-12-02’就不能使用到索引，原因很簡單，b+樹中存的都是資料表中的欄位值，但進行檢索時，需要把所有元素都應用函式才能比較，顯然成本太大，所以陳述句應該寫成create_time = unix_timestamp(’2014-05-29’);
即索引列不能帶函式，否則會導致索引失效
7.擴展索引
盡量的擴展索引，不要新建索引，比如表中已經有a的索引，現在要加(a,b)的索引，那么只需要修改原來的索引即可
8.條件帶like 注意事項
like 模糊查詢中，右模糊查詢(abc%)會使用索引，而(%abc)和(%abc%)會放棄索引而使用全表掃描
9.盡量使用資料量少的索引
如果索引的值很長，那么查詢的速度會受到影響，例如，對一個CHAR(100)型別的欄位進行全文檢索需要的時間要比對CHAR(10)型別的欄位需要的時間要多，
10.盡量使用前綴來索引
如果索引欄位的值很長，最好使用值的前綴來索引，例如，TEXT和BLOG型別的欄位，進行全文檢索會很浪費時間，如果只檢索欄位的前面的若干個字符，這樣可以提高檢索速度，
11.洗掉不再使用或者很少使用的索引
表中的資料被大量更新，或者資料的使用方式被改變后，原有的一些索引可能不再需要，資料庫管理員應當定期找出這些索引，將它們洗掉，從而減少索引對更新操作的影響，
12.=和in可以亂序，
比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優化器會幫你優化成索引可以識別的形式
13.聯合查詢
聯合查詢，子查詢等多表操作時關連欄位要加索引
資料
- Java問題收集
- 原文地址
Spring Boot、Cloud 學習專案
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/99381.html
標籤：MySQL