mysql調優（七）--索引的幾個概要

一、哈希索引

思維導圖

1.哈希索引的特點

• 基于哈希表的實作，隻有精确比對索引所有列的查詢才有效
• 在mysql中，隻有memory的存儲引擎顯式支援哈希索引
• 哈希索引自身隻需存儲對應的hash值，是以索引的結構十分緊湊，這讓哈希索引查找的速度非常快

2.哈希索引的限制

• 1、哈希索引隻包含哈希值和行指針，而不存儲字段值，索引不能使用索引中的值來避免讀取行
• 2、哈希索引資料并不是按照索引值順序存儲的，是以無法進行排序
• 3、哈希索引不支援部分列比對查找，哈希索引是使用索引列的全部内容來計算哈希值
• 4、哈希索引支援等值比較查詢，也不支援任何範圍查詢
• 5、通路哈希索引的資料非常快，除非有很多哈希沖突，當出現哈希沖突的時候，存儲引擎必須周遊連結清單中的所有行指針，逐行進行比較，直到找到所有符合條件的行

思考：什麼是哈希沖突？

因為哈希值是通過一定算法生成的，那麼就有一定的可能出現不同的輸入得到的Hash值是一樣的，就算我們可以通過調整算法盡量減少這種情況，但是也不可完全避免。發生這種情況後，我們就會出現兩個不同的鍵值被映射到同一個位置了，這就是哈希沖突。

思考：哈希沖突怎樣解決？

1.開放位址法：

基本思想：當發生位址沖突的時候，按照某種方法繼續探測哈希表中的其他存儲單元，直到找到空位置為止；

所用公式 Hi(key) = [H(key) + di]mod m;其中i = 1、2、3…k（k<m-1）,H(key)為關鍵字key的直接hash位址；M為hash表的長度；

di為再次探測時的位址增量；根據di的不同取法，有不同的稱呼；

線性探測再散列：di = 1、2、3、4…k (k<m-1)

二次探測再散列：di = 12,-12,22,-22…k2,-k2 (k<=m/2)

僞随機再散列：di = 僞随機數

2.再hash的方法：

當發生沖突時，使用第二個、第三個、哈希函數計算位址，直到無沖突時。缺點：計算時間增加。 比如上面第一次按照姓首字母進行哈希，如果産生沖突可以按照姓字母首字母第二位進行哈希，再沖突，第三位，直到不沖突為止；

3.拉鍊法：

在HashMap中 就是使用拉鍊法 來解決hash沖突的問題的；将所有關鍵字為同義詞的記錄存儲在同一線性連結清單中。

4.建立公共溢出區：

建立公共溢出區的基本思想是：假設哈希函數的值域是[1,m-1]，則設向量HashTable[0…m-1]為基本表，每個分量存放一個記錄，另外設向量OverTable[0…v]為溢出表，所有關鍵字和基本表中關鍵字為同義詞的記錄，不管它們由哈希函數得到的哈希位址是什麼，一旦發生沖突，都填入溢出表。

思考：HashMap處理哈希沖突采用的是哪種方式？

HashMap解決哈希沖突采用的是鍊位址法。屬于開散列。說白了就是把沖突的key連接配接起來，放到桶裡。當桶中的元素個數不超過6個時，以單連結清單的形式串起來，當桶中的元素個數超過6個時，以紅黑樹的形式串起來。

• 6、哈希沖突比較多的話，維護的代價也會很高

3.案例

當需要存儲大量的URL，并且根據URL進行搜尋查找，如果使用B+樹，存儲的内容就會很大

select id from url where url=""

也可以利用将url使用CRC32做哈希，可以使用以下查詢方式：

select id fom url where url="" and url_crc=CRC32("")

此查詢性能較高原因是使用體積很小的索引來完成查找

二、組合索引

思維導圖

• 當包含多個列作為索引，需要注意的是正确的順序依賴于該索引的查詢，同時需要考慮如何更好的滿足排序和分組的需要
• 案例

案例，建立組合索引a,b,c,不同SQL語句使用索引情況

三、聚簇索引與非聚簇索引

思維導圖

1.聚簇索引

• 不是單獨的索引類型，而是一種資料存儲方式，指的是資料行跟相鄰的鍵值緊湊的存儲在一起

1.1優點

• 1、可以把相關資料儲存在一起
• 2、資料通路更快，因為索引和資料儲存在同一個樹中
• 3、使用覆寫索引掃描的查詢可以直接使用頁節點中的主鍵值

1.2缺點

• 1、聚簇資料最大限度地提高了IO密集型應用的性能，如果資料全部在記憶體，那麼聚簇索引就沒有什麼優勢
• 2、插入速度嚴重依賴于插入順序，按照主鍵的順序插入是最快的方式

什麼是頁分裂？什麼是頁合并？

