高性能MySQL读书笔记（四）

发表于 | 分类于

高性能MySQL读书笔记（四）

  • 数据类型
  • 范式设计
  • 大表的Alter操作
  • 相关资料

数据类型

整数与小数类型

  • 整数类型 : 有如下几种：
    TINYINT(1个字节),SMALLINT(2个字节),MEDIUMINT(3个字节),INT(4个字节),BIGINT(6个字节)
  • 小数（实数）类型有如下几种：
    FLOAT4个字节)、DOUBLE(8个字节)、DECIMAL(占用字节更多)。
    DECIMAL(n,m)表示仅允许保留m位小数；n表示小数点左右两边数字总和。也就是说只允许n-m个整数位。

字符串类型

  • varchar是使用最频繁的字符串类型，它除了维护字符串还维护了一个或者两个字节用来记录长度。varchar(n)，n代表单个字符的个数,当我们设置varchar(5)或者varchar(100)来存储”mysql”，哪个会更好呢？当然是varchar(5)更好，因为varchar(100)即使之存储5个字符的字符串，也会分配100个字符的内存空间，特别是当使用临时表会更不好。
  • char用来存储较小的字符串 char(n)代表可以存储n个字符的字符串。
  • blob 与text
    这两种类型是用来存储较大字符串的类型，与其他类型不同，它们的排序只按照最前边max_sort_length个字符进行排序，而不是按照全部字符排序。
    因此如果只需要按照较小的字符排序，可以缩小max_sort_length的设定。或者使用 order by substring( column，length)进行排序。
  • enum 用时可以使用 enum(“string1”,“string2”,“string3”)代替varchar\char等字符串类型。
    enum类型对字符，实际是使用整数进行存储的，并且当对enum列排序也是按照实际存储的整数值排序。适合存储如有序的状态，性别等等。
    比如，我们新增一个enum类型的列，并按照主键id依次顺序插入：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
ALTER TABLE USER ADD  enumType ENUM("apple","banana","orange");
ALTER TABLE USER ADD  enumType ENUM("apple","banana","orange");


UPDATE `user`.`user` SET `enumType` = 'apple' WHERE id=1;
UPDATE `user`.`user` SET `enumType` = 'banana' WHERE id=2;
UPDATE `user`.`user` SET `enumType` = 'orange' WHERE id=3;

SELECT id,(enumType+0) AS a FROM USER ;

OUTPUT:
id	enumType
1	1
2	2
3	3

可以看到，插入的 “apple”,“banana”,”orange”分别使用 1，2, 3进行存储，也就是按照enum设定的顺序。
当然对这个enum列的排序也是按照数字排序。

注意：因此在使用enum类型的时候，切记不要存储 如 enum(“10”,“29”,“42”,“99”)这有的字符串类型的数字，以避免产生不必要的混淆！

enum类型
优点：更紧凑的存储，更加节省空间；
不足：当enum类型的列 与 varchar类型的列进行关联时比两个varchar进行关联慢一些。

日期与时间

  • DATETIME
    datetime 用来表示日期，范围从1001年到9999年，精确到秒:YYYYMMDDHHMMSS，占用8个字节。这种类型与时区无关。
  • TIMESTAMP
    从1970.1.1以来的描述，跟UNIX时间戳相同。
    占用4个字节，也是精确到秒，但是范围要比DateTime小很多，1970~2038。
    另外，这种类型默认not null，首次插入会自动用当前时间填充。

FROM_UNIXTIME()可以把时间戳转为日期。
UNIX_TIMESTAMP()可以把日期转为时间戳。

对于比秒更小的粒度：目前暂无支持，可以使用bigint存储更小的时间戳。

数据类型优化技巧

  • 数据类型更小通常更好，数据类型越简单越好
  • 数值操作比字符操作快，小类型的处理速度比大类型快
  • 尽量避免使用NULL，要是有必要用NULL，那也可考虑使用0来进行代替
  • IP用int存：inet_aton()、inet_ntoa()

范式设计

第一范式：要求有主键，并且要求每一个字段原子性不可再分
第二范式：要求所有非主键字段完全依赖主键，不能产生部分依赖
第三范式：所有非主键字段和主键字段之间不能产生传递依赖

第二范式：要求所有非主键字段完全依赖主键，不能产生部分依赖
即满足第一范式前提，当存在多个主键的时候，才会发生不符合第二范式的情况。
比如有两个主键，不能存在这样的属性，它只依赖于其中一个主键，这就是不符合第二范式。
通俗理解是任意一个字段都只依赖表中的同一个字段。（涉及到表的拆分）

第三范式：所有非主键字段和主键字段之间不能产生传递依赖
请看下边的例子：

1
2
3
4
"cid"	"role"				"phone"			"qqNumber"			"level"
"1"		"programmer"		"15472152456"	"502327342"			"6"
"2"		"manager"			"15372152456"	"534232"			"2"
"3"		"manager"			"15372152436"	"5023272"			"3"

