一、简介
1.1、什么是存储引擎
MySQL 可以将数据以不同的技术存储在文件(内存)中,这种技术就称为存储引擎。
每一种存储引擎使用不同的存储机制、索引技巧、锁定水平,最终提供广泛且不同的功能。
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:存储限制可达256TB,支持索引、表级锁定、数据压缩。
1.6、MEMORY
提供“内存中”表
MERGE 存储引擎允许集合将被处理同样的 MyISAM 表作为一个单独的表。
就像MyISAM 一样,MEMORY 和 MERGE 存储引擎处理非事务表,这两个引擎也都被默认包含在 MySQL 中。
注释:MEMORY 存储引擎正式地被确定为 HEAP 引擎。
1.7、InnoDB
提供事务安全表
提供事务和外键。默认被包括在所 有 MySQL 5.1 二进制分发版里,可以按照喜好通过配置 MySQL 来允许或禁止任一引擎。
InnoDB:存储限制为64TB,支持事务和索引,锁颗粒为行锁。
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 表示级联操作,就是说,如果主键表中被参考字段更新,外键表中也更新,主键表中的记录被删除,外键表中改行也相应删除