【MySQL数据库】第四章解读：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 &＃39;0000-00-00 00:00:00&＃39;
    ……
）

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

4.3.1优缺点

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

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

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

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

缺点：

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

4.3.2反范式的优点和缺点

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

缓存表：

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

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

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

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

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

但是对唯一索引无效（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索引、包括唯一索引

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

1. 最好避免使用bit

2. 使用小而简单的合适类型；

3. 尽量使用整型定义标识列

4. 避免过度设计，比如会导致极复杂查询的schema设计，或很多列；

5. 应该尽可能避免使用null值，除非真实数据模型中有确切需要

6. 尽量使用相同的类型存储相似、相关的值，特别是关联条件中使用的列

7. 注意可变长字符串，其在临时表和排序时可能导致悲观的按max长度分配内存

8. 避免使用遗弃的特性，如指定浮点数的精度，或整数的显示宽度

9. 小心使用enum和set，虽然他们用起来很方便，但不要滥用，有时会变陷阱

10. 范式是好的，但反范式有时也是必要的；预先计算、缓存或生成汇总表也可获很大好处

11. alter table 大部分情况会锁表且重建整张表（让人痛苦）本章提供了一些有风险的方法，大部分场景必须使用其他更常规的方法

相关文章：

【MySQL数据库】第三章解读：服务器性能剖析（上）

【MySQL数据库】第三章解读：服务器性能剖析 （下）

以上就是【MySQL数据库】第四章解读：Schema与数据类型优化（下）的详细内容，更多请关注 第一PHP社区 其它相关文章！