优化MySQL索引策略与数据结构详解-CSDN博客

一数据库索引

索引是存储引擎用于快速找到记录的一种数据结构。

索引分类(按照创建方式)：

mysql的索引分为单列索引(主键索引,唯一索引,普通索引)和组合索引.

单列索引:一个索引只包含一个列,一个表可以有多个单列索引。

组合索引:一个组合索引包含两个或两个以上的列。

单列索引

普通索引

其sql格式是 CREATE INDEX IndexName ON `TableName`(`字段名`(length)) 或者 ALTER TABLE TableName ADD INDEX IndexName(`字段名`(length))

CREATE INDEX account_Index ON `award`(`account`);

ALTER TABLE award ADD INDEX account_Index(`account`)

唯一索引

与普通索引类似,但是不同的是唯一索引要求所有的类的值是唯一的,这一点和主键索引一样.但是他允许有空值,

其sql格式是 CREATE UNIQUE INDEX IndexName ON `TableName`(`字段名`(length)); 或者 ALTER TABLE TableName ADD UNIQUE (column_list)

CREATE UNIQUE INDEX account_UNIQUE_Index ON `award`(`account`);

主键索引

不允许有空值,(在B+TREE中的InnoDB引擎中,主键索引起到了至关重要的地位)

主键索引建立的规则是 int优于varchar,一般在建表的时候创建,最好是与表的其他字段不相关的列或者是业务不相关的列.一般会设为 int 而且是 AUTO_INCREMENT自增类型的。

组合索引

一个表中含有多个单列索引不代表是组合索引,通俗一点讲组合索引是:包含多个字段但是只有一个索引名称。

其sql格式是 CREATE INDEX IndexName On `TableName`(`字段名`(length),`字段名`(length),...);

CREATE INDEX nickname_account_createdTime_Index ON `award`(`nickname`, `account`, `created_time`);

如果建立了组合索引(nickname_account_createdTime_Index) 那么他实际包含的是3个索引 (nickname) (nickname,account)(nickname,account,created_time)

在使用查询的时候遵循mysql组合索引的"最左前缀"

（1）不按索引最左列开始查询（多列索引）例如index(‘c1’, ‘c2’, ‘c3’) where ‘c2’ = ‘aaa’ 不使用索引,where `c2` = `aaa` and `c3`=`sss` 不能使用索引。

（2）查询中某个列有范围查询，则其右边的所有列都无法使用查询（多列查询）Where c1= ‘xxx’ and c2 like = ‘aa%’ and c3=’sss’ 改查询只会使用索引中的前两列,因为like是范围查询

（3）不能跳过某个字段来进行查询,这样利用不到索引

索引的删除

删除索引的mysql格式 :DORP INDEX IndexName ON `TableName`

索引分类方式（索引底层实现）

B+树索引

是mysql的默认索引类型。

B+树索引可以加快访问数据速度，因为存储引擎不再需要全表扫描来获取需要的数据，而是从索引的根节点开始搜索，根节点中存放了指向子节点的指针，存储引擎根据这些指针向下层寻找，直至找到对应的值，或者记录不存在。

B+树索引列是顺序组织存储的，所以比交适合查找范围数据。也适用于以下查询类型：

（1）全值查询：和索引列中所有列进行匹配

（2）匹配最左前缀

（3）匹配列前缀：只匹配某一列的值的开头部分

（4）匹配范围值

（5）精确匹配某一列并范围匹配另一列

（6）只访问索引的查询：即查询只需要访问索引而无需访问数据行

B+树索引的限制：

（1）如果不按照索引的最左列开始查找，则无法使用索引

（2）不可以跳过索引中的某列进行查询

（3）查询中某个列有范围查询，则其右边的所有列都无法使用查询

哈希索引

只有memory引擎显式支持哈希索引，且把它作为默认索引

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（一个较小的值），哈希索引把所有的哈希码存储在索引中，并在哈希表中保存指向每个数据行的指针。哈希索引内部以哈希码有序。

哈希索引自身只存储对应的哈希码，因此索引的结构十分紧凑，哈希索引查找的速度很快。

哈希索引的缺点：

（1）哈希索引只包括哈希码和行指针，而不存储字段值，所以不可以使用索引中的值来避免读取行。但一般访问内存中的数据行速度很快。

