《高性能Mysql》学习笔记（2） - Schema与数据类型优化

1 如何选择更合适的数据类型

  • 更小的通常更好：
    • 一般情况下，应该选择可以正确存储数据的最小数据类型，因为他们占用更少的磁盘，内存和CPU缓存，处理时候需要的CPU周期也更少。
    • 但是要确保没有低估存值的范围
  • 简单更好
    • 比如整数比字符操作代价更低，整数更适合做比较操作，这就涉及修改数据时索引插入这些
    • IP地址应该用整数存储，因为其本质是32/64位 的无符号数
  • 尽量避免使用NULL
    • NULL会影响执行计划选择
      • 所有null值会被视作相等，当null值过多，基数下降，会被优化器判断不适合走索引 统计信息不准 → 慢查询
    • NULL值的索引会更复杂
      • 索引同样会存储NULL值，通常按最小的处理
    • NULL值字段需要更多的存储空间
      • 例如compact行格式记录头信息包含变长字段长度列表、null值列表（1字节）和记录头信息。所以NULL的列需要更多空间。 NULL值一样会进入索引，这样就会导致索引变大

选择数据类型：

  1. 先确定合适的大类型：数字，字符串,时间
  2. 选择具体类型，相同大类型的不同子类型数据有时也有一些特殊行为和属性
    • 例如DATETIME 和 TIMESTAMP列都可以存储时间和日期，精确到秒，但是TIMESTAMP只需要DATETIME一半的存储空间，并且会根据时区变化，另一方面，TIMESTAMP允许的时间范围要小很多

2 数字型

主要分为整数和实数

2.1 整数

TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT 分别占用1，2，3，4，8个字节

整数类型有可选的UNSIGNED属性，表示不允许赋值，这使得整数存储正数上限提高一倍。

类型的选择决定MYSQL是怎么在内存和磁盘种存储数据的，但是，整数的计算一般使用64为的BIGINT整数

2.2 实数类型

实数是带有小数部分的数字，也可以用DECIMAL存储比BIGINT还大的整数

  • DECIAMAL:适用于需要精确计算小数运算的场景。高精度运算是在MYSQL自身实现的
    • 因为需要额外的空间和计算开销，所有应该尽量只在对小数进行准确计算时才使用DECIMAL。
  • FLOAT：4字节
  • DOUBLE：8字节

浮点数和DECIMAL类型都可以指定精度，如DECIMAL(18,9)指定了小数后存储9位小数

2.3 字符串类型

2.3.1 VARCHAR和CHAR类型

VARCHAR和CHAR是最主要的字符串类型

  • VARCHAR
    • 用于存储可变长字符串，比定长更节省空间，因为它只用必要的空间。但是如果行格式用FIXED，每一行都会定行存储
    • VARCHAR需要使用1或2个额外字节记录字符串的长度
    • 因为VARCHAR是长度可变的，在更新操作的时候就可能发生字符串长度变化的问题，对Innodb来说，就更可能导致页分裂，影响插入效率，
    • VARCHAR在存储和检索的时候会保留末尾的空格
    • 在Innodb，过长的VARCHAR会被存储为BLOB
    • 虽然在磁盘上varchar（5）和varchar（200） 存储‘Hello’ 是一样的，但是在内存上，mysql会分配固定大小内存块来保留内布置，更长的列会消耗更多内存。
  • CHAR
    • CHAR类型是定长的，会根据字符串长度分配固定空间
    • CHAR字符存储时会删掉末尾所有的空格。
    • CHAR时候存储很短的字符串，或者所有值都接近一个长度，列如密码的MD5值
    • 对于经常变更的数据，CHAR也比VARCHAR更好，定长的VARCHAR在更新数据时不容易产生碎片。
    • 当存储较短字符时，CHAR也比VARCHAR更好，因为VARCHAR还需要额外空间记录字符长度

与CHAR和VARCHAR类似的还有BINARY和VARBINARY，他们存储的是二进制字符串。

2.3.2 BLOB和TEXT类型

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

二者都不能用全部字段进行索引，这就涉及了前缀索引。

实际上，他们分别属于不同的数据类型家族

字符型是TINYTEXT,SMALLTEXT,TEXT,MEDIUMTEXT,LONGTEXT （有字符集和排序规则）

对应的二进制类型是 TINYBLOB,SMALLBLOB,BLOB,MEDIUMBLOB,LONGBLOB。（无字符集和排序规则）

与其他类型不同，mysql会把blob和text当作一个独立的对象处理。存储引擎在存储时候会特殊处理，当BLOB/TEXT过大时，innodb会使用专门的”外部”存储区域来存储，此时每个值需要在行内用1-4字节存储一个指针，在外部存储实际值。

2.4 枚举类型

