深入浅出mysql第三版《深入浅出mysql》

转载

mob6454cc673226 2023-07-29 10:40:11

文章标签 深入浅出mysql第三版 mysql 数据库 java 数据 文章分类 MySQL 数据库

路漫漫其修远兮，吾将上线而求索。

《深入浅出MySql》学习笔记

最近在看《深入浅出MySql》，打算系统学习一下MySql数据库，做些许记录，权做学习笔记。

一、存储引擎概述

登录数据库以后使用show engines命令可以查看当前安装数据库所支持的存储引擎，笔者安装的数据库版本为5.7.28，使用命令后查询出9种存储引擎，但是有一种并不支持。如下图：

深入浅出mysql第三版《深入浅出mysql》_深入浅出mysql第三版

以上存储引擎用户可以根据应用的需要选择如何存储和索引数据，是否使用事务等。各个存储引擎主要不同之处如下表所示：

特点	MyISAM	InnoDB	MEMORY	MERGE	NDB
存储限制	有	64TB	有	没有	有
事务安全		支持
锁机制	表锁	行锁	表锁	表锁	行锁
B树索引	支持	支持	支持	支持	支持
哈希索引			支持		支持
全文索引	支持
集群索引		支持
索引缓存	支持	支持	支持	支持	支持
数据可压缩	支持
空间使用	低	高	N\A	低	低
内存使用	低	高	中等	低	高
批量插入的速度	高	低	高	高	高
支持外键		支持

二、MyISAM

MyISAM存储引擎并不支持事务与外键，但其优势为访问的速度快，对事务完整新没有要求或者以SELECT、INSERT为主的应用可以考虑使用MyISAM来创建数据库表。

(一)MyISAM数据存储

每个MyISAM表在磁盘上存储为三个文件，文件名与表明相同，分别为：
.frm(存储表定义)
.MYD(MYData,存储数据)
.MYI(MYIndex,存储索引)
另外为提升访问速度，可以在创建表的时候通过DATA DIRECTORY和INDEX DIRECTORY语句指定数据文件和索引文件放在不同的目录，以平均分布IO。

(二)数据存储格式

MyISAM有三种数据存储格式，分别为：
静态(固定长度)表
字段均为非变长字段，每个记录均为固定长度，存储迅速，容易缓存，出现故障容易恢复，但是占用空间比动态表多。**静态表的数据存储会按照列的宽度定义补足空格，然后在访问数据时会去掉空格，且原有数据右侧存在空格时也会去掉！
动态表
动态表包含变长字段，占用空间较少，但是频繁的更新和删除记录会产生碎片，需定期执行OPTIMIZE TABLE语句或myisamchk-r命令来改善性能，且出现故障时相对比较困难
压缩表
压缩表只能由myisampack工具创建，占据空间小，访问开支小。

三、InnoDB

InnoDB存储引擎具有提交、回滚、崩溃恢复的食物安全。但是对比MyISAM,InnoDB写入数据效率差一些，并且会占用更多的磁盘空间以保留数据和索引。

(一)自动增长列

使用较多，不在赘述。需要注意的是指定自动增长的初始值时，对应的初始值保存在内存中，该值使用之前重启数据库的话会丢失。

(二)外键约束

在创建索引时，可以指定在删除、更新父表时对子表进行的相应操作，包括RESTRICT、CASCADE、SET NULL、NO ACTION。RESTRICT和NO ACTION相同，指限制在子表有关联记录的情况下父表不能更新；CASCADE表示父表在更新或者删除时，更新或者删除子表对应记录；SET NULL则表示父表在更新或者删除时，子表对应字段被SET NULL。选择后两种方式时应谨慎，防止数据丢失。
SET FOREIGN_KEY_CHECKS = 0或者1可以关闭和打开外键约束。

(三)存储方式

InnoDB
·共享表空间存储
表结构存储在.frm文件中，数据和索引存储在innodb_data_home_dir和innodb_data_file_path定义的表空间种，可以是多个文件。
·多表空间存储
表结构存储在.frm文件中，但是每个表的数据和索引单独保存在.ibd文件中，如果是个分区表，则每个分区对应单独的.ibd文件，文件名为“表名+分区名”，可在创建分区时指定每个分区的数据文件的位置，以此来将表的IO均匀分布在多个磁盘上。
多表空间的数据文件没有大小限制，不需设置初始大小，也不需设置文件的最大限制，扩展大小等参数。