Java教程

索引

本文主要是介绍索引,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

十、索引

1 介绍

索引(Index)是帮助MySQL高效获取数据的数据结构——索引是一种数据结构,是数据库管理系统中一个排序的数据结构,以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。一般来说索引本身占用内存空间也很大,不可能全部存储在内存中,因此索引往往以文件形式存储在硬盘上,占据一定的物理空间。

更通俗的说,索引就相当于目录。为了方便查找书中的内容,通过对内容建立索引形成目录。可以简单理解为排好序的快速查找数据结构",即索引 = 排序 + 查找,建立索引之后,查找起来会变快。

2 建立索引

2.1 单值索引

假设有如下查询语句经常被使用:

SELECT * FROM user WHERE NAME = 'xxx';

可以通过建立索引,使得杂乱的数据变得有序、易查询。

CREATE INDEX idx_user_name ON USER (name);
# idx_user_name:约定俗成的命名规范,idx表示这是一个索引,user表示在user表建立索引,name是建立索引的字段

2.2 复合索引

复合索引就是给多个字段建立索引:

CREATE INDEX idx_user_name ON USER (name,email);

3 索引的优缺点

索引的优点:

  • 可以大大加快数据的检索速度,这也是创建索引的最主要的原因。
  • 通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。
  • 降低数据排序成本,降低了CPU的消耗

索引的缺点:

  • 时间方面:创建索引和维护索引要耗费时间,具体地,当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,会降低增/改/删的执行效率;
  • 空间方面:索引需要占物理空间。

4 索引分类

主键索引:数据列不允许重复,不允许为NULL,一个表只能有一个主键。

  • 可以通过 ALTER TABLE table_name ADD PRIMARY KEY(column); 创建主键索引

唯一索引:数据列不允许重复,允许为NULL值,一个表允许多个列创建唯一索引。

  • 可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引
  • 可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一复合索引

普通索引:基本的索引类型,没有唯一性的限制,即一个索引只包含单个列,允许为NULL值。一个表可以有多个单列索引;建议一张表索引不要超过5个。

  • 可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引
  • 可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建复合索引

全文索引: 是目前搜索引擎使用的一种关键技术。

  • 可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引

5 索引语法

5.1 建立索引

CREATE [UNIQUE] INDEX  indexName ON mytable(columnname(length));
# 或者
ALTER mytable ADD [UNIQUE]  INDEX [indexName] ON(columnname(length));

# 如果是CHAR和VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定length。

5.2 删除索引

DROP INDEX [indexName] ON mytable;

5.3 查看索引

\G表示将查询到的横向表格纵向输出,方便阅读

SHOW INDEX FROM table_name\G

6 索引的数据结构

索引的数据结构和具体存储引擎的实现有关,在MySQL中使用较多的索引有Hash索引,B+树索引等,而我们经常使用的InnoDB存储引擎的默认索引实现为:B+树索引。对于哈希索引来说,底层的数据结构就是哈希表,因此在绝大多数需求为单条记录查询的时候,可以选择哈希索引,查询性能最快;其余大部分场景,建议选择BTree索引。

6.1 BTree索引

B树索引是Mysql数据库中使用最频繁的索引类型,基本所有存储引擎都支持BTree索引。通常我们说的索引不出意外指的就是(B树)索引。

如下图的一颗 B 树, 浅蓝色的块我们称之为一个磁盘块, 可以看到每个磁盘块包含几个数据项(深蓝色所示) 和指针(黄色所示)。比如磁盘块 1 包含数据项 17 和 35, 包含指针 P1、 P2、 P3。P1 表示小于 17 的磁盘块, P2 表示在 17 和 35 之间的磁盘块, P3 表示大于 35 的磁盘块,以此类推。

如果要查找数据项 29, 那么首先会把磁盘块 1 由磁盘加载到内存, 此时发生一次 IO, 在内存中用二分查找确定 29在 17 和 35 之间, 锁定磁盘块 1 的 P2 指针, 内存时间因为非常短(相比磁盘的 IO) 可以忽略不计
通过磁盘块 1的 P2 指针的磁盘地址把磁盘块 3 由磁盘加载到内存, 发生第二次 IO, 29 在 26 和 30 之间, 锁定磁盘块 3 的 P2 指针
通过指针加载磁盘块 8 到内存, 发生第三次 IO, 同时内存中做二分查找找到 29, 结束查询, 总计三次 IO。
image

6.2 B+Tree索引

B-树的关键字(数据项)和记录是放在一起的; B+树的非叶子节点中只有关键字和指向下一个节点的索引, 记录只放在叶子节点中。

