MySQL数据库设计和优化

一.范式化和反范式化设计的优缺点

1.范式化设计的优缺点

优点:

• 可以尽量减少数据冗余,数据表更新快体积小
• 范式化的更新操作比反范式化更快
• 范式化的表通常比反范式化更小

缺点:

• 对于查询需要对多个表进行关联
• 更难进行索引优化

2.反范式化设计的优缺点

优点:

• 可以减少表的关联
• 可以更好的进行索引优化

缺点:

• 存在数据冗余及数据维护异常
• 对数据的修改需要更多的成本

二.物理设计

物理设计涉及的内容

• 定义数据库,表及字段的命名规范
• 选择合适的存储引擎
• 为表中的字段选择合适的数据类型
• 建立数据库结构

1.定义数据库,表及字段的命名规范

• 数据库、表及字段的命名要遵守可读性原则
• 数据库、表及字段的命名要遵守表意性原则
• 数据库、表及字段的命名要遵守长名原则

2.选择合适的存储引擎

存储引擎	事务	锁粒度	主要应用	忌用
MyISAM	不支持	支持并发插入的表级锁	SELECT,INSERT	读写操作频繁
MRG_MYISAM	不支持	支持并发插入的表级锁	分段归档,数据仓库	全局查找过多的场景
Innodb	支持	支持MVCC的行级锁	事务处理	无
Archive	不支持	行级锁	日志记录,只支持insert,select	需要随机读取,更新,删除
Ndb cluster	支持	行级锁	高可用性	大部分应用

3.为表中的字段选择合适的数据类型

当一个列可以选择多种数据类型时,应该优先考虑数字类型,其次是日期或者二进制类型,最后是字符类型.对于相同级别的数据类型,应该优先选择占用空间小的数据类型

3.1如何选择正确的整数类型

列类型	存储空间	取值范围(SIGNED)	取值范围(UNSIGNED)
tinyint	1字节	-128~127	0~255
smallint	2字节	-32768~32767	0~65535
mediumint	3字节	-8388608~8388607	0~16777215
int	4字节	-2147483648~2147483647	0~4294967295
bigint	8字节	-9223372036854775808~9223372036854775807	0~18446744073709551615

3.2如何选择正确的实数类型

列类型	存储空间	是否精确类型
FLOAT	4字节	否
DOUBLE	8字节	否
DECIMAL	每4个字节存9个数字,小数点占一个字节	是

DECIMAL(18,9)需要9个字节来存储

3.3如何选择VARCHAR和CHAR类型

VARCHAR类型的存储特点

• varchar用于存储变长字符串,只占用必要的存储空间
• 列的最大长度小于255则只占用一个额外字节用于记录字符串长度
• 列的最大长度大于255则要占用两个额外字节用于记录字符串长度

VARCHAR长度的选择问题

• 使用最小的符合需求的长度
• varchar(5)和varchar(200)存储’MySQL’字符串性能不同

VARCHAR的适用场景

• 字符串列的最大长度比平均长度大很多
• 字符串列很少被更新

CHAR类型的存储特点

• char类型是定长的
• 字符串存储在char类型的列中会删除末尾的空格
• char类型的最大宽度为255

CHAR类型的适用场景

• CHAR类型适合存储长度近似的值
• CHAR类型适合存储短字符串
• CHAR类型适合存储经常更新的字符串列

3.4如何存储日期数据

DATETIME类型

• 以YYYY-MM-DD HH:MM:SS[.fraction]格式存储时间
• DATETIME类型与时区无关,占用8个字节的存储空间
• 时间范围1000-01-01 00:00:00到9999-12-31 23:59:59

TIMESTAMP类型

• 存储了由格林尼治时间1970年1月1日到当前时间的秒数,以YYYY-MM-DD HH:MM:SS[.fraction]的格式显示,占用四个字节
• 时间范围1970-01-01 到 2038-01-19
• timestamp类型显示依赖于所指定的时区
• 在行的数据修改是可以自动修改timestamp的值

date类型和time类型
当仅需存储日期部分(如生日),传统解决方案

• 把日期部分存储为字符串(至少8个字节)
• 使用int类型来存储(4个字节)
• 使用datetime类型来存储(8个字节)

date类型的优点

• 占用的字节数更少,只需3个字节
• 可以利用日期时间函数进行日期之间的计算
• date类型用于保存1000-01-01到9999-12-31之间的日期

time类型用于存储时间,格式为HH:MM:SS

存储日期时间数据的注意事项

1.不要使用字符串类型来存储日期数据

• 日期时间类型通常比字符串占用的存储空间小
• 日期时间类型在进行查找过滤时可以利用日期来进行对比
• 日期时间类型还有着丰富的处理函数,可以方便的对时期类型进行日期计算

