说到数据库这个词，我只能用爱恨交加这个词来形容它。自己之前还单纯懵懂的时候进了数据库的课堂，听完数据库的课，觉得这是一门再简单不过的课程，任何一门编程语言都比SQL要晦涩难懂，任何一门理论课程都比数据库关系要复杂得多。

直到从被面试官按在地上摩擦，到工作中那一条条令人发指的慢查询SQL，这就已经完全颠覆了我对数据库的看法。

在有各种数据库工具的今天，我们是看不到那简单到不能再简单的一张表的背后，隐藏着多少数据结构的支撑，也看不到我们随手敲的一条SELECT，背后会有多少算法和数据结构在给我们做优化，作为一个有技术热情的coder，应该需要对我们每日在用的数据库做一次深入了解。

这个问题很宽泛，需要我们对于整体有一个掌控，对我们平时所用的数据库要有足够的了解，对整个数据库做模块划分是这道题的关键，这就更需要我们足够了解数据库，对数据库做一个合理的模块设计。

从开始，首先要明白，数据库的最最根本的作用是什么——存储数据，所以我们需要一个存储模块来存储我们的数据，它可以是一个文件系统（机械硬盘？SSD固态硬盘？）。但光有存储模块是不够的，我们还需要一个程序实例，来组织或者获取这些数据，在程序实例中我们需要提供获取和组织这些数据的方式，所以我们需要在程序实例中实现存储管理模块。

我们还可以在存储管理模块中做一些提升效能的工作，例如同时读取多行、分块分页存储等。数据库作为一款对性能要求极高的软件，我们应该加入缓存机制，来提高其速度，当查询到缓存中已存在的数据，我们应该直接将其从缓存中读取，这样可以减少硬盘IO次数，提高效能。

到这里为止，我们已经完成了对数据库的存储方面的功能设计，但是作为数据库，应该需要提供给用户对数据进行增删改查的接口，即平时所写的SQL，所以我们应该提供一个SQL解析模块，来对日常用户所写的SQL语句进行解析，转换成机器可识别的指令，我们也可以直接将编译过的SQL加入缓存，下次再有同样的SQL就直接从缓存中读取，同样可以提高效能。

作为一款成熟的数据库，需要应对各种复杂的环境，要时刻记录数据库的状态，所以我们还需要一个日志管理模块，对操作和错误信息进行记录。数据库中需要支持多用户操作，但每个用户都能操作所有的数据，这是不现实的，所以还需要权限划分模块对数据库用户进行权限管理。

当然数据库说到底也只是一款软件，是软件就会有bug，就会出故障，故障不可怕，可怕的是在数据库这种敏感软件下对故障没有特殊的处理方式，导致数据丢失，毕竟数据是无价的，所以数据库应该引入容灾机制，在数据库挂了的时候，对数据进行恢复。

还有作为数据库最重要的两个模块，也是现今任何一个数据库都需要考虑的问题——并发和查找效率，所以还需引入索引和锁这两个模块，这就是实现一个最基础的数据库所必需的几大模块。

综上对数据库设计模块做一个汇总：

1.存储模块

2.程序实例

要考虑这个问题，首先要从最基础的查找表中数据的过程开始说起。通常我们在查找一个序列中的某一个元素时，用到的最简单的方式就是遍历，数据库也是一样，在一张表中查找某一行数据时，如果不考虑索引的状况下，也会采用一个逐行扫描的方式，只不过数据库通常以块或者页为单位，所以它通常将整个块或者页加载进内存，然后逐块轮询查找到结果并返回。

如果数据库中只有少量数据，那么进行全表扫描，速度还是会很快，但是如果在数据量很大的表中，这种方法就不再适用了，在数据量很大的表中，由于逐行扫描代价变大，通常需要避免采用这种逐行扫描的方式进行数据查找，数据库为了使查询变得高效，所以引入了索引这种方式对数据进行查找。

众所周知，二叉查找树是每个节点最多由两个子树的树结构，而其还有一个特点是，在任意一颗树中，根节点的左孩子永远小于根节点，根节点的右孩子永远大于根节点，用二叉查找树作为索引，确实可以提高查找效率，其可以使用二分查找将时间复杂度控制在O(lgn)，但是二叉查找树有一个显而易见的缺陷，当某种特殊情况（按照某个特定顺序插入树）发生时，二叉查找树将变为下图右侧（线性二叉树）的状况：

此时二叉查找树查找任意某个元素时，其查找顺序与逐行查找无异，查询时间复杂度又将回到O(n)，查询效率无法保持。

B-Tree，平衡多路查找树，如果每个节点，最多有N个孩子，那么这样的树就叫N阶B-Tree， 每个节点中主要包含关键字和指向孩子的指针，最多能有几个孩子，取决于节点的容量和数据库的相关配置，通常情况下这个N是很大的。

B-Tree作为一种数据结构，有如下特征：

1、ki(i=1..n)为关键字，且关键字按顺序升序排序k(i-1)fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all

如果type为index或all，表示本次扫描为全表扫描，说明这个语句是需要优化的。

extra：可以用来辅助type帮助我们进行SQL优化，extra中出现以下两项，意味着MySQL根本不能使用索引，效率会受到重大影响，应该尽可能对此进行优化。

Using filesort:表示MySQL会对结果使用一个外部索引排序，而不是从表里按索引次序读到相关内容，可能在内存或者磁盘上进行排序。MySQL中无法例用索引完成的排序操作称为“文件排序”。

Using temporary:表示MySQL在对查询结果排序时使用临时表，常见于排序 order by和分组查询group by。

