一、简介

1.1、什么是存储引擎

• MySQL 可以将数据以不同的技术存储在文件（内存）中，这种技术就称为存储引擎。

• 每一种存储引擎使用不同的存储机制、索引技巧、锁定水平，最终提供广泛且不同的功能。

• MyISAM 和 InnoDB 是 MySQL 数据库管理系统中两种主要的存储引擎。它们在性能、功能和支持方面有显著的区别。

• MyISAM 和 InnoDB 主要区别总结：

  • 锁机制：MyISAM 使用表锁，而 InnoDB 使用行锁。

  • 事务支持：MyISAM 不支持事务，InnoDB 支持事务。

  • 外键约束：MyISAM 不支持外键约束，InnoDB 支持。

  • 存储格式：MyISAM 的数据和索引分开存储，InnoDB 的数据和索引存储在一起。

  • 并发控制：InnoDB 通过行锁和 MVCC 实现更好的并发控制。

  • 选择存储引擎时，需要根据具体应用的需求，如事务处理能力、并发性能、数据完整性等，来决定使用MyISAM还是InnoDB。

1.2、mysql 支持的存储引擎

• MyISAM

• InnoDB

• Memory

• CSV

• Archive

1.3、并发处理

①、并发控制

当多个连接对记录进行修改时保证数据的一致性和完整性。

②、锁

• 共享锁（读锁）：在同一时间段内，多个用户可以读取同一个资源，读取过程中数据不会发生任何变化。

• 排他锁（写锁）：在任何时候只能有一个用户写入资源，当进行写锁时会阻塞其他的读锁或者写锁操作。

③、锁颗粒

• 锁力度/锁颗粒（锁定的单位）：修改目的锁即可。比如存在用户表和商品表，当你修改用户信息的时候，只需对用户表或者某条记录加锁即可。

• 总之，加锁只加最对的，不加最大的。加锁会增加系统的开销，我们通过锁策略，在锁开销与服务器安全之间寻求一种平衡。

• 表锁，是一种开销最小的锁策略。

• 行锁，是一种开销最大的锁策略。

1.4、 设置存储引擎

①、通过修改MySQL配置文件实现

default-storage-engine=engine

②、通过创建数据表命令实现

CREATE TABLE table_name（ ）ENGINE=engine；

③、通过修改数据表命令实现

ALTER TABLE table_name ENGINE[=]engine_name；

1.5、MyISAM

• 管理非事务表
  

• 提供高速存储和检索，以及全文搜索能力。MyISAM 在所有 MySQL 配置里被支持，是默认的存储引擎，除非配置 MySQL 默认使用另外一个引擎。

• MyISAM：存储限制可达 256 TB，支持索引、表级锁定、数据压缩。

• MyISAM 特性：

  • 表锁：MyISAM 使用表锁来管理并发访问，这意味着在对表进行写操作时，整个表会被锁定，其他的读写操作会被阻塞。

  • 不支持事务：MyISAM 不支持事务（ACID 特性），这意味着你无法进行事务回滚或提交。

  • 不支持外键约束：MyISAM 不支持外键约束，这会影响数据的完整性。

  • 压缩：MyISAM 支持表的压缩功能，可以减少存储空间的使用。

  • 快速读操作：MyISAM 在读取操作上通常比 InnoDB 更快，适用于读操作频繁的应用。

  • 表级锁：适合对读操作频繁的环境，但对写操作较多的环境可能性能较差。

  • 存储格式：数据和索引分别存储在不同的文件中，数据文件的扩展名通常为 .MYD，索引文件为 .MYI。

• MyISAM 使用场景：

  • 主要用于读操作较多且对事务性要求不高的应用。

  • 适合对数据完整性要求不高但对查询性能有要求的环境。

• MyISAM 示例：

CREATE TABLE example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255)
) ENGINE=MyISAM;

1.6、MEMORY

• 提供“内存中”表
  

• MERGE 存储引擎允许集合将被处理同样的 MyISAM 表作为一个单独的表。

• 就像MyISAM 一样，MEMORY 和 MERGE 存储引擎处理非事务表，这两个引擎也都被默认包含在 MySQL 中。

• 注释：MEMORY 存储引擎正式地被确定为 HEAP 引擎。

1.7、InnoDB

• 提供事务安全表
  

• 提供事务和外键。默认被包括在所 有 MySQL 5.1 二进制分发版里，可以按照喜好通过配置 MySQL 来允许或禁止任一引擎。

• InnoDB：存储限制为64TB，支持事务和索引，锁颗粒为行锁。

• InnoDB 特性：

  • 行锁：InnoDB 使用行锁来管理并发访问，这可以减少锁争用，提高并发性。

  • 支持事务：InnoDB 支持事务，包括 ACID 特性（原子性、一致性、隔离性、持久性），可以进行事务回滚和提交。

  • 支持外键约束：InnoDB 支持外键约束，可以确保数据的完整性和一致性。

  • 聚集索引：InnoDB 的主键索引是聚集索引，数据是按主键排序存储的，这可以提高基于主键的查询效率。

  • 自恢复：InnoDB 具有崩溃恢复功能，能够在系统崩溃后自动恢复数据。

  • 表空间：InnoDB 将表数据存储在表空间中，可以在多个文件中进行数据存储，通常是 .ibd 文件。

  • 支持多版本并发控制（MVCC）：提高了并发性，减少了读写操作的冲突。

