Java數據庫之索引

索引：

索引是一種提高查詢效率的數據結構（B樹或者是哈希結構）；索引是創建在數據庫表中，是對數據庫表中的一列或者多列的值進行排序的一個結果，好處就是提高查詢效率；

一般情況下，一次查詢只用一個索引；

索引的分類：

• 普通索引：沒有任何限制，可以給任意字段創建普通索引；

• 唯一性索引：使用unique修飾的字段，值不能重復的，主鍵索引就屬于唯一性索引；

• 主鍵索引：使用primary key修飾的字段自動創建主鍵索引；

• 單列索引：在一個字段上創建索引 ；

• 多列索引：在多個字段上創建索引；

• 全文索引：使用fulltext參數可以設置全文索引，只支持char\varchar\text類型的字段上，常用于數據量較大的字符串類型上；

索引的創建和刪除的SQL語句：

（一）索引的創建

在創建表的時候指定索引

在已經創建好的表上添加索引

create [unique|fulltext|spatial] index idx_id(索引名) on 表名(id屬性名);alter table 表名 add [unique|fulltext|spatial] index index_name; //第二種方法

（二）索引的刪除

索引執行過程分析：

使用explain關鍵分析查詢SQL

 explain select * from Student where Sname like 'zhaolei'\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: Student type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4 Extra: Using where possible_keys:表示SQL執行可能會命中的索引有哪些; key:表示執行過程真正使用的索引名稱 rows:表示查詢影響的數據行數 當前查詢possible_keys\key都為null，則未命中索引 rows=4表示當前查詢操作對每一行數據都進行比較  添加索引后，分析執行過程 explain select * from Student where Sname like 'zhaolei'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: Student type: range possible_keys: idx_name key: idx_name key_len: 27 ref: NULL rows: 1 Extra: Using index condition 1 row in set (0.01 sec)通過explain關鍵分析查詢SQL，可以看出當前查詢命中索引idx_name， rows: 1表示通過一條記錄就查詢到了結果

索引的底層原理

mysql支持兩種索引，一種是B樹索引，一種是哈希表索引；

問：數據庫中常見的慢查詢的優化方式是什么？

答：加索引；

問：問什么加索引可以優化查詢？

答：因為能減少磁盤IO;

問：怎么減少磁盤IO的？

答：索引是一種優化查詢的數據結構，比如在MySQL中用到的B+樹，這種數據結構是可以優化查詢的，所以我們可以利用索引來快速的查找數據；

問：那你知道哪些數據結構可以提高查詢速度嗎？

答：紅黑樹，二叉樹，哈希表，B樹（B-樹），B+樹等；

問：那為什么MySQL使用B+樹呢？

答：如下：

MYSQL InnoDB存儲引擎，基于B-樹(實際MYSQL采用的是B+樹)的索引結構。B-樹是一種m階平衡樹，葉子節點都在同一層，由于每一個節點存儲的數據量比較大，所以整個B-樹的層數是非常低的，基本上不超過三層；

由于磁盤的讀取也是按block塊操作的（內存是按page頁面操作的），因此B-樹的節點大小一般設置為和磁盤塊大小一致，這樣一個B-樹節點，就可以通過一次磁盤I/O把一個磁盤塊的數據全部存儲下來，所以當使用B-樹存儲索引的時候，磁盤I/O的操作次數是最少的（MySQL的讀寫效率，主要集中在磁盤I/O上）。

那么MySQL最終為什么要采用B+樹存儲索引結構呢，那么看看B-樹和B+樹在存儲結構上有什么不同？

• B-樹的每一個節點，存了關鍵字和對應的數據地址，而B+樹的非葉子節點只存關鍵字，不存數據地址。因此B+樹的每一個非葉子節點存儲的關鍵字是遠遠多于B-樹的，B+樹的葉子節點存放關鍵字和數據，因此，從樹的高度上來說，B+樹的高度要小于B-樹，使用的磁盤I/O次數少，因此查詢會更快一些。

• B-樹由于每個節點都存儲關鍵字和數據，因此離根節點近的數據，查詢的就快，離根節點遠的數據，查詢的就慢；B+樹所有的數據都存在葉子節點上，因此在B+樹上搜索關鍵字，找到對應數據的時間是比較平均的，沒有快慢之分。

