数据库面试总结

索引模块

为什么要使用索引？

      使用索引主要是为了在项目中，数据量大，如果一直使用全表扫描，会在很大程度影响程序的执行效率，所以已引入了索引。

数据记录中，有什么样的信息能够成为索引？

      能够把查找范围确立在一定范围内的字段，主键就是一个很好的索引切入点。包括唯一键，普通键等也是可以作为索引存在的。

索引数据结构

• 建立二叉树进行二分查找
• 建立B-Tree结构进行查找
• 建立B+Tree结构进行查找
• 建立一些Hash结构进行查找

二叉查找树

      每个节点有两个孩子节点，且左孩子的值一定小于父节点的值，右孩子的值一定大于父节点的值。二叉查找树又是平衡二叉树，即左右孩子的深度相差不能大于1.

B-Tree

• 根节点至少包括两个孩子
• 树中每个节点的最多含有m个孩子（m>=2）
• 除根节点和叶子结点以外，其他每个节点至少有ceil(m/2)个孩子 （ceil是取上限，1.2取2）
• 所有叶子结点都位于同一层
• 假设每个非终端节点中包含有n个关键字信息，其中
  • Ki（i= 1 … n），为关键字，且关键字按顺序升序排序K(i -1 ) < K i.

• • 关键字的个数n必须满足：[ceil (m / 2) -1] <= n <= m-1;  任意节点的关键字个数上限比他的孩子数上限少1，且对于非叶子节点来说，任何一个节点的关键字个数比他的指向孩子的指针个数少一个

• • 非叶子结点的指针：P[1]，P[2]，… P[M]；其中P[1]指向的关键字小于K[1]的子树，P[M]指向关键字大于K[M-1]，其他P[i]指向关键字属于（K[i-1]，K[i]）的子树；

B+-Tree

密集索引和系数索引

• • 密集索引文件中的每个索引码值对对应一个索引值：叶子节点保存的不仅仅是键值，还保存了位于同一行记录里的其他列的信息，由于密集索引决定了表的物理排列顺序，一个表只能有一个物理排列顺序，所以一个表只能创建一个密集索引。
  • 稀疏索引文件中只为索引码的某些值建立索引项：叶子结点仅保存了键位信息以及改行数据的地址，有的稀疏索引是仅保存了键位信息及其主键。定位到叶子结点之后，任然需要通过地址或者主键信息进一步定位到数据。

对于MySQL的两大主流索引，MyISAM 和InnoDB，MyISAM不论是主键索引，普通索引还是唯一键索引，其索引均数据稀疏键索引，而InnoDB必须是有且仅有一个稠密索引。

InnoDB

• 如果一个主键被定义，则该主键作为密集索引
• 若没有主键被定义，则该表的第一个唯一非空索引则作为密集索引
• 若不满足以上条件，InnoDB内部会生成一个隐藏的主键作为密集索引
• 非主键索引存储相关键和其对应的主键值，查找时需要两次查找。

主键索引

主键：

　　某一个属性组能唯一标识一条记录

　　如：学生表（学号，姓名，班级，性别等等），学号时唯一标识的，可以作为主键

特点：

　　最常见的索引类型

　　确保数据记录的唯一性

　　确定特定数据记录在数据库中的位置

实例：

CREATE TABLE `表名`（、

　　`GradeID` INT(11)  AUTO_INCREMENT PRIMARY KEY,

　　#或 PRIMARY KEY(`GradeID`)

）

唯一索引

作用：

      避免同一个表中某数据列中的值重复

与主键索引的区别

　　主键索引只能有一个

　　唯一索引可有多个

实例：

CREATE TABLE `Grade`（、

　　`GradeID` INT(11)  AUTO_INCREMENT PRIMARY KEY,

　　`GradeName` VARCHAR(32) NOT NULL UNIQUE

　　#或 UNIQUE KEY ` GradeID`(`GradeID`)

常规索引

作用：

快速定位特定数据

