索引
事务隔离级别
锁
SQL join
Innodb和MyISAM引擎
MyCAT

索引

数据结构：

二叉树：O(logn),深度会深，io多
B树、B+树
Hash：哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。
哈希索引有好几个局限(根据他本质的原理可得)：
- 哈希索引也没办法利用索引完成排序
- 不支持最左匹配原则
- 在有大量重复键值情况下，哈希索引的效率也是极低的—->哈希碰撞问题。
- 不支持范围查询

innoDB密集索引：每个搜索码值都对应一个索引值
Myisam稀疏索引：只为索引码的某些值建立索引项

如何定位并优化慢查询sql：
根据慢日志定位慢查询SQL
使用explain等工具分析SQL
修改SQL或尽量让SQL走索引

没有索引的查找

本集的主题是索引，在正式介绍索引之前，我们需要了解一下没有索引的时候是怎么查找记录的。为了方便大家理解，我们下边先只唠叨搜索条件为对某个列精确匹配的情况，所谓精确匹配，就是搜索条件中用等于=连接起的表达式，比如这样：

1	SELECT [列名列表] FROM 表名 WHERE 列名 = xxx;

在一个页中的查找

假设目前表中的记录比较少，所有的记录都可以被存放到一个页中，在查找记录的时候可以根据搜索条件的不同分为两种情况：

以主键为搜索条件
这个查找过程我们已经很熟悉了，可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。
以其他列作为搜索条件
对非主键列的查找的过程可就不这么幸运了，因为在数据页中并没有对非主键列建立所谓的页目录，所以我们无法通过二分法快速定位相应的槽。这种情况下只能从最小记录开始依次遍历单链表中的每条记录，然后对比每条记录是不是符合搜索条件。很显然，这种查找的效率是非常低的。

在很多页中查找

大部分情况下我们表中存放的记录都是非常多的，需要好多的数据页来存储这些记录。在很多页中查找记录的话可以分为两个步骤：

定位到记录所在的页。
从所在的页内中查找相应的记录。

不论是根据主键列或者其他列的值进行查找，由于我们并不能快速的定位到记录所在的页，所以只能从第一个页沿着双向链表一直往下找，在每一个页中根据我们上边已经唠叨过的查找方式去查找指定的记录。因为要遍历所有的数据页，所以这种方式显然是超级耗时的，如果一个表有一亿条记录，使用这种方式去查找记录那要等到猴年马月才能等到查找结果。

为了故事的顺利发展，我们先建一个表：

mysql> CREATE TABLE index_demo(    
    ->     c1 INT,    
    ->     c2 INT,    
    ->     c3 CHAR(1),    
    ->     PRIMARY KEY(c1)    
    -> ) ROW_FORMAT = Compact;Query OK, 0 rows affected (0.03 sec)
mysql>

这个新建的index_demo表中有2个INT类型的列，1个CHAR(1)类型的列，而且我们规定了c1列为主键，这个表使用Compact行格式来实际存储记录的。为了我们理解上的方便，我们简化了一下index_demo表的行格式示意图：

我们只在示意图里展示记录的这几个部分：

record_type：记录头信息的一项属性，表示记录的类型，0表示普通记录、2表示最小记录、3表示最大记录、1我们还没用过，等会再说～
next_type：记录头信息的一项属性，表示下一条地址的偏移量，为了方便大家理解，我们都会用箭头来表明下一条记录是谁。
各个列的值：就是各个数据列的值，其中我们用橘黄色的格子代表c1列，深蓝色的格子代表c2列，红色格子代表c3列。
其他信息：除了上述3种信息以外的所有信息，包括其他隐藏列的值以及记录的额外信息。

为了节省篇幅，我们之后的示意图中会把记录的其他信息这个部分省略掉，因为它占地方并且不会有什么观赏效果。另外，为了方便理解，我们觉得把记录竖着放看起来感觉更好，所以将记录格式示意图的其他信息去掉并把它竖起来的效果就是这样：

一个简单的索引方案

回到正题，我们为什么要遍历所有的数据页呢？因为各个页中的记录并没有规律，我们并不知道我们的搜索条件匹配哪些页中的记录，所以 不得不 依次遍历。所以如果我们想快速的定位到需要查找的记录在哪些数据页中该咋办？就像为数据页中的记录建立一个目录一样，我们也可以为所有的数据页建立一个目录呀，建这个目录必须完成下边这些事儿：

下一个数据页的主键值必须大于上一个页中的主键值。

其实这句话的完整表述是这样的：下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。为了故事的顺利发展，我们这里需要做一个假设：假设我们的每个数据页最多能存放3条记录（实际上一个数据页非常大，可以存放下好多记录）。有了这个假设之后我们向index_demo表插入3条记录：

mysql> INSERT INTO index_demo VALUES(1, 4, 'u'), (3, 9, 'd'), (5, 3, 'y');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql>

那么这些记录已经按照主键值的大小串联成一个单向链表了，如图所示：

从途中可以看出来，index_demo表中的3条记录都被插入到了编号为10的数据页中了。此时我们再来插入一条记录：

1
2
3

mysql> INSERT INTO index_demo VALUES(4, 4, 'a');
Query OK, 1 row affected (0.00 sec)
mysql>

因为页10最多只能放3条记录，所以我们不得不再分配一个新页：

需要注意的一点是，新分配的数据页编号可能并不是连续的，也就是说我们使用的这些页在存储空间里可能并不挨着。它们只是通过维护着上一个页和下一个页的编号而建立了链表关系。另外，页10中用户记录最大的主键值是5，而页28中有一条记录的主键值是4，因为5 > 4，所以这就不符合下一个数据页的主键值必须大于上一个页中的主键值的要求，所以在插入主键值为4的记录的时候需要伴随着一次记录移动，也就是把主键值为5的记录移动到页28中，然后再把主键值为4的记录插入到页10中，这个过程的示意图如下：

这个过程表明了在对页中的记录进行增删改操作的过程中，我们必须通过一些诸如记录移动的操作来始终保证这个状态一直成立：下一个数据页的主键值必须大于上一个页中的主键值。

给所有的页建立一个目录项。
由于数据页的编号可能并不是连续的，所以在向index_demo表中插入许多条记录后，可能是这样的效果：

因为这些16KB的页在物理存储上并不挨着，所以如果想从这么多页中根据主键值快速定位某些记录所在的页，我们需要给它们做个目录，每个页对应一个目录项，每个目录项包括下边两个部分：

页的用户记录中最小的主键值，我们用key来表示。
页号，我们用page_no表示。

所以我们为上边几个页做好的目录就像这样子：

以页28为例，它对应目录项2，这个目录项中包含着该页的页号28以及该页中用户记录的最小主键值5。我们只需要把几个目录项在物理存储器上连续存储，比如把他们放到一个数组里，就可以实现根据主键值快速查找某条记录的功能了。比方说我们想找主键值为20的记录，具体查找过程分两步：

先从目录项中根据二分法快速确定出主键值为20的记录在目录项3中（因为 12 < 20 < 209），它对应的页是页9。
再根据前边说的在页中查找记录的方式去页9中定位具体的记录。

至此，针对数据页做的简易目录就搞定了。不过忘了说了，这个目录有一个别名，称为索引。

InnoDB中的索引方案

上边之所以称为一个简易的索引方案，是因为我们假设所有目录项都可以在物理存储器上连续存储，但是这样做有几个问题：

InnoDB是使用页来作为管理存储空间的基本单位，也就是最多能保证16KB的连续存储空间，而随着表中记录数量的增多，需要非常大的连续的存储空间才能把所有的目录项都放下，这对记录数量非常多的表是不现实的。
我们时常会对记录进行增删，假设我们把页28中的记录都删除了，页28也就没有存在的必要了，那意味着目录项2也就没有存在的必要了，这就需要把目录项2后的目录项都向前移动一下，这种牵一发而动全身的设计不是什么好主意。

所以，设计InnoDB的大叔们需要一种可以灵活管理所有目录项的方式。他们灵光乍现，忽然发现这些目录项其实长得跟我们的用户记录差不多，只不过目录项中的两个列是主键和页号而已，所以他们复用了之前存储用户记录的数据页来存储目录项，为了和用户记录做一下区分，我们把这些用来表示目录项的记录称为目录项记录。那InnoDB怎么区分一条记录是普通的用户记录还是目录项记录呢？别忘了记录头信息里的record_type属性，它的各个取值代表的意思如下：

0：普通的用户记录
1：目录项记录
2：最小记录
3：最大记录

原来这个值为1的record_type是这个意思呀，我们把前边使用到的目录项放到数据页中的样子就是这样：

从图中可以看出来，我们新分配了一个编号为30的页来专门存储目录项记录。这里再次强调一遍目录项记录和普通的用户记录的不同点：

目录项记录的record_type值是1，而普通用户记录的record_type值是0。
目录项记录只有主键值和页的编号两个列，而普通的用户记录的列是用户自己定义的，可能包含很多列，另外还有InnoDB自己添加的隐藏列。
还记得我们之前在唠叨记录头信息的时候说过一个叫min_rec_mask的属性么，只有在存储目录项记录的页中的主键值最小的目录项记录的min_rec_mask值为1，其他别的记录的min_rec_mask值都是0。

除了上述几点外，这两者就没啥差别了，它们用的是一样的数据页（页面类型都是0x45BF，这个属性在Page Header中，忘了的话可以翻到前边的文章看），页的组成结构也是一样一样的（就是我们前边介绍过的7个部分），都会为主键值生成Page Directory（页目录）以加快在页内的查询速度。所以现在根据某个主键值去查找记录的步骤可以大致拆分成下边两步，以查找主键为20的记录为例（因为都是从一个页中通过主键查某条记录，所以都可以使用Page Directory通过二分法而实现快速查找）：

先到存储目录项记录的页中通过二分法快速定位到对应目录项，因为12 < 20 < 209，所以定位到对应的记录所在的页就是页9.
从页9中根据二分法快速定位到主键值为20的用户记录。

虽然说目录项记录中只存储主键值和对应的页号，比用户记录需要的存储空间小多了，但是不论怎么说一个页只有16KB大小，能存放的目录项记录也是有限的，那如果表中的数据太多，以至于一个数据页不足以存放所有的目录项记录，该咋办呢？

