Mysql高級3-索引的結構和分類

一、索引概述

　　1.1 索引的介紹

　　　　索引index：是幫助 Mysql 高效獲取數據 的 有序的數據結構，在數據之外，資料庫系統維護著的滿足特定查找演算法的數據結構，這些數據結構以某種方式引用(指向)數據，這樣就可以在這些數據結構上實現高級查找演算法，這種數據結構就是索引

　　1.2 索引的優缺點

• • 優點1：提高數據檢索的效率，降低資料庫的IO成本
  • 優點2：通過索引列對數據進行排序，降低數據排序的成本，降低CPU的消耗
  • 缺點1：索引列也要占磁碟空間。
  • 缺點2：索引大大提高了查詢效率，同時卻也降低了更新表的速度，如對錶進行insert，update，delete時，效率降低

二、索引結構

　　2.1 Mysql的索引常見結構

　　　　Mysql的索引是在儲存引擎層實現的，不同的存儲引擎有不同的結構，主要包含一下幾種　

• • B+樹：最常見的索引類型，大部分引擎都支持B+樹索引
  • Hash索引：底層數據結構是用哈希表實現的，只有精確匹配索引的查詢才有效，不支持範圍查詢　

　　2.2 Mysql常見索引對不同引擎的支持

• • B+樹：InnoDB(支持)、MyISAM(支持)、Memory(支持)
  • Hash索引：InnoDB(不支持)、MyISAM(不支持)、memory(支持)

　　2.3 二叉樹實現索引的弊端

　　　　說明1：實際中的索引是沒有使用二叉樹的，因為二叉樹具有一下的弊端　　 

　　　　說明2：當順序插入時，會形成一個鏈表，查詢性能大大降低，大數據量的情況下，層級較深，檢索速度慢。

　　　　說明3：特殊二叉樹紅黑樹當做索引是，大數據量情況下，層級比較深，檢索速度慢

　　2.4 B樹實現索引的弊端

　　　　以一個最大度數（max-degree）為5（5階）的b樹為例（每個節點最多儲存4個key，5個指針）

　　　　說明：B樹的數據會存在每個節點上，而節點存在頁(2.6 Mysql索引對B+樹的優化有說明)上面，每頁的大小為16K，這樣每個頁能存放的索引就比較少，導致同樣數據體積小，層級要比B+樹深。

　　2.5 B+樹實現索引

　　　　以一個最大度數（max-degree）為4（4階）的b+樹為例

　　　　說明：對比較與B樹

　　　　　　1、所有的數據都會出現在葉子節點上

　　　　　　2、葉子節點形成一個單向鏈表

　　2.6 Mysql索引對B+樹的優化

　　　　Mysql索引數據結構對經典的B+樹進行了優化，在原來的B+樹基礎上，增加了一個指向相鄰葉子節點的鏈表指針，就行了帶有順序指針的B+樹，提高了區間訪問的性能

　　　　說明：每頁在InnoDB中預設16K

　　2.7 hash索引

　　　　哈希索引就是採用一定的hash演算法，將鍵值換成新的hash值，映射到對應的槽位上，然後儲存在hash表中

　　　　說明：如果兩個（或者多個）鍵映射到同一個槽位上，他們就產生了hash衝突，也稱hash碰撞，可以通過鏈表來解決

　　2.8 hash索引特點

• • hash索引只能用於對等比較（=，in），不支持範圍查詢（between,>,<）
  • 無法利用索引完成排序操作
  • 查詢效率高，通常只需要一次檢索就可以了，效率通常要高於B+樹索引
  • 在Mysql中，支持hash索引的事Memory引擎，而InnoDB中具有自適應hash功能，hash索引是存儲引擎根據B+樹索引在指定條件下自動構建的

 　　2.9 InnoDB引擎選擇B+樹的優勢

• • 相對於二叉樹，層級更少，搜索效率高
  • 對於B樹，無論是葉子節點還是非葉子節點，都會保存數據，這樣導致一頁中存儲的鍵值減少，指針跟著減少，同樣保存大量數據，只能增加樹的高度，導致性能降低。
  • 相對於hash索引，B+樹支持範圍匹配及排序操作  

三、索引分類

　　3.1 主鍵索引

　　　　針對於表中主鍵創建的索引，預設自動創建，只能有一個， 關鍵字：primary

　　3.2 唯一索引

　　　　避免同一個表中某數據列中的值重覆，可以有多個，關鍵字：unique

　　3.3 常規索引

　　　　快速定位特定數據，可以有多個，

　　3.4 全文索引

　　　　全文索引查找的是文本中的關鍵字，而不是比較索引中的值，可以有多個，fulltext

　　3.5 聚集索引

　　　　在InnoDB中，根據索引的儲存形式劃分的，將數據儲存與索引放到一起，索引結構的葉子節點保存了行數據，必須有，而且只有一個

• • 如果存在主鍵，主鍵索引就是聚集索引
  • 如果不存在主鍵，將使用第一個唯一（unique）索引作為聚集索引
  • 如果表沒有主鍵，也沒有合適的唯一索引，則InnoDB會自動生成一個rowid作為隱藏的聚集索引

　　3.6 二級索引

　　　　在InnoDB中，根據索引的儲存形式劃分的，將數據與索引分開儲存，索引結構的葉子節點關聯的是對應的主鍵，可以存在多個

 　　　　說明：聚集索引下麵存放的是整行的數據，二級索引下麵存放的對應的主鍵，要不然聚集索引下存放了整行數據，二級索引下也放整行數據，就會很冗餘

　　3.7 回表查詢

　　　　說明1：首先根據name欄位走二級索引

　　　　說明2：找到Arm對應的id=10

　　　　說明3：然後再根據id=10找到對應的數據

　　　　說明4：整個過程也叫做回表查詢

四、索引語法

　　4.1 查看索引

show index from 表名

　　　　示例：

mysql> show index from account;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| account |          0 | PRIMARY  |            1 | id          | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)

　　　　說明：account 有一個主鍵索引

　　4.2 創建索引

create [unique | fulltext] index 索引名 on 表名(索引的列名, ..); 

　　　　說明1：如果創建索引的欄位是唯一的，值都不重覆，可以加unique約束，說明這是一個唯一欄位索引

　　　　說明2：fulltext 是全文檢索索引，主要針對大的文本欄位

mysql> create index name_idx on account(name);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from account;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| account |          0 | PRIMARY  |            1 | id          | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| account |          1 | name_idx |            1 | name        | A         |           4 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.01 sec)

　　　　說明1：這就創建了一個名為name_idx的索引

　　4.3 刪除索引

drop index 索引名 on 表名

　　　　示例

mysql> drop index name_idx on account;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from account;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| account |          0 | PRIMARY  |            1 | id          | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)

　　　　說明：這就刪除了一個索引

五、預告

　　後面的文章會繼續介紹索引的使用和設計原則