Osheep

时光不回头,当下最重要。

二、Schema与数据类型优化

难得一个周末,终于可以静下心来整理一下笔记了,最近确实没时间。但是我已经预感到风雨后的彩虹,所以一切都会变得很好……

《二、Schema与数据类型优化》

风雨过后见彩虹.jpg

今天我们来讲一下mysql数据库中的schema类型优化相关的知识
在进行mysql数据库表设计的时候

需要遵守的几点原则

更小的通常更好

使用更小的类型存储数据,通常更快,占用空间更小,CPU运行速度更快

简单就好

简单数据类型的操作通常需要更少的CPU周期,整型比字符型操作代价更低,因为字符集和校验规则使字符型更复杂,使用mysql內建的类型而不是字符串来存储日期和时间,使用整型来存储IP地址
mysql支持很多别名,但通过别名创建表之后,通过show create table显示表创建语句时,采用的是具体的类型

避免NULL

NULL值列会影响到索引、索引统计和值比较,mysql更难优化,尽量避免在NULL列上建立索引
可以存储相同数据的不同数据库类型很多,但是他们的长度范围空间占用都不同,比如使用datetimetimestamp来存储日期时间,但是timestamp只使用了datetime一半的存储空间

mysql数据类型

整型

数字分为整数和实数,如果存储整数,又有TINYINT SMALLINT MEDIUMINT INT BIGINT,他们的长度分别为8 16 24 32 64字节
整数类型有可选的UNSIGNED属性,表示不能为负数,有符号和无符号类型使用相同存储空间,并具有相同的性能
数据库中使用BIGINT来进行整数计算
int(1)int(10)在存储层面上来说其实都占用一样的空间,只是从mysql客户端层面来进行限制显示的字符长度
int(1)int(10)如果不加zerofill,在展示上并没有什么明显的变化,如果添加上zerofill就可以看到他们之间的区别

create table int_test(num(1),num2(10));
insert into int_test(num,num2) values(8,8);
select * from int_test;
+---+
| 8 |
+---+
| 8 |
+---+

如果添加了zerofill属性

create table int_test(num(1),num2(10) zerofill);
insert into int_test(num,num2) values(8,8);
select * from int_test;
+------+------------+
| num  | num2       |
+------+------------+
|    1 | 0000000001 |
+------+------------+

实数

分两种:近似计算
FLOAT DOUBLE分别包含8,16个字节,对于浮点运算,MYSQL内部使用DOUBLE作为浮点运算类型
精确计算 DECIMAL该类型允许最多65个数字
浮点类型在存储相同范围的值时,通常比 DECIMAL使用更少的空间
应该避免在非小数进行精确计算的时候使用DECIMAL,如果在数据量大的时候,可以使用BIGINT代理DECIMAL,可以使用BIGINT * 相应倍数,主要是考虑到性能代价问题

字符串类型

varchar与char

varchar

varchar类型用于存储可变长度字符串,是最常见的字符串数据类型,它比char更节省空间,它只使用必要的空间,越短的字符串,使用越少的空间
但是如果是用ROW_FORMAT=FIXED创建的话,这根char就没有什么区别了
ROW_FORMAT的具体修改方式:

alter table tablename ROW_FORMAT=[DEFAULT,FIXED,DYNAMIC,COMPRESS,REDUNDANT,COMPACT]

ROW_FORMATFIXED->DYNAMIC时,CHAR->VARCHAR,
反之,
DYNAMIC->FIXED时,VARCHAR->CHAR
varchar需要1到两个字节来记录字符串长度,如果长度小于255就用一个字节,否则用两个字节
适用场景:字符串列的最大长度比平均长度大很多,这样列的更新少了,主要是考虑到更新的时候如果长度超过了指定的限制,那么就会导致分段(myisam)或者分页(innodb)存储

char

char是定长的,存储char时,MySQL会删除末尾空格,char会根据需要采用空格进行填充以方便比较,需要注意的是char(1)表示存储的是一个字符,而不是一个字节

