MySQL如何优化Schema与数据类型性能

本文主要给大家介绍MySQL如何优化Schema与数据类型性能，文章内容都是笔者用心摘选和编辑的，具有一定的针对性，对大家的参考意义还是比较大的，下面跟笔者一起了解下MySQL如何优化Schema与数据类型性能吧。

良好的逻辑设计和物理设计是高性能的基石，应该根据系统将要执行的查询语句来设计schema。

反范式的设计可以加快某些类型的查询，单同时可能使另一类型的查询变慢，比如添加计数表和汇总表是一种很好的优化查询的方式，但这些表的维护成本可能会很高。

1.选择优化的数据类型

更小的通常更好。

应该尽量使用可以正确存储数据的最小类型，更小的数据类型通常更快，因为他们占用更少的磁盘，内存和CPU缓存，并且处理时需要的CPU周期更少。

简单就好

更简单的数据类型的操作通常需要更少的CPU周期。例如，整型数字比字符操作代价更低，因为字符集和校对规则（排序规则）使字符比较相对整型数字比较更复杂。比如，应使用INTERGER存储IP地址（inet_aton）

尽量避免NULL

通常情况下，最好指定列为NOT NULL。如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引，索引统计和值比较非常复杂，可为NULL的列会使用更多的存储空间，当可谓NULL的列被索引时，每个索引记录需要一个额外的字节。但是把可为NULL的列改成NOT NULL带来的性能提升比较小，但如果计划在列上创建索引，就应该避免设计成可为NULL的列。

1.1整数类型

整型类型有可选的UNSIGNED属性，表示不允许负值，可以使原本正数的上线提高一倍。有符号和无符号类型使用相同的存储空间，并具有相同的性能。整型之间相互计算，是以64位的BIGINT作为中间类型进行计算的。

1.2实数类型

实数是带有小数部分的数字，可以使用DECIMAL存储比BIGINT还大的整数。

DECIMAL类型用于存储精确的小数，支持精确计算。例如，DECIMAL(18，9)小数点两边将各存储9个数字，一共使用9个字节，其中小数点前面的数字使用。DECIMAL最多允许65个数字。

浮点类型在存储同样范围的值时，通常比DECIMAL占用更少的空间，内部计算时采用DOUBLE作为计算类型。

因为需要额外的空间和计算开销，尽量只在对小鼠进行精确计算时才使用DECIMAL，在数据量比较大的时候，可以考虑使用BIGINT代替DECIMAL，讲需要存储的货币单位根据小数的位数乘以相应的倍数即可。

1.3字符串类型

varchar

varchar类型用于存储可变长字符串，比定长更节省空间，varchar需要使用1个或2个额外字节记录字符串的长度，如果列的最大长度小于或等于255个字节，则只是用1个字节表示，否则使用2个字节。varchar节省了存储空间，所以对性能也有帮助。但是，由于行是变长的，如果在UPDATE时增加了该边长列的实际存储长度，这就导致需要额外的工作，如果一个行占用的空间增长，并且在页内没有更多的存储空间可以存储，在这种情况下，InnoDB需要分裂页来使行可以放进页内。

varchar使用场合：1.字符串列的最大长度比平均长度大很多，列的更新很少

2.使用了UTF-8这种复合的字符集（每个字符都使用不同的字节数存储）

MySQL在存储和检索时会保留varchar尾部的空格。InnoDB可以把过长的VARCHAR存储为BLOB。

char

定长字符串，MySQL在存储时会去除char尾部的空格。会造成"A "与"A"产生唯一性冲突。数据如何存储取决于存储引擎，填充和截取空格的行为是在MySQL服务层进行的。

更长的列会消耗更高的内存，MySQL通常会分配固定大小的内存来保存内部值，尤其是使用内存临时表进行排序或操作总是会特别糟糕。

blob

采用二进制的方式存储，没有排序规则和字符集。包含tinyblob,blob,mediumblob,longblob

text

采用字符串的方式存储，有排序规则和字符集，包含tinytext,text,mediumtext,longtext。