所謂的索引其實就是一顆 B+ 樹，一個表有多少個索引就會有多少顆 B+ 樹，mysql 中的資料都是按順序儲存在 B+ 樹上的（是以說索引本身是有序的）。mysql 在底層又是以資料頁為機關來存儲資料的，一個資料頁大小預設為 16k，當然你也可以自定義大小，也就是說如果一個資料頁存滿了，mysql就會去申請一個新的資料頁來存儲資料，如果主鍵為自增 id 的話，mysql 在寫滿一個資料頁的時候，直接申請另一個新資料頁接着寫就可以了。如果主鍵是非自增 id，為了確定索引有序，mysql 就需要将每次插入的資料都放到合适的位置上。當往一個快滿或已滿的資料頁中插入資料時，新插入的資料會将資料頁寫滿，mysql 就需要申請新的資料頁，由于可能把上個資料頁中的部分資料挪到新的資料頁上。這就造成了頁分裂，這個大量移動資料的過程是會嚴重影響插入效率的。頁合并反之

作者：強強程式設計

連結：https://juejin.cn/post/6844904117307965447

來源：掘金

著作權歸作者所有。商業轉載請聯系作者獲得授權，非商業轉載請注明出處。

• 3、更新聚簇索引列的代價很高，因為會強制将每個被更新的行移動到新的位置
• 4、基于聚簇索引的表在插入新行，或者主鍵被更新導緻需要移動行的時候，可能面臨頁分裂的問題
• 5、聚簇索引可能導緻全表掃描變慢，尤其是行比較稀疏，或者由于頁分裂導緻資料存儲不連續的時候

2.非聚簇索引

• 資料檔案跟索引檔案分開存放

四、覆寫索引

思維導圖

1.基本介紹

• 1、如果一個索引包含所有需要查詢的字段的值，我們稱之為覆寫索引
• 2、不是所有類型的索引都可以稱為覆寫索引，覆寫索引必須要存儲索引列的值
• 3、不同的存儲實作覆寫索引的方式不同，不是所有的引擎都支援覆寫索引，memory不支援覆寫索引

2.優勢

• 1、索引條目通常遠小于資料行大小，如果隻需要讀取索引，那麼mysql就會極大的減少資料通路量
• 2、因為索引是按照列值順序存儲的，是以對于IO密集型的範圍查詢會比随機從磁盤讀取每一行資料的IO要少的多
• 3、一些存儲引擎如MYISAM在記憶體中隻緩存索引，資料則依賴于作業系統來緩存，是以要通路資料需要一次系統調用，這可能會導緻嚴重的性能問題
• 4、由于INNODB的聚簇索引，覆寫索引對INNODB表特别有用

3.案例示範

• 準備條件

ZIP格式：http://downloads.mysql.com/docs/sakila-db.zip

tar格式: http://downloads.mysql.com/docs/sakila-db.tar.gz

官方文檔 http://dev.mysql.com/doc/sakila/en/index.html

解壓後得到三個檔案：

• sakila-schema.sql 檔案包含建立Sakila資料庫的結構：表、視圖、存儲過程和觸發器
• sakila-data.sql檔案包含：使用 INSERT語句填充資料及在初始資料加載後，必須建立的觸發器的定義
• sakila.mwb檔案是一個MySQL Workbench資料模型，可以在MySQL的工作台打開檢視資料庫結構。

--登入mysql
mysql -uroot -p123456
--導入表的結構資料
source /root/sakila-schema.sql
--導入表的資料
source /root/sakila-data.sql
           

• 1、當發起一個被索引覆寫的查詢時，在explain的extra列可以看到using index的資訊，此時就使用了覆寫索引

mysql> explain select store_id,film_id from inventory\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: inventory
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_store_id_film_id
      key_len: 3
          ref: NULL
         rows: 4581
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.01 sec)

           

• 2、在大多數存儲引擎中，覆寫索引隻能覆寫那些隻通路索引中部分列的查詢。不過，可以進一步的進行優化，可以使用innodb的二級索引來覆寫查詢。

例如：actor使用innodb存儲引擎，并在last_name字段有二級索引，雖然該索引的列不包括主鍵actor_id，但也能夠用于對actor_id做覆寫查詢

• 什麼是二級索引？

mysql中每個表都有一個聚簇索引（clustered index ），除此之外的表上的每個非聚簇索引都是二級索引，又叫輔助索引（secondary indexes）。

以InnoDB來說，每個InnoDB表具有一個特殊的索引稱為聚集索引。如果您的表上定義有主鍵，該主鍵索引是聚集索引。如果你不定義為您的表的主鍵時，MySQL取第一個唯一索引（unique）而且隻含非空列（NOT NULL）作為主鍵，InnoDB使用它作為聚集索引。如果沒有這樣的列，InnoDB就自己産生一個這樣的ID值，它有六個位元組，而且是隐藏的，使其作為聚簇索引。

一句話：主鍵是聚集索引 ，唯一索引、普通索引、字首索引等都是二級索引（輔助索引）。

mysql> explain select actor_id,last_name from actor where last_name='HOPPER'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ref
possible_keys: idx_actor_last_name
          key: idx_actor_last_name
      key_len: 137
          ref: const
         rows: 2
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)