MySQL几道面试题

1. 什么是事务？特性？

事务含义：作为一个单元的一组有序的数据库操作，要么全执行，要么全不执行【基本逻辑单位】

• 原子性：整个事务中的所有操作，要么全部完成，要么全部不完成。若执行过程中发生错误则就回滚到事务开始前的状态
• 一致性：事务开始前到结束后，数据库完整性约束没受到破坏；（实体完整性，参照完整性，用户定义的完整性）
• 隔离性：在隔离状态下执行事务，让并发事务之间不会相互影响
   【为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求处理同一数据】
• 持久性：事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作

2.说一说脏读、不可重复读、幻读？

1. 脏读：一个事务在处理过程中读取了另外一个事务未提交的数据
   例如：事务A修改了一行数据，但是还没有提交，
   这时候事务B读取了被事务A修改后的数据，之后事务A因为某种原因回滚了，那么事务B读取的数据就是脏的

2. 不可重复读：一个事务范围内，多次查询某个数据，却得到不同的结果。【数据本身的对比】
   例如：事务A读取某一数据，事务B读取并修改了该数据，A为了对读取值进行检验而再次读取该数据，便得到了不同的结果

3. 幻读：读取整体的数据后，因其他事务对数据的更新【插入或者删除】，再次查询时结果不同，【数据条数的对比】
   例如：
   事务 A对一个学生成绩表查询数学成绩为80分的人数有5人，
   这时事务 B又对这个表中插入了一条数学成绩也为80分的学生记录，并且提交给数据库。
   而操作事务 A的用户如果再查看数学成绩为80分的，会发现有6条数据，就会产生幻读。

3.如何通俗理解三个范式？

• 第一范式：
  1）1NF是对属性的原子性约束，即表中所有的字段值都是不可再分割的原子值
  2）某个属性不能有多个值，属性不能有重复的
  例如：

  学号	姓名	年龄	性别	所在地 [可再分]
  001	张三	20	男	吉林省、长春市

• 第二范式：
  1）2NF要先满足1NF
  2）每个表有且仅有一个数据元素为主键,
  3）非主属性需完全依赖于主键

  例子:（学号和课程号为联合主键，学分、课程名不与主键学号相关，存在数据之间的部分依赖）

  学号	姓名	性别	课程号	课程名	学分	成绩
  001	张三	男	002	java	3	85

  拆分改良：

  学号	姓名	性别
  001	张三	男

  课程号	课程名	学分
  0524	java	3

  学号	课程号	成绩
  001	0524	85

• 第三范式：【独立性】
  1）3NF要先满足2NF
  2）一个数据库表中不能包含已在其它表中已包含的非主关键字信息
  3）如果一个实体中出现其他实体的非主属性，可以将这两个实体用外键关联
  例子：学生信息表和选课表【选课编号做外键关联】

  学号	姓名	性别	年龄	选课编号
  001	0524	85	22	0514

  选课编号	课程名	学分	课时
  0514	java	3	48

  范式化设计优缺点:
  优点：可以尽量得减少数据冗余，使得更新快，体积小，减少存储成本
  缺点：对于查询需要多个表进行关联，拉低了读写效率，更难进行索引优化
  

  注意：目前的项目应用并不会完全遵循范式的理念，通过反范式的设计来提高性能
  1.性能降低：没有任何冗余的表设计会产生更多的查询行为，即产生更多次的数据库IO操作，拉低了性能
  2.成本降低：磁盘存储设备成本的降低
  但是在项目中仍然需要根据自身的业务特点来选择范式或者反范式，一般是两者的结合
  

  反范式化：通过增加冗余、聚合的手段来提升性能
  优点：可以减少表得关联，可以更好得进行索引优化
  缺点：数据冗余以及数据异常，数据得修改需要更多的成本
  

4. 说几条你能想到的sql优化？

【重点】：尽量避免索引被放弃从而导致全表扫描查找

1. 任何地方都不要用 select * from table ,用具体的字段列表替换 *，不要返回用不到的字段

2. 尽量避免在 where 子句中对字段进行 null 值判断，比如：
   select id from t1 where num is null
   可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
   select id from t1 where num=0

3. 应尽量避免在 where 子句中使用 or 来连接条件，可替换成union all或者unin 如：
   select id from t1 where num=10 or num=20
   优化后的查询：
   select id from t1 where num=10
   union all
   select id from t1 where num=20

4. in 和 not in 也要慎用，可替换成 between、exists，如：
   对于连续的数值，能用 between 不用 in ：
   select id from t where num in(1,2,3)
   优化后：
   select id from t where num between 1 and 3
   或者用 exists 代替 in ：
   select num from t1 where num in(select num from t2)
   优化为：
   select num from t1 where exists(select 1 from t2 where num=t1.num)

5. 尽量避免在where子句中使用 ！= 或 <>操作符，索引将被放弃使用，会进行全表查询。
   例如:
   select id from t1 where id != 5
   优化成：select id from t1 where id>5 or id<5

6. 尽量避免在关键字前加 %
   在adc前有%必然会走全表查询
   select id from t where name like ‘%abc%’
   select id from t where name like ‘%abc’
   优化使用：
   select id from t where name like ‘abc%’

索引的几个问题

1. 索引的目的是什么？
   1）快速访问数据表中的特定信息，提高检索速度

   2）创建唯一性索引，保证数据库表中每一行数据的唯一性。

   3）加速表和表之间的连接

   4）使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间

2. 索引对数据库系统的负面影响是什么？

   1）创建索引和维护索引需要耗费时间，这个时间随着数据量的增加而增加；
   2）索引需要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间
   3）当对表进行增、删、改、的时候索引也要动态维护，这样就降低了数据的维护速度。

3. 为数据表建立索引的原则有哪些？
   1）在最频繁使用的、用以缩小查询范围的字段上建立索引。
   2）在频繁使用的、需要排序的字段上建立索引

4. 什么情况下不宜建立索引？
   1）对于查询中很少涉及的列或者重复值比较多的列，不宜建立索引。
   2）对于一些特殊的数据类型，不宜建立索引，比如文本字段（text）等