in mysql 索引字段 mysql in or 索引

索引

概述

• 索引是帮助数据库高效获取数据的数据结构，就像是一本字典的目录一样，因此也是有维护成本的。

比如，表中的数据经常被增删改，则不适合添加索引，索引能够加快查询效率，却降低了增删改的效率。

• 索引类型分为

• 主键索引（PRIMAY KEY）
• 唯一索引（UNIQUE）（在Mysql中，唯一索引和唯一约束有概念上差别，但在实际使用上并无差别，建表语句都相同）
• 常规索引（INDEX）
• 全文索引（FULLTEXT）

• 数据库索引主要有哈希索引、B+树索引、R 树和位图索引。 Mysql数据库中InnoDB引擎的索引默认是B+树

R 树通常用来为空间问题提供帮助。例如，一个查询要求“查询出所有距离我两公里之内的麦当劳”，如果数据库表使用R树索引，这类查询的效率将会提高。

位图索引（bitmap index）， 这类索引适合放在包含布尔值(true 和 false)的列上。

这两种不咋重要，主要是B+树其次是哈希

• 索引是在存储引擎中实现的，因此每种存储引擎的实现可能不同，并且每种存储引擎不一定支持所有类型，同时存储引擎可以定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表最少16个索引，总索引长度最少256字节，有的存储引擎会更多。
• 什么时候考虑给字段增加索引

1、数据量庞大

2、该字段很少进行DML操作

3、该字段经常出现在查询的where字句中

B+树索引

B+树

B树：

B树不是二叉树，可以有多个节点。B树不是某一种树，是符合一类特点的树统称为B树，又名"平衡多路查找树"。

首先，B树是一种平衡树，树左右子树的高度差小于等于1。

其次，是查找树（排序树），数据为升序排列（从左至右为由小到大）。（ps: B+树的非叶子节点不储存数据，只储存其子树的数据范围，即索引）

最后，是多路的，即有多个子树，且每个节点中有多条数据。

B+树：

是B树的升级版，在B树的基础上，只有叶子节点储存数据，而其他非叶子节点只储存数据的索引

• 非叶子节点的关键字储存的是其 子节点的 关键字的 最小值，其关键字数与子节点数相等；
• 叶子节点的关键字为真实数据。

树的所有叶节点构成一个有序链表，可以按照关键字排序的次序遍历全部记录。

非叶子节点的子节点数 = 关键字数

B+树索引

innoDB构造索引储存数据的单位是页，叶子节点储存的是真实数据，为数据页，非叶子节点储存的是真实数据的索引，为目录页。

innoDB的一个数据页至少可以存放两条数据

默认情况下，一个数据页大小为16KB

假设每个叶子节点能够储存100条关键字，非叶子节点可以储存1000条关键字，那么：

1层：只有一个节点，则可以储存100条数据

2层：1000*100 条

4层：10001000 * 1000100条

4层就能够储存相当多的数据了，所以一般情况下，用到的B+树都不会超过4层

innoDB索引数据结构

概述

使用B+树进行保存。

其中节点为索引页构成，节点内储存的具体数据参考 数据页结构章节。

在叶子节点中，主要保存的用户数据为 行格式组成的链表，链表的头尾是自动生成的最大最小记录。链表的节点有分组的概念，每个组被一个数组 页目录维护着。

在非叶子节点中，这个链表的节点就没有用户数据了，而是保存了树下一层级的某节点中的最小的记录的值（非官方生成的那个），以及此节点（页面）的指针。

这些节点中，同一层级的节点组成了一个双向链表。

图示

聚簇索引

索引按照物理实现方式，可以分为聚簇索引和非聚簇索引（也可以称为二级索引或辅助索引）

概述

聚簇索引并不是一种索引类型，而是一种数据的储存方式，即数据储存在B+树的叶子节点中，也就是所谓的“索引即数据，数据即索引”

术语 聚簇 ，表示数据行和相邻的键值聚簇的存储在一起

innodb储存数据的文件有.ibd结尾的，即是聚簇索引文件。MyISAM引擎索引跟数据就是分开的。

innodb的聚簇索引是针对主键构建的索引。

特点

1. 使用主键值的大大小进行记录和排序

• 页中的记录是按照主键大小排列的，最终形成一个单向链表
• 各个叶子节点按照主键大小，排序为一个双向链表

2. B+树的叶子节点储存的是完整的用户记录

• 即储存了所有列的值，包括隐藏列

这种聚簇索引并不需要显式的进行创建，innodb会自动帮我们创建。

优缺点

优点

• 数据访问很快，因为数据也在同一个树内
• 排序查找和范围查找也很快，因为都是顺序的
• 节省io操作，因为如果是查询一个范围内的数据，数据是连续的，不需要从多个数据块查询

缺点

• 插入顺序严重影响插入速度，如果非顺序插入，会出现页分裂的情况，影响性能。因此一般都会定义一个id作为主键，进行自增。
• 更新主键的代价很高，因为会导致被更新的行进行移动
• 二级索引访问需要两次索引查找，一是找到主键值，二是根据主键值找到数据

限制