2.使用Int存储日期时间不如使用Timestamp类型

三.索引

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

我们知道，数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序查找（linear search），这种复杂度为O(n)的算法在数据量很大时显然是糟糕的，好在计算机科学的发展提供了很多更优秀的查找算法，例如二分查找（binary search）、二叉树查找（binary tree search）等。如果稍微分析一下会发现，每种查找算法都只能应用于特定的数据结构之上，例如二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树上，但是数据本身的组织结构不可能完全满足各种数据结构（例如，理论上不可能同时将两列都按顺序进行组织），所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

1.索引的各种存储结构及其优缺点

我们先来看一下在数据库没有加索引的情况下，SQL中的where字句是如何查找目标记录的。

我们先看下左边表格第二列Col2列的数据时如何查找的，如果我们希望查找where Col2 = 22的记录，我们在没加索引的情况下是按顺序从第一条记录查找，由此可知需要查找5次才能找到；

如果对Col2字段加上索引后，我们假设使用最简单的二叉树作为索引存储方式，再次查找where Col2 = 22的记录这次只需要查找2次就能找到目标记录，效率提高十分明显。

（一） 二叉树

1. 优点：
   二叉树是一种比顺序结构更加高效地查找目标元素的结构，它可以从第一个父节点开始跟目标元素值比较，如果相等则返回当前节点，如果目标元素值小于当前节点，则移动到左侧子节点进行比较，大于的情况则移动到右侧子节点进行比较，反复进行操作最终移动到目标元素节点位置。
2. 缺点：
   在大部分情况下，我们设计索引时都会在表中提供一个自增整形字段作为建立索引的列，在这种场景下使用二叉树的结构会导致我们的索引总是添加到右侧，在查找记录时跟没加索引的情况是一样的。

（二） 红黑树

1. 优点：
   红黑树也叫平衡二叉树，它不仅继承了二叉树的优点，而且解决了上面二叉树遇到的自增整形索引的问题，红黑树会走动对结构进行调整，始终保证左子节点数 < 父节点数 < 右子节点数的规则。
2. 缺点：
   在数据量大的时候，深度也很大。从图中可以看出每个父节点只能存在两个子节点，如果我们有很多数据，那么树的深度依然会很大，可能就会超过十几二十层以上，对我们的磁盘寻址不利，依然会花费很多时间查找。

（三） Hash

1. 优点：
   对数据进行Hash（散列）运算，主流的Hash算法有MD5、SHA256等等，然后将哈希结果作为文件指针可以从索引文件中获得数据的文件指针，再到数据文件中获取到数据，按照这样的设计，我们在查找where Col2 = 22的记录时只需要对22做哈希运算得到该索引所对应那行数据的文件指针，从而在MySQL的数据文件中定位到目标记录，查询效率非常高。

2. 缺点：
   无法解决范围查询（Range）的场景，比如 select count(id) from sus_user where id >10；因此Hash这种索引结构只能针对字段名=目标值的场景使用。
   不适合模糊查询（like）的场景。

2.B-Tree和B+Tree

2.1 B-Tree

既然红黑树存在缺点，那么我们可以在红黑树的基础上构思一种新的储存结构。解决的思路也很简单，既然觉得树的深度太长，就只需要适当地增加每个树节点能存储的数据个数即可，但是数据个数也必须要设定一个合理的阈值，不然一个节点数据个数过多会产生多余的消耗。

为了描述B-Tree，首先定义一条数据记录为一个二元组[key, data]，key为记录的键值，对于不同数据记录，key是互不相同的；data为数据记录除key外的数据。那么B-Tree是满足下列条件的数据结构：

• d为大于1的一个正整数，称为B-Tree的度(节点的数据存储个数),每个树节点中数据个数大于 15/16*Degree（未验证） 时会自动分裂，调整结构。
• h为一个正整数，称为B-Tree的高度。
• 每个非叶子节点由n-1个key和n个指针组成，其中d<=n<=2d。
• 每个叶子节点最少包含一个key和两个指针，最多包含2d-1个key和2d个指针，叶节点的指针均为null 。
• 所有叶节点具有相同的深度，等于树高h,叶节点的指针为空。
• key和指针互相间隔，节点两端是指针。
• 一个节点中的key从左到右非递减排列。
• 所有节点组成树结构。
• 每个指针要么为null，要么指向另外一个节点。
• 如果某个指针在节点node最左边且不为null，则其指向节点的所有key小于v(key1)，其中v(key1)为node的第一个key的值。
• 如果某个指针在节点node最右边且不为null，则其指向节点的所有key大于v(keym)，其中v(keym)为node的最后一个key的值。
• 如果某个指针在节点node的左右相邻key分别是keyi和keyi+1且不为null，则其指向节点的所有key小于v(keyi+1)且大于v(keyi)。

