勇闯BAT 聊聊Mysql的存储引擎

2023-11-21 07:10

本文主要是介绍勇闯BAT 聊聊Mysql的存储引擎,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

文章目录

    • 存储引擎是什么
    • 查看支持的引擎
    • 常用存储引擎介绍
      • Archive
      • Memory
      • MyISAM
      • InnoDB
      • 概要
    • 常见问题
      • MyISAM 和 InnoDB的区别
      • 存储引擎是针对实例的还是表
      • 自增主键最大值保存问题
      • InnoDB引擎是否支持hash索引

存储引擎是什么

MySQL架构可以分为4层,先看下,其所在的位置。

  • 连接层:包括了数据库连接、授权认证、安全管理等
  • Server层:包括 SQL 解析、优化、执行以及缓存等
  • 存储引擎层:包括了各种存储引擎,主要负责数据的存取等
  • 数据存储层:主要负责将数据存储在文件系统中,并完成与存储引擎的交互。

img

  • 存储引擎层负责数据的存储和提取。其架构模式是插件式的,支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB。

  • 存储引擎其实就是对于数据库文件的一种存取机制,如何实现存储数据,如何为存储的数据建立索引以及如何更新,查询数据等技术实现的方法。

  • MySQL中的数据用各种不同的技术存储在文件(或内存)中,这些技术中的每一种技术都使用不同的存储机制,索引技巧,锁定水平并且最终提供广泛的不同功能和能力。在MySQL中将这些不同的技术及配套的相关功能称为存储引擎。

查看支持的引擎

SHOW ENGINES;

常用存储引擎介绍

Archive

压缩协议进行数据的存储,数据存储为ARZ文件的格式。在归档之后很多的高级功能就不再支持了,仅仅支持最基本的插入和查询两种功能,拥有高效的插入速度,但是这种引擎不支持索引,所以查询性能较差一些。

特点:

  • 只支持insert和select操作
  • 只允许自增ID列建索引
  • 行级锁
  • 不支持事务
  • 数据占用磁盘少

应用场景:

  • 日志系统
  • 大量的设备采集

Memory

数据都是存储在内存中,IO效率高,服务重启数据会丢失,内存表默认只有16M。

特点:

  • 支持Hash、Btree索引
  • 不支持大数据存储类型,例如:blob、text
  • 表级锁
  • 只能使用比较运算符=,<,>,=>,= <
  • HEAP表不支持AUTO_INCREMENT

应用场景:

  • 缓存表

MyISAM

在 MySQL 5.5 版本之前是默认的存储引擎,不支持事务,也不支持外键,最大的特点是速度快,占用资源少。

特点:

  • select count(*)无需扫描所有行
  • 数据(MYD)和索引(MYI)分开存储
  • 表级锁,限制了读/写工作负载中的性能
  • 不支持事务

应用场景:

  • 通常用于Web和数据仓库配置中的只读场景。
  • 以读为主的应用程序,比如博客系统、新闻门户网站

InnoDB

它是 MySQL 5.5 版本之后默认的存储引擎,最大的特点是支持事务、行级锁定、外键约束等。

特点:

  • 数据和索引在一个文件(.ibd)
  • 行级锁
  • 支持事务

应用场景

  • 更新(删除)操作频率高,或者要保证数据的完整性。
  • 并发量高,支持事务和外键保证数据完整性。比如OA自动化办公系统。

概要

功能MyISAMInnoDBArchiveMemory
B-tree 索引YesYesNoYes
安全点恢复YesYesYesYes
集群数据库支持NoNoNoNo
聚集索引NoYesNoNo
压缩数据YesYesYesNo
数据缓存NoYesNoN/A
加密数据YesYesYesYes
外键NoYesNoNo
全文搜索索引YesYesNoNo
地理空间数据类型支持YesYesYesNo
地理空间索引支持YesYesNoNo
哈希索引NoNoNoYes
索引缓存YesYesNoN/A
锁定粒度TableRowRowTable
MVCCNoYesNoNo
复制支持YesYesYesLimited
储存限制256TB64TBNoneRAM
T树索引NoNoNoNo
事务NoYesNoNo
更新数据字典的统计信息YesYesYesYes

