MySQL性能调优与架构设计【数据库基础二】

1.1.5.命名规范

1、可读性原则

数据库、表、字段的命名要遵守可读性原则，尽可能少使用或者不使用缩写。

对象的名字应该能够描述它所表示的对象。例如：表的名称应该能够体现表中存储的数据内容，最好是遵循“业务名称_表的作用”；对于存储过程存储过程应该能够体现存储过程的功能。库名与应用名称尽量一致。

表达是与否概念的字段，应该使用is_xxx的方式命名，数据类型是unsigned tinyint（1表示是，0表示否）。

2、表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。
说明：MySQL在Windows下不区分大小写，但在Linux下默认是区分大小写。因此，数据库名、表名、字段名，都不允许出现任何大写字母，避免节外生枝。

3、表名不使用复数名词

4、数据库、表、字段的命名禁用保留字，如desc、range、match之类

6、主键索引名为pk字段名；唯一索引名为uk字段名；普通索引名则为idx_字段名。

在面试过程中涉及到设计表的时，如果命名不规范，必定是一个很大的扣分项。

1.1.6MySql中的索引

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质： 索引是数据结构 。InnoDB存储引擎支持以下几种常见的索引：B+树索引、全文索引、哈希索引，其中比较关键的是B+树索引

那为什么HashMap不适合做数据库索引？

1、hash表只能匹配是否相等，不能实现范围查找；

2、当需要按照索引进行order by时，hash值没办法支持排序；

3、组合索引可以支持部分索引查询，如(a,b,c)的组合索引，查询中只用到了a和b也可以查询的，如果使用hash表，组合索引会将几个字段合并hash，没办法支持部分索引；

4、当数据量很大时，hash冲突的概率也会非常大。

1.1.7.B+Tree

B+树索引就是传统意义上的索引，这是目前关系型数据库系统中查找最常用和最为有效的索引。B+树索引的构造类似于二叉树，根据键值（Key Value）快速找到数据。注意B+树中的B不是代表二叉(binary)，而是代表平衡(balance)，因为B+树是从最早的平衡二叉树演化而来，但是B+树不是一个二叉树。

在讲二叉树之前，我们必须了解一下二分查找：

二分查找法（binary search） 也称为折半查找法，用来查找一组有序的记录数组中的某一记录。

在以下数组中找到数字48对应的下标

通过3次二分查找 就找到了我们所要的数字，而顺序查找需8次。

对于上面10个数来说，顺序查找平均查找次数为（1+2+3+4+5+6+7+8+9+10)/10=5.5次。而二分查找法为(4+3+2+4+3+1+4+3+2+3)/10=2.9次。在最坏的情况下，顺序查找的次数为10，而二分查找的次数为4。

所以为了索引查找的高效性，我们引入了二叉查找树。

1.1.7.1.二叉树

1.1.7.1.1.树(Tree)

N个结点构成的有限集合。

• 树中有一个称为”根(Root)”的特殊结点
• 其余结点可分为M个互不相交的树，称为原来结点的”子树”

1.1.7.1.2.树与非树

1.1.7.1.3.树的一些基本术语

1.1.7.1.4.二叉树

度为2的树（也可称之为阶）：（树的度：树中所有结点中最大的度。结点的度：结点的子树个数）

子树有左右顺序之分：

1.1.7.1.5.二叉查找(搜索)树

二叉查找树首先肯定是个二叉树，除此之外还符合以下几点：

• 左子树的所有的值小于根节点的值
• 右子树的所有的值大于或等于根节点的值
• 左、右子树满足以上两点

但是二叉查找树，如果设计不良，完全可以变成一颗极不平衡的二叉查找树：

因此若想最大性能地构造一棵二叉查找树，需要这棵二叉查找树是平衡的，从而引出了新的定义——平衡二叉树，或称为AVL树。

1.1.7.1.6.平衡二叉树（AVL-树）

它是一棵二叉排序树，它的左右两个子树的高度差（平衡因子）的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。

目的：使得树的高度最低，因为树查找的效率决定于树的高度

平衡二叉树的查找性能是比较高的，但是维护一棵平衡二叉树的代价是非常大的。通常来说，需要1次或多次左旋和右旋来得到插入、更新和删除后树的平衡性。

具体树的旋转见：算法数据结构体系学习班 马士兵教育官网 - IT职业领路人 (mashibing.com)

章节11-13

1.1.7.2.B+树

B+ 树是从平衡二叉查找树演化而来（但B+树不是二叉树，而是一个多叉查找平衡树）。

下图就是一颗平衡二叉查找树

借助网页工具：Data Structure Visualization (usfca.edu)

