MySql数据优化

选择优化的数据类型

通用原则

1. 更小的通常越好

   1. 更小的数据类型占用更小的磁盘空间,内存和CPU缓存,并且处理时需要的CPU周期也更少

   2. 注意不能低估需要存储的值的范围

2. 简单就好

   1. 简单数据类型的操作通常需要更少的CPU周期如整型比字符操作代价更低

      1. 使用MySql内建的类型而不是字符串来存储日期和时间

      2. 应该用整型存储ip地址

3. 避免NULL值

   1. 通常情况下最好指定列为NOT NULL,除非真的需要存储NULL值

      1. 查询种包含NULL使得索引,索引统计和值比较都更复杂

      2. 可为NULL的列会使用更多的存储空间

   2. 调优时将NULL改为NOT　NULL 带来的性能提升较小,所以没有必要首先这么做,但在设计时需要注意

DATETIME与TIMESTAMP都可以存储相同类型的数据:时间和日期,精确到秒,但后者只占一半的存储空间,而且会随着时区变化而自动更新

整数类型

特点

存储范围由-2(N-1)   ----->    2(N-1)-1

INT(11)并不是说用11位去存储,而是说显示为11位宽度,但实际存储是不变的,也就是说INT(1)与INT(20)存储空间是相同的

以BIGINT作为内部运算的格式但一些聚合函数也以DOUBLE,DECIMAL作为运算格式

分类

TINYINT(8)

SMALLINT(16)

MEDIUINT(24)

INT(32)

BIGINT(64)

UNSIGNED

标志无符号正数,可以将正数的范围提高一倍,但两者具有相同的存储空间和性能

实数类型

实数是指含有小数部分的数字

FLOAT(4)

DOUBLE(8)

DECIMAL(最多65位)

用于存储精确的小数,但其只是一种存储格式,计算时依旧采用DOUBLE

可以指定小数点前后的位数

如DECIMAL(18,9)指总位数为18,占9个字节,4个字节存储9位,小数点占一个字节

选择条件

MySql以double作为内部浮点计算的类型

CPU支持原生浮点计算,但不支持直接的DECIMAL计算(由MySql服务器自己实现),所以前者的速度要快一些

应当只在需要对小数进行精确运算时才会选择DECIMAL,也可以将实数乘以10的倍数从而用BIGINT存储

字符串类型

VARCHAR

最多65535个字节

VARCHAR用于存储可变长字符串

除了存储的字符串外,还需一或两个字节存储每个字符串的长度

• 小于255时,用1个字节,否则用两个

• VARCHAR(10)需要11个字节,VARCHAR(1000)需要1002个字节

问题

由于是变长的,在update时很可能出现长度增加的情况(原本只用了5个字节,现在变成了11个)不同的存储引擎会使用不同的方式处理

使用时机

字符串列的最大长度远大于平均长度(这样update时不会出现频繁的空间不够的情况)

列的更新很少

采用UTF-8这种复杂的编码(字符使用不同的字节数存储)

CHAR

最多255个字节

存储定长的字符串,MySql会根据定义的字符串长度,分配足够的空间,

如果字符串长度不够,会在后面添加空格查询时再去掉空格(这是在MySql服务层进行处理的,与存储引擎无关)

使用时机

适合存储较短的字符串或者字符长度差不多的字符串

注意: CHAR(N),VARCHAR(N)里面的N代表字符(不是字节!!!!)

 

BLOB与TEXT

最好不要使用,使用也需要SUBSTRING(COLUMN,LENGTH)将其转化成足够小的字符串

使用枚举(ENUM)代替字符串类型

日期和时间类型

MySql存储的最小时间粒度为秒(Maraia存储引擎为微秒)但可以使用微秒级的粒度进行临时计算

DATATIME

能保存最大范围的数从1001-9999,精度为秒

封装格式: YYYY-MM-DD HH:MM:SS(去掉分隔符后用整数存储)

占用8个字节存储

可排序

TIMESTAMP

只使用四个字节,时间范围为1970-2038

MySql4.1及更新的版本按照DATATIME格式化TIMESTAMP(之前没有分隔符),但是内部存储格式是不变的

TIMESTAMP数据会随着时区更新,而DATETIME只显示固定的文本

TIMESTAMP默认为NOT NULL

插入时如果没有指定TIMESTAMP会自动插入当前时间

位数据类型

所有位类型,从技术上来说都是字符串

BIT

SET

选择合适的标识符(即主码)

• 一旦选定了一种类型,要保证所有相关联的表中都使用同样的类型包括UNSIGNED

• 整数通常是标识列的最好选择

• 避免使用字符串,SET和ENUM作为主码

特殊类型的数据

例如IP地址,其实是16位的无符号整数(小数点是便于人类阅读),所以应该存储为INT 而不是VARCHAR

INT_NTOA(),INT_ATON()进行转化

mysql设计中的陷阱

太多的列

太多的关联

全能枚举

变相的使用枚举

非此发明的NULL

范式与反范式

第一范式,不能表中有表

第二范式,所有属性都要与主属性相关

第三范式,所有属性都要与主属性直接相关

范式的优点和缺点

反范式的优点和缺点

可以很好的避免关联(所有数据都在一张表中),这样也能使用更有效的索引策略

混合使用范式与反范式

缓存表与汇总表

物化视图

其实和普通表的差别不大,就是把要查询

计数器表

加快ALTER TABLE 的速度

一般步骤

1. 用新的结构创建空表

2. 从旧表查出数据插入新表

3. 删除旧表

缺点

速度慢

改进

1. 在不提供服务的服务器上执行操作,然后和提供服务的服务器进行主从切换

2. 影子拷贝,用要求的表结构创建一张和源表无关的新表,然后通过重命名和删表操作交换两张表

   1. 创建新表(与源表无关)

   2. 将数据插入新表

   3. 插入完成后将源表删除,新表重命名成为新的原表

注意

不是所有ALTER TABLE都会重建表

 

只修改.frm文件

快速创建MyISAM索引

为了高效导入数据,可以先禁用索引,数据导入后再启用索引

对唯一索引无效,DISABLE KEYS 只对非唯一索引有效

也可以先删除所有非唯一索引,数据导入后,再重新建立索引

总结