3.修改SQL，尽量让SQL走索引

我们可以知道，创建表时，我们将id设为主键，那么id也就自然称为了索引，所以我们只要修改排序字段为id，即可以通过索引排序。

key:PRIMARY 代表使用了主键索引

另一种情况，在特定状况下一定需要使用name进行排序，那还有一种做法，就是将name字段加索引。

结果：

上文中只是用了单一索引对表进行排序，如果使用联合索引又会是什么样的一种状况？ 最左匹配原则：假设数据表中有两列，A and B,我们将A、B设置为联合索引，然后在where语句中调用where A = ? AND B = ?，该查询语句会使用AB联合索引，调用where A = ?，该查询语句也会使用AB联合索引，但当调用where B = ？时，它将不会使用AB联合索引。

官方定义：

1.最左前缀匹配原则，MySQL会一直向右匹配直到遇到范围查询（>、5 and d=6,如果建立(a,b,c,d)顺序的索引，d是无法使用索引的，如果建立(a,b,d,c)的索引则都可以使用到，a、b、d的顺序可以任意调整。 2.=和in可以乱序，比如 a=1 and b=2 and c=3 建立(a,b,c)索引可以任意顺序，MySQL的查询优化器会帮你优化成索引可以识别的形式。

答：NO，数据量小的表不需要建立索引，建立会增加额外的索引开销。另外数据变更需要维护索引，因此更多的索引意味着更多的维护成本。更多的索引还需要消耗更多的空间。

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

InnoDB在使用索引时，默认使用行级锁，但当其没有用到索引时，默认使用表级锁。

表级锁

当一条数据库语句对某条数据进行操作时，默认将整张表进行加锁，其余操作无法对表进行更改，需等到当前操作执行完毕释放锁后，才能对该表进行更改。

行级锁

当一条数据库语句对某条数据或多条数据进行操作时，默认将正在操作的这几条数据进行加锁，其余操作无法对当前语句正在操作的这几条数据进行操作，但可以操作其他数据。

共享锁和排他锁

共享锁：当对某行或某张表上了共享锁之后，其它任意语句依然支持上共享锁，但不支持上排他锁。（MySQL使用lock in share mode加共享锁）

排他锁：当对某行或某张表上了排他锁之后，其它任意语句对这一行或者这一张表进行读或写都是不允许的。（MySQL使用 for update 加排他锁）

乐观锁和悲观锁

悲观锁：总是假设最坏的情况，认为竞争总是存在，认为每次对数据的修改总会产生冲突，因此每次都会先上锁，其他线程阻塞等待释放锁。

乐观锁：总是假设最好的情况，认为竞争总是不存在，认为每次对数据的修改都不会产生冲突，因此不会先上锁，再最后更新的时候，比较数据是否已经被更新，可用版本号或CAS实现。

行级锁一定比表级锁优吗？

答：不一定，锁的粒度越细，其消耗的资源代价越高。行级锁在上锁的时候需要扫描到某行再进行上锁，这样的代价是较大的。

MyISAM和InnoDB各自适合的场景

MyISAM适合的场景：

InnoDB适合的场景：

事务：作为单个逻辑单元执行的一个操作，要么全部完成，要么全部失败。

原子性

事务包含的所有操作，要么全部执行，要么全部失败。

隔离性

多个事务并发执行时，一个事务的执行不应该影响其它事务的执行。

一致性

事务应保证数据库状态从一个一致性状态转换到另一个一致性状态。（一致性状态：数据库的数据应满足完整性约束）

持久性

一个事务一旦提交，它的数据应该永久地保存在数据库中。

快照读（非阻塞读）--伪MVCC。使用此种机制避免使我们看到“幻行”。

当前读：select …lock in share mode,select …for update,update,delete,insert.即加了锁的增删改查语句。由于其读取的是记录的最新版本，所以称为当前读。

快照读：不加锁的非阻塞读（非SERIALIZABLE），select。基于提升并发性能的考虑，基于多版本并发控制。既然是基于多版本，也就是说快照读有可能读到非最新版本的数据。

next-key锁（行锁+gap锁）：next-key锁由两部分组成，行锁+gap锁，行锁即对单个行记录上的锁。Gap锁，即对插入索引间的空隙上锁，即锁定一个范围，但不包括记录本身。

Gap锁的目的，是为了防止一个记录两次当前读出现幻读的情况。Gap锁只存在与Read-Committed（不包括Read-committed）以上的隔离级别存在。如果查询时，where条件全部命中（精确查询时），则不会用Gap锁，只会加记录锁。

因为在精确查询的状况下，即使在读结果集的过程中，另一个事务增加一条数据，也不会增加到当前结果集下，只会在where条件的范围之外，所以并不会产生幻读现象，加行锁就足够了。如果where条件部分命中或者全不命中，则会加Gap锁。

1.数据行里的DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID字段，DB_TRX_ID标识最近一次对本行记录做修改的事务ID，DB_ROLL_PTR回滚指针，当事务回滚时，去往undo日志寻找上一版本的数据，DB_ROW_ID行号（MySQL自动创建的隐藏自增主键）。

2.undo日志：存储历史版本的数据。当某行的某个字段进行修改时，首先用排他锁锁住该行，然后将该行数据拷贝一份放入undolog中，通过行中的DB_ROLL_PTR指针，指向undolog中的这条数据，然后修改当前行的值，并填写DB_TRX_ID字段为当前事务的ID。

3.read view：可见性判断。决定当前事务能看到的是哪个版本的数据。