• 在B-樹上如果做區間查找，遍歷的節點是非常多的；B+樹所有葉子節點被連接成了有序鏈表結構，因此做整表遍歷和區間查找是非常容易的。

哈希索引當然是由哈希表實現的，哈希表對數據并不排序，因此不適合做區間查找，效率非常低，需要搜索整個哈希表結構。

MySQL數據庫的存儲引擎MyISAM和InNoDB的索引結構

聚集索引和非聚集索引：聚集就是索引和數據存放在一個文件里面，非聚集索引就是索引和數據分別存放在兩個文件里面；

MyISAM存儲引擎（非聚集索引）

MyISAM引擎使用B+樹作為索引結構、葉節點的數據域存放的是數據地址，在MyISAM引擎中，主索引和輔助索引在結構上沒有任何區別，只是主索引要求key是唯一的，而輔助索引的key可以重復，如果給其他字段創建輔助索引，結構圖如下：

根據上面兩張圖，首先按照B+樹搜索算法搜索索引，如果指定的key存在，則取出其數據域的值，然后以數據域的值為地址，讀取相應的數據記錄；

InNoDB存儲引擎（聚集索引）

InNoDB存儲引擎的主鍵索引，葉子節點中，索引關鍵字和數據是在一起存放的，如圖：

可以看到，索引關鍵字和數據存儲在葉子節點中；

InNoDB輔助索引，葉子節點存放的是索引關鍵字和對應的主鍵（為了一致性和節省存儲空間）：

輔助索引的B+樹，先根據關鍵字找到對應的主鍵，再去主鍵索引樹上找到對應的行記錄數據，從索引樹上可以看到，InNoDB的索引關鍵字和數據都是在一起存放的，體現在磁盤存儲上，例如創建一個user表，在磁盤上只存儲兩種結構，user.frm（存儲表的結構），user.idb（存儲索引和數據）；

聯合索引

就是先根據第一個鍵排序，第一個鍵相同的話，按第二個鍵排序……

索引的優化

1. 索引的優化，主要就是分析索引在哪些情況下會失效的問題：

2. 在where后使用or，導致索引失效（盡量少用or）；

3. 使用like ，like查詢是以%開頭；

4. 復合索引遵守“最左前綴”原則，即在查詢條件中使用了復合索引的第一個字段，索引才會被使用；

5. 如果列類型是字符串，那一定要在條件中將數據使用引號引用起來,否則不使用索引；

6. 使用in導致索引失效；

7. DATE_FORMAT()格式化時間，格式化后的時間再去比較，可能會導致索引失效；

8. 對于order by、group by 、 union、 distinc 中的字段出現在where條件中時，才會利用索引！

總結下索引的優化：

• 最左前綴匹配原則；

• 主鍵外鍵一定要建索引；

• 對 where,on,group by,order by 中出現的列使用索引；

• 盡量選擇區分度高的列作為索引,區分度的公式是count(distinct col)/count(*)，表示字段不重復的比例，比例越大我們掃描的記錄數越少，唯一鍵的區分度是1，而一些狀態、性別字段可能在大數據面前區分度就是0；

• 對較小的數據列使用索引,這樣會使索引文件更小,同時內存中也可以裝載更多的索引鍵；

• 索引列不能參與計算，保持列“干凈”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很簡單，b+樹中存的都是數據表中的字段值，但進行檢索時，需要把所有元素都應用函數才能比較，顯然成本太大。所以語句應該寫成create_time = unix_timestamp(’2014-05-29’);

• 為較長的字符串使用前綴索引；

• 盡量的擴展索引，不要新建索引。比如表中已經有a的索引，現在要加(a,b)的索引，那么只需要修改原來的索引即可；

• 不要過多創建索引, 權衡索引個數與DML之間關系，DML也就是插入、刪除數據操作。這里需要權衡一個問題，建立索引的目的是為了提高查詢效率的，但建立的索引過多，會影響插入、刪除數據的速度，因為我們修改的表數據，索引也需要進行調整重建；

• 對于like查詢，”%”不要放在前面。

• 查詢where條件數據類型不匹配也無法使用索引

• 字符串與數字比較不使用索引;

CREATE TABLEa(achar(10)); EXPLAIN SELECT * FROMaWHEREa='1' – 走索引 EXPLAIN SELECT * FROM a WHERE a=1 – 不走索引 

• 正則表達式不使用索引,這應該很好理解,所以為什么在SQL中很難看到regexp關鍵字的原因；