你可能会忽视的 MySQL 细节(上)

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 你可能会忽视的 MySQL 细节(上)

我们在 MySQL 入门篇主要介绍了基本的 SQL 命令、数据类型和函数,在具备以上知识后,你就可以进行 MySQL 的开发工作了,但是如果要成为一个合格的开发人员,你还要具备一些更高级的技能,下面我们就来探讨一下 MySQL 都需要哪些高级的技能


MySQL 存储引擎


存储引擎概述


数据库最核心的一点就是用来存储数据,数据存储就避免不了和磁盘打交道。那么数据以哪种方式进行存储,如何存储是存储的关键所在。所以存储引擎就相当于是数据存储的发动机,来驱动数据在磁盘层面进行存储。


MySQL 的架构可以按照三层模式来理解


image.png


存储引擎也是 MySQL 的组建,它是一种软件,它所能做的和支持的功能主要有


  • 并发
  • 支持事务
  • 完整性约束
  • 物理存储
  • 支持索引
  • 性能帮助


MySQL 默认支持多种存储引擎,来适用不同数据库应用,用户可以根据需要选择合适的存储引擎,下面是 MySQL 支持的存储引擎


  • MyISAM
  • InnoDB
  • BDB
  • MEMORY
  • MERGE
  • EXAMPLE
  • NDB Cluster
  • ARCHIVE
  • CSV
  • BLACKHOLE
  • FEDERATED


默认情况下,如果创建表不指定存储引擎,会使用默认的存储引擎,如果要修改默认的存储引擎,那么就可以在参数文件中设置 default-table-type,能够查看当前的存储引擎


show variables like 'table_type';


image.png


奇怪,为什么没有了呢?网上求证一下,在 5.5.3 取消了这个参数


可以通过下面两种方法查询当前数据库支持的存储引擎


show engines \g


image.png


在创建新表的时候,可以通过增加 ENGINE 关键字设置新建表的存储引擎。


create table cxuan002(id int(10),name varchar(20)) engine = MyISAM;


image.png


上图我们指定了 MyISAM 的存储引擎。


如果你不知道表的存储引擎怎么办?你可以通过 show create table 来查看


image.png


image.png


可以看到,默认的存储引擎是 InnoDB


如果你的存储引擎想要更换,可以使用


alter table cxuan003 engine = myisam;


来更换,更换完成后会显示 「0 rows affected」 ,但其实已经操作成功


image.png


存储引擎特性


下面会介绍几个常用的存储引擎以及它的基本特性,这些存储引擎是 MyISAM、InnoDB、MEMORY 和 MERGE


MyISAM



在 5.1 版本之前,MyISAM 是 MySQL 的默认存储引擎,MyISAM 并发性比较差,使用的场景比较少,主要特点是


  • 不支持事务操作,ACID 的特性也就不存在了,这一设计是为了性能和效率考虑的。


  • 不支持外键操作,如果强行增加外键,MySQL 不会报错,只不过外键不起作用。


  • MyISAM 默认的锁粒度是表级锁,所以并发性能比较差,加锁比较快,锁冲突比较少,不太容易发生死锁的情况。


  • MyISAM 会在磁盘上存储三个文件,文件名和表名相同,扩展名分别是 .frm(存储表定义).MYD(MYData,存储数据)MYI(MyIndex,存储索引)。这里需要特别注意的是 MyISAM 只缓存索引文件,并不缓存数据文件。


  • MyISAM 支持的索引类型有 全局索引(Full-Text)B-Tree 索引R-Tree 索引


Full-Text 索引:它的出现是为了解决针对文本的模糊查询效率较低的问题。
B-Tree 索引:所有的索引节点都按照平衡树的数据结构来存储,所有的索引数据节点都在叶节点




R-Tree索引:它的存储方式和 B-Tree 索引有一些区别,主要设计用于存储空间和多维数据的字段做索引,目前的 MySQL 版本仅支持 geometry 类型的字段作索引,相对于 BTREE,RTREE 的优势在于范围查找。


  • 数据库所在主机如果宕机,MyISAM 的数据文件容易损坏,而且难以恢复。


  • 增删改查性能方面:SELECT 性能较高,适用于查询较多的情况


InnoDB


自从 MySQL 5.1 之后,默认的存储引擎变成了 InnoDB 存储引擎,相对于 MyISAM,InnoDB 存储引擎有了较大的改变,它的主要特点是



  • 支持事务操作,具有事务 ACID 隔离特性,默认的隔离级别是可重复读(repetable-read)、通过MVCC(并发版本控制)来实现的。能够解决脏读不可重复读的问题。


  • InnoDB 支持外键操作。


  • InnoDB 默认的锁粒度行级锁,并发性能比较好,会发生死锁的情况。


  • 和 MyISAM 一样的是,InnoDB 存储引擎也有 .frm文件存储表结构 定义,但是不同的是,InnoDB 的表数据与索引数据是存储在一起的,都位于 B+ 数的叶子节点上,而 MyISAM 的表数据和索引数据是分开的。


  • InnoDB 有安全的日志文件,这个日志文件用于恢复因数据库崩溃或其他情况导致的数据丢失问题,保证数据的一致性。


  • InnoDB 和 MyISAM 支持的索引类型相同,但具体实现因为文件结构的不同有很大差异。


  • 增删改查性能方面,果执行大量的增删改操作,推荐使用 InnoDB 存储引擎,它在删除操作时是对行删除,不会重建表。


