索引(三)

 

前言

在MySQL中，创建一张表时会默认为主键创建聚簇索引，B+树将表中所有的数据组织起来，即数据就是索引主键所以在InnoDB里，主键索引也被称为聚簇索引，索引的叶子节点存的是整行数据。而除了聚簇索引以外的所有索引都称为二级索引，二级索引的叶子节点内容是主键的值。

二级索引

创建二级索引

CREATE INDEX [index name] ON [table name]([column name]);

或者

ALTER TABLE [table name] ADD INDEX [index name]([column name]);

在MySQL中，CREATE INDEX 操作被映射为 ALTER TABLE ADD_INDEX。

二级索引格式

例如创建如下一张表:

CREATE TABLE users(
    id INT NOT NULL,
    name VARCHAR(20) NOT NULL,
    age INT NOT NULL,
    PRIMARY KEY(id)
);

新建一个以age字段的二级索引:

ALTER TABLE users ADD INDEX index_age(age);

MySQL会分别创建主键id的聚簇索引和age的二级索引:

在MySQL中主键索引的叶子节点存的是整行数据，而二级索引叶子节点内容是主键的值.

二级索引的创建流程

在MySQL8.0中，二级索引的创建具体流程如下图:

二级索引所属的Onine DDL可以分为三个阶段: DDL prepare 阶段, DDL执行阶段和 DDL commit 阶段.

DDL prepare 阶段

• 升级至X锁, 禁止读写.

• ha_prepare_inplace_alter_table()根据ALTER TABLE语句传入的参数进行检查，构建被创建的索引信息，创建索引的B+树.

DDL执行阶段

在MySQL8.0实现中，基本上所有的ALTER TABLE操作都实现在mysql_alter_table()函数，而Online DDL支持使用Inplace方式创建二级索引:

• row_merge_build_indexes()用来构建二级索引的索引内容，在MySQL中，二级索引的组织关系是<Key, Primay key>即指定的索引column与主键组成的映射关系. 所以需要读取聚簇索引来构建二级索引内容:

  • 申请内存用来排序，大小为3 * srv_sort_buf_size，申请临时文件merge_file_t用来合并排序.

  • 读取扫描表中的整个聚簇索引B+树构建二级索引，假如merge buffer的空间不满足Index的排序，则需要利用临时文件进行合并排序.

  • 根据prepare阶段构建的索引信息，遍历聚簇索引，构造对应的索引字段. 假如建表时没有指定主键，InnoDB会默认创建一个名为DB_ROW_ID的自增字段，所以二级索引的映射关系就是<Key, DB_ROW_ID>.

  • 将合并排序后的二级索引内容通过 Bulk Load 的方式写入Page，使用flush_observer落盘对应的数据脏页.

  • 关闭删除临时文件，释放排序内存merge_buf.

MySQL8.0要求DDL保持原子性，所以在上述的合并排序后插入 Page 的过程中，可以使用 flush_observer 直接落盘数据页或者记录Redo. 这样来保证整个DDL操作是原子的.

DDL commit 阶段

• 为Table加上X锁, 禁止读写.

• 更新InnoDB的数据字典DD.

• 提交 DDL 事务.

• 清理操作clean up.

在一些需要 rebuild table 的 Online DDL 操作中，例如Dropping a column, 为了不阻塞 DML 操作，需要引入row_log来暂存在 DDL 过程中用户的数据修改操作，而在二级索引的创建过程中并不需要 rebuild table, 所以不需要row_log, 用户对于数据的修改可以直接基于聚簇索引进行修改.

假如二级索引创建的过程中发生 crash, 重启后打开临时文件的 Tablespace 会清理上次意外 crash 遗留的临时文件.

索引定义

/** Definition of an index being created */
struct index_def_t {
  const char *name;          /*!< index name */
  bool rebuild;              /*!< whether the table is rebuilt */
  ulint ind_type;            /*!< 0, DICT_UNIQUE,
                             or DICT_CLUSTERED */
  ulint key_number;          /*!< MySQL key number,
                             or ULINT_UNDEFINED if none */
  ulint n_fields;            /*!< number of fields in index */
  index_field_t *fields;     /*!< field definitions */
  /* ... */
};

• name即索引名.
• rebuild表示是否需要重建表.
• ind_type表示索引类型.
• key_number表示表中索引数量.
• n_fields表示索引字段的数量.
• fields表示索引字段的定义.

二级索引的检索过程

在MySQL的查询过程中，SQL优化器会选择合适的索引进行检索，在使用二级索引的过程中，因为二级索引没有存储全部的数据，假如二级索引满足查询需求，则直接返回，即为覆盖索引，反之则需要回表去主键索引(聚簇索引)查询。

例如执行SELECT * FROM users WHERE age=35;则需要进行回表:

使用 EXPLAIN 查看执行计划可以看到使用的索引是我们之前创建的 index_age:

MySQL [sbtest]> EXPLAIN SELECT * FROM users WHERE age=35;
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | ref  | index_age     | index_age | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

总结

二级索引是指定字段与主键的映射，主键长度越小，普通索引的叶子节点就越小，二级索引占用的空间也就越小，所以要避免使用过长的字段作为主键。

 

       在MySQL中，创建一张表时会默认为主键创建聚簇索引，B+树将表中所有的数据组织起来，即数据就是索引主键所以在InnoDB里，主键索引也被称为聚簇索引，索引的叶子节点存的是整行数据。

       而除了聚簇索引以外的所有索引都称为二级索引，二级索引的叶子节点内容是主键的值。

 

 

 

 

 

 

参考资料：

1、https://blog.csdn.net/IceCaptain/article/details/80201738

2、

3、

4、

5、

6、

1、myisam索引。

 

   这里设表一共有三列，假设我们以Col1为主键，则图8是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

 

    同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

 

2、InnoDB索引实。

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，图11为定义在Col3上的一个辅助索引：

 

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

4、

5、

6、