• mysql目前只有innodb支持聚簇索引
• 由于数据物理存储方式只能有一种，所以每个表只能有一个聚簇索引，一般为主键
• 如果没有主键，innodb会选择非空的唯一索引代替。如果没有，则会隐式的定义一个主键来作为聚簇索引。
• 为了充分利用其特性，主键应该尽量选择有序的顺序id

二级索引

二级索引即非聚簇索引，相较于聚簇索引，它的叶子节点中并不储存万整的数据，而是储存对应数据的主键的值，而其查找、排序的依据，则为建立索引的那个字段。

比如：

有一张表，拥有两个字段：id、code，设置id为主键，并以code字段建立索引，则会生成聚簇索引和一个非聚簇索引，在根据code查询时，会先去二级索引中查找到对应的叶子节点获取到其数据，即 id值，再根据id值去聚簇索引中查询具体的数据，此过程被称为回表。

二级索引B+树的非叶子节点中也会保存子节点最左侧数据的主键值。

这是因为非主键字段是有可能相同的。在叶子节点保存数据时，此字段相同，则会根据主键值进行排序。而在非叶子节点中，也可能会出现其子节点的此字段全部相同，此时需要结合主键进行排序。（有点类似 索引字段+主键字段构建的联合索引）

联合索引

联合索引也是非聚簇索引，但是相对较为特殊，故此处单独说明。

联合索引，顾名思义，多个字段联合建立的。其B+树的构成为：

叶子节点中存储的有建立索引的多个字段和 主键字段。

非叶子节点中储存子节点中最左侧的 建立索引的多个字段值和子节点的页号

各条记录，由建立索引的字段的第一个值先排序，若相同，则按下一个排序。

如上图所示，蓝色数字字段为c1、紫色字母字段为c2，此B+树为c1、c2字段建立的联合索引的树。

构建过程

根页面位置不变

每当为某个表创建一个B+树索引(聚簇索引不是人为创建的，默认就有)的时候，都会为这个索引创建一个根节点页面。最开始表中没有数据的时候，每个B+树索引对应的根节点中既没有用户记录，也没有目录项记录。

随后向表中插入用户记录时，先把用户记录存储到这个根节点中。

当根节点中的可用空间用完时继续插入记录，此时会将根节点中的所有记录复制到一个新分配的页,比如页a中，然后对这个新页进行页分裂的操作，得到另一个新页，比如页b。这时新插入的记录根据键值(也就是聚簇索引中的主键值，二级索引中对应的索引列的值)的大小就会被分配到页a或者页b中，而根节点便升级为存储目录项记录的页。

这个过程特别注意的是: 一个B+树索引的根节点自诞生之日起，便不会再移动。这样只要我们对某个表建立一个索引，那么它的根节点的页号便会被记录到某个地方，然后凡是InnoDB存储引擎需要用到这个索引的时候，都会从那个固定的地方取出根节点的页号，从而来访问这个索引。

—— 尚硅谷

简而言之：即根节点的物理内存地址不变

• 向索引B+树中添加数据时，一开始是在根节点中的
• 此根节点满了之后，会复制一个节点作为叶子节点储存数据，之前的节点则作为非叶子节点（目录页）
• 此后再添加数据，则是在叶子节点中添加（该页分裂分裂、该移动数据位置移动）
• 直到此根节点储存满了索引，则再复制一个，作为其子节点，而其则清空，还为根节点。

优缺点

缺点

索引是个好东西，可不能乱建，它在空间和时间，上都会有消耗:
●空间上的代价

每建立一个索引都要为它建立一棵B+树, 每一棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，一棵很大的B+树由许多数据页组成，那 就是很大的一片存储空间。

●时间上的代价

每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。而且我们讲过，B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录，还是内节点中的记录(也就是不论是用户记录还是目录项记录)都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收等操作来维护好节点和记录的排序。如果我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作，会给性能拖后腿。

一个表上索引建的越多，就会占用越多的存储空间，在增删改记录的时候性能就越差。为了能建立又好又少的索引，我们得学学这些索引在哪些条件下起作用的。

优点

快

这里的快不止是因为B+树的是一颗查找树，更是因为其降低了I/O的次数，因为每读取一次数据页，都会进行一次I/O操作。而Mysql存储的数据很可能分布在多个数据页上。

其他索引

Hash索引

哈希索引（参考hash map）因为其特点，会难以处理：1、范围查找的情况。2、排序的情况。3、重复值较多会出现哈希冲突。

InnoDB本身不支持Hash索引，但是提供自适应Hash索引(Adaptive Hash Index)。什么情况下才会使用自适应Hash索引呢?

如果某个数据经常被访问，当满足一定条件的时候（达到一定次数），就会将这个数据页的地址存放到Hash表中。这样下次查询的时候，就可以直接找到这个页面的所在位置。这样让B+树也具备了Hash索引的优点。

采用自适应Hash索引目的是方便根据SQL的查询条件加速定位到叶子节点，特别是当B+树比较深的时候，通过自适应Hash索引可以明显提高数据的检索效率。

我们可以通过innodb_adaptive_hash_index变量来查看是否开启了自适应Hash,比如:

mysql> show variables like' %adaptive_ hash_ index';