MySQL如何设计最优的库表结构

最新推荐文章于 2024-05-02 10:00:00 发布

nwdyd_wu

最新推荐文章于 2024-05-02 10:00:00 发布

阅读量449

点赞数

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

本文链接：https://blog.csdn.net/NWDYD_WU/article/details/114292367

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

牛客网面经：1、一张大表怎么更改表的数据结构，字段，用alter会有什么问题，怎么解决呢，有什么好的方案?

如何设计最优的库表结构

设计表结构的时候我们应该遵守哪些原则，需要考虑哪些因素？我们先不讨论索引相关内容，本文为《高性能MySQL》第4章读书笔记，并回答牛客网面经上的面试题。

选择数据类型

三个基本原则：

更小的通常更好
- 尽量使用可以正确存储数据的最小数据类型，但是要确保没有低估需要存储的值的范围，因为在schema中的多个地方增加数据类型的范围（可能不是改范围，而是改类型）是一个非常耗时和痛苦的操作。如果无法确定哪个数据类型是最好的，就选择我们认为不会超过范围的最小类型
简单就好
- 简单的数据类型的操作可以使用更少的CPU周期。例如，整型比字符操作代价低
尽量避免NULL
- 很多表都包含可为NULL的列，是因为可为NULL是列的默认属性，除非我们指定列为NOT NULL。
- 可为NULL的列是索引、索引统计和值比较都更复杂，所以查询中包含可为NULL的列时更难优化
- 将可为NULL的列改为NOT NULL带来的性能提升比较小，不用刻意修改已有表结构。但是，如果要建索引，应该尽量避免建在可为NULL的列上

先确定合适的大的数据类型范围：数字、字符串、时间等，再根据三个基本原则选择具体的数据类型。

在确定具体的数据类型时，需了解类似的数据类型之间的区别，可以参考另一篇文章：数据类型注意项

范式和反范式

1、在范式化的数据库中，每个事实数据会出现且只出现一次。相反，在反范式化的数据库中，信息是冗余的，可能回存储在多个地方。

2、范式的优缺点

优点
1.范式化的更新操作通常比反范式化要快
2.只有很少或者没有重复数据，所以只需要修改更少的数据
3.范式化的表通常更小，可以更好地放在内存中，所以执行操作会更快。
4.很少有多余的数据意味着检索列表数据时更少需要 DISTINCT 或者 GROUP BY 语句
缺点
1.通常复杂些的查询时需要关联
2.可能会使一些索引策略无效，因为不同表中的列若在一个表中本可以属于同一个索引来优化查询

3.反范式的优缺点

优点
1.很好的避免关联，如果不需要关联，则大部分查询最差情况使全表扫描（没有使用索引）。当数据比内存大时这可能比关联要快得多
2.由于冗余部分字段，可使用这些字段创建更有效的索引策略

3、混用范式化和反范式化
在实际应用中经常混用。最常见的反范式化数据的方法是复制和缓存，在不同的表中存储相同的特定列，可以使用触发器更新缓存值。另一个从 a 表冗余一些数据到 b 表的理由是排序的需要，需要用于排序的多个字段分布在不同表上，难以很好实现

缓存表和汇总表

“缓存表”表示存储那些可以比较简单地从其他表获取数据的（冗余）表；“汇总表”保存的是使用 GROUP BY 等语句聚合数据的表。

缓存表，对优化搜索和查询有效，一个有用的技巧是对缓存表使用不同的存储引擎

汇总表建立的最关键原因是实时计算统计值是很昂贵的操作（大概率扫描表中大部分数据）

使用缓存表和汇总表时，需要决定是实时维护数据还是定期重建。如果选择定期重建且需要保证数据在操作是依然可用，就需要使用“影子库”来实现。

参见应用的缓存表场景：物化视图，https://www.cnblogs.com/hkdpp/p/8302657.html

常见应用的汇总表场景：计数器表，就存在一些典型问题

创建一张独立的表存储计数器，表小且快
任何更新这个计数器数字的事务，这条记录上有一个全局的互斥锁，只能串行执行
可以将计数器的值保存在多行，每次随机选择一行进行加一，获取统计结果时使用聚合查询

加快 ALTER TABLE 操作速度

MySQL 的 ALTER TABLE 操作的性能对达标来说是个大问题。MySQL 执行大部分修改表结构操作的方法是用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表。这样可能花费很长时间，如果内存不足/表很大/很多索引的情况尤其如此。

对常见的场景，能用的技巧只有两种（工作中常用方法）：

一种是先在一台不提供服务的机器上执行 ALTER TABLE 操作，然后和提供服务的主库进行切换；
另一种技巧是“影子拷贝”，影子拷贝的技巧是用要求的表结构创建一张和源表无关的新表，然后通过重命名和删表操作交换两张表。

-- 影子库如下操作，工作中应有基础工具平台进行操作
DROP TABLE IF EXISTS table_name_new, table_name_old;
CREATE TABLE table_name_new LIKE table_name;
-- 加载原表 table_name 数据到 新表 table_name_new
RENAME TABLE table_name TO table_name_old, table_name_new TO table_name;
-- 可以通过一个原子的重命名操作切换影子表和原表

特殊场景，使用技巧（慎用，需要对MySQL的实现细节很了解）：

基本的技术就是为想要的表结构创建一个新的.frm文件，然后用它替换已经存在的那张表的.frm文件，原理如下：
不是所有的 ALTER TABLE 操作都会引起表重建。修改默认值为5:
ALTER TABLE TABLE_NAME MODIFY COLUMN COLUMN_NAME TINYINT(3) NOT NULL DEFAULT 5这种方式需要拷贝整张表到一张新表；
ALTER TABLE TABLE_NAME ALTER COLUMN COLUMN_NAME TINYINT(3) NOT NULL DEFAULT 5这种方式直接修改了.frm文件不涉及表数据拷贝，操作很快；
理论上，MySQL 可以跳过创建新表的步骤。列的默认值实际上存在于表的.frm文件中，所以可以直接修改这个文件不需要改动表本身。
另一个常用技巧是先删除所有的非唯一索引（对唯一索引无效）、然后增加新的列（载入数据），最后重新创建删除掉的索引。原因是创建索引的工作被延迟到数据完全载入以后，这个时候已经可以通过排序来构建索引了，这样会快很多。