MySQL存储引擎如何理解
发布时间：2022-06-15 03:18:04 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要讲解了MySQL存储引擎怎么理解，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习MySQL存储引擎怎么理解吧！ 今天发现了一个神奇的参数：-site:xxxx.net 一、存储引擎的选择（表类型） 1、存储引
|
这篇文章主要讲解了“MySQL存储引擎怎么理解”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“MySQL存储引擎怎么理解”吧！ 今天发现了一个神奇的参数：-site:xxxx.net 一、存储引擎的选择（表类型） 1、存储引擎的介绍 与到多数关系型数据库的区别在于MySQL有一个存储引擎的概念，针对不同的存储需求可以选择最合适的存储引擎。MySQL中的插件式的存储引擎是其一大特色，用户可以根据应用的需求选择如何存储、是否索引，是否使用事务。嘿嘿，你也可以根据业务环境去适配最适合自己业务的存储引擎。 Oracle从中嗅到了商机，收购了MySQL，从此有了企业版（商业支持）。社区版依旧可以免费下载。另一大魅力也是因为开源，社区高度活跃，人人都可贡献。接下来介绍几种使用比较多的存储引擎，存储引擎并无优劣之分，有的只是谁更适合对应的生产业务环境。 MySQL5.0中支持的存储引擎有FEDERATED、MRG_MYISAM、MyISAM、BLACKHOLE、CSV、MEMORY、ARCHIVE、NDB Cluster、BDB、EXAMPLE、InnoDB（MySQL5.5以及MariaDB10.2之后的默认存储引擎）、PERFORMANCE_SCHEMA（非常规存储数据引擎）。下面给出MySQL与MariaDB支持的存储器引擎的对比，可以看出MariaDB新增了Aria引擎： 作用描述： Engine：引擎名称（描述）； Support：当前版本数据库是否支持该存储引擎，YES：支持、NO：不支持；Supports transactions, row-level locking, and foreign keys，个人字面上翻译这段话：支持事务、行级别锁和外键； Comment：对该存储引擎的详情描述，比如描述该引擎否支持事务和外键； Transactions：对该存储引擎是否支持事务的描述，YES：支持、NO：不支持； XA：是否满足XA规范。XA规范是开放群组关于分布式事务处理(DTP)的规范。YES：支持、NO：不支持； Savepoints：字面意思是保存点，对事物控制是否支持，YES：支持、NO：不支持。 小声哔哔，如果你能阅读明白官方的一些英文文档，这将有助于你对MySQL存储引擎的进一步理解，养成阅读源码或者文档的能力。 顺带的提一下MySQL的妹妹MariaDB。在MySQL的复刻版本MariaDB中10.2之前使用的自带的新引擎Aria，在MariaDB10.2之后使用的默认存储引擎也是InnoDB，足以看出InnoDB存储引擎的优秀之处。MariaDB的API和协议兼容MySQL，另外又添加了一些功能，以支持本地的非阻塞操作和进度报告。这意味着，所有使用MySQL的连接器、程序库和应用程序也将可以在MariaDB下工作。在此基础上，由于担心甲骨文MySQL的一个更加封闭的软件项目，Fedora等Linux发行版已经在最新版本中以MariaDB取代MySQL，维基媒体基金会的服务器同样也使用MariaDB取代了MySQL。 主要需要了解的几种存储引擎： MyISAM InnoDB MEMORY MERGE 下面将着重介绍我最近看书认识的几种常用的存储引擎，对比各个存储引擎之间的区别，帮助我们理解不同存储引擎的使用方式。更多详情可以参考MySQL的官方文档。 2、部分存储引擎的特性 存储引擎/支持特性 存储限制 事务安全 锁机制 B树索引 哈希索引 全文索引 集群索引 数据缓存 索引缓存 数据可压缩 空间使用 内存使用 批量插入速度 外键支持 InnoDB存储引擎在MySQL5.6版本开始支持全文索引。在MySQL5.7推出了虚拟列，MySQL8.0新特性加入了函数索引支持。 2.1、MyISAM存储引擎 MyISAM是MySQL5.5之前默认的存储引擎。MyISAM不支持事务、不支持外键。优势在于访问速度快，对事务完整性没有特殊要求或者以select和insert为主的应用基本上可以使用MyISAM作为存储引擎创建表。我们先弄个例子出来演示，事先准备了一张数据千万级别的表，看看这个存储引擎的特性： 我已经创建好了数据库为test，在test中分别创建了两张表test和tolove。test表在创建的时候指定默认存储引擎为MyISAM，tolove表指定存储引擎为InnoDB。 使用MyISAM存储引擎创建的表tolove，查询存储有1kw数据的表tolove。 tips：你可以使用use test，切换到test数据库，就不用像我这样查询tolove表去指定test数据库了哟！ MySQL [(none)]> select count(*) from test.tolove; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (0.000 sec) 再看演示使用InnoDB存储引擎创建的表test，同样为了演示，事先随机生成了1kw条数据。 MySQL [(none)]> select count(*) from test.test; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (3.080 sec) 进行对比同样存储1kw条数据的表，使用MyISAM作为存储引擎查询速度堪称光速1 row in set (0.000 sec)，使用InnoDB存储引擎查询速度稍逊一筹1 row in set (3.080 sec)。 MyISAM在磁盘中存储的文件： 每个MyISAM在磁盘上存储成3个文件，其文件名和表名都相同，扩展名分别是： .frm：存储表定义； .MYD：MYData，存储数据； .MYI：MYindex，存储索引。 MySQL存储引擎怎么理解 数据文件和索引文件可以存放在不同的目录，平均分布IO，获得更快的速度，提升性能。需要指定索引文件和数据文件存储的路径，创建表时通过DATA DIRECTORY和INDEX DIRECTORY参数指定，表明不同MyISAM表的索引文件和数据文件可以存放在不同的路径下。当然，需要给予该路径的访问权限。 MyISAM损坏处理方式 ： MyISAM类型的表可能会损坏，原因多种多样。损坏后的表有可能不能被访问，会提示需要修复或者访问后提示返回错误结果。MyISAM类型的表，可以通过提供的修复工具执行CHECK TABLE语句检查MyISAM表的健康程度，使用REPAIR TABLE语句修复一个损坏的表。表损坏可能会导致数据库异常重新启动，需要尽快修复并确定原因好做应对策略。 MySQL存储引擎怎么理解 使用MyISAM存储引擎的表支持3种不同的存储格式，如下： 静态表，固定长度； 动态表 压缩表 静态表是MyISAM存储引擎的默认存储格式，字段长度是定长，记录都是固定长度。优势在于存储迅速、容易缓存、出现故障易恢复；缺点是相对耗存储空间。需要注意的是：如需保存内容后面的空格，默认返回结果会去掉后面的空格。 动态表包含变长字段，记录不是固定长度，存储优势：占用空间相对较小、但频繁删除和更新记录会产生碎片。这时，需要定期执行optimize table语句或者myisamchk -r命令来改善性能，出现故障恢复相对较难。 压缩表由mysiampack工具创建，占用磁盘空间很小。因为每个记录是被单独压缩，所以访问开始非常小。 梳理一下MyISAM存储引擎的要点，如下图1-2-2-1所示： MySQL存储引擎怎么理解 顺带安利一波，前段时间发现WPS也能够制作精美的思维导图，并且支持一键导入到doc文件中。普通用户最多可存储150个文件。之前也用过XMind、processon、gitmind等等，现在使用WPS更方便了。 2.2、InnoDB存储引擎 优点与缺点：InnoDB存储引擎提供了具有提交（commit）、回滚（rollback）和崩溃恢复能力的事务安全。但对比MyISAM存储引擎，InnoDB写的处理效率相对差一些，并且会占用更多的磁盘空间保留数据和索引。下图是我存储了1kw条数据的表，并且使用的是InnoDB存储引擎。student01表同样使用了InnoDB存储引擎，存储数据为100w条。从下图可以看出存储数据索引在.ibd文件中、表结构则存在.frm文件中。 MySQL存储引擎怎么理解 2.2.1、自动增长列 InnoDB表的自动增长列可以手工插入，但插入的值为空或者0，则实际插入的将是自动自动增长后的值。 本来想继续使用bols那张表作为演示的，思来想去还是正经一点。为了演示，我又新增了一张表为autoincre_test，表示id设置为主键且自增长，存储引擎选择InnoDB。然后插入了3条数据进行演示。查询当前线程最后插入数据的记录使用值： MySQL [test]> create table autoincre_test(id int not null auto_increment,name varchar(16),primary key(id))engine=innodb; Query OK, 0 rows affected (0.018 sec) MySQL [test]> insert into autoincre_test values(1,'1'),(0,'2'),(null,'3'); Query OK, 3 rows affected (0.007 sec) Records: 3 Duplicates: 0 Warnings: 0 MySQL [test]> select * from autoincre_test; +----+------+ | id | name | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | +----+------+ 3 rows in set (0.000 sec) select last_insert_id(); MySQL [test]> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 2 | +------------------+ 1 row in set (0.000 sec) tips：可以通过alter table table_name=n;语句强制设置自动增长列的初始值，默认从1开始，但该强制的默认值是保留在内存中的，如果使用该值之前数据库重新启动，强制默认值则会丢失，就需要重新设置，毕竟使用内存没有加载到磁盘中。 通过上面的演示，你会发现插入记录是0或者空时，实际插入的将是自动增长后的值。通过last_insert_id()函数可以查询当前线程最后插入数据的记录使用值。如果一次插入多条记录，则返回的是第一条记录使用的自动增长值，这里就不演示插入多条数据了。记住一点，可以使用last_insert_id()去查询id记录值。 对于InnoDB表，自动增长列必须是索引。如果是组合索引，也必须是组合索引的第一列。但对于MyISAM表，自动增长列可以是组合索引的其它列。这样插入记录后，自动增长列是按照组合索引的前面几列排序后递增的。你可以创建一张表指定MyISAM存储引擎，然后将两列字段组合索引进行测试验证。 2.2.2、外键约束 在MySQL中，目前支持外键约束的存储引擎只有InnoDB。创建外键的时候，要求父表必须有对应的索引。子表创建外键的时候，也会自动创建对应的索引。下面将通过实例进行讲解。可以从MySQL官网下载示例数据库world和sakila进行参考。 city表，FOREIGN KEY (CountryCode) REFERENCES country (Code) country表 countrylanguage表，FOREIGN KEY (CountryCode) REFERENCES country (Code) 通过MySQL workbench或者Navicat逆向生成物理模型进行参考，更加直观。插一句，在MySQL的官网同样有一个sakila数据库是关于演员电影的，也提供了sakila的ERR物理模型图，这句话做了超链接，可以直接访问。给出我之前逆向生成的world数据库的物理模型： MySQL存储引擎怎么理解 在创建索引时，可以指定在删除、更新父表时，对子表进行的相应操作包含： restrict cascade set null和no action 其中restrict和no action相同，restrict限制在子表有关联记录的情况下父表无法更新；cascade表示在父表更新或删除的时候，级联更新或者删除子表对应记录；set null表示在父表更新或删除的时候，子表的对应字段被set null。选择cascade以及set null时需要谨慎操作，有可能导致数据丢失。 在导入多个表的数据时，如果忽略表之前的导入顺序，可以暂时关闭外键检查；同样执行load data和alter table时也可以暂时关闭外键检查加快处理的速度，提升效率。关闭外键检查的命令为： set foreign_key_checks=0; 执行完导入数据或者修改表的操作后，通过开启外键检查命令改回来： set foreign_key_checks=1; 对于InnoDB类型的表，外键信息可以通过show create table或者show table status查看。比如查找world数据库中的city表： MySQL [sakila]> show table status like 'city'G 关于外键约束就提这么多，没有演示创建以及删除，因为贴太多的SQL语句太占篇幅了。可以到MySQL官网下载world和sakila数据库进行测试。 2.2.3、存储方式 InnoDB存储表和索引有两种方式： 共享表空间存储 多表空间存储 使用共享表空间存储，这种方式创建的表的表结构保存在.frm文件中，数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中，可以是多个文件。在开头介绍InnoDB存储引擎时也提到过文件存储位置。 使用多表空间存储，这种方式创建的表的表结构仍然保存在.frm文件中，但每个表的数据和索引单独保存在.ibd文件中。如果是个分区表，则每个分区对应单独的.ibd文件，文件名为表名+分区名。可以在创建分区的时候指定每个分区的数据文件位置，以此来平均分布磁盘的IO，达到缓解磁盘压力的目的。如下是在Windows下使用InnoDB存储了海量数据的文件： MySQL存储引擎怎么理解 使用多表空间存储需要设置参数innodb_file_per_table，重启数据库服务器才能生效哟。多表空间的参数生效后，只对新建的表生效。多表空间的数据文件无大小限制，无需设置初始大小，也不需设置文件的最大限制与扩展大小等参数。使用多表空间存储优势在于方便单表备份和恢复操作。虽然不能直接复制.frm和.ibd文件达到目的，但可以使用如下命令操作： alter table table_name discard tablespace;alter table table_name import tablespace; 将备份恢复到数据库中，单表备份，只能恢复到原来所在的数据库中，无法恢复到其它数据库中。如过需要将单表恢复至其它目标数据库中，则需要通过mysqldump和mysqlimport来实现。 注意：即便多表存储更有优势，但是共享表存储空间依旧是必须的，InnoDB将内部数据字典和在线重做日志存在这个文件中。 梳理一下InnoDB存储引擎的要点， MySQL存储引擎怎么理解 关于InnoDB存储引擎就介绍到此处了，更多详情可以参考MySQL的官方文档。是不是发现了我只在MyISAM和InnoDB存储引擎做了总结的思维导图。没错，只做了这两个，因为这俩最常用。至于为啥是粉色背景，因为老夫有一颗少女心！ 2.3、MEMORY存储引擎 MEMORY存储引擎使用存在与内存中的内容来创建表。每个MEMORY表只对应一个磁盘文件，格式是.frm。MEMORY类型的表访问速度极快，存在内存中当然快。这就是Redis为什么这么快？不仅小？还能持久？咱回到正题，MEMORY存在内存中并默认使用hash索引，一旦服务关闭，表中数据会丢失。创建一张名为GIRLS的表指定存储引擎为MEMORY，注意了在UNIX和Linux操作系统下，是对字段和表名大小是写敏感的，关键字不影响。 CREATE TABLE GIRLS ( ID int NOT NULL,GIRE_NAME varchar(64) NOT NULL,GIRL_AGE varchar(10) NOT NULL, CUP_SIZE varchar(2) NOT NULL,PRIMARY KEY (ID)) ENGINE=MEMORY DEFAULT CHARSET=utf8 COLLATE=utf8_bin; 还记得在介绍存储引擎做的那会张表格吗，有介绍到MEMORY支持B TREE索引。虽然MEMORY默认使用的索引是hash索引，但是你可以手动指定索引类型。例如默认手动指定使用关键字USING HASH： -- 创建索引指定索引类型为hash。create index mem_hash USING HASH on GIRLS(ID);-- 查询索引类型，简化了一下，只展示了部分参数。mysql> SHOW TABLE STATUS LIKE 'GIRLS'G*************************** 1. row *************************** Name: GIRLS Engine: MEMORY Version: 10 Row_format: Fixed1 row in set (0.00 sec) 虽然MEMORY容易丢失数据，但是在启动MySQL服务的时候，我们可以使用**–init-file选项，将insert into … select或者load data infile**这样的语句存放在这个指定的文件中，就可以在服务启动时从持久稳固的数据源装载表。 服务器需要提供足够的内存来维持所有在同一时间使用的MEMORY表，当不在需要MEMORY表内容之时，释放被MEMORY表使用的内存。仔细思考一下，如果内存用了不释放那将有多可怕。此时可以执行delete form 或truncate table亦或完整地删除整个表，使用drop table。这里提一点，在Oracle中也同样支持truncate，使用truncate的好处在于不用再去考虑回滚（rollback），效率更高。使用truncate需要在命令模式下使用，其它客户端工具可能不支持。 每个MEMORY表中存放的数据量大小，受max_heap_table_size系统变量约束，初始值为16MB，可以根据需求调整。通过max_rows可以指定表的最大行数。 MEMORY存储引擎最大特色是快，主要用于内容变化不频繁的代码表，或者是为了做统计提供的中间表，效率更高。使用MEMORY时需谨慎，万一忘了这厮重启数据就没了就尴尬了。所以在使用时，考虑好重启服务器后如何取得数据。 关于MEMORY存储引擎就介绍到这里，大部分都是些理论知识，更多的需要自己去实践测试。 2.4、MERGE存储引擎 MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表必须结果完全相同，MERGE表本身没有数据，对MERGE类型的表可以进行查询、更新、删除操作，实际上是对内部的MyISAM表进行操作的。对于MERGE类型表的插入操作，通过insert_method子句定义插入的，可以有3个不同的值，使用first或last插入操作对应开始与最后一个表上。如果不定义这个子句，或者定义为NO，表示不能对MERGE表进行操作。 对MERGE表进行DROP操作，只是对MERGE的定义进行删除，对内部表没有任何影响。MERGE表上保留两个文件，文件名以表的名字开始，分别为： .frm文件存储表定义； .mrg文件包含组合表的信息，包含表组成、插入数据依据。 可以通过修改.mrg文件来修改表，但修改后需要使用flush tables刷新。测试可以先创建两张存储引擎为MyISAM的表，再建一张存储引擎为MERGE存储引擎的表。 （编辑：大连站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