数据库的三范式
第一范式:列不可再分
第二范式:行可以唯一区分,主键约束
第三范式:表的非主属性不能依赖与其他表的非主属性 外键约束
且三大范式是一级一级依赖的,第二范式建立在第一范式上,第三范式建立第一第二范式上。
数据库引擎
MYISAM:全表锁,拥有较高的执行速度,不支持事务,不支持外键,并发性能差,占用空间相对较小,对事务完整性没有要求,以select、insert为主的应用基本上可以使用这引擎
INNODB:行级锁,提供了具有提交、回滚和崩溃回复能力的事务安全,支持自动增长列,支持外键约束,并发能力强,占用空间是MYISAM的2.5倍,处理效率相对会差一些
MEMORY:全表锁,存储在内容中,速度快,但会占用和数据量成正比的内存空间且数据在mysql重启时会丢失,默认使用HASH索引,检索效率非常高,但不适用于精确查找,主要用于那些内容变化不频繁的代码表
MERGE:是一组MYISAM表的组合
INNODB与MYISAM的区别
1.INNODB支持事务,MYISAM不支持事务,对于INNODB每一条SQL都会默认封装成事务,自动提交。但是这样会影响速度,最好把多条sql放在begin和commit之间,组成一个事务
2INNODB支持外键,MYISAM不支持外键,对一个包含了外键的INNODB表转成MYISAM表会失败
3.INNODB是聚合索引,数据文件和索引是绑定在一起的,必须要有主键,通过主键检索效率很高,但是辅助索引需要两次查询,先查询到主键,再通过主键查询到数据,所以,主键不能太大,否则其他所以也会很大,MYISAM是非聚合索引,数据文件是独立的,索引保存的是数据文件的指针,主键索引和辅助索引是独立的
4.INNODB不支持全文索引,MYISAM支持全文索引,所以查询效率MYISAM效率要高于INNODB
5.MYISAM和INNODB底层都使用了B+树
数据库事务
事务的特征:原子性(Atomic)、一致性(Consistency)、隔离性(lsolation)、持久性(Durabiliy),简称ACID
并发事务带的问题
多事务并发运行,经常会操作相同的数据来完成各自的任务(多个用户对同一数据操作),可能导致以下问题:
脏读: 当一个事务A正在读取某条数据时并进行了修改,但是还没有提交,此时另一个事务B也读取了改条数据,由于事务A没有提交,事务B拿到的数据就是脏数据,并不是事务A修改后的数据。
丢失修改: 当一个事务A正在读取某条数据时,此时另一个事务B也读取了改条数据,事务A对该条数据进行了修改,事务B也对该条数据进行了修改,此时该条数据的最后结果是事务B修改后的接口,事务A的修改也就丢失了,例如:事务A读取的数据是a = 5,事务BYE 读取的数据是a = 5,事务A修改a=a-1,事务B修改a=a-1,最后的结果是4,事务A的修改丢失。
不可重复读: 当事务A多次读取某条数据时,事务B也读取了该条数据并进行了修改,此时造成事务A前后读取的数据不一致
幻读: 当事务A读取部分段数据时,事务B在该部分数据中增加或删除某些数据,导致事务A再次读取是突然多出了或者减少了某些数据,像产生了幻觉一样。
事务的隔离级别
SQL标准定义了四个隔离级别:
READ-UNCOMMITTED(读取未提交): 最低隔离级别,允许读取尚未提交的数据变更,可能会导致脏读、幻读或不可重复读。
READ-COMMITTED(读取已提交): 允许读取并发事务已经提交的数据,可以阻止脏读,但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ(可重复读): 对同一字段的多次读取结果都是一致的,除非数据是被本身事务
自己所修改,可以阻止脏读和不可重复读,但幻读仍有可能发生。
SERILIZABLE(可串行化的): 最高隔离级别,完全服从ACID的隔离级别。所有的事务依次逐个执行,这样事务之间就完全不可能产生干扰,也就是说,该级别可以防止脏读、不可重复读以及幻读
MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ(可重读)。我们可以通过SELECT @@tx_isolation; 命令来查看
索引
- 主键索引
- 唯一索引
- 普通索引
- 全文索引
索引加快了数据库检索速度,唯一索引可以确保每一行数据的唯一性,索引需要占物理和数据空间
索引并非越多越好,创建所以需要消耗资源,一是增加了数据库的存储空间,二是在查询和删除时需要花费较多的时间去维护索引
索引失效的几种方式
- 使用like时,%在关键字前(%xxx_),索引失效,_xxx%在后面时索引才有效
- 使用or时,前后都要有索引字段,否则索引失效。
- 当mysql全表检索速度比索引检索块时,不使用索引
- 创建复合索引时,不是使用到第一部分索引就不会生效
- 如果mysql估计使用全表扫描要比使用索引快,则不使用索引
- where 后如有索引字段做隐式转换,会造成索引失效 (where id= ‘1’ ,id本身为 1,这里将id类型隐式的转换为了 varchar)
### sql语句优化
1.关闭唯一校验
2.修改事务提交方式,将多次提交改为一次提交
3.Oder by多使用索引排序
4.在where后尽量不要使用运算符
5.在or中尽量使用 union或者union all(无重复)
6.在where尽量避免对null的判断
7.查询中少用select *
8.尽量减少子查询,使用关联查询(left join,right join,inner join)替代
9.减少使用IN或者NOT IN ,使用exists,not exists或者关联查询语句替代
大表优化
1.限定数据范围
如:查询一个月历史订单
2.读写分离(集群)
在master写,从slave读
3.垂直分表
当列很多的时候,将列拆分为多张表,这样表中列减少,简化了表结构,易于维护
缺点:产生冗余字段,会引起join操作,事务更加复杂
4.水平分表
当表中数据超过200万时,检索速度就会变得很慢,我们可以将表中的数据拆成多张表来存放。(如:1-200W存一张表,200W-400W存一张表。。。),它支持较大的数据量存储,水平拆分最好分库。
缺点:分片事务难以解决(尽量对不要对数据进行分片)