MySQL掌握的怎么样？听说这几道MySQL面试题没有几个人能答得出来_面试问数据库知识掌握怎么样

MySQL可谓是程序员必备技能，怎么检测自己掌握了多少呢，一起来测试一下吧！一共12个关卡，看看你能闯到第几关？

---

目录

什么是左外链接，什么是右外链接？

分页关键字是什么？

Select 语句的执行顺序？

数据库三范式

MyISAM存储引擎

InnoDB存储引擎

数据库事务的特性

事务的隔离级别

什么是索引？有什么优点？

索引有哪些分类？

索引的底层实现原理？

如何避免索引失效？

---

什么是左外链接，什么是右外链接？

左连接

（左外连接）以左表为基准进行查询,左表数据会全部显示出来,右表 如果和左表匹配 的数据则显示相应字段的数据,如果不匹配,则显示为 NULL。

右连接

（右外连接）以右表为基准进行查询,右表数据会全部显示出来,右表 如果和左表匹配的数据则显示相应字段的数据,如果不匹配,则显示为 NULL。

分页关键字是什么？

MySQL的分页关键词limit

SELECT * FROM student3 LIMIT 2,6; 查询学生表中数据，从第三条开始显示，显示6条 。

Select 语句的执行顺序？

顺序依次为form... on...left join...where...group by...avg()/sum()...having..select...

order by...asc/desc...limit...

数据库三范式

第一范式：1NF 原子性，列或者字段不能再分，要求属性具有原子性，不可再分解；

第二范式：2NF 唯一性，一张表只说一件事，是对记录的惟一性约束，要求记录有惟一标识，

第三范式：3NF 直接性，数据不能存在传递关系，即每个属性都跟主键有直接关系，而不是间接关系。

MyISAM存储引擎

主要特点：

MySQL5.5版本之前的默认存储引擎

支持表级锁（表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁）；

不支持事务，外键。

适用场景：

对事务的完整性没有要求，或以select、insert为主的应用基本都可以选用MYISAM。在Web、数据仓库中应用广泛。

InnoDB存储引擎

主要特点：

MySQL5.5版本之后的默认存储引擎；

支持事务,支持行级锁（行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁）；

支持聚集索引方式存储数据。

数据库事务的特性

原子性：即不可分割性，事务要么全部被执行，要么就全部不被执行。

一致性：事务的执行使得数据库从一种正确状态转换成另一种正确状态

隔离性：在事务正确提交之前，不允许把该事务对数据的任何改变提供给任何其他事务，

持久性：事务正确提交后，其结果将永久保存在数据库中，即使在事务提交后有了其他故障，事务的处理结果也会得到保存。

事务的隔离级别

读未提交（read Uncommited）:

在该隔离级别，所有的事务都可以读取到别的事务中未提交的数据，会产生脏读问题，在项目中基本不怎么用， 安全性太差。

读已提交（read commited）:

这是大多数数据库默认的隔离级别，但是不是MySQL的默认隔离级别；这个隔离级别满足了简单的隔离要求：一个事务只能看见已经提交事务所做的改变，所以会避免脏读问题；
由于一个事务可以看到别的事务已经提交的数据，于是随之而来产生了不可重复读和虚读等问题（下面详细介绍这种问题，结合问题来理解隔离级别的含义）。

可重复读（Repeatable read）：

这是MySQL的默认隔离级别，它确保了一个事务中多个实例在并发读取数据的时候会读取到一样的数据；不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

可串行化（serializable）：

事物的最高级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争，一般为了提升程序的吞吐量不会采用这个。

什么是索引？有什么优点？

概念：

索引存储在内存中，为服务器存储引擎为了快速找到记录的一种数据结构。索引的主要作用是加快数据查找速度，提高数据库的性能。

优点：

1. 创建唯一性索引，保证数据库表中每一行数据的唯一性
2. 大大加快数据的检索速度，这也是创建索引的最主要的原因
3. 加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
4. 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

索引有哪些分类？

1. 普通索引：最基本的索引，它没有任何限制。
2. 唯一索引：与普通索引类似，不同的就是索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
3. 主键索引：它是一种特殊的唯一索引，用于唯一标识数据表中的某一条记录，不允许有空值，一般用     primary key 来约束。
4. 联合索引（又叫复合索引）：多个字段上建立的索引，能够加速复合查询条件的检索。
5. 全文索引：老版本 MySQL 自带的全文索引只能用于数据库引擎为 MyISAM 的数据表，新版本 MySQL 5.6 的 InnoDB 支持全文索引。默认 MySQL 不支持中文全文检索，可以通过扩展 MySQL，添加中文全文检索或为中文内容表提供一个对应的英文索引表的方式来支持中文。

索引的底层实现原理？

1. 索引结构

索引是在Mysql的存储引擎(InnoDB,MyISAM)层中实现的, 而不是在服务层实现的. 所以每种存储引擎的索引都不一定完全相同, 也不是所有的存储引擎都支持所有的索引类型的, Mysql目前提供了以下4种索引:

B+Tree 索引: 最常见的索引类型, 大部分索引都支持B+树索引.

Hash 索引: 只有Memory引擎支持, 使用场景简单.

1. Tree索引(空间索引): 空间索引是MyISAM引擎的一个特殊索引类型, 主要地理空间数据, 使用也很少.

Full-text(全文索引): 全文索引也是MyISAM的一个特殊索引类型, 主要用于全文索引, InnoDB从Mysql5.6版本开始支持全文索引。

1.  BTree结构

B+Tree是在BTree基础上进行演变的, 所以我们先来看看BTree, BTree又叫多路平衡搜索树, 一颗m叉BTree特性如下:

1. 树中每个节点最多包含m个孩子.
2. 除根节点与叶子节点外, 每个节点至少有[ceil(m/2)] 个孩子(ceil函数指向上取整).
3. 若根节点不是叶子节点, 则至少有两个孩子.
4. 每个非叶子节点由n个Key和n+1个指针组成, 其中 [ceil(m/2) -1 ] <= n <= m-1.

以5叉BTree为例, key的数量: 公式推导 [ceil(m/2) -1 ] <= n <= m-1.

所以 2 <= n <= 4, 中间节点分裂父节点,两边节点分裂。

 

1.  B+Tree 结构

B+Tree为BTree的变种, B+Tree与BTree的区别:

        1.B+Tree的叶子节点保存所有的key信息, 依key大小顺序排列.

        2.B+Tree叶子节点元素维护了一个单项链表.

所有的非叶子节点都可以看作是key的索引部分。

由于B+Tree只有叶子节点保存key信息, 查询任何key都要从root走的叶子. 所以B+Tree查询效率更稳定.

Mysql中的B+Tree

MySql索引数据结构对经典的B+Tree进行了优化, 在原B+Tree的基础上, 增加了一个指向相邻叶子节点的链表指针, 就形成了带有顺序指针的B+Tree, 提高区间访问的性能.

MySql中的B+Tree索引结构示意图:

 

如何避免索引失效？

范围查询, 右边的列不能使用索引, 否则右边的索引也会失效

不要在索引上使用运算, 否则索引也会失效

字符串不加引号, 造成索引失效

---