枚举类型可以代替字符串类型，枚举列可以把一些不重复的字符串存储成一个预定义的集合。并且在.frm文件种保存 "数字 -- 字符串"映射关系的查询表 也就是说，枚举类型在存储中实际是整数,数字的大小是根据枚举列表的顺序。

枚举的优点：

  • 枚举类型和枚举类型相关联时，既能在逻辑上相关，在比较上，由于是整数比较，效率也很高
  • 枚举类型底层存放整数，使得表的内存更小

枚举类型的缺点在于

  • 修改枚举的字段消耗很高，添加或者删除字符串必须使用ALTER TABLE。
  • 枚举在查找的时候有格外的开销，因为存储是整数，需要先查找映射关系找到字符串。 所以枚举类型和CHAR/VARCHAR关联比起 CHAR/VARCHAR和CHAR/VARCHAR关联更慢。

2.5 日期和时间类型

  • DATETIME
    • 能保存大范围的值，从1001年到9999年，精度为秒。把日期和时间封装到YYYYMMDDHHMMSS中，与时区无关。使用8字节存储空间。
  • TIMESTAMP
    • 存储从1970年1月1日以来的秒数。只使用4字节存储空间，会随着时区的变化而变化。
    • 新插入TIMESTAMP值的时候，会更新所有的TIMESTAMP值。
    • 尽量使用TIMESTAMP，因为其空间收益更高

2.6 位数据类型

位数据类型从技术来说还是字符串类型

  1. BIT
    • 可以用来存储一个或者多个true/false，最大长度是64 位
    • MYSQL把BIT当成字符串类型，直接检索BIT列，会通过ASCII码转化成字符，但是在数字上下文的场景检索时，会被转化成数字
    • 谨慎使用
  2. SET
    • SET在mysql内部是以一系列的打包的位的集合来实现的。SET被视作字符串集合
    • 代价是类似枚举，修改需要ALTER TABLE
    • 对比ENUM
      • SET是多选，Enum是单选
      • SET需要用Find_IN_SET方法查找
      • 两者都是修改高成本字段，需要alter table来修改

3. 数据库设计中的陷阱

  • 太多的列

    • 存储引擎和服务层交互的逻辑：
      • 存储引擎会将自身格式的行数据（行格式），封装成统一的【行缓冲（Row Buffer）】传递给服务层，服务层接收后，会将 Row Buffer 解码为服务层可处理的标准行格式，并可缓存起来供重复使用。
    • 如果列太多了，行缓冲的转化成本就会很高，影响查询。

  • 太多关联

  • 防止过度使用枚举

    • 枚举列修改成本较高

  • 改用NULL的时候就用NULL

    • 该用null就别去不用null，非要扣null不好的地方，你比起索引和内存上的复杂，用特殊值代替null出问题了你就老实了。
      • 比如你时期类型，空数据编成'0000-00-00 00:00:00' 肯定会有问题的

4. 缓存表和汇总表

在同一张表中保存衍生的冗余数据，有时候也是一种不错的提升性能的办法

  • 缓存表
    • 缓存表表示存储那些可以比较简单从数据库其他表获取，但是每次获取速度较慢的数据的表，例如逻辑上冗余的数据。
  • 汇总表
    • 保存一些group by聚合数据的表
      • 例如想计算24小时发送的信息数，直接全表扫描效率很低，可以通过汇总表，每小时记录当前这一小时的消息数，最后累加

5. 计数器表

如果想在表中保存计数器时，可以使用计数表。如下

CREATE TABLE hit_counter (
	cnt int unsigned not null
) ENGINE=Innodb

针对高并发的场景，还可以建立多个累加单元，最后相加

CREATE TABLE hit_counter (
	cnt int unsigned not null,
	slot tinyint unsigned not null primary key,
) ENGINE=Innodb

6. 加快ALTER TABLE 操作速度

通常ALTER TABLE 会导致mysql服务中断

技巧

  • 可以先在一台不提供服务的机器执行alter table，在和主库进行切换
  • 影子拷贝：用相同表结构创建一张和源表无关的新表，再通过重命名和删表操作交换

大部分修改表结构的操作都是用新结构创建一个空表，然后从旧表中导入数据，最后删除旧表。

优化：

  • 不是所有ALTER TABLE都会引起表重建，比如MODIFY COLUMN 就会，但是换成alter column有时候也能实现一样的效果(比如列的默认值，实际上是存储在.frm文件上，所以可以直接修改这个文件而不是表本身)，而且这种修改只是去修改.frm文件，不会引起表重建

    • alter column只能修改列非空/默认值，modify column能做很多事情，alter column的优点就是更轻量

  • 还可以直接修改.frm

    • 创建一张相同结构的空表，并进行修改
    • 执行FLUSH TABLES WITH READ LOCK.
    • 交换.frm文件
    • 释放锁
posted @ 2026-05-01 18:27  不会coding的喵酱  阅读(11)  评论(0)    收藏  举报