特别注意InnoDB的更新情况

  • MySQL 5.6和更高版本提供了对FULLTEXT索引的支持。
  • MySQL 5.7和更高版本提供了对地理空间索引的支持。

https://dev.mysql.com/doc/refman/5.7/en/innodb-storage-engine.html

常见问题

MyISAM 和 InnoDB的区别

存储结构MyISAMInnoDB
存储格式在磁盘上存储成三个文件。文件的名字以表的名字开始,扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。在磁盘上存储成二个文件。文件的名字以表的名字开始,扩展名指出文件类型。.frm文件存储表定义。数据和索引集中存储.ibd。
存储空间可被压缩,存储空间较小。支持三种不同的存储格式:静态表(默认,但是注意数据末尾不能有空格,会被去掉)、动态表、压缩表需要更多的内存和存储,它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
可移植性、备份及恢复数据是以文件的形式存储,所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。可以是拷贝数据文件、备份 binlog,或者用 mysqldump,在数据量达到几十G的时候就相对痛苦了。
事务支持强调的是性能,每次查询具有原子性,其执行数度比InnoDB类型更快,但是不提供事务支持。提供事务支持事务,外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表
表锁差异只支持表级锁,用户在操作myisam表时,select,update,delete,insert语句都会给表自动加锁,如果加锁以后的表满足insert并发的情况下,可以在表的尾部插入新的数据。支持事务和行级锁,是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁,只是在WHERE的主键是有效的,非主键的WHERE都会锁全表的
表的具体行数保存有表的总行数,如果select count(*) from table;会直接取出出该值没有保存表的总行数,如果使用select count(*) from table;就会遍历整个表,消耗相当大,但是在加了wehre条件后,myisam和innodb处理的方式都一样

来源:https://www.jianshu.com/p/a957b18ba40d

存储引擎是针对实例的还是表

存储引擎是指定在表之上的,即一个库中的每一个表都可 以指定专用的存储引擎。

自增主键最大值保存问题

一张表,里面有ID自增主键,当insert了17条记录之后,删除了第15,16,17条记录,再把Mysql重启,再insert一条记录,这条记录的ID是18还是15 ?

1.Mysql8.0以下版本
表类型为InnoDB引擎,这条记录的ID是15。因为InnoDB表只把自增主键的最大ID记录到内存中,所以重启MYSQL或者对表OPTIMIZE操作,都会使最大ID丢失。
表类型为MylSAM引擎,这条记录的ID是18。因为MylSAM表会把自增主键的最大ID记录到数据文件里面,重启MYSQL后,自增主键的最大ID也不会丢失。

2.Mysql8.0及以上版本
这条记录的ID是18,因为这个版本保存ID的值是在redo日志中的,重启之后是可以恢复的。

InnoDB引擎是否支持hash索引

上面其实已经介绍过了,是不支持Hash索引的,但是其内部有个自适应Hash索引

自适应Hash索引特征能使InnoDB在具有适当的工作负载和足够缓冲池内存的系统上执行的更像内存中的数据库的操作,且不会牺牲事务特性或可靠性,MySQL能基于监视到的搜索规则,使用索引键的前缀构建Hash索引,前缀可以是任意长度,并且可能只有b+树中的某些值出现在Hash索引中,Hash索引其实就是对经常访问的索引页进行构建的。

这又说明其实InnoDB是支持Hash索引的,但并不是真正意义上的Hash,而是通过自己的监视情况自动对某些热点索引值构建的内存Hash。可以理解为其是索引的索引

看下具体的图就很容易明白,这里借用沈剑老师的图:

但是我自己在InnoDB下怎么可以设置Hash索引呢?

这里对name做了hash索引,并没有报错,能正常执行。