当然是再多整一个存储目录项记录的页喽～为了大家更好的理解如何新分配一个目录项记录页的过程，我们假设一个存储目录项记录的页最多只能存放4条目录项记录（请注意是假设哦，真实情况下可以存放好多条的），所以如果此时我们再向上图中插入一条主键值为320的用户记录的话，那就需要一个分配一个新的存储目录项记录的页：

从图中可以看出，我们插入了一条主键值为320的用户记录之后新生成了2个数据页，以查找主键值为20的记录为例：

为存储该用户记录而新生成了页31。
因为原先存储目录项记录的页30的容量已满（我们前边假设只能存储4条目录项记录），所以不得不新生成了一个页32来存放页31对应的目录项。

因为存储目录项记录的页不止一个，所以如果我们想根据主键值查找一条用户记录大致需要3个步骤：

确定目录项记录页
我们现在的存储目录项记录的页有两个，即页30和页32，又因为页30表示的目录项的主键值的范围是[1, 320)，页32表示的目录项的主键值不小于320，所以主键值为20的记录对应的目录项记录在页30中。
通过目录项记录页确定用户记录真实所在的页。
在一个存储目录项记录中定位一条目录项记录的方式说过了，不赘述了～
在真实存储用户记录的页中定位到具体的记录。
在一个存储用户记录的页中定位一条真实的用户记录的方式已经说过200遍了，你再不会我就，我就，我就求你到上一篇唠叨数据页结构的文章中多看几遍，求你了～

那么问题来了，在这个查询步骤的第1步中我们需要定位存储目录项记录的页，但是这些页在存储空间中也可能不挨着，如果我们表中的数据非常多则会产生很多存储目录项记录的页，那我们怎么根据主键值快速定位一个存储目录项记录的页呢？其实也简单，为这些存储目录项记录的页再生成一个更高级的目录，就像是一个多级目录一样，大目录里嵌套小目录，小目录里才是实际的数据，所以现在各个页的示意图就是这样子：

如图，我们生成了一个存储更高级目录项的页33，这个页中的两条记录分别代表页30和页32，如果用户记录的主键值在[1, 320)之间，则到页30中查找更详细的目录项记录，如果主键值不小于320的话，就到页32中查找更详细的目录项记录。不过这张图好漂亮喔，随着表中记录的增加，这个目录的层级会继续增加，如果简化一下，那么我们可以用下边这个图来描述它（B+tree）：

因为我们把数据页都存放到B+树这个数据结构中了，所以我们也把我们的数据页称为节点。从图中可以看出来，我们的实际用户记录其实都存放在B+树的最底层的节点上，这些节点也被称为叶子节点或叶节点，其余的节点都是用来存放目录项的，这些节点统统被称为内节点或者说非叶节点。其中最上边的那个节点也称为根节点。

从图中可以看出来，一个B+树的节点其实可以分成好多层，设计InnoDB的大叔们为了讨论方便，规定最下边的那层，也就是存放我们用户记录的那层为第0层，之后依次往上加。上边我们做了一个非常极端的假设，存放用户记录的页最多存放3条记录，存放目录项记录的页最多存放4条记录，其实真实环境中一个页存放的记录数量是非常大的，假设，假设，假设所有的数据页，包括存储真实用户记录和目录项记录的页，都可以存放1000条记录，那么：

如果B+树只有1层，也就是只有1个用于存放用户记录的节点，最多能存放1000条记录。
如果B+树有2层，最多能存放1000×1000=1000000条记录。
如果B+树有3层，最多能存放1000×1000×1000=1000000000条记录。
如果B+树有4层，最多能存放1000×1000×1000×1000=1000000000000条记录。

你的表里能存放1000000000000条记录么？所以一般情况下，我们用到的B+树都不会超过4层，那我们通过主键去查找某条记录最多只需要做4个页面内的查找，又因为在每个页面内有所谓的Page Directory（页目录），所以在页面内也可以通过二分法实现快速定位记录。

聚簇索引

我们上边介绍的B+树本身就是一个目录，或者说本身就是一个索引。它有两个特点：

使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：
- 页内的记录是按照主键的大小顺序排成一个单向链表。
- 各个存放用户记录的页也是根据页中记录的主键大小顺序排成一个双向链表。
- 各个存放目录项的页也是根据页中记录的主键大小顺序排成一个双向链表。
B+树的叶子节点存储的是完整的用户记录。
所谓完整的用户记录，就是指这个记录中存储了所有列的值。

我们把具有这两种特性的B+树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点处。这种聚簇索引并不需要我们在MySQL语句中显式的去创建，InnoDB存储引擎会自动的为我们创建聚簇索引。另外有趣的一点是，在InnoDB存储引擎中，聚簇索引就是数据的存储方式（所有的用户记录都存储在了叶子节点），也就是所谓的索引即数据。

二级索引

大家有木有发现，上边介绍的聚簇索引只能在搜索条件是主键值时才能发挥作用，因为B+树中的数据都是按照主键进行排序的。那如果我们想以别的列作为搜索条件可以多建几棵B+树，不同的B+树中的数据采用不同的排序规则。比方说我们用c2列的大小作为数据页、页中记录的排序规则，再建一棵B+树，效果如下图所示：

这个B+树与上边介绍的聚簇索引有几处不同：

使用记录c2列的大小进行记录和页的排序，这包括三个方面的含义：
页内的记录是按照c2列的大小顺序排成一个单向链表。
各个存放用户记录的页也是根据页中记录的c2列大小顺序排成一个双向链表。
各个存放目录项的页也是根据页中记录的c2列大小顺序排成一个双向链表。
B+树的叶子节点存储的并不是完整的用户记录，而只是c2列+主键这两个列的值。
目录项记录中不再是主键+页号的搭配，而变成了c2列+页号的搭配。

所以如果我们现在想通过c2列的值查找某些记录的话就可以使用我们刚刚建好的这个B+树了，以查找c2列的值为4的记录为例，查找过程如下：

确定目录项记录页
根据根页面，也就是页44，可以快速定位到目录项记录所在的页为页42（因为2 < 4 < 9）。
通过目录项记录页确定用户记录真实所在的页。
在页42中可以快速定位到实际存储用户记录的页，但是由于c2列并没有唯一性约束，所以c2列值为4的记录可能分布在多个数据页中，又因为2 < 4 ≤ 4，所以确定实际存储用户记录的页在页34和页35中。
在真实存储用户记录的页中定位到具体的记录。
到页34和页35中定位到具体的记录。
但是这个B+树的叶子节点中的记录只存储了c2和c1（也就是主键）两个列，所以我们必须再根据主键值去聚簇索引中再查找一遍完整的用户记录。

我们根据这个以c2列大小排序的B+树只能确定我们要查找记录的主键值，所以如果我们想根据c2列的值查找到完整的用户记录的话，仍然需要到聚簇索引中再查一遍，这个过程也被称为回表。也就是根据c2列的值查询一条完整的用户记录需要使用到2`棵B+`树！

为什么我们还需要一次回表操作呢？直接把完整的用户记录放到叶子节点不就好了么？你说的对，如果把完整的用户记录放到叶子节点是可以不用回表，但是太占地方了呀～相当于每建立一棵B+树都需要把所有的用户记录再都拷贝一遍，这就有点太浪费存储空间了。因为这种按照非主键列建立的B+树需要一次回表操作才可以定位到完整的用户记录，所以这种B+树也被称为二级索引（英文名secondary index），或者辅助索引。由于我们使用的是c2列的大小作为B+树的排序规则，所以我们也称这个B+树为为c2列建立的索引。

联合索引

我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比方说我们想让B+树按照c2和c3列的大小进行排序，这个包含两层：

先把各个记录和页按照c2列进行排序。
在记录的c2列相同的情况下，采用c3列进行排序

为c2和c3列建立的索引的示意图如下：

如图所示，我们需要注意一下几点：

每条目录项记录都由c2、c3、页号这三个部分组成，各条记录先按照c2列的值进行排序，如果记录的c2列相同，则按照c3列的值进行排序。
B+树叶子节点处的用户记录由c2、c3和主键c1列组成。

千万要注意一点，以c2和c3列的大小为排序规则建立的B+树称为联合索引，它的意思与分别为c2和c3列建立索引的表述是不同的，不同点如下：

建立联合索引只会建立如上图一样的1棵B+树。
为c2和c3列建立索引会分别以c2和c3列的大小为排序规则建立2棵B+树。

MyISAM中的索引方案

至此，我们介绍的都是InnoDB存储引擎中的索引方案，为了内容的完整性，以及各位可能在面试的时候遇到这类的问题，我们有必要再简单介绍一下MyISAM存储引擎中的索引方案。我们知道InnoDB中索引即数据，也就是聚簇索引的那棵B+树的叶子节点中已经把所有完整的用户记录都包含了，而MyISAM的索引方案虽然也使用B+树，但是却将索引和数据分开存储：

将表中的记录按照插入时间顺序的存储在一块存储空间上，我们可以通过行号而快速访问到一条记录（因为index_demo表的记录是定长的，所以可以使用行号来进行快速访问，对于变长的记录MyISAM有不同的处理方案，我们这里就不介绍了），如图所示：
由于在插入数据的时候并没有刻意按照主键大小排序，所以我们并不能在这些数据上使用二分法进行查找。
MyISAM会单独为表的主键创建一个B+树索引，只不过在B+树的叶子节点中存储的不是完整的用户记录，而是主键值 + 行号的组合。也就是先通过索引找到对应的行号，再通过行号去找对应的记录！
这一点和InnoDB是完全不相同的，在InnoDB存储引擎中，我们只需要根据主键值对聚簇索引进行一次查找能找到对应的记录，而在MyISAM中却需要进行一次回表操作，意味着MyISAM中建立的索引全部都是二级索引！
如果有需要的话，我们也可以对其它的列分别建立索引或者建立联合索引，原理和InnoDB中的索引是一样的，只不过在叶子节点处存储的是相应的列 + 行号而已。这些索引也全部都是二级索引。

MySQL中创建和删除索引的语句