（2）哈希索引数据不是按照索引值顺序存储，所以无法用于排序

（3）哈希索引不支持部分索引列匹配查找，因为哈希索引始终都是使用索引列的全部内容来计算哈希值

（4）哈希索引只支持等值比较查询，不支持任何范围查询

（5）访问哈希索引的数据非常快，除非有很多哈希冲突，当出现冲突时，存储引擎必须遍历链表中所有行指针，逐行比较。

（6）如果有哈希冲突的话，一些索引维护操作的代价比较大。

自适应哈希索引

当InnoDB存储引擎注意到某些索引值被使用的很频繁时，它会在内存中基于B+树索引之上再自动创建一个哈希索引，提高查找效率

空间数据索引

全文索引

聚集索引与非聚集索引

（都采用B+树作为索引）聚集索引与非聚集索引的总结 - {-）大傻逼 - 博客园

（1）聚集索引：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。索引文件即数据文件。叶子节点存储了数据行

（2）非聚集索引：索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。其实按照定义，除了聚集索引以外的索引都是非聚集索引，只是人们想细分一下非聚集索引，分成普通索引，唯一索引，全文索引。叶子节点还是索引节点，存储了数据行的地址。存在二次查询的问题，即使用非聚簇索引时，查询列中包含了该索引没有覆盖的列，那么他还要进行第二次的查询，查询节点上对应的数据行的数据。解决方案：使用组合索引覆盖掉查询列。

B+树索引：

1 聚集索引

聚集索引的索引文件其实包含了数据文件，因为数据行存储在B+树索引的叶子节点上（非叶子节点存放索引列）。（聚集表示数据行和相邻的键值紧凑地存储在一起），因为无法把数据行存放在2个不同的地方，所以一个表只可以有一个聚集索引。

InnoDB通过主键聚集数据，如果没有定义主键，InnoDB会选择一个唯一的非空索引代替，如果没有非空的索引，InnoDB会隐式定义一个主键来作为聚集索引。

聚集索引优点：

（1）可以把相关数据保存在一起。这样只需要从磁盘读取少数的数据页就可以获取相关数据，减少磁盘IO次数

（2）数据访问更快。因为聚集索引把索引和数据行放在同一个B+树中。

聚集索引缺点：

（1）最大限度地提高了IO密集应用的性能，但是如果数据全部放在内存中，则访问的顺序不是那么重要。

（2）插入速度严重依赖于插入顺序。按照主键顺序插入是加载数据到InnoDB表中速度最快的方式。

（3）更新聚集索引的代价太大。因为会强制InnoDB将每个更新的行移动到新的位置

（4）基于聚集索引的表在插入新行或主键被更新需要移动行时，可能会面临“页分裂”的问题

（5）聚集索引可能导致全表扫面变慢，尤其当行比较稀疏，或者由于页分裂导致数据存储不连续时候。

（6）二级索引（非聚集索引）会比想象中大，因为二级索引的叶子节点包含了引用行的主键列。

（7）二级索引访问需要2次索引查找，而不是1次。（因为二级索引叶子节点保存的不是指向行的物理地址的指针，而是行的主键值）。因此通过二级索引查找行，存储引擎会找到二级索引叶子节点获得对应的主键值，然后根据这个值去聚集索引中查找对应的行。

建立聚簇索引的思想

1、大多数表都应该有聚簇索引或使用分区来降低对表尾页的竞争，在一个高事务的环境中，对最后一页的封锁严重影响系统的吞吐量。

2、在聚簇索引下，数据在物理上按顺序排在数据页上，重复值也排在一起，因而在那些包含范围检查(between、<、<=、>、>=)或使用group by或orderby的查询时，一旦找到具有范围中第一个键值的行，具有后续索引值的行保证物理上毗连在一起而不必进一步搜索，避免了大范围扫描，可以大大提高查询速度。

3、在一个频繁发生插入操作的表上建立聚簇索引时，不要建在具有单调上升值的列(如IDENTITY)上，否则会经常引起封锁冲突。

4、在聚簇索引中不要包含经常修改的列，因为码值修改后，数据行必须移动到新的位置。

5、选择聚簇索引应基于where子句和连接操作的类型。

聚簇索引的侯选列

1、主键列,该列在where子句中使用并且插入是随机的。

2、按范围存取的列，如pri_order > 100 and pri_order < 200。

3、在group by或order by中使用的列。

4、不经常修改的列。

5、在连接操作中使用的列。

2 非聚集索引（默认索引）

非聚集索引的索引文件和数据文件是分开的，索引叶子节点存储的是对应记录的物理存储地址。

主索引和辅助索引在结构上没有区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

一个表可以有不止一个非聚簇索引。实际上，对每个表你最多可以建立249个非聚簇索引。非聚簇索引需要大量的硬盘空间和内存。另外，虽然非聚簇索引可以提高从表中取数据的速度，它也会降低向表中插入和更新数据的速度。每当你改变了一个建立了非聚簇索引的表中的数据时，必须同时更新索引。

因此你对一个表建立非聚簇索引时要慎重考虑。如果你预计一个表需要频繁地更新数据，那么不要对它建立太多非聚簇索引。另外，如果硬盘和内存空间有限，也应该限制使用非聚簇索引的数量

二索引优缺点

索引的优点

索引可以让服务器快速定位到表的指定位置。

B+树索引，按照顺序存储数据，所以mysql可以做ORDER BY和GROUP BY操作。因为数据有序，所以B+树会把相邻的列值存储在一起。因为索引中存储了实际的列值，所以某些查询可以只使用索引就可以完成全部查询。

（1）大大减少服务器需要扫描的数据量

（2）可以帮助服务器避免排序和临时表

（3）可以将随机IO变为顺序IO。

索引缺点

创建索引的地方

不建议创建索引的地方

高性能索引策略

（1）独立的列：索引列不可以是表达式的一部分，或者函数的参数。

（2）使用前缀索引：当需要索引很长的字符串时，会让索引很大且慢。比如BLOB，TEXT等必须使用前缀索引。前缀索引可以使索引变得更小，更快，但缺点也很明显：mysql无法使用前缀索引做ORDER BY和GROUUP BY，也无法使用前缀索引做覆盖扫描。

（3）多列索引

（4）选择合适的索引列顺序

三红黑树/B树/B+树

Mysql索引介绍：https://www.cnblogs.com/chenshishuo/p/5030029.html

关于Mysql为什么使用B+树作为索引的详细介绍：https://www.cnblogs.com/tiancai/p/9024351.html

动态查找树

动态查找树主要有二叉查找树（binary search tree）、平衡二叉查找树（balanced binary search tree）、红黑树（Red-Black Tree）、B 树、B+树、B*树。

前三者是典型的二叉查找树结构，其查找的时间复杂度O(log2N)与树的深度相关，那么降低树的深度自然会提高查找效率。

红黑树

红黑树本质上是一个二叉查找树，查找效率是logn，即树的高度或深度，n为节点数目。在极端情况下，二叉树会退化成一个有序链表，查找的复杂度为O(n)。红黑树在二叉查找树的基础上增加了着色和相关的性质使得红黑树相对平衡，从而保证了红黑树的查找、插入、删除的时间复杂度最坏为O(log n)。

红黑树的性质：

1）每个结点要么是红的，要么是黑的。

2）根结点是黑的。

3）每个叶结点（叶结点即指树尾端NIL指针或NULL结点）是黑的。

4）如果一个结点是红的，那么它的俩个儿子都是黑的。

5）对于任一结点而言，其到叶结点树尾端NIL指针的每一条路径都包含相同数目的黑结点。

正是红黑树的这5条性质，使得一棵n个结点是红黑树始终保持了logn的高度，从而也就解释了上面我们所说的“红黑树的查找、插入、删除的时间复杂度最坏为O(log n)”这一结论的原因。

红黑树和AVL平衡二叉树的区别

AVL树是带有平衡条件的二叉查找树，一般是用平衡因子差值判断是否平衡并通过旋转来实现平衡，任一节点的左右子树高度差的绝对值不超过1。

和红黑树相比，AVL树是严格的平衡二叉树，平衡条件必须满足（所有节点的左右子树高度差绝对值不超过1）。不管我们是执行插入还是删除操作，只要不满足上面的条件，就要通过旋转来保持平衡，而旋转非常耗时的，由此我们可以知道AVL树适合用于插入与删除次数比较少，但查找多的情况。

红黑树是一种弱平衡二叉树（由于是弱平衡，可以看到，在相同的节点情况下，AVL树的高度低于红黑树），相对于要求严格的AVL树来说，它的旋转次数少，所以对于搜索，插入，删除操作较多的情况下，我们就用红黑树。

有了红黑树为什么还要发明B树？

但为何有了红黑树，还要发明B树呢？原因是，当计算机要处理的数据量很大无法一次性装入内存进行处理时，计算机会把大部分备用的数据存在磁盘中，有需要的时候，就从磁盘中调取数据到在内存中处理，如果处理时修改了数据，则再次将数据写入磁盘，如此导致了不断的磁盘IO读写，而树的高度越高，查找文件所需要的磁盘IO读写次数越多，所以为了减少磁盘的IO读写，要想办法进一步降低树的高度。因此，具有多个孩子的B树便应运而生，因为B树每一个结点可以有几个到几千个孩子，使得在结点数目一定的情况下，树的高度会大大降低，从而有效减少磁盘IO读写消耗。

此外，无论是B树，还是B+树、B树，由于根或者树的上面几层被反复查询，所以树上层几块的数据可以存在内存中。换言之，B树、B+树、B树的根结点和部分顶层数据存在内存中，大部分下层数据存在磁盘上。

B树

mysql数据库中的数据一般存储在磁盘上，而读/写磁盘上某一部分数据一般有3个步骤：

● 首先移动臂根据柱面号使磁头移动到所需要的柱面上，这一过程被称为定位或查找。

●这时根据盘面号来确定指定盘面上的磁道。

● 盘面确定以后，盘片开始旋转，将指定块号的磁道段移动至磁头下。

访问某一具体信息的时间开销为：

● 查找时间(seek time) Ts: 完成上述步骤(1)所需要的时间。这部分时间代价最高，最大可达到 0.1s 左右。

● 等待时间(latency time) Tl: 完成上述步骤(3)所需要的时间。由于盘片绕主轴旋转速度很快，一般为 7200转/分(电脑硬盘的性能指标之一, 家用的普通硬盘的转速一般有 5400rpm(笔记本)、7200rpm 几种)。因此一般旋转一圈大约 0.0083s。

● 传输时间(transmission time) Tt: 数据通过系统总线传送到内存的时间，一般传输一个字节(byte)大概0.02us=2*10^(-8)s

磁盘读取数据是以盘块(block)为基本单位的。位于同一盘块中的所有数据都能被一次性全部读取出来。而磁盘 IO 代价主要花费在查找时间 Ts 上。因此我们应该尽量将相关信息存放在同一盘块，同一磁道中。或者至少放在同一柱面或相邻柱面上，以求在读/写信息时尽量减少磁头来回移动的次数，避免产生过多的查找时间 Ts。

所以，在遇到大规模数据存储的情况时，大量数据存储在外存磁盘中，而在外存磁盘中读取/写入块(block)中某数据时，首先需要定位到磁盘中的某块，如何有效地查找磁盘中的数据，需要一种合理高效的外存数据结构，就是下面所要重点阐述的 B-tree 结构，以及相关的变种结构：B+树结构和 B*树结构。

B 树是为了磁盘或其他存储设备而设计的一种多叉（下面你会看到，相对于二叉，B 树每个内结点有多个分支，即多叉）平衡查找树。虽然与之前介绍的红黑树很相似，但在降低磁盘 I/0 操作方面 B 树表现得要更好一些，而且许多数据库系统一般都使用 B 树或者 B 树的各种变形结构。

B 树与红黑树最大的不同在于，B 树的结点可以有许多子女，从几个到几千个。不过 B 树与红黑树也有相同点，一棵含 n 个结点的 B 树的高度也为 O(lgn)，但可能比一棵红黑树的高度小许多，因为它的分支因子比较大，从几十个到几千个分支因子。所以，B 树可以在 O（logn）时间内，实现各种如插入（insert），删除（delete）等动态集合操作。

B 树又叫平衡多路查找树，一棵 m 阶的 B 树的特性如下：

● 树中每个结点最多含有 m 个孩子（m>=2）；

● 除根结点和叶子结点外，其他每个结点至少有[ceil(m / 2)]个孩子（其中 ceil(x)是一个取上限的函数）；

● 根结点至少有 2 个孩子（除非 B 树只包含一个结点：根结点）；

● 所有叶子结点都出现在同一层，叶子结点不包含任何关键字信息(可以看做是外部结点或查询失败的结点，指向这些结点的指针都为 null)；（注：叶子节点只是没有孩子和指向孩子的指针，这些节点也存在，也有元素。类似红黑树中，每一个 NULL 指针即当做叶子结点，只是没画出来而已）。

● 每个非终端结点中包含有 n 个关键字信息： (n，P0，K1，P1，K2，P2，......，Kn，Pn)。其中：

a) Ki (i=1...n)为关键字，且关键字按顺序升序排序 K(i-1)< Ki。