这个设计就违反了第三范式，因为level字段依赖于role，role依赖于cid：
cid 为1的员工 programmer 角色 级别为6;
cid 为2的员工 manager级别 级别为2；
cid 为3的员工 manager级别为3

– - 符合第二范式：

1
2
3
4
5
6
7
8
9
"cid"	"phone"			"qqNumber"
"1"		"15472152456"	"502327342"
"2"		"15372152456"	"534232"
"3"		"15372152436"	"5023272"

"cid"	"role"			"level"
"2"		"manager"		"3"
"3"		"manager"		"2"
"1"		"programmer"	"6"

换个例子，比如一个表中有 年龄\是否未成年 ；这两个字段明显存在依赖。

范式设计的优缺点

  • 优点：拆分的更细，冗余数据更少。占用空间更小，可以更好的放入内存，操作更快
  • 会导致查询需要频繁使用多表关联,会导致执行效率降低、并且有些索引无法很好执行。

反范式设计的优缺点

  • 优点：把所有业务相关的字段尽可能的放入一张表中，避免多表关联查询，单表可以更好的使用索引，有时候执行效率更高！
  • 缺点：更多的冗余数据，导致更新或者删除缓慢。

一些策略：

  • 基于静态变更频率不高数据的冗余 ：静态数据指相关字段变更频率不高，如用户资料，可作冗余字段。
    以消息表为例
    Fuid，Tuid，Msg，Dateline
    范式化设计后，在内容呈现上可能需要涉及多表关联，因为用户想知道发消息的人是谁？
    那么，可增加冗余字段：Funame

  • 基于谓词的冗余
    有些where连接条件很热门，一个谓词为多个SQL服务，那么，我们可以把这些热门的谓词收集，集中存在到一张冗余表，以用户登录为例子，假设有上亿条用户资料并做了分表处理，用户只关心uname，根据uname需要到各个分表去查询uid，此时，我们可按：uname，uid建立冗余表。

  • 基于函数的冗余
    频繁的count，max，sum等需求，例如，论坛今日发帖总量，可建立冗余字段Spost

  • 基于排序的冗余
    索引用来加速查询或者已经是件家喻户晓的事，但还有一个优势，即：降低排序成本。
    仍然以消息表为例，若我们按uname对消息做排序的代价估计会蛮高的，但如果在消息表里把uname冗余并且建立好索引，则可以借助索引来抵消排序.

大表的Alter操作

总结一下Alter大表的几种方式：

先在一个不提供服务的数据库表中进行执行alter脚本，执行完毕后与提供服务的主库进行切换。当然要最大限度保证这两个库的数据是同步的。
影子拷贝：通过重命名替换表。
创建一个张影子表，新增需求字段，同步数据，同步一致后通过原子性的重命名操作替换两个表。

1
2
3
4
5
6
7
8
9
-- step1
CREATE TABLE User_new LIKE USER;
-- step2
alter table User_new add column;//增加字段
--step3
同步数据，直到一致
-- step4 原子替换
RENAME TABLE USER TO User_old ,User_new TO USER
在一个不提供服务的同步从库中执行alter，然后主从切换

附一个db升级脚本

1
2
3
4
5
6
7
8
9
10
11
ALTER TABLE `fight_over_log` ADD COLUMN `open_treasure_box_by_promt_button_times` int(11) default '0';
ALTER TABLE `fight_over_log` ADD COLUMN `ride_up_by_promt_button_times` int(11) default '0';

DROP PROCEDURE IF EXISTS `sp_heart_log`;
DELIMITER ;;
CREATE  PROCEDURE `sp_heart_log`(IN `_server_id` varchar(11),IN `_online_player_count` int,IN `_room_player_count` int,IN `_solo_room_user_single_leisure` int,IN `_solo_rooms_single_leisure` int,IN `_solo_room_user_single_rank` int,IN `_solo_rooms_single_rank` int,IN `_group_room_user_leisure` int,IN `_group_rooms_leisure` int,IN `_group_room_user_rank` int,IN `_group_rooms_rank` int,IN `_time_stamp` int)
BEGIN
		insert into heart_log (server_id,online_player_count,room_player_count,solo_room_user_single_leisure,solo_rooms_single_leisure,solo_room_user_single_rank,solo_rooms_single_rank,group_room_user_leisure,group_rooms_leisure,group_room_user_rank,group_rooms_rank,log_time) values(`_server_id`,`_online_player_count`,`_room_player_count`,`_solo_room_user_single_leisure`,`_solo_rooms_single_leisure`,`_solo_room_user_single_rank`,`_solo_rooms_single_rank`,`_group_room_user_leisure`,`_group_rooms_leisure`,`_group_room_user_rank`,`_group_rooms_rank`,FROM_UNIXTIME(`_time_stamp`));
END
;;
DELIMITER ;

注：db升级脚本要多加一行空白作为结束符，不然会执行报错= =

相关资料

参考资料

------ 本文结束 ------