数据库

1.select * from table where a>0 and b<0，ab是联合索引，问索引能不能命中？

可以命中，不过key_len只有一半。

2.MySql的char和varchar的区别？

• char不管值的长度都会占用给定的字符数；varchar会根据实际的情况占用字节空间。
• char最大字节数为255字节，varchar为65535字节（如果使用utf8编码格式，一个字符占用3个字节，最大设定值为21844）。
• char在存储时是会对尾部的空格进行裁剪，varchar不会。

3.sql索引失效场景？

• 如果字段类型是字符串需要加上引号
• 使用or
• 最左匹配原则
• like以%开头
• 如果mysql估计使用全表扫描要比使用索引快,则不使用索引

4.事务是什么？事务的四大特性？

事务提供一种机制将一个活动涉及的所有操作纳入到一个不可分割的执行单元，组成事务的所有操作只有在所有操作均能正常执行的情况下方能提交，只要其中任一操作执行失败，都将导致整个事务的回滚。

• 原子性（Atomicity）
  一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样。

• 一致性（Consistency）
  一个事务执行之前和执行之后数据库都必须处于一致性状态。如果事务成功地完成，那么系统中所有变化将正确的应用，系统处于有效状态。如果在事务中出现错误，那么系统中的所有变化将自动地回滚，系统返回到原始状态。

• 隔离性（Isolation）
  不同的事务同时操作相同的数据时，每个事务都有各自的完整数据空间。由并发事务所作的修改必须与任何其他并发事务所做的修改隔离。事务查看数据更新时，数据所处的状态要么是另一事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看到中间状态的数据。

• 持久性（Durability）
  只要事务成功结束，它对数据库所做的更新就必须永久保存下来。即使发生系统崩溃，重新启动数据库系统后，数据库还能恢复到事务成功结束时的状态。

5.数据库去重操作时，distinct和group by相比什么情况下用哪个？

去重操作，distinct速度要快于group by，因为group by是进行聚合操作。（distinct适合用于分析数量）

6.事务ACID如何实现？

事务的ACID是通过InnoDB日志和锁来保证。事务的隔离性是通过数据库锁的机制实现的，持久性是通过redo log（重做日志）来实现，原子性和一致性通过Undo log来实现。Undo Log是指在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方称为Undo Log）。然后进行数据的修改。如果出现了错误或者用户执行ROLLBACK命令，系统就可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。和Undo Log相反，Redo Log记录的是新数据的备份。在事务提交之前，只要将Redo Log持久化即可，不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，但是Redo Log已经持久化。系统可以根据Redo Log的内容，将所有数据恢复到最新的状态。

7.数据库三范式？

• 1NF：数据库表中不能出现重复记录，每个字段是原子性的不能再分。

比如，用户的联系方式分为手机号码和email邮箱地址，这里不能单独使用联系方式字段，而是将练习方式拆成email和手机号码两个字段。

• 2NF：所有非主键字符按完全依赖主键，不能产生部分依赖。

比如，成绩表中除了用户ID，课程ID还存在用户名字段。

• 3NF：非主键字符不能传递依赖于主键字段（不要产生传递依赖）。

比如，学生表中有班级编号作为外键，还存在有关班级的信息。

8.b+树原理？

• 除根节点外的内部节点，每个节点最多有m（阶数）个关键字，最少有m/2个关键字。其中每个关键字对应一个子树（也就是最多有m棵子树，最少有m/2棵子树）。
• 根节点要么没有子树，要么至少有两棵子树。
• 所有叶子节点包含了全部的关键字以及这些关键字指向文件的指针，并且：

所有叶子节点中的关键字按大小顺序排列；
相邻的叶子节点顺序链接（相当于是构成了一个顺序链表）
所有叶子节点在同一层。

特点

• 每个父节点的元素都出现在子节点中，是子节点的最大（或最小）元素。
• 叶子点中包含了全部的元素信息，并且每个叶子节点都有指向下一个叶子节点的指针，形成一个有序链表结构。
• 在数据库的聚集索引中，叶子节点直接包含数据，而非聚集索引中，叶子节点带有指向数据的指针。

b+树比b-树的好处？

