Schema与数据类型优化是什么样的

这期内容当中小编将会给大家带来有关Schema与数据类型优化是什么样的，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。

创新互联专注于东兴网站建设服务及定制，我们拥有丰富的企业做网站经验。热诚为您提供东兴营销型网站建设，东兴网站制作、东兴网页设计、东兴网站官网定制、小程序制作服务，打造东兴网络公司原创品牌,更为您提供东兴网站排名全网营销落地服务。

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

选择优化的数据类型

选择正确的数据类型对于获得高性能至关重要。

更小的通常更好

一般情况下应该尽量使用可以正确存储数据的最小数据类型，例如只需要存0-200，那么tinyint unsigned更好。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少，但需要确定不会超过范围的最小类型。

简单就好

简单数据类型的操作通常需要更少的CPU周期，例如整型比字符串操作代价更低，因为字符集和校对规则（排序规则）使字符比较比整型比较更复杂。例如：应该使用MySQL内建的类型而不是字符串来存储日期和时间，用整型存储IP地址。

尽量避免NULL

通常情况下最好指定列位NOT NULL，除非真的需要存储NULL值。如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊处理。当可为NULL的列被索引时，每个索引记录需要一个额外的字节。如果计划在列上建索引，就应当尽量避免设计成为可为NULL的列。

MySQL数据类型

MySQL 数据类型-菜鸟教程

一些经验技巧

CHAR和VARCHAR
要根据存储引擎做出相应的选择：
（1）MyISAM存储引擎：建议使用固定长度的数据列
（2）MEMORY存储引擎：目前都使用固定长度的数据行存储，无论使用CHAR还是VARCHAR都作为CHAR类型处理
（3）InnoDB存储引擎：建议使用VARCHAR类型，主要的性能因素是使用数据行使用的存储容量。由于CAHR平均占用的空间多于VARCHAR，因此使用VARCHAR来最小化需要处理的数据行的存储容量和磁盘IO是比较好的
由于CHAR固定长度，处理速度比VARCHAR快得多，缺点是浪费存储空间，程序需要对行尾空格进行处理（存储时会去除尾部空格），所以对于那些长度变化不大并且对查询速度有较高要求的数据可以考虑使用CAHR类型来存储。

TEXT和BLOB
BLOB二进制数据
TEXT字符数据
（1）删除操作会在数据表中留下很大的空洞，以后填入这些空洞的记录在插入的性能上会有影响。为了提高性能，建议定期使用optimizetable（optimize英文意为性能优化）功能对这类表进行碎片整理，避免因为“空洞”导致性能问题。
（2）使用合成索引来提高大文本字段（BLOB或TEXT）的精确查询性能
（3）使用前缀索引（对这列的前几个字符建立索引而不是全部）对BLOB或者CLOB字段进行模糊查询
（4）在不必要的时候避免检索大型的BLOB或者TEXT值
（5）把BLOB或TEXT列分离到单独的表中

浮点数与定点数
浮点数：float、double存在误差问题，实际编程中浮点数的比较最好使用范围比较而不要使用==直接比较
定点数：decimal（或numberic）字符串形式存放，更加精确地保存数据，如应用在货币等对精度敏感的数据

日期类型选择
根据实际需要选择能满足应用的最小存储的日期类型，不仅可以节约存储，更能够提高表的操作效率。

其它

MySQL可以为整型类型指定宽度例如：int(11)，但对大多数应用没有意义，不会限制值的合法范围，只是规定交互工具如客户端用来显示字符的个数。对于存储和计算来说int(1) 和int(20)是相同的。
DECIMAL需要额外的空间个计算开销，在数据量比较大的情况下，可以考虑使用BIGINT代替，将需要存储的货币单位根据小数的位数乘以相应的倍数即可，例如需要存储的财务数据精确到万分之一，则可以把所有金额乘以一百万，然后存储在BIGINT里，这样可以同时避免浮点存储计算不精确和DECIMAL精确代价高的问题。
整数通常是标志列最好的选择，因为它们很快并且可以使用auto_increment。如果可能，应该尽量避免使用字符串类型作为标志列，因为它们很耗空间，并且通常比数字类型慢。

