MySQL-数据库基础与索引解析

思维导图：https://www.processon.com/view/link/6283c3dce0b34d5ac4197efc

1.操作与落地

1.1.数据库基础知识

1.1.1.范式化设计

1.1.1.1.什么是范式

范式来自英文Normal Form，简称NF。

实际上你可以把它粗略地理解为 一张数据表的表结构所符合的某种设计标准的级别 。就像家里装修买建材，最环保的是E0级，其次是E1级，还有E2级等等

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式5NF，又称完美范式）。

满足最低要求的范式是第一范式（1NF），在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般来说，数据库只需满足第三范式(3NF）就行了。

1.1.1.2.第一范式（1NF）

定义： 属于第一范式关系的所有属性都不可再分，即数据项不可分。

理解：第一范式强调数据表的原子性，是其他范式的基础。一张表有一个name-age列，这个列具有两个属性，一个name,一个 age，所以不符合第一范式，我们把它拆分成两列name和age，这张表就符合第一范式关系。

你在关系型数据库管理系统（RDBMS），例如SQL Server，Oracle，MySQL中创建数据表的时候，1NF是所有关系型数据库设计的最基本要求。

第一范式详细的要求如下：

1、每一列属性都是不可再分的属性值，确保每一列的原子性；

2、两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据；

3、单一属性的列为基本数据类型构成；

4、设计出来的表都是简单的二维表。

1.1.1.2.第二范式（2NF）

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。

第二范式（2NF）要求实体的属性完全依赖于主关键字。

以上这张表不符合第二范式（2NF），虽然有主键，但是实体的属性不完全依赖于主关键字。

所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。

设计成两张表，主键分别是id和op_id，这样就符合第二范式（2NF）。

1.1.1.3.第三范式（3NF）

满足第三范式（3NF）必须先满足第二范式（2NF）；

第三范式（3NF）要求一个数据库表中不包含已在其它表中包含的非主关键字信息，即数据不能存在传递关系，即每个属性都跟主键有直接关系而不是间接关系。

产品表

这里如果产品ID或产品名称变化会发生什么情况？所以以上不符合第三范式（3NF）

以上订单表就符合第三范式

1.1.2.反范式化设计

完全符合范式化的设计真的完美无缺吗？很明显在实际的业务查询中会大量存在着表的关联查询，而表设计都做成了范式化设计（甚至很高的范式），大量的表关联很多的时候非常影响查询的性能。

反范式化就是违反范式化设计：

1、为了性能和读取效率而适当的违反对数据库设计范式的要求；

2、为了查询的性能，允许存在部分（少量）冗余数据

换句话来说反范式化就是使用空间来换取时间。

1.1.3.范式化和反范式对比

1、范式化的更新操作通常比反范式化要快（字段较少）。

2、当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据。

3、范式化的表通常更小，所以占据的内存更少。

4、范式化设计的缺点是通常需要关联，稍微复杂一些的查询语句在符合范式的表上都可能需要至少一次关联，也许更多。

5、复杂一些的查询语句也可能使一些索引策略无效。例如，范式化可能将列存放在不同的表中，而这些列如果在一个表中本可以属于同一个索引。

1.1.4.项目中常见的反范式实现

范式化和反范式化的各有优劣，怎么选择最佳的设计?小孩子才做选择，我们全都要！！！

1.1.4.1.缓存与汇总数据

“缓存”来表示存储那些可以比较简单地从其他表获取数据的表。

比如从父表冗余一些数据到子表的。前面我们看到的分类信息放到商品表里面进行冗余存放就是典型的例子。

“汇总”则保存的是使用GROUP BY语句聚合数据的表。

如果需要显示每个用户发了多少消息，可以每次执行一个对用户发送消息进行count的子查询来计算并显示它，也可以在user表用户中建一个消息发送数目的专门列，每当用户发新消息时更新这个值。

在使用缓存表和汇总表时，有个关键点是如何维护缓存表和汇总表中的数据，常用的有两种方式，实时维护数据和定期重建，这个取决于应用程序，不过一般来说，缓存表用实时维护数据更多点，往往在一个事务中同时更新数据本表和缓存表，汇总表则用定期重建更多，使用定时任务对汇总表进行更新。

1.1.4.2.计数器表设计

计数器表在Web应用中很常见。比如网站点击数、用户的朋友数、文件下载次数等。对于高并发下的处理，首先可以创建一张独立的表存储计数器，这样可使计数器表小且快，并且可以使用一些更高级的技巧。

比如假设有一个计数器表，只有一行数据，记录网站的点击次数，网站的每次点击都会导致对计数器进行更新，问题在于，对于任何想要更新这一行的事务来说，这条记录上都有一个全局的互斥锁(mutex)。这会使得这些事务只能串行执行，会严重限制系统的并发能力。

怎么改进呢？可以将计数器保存在多行中，每次随机选择一行进行更新。在具体实现上，可以增加一个槽（slot)字段，然后预先在这张表增加100行或者更多数据，当对计数器更新时，选择一个随机的槽（slot)进行更新即可。