b) Pi 为指向子树根的结点，且指针 P(i-1)指向子树种所有结点的关键字均小于 Ki，但都大于 K(i-1)。

c) 关键字的个数 n 必须满足： [ceil(m / 2)-1]<= n <= m-1。比如有 j 个孩子的非叶结点恰好有 j-1 个关

键码。

B 树中的每个结点根据实际情况可以包含大量的关键字信息和分支(当然是不能超过磁盘块的大小，根据磁盘驱动(disk drives)的不同，一般块的大小在 1k~4k 左右)；这样树的深度降低了，这就意味着查找一个元素只需要把很少的结点从外存磁盘中读入内存即可，从而很快的访问到要查找的数据。在查找数据的过程中，磁盘IO的次数取决于树的高度，而由于B树每个节点可以有多个子女，因此树的高度相对较低，提高查询效率。

B+树

B+-tree：是应文件系统所需而产生的一种 B-tree 的变形树。一棵 m 阶的 B+树和 m 阶的 B 树的异同点在于：

● 有 n 棵子树的结点中含有 n-1 个关键字；

● 所有的叶子结点中包含了全部关键字的信息，及指向含有这些关键字记录的指针，且叶子结点本身依关键字的大小，自小而大的顺序链接。 (而 B 树的叶子节点并没有包括全部需要查找的信息)