在 B 树中, 越靠近根节点的记录查找时间越快, 只要找到关键字即可确定记录的存在; 而 B+ 树中每个记录的查找时间基本是一样的, 都需要从根节点走到叶子节点, 而且在叶子节点中还要再比较关键字。从这个角度看 B 树的性能好像要比 B+ 树好, 而在实际应用中却是 B+ 树的性能要好些。 因为 B+ 树的非叶子节点不存放实际的数据,这样每个节点可容纳的元素个数比 B 树多, 树高比 B 树小, 这样带来的好处是减少磁盘访问次数。

尽管 B+ 树找到一个记录所需的比较次数要比 B 树多, 但是一次磁盘访问的时间相当于成百上千次内存比较的时间, 因此实际中B+ 树的性能可能还会好些, 而且 B+树的叶子节点使用指针连接在一起, 方便顺序遍历(范围搜索), 这也是很多数据库和文件系统使用 B+树的缘故。

下图是一个B+树的示例:

image

B+tree性质:

  • B+跟B树不同B+树的非叶子节点不保存关键字记录的指针,只进行数据索引,这样使得B+树每个非叶子节点所能保存的关键字大大增加

  • B+树叶子节点保存了父节点的所有关键字记录的指针,所有数据地址必须要到叶子节点才能获取到。所以每次数据查询的次数都一样

  • 叶子节点本身依关键字的大小自小而大顺序链接。左边结尾数据都会保存右边节点开始数据的指针。

总的来说,B+树查找的效率要比B树更高、更稳定。由于非叶子节点并不是最终指向文件内容的节点, 而只是叶子节点中关键字的索引, 所以任何关键字的查找必须走一条从根节点到叶子节点的路。 所有关键字查询的路径长度相同,使得每一个数据的查询效率相当。

6.3 Hash索引

类似于数据结构中简单实现的HASH表(散列表)一样,当我们在mysql中用哈希索引时,主要就是通过Hash算法(常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法),将数据库字段数据转换成定长的Hash值,与这条数据的行指针一并存入Hash表的对应位置;如果发生Hash冲突(两个不同关键字的Hash值相同),则在对应Hash键下以链表形式存储(拉链法),如图:

image

7 何时需要建索引

7.1 适合建立索引的情况

  • 主键自动建立唯一索引
  • 频繁作为查询的条件的字段应该创建索引
  • 查询中与其他表关联的字段,外键关系建立索引
  • 查询中排序的字段,排序字段若通过索引去访问将大大提高排序的速度
  • 查询中统计或者分组字段

7.2 不适合建立索引的情况

  • 频繁更新的字段不适合创建索引
  • Where 条件里用不到的字段不创建索引
  • 表记录太少
  • 数据重复且分布平均的表字段,注意,如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。假如一个表有10万行记录,有一个字段A只有T和F两种值,且每个值的分布概率大约为50%,那么对这种表A字段建索引一般不会提高数据库的查询速度。

7.3 总结

索引的创建最好符合以下原则:

  • 最左前缀匹配原则。组合索引非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。
  • 较频繁作为查询条件的字段才去创建索引
  • 更新频繁字段不适合创建索引
  • 若是不能有效区分数据的列不适合做索引列(如性别,男女未知,最多也就三种,区分度实在太低)
  • 尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。
  • 定义有外键的数据列一定要建立索引。
  • 对于那些查询中很少涉及的列,重复值比较多的列不要建立索引。
  • 对于定义为text、image和bit的数据类型的列不要建立索引。

另外,索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录,表索引列有1980个不同的值,那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1,这个索引的效率就越高。

8 聚簇索引、回表

8.1 基本概念

聚簇索引:将数据与索引存放到一起,找到索引就可以直接找到数据。

非聚簇索引:将数据存储与索引分开,索引结构的叶子节点存储的是对应数据的地址,并不能直接获取到数据。

回表:举个例子,非聚簇索引中找到了叶子节点,获取叶子节点存储的地址,根据这个地址再去查找一次,这个过程叫做回表。

下图为一个聚簇索引:

image

一个表建立后,如果有主键,主键就是默认的聚簇索引。如果表中没有定义主键,InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引,InnoDB会隐式定义一个主键来作为聚簇索引。

由于存储引擎负责实现索引,因此不是所有的存储引擎都支持聚簇索引(比如MyISAM不支持聚簇索引),MyISAM使用非聚簇索引,如下图:
image

