Java面试题———MySql篇②
目录
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主键索引的数据部分存储的是表记录，非主键索引的数据部分存储的是主键值