@TOC

0. 物理设计-常用的MySQL存储引擎

‌每一列所使用的数据类型,以及如何对数据库中的库,表 对象来进行命名。‌‌
但是对于 MySQL 数据库来说,还有一项工作是必须要做的,首先要选择表所使用的存储引擎,‌‌MySQL 支持多种存储引擎,而存储引擎又决定了表中数据的实际存储结构,所以我们先来看一下‌‌如何被表选择一个适合的存储引擎。‌‌

那么面对一道选择题,我们首先要知道的是可供我们选择的选项都有些什么?
因此我们先来了解一下 MySQL 中几个比较常用的存储引擎,‌‌
那么我们先来看 MYISAM 存储引擎【念买萨姆】,它是 MySQL 使用的最为广泛的一种存储引擎了,‌‌ MYISAM 引擎是在 MySQL5.6版本之前,MySQL 默认使用的一种存储引擎,‌‌甚至到了 MySQL5.7 版本的时候, MySQL 的系统数据库中‌‌的表以及超过一定限制的临时表,还都在使用这种存储引擎。‌

‌那么 MYISAM 存储引擎也是一种非事务性的存储引擎,也就是说它并不支持事务,同时‌‌在 MYISAM 存储引擎中,数据是以堆表的方式来进行存储的,也就是说‌‌存储在 MYISAM 表中的数据并没有任何的特定顺序,不像存储在聚集索引的表中,‌‌数据可以按聚集索引的顺序来存储,那么在 MYISAM 存储引擎的表中并不存在聚集索引的概念,

所以你的叶子节点直接是指向的是数据的物理地址,而不是聚集索引的位置,‌‌因此这也就避免了查询中使用索引的二次查找,对于大量的查询的性能可能会有一定的提高,‌‌但是由于其读写都会对数据加锁,那么在频繁读写的业务系统中很容易产生大量的阻塞,‌‌从而影响系统的整体性能。‌

‌所以现在并不推荐来使用这种存储引擎 来 作为核心业务系统的存储引擎来使用。‌

‌接下来的是 CSV 存储引擎,CSV 存储引擎就跟它的名字一样,‌‌是采用 CSV 文件格式来存储数据的一种存储引擎,‌‌我们甚至可以直接编辑表的 CSV 存储文件的内容来对表中的数据来进行修改。‌‌
而且 CSV 存储引擎它也是一种不支持事物的非事务型存储引擎。‌

‌那么由于其不支持事务,‌‌读写时会对整个表加锁,所以我们通常会使用 CSV 存储引擎进行‌‌不同系统间的数据交换,但并不建议使用 CSV 存储引擎作为存储核心的业务系统的数据来使用。‌

下面这个 Archive 存储引擎,接触过的人就不是那么多了,‌‌这也是 MySQL 官方默认提供的一种存储引擎,但是由于它的适用场景比较特别,‌‌所以可能大多数人并没有用过,但是 Archive 存储引擎在一些场景下,‌‌确实可以帮助我们实现一些业务数据的存储需求。‌

‌Archive 存储引擎是一种默认,只允许查询‌‌和新增数据,而不允许对已经存在的数据进行修改的非事务性存储引擎。‌
‌也就是说使用这种存储引擎的表,我们只能进行select的和insert的操作,‌‌而不能进行其他的比如delete 或 update的操作。‌
‌那么现在大家是不是可以想到它的使用场景了?‌‌‌

Archive 存储引擎 通常我们可以把它用在归档历史数据,‌‌或是记录日志类数据的这样的只会新增而不会被修改的业务场景中,‌‌
并且使用‌ Archive 存储引擎 所记录的数据,其所占用的物理文件的大小要比其他存储引擎都要小,‌‌而在下面的 Memory 存储引擎,从名字中我们就可以知道,这是一种存储在内存中的‌‌非事务型的存储引擎。‌

‌那么由于 Memory 存储引擎的数据是存储在内存中的,‌‌其最大的特点应该就是读写的速度会非常的快。‌

‌我们知道数据库的最大瓶颈就在于 IO 的性能,‌‌而 Memory 存储引擎恰好有很强的IO性能,
不过‌‌一旦 MySQL 实例重启,‌‌那么存储在 Memory 存储 的数据就会消失,‌‌所以它也是一种易失性的存储引擎,
所以 Memory 存储引擎‌‌并不适合作为我们的核心业务的数据存储引擎来使用,
我们一般也很少主动的去建立这种存储引擎的表,‌‌ Memory 存储引擎‌‌的主要用处是在 MySQL 内部,

MySQL 在执行一些大的 sql 的过程中,‌‌如果需要临时存储中间结果的数据集,并且这个中间数据在符合一定的条件的前提下,就会把中间数据‌‌临时存储在 Memory 存储引擎‌‌的表中,这样可以加快 sql 的执行速度,