BINARY和VARBINARY

CHARVARCHAR对应的一组就是BINARYVARBINARY,这两者是用于存储二进制字符串,二进制字符串与常规字符串相似,但是二进制字符串存储的是二进制字节而不是字符,填充也不一样,BINARY采用的是\0(零字节)而不是空格进行填充,检索时也不会去掉填充值

BLOB与TEXT

两者都是用来存储很大的数据,分别用于存储二进制和字符方式的数据
与其他类型不一样,mysql把他们当做独立的对象处理,存储引擎在存储时通常会做特殊处理,当BLOBTEXT值太大时,INNODB会使用外部的存储区域进行存储,此时每个值在行内需要1~4字节存储一个指针,具体值存储在外部区域中
两者之间的不同是,采用BLOB类型存储的是二进制数据,没有排序规则或字符集
mysql不允许对TEXT/BLOB全列进行索引,只能根据max_sort_length设置的最大长度进行索引,默认是1024,可以通过order by substring(column, length)来对前面length长度字符串进行排序
如果使用了BLOB,TEXT,在进行结果排序时,会使用到磁盘临时表,尽量不要使用TEXT/BLOB,如果实在没有办法,可以通过substring(column, length)来代替整列值进行排序,这样就可以在内存中使用内存临时表了
如果通过explain分析sql语句,extra列出现了using temporary,则说明这个查询使用了隐式临时表

枚举

枚举可以把不重复的字符串存储成一个预定义的集合,mysql会以整数保存各个字符串的位置,对枚举类型字段进行排序,默认是按照整数值来进行排序的,如果非要使用字符串顺序排序,那么有两种解决方案:

  1. 按照字符串顺序插入枚举
  2. 使用field()函数,但是这样会导致mysql无法利用索引消除排序
    select e from enumtest order by field(e, 'apple', 'fish','dog')
    field(column, order serials),根据给定的order serials顺序对结果字符串进行排序,但是这样会导致无法使用索引消除排序
    在使用char/varchar与枚举列进行关联时,可能会比直接关联char/varchar列更慢
    如果直接查询枚举字段,则是显示的字符形式,可以通过数字上下文查看当前枚举值的位置
create table enum_test(animal enum(‘fish’,’dog’,’cat’);
insert into enum_test(animal) values(‘fish’), (‘dog’);
select * from enum_test;
+--------+
| animal |
+--------+
| fish   |
| dog    |
+--------+
select animal+0 from enum_test
+----------+
| animal+0 |
+----------+
|        1 |
|        2 |
+----------+

日期和时间类型

mysql中日期时间类型最小单位是,但是可以用微妙级粒度进行临时计算
DATETIME保存大范围的值,从1001年到9999年,精度为秒,封装到格式为YYYYMMDDHHMMSS的整数中进行存储,与时区无关,采用8个字节表示
TIMESTAMP保存了197011日到现在的秒数,与UNIX时间戳相同,只使用4个字节表示,只能表示1970年到2038年,提供了FROM_UNIXTIME()unix时间戳转换为日期,并提供了UNIX_TIMESTAMP()将日期转化为时间戳
timestamp显示的值跟时区有关系,不同时区显示的值可能会有差异,使用timestamp在进行sql更新插入时,如果没有指定,则会将当前时间插入进去

BIT/SET

所有位类型,从技术上来说都是字符串类型
BIT可以使用bit在一个或者多个列上使用0/1,BIT(1)代表1位,BIT(2)代表2位,最大长度为64
BIT因存储引擎而差异,MYISAM会打包存储所有的BIT列,所以17个单独的bit只需要17位,myisam将会打包存储所有的bit列,只使用3个字节就可以存储
对于memoryinnodb,则使用足够存储的最小整数类型来存放,所以在存储空间上无法减少消耗
mysqlBIT当做字符串类型,比如存放b’00111001’,二进制=57BIT(8)的列并检索,得到的内容是ASCII码位57的字符“9”,在数字上下文中,是57

createtable bittest(a bit(8));
insert into bittest values(57);
select a, a+0 from bittest
9, 57

应该谨慎使用这种类型,对于大部分应用,最好避免使用这种类型

SET

用于保存并合并这些BIT,但是一般不建议使用这样的方式,而是采用一个整数包装一系列位,通过位运算来得到整数
ACL权限控制

can_read  1
can_write 2
can_delete 4
set @can_read := 1 << 0,
@can_write := 1 << 1,
@can_delete := 1 << 2;
create table acl(persm tinyint unsigned not null default 0));
insert into acl(perms) values(@can_read + @can_write);
select persm from acl where perms&@can_read;//查询拥有读权限