● 所有的非终端结点可以看成是索引部分，结点中仅含有其子树根结点中最大（或最小）关键字。 (而B 树的非终节点也包含需要查找的有效信息)

● B+树的磁盘读写代价更低，B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对 B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说 IO 读写次数也就降低了。

● B+树的查询效率更加稳定由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找都必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

总而言之，B 树在提高了磁盘 IO 性能的同时并没有解决元素遍历效率低下的问题。正是为了解决这个问题，B+树应运而生。B+树只要遍历叶子节点就可以实现整棵树的遍历，支持基于范围的查询，而 B树不支持 range-query 这样的操作（或者说效率太低）。

B树与B+树的区别

b树和b+树的区别 - 雪球球 - 博客园（有的地方是错误的，比如）

B树与B+树

B树与B+树简明扼要的区别_Hannah-CSDN博客_b树与b+树的区别这个不错呦。。。

mysql为什么使用B+树作为索引

为了提升查询效率。

通常用二叉查找树或平衡二叉树作为查找工具，查找的时间复杂度为O(logn)。但是数据量多的时候，树的高度很大，磁盘IO的次数就会很多，也很低效。因此通常用采用节点个数更多的B树和B+树来降低树的高度，较少磁盘IO的次数。

而相对于B树

●B 树在提高了磁盘 IO 性能的同时并没有解决元素遍历效率低下的问题。正是为了解决这个问题，B+树应运而生。B+树只要遍历叶子节点就可以实现整棵树的遍历，支持基于范围的查询，而 B树不支持 range-query 这样的操作（或者说效率太低）。比如，如果选取多条数据，B树需要执行局部的中序遍历，可能需要跨层访问，而B+树由于所有数据都在叶子节点，不用跨层，只用找到首位指针，就可以遍历链表取出数据。