CREATE TABLE `test` (`id` int(11) NOT NULL,`name` varchar(255) DEFAULT NULL,PRIMARY KEY (`id`),KEY `name` (`name`) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

这也太奇怪了,使用 show index from test查看索引信息

实际上其还是BTREE索引,应该是内部自动转换为 BTREE索引了,明显的泰国人妖,外壳妹子,内核汉子。

这篇关于勇闯BAT 聊聊Mysql的存储引擎的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/400698

相关文章

MySQL 8 中的一个强大功能 JSON_TABLE示例详解

《MySQL8中的一个强大功能JSON_TABLE示例详解》JSON_TABLE是MySQL8中引入的一个强大功能,它允许用户将JSON数据转换为关系表格式,从而可以更方便地在SQL查询中处理J... 目录基本语法示例示例查询解释应用场景不适用场景1. ‌jsON 数据结构过于复杂或动态变化‌2. ‌性能要

MySQL字符串常用函数详解

《MySQL字符串常用函数详解》本文给大家介绍MySQL字符串常用函数,本文结合实例代码给大家介绍的非常详细,对大家学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录mysql字符串常用函数一、获取二、大小写转换三、拼接四、截取五、比较、反转、替换六、去空白、填充MySQL字符串常用函数一、

MySQL中比较运算符的具体使用

《MySQL中比较运算符的具体使用》本文介绍了SQL中常用的符号类型和非符号类型运算符,符号类型运算符包括等于(=)、安全等于(=)、不等于(/!=)、大小比较(,=,,=)等,感兴趣的可以了解一下... 目录符号类型运算符1. 等于运算符=2. 安全等于运算符<=>3. 不等于运算符<>或!=4. 小于运

虚拟机Centos7安装MySQL数据库实践

《虚拟机Centos7安装MySQL数据库实践》用户分享在虚拟机安装MySQL的全过程及常见问题解决方案,包括处理GPG密钥、修改密码策略、配置远程访问权限及防火墙设置,最终通过关闭防火墙和停止Net... 目录安装mysql数据库下载wget命令下载MySQL安装包安装MySQL安装MySQL服务安装完成

MySQL进行数据库审计的详细步骤和示例代码

《MySQL进行数据库审计的详细步骤和示例代码》数据库审计通过触发器、内置功能及第三方工具记录和监控数据库活动,确保安全、完整与合规,Java代码实现自动化日志记录,整合分析系统提升监控效率,本文给大... 目录一、数据库审计的基本概念二、使用触发器进行数据库审计1. 创建审计表2. 创建触发器三、Java

MySQL逻辑删除与唯一索引冲突解决方案

《MySQL逻辑删除与唯一索引冲突解决方案》本文探讨MySQL逻辑删除与唯一索引冲突问题,提出四种解决方案:复合索引+时间戳、修改唯一字段、历史表、业务层校验,推荐方案1和方案3,适用于不同场景,感兴... 目录问题背景问题复现解决方案解决方案1.复合唯一索引 + 时间戳删除字段解决方案2:删除后修改唯一字

Zabbix在MySQL性能监控方面的运用及最佳实践记录

《Zabbix在MySQL性能监控方面的运用及最佳实践记录》Zabbix通过自定义脚本和内置模板监控MySQL核心指标(连接、查询、资源、复制),支持自动发现多实例及告警通知,结合可视化仪表盘,可有效... 目录一、核心监控指标及配置1. 关键监控指标示例2. 配置方法二、自动发现与多实例管理1. 实践步骤

MySQL 主从复制部署及验证(示例详解)

《MySQL主从复制部署及验证(示例详解)》本文介绍MySQL主从复制部署步骤及学校管理数据库创建脚本,包含表结构设计、示例数据插入和查询语句,用于验证主从同步功能,感兴趣的朋友一起看看吧... 目录mysql 主从复制部署指南部署步骤1.环境准备2. 主服务器配置3. 创建复制用户4. 获取主服务器状态5

SpringBoot中六种批量更新Mysql的方式效率对比分析

《SpringBoot中六种批量更新Mysql的方式效率对比分析》文章比较了MySQL大数据量批量更新的多种方法,指出REPLACEINTO和ONDUPLICATEKEY效率最高但存在数据风险,MyB... 目录效率比较测试结构数据库初始化测试数据批量修改方案第一种 for第二种 case when第三种

MySql基本查询之表的增删查改+聚合函数案例详解

《MySql基本查询之表的增删查改+聚合函数案例详解》本文详解SQL的CURD操作INSERT用于数据插入(单行/多行及冲突处理),SELECT实现数据检索(列选择、条件过滤、排序分页),UPDATE... 目录一、Create1.1 单行数据 + 全列插入1.2 多行数据 + 指定列插入1.3 插入否则更