大数据面试题之Mysql:每日三题(四)

我很喜欢的一句勉励今天的自己和大家：“人生当中成功只是一时，失败却是主旋律，但是如何面对失败，却把人分成了不同的样子，有的人会被失败击倒，有的人会爬起来继续向前。我想真正的成熟应该不是追求完美，而是直面自己的缺憾，这才是生活的本质。罗曼罗兰曾说过：“这个世界上只有一种真正的英雄主义，那就是认清生活的真相并且仍然热爱它。”

为什么使用索引

索引是存储引擎用于快速找到数据记录的一种数据结构，就好比一本教课书的目录部分，通过目录中找到对应文章的页码，便可快速定位到需要的文章。Mysql中也是一样的道理，进行数据查找时，首先查看查询条件是否命中某条索引，符合则通过索引查找相关数据，如果不符合则需要全表扫描，即需要一条一条地查找记录，直到找到与条件符合的记录。

假如给数据使用二叉树这样的数据结构进行存储

对字段Col2添加了索引，就相当于在硬盘上为Col2维护了一个索引的数据结构，即这个二叉搜索树。二叉搜索树的每个结点存储的是(K,V)结构，key是Col2，value是该key所在行的文件指针(地址)。比如：该二叉搜索树的根节点就是：(34,0x07)。现在对Col2添加了索引，这时再去查找Col2=89这条记录的时候会先去查找该二叉搜索树(二叉树的遍历查找)。读34到内存，89》34，继续右侧数据，读89到内存，89==89；找到数据返回。找到之后就根据当前结点的value快速定位要查找的记录对应的地址。我们可以发现，只需要查找两次就可以定位到记录的地址，查询速度就提高了。
这就是我们为什么要建立索引，目的就是为了减少磁盘I/O的次数，加快查询速率。

1.索引的优缺点

索引的优点
1.可以通过建立唯一索引或者主键索引，保证数据库表中每一行数据的唯一性；
2.建立索引可以大大提高检索的数据，以及减少表的检索行数；
3.在表连接的连接条件，可以加速表与表直接的相连；
4.在分组和排序字句进行数据检索，可以减少查询时间中分组和排序时所消耗时间(数据库的记录会重新排序)
5.建立索引，在查询中使用索引，可以提高性能。
索引的缺点
1.创建索引和维护索引，会耗费时间，随着数据量的增加而增加；
2.索引文件会占用物理空间，除了数据表需要占用物理空间之外，每一个索引还会占用一定的物理空间。
3.当对表的数据进行insert，update，delete的时候，索引也要动态的维护，这样就会降低的维护速度，(建立索引会占用磁盘空间的索引文件).
提示：索引可以提高查询的速度，但是会影响插入记录的速度。这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后再创建索引。

2.普通索引，唯一索引，主键索引，联合索引的区别

普通索引：由关键字key或者index定义的索引的唯一任务是加快对数据的访问速度，一张表允许创建多个普通索引，并且允许数据重复和NULL。

主键索引（Primary key）：数据表的主键列使用的就是主键索引。一张数据表有只能有一个主键，并且主键不能为null，不能重复。

唯一索引（Unique Key）：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为NULL，一张表允许创建多个唯一索引。建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。也就是说，唯一索引可以保证数据记录的唯一性。

联合索引：可以覆盖多个数据列，像index（columnA,columnB)索引，这就是联合索引

主键和唯一索引的区别主要有以下几点：
1）主键一定会创建一个唯一索引，有唯一索引的列不一定为主键；
2）主键不允许空值，唯一索引列允许空值；
3）一个表只能有一个主键，但是可以有多个唯一索引；
4）主键可以被其它表引用为外键，唯一索引列不可以；
5）主键是一种约束，而唯一索引是一种索引，是表的冗余数据结构，两者存在本质的区别。

3.MySQL有哪些锁

• 事务:一组操作要么全部成功，要么全部失败，目的是为了保证数据最终的一致性

• 事务ACID特性：

  • 原子性(Atomicity) ：当前事务的操作要么同时成功，要么同时失败。原则性由undo log日志来保证
  • 一致性（Consistency）：使用事务的最终目的，由业务代码正确逻辑保证
  • 隔离性(Islation):在事务并发执行时，他们内部的操作不能相互干扰
  • 持久性(Durability):一旦提交了事务，它对数据库的改变就应该是永久性的。持久性由redo log日志来保证
    • Mysql引入了redo log，Buffer Pool内存写完了，然后会写一份redo log，这份redo log记载着这次在某个页面上做了什么修改，即便mysql在中途挂了，我们还可以根据redo log来对数据进行恢复，redo log是顺序写的，写入速度很快。并且它记录的是物理修改（xxx页做了xxx修改），文件的体积很小，恢复速度很快。

• 事务隔离性
  innoDB引擎中，定义了四种隔离级别供我们使用，级别越高事务隔离性越好，但性能越低，而隔离性是由Mysql的各种锁以及MVCC机制来实现的

  • read uncommit(读未提交）：有脏读问题
    • 脏读：事务A读取到事务B未提交的数据，数据不一致。
  • read commit（读已提交）：有不可重复读问题
    • (并发更新) 指在同一次事务中前后查询不一致的问题
  • repeatable read（可重复读）：有幻读问题
    • (并发插入，删除) 一次事务中前后数据量发生变化，用户产生，不可预料的问题
  • serializable(串行)：上面问题全部解决

• 读锁(共享锁，S锁)：Select…lock in share mode; //串行化
  读锁是共享的，多个事务可以同时读取同一个资源，但不允许其他事务修改。

• 写锁(排它锁，x锁)：select… for update;
  写锁是排他的，会阻塞其他的写锁和读锁，update，delete，insert都对加写锁

• MVCC机制
  MVCC多版本并发控制，就可以做到读写不阻塞，且避免了类似脏读这样的问题，主要通过undo日志链来实现 事务id和回滚指针
  read commit(读已提交)，语句级快照
  repeatable read (可重复读)，事务级快照

• 长事务的影响

  • 并发情况下，数据库连接池容易撑爆
  • 锁定太多的数据，造成大量的阻塞和锁超时
  • 执行时间长，容易造成主从延迟
  • 回滚所需要的时间比较长
  • undo log膨胀
  • 容易导致死锁

• 长事务的优化

  • 将查询等数据准备操作放在事务外
  • 事务中避免远程调用，远程调用要设置超时，防止事务等待时间太久
  • 事务中避免一次性处理太多数据，可以拆分成多个事务分次处理
  • 更新等涉及加锁的操作尽可能放在事务靠后的位置
  • 能异步处理的尽量异步处理
  • 应用侧(业务代码)保证数据一致性，非事务执行

• 从锁的粒度上分mysql支持锁的表级锁，行级锁（innodb），页级锁（BDB）

• 从锁的操作上说可以分为读锁和写锁

• 从实现方式上分乐观锁和悲观锁
  使用的场景：
  修改数据库表结构会自动加表级锁一元数据锁
  更新数据未使用索引，行级锁会上升为表级锁
  更新数据使用索引会使用行级锁
  select。。。for update 会使用行级锁