MySQL数据库优化小结

有什么错漏，说一下，数据库这些都是经验之谈，总有错过的。

 

一 数据库设计

以前都说三大范式，具体应该叫数据库范式

第一范式-表的数据不重复，数据是唯一的

第二范式-表的数据有主键，数据是有主属性可查的

第三范式-表的其他普通数据不依赖其他普通数据，就是依赖的数据记得给索引。要用其他属性做查询条件记得用索引

巴斯-科德范式(我称为三个半范式)-表的数据与其他表的数据（子集）的关系，表的其他普通数据 不依赖 其他表的数据，外键。其他表的数据（子集）不该有表的冗杂数据，表不该有其他表的数据（子集）的冗杂数据，而且还要有他的主键或者索引（外键）

以上基本面试可能问到，三个半范式答出来算加分项。后面的范式就到了基本这时候就到了代码设计事务逻辑这层次了。

 

第四范式-表插入、删除、修改数据时，不传递未修改的数据。其他表（子集）修改数据设计到表依赖的索引时要传递修改

//false
sql.update(dto)；

//true
Dto new =new Dto();
new.setChangeData(dto.getChangeData());
sql.update(new)；

第五范式（完美范式）-表的数据仅含有其他表的数据（子集）的主键。查询相关数据时要连表查询，表基本变成索引表（字典表）。ps.每次新增要新增其他子集表！！

TABLE{
TNO,
USERNO,
CARNO
}

 

实际上

因为需求的多变性，除非一些大型的不思进取的项目，基本都不会整个项目符合第五范式，顶多某一部分用了字典表。而每次新增都要新增其他子集表，给数据库带来极大压力。

故第五范式淘汰。总结，字典表可小功能实现，不要求到整个项目。

因为表插入、删除、修改数据时，不传递未修改的数据可以用代码控制，而其他表（子集）修改数据（表依赖的索引）时要传递修改到表，则同样给数据库带来一部分压力，而且为什么不借鉴第六范式，外键为其主键，规定其不可修改，那么就不存在传递修改到表。

故第四范式扑街。总结，用代码控制插入修改删除的传递，外键通过不可变和数据一致性来取消使用。

因为现在过量设计的原因，能冗杂就冗杂，例如一个用户表一个文章表，文章必显示用户昵称头像，难道还要再查一次用户数据？

故第三个半范式牺牲。总结，过量设计，适当冗杂。

因为涉及到时间排序查询时，给时间做索引基本不会起作用，依然是全表查询，所以整条数据都依赖时间的时候，不给时间加索引。

故第三范式死不瞑目。总结，索引设计要合理。

 

也就是说我们设计数据库，符合第一第二范式就是一个合格的基本设计了。给表加主键！而其他的地方都是我们优化的点！

 

二 常用的的优化

字段固定长度，尽可能短。

可确定值设计为enum性，如男女省份等（快是快了，但是要改的话就麻烦了，所以不建议）

使用not null（null，empty并不是不存数据，null存了null数据，empty则存相应类型的默认值，那设置为null还要在程序做个判断多麻烦啊）ps.mybatis返回自动过滤null，所以java接触到这情况很少。但是其他语言有啊。

水平划分。

垂直划分。

大数据保存地址。

不使用Distinct，用Group By，exist 代替

批量操作，不是存储过程那种批量操作。（查询返回list，插入参数list）

查询不用select*

查询一个时，使用limit 1.

查询条件先索引再让容易过滤的过滤。

时间的比较，远比时间转为毫秒比较慢。

多表查询用join不用子查询

不用order by rand()（这个是全查完在重排，正常是边查边重排）

启动慢查询日志。

启动查询缓存

更换数据库引擎。（到了这步，还不如重新设计）

读写服务分离。

 

三 索引设计

如何建立索引一定会用到explain命令，查询性能分析，以前写过，现在就不说了。

https://www.cnblogs.com/ydymz/p/9167734.html

https://www.cnblogs.com/ydymz/p/9167637.html

 

而索引的设计只要绕开索引失效的情况，而索引失效的情况explain的返回就有

 

例如

MyISAM的索引长度超过1000就大幅降低性能，最好在500左右。（这个是真网上看来，自己试都没试）组合索引所有索引的最大长度总和不能超过1000，单个索引最大长度不超过500

建有索引的字段上尽量不要使用函数进行操作，除非做了函数索引。（不是所有数据库支持）

索引表应该小于数据表。查询索引表时间长于数据表，那还不如全表查询。这个时候说明你要重新设计表或者索引了（水平垂直划分，前缀索引）。

比较用了强制类型转换，索引不生效。

使用模糊查询，索引不生效。

使用<>,!=，not in，not exist等运算，索引不生效。

查询得到的结果太大，接近总量的30%（接近总量的1%都很恐怖了）

单独引用复合索引里非第一位置的索引列。