最新公告
  • 欢迎您光临欧资源网,本站秉承服务宗旨 履行“站长”责任,销售只是起点 服务永无止境!立即加入我们
  • InnoDB是如何存储数据的?把数据存储和查询操作抽象成了存储引擎

    几乎所有的商业项目都涉及数据存储。虽然目前各种 NoSQL 和文件系统很流行,但 MySQL 等关系型数据库仍然是最常用于存储重要数据的数据库,因为它们具有 ACID、高可靠性和易于开发的特性。在关系数据库中,索引是优化查询性能的重要手段。

    为此,我经常看到一些同学在遇到查询性能问题时,盲目的要求运维或者DBA为数据表的相关字段创建大量索引。显然,这个想法是错误的。今天,我们以 MySQL 为例,深入了解索引的原理以及相关的误区。

    InnoDB 如何存储数据?

    MySQL 将数据存储和查询操作抽象为存储引擎。不同的存储引擎以不同的方式存储和读取数据。MySQL 支持多种存储引擎,存储引擎可以设置为表粒度。我们最常使用 InnoDB内存卡无法访问函数不正确,因为它支持事务。为了便于理解后面的内容,我先给大家简单介绍一下InnoDB是如何存储数据的。

    虽然数据保存在磁盘上,但它的处理是在内存中完成的。为了减少从磁盘随机读取的次数,InnoDB 使用页粒度而不是行来存储数据,即将数据分成若干页,以页为单位存储在磁盘中。InnoDB 的页面大小一般为 16KB。

    每个数据页构成一个双向链表,每个数据页中的记录按照主键顺序构成一个单链表;每个数据页都有一个页目录,方便根据主键查询记录。数据页的结构如下:

    页面目录将记录按槽分为不同的组,每组有若干条记录。如图,记录前面小方块内的数字代表当前组的记录数,最小和最大的slot分别指向2条特殊伪记录。有了slot,当我们根据主键在页面中搜索记录时,可以使用二分法进行快速搜索,而不用从最小的记录开始遍历整个页面中的记录列表。

    例如,如果要搜索主键 (PK) = 15 的记录:

    了解了InnoDB存储数据的原理之后,我们可以继续学习MySQL索引相关的原理和坑。

    聚集索引和二级索引

    在索引方面,页目录是最简单的索引,通过将记录分组到一个级别来降低搜索的时间复杂度。但是,通过这种方式可以降低的时间复杂度的数量级是非常有限的。当存储表数据的数据页数不胜数时,我们需要考虑如何建立合适的索引,以便轻松定位记录所在的页。

    为了解决这个问题,InnoDB 引入了 B+ 树。如下图所示,B+树是倒挂树:

    B+树的特点包括:

    因此,InnoDB 使用了 B+ 树,既可以保存实际数据,又可以加快数据查找速度,这就是聚集索引。如果将上图中叶子节点下方方框中的省略号视为实际数据,则为聚簇索引示意图。由于物理上只存储了一份数据副本,因此只能有一个包含实际数据的聚集索引。

    InnoDB 会自动使用主键(唯一定义一条记录的单个或多个字段)作为聚集索引的索引键(如果没有主键,则选择第一个不包含 NULL 值的唯一列)。上框中的数字代表索引键的值,一般是聚集索引的主键。

    下面我们来看看B+树是如何快速找到主键的。比如我们要查找PK=4的数据,通过根节点中的索引可以知道数据在第一条记录所指向的第2页,可以知道数据在第5页和第5页通过第2页的索引。它是实际的数据页,然后通过二分法搜索页目录可以立即找到指向该记录的指针。

    为了实现对非主键字段的快速搜索,引入了二级索引,也称为非聚集索引和辅助索引。二级索引也是使用的B+树的数据结构,如下图所示:

    这一次,二级索引的叶子节点存储的不是实际数据,而是主键。获取主键值后,去聚集索引获取数据行。这个过程称为后备列表。

    例如,为用户名字段创建索引。索引记录上方方框中的字母为用户名,按顺序形成一个链表。如果我们要搜索用户名b的数据,经过两次定位,可以发现#5数据页中所有的主键都是7和6,然后继续使用聚簇索引进行两次检索这两个主键。完整数据表。

    考虑创建额外二级索引的成本

    创建二级索引的成本主要体现在维护成本、空间成本和回表成本三个方面。接下来就和大家详细分析一下。

    首先是维护成本。创建N个二级索引,需要创建N个B+树。添加数据时,不仅需要修改聚集索引,还需要修改N个二级索引。

    让我们通过实验测试创建索引的成本。假设有一张person表,主键ID,name、score、create_time三个字段:

    CREATE TABLE `person` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) NOT NULL,
      `score` int(11) NOT NULL,
      `create_time` timestamp NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    

    通过下面的存储过程创建10万条测试数据的周期在我的机器上需要140秒(本文中的例子都是在MySQL5.7.26中执行的):

    CREATE DEFINER=`root`@`%` PROCEDURE `insert_person`()
    begin
        declare c_id integer default 1;
        while c_id<=100000 do
        insert into person values(c_id, concat('name',c_id), c_id+100, date_sub(NOW(), interval c_id second));
        set c_id=c_id+1;
        end while;
    end
    

    如果再创建两个索引,一个是name和score组成的联合索引,另一个是单列create_time的索引,那么创建10万条记录所需时间增加到154秒:

    KEY `name_score` (`name`,`score`) USING BTREE,
    KEY `create_time` (`create_time`) USING BTREE
    

    这里要提一下,页面中的记录是按照索引值从小到大的顺序存储的。要添加新记录,您需要在页面中插入数据。如果现有页面已满,则需要创建一个新页面。将已有页面的部分数据移过来,即分页;如果删除的数据很多,页面比较空闲,也需要进行页面合并。页面拆分和合并有 IO 成本,在操作过程中可能会导致死锁。

    您可以查看此文档以了解有关如何设置合理的合并阈值以平衡页面空闲与另一个页面拆分成本的更多信息。

    二是空间成本。二级索引虽然不保存原始数据,但需要保存索引列的数据,所以会占用较多空间。例如在person表上创建两个索引后,使用如下SQL查看数据和索引占用的磁盘:

    SELECT DATA_LENGTH, INDEX_LENGTH FROM information_schema.TABLES WHERE TABLE_NAME='person'
    

    结果显示,数据本身只占用4.7M,而索引占用8.4M。

    最后,还有重回餐桌的成本。二级索引不保存原始数据。通过索引找到主键后,我们需要查询聚集索引来获取我们想要的数据。例如,使用 SELECT * 按名称字段查询用户,使用 EXPLAIN 查看执行计划:

    EXPLAIN SELECT * FROM person WHERE NAME='name1'
    

    执行计划如下,可以查到:

    将SQL中的*改为NAME和SCORE,即SELECT name_score联合索引中包含的两列:

    EXPLAIN SELECT NAME,SCORE FROM person WHERE NAME='name1'
    

    我们来看看执行计划:

    可以看到Extra列多了一行Using index提示,证明本次查询直接搜索二级索引,省去了回表。

    原因很简单。联合索引实际上保存了多个索引列的值。页面中的记录先按字段1排序,如果相同则按字段2排序,如图:

    图中,叶子节点每条记录的第一块和第二块是索引列的数据,第三块是记录的主键。如果我们需要查询索引列索引或者联合索引可以覆盖的数据,那么查询索引本身已经“覆盖”了需要的数据,不需要再查询回表。因此,这种情况也称为索引覆盖。上一节我会介绍如何查看不同查询的成本,和大家一起看看索引覆盖率和索引查询返回表的成本差异。

    最后,让我与您一起总结索引开销的最佳实践。

    首先,不需要从一开始就建立索引。可以等到业务场景清晰,或者数据量超过10000,查询变慢的时候,再为需要查询、排序、分组的字段创建索引。创建索引后,可以使用 EXPLAIN 命令检查查询是否可以使用索引。我将在下一小节中对此进行扩展。

    其次,尝试索引轻量级字段。例如,如果您可以索引 int 字段,则不要索引 varchar 字段。索引字段也可以是部分前缀,字段索引长度在创建时指定。对于长文本搜索,请考虑使用专门用于文本搜索的索引数据库,例如 Elasticsearch。

    第三,尽量不要在SQL语句中选择*,而是选择必要的字段。甚至可以考虑使用联合索引来包含我们要搜索的字段,这样不仅可以加快索引速度,还可以避免回表的开销。

    并非所有针对索引列的查询都可以使用索引

    前面的例子,我创建了一个name+score的联合索引,可以在只搜索name的时候使用。这导致两个问题:

    首先,我们通过几个案例来分析一下索引失效的情况。

    首先,索引只能匹配列前缀。比如下面的LIKE语句中,名字后缀为name123的用户是无法通过索引的,执行计划的type=ALL代表全表扫描:

    EXPLAIN SELECT * FROM person WHERE NAME LIKE '%name123' LIMIT 100
    

    把百分号放在后面进行前缀匹配,type=range表示进行索引扫描,key=name_score看到的是实际的name_score索引:

    EXPLAIN SELECT * FROM person WHERE NAME LIKE 'name123%' LIMIT 100
    

    原因很简单,索引B+树中的行数据是按照索引值排序的,只能按照前缀进行比较。如果想按后缀查找,又想用索引,一直只按后缀查找,可以把数据倒过来存,用的时候再倒过来。

    二是条件涉及函数操作不能通过索引。例如,如果在搜索条件中使用了 LENGTH 函数,则不能使用索引:

    EXPLAIN SELECT * FROM person WHERE LENGTH(NAME)=7
    

    出于同样的原因,索引保存索引列的原始值,而不是函数计算的值。如果函数调用需要使用数据库索引,只能保存一份函数转换后的值,然后重新索引计算的列。

    第三,联合索引只能匹配左边的列。也就是说,虽然为name和score建立了联合索引,但是仅仅通过score列搜索是无法访问索引的:

    EXPLAIN SELECT * FROM person WHERE SCORE>45678
    

    原因也很简单。在联合索引的情况下,按照索引的第一列对数据进行排序,只有当第一列的数据相同时才对第二列进行排序。也就是说,如果我们想在联合索引中使用尽可能多的列内存卡无法访问函数不正确,则查询条件中的每一列必须是联合索引中从最左边开始连续的列。如果只按第二列搜索,肯定走不了索引。尝试将搜索条件添加到 name 列,可以看到 name_score 索引不见了:

    EXPLAIN SELECT * FROM person WHERE SCORE>45678 AND NAME LIKE 'NAME45%'
    

    需要注意的是,由于查询优化器的原因,名称是 WHERE 子句的第一个条件并不是很重要。

    现在回到前两个问题。

    数据库根据成本决定是否去索引

    从前面的案例可以看出,查询数据可以直接在聚集索引上扫描,也可以二级索引扫描到聚集索引返回表。看到这里,你不禁要问,MySQL是如何决定走哪个计划的?

    事实上,在查询数据之前,MySQL 会先针对可能的解决方案制定一个执行计划,然后根据成本决定走哪个执行计划。

    这里的成本,包括IO成本和CPU成本:

    基于此,我们分析全表扫描的成本。

    全表扫描是将聚集索引中的记录依次与给定的搜索条件进行比较,并将满足搜索条件的记录添加到结果集中的过程。然后,需要两条信息来计算全表扫描的成本:

    那么,MySQL 会实时统计这些信息吗?事实上,事实并非如此。MySQL维护表的统计信息,可以通过以下命令查看:

    SHOW TABLE STATUS LIKE 'person'
    

    输出如下:

    看得见:

    因此,全表扫描的总成本约为 20306。

    站内大部分资源收集于网络,若侵犯了您的合法权益,请联系我们删除!
    欧资源网 » InnoDB是如何存储数据的?把数据存储和查询操作抽象成了存储引擎

    常见问题FAQ

    免费下载或者VIP会员专享资源能否直接商用?
    本站所有资源版权均属于原作者所有,这里所提供资源均只能用于参考学习用,请勿直接商用。若由于商用引起版权纠纷,一切责任均由使用者承担。更多说明请参考 VIP介绍。
    提示下载完但解压或打开不了?
    最常见的情况是下载不完整: 可对比下载完压缩包的与网盘上的容量,若小于网盘提示的容量则是这个原因。这是浏览器下载的bug,建议用百度网盘软件或迅雷下载。若排除这种情况,可在对应资源底部留言,或 联络我们.。
    找不到素材资源介绍文章里的示例图片?
    对于PPT,KEY,Mockups,APP,网页模版等类型的素材,文章内用于介绍的图片通常并不包含在对应可供下载素材包内。这些相关商业图片需另外购买,且本站不负责(也没有办法)找到出处。 同样地一些字体文件也是这种情况,但部分素材会在素材包内有一份字体下载链接清单。
    欧资源网
    一个高级程序员模板开发平台

    发表评论