数据库基础知识

最新推荐文章于 2025-06-03 11:51:41 发布

Cassandra_C

最新推荐文章于 2025-06-03 11:51:41 发布

阅读量114

点赞数 1

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.youkuaiyun.com/weixin_44038450/article/details/132395752

版权

MySQL 专栏收录该内容

2 篇文章

订阅专栏

范式化设计

范式即一张数据表的表结构所符合的某种设计标准的级别。关系数据库需要满足三范式。

第一范式

属于第一范式的所有属性都不可再分，即数据项不可分。第一范式强调数据表的原子性，是其他范式的基础。第一范式是所有关系型数据库设计的最基本要求。

第二范式

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

如下所示：

第三范式

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

满足第二范式必须先满足第一范式。满足第三范式必须先满足第二范式。

反范式化设计

在实际的业务查询中会大量存在着表的关联查询，而表设计都做成了范式化设计，那么大量的表关联很多时候会非常影响查询的性能。

反范式化设计

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

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

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

范式化和反范式化对比

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

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

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

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

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

项目中常见的反范式实现

1、缓存与汇总数据

“缓存”表示存储那些可以比较简单地从其他表获取数据的表。比如将商品的分类信息放到商品表里进行冗余存放。

“汇总”则保存的是使用GROUP BY语句聚合数据的表。如果需要显示每个用户发了多少消息，可以每次执行一个对用户发送消息进行count的子查询来计算并显示它，也可以在用户表中建一个消息发送数目的专门列，每当用户发新消息时更新这个值。

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

2、计数器表设计

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

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

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

字段数据类型优化

MySQL支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。

优化基本原则

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

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

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

3、指定列为NOT NULL，除非真的需要存储NULL值。可为NULL的列会使用更多的存储空间，还会使得索引、索引统计和值比较都更复杂。当可为NULL的列被索引时，每个索引记录需要一个额外的字节。如果计划在列上建索引，应该尽量避免设计成可为NULL的列。

整数类型

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。

实数类型

带有小数部分的数字。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精确计算代价高的问题。

字符串类型

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

VARCHAR类型用于存储可变长字符串，是最常见的字符串数据类型。它比定长类型更节省空间，因为它仅使用必要的空间（例如，越短的字符串使用越少的空间）。在内部实现上，VARCHAR需要使用1或2个额外字节记录字符串的长度，如果列的最大长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节。

CHAR类型是定长的，MySQL总是根据定义的字符串长度分配足够的空间。当存储CHAR值时，MySQL会删除所有的末尾空格，CHAR值会根据需要采用空格进行填充以方便比较。

在CHAR和VARCHAR的选择上，这些情况下适合使用VARCHAR：

字符串列的最大长度比平均长度大很多，列的更新很少；使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。

CHAR适合存储很短的字符串，或者所有值定长或都接近同一个长度。例如，CHAR非常适合存储密码的MD5值，因为这是一个定长的值。对于经常变更的数据，CHAR也比VARCHAR更好，因为定长的CHAR类型不容易产生碎片。

对于非常短的列，CHAR比VARCHAR在存储空间上也更有效率。例如用CHAR(1)来存储只有Y和N的值，如果采用单字节字符集只需要一个字节，但是VARCHAR(1)却需要两个字节，因为还有一个记录长度的额外字节。

BLOB和TEXT类型

BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。

与其他类型不同，MySQL把每个BLOB和TEXT值当作一个独立的对象处理。存储引擎在存储时通常会做特殊处理。当BLOB和TEXT值太大时，InnoDB会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要1~4个字节存储一个指针，然后在外部存储区域存储实际的值。

BLOB和TEXT家族之间仅有的不同是BLOB类型存储的是二进制数据，没有排序规则或字符集，而TEXT类型有字符集和排序规则。

使用BLOB和TEXT要慎重：

(1）BLOB和TEXT值会引起一些性能问题，所以尽量避免使用BLOB和TEXT类型；

(2）一定要用，建议把BLOB或TEXT列分离到单独的表中；

(3）在不必要的时候避免检索大型的BLOB或TEXT值。例如，SELECT*查询就不是很好的想法，除非能够确定作为约束条件的WHERE子句只会找到所需要的数据行。否则，很可能毫无目的地在网络上传输大量的值。建议可以搜索索引列，决定需要的哪些数据行，然后从符合条件的数据行中检索BLOB或TEXT值；

(4）还可以使用合成的(Synthetic)索引来提高大文本字段（BLOB或TEXT）的查询性能。简单来说，合成索引就是根据大文本字段的内容建立一个散列值，并把这个值存储在单独的数据列中，接下来就可以通过检索散列值找到数据行了。但是，要注意这种技术只能用于精确匹配的查询（散列值对于类似“<”或“>=”等范围搜索操作符是没有用处的)。可以使用MD5函数生成散列值，也可以使用SHA1(或CRC32)，或者使用自己的应用程序逻辑来计算散列值。