聚集索引
聚集索引又称为聚簇索引，聚集索引就是按照每张表的主键构造一颗 B+ 树，同时叶子节点中存放的即是整张表的行记录数，也称为数据页。这种特性决定了索引组织表中的数据也是索引的一部分；
注意：
由于磁盘上的数据页只能按照一个顺序进行存储，因此每张表只能按照一个 B+ 树进行排序，所以每张表最多只能有一个聚集索引。
辅助索引
辅助索引也称为非聚集索引，叶子结点并不包含行记录的全部数据，而是一个指向该行数据的聚集索引的书签。
联合索引
联合索引指对表上的多列进行索引
image.png
联合索引的意思就相当于数组按照多个字段进行排序，如：a，b 两列创建联合索引，那么叶子节点上的数据先按照 a 列值进行排序，如果 a 列值相等在按照 b 列值进行排序，因此也可以理解联合索引在什么情况下会失效。
注意：联合索引的使用及失效情况
// 1、创建一个三列的联合索引
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、调换索引列顺序后也是可以生效的
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)
// 或者条件中缺少第二或第三列，联合索引也可以生效
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)
根据联合索引实现方式，很容易理解为什么第三种查询索引会失效。因为联合索引首先是按照 a 列进行排序的，b 列和 c 列此时是散列放置的，如果只是按照 b 和 c 来查询，索引是无法起作用的；
覆盖索引
覆盖索引是指从辅助索引中就可以查到记录，而不需要查询聚集索引中的记录。（从索引文件中即可查到结果，不需要查询原表数据）
// 联合索引如果只使用 b 当做条件查询所有列值时，索引是无法起作用的，但是如果是统计或者只查询 b 列值，联合索引就可起作用
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、创建一个普通索引
create index in_d on joint(d);
// 2、只查询索引列数据
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，表示使用了覆盖索引。索引文件通常比数据文件要小，因此直接从索引文件中查询数据会比从数据文件中差数据要快。
哈希算法
Innodb 支持哈希索引，但是不能通过人为进行干预，是数据库自己创建的，称为自适应哈希。
全文索引
Mysql 5.6 之前 Innodb 不支持全文索引，5.6版本之后支持全文索引。
全文索引是通过倒叙排序来实现的，它的辅助表中存储了单词与单词自身在一个或者多个文档中所在位置之间的映射。Innodb 采用的是 full inverted index 的形式来存储映射关系，即{单词，（单词所在文档 id，文档具体位置）}
// 1、创建索引
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)