光顾着唠叨索引的原理了，那我们如何使用MySQL语句去建立这种索引呢？InnoDB和MyISAM会自动为主键或者声明为UNIQUE的列去自动建立B+树索引，但是如果我们想为其他的列建立索引就需要我们显式的去指明。为啥不自动为每个列都建立个索引呢？别忘了，每建立一个索引都会建立一棵B+树，每插入一条记录都要维护各个记录、数据页的排序关系，这是很费性能和存储空间的。

我们可以在创建表的时候指定需要建立索引的单个列或者建立联合索引的多个列：

1	CREATE TALBE 表名 ( 各种列的信息 ··· , [KEY\|INDEX] 索引名 (需要被索引的单个列或多个列))

其中的KEY和INDEX是同义词，任意选用一个就可以。我们也可以在修改表结构的时候添加索引：

1	ALTER TABLE 表名 ADD [INDEX\|KEY] 索引名 (需要被索引的单个列或多个列);

也可以在修改表结构的时候删除索引：

1	ALTER TABLE 表名 DROP [INDEX\|KEY] 索引名;

比方说我们想在创建index_demo表的时候就为c2和c3列添加一个联合索引，可以这么写建表语句：

CREATE TABLE index_demo(    
c1 INT,    
c2 INT,    
c3 CHAR(1),    
PRIMARY KEY(c1),    
INDEX idx_c2_c3 (c2, c3));

在这个建表语句中我们创建的索引名是idx_c2_c3，这个名称可以随便起，不过我们还是建议以idx_为前缀，后边跟着需要建立索引的列名，多个列名之间用下划线_分隔开。

如果我们想删除这个索引，可以这么写：

1	ALTER TABLE index_demo DROP INDEX idx_c2_c3;

总结

对于InnoDB存储引擎来说，在单个页中查找某条记录分为两种情况：
- 以主键为搜索条件，可以使用Page Directory通过二分法快速定位相应的用户记录。
- 以其他列为搜索条件，需要按照记录组成的单链表依次遍历各条记录。
没有索引的情况下，不论是以主键还是其他列作为搜索条件，只能沿着页的双链表从左到右依次遍历各个页。
InnoDB存储引擎的索引是一棵B+树，完整的用户记录都存储在B+树第0层的叶子节点，其他层次的节点都属于内节点，内节点里存储的是目录项记录。InnoDB的索引分为两大种：
- 聚簇索引
  以主键值的大小为页和记录的排序规则，在叶子节点处存储的记录包含了表中所有的列。
- 二级索引
  以自定义的列的大小为页和记录的排序规则，在叶子节点处存储的记录内容是列 + 主键。
MyISAM存储引擎的数据和索引分开存储，这种存储引擎的索引全部都是二级索引，在叶子节点处存储的是列 + 页号。

查询的匹配问题

CREATE TABLE person_info(
    name VARCHAR(100) NOT NULL,
    birthday DATE NOT NULL,
    phone_number CHAR(11) NOT NULL,
    country varchar(100) NOT NULL,
    KEY idx_name_age_birthday (name, birthday, phone_number)
);

全值匹配

如果我们的搜索条件中的列和索引列一致的话，这种情况就称为全值匹配，比方说下边这个查找语句：

1	SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27' AND phone_number = '15123983239';

我们建立的idx_name_age_birthday索引包含的3个列在这个查询语句中都展现出来了，而且搜索条件中列的顺序和定义索引列时的顺序是一致的。
如果搜索条件中的列的顺序和索引列的顺序不一致就不太好了，比方说先对birthday列的值进行匹配的话，由于B+树中的数据页和记录是先按name列的值进行排序的，不能直接使用二分法快速定位记录，所以只能扫描所有的记录页。

匹配左边的列

比如下边的语句就用不到这个B+树索引么？

1	SELECT * FROM person_info WHERE birthday = '1990-09-27';

是的，的确用不到，因为B+树的数据页和记录先是按照name列的值排序的，你直接根据birthday的值去查找，臣妾做不到呀～那如果我就想在只使用birthday的值去通过B+树索引进行查找咋办呢？这好办，你再对birthday列建一个B+树索引就行了。

但是需要特别注意的一点是，搜索条件中的列的顺序必须和索引列的定义顺序一致，比方说索引列的定义顺序是name、birthday、phone_number，如果我们的搜索条件中只有name和phone_number，而没有birthday，这样只能用到name列的索引，birthday和phone_number的索引就用不上了（因为name值相同的记录先按照birthday进行排序，birthday值相同的记录才按照phone_number值进行排序）。

匹配列前缀

对于字符串类型的索引列来说，我们没必要对该列的值进行精确匹配，只匹配它的前缀也是可以的，因为前缀本身就已经是排好序的。比方说我们想查询名字以'As'开头的记录，那就可以这么写查询语句：

1	SELECT * FROM person_info WHERE name LIKE 'As%';

B+树中的数据页和记录都先是按照name列排序的，只给出前缀也是可以通过二分法快速定位的。但是需要注意的是，如果只给出后缀或者中间的某个字符串，比如这样：

1	SELECT * FROM person_info WHERE name LIKE '%As%';

MySQL就无法通过二分法来快速定位记录位置了，因为字符串中间有'As'的字符串并没有排好序，所以只能全表扫描了。

匹配范围值

回头看我们idx_name_age_birthday索引的B+树示意图，所有记录都是按照索引列的值从小到大的顺序排好序的，所以这极大的方便我们查找索引列的值在某个范围内的记录。比方说下边这个查询语句：

1	SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';

由于B+树中的节点和数据页是先按name列排序的，所以我们上边的查询过程其实是这样的：

找到name值为Asa的记录。
找到name值为Barlow的记录。
由于所有记录都是由连链表连起来的（记录之间用单链表，数据页之间用双链表），所以他们之间的记录都可以很容易的取出来喽～
找到这些记录的主键值，再到聚簇索引中回表查找完整的记录。

不过进行范围查找的时候需要注意，如果对多个列同时进行范围查找的话，只有对索引最左边的那个列进行范围查找的时候才能用到B+树索引，比方说这样：

1	SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow' AND birthday > '1980-01-01';

为啥不能呢？因为上边这个查询可以分成两个部分：

通过条件name > 'Asa' AND name < 'Barlow'来对name进行范围，查找的结果可能有多条name值不同的记录，
对这些name值不同的记录继续通过birthday > '1980-01-01'条件继续过滤。

但是值得注意的是，只有name值相同的情况下才能用birthday列的值进行排序，也就是说通过name进行范围查找的结果并不是按照birthday列进行排序的，所以在搜索条件中继续以birthday列进行查找时是用不到B+树索引的。

精确匹配某一列并范围匹配另外一列

对于同一个联合索引来说，虽然对多个列都进行范围查找时只能用到最左边那个索引列，但是如果左边的列是精确查找，则右边的列可以进行范围查找，比方说这样：

1	SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday > '1980-01-01' AND birthday < '2000-12-31' AND phone_number > '15100000000';

这个查询的条件可以分为3个部分：

name = 'Ashburn'，对name列进行精确查找，当然可以使用B+树索引了。
birthday > '1980-01-01' AND birthday < '2000-12-31'，由于name列是精确查找，所以通过name = 'Ashburn'条件查找后得到的结果的name值都是相同的，它们会再按照birthday的值进行排序。所以此时对birthday列进行范围查找是可以用到B+树索引的。
phone_number > '15100000000'，通过birthday的范围查找的记录的birthday的值可能不同，所以这个条件无法再利用B+树索引了，只能遍历上一步查询得到的记录。

用于排序

我们在写查询语句的时候经常需要对查询出来的记录按照某种规则进行排序，对于不适用B+树索引进行排序的情况，我们只能把记录都加载到内存中，再用一些排序算法，比如快速排序、归并排序、吧啦吧啦排序等等在内存中对这些记录进行排序，然后再把排好序的结果集返回到客户端。但是如果ORDER BY子句里使用到了我们的索引列，就有可能省去在内存中排序的步骤，比如下边这个简单的查询语句：

1	SELECT * FROM person_info ORDER BY name, birthday, phone_number LIMIT 10;

这个查询的结果集需要先按照name值排序，如果记录的name值相同，则需要按照birthday来排序，如果birthday的值相同，则需要按照phone_number排序。大家可以回过头去看我们建立的idx_name_age_birthday索引的示意图，因为这个B+树索引本身就是排好序的，所以直接从索引中提取数据就好了。简单吧？是的，索引就是这么diao~

但是有个问题需要注意，ORDER BY的子句后边的列的顺序也必须按照索引列的顺序给出，如果给出ORDER BY phone_number, birthday, name的顺序，那也是用不了B+树索引，这种颠倒顺序就不能使用索引的原因我们上边详细说过了，这就不赘述了。同理，ORDER BY name、ORDER BY name, birthday这种匹配索引左边的列的形式可以使用部分的B+树索引。

用于分组

有时候我们为了方便统计表中的一些信息，会把表中的记录按照某些列进行分组。比如下边这个分组查询：

1	SELECT name, birthday, phone_number, COUNT(*) FROM person_info GROUP BY name, birthday, phone_number

这个查询语句相当于做了3次分组操作：

先把记录按照name值进行分组，所有name值相同的记录划分为一组。
将每个name值相同的分组里的记录再按照birthday的值进行分组，将birthday值相同的记录放到一个小分组里，所以看起来就像在一个大分组里又化分了好多小分组。
再将上一步中产生的小分组按照phone_number的值分成更小的分组，所以整体上看起来就像是先把记录分成一个大分组，然后把大分组分成若干个小分组，然后把若干个小分组再细分成更多的小小分组。

然后针对那些小小分组进行统计，比如在我们这个查询语句中就是统计每个小小分组包含的记录条数。如果没有索引的话，这个分组过程全部需要在内存里实现，而如果有了索引的话，恰巧这个分组顺序又和我们的B+树中的索引列的顺序是一致的，而我们的B+树索引又是按照索引列排好序的，这不正好么，所以可以直接使用B+树索引进行分组。

和使用B+树索引进行排序是一个道理，分组列的顺序也需要和索引列的顺序一致，也可以只使用索引列中左边的列进行分组，

索引失效问题

对于创建的多列索引（复合索引），不是使用的第一部分就不会使用索引