hash比B+树更快，为什么Mysql不用哈希作为索引？

与业务场景有关，如果只选一条记录，确实是哈希比较快，但是数据库操作经常会选取多条数据，这时候由于B+树索引有序，并且又有链表相连，查询效率就比hash快多了。

而且数据库的索引一般存放在磁盘上，数据量大的话可能无法一次装入内存，而B+树的设计可以允许数据分批加载（甚至每次只加载一个节点，同时树的高度较低，提高查找效率）

联合索引的原理：MySQL联合索引原理解析_cherry93925的博客-CSDN博客

四 MYSQL存储引擎

一 MyISAM

（1）特性

不支持事务：MyISAM存储引擎不支持事务，所以对事务有要求的业务场景不能使用

表级锁定：其锁定机制是表级索引，这虽然可以让锁定的实现成本很小但是也同时大大降低了其并发性能

读写互相阻塞：不仅会在写入的时候阻塞读取，MyISAM还会在读取的时候阻塞写入，但读本身并不会阻塞另外的读

只会缓存索引：MyISAM可以通过key_buffer缓存以大大提高访问性能减少磁盘IO，但是这个缓存区只会缓存索引，而不会缓存数据。

崩溃后无法安全恢复：不支持事务和行级锁。

将表存储在2个文件中：数据文件和索引文件

