架构和数据类型优化示例

4.2 MySQL模式设计中的陷阱

1.列太多

MySQL存储引擎api工作时，需要在服务器层和存储引擎层通过以row buffer格式复制数据，然后解码缓冲区内容在服务器层将其编码为各个列，并将编码后的列转换为行缓冲区中的行数据。操作由myisam高度决定 长行完全匹配服务器行结构，不需要转换；然而，InnoDB的变长行结构总是需要转换，转换成本取决于列数。

2.关联太多

Entity-Attribute-Value EAV：糟糕的设计模式，MySQL 限制了每个关联操作只能有最多61张表，但EAV数据库需要很多自相关；根据粗略的经验，如果希望查询执行速度快并且并发性好，最好单次查询在12张表中进行关联；

3.防止过度使用枚举

小心防止过度使用枚举；使用外键与字典表或查找表关联以查找特定值。在mysql中，需要在枚举列表中添加值时，需要做alter table； MySQL5.0早期的alter table阻塞操作，5.1更新版本，即使在列表末尾添加一个值也需要alter table

4。 Null 不是这里发明的

建议使用 0、特殊值或 null 来存储 null 值。字符串替换，尽量不要使用null；但不要走极端，在某些场景下，使用 null 会更好：

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

MySQL会在索引中存储空值，Oracle不会

4.3范式和反范式

4.3.1优化缺点< /h3>

1. 标准化更新操作更快

2. 数据标准化良好时，重复数据很少，需要修改的数据更少

p>< p>3. 规范化表更小，可以更好地放在内存中，并且执行操作更快

4. 冗余数据很少，检索列表数据时需要较少的区别。、group by 语句< /p>

缺点：

需要关联，成本较高，并且可能会使索引失效

4.3.2 反范式的优缺点

避免关联，大于内存的数据可能比关联快很多（避免随机I/O）

缓存表：

对于优化搜索和检索查询语句非常有效，< /p>

存储可以更方便地从其他表获取数据的表（每次检索速度较慢）

汇总表：保存使用group by语句

使用时，决定是实时维护数据还是定期重建数据。 定期重建：节省资源，碎片少，索引顺序组织（高效）

重建时，保证数据在运行过程中仍然可用，通过实现“影子表”。影子表：在真实表后面创建的表。表创建操作完成后，可以通过原子操作进行重命名。切换影子表和原始表

4.4.1 物化视图

预先计算并存储在磁盘上的表，可以通过各种策略进行刷新和更新。 MySQL 本身并不支持它。可以使用Justin Swanhart工具flexviews来实现：

Flexviews组成：

更改数据 Fetch，读取服务器二进制日志并解析其中的更改相关行

一系列存储过程，可以帮助创建和管理视图的定义

一些可以将更改应用于数据库视图工具中的具体化

flexviews可以通过提取源表的更改来增量重新计算物化视图的内容：无需查询原始数据（高效）

4.4.2 Counter表

计数器表：缓存用户的好友数量、文件下载次数等，建议创建独立的表来存储计数器，避免查询缓存失败；

更新和交易只能y 串行执行。对于更高的并发，可以将计数器保存在多行中，每次随机选择一行更新，并且必须统计结果。 When，聚合查询； （这个我看了两三遍，可能比较笨，意思是同一个计数器保存多个点，每次选择其中一个更新，最后总结一下，好像不是很好理解。请多读几遍。）

4.5 加快alter table操作

对mysql表结构的修改大部分是：创建一个空表，并有新的结果，从旧表中查找所有数据并将其插入到新表中。删除旧表

MySQL 5.1 及更高版本包括对某些类型的“在线”操作的支持。整个过程不需要全表锁。最新版本的InnoDB（MySQL 5.5及以上版本中唯一的InnoDB）支持通过排序建立索引，建立索引更快且布局紧凑；

一般来说，大多数alter table都会导致mysql服务中断。对于常见场景，使用提示：

1.先在不提供服务的机器上执行alter table操作，然后与提取服务的主数据库进行切换