1
2
3

alter table student add index my_index(name, age)   // name左边的列， age 右边的列                                                              
select * from student where name = 'aaa'     // 会用到索引
select * from student where age = 18          //  不会使用索引

对于使用 like 查询，查询如果是 ‘%aaa’ 不会使用索引，而 ‘aaa%’ 会使用到索引。

1 2	select * from student where name like 'aaa%' // 会用到索引 select * from student where name like '%aaa' 或者 '_aaa' // 不会使用索引

如果条件中有 or，有条件没有使用索引，即使其中有条件带索引也不会使用，换言之，就是要求使用的所有字段，都必须单独使用时能使用索引。
如果列类型是字符串，那么一定要在条件中使用引号引用起来，否则不使用索引。
如果MySQL认为全表扫描要比使用索引快，则不使用索引。如：表里只有一条数据。

事务的隔离级别、锁

深入理解乐观锁与悲观锁

事务的隔离级别

锁

在关系数据库管理系统里，悲观并发控制（又名“悲观锁”，Pessimistic Concurrency Control，缩写“PCC”）是一种并发控制的方法。它可以阻止一个事务以影响其他用户的方式来修改数据。如果一个事务执行的操作都某行数据应用了锁，那只有当这个事务把锁释放，其他事务才能够执行与该锁冲突的操作。
悲观并发控制主要用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。

在对任意记录进行修改前，先尝试为该记录加上排他锁（exclusive locking）。如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常。具体响应方式由开发者根据实际需要决定。如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁了。
其间如果有其他对该记录做修改或加排他锁的操作，都会等待我们解锁或直接抛出异常。

上面我们提到，使用select…for update会把数据给锁住，不过我们需要注意一些锁的级别，MySQL InnoDB默认行级锁。行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁把整张表锁住，这点需要注意。

乐观锁（ Optimistic Locking ）相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。只能防止脏读后数据的提交，不能解决脏读。
相对于悲观锁，在对数据库进行处理的时候，乐观锁并不会使用数据库提供的锁机制。一般的实现乐观锁的方式就是记录数据版本。

行级锁
开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

表级锁
开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。

页级锁
开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

在不通过索引条件查询的时候,InnoDB 确实使用的是表锁,而不是行锁。
MyISAM中是不会产生死锁的，因为MyISAM总是一次性获得所需的全部锁，要么全部满足，要么全部等待。而在InnoDB中，锁是逐步获得的，就造成了死锁的可能。

行锁
TODO….
gap锁

ANSI/ISO SQL定义的标准隔离级别有四种，从高到底依次为：可序列化(Serializable)、可重复读(Repeatable reads)、提交读(Read committed)、未提交读(Read uncommitted)。

未提交读(Read uncommitted)

事务在读数据的时候并未对数据加锁。
事务在修改数据的时候只对数据增加行级共享锁。
所以，未提交读会导致脏读
可以读到其他事务未提交的结果

提交读(Read committed)

提交读(READ COMMITTED)也可以翻译成读已提交，通过名字也可以分析出，在一个事务修改数据过程中，如果事务还没提交，其他事务不能读该数据。

事务对当前被读取的数据加行级共享锁（当读到时才加锁），一旦读完该行，立即释放该行级共享锁；
事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加行级排他锁，直到事务结束才释放。
简而言之，提交读这种隔离级别保证了读到的任何数据都是提交的数据，避免了脏读(dirty reads)。但是不保证事务重新读的时候能读到相同的数据，因为在每次数据读完之后其他事务可以修改刚才读到的数据。

| 事务一 | 事务二 |
| /* Query 1 */
SELECT * FROM users WHERE id = 1; | |
| | /* Query 2 */
UPDATE users SET age = 21 WHERE id = 1;
COMMIT;
/* in multiversion concurrency control, or lock-based READ COMMITTED */ |
| /* Query 1 */
SELECT * FROM users WHERE id = 1;
COMMIT;
/*lock-based REPEATABLE READ */ |

可重复读(Repeatable reads)

事务在读取某数据的瞬间（就是开始读取的瞬间），必须先对其加行级共享锁，直到事务结束才释放；
事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加行级排他锁，直到事务结束才释放。

事务1在读取某行记录的整个过程中，事务2都可以对该行记录进行读取（因为事务一对该行记录增加行级共享锁的情况下，事务二同样可以对该数据增加共享锁来读数据。）。

事务1在读取某行记录的整个过程中，事务2都不能修改该行数据（事务一在读取的整个过程会对数据增加共享锁，直到事务提交才会释放锁，所以整个过程中，任何其他事务都不能对该行数据增加排他锁。所以，可重复读能够解决不可重复读的读现象）

事务1更新某行记录时，事务2不能对这行记录做更新，直到事务1结束。（事务一在更新数据的时候，会对该行数据增加排他锁，知道事务结束才会释放锁，所以，在事务二没有提交之前，事务一都能不对数据增加共享锁进行数据的读取。所以，提交读可以解决脏读的现象）

可序列化(Serializable)

事务在读取数据时，必须先对其加表级共享锁，直到事务结束才释放；
事务在更新数据时，必须先对其加表级排他锁，直到事务结束才释放。

脏读

脏读又称无效数据的读出，是指在数据库访问中，事务T1将某一值修改，然后事务T2读取该值，此后T1因为某种原因撤销对该值的修改，这就导致了T2所读取到的数据是无效的。

不可重复读

一种更易理解的说法是：在一个事务内，多次读同一个数据。在这个事务还没有结束时，另一个事务也访问该同一数据。那么，在第一个事务的两次读数据之间。由于第二个事务的修改，那么第一个事务读到的数据可能不一样，这样就发生了在一个事务内两次读到的数据是不一样的，因此称为不可重复读，即原始读取不可重复。

幻读

幻读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，比如这种修改涉及到表中的“全部数据行”。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入“一行新数据”。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样.一般解决幻读的方法是增加范围锁RangeS，锁定检锁范围为只读，这样就避免了幻读。　　
幻读(phantom read)”是不可重复读(Non-repeatable reads)的一种特殊场景：当事务没有获取范围锁的情况下执行SELECT … WHERE操作可能会发生“幻影读(phantom read)”。

| 事务一 | 事务二 |
| /* Query 1 */
SELECT * FROM users WHERE age BETWEEN 10 AND 30; | |
| | /* Query 2 */
INSERT INTO users VALUES ( 3, 'Bob', 27 );
COMMIT; |
| /* Query 1 */
SELECT * FROM users WHERE age BETWEEN 10 AND 30; |

1.事务一的第一次查询条件是age BETWEEN 10 AND 30;如果这是有十条记录符合条件。这时，他会给符合条件的这十条记录增加行级共享锁。任何其他事务无法更改这十条记录。

2.事务二执行一条sql语句，语句的内容是向表中插入一条数据。因为此时没有任何事务对表增加表级锁，所以，该操作可以顺利执行。

3.事务一再次执行SELECT * FROM users WHERE age BETWEEN 10 AND 30;时，结果返回的记录变成了十一条，比刚刚增加了一条，增加的这条正是事务二刚刚插入的那条。

所以，事务一的两次范围查询结果并不相同。这也就是我们提到的幻读。

当前读、快照读

DB_TRX_ID ，，DB_ROLL_PTR ，， DB_ROW_ID字段

undo日志

函数依赖

函数依赖定义为：设R(U)是属性集U上的关系模式。X,Y是U的子集，若对于R(U)的任意一个可能的关系r，r中不可能存在两个元组在X上的属性值相等，而在Y上的属性值不等，则成X函数确定Y或者Y函数依赖与X，记作：X→Y。

平凡的函数依赖：如果X→Y，但Y∈X，则称X→Y是平凡的函数依赖

非平凡的函数依赖：如果X→Y，但Y∉X，则称X→Y是非平凡的函数依赖。通常情况下总是讨论非平凡的函数依赖

完全函数依赖：在R(U)中，如果X→Y，并且对于x的任何一个真子集X’，都有X´不能决定Y，则称Y对X完全函数依赖，记作：X-f->Y (f即 full)

部分函数依赖：在R(U)中，如果X→Y，但Y不完全函数依赖于X，则称Y对X部分函数依赖，记作：X-p->Y (p即part) 部分函数依赖也称为局部函数依赖

传递依赖：在R(U,F)中，如果X→Y，Y∉X，Y→Z，Y不完全函数依赖于X，则称Z对X传递依赖

无损连接

PreparedStatement

PreparedStatement表示预编译得SQL语句的对象，也就是说SQL语句被预编译并存储在PreparedStatement对象中，然后可以使用此对象多次高效地执行改语句。
Statement是将完整的需要执行的SQL语句通过执行平台传输过去，而PreparedStatement则是在需传输的数据方面用？号表示。

数据库系统会对sql语句进行预编译处理（如果JDBC驱动支持的话），预处理语句将被预先编译好，这条预编译的sql查询语句能在将来的查询中重用，这样一来，它比Statement对象生成的查询速度更快。

SQL 知识

for update

select * from … for update
需要获取这个表的锁，要在其他事务提交后才能查询。

if，ifnull

把salary表中的女改成男，男改成女:

update salary set sex = if( sex = ‘男’,’女’,’男’);

if(true,a,b), if(false,a,b) 这个就是第一个如果是true，就等于a，false就等于b，有点像三元表达式

MySQL IFNULL函数是MySQL控制流函数之一，它接受两个参数，如果不是NULL，则返回第一个参数。否则，IFNULL函数返回第二个参数。

两个参数可以是文字值或表达式。

以下说明了IFNULL函数的语法：

IFNULL(expression_1,expression_2);
SQL
如果expression_1不为NULL，则IFNULL函数返回expression_1; 否则返回expression_2的结果。

IFNULL函数根据使用的上下文返回字符串或数字。

limit ，offset

① select * from table limit 2,1;
//含义是跳过2条取出1条数据，limit后面是从第2条开始读，读取1条信息，即读取第3条数据

② select * from table limit 2 offset 1;
//含义是从第1条（不包括）数据开始取出2条数据，limit后面跟的是2条数据，offset后面是从第1条开始读取，即读取第2,3条

distinct

去掉重复值，如果有多个列，会应用到每个列

