索引

发表于 更新于
本文字数： 4.9k 阅读时长 ≈ 4 分钟

索引的优缺点

优点：

  1. 可以快速检索，减少 IO 次数，加快检索速度。
  2. 根据索引分组和排序，可以加快分组和排序。
  3. 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
  4. 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
  5. 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点：

  1. 索引本身也是表，因此也会占用存储空间，一般来说，索引表占用空间是数据表的 1.5 倍。
  2. 索引表的维护和创建需要时间成本，这个成本随着数据量增大而增大。
  3. 建立索引会降低数据表的修改操作（删除、增加、修改）的效率，因为在修改数据表的同时还需要修改索引表。

创建索引：

mysql 版本 5.0 之后单表最多建 16 个索引，索引最大长度 256 字节。

适合创建的情况：

  1. 在经常需要搜索的列上，可以加快搜索的速度；
  2. 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
  3. 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
  4. 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
  5. 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
  6. 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
  7. 数据量超过 300 的表应该有索引。
  8. 索引应该建立在小字段上，大文本字段，超长字段不要建立索引。
  9. 复合索引尽量使用单字段索引代替。
    1. 选择性较好的字段作为主字段。
    2. 几个字段是否经常以 AND 方式出现在 where 语句中。
    3. 如果复合索引中的字段经常单独出现在 where 语句中，应该拆分复合索引。
    4. 字段超过 3 个，就需要考虑必要性，减少字段数量。
    5. 如果字段上既有单字段索引，又有复合索引，一般删除复合索引。

不适合创建的情况：

  1. 对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
  2. 对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
  3. 对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
  4. 当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

索引失效

索引在不正确的使用时会失效，即语句执行计划不使用索引。

  1. like 以%开头，索引无效；当 like 前缀没有%，后缀有%时，索引有效。前缀必须准确。
  2. or 语句前后没有同时使用索引。当 or 左右查询字段只有一个是索引，该索引失效，只有当 or 左右查询字段均为索引时，才会生效。
  3. 组合索引，不是使用第一列索引，索引失效。
  4. 数据类型出现隐式转化。如 varchar 不加单引号的话可能会自动转换为 int 型，使索引无效，产生全表扫描。
  5. 在索引列上使用 IS NULL 或 IS NOT NULL 操作。索引是不索引空值的，所以这样的操作不能使用索引，可以用其他的办法处理，例如：数字类型，判断大于 0，字符串类型设置一个默认值，判断是否等于默认值即可。
  6. 在索引字段上使用 not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。
  7. 对索引字段进行计算操作、字段上使用函数。
  8. 当全表扫描速度比索引速度快时，mysql 会使用全表扫描，此时索引失效。
  9. 一次查询不能用多个索引。
没有必要使用索引的情况：
  1. 唯一性差；
  2. 频繁更新的字段不用（更新索引消耗）；
  3. where 中不用的字段；
  4. 索引使用<>时，效果一般；

为什么使用 B+树做索引？


一颗高度为 3 的 B+树可以存放 21902400 个数据，足以满足大部分场景的需要。如果数据量更大就应该考虑分库分表。

  1. B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对 B 树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对 IO 读写次数就降低了。
  2. B+树的查询效率更加稳定：由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。
  3. 由于 B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是 B 树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以 B+树更加适合在区间查询的情况，所以通常 B+树用于数据库索引。
总结：

B 树在提高了 IO 性能的同时并没有解决元素遍历的我效率低下的问题，正是为了解决这个问题，B+树应用而生。B+树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而 B 树不支持这样的操作或者说效率太低。

索引分类

无语，我差点被面试官怼坏了，又给我问到 MySQL 索引

索引类型：

普通索引：

最普通的索引，没有任何限制，用于加速查询。

创建：
  1. CREATE TABLE mytable (name VARCHAR(32), INDEX index_mytable_name (name));
  2. CREATE INDEX index_mytable_name ON mytable(name);
  3. ALTER TABLE mytable ADD INDEX index_mytable_name (name);

唯一索引：

索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

创建：
  1. CREATE TABLE mytable ( name VARCHAR(32), UNIQUE index_unique_mytable_name (name));
  2. CREATE UNIQUE INDEX index_mytable_name ON mytable(name);
  3. ALTER TABLE mytable ADD UNIQUE INDEX index_mytable_name (name);

主键索引：

