mysql索引(index)详情介绍

---

一 索引

1.1 索引介绍

​ 索引（index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足

特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构

上实现高级查找算法，这种数据结构就是索引。

​ 没有索引的情况下，类似执行select * from user where age = 45;的语句，就需要从第一行开始扫描，一直扫描到最后一行，我们称之为 全表扫描，性能很低。

1.2 特点

• 优势： 提高数据检索的效率，降低数据库的IO成本；通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消 耗。

• 劣势： 索引列也是要占用空间的；索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低。

二 索引结构

2.1 概述

索引结构	描述	InnoDB	MyISAM	Memory
B+Tree索引	最常见的索引类型，大部分引擎都支持 B+ 树索引	支持	支持	支持
Hash索引	底层数据结构是用哈希表实现的, 只有精确匹配索引列的查询才有效, 不支持范围查询	不支持	不支持	支持
R-tree(空间索引）	空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少	不支持	支持	不支持
Full-text(全文 索引)	是一种通过建立倒排索引,快速匹配文档的方式。类似于 Lucene,Solr,ES	5.6版本之后支持	支持	不支持

2.2 B-Tree

​ B-Tree，B树是一种多叉路衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。

以一颗最大度数（max-degree）为5(5阶)的b-tree为例，那这个B树每个节点最多存储4个key，5个指针：

特点：

• 5阶的B树，每一个节点最多存储4个key，对应5个指针。

• 一旦节点存储的key数量到达5，就会裂变，中间元素向上分裂。

• 在B树中，非叶子节点和叶子节点都会存放数据。

2.3 B+Tree

B+Tree是B-Tree的变种，我们以一颗最大度数（max-degree）为4的b+tree为例，来看一下其结构示意图：

我们可以看到两部分：

• 绿色框框起来的部分，是索引部分，仅仅起到索引数据的作用，不存储数据。

• 红色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据。

B+Tree 与 B-Tree相比，主要有以下三点区别：

• 所有的数据都会出现在叶子节点。

• 叶子节点形成一个单向链表。

• 非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。

MySQL中优化之后的 B+Tree，在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能，利于排序。

为什么InnoDB存储引擎选择使用B+tree索引结构?

答：相对于二叉树，层级更少，搜索效率高；

​ 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；

​ 相对Hash索引，B+tree支持范围匹配及排序操作；

2.4 Hash索引

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在

hash表中。

如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可

特点：

1. Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，…）
2. 无法利用索引完成排序操作
3. 查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索引。

三 索引分类

3.1 索引分类

分类	含义	特点	关键字
主键索引	针对于表中主键创建的索引	默认自动创建, 只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	FULLTEXT
常规索引	快速定位特定数据	可以有多个

3.2 聚集索引&二级索引

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

分类	含义	特点
聚集索引(ClusteredIndex)	将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据	必须有,而且只有一个
二级索引(Secondary Index)	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

聚集索引选取规则:

• 如果存在主键，主键索引就是聚集索引。如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。

• 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

特点：

• 聚集索引的叶子节点下挂的是这一行的数据 。

• 二级索引的叶子节点下挂的是该字段值对应的主键值。

回表查询： 这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取

数据的方式，就称之为回表查询。

四 索引语法

1. 创建索引

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (
index_col_name,... ) ;

2. 查看索引

SHOW INDEX FROM table_name ; 

3. 删除索引

DROP INDEX index_name ON table_name ;

五 索引失效情况

1. 不要在索引列上进行函数运算操作， 索引将失效。
2. 字符串类型字段使用时，不加引号，索引将失效
3. 如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
4. 用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。当or连接的条件，左右两侧字段都有索引时，索引才会生效。

六 SQL提示以及设计原则

6.1 sql提示

1. use index ： 建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进行评估）
2. ignore index ： 忽略指定的索引。
3. force index ： 强制使用索引。

6.2 索引设计原则

1. 针对于数据量较大，且查询比较频繁的表建立索引。
2. 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。