-- 统计A公司的男女人数 
select count(distinct id) from table_1 
where company = 'A' 
group by sex 
-- 统计各公司的男性平均年龄，并且仅保留平均年龄30岁以上的公司 
select company, avg(age) from table_1 
where sex = 'M' 
group by company 
having avg(age)>30;

case cast

将数值型的变量转化为分类型的变量？ —— case when 条件函数

-- 收入区间分组
select id,
(case when CAST(salary as float)<50000 Then '0-5万'
when CAST(salary as float)>=50000 and CAST(salary as float)<100000 then '5-10万'
when CAST(salary as float) >=100000 and CAST(salary as float)<200000 then '10-20万'
when CAST(salary as float)>200000 then '20万以上'
else NULL end 
from table_1;

case 函数的格式为（case when 条件1 then value1 else null end）, 其中else 可以省，但是end不可以省。

在这个例子里也穿插了一个CAST的用法，它常用于string/int/double型的转换。

字符串

concat( A, B…)返回将A和B按顺序连接在一起的字符串，如：concat(‘foo’, ‘bar’) 返回’foobar’
1
2
3
4
select concat('www','.iteblog','.com') from
iteblog;

--得到 www.iteblog.com
split(str, regex)用于将string类型数据按regex提取，分隔后转换为array。

-- 以","为分隔符分割字符串，并转化为array
Select split("1,2,3",",")as value_array from table_1;

-- 结合array index,将原始字符串分割为3列
select value_array[0],value_array[1],value_array[2] from 
(select  split("1,2,3",",")as value_array from table_1 )t

substr（str,0,len) 截取字符串从0位开始的长度为len个字符。

select substr('abcde',3,2) from
iteblog;

-- 得到cd

on条件与where条件的区别

综上得出，ON后面对于左表的过滤条件，在最后结果行数中会被忽略，并不会先去过滤左表数据再连接查询，但是ON后的右表条件会先过滤右表数据再连接左表进行查询。

连接查询时，都是用符合ON后的左右表的过滤条件的数据进行连接查询，只有符合左右表过滤条件的数据才能正确匹配，剩下的左表数据会正常出现在结果集中，但匹配的右表数据是NULL。因此对于左表的过滤条件切记要放到Where后，对于右表的过滤条件要看情况了。如果需要先过滤右表数据就把条件放到ON后面即可。

on、where、having的区别

on、where、having这三个都可以加条件的子句中，on是最先执行，where次之，having最后。有时候如果这先后顺序不影响中间结果的话，那最终结果是相同的。但因为on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的。根据上面的分析，可以知道where也应该比having快点的，因为它过滤数据后才进行sum，所以having是最慢的。但也不是说having没用，因为有时在步骤3还没出来都不知道那个记录才符合要求时，就要用having了。在两个表联接时才用on的，所以在一个表的时候，就剩下where跟having比较了。在这单表查询统计的情况下，如果要过滤的条件没有涉及到要计算字段，那它们的结果是一样的，只是where可以使用rushmore技术，而having就不能，在速度上后者要慢。如果要涉及到计算的字段，就表示在没计算之前，这个字段的值是不确定的，根据上篇写的工作流程，where的作用时间是在计算之前就完成的，而having就是在计算后才起作用的，所以在这种情况下，两者的结果会不同。在多表联接查询时，on比where更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where进行过滤，然后再计算，计算完后再由having进行过滤。由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什么时候起作用，然后再决定放在那里

JOIN

Id_P	LastName	FirstName	Address	City
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

Id_O OrderNo Id_P

1     77895 3
2     44678 3
3     22456 1
4     24562 1
5     34764 65

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM PersonsINNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName

结果：

LastName FirstName OrderNo
Adams John 22456
Adams John 24562
Carter Thomas 77895
Carter Thomas 44678

在表中存在至少一个匹配时（来自两个表的所有字段都不为空），INNER JOIN 关键字返回行。与 JOIN 是相同的
LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。
LEFT OUTER JOIN。

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

LastName	FirstName	OrderNo
Adams	John	22456
Adams	John	24562
Carter	Thomas	77895
Carter	Thomas	44678
Bush	George

RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行，即使在左表 (table_name1) 中没有匹配的行。只要其中某个表存在匹配，FULL JOIN 关键字就会返回行。
LastName FirstName OrderNo
Adams John 22456
Adams John 24562
Carter Thomas 77895
Carter Thomas 44678
Bush George 34764

AVG()、COUNT()、SUM()等函数对NULL值处理

AVE()忽略NULL值，而不是将其作为“0”参与计算

COUNT(*)对表中行数进行计数，不管是否有NULL

COUNT(字段名)对特定列有数据的行进行计数，忽略NULL值

SUM()可以对单个列求和，也可以对多个列运算后求和。忽略NULL值，且当对多个列运算求和时，如果运算的列中任意一列的值为NULL，则忽略这行的记录。
例如： SUM(A+B+C)，A、B、C 为三列，如果某行记录中A列值为NULL，则不统计这行。

max() min()忽略null、

HAVING

过滤分组
group by 分组之后，可以用having过滤，。where是在分组之前过滤。

inner join…on…

case when

 --简单case函数
case sex
         when '1' then '男'
         when '2' then '女'
else '其他' end
--case搜索函数
case when sex = '1' then '男'
         when sex = '2' then '女'
else '其他' end

这两种方式，可以实现相同的功能。简单case函数的写法相对比较简洁，但是和case搜索函数相比，功能方面会有些限制，比如写判断式。
还有一个需要注意的问题，case函数只返回第一个符合条件的值，剩下的case部分将会被自动忽略。
–比如说，下面这段sql，你永远无法得到“第二类”这个结果

1
2
3

case when col_1 in ( 'a', 'b') then '第一类'
         when col_1 in ('a')       then '第二类'
else'其他' end

Innodb与Myisam引擎

1. 区别：

（1）事务处理：
MyISAM是非事务安全型的，而InnoDB是事务安全型的（支持事务处理等高级处理）；
（2）锁机制不同：
MyISAM是表级锁，而InnoDB是行级锁；
（3）select ,update ,insert ,delete 操作：
MyISAM：如果执行大量的SELECT，MyISAM是更好的选择
InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表
（4）查询表的行数不同：
MyISAM：select count() from table,MyISAM只要简单的读出保存好的行数，注意的是，当count()语句包含 where条件时，两种表的操作是一样的
InnoDB ： InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行
（5）外键支持：
mysiam表不支持外键，而InnoDB支持

2. 为什么MyISAM会比Innodb 的查询速度快。

INNODB在做SELECT的时候，要维护的东西比MYISAM引擎多很多；
1）数据块，INNODB要缓存，MYISAM只缓存索引块，这中间还有换进换出的减少；
2）innodb寻址要映射到块，再到行，MYISAM 记录的直接是文件的OFFSET，定位比INNODB要快
3）INNODB还需要维护MVCC一致；虽然你的场景没有，但他还是需要去检查和维护
MVCC ( Multi-Version Concurrency Control )多版本并发控制

3. 应用场景

MyISAM适合：(1)做很多count 的计算；(2)插入不频繁，查询非常频繁；(3)没有事务。
InnoDB适合：(1)可靠性要求比较高，或者要求事务；(2)表更新和查询都相当的频繁，并且行锁定的机会比较大的情况。

MVCC

【mysql】关于innodb中MVCC的一些理解
通过加锁，让所有的读者等待写者工作完成，但是这样效率会很差。MVCC 使用了一种不同的手段，每个连接到数据库的读者，在某个瞬间看到的是数据库的一个快照，写者写操作造成的变化在写操作完成之前（或者数据库事务提交之前）对于其他的读者来说是不可见的。

innodb存储的最基本row中包含一些额外的存储信息 DATA_TRX_ID，DATA_ROLL_PTR，DB_ROW_ID，DELETE BIT

6字节的DATA_TRX_ID 标记了最新更新这条行记录的transaction id，每处理一个事务，其值自动+1
7字节的DATA_ROLL_PTR 指向当前记录项的rollback segment的undo log记录，找之前版本的数据就是通过这个指针
6字节的DB_ROW_ID，当由innodb自动产生聚集索引时，聚集索引包括这个DB_ROW_ID的值，否则聚集索引中不包括这个值.，这个用于索引当中
DELETE BIT位用于标识该记录是否被删除，这里的不是真正的删除数据，而是标志出来的删除。真正意义的删除是在commit的时候

InnoDB的MVCC,是通过在每行记录后面保存两个隐藏的列来实现的,这两个列，分别保存了这个行的创建时间，一个保存的是行的删除时间。这里存储的并不是实际的时间值,而是系统版本号(可以理解为事务的ID)，每开始一个新的事务，系统版本号就会自动递增，事务开始时刻的系统版本号会作为事务的ID.

SELECT
InnoDB会根据以下两个条件检查每行记录: a.InnoDB只会查找版本早于当前事务版本的数据行(也就是,行的系统版本号小于或等于事务的系统版本号)，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的. b.行的删除版本要么未定义,要么大于当前事务版本号,这可以确保事务读取到的行，在事务开始之前未被删除.
只有a,b同时满足的记录，才能返回作为查询结果.
DELETE
InnoDB会为删除的每一行保存当前系统的版本号(事务的ID)作为删除标识.

innodb索引和数据在一起

myisam不在一起

这种额外的记录所带来的结果就是对于大多数查询来说根本就不需要获得一个锁。他们只是简单地以最快的速度来读取数据，确保只选择符合条件的行。这个方案的缺点在于存储引擎必须为每一行存储更多的数据，做更多的检查工作，处理更多的善后操作。

MVCC只工作在REPEATABLE READ和READ COMMITED隔离级别下。READ UNCOMMITED不是MVCC兼容的，因为查询不能找到适合他们事务版本的行版本；它们每次都只能读到最新的版本。
SERIABLABLE也不与MVCC兼容，因为读操作会锁定他们返回的每一行数据。

存储程序

原文链接

