Design6选择合适的数据类型探索.docxVIP

数据库使用Table来存储海量的数据，细分Table结构，数据最终存储在Table Column中，因此，在设计Table Schema时，必须慎重选择Table Column的Data Type，数据类型不仅决定了Column能够存储的数据范围和能够进行的操作，而且合适的数据类型还能提高查询和修改数据的性能。数据类型的选择标准既要满足业务和扩展性的需求，又要使行宽最小（行宽是一行中所有column占用的Byte）。最佳实践：使用与Column最大值最接近的数据类型。 例如，bit 类型只能存储1和0，能够对bit进行逻辑比较（=或），不能对进行算术运算（+,-,*,/,%），不要对其进行或的比较，虽然bit类型支持，但是，这不 make sense。 declare @b1 bit declare @b2 bit set @b1=1 set @b2=0 -- right,return 0 select iif(@b1=@b2,1,0) --error,The data types bit and bit are incompatible in the add operator. select @b1+@b2 在设计Table Schema时，要实现三大目标：占用空间少，查询速度快???更新速度快。这三个目标有些千丝万缕的关联，设计良好的Table Schema，都会实现，反之，设计差的Table Schema，都不能实现。 内存是访问速度最快的存储介质，如果数据全部存储在内存中，那会极大的提高数据库系统的吞吐量，但是，每个数据库系统能够使用的内存有限，为了提高查询性能，SQL Server将最近使用过的数据驻留在内存中。SQL Server 查询的数据必须在内存中，如果目标数据页不在内存中，那么SQL Server会将数据从Disk读取到内存中。SQL Server 响应时间跟数据加载很大的关系，如果加载的数据集占用的空间小，数据页分布集中，那么SQL Server使用预读机制，能够很快将数据加载到内存，相应地，SQL Server的响应时间会很小。 创建索引能够提高查询性能，其实是因为，索引字段比Base Table的字段少，索引结构占用的存储空间小，SQL Server 加载索引结构的耗时少。由于索引结构是有序的，避免了全表扫描，也能提高查询性能。使用窄的数据类型，使用数据压缩，创建BTree索引，创建ClumnStore 索引，都能减少数据集占用的存储空间，提高数据加载到内存的速度。SQL Server在执行用户的查询请求时，每一行数据都必须在内存中，因此，数据集占用的空间越少，加载的过程越快，SQL Server的查询性能越高。 一，窄的数据行会节省存储空间，减少IO次数 使用窄的数据类型，使行的宽度达到最小，在存储相同数据量时，能够节省存储空间，减少Disk IO的次数。 在存储日期数据时，Date占用3Byte，DateTime占用8Byte，DateTime2(2)占用6Byte，DateTime2(4)占用7Byte，DateTime2(7)占用8Byte。不管从表示的精度上，还是从占用的存储空间上来看，DateTime2(N)都完胜DateTime。 例如，存储‘yyyy-mm-dd MM:HH:SS’格式的日期数据，有以下4中选择： 使用字符串 varchar(19) 或 nvarchar(19)存储，十分不明智，前者占用19Byte后再占用38Byte； 使用数据类型 datetime2(7)存储，占用8Byte，虽然精度更高，但是毫秒都是0，浪费存储空间； 使用数据类型 datetime存储，占用8Byte，如果需要存储毫秒，datetime不满足； 使用数据类型 datetime2(2)存储，占用6Byte，相比较是最理想的。 由于SQL Server存储数据是按照row存储数据的，每个Page能够存储的数据行是有限的。在查询同等数量的数据行时，如果row宽度窄，那么每个page会容纳更多的数据行，不仅减少IO次数，而且节省存储空间。 二，在窄的数据列上创建index，能够提高查询性能 在窄的数据列上创建Index，索引结构占用的存储空间更小，SQL Server消耗更少的Disk IO就能将索引结构加载到内存中，能够提高查询性能。 在创建Index时，必须慎重选择聚集索引键，主要有两个原因 1，聚集索引其实就是表本身，SQL Server必须保持物理存储顺序和逻辑存储顺序一致 在SQL Server中，Clustered Index能够确定Table的物理存储，使Table的物理存储顺序和聚集索引键的逻辑顺序保持一致。在对Table数据进行update时，如果更新聚集索引键，导致数据行所在聚集索