細聊MySQL的Innodb儲存引擎（完）

標籤：聚簇索引   輔助索引   

細聊MySQL的Innodb儲存引擎（一）

細聊MySQL的Innodb儲存引擎（二）

細聊MySQL的Innodb儲存引擎（完）

上篇主要和大家探討了Innodb引擎中出現幻讀的處理方法與死結的探測及避免死結的一些注意事項。此篇，我們來研究下Innodb的索引。

Innodb裡涉及到的索引主要有四種，分別為聚簇索引（Clustered Index）、次級索引（Secondary Index）、全文索引（FULLTEXT Index）、雜湊索引（Hash Index）。

聚簇索引與次級索引

每一個Innodb表都有一個唯一的聚簇索引。一般來說，每個表的主鍵就是聚簇索引。如果你的表中沒有定義主鍵，那麼MySQL會將第一個非空唯一索引作為聚簇索引。如果表中既沒有主鍵，也沒有合適的唯一索引，Innodb會自己產生一個隱藏的聚簇索引。

通過聚簇索引的查詢速度是很快的，因為查詢到的索引會直接指向資料行。如果一個表的資料量非常大，聚簇索引會頻繁的被讀寫而造成I/O負載教高。特別是資料檔案與索引檔案不在一個檔案的情況下。

次級索引，所有非聚簇索引的索引就被稱為次級索引。次級索引可以有很多個，每一個次級索引記錄內都包含主鍵列。使用者在使用次級索引查詢時，MySQL根據次級索引對應的主鍵進行查詢。如果主鍵所佔的位元組過大，那麼次級索引也就需要更大的空間。所以，主鍵還是越短越好。

全文索引

Innodb在5.5之後支援全文索引。全文索引能協助使用者快速查詢設定了全文索引的列。全文索引可以使用CREATE TABLE或ALTER TABLE或CREATE INDEX等文法設定。

全文索引有一個被稱為“反轉索引”的設計。反轉索引儲存資料列中出現的每一個單詞。它會將資料列中的文檔劃分為不同的單詞，將單詞、單詞所在的位置資訊、位移量等資訊都儲存在全文索引表裡。

下面介紹下儲存全文索引的“全文索引表”，首先進行以下操作：

mysql> use test;

mysql> create table opening_lines 

( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, 

  opening_line TEXT(500), 

  author VARCHAR(200), 

  title VARCHAR(200), 

  FULLTEXT idx (opening_line) )

ENGINE=InnoDB;

Query OK, 0 rows affected (0.11 sec)

mysql> select table_id, name, space from INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE name LIKE ‘test/%‘;

+----------+----------------------------------------------------+-------+

| table_id | name                                               | space |

+----------+----------------------------------------------------+-------+

|       54 | test/FTS_0000000000000030_0000000000000039_INDEX_1 |    40 |

|       55 | test/FTS_0000000000000030_0000000000000039_INDEX_2 |    41 |

|       56 | test/FTS_0000000000000030_0000000000000039_INDEX_3 |    42 |

|       57 | test/FTS_0000000000000030_0000000000000039_INDEX_4 |    43 |

|       58 | test/FTS_0000000000000030_0000000000000039_INDEX_5 |    44 |

|       59 | test/FTS_0000000000000030_0000000000000039_INDEX_6 |    45 |

|       51 | test/FTS_0000000000000030_BEING_DELETED            |    37 |

|       52 | test/FTS_0000000000000030_BEING_DELETED_CACHE      |    38 |

|       53 | test/FTS_0000000000000030_CONFIG                   |    39 |

|       49 | test/FTS_0000000000000030_DELETED                  |    35 |

|       50 | test/FTS_0000000000000030_DELETED_CACHE            |    36 |

|       45 | test/b#P#p0                                        |    31 |

|       46 | test/b#P#p1                                        |    32 |

|       47 | test/b#P#p2                                        |    33 |

|       21 | test/imptest                                       |     7 |

|       48 | test/opening_lines                                 |    34 |

|       20 | test/product                                       |     6 |

|       42 | test/t                                             |    28 |

