【Mysql】索引数据结构深入研究（二）

前言

在这里需要明确的一点是，数据库的引擎InnoDB或者是MyISAM引擎它们是形容数据表的，不是形容数据库的。
另外：文章中提到的索引的数据结构暂且都默认使用B+Tree

InnoDB引擎

InnoDB的索引数据文件有两个，tableName.frm和tableName.ibd文件。

• frm文件：表结构相关信息
• ibd文件：所有数据(按照索引数据结构构建的)
  

在InnoDB表中，索引存储的是叶子节点上存储的是具体的数据。

MyISAM引擎

MyISAM的索引数据文件有三个，tableName.frm、tableName.MYD和tableName.MYI文件

• frm文件：表结构的相关信息（字段啊什么信息）
• MYD文件：数据存储（具体的数据）
• MYI文件：索引存储（存放索引数据）
  

select * from table where table.Col1 = 30;

我们查询上述Col1为30的数据，会从MYI文件中索引数据结构中查找对于的数据，找到叶子节点之后MyISAM引擎的叶子节点不会存储具体的数据，而是存储对于数据所在的磁盘地址，然后在从MYD文件中找到具体的对应行数据。

在这里我们只了解两种引擎下的数据存储文件结构，了解了这两种索引文件存储结构之后我们继续往下

聚集索引（聚簇索引）

叶子节点包含了完整的数据记录，在InnoDB的主键索引就是一个聚集索引。

非聚集索引（非聚簇索引）

比如MyISAM引擎的主键索引就是一个非聚集索引，MYI索引文件中的叶子节点存储的数据对应着MYD文件中的具体数据。

---

这里有三个问题：
1.为什么建议InnoDB表建议建主键，并且推荐使用整型的自增主键?

• 表数据文件(ibd)文件本身就是按照B+Tree组织的一个索引结构文件，如果我们不建主键，它会默认选择一列(所有数据不相等的数据)，如果所有列都不符合的话它会帮我们建一个隐藏列，隐藏列会帮我们维护一个唯一ID，然后来组织整张表的索引数据，这样会造成不必要的开销。
• 使用整型的主键是因为在进行索引查找的时候是从根节点进行查询然后进行大小比较整型的效率一定会大于非整形的比对效率，并且整型的磁盘空间占用要小于非整型
• 使用自增主键会增加插入效率，如果使用非自增的主键，会导致索引的数据结构重新排列(B+Tree是排好序的数据结构)和树的平衡，自增主键只会往右开辟节点

2.聚集索引和非聚集索引查询效率如何？

从上面很容易看出来，聚集索引可以很直接的拿到数据，因此聚集索引效率要高

3.为什么非主键索引(二级索引，非聚集索引)结构叶子节点存储的是主键值（如图）

一致性和节省存储空间：节约空间应该比较好理解，一致性问题就是减少复杂增度，插入的时候只需要关注主键索引的数据就行。

联合索引

如上图，我们建立 name、age、position 的主键联合索引。

联合索引的数据结构是有顺序的，比如它先比较name的值，name的值相同在比较age，age相同在比较postion，可以仔细观察一下上述的图体会一下，然后在索引命中的时候是遵循最左前缀匹配的比如：

select * from table where name ='Bill';
select * from table where age = 30 and position='dev';
select * from table where position = 'dev';

比如上述Sql，只有第一个会命中索引，在或者如下sql

select * from table where age = 30 and position='dev' and name = 'Bill';

这个sql是可以进行索引查询的，因为这种条件的情况下Mysql内部会进行优化。