一种特殊的唯一索引，一个表只能有一个，不允许有空值，一般在建表的时候同时创建。

创建：
  1. CREATE TABLE mytable (id int(11) NOT NULL AUTO_INCREMENT , name VARCHAR(32), PRIMARY KEY (id));
  2. ALTER TABLE test.t1 ADD CONSTRAINT t1_pk PRIMARY KEY (id);

组合索引：

指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用，使用组合索引时遵循最左前缀原则。

创建:
  1. CREATE TABLE mytable (id int(11), name VARCHAR(32), INDEX index_mytable_id_name (id,name));
  2. CREATE INDEX index_mytable_id_name ON mytable(id,name);
  3. ALTER TABLE mytable ADD INDEX index_mytable_id_name (id,name);

全文索引：

主要用来查找文本中的关键字，而不是直接与索引中的值相比较。
fulltext 索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的 where 语句的参数匹配。
fulltext 索引配合 match against 操作使用，而不是一般的 where 语句加 like。
它可以在 create table，alter table ，create index 使用，不过目前只有 char、varchar，text 列上可以创建全文索引。

创建：
  1. CREATE TABLE article (id int(11) NOT NULL AUTO_INCREMENT, title char(250) NOT NULL, contents text NULL, create_at int(10) NULL DEFAULT NULL, PRIMARY KEY (id), FULLTEXT (contents));
  2. CREATE FULLTEXT INDEX index_article_contents ON article(contents);
  3. ALTER TABLE article ADD FULLTEXT INDEX index_article_contents (contents);

组织类型：

聚簇索引：

表数据按照索引的顺序来储存的。也就是说索引项的顺序与表中记录的物理顺序一致。在 B+树的索引中叶子节点可能储存了当前的 key 值，也可能储存了当前的 key 值以及整行的数据，在一张表上最多只能创建一个聚簇索引，因为真实数据的物理顺序只有一种。

非聚簇索引：

表数据存储顺序与索引顺序无关，对于非聚簇索引，叶子节点包含索引字段值及指向数据页数据行的逻辑指针。

聚簇与非聚簇的总结区别：

  1. 聚簇索引是一种稀疏索引，数据页的上一级的索引页储存的是页指针，而不是行指针，而非聚簇索引，则是密集索引，在数据页的上一级索引页它为每一个数据行存储一条索引记录。
  2. 在 InnoDB 中，只有主键索引是聚簇索引，如果没有主键，则挑选一个唯一键建立聚簇索引，如果没有唯一键，则隐式的生成一个键建立索引。
  3. 当查询使用聚簇索引时，在对应的叶子节点，可以获得到整行数据，因此不用再次进行回表查询。

覆盖索引：

  1. 就是 select 的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。
  2. 索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫 做覆盖索引。
  3. 是非聚集组合索引的一种形式，它包括在查询里的 Select、Join 和 Where 子句用到的所有列（即建立索引的字段正好是覆盖查询语句[select 子句]与查询条件[Where 子句]中所涉及的字段，也即，索引包含了查询正在查找的所有数据）。

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引的列，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以 MySQL 只能使用 B-Tree 索引做覆盖索引。

数据结构类型：

Hash 索引：

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。
对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。
对于 hash 冲突的采用链表方式解决冲突，类似于 hashmap。因为索引结构是十分紧凑的，所以 hash 索引的查询很快。

限制：
  1. 哈希索引只包含哈希值和行指针，而不存储字段值，索引不能用索引中的值来避免读取行。
  2. 哈希索引数据并不是安装索引值顺序存储的，所以也就无法用于排序。
  3. 哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。
  4. 哈希索引只支持等值比较查询，包括=，in()，<>。也不支持任何范围查询。
  5. 访问哈希索引的数据非常快，除非有很多哈希冲突。当出现哈希冲突时，存储引擎必须遍历链表中的所有指针，逐行进行比较，直到知道所有符合条件的行。
  6. 如果哈希冲突很高的话，一些索引维护操作的代价也会很高。例如，在某个选择性很低（冲突高）的列上建立哈希索引，那么当从表中删除一行时，存储引擎需要遍历对应哈希值的链表中的每一行，找到并删除对应行的引用，冲突越多，代价越大。

B+树索引：

说的是 B 树，其实是 B+树。
MySQL 和 B 树的那些事 - 平凡希 - 博客园