选择标识符

标识符选择合适的类型非常重要,一般来说它可能会被用于与其他值比较、外键关联、查找,在用于外键关联时,需要严格要求外键类型一致,避免关联的性能问题和类型隐式转换问题
整数类型是标识列最好的选择,因为他们可以使用auto_increment,应该避免使用字符串类型作为标识列,因为他们很消耗空间,通常,字符串比数字类型慢,在myisam,对字符串默认使用的是压缩索引,对于随机的字符串比如MD5(),SHA1(),UUID()产生的字符串,任意分布在很大的空间内,这会导致查询语句insert/select变得很慢:
插入新值会随机的写到索引的不同位置,导致分页、磁盘随机访问,聚簇索引产生碎片化
select语句慢,因为逻辑上相邻的行会分布到磁盘和内存的任意地方,导致缓存对所有类型的查询语句效果都很差,访问局部性原理失效
存储UUID值应该去掉-,更好的做法是使用HEX()函数转化成16字节的数字,并采用binary(16)存储,如果要将16字节数字转化回去,应该使用unhex()

select hex(uuid()) from dual;
+--------------------------------------------------------------------------+
| hex(uuid())                                                              |
+--------------------------------------------------------------------------+
| 30333164396564612D396261662D313165372D383736352D646330656131363064353363 |
+--------------------------------------------------------------------------+
select unhex('30333164396564612D396261662D313165372D383736352D646330656131363064353363');
+-----------------------------------------------------------------------------------+
| unhex('30333164396564612D396261662D313165372D383736352D646330656131363064353363') |
+-----------------------------------------------------------------------------------+
| 031d9eda-9baf-11e7-8765-dc0ea160d53c                                              |
+-----------------------------------------------------------------------------------+

特殊类型

给定的数据并不直接与数据库内置类型一致,比如时间<秒级,数据库最低单位为秒,那么可以通过BIGINT存储微妙级别的时间戳,或者使用double存储秒之后的小数部分
另一个例子是IPV4地址,其实IPV4地址实际上是一个32位的无符号整数,不是字符串,用小数点将地址分成4段的表示方法是为了让人们阅读容易,所以应该用无符号整数存储IP地址
解释:
ip地址一共4段,每段取值为0~255,也就是说每段可以用1个字节表示,4 * 1byte * 8bit = 32bit
如何将ip地址转换成数字

使用数据库提供的方法

select inet_aton (ip -> number)
select inet_ntoa (number -> ip)
同理`ipv6`采用`128`位,通过`varbinary`存储(`bigint`最大支持`64`位)
`inet6_aton/inet6_ntoa `

使用程序
iplong

/** 
         * 把字符串IP转换成long 
         * 
         * @param ipStr 字符串IP 
         * @return IP对应的long值 
         */  
        public static long ip2Long(String ipStr) {  
            String[] ip = ipStr.split("\\.");  
            return (Long.valueOf(ip[0]) << 24) + (Long.valueOf(ip[1]) << 16)  
                    + (Long.valueOf(ip[2]) << 8) + Long.valueOf(ip[3]);  
        }  
    /** 
         * 把IP的long值转换成字符串 
         * 
         * @param ipLong IP的long值 
         * @return long值对应的字符串 
         */  
        public static String long2Ip(long ipLong) {  
            StringBuilder ip = new StringBuilder();  
            ip.append(ipLong >>> 24).append(".");  
            ip.append((ipLong >>> 16) & 0xFF).append(".");  
            ip.append((ipLong >>> 8) & 0xFF).append(".");  
            ip.append(ipLong & 0xFF);  
            return ip.toString();  
        }  

