1 MYSQL面试

第一弹 MYSQL中的常见问题

1 三范式

列不可分非主键属性完全依赖于主键 [ 主键（订单ID，商品ID）, 商品名] 商品名依赖于非主键商品ID，没有完全依赖于主键不存在传递依赖 【主键（订单ID），用户ID，用户名】 用户名完全依赖于用户ID，而用户ID依赖于主键（订单ID），存在传递

2 B树和B+树的区别

 

B树（m叉）

最多有m颗子树除了根节点，每个节点最少有M/2颗子树每个节点有m-1个关键字关键字di的右边有指针ci, ci指向的节点包含的关键字均大于或等于di，且小于di+1叶子节点出现在同一层，且不包含任何信息

 

B+树(m叉)

有n颗子树的节点有n个关键字所有的关键字都在叶子节点上非终端节点可以看成索引，存放的是子树（根节点）较大或者较小的关键字在查找的过程中，如果要查找的值和非终端节点中的值相等，并不终止，依然要查找到叶子节点

 

为什么B+树更适合做索引？

B+树的节点中没有指向关键字具体信息的指针，因此，其内部节点更小。也就是说相同的盘块可以容纳更多的关键字的数量，一次性读入内存的关键字越多，IO的次数就越少B+查询更为稳定，怎么查询都会查到叶子节点，所有的关键字的查询路径相同，查效率相当范围查询，只需要遍历叶子节点

 

3 索引的优点

加快查询的速度加快表连接的速度加快分组和排序的速度

4 什么情况下索引无效

以%开头的模糊查询OR的两端没有同时使用索引多列索引不满足最左匹配原则数据类型的隐式转换

5 为什么以%开头的模糊查询会使得索引失效

索引是树形的结构，如果，以%开头，那么这个索引可能是任意一个节点，需要遍历整个树，索引也就失效了。

6 什么样的字段适合做索引

经常被用作查询经常用作表连接Group by order by 后的字段

7 索引的缺点

时间：插入和删除修改都要维护索引

空间：索引需要占用物理空间

 

8 聚集索引和非聚集索引

聚集索引的索引顺序和数据物理存储顺序一致，而非聚集索索引是逻辑顺序聚集索引的叶子节点存的是具体的数据，而非聚集索引存放的依然是索引聚集索引一般要比非聚集索引的查找速度快，因为，非聚集索引查到叶子节点还要再通过聚集索引再查找才能找到具体的数据（除非使用了覆盖索引）。

 

9 事务的隔离级别

读未提交读已提交可重复度串行化

(1) MVCC保证可重复读

每行记录增加两个字段，分别为行的创建时间和行的删除时间。这个时间就是事务的版本号。每开启一个新的事务，事务的版本号就会递增。

Select的时候，行的创建时间小于当前事务版本号，行的删除时间未定义或者删除时间大于当前事务的版本号。

Insert

创建时间为当前的事务的版本号

Delect

删除时间未当前事务的版本号

Update

创建时间为当前事务的版本号，删除时间为当前事务的版本号

 

(2) MVCC能消除幻读吗

MVCC可以解决快照读，但是，没法解决当前读。

快照读

  读取的是记录数据的可见版本（可能是过期的数据），不用加锁

当前读

  读取的是记录数据的最新版本，并且当前读返回的记录都会加上锁，保证其他事务不会再并发的修改这条记录   概念说的比较虚，也不好理解，接着举一个例子吧，假设你开启了两个事务，分别是A和B，这里有个张表，user表，里面有

1、select快照读（照片）

  当你执行select *之后，在A与B事务中都会返回4条一样的数据，这是不用想的，当执行select的时候，innodb默认会执行快照读，相当于就是给你目前的状态找了一张照片，以后执行select 的时候就会返回当前照片里面的数据，当其他事务提交了也对你不造成影响，和你没关系，这就实现了可重复读了，那这个照片是什么时候生成的呢？不是开启事务的时候，是当你第一次执行select的时候，也就是说，当A开启了事务，然后没有执行任何操作，这时候B insert了一条数据然后commit,这时候A执行 select，那么返回的数据中就会有B添加的那条数据......之后无论再有其他事务commit都没有关系，因为照片已经生成了，而且不会再生成了，以后都会参考这张照片。

2、update、insert、delete 当前读

  当你执行这几个操作的时候默认会执行当前读，也就是会读取最新的记录，也就是别的事务提交的数据你也可以看到，这样很好理解啊，假设你要update一个记录，另一个事务已经delete这条数据并且commit了，这样不是会产生冲突吗，所以你update的时候肯定要知道最新的信息啊。

  我在这里介绍一下update的过程吧，首先会执行当前读，然后把返回的数据加锁，之后执行update。加锁是防止别的事务在这个时候对这条记录做什么，默认加的是排他锁，也就是你读都不可以，这样就可以保证数据不会出错了。但注意一点，就算你这里加了写锁，别的事务也还是能访问的，是不是很奇怪？数据库采取了一致性非锁定读，别的事务会去读取一个快照数据。   innodb默认隔离级别是RR， 是通过MVVC来实现了，读方式有两种，执行select的时候是快照读，其余是当前读，所以，mvvc不能根本上解决幻读的情况。

仅仅通过MVCC是无法完全的解决幻读的。

(3) Next-key LOCK

行锁（Record Lock）：锁直接加在索引记录上面。间隙锁（Gap Lock）：锁加在不存在的空闲空间，可以是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引之后的空间。Next-Key Lock：行锁与间隙锁组合起来用就叫做Next-Key Lock。

如果InnoDB扫描的是一个主键、或是一个唯一索引的话，那InnoDB只会采用行锁方式来加锁，而不会使用Next-Key Lock的方式，也就是说不会对索引之间的间隙加锁

 

10 主键自增和UUID的优缺点

自增：

优点

查询速度快，因为短，比较时比较快增加和删除记录时，索引调节的更少

缺点：

不安全不利于表的合并

UUID

优点：

安全性高表合并方便

缺点：

查询速度比自增慢索引变动较大

11 如何创建索引

Alter table index add index

 

12 数据库优化

找到需要优化的SQL语句 （2）查看SQL语句的执行 （3）SQL语句的优化 查找需要优化的SQL语句

慢日志查询

查看SQL语句的执行

可以通过EXPLAIN语句查看SQL语句的执行：（1）表的读取顺序（2）数据读取操作的操作类型（3）可用的索引（4）实际使用的索引（5）表之间的引用关系 extra出现usring fileSort 和using tempopry往往表示需要优化

SQL语句的优化 inset可以批量的插入where避免使用<>!=，因为会使索引失效避免极性Null的判断，会是索引失效使用join代替子查询

索引的优化

避免索引失效

       数据库表结构的优化

字段尽量使用短的数据类型使用非空的字段Text字段避免使用，可以单独一张表三大范式表的垂直和水平拆分

垂直：（1）经常使用的字段放到一个表

     （2）不经常使用的字段放到一个表

     （3）大字段放到一个表

 

 

13 存储过程

多条SQL语句的集合

好处：

（1）一次编译多次运行

（2）存储过程的网络传输比多条SQL语句的传输的数据量要小

（3）控制用户的权限，保证安全

 

14 悲观锁和乐观锁

悲观锁 访问资源的时候，先加锁。Select for update 会在事务结束的时候自动释放，因此，必须在事务中使用

乐观锁 不加锁，只是在修改的时候检查数据是否被更新过，如果，未更新过，则更新成功。否则，失败重试。加版本号或者时间戳。

15 MySQL MyISAM InnoDB

MyISAM不支持事务，不支持外键，只支持表锁，查询速度更快。