延迟更新索引：如果在创建myisam表时指定了DELAY_KEY_WRITE选项，每次修改之后不会立即把修改的索引写入磁盘，会先写入内存的键缓冲区，只有在清理键缓冲区或关闭表的时候才将对应索引写入磁盘（可以提升写入性能，但在数据库或主机崩溃时会造成索引损坏，需要修复）。

Myisam压缩表：可以使用myisampack对Myisam表进行压缩。压缩表不可以修改，可以极大地减少磁盘空间占用，减少磁盘IO，提升查询性能。适用于表在创建和导入数据之后不再修改的状态。

（2）适用场景

不需要事务支持（不支持）

并发相对较低（锁定机制问题）

数据修改相对较少（阻塞问题）

以读为主

数据一致性要求不是非常高

使用这个存储引擎，每个MyISAM在磁盘上存储成三个文件。

frm文件：存储表的定义数据
MYD文件：存放表具体记录的数据
MYI文件：存储索引

二 InnoDB

是mysql的默认事务性引擎，也是最重要，使用最广泛的存储引擎。

（1）特性

具有较好的事务支持：支持4个事务隔离级别，默认级别是可重复读，并通过间隙锁策略防止幻读的出现。（通过MVCC（并发版本控制）来实现的。）

行级锁定：通过索引实现，全表扫描仍然会是表锁，注意间隙锁的影响

读写阻塞与事务隔离级别相关

具有非常高效的缓存特性：能缓存索引，也能缓存数据

整个表和主键以Cluster方式存储，组成一颗平衡树

所有Secondary Index都会保存主键信息，如果主键很大，其他索引也会很大。

与myisam相比Innodb存储引擎支持事务，支持行锁和外键，支持非锁定读(默认读操作不会产生锁)。还可以通过MVCC(多版本并发控制)来获得高并发性，实现了标准的4中隔离级别，默认为Repeatable read级别。还通过next—key Locking的策略来避免幻读(phantom)现象的产生，Innodb还支持插入缓存（insert buffer）、二次写（double write）、自适应哈希索引（adaptive hash index）、预读（read ahead）等功能。

Innodb中每张表的存储都是按照主键的顺序进行存放的，如果没有定义主键，Innodb会为每一行生成一个6字节的ROW ID，以此作为主键。

（2）适用场景

需要事务支持（具有较好的事务特性）

行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成

数据更新较为频繁的场景

数据一致性要求较高

硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，尽可能减少磁盘 IO

innodb引擎的好处

a 更新密集的表。InnoDB存储引擎特别适合处理多重并发的更新请求。

b 事务。InnoDB存储引擎是支持事务的标准MySQL存储引擎。

c 自动灾难恢复。与其它存储引擎不同，InnoDB表能够自动从灾难中恢复。

d 外键约束。MySQL支持外键的存储引擎只有InnoDB。

e 支持自动增加列AUTO_INCREMENT属性

f 热备份

MyISAM索引实现

MyISAM引擎使用B+树作为索引结构，叶子节点存放的是数据记录的地址。

MyISAM索引文件仅仅保存数据记录的地址。

MyISAM中，主索引和辅助索引在结构上没有区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

按照数据插入的顺序存储在磁盘上

InnoDB索引实现

