数据库设计与优化实践-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_41896427/article/details/90142769

本文总结了传统数据库的ACID特性，介绍了分库分表的原则，包括垂直分表和水平分表，强调了数据库表设计的第三范式，并探讨了存储过程的优缺点及适用场景。此外，还讨论了索引设计、主键生成规则和外键设计的重要性，旨在提升数据库性能和数据完整性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

传统数据库的几大特性

传统数据库服务系统需要完全实现对事务的支持。

事务的四大特性（ACID）

Atomicity（原子性）：一个事务（transaction）中的所有操作，或者全部完成，或者全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。即，事务不可分割、不可约简。
Consistency（一致性）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束、触发器、级联回滚等。
Isolation（隔离性）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
Durability（持久性）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

分库原则

当应用的数据库服务器出现性能瓶颈时，我们就需要考虑对数据库进行拆分，将单个数据库中的表按照业务（或访问频率）拆分到不同的数据库中，部署到不同的数据库服务器中，来减少应用对每个数据库的访问频率，以此提高应用访问数据库的效率。这样划分需要改动应用层查询语句，适合前期设计阶段做拆分，但后期系统一旦稳定，此种拆分难度很大，尤其是涉及到事务操作，分布式事务会对应用的要求更高，且没有一个固定的解决方案，会提高应用重构的复杂程度。在系统稳定后，更多的情况下，可以采用数据库主从模式来进行替代。

分表原则

当数据库中单表的数据量越来越大，此时查询表中的记录会越来越慢，在高并发的应用下无疑是不可容忍的，此时，就需要考虑对表中的数据进行拆分，提高数据的查询效率。

垂直分表

当数据库中表的字段数过多时，由于数据库的数据存储机制，字段数太多的表会造成一个记录占用的磁盘空间更大，达到一定的阀值后，数据库会将一个记录拆分到不同磁盘扇道中存储，这样就造成查询单条记录会有更多次的IO操作，提高记录的插入与查询效率。很多查询并不是每次都需要用到记录的所有字段的，我们可以将同一个表的字段按照不同的使用频率来进行表的拆分，将不常用的字段拆分出去，放在应用层上进行联查。此种拆分往往需要改变应用层的查询数据库语句，需要对应用修改量做综合评估，改动难度较大。

水平分表

此种方式进行分表不会改变表的结构，将不同的记录分布到结构相同的几张表中。由于表结构不发生变动，尤其适用于系统已经趋于成熟稳定，应用的代码改动较小，由于数据的使用特征，很久之前的数据一般都是不常用的，所以很多情况下，我们都可以考虑直接以时间为界限对表中数据做水平拆分。

数据库表设计

为了建立冗余较小，结构合理的数据库表，我们在设计数据库表时需要遵循一定的规则，我们将这种规则叫做范式。一般情况下，我们只需要表设计满足以下的第三范式即可。

第一范式

第一范式是最基本范式，只要满足表中所有字段都是不可再分的原子变量，即满足了第一范式。

第二范式

第二范式是在第一范式的基础上，满足所有表都有一个主键，并且一条记录中非主键列必须完全依赖于主键，不能依赖于主键的部分（主要是针对于联合主键）。

第三范式

第三范式是在第二范式的基础上，表中的所有字段都不应该除了与主键外的其他列有依赖，比如，通过非主键A列可以计算出B列的值，达到这种要求的表字段设计，即满足了数据库表设计的第三范式。

表字段冗余设计

在数据库表字段不是特别多的时候，我们可以将某些需要连表查询的字段冗余到某张表中，减少连表查询操作。

字段设计

数据库表的字段设计，主要需要考虑表字段的存储类型，存储长度等因素。

自增量设计

数据库表设计增量字段，一般实现是获取到当前数据库该字段的最大值，然后插入新值时将该值自增插入，会存在相关性能问题，在数据库迁移或者导入数据的时候自增量字段有可能会出现重复，因此，自增字段很少出现，如果是要记录数量，一般情况下可以采用PinelineDB实现。

主键生成规则

单数据库的情况下，如果不需要主键具有具体含义时，可以采用UUID的方式生成主键，一般不会重复，可以唯一标识一条记录；集群情况下，一般情况下将主键的生成规则放入到应用中去做，可以采用如snowflake等算法。

外键设计

通过合理的外键设计，可以用来保证业务数据的完整性，可以限制某些脏数据的插入，但是数据库中维护外键操作，需要额外的性能开销，插入的时候效率也会有所降低，外键设置的不合理，会造成数据库维护工作难以开展。所以一般情况下，我们可以采用创建索引的方式替代数据库的外键。将维护业务数据完整性的操作委托到上层应用里面自己完成。

索引设计

一个满足第一范式的数据库表必然存在主键，数据库中，会为主键默认创建主键索引，在应用中，如果经常用到某列作为查询条件，并且该列数据重复的情况不是很多时（由于索引采用最左前缀匹配原则查找数据），我们可以为该列创建索引，来提高我们的查询效率。但是索引也不是创建越多越好，索引会维护自己的存储结构到磁盘中，占用额外的存储资源，并且插入时，会同时将新增的数据插入到索引结构中，造成数据的插入速度较慢。所以索引的量需要综合衡量，在一个合适的数值范围内。