MySQL InnoDB创建索引

1.基本概念

1.1 聚簇索引

InnoDB的索引基于B+树实现，每张InnoDB的表都有一个特殊的索引，叫做聚簇索引(Clustered Index)，聚簇索引存储了表中的真实数据。索引项的顺序和真实的表数据顺序是一致的，B+树的叶子节点存储了真实的数据。这也就是所谓的“数据即索引”。聚簇索引的创建方式一般有三种：

1. 用户定义了主键，那么InnoDB依据主键创建聚簇索引
2. 用户没有定义主键，那么InnoDB根据表上的第一个唯一非空的列创建聚簇索引
3. 如果以上两条都不符合，那么InnoDB会自动指定一个系统列作为聚簇索引（后面提到）

1.2 二级索引

InnoDB中，所有的非聚簇索引都叫二级索引(Secondary Indexes)，与聚簇索引不同，二级索引的叶子节点不再是数据，而是存储类似<索引值，主键>的结构，通过主键查询聚簇索引。二级索引的创建可以由用户在 SQL 中自定义。

1.3 InnoDB系统列

InnoDB在创建表的时候，除了用户自定义的列之外，还会额外地增加几个隐藏的列，这些列在MySQL Server看来是不可见的，我们称之为系统列。比如，用户建表语句为 CREATE TABLE t (a int, b int) ENGINE=InnoDB; 在InnoDB中，实际创建的列为| DB_ROW_ID | DB_TRX_ID | DB_ROLL_PTR | a | b |，对这些字段的解释如下：

在聚簇索引的创建过程中，若用户既没有定义主键，也没有符合要求的唯一非空列，则InnoDB使用DB_ROW_ID作为主键创建聚簇索引。

1.4 索引相关基本数据结构

1. dict_index_t 这个是描述索引的数据结构，其大多成员会在dict_mem_index_create()被初始化成0, NULL, false. dict_mem_index_create()用于创建一个索引的内部对象。
2. dict_table_t 这个是描述表结构的数据结构，其大多成员会在dict_mem_table_create()被初始化成0, NULL, false. dict_mem_table_create()用于创建一个表的内部对象。其中一个和索引直接相关的成员是indexes，这是一个链表，代表了该表上的所有索引。

2.代码分析

2.1 建表时创建索引

假设我们在建表时不创建主键，但是创建一个二级索引，SQL语句形如： CREATE TABLE t (a int, b int, index idx(b)) ENGINE=InnoDB; step1: 无论如何，聚簇索引都是会在第一步产生的，这里我们没有定义主键，也，没有定义唯一非空列，所以只能让InnoDB自动创建聚簇索引。其大致的函数调用关系如下：

|-ha_create_table ()
  |-handler::ha_create ()
	  |-ha_innobase::create ()
	    |-innobase_basic_ddl::create_impl<dd::Table> ()
	      |-create_table_info_t::create_table ()
	        |-create_clustered_index_when_no_primary ()
	          |-...
	            |-dict_index_add_to_cache ()
				        |-dict_index_add_to_cache_w_vcol () //把新建的索引加入索引链表
				          |-dict_index_build_internal_clust () //创建聚簇索引缓存
					          |-dict_index_add_col ()  //将需要索引的列添加到索引中

整个创建默认聚簇索引的过程在InnoDB层完成，主要涉及的开始创建的函数create_table在文件ha_innodb.cc,判断表定义是否有主键的信息来自于结构体TABLE_SHARE->primary_key以及TABLE_SHARE->keys，如果用户没有定义主键，则调用函数create_clustered_index_when_no_primary()进行默认主键的创建任务，期间的调用关系如上文所示，还需要注意的就是这个dict_index_build_internal_clust(),该函数为聚簇索引创建了一个内部数据字典缓存。

step2: 还是基于上述的建表语句，当聚簇索引创建成功后，紧接着需要创建二级索引，也即上表中'idx'. 创建二级索引的函数调用和创建聚簇索引基本一致，大致关系如下：

|-ha_create_table ()
  |-handler::ha_create ()
	  |-ha_innobase::create ()
	    |-innobase_basic_ddl::create_impl<dd::Table> ()
	      |-create_table_info_t::create_table ()
	        |-create_index ()
	          |-...
	            |-dict_index_add_to_cache ()
				        |-dict_index_add_to_cache_w_vcol () //把新建的索引加入索引链表
				          |-dict_index_build_internal_non_clust () //创建二级索引缓存
				    	      |-dict_index_add_col ()  //将需要索引的列添加到索引中

二级索引的判断依据是TABLE_SHARE->keys,keys代表了表中定义的索引键值的数量，在创建二级索引的过程中，会通过一个for循环扫描所有键，并为之创建二级索引，当然，主键已经创建了聚簇索引，所以会被排除在外。create_index()函数是创建二级索引的入口，最后通过dict_index_build_internal_non_clust()创建二级索引的内部缓存，从整个流程上来看，两者的区别不大。

2.2 重启后创建索引

MySQL重启后，内部索引对象丢失，需要在启动后重新创建相关的索引。MySQL重启后首先会将数据字典内的信息进行读取和初始化，然后根据数据字典的信息进行索引的创建。还是以上文的表t为例，假设现在 MySQL 重启，如何在t上构建索引？ step1: 创建聚簇索引 无论如何，聚簇索引都会第一个创建。当我们首次对t进行查询或者更新时，由于默认聚簇索引不会被持久化到数据字典，所以对于表t来说，重启之后系统需要重新为之创建一个默认的聚簇索引。其大致的函数调用关系如下：

|-open_tables_for_query ()
  |-open_tables ()
    |-open_and_process_table ()
	    |-open_table ()
	      |-open_table_from_share ()
		      |-handler::ha_open ()
		        |-ha_innobase::open ()
			        |-dd_open_table<dd::Table> ()
			          |-dd_open_table_one<dd::Table> ()
				          |-dd_fill_dict_index ()
				            |-dict_index_add_to_cache ()
                      |-...	// 和建表时创建索引的流程一致

可以看出，首先我们会打开一个表对象，并获取其中的信息。当然，首先会打开系统表以查看持久化的信息。关键的函数是dd_fill_dict_index()，该函数用于实例化索引相关的元数据。对于没有定义聚簇索引的情况，在该函数中作出判断，并创建新的聚簇索引，加入缓存。 step2: 创建二级索引 创建二级索引的过程和创建聚簇索引的过程稍有不同，原因在于用户自定义的二级索引是需要持久化的，所以需要先读数据字典，然后建立索引。在dd_fill_dict_index()中一个一个建立二级索引，基于函数dd_fill_one_dict_index(),将索引添加到表中。其大致的函数调用关系如下：

|-open_tables_for_query ()
  |-open_tables ()
    |-open_and_process_table ()
	    |-open_table ()
	      |-open_table_from_share ()
		      |-handler::ha_open ()
		        |-ha_innobase::open ()
			        |-dd_open_table<dd::Table> ()
			          |-dd_open_table_one<dd::Table> ()
				          |-dd_fill_dict_index ()
				            |-dd_fill_one_dict_index () //创建二级索引
				              |-dict_index_add_to_cache ()
                        |-...	// 和建表时创建索引的流程一致

综上所述，其实建表时创建索引和重启后创建索引的本质过程是一样的，只是在前期步骤有所区别，到了真正为某张表添加索引的时候，所走的路径可以说是完全一样的，不同情况下创建索引的步骤是殊途同归。

How to create index Clustered and Secondary Indexes

封面图片： Marcin Wichary , used under a Creative Commons license