数据库设计优化
- 适度的违反范式
遵循三大范式就会带来查询时经常需要join导致查询效率降低,机遇性能要求就和业务适当做冗余
- 适度的建立索引
IO更新操作会降低索引的操作,存储空间.
建立索引的规则:
a. 多音的字段必须是经常用来做查询条件的字段
b.索引是多个字段的情况,第一个字段是经常作为查询条件,放在第二个不会产生索引
c.索引的字段必须有足够的区分度
- 对表进行水平划分
例:每年10万条数据,表按照年区分建立表 log_2016 ,log_2017
- 对表进行垂直划分
字段很长,占用空间较大,检索表时需要进行大量I/O操作,将字段独立出一张表,跟原表建立一对一的关系
- 选择适当的字段类型
ID建议自增,使用小字段
- 文件图片等大文件使用文件系统存储(硬盘/挂在存储),数据库只存储文件路径
- 外键要标识清楚
-
实际工作大部分不会建立外键索引,除非增删改操作比较少
- 宁可集中批量操作,也不要频繁读写
- 选择合适的引擎
SQL优化
- 慢日志, explain
- 避免全表扫描,在where和order by的列建立索引
- 尽量避免在where字句中使用null值判断
where username is null 会放弃使用索引进行全表扫描.
解决: 设计默认值,如: 0
- 尽量避免在where子句中使用 != 或 (不等于) ,会放弃索引,全表扫描.
可以使用 小于, 小于等于,等于,大于,大于等于,between , in 会走索引
- 尽量避免在where子句中使用or来连接,会放弃索引,全表扫描,可以使用union all
- 能用between就尽量不使用in
- like 避免使用%%查询,会放弃索引,全表扫描,可以使用abc%
- 查询的时候尽量不要使用 * 作为结果列仅列出需要查询的字段,节省应用服务器的内存
- 在where中,尽量避免做列运算,尽量避免使用函数,会放弃索引,全表扫描
- Join操作 小的结果驱动大的结果.
小左主,大驱右
- 分页在基数比较大时,尽量不要使用limit,使用between
ID自增 between 100000 and 100010
- 不要使用rand获取随机条数记录
- 尽量避免使用long 不要使用count(列名),使用count(*),数据库对其做了特殊的优化
count(列名) 表示该列有多少个不为空的记录
count(*)表示整个结果集有多少条记录. 建议使用count(1)
- 不要做无谓的排序
- 出现局部变量时,强制使用索引 with(index(索引名))
- 尽量避免对字段使用表达式操作
select 1 from table where num/2 = 100 –> num = 100*2
- 用exists代替in
- 索引并不是越多越好,最好不超过6个
- 尽量使用数字类型字段 占用字节少 用varchar(变长)代替char
- 尽量避免创建临时表
- 尽量避免使用游标,游标操作超过万条记录时,必须考虑改写
- 尽量避免大事物操作,索引优化
架构优化
- 数据库参数配置,调整内存,缓存,线程池等
- 合理的硬件资源和操作系统
大部分64位的操作系统和64位mysql数据库
- 读写分离
通过mysql的复制机制实现多台机器同步,将数据库的压力分散
主库–Master ———————–> 从库 slave1
更新操作 ———————-> slave2 查询
同步数据到从库
程序需要进行特别设计
更新操作的业务到Master
查询的业务代码连接slave
解决方案: 使用Amoeba/mysql-proxy代理解决
以上观点属于个人整理,内容源于java教育视频