而 INNODB 存储引擎才是我们在当前业务环境中使用最多的一种存储引擎。‌

‌INNODB 是目前来说最常用的 MySQL 事务型存储引擎,也是 MySQL 自5.6之后默认使用的一个存储引擎。‌

‌除了 ‌INNODB 之外, MySQL 还有其他的一些事务性存储引擎,如果没有什么特别的需要,‌‌在选择存储核心业务数据的时候,我们都会首选 INNODB 存储引擎 存储引擎。‌​

1. 物理设计-INNODB存储引擎的特点

INNODB 存储引擎和之前所说的其他存储引擎相比,‌‌最重要的特点就是INNODB 存储引擎,它是一款事务性的存储引擎,‌‌
它完全支持事务的原子性、一致性、隔离性和持久性的一些特点。那么换句话说,如果在我们的业务场景中‌‌需要使用到数据库事务的话,那么最好的选择就是使用 INNODB 存储引擎,并且要注意‌‌在需要事务支持的场景中,一定不要混合使用事务性存储引擎和非事务性存储引擎。‌

‌因为如果我们在一个事务中同时使用了事务性存储引擎的表和非事务性存储引擎的表的话,‌‌
那么一旦事务由于某种原因进行了回滚,那么对于非事务性存储引擎中的数据‌‌所进行的修改就是无法回滚的,这样就破坏了事务的一致性的要求,同时也就破坏了‌‌数据的完整性。‌

‌那么 INNODB 存储引擎的第二个特点就是 INNODB 表中的数据在逻辑上‌‌它是按照表中主键的顺序来存储,
也就是说 INNODB 的表中的主键,它是一种聚集索引的主键,‌‌那么我们在选择使用什么样的列为表中组件的时候就要特别注意了,‌‌和 MYISAM 存储引擎使用的是堆的存储方式不同,在具有聚集索引主键的这种存储引擎中,‌‌每一个非主键的索引的叶子节点所指向的都是数据行中的主键,而不是数据行的物理存储位置,‌‌
因此主键的大小就直接影响到索引查找数据的性能。‌

‌另一方面由于数据是按主键的逻辑顺序来进行存储的,如果键的顺序经常无规则的变化,‌‌一定会造成数据的迁移,这样也会带来 IO 性能上的一些损耗。‌

‌所以一般情况下,如果我们使用 INNODB 存储引擎的表,都是建议使用一个自增ID来作为表的主键的。‌‌
那么从这一点上来看,我们之前在对表进行逻辑设计时,所选择的业务主键并不适合作为 INNODB 表的主键来使用,
但是我们现在又需要使用 INNODB 存储引擎‌‌来存储我们的业务数据,那要怎么办呢?‌‌

这时候我们就要给每一个表再加一个自动ID列 来 作为表的数据库主键,而之前所选择的这种业务主键,‌‌我们可以在上面建立一个唯一索引,这样也同样可以保证其数据是唯一的。‌

‌那么 INNODB 存储引擎 的第三个特点‌‌就是它支持行级锁及 MVCC,
比如说在进行数据读写操作时,‌‌只会在需要读取的数据行上来加锁,而不会像 MYISAM 那样在整个表上来加锁,‌‌这无疑就可以大大增强 INNODB 存储引擎的数据的并发处理能力。‌

‌另外 INNODB 存储引擎 还支持 MVCC,‌‌也就是多版本的并发控制,可以进一步来避免读写操作的互相阻塞,‌‌
所以 INNODB 存储引擎非常适合在这种高并发的读写混合的应用场景中来使用。‌‌

那么从对索引方面的支持来看, INNODB 存储引擎 除了支持 b树 索引之外,‌‌还支持自适应的哈希索引。‌
‌所谓自适应哈希索引,就是 INNODB 存储引擎 ,根据数据的统计信息,‌‌在内存中自动建立的哈希索引,这种索引只能用于等值查找,‌‌并且只能由 INNODB 内部来进行维护,不需要 DBA 来进行人为的干预,‌‌并且自 MySQL5.6 之后, INNODB 同时也支持了全文索引。‌

‌在 MySQL5.7 之后的版本中, INNODB 又支持了对空间数据进行了索引,
在 MySQL5.7 版本之前,如果我们‌‌想使用全文索引或者是空间类型数据的话,就只能使用 MYISAM 的表,因为那个时候只有MYISAM 存储引擎 支持 对这两种类型的索引。

自 MySQL5.7 之后,如果你再想使用空间类型数据或者说是全文索引的话,‌‌完全可以使用 INNODB 做存储引擎了。
这也是说为什么我们在没有什么特殊要求情况下,可以完全使用 INNODB 搞定我们业务数据的存储需求。‌​