1.1.4.字段数据类型优化

MySQL支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。不管存储哪种类型的数据，下面几个简单的原则都有助于做出更好的选择。

1.1.4.1.字段优化基本原则

• 更小的通常更好

一般情况下,应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。

比如：是有一个类型既可以用字符串也可以使用整型，优先选择整型。因为字符串牵涉到了字符集及校对规则等。

• 简单就好

简单数据类型的操作通常需要更少的CPU周期。例如，整型比字符操作代价更低，因为字符集和校对规则(排序规则)使字符比较比整型比较更复杂。比如应该使用MySQL内建的类型而不是字符串来存储日期和时间。

• 尽量避免NULL

通常情况下最好指定列为NOT NULL，除非真的需要存储NULL值。

如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊处理。当可为NULL的列被索引时，每个索引记录需要一个额外的字节。

通常把可为NULL的列改为NOT NULL带来的性能提升比较小，所以（调优时）没有必要首先在现有schema中查找并修改掉这种情况，除非确定这会导致问题。但是，如果计划在列上建索引，就应该尽量避免设计成可为NULL的列。

1.1.4.2.Int/整数类型

存储整数，可以使用这几种整数类型:TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT。分别使用8，16，24，32，64位存储空间，也就是1、2、3、4、8个字节。它们可以存储的值的范围请自行计算。

同时整数类型有可选的 UNSIGNED属性，表示不允许负值，这大致可以使正数的上限提高一倍。例如TINYINT UNSIGNED可以存储的范围是0255，而TINYINT的存储范围是-128127。

有符号和无符号类型使用相同的存储空间，并具有相同的性能，因此可以根据实际情况选择合适的类型。

另外integer和int存储及大小没有任何差别，只是为了业务上区分。

MySQL可以为整数类型指定宽度，例如INT(11)，对大多数应用这是没有意义的，它不会限制值的合法范围,只是规定了MySQL的一些交互工具（例如MySQL命令行客户端)用来显示字符的个数。对于存储和计算来说，INT(1)和INT(20)是相同的。

MySQL中没有long型，对应的只有bigint

1.1.4.3.实数类型

实数是带有小数部分的数字。MySQL既支持精确类型的存储DECIMAL类型，也支持不精确类型存储FLOAT和 DOUBLE类型（浮点类型）。DECIMAL类型用于存储精确的小数，本质上MySQL是以字符串形式存放的。所以CPU不支持对DECIMAL的直接计算，只是在MySQL中自身实现了DECIMAL的高精度计算。相对而言，CPU直接支持原生浮点计算，所以浮点运算明显更快。

浮点类型在存储同样范围的值时，通常比DECIMAL使用更少的空间。FLOAT使用4个字节存储，DOUBLE占用8个字节，DECIMAL里面存储65个数字，DECIMAL对于列的空间消耗比较大，另外DOUBLE比 FLOAT有更高的精度和更大的范围。

如何选择？
在精度不敏感和需要快速运算的时候，选择FLOAT和 DOUBLE。

应该尽量只在对小数进行精确计算时才使用DECIMAL，例如存储财务或金融数据。

但在数据量比较大的而且要求精度时，可以考虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财务数据精确到万分之一分，则可以把所有金额乘以一百万,然后将结果存储在BIGINT里，这样可以同时避免浮点存储计算不精确和 DECIMAL精确计算代价高的问题。

1.1.4.4.字符串类型

MysQL支持多种字符串类型，包括VARCHAR和CHAR类型、BLOB和TEXT类型、ENUM（枚举）和SET类型。

VARCHAR和 CHAR是两种最主要的字符串类型。