需要注意:

  • 聚簇索引不是一种单独的数据类型,而是一种数据存储方式。

  • InnoDB的聚簇索引实际上在同一结构中保存了B+Tree索引和数据,当表有聚簇索引时,它的数据行实际上存放在索引的叶子节点中。

  • 因为无法同时把数据行放在两个不同的地方,所以一个表只能有一个聚簇索引。

  • InnoDB中,在聚簇索引之上创建的索引称之为辅助索引,辅助索引访问数据总是需要二次查找,非聚簇索引都是辅助索引。辅助索引叶子节点存储的不再是行的物理位置,而是主键值

8.2 聚簇索引和费聚簇索引的区别

image

InnoDB使用的是聚簇索引,将主键组织到一棵B+树中,而行数据就储存在叶子节点上,若使用"where id = 14" 这样的条件查找主键,则按照B+树的检索算法即可查找到对应的叶节点,之后获得行数据。

若对Name列进行条件搜索,则需要两个步骤:第一步在辅助索引B+树中检索Name,到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作,最终到达叶子节点即可获取整行数据。(重点在于通过其他键需要建立辅助索引)

聚簇索引优点:

  • 由于行数据和叶子节点存储在一起,同一页中会有多条行数据,访问同一数据页不同行记录时,已经把页加载到了Buffer中,再次访问的时候,会在内存中完成访问,不必访问磁盘。这样主键和行数据是一起被载入内存的,找到叶子节点就可以立刻将行数据返回了,如果按照主键Id来组织数据,获得数据更快。
  • 聚簇索引适合用在排序的场合,非聚簇索引不适合
  • 取出一定范围数据的时候,使用用聚簇索引速度快
  • 可以把相关数据保存在一起。例如实现电子邮箱时,可以根据用户 ID 来聚集数据,这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引,则每封邮件都可能导致一次磁盘 I/O。

聚簇索引缺点:

  • 如果使用UUId(随机ID)作为主键,使数据存储稀疏,这就会出现聚簇索引有可能有比全表扫面更慢。聚簇索引的数据的物理存放顺序与索引顺序是一致的,即:只要索引是相邻的,那么对应的数据一定也是相邻地存放在磁盘上的。因此,主键通常使用自增id。
  • 如果主键比较大的话,那辅助索引将会变的更大,因为辅助索引的叶子存储的是主键值;过长的主键值,会导致非叶子节点占用占用更多的物理空间。

下图展示了使用uuid主键和自增主键的区别:
image
image

8.3 非聚簇索引与回表

非聚簇索引不一定进行回表查询。

这涉及到查询语句所要求的字段是否全部命中了索引,如果全部命中了索引,那么就不必再进行回表查询。

举个简单的例子,假设我们在员工表的年龄上建立了索引,那么当进行SELECT age FROM employee WHERE age < 20的查询时,在索引的叶子节点上,已经包含了age信息,就不会再次进行回表查询。

8.4 何时使用聚簇索引与非聚簇索引

image

9 MyISAM索引与InnoDB索引的区别

  • InnoDB索引是聚簇索引,MyISAM索引是非聚簇索引。
  • InnoDB的主键索引的叶子节点存储着行数据,因此主键索引非常高效。
  • MyISAM索引的叶子节点存储的是行数据地址,需要再寻址一次才能得到数据。
  • InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据,因此查询时做到覆盖索引会非常高效。

覆盖索引:

如果要查询的字段都建立过索引,那么引擎会直接在索引表中查询而不会访问原始数据(否则只要有一个字段没有建立索引就会做全表扫描),这叫索引覆盖。因此我们需要尽可能的在select后只写必要的查询字段,以增加索引覆盖的几率。

这里值得注意的是不要想着为每个字段建立索引,因为优先使用索引的优势就在于其体积小。

10 联合索引

MySQL可以使用多个字段同时建立一个索引,叫做联合索引。在联合索引中,如果想要命中索引,需要按照建立索引时的字段顺序挨个使用,否则无法命中索引。
image
如图,我们在三个列上建立了一个联合索引:整形id、字符串name、日期birthDate。那么索引的排序为:先按照id 排序,如果id 相同,则按照name排序,如果name的值也相等,则按照birthDate进行排序。

当进行查询时,此时索引仅仅按照id严格有序,因此必须首先使用id字段进行等值查询,之后对于匹配到的列而言,其按照name字段严格有序,此时可以使用name字段用做索引查找,以此类推。如果直接按照name查找: select* from employee where name='Staff' 就不会走索引,因为name是依靠着id进行排序的。

因此,在建立联合索引的时候应该注意索引列的顺序,一般情况下,将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。

这篇关于索引的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!