mysql数据类型选择与优化 整理自高性能Mysql 绝对干货

mysql数据类型选择与优化

数据类型选择与优化

数据库数据类型选择原则

1. 更小的通常更好，尽量使用可以正确存储数据的最小类型，例如age字段，tinyint unsigned 是更好的选择
2. 简单就好，能用整型不用字符串，用datetime、date等内建类型存储日期时间，用整型存储ip地址等 INET_ATON INET_NTOA函数可完成ip地址与32位无符号整数的转换
3. 尽量避免NULL，尤其是在准备建立索引的列上

数据类型

整型

类型	占用空间(bit)	无符号	有符号
TINYINT	8	0~255	-128~127
SMALLINT	16	0~65535	-32768~32767
MEDIUMINT	24	0~16777215	-8388608~8388607
INT	32	2^32-1	-231~231-1
BIGINT	64	2^64-1	-263~263-1

整型的位宽是没有实际意义的，int(1)与int(11)存储范围一致，只是针对某些客户端展示的字符数不同而已

实数类型

DECIMAL

DOUBLE

FLOAT

DECIMAL是高精度计算，依然不建议使用实数，可以通过整型提高倍数的方式来实现精确计算

字符串类型

VARCHAR 变长字符串，如果长度大于255，那么需要2个字节来记录长度，

回忆一下byte与bit 1byte = 8bit，所以一个字节最多记录2^8 - 1(255)的长度，2字节是65535

使用VARCHAR也要遵循第一原则，够用就好，更长的列会消耗更多的内存，只分配真正需要的空间！

CHAR 类型适合存储定长的数据，如MD5加密后的密码

TEXT 与 BLOB 尽量不要使用

字符编码永远使用utf8mb4而不是utf8

枚举类型

ENUM

可以使用枚举类型代替常用的字符串，枚举类型会在数据库文件中映射"数字-字符串"的集合，实际存储的是整数！

切记，排序时使用枚举列，是按照内部存储的整数而不是字符串进行排序的

可以用FIELD()函数显式的指定排序，这会无法使用索引

使用ENUM可以减少空间占用

日期与时间类型

timestamp所能存储的时间范围为：‘1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’

datetime所能存储的时间范围为：‘1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’

timestamp类型与当前会话的时区有关，根据时区不同，输出不同，datetime则是原样输出

通常timestamp效率要高于datetime，而使用整数时间戳不会带来任何收益，格式转换还更麻烦

注意schema设计中的陷阱

• 太多的列
• 全能的枚举 ENUM(‘0’,‘1’,‘2’…‘31’) 数字指向字符串数字，太混乱
• 某些特殊的值可能会带来更严重的问题比如 ‘0000-00-00 00:00:00’，此时应使用NULL

缓存表、汇总表

缓存表 存储某些查询耗时的冗余数据

汇总表 存储GROUP BY等语句聚合计算的非冗余数据

影子表操作

在重建缓存表或者汇总表时，可以通过RENAME的原子操作完成不影响服务的表更换

DROP TABLE IF exists `table_cache_old`,`table_cache_new`;
CREATE TABLE `table_cache_new` like `table_cache`;
RENAME table `table_cache` to `table_cache_old`, `table_cache_new` to `table_cache`;

计数器表

假设有个记录网站点击次数的表，只有一行记录总次数，频繁更新，更新是串行化的操作，要获得更高的性能我们可以插入100条记录，每次随机更新其中的一条，统计的时候使用SUM聚合，可以有效的防止因为写锁带来的并发问题。

update count_table set clicks  = clicks + 1 where line_num = rand() * 100;
# 从100行中随机修改一行

总结

• 尽量避免过度设计，避免太多的列
• 使用小而简单的合适数据类型，尽量避免使用NULL值
• 尽量使用相同的数据类型存储相似或相关的值，尤其是在关联条件中使用的列
• 注意可边长字符串，在临时表和排序时，可能导致悲观的按最大长度分配内存
• 尽量使用整型定义标识列