目录
1.事务隔离级别
2.数据库三大范式
3.索引的分类
4.索引的创建原则
5.索引失效的情况
6.如何知道索引是否失效
7.MyISAM和InnoDB的区别
1.事务隔离级别
事务隔离级别是用来解决并发事务问题的方案,不同的隔离级别可以解决的事务问题不一样
读未提交: 允许读取尚未提交的数据,可能会导致脏读、幻读或不可重复读
读已提交: 允许读取并发事务已提交的数据,可以阻止脏读,但是幻读或不可重复读仍有可能发生
可重复读: 对同一字段的多次读取结果都是一致的,除非数据是被本身事务自己所修改,可以阻止脏读和不可重复读,但幻读仍有可能发生
可串行化: 所有的事务依次逐个执行,这样事务之间就完全不可能产生干扰,该级别可以防止脏读、不可重复读以及幻读。
上面的这些事务隔离级别效率依次降低,安全性依次升高,如果不单独设置,MySQL默认的隔离级别是可重复读
2.数据库三大范式
三大范式是指导设计数据库的原则
第一范式:表中的每一列不能再进行拆分,也就是每一列都应该是原子的
第二范式:一张表只做一件事,不要将多个层次的数据列保存到一张表中
第三范式:数据不能存在传递关系,也就是说可以通过其它字段推出来的字段没必要再存储
在现有的程序设计中认为第三范式是可以不遵守的,也就是通过添加冗余字段,来减少多表联查或计算,我们称为反三范式
3.索引的分类
索引是数据库中用于提供查询效率的一种手段
从物理存储角度上分为聚集索引和非聚集索引
聚集索引指的是数据和索引存储在同一个文件中,InnoDB存储引擎使用的是此类索引存储方式
非聚集索引指的是数据和索引存储在不同的文件中,MyISAM存储引擎使用的是此类索引存储方式
从逻辑角度上分为普通、唯一、主键和联合索引,它们都可以用来提高查询效率,区别点在于
唯一索引可以限制某列数据不出现重复,主键索引能够限制字段唯一、非空
联合索引指的是对多个字段建立一个索引,一般是当经常使用某几个字段查询时才会使用,它比对这几个列单独建立索引效率要高
4.索引的创建原则
索引可以大幅度提高查询的效率,但不是所有的字段都要加,也不是加的越多越好,因为索引会占据磁盘空间,也会影响增删改的效率
我们在建立索引的时候应该遵循下面这些原则:
主键字段、外键字段应该添加索引
经常作为查询条件、排序条件或分组条件的字段需要建立索引
经常使用聚合函数进行统计的列可以建立索引
经常使用多个条件查询时建议使用组合索引代替多个单列索引
除此之外,下面这些情况,不应该建立索引
数据量小的表不建议添加索引
数据类型的字段是TEXT、BLOB、BIT等数据类型的字段不建议建索引
不要在区分度低的字段建立索引,比如性别字段、年龄字段等
5.索引失效的情况
索引失效指的是虽然在查询的列上添加了索引,但是某些情况下,查询的时候依旧没有用到索引,常见的情况有
使用like关键字时,模糊匹配使用%开头将导致索引失效
使用连接条件时,如果条件中存在没有索引的列会导致索引失效
在索引列上进行计算、函数运算、类型转换将导致索引失效
使用 !=、not in、is null、is not null时会导致索引失效
使用联合索引时,没有遵循最左匹配原则会导致索引失效
6.如何知道索引是否失效
MySQL中自带了一个关键字叫explain,它可以加在一个sql的前面来查看这条sql的执行计划
在执行计划中,我们主要观察两列的结果,一列是type,一列是extra
第一个type是重要的列,显示本次查询使用了何种类型,常见的值从坏到好依次为:all、index、range、ref、eq_ref 、const、system
all表示全表扫描数据文件返回符合要求的记录
index表示全表扫描索引文件返回符合要求的记录
range表示检索指定范围的行,常见于使用>,<,between,in,like等运算符的查询中
ref表示两表查询时,驱动表可能返回多行数据,也就是查询条件在主表中是加了一个普通索引
eq_ref表示两表查询时,驱动表只返回一行数据,也就是查询条件在主表中是唯一的
const表示索引一次就能得到结果,一般是使用唯一索引或者主键作为查询条件
system表示表中仅有一行数据,很少见到
我们在优化的时候尽量优化到range级别以上
除了type之外我们需要关注一下extra列,它表示执行状态说明
要保证此列不要出现using filesort、using temporary等使用临时表或外部文件的情况
如果出现using index最好了,它表示列数据仅仅使用了索引中的信息而没有回表查询
7.MyISAM和InnoDB的区别
MyISAM和InnoDB是目前MySQL中最为流行的两种存储引擎,它们的区别有这几方面:
MyISAM不支持事务,每次操作都是原子的;InnoDB支持事务,支持事务的四种隔离级别
MyISAM不支持外键,InnoDB支持外键
MyISAM仅仅支持表级锁,即每次操作是对整个表加锁;InnoDB支持行级锁,因此可以支持写并发
MyISAM属于非聚集性索引,它的数据和索引不在同一个文件中;InnoDB属于聚集性索引,它的数据和索引在同一个文件中
MyISAM中主键和非主键索引的数据部分都是存储的文件的指针;InnoDB主键索引的数据部分存储的是表记录,非主键索引的数据部分存储的是主键值