选择合适的数据类型
不使用null,使用null会使得值比较或者排序更加复杂,并且需要更多的空间
尽量使用数字(比较时候简单)
更小的数据类型
tinyint(1字节),smallint(2字节),mediumint(3字节),int(4字节),bigint(8字节)
存储范围为-2(N-1) 到2^(N-1)-1
使用unsigned,是0到2(N-1)-1
有符号和无符号类型使用相同的存储空间,并且有相同的性能,因此应该根据范围选择类型。
Decimal(M,D)用于存储精确的小数。它常用于财务数据、统计数据和其他需要高精度的场合。
DECIMAL(10, 2)表示一个最多有10位数字的小数,其中有2位在小数点后。
- 精度和范围:确保M和D的值能满足你的需求,避免数据溢出。
- 四舍五入:MySQL会对插入的DECIMAL值进行四舍五入。如果插入的值精度超过了定义的D,将会被四舍五入到最接近的值。
- 性能:DECIMAL的计算比整数和浮点数慢,但它提供了更高的精度。
MySQL在进行计算时会对不同类型的数据进行转换,以确保精度和正确性。具体来说:
- 整数类型:在计算过程中,所有的整数类型(如INT、SMALLINT、TINYINT等)都会被转换为BIGINT类型。这确保了即使是大范围的整数运算也能得到正确的结果。
- 实数类型:在计算过程中,所有的实数类型(如FLOAT和DOUBLE)都会被转换为DOUBLE类型。这确保了浮点运算的精度和范围。
- DECIMAL类型:在计算过程中,DECIMAL类型会保持其定义的精度进行计算。这确保了高精度的十进制运算,尤其是在金融和其他对精度要求高的场合。
VARCHAR和CHAR
char和varchar都需要规定存储的字符集和该字符集的排序规则。
默认使用的是utf8mb4
索引长度:由于 utf8mb4 字符集使用最多四个字节存储每个字符,因此索引的长度限制也会增加。MySQL 索引长度限制是 767 字节,对于 utf8mb4,这意味着最多可以索引 191 个字符(767 / 4)。
存储空间:utf8mb4 相比 utf8 可能会占用更多的存储空间,因为它需要最多四个字节来存储一个字符。
varchar长度更新可能会导致跨页存储,此时一行数据需要跨页读取io,性能不友好。
varchar需要1-2个字节空间去存储varchar的实际大小。
varchar(10)和char(10)中的10都是规定的最大字符数量,如果使用的是utf8mb4,一个字符的大小是1-4字节,会在插入时开辟空间。
char未存储的空间用空格填充,所以char存储的尾空格会删除,而varchar不会。
FLOAT 4字节 和DOUBLE 8字节
如果是财务,使用Decimal或者乘以百万,然后存储为bigint
BINARY和VARBINARY()
存储二进制数据,是按照字节存储的。
binary(10)代表存储10个字节,而不是字符。
binary填充用的是/0,而不是空格,所以不影响空格存储。
当比较排序时,使用的是字节比较。
BLOB和TEXT
存储大数据,分别采用二进制和字符方式。
两者的区别是:TEXT有字符集和排序规则,而BLOB是字节二进制存储。
与其他数据类型不同,MySql把每一个BLOB和TEXT当作一个具有自己标识的对象来处理,存储引擎会专门存储。
由于TEXT和BLOB字段通常存储大块数据,在MySQL中无法直接对整个列创建索引。相反,可以对列的前若干个字符创建部分索引。
CREATE INDEX idx_comment_prefix ON table_name (comment(100));
日期和时间
DATETIME和TIMESTAMP
时间戳timestamp依赖于时区,mysql服务器和操作系统和客户端连接都有时区设置。
timestamp保留与所使用的时区相关的值,而后者保留时间和日期的文本。
timestamp更新或者插入都会自动设置为当前时间,默认not null。
DATETIME使用YYYY-MM-DD HH:MM:SS,中的年(4 个字节):用于存储年份信息。
月、日、小时、分钟、秒(每个字段各 1 个字节):分别用于存储月份、日期、小时、分钟和秒的信息。
时间戳显示的值依赖于时区。
标识符的列的类型选择
标识符列,能代表一行数据的列,经常用来级联或者比较,所以要求尽量性能高。
最好使用整数类型,并且保证级联列的类型相同。
应避免使用字符串类型作为标识符,性能不高。
随机字符串插入会有很大的性能问题
因为随机插入的值会写入到索引的随机位置,所以会使得insert变慢,这会导致页分裂,磁盘随机访问,以及对于聚簇存储引擎产生聚簇索引碎片。
并且会破坏缓存的局部性。
避免字符串做主键和索引,比较会耗时
索引尽量不要随机生成,而是按序,从而提高bufferpool中的缓存命中率
避免太多的列
存储引擎将结果存储在缓冲区中,mysql服务器需要将结果解码转换为行格式,性能消耗巨大。
避免太多的链接
避免使用NULL