MySQL存储引擎

MySQL存储引擎

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以 获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySql的核心就是存储引擎。

一. MyISAM与InnoDB优缺点及使用场景

存储引擎之InnoDB

• InnoDB 优点
  支持事务
  支持外键
  支持行级锁

• InnoDB 缺点
  InnoDB不支持FULLTEXT类型的索引，但是InnoDB可以使用sphinx插件支持全文索引
  InnoDB中不保存表的行数，InnoDB需要扫描一遍整个表来计算有多少行
  清空整个表时，InnoDB是一行一行的删除，效率非常慢
  对于自增长的字段，InnoDB中必须包含只有该字段的索引

• InnoDB 使用场景
  适合频繁修改以及涉及到安全性较高的应用（事务、外键和行级锁的特色）

存储引擎之MyISAM

• MyISAM 优点
  MyISAM可以简单的读出保存好的行数
  对于自增长的字段，MyISAM表中可以和其他字段一起建立联合索引
  清空整个表时，MyISAM则会重建表

• MyISAM 缺点
  不支持事务
  不支持外键

• MyISAM 使用场景
  MyISAM适合查询以及插入为主的应用。MYISAM索引和数据是分开的，而且其索引是压缩的，可以更好地利用内存。所以它的查询性能明显优于INNODB。压缩后的索引也能节约一些磁盘空间。MYISAM拥有全文索引的功能，这可以极大地优化LIKE查询的效率。

小结

1. 如果数据量比较大，这是需要通过升级架构来解决，比如分表分库，而不是单纯地依赖存储引擎。
2. 现在一般都是选用InnoDB了，主要是MYISAM的全表锁，读写串行问题，并发效率锁表，效率低，MYISAM对于读写密集型应用一般是不会去选用的。
3. 如果是默认安装，那就应该是InnoDB，你可以在my.cnf文件中找到default-storage-engine=INNODB，如果需要可以修改这个默认值

二. MEMORY

使用存在于内存中的内容创建表，每一个memory只实际对应一个磁盘文件。因为是存在内存中的，所以memory访问速度非常快，而且该引擎使用hash索引，可以一次定位，不需要像B树一样从根节点查找到支节点，所以精确查询时访问速度特别快，但是非精确查找时，比如like，这种范围查找，hash就起不到作用了。另外一旦服务关闭，表中的数据就会丢失，因为没有存到磁盘中。

适用场景：主要用于内容变化不频繁的表，或者作为中间的查找表。对表的更新要谨慎因为数据没有被写入到磁盘中，服务关闭前要考虑好数据的存储。

三. 存储引擎的选择

不同的存储引擎都有各自的特点，以适应不同的需求，如下表所示：

功 能	MYISAM	Memory	InnoDB	Archive
存储限制	256TB	RAM	64TB	NONE
支持事务	NO	NO	YES	NO
支持全文索引	YES	NO	NO	NO
支持数索引	Yes	Yes	Yes	No
支持哈希索引	No	Yes	No	No
支持数据缓存	No	N/A	Yes	No
支持外键	No	No	Yes	No

如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择

如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率

如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果

如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive

使用哪一种引擎需要灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能

四. 知识补充

事务

1.什么是事务？
事务是应用程序中一系列严密的操作，所有操作必须成功完成，否则在每个操作中所作的所有更改都会被撤消。也就是事务具有原子性，一个事务中的一系列的操作要么全部成功，要么一个都不做。

事务的结束有两种，当事务中的所以步骤全部成功执行时，事务提交。如果其中一个步骤失败，将发生回滚操作，撤消撤消之前到事务开始时的所以操作。

2.事务的 ACID
事务具有四个特征：原子性（ Atomicity ）、一致性（ Consistency ）、隔离性（ Isolation ）和持续性（ Durability ）。这四个特性简称为 ACID 特性：

1. 原子性：事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做
2. 一致性：事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统 运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是 不一致的状态。
3. 隔离性：一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。
4. 持续性：也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。

外键

如果一张表中有一个非主键的字段指向了别一张表中的主键，就将该字段叫做外键。

外键的功能也是开发友好型，DBA不友好型。同样外键所提供的功能也是属于业务逻辑。外键的存在对数据库的性能损耗也是巨大的、对数据库的日常维护也是不友好的，更易造成死锁的问题，也不易于后期数据库架构的扩展。对于并发量较大的应用，强烈不建议使用外键约束。当然对于性能要求不高，而且开发人员较少的情况下（主要降低开发人员的代码量，加快项目进度）可以使用外键。

外键的默认作用有两点：

1. 对子表(外键所在的表)的作用：子表在进行写操作的时候，如果外键字段在父表中找不到对应的匹配，操作就会失败。
2. 对父表的作用：对父表的主键字段进行删和改时，如果对应的主键在子表中被引用，操作就会失败。

外键的定制作用，三种约束模式：

