mysql索引结构和优缺点——聚簇索引、二级索引、页分裂

innodb：

innodb表默认创建为聚簇索引，与oracle的索引组织表（iot）类似，页节点不存储rowid，而是存储数据本身，聚簇索引不是一种单独的索引类型，而是一种数据存储方式，也就是索引组织表

二级索引的叶节点存放了key键值（不一定是所有列），比如下面的b+树的键值没有存放gender列

oracle中b+树索引除了存放键值还有rowid，通过rowid去回表找到其他列的信息（rowid就是行的地址）

mysql中的b+树没有rowid，二级索引需要通过主键去回聚簇索引，通过聚簇索引找到叶节点数据页

 

B+树索引：

 

（注意索引列的不同和叶节点的存储信息不同）

聚簇索引：

 

 

 

非主键列创建二级索引虽然在创建后也节点还是会存储主键，但是与包含主键列的二级索引仍然有区别

比如

create table tab1 (

id int primary key auto_increment,

day date

);

create index idx1 on tab1(date)

create index idx2 on tab1(id,date)

虽然二级索引idx1和idx2的也节点都存储了id和date值，但是他们的结构是不同的，在分支节点上的key值分别是date1，和（id1，date1），索引扫描方式不一样

 

如果没有主键，InnoDB会选择一个唯一的非空索引替代

如果没有这样的索引，InnoDB会隐式创建主键

 

 

聚簇索引的优缺点：

优点：

1.访问数据较快，数据和索引存储在同一个b+树中，与oracle不同的是，在访问索引时不需要通过rowid去回表

2.使用覆盖索引扫描可以直接使用叶节点中的键值。这个同样适用于二级索引

缺点：

1.插入速度严重依赖于顺序插入。mysql页分裂与oracle索引分裂类似，在索引数左侧插入数据时，块（或者页）没有剩余空间插入该行，会导致分配新块（或者页）来存储改行，索引树重新rebalance将该块（或者页）存放到合适的位置。（索引分裂的触发条件更多，不仅是插入数据的情况）

2.更新索引列可能会导致将行移动到其他页中

3.dml操作或者页分裂后，索引的叶节点可能非常松散，叶节点可能只存放了少量数据，可能导致索引变慢。这个在b+树索引上同样适用

4.二级索引需要两次索引查找

5.二级索引可能会很大，二级索引的叶节点存储了主键列，如果表数据量较大，二级索引的效率会很低

 

 

myISAM

特点：

1.myISAM表顺手存储

2.myISAM索引跟oracle索引类似，需要返回行号

3.myISAM是定长的，直接跳过字节找到需要的行

4.二级索引的叶节点也就是主键索引的叶节点，他们的分支节点指针都指向表的数据页

 

参考文档：

《高性能MYSQL》