InnoDB引擎也使用B+树作为索引结构，但它与MyISAM索引实现有很大区别

（1）InnoDB数据文件本身就是索引文件。（聚集索引）

MyISAM索引文件和数据文件是分离的，索引文件仅仅保留数据记录的地址。

而InnoDB中，表数据文件本身就是按照B+树结构组织的索引结构，树的叶子节点数据域保存了完整的数据记录。这个索引的key就是数据表的主键，因此InnoDB表数据文件本身就是主索引。

（2）InnoDB的辅助索引数据域存储相应记录主键的值而不是地址。InnoDB所有辅助索引都引用主键作为数据域，这样可以减少当出现行移动或数据页分裂时二级索引的维护工作

五关系型数据库/非关系型数据库

SNS，专指社交网络服务，包括了社交软件和社交网站。

六事务/隔离级别

数据库事务正确执行的四个要素

ACID

原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

原子性

整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性

事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务有多少。

如果事务是并发多个，系统也必须如同串行事务一样操作。其主要特征是保护性和不变性(Preserving an Invariant)，以转账案例为例，假设有五个账户，每个账户余额是100元，那么五个账户总额是500元，如果在这个5个账户之间同时发生多个转账，无论并发多少个，比如在A与B账户之间转账5元，在C与D账户之间转账10元，在B与E之间转账15元，五个账户总额也应该还是500元，这就是保护性和不变性。

数据库总是从一个一致性状态转移到另一个一致性状态。

隔离性

如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。

持久性

在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

数据库隔离级别设置不同会发生的问题

数据库隔离级别

规定了4种隔离级别，每一种级别都规定了一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的。较低级别的隔离通常可以执行更高的并发，系统的开销也更低。

（1）未提交读 READ UNCOMMITED

事务的修改即使没有被提交，对其他事务也都是可见的。事务可以读取未提交的数据，即脏读。

（2）提交读READ COMMITED

大多数数据库系统的默认隔离级别是READ COMMITED。一个事务从开始直到提交前，所作的任何修改对其他事务都是不可见的，但会带来不可重复读的问题

（3）可重复读REPEATABLE READ

该级别保证了在同一个事务中多次读取同样记录的结果是一致的。但可能出现幻读。该级别是MYSQL的默认隔离界别

（4）SERIALIZABLE 可串行化

是最高的隔离级别。

通过强制事务串行执行来避免幻读。在读取每一行的数据上都加锁，可能导致大量的超时和锁争用问题。实际上很少用到

MySQL主从复制_ST Cp的博客-CSDN博客_mysql主从复制

MYSQL架构

MySQL三大日志——binlog、redoLog、undoLog详解_向着百万年薪努力的小赵的博客-CSDN博客_binlog redolog undolog

https://www.jianshu.com/p/081a3e208e32 MYSQL事务

MySQL MVCC原理 - chinesern - 博客园

MySQL 是如何解决幻读的？ - 知乎
 MySQL的锁机制 - 记录锁、间隙锁、临键锁_松myth的博客-CSDN博客_mysql 记录锁

锁粒度

即锁策略，一般分为2种：表锁和行级锁

（1）表锁

是mysql中最基本的锁策略，也是开销最小的策略。当用户对一个表进行写操作时，会获得写锁，锁定整个表，阻塞其他用户对该表的所有读写操作。

（2）行级锁

可以最大限度地支持并发处理，同时也带来了最大的锁开销。

七乐观锁/悲观锁

数据的锁定分为两种，第一种叫作悲观锁，第二种叫作乐观锁。

1、悲观锁，

就是对数据的冲突采取一种悲观的态度，也就是说假设数据肯定会冲突，所以在数据开始读取的时候就把数据锁定住。【数据锁定：数据将暂时不会得到修改】

2、乐观锁，

认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让用户返回错误的信息。让用户决定如何去做。

理解：

1. 乐观锁是一种思想，具体实现是，表中有一个版本字段，第一次读的时候，获取到这个字段。处理完业务逻辑开始更新的时候，需要再次查看该字段的值是否和第一次的一样。如果一样更新，反之拒绝。之所以叫乐观，因为这个模式没有从数据库加锁。

2. 悲观锁是读取的时候为后面的更新加锁，之后再来的读操作都会等待。这种是数据库锁

