良好的逻辑设计和物理设计是高性能的基石,前期的表结构设计质量,会直接影响基于表结构的业务SQL性能,所以,表结构的设计质量,直接关系到系统的运行情况,甚至是系统的用户体验,本章,主要对MySQL支持的数据进行讲解,说明数据类型的优缺点及适用场景 一、选择数据类型的基本原则1. 更小的通常最好一般情况下,应该尽量选择可以正确存储数据的最小数据类型,因为它们占用的磁盘、内存和CPU缓存更少,并且调用CPU周期也更短,但是如果由于前期选择的数据类型支持的范围不足以支持业务数据,需要修改数据类型以增加数据范围,这将需要一个非常耗时和痛苦的操作,所有在设计数据类型时,无法确认哪个数据类型是最好的,就选择你觉得不会超过范围的最小类型。 2. 简单就好简单的数据类型的操作通常需要更少的CPU周期,例如整形比字符串代价更低、使用内建类型(datetime)而不是字符串来保持时间 3. 尽量避免NULLnull是MySQL数据列的默认属性,但是通常情况下,最好知道列为NOT NULL,除非系统真的需要保持NULL值,查询中包含NULL的列,对MySQL来说,更难优化,因为NULL会使得所有、索引统计更为复杂 二、数据类型在为列选择数据类型时,第一步需要确定合适的大致类型,下一步就是选择具体的类型,有些数据,有几种数据类型都支持存储,只是它们的存储范围、精度不一样,这就需要我们根据业务来选择更加合适的类型,下面将对MySQL支持的数据类型进行介绍 1.整数类型数据类型: 特性:
tip:整数可以指定宽度,但是幷不能限制数据的范围,它只是规定了在交互工具上显式的字符个数,对于存储来说,INT(1)和INT(20)没有区别 2.实数整形数据类型: 特性: tip:可以通过对要存储的高精度小数据数据乘以相应的倍数后用bigint来进行存储,因为bigint开销更,当然,只有在数据量比较大的时候才需要考虑这样做。 3.字符串类型数据类型: 特性:
tip:1. 当使用VARCHAR 时,如果表使用ROW_FORMAT = FIXED,每一行都会使用定长存储,这回很浪费空间 4.日期和时间类型数据类型: 特性:
5.位数据类型数据类型: 6.特殊类型数据inteIPv4地址,可以使用无符号整数存储IP地址,MySQL提供inet_ATON()和INET_NTOA()来转化ip地址 三、MySQL schema设计中的陷阱1.太多的列MySQL的存储引擎API在工作时,需要在服务器层和存储引擎层之间进行缓冲格式拷贝数据,然后在服务器层将缓存内容解码成各个列,而转换的代价依赖列的数量。 2.太多的关联大多的关联,会导致解析和优化查询的代价成为问题,单个查询,最好在12个表以内做关联 3.全能的枚举使用枚举,当需要在枚举列表中增加一个新的值时,需要做一次ALTER TABLE操作,这是全表的阻塞操作,操作起来会很麻烦 4.非此发明的NULL避免使用NULL会带来很多好处,而且即使需要存储null值,也可以考虑使用其他的特殊值来代替,但是也不能走极端,当使用其他替代方案会导致不确定性,或者bug时,也许null幷不是不能接受的,而且MySQL会在索引中存储null的值,只是处理起来相比于not null 更复杂一点 三、范式和反范式1.范式的优点和缺点
范式化设计的schema通常的缺点是需要关联,稍微复杂一点的语句在符合范式的schema上都可能需要至少一次关联 2.反范式的优点和缺点
范式化和反范式化各有优缺点,在正常的情况下,我们应综合业务需要,混合使用二者,所谓,黑猫白猫,能抓老鼠的才是好猫。 四、缓存表和汇总表有时,提升性能最好的办法是在同一张表中保存衍生的冗余数据,然而有时候也需要创建一张完全独立的汇总表和缓存表,汇总表,指的是对某些数据进行聚合而产生具有统计功能的表,缓存表用来存储那些可以比较简单获取但获取速度比较慢的数据。 汇总表 缓存表 1.物化视图许多数据库管理系统都提供了一个叫物化视图的功能,物化视图实际上是预先计算并且存储在磁盘上的表,可以通过各种各样的策略刷新和更新,MySQL并不支持物化视图,然而我们可以使用Jsitin swanhart的开源工具Flexviews,对比传统的维护汇总表和缓存表的方法,Flexviews通过提取对源表的更改,可以增量的重新计算物化视图的内容。 2.计数器表计数器表最大的问题就是会遇到更新并发的问题,当多个线程对计数字段进行更新时,因为互斥的关系,这些更新需要串行化的执行,这会严重影响性能,这里有个和好的技巧可供参开,而且这个思路也可以在其他地方获益 比如,你要记录一个网站的点击次数,你可能会建一张表
每点击一次,就执行如下sql:
但是当许多人点击时,这些事务只能串行的执行,这时,可以考虑如下修改:
这时,在进行更新时,就可以随机对一行数据进行更新
当要获得统计结果时,需要使用下面这样的聚合查询
更进一步,每隔一段时间开始一个新的计数器,可以做如下修改:
更新则可使用如下语句
五、加快ALTER TABLE 操作的速度MySQL的ALTER TABLE操作的性能对大表来说是个大问题,MySQL执行大部分修改表结构操作的方法是用新的结构创建一个空表,从旧表中查出素有数据插入新表,然后删除旧表,这样的操作可能需要花费很长的时间,如果内存不足而表又很大,而且需要很多索引的情况下尤其如此。 对于常见的场景,能使用的技巧有两种: 理论上,MYSQL可以跳过创建新表的步骤,列的默认值实际上存在表的.frm中,可以直接修改这个文件而不需要改动表本身,然而mysql还没有采取这种优化的方法,所以ALTER TABLE 操作都将导致表重建
这个语句会直接修改.frm而不涉及表数据 tip:ALTER TABLE 允许使用ALTER COLUMN 、MODIFY COLUMN 、CHANGE COLUMN 语句修改列 只修改.frm文件有时候mysql会在没有必要的时候重建表,如果愿意冒一点风险,可以让mysql做一些其他类型的修改而不用重建表。
快速创建索引
总结良好的schema设计原则是普遍适用的,但是MySQL有他自己的实现细节要注意,概况来讲,尽可能保持任何东西小而简单总是好的。有以下简单的原则值得你去考虑使用
|
|