数据库优化篇（一）—— SQL语句优化建议
目录
-
前言 优化前知识准备
MySQL表关系
字段类型设计
数据库三范式
书写高质量SQL优化建议
前言
优化前知识准备
-
1、表之间的关系
上一篇文章最后，创建表同时设置了约束。那么，约束还有一个非常重要的作用是建立表与表之间的联系，通过外键关联，最终形成完整的业务数据库。
①一对多
一对多是一个实体的某个数据与另外一个实体的多个数据有关联关系， 比如: 用户表和用户订单表。一个用户有多个订单。
②一对一
比如: 员工表和员工职位表。一个用户对应一个职位名称。
③多对多
比如: 学生表和教师表、学生教师关系表。一个学生可以有多个教师，一个教师也可以有多个学生。
2、字段类型设计
① 字段类型优先选择符合存储需要的最小类型
字段类型优先级：int>date;time >enum>char;varchar>blob
原因：整型，time运算快，节省内存；enum列内部是用整型存储的，char，varchar要考虑字符集的转换和排序的校对集，速度慢；blob无法使用临时表。
② 够用就行（如smallint，varchar(N)）
原因：大的字段浪费内存，影响速度，如varchar(10),varchar(300),虽然存储的内容一样，但是,在表联查时，varchar(300)要花更多内存。
③ 尽量避免使用允许为null()
原因：null不利于索引，要用特殊的字节标注，在磁盘上占的空间其实更大
例子：建两个相同字段的表，一个允许为null，一个不允许。可以发现为null的索引更大些。
④ 避免使用ENUM类型
修改ENUM值需要使用ALTER语句，ENUM类型的ORDER BY操作效率低，需要额外操作，禁止使用数值作为ENUM的枚举值。
⑤ 使用TIMESTAMP（4个字节）或DATETIME类型（8个字节）存储时间
TIMESTAMP 存储的时间范围 1970-01-01 00:00:01 ~ 2038-01-19-03:14:07
TIMESTAMP 占用4字节和INT相同，但比INT可读性高。
超出TIMESTAMP取值范围的使用DATETIME类型存储。
⑥ 金额类数据使用decimal
Decimal类型为精准浮点数，在计算时不会丢失精度。
占用空间由定义的宽度决定，每4个字节可以存储9位数字，并且小数点要占用一个字节，可用于存储比bigint更大的整型数据。
3、数据库的三大范式
① 字段不可再分
用户信息遵循了第一范式的要求，这样在对用户使用城市进行分类的时候就非常方便，也提高了数据库的性能。
② 非主属性完全依赖于主键（确保表中的每列都和主键相关）
第二范式在第一范式的基础之上更进一层。一个表只能保存一种数据，不能将多种数据存在同一张数据表里。比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键，如下表所示：这样就产生一个问题：这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关，而仅仅是与商品编号相关。
所以在这里违反了第二范式的设计原则。而如果把这个订单信息表进行拆分，把商品信息分离到另一个表中，把订单项目表也分离到另一个表中，就非常完美了。这样设计，在很大程度上减小了数据库的冗余。如果要获取订单的商品信息，使用商品编号到商品信息表中查询即可。
③ 确保每列都和主键直接相关，不能间接相关
比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。如下面这两个表所示的设计就是一个满足第三范式的数据库表。
这样在查询订单信息的时候，就可以使用客户编号来引用客户信息表中的记录，也不必在订单信息表中多次输入客户信息的内容，减小了数据冗余。
书写高质量SQL优化建议
1、避免使用SELECT * 替换成SELECT <字段列表> 查询
原因：消耗更多的CPU和IO以网络带宽资源；无法使用覆盖索引；可减少表结构变更带来的影响
2、避免使用不含字段列表的INSERT语句
# 如：insert into values ('a','b','c');# 应使用：insert into t(c1,c2,c3) values ('a','b','c');
3、Inner join 、left join、right join，优先使用 Inner join，如果是 left join，左边表结果尽量小
Inner join 内连接，在两张表进行连接查询时，只保留两张表中完全匹配的结果集。
left join 在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录。
right join 在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录。
都满足 SQL 需求的前提下，推荐优先使用 Inner join（内连接），如果要使用 left join，左边表数据结果尽量小，如果有条件的尽量放到左边处理。
4、避免使用JOIN关联超过5个以上表
对于Mysql来说，是存在关联缓存的，缓存的大小可以由join_buffer_size参数进行设置。
原因：在Mysql中，对于同一个SQL多关联（join）一个表，就会多分配一个关联缓存，如果在一个SQL中关联的表越多，所占用的内存也就越大。如果程序中大量的使用了多表关联的操作，同时join_buffer_size设置的也不合理的情况下，就容易造成服务器内存溢出的情况，就会影响到服务器数据库性能的稳定性。同时对于关联操作来说，会产生临时表操作，影响查询效率，Mysql最多允许关联61个表，建议不超过5个。
5、 减少同数据库的交互次数
数据库更适合处理批量操作，合并多个相同的操作到一起，可以提高处理效率。比如：插入数据过多，考虑批量插入。
6、 慎用 distinct 关键字
distinct 关键字一般用来过滤重复记录，以返回不重复的记录。在查询一个字段或者很少字段的情况下使用时，给查询带来优化效果。但是在字段很多的时候使用，却会大大降低查询效率。
7、 禁止使用order by rand() 进行随机排序
原因：order by rand()会把表中所有符合条件的数据装载到内存中，然后在内存中对所有数据根据随机生成的值进行排序，并且可能会对每一行都生成一个随机值，如果满足条件的数据集非常大，就会消耗大量的CPU和IO及内存资源。
推荐在程序中获取一个随机值，然后从数据库中获取数据的方式。
8、 使用 where 条件限定要查询的数据，避免返回多余的行
需要什么数据，就去查什么数据，避免返回不必要的数据，节省开销。
9、 在明显不会有重复值时使用UNION ALL 而不是UNION
UNION 会把两个结果集的所有数据放到临时表中后再进行去重操作，
UNION ALL 不会再对结果集进行去重操作
10、 拆分复杂的大SQL为多个小SQL
大SQL逻辑上比较复杂，需要占用大量CPU进行计算的SQL
MySQL中，一个SQL只能使用一个CPU进行计算
SQL拆分后可以通过并行执行来提高处理效率。
11、where 子句中考虑使用默认值代替 null
# 反例select * from user where age is not null;# 正例# 设置0为默认值select * from user where age>0;