在MySQL中,最常用的两个存储引擎是MyISAM和InnoDB,它们对索引的实现方式是不同的。

MyISAM:data存的是数据地址。索引是索引,数据是数据。索引放在XX.MYI文件中,数据放在XX.MYD文件中,所以也叫非聚集索引。

InnoDB:data存的是数据本身。索引也是数据。数据和索引存在一个XX.IDB文件中,所以也叫聚集索引。

说下聚簇和非聚簇索引,聚簇和非聚簇索引都是B+树的数据结构。

聚簇索引:将数据存储和索引放在一起、并且是按照一定的顺序组织的,找到索引也就找到了数据,数据的物理存放顺序与索引顺序是一致的,即:只要索引是相邻的,那么对应的数据一定也是相邻的存放在磁盘上的。

非聚簇索引:叶子节点不存储数据,存储的是数据行地址,也就是说根据索引查找到数据行的位置再去磁盘查找数据,这就有点类似一本书的目录,比如要找到第三章第一节,那就现在目录里面查找,找到对应的页码后再去对应的页码看文章。

聚簇索引的优势:

1、查找通过聚簇索引可以直接获取到数据,相比非聚簇索引需要第二次查询(覆盖索引除外)效率要高

2、聚簇索引对范围查询的效率很高,因为其数据是按照大小排列的

3、聚簇索引适合用在排序场合,非聚簇索引不适合。

聚簇索引的劣势:

1、维护索引代价大,特别是插入新行或者主键被更新导致要分页的时候。建议在大量插入新行后,选择负载较低的时间段,通过OPTIMIZE TABLE优化表

2、表因为使用UUID作为主键,使数据存储稀疏,这就会出现聚簇索引有可能会比全表扫面更慢,所以建议使用int的auto_increment作为主键。

3、如果主键比较大的话,那辅助索引将会变得更大,因为辅助索引的叶子节点存储的是主键值,过长的主键值,会导致非叶子节点占用更多的物理空间

其他:

InnoDB中一定有主键,主键一定是聚簇索引,不手动设置,则会使用一个unique索引作为主键索引,没有unique索引,则会使用数据库内部的一个隐藏行id来当作主键索引。在聚簇索引之上创建的索引称为辅助索引,辅助索引访问数据总是需要二次查找,非聚簇索引都是辅助索引,像复合索引,前缀索引、唯一索引。辅助索引叶子节点存储的不再是行的物理位置,而是主键值。

MyISM使用的是非聚簇索引,没有聚簇索引。

Logo

为开发者提供学习成长、分享交流、生态实践、资源工具等服务,帮助开发者快速成长。

更多推荐