关键词数据库范式, MySQL索引, B树, 反范式, 性能优化你是否在面试中被问到为什么数据库要设计范式或者B树相比B树有什么优势这些问题看似简单但要想回答得深入浅出、条理清晰还需要系统的知识体系。本文将从第一范式到第三范式从理论到实践带你全面掌握数据库设计的核心要点。目录数据库范式化设计什么是范式第一范式1NF第二范式2NF第三范式3NF反范式化设计什么是反范式范式与反范式对比项目中常见的反范式实现缓存与汇总数据计数器表设计字段数据类型优化字段优化基本原则整数类型选择字符串类型选择数据库命名规范MySQL索引与B树原理索引的本质B树详解为什么选择B树1. 数据库范式化设计1.1 什么是范式范式Normal Form简称NF可以理解为一张数据表的表结构所符合的某种设计标准的级别。就像家里装修买建材最环保的是E0级其次是E1级还有E2级等等。目前关系数据库有六种范式第一范式1NF第二范式2NF第三范式3NF巴斯-科德范式BCNF第四范式4NF第五范式5NF又称完美范式满足最低要求的范式是第一范式1NF在第一范式的基础上进一步满足更多规范要求的称为第二范式2NF其余范式以此类推。一般来说数据库只需满足**第三范式3NF**就够了。1.2 第一范式1NF定义属于第一范式关系的所有属性都不可再分即数据项不可分。第一范式强调数据表的原子性是其他范式的基础。例如一张表有一个name-age列这个列具有两个属性一个name一个age所以不符合第一范式。我们把它拆分成两列name和age这张表就符合第一范式关系。第一范式的详细要求每一列属性都是不可再分的属性值确保每一列的原子性两列的属性相近或相似或一样尽量合并属性一样的列确保不产生冗余数据单一属性的列为基本数据类型构成设计出来的表都是简单的二维表1.3 第二范式2NF第二范式2NF是在第一范式1NF的基础上建立起来的即满足第二范式必须先满足第一范式。核心要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。如果存在那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体新实体与原实体之间是一对多的关系。典型案例订单明细表中如果有订单ID和产品ID作为联合主键那么产品名称只依赖于产品ID而不是联合主键这就违反了2NF。解决方案将产品信息拆分到单独的产品表中订单明细表只保留产品ID作为外键。1.4 第三范式3NF满足第三范式必须先满足第二范式。核心要求一个数据库表中不包含已在其它表中包含的非主关键字信息即数据不能存在传递依赖关系。传递依赖示例订单表中存储了产品ID和产品名称产品名称依赖于产品ID产品ID依赖于订单ID这就是传递依赖。解决方案将产品名称移到产品表中订单表只保留产品ID。2. 反范式化设计2.1 什么是反范式完全符合范式化的设计真的完美无缺吗很明显在实际的业务查询中会大量存在着表的关联查询而表设计都做成了范式化设计大量的表关联很多时候非常影响查询的性能。反范式化就是违反范式化设计为了性能和读取效率而适当的违反对数据库设计范式的要求为了查询的性能允许存在部分少量冗余数据换句话来说反范式化就是使用空间来换取时间。2.2 范式与反范式对比维度范式化反范式化更新操作通常更快字段较少相对较慢数据冗余很少或没有重复数据允许适当冗余存储空间表通常更小占用更多空间查询性能需要关联性能较低减少关联性能较高适用场景写多读少读多写少设计原则范式化和反范式化各有优劣小孩子才做选择我们全都要在实际项目中需要根据业务特点灵活运用。3. 项目中常见的反范式实现3.1 缓存与汇总数据缓存表示存储那些可以比较简单地从其他表获取数据的表。比如从父表冗余一些数据到子表。前面我们看到的分类信息放到商品表里面进行冗余存放就是典型的例子。汇总保存的是使用GROUP BY语句聚合数据的表。如果需要显示每个用户发了多少消息可以每次执行一个对用户发送消息进行count的子查询来计算并显示它也可以在user表中建一个消息发送数目的专门列每当用户发新消息时更新这个值。维护策略实时维护缓存表用实时维护数据更多点往往在一个事务中同时更新数据本表和缓存表定期重建汇总表则用定期重建更多使用定时任务对汇总表进行更新3.2 计数器表设计计数器表在Web应用中很常见比如网站点击数、用户的朋友数、文件下载次数等。问题场景如果有一个计数器表只有一行数据记录网站的点击次数每次点击都会导致对计数器进行更新。问题在于对于任何想要更新这一行的事务来说这条记录上都有一个全局的互斥锁mutex。这会使得这些事务只能串行执行严重限制系统的并发能力。解决方案可以将计数器保存在多行中每次随机选择一行进行更新。-- 创建计数器表增加slot字段CREATETABLEhit_counter(slotINTUNSIGNEDNOTNULLPRIMARYKEY,cntINTUNSIGNEDNOTNULL);-- 预先插入100行数据INSERTINTOhit_counter(slot,cnt)VALUES(0,0),(1,0),(2,0),...,(99,0);-- 更新时随机选择slotUPDATEhit_counterSETcntcnt1WHEREslotFLOOR(RAND()*100);-- 查询时求和SELECTSUM(cnt)FROMhit_counter;4. 字段数据类型优化MySQL支持的数据类型非常多选择正确的数据类型对于获得高性能至关重要。4.1 字段优化基本原则原则1更小的通常更好一般情况下应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快因为它们占用更少的磁盘、内存和CPU缓存并且处理时需要的CPU周期也更少。示例如果有一个类型既可以用字符串也可以使用整型优先选择整型。因为字符串牵涉到了字符集及校对规则等。原则2简单就好简单数据类型的操作通常需要更少的CPU周期。例如整型比字符操作代价更低因为字符集和校对规则使字符比较比整型比较更复杂。原则3尽量避免NULL通常情况下最好指定列为NOT NULL除非真的需要存储NULL值。可为NULL的列使得索引、索引统计和值比较都更复杂可为NULL的列会使用更多的存储空间可为NULL的列被索引时每个索引记录需要一个额外的字节4.2 整数类型选择MySQL支持多种整数类型类型存储空间有符号范围无符号范围TINYINT1字节-128 ~ 1270 ~ 255SMALLINT2字节-32768 ~ 327670 ~ 65535MEDIUMINT3字节-8388608 ~ 83886070 ~ 16777215INT4字节-21亿 ~ 21亿0 ~ 43亿BIGINT8字节极大范围极大范围注意整数类型有可选的UNSIGNED属性表示不允许负值可以使正数的上限提高一倍INT(11)只是规定了MySQL交互工具用来显示字符的个数对存储和计算来说INT(1)和INT(20)是相同的4.3 字符串类型选择MySQL支持多种字符串类型包括VARCHAR和CHAR类型、BLOB和TEXT类型、ENUM枚举和SET类型。VARCHAR vs CHARVARCHAR用于存储可变长字符串仅使用必要的空间使用1或2个额外字节记录字符串长度适合字符串列的最大长度比平均长度大很多列的更新很少CHAR定长字符串根据定义的字符串长度分配足够的空间存储时会删除所有末尾空格适合很短的字符串或所有值定长/接近同一个长度选择建议对于经常变更的数据CHAR比VARCHAR更好因为定长的CHAR类型不容易产生碎片对于非常短的列如Y/NCHAR比VARCHAR在存储空间上更有效率5. 数据库命名规范在面试过程中涉及到设计表的时候如果命名不规范必定是一个很大的扣分项。以下是常见的命名规范5.1 可读性原则数据库、表、字段的命名要遵守可读性原则尽可能少使用或者不使用缩写表达是与否概念的字段应该使用is_xxx的方式命名数据类型是unsigned tinyint1表示是0表示否5.2 命名规则表名、字段名必须使用小写字母或数字禁止出现数字开头表名不使用复数名词数据库、表、字段的命名禁用保留字如desc、range、match等主键索引名为pk_字段名唯一索引名为uk_字段名普通索引名则为idx_字段名5.3 库名规范库名与应用名称尽量一致表名遵循业务名称_表的作用的格式6. MySQL索引与B树原理6.1 索引的本质MySQL官方对索引的定义为索引Index是帮助MySQL高效获取数据的数据结构。InnoDB存储引擎支持以下几种常见的索引B树索引最常用、最关键全文索引哈希索引为什么HashMap不适合做数据库索引hash表只能匹配是否相等不能实现范围查找当需要按照索引进行order by时hash值没办法支持排序组合索引可以支持部分索引查询hash表没办法支持部分索引当数据量很大时hash冲突的概率也会非常大6.2 B树详解从二分查找到二叉树二分查找法binary search也称为折半查找法用来查找一组有序的记录数组中的某一记录。例如在数组[5, 16, 39, 45, 51, 98, 100, 202, 226, 321]中查找数字48只需要3次二分查找就能找到而顺序查找需要8次。二叉查找树二叉查找树的特点左子树的所有值小于根节点的值右子树的所有值大于或等于根节点的值左、右子树满足以上两点问题如果设计不良二叉查找树完全可以变成一颗极不平衡的树退化成链表查询效率变为O(n)。平衡二叉树AVL树平衡二叉树AVL树是一棵二叉排序树它的左右两个子树的高度差平衡因子的绝对值不超过1并且左右两个子树都是一棵平衡二叉树。问题维护一棵平衡二叉树的代价非常大需要1次或多次的左旋和右旋来保持平衡。B树的特征B树是从平衡二叉查找树演化而来但B树不是二叉树而是一个多叉查找平衡树。B树的特征相同节点数量的情况下B树高度远低于平衡二叉树非叶子节点只保存索引信息和下一层节点的指针信息不保存实际数据记录每个叶子页LeafPage存储实际的数据叶子节点由小到大有序串联在一起相邻的叶子节点之间用指针相连MySQL中是双向链表6.3 为什么选择B树B树 vs B树B树每个节点都存储数据B树数据只存在叶子节点上非叶子节点只存索引MySQL选择B树的原因IO次数更少B树每个节点都存储数据每次查询返回的数据条数变少B树非叶子节点只存索引一次可以返回多条记录IO次数较少范围查询更优B树的叶子节点形成有序链表范围查询时只需遍历叶子节点B树需要中序遍历整棵树磁盘顺序读写InnoDB默认B树节点大小是16KB充分利用磁盘顺序IO的高速读写特性叶子节点双向链表结构相邻节点物理上也可能相邻减少磁盘寻道时间磁盘读写效率对比顺序读效率是随机读的40到400倍顺序写是随机写的10到100倍MySQL优化的一大方向尽可能让数据顺序读写少让数据随机读写。总结本文从数据库范式化设计出发详细介绍了三大范式第一范式原子性、第二范式完全依赖、第三范式消除传递依赖以及实际工作中的反范式化设计思想字段优化选择合适的数据类型遵循更小、更简单、避免NULL的原则命名规范小写字母、可读性、避免保留字等面试必考点索引原理B树的结构特点以及为什么MySQL选择B树而不是B树或HashMap核心要点范式化适合写多读少的场景减少数据冗余保证数据一致性反范式化适合读多写少的场景用空间换时间减少关联查询B树通过多叉结构降低树高通过叶子节点链表优化范围查询通过顺序IO提升磁盘读写效率希望这篇文章能帮助你在面试中游刃有余地应对数据库相关问题如果觉得有帮助欢迎点赞、收藏、关注推荐标签数据库MySQL索引B树范式面试性能优化