• InnoDB 使用场景：

  • 主要用于需要事务支持和数据完整性约束的应用。

  • 适合对写操作频繁且需要高并发的环境。

  • 适合需要外键约束和数据恢复功能的应用。

• InnoDB 示例

CREATE TABLE example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255)
) ENGINE=InnoDB;

1.8、CSV

• CSV 由逗号分割的存储引擎，在数据库的子目录为每一个表创建.csv文件，这是一种普通的文本文件，每一个数据行占用文本行。不支持 |索引。

1.9、BlackHole

• 黑洞引擎，写入的数据都会消失，一般用于做数据复制的中继。

二、功能点简介

2.1、MyISAM 存储引擎

MyISAM 存储引擎不支持事务，不支持行级锁，只支持并发插入的表锁，主要用于高负载的 select。

MyISAM 类型的表支持三种不同的存储结构：静态型、动态型、压缩型。

① 静态型：就是定义的表列的大小是固定（即不含有：xblob、xtext、varchar 等长度可变的数据类型），这样 mysql 就会自动使用静态 MyISAM 格式。

使用静态格式的表的性能比较高，因为在维护和访问的时候以预定格式存储数据时需要的开销很低。

但是这高性能是有空间换来的，因为在定义的时候是固定的，所以不管列中的值有多大，都会以最大值为准，占据了整个空间。

② 动态型：如果列（即使只有一列）定义为动态的（xblob, xtext, varchar 等数据类型），这时 MyISAM 就自动使用动态型。

虽然动态型的表占用了比静态型表较少的空间，但带来了性能的降低，因为如果某个字段的内容发生改变则其位置很可能需要移动，这样就会导致碎片的产生。

随着数据变化的怎多，碎片就会增加，数据访问性能就会相应的降低。

③ 压缩型：如果在这个数据库中创建的是在整个生命周期内只读的表，则这种情况就是用 MyISAM 的压缩型表来减少空间的占用。

2.2、MEMORY存储引擎

① memory 存储引擎相比前面的一些存储引擎，有点不一样，其使用存储在内存中的数据来创建表，而且所有的数据也都存储在内存中。

② 每个基于 memory 存储引擎的表实际对应一个磁盘文件，该文件的文件名和表名是相同的，类型为 .frm。

该文件只存储表的结构，而其数据文件，都是存储在内存中，这样有利于对数据的快速处理，提高整个表的处理能力。

③ memory 存储引擎默认使用哈希（HASH）索引，其速度比使用 B-+Tree 型要快，如果读者希望使用 B 树型，则在创建的时候可以引用。

④ memory 存储引擎文件数据都存储在内存中，如果 mysqld 进程发生异常，重启或关闭机器这些数据都会消失。所以 memory 存储引擎中的表的生命周期很短，一般只使用一次。

2.3、innoDB存储引擎

① innodb 存储引擎该 mysql 表提供了事务，回滚以及系统崩溃修复能力和多版本迸发控制的事务的安全。

② innodb 支持自增长列（auto_increment）,自增长列的值不能为空，如果在使用的时候为空的话怎会进行自动存现有的值开始增值，如果有但是比现在的还大，则就保存这个值。

③ innodb存储引擎支持外键（foreign key） ,外键所在的表称为子表而所依赖的表称为父表。

④ innodb存储引擎最重要的是支持事务，以及事务相关联功能。

⑤ innodb存储引擎支持 mvcc的行级锁。

三、mysql Innodb 引擎的外键约束

3.1、 mysql 启动和关闭外键约束的方法

在 mysql 中删除一张表或一条数据的时候，出现

[Err] 1451 -Cannot delete or update a parent row: a foreign key constraint fails (...)

这是因为 mysql 中设置了foreign key关联，造成无法更新或删除数据。可以通过设置 FOREIGN_KEY_CHECKS 变量来避免这种情况。

我们可以使用 SET FOREIGN_KEY_CHECKS=0 来禁用外键约束。

之后再用 SET FOREIGN_KEY_CHECKS=1 来启动外键约束。

查看当前 FOREIGN_KEY_CHECKS 的值可用如下命令 SELECT @@FOREIGN_KEY_CHECKS。

3.2、on update cascade 和on delete cascade 作用区别

这是数据库外键定义的一个可选项，用来设置当主键表中的被参考列的数据发生变化时，外键表中响应字段的变换规则的。

update 则是主键表中被参考字段的值更新，delete是指在主键表中删除一条记录。

on update 和 on delete 后面可以跟的词语有四个：

① no action 表示不做任何操作

② set null 表示在外键表中将相应字段设置为null

③ set default 表示设置为默认值

④ cascade 表示级联操作，就是说，如果主键表中被参考字段更新，外键表中也更新，主键表中的记录被删除，外键表中改行也相应删除