二、Schema与数据类型优化

作者: 小炼君 | 来源:发表于2017-09-17 22:29 被阅读68次

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

风雨过后见彩虹.jpg

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

需要遵守的几点原则

更小的通常更好

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

简单就好

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

避免NULL

NULL值列会影响到索引、索引统计和值比较，mysql更难优化，尽量避免在NULL列上建立索引
可以存储相同数据的不同数据库类型很多，但是他们的长度范围空间占用都不同，比如使用datetime和timestamp来存储日期时间，但是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_FORMAT从FIXED->DYNAMIC时，CHAR->VARCHAR,
反之，
从DYNAMIC->FIXED时，VARCHAR->CHAR
varchar需要1到两个字节来记录字符串长度，如果长度小于255就用一个字节，否则用两个字节
适用场景：字符串列的最大长度比平均长度大很多，这样列的更新少了，主要是考虑到更新的时候如果长度超过了指定的限制，那么就会导致分段(myisam)或者分页(innodb)存储

char

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

BINARY和VARBINARY

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

BLOB与TEXT

两者都是用来存储很大的数据，分别用于存储二进制和字符方式的数据
与其他类型不一样，mysql把他们当做独立的对象处理,存储引擎在存储时通常会做特殊处理，当BLOB和TEXT值太大时，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会以整数保存各个字符串的位置，对枚举类型字段进行排序，默认是按照整数值来进行排序的，如果非要使用字符串顺序排序，那么有两种解决方案：

按照字符串顺序插入枚举
使用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保存了1970年1月1日到现在的秒数，与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个字节就可以存储
对于memory与innodb，则使用足够存储的最小整数类型来存放，所以在存储空间上无法减少消耗
mysql把BIT当做字符串类型，比如存放b’00111001’,二进制=57到BIT（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 `

使用程序
ip转long

/** 
         * 把字符串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执行大部分修改表结构操作的方法使用新的结构创建一个空表，从旧表中查询出所有的数据插入到新表中，然后删除旧表
对于常见的场景：

先在一台不提供服务的机器上执行alter table，然后提供服务的主库进行切换
另外一种是创建"影子表"拷贝，影子拷贝的技巧用要求的表结构创建一张与源表无关的新表，然后通过重命名和删除操作交换两张表
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文件
创建一张有相同结构的空表，并进行相应的修改
执行flush table with read lock,这会关闭所有正在使用中的表，并禁止任何表被打开
交换.frm文件
执行unlock tables 来释放第2步的读锁
快速创建myisam索引
要将数据高效的导入myisam表中，常用的一个技巧是，先禁用索引，导入数据、启用索引

alter table tablename disable keys;
loading data
alter table tablename enable keys;

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

二、Schema与数据类型优化

需要遵守的几点原则

更小的通常更好

简单就好

避免NULL

mysql数据类型

整型

实数

字符串类型

varchar与char

varchar

char

BINARY和VARBINARY

BLOB与TEXT

枚举

日期和时间类型

BIT/SET

SET

选择标识符

特殊类型

范式和反范式

缓存表和汇总表

加快alter table速度

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

高性能mysql

程序员

@IT·互联网

计算机微刊

前端程序员干货