MEMORY


MEMORY 存储引擎使用存在内存中的内容来创建表。每个 MEMORY 表实际只对应一个磁盘文件,格式是 .frm。MEMORY 类型的表访问速度很快,因为其数据是存放在内存中。默认使用 HASH 索引


MERGE


MERGE 存储引擎是一组 MyISAM 表的组合,MERGE 表本身没有数据,对 MERGE 类型的表进行查询、更新、删除的操作,实际上是对内部的 MyISAM 表进行的。MERGE 表在磁盘上保留两个文件,一个是 .frm 文件存储表定义、一个是 .MRG 文件存储 MERGE 表的组成等。


选择合适的存储引擎


在实际开发过程中,我们往往会根据应用特点选择合适的存储引擎。


  • MyISAM:如果应用程序通常以检索为主,只有少量的插入、更新和删除操作,并且对事物的完整性、并发程度不是很高的话,通常建议选择 MyISAM 存储引擎。


  • InnoDB:如果使用到外键、需要并发程度较高,数据一致性要求较高,那么通常选择 InnoDB 引擎,一般互联网大厂对并发和数据完整性要求较高,所以一般都使用 InnoDB 存储引擎。


  • MEMORY:MEMORY 存储引擎将所有数据保存在内存中,在需要快速定位下能够提供及其迅速的访问。MEMORY 通常用于更新不太频繁的小表,用于快速访问取得结果。


  • MERGE:MERGE 的内部是使用 MyISAM 表,MERGE 表的优点在于可以突破对单个 MyISAM 表大小的限制,并且通过将不同的表分布在多个磁盘上, 可以有效地改善 MERGE 表的访问效率。




相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
存储 算法 关系型数据库
你可能会忽视的 MySQL 细节(下)
你可能会忽视的 MySQL 细节(下)
104 0
你可能会忽视的 MySQL 细节(下)
|
存储 SQL 缓存
你可能会忽视的 MySQL 细节(上)
你可能会忽视的 MySQL 细节(上)
117 0
你可能会忽视的 MySQL 细节(上)
|
存储 算法 关系型数据库
你可能会忽视的 MySQL 细节(下)
你可能会忽视的 MySQL 细节(下)
你可能会忽视的 MySQL 细节(下)
|
9天前
|
SQL 关系型数据库 MySQL
12 PHP配置数据库MySQL
路老师分享了PHP操作MySQL数据库的方法,包括安装并连接MySQL服务器、选择数据库、执行SQL语句(如插入、更新、删除和查询),以及将结果集返回到数组。通过具体示例代码,详细介绍了每一步的操作流程,帮助读者快速入门PHP与MySQL的交互。
24 1
|
11天前
|
SQL 关系型数据库 MySQL
go语言数据库中mysql驱动安装
【11月更文挑战第2天】
27 4
|
1月前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构,类似于书籍中的索引。它允许用户快速找到数据,而无需扫描整个表。MySQL中的索引可以显著提升查询速度,使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
61 3
Mysql(4)—数据库索引
|
18天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
86 1
|
20天前
|
关系型数据库 MySQL Linux
在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。
本文介绍了在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。同时,文章还对比了编译源码安装与使用 RPM 包安装的优缺点,帮助读者根据需求选择最合适的方法。通过具体案例,展示了编译源码安装的灵活性和定制性。
61 2
|
23天前
|
存储 关系型数据库 MySQL
MySQL vs. PostgreSQL:选择适合你的开源数据库
在众多开源数据库中,MySQL和PostgreSQL无疑是最受欢迎的两个。它们都有着强大的功能、广泛的社区支持和丰富的生态系统。然而,它们在设计理念、性能特点、功能特性等方面存在着显著的差异。本文将从这三个方面对MySQL和PostgreSQL进行比较,以帮助您选择更适合您需求的开源数据库。
90 4
|
6天前
|
运维 关系型数据库 MySQL
安装MySQL8数据库
本文介绍了MySQL的不同版本及其特点,并详细描述了如何通过Yum源安装MySQL 8.4社区版,包括配置Yum源、安装MySQL、启动服务、设置开机自启动、修改root用户密码以及设置远程登录等步骤。最后还提供了测试连接的方法。适用于初学者和运维人员。
45 0