现在我们将其改造成 B+ 树

树的阶数表示一个节点最多能有多少个子节点。

每个叶子页（LeafPage）存储了实际的数据，如下图中有的叶子页就存放了3条数据记录，当然可以更多，叶子节点由小到大（有序）串联在一起，叶子页中的数据也是排好序的；

从AVL到B+树的变化可知，如果节点特别多的话，AVL树的高度远远高于B+树。

我们可以归纳出B+树的几个特征：

1、相同节点数量的情况下，B+树高度远低于平衡二叉树；

2、非叶子节点只保存索引信息和下一层节点的指针信息，不保存实际数据记录；

3、每个叶子页（LeafPage）存储了实际的数据，比如上图中每个叶子页就存放了3条数据记录，当然可以更多，叶子节点由小到大（有序）串联在一起，叶子页中的数据也是排好序的；

4、相邻的叶子节点之间用指针相连。

注意：叶子节点中的数据在物理存储上完全可以是无序的，仅仅是在逻辑上有序（通过指针串在一起）。

当然，一棵m阶的B+树完整定义如下：

• 每个节点最多可以有 m 个元素；

• 除了根节点外，每个节点最少有 (m/2) 个元素；

• 如果根节点不是叶节点，那么它最少有 2 个孩子节点；

• 所有的叶子节点都在同一层；

• 非叶子节点只存放关键字和指向下一个孩子节点的索引，记录只存放在叶子节点中；

• 一个有 k 个孩子节点的非叶子节点有(k-1) 个元素，按升序排列；

• 某个元素的左子树中的元素都比它小，右子树的元素都大于或等于它（二叉排序树的特征）；

• 相邻的叶子节点之间用指针相连。
  以上知识可以不了解，一般面试不会问到B+树的细节（B+树的插入、B+树的删除、B+树的旋转等等），除非面试岗位就是做数据库实现的。
  如果想详细了解，可以找《算法导论》这本书

  面试问得比较多的可能是B树(也可以是B-树)、B*树，以及为什么选用B+树。
  B树也B+树的差别是，B树的非叶子节点也需要存放数据，下图是B树

  而B+树的话，数据只存在叶子节点上，同时相邻的叶子节点有链表的结构

  同时要注意，MySQL中实现的B+树，叶子节点之间的链表是双向链表，这是一个细微的差别。
  另外B* 树的话，与B+树的差别就是在非叶子节点之间，也有相互的指针指向。Oracle中使用的是B* 树。

  那为什么MySQL不用B树而使用B+树呢？

• 因为B数据每个节点都存储数据，每次查询的数据大小固定，就会造成每次查询返回的数据的条数变少，相同数据规模的情况下B树会增加io次数，而B+树，则数据量较小，一次可以返回多条记录，io次数较少

• 范围查询B+树明显优于B树

1.1.8.MySQL与B+树

为什么关系型数据库都选择了B+树，这个和磁盘的特性有着非常大的关系。

为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存，这个称之为预读。

预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页，页大小通常为4k。

按照磁盘的这种性质，如果是一个页存放一个B+树的节点，自然是可以存放很多的数据的，比如InnoDB里，默认定义的B+树的节点大小是16KB，这就是说，假如一个Key是8个字节，那么一个节点可以存放大约1000个Key，意味着B+树可以有1000个分叉。同时InnoDB每一次磁盘I/O，读取的都是 16KB的整数倍的数据。也就是说InnoDB在节点的读写上是可以充分利用磁盘顺序IO的高速读写特性。

同时按照B+树逻辑结构来说，在叶子节点一层，所有记录的主键按照从小到大的顺序排列，并且形成了一个双向链表。同一层的非叶子节点也互相串联，形成了一个双向链表。那么在实际读写的时候，很大的概率相邻的节点会放在相邻的页上，又可以充分利用磁盘顺序IO的高速读写特性。

所以我们对MySQL优化的一大方向就是 尽可能的多让数据顺序读写，少让数据随机读写 。

磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），一般来说，磁盘的顺序读的效率是随机读的40到400倍都有可能，顺序写是随机写的10到100倍。

1.1.9.B+树的作用总结

• 在磁盘设备上，通过B+树可以有效的存储数据；
• 所有记录都存储在叶子节点上，非叶子(non-leaf)存储索引(keys)信息；而且记录按照索引列的值由小到大排好了序。
• B+树含有非常高的扇出（fanout），通常超过100，在查找一个记录时，可以有效的减少IO操作；

*扇出：是每个索引节点(Non-LeafPage)指向每个叶子节点(LeafPage)的指针；

*扇出数 = 索引节点(Non-LeafPage)可存储的最大关键字个数 + 1