频道澳门葡京手机版网址
登录注册
澳门葡京手机版网址 > 数据库 > 其他综合 > 正文
Schema与数据类型优化深度剖析
2018-08-17 13:55:48      个评论      
收藏   我要投稿

4.2MySQL schema设计中的陷阱

因为mysql实现机制导致了一些特定错误,如何避免,慢慢道来:

1、太多的列

MySQL存储引擎api工作时需要在服务器层和存储引擎层通过行缓冲格式拷贝数据,然后在服务器层将缓冲内容解码成各个列,从行缓冲中将编码过的列转换成行数据的操作代价高,myisam定长行与服务器行结构正好匹配,不需要转换;但是变长行结构 InnoDB的行结构总是需要转换,转换代价依赖于列的数量。

2、太多的关联

实体-属性-值EAV:糟糕的设计模式,mysql限制了每个关联操作最多只能有61张表,但EAV数据库需许多自关联;一个粗略的经验法则,如果希翼查询实行得快速且并发性好,单个查询最好在12个表内做关联;

3、防止过度使用枚举

注意防止过度使用枚举;使用外键关联到字典表或查找表查找具体的值,在mysql中,需要在枚举列表中添加值时,要做一次alter table;MySQL5.0更早alter table阻塞操作,5.1更新版本中,不是在列表末尾增加值也会一样需要alter table

?

4、非此发明not invent here的null

建议存空值可以用0、特殊值、空字符串代替,尽量不要null;但是不要走极端,在某些场景下、使用null会更好:

create table ……(
//全0 (不可能的日期)会导致很多问题
    dt datetime not null default '0000-00-00 00:00:00'
    ……
)

MySQL会在索引中存储null值Oracle不会

?

4.3范式与反范式

4.3.1优缺点

1、范式化的更新操作更快

2、当数据较好地范式化时,很少有重复数据,只需要修改更少的数据

3、范式化的表更小,可更好地放到内存里,实行操作更快

4、很少冗余数据,检索列表数据时更少需要distinct、group by语句

缺点:

需要关联,有代价且可能使索引无效

4.3.2反范式的优点和缺点

避免关联,数据比内存大可能比关联要快很多(避免了随机I/O)

?

4.4缓存表和汇总表

缓存表:

对优化搜索和检索查询语句很有效,

存储那些可以较简单地从其他表获取数据(每次获取速度比较慢)的表

汇总表:保存使用group by语句聚合数据的表

使用时决定是实时维护数据还是定期重建,定期重建:节省资源、碎片少、顺序组织的索引(高效)

重建时,保证数据在操作时依然可用,通过“影子表”来实现,影子表:一张在真实表背后创建的表,在完成建表操作后,可通过原子的重命名操作切换影子表和原表

\

?

4.4.1物化视图

预先计算并存在磁盘上的表,可通过各种策略刷新和更新,mysql不原生支撑,可使用Justin Swanhart工具flexviews实现:

flexviews组成:

变更数据抓取,读取服务器二进制日志且解析相关行的变更 一系列可以帮助 ?创建和管理 ?视图 的定义 ? 的 ? 存储过程 一些可应用变更到 ? ?数据库中的物化视图 ? ?的工具

flexviews通过提取对源表的更改,可增量地重新计算物化视图的内容:不需要查询原始数据(高效)

?

4.4.2计数器表

计数器表:缓存一个用户朋友数、文件下载次数等,推荐创建一张独立的表存储计数器,避免查询缓存失效;

更新加事务,只能串行实行,为了更高的并发性,可将计数器保存在多行,每次随机选一行更新,要统计结果时,聚合查询;(这个我读了两三边,可能比较笨吧,就是同一个计数器保存多分,每次选其中一个更新,最后求和,好像还不是很好理解哈,多读几遍吧)

?

4.5加快alter table 操作的速度

mysql大部分修改表结构是:用新的结果创建空表、从旧表中查出all数据插入新表,删除旧表

mysql5.1及更新包含一些类型的“在线”操作的支撑,整个过程不需要全锁表,最新版的InnoDB(MySQL5.5和更新版本中唯一的InnoDB)支撑通过排序来建索引,建索引更快且紧凑的布局;

一般而言,大部分alter table导致mysql服务中断,对常见场景,使用的技巧

