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 |,对这些字段的解释如下:

字段名

长度

描述

DB_ROW_ID

6B

一个单调递增的行ID

DB_TRX_ID

6B

表示记录最后被插入或更新时对应的事务ID

DB_ROLL_PTR

7B

指向回滚段中的UNDO log

在聚簇索引的创建过程中,若用户既没有定义主键,也没有符合要求的唯一非空列,则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

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏java一日一条

理解MySQL——架构与概念

写在前面:最早接触的MySQL是在三年前,那时候MySQL还是4.x版本,很多功能都不支持,比如,存储过程,视图,触发器,更别说分布式事务等复杂特性了。但从5....

7720
来自专栏北京马哥教育

给linux用户的11个高级MySQL数据库面试问题和答案

1. 如何使用SELECT语句找到你正在运行的服务器的版本并打印出当前数据库的名称? 答:下面的语句的结果会显示服务器的版本和当前的数据库名称 mysql>...

28840
来自专栏Hongten

python开发_python操作mysql数据库

如果你还没有准备好开发环境,你不妨花上一小点时间去看看:python开发_mysqldb安装

11530
来自专栏后端技术探索

MySQL中的两种临时表 外部临时表

通过CREATE TEMPORARY TABLE 创建的临时表,这种临时表称为外部临时表。这种临时表只对当前用户可见,当前会话结束的时候,该临时表会自动关闭。这...

9900
来自专栏程序猿

SQL 注入语句特征

语句特征 1.判断有无注入点 ; and 1=1 and 1=2 2.猜表一般的表的名称无非是admin adminuser user pass passwor...

534110
来自专栏资深Tester

SQL系列之DDL/DCL语言

26060
来自专栏Jackson0714

聚集索引VS非聚集索引

13430
来自专栏北京马哥教育

MySQL/MariaDB基础性知识及DDL操作详解

前言 MySQL/MariaDB是一个开放源码的小型关联式数据库管理系统,由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多中小型网站为了降...

52760
来自专栏乐沙弥的世界

MySQL数据类型选择性能比对

版权声明:本文为博主原创文章,欢迎扩散,扩散请务必注明出处。 https://blog.csdn.net/robinson_0612/art...

14520
来自专栏Linyb极客之路

SQL优化指南

slow_launch_time:表示如果建立线程花费了比这个值更长的时间,slow_launch_threads 计数器将增加

13020

扫码关注云+社区

领取腾讯云代金券