深入理解Mysql——schema设计与大表alter操作

最新推荐文章于 2025-06-13 20:21:45 发布

原创最新推荐文章于 2025-06-13 20:21:45 发布 · 8.3k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #schema #数据 #设计

mysql 专栏收录该内容

4 篇文章

订阅专栏

文章目录

数据类型
范式设计
- 范式设计的优缺点
- 反范式设计的优缺点
大表的Alter操作

数据类型

整数与小数类型

整数类型有如下几种：
TINYINT(1个字节),SMALLINT(2个字节),MEDIUMINT(3个字节),INT(4个字节),BIGINT(6个字节)。

小数（实数）类型有如下几种：
FLOAT4个字节)、DOUBLE(8个字节)、DECIMAL(占用字节更多)。

**DECIMAL(n,m)**表示仅允许保留m位小数；n表示小数点左右两边数字总和。也就是说只允许n-m个整数位。

字符串类型

varchar是使用最频繁的字符串类型，它除了维护字符串还维护了一个或者两个字节用来记录长度。varchar(n)，n代表单个字符的个数

当我们设置varchar(5)或者varchar(100)来存储 “mysql"，哪个会更好呢？当然是varchar(5)更好，因为varchar(100)即使之存储5个字符的字符串，也会分配100个字符的内存空间，特别是当使用临时表会更不好。

char用来存储较小的字符串 char(n)代表可以存储n个字符的字符串。

blob 与text
这两种类型是用来存储较大字符串的类型，与其他类型不同，它们的排序只按照最前边max_sort_length个字符进行排序，而不是按照全部字符排序。因此如果只需要按照较小的字符排序，可以缩小max_sort_length的设定。或者使用 order by substring( column，length)进行排序。

enum
用时可以使用 enum(“string1”,“string2”,“string3”)代替varchar\char等字符串类型。

enum类型对字符，实际是使用整数进行存储的，并且当对enum列排序也是按照实际存储的整数值排序。适合存储如有序的状态，性别等等。

比如，我们新增一个enum类型的列，并按照主键id依次顺序插入：


ALTER TABLE USER ADD  enumType ENUM("apple","banana","orange");


ALTER TABLE USER ADD  enumType ENUM("apple","banana","orange");


UPDATE `user`.`user` SET `enumType` = 'apple' WHERE id=1;
UPDATE `user`.`user` SET `enumType` = 'banana' WHERE id=2;
UPDATE `user`.`user` SET `enumType` = 'orange' WHERE id=3;

SELECT id,(enumType+0) AS a FROM USER ;

OUTPUT:
id	enumType
1	1
2	2
3	3

可以看到，插入的 “apple”,“banana”,"orange"分别使用 1，2, 3进行存储，也就是按照enum设定的顺序。当然对这个enum列的排序也是按照数字排序。

注意：因此我们在使用enum类型的时候，切记不要存储如 enum(“10”,“29”,“42”,“99”)这有的字符串类型的数字，以避免产生不必要的混淆！

enum类型
优点：更紧凑的存储，更加节省空间；
不足：当enum类型的列与 varchar类型的列进行关联时比两个varchar进行关联慢一些。

日期与时间

DATETIME
datetime 用来表示日期，范围从1001年到9999年，精确到秒:YYYYMMDDHHMMSS，占用8个字节。这种类型与时区无关。

TIMESTAMP
从1970.1.1以来的描述，跟UNIX时间戳相同。
占用4个字节，也是精确到秒，但是范围要比DateTime小很多，1970~2038。
另外，这种类型默认not null，首次插入会自动用当前时间填充。

FROM_UNIXTIME()可以把时间戳转为日期。
UNIX_TIMESTAMP()可以把日期转为时间戳。

对于比秒更小的粒度：目前暂无支持，可以使用bigint存储更小的时间戳。

数据类型优化技巧
1、数据类型更小通常更好，数据类型越简单越好
2、数值操作比字符操作快，小类型的处理速度比大类型快
3、尽量避免使用NULL，要是有必要用NULL，那也可考虑使用0来进行代替
4、IP用int存：inet_aton()、inet_ntoa()

范式设计

第一范式：要求有主键，并且要求每一个字段原子性不可再分
第二范式：要求所有非主键字段完全依赖主键，不能产生部分依赖
第三范式：所有非主键字段和主键字段之间不能产生传递依赖