2.卷影复制，使用所需的表结构创建一个与源表无关的新表，并通过重命名和删除表来交换两个表（如上所示）

Alter not所有这些都会导致表重建。理论上可以跳过创建表的步骤：列默认值实际上存在于表的.frm文件中。所以可以直接修改这个文件而不改变表本身。不过mysql还没有采用这种优化方式。 修改全部列会导致表重建；

alter column：通过frm文件更改列的默认值：alter table允许you 使用altercolumn、modifycolumn 更改column 来修改列。三个操作不同；

alter table sakila.film alter columnrental_duration set default 5;

4.5.1仅修改frm 文件

mysql 有时会在不需要时重建表。如果你愿意承担一些风险，你可以在不重建表的情况下进行其他类型的修改：以下操作可能无法正常工作，请先备份数据

以下操作不需要重建表：

1.去掉某列的auto_increment

2.添加、删除和更改枚举并设置常量。如果删除了使用过的常量，则查询返回空字符串

基础技术为所需的frm文件创建一个新的表结果，然后用它来替换现有表的frm文件：

1.创建一个相同结构的空表并进行所需的修改

2、执行flush带有读锁的表：关闭所有正在使用的表，并禁止任何表被打开

3.交换frm文件

4.执行unlock table释放步骤2中的读锁定

示例省略

4.5.2 快速创建myISAM索引

1.为了高效地将数据加载到MyISAM表中，常用技巧： 首先禁用索引，加载数据，然后重新启动索引：因为构建索引的工作会延迟到数据加载之后，此时可以通过排序来建立索引，速度更快，并且索引树的碎片更少。 ，更加紧凑

但对于唯一索引无效（禁用键），myisam会在内存中构建唯一索引并检查加载的每一行的唯一性，一旦索引大小超过有效内存，加载操作就会越来越慢；

2.在现代版本的InnoDB中，有一个类似的技巧：首先删除所有非唯一索引，然后添加新的ones列，最后重建删除的索引（依靠innodb的快速在线索引创建功能）Percona服务器可以自动完成这些操作；

3．像之前的alter table这样的hacking方法可以加速这个操作，但是需要做额外的工作并承担风险，这对于从备份加载数据很有用，已经知道所有数据都是有效的，并且不需要做唯一的检查

创建一张需要的表结构表，不包含索引（比如使用加载数据文件，并且加载的表为空，myisam可以排序并建索引）

将数据加载到表中即可构建MYD文件

按照需要的结构再创建一个空表，这次要包含索引，会创建一个.frm .MYI文件

获取读锁并刷新该表

重命名第二个表的frm文件MYI，让mysql认为是第一个表该表的文件

释放读锁

使用repair table重建表的索引。这个操作会通过排序构建所有索引，包括唯一索引

4.6总结

良好的模式设计原则是常用的，但是mysql有自己的实现细节需要注意。总结：让任何东西尽可能小和简单总是好的；mysql喜欢简单（巧合的是，我也是）

最好避免使用bit

使用小而简单的适当类型；

尽可能使用整数定义标识列

避免过度设计，例如模式设计会导致极其复杂的查询，或多列；

应尽量避免空值，除非真实数据模型中有精确值需要

尽量使用相同的类型存储相似且相关的值，尤其是关联条件中使用的列

注意变长字符串，这种字符串可能出现在临时表a中nd 排序导致按最大长度悲观分配内存

避免使用废弃的功能，例如指定浮点数的精度，或整数的显示宽度

小心 enum和set，虽然它们有用很方便，但是不要滥用它，有时它会成为陷阱

正常形式是好的，但反正常形式有时是必要的；预计算、缓存或生成汇总表也能获得很大的好处

大多数情况下，alter table 会锁定表并重建整个表（这是痛苦的）。本章提供了一些有风险的方法。

读完本文后，相信你会对《Schema 和数据类型优化示例》有一定的了解。如果您想了解更多相关知识，请关注行业资讯频道。感谢您的阅读！