淺談MySQL索引 實用又方便的方法

MySQL索引對於mysql性能查詢優化是起了非常大的作用的了，我們今天來看一篇關於MySQL索引的分析文章吧，希望此文章對各位了解MySQL索引會有所幫助。

索引基礎

   索引的作用其實就是在MySQL中高效的獲取數據。在數據庫中，一般索引會很大，不可能全部儲存在內存中，會是以文件形式存儲在文件系統中。這樣查詢索引就會設計到磁盤I/O，而磁盤I/O相對於內存的讀寫速度差幾個數量級，索引的目的即是減少一次查詢中的磁盤I/O次數。而在計算機中，當做一次I/O操作時，在吧當前磁盤地址數據讀取到內存中時，也會把相鄰地址的數據讀到內存中，這個是計算機中著名的局部性原理:當一個數據被用到時，其相鄰地址的數據也通常會馬上被使用。由於磁盤順序讀取的效率很高(不需要尋道時間，只需很少的旋轉時間)，因此對於具有局部性的程序來說，預讀可以提高I/O效率。索引數據結構的設計即是基於這個原理。

B-/+Tree索引簡單分析

維基百科對B樹的定義為：B樹（B-Tree）是一種樹狀數據結構，它能夠存儲數據、對其進行排序並允許以O(log n)的時間復雜度運行進行查找、順序讀取、插入和刪除的數據結構。B樹，概括來說是一個節點可以擁有多於2個子節點的二叉查找樹。與自平衡二叉查找樹不同，B-Tree為系統最優化大塊數據的讀和寫操作。B-Tree算法減少定位記錄時所經歷的中間過程，從而加快存取速度。

B-Tree有一個重要的特點是：所有葉節點具有相同的深度，等於樹高h。這樣索引檢索一次最多需要訪問h個節點。

B-Tree和B+Tree的區別在於，B+Tree只有葉節點才存儲數據，另外在B+Tree的每個葉子節點包含一個指向相鄰葉子節點的指針，這樣它即帶有順序訪問功能。目的是為了提高區間查找和遍歷的性能。

B+Tree的優點在於：

由於B+樹在內部節點上不好含數據信息，因此在內存頁中能夠存放更多的key。 數據存放的更加緊密，具有更好的空間局部性。因此訪問葉子幾點上關聯的數據也具有更好的緩存命中率。
B+樹的葉子結點都是相鏈的，因此對整棵樹的便利只需要一次線性遍歷葉子結點即可。而且由於數據順序排列並且相連，所以便於區間查找和搜索。而B-Tree則需要進行每一層的遞歸遍歷。相鄰的元素可能在內存中不相鄰，所以緩存命中性沒有B+Tree好。
B-Tree的優點在於，由於B-Tree的每一個節點都包含key和value，因此經常訪問的元素可能離根節點更近，因此訪問也更迅速。
最左前綴匹配

建索引時都知道會有這一個最左前綴匹配原則，這個通常是在復合索引中遇到的。查詢條件中的所有字段需要從左邊起按順序出現在多列索引中，查詢條件的字段數要小於等於多列索引的字段數，中間字段不能存在范圍查詢的字段(<,like等)，這樣的sql可以使用該多列索引。

可以使用B+Tree索引的查詢類型

1.全值匹配

2.匹配最左前綴

3.匹配列前綴(可用用like a%,但不能使用like %b)

4.匹配范圍值

5.精確匹配某一列和和范圍匹配另外一列

因為索引樹中的節點是有序的，所以除了按值查找之外，索引還可以用於查詢中的order by操作（按順序查找）。

索引建立原則

1.最左前綴匹配原則，組合索引必用，mysql會一直向右匹配直到遇到范圍查詢(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調整。

2.=和in可以亂序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優化器會幫你優化成索引可以識別的形式。

3. 盡量選擇區分度高的列作為索引,區分度的公式是count(distinct col)/count(*)，表示字段不重復的比例，比例越大我們掃描的記錄數越少。

4.索引列不要參與計算，比如date_format(create_time,’%Y-%m-%d’) = ’2015-12-31’就使用不到索引，原因很簡單，b+樹中存的都是數據表中的字段值，但進行檢索時，需要把所有元素都應用函數才能比較，顯然成本太大。所以語句應該寫成create_time = str_to_date(‘2014-05-29’,’%Y-%m-%d’);

Mysql 優化原則

簡化SQL，快速執行，無阻塞，簡單SQL比復雜SQL更高效；
僅僅使用最有效的過濾條件，索引字段不是越多越好；
只取出自己需要的 Columns，避免使用select *；
覆蓋索引可以直接返回結果，無須掃描數據；
例如：select id,status from tab where id=2 ，建立組合索引(id,status)，這個索引包含（或者說覆蓋）所有需要查詢的字段的值，MySQL利用索引返回select列表中的字段，而不必根據索引再次回表讀取數據頁。
不僅僅是select，delete/update語句也需要建索引；
盡可能在索引中完成排序（order by， group by的優化）；
盡量少用子查詢，改寫成多表JOIN；
多表JOIN，永遠用小結果集驅動大的結果集；
索引列不能是表達式的一部分，也不能是函數的參數。

下面兩例中即使在 id, gmt_created 上建立索引，也會導致索引失效。

 
select id from tab where id+1 = 5;
select id,value from tab where to_days(now()) - to_days(gmt_created) <= 10;
 
應該養成簡化 where 條件的習慣，始終將索引列單獨放在比較符號的一側。正確的寫法是：

select id from tab where id = 5-1;
select id, value from tab where gmt_created >= DATA_SUB(now(),interval 10 day );