第一范式：要求有主键，并且要求每一个字段原子性不可再分

第一范式很好理解，首先要确保每一行都有主键存在，另外每一列都要有不可拆分的业务对象。

-- -违反范式一 userContact 中是“手机号，qq号”
"cid"	"userContact"
"2"		"15472152456,502327342"
"3"		"13676574456,7702322"

修改为：

"cid"	"phone"	"qqNumber"
"1"		"15472152456"	"502327342"

第二范式：要求所有非主键字段完全依赖主键，不能产生部分依赖
即满足第一范式前提，当存在多个主键的时候，才会发生不符合第二范式的情况。比如有两个主键，不能存在这样的属性，它只依赖于其中一个主键，这就是不符合第二范式。通俗理解是任意一个字段都只依赖表中的同一个字段。（涉及到表的拆分）

第三范式：所有非主键字段和主键字段之间不能产生传递依赖
请看下边的例子：

"cid"	"role"				"phone"			"qqNumber"			"level"
"1"		"programmer"		"15472152456"	"502327342"			"6"
"2"		"manager"			"15372152456"	"534232"			"2"
"3"		"manager"			"15372152436"	"5023272"			"3"

这个设计就违反了第二范式，因为level字段依赖于role，role依赖于cid：
cid 为1的员工 programmer 角色级别为6;
cid 为2的员工 manager级别级别为2；
cid 为3的员工 manager级别为3

-- - 符合第二范式：

"cid"	"phone"			"qqNumber"
"1"		"15472152456"	"502327342"
"2"		"15372152456"	"534232"
"3"		"15372152436"	"5023272"

"cid"	"role"			"level"
"2"		"manager"		"3"
"3"		"manager"		"2"
"1"		"programmer"	"6"

换个例子，比如一个表中有年龄\是否未成年；这两个字段明显存在依赖。

范式设计的优缺点

优点：
1.拆分的更细，冗余数据更少
2.占用空间更小，可以更好的放入内存，操作更快

缺点：
会导致查询需要频繁使用多表关联,会导致执行效率降低、并且有些索引无法很好执行。

反范式设计的优缺点

优点：
把所有业务相关的字段尽可能的放入一张表中，避免多表关联查询。
单表可以更好的使用索引，有时候执行效率更高！
缺点：
更多的冗余数据，导致更新或者删除缓慢。

反范式常用的冗余设计

基于静态变更频率不高数据的冗余
静态数据指相关字段变更频率不高，如用户资料，可作冗余字段
以消息表为例
Fuid，Tuid，Msg，Dateline
范式化设计后，在内容呈现上可能需要涉及多表关联，因为用户想知道发消息的人是谁？
那么，可增加冗余字段：Funame
基于谓词的冗余
有些where连接条件很热门，一个谓词为多个SQL服务，那么，我们可以把这些热门的谓词收集，集中存在到一张冗余表
以用户登录为例子，假设有上亿条用户资料并做了分表处理，用户只关心uname，根据uname需要到各个分表去查询uid，此时，我们可按：uname，uid建立冗余表
基于聚会函数的冗余
频繁的count，max，sum等需求，例如，论坛今日发帖总量，可建立冗余字段Spost
基于排序的冗余
索引用来加速查询或者已经是件家喻户晓的事，但还有一个优势，即：降低排序成本。
仍然以消息表为例，若我们按uname对消息做排序的代价估计会蛮高的，但如果在消息表里把uname冗余并且建立好索引，则可以借助索引来抵消排序

大表的Alter操作

之前处理过多个千万级别的表的alter操作，竟然跑脚本跑了一两天才跑完。下面总结一下Alter大表的几种方式：

现在一个不提供服务的数据库表中进行执行alter脚本，执行完毕后与提供服务的主库进行切换。当然要最大限度保证这两个库的数据是同步的。
影子拷贝：通过重命名替换表。

创建一个张影子表，新增需求字段，同步数据，同步一致后通过原子性的重命名操作替换两个表。

-- step1
CREATE TABLE User_new LIKE USER;
-- step2
alter table User_new add column;//增加字段
--step3
同步数据，直到一致
-- step4 原子替换
RENAME TABLE USER TO User_old ,User_new TO USER