• IO次数少：
  我们都知道磁盘时可以块存储的，也就是同一个磁道上同一盘块中的所有数据都可以一次全部读取(详见《 外部存储器—磁盘 》 )。而B+树的内部结点并没有指向关键字具体信息的指针(比如文件内容的具体地址 ， 比如说不包含B~树结点中的FileHardAddress[filenum]部分） 。因此其内部结点相对B~树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。这样，一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

举个例子，假设磁盘中的一个盘块容纳16bytes，而一个关键字2bytes，一个关键字具体信息指针2bytes。一棵9阶B_{树(一个结点最多8个关键字)的内部结点需要2个盘快。而B+树内部结点只需要1个盘快。当需要把内部结点读入内存中的时候，B}树就比B+数多一次盘块查找时间(在磁盘中就是盘片旋转的时间)。

• 查询性能稳定：b+树每次都会访问到叶子节点，而b-树可能在中间节点就获取到相应的数据。
• 范围查询更方便：b+树直接通过叶子节点的链表可以方便获取到范围值，而b-树需要通过中序遍历来寻找。

8.数据库隔离机制？

隔离级别	读数据一致性	脏读	不可重复 读	幻读
未提交读(Read uncommitted)	最低级别	是	是	是
已提交读(Read committed)	语句级	否	是	是
可重复读(Repeatable read)	事务级	否	否	是
可序列化(Serializable)	最高级别，事务级	否	否	否

8.1 脏读

一个事务访问到另外一个事务未提交的数据。

如果两个线程分别开启了事务，一个进行写操作，一个进行读操作，写操作的事务还未提交，读操作中就读到写操作中未提交的数据值。

8.2 不可重复读

在一个事务内对同一个记录多次查询，获取的记录不同。

8.3 幻读

同一个事务多次查询返回的结果集不一样。

9. 数据库隔离机制实现？

MVCC（多版本并发控制）
InnoDB的MVCC，是通过在每行记录后面保存两个隐藏的列来实现的，一个保存了行的创建时间，一个保存行的过期时间（删除时间）。当然存储的并不是实际的时间值，而是系统版本号。

9.1 SELECT

• InnoDB只查找版本早于当前事务版本的数据行（也就是，行的系统版本号小于或等于事务的系统版本号），这样可以确保事务读取的行，要么是事务开始前已经存在的，要么是事务自身插入或者修改过的。
• 行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务盗取到的行，在事务开始之前未被删除。

9.2 INSERT

• InnoDB为新插入的每一行保存当前系统版本号作为行版本号。

9.3 DELEET

• InnoDB为删除的每一行保存当前系统版本号作为行删除标识。

9.4 UPDATE

• InnoDB为插入一行新纪录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

MVCC只在Repeatable Read和 Read Commited两个隔离级别下工作。因为Read Uncommitted总是读取最新的数据行，而不是符合当前事务版本的数据行。而Serializable则会对所有读取的行都加锁。

10. 聚簇索引和非聚簇索引

10.1 聚簇索引

因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。（覆盖索引可以模拟多个聚簇索引的情况。）
缺点

• 插入速度依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表重速度最快的方式。如果不是按照主键顺序加载数据，那么在加载完成后最好使用OPTIME TABLE命令重新组织一下表。
• 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。会导致表占用更多的磁盘空间。
• 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。

不连续插入可能导致的问题：

• 写入的目标页可能已经刷到磁盘上并从缓存中移除，或者是还没有被加载到缓存中，InnoDB在插入之前不得不先找到并从磁盘读取目标页到内存中。这会导致大量的随机I/O。
• 因为写入是乱序的，InnoDB不得不频繁地做页分裂操作，以便为新的行分配空间。页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个页。
• 由于频繁的页分离，页会变得稀疏并被不规则地填充，所以最终数据会有碎片。

11.索引条件下推

不是用icp的情况：

• 获取下一行，首先读取索引元祖，然后使用索引元组找到并读取整个表行；
• 测试适用于此表的WHERE条件的一部分，根据测试结果接收或拒绝该行。

使用icp的情况：

• 获取下一行的索引元祖（但不是整个表行）；
• 测试适用于此表的WHERE条件的一部分，并且只能使用索引列进行检查。如果不满足条件，则继续下一行的索引元祖；
• 如果满足条件，请使用索引元祖查找并读取整个表行；
• 测试适用于此表的WHERE条件的剩余部分。根据测试结果接收或拒绝该行。

12.强制和禁止使用索引

• mysql强制使用索引:force index(索引名或者主键PRI)
• mysql禁止某个索引：ignore index(索引名或者主键PRI)

13. 如何有效的创建索引？

• 最左前缀匹配原则，mysql会一直右匹配知道遇到范围查询（>、<、between、like)就停止匹配；
• =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，MySQL的查询优化器会自动优化成索引可以识别的形式；
• 尽量选择区分度高的列作为索引，区分度公式是count(distinct col)/count(*);
• 索引列不能参与计算；
• 尽量拓展索引，不要新建索引。

14. union 和 union all 的区别？

Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；Union All：对两个结果集进行并集操作，包括重复行，不进行排序。

15. select 语句执行顺序？

• FROM
• ON
• JOIN
• WHERE
• GROUP BY
• WITH
• HAVING
• SELECT
• DISTINCT
• ORDERBY
• LIMIT