MySQL数据库设计优化

本章关注的是MySQL数据库的设计，主要介绍的是MySQL数据库设计与其他关系型数据库管理系统的差别。

一、选择优化的数据类型

1.1原则

①跟小的通常更好；

②简单就好；

③尽量避免null;

1.1.1整数类型

类型：tinyint，smallint，mediumint，int，bigint，分别使用8，16，24，32，64位存储空间，存储值的范围-2(n-1)—2(n-1)-1，其中n是存储空间的位数。

属性：unsigned，没有符号，整数存储范围提高一倍。

1.1.2实数类型

类型：float，double，decimal（支持精准小数计算）

1.1.3字符串类型

类型：varchar，char，blob，text；

varchar：变长字符串，比定长类型更节省空间，适合字符串的最长长度比平均长度长很多的情况，需要额外的记录长度的字节空间；

char:定长字符串，会根据需要采用空格进行填充，适合定长的数据；

blob：二进制方式存储；

text：字符方式存储；

使用枚举替代字符串类型的好处：

①枚举会将字符串存储在一个预设的集合中，存储会变得紧凑，并在内部通过整数保存；

②缩小表的大小，主键及其他索引更小；

坏处：

①修改索引预设值时，必须要alter table；

②关联查询时，enum和enum会更快，但是enum和varchar效率会很低；

1.1.4日期和时间类型

类型：datetime，timestamp；timestamp只用4个字节的存储空间，范围也更小（1970-2038年）；

1.1.5位数据类型

类型：bit，set；

1.1.6选择标识符

选择标识符在表中一般作为外键使用，应该选择与关联表相同的数据类型；通常整数类型时最好的选择，字符串、enum/set均为糟糕的类型。

1.1.7特殊的数据类型

IP地址：人们通常用字符串来存储ip地址，但是实际上，他是32位无符号的整数。存储时，可以使用MySQL内置的函数：INET_ATON()，INET_NTOA()来进行转换。

二、MySQL设计的陷阱

太多的列，太多的关联，全能的枚举，变相的枚举，非此发明的null。

三、范式与反范式

概念：

第一范式：所有属性都是不可分割的原子值。

第二范式：在第一范式的基础上，要求非主属性都要和码有完全依赖关系。

依赖：若在一张表中，在属性（或属性组）X的值确定的情况下，必定能确定属性Y的值，那么就可以说Y函数依赖于X，写作 X → Y。

码：设 K 为某表中的一个属性或属性组，若除 K 之外的所有属性都完全函数依赖于 K（这个“完全”不要漏了），那么我们称 K 为候选码，简称为码。

第三范式：任何非主属性不依赖于其它非主属性。

3.1范式的优点和缺点

优点：

①范式更新操作通常比反范式快；

②数据较号的范式化，很少重复，只需要修改更少数据；

③范式表通常更小；

④很少多余数据，以为着，更少需要distinct和group by语句；

**缺点：**通常以为着关联，复杂查询时，至少需要一次关联，代价昂贵；

3.2反范式的优点和缺点

所有数据都在一个表，不需要关联；可以更有效的使用索引策略；

四、缓存表和汇总表

有时，提升性能的最好方法是在同一张表中保存冗余数据，或者需要创建一张完全独立的汇总表或者缓存表。

例如，web应用中，需要统计文件下载数，可以使用一张计数器表，保存用户下载的次数；但是累加次数时，由于写互斥锁的存在，可能造成程序的阻塞，可以增加统计的行数，累计插入时，随机在不同行id之间执行操作，需要结果时，进行累加汇总即可。

五、加快ALTER TABLE

①主库切换：在另一台服务器执行ALTER TABLE操作，然后进行服务的切换；

②影子拷贝：创建一张新表，通过重命名和删表的方式进行切换；

③alter column：直接修改.frm文件，而不会重建表；

④修改.frm文件：

⑤快速创建索引：先禁用索引、载入数据，然后重建索引；该操作对主键索引无效，所以可以先删除主键索引，然后重新生成；

⑥黑客方法：存在风险，请谨慎操作！！！