Mysql-索引数据结构&引擎篇

Mysql可以说是最广泛使用的数据库之一了,体积小,成本低,开源(免费才是王道呀-。-),本文旨在和大家一起探索Mysql的一些相关知识,不仅要会用它来写sql,更要学习它的底层设计和技术延伸。

索引数据结构

Mysql索引是基于B+tree的数据结构来设计的,那么为什么不使用二叉树,Hash(其实是支持的),B-tree等结构来设计索引呢?

二叉树:

树的层数过高,容易退化成链表

平衡二叉树,红黑树:

层数仍然过高,会大大增加系统的IO频率

Hash

Mysql是支持Hash索引的,只不过Hash索引不支持范围查找,而我们在日常工作中需要广泛的运用到范围查询

B-tree:

1.在B-tree和B+tree中,每一个节点叫做一个磁盘页,每一个磁盘页的大小是16K,那么相比较于B-tree是在每个节点上都存储数据,B+tree是只在叶子节点上存储数据,相同层数下,B+tree能存储的数据量要大于B-tree
2.B+tree的叶子节点有双向指针,对于范围查找的效率能大大提升

存储引擎

Mysql中有很多种存储引擎,我们这里主要介绍的是MyISAMInnoDB

打开不同引擎的表的存储文件夹,会发现这两种引擎用来保存相关数据的文件不同:
MyISAM

.frm文件:存储表结构
.MYD文件:存储数据
.MYI文件:存储索引
查询时,如果有索引,在MYI文件中根据索引获取数据地址,再去MYD文件中查找到数据

InnoDB

.frm文件:存储表结构
.ibd文件:存储索引和数据
索引和数据都存储在ibd文件

除了存储文件上的区别,MyISAM和InnoDB还有以下的区别:

  • 前者是非聚集索引,后者是聚集索引
  • 前者不支持事务,后者支持
  • 前者不支持外键,后者支持
  • 前者只支持表锁,后者支持表锁和行锁
  • 前者保存表的行数,后者每次仅限count(*)操作需要去扫描全表
  • delete表的时候,前者是重新建表,后者是一行行的删
    ···

那么,什么时候使用哪个最好呢?一般来说,系统业务涉及到查询占大部分,对事务需求度低,容忍度高的,可以使用MyISAM引擎,MyISAM查询效率要高于InnoDB。反之,系统涉及并发量大,需要大量的增删改操作,建议使用InnoDB引擎。

tips:MyISAM查询效率更高,是因为:InnoDB要缓存数据块,而MyISAM只要缓存索引块;在select的时候InnoDB需要去维护MVCC(多版本并发控制);InnoDB查询需要映射到块再到行,而MyISAM直接记录文件的offset,定位更快

聚集索引和非聚集索引

  • 对于主键索引和非主键索引来说,MyISAM主键索引的叶子节点和非主键索引的叶子节点都存放的是行数据的磁盘地址InnoDB非主键索引的叶子节点存储的是主键值,而主键索引的叶子节点里存储的是行数据,当进行非主键索引查询时,先在非主键索引中查找到对应的主键值,然后根据主键值再去主键索引里进行一次树查询,获取主键索引中存储的行数据。(这种第一次树查询定位主键,第二次再进行一次树查询的操作叫做回表
  • 根据索引存储方式的不同,我们把MyISAM的主键索引和非主键索引类型叫做非聚集索引,把InnoDB的主键索引类型叫做聚集索引,非主键索引类型叫做辅助索引(普通索引)。聚集的含义可以理解为索引和数据聚合在一起。
使用InnoDB时的tips:
1.基于上面的设计,InnoDB必须设置主键索引,所以一般建议我们在进行表的设计的时候都要添加主键列,如果不设置主键,mysql会在表中寻找一个唯一列来当做主键索引,如果没有这样的列,它会去维护一个虚拟列,用以建立主键索引
2.主键尽可能的要设置成自增整型类型,因为最终在B+tree中是需要去比较索引大小的,如果是非整型的,或者是无序的主键,还需要先去进行值转换,无疑增加了额外时间开支

But every once in a while you find someone who's iridescent, and when you do, nothing will ever c...

55 声望
0 粉丝
0 条评论
推荐阅读
集合篇-ArrayList源码解析
和 LinkedList的区别:ArrayList:底层是动态数组,支持扩容,线程不安全。对于添加和删除方法,如果是添加到列表尾部,时间复杂度是O(1);如果是添加到指定位置i,时间复杂度就是O(n-i),因为需要将后续数组的元...

发条橙阅读 710

万字长文~vue+express+mysql带你彻底搞懂项目中的权限控制(附所有源码)
所谓的权限,其实指的就是:用户是否能看到,以及是否允许其对数据进行增删改查的操作,因为现在开发项目的主流方式是前后端分离,所以整个项目的权限是后端权限控制搭配前端权限控制共同实现的

水冗水孚11阅读 1.5k

花了几个月时间把 MySQL 重新巩固了一遍,梳理了一篇几万字 “超硬核” 的保姆式学习教程!(持续更新中~)
MySQL 是最流行的关系型数据库管理系统,在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System:关系数据库管理系统)应用软件之一。

民工哥11阅读 1.1k

封面图
一次偶然机会发现的MySQL“负优化”
今天要讲的这件事和上述的两个sql有关,是数年前遇到的一个关于MySQL查询性能的问题。主要是最近刷到了一些关于MySQL查询性能的文章,大部分文章中讲到的都只是一些常见的索引失效场合,于是我回想起了当初被那个...

骑牛上青山8阅读 2.3k评论 2

初学后端,如何做好表结构设计?
这篇文章介绍了设计数据库表结构应该考虑的4个方面,还有优雅设计的6个原则,举了一个例子分享了我的设计思路,为了提高性能我们也要从多方面考虑缓存问题。

王中阳Go3阅读 707评论 1

封面图
2023最新MySQL高频面试题汇总
本文已经收录到Github仓库,该仓库包含计算机基础、Java基础、多线程、JVM、数据库、Redis、Spring、Mybatis、SpringMVC、SpringBoot、分布式、微服务、设计模式、架构、校招社招分享等核心知识点,欢迎star~

程序员大彬3阅读 1k

Mysql索引覆盖
通常情况下,我们创建索引的时候只关注where条件,不过这只是索引优化的一个方向。优秀的索引设计应该纵观整个查询,而不仅仅是where条件部分,还应该关注查询所包含的列。索引确实是一种高效的查找数据方式,但...

京东云开发者2阅读 955

封面图

But every once in a while you find someone who's iridescent, and when you do, nothing will ever c...

55 声望
0 粉丝
宣传栏