福州开发企业网站,林芝企业网站建设公司,青岛即墨网站建设,某绿色园林企业网站源码最近在学习MySQL优化方面的知识。本文就数据类型和schema方面的优化进行介绍。1. 选择优化的数据类型MySQL支持的数据类型有很多#xff0c;而如何选择出正确的数据类型#xff0c;对于性能是至关重要的。以下几个原则能够帮助确定数据类型#xff1a;更小的通常更好应尽可能… 最近在学习MySQL优化方面的知识。本文就数据类型和schema方面的优化进行介绍。1. 选择优化的数据类型MySQL支持的数据类型有很多而如何选择出正确的数据类型对于性能是至关重要的。以下几个原则能够帮助确定数据类型更小的通常更好应尽可能使用可以正确存储数据的最小数据类型够用就好。这样将占用更少的磁盘、内存和缓存而在处理时也会耗时更少。简单就好当两种数据类型都能胜任一个字段的存储工作时选择简单的那一方往往是最好的选择。例如整型和字符串由于整型的操作代价要小于字符所以当在两者之间选择时选择整型通常能够获得更好的性能。尽量避免NULL当列可为NULL时对于MySQL来说在索引和值比较等方面需要做更多的工作虽然对性能的影响不是很大但也应尽量避免设计为可为NULL。除了以上原则在选择数据类型时需遵循的步骤首先确定合适的大类型例如数据、字符串、时间等然后再选择具体的类型。下面将讨论大类型下的一些具体类型首先是数字有两种类型整数和实数。1.1 整数类型整数类型和所占用的空间如下整数类型空间大小(bit)TINYINT8SMALLINT16MEDIUMINT24INT32BIGINT64整数类型所能存储的范围和空间大小有关-2^(N-1)至2^(N-1)-1其中N为空间大小的位数。整数类型具有UNSIGNED的可选属性当声明时表示不允许负数则存储范围变为0至2^(N)-1扩大了一倍。在MySQL中还可以为整数类型指定宽度例如INT(1)但这样的意义并不大并不会限制值的合法范围仍能存储-2^31至2^31-1的值所影响的是与MySQL的交互工具显示字符的个数。1.2 实数类型实数类型的对比如下实数类型空间大小(Byte)取值范围计算精度FLOAT4负数-3.4E38-1.17E-38非负数0、1.17E-38~3.4E38近似计算DOUBLE8负数-1.79E308-2.22E-308非负数0、2.22E-308~1.79E308近似计算DECIMAL与精度有关同DOUBLE精确计算从上面可以看出FLOAT和DOUBLE都有固定的空间大小但同时由于是使用标准的浮点运算所以只能近似计算。而DECIMAL则可以实现精确计算与此同时占用的空间会相较更大所耗费的计算开销也更多。DECIMAL所占空间大小与指定的精度有关例如DECIMAL(M,D)M为整个数字的最大长度取值范围为[1, 65]默认值为10D为小数点后的长度取值范围为[0, 30]且D M默认值为0。MySQL在存储DECIMAL类型时会作为二进制字符串存储每4个字节存9个数字当不足9位时数字的占用空间如下数字个数占用空间(Byte)1、213、425、637、84小数点前后将分别存储同时小数点也要占1个字节。下面举两个计算的例子DECIMAL(18, 9)整数部分长度为9占用4个字节。小数部分长度为9占用4个字节。同时加上小数点1个字节则总共占用9个字节。DECIMAL(20, 9)整数部分长度为14占用7(43)个字节。小数部分长度为9占用4个字节。同时加上小数点1个字节则总共占用12个字节。可以看出DECIMAL的空间占用还是很大的因此只有当需要对小数进行精确计算时才需要使用DECIMAL。除此之外我们还可以使用BIGINT代替DECIMAL例如需要保证小数点后5位的计算可以将值乘上10的5次方后作为BIGINT存储这样能同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。1.3 字符串类型最常用的字符串类型当属VARCHAR和CHAR。VARCHAR作为可变长字符串会使用1或2个额外字节记录字符串的长度当最大长度未超过255时只需1个字节记录长度超过255则需2个字节。VARCHAR的适用场景最大长度比平均长度大很多列的更新少避免碎片使用复杂的字符集如UTF-8每个字符能使用不同的字节存储。CHAR则为定长字符串根据定义的字符串长度分配足够的空间适用场景长度短长度相近例如MD5经常更新。除了VARCHAR和CHAR针对存储大字符串可以使用BLOB和TEXT类型。BLOB和TEXT的区别在于BLOB是以二进制方式存储而TEXT是以字符方式存储。这也导致BLOB类型的数据没有字符集的概念无法按字符排序而TEXT类型则有字符集的概念可以按字符排序。两者的使用场景也由存储格式决定了当存储二进制数据时例如图片应使用BLOB而存储文本时例如文章则应使用TEXT类型。1.4 日期和时间类型MySQL中所能存储的最小时间粒度为秒常用的日期类型有DATETIME和TIMESTAMP。类型存储内容空间大小(Byte)时区概念DATETIME格式为YYYYMMDDHHMMSS的整数8无TIMESTAMP从1970年1月1日零点以来的秒数4有TIMESTAMP显示的值将依赖于时区意味在不同时区查询到的值将不一样。除了以上列出的不同TIMESTAMP还具有一个特殊属性在插入和更新时如果没有指定第一个TIMESTAMP列的值将会设置这个列的值为当前时间。我们在开发过程中应尽量使用TIMESTAMP主要是因为其空间大小仅需DATETIME的一半空间效率更高。如果我们想存储的日期和时间精确到秒之后怎么办由于MySQL并未提供所以我们可以使用BIGINT存储微妙级别的时间戳或者使用DOUBLE存储秒之后的小数部分。1.5 选择标识符通常来说整数是标识符的最好选择主要是因为其简单计算快且可使用AUTO_INCREMENT。2. 范式和反范式简单来说范式就是一张数据表的表结构所符合的某种设计标准的级别。第一范式属性不可分割现在的RDBMS系统建成的表都是符合第一范式的。而第二范式则是消除非主属性对码(可以理解为主键)的部分依赖。第三范式消除非主属性对码的传递依赖。具体的介绍可以读读知乎上的这个回答(https://www.zhihu.com/question/24696366/answer/29189700)严格范式化的数据库中每个事实数据会出现且只出现一次不会出现数据冗余这样所能带能带来的好处有更新操作更快修改更少的数据表更小更好地放内存中执行操作更快更少需要DISTINCT或GROUP BY。但也由于数据分散存在各张表中查询时需要对表进行关联。而反范式的优点则是不用进行关联将数据冗余存储。在实际应用中不会出现完全的范式化或完全的反范式化时常需要混用范式和反范式使用部分范式化的schema往往是最好的选择。关于数据库设计在网上看到这样一段话大家可以感受下。数据库设计应该分为三个境界第一境界刚入门数据库设计范式的重要性还未深刻理解。这时候出现的反范式设计一般会出问题。第二境界随着遇到问题解决问题渐渐了解到范式的真正好处从而能快速设计出低冗余、高效率的数据库。第三境界再经过N年的锻炼是一定会发觉范式的局限性的。此时再去打破范式设计更合理的反范式部分。范式就像武侠里面的招数初学者妄想不按招数来只能死的很难堪。毕竟招数都是高手总结归纳的精华。而随着武功提高招数熟练之后必然是发现招数的局限性要么忘掉招数要么自创招数。只要努力加上多熬几年总能达到第二个境界总会觉得范式是经典。此时能不过分依赖范式快速突破范式局限性的人自然是高手。4. 缓存表和汇总表除了上述说到的反范式在表中存储冗余数据我们还可以创建一张完全独立的汇总表或缓存表来满足检索的需要。缓存表指的是存储可以从schema其他表中获取数据的表也就是逻辑上冗余的数据。而汇总表则指的是存储使用GROUP BY等语句聚合数据计算出的不冗余的数据。缓存表可用于优化搜索和检索查询语句这里可以使用的技巧有对缓存表使用不同的存储引擎例如主表使用InnoDB而缓存表则可使用MyISAM获得更小的索引占用空间。甚至可以将缓存表放到专门的搜索系统中例如Lucene。汇总表则是为了避免实时计算统计值所带来的高昂代价代价来自两方面一是需要扫描表中的大部分数据二是建立特定的索引会对UPDATE操作有影响。例如查询微信过去24小时的朋友圈数量则可固定每1小时扫描全表统计后写一条记录到汇总表当查询时只需查询汇总表上最新的24条记录而不必每次查询时都去扫描全表进行统计。在使用缓存表和汇总表时必须决定是实时维护数据还是定期重建这取决于我们的需求。定期重建相比实时维护能节省更多的资源表的碎片更少。而在重建时我们仍需保证数据在操作时可用需要通过“影子表”来实现。在真实表后创建一张影子表当填充好数据后通过原子的重命名操作来切换影子表和原表。5. 加快ALTER TABLE操作的速度当MySQL在执行ALTER TABLE操作时往往是新建一张表然后把数据从旧表查出并插入到新表中再删除旧表如果表很大这样需要花费很长时间且会导致MySQL的服务中断。为了避免服务中断通常可以使用两种技巧在一台不提供服务的机器上执行ALTER TABLE操作然后再与提供服务的主库进行切换“影子拷贝”建立一张与原表无关的新表在数据迁移完成后通过重命名操作进行切换。但也不是所有的ALTER TABLE操作会引起表重建例如在修改字段的默认值时使用MODIFY COLUMN会进行表重建而使用ALTER COLUMN则不会进行表重建操作速度很快。这是因为ALTER COLUMN在修改默认值时会直接修改了存在表的.frm文件(存储字段的默认值)而并未重建表。参考《高性能MySQL》MySQL DECIMAL 数据类型(https://my.oschina.net/u/559356/blog/3057960)
