SQL笔记之数据库结构优化
1. 拆分表（冷热数据分离）
不常用的数据为冷数据，反之则为热数据。
如果一个表中的数据存在明显的使用频率差异，那么可以将冷热数据分离。
通过这种分解可以提高表的查询效率。
对于字段很多且有些字段使用不频繁的表，可以通过这种分解的方式来优化数据库的性能。
例如：
会员members表存储会员登录认证信息，该表中有很多字段，如id、姓名、密码、地址、电 话、个人描述字段。
其中地址、电话、个人描述等字段并不常用，可以将这些不常用的字段分解出另一 个表。
2. 增加中间表
假如当前有两个表，学生信息表包含id、学号、姓名、年龄和班号，班级表包含id、班级名、地址、班长。
若现在有一个模块需要经常查询带有学生名称（name）、学生所在班级名称（className）、学生班级班 长（monitor）的学生信息。
根据这种情况可以创建一个 temp_student 表。temp_student表中存储学生名称（stu_name）、学生所在班级名称（className）和学生班级班长（monitor）信息。
以后，可以直接从temp_student表中查询学生名称、班级名称和班级班长，而不用每次都进行联合查 询。这样可以提高数据库的查询速度。
3. 增加冗余字段
设计数据库表时应尽量遵循范式理论的规约，尽可能减少冗余字段，让数据库设计看起来精致、优雅。 但是，合理地加入冗余字段可以提高查询速度。
表的规范化程度越高，表与表之间的关系就越多，需要连接查询的情况也就越多。
尤其在数据量大，而 且需要频繁进行连接的时候，为了提升效率，我们也可以考虑增加冗余字段来减少连接。
4. 优化数据类型
（1）对整数类型数据进行优化。
遇到整数类型的字段可以用 INT 型 。这样做的理由是，INT 型数据有足够大的取值范围，不用担心数 据超出取值范围的问题。
刚开始做项目的时候，首先要保证系统的稳定性，这样设计字段类型是可以 的。
但在数据量很大的时候，数据类型的定义，在很大程度上会影响到系统整体的执行效率。
对于 非负型 的数据（如自增ID、整型IP）来说，要优先使用无符号整型 UNSIGNED 来存储。
因为无符号 相对于有符号，同样的字节数，存储的数值范围更大。
如tinyint有符号为-128-127，无符号为0-255，多出一倍的存储空间。
（2）既可以使用文本类型也可以使用整数类型的字段，要选择使用整数类型。
跟文本类型数据相比，大整数往往占用更少的存储空间 ，因此，在存取和比对的时候，可以占用更少的 内存空间。
所以，在二者皆可用的情况下，尽量使用整数类型，这样可以提高查询的效率。
如：将IP地 址转换成整型数据。
（3）避免使用TEXT、BLOB数据类型
（4）避免使用ENUM类型
修改ENUM值需要使用ALTER语句。
ENUM类型的ORDER BY 操作效率低，需要额外操作。使用TINYINT来代替ENUM类型。
（5）使用TIMESTAMP存储时间
TIMESTAMP存储的时间范围1970-01-01 00:00:01 ~ 2038-01_19-03:14:07。
TIMESTAMP使用4字节，DATETIME使用8个字节，同时TIMESTAMP具有自动赋值以及自动更新的特性。
（6）用DECIMAL代替FLOAT和DOUBLE存储精确浮点数
- 非精准浮点： float, double
- 精准浮点：decimal
Decimal类型为精准浮点数，在计算时不会丢失精度，尤其是财务相关的金融类数据。
占用空间由定义的宽度决定，每4个字节可以存储9位数字，并且小数点要占用一个字节。
可用于存储比bigint更大的整型数据。
总之，遇到数据量大的项目时，一定要在充分了解业务需求的前提下，合理优化数据类型，这样才能充 分发挥资源的效率，使系统达到最优。
5. 优化插入记录的速度
插入记录时，影响插入速度的主要是索引、唯一性校验、一次插入记录条数等。根据这些情况可以分别进行优化。
（1）MyISAM引擎的表：
- ① 禁用索引
- ② 禁用唯一性检查
- ③ 使用批量插入
插入多条记录时，可以使用一条INSERT语句插入一条数据，也可以使用一条INSERT语句插入多条数据。
插入一条记录的INSERT语句情形如下：
insert into student values(1,'zhangsan',18,1); insert into student values(2,'lisi',17,1); insert into student values(3,'wangwu',17,1); insert into student values(4,'zhaoliu',19,1);
使用一条INSERT语句插入多条记录的情形如下：
insert into student values (1,'zhangsan',18,1), (2,'lisi',17,1), (3,'wangwu',17,1), (4,'zhaoliu',19,1);
第2种情形的插入速度要比第1种情形快。
- ④ 使用LOAD DATA INFILE 批量导入
当需要批量导入数据时，如果能用LOAD DATA INFILE语句，就尽量使用。
因为LOAD DATA INFILE语句导入数据的速度比INSERT语句块。
（2） InnoDB引擎的表：
- ① 禁用唯一性检查
插入数据之前执行set unique_checks=0来禁止对唯一索引的检查，数据导入完成之后再运行set unique_check=1。
这个和MyISAM引擎的使用方法一样。
- ② 禁用外键检查
- ③ 禁止自动提交
6. 使用非空约束
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持。