乐观锁优点程序实现，不会存在死锁等问题。他的适用场景也相对乐观。阻止不了除了程序之外的数据库操作。

悲观锁是数据库实现，他阻止一切数据库操作。再来说更新数据丢失，所有的读锁都是为了保持数据一致性。乐观锁如果有人在你之前更新了，你的更新应当是被拒绝的，可以让用户从新操作。悲观锁则会等待前一个更新完成。这也是区别。具体业务具体分析实现。

一、悲观锁

1、排它锁，当事务在操作数据时把这部分数据进行锁定，直到操作完毕后再解锁，其他事务操作才可操作该部分数据。这将防止其他进程读取或修改表中的数据。

2、实现：大多数情况下依靠数据库的锁机制实现

一般使用 select ...for update 对所选择的数据进行加锁处理，

例如select * from account where name=”Max” for update，这条sql 语句锁定了account 表中所有符合检索条件（name=”Max”）的记录。本次事务提交之前（事务提交时会释放事务过程中的锁），外界无法修改这些记录。

二、乐观锁

1、如果有人在你之前更新了，你的更新应当是被拒绝的，可以让用户重新操作。

2、实现：大多数基于数据版本（Version）记录机制实现具体可通过给表加一个版本号或时间戳字段实现，当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断当前版本信息与第一次取出来的版本值大小，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据，拒绝更新，让用户重新操作。

八 MYSQL表与字段设计

1 数据库基本设计规范

（1）尽量控制单表数据量的大小，建议控制在 500 万以。500 万并不是 MySQL 数据库的限制，过大会造成修改表结构、备份、恢复都会有很大的问题，可以用历史数据归档（应用于日志数据），分库分表（应用于业务数据）等手段来控制数据量大小

（2）谨慎使用 MySQL 分区表。分区表在物理上表现为多个文件，在逻辑上表现为一个表谨慎选择分区键，跨分区查询效率可能更低建议采用物理分表的方式管理大数据。

（3）禁止在数据库中存储图片，文件等大的二进制数据。通常文件很大，会短时间内造成数据量快速增长，数据库进行数据库读取时，通常会进行大量的随机 IO 操作，文件很大时，IO 操作很耗时通常存储于文件服务器，数据库只存储文件地址信息

（4）禁止在线上做数据库压力测试

2 数据库字段设计规范

（1）优先选择符合存储需要的最小的数据类型。列的字段越大，建立索引时所需要的空间也就越大，这样一页中所能存储的索引节点的数量也就越少也越少，在遍历时所需要的 IO 次数也就越多，索引的性能也就越差

（2）避免使用 TEXT、BLOB 数据类型，最常见的 TEXT 类型可以存储 64k 的数据

（3）尽可能把所有列定义为 NOT NULL

3 索引设计规范

（1）限制每张表上的索引数量，建议单张表索引不超过 5 个

（2）禁止给表中的每一列都建立单独的索引

（3）每个 InnoDB 表必须有个主键

（4）建立索引的目的是：希望通过索引进行数据查找，减少随机 IO，增加查询性能，索引能过滤出越少的数据，则从磁盘中读入的数据也就越少。区分度最高的放在联合索引的最左侧（区分度 = 列中不同值的数量 / 列的总行数）。尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大，IO 性能也就越好）。使用最频繁的列放到联合索引的左侧（这样可以比较少的建立一些索引）。

4 数据库 SQL 开发规范

（1）充分利用表上已经存在的索引,避免使用双 % 号的查询条件。如 a like '%123%'，（如果无前置 %，只有后置 %，是可以用到列上的索引的）
一个 SQL 只能利用到复合索引中的一列进行范围查询，如：有 a,b,c 列的联合索引，在查询条件中有 a 列的范围查询，则在 b,c 列上的索引将不会被用到，在定义联合索引时，如果 a 列要用到范围查找的话，就要把 a 列放到联合索引的右侧。
使用 left join 或 not exists 来优化 not in 操作，因为 not in 也通常会使用索引失效。

（2）禁止使用 SELECT * 必须使用 SELECT <字段列表> 查询

（3）避免使用子查询，可以把子查询优化为 JOIN 操作

（4）避免使用 JOIN 关联太多的表