聚集索引

聚集索引又稱為聚簇索引，聚集索引就是按照每張表的主鍵構造一顆 B+ 樹，同時葉子節(jié)點中存放的即是整張表的行記錄數(shù)，也稱為數(shù)據(jù)頁。這種特性決定了索引組織表中的數(shù)據(jù)也是索引的一部分；

注意：
由于磁盤上的數(shù)據(jù)頁只能按照一個順序進行存儲，因此每張表只能按照一個 B+ 樹進行排序，所以每張表最多只能有一個聚集索引。

輔助索引

輔助索引也稱為非聚集索引，葉子結點并不包含行記錄的全部數(shù)據(jù)，而是一個指向該行數(shù)據(jù)的聚集索引的書簽。

聯(lián)合索引

聯(lián)合索引指對表上的多列進行索引

image.png

聯(lián)合索引的意思就相當于數(shù)組按照多個字段進行排序，如：a，b 兩列創(chuàng)建聯(lián)合索引，那么葉子節(jié)點上的數(shù)據(jù)先按照 a 列值進行排序，如果 a 列值相等在按照 b 列值進行排序，因此也可以理解聯(lián)合索引在什么情況下會失效。

注意：聯(lián)合索引的使用及失效情況

// 1、創(chuàng)建一個三列的聯(lián)合索引
create index in_a_b_c on joint(a, b, c);

// 2、推薦使用方式
mysql> explain select * from joint where a = 'aa' and b = 'bb' and c = 'cc';
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref               | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | joint | NULL       | ref  | in_a_b_c      | in_a_b_c | 414     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.02 sec)

// 3、調(diào)換索引列順序后也是可以生效的
mysql> explain select * from joint where b = 'bb' and c = 'cc' and a = 'aa';
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref               | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | joint | NULL       | ref  | in_a_b_c      | in_a_b_c | 414     | const,const,const |   12 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

// 或者條件中缺少第二或第三列，聯(lián)合索引也可以生效
mysql> explain select * from joint where c = 'cc' and a = 'aa';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | joint | NULL       | ref  | in_a_b_c      | in_a_b_c | 138     | const |   12 |    10.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

// 4、索引失效
mysql> explain select * from joint where b = 'bb' and c = 'cc';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | joint | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 986832 |     1.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

根據(jù)聯(lián)合索引實現(xiàn)方式，很容易理解為什么第三種查詢索引會失效。因為聯(lián)合索引首先是按照 a 列進行排序的，b 列和 c 列此時是散列放置的，如果只是按照 b 和 c 來查詢，索引是無法起作用的；

覆蓋索引

覆蓋索引是指從輔助索引中就可以查到記錄，而不需要查詢聚集索引中的記錄。（從索引文件中即可查到結果，不需要查詢原表數(shù)據(jù)）

// 聯(lián)合索引如果只使用 b 當做條件查詢所有列值時，索引是無法起作用的，但是如果是統(tǒng)計或者只查詢 b 列值，聯(lián)合索引就可起作用
mysql> explain select count(*) from joint where b = 'bb';
+----+-------------+-------+------------+-------+---------------+----------+---------+------+--------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+--------+----------+--------------------------+
|  1 | SIMPLE      | joint | NULL       | index | NULL          | in_a_b_c | 414     | NULL | 986832 |    33.33 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+--------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)


// 1、創(chuàng)建一個普通索引
create index in_d on joint(d);
// 2、只查詢索引列數(shù)據(jù)
mysql> explain select d from joint;
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | joint | NULL       | index | NULL          | in_d | 138     | NULL | 986832 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

雖然 possible_keys 顯示為空，但是實際上是用到了索引的，并且 Extra 的值為 Using index，表示使用了覆蓋索引。索引文件通常比數(shù)據(jù)文件要小，因此直接從索引文件中查詢數(shù)據(jù)會比從數(shù)據(jù)文件中差數(shù)據(jù)要快。

哈希算法

Innodb 支持哈希索引，但是不能通過人為進行干預，是數(shù)據(jù)庫自己創(chuàng)建的，稱為自適應哈希。

全文索引

Mysql 5.6 之前 Innodb 不支持全文索引，5.6版本之后支持全文索引。

全文索引是通過倒敘排序來實現(xiàn)的，它的輔助表中存儲了單詞與單詞自身在一個或者多個文檔中所在位置之間的映射。Innodb 采用的是 full inverted index 的形式來存儲映射關系，即{單詞，（單詞所在文檔 id，文檔具體位置）}

// 1、創(chuàng)建索引
mysql> create fulltext index ft_index on demo_text(text);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> create fulltext index ft_index on demo_text(text) with parser ngram;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

// 2、使用索引
mysql> select * from demo_text where match(text) against ('one');
+----+---------------+
| id | text          |
+----+---------------+
|  1 | some one here |
|  2 | one two three |
+----+---------------+
2 rows in set (0.00 sec)

// 3、查詢每個詞的映射
mysql> set global innodb_ft_aux_table = 'myproject/demo_text';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from information_schema.innodb_ft_index_table;
+-------+--------------+-------------+-----------+--------+----------+
| WORD  | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+-------+--------------+-------------+-----------+--------+----------+
| four  |            5 |           5 |         1 |      5 |        9 |
| here  |            3 |           5 |         2 |      3 |        9 |
| here  |            3 |           5 |         2 |      5 |        4 |
| one   |            3 |           4 |         2 |      3 |        5 |
| one   |            3 |           4 |         2 |      4 |        0 |
| some  |            3 |           3 |         1 |      3 |        0 |
| three |            4 |           4 |         1 |      4 |        8 |
| two   |            4 |           5 |         2 |      4 |        4 |
| two   |            4 |           5 |         2 |      5 |        0 |
+-------+--------------+-------------+-----------+--------+----------+
9 rows in set (0.02 sec)