有时候为了完成一个常用的功能需要执行许多条语句，每次都在客户端里一条一条的去输入这么多语句是很烦的，我们希望有一种批处理的形式，让我们以很简单的方式一次性的执行完这些语句，MySQL中的存储程序本质上封装了一些可执行的语句，然后给用户提供一种简单的调用方式来执行这些语句，根据调用方式的不同，我们可以把存储程序分为存储例程、触发器和事件这几种类型。其中，存储例程又可以被细分为存储函数和存储过程。我们画个图表示一下：

自定义变量

变量是和常量相对的，一般的程序语言都提供对变量的支持，MySQL中对我们自定义的变量的命名有个要求，那就是变量名称前必须加一个@符号。我们自定义变量的值的类型可以是任意MySQL支持的类型，比方说我们来自定义一个变量：

mysql> //多种设置方式
SET @a = 1;
SET @b = @a;
SET @a = (SELECT m1 FROM t1 LIMIT 1);
SELECT m1, n1 FROM t1 LIMIT 1 INTO @a, @b

SELECT @a

可以设置结束分隔符

mysql> delimiter $
mysql> SELECT * FROM t1 LIMIT 1;
    -> SELECT * FROM t2 LIMIT 1;
    -> SELECT * FROM t3 LIMIT 1;
    -> $
    用完改回去
    delimiter ;

存储例程简介

存储例程是存储程序的一种类型，本质上也是封装了一些可执行的语句，只不过它的调用方式是：需要手动去调用！存储例程又可以分为存储函数和存储过程，下边我们详细唠叨这两个家伙。

存储函数

存储函数其实就是一种函数，只不过在这个函数里可以执行命令语句而已。函数的概念大家都应该不陌生，它可以把处理某个问题的过程封装起来，之后我们直接调用函数就可以去解决同样的问题了，简单方便又环保。MySQL中定义存储函数的语句如下：

CREATE FUNCTION avg_score(s VARCHAR(100))
RETURNS DOUBLE
BEGIN

（declare count  integer有需要的话在这里定义变量）
    RETURN (SELECT AVG(score) FROM student_score WHERE subject = s);
END

从这里我们可以看出，定义一个存储函数需要指定函数名称、参数列表、返回值类型以及函数体内容，如果该函数不需要参数，那参数列表可以被省略，函数体内容可以包括一条或多条语句，每条语句都要以分号;结尾。

存储过程

存储函数侧重于执行这些语句并返回一个值，而存储过程更侧重于单纯的去执行这些语句。存储函数执行语句并返回一个值，所以常用在表达式中。存储过程偏向于调用那些语句，并不能用在表达式中，我们需要显式的使用CALL语句来调用一个存储过程：

CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
    需要执行的语句
END

CALL 存储过程([参数列表]);

比存储函数牛逼的一点是，存储过程在定义参数的时候可以选择参数类型（注意！不是数据类型）,如果我们不写明参数类型的话，该参数的类型默认是IN

参数类型 参数名 数据类型

mysql> CREATE PROCEDURE p_out (
->     OUT a INT
-> )
-> BEGIN
->     SELECT a;//由于a是out，所以外面传进来的值里面读不到，但是可以改变后输出
->     SET a = 123;
-> END

存储过程和存储函数的不同点

存储函数在定义时需要显式用RETURNS语句标明返回的数据类型，而且在函数体中必须使用RETURN语句来显式指定返回的值，存储过程不需要。
存储函数的参数类型只能是IN，而存储过程支持IN、OUT、INOUT三种参数类型。
存储函数只能返回一个值，而存储过程可以通过设置多个OUT类型的参数来返回多个结果。
存储函数执行过程中产生的结果集并不会被显示到客户端，而存储过程执行过程中产生的结果集会被显示到客户端。
存储函数的调用直接使用在表达式中，而存储过程只能通过CALL语句来显式调用。

游标简介

截止到现在为止，我们只能使用INTO语句将一行记录的各个列值赋值到多个变量里，比如在上边的get_score_data存储过程里这么写：

1	SELECT MAX(score), MIN(score), AVG(score) FROM student_score WHERE subject = s INTO max_score, min_score, avg_score;

但是如果结果集中有多条记录的话，我们就无法把它们赋值给某些变量了～所以为了方便我们去访问这些有多条记录的结果集，MySQL中引入了游标的概念。

我们下边以对t1表的查询为例来介绍一下游标，比如我们有这样一个查询：

mysql> SELECT m1, n1 FROM t1;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+4 rows in set (0.00 sec)
mysql>

这个SELECT m1, n1 FROM t1查询语句对应的结果集有4条记录，这个游标其实是用来标记结果集中我们正在访问的某一行记录，初始状态下它标记结果集中的第一条记录，就像这样：

我们可以根据这个游标取出它对应记录的信息，随后再移动游标，让它执向别的记录。游标既可以用在存储函数中，也可以用在存储过程中，我们下边以存储过程为例来说明游标的使用方式，它的使用大致分成这么四个步骤：

创建游标
打开游标
通过游标访问记录
关闭游标