由于B-Tree的特性，在B-Tree中按key检索数据的算法非常直观：首先从根节点进行二分查找，如果找到则返回对应节点的data，否则对相应区间的指针指向的节点递归进行查找，直到找到节点或找到null指针，前者查找成功，后者查找失败。

由于插入删除新的数据记录会破坏B-Tree的性质，因此在插入删除时，需要对树进行一个分裂、合并、转移等操作以保持B-Tree性质,后文中讲述InnoDB主键选择时会用到这里

2.2 B+Tree

B-Tree有许多变种，其中最常见的是B+Tree，例如MySQL就普遍使用B+Tree实现其索引结构。

与B-Tree相比，B+Tree有以下不同点：

• 每个节点的指针上限为2d而不是2d+1
• 非叶子节点不存储data，只存储key,可以增大度
• 叶子节点不存储指针

一般在数据库系统或文件系统中使用的B+Tree结构都在经典B+Tree的基础上进行了优化，增加了顺序访问指针。

如图所示，在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能，例如图4中如果要查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。

3.为什么使用B-Tree（B+Tree）

红黑树等数据结构也可以用来实现索引，但是文件系统及数据库系统普遍采用B-/+Tree作为索引结构，这一节将结合计算机组成原理相关知识讨论B-/+Tree作为索引的理论基础。

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。下面先介绍内存和磁盘存取原理，然后再结合这些原理分析B-/+Tree作为索引的效率。

上文说过一般使用磁盘I/O次数评价索引结构的优劣。先从B-Tree分析，根据B-Tree的定义，可知检索一次最多需要访问h个节点。数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的，在实际实现B-Tree还需要使用如下技巧：

每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。

B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为O(h)=O(logdN)。一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小（通常不超过3）。

综上所述，用B-Tree作为索引结构效率是非常高的。

而红黑树这种结构，h明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，所以红黑树的I/O渐进复杂度也为O(h)，效率明显比B-Tree差很多。

• 一般使用磁盘I/O次数评价索引结构的优劣
• 预读:磁盘一般会顺序向后读取一定长度的数据(页的整数倍)放入内存
• 局部性原理:当一个数据被用到时,其附近的数据通常会马上被使用
• B+Tree的节点的大小设为等于一个页,每次新建节点直接申请一个页的空间,这样就保证一个节点物理上也存储在一个页里,就实现了一个节点的载入只需一次I/O
• B+Tree的度一般会超过100,因此h非常小(一般为3到5之间)

4.MySQL索引实现

在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，下面讨论MyISAM和InnoDB两个存储引擎的索引实现方式。

4.1 MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图：

这里设表一共有三列，假设我们以Col1为主键，则图8是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

4.2 InnoDB索引实现

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

上图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，下图为定义在Col3上的一个辅助索引：

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

5.索引使用策略及优化

5.1 最左前缀原理与相关优化

高效使用索引的首要条件是知道什么样的查询会使用到索引，这个问题和B+Tree中的“最左前缀原理”有关，下面通过例子说明最左前缀原理。

这里先说一下联合索引的概念。在上文中，我们都是假设索引只引用了单个的列，实际上，MySQL中的索引可以以一定顺序引用多个列，这种索引叫做联合索引，一般的，一个联合索引是一个有序元组<a1, a2, …, an>，其中各个元素均为数据表的一列，实际上要严格定义索引需要用到关系代数，但是这里我不想讨论太多关系代数的话题，因为那样会显得很枯燥，所以这里就不再做严格定义。

单列索引其实也可以看做联合索引，索引列为1的联合索引，从下图就可以看出联合索引的底层存储跟单列索引时类似的，区别在于联合索引是每个树节点中包含多个索引值，在通过索引查找记录时，会先将联合索引中第一个索引列与节点中第一个索引值进行匹配，匹配成功接着匹配第二个索引列和索引值，直到联合索引的所有索引列都匹配完；如果过程中出现某一个索引列与节点相应位置的索引值不匹配的情况，则无需再匹配节点中剩余索引列，前往下一个节点。

以employees.titles表为例，下面先查看其上都有哪些索引：

SHOW INDEX FROM employees.titles;
+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Null | Index_type |
+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+
| titles |          0 | PRIMARY  |            1 | emp_no      | A         |        NULL |      | BTREE      |
| titles |          0 | PRIMARY  |            2 | title       | A         |        NULL |      | BTREE      |
| titles |          0 | PRIMARY  |            3 | from_date   | A         |      443308 |      | BTREE      |
| titles |          1 | emp_no   |            1 | emp_no      | A         |      443308 |      | BTREE      |
+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+

从结果中可以到titles表的主索引为<emp_no, title, from_date>，还有一个辅助索引<emp_no>。为了避免多个索引使事情变复杂（MySQL的