对于完全随机的字符串也需要注意，例如MD5()、SHA1()或者UUID()产生的字符串。这些函数生成的新值会任意分布在很大的空间内，这将导致INSERT以及SELECT语句变得很慢：
（1）因为新插入值会随机地写到索引的不同位置，所以使得INSERT语句变慢，引起页分裂、磁盘随机访问以及对于聚簇索引引擎产生聚簇索引碎片。
（2）select语句变得更慢，因为逻辑上相邻的行会分布在磁盘和内存的不同地方。
（3）随机值导致缓存对所有类型的查询语句效果都很差，因为会使得缓存赖以工作的访问局部性原理失效。

IPv4地址实际是32位无符号整数，不是字符串，分段表示仅为阅读容易，MySQL提供INET_ATON()和INET_NTOA()函数在这两种表示方法之间转换。

MySQL schema设计中的陷阱

太多的列

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

太多的关联

实体-属性-值（EAV）设计模式是一个常见的糟糕设计模式，尤其是在MySQL下不能靠谱的正常工作。《阿里巴巴Java开发手册》规定超过三个表禁止关联，需要关联的字段，数据类型需要保持绝对一致且关联字段需要有索引（尽量使用相同的数据类型存储相似或相关的值，尤其是在关联条件中使用的列）。

慎用枚举

MySQL 5.0以及更早的版本中ALTER TABLE是一种阻塞操作；即使在5.1或者更新版本中，如果不是在列表的末尾增加值也会一样需要ALTER TABLE造成阻塞；

范式和反范式

在范式化的数据库中，每个事实数据会出现并且只出现一次，相反，在反范式化的数据库中，信息是冗余的，可能会存储在多个地方。

范式化的优缺点

对于写密集的场景，范式化的设计有利于性能优化。范式化的更新操作通常比反范式化要快；当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据；范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快；

缺点在于范式化设计的schema通常需要关联，稍微复杂一些的查询语句在符合范式的schema上都可能需要至少一次关联，也许更多，这不但代价昂贵，也可能使一些索引策略无效。

反范式化的优缺点

反范式化的schema因为所有数据都在一张表中，可以很好地避免关联。如果不需要关联表，则对大部分查询最差的情况-即使没有使用索引是全表扫描，当数据比内存大时这可能比关联快得多，因为这样避免了随机I/O（全表扫描基本是顺序I/O，但也取决于引擎实现）。单独的表也能使用更有效的索引策略。

混用范式化和反范式化

在实际应用经常需要混用，可能使用部分范式化的schema、缓存表，以及其它技巧。最常见的反范式化数据的方法是复制或者缓存，在不同的表中存储相同的特定列，这有利于高效地查询数据，但也会引起更新操作代价更高，因为可能需要同时更新多张表。在实际使用中需要考虑更新的频率以及更新的时长，并和执行SELECT查询的频率进行比较（折中考虑）。

缓存表和汇总表

有时提升性能最好的方法是在同一张表保存衍生的冗余数据，然而有时也需要创建一张完全独立的汇总表或者缓存表（特别是为满足检索的需求时）。如果能容许少量的脏数据，这是非常好的方法，但是有时确实没有选择的余地（例如需要避免复杂、昂贵的实时更新操作）。这里缓存表表示存储那些可以比较简单地从schema其它表获取（但是每次获取的速度比较慢）数据的表，而汇总表保存的是使用GROUP BY语句聚合数据的表。

