或者

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

例如创建如下一张表:

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

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

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

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

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中，二级索引的组织关系是即指定的索引column与主键组成的映射关系. 所以需要读取聚簇索引来构建二级索引内容:

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

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

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

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

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

DDL commit 阶段

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

• 更新InnoDB的数据字典DD.

• 提交 DDL 事务.

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

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

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

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

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

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  |

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