与其他类型不同，MySQL把每个BLOB值和TEXT值当作一个独立的对象处理，存储引擎在存储时通常会做特殊处理，当BLOB和TEXT值太大时，InnoDB会使用专门的"外部"存储区域来存储，在原本的行中使用指针指向外部的存储区域。同事这两种数据格式最多只能建立前缀索引。

ENUM

枚举不推荐使用（想了解可以参考原书）

1.4日期和时间类型

DATETIME和TIMESTAMP

现在推荐使用DATETIME，范围更大，与时区无关，占用8个字节

1.5位数据类型

InnoDB为每个BIT列使用一个足够存储的最小整数类型来存放，使用BIT类型并不能节省太多的存储空间，MySQL把BIT当作字符串类型，当检索BIT(1)的值时，结果是一个包含二进制0或者1的字符串。

2.MySQL 模式设计的陷阱

2.1 太多的列

MySQL的存储引擎API在工作的时需要在云服务器层和存储引擎层通过行缓冲格式拷贝数据，然后在云服务器层将行缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行结构的操作代价非常的高，转换的代价依赖于列的数量。

2.2太多的关联

一个粗略的经验法则，如果希望查询执行的快且并发性好，单个查询最好在12个表内做关联

2.3NULL值

需要存储一个事实上的"空值"到列表中时，可以使用0，某个特殊值，或者空字符串代替。MySQL会在索引中存储NULL值，而Oracle则不会。

3.范式和反范式

在范式化的数据库中，每个事实数据只会出现一次，

反范式化的数据库中，信息是冗余的，可能会存储在多个地方。

3.1范式化的优点和缺点

优点：

范式化的更新操作更快，只需要更改较少的数据。

范式化的表更小，可以更好的放在内存里，执行操作会更快。

没有多余的数据，可以减少distinct或GROUP BY的操作。

缺点：

通常需要关联，关联代价昂贵，也可能使一些索引策略无效。

3.2 反范式的优点和缺点

优点:

所有的数据都在一张表中，可以避免关联。

不关联的时候即使全表扫描，也是顺序IO。

缺点：

冗余的多余数据，更新更慢

表大，放到内存中，占用大，容易挤出热数据

4.更快的读，更慢的写

为了提升读查询的速度，经常会建一些额外索引，增加冗余列，甚至是创建缓存表和汇总表，这些方法会增加写查询的负担。

写操作变慢并不是读操作变得更快所付出的唯一代价，还可能同时增加了读操作和写操作的并发难度。

5.加快ALTER TABLE操作的速度

ALTER TABLE操作对特大表来说，是个大问题。

MySQL执行大部分修改表结构的步骤：

1.用新结构创建一个空表

2.从旧表中查出所有数据插入新表

3.删除旧表

一般而言，大部分ALTER TABLE操作将导致MySQL服务对该表的访问中断。

对于常见的场景，常见的技巧有两种：

1.现在一台不提供服务的机器上执行ALTER TABLE操作，然后切换

2.影子拷贝，即和原来的步骤一样，但是通过触发器的方式更新新表旧表数据，然后重命名

所有的MODIFY COLUMN操作，都会导致表重建。

5.1 只修改frm（表结构）文件

下面这些操作是有可能不需要重建的：

移除一个列的AUTO_INCREMENT属性

增加，移除，或更改ENUM和SET常量

步骤（本操作是火中取栗）：

1.创建一张有相同结构的空表，进行所需要的修改

2.执行FLUSH TABLES WITH READ LOCK。关闭所有正在使用的表，并且禁止表被打开

3.交换frm文件

4.执行UNLOCK TABLES来释放第二步的读锁。

6.总结

1.避免设计过度复杂的数据库模式

2.使用小而简单的合适数据类型，尽可能避免使用NULL值

3.尽量使用相同的数据类型存储相似或者相关的值。

4.可变长字符串在临时表和排序时有可能悲观的按照最大长度分配内存。

5.尽量使用自增整数列定义主键

6.避免使用MySQL不再推荐的特性

7.谨慎对待BIT，ENUM,SET

看完以上关于MySQL如何优化Schema与数据类型性能，很多读者朋友肯定多少有一定的了解，如需获取更多的行业知识信息 ，可以持续关注我们的行业资讯栏目的。