数据库优化可以从单体优化和分布式优化来做。
单体优化
表结构
- 从字段上来说,可以使用char,不适用varchar,即用小不用大。
- 再创建表的时候,可以适当违反3NF创建冗余字段,比如员工和部门,前端使用员工人数比较多的话,可以在部门表设计一个字段表示员工的数量,省去join查询的时间,但是在员工表有人员变动的时候,可以使用触发器,更新部门表的人数。
- 创建合适的索引,减少查询时间。
- 使用合适的引擎。
- 优化sql。
- 垂直分表和水平分表。
关于索引
索引的底层原理
索引是Btree,是类似于二叉树,创建索引的时候,会将字段进行编号,存入到二叉树,查找的时候,以二叉树进行查找,就可以定位到需要查找的数据。
索引的分类
单列索引
单列索引又可以分为
- 普通索引
允许重复的值出现,可以在任何字段上面添加 - 唯一索引
除了不能有重复的记录外,其它和普通索引一样,可以在值是唯一的字段添加(用户名、手机号码、身份证、email,QQ),可以为null,并且可以有多个null - 主键索引
是随着设定主键而创建的,也就是把某个列设为主键的时候,数据库就会給改列创建索引。这就是主键索引.唯一且没有null值(也被称为非聚集索引) - 全文索引
用来对表中的文本域(char,varchar,text)进行索引, 全文索引针对MyISAM有用
单列索引失效情况(不会使用索引)
- sql语句使用模糊查询,以%开头.
- 使用or,in,not in,is null,!=等
复合索引
复合索引就是使用多个字段创建的索引
复合索引失效的情况
- sql的where条件后面只是用了第二个字段。
- 以及单列索引失效的情况。.
索引的优缺点
优点:
索引提高了查询速度,select where 。
缺点:
因为索引的存在,降低了修改、删除,插入的效率,因为这三个操作会重构索引。而且因为索引的存在,要占用磁盘空间。
关于sql优化
DDL优化(定义)
- 通过禁用索引,提供数据导入性能,最后再恢复索引,即变多次索引维护为一次索引维护。
- 关闭唯一校验,提高数据导入性能,再后再开启校验,变多次校验为一次。
- 变多次事务提交为一次。
DML(manage) - 变多次插入为一次插入,使用批量插入。
DQL优化 - order by 使用索引。
- limit优化 在分页是使用一个有索引的字段排序
select film_id,description from film order by title limit 50,5;
存储引擎
-
MYISAM:不支持事务,不支持外键,支持全文索引,支持表锁,速度快。
适用于以select和insert为主。 -
InooDB:支持事务,支持外键,比支持全文索引,支持表锁,但是访问速度慢。
适用于有事务要求有update的需求的。
关于分表(数据量达到500w,可以考虑分表)
水平分表:是对行进行分为几个表。可以按照业务来分,比如说按月份来分。
关于水平分表主键id生成以及定位的问题:
可以采用雪花算法生成一个全局唯一的主键,然后java代码调用hashcode方法获取hash值,并对id值进行取余的方法确定数据存放的位置,类似于hashmap存值取值得原理。
垂直分表:垂直分表是对列进行拆分。比如用户表,像用户的地址是很少查询和变动,可以将用户的地址单独分一张表,使用同一个主键
多机优化
当单体优化不满足需求时,可以采用:
- 读写分离,主机只负责写,从负责读,通过二进制日志文件来同步。
- 按业务进行集群
使用缓存
使用redis做缓存,减少数据库访问次数。
排查慢sql
以慢查询方式启动数据库,设置值慢查询时间,在日志中查看慢sql,再使用explain 分析sql语句慢的原因。