CREATE PROCEDURE cursor_demo()
BEGIN
    -- 声明变量
    DECLARE m_value INT;
    DECLARE n_value CHAR(1);
    DECLARE not_done INT DEFAULT 1;

    -- 声明游标
    DECLARE t1_record_cursor CURSOR FOR SELECT m1, n1 FROM t1; 

    -- 在游标遍历完记录的时候将变量 not_done 的值设置为 0，并且继续执行后边的语句
    -- CONTINUE表示在`FETCH`语句获取不到记录的时候仍然会执行之后存储过程的语句，`EXIT`表示在
    -- FETCH`语句获取不到记录的时候仍然不会执行之后存储过程的语句
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_done = 0;  

    -- 使用游标遍历
    OPEN t1_record_cursor;

    WHILE not_done = 1 DO
        FETCH t1_record_cursor INTO m_value, n_value;
        SELECT m_value, n_value, not_done;
    END WHILE;

    CLOSE t1_record_cursor;
END

触发器

存储函数与存储过程都是需要我们手动调用的，如果想在执行某条语句之前或者之后自动去调用另外一些语句，比如下边的这些场景：

在向t1表插入数据之前对自动对数据进行校验，要求m1列的值必须在1~10之间，校验规则如下：
如果插入的记录的m1列的值小于1，则按1插入。
如果m1列的值大于10，则按10插入。
在向t1表中插入记录之后自动把这条记录插入到t2表。

那我们就需要考虑一下触发器了。我们看一下定义触发器的语句：

CREATE TRIGGER 触发器名
{BEFORE|AFTER}          
{INSERT|DELETE|UPDATE}
ON 表名
FOR EACH ROW 
BEGIN    
  触发器内容
END

需要注意的是，由大括号{}包裹并且内部用竖线|分隔的语句表示必须在给定的选项中选取一个值，比如{BEFORE|AFTER}表示必须在BEFORE、AFTER这两个之间选取一个。
因为触发器会对某个语句影响的所有记录依次调用我们自定义的触发器内容，所以我们需要一种访问该记录中的内容的方式，MySQL提供了NEW和OLD两个单词来分别代表新记录和旧记录，它们在不同操作中的含义不同：

对于INSERT语句设置的触发器来说，NEW代表准备插入的记录，不能使用OLD。
对于DELETE语句设置的触发器来说，OLD代表删除前的记录，不能使用NEW。
对于UPDATE语句设置的触发器来说，NEW代表修改后的记录，OLD代表修改前的记录。

触发器内容中不能有输出结果集的语句

事件

如果我们想指定某些语句在某个时间点或者每隔一个时间段执行一次的话，可以选择创建一个事件，语法就是这样：SET GLOBAL event_scheduler = ON;

CREATE EVENT 事件名
ON SCHEDULE{AT 某个确定的时间点 | EVERY 期望的时间间隔 [STARTS datetime][END datetime]}
-- AT DATE_ADD(NOW(), INTERVAL 2 DAY)
-- AT '2018-03-10 15:48:54'
-- EVERY 1 HOUR STARTS '2018-03-10 15:48:54' ENDS '2018-03-12 15:48:54'
DO
BEGIN
  具体的语句
END

MyCAT

https://www.jianshu.com/p/21b1e133dd9b
分布式数据库系统中间层

应用场景：需要读写分离，需要分库分表，多租户，数据统计系统，HBASE的一种替代方案

支持全局表
支持ER的分片策略
支持一致性hash分片

使用MySQL客户端管理mycat
动态加载配置文件：reload @@config;
查看数据节点：show @@datanode;
查看后端数据库：show @@datasource;

复制（replication）和集群（cluster）/读写分离

Replication

Replication的思想是将数据在集群的多个节点同步、备份，以提高集群数据的可用性（HA）；Mysql使用Replication架构来实现上述目的，同时可以提升了集群整体的并发能力。。

Replication具有如下优点：

扩展：将负载分布在多个slaves上以提高性能，所有的writes以及事务中的read操作都将有master处理，其他reads将转发给slaves；对于“读写比”较高的应用，replication可以通过增加slaves节点来提高并发能力；因为write只能在master上提交，因此架构扩展对提升write并发能力并不明显，对于writes密集性应用我们应该考虑其他架构。
数据安全：slave可以中断自己的replication进程，这不会打断master上的数据请求，所以可以在slave上运行backup服务，定期全量backup是保护数据的手段之一。（如果在master上执行backup，需要让master处于readonly状态，这也意味这所有的write请求需要阻塞）。
分析：数据在master上创建，那么数据分析可以在slave上进行，这将不会影响master的性能。利用mysql做数据分析（或者数据分析平台的源数据），通常都是将某个slave作为数据输入端。
远距数据分布：如果master的物理位置较远，你可以在临近需求的地方部署slaves，以便就近使用数据，而不需要总是访问远端的master，这在数据分析、数据备份与容灾等方面有很大帮助。

Sharding和Replication

replication机制的优缺点

优点：负载高时可以通过replication机制来提高读写的吞吐和性能。
缺点：首先它的有效很依赖于读操作的比例，Master往往会成为瓶颈所在，写操作需要顺序排队来执行，过载的话Master首先扛不住，Slaves的数据同步的延迟也可能比较大，而且会大大耗费CPU的计算能力，因为write操作在Master上执行以后还是需要在每台slave机器上都跑一次。

sharding技术的优缺点

优点：sharding技术可以弥补replication机制的缺点。因为sharding可以很好的扩展，我们知道每台机器无论配置多么好它都有自身的物理上限，所以当我们应用已经能触及或远远超出单台机器的某个上限的时候，我们惟有寻找别的机器的帮助或者继续升级的我们的硬件，但常见的方案还是横向扩展, 通过添加更多的机器来共同承担压力。我们还得考虑当我们的业务逻辑不断增长，我们的机器能不能通过线性增长就能满足需求？Sharding可以轻松的将计算，存储，I/O并行分发到多台机器上，这样可以充分利用多台机器各种处理能力，同时可以避免单点失败，提供系统的可用性，进行很好的错误隔离。

Mysql主从复制的原理

主从复制通过三个过程实现，其一个过程发生在主服务器上，另外两个过程发生在从服务器上。具体情况如下：

主服务器将用户对数据库的写操作以二进制格式保存到Binary Log（二进制日志）文件中，然后由Binlog Dump线程将二进制日志文件传输给从服务器。

从服务器通过一个 I/O 线程将主服务器的二进制日志文件中的写操作复制到一个叫 Relay Log 的中继日志文件中。

从服务器通过另一个 SQL 线程将 Relay Log 中继日志文件中的写操作依次在本地执行，从而实现主从服务器之间的数据的同步。

BinLog Dump线程

该线程运行在主服务器上，主要工作是把 Binary Log 二进制日志文件的数据发送给从服务器。

使用SHOW PROCESSLIST语句，可查看该线程是否正在运行。

I/O线程

从服务器执行 START SLAVE 语句后，会创建一个 I/O 线程。此线程运行在从服务器上，与主服务器建立连接，然后向主服务器发出同步请求。之后，I/O 线程将主服务器发送的写操作复制到本地 Relay Log 日志文件中。

使用SHOW SLAVE STATUS语句，可查看 I/O 线程状态。

SQL线程

该线程运行在从服务器上，主要工作是读取 Relay Log 日志文件中的更新操作，并将这些操作依次执行，从而使主从服务器的数据保持同步。

复制级别

基于语句的复制：在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。一旦发现没法精确复制时，会自动选着基于行的复制。
基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍. 从mysql5.0开始支持
混合类型的复制: 默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。

Replication的三种常用架构

Master - Slaves

在实际应用场景中，MySQL复制90%以上都是一个Master复制到一个或者多个Slave的架构模式，主要用于读压力比较大的应用的数据库端廉价扩展解决方案。因为只要Master和Slave的压力不是太大（尤其是Slave端压力）的话，异步复制的延时一般都很少很少。尤其是自从Slave端的复制方式改成两个线程处理之后，更是减小了Slave端的延时问题。而带来的效益是，对于数据实时性要求不是特别严格的应用，只需要通过廉价的pcserver来扩展Slave的数量，将读压力分散到多台Slave的机器上面，即可通过分散单台数据库服务器的读压力来解决数据库端的读性能瓶颈，毕竟在大多数数据库应用系统中的读压力还是要比写压力大很多。这在很大程度上解决了目前很多中小型网站的数据库压力瓶颈问题，甚至有些大型网站也在使用类似方案解决数据库瓶颈。

Master - Master

有些时候，简单的从一个MySQL复制到另外一个MySQL的基本Replication架构，可能还会需要在一些特定的场景下进行Master的切换。如在Master端需要进行一些特别的维护操作的时候，可能需要停MySQL的服务。这时候，为了尽可能减少应用系统写服务的停机时间，最佳的做法就是将我们的Slave节点切换成Master来提供写入的服务。

但是这样一来，我们原来Master节点的数据就会和实际的数据不一致了。为了解决这个问题，我们可以通过搭建DualMaster环境来避免很多的问题。何谓DualMaster环境？实际上就是两个MySQLServer互相将对方作为自己的Master，自己作为对方的Slave来进行复制。这样，任何一方所做的变更，都会通过复制应用到另外一方的数据库中。

可能有些读者朋友会有一个担心，这样搭建复制环境之后，难道不会造成两台MySQL之间的循环复制么？实际上MySQL自己早就想到了这一点，所以在MySQL的BinaryLog中记录了当前MySQL的server-id，而且这个参数也是我们搭建MySQLReplication的时候必须明确指定，而且Master和Slave的server-id参数值比需要不一致才能使MySQLReplication搭建成功。一旦有了server-id的值之后，MySQL就很容易判断某个变更是从哪一个MySQLServer最初产生的，所以就很容易避免出现循环复制的情况。而且，如果我们不打开记录Slave的BinaryLog的选项（–log-slave-update）的时候，MySQL根本就不会记录复制过程中的变更到BinaryLog中，就更不用担心可能会出现循环复制的情形了。下如将更清晰的展示DualMaster复制架构组成：

通过DualMaster复制架构，我们不仅能够避免因为正常的常规维护操作需要的停机所带来的重新搭建Replication环境的操作，因为我们任何一端都记录了自己当前复制到对方的什么位置了，当系统起来之后，就会自动开始从之前的位置重新开始复制，而不需要人为去进行任何干预，大大节省了维护成本。

不仅仅如此，DualMaster复制架构和一些第三方的HA管理软件结合，还可以在我们当前正在使用的Master出现异常无法提供服务之后，非常迅速的自动切换另外一端来提供相应的服务，减少异常情况下带来的停机时间，并且完全不需要人工干预。

当然，我们搭建成一个DualMaster环境，并不是为了让两端都提供写的服务。在正常情况下，我们都只会将其中一端开启写服务，另外一端仅仅只是提供读服务，或者完全不提供任何服务，仅仅只是作为一个备用的机器存在。为什么我们一般都只开启其中的一端来提供写服务呢？主要还是为了避免数据的冲突，防止造成数据的不一致性。因为即使在两边执行的修改有先后顺序，但由于Replication是异步的实现机制（CAP），同样会导致即使晚做的修改也可能会被早做的修改所覆盖。

Master –Slaves - Slaves

在有些应用场景中，可能读写压力差别比较大，读压力特别的大，一个Master可能需要上10台甚至更多的Slave才能够支撑注读的压力。这时候，Master就会比较吃力了，因为仅仅连上来的SlaveIO线程就比较多了，这样写的压力稍微大一点的时候，Master端因为复制就会消耗较多的资源，很容易造成复制的延时。

遇到这种情况如何解决呢？这时候我们就可以利用MySQL可以在Slave端记录复制所产生变更的BinaryLog信息的功能，也就是打开—log-slave-update选项。然后，通过二级（或者是更多级别）复制来减少Master端因为复制所带来的压力。也就是说，我们首先通过少数几台MySQL从Master来进行复制，这几台机器我们姑且称之为第一级Slave集群，然后其他的Slave再从第一级Slave集群来进行复制。从第一级Slave进行复制的Slave，我称之为第二级Slave集群。如果有需要，我们可以继续往下增加更多层次的复制。这样，我们很容易就控制了每一台MySQL上面所附属Slave的数量。这种架构我称之为Master-Slaves-Slaves架构

这种多层级联复制的架构，很容易就解决了Master端因为附属Slave太多而成为瓶颈的风险。下图展示了多层级联复制的Replication架构。

读写分离

读写分离，基本的原理是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。

读写分离的好处：

增加冗余
增加了机器的处理能力
对于读操作为主的应用，使用读写分离是最好的场景，因为可以确保写的服务器压力更小，而读又可以接受点时间上的延迟。

读写分离在实现上一般采用复制的方式，读在Slave端，写和事务在Master端，按照其实现可分为如下两种大类：

程序修改mysql操作类

在数据库操作时直接指定读写库的位置，这种方式

优点：直接和数据库通信，简单快捷的读写分离和随机的方式实现的负载均衡，权限独立分配
缺点：数据段和程序的耦合度太高，自己维护更新，增减服务器在代码处理。

数据库代理

使用mysql官方（mysql proxy）或者第三方数据库代理（如mycat）将数据库连接抽象和屏蔽，

优点：直接实现读写分离和负载均衡，不用修改代码，
缺点：增加额外耗时和性能损耗。

Replication的常用方案

master-master架构

两台服务器装mysql，各自作为对方的从机接受对方发来的数据，做到数据的同步备份，感觉和master-slave基本实现原理是一样的。这样保证了数据的一致性，如何保证其中一台服务器故障，自动切换到另外的一个master上呢，使用MMM(MySQL Master-Master Replication Manager)来管理。

heartbeat+drbd+mysql主从复制

基本原理与1相似，这里需要做一个master库的冗余备份，使用drbd来保证不同服务器中两个master库的数据一致性。利用heartbeat来完成其中一台服务器发生故障后的自动切换。结构如下图：

Mysql + keepalived 实现双主热备读写分离

keepalived主要用于实现故障切换和热备，作为mysql的补充。

NDB Cluster

MySQL NDB Cluster是一个适用于分布式计算环境的高可用性、高冗余版本的MySQL。
NDB集群由一组计算机组成，称为主机，每个计算机运行一个或多个进程。这些进程称为节点，可能包括MySQL服务器（用于访问NDB数据）、数据节点（用于存储数据）、一个或多个管理服务器，以及可能的其他专门的数据访问程序。在NDB集群中这些组件的关系如下所示：

所有这些程序一起工作来形成一个NDB集群。当数据被NDB存储引擎存储时，表（和表数据）存储在数据节点中。这样的表可以直接从集群中的所有MySQL服务器（SQL节点）访问。因此，在一个将数据存储在集群中的工资单应用程序中，如果一个应用程序更新了雇员的工资，那么查询这些数据的所有其他MySQL服务器都可以立即看到这个变化。

作者：季舟1
链接：https://www.jianshu.com/p/c989ee86d7cf
来源：简书
简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。

NDB CLUSTER（也称为NDB）是一个内存存储引擎，提供高可用的数据持久化功能。
NDB CLUSTER存储引擎可以配置一系列故障转移和负载平衡。

集群节点

集群节点有三种类型，在最小的NDB集群配置中，至少会有三个节点。

Management node

这种类型节点的作用是管理NDB集群中的其他节点，执行诸如提供配置数据、启动和停止节点以及运行备份等功能。因为这个节点类型管理其他节点的配置，所以应该首先启动这种类型的节点，在任何其他节点之前。执行ndb_mgmd命令启动该节点。

Data node

这种类型节点的作用是存储集群数据。一个副本足以用于数据存储，但不提供冗余;因此，建议使用2（或更多）副本来提供冗余，从而获得高可用性。执行ndbd或ndbmtd（多线程）命令启动该节点。NDB集群表通常存储在内存中，而不是在磁盘上（这就是为什么我们将NDB集群称为内存中的数据库）。然而，一些NDB集群数据可以存储在磁盘上。

SQL node

在NDB Cluster中SQL节点是一个使用NDBCLUSTER存储引擎的传统MySQL服务器。

期望在生产环境中使用三个节点的设置是不现实的。这样的配置不提供冗余;为了从NDB集群的高可用性特性中获益，您必须使用多个数据和SQL节点。还强烈推荐使用多个管理节点。

客户端

标准MySQL客户端

NDB集群可以与用PHP、Perl、C、C++、Java、Python、Ruby等编写的现有MySQL应用程序一起使用。这样的客户端应用程序发送SQL语句并接收来自MySQL服务器的响应，它们充当NDB集群SQL节点，就像它们与独立的MySQL服务器交互一样。例如，使用Connector/J 5.0.6和更高版本的Java客户端可以使用jdbc:mysql:loadbalance://url以透明地实现负载平衡。

NDB客户端

客户端程序可以使用NDB API（一个高层次的C++ API，NDBCLUSTER存储引擎）直接访问NDB集群数据，绕过任何可能连接到集群的MySQL服务器。
对于NDB集群来说，也可以使用NDB集群连接器来为NDB集群编写Java应用程序。NDB集群连接器包括ClusterJ，这是一种类似于对象关系映射持久性框架的高级数据库API，如Hibernate和JPA，它们直接连接到NDBCLUSTER，因此不需要访问MySQL服务器。在NDB集群中也为ClusterJPA提供了支持，这是一个利用ClusterJ和JDBC的优势的NDB集群的OpenJPA实现。ID查找和其他快速操作是使用ClusterJ（绕过MySQL服务器）执行的，而可以从MySQL查询优化器中获益的更复杂的查询是通过MySQL服务器发送的，使用JDBC。

管理客户端

这些客户端连接到管理服务器，并提供启动和停止节点的命令，启动和停止消息追踪（仅调试版本），显示节点版本和状态，启动和停止备份，等等。这种类型的程序的一个例子是ndbmgm管理客户端提供的NDB集群。

检查点

一般来说，当数据保存到磁盘时，据说已到达检查点。更具体的是NDB Cluster，检查点是所有已提交事务存储在磁盘上的时间点。关于NDB存储引擎，有两种类型的检查点可以协同工作，以确保维护集群数据的一致视图。这些显示在以下列表中：

本地检查点（LCP）：这是一个特定于单个节点的检查点; 但是，LCP或多或少同时发生在集群中的所有节点上。 LCP通常每隔几分钟发生一次; 精确的间隔会有所不同，并取决于节点存储的数据量，群集活动的级别以及其他因素。
在NDB 7.6.4之前，LCP涉及将所有节点的数据保存到磁盘。 NDB 7.6.4引入了对部分LCP的支持，在某些条件下可以显着提高恢复时间。有关更多信息，请参见第21.1.4.2节“NDB Cluster 7.6中的新增功能”，以及启用部分LCP并控制其使用的存储量的EnablePartialLcp和RecoveryWork配置参数的说明。
全局检查点（GCP）：每隔几秒就会发生一次GCP，此时所有节点的事务都已同步，并且重做日志被刷新到磁盘。
Nodes, Node Groups, Replicas, and Partitions

Data Node。 ndbd或ndbmtd进程，用于存储一个或多个副本，即分配给该节点所属的节点组的分区副本（本节稍后讨论）。

每个数据节点应位于单独的计算机上。虽然也可以在一台计算机上托管多个数据节点进程，但通常不建议这样的配置。

当引用ndbd或ndbmtd进程时，术语“节点”和“数据节点”通常可互换使用; 如上所述，管理节点（ndb_mgmd进程）和SQL节点（mysqld进程）在本讨论中如此指定。

Node group。节点组由一个或多个节点组成，并存储分区或副本集（请参阅下一项）。

NDB群集中的节点组数量不能直接配置; 它是数据节点数和副本数（NoOfReplicas配置参数）的函数，如下所示：# of node groups = # of data nodes / NoOfReplicas

Partition。 这是群集存储的数据的一部分。每个节点负责保留分配给它的任何分区的至少一个副本（即，至少一个副本）。
NDB Cluster默认使用的分区数取决于数据节点的数量和数据节点使用的LDM线程数，如下所示：[# of partitions] = [# of data nodes] * [# of LDM threads]

使用运行ndbmtd的数据节点时，LDM线程的数量由MaxNoOfExecutionThreads的设置控制。使用ndbd时，只有一个LDM线程，这意味着与参与集群的节点一样多的集群分区。使用ndbmtd且MaxNoOfExecutionThreads设置为3或更小时也是如此。（您应该知道LDM线程的数量随着此参数的值而增加，但不是严格线性的，并且设置它有其他限制;有关详细信息，请参阅MaxNoOfExecutionThreads的说明。）

NDB and user-defined partitioning。 NDB群集通常会自动分区NDBCLUSTER表。但是，也可以使用NDBCLUSTER表进行用户定义的分区。这受到以下限制：

使用NDB表生产时仅支持KEY和LINEAR KEY分区方案。
可以为任何NDB表显式定义的最大分区数是8 MaxNoOfExecutionThreads [节点组数]，NDB群集中的节点组数量正如本节前面所讨论的那样确定。使用ndbd进行数据节点进程时，设置MaxNoOfExecutionThreads无效;在这种情况下，为了执行该计算，可以将其视为等于1。

Replica。这是群集分区的副本。节点组中的每个节点都存储一个副本。有时也称为分区副本。副本数等于每个节点组的节点数。

副本完全属于单个节点; 节点可以（通常会）存储多个副本。

下图说明了一个NDB集群，其中四个数据节点运行ndbd，分别安排在两个节点组中，每个节点组包含两个节点; 节点1和2属于节点组0，节点3和4属于节点组1。这里只显示数据节点; 虽然有效的NDB群集需要ndb_mgmd进程进行群集管理，并且至少有一个SQL节点需要访问群集存储的数据，但为清楚起见，这些已从图中省略。

群集存储的数据分为四个分区，编号为0,1,2和3.每个分区在同一节点组中存储多个副本。分区存储在备用节点组中，如下所示：

分区0存储在节点组0上;主副本（主副本）存储在节点1上，备份副本（分区的备份副本）存储在节点2上。
分区1存储在另一个节点组（节点组1）上;此分区的主副本位于节点3上，其备份副本位于节点4上。
分区2存储在节点组0上。但是，它的两个副本的放置与分区0的放置相反;对于分区2，主副本存储在节点2上，备份存储在节点1上。
分区3存储在节点组1上，并且其两个副本的放置与分区1的位置相反。即，其主副本位于节点4上，备份在节点3上。

对于NDB集群的持续运行，这意味着：只要参与集群的每个节点组至少有一个节点运行，集群就拥有所有数据的完整副本并且仍然可行。这将在下图中说明。

在此示例中，群集由两个节点组组成，每个节点组由两个数据节点组成。每个数据节点都运行ndbd的实例。来自节点组0的至少一个节点和来自节点组1的至少一个节点的任何组合足以使群集保持“活动”。但是，如果来自单个节点组的两个节点都发生故障，则由另一个节点组中的其余两个节点组成的组合是不够的。在这种情况下，群集已丢失整个分区，因此无法再提供对所有NDB群集数据的完整集合的访问。

在NDB 7.5.4和更高版本中，单个NDB群集实例支持的最大节点组数为48（Bug＃80845，Bug＃22996305）。

Network communication and latency

NDB Cluster需要数据节点和API节点（包括SQL节点）之间以及数据节点和其他数据节点之间的通信，以执行查询和更新。这些进程之间的通信延迟可直接影响观察到的用户查询的性能和延迟。此外，为了在节点无声故障的情况下保持一致性和服务，NDB Cluster使用心跳和超时机制，将来自节点的通信的延长丢失视为节点故障。这可以减少冗余。回想一下，为了保持数据一致性，当节点组中的最后一个节点发生故障时，NDB集群会关闭。因此，为了避免增加强制关闭的风险，应尽可能避免节点之间的通信中断。

数据或API节点的故障导致涉及故障节点的所有未提交事务的中止。数据节点恢复需要在数据节点恢复服务之前，从幸存的数据节点同步故障节点的数据，并重新建立基于磁盘的重做和检查点日志。此恢复可能需要一些时间，在此期间群集以减少的冗余运行。

心跳依赖于所有节点及时生成心跳信号。如果节点过载，由于与其他程序共享导致机器CPU不足，或者由于交换而出现延迟，则可能无法执行此操作。如果心跳生成被充分延迟，则其他节点会将响应缓慢的节点视为失败。

在某些情况下，将慢节点作为故障节点的这种处理可能是或可能不是合乎需要的，这取决于节点的慢速操作对集群其余部分的影响。为NDB群集设置HeartbeatIntervalDbDb和HeartbeatIntervalDbApi等超时值时，必须注意实现快速检测，故障转移和恢复服务，同时避免可能出现的代价高昂的误报。

如果预期数据节点之间的通信延迟高于LAN环境中预期的通信延迟（大约100μs），则必须增加超时参数以确保任何允许的延迟时段都在配置的超时内。以这种方式增加超时对于检测故障的最坏情况时间以及因此服务恢复的时间具有相应的影响。

LAN环境通常可以配置稳定的低延迟，并且可以通过快速故障转移提供冗余。可以从TCP级别可见的最小和受控延迟（NDB群集正常运行）中恢复单个链路故障。 WAN环境可能会提供一系列延迟，以及冗余和较慢的故障转移时间。单个链路故障可能需要在端到端连接恢复之前传播路由更改。在TCP级别，这可能表现为各个通道上的大延迟。在这些情况下观察到的最坏情况的TCP延迟与IP层在故障周围重新路由的最坏情况时间有关。

索引