高性能Mysql-schema与数据类型优化

选择优化的数据类型

遵循简单就好的原则,能用整形的就不用字符串。比如日期尽量用内建类型,用整形存储ip。尽量避免出现null的列,特别是对其添加索引。当然,使用不同的存储引擎可能会不一样。

整数

对于整数类型,有TINYINT,SMALINT,MEDIUMINT,INT,BIGINT,存储空间从8-64位。如果确定不会有负数,可以使用UNSIGNED属。比如TINYINT UNSIGNED可以存储0~255大小。

示例

> show create table t;
+-------+----------------------------------------------------------------+
| Table | Create Table                                                   |
+-------+----------------------------------------------------------------+
| t     | CREATE TABLE `t` (                                             |
|       |   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,               |
|       |   `flag` tinyint(1) unsigned NOT NULL DEFAULT '0',             |
|       |   PRIMARY KEY (`id`)                                           |
|       | ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------+

id 为unsingned int类型,长度11,flag字段unsigned 的tinyint,长度为1。 int 存储访问:-2^(32-1) ~ 2^(32-1) -1。如果unsigned,访问0~2^(32) -1。

下面试试:

> insert into t(id,flag) values(4294967295, 255)
Query OK, 1 row affected
> insert into t(id,flag) values(4294967296, 255)
(1264, u"Out of range value for column 'id' at row 1")

结论:

  1. 可存储访问由类型和属性,跟INT(11)的11没有关系。
  2. 尽量选择需要存储空间小的类型。
  3. 有无符号使用的存储空间相同,性能相同,范围不同。
  4. 对于1,2,3等简单标识的字段,选择tinyint(1) unsigned较为合适

实数类型

对于msyql存储小数时一定要小心,虽然内部用double计算,但存储需要高精度数据时,最好选用DECIMAl。使用FLOAT和DOUBLE存储并不能保证精确。声明语法是DECIMAL(M,D),M是数字最大位数,范围1-65;D是小数点右侧数字个数(标度scale),范围0-30,但不得超过M。

对于存储钱等数据时,最好的方式是都乘一个基数(如1000)后转为BIGINT存储。如:

  `bonus` bigint(19) NOT NULL DEFAULT '0' COMMENT '奖励 x 10_000',

字符串类型

常用的两种类型VARCHARCHAR,分别为变长和定长类型。 VARCHAR:

  1. 优点: 节省存储空间,对性能有帮助。
  2. 缺点:变长列更新时,如果空间不够,需要重新分配。MyISAM会拆成不同片段,InnoDB需要分裂页。
  3. 适用: 列更新少,最大长度比平均长度大很多。

CHAR:

  1. 优点: 定长不易产生碎片
  2. 缺点:对长度不定列,浪费空间
  3. 适合:定长,如MD5值,Y/N等定长字符串的列。

TEXT 对于需要存储大文本,在无法避免的情况下可以选用这种类型。TEXT采用字符存储,BLOB采用二进制方式存储。在对其进行排序是,并不会全部比对,而是对前max_sort_length字节。或者使用 ORDER BY SUSTRING(column,length)来排序。

对其进行查询时,开销大。在使用时,最好通过SUSTRING(column,length)将列值转换为字符串,从而使用内存临时表。另一个常用的方式是独立出来一张表,用主键来对应,避免影响其它字段索引效率。

使用枚举类型代替字符串类型

mysql 枚举(ENUM)类型会吧一些不重复的字符串存储为一个预定义的集合,形成映射关系,存储时只需要存整数即可。

> create table enum_test(
	e ENUM('fish','apple','dog') NOT NULL
);
> insert into enum_test(e) values('apple'),('dog'),('fish');
> select e+0 from `enum_test`
+-----+
| e+0 |
+-----+
| 1.0 |
| 2.0 |
| 3.0 |
+-----+
3 rows in set

排序时,也是按照整数来排序的。减少空间使用,提升性能是我们使用枚举代替字符串的好处,但是它的缺点很明显,如果需要改变表结构,使用枚举不是个好注意,除非只是在后面追加新的枚举值。还有需要注意的是,如果出现char、varchar和枚举列进行关联时,可能会更慢。

日期和时间

Mysql提供了两种相似的时间类型:DATETIME和TIMESTAMP,如果在不同场景下选用两种,是主要研究的工作。

DATETIME 保存大范围的值,从 1001 年到 9999 年,精度为秒。把日期和时间封装到格式为 YYYYMMDDHHMMSS 的整数中,与时区无关。使用 8 个字节的存储空间。

TIMESTAMP 保存从 1970 年 1 月 1 日午夜以来的秒数,和 UNIX 时间戳相同。TIMESTAMP 只使用 4 个字节的存储空间,范围是从 1970 年到 2038 年。默认情况下,如果插入时没有指定第一个 TIMESTAMP 列的值,MySQL 则设置这个列的值为当前时间。TIMESTAMP 列默认为 NOT NULL。

通常,尽量使用TIMESTAMP,他的效率更高,在国际化环境下更是必须。但使用时间戳是需要注意,建表后一定要使用show create table t_name查看建表语句(所有schema都应该检查),默认处理可能会映入一些期望之外的默认设置。还有就是,如果使用客户端查看显示,和时区相关,不要搞混淆。

缓存表和汇总表

缓存表,比如有些情况下,在运营统计等需求,可以简单获取,但一般查询较慢,定时器也难维护。汇总表一般也是运营统计等需求,数据不冗余,如计数器等。

在使用缓存表和汇总表时,必须决定是实时维护还是定期重建。能重建更好,数据可以都维护到历史表中。在维护过程中,可以通过影子表方式实现。

>create table t_new like t;
> rename table t to t_old,t_new to t;

计数器 需要计数器的地方很多,最简单就是建一张只有一行的字段,每次更新。如:

create table hit_counter(
	cnt int unsigned not null
)engine= InnoDB;
update hit_counter set cnt=cnt+1;

但引入互斥锁,并发性能低。改善性能的下一步是类似java 中hashmap的做发,分成多个slot,随机更新一行,最后汇总。

>create table hit_counter(
	slot tinyint unsigned not null primary key,
	cnt int unsigned not null
)engine= InnoDB;

> select * from hit_counter
+------+-----+
| slot | cnt |
+------+-----+
| 1    | 0   |
| 2    | 0   |
| 3    | 0   |
+------+-----+

> update hit_counter set cnt=cnt+1 where slot = ${1-3随机数}

如果按一定的周期统计,还可以添加字段如day,通过高级用法实现:

ON DUPLICATE KEY UPDATE cnt=cnt +1;

参考:

  1. 本文来自《高性能mysql》学习记录
CONTENTS