什么是索引?

  • 索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B 树， B+树和 Hash。
  • 索引的作用就相当于目录的作用。打个比方: 我们在查字典的时候，如果没有目录，那我们就只能一页一页的去找我们需要查的那个字，速度很慢。如果有目录了，我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了。

  • 在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向（引用）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。可以简单理解为索引是“排好序的快速查找数据结构”（例如，在链表中查询数据会慢于在二叉搜索树中查询）。一般来说索引本身也很大（比如二叉树会存左右孩子），不可能全部存储在内存中，因此往往以索引文件的形式存储在磁盘上。

    为什么要用索引？索引的优缺点分析

  • 索引的优点

    • 提高了数据检索的效率，降低了数据库的IO成本。
    • 通过索引列对数据进行排序，降低了数据排序的成本，降低了CPU的消耗。
    • 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

  • 索引的缺点

    • 创建索引和维护索引需要耗费许多时间：当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
    • 占用物理存储空间 ：索引需要使用物理文件存储，也会耗费一定空间。

      B 树和 B+树区别

      MySQL若采用普通二叉树作为索引结构，会存在一个问题，当二叉树严重失衡，其结构会倾向于链表（复杂度O(N)）。所以需要使用平衡二叉树（复杂度稳定在O(logN)），平衡二叉树有B树和B+树这两种：

  • B 树的所有节点既存放键(索引值) 也存放数据(data)；而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。

  • B 树的叶子节点都是独立的；B+树的叶子节点有一条引用链指向与它相邻的叶子节点。B+树叶节点两两相连可大大增加区间访问性，可使用在范围查询等，而B树每个节点 key 和 data 在一起，则无法区间查找。
  • B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了（最快是O(1)）。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显(固定为O(logn))。
  • B+树更适合外部存储。由于内节点无 data 域，每个节点能索引的范围更大更精确。（磁盘 IO 数据大小是固定的，在一次 IO 中，单个元素越小，量就越大。这就意味着B+树单次磁盘 IO 的信息量大于B-树，从这点来看B+树相对B-树磁盘 IO 次数少。）

索引 - 图1
索引 - 图2

为什么B树的效率更高，但是mysql选择B+树作为索引结构？

  1. 更少的IO次数：索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。B+树的非叶节点只包含键，而不包含真实数据，因此每个节点存储的记录个数比B树多很多（即阶m更大），因此B+树的高度更低，访问时所需要的IO次数更少。此外，由于每个节点存储的记录数更多，所以对访问局部性原理的利用更好，缓存命中率更高。
  2. 更适于范围查询：在B树中进行范围查询时，首先找到要查找的下限，然后对B树进行中序遍历，直到找到查找的上限；而B+树的范围查询，只需要对链表进行遍历即可。
  3. 更稳定的查询效率：B树的查询时间复杂度在1到树高之间(分别对应记录在根节点和叶节点)，而B+树的查询复杂度则稳定为树高，因为所有数据都在叶节点。

    innodb中一颗B+树能存储多少条数据？

  • innodb存储引擎的最小存储单元为16k(就像操作系统的最小单元为4k即一页)，即B+树的一个节点的大小为16k。
  • 数据表中的数据都是存储在页中的，假设一行数据的大小是1k，那么一个页可以存放16行这样的数据。
  • 设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，即16384/14=1170。那么可以算出一棵高度为2的B+树，能存放1170*16=18720条（第一层一个节点1170条记录，第二层1170个节点，一个节点16条记录）这样的数据记录。根据同样的原理我们可以算出一个高度为3的B+树可以存放：1170 1170 16=21902400条这样的记录。

  • 所以在InnoDB中B+树高度一般为1-3层，它就能满足千万级的数据存储。在查找数据时一次页的查找代表一次IO，所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据。

    Hash 索引和 B+树索引优劣分析

  • Hash 索引定位快
    Hash 索引指的就是 Hash 表，最大的优点就是能够在很短的时间内，根据 Hash 函数定位到数据所在的位置，这是 B+树所不能比的。

  • Hash 冲突问题
    知道 HashMap 或 HashTable 的同学，相信都知道它们最大的缺点就是 Hash 冲突了。不过对于数据库来说这还不算最大的缺点。
  • Hash 索引不支持顺序和范围查询(Hash 索引不支持顺序和范围查询是它最大的缺点。
    试想一种情况: SELECT * FROM tb1 WHERE id < 500;

B+树是有序的，在这种范围查询中，优势非常大，直接遍历比 500 小的叶子节点就够了。而 Hash 索引是根据 hash 算法来定位的，难不成还要把 1 - 499 的数据，每个都进行一次 hash 计算来定位吗?这就是 Hash 最大的缺点了。

索引类型(从逻辑角度分类)

1. 普通索引或者单列索引

普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。是最基本的索引，它没有任何限制。它有以下几种创建方式：

  1. （1）直接创建索引
  2. CREATE INDEX index_name ON table_name(column_name)
  4. （2）修改表结构的方式添加索引
  5. ALTER TABLE table_name ADD INDEX index_name ON (column_name)
  7. （3）创建表的时候同时创建索引
  8. CREATE TABLE `table_name` (
  9.     `id` int(11) NOT NULL AUTO_INCREMENT ,
  10.     `title` char(255) CHARACTER NOT NULL ,
  11.     `content` text CHARACTER NULL ,
  12.     `time` int(10) NULL DEFAULT NULL ,
  13.     PRIMARY KEY (`id`),
  14.     INDEX index_name (title)
  15. )
  17. （4）删除索引
  18. DROP INDEX index_name ON table_name

2. 唯一索引

唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

1）创建唯一索引
CREATE UNIQUE INDEX indexName ON table_name(column_name)

（2）修改表结构
ALTER TABLE table_name ADD UNIQUE indexName ON (column_name)

（3）创建表的时候直接指定
CREATE TABLE `table_name` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    UNIQUE indexName (title)
);