1. district：严格模式(默认), 父表不能删除或更新一个被子表引用的记录。
2. cascade：级联模式, 父表操作后，子表关联的数据也跟着一起操作。
3. set null：置空模式,前提外键字段允许为NLL, 父表操作后，子表对应的字段被置空。

使用外键的前提：

1. 表储存引擎必须是innodb，否则创建的外键无约束效果。
2. 外键的列类型必须与父表的主键类型完全一致。
3. 外键的名字不能重复。
4. 已经存在数据的字段被设为外键时，必须保证字段中的数据与父表的主键数据对应起来。

常用命令：

在创建时增加外键
foreign key(class)　references my_tab2(主键字段名);
在创建好的表中增加外键
alter table my_tab1 add [constraint 外键名] foreign key(外键字段名) references mytab2(主键字段名);
删除外键
alter table my_tab drop foreign key 外键名字;

MySQL表级锁和行级锁简单研究

相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。比如，MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；InnoDB存储引擎既支持行级锁（ row-level locking），也支持表级锁，但默认情况下是采用行级锁。

MySQL主要的两种锁的特性可大致归纳如下:

1. 表级锁： 开销小，加锁快；不会出现死锁(因为MyISAM会一次性获得SQL所需的全部锁)；锁定粒度大，发生锁冲突的概率最高,并发度最低。
2. 行级锁： 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

考虑上述特点，表级锁使用与并发性不高，以查询为主，少量更新的应用，比如小型的web应用；而行级锁适用于高并发环境下，对事务完整性要求较高的系统，如在线事务处理系统。

MyISAM锁细述：

1. 锁模式：MySQL的表级锁有两种模式： 表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。
2. 如何加锁：当MyISAM在执行查询语句时，会自动给涉及到表加读锁，在执行更新操作时，会加写锁。当然用户也可以用LOCK TABLE 去显式的加锁。显式的加锁一般是应用于：需要在一个时间点实现多个表的一致性读取，不然的话，可能读第一个表时，其他表由于还没进行读操作，没有自动加锁，可能数据会发生改变。并且显示加锁后只能访问加锁的表，不能访问其他表。
3. 并发插入：MyISAM存储引擎有个系统变量 concurrent_insert，专门用来控制并发插入的行为，可以取 0 ， 1 ， 2。0表示不允许并发插入，1表示表中间没有删除的行时可以在表末尾插入，2表示总是可以插入。一般如果对并发要求比较高的情况下，可以设置为2，总是可以插入，然后定期在数据库空闲时间对表进行optimize。
4. 锁的调度：需要注意的是，其中读操作不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；并且当写锁和读锁同时被申请时，优先获得写锁，这也这正是表级锁发生锁冲突概率最高的原因，因为写锁可能会一直阻塞读锁，所以不适合有大量写操作的环境下工作。这一问题可以通过设置low-priority-updates这一启动参数来降低写的优先级。
   虽然写锁优先于读锁获取，但是长时间的查询操作也可能会让写操作饿死，所以尽量避免一条SQL语句执行所有的查询，应该进行必要的分解。

InnoDB锁细述：

1. 锁模式：共享锁(S)和排他锁(X)，分别类似于MyISAM的读锁和写锁。对于 UPDATE、 DELETE 和 INSERT 语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通 SELECT 语句，InnoDB不会加任何锁。
2. 如何加锁：可以显式的加锁，用lock in share mode 显式的加共享锁，用 for update 显式的加排他锁。需要注意的是，如果线程A加了共享锁后，线程B对同一个表加了共享锁，那么两个线程需要进行更新操作时会产生死锁。所以，进行更新操作时最好加排他锁。
3. InnoDB行锁的实现方式——索引加锁：这一点与Oracle不同，所以这也意味着(重要)：1. 只有通过索引条件检索数据时，InnoDB才会使用行级锁，否则会使用表级锁。 2. 即使是访问不同行的记录，如果使用的是相同的索引键，会发生锁冲突。 3. 如果数据表建有多个索引时，可以通过不同的索引锁定不同的行。
4. 间隙锁：InnoDB支持事务，为了满足隔离级别的要求，InnoDB有个间隙锁，当使用范围查找时，InnoDB会给满足key范围要求，但实际并不存在的记录加锁。例如：select * from user where id > 100 for updata 会给ID>100的记录加排他锁，满足这个范围，但不存在的记录，会加间隙锁，这样可以避免幻读，避免读取的时候插入满足条件的记录。
5. 隔离级别与锁：一般来说，隔离级别越高，加锁就越严格。这样，产生锁冲突的概率就越大，一般实际应用中，通过优化应用逻辑，选用 可提交读 级别就够了。对于一些确实需要更高隔离级别的事务，再通过set session transaction isolation level+”级别” 来动态改变满足需求。

死锁：
MyISAM是没有死锁问题的，因为他会一次性获得所有的锁。InnoDB发生死锁后一般能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。

在应用中，可以通过如下方式来尽可能的避免死锁：

1. 如果不同的程序会并发的存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。
2. 在程序以批量方式处理数据时，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大的降低出现死锁的可能。