注意：

index 和 key 关键字都可以设置常规索引

应加在查询条件的字段

不易添加太多常规索引，影响数据的插入，删除和修改操作

实例：

##创建表时添加

CREATE TABLE `result`{

　　//省略一些代码

　　INDEX / KEY `ind` (`studentNo`,`subjectNo`)

}

##创建后追加

ALTER TABLE `result` ADD  INDEX `ind` (`studentNo`,`subjectNo`);

全文索引

作用：

快速定位特定数据

注意：

只能用于MyISAM类型的数据表

只能用于CHAR ,VARCHAR,TEXT数据列类型

使用大型数据集

实例：

CREATE TABLE `student`(

　　#省略一些sql语句

　　　　FULLTEXT(`StudentName`)

)ENDINE=MYISAM;

ALTER TABLE employee ADD FULLTEXT(`first_name`)

密集索引和非密集索引的区别

稀疏索引使用的是B-Tree，各节点存放索引值和索引地址，而稠密索引使用的B+-Tree，树中的非叶子节点当中是不存索引地址的，存的是索引值和指向下一个节点的指针，在树的叶子结点才会真正的存储数据的索引地址或者数据。

如何定位并优化慢查询Sql

• 根据慢查询日志定位慢查询SQL
• 使用explain等工具分析SQL
• 修改SQL或者尽量让SQL走索引

最左匹配原则

MySQL会一直向右匹配直到遇到范围查询（<,>,between,like）就停止匹配，比如，a=3 and b = 4 and c > 5 and d = 6 ,如果建立a,b,c,d顺序的索引，d是用不到索引的，如果建立（a,b,d,c）顺序的索引则都可以用到，a,b,d的顺序顺序可以任意调整。

=和in可以乱序，比如 a= 1 and b = 2  and c = 3 家婚礼(a,b,c)索引可以任意顺序，MySQL的查询优化器会棒你优化成索引可以识别的形式。

联合索引的最左匹配原则的成因

首先会根据复合索引的最左边也就是第一个字段进行排序，在第一个字段排序的基础上在对第二个字段进行排序，类似于order by 第一个字段，在order by 第二个字段，因此，第一个字段是绝对有序的，第二个字段到以后的字段就是无序的了。因此直接使用第二个字段进行条件判断是用不到索引的。

锁模块

MyISAM与InnoDB关于锁方面的区别是什么

• MyISAM默认使用的表级锁，不支持行级锁
• InnoDB默认使用的是行级锁，也支持表级锁

 

xxx for update ; 上排他锁

xxx lock in share mode;  行级锁上共享锁

无论是表锁还是行锁都有共享锁和排他锁，其关系如表所示：

如session1对于某一行数据上了一个排他锁，也就是增删改语句，或者select，update的时候都可以上排他锁。与此同时，session2还想对同一行数据上排他锁是不可以的，会出现冲突，直到所得释放，上共享锁也会是不可以的，也是会出现冲突。如果是先对一行数据上了共享锁，在对同一行数据上排他锁也是不可以的，是会有冲突的。但是如果对一行数据上了共享锁，再次的对同一行数据上共享锁，是可以的，他俩是兼容的。

MyISAM适合的场景

• 频繁执行全表count语句。对于InnoDB来说，是不保存表的具体行数的，执行 select count(*) from 表名 时需要重新扫描统计。MyISAM是用一个变量保存了整个表的行数。
• 对数据增删改的频率不高，查询非常频繁的操作。增删改设涉及到锁表操作，虽然对于插入操作可以通过一些配置支持从表的尾部插入数据，但是依然会产生很多碎片，依然会很影响性能。如果是纯查询的话效率是非常高的。
• MyISAM适合不需要事务的操作，它不支持事务

InnoDB适合的场景

• 数据的增删改查都相当频繁的操作，增删改只是某些行被锁，在大多数情况下避免了阻塞。
• 可靠性要求高，要求支持事物