3. 主键索引：

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

CREATE TABLE `table_name` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`)
);

4. 多列索引（组合索引）：

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

ALTER TABLE `table_name` ADD INDEX name_city_age (name,city,age);

5. 空间索引：

空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建

1. CREATE TABLE table_name[col_name data type]
2. [unique|fulltext|spatial][index|key][index_name](col_name[length])[asc|desc]

1、unique|fulltext|spatial为可选参数，分别表示唯一索引、全文索引和空间索引；
2、index和key为同义词，两者作用相同，用来指定创建索引
3、col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择；
4、index_name指定索引的名称，为可选参数，如果不指定，MYSQL默认col_name为索引值；
5、length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
6、asc或desc指定升序或降序的索引值存储

6. 前缀索引(Prefix) ：

前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。

7. 全文索引(Full Text) ：

全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。

聚集索引与非聚集索引(从物理存储角度分类)

1. 聚集索引

聚集索引即索引结构和数据一起存放的索引。叶子节点是完整的数据记录
主键索引属于聚集索引。
在 Mysql 中，InnoDB 引擎的表的 .ibd文件就包含了该表的索引和数据，对于 InnoDB 引擎表来说，该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。

  • 聚集索引的优点
    • 聚集索引的查询速度非常的快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。

  • 聚集索引的缺点

    • 依赖于有序的数据 ：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。
    • 更新代价大 ： 如果索引列的数据被修改，那么对应的索引也将会被修改， 而且聚集索引的叶子节点还存放着数据，修改代价肯定是较大的， 所以对于主键索引来说，主键一般都是不可被修改的。

      2. 非聚集索引

      非聚集索引即索引结构和数据分开存放的索引。
      二级索引属于非聚集索引。

      MYISAM 引擎的表的.MYI 文件包含了表的索引， 该表的索引(B+树)的每个非叶子节点存储索引， 叶子节点存储索引和索引对应数据的指针，指向.MYD 文件的数据。 非聚集索引的叶子节点并不一定存放数据的指针， 因为innoDB的二级索引的叶子节点就存放的是主键，根据主键再回表查数据。

  • 非聚集索引的优点

    • 更新代价比聚集索引要小 。非聚集索引的更新代价就没有聚集索引那么大了，非聚集索引的叶子节点是不存放数据的
  • 非聚集索引的缺点
    • 跟聚集索引一样，非聚集索引也依赖于有序的数据
    • 可能会二次查询(回表) :这应该是非聚集索引最大的缺点了。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

这是 Mysql 的表的文件截图:
索引 - 图3
聚集索引和非聚集索引:
索引 - 图4

3. 非聚集索引一定回表查询吗(覆盖索引)?

非聚集索引不一定回表查询。

试想一种情况，用户准备使用 SQL 查询用户名，而用户名字段正好建立了索引。

SELECT name FROM table WHERE name='guang19';

那么这个索引的 key 本身就是 name，查到对应的 name 直接返回就行了，无需回表查询。

即使是 MYISAM 也是这样，虽然 MYISAM 的主键索引确实需要回表， 因为它的主键索引的叶子节点的data存放的是指针。但是如果 SQL 查的就是主键呢?
SELECT id FROM table WHERE id=1;
主键索引本身的 key 就是主键，查到返回就行了。这种情况就称之为覆盖索引了。

4. 覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。我们知道在 InnoDB 存储引擎中，如果不是主键索引（存放数据），叶子节点存储的是主键+列值（列值是data，主键是key）。最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢，覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！
覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了， 而无需回表查询。

如主键索引，如果一条 SQL 需要查询主键，那么正好根据主键索引就可以查到主键。 再如普通索引，如果一条 SQL 需要查询 name，name 字段正好有索引， 那么直接根据这个索引就可以查到数据，也无需回表。

覆盖索引:
索引 - 图5

索引创建注意点

1. 最左前缀原则

对于组合索引，要遵循最左前缀原则。

最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。

  • 如果有一个 2 列的索引 (col1, col2)，则已经对 (col1)、(col1, col2) 上建立了索引；
  • 如果有一个 3 列索引 (col1, col2, col3)，则已经对 (col1)、(col1, col2)、(col1, col2, col3) 上建立了索引，如果条件是where col1=a and col2=b可以命中，但是如果是where col1=a and col3=c则无法命中，因为不是从左边数连续的字段；

2. 选择合适的字段

  • 不为 NULL 的字段
    索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。
  • 被频繁查询的字段
    我们创建索引的字段应该是查询操作非常频繁的字段。
  • 被作为条件查询的字段
    被作为 WHERE 条件查询的字段，应该被考虑建立索引。

  • 被频繁用于连接的字段
    经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

    3. 不适合创建索引的字段

  • 被频繁更新的字段应该慎重建立索引
    虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。 如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。

  • 不被经常查询的字段没有必要建立索引
  • 尽可能的考虑建立联合索引而不是单列索引
    因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。
  • 注意避免冗余索引
    冗余索引指的是索引的功能相同，能够命中就肯定能命中 ，那么就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引，能够命中后者的查询肯定是能够命中前者的。在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。
  • 考虑在字符串类型的字段上使用前缀索引代替普通索引
    前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。

    4. 使用索引一定能提高查询性能吗?

    大多数情况下，索引查询都是比全表扫描要快的。但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。

    Mysql如何为表字段添加索引？

    ```sql
  1. 添加PRIMARY KEY（主键索引） ALTER TABLE table_name ADD PRIMARY KEY ( column )
  2. 添加UNIQUE(唯一索引) ALTER TABLE table_name ADD UNIQUE ( column )
  3. 添加INDEX(普通索引) ALTER TABLE table_name ADD INDEX index_name ( column )
  4. 添加FULLTEXT(全文索引) ALTER TABLE table_name ADD FULLTEXT ( column)
  5. 添加多列索引 ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 ) ```

    MyISAM和InnoDB实现BTree索引方式的区别

  • MyISAM
    B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。
  • InnoDB（主键索引+二级索引）
    其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”，而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。 因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。 PS：整理自《Java工程师修炼之道》

    MySQL怎么判断要不要加索引？

    建议按照如下的原则来创建索引：
  1. 当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高查询速度。
  2. 在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

    如何判断数据库的索引有没有生效？

    可以使用EXPLAIN语句查看索引是否正在使用。
    举例，假设已经创建了book表，并已经在其year_publication字段上建立了普通索引。执行如下语句：
    EXPLAIN SELECT * FROM book WHERE year_publication=1990;
    EXPLAIN语句将为我们输出详细的SQL执行信息，其中：
  • possible_keys行给出了MySQL在搜索数据记录时可选用的各个索引。
  • key行是MySQL实际选用的索引。

如果possible_keys行和key行都包含year_publication字段，则说明在查询时使用了该索引。
image.png

如何评估一个索引创建的是否合理？（索引创建原则）

建议按照如下的原则来设计索引：

  1. 避免对经常更新的表进行过多的索引，并且索引中的列要尽可能少。应该为经常用于查询的字段创建索引，但要避免添加不必要的字段。
  2. 数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。
  3. 在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列上不要建立索引。比如在学生表的“性别”字段上只有“男”与“女”两个不同值，因此就无须建立索引，如果建立索引不但不会提高查询效率，反而会严重降低数据更新速度。
  4. 当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高查询速度。

  5. 在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

    所有的字段都适合创建索引吗？

    下列几种情况，是不适合创建索引的：

  6. 频繁更新的字段不适合建立索引；

  7. where条件中用不到的字段不适合建立索引；
  8. 数据比较少的表不需要建索引；
  9. 数据重复且分布比较均匀的的字段不适合建索引，例如性别、真假值；

  10. 参与列计算的列不适合建索引。

    索引是越多越好吗？

    索引并非越多越好，一个表中如有大量的索引，不仅占用磁盘空间，还会影响INSERT、DELETE、UPDATE等语句的性能，因为在表中的数据更改时，索引也会进行调整和更新。

    数据库索引失效了怎么办？

    可以采用以下几种方式，来避免索引失效：

  11. 使用组合索引时，需要遵循“最左前缀”原则；

  12. 不在索引列上做任何操作，例如计算、函数、类型转换，会导致索引失效而转向全表扫描；
  13. 尽量使用覆盖索引（只访问索引列的查询），减少 **select *** 覆盖索引能减少回表次数；
  14. MySQL在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描；
  15. LIKE以通配符开头（%abc）MySQL索引会失效变成全表扫描的操作；
  16. 字符串不加单引号会导致索引失效（可能发生了索引列的隐式转换）；
  17. 少用or，用它来连接时会索引失效。

索引 - 图7

说一说索引的实现原理

在MySQL中，索引是在存储引擎层实现的，不同存储引擎对索引的实现方式是不同的，下面我们探讨一下MyISAM和InnoDB两个存储引擎的索引实现方式。

1. MyISAM索引实现：

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址（非聚集索引），MyISAM索引的原理图如下。这里假设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主键索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。
索引 - 图8
如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示。同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。
索引 - 图9

2. InnoDB索引实现：

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。
第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
下图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
索引 - 图10
第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。下图为定义在Col3上的一个辅助索引。这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。
索引 - 图11
了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

介绍一下数据库索引的重构过程

什么时候需要重建索引呢？

  1. 表上频繁发生update,delete操作；
  2. 表上发生了alter table ..move操作（move操作导致了rowid变化）。

怎么判断索引是否应该重建？

  1. 一般看索引是否倾斜的严重，是否浪费了空间，对索引进行结构分析：

    analyze index index_name validate structure;

  2. 在相同的session中查询index_stats表：

    select height,DEL_LF_ROWS/LF_ROWS from index_stats;

  3. 当查询的height>=4（索引的深度，即从根到叶节点的高度）或DEL_LF_ROWS/LF_ROWS>0.2的情况下，就应该考虑重建该索引。

如何重建索引？

  • drop原索引，然后再创建索引：

    drop index index_name;
create index index_name on table_name (index_column);

    这种方式相当耗时，一般不建议使用。

  • 直接重建索引：

    alter index indexname rebuild;
alter index indexname rebuild online;

    此方法较快，建议使用。
    rebuild是快速重建索引的一种有效的办法，因为它是一种使用现有索引项来重建新索引的方法。如果重建索引时有其他用户在对这个表操作，尽量使用带online参数来最大限度的减少索引重建时将会出现的任何加锁问题。由于新旧索引在建立时同时存在，因此，使用这种重建方法需要有额外的磁盘空间可供临时使用，当索引建完后把老索引删除，如果没有成功，也不会影响原来的索引。利用这种办法可以用来将一个索引移到新的表空间。

rebuild重建索引的过程：

  1. Rebuild以index fast full scan或table full scan方式（采用那种方式取决于cost）读取原索引中的数据来构建一个新的索引，重建过程中有排序操作，rebuild online执行表扫描获取数据，重建过程中有排序的操作；
  2. Rebuild会阻塞DML操作，rebuild online不会阻塞DML操作；
  3. rebuild online时系统会产生一个SYS_JOURNAL_xxx的IOT类型的系统临时日志表，所有rebuild online时索引的变化都记录在这个表中，当新的索引创建完成后，把这个表的记录维护到新的索引中去，然后drop掉旧的索引，rebuild online就完成了。

重建索引过程中的注意事项：

  1. 执行rebuild操作时，需要检查表空间是否足够；
  2. 虽然说rebuild online操作允许DML操作，但还是建议在业务不繁忙时间段进行；
  3. Rebuild操作会产生大量Redo Log；

联合索引的存储结构是什么，它的有效方式是什么？

从本质上来说，联合索引还是一棵B+树，不同的是联合索引的键值数量不是1，而是大于等于2，参考下图。另外，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，所以使用联合索引时遵循最左前缀集合。
索引 - 图12

模糊查询语句中如何使用索引？

在MySQL中模糊查询 mobile like ‘%8765’，这种情况是不能使用 mobile 上的索引的，那么如果需要根据手机号码后四位进行模糊查询，可以用一下方法进行改造。

我们可以加入冗余列（MySQL5.7之后加入了虚拟列，使用虚拟列更合适，思路相同），比如 mobile_reverse，内部存储为 mobile 的倒叙文本，如 mobile为17312345678，那么 mobile_reverse 存储 87654321371，为 mobile_reverse 列建立索引，查询中使用语句 mobile_reverse like reverse(’%5678’) 即可。
reverse 是 MySQL 中的反转函数，这条语句相当于 mobile_reverse like ‘8765%’ ，这种语句是可以使用索引的。