MySQL索引简介

1. 索引简介

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。可以得知索引的本质：索引是数据结构。索引的目的在于提高查询效率，可以类比字典，如果要查MySQL这个单词，我们肯定需要定位到M字母，然后再定位到y字母，再找到剩下的SQL。如果没有索引，那么你可能需要整本字典都找一遍，如果我想找到Java开头的单词呢？或者Oracle开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成。

对于索引，可以简单理解为排好序的快速查找的数据结构。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构，就是索引。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。索引的优点类似大学图书馆借书目录索引，提高数据检索的效率，降低数据库的IO成本。通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

索引的缺点：实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE，因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

2. 索引结构

2.1 B-Tree索引

二叉树弊端之一：二叉树很可能会发生两边不平衡的情况。B-Tree：（B：balance）会自动根据两边的情况自动调节，使两端无限趋近于平衡状态，可以使性能最稳定（MyISAM使用的方式）。B-Tree弊端：插入/修改操作多时，B-Tree会不断调整平衡，消耗性能，从侧面说明了索引不是越多越好。

B-Tree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如上图所示为一个3阶的B-Tree。每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址，两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

模拟查找关键字29的过程：

1. 根据根节点找到磁盘块1，读入内存。（磁盘I/O操作第1次）
2. 比较关键字29在区间（17，35），找到磁盘块1的指针P2。
3. 根据P2指针找到磁盘块3，读入内存。（磁盘I/O操作第2次）
4. 比较关键字29在区间（26，30），找到磁盘块3的指针P2。
5. 根据P2指针找到磁盘块8，读入内存。（磁盘I/O操作第3次）
6. 在磁盘块8中的关键字列表中找到关键字29。

分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率，而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。真实的情况是，3层的B-Tree可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

2.2 B+Tree索引

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。从上面的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同：非叶子节点只存储键值信息、所有叶子节点之间都有一个链指针、数据记录都存放在叶子节点中。

为什么说B+Tree比B-Tree更适合实际应用中操作系统的文件索引和数据库索引：

• B+Tree的磁盘读写代价更低：B+Tree的内部结点并没有指向关键字具体信息的指针，因此其内部结点相对B-Tree更小，如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存中的需要查找的关键字也就越多，相对来说IO读写次数也就降低了。
• B+Tree的查询效率更加稳定：由于非叶子节点只存储键值信息，所以任何关键字的查找必须走一条从根结点到叶子结点的路，所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

2.3 Full-text全文索引

全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术，它能够利用分词技术等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。

#不同于like方式的的查询：
SELECT * FROM article WHERE content LIKE '%查询字符串%';

#全文索引用match + against方式查询：
SELECT * FROM article WHERE MATCH(title, content) AGAINST ('查询字符串'); -- 明显的提高查询效率。

MySQL5.6.4以前只有MyISAM支持，5.6.4版本以后InnoDB才支持，但是官方版本不支持中文分词，需要第三方分词插件，5.7以后官方支持中文分词。随着大数据时代的到来，关系型数据库应对全文索引的需求已力不从心，逐渐被solr、elasticSearch等专门的搜索引擎所替代。

2.4 Hash索引

Hash索引只有Memory、NDB两种引擎支持，Memory引擎默认支持Hash索引，如果多个hash值相同，出现哈希碰撞，那么索引以链表方式存储。NoSql采用此种索引结构。

2.5 R-Tree索引

R-Tree在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDB、InnoDB、NDB、Archive几种。相对于B-Tree，R-Tree的优势在于范围查找。

2.6 聚簇索引与非聚簇索引

聚簇索引（也叫聚集索引）并不是一种单独的索引类型，而是一种数据存储方式。聚簇索引的表数据按照索引的顺序来存储的，也就是说索引项的顺序与表中记录的物理顺序一致。对于聚簇索引，叶子结点即存储了真实的数据行，不再有另外单独的数据页。在一张表上最多只能创建一个聚集索引，因为真实数据的物理顺序只能有一种。非聚簇索引的表数据存储顺序与索引顺序无关，对于非聚簇索引，叶子结点包含索引字段值及指向数据页数据行的逻辑指针，其行数量与数据表行数据量一致。

聚簇索引的好处：按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的io操作。

对于MySQL数据库，目前只有InnoDB数据引擎支持聚簇索引，而MyISAM并不支持聚簇索引。由于数据物理存储排序方式只能有一种，所以每个MySQL的表只能有一个聚簇索引，一般情况下就是该表的主键。为了充分利用聚簇索引的聚簇的特性，所以InnoDB表的主键列尽量选用有序的顺序id，而不建议用无序的id（比如uuid这种）。

3. 索引分类

主键索引：设定为主键后，数据库会自动建立索引，InnoDB为聚簇索引。

单值索引：即一个索引只包含单个列，一个表可以有多个单列索引。根据数据引擎类型自动选择的索引类型，除了InnoDB引擎主键默认为聚簇索引外，InnoDB的索引都采用的B+TREE，MyISAM则都采用的B-Tree索引。

唯一索引：索引列的值必须唯一，但允许有空值。

复合索引：即一个索引包含多个列。在数据库操作期间，对于相同的多个列建索引，复合索引比单值索引所需要的开销更小，当表的行数远大于索引列的数目时可以使用复合索引。

#创建语法
CREATE [UNIQUE] INDEX index_name ON tb_name(index_col_name, ...);

ALTER tb_name ADD [UNIQUE] INDEX indexName ON (index_col_name);

#删除语法
DROP INDEX indexName ON tb_name;

#查看索引
SHOW INDEX FROM tb_name\G

4. 索引建议

哪些情况需要创建索引：

• 主键自动建立主键索引。
• 频繁作为查询条件的字段应该创建索引（where后面的语句）。
• 查询中与其它表关联的字段，外键关系建立索引。
• 单键/组合索引的选择问题，倾向创建组合索引。
• 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。
• 查询中统计或者分组字段。

哪些情况不要创建索引：

• 表记录太少。
• 经常增删改的表。
• where条件里用不到的字段不创建索引。
• 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。