数据库事务的四大特性

ACID

      原子性(Atomic)，一致性(Consistency)，隔离性(Isolation)，持久性(Durability)

事务的隔离级别以及各级别下的并发访问问题

脏读，一个线程读取到另一个线程修改但为提交的数据，通过将隔离级别设置为Committed Read就可以解决这一类问题。

不可重复诶，一个线程在读数据，读取到一个数据，之后里一个线程对数据做了修改，然后之前的那个线程再又读了一次，发现与之前的数据不一致了，这就存在很大隐患，对于第一个线程而言，到底那次读取到的数据是真是可靠的。这个问题通过将隔离级别设置为Repeatable Read就可以解决。

幻读，一个线程在对表中数据进行修改或者查询操作时，原来应该是n条数据，可是执行的时候突然出现了n+k条数据或者是n-k 条数据，这就是幻读，这样的问题将数据库的isolation level 设置成serializable就可以解决。

InnoDB可重复读隔离级别下如何避免幻读

• 表象：快照读（非阻塞读）---伪MVCC

• 内在：next-key锁（行锁+gap酥锁）

 

当前读：上了锁的增删改，不论是什么锁

当前读，它读取的是记录的最新版本，读取之后还要保证其他的的并发事物不能修改当前记录，对读取的记录加锁，其中除了 lock in share mode 加的是共享锁，其他的语句加的都是排他锁。

快照读：就是简单的select，不加锁，这个不加锁的条件是事务隔离级别不为Serializable的前提下，在Serializable级别下，由于是穿行读，此时的快照读也退化成当前读。

之所以出现快照读是基于提升并发性能的考虑，快照读是基于多版本并发控制即MVCC，可以认为MVCC是行级锁的一个变种，它在很多情况下避免了加锁，开销更低，由于是快照读，所以读到的很可能是不是当前的最新版本，而是历史版本。

在RR级别下，如果是先创建快照，也就是先执行了select，然后就去其他线程中修改数据，然后使用当前读，是能够读到新数据的，但是再次使用快照读，读取到还是历史数据；但是如果之前没有使用select创建快照，而是修改，之后再读，快照读和当前读读到的都是新数据。

RC，RR级别下的InnoDB的非阻塞读如何实现

• 数据行里的DB_TRX_ID，DB_ROLL，DB_ROW_ID字段

DB_TRX_ID：与事务有关，该字段记录最近一次对本行记录做修改。

DB_ROLL：回滚指针，通过undo日志重建之前的数据信息。

DB_ROW_ID：即行号，包含一个随着新行插入和单调递增的行id。当InnoDB产生聚集索引时，聚集索引会包括这个行id的值，否则这个行id不会出现任何索引中。

• undo日志

对记录做了变更操作时，就会产生undo记录，undo记录中存储的老版记录，当一个旧的事物需要读取数据时，为了能读取到老版的数据，需要顺着undo列找到满足其可见性的数据列。undolog主要分为两种，insert undolog和update undolog，insert Undolog是事务在insert新纪录产生的Undolog，只在事物回滚时需要，在事物提交后就可以立即丢弃。update Undolog是事务在delete或者update时产生的Undolog，不仅在事物回滚时需要，快照读也需要，不能够随便删除，只有在数据库中所使用的的快照中不涉及改日志记录，对应的日志记录才会被删除。

• read view 主要是做可见性判断

去执行快照读select的时候，会针对查询的数据创建出一个read view，来决定当前事物能看到的是那个版本的数据，有可能允许看到最新版本的，也有可能看到Undolog中的老版本数据，它遵循一个可见性算法，主要是将要修改的数据的DB_TRX_ID取出来。与系统其他活跃事物id作对比，如果大于或等于这些id，就通过DB_ROLL_PTR指针去取出上次一的Undolog，可以一直向上回溯，直到DB_TRX_ID为0，就是最原始的版本。

next-key锁（行锁+gap锁）

• 行锁
• gap锁