[TOC] ### 选择优化的数据类型 　 ### MySQL schema设计中的陷阱 ### 范式和反范式 范式和反范式: ``` 在范式花数据库中，每个事实数据会出现并且只出现一次。 反范式化的数据库中，信息是冗余的，可能会存储在多个地方。 ``` #### 范式的优缺点 范式化带来的好处: ``` 1. 范式化的更新操作通常比反范式化要块。 2. 当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据。 3. 范式化的表通常更小，可以更好的放在内存里，所以执行操作更快。 4. 很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句。　 ``` ### 缓存表和汇总表 #### 物化视图 #### 计数器表 ### 加快ALTER TabLE操作的速度 MySQL的ALTER TABLE操作的性能对大表来说是个大问题。 ``` MySQL执行大部分修改表结构操作的方法是用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表。 ``` #### 只修改.frm文件 大部分ALTER TABLE操作将导致MySQL服务中断。 对常见的场景，使用的技巧有两种: ``` 1. 先在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换。 2. “影子拷贝“的技巧是用要求的表结构创建一张和源表无关的新表，然后通过重命名和删表操作交换两张表。 ``` 修改或删除一个列的默认值。假如要修改电影的默认租赁期限，从是哪天改到五天，很慢的方式是: ``` mysql> ALTER TABLE sakila.film ->MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5; ``` SHOW STATUS 显示这个语句做列1000次读和1000次插入操作。它拷贝了整张表到一张新表，甚至列的类型、大小和可否为NULL属性都没改变。 理论上列的默认值实际上存在表的.frm文件中，可以直接修改这个文件而不需要改动表的本身。然而MySQL还没有采用这种优化的方法，所有的MODIFY COLUMN操作都将导致表重建。 很快的方法是通过ALTER COLUMN操作来改变列的默认值: ``` mysql> ALTER TABLE sakila.film -> ALTER COLUMN rental_duration SET DEFAULT 5; ``` 下面这些操作是有可能不需要重建表的: ``` 移除（不是增加）一个列的AUTO_INCREMENT属性 增加、移除或更改ENUM和SET常量。如果移除的是已经有行数据用到其值的常量，查询将会返回一个空字串值。 ``` 该技巧不是官方支持的，执行前请备份数据。 为想要的表结构创建一个新的.frm 文件，然后用它替换掉已经存在的那张表.frm文件: ``` 1. 创建一张有相同结构的空表，并进行所需要的修改（例如增加ENUM常量） 2. 执行FLUSH TABLES WITH READ LOCK。这将会关闭正在使用的表，并且禁止任何表被打开。 3. 交换.frm文件 4. 执行UNLOCK TABLES 来释放第二步的读锁。 ``` #### 快速创建MyISAM索引 高效地载入数据到MyISAM表中，有一个常用的技巧是先禁用索引、载入数据，然后重新启用索引： ``` mysql> ALTER TABLE test.load_data DISABLE KEYS; -- load the data mysql> ALTER TABLE test.load_data ENABLE KEYS; ``` 这个方法对唯一索引无效。DISABLE KEYS只对非唯一索引有效。 MyISAM会在内存中构造唯一索引，并且为载入的每一行检查唯一性。一旦索引的大小超过列有效内存，载入操作就会变得越来越慢。 * InnoDB也有类似的技巧 ``` 这依赖InnoDB快速在线索引创建功能。 这个技巧是先删除所有的非唯一索引，然后增加新的列，最后重新创建删除掉的索引。Percona Server可以自动完成这些操作。 ``` * ALTER TABLE的方法来加速这个操作。当已经知道所有数据都是有效的并且没有必要做唯一性检查时: ``` 1.用需要的表结构创建一张表，但是不包括索引。 2. 载入数据到表中以构建.MYD文件 3. 按照需要的结构创建另外一张空表，这次要包含索引。这会创建需要的.frm和.MYI文件 4. 获取读锁并刷新表 5. 重命名第二张表的.frm和.MYI文件，让MySQL认为是第一张表的文件 6. 释放读锁 7. 使用REPAIR TABLE来重建表的索引。该操作会通过排序来构建所有索引，包括唯一索引。 ``` ### 总结