mysql调优--了解mysql索引结构

对于mysql调优来说，简历合适的索引是极其重要的一部，因此，有必要了解mysql中索引的相关知识。这里记录一下mysql 中关于索引的常见的基本知识。

innodb和myisam存储结构

我们工作中常见的mysql存储引擎包括innodb和myisam，两种存储引擎的数据存储结构不一样，最终两者的索引结构也是不一样的。

innodb	myisam
.frm 表定义文件 .ibd 数据文件	.frm 表定义文件 .myd 数据文件 .myi 索引文件

如上所示，对于不同的存储引擎，都有一个 以frm结尾的文件来存储表结构信息定义，但是myisam的话将索引文件与数据存储分离，从这一点也可以看出myisam的索引结构是有别于innodb的。

B+树

关于B+树的介绍不再多说，由于具有以下特点，B+树很适合做索引检索树。

• 结构绝对平衡，保证树的深度不会太深，提高检索速度

• 具有二叉查找树的特性，保证检索效率

• 叶子节点存储数据，并且叶子节点之间具有顺序性，排序能力更强。

  具体的演示效果可以进入以下网站动态演示演示网站

  在两种存储引擎中，索引存储都是使用B+树的结构。

  Myisam索引结构

  定义一个表结构如下所示。

  create table user
  (
  	id int null,
  	name varchar(30) default '' not null,
  	constraint user_pk
  		primary key (id)
  );
  
  

  id作为主键索引，肯定会建立一棵相关的主键索引树。结构如下图所示。其中主键索引树的数字为索引id。

  
  
  如上图所示，对于myisam储存引擎来说有以下特点

  • 索引树使用B+树结构，叶子节点存储数据为对应的myd文件中的存储地址。
  • 使用存储地址可以直接检索到数据文件中对应的数据记录。

  一般的使用主键id进行索引数据查找过程如下

  1. 根据id=？，在id索引树中检索到对应的记录存储地址。
  2. 根据存储地址，直接检索到数据文件中对应的记录内容。

  主键索引的创建如上所示，那么普通的索引呢》对于myisam来说，普通的索引创建也一样，创建一个B+索引树，然后叶子节点存记录地址。

  innodb索引结构

  innodb的索引结构跟myisam的实现有所区别，我们依然使用上面的表结构来作为示范例子。不过要为另一个字段加上索引。

  create index user_no_uindex
  	on user (no);
  

  数据跟原来一样，然后索引结构如下图所示。

  
  

  索引结构具有以下特点：

  • 必定创建一棵B+主键索引树，并且主键索引是聚簇索引，并且一定是覆盖索引，也就是说主键索引树叶子节点保存完整的记录数据
  • 辅助B+索引树，叶子节点存储的是主键数据，如果通过辅助索引检索数据，就需要先通过辅助索引树得到主键，然后再去主键索引树检索一次，得到完整的记录数据。

  了解到以上结构，可以更加清晰分析一些实际操作中行锁、gap 锁或者next key lock等的运行机制。