Schema与数据类型优化的示例

共计 3375 个字符，预计需要花费 9 分钟才能阅读完成。

自动写代码机器人，免费开通

丸趣 TV 小编给大家分享一下 Schema 与数据类型优化的示例，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！

4.2MySQL schema 设计中的陷阱 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 大部分情况会锁表且重建整张表（让人痛苦）本章提供了一些有风险的方法，

看完了这篇文章，相信你对“Schema 与数据类型优化的示例”有了一定的了解，如果想了解更多相关知识，欢迎关注丸趣 TV 行业资讯频道，感谢各位的阅读！