1、先在一台不提供服务的机器上实行alter table操作,然后和提取服务的主库进行切换

2、影子拷贝,用要求的表结构创建张和源表无关的新表,通过重命名、删表交换两张表(上有)

不是all的alter table都引起表重建,理论上可跳过创建表的步骤:列默认值实际上存在表的.frm文件中,so可直接修改这个文件不需要改动表本身,但mysql还没有采用这种优化方法,all的modify column将导致表重建;

\

alter column:通frm文件改变列默认值:alter table容许使用alter column、modify column change column修改列,三种操作不一样;

alter table sakila.film alter column rental_duration set default 5;

?

4.5.1只修改frm文件

mysql有时在没有必要的时候也重建表,如果愿冒一些风险,可做些其他类型的修改而不用重建表:下面操作可能不能正常工作,先备份数据

下面操作不需要重建表:

? ? ?1、移除一个列的auto_increment

? ? ?2、增加、移除、更改enum和set常量,如果移除的是被用到的常量、查询返回空字符串

基本技术为想要的表结果创建新的frm文件,然后用它替换掉已经存在的那张表的frm文件:

? ? ?1、创建一张有相同结构的空表,进行所需的修改

? ? ?2、实行flush tables with read lock:关闭all正在使用的表且禁止任何表被打开

? ? ?3、交换frm文件

? ? ?4、实行unlock tables释放第2步的读锁

示例略

?

4.5.2快速创建myISAM索引

1、为高效地载入数据到MyISAM表,常用技巧:先禁用索引、载入数据、重启索引:因为构建索引的工作延迟到数据载入后,此时可通过排序构建索引,快且使得索引树的碎片更少、更紧凑

\

但是对唯一索引无效(disable ?keys),myisam会在内存中构造唯一索引且为载入的每一行检查唯一性,一旦索引大小超过有效内存、载入操作会越来越慢;

2、在现代版InnoDB中,有个类似技巧:先删除all非唯一索引,然后增加新的列,最后重建删除掉的索引(依赖于innodb快速在线索引创建功能)Percona server可自动完成这些操作;

3、像前alter table 的骇客方法来加速这个操作,但需多做些工作且承担风险,这对从备份中载入数据很有用,如already know all data is effective ,and no need to do the unique check

? ? 用需要的表结构创建一张表,不包括索引(如用load data file 且载入的表是空的,myisam可排序建索引) 载入数据到表中以构建MYD文件 按需要的结构创建另外一张空表,这次要包含索引,会创建.frm .MYI文件 获读锁并刷新表 重命名第二张表的frm文件 MYI,让mysql认为这是第一张表的文件 释放读锁 使用repair table来重建表的索引,该操作会通过排序来构建all索引、包括唯一索引

?

4.6总结

良好的schema设计原则是普通使用的,但mysql有自己的实现细节要注意,概括来说:尽可能保持任何东西小而简单总是好的;mysql喜欢简单(好恰、我也是)

最好避免使用bit 使用小而简单的合适类型; 尽量使用整型定义标识列 避免过度设计,比如会导致极复杂查询的schema设计,或很多列; 应该尽可能避免使用null值,除非真实数据模型中有确切需要 尽量使用相同的类型存储相似、相关的值,特别是关联条件中使用的列 注意可变长字符串,其在临时表和排序时可能导致悲观的按max长度分配内存 避免使用遗弃的特性,如指定浮点数的精度,或整数的显示宽度 小心使用enum和set,虽然他们用起来很方便,但不要滥用,有时会变陷阱 范式是好的,但反范式有时也是必要的;预先计算、缓存或生成汇总表也可获很大好处 alter table 大部分情况会锁表且重建整张表(让人痛苦)本章提供了一些有风险的方法,大部分场景必须使用其他更常规的方法
点击复制链接 与好友分享!回澳门葡京手机版网址澳门葡京手机版网址
上一篇:算法中的算术表达式
下一篇:入编住房和城乡建设产品BIM大型数据库
相关文章
图文推荐

关于大家 | 联系大家 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 澳门葡京手机版网址_澳门新莆京娱乐_www.88807.com - 点此进入--致力于做实用的IT技术学习网站

XML 地图 | Sitemap 地图