实时计算统计属于比较昂贵的操作，因为要么需要扫描表中的大部分数据，要么查询语句只能在特定的索引上才能有效运行，而这类特定的索引一般会对UPDATE操作有影响，所以一般不希望创建这样的索引，而建立汇总表通过牺牲一定时效性则可以避免这点。缓存表则相反，其对优化搜索和检索语句很有效。当在使用缓存表和汇总表时，必须决定是实时维护数据还是定期重建，定期重建并不只是节省资源，也可以保持表不会有很多碎片，以及有完全顺序组织的索引(这会更加高效)。当在重建汇总表和缓存表时，通常需要保证数据在操作时依然可用，这时可以采用影子表来实现。

物化视图

物化视图预先计算并存储在磁盘上的表，可以通过各种各样的策略刷新和更新。MySQL并不支持物化视图，可以使用开源的工具Flexviews来实现，做到变更数据抓取功能，可以读取服务器的二进制日志并解析相关行的变更。对比传统的维护汇总表和缓存表的方法，Flexviews基于二进制日志包含行更新前后的镜像，提取对源表的更改，可以增量地计算物化视图的内容，这意味着不需要通过查询原始数据来更新视图，这样比从源表中读取数据的效率要高很多。

计数器表

计数器表在并发更新某行记录时会存在全局的互斥锁，这会导致事务的串行执行，为了获得更高的并发更新性能，可以将计数器保存在多行中（引入槽的概念），每次随机选择一行进行更新。

update hit_counter set cnt = cnt + 1 where slot = RAND() * 100;

更快地读，更慢地写
为了提升查询的速度，经常会需要建一些额外的索引，增加冗余列，甚至是创建缓存表和汇总表。这些方法会增加写查询的负担，也需要额外的维护任务，但在设计高性能数据库时，这些都是常见的技巧：虽然写操作变得更慢了，但更显著地提高了读操作的性能。
然而写操作变慢并不是读操作变得更快所付出的唯一代价，还可能同时增加了读操作和写操作的开发难度。

加快ALTER TABLE操作的速度

MySQL的ALTER TABLE操作的性能对大表来说是个大问题，在大部分情况下，它都会锁表并且重建整张表。

只修改.frm文件

MySQL执行大部分修改表结构操作的方法是用新的表结构一个空表，从旧表中查出所有数据插入新表，然后删除旧表。这样操作可能需要花费很长的时间，如果内存不足而表很大，而且还有很多索引的情况下尤其如此。许多人都有这样的经验，ALTER TABLE操作需要花费数个小时甚至数天才能完成。一般而言大部分ALTER TABLRE操作将导致MySQL服务中断。对常见的场景，能使用的场景只有两种：一种是先在不提供服务的机器上ALTER TABLE操作，然后和提供服务的主库进行切换；另外一种技巧是“影子拷贝”。影子拷贝的技巧是用要求的表结构创建一张和源表无关的新表，然后通过重命名和删除表的操作交换两张表。也有一些工具可以帮助完成影子表的拷贝工作：如Facebook数据运维工具团队的“online schema change”工具。

ALTER TABLE允许使用ALTER COLUMN、MODIFY COLUMN和CHANGE COLUMN语句修改，这三种操作都是不一样的。ALTER COLUMN：设置或删除列的默认值（操作速度非常快）；CHANGE COLUMN：列的重命名、列类型的变更以及列位置的移动；MODIFY COLUMN：除了不能给列重命名之外，和CHANGE COLUMN是一样的。

理论上，MySQL可以跳过创建新表的步骤。列的默认值实际上存在表的.frm文件中，所以可以直接修改这个文件而不需要改动表本身。

快速创建MyISAM索引

为了高效地载入数据到MyISAM表中，有一个常用的技巧是先禁用索引、载入数据，然后重新启用索引（仅试用于非唯一索引）。在现代版本的InnoDB版本中，也有类似的技巧，这依赖于InnoDB的快速在线索引创建功能：先删除所有的唯一索引，然后增加新的列，最后重新创建删除掉的索引。

上述就是小编为大家分享的Schema与数据类型优化是什么样的了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注创新互联行业资讯频道。

文章名称：Schema与数据类型优化是什么样的
文章源于：http://shouzuofang.com/article/gjoosi.html

网站建设知识