高性能mysql四.Schema和数据类型优化

灰太狼 2023-07-16 03:59 2阅读 0赞

### 文章目录 ###

*   *   *   *  4.1选择优化的数据类型
             *   *  4.1.1整数类型
                 *  4.1.2实数类型
                 *  4.1.3字符串类型
                 *  4.1.4日期和时间类型
                 *  4.1.5位数据类型
             *  4.2Mysql schema设计中的陷阱
             *  4.3范式和反范式
             *   *  4.3.1范式优点缺点
                 *  4.3.2反范式的有点和缺点
             *  4.4加快Alter table操作的速度
             *   *  4.4.1只修改.frm文件
                 *  4.4.2快速创建mylsam索引

前言:良好的逻辑设计和物理设计是高性能的基石,该篇主要描述Mysql特有的schema的设计.

#### 4.1选择优化的数据类型 ####

更小的通常更好  
正常情况下,一般使用可以正确存储数据的最小数据类型,小的数据类型通常更快,它们占用更少的磁盘,内存和cpu缓存,并且处理时需要的cpu周期也更少.  
简单就好  
简单数据类型的操作通常需要更少的cpu周期,整形比字符操作代价更低,因为字符集和校队规则(排列规则)使字符串比整形更复杂  
尽量避免null  
如果查询中包含null的列,对于mysql来说更难优化,因为null会使索引,索引统计和值比较更复杂;可为null的列会使用更多的存储空间,在mysql里也需要特殊处理;

##### 4.1.1整数类型 #####

有两种类型数字 整数whole number 和 实数read number  
整数 tinyint8位 smallint 16位 mediumint24位 int 32位 bigint64位  
注意: Mysql可以为整型设置宽度,例如int(11),对于大多数应用这是没有意义的,它不会限制值的合法范围,只是规定了Mysql的一些交互工具用来展示字符的个数;对于存储和计算int(1)和int(20)是相同的

##### 4.1.2实数类型 #####

实数是带有小数点部分的数字,支持精确类型和不精确类型;  
CPU不支持对decimal的直接计算,所以在mysql5.0以及以上版本,mysql服务器自身实现了decimal的高精度计算,相对而言,CPU直接支持原生decimal浮点计算会更快;decimal只是一种存储格式,计算时decimal回转换成double类型

##### 4.1.3字符串类型 #####

varchar和char是两种最主要的字符串类型,如何存储在磁盘主要跟存储引擎有关, 假设使用Innodb和myisam  
Varchar  
用于存储可变长度字符串,是最常见的字符串数据类型;它比定长类型更节省空间,因为它仅使用必要的空间; 如果配置ROW\_FORMAT=FIXED情况会变成定长  
Varchar需要使用1或者2个额外字符记录字符串的长度:如果列的最大长度小于等于255字节,则只是用1个字节表示,而否2个字节  
如果一个行占用的空间增长,别切在页内灭有更多的空间可以存储,在这种情况下,不同存储引擎处理方式不一样;myisam会拆成不同的片段存储,Innodb则会需要分裂页来使行可以放进页内  
在5.0或更高版本,mysql在存储或者检索时会保留末尾空格  
Innodb可以把过长的varchar存储为blob

char  
char类型是定长的,mysql会根据定义的长度分配足够的空间,当存储char时,会删除所有末尾的空格  
char适合存储很短的字符串,所有的长度接近一个长度,对于经常变更的数据,char比varchar更好,定长的char不容易产生碎片  
char,varchar对空格处理对比 name是varchar desc是

INSERT INTO t_user ( `NAME`, `DESC` )
    VALUES
    	( "char1 ", "string " ),
    	( " char2", " string" ),
    	( "char3 ", "string " );
    	
    	select CONCAT("'",`name`,"'"),CONCAT("'",`desc`,"'") from t_user where id in (14,15,16);

![在这里插入图片描述][20200318015627481.png]  
与char和varchar相似的类型还有binary和varbinary ,它们存储的是二进制字符串,二进制字符串和常规的字符串非常相似,但二进制字符串存储的是字节码而不是字符,填充方式也不一样,mysql填充binary是用\\0而不是空格,检索时也不会去掉填充值

varchar(5)和varchar(200)存储"hello"空间开销一样,那么使用短的列有什么优势吗?  
更长的列会消耗更多的内存,因为mysql通常会分配固定大小的内存块来保存内部值,尤其是使用内存临时表进行排序或操作时特别糟糕

Blob和text  
都是为了存储很大的数据而设计的字符串数据类型,分别采用二进制和字符的形式  
BLOB和TEXT区别在于blob类型是二进制数据,没有排序跪着或者字符集,text有字符集和排序规则  
mysql对text和 blob进行排序和其他类型不同,只对每列前,max\_sort\_length字节而不是所有做排序, 可以通过减小max\_sort\_length的值或者使用substrng

##### 4.1.4日期和时间类型 #####

DateTime  
可保存大范围你的值,精确到秒,使用8个字节存储  
Timestamp  
1970年1.1格林尼治时间 使用4个字节才能出,只能表示1970-2038年, 默认not null

##### 4.1.5位数据类型 #####

Bit(1)定义一个包含当个单位的字段,BIT(2)存储2个位,以此类推,最大长度64个位;mysql把bit当做字符类型,而不是数字类型,BIT(1)结果是一个包含二进制0/1的字符串而不是ASCII码的’0’/‘1’

#### 4.2Mysql schema设计中的陷阱 ####

太多的列  
Mysql的存储引擎API工作时需要在服务器层和存储引擎之间通过缓冲形式拷贝数据,然后再服务器层将缓冲内容解码成各个列,从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的  
太多关联  
mysql限制了每个关联操作最多只有61张表

#### 4.3范式和反范式 ####

##### 4.3.1范式优点缺点 #####

优点:  
1.范式化的更新操作通常比反范式的快  
2.当数据有较好的范式化时,就只有很少或者没有重复数据,所以只需要修改更少的数据  
3.范式化表通常更小,可以更好的放在内存中,执行更快  
4.减少冗余那么使用distinct 或者group by 就更少  
缺点:  
1.范式化的表通常需要关联查询

##### 4.3.2反范式的有点和缺点 #####

优点:  
1.减少关联查询  
缺点:  
1.冗余数据占用内存

#### 4.4加快Alter table操作的速度 ####

Mysql的alter table 操作的性能对大表来说是个大问题;常见的场景,能使用的技巧有两种 1.先在一台不提供服务的机器上执行alter table操作,然后和提供服务的主库进行切换,2.使用"影子拷贝",技巧是用要求的表结构创建一张和源表无关的新表,然后通过重命名和删表操作交换两张表.

不是所有alter table操作都会引起表重建,eg 有两种方法可以改变或者删除一个列的默认值

alter table sakila modify column rental tinyint(3) not null Default 5;

show status 显示这个语句做了1000次读取 和 1000次插入  
另一种方法

alter table sakila alter culum retal set default 5;

这个语句指挥修改.frm所以这个很快

##### 4.4.1只修改.frm文件 #####

基本的技术是为想要的表结构创建一个新的.frm文件,然后用它替换已经粗在的那张表的.frm文件

##### 4.4.2快速创建mylsam索引 #####