+----------+----------------------------------------------------+-------+

18 rows in set (0.01 sec)

首先在資料庫內建立一個有全文索引的表”opening_lines”，然後查看innodb的系統資料表資訊。

類似test/FTS_XXXXXXX_XXXXXXXX_INDEX_XX的就是索引表。索引表以FTS_為首碼，INDEX_XX為尾碼。表的命名規則如下：我們可以看到，opeining_lines表對應的table_id是48，48轉換為16進位為30，而索引表的名稱中第一個底線後面也是跟的00000..030，它們是相對應的。另外一個具有對應關係的是索引表名稱中第二個底線後面的值，此例中該值為39，轉換為10進位是57，這個值為索引ID。根據索引ID可查詢到table_id。

mysql> select index_id,name,table_id,space from INFORMATION_SCHEMA.INNODB_SYS_INDEXES WHERE index_id=57;

+----------+------+----------+-------+

| index_id | name | table_id | space |

+----------+------+----------+-------+

|       57 | idx  |       48 |    34 |

+----------+------+----------+-------+

1 row in set (0.01 sec)

可以看到，查詢出來的值所對應的table_id就是opening_lines所對應的table_id。

全文索引將文檔內的單詞過濾出來存放到不同的索引表裡，此操作可能在高並發的情況下產生大量的I/O操作，從而影響系統效能。為此，MySQL設計了全文索引緩衝。該緩衝儲存最近插入的資料索引，當緩衝存滿時，再將索引資料批量寫入到磁碟中。由於存在緩衝，在事務中，MySQL對全文索引有特殊的處理方式，全文索引必須在事務提交後才會生效。可以參考以下例子：

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO opening_lines(opening_line,author,title) VALUES

    -> (‘Call me Ishmael.‘,‘Herman Melville‘,‘Moby-Dick‘),

    -> (‘A screaming comes across the sky.‘,‘Thomas Pynchon‘,‘Gravity\‘s Rainbow‘),

    -> (‘I am an invisible man.‘,‘Ralph Ellison‘,‘Invisible Man‘),

    -> (‘Where now? Who now? When now?‘,‘Samuel Beckett‘,‘The Unnamable‘),

    -> (‘It was love at first sight.‘,‘Joseph Heller‘,‘Catch-22‘),

    -> (‘All this happened, more or less.‘,‘Kurt Vonnegut‘,‘Slaughterhouse-Five‘),

    -> (‘Mrs. Dalloway said she would buy the flowers herself.‘,‘Virginia Woolf‘,‘Mrs. Dalloway‘),

    -> (‘It was a pleasure to burn.‘,‘Ray Bradbury‘,‘Fahrenheit 451‘);

Query OK, 8 rows affected (0.02 sec)

Records: 8  Duplicates: 0  Warnings: 0

mysql> SELECT COUNT(*) FROM opening_lines WHERE MATCH(opening_line) AGAINST(‘Ishmael‘);

+----------+

| COUNT(*) |

+----------+

|        0 |

+----------+

1 row in set (0.02 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COUNT(*) FROM opening_lines WHERE MATCH(opening_line) AGAINST(‘Ishmael‘);

+----------+

| COUNT(*) |

+----------+

|        1 |

+----------+

1 row in set (0.00 sec)

在提交前，查詢不到關鍵詞’Ishmael’，在提交後就查詢到了。

雜湊索引

啟動雜湊索引的參數為innodb_adaptive_hash_index。雜湊索引將B-tree索引樹上的關鍵字進行雜湊處理存放到雜湊表上。雜湊索引主要用於精確尋找，如=，IN等。像LIKE或萬用字元的尋找不適合使用雜湊索引。雜湊索引由於是對索引關鍵字進行雜湊操作，而變換之後的雜湊值的大小關係無法與原始值相對應。所以雜湊索引無法被用來避免資料的排序操作。

本文出自 “架構師之路” 部落格，請務必保留此出處http://wangweiak47.blog.51cto.com/2337362/1592569

細聊MySQL的Innodb儲存引擎（完）