范式和反范式

常用的数据库范式有3大范式
1NF: 数据库中的每一列都是最小的单元,不可拆分
2NF: 数据库表中的每一条记录都能唯一标识(主键唯一性约束)
3NF:数据库表中不存在其他表中的非主键列
反范式化的schema,因为所有数据都在一张表上,所以就不用关联其他表了,当数据量超大时,这样就避免了随机IO产生

缓存表和汇总表

缓存表:用于存储可以比较简单从schema其他表获取数据的表,(但是获取数据的速度比较慢)
汇总表:保存的是使用group by语句聚合数据的表,也就是统计过后的数据
以获取用户24小时之前内发送的消息数来说,系统可以每小时生成一张汇总表,如果必须获取24小时之内发送的消息数,以每小时汇总表为基础,把前23个小时的统计表中的计数全部加起来,最后再加上开始阶段和结束阶段不完整的小时数,假设统计表叫

msg_per_hr:
create table msg_per_hr {
    hr datetime not null,
    cnt int unsigned not null,
    primary key (hr)
}

通过concat(left(now(), 14), ’00:00’)来获取最近的小时数
计算前面完整的23个小时的消息总数
select sum(cnt) from msg_per_hr where hr between concat(left(now(), 14), ’00:00’) – interval 23 hour and concat(left(now(), 14), ’00:00’);
获取前面第24小时不完整的时间片段
select sum(cnt) from msg_per_hr where hr>= now() – interval 24 hour < concat(left(now(),14), ’00:00’) – interval 23 hour;
获取最近1小时内的统计信息
select sum(cnt)from msg_per_hr where hr > concat(left(now(), 14), ’00:00’);
将这三个统计数加起来就得到之前24小时内的统计信息
在添加缓存表或者汇总表后,必须决定是实时维护还是定期重建数据,但是采用定期重建并不只是节省资源,也可以保持表不会有很多碎片,通常在重建汇总表和缓存表时,也要求数据在操作时可用,这时需要通过影子表来实现,当完成影子表创建后通过原子性的重命名操作切换影子表和原表

加快alter table速度

alter table操作的性能对于大表来说是个大问题,mysql执行大部分修改表结构操作的方法使用新的结构创建一个空表,从旧表中查询出所有的数据插入到新表中,然后删除旧表
对于常见的场景:

  1. 先在一台不提供服务的机器上执行alter table,然后提供服务的主库进行切换
  2. 另外一种是创建”影子表”拷贝,影子拷贝的技巧用要求的表结构创建一张与源表无关的新表,然后通过重命名和删除操作交换两张表
    alter table 不引起表重建
    这里以rental_duration tinyint(5)改为tinyint(3)来说
    通过alter table modify column会导致表重建,所有的modify column都将导致表重建
    alter table film modify column rental_duration tinyint(3) not null default 5;
    使用alter table … alter column来操作表的列
    alter table film alter column rental_duration set default 5;
    他会直接修改.frm文件而不涉及表数据
    骇客做法,请先备份您的数据,不推荐
    直接修改.frm文件
  3. 创建一张有相同结构的空表,并进行相应的修改
  4. 执行flush table with read lock,这会关闭所有正在使用中的表,并禁止任何表被打开
  5. 交换.frm文件
  6. 执行unlock tables 来释放第2步的读锁
    快速创建myisam索引
    要将数据高效的导入myisam表中,常用的一个技巧是,先禁用索引,导入数据、启用索引
alter table tablename disable keys;
loading data
alter table tablename enable keys;

但是上面的这种方式对唯一索引无效,因为DISABLE KEY只对非唯一索引有效

点赞