Loading

索引

十一、索引

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进行排序的。

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

posted @ 2022-01-27 06:30  yyyz  阅读(304)  评论(0)  编辑  收藏  举报