《剑指Java面试-Offer直通车》--数据库

目录

如何设计一个关系型数据库？

索引模块

一、常见问题

为什么要使用索引？

什么样的信息能成为索引？

索引的分类？

索引的数据结构？

二、优化索引

利用二叉查找树

利用B树

利用B+树

利用Hash以及BitMap

三、密集索引和稀疏索引的区别

四、索引模块衍生出来的问题（以MySQL为例）

如何定位并优化慢查询SQL？

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

索引是建立的越多越好吗？

索引失效

锁模块

一、MyISAM和InnoDB关于锁方面的区别？

MyISAM

InnoDB

数据库锁的分类

二、数据库事务的四大特性

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

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

五、RC、RR级别下的InnoDB的非阻塞读如何实现？（MVCC）

MyISAM和InnoDB引擎区别

关键语法

数据库范式

主从复制、读写分离、分库分表

推荐资料

---

如何设计一个关系型数据库？

数据库最主要的功能是存储数据，因此有一个存储模块存储数据。存储模块类似OS文件系统，将数据持久化存入磁盘中，如存入机械硬盘、SSD固态硬盘、亦或者是它们的磁盘阵列矩阵中。

但是只有存储是不行的，还需要组织并且用到这些数据，因此需要有程序的实例，用逻辑结构来映射出物理结构，并且在程序中提供获取和管理数据的方式，以及提供必要的问题追踪机制。

细分程序模块：

1）数据逻辑关系转换成物理存储关系的存储管理模块：首先对数据的格式和文件的分隔进行统一的管理，即把物理数据通过逻辑的形式组织表示出来，便涉及到程序的存储管理模块。（优化存储效能：处理数据不在磁盘上做，而是加载到程序空间所在内存里，磁盘IO速率是程序执行速率的主要瓶颈，远差于内存的执行效率。为了执行效率，要尽可能减少IO。就存储管理而言，如果按照逐行查找并返回，频繁的IO会使数据库的执行效率慢。因为一次IO读取单条数据和多条数据没有太大的区别，所以可以一次性的读取多行，以提升IO的效能。行就失去了意义，数据以块和页作为逻辑存储单位，每个块和页中存放多行数据，读取的时候将多个块和页加载进内存中。）

2）优化执行效率的缓存模块：为了更快更好的优化利用内存，可以利用缓存机制，把取出来的数据块放进缓存里，下次需要的时候直接从内存返回，而不用发生IO。一次性加载多个模块或者页，块里包含的数据行有数据可能不是我们本次查询需要的行，但是一旦某行数据被访问了，它周围的数据也极有可能被访问的经验，缓存的非本质数据也能起到优化访问效率的作用，提升访问的性能。管理缓存的方法有LRU等。

3）将SQL语句解析的SQL解析模块：提供外部指令操纵数据，即可读的SQL语言，需要SQL解析模块将SQL编译解析，转换成机器可识别的指令。这时为了进一步提升SQL的执行效率，将SQL缓存到缓存里直接解析。缓存不宜过大，且有算法里淘汰机制，淘汰掉之后不常用的数据。

4）记录操作的日志管理模块：SQL操作需要记录下来，方便数据库的主从同步或者灾难恢复，因此需要日志管理对操作进行记录，如binlog的记录方式。

5）多用户管理的权限划分模块：还需要提供给用户管理数据的私密空间，即权限划分。通俗将就是老板可以看到员工的数据，员工只能看到自己该看到的数据。权限划分是DBA做的。

6）灾难恢复模块：除了考虑正常情况，还需考虑异常情况，需要引入异常机制，即容灾机制。当数据库挂了如何恢复，恢复到什么程度。

7）优化数据查询效率的索引模块和使得数据库支持并发操作的锁模块：为了进一步提升查询数据的速度以及让数据库支持并发，需要引入索引和锁模块。

 

索引模块

一、常见问题

• 为什么要使用索引？

快速查询数据

（最简单的方式实现数据查询，即全表扫描，将整张表的数据全部或者分批次加载到内存中。存储的最小单位是块或者页，他们是由多行数据组成。将块加载进来，逐个块轮询，找到目标并返回。这种方式普遍比较慢。很多情况下都要避免全表扫描情况的发生，所以数据库引入更高效的机制，即索引。关键信息和查找信息的方式组成索引，通过索引可以大幅提升查询速度。）

• 什么样的信息能成为索引？

主键、唯一键、普通键

（把记录限定在一定查找范围内的字段，主键便是一个很好的切入点，其他包括唯一键、普通键等也可以作为索引。）

• 索引的分类？

主键索引（列值唯一，表中只有一个）、唯一索引（列值唯一）、普通索引、全文索引、联合索引

创建索引

直接创建索引
-- 创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name(col_name);
-- 创建普通索引
CREATE INDEX index_name ON table_name(col_name);
-- 创建唯一组合索引
CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);
-- 创建普通组合索引
CREATE INDEX index_name ON table_name(col_name_1,col_name_2);

通过修改表结构创建索引
ALTER TABLE table_name ADD INDEX index_name(col_name);

创建表的时候直接指定
CREATE TABLE mytable(
       id INT NOT NULL, 
       username VARCHAR(16) NOT NULL, 
       INDEX [indexName] (username(length))
 );

删除索引

-- 直接删除索引
DROP INDEX index_name ON table_name;
-- 修改表结构删除索引
ALTER TABLE table_name DROP INDEX index_name;

 查看索引

#查看:
show index from `表名`;
#或
show keys from `表名`;

其他命令

-- 查看表结构
desc table_name;
-- 查看生成表的SQL
show create table table_name;

• 索引的数据结构？

生成索引，建立二叉查找树进行二分查找。

生成索引，建立B-Tree结构进行查找。

生成索引，建立B+-Tree结构进行查找。

生成索引，建立Hash结构进行查找。

（让查询变得高效的数据结构，如二叉查找树和二叉查找树的变种平衡二叉树、红黑树、BTree、B+Tree以及Hash结构。MySQL数据库索引是通过B+Tree实现。）

 

二、优化索引

• 利用二叉查找树

二叉查找树是每个节点最多有两个子树的树结构，通常子树被称为左子树或右子树。左子树节点的值均小于根节点，右子树节点的值均大于根节点（注意索引的存储块和数据库的最小存储单位块或者页并非一一对应，为了方便理解先一一对应起来）。每个存储块存储的是关键字和指向子树的指针。平衡二叉树任意一个节点的左子树和右子树高度差不超过1。

查询时间复杂度O（logn），查询效率高。极端情况（节点全部在左子树或右子树上）时间复杂度将为O（n）。可以利用树的旋转的特性保持树为平衡二叉树。但还有另一个问题，影响程序运行速度的瓶颈是IO。如果假定索引块在磁盘中，找索引会先发生一次IO，将数据读入内存中，之后再发生IO继续查找，直到找到。检索深度每增加1，就发生一次IO。平衡二叉树、红黑树等每个节点只能有两个孩子。为了组织起数据块，树的深度很深，IO的次数也会很多，检索性能没法满足优化查询需求。

即降低查询的时间复杂度，又降低IO的次数，要让树每个节点能承受的数据多一些，即利用B-Tree、B+-Tree。

• 利用B树

B树，即平衡多路查找树。每个节点最多有m个孩子，这样的树即为m阶B树。每个存储块主要包含关键字和指向孩子的指针，最多能有几个孩子取决于每个存储块的容量和数据库的相关配置（通常情况下m是很大的）。

B树特征：

1）根节点至少包括两个孩子。

2）树中每个节点最多含有m个孩子（m>=2）。

3）除根节点和叶节点外，每个节点至少有ceil（m/2）个孩子。ceil向上取整

4）所有叶子节点位于同一层。

5）假设每个非终端节点中包含有n个关键字信息，其中：

    a）Ki（i=1...n）为关键字，且关键字按顺序升序排序K(i-1)<K(i)。

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

    c）非叶子节点的指针：P[1]，P[2]...P[M]，其中P[1]指向关键字小于K[1]的子树（某节点最左边孩子节点关键字的值均小于该节点最左边关键字的值），P[M]指向关键字大于K[M-1]的子树（某节点最右边孩子节点的关键字的值均大于该节点里所有关键字的值），其他P[i]指向关键字属于（K[i-1]，K[i]）的子树（某节点其余孩子节点关键字的值的大小均位于离该孩子节点指针最近的两个关键值之间）。

查找效率和二叉查找树一样，为O（logn）。B树通过合并、分裂、上移、下移节点保持特征，使树比二叉树矮，数据不断变动后不会变成线性的。

B树示例：

• 利用B+树

B+树是B树的变体，其定义基本与B树相同，除了：

1）非叶子节点的子树指针与关键字个数相同。（B+树能存储更多的关键字）

2）非叶子节点的子树指针P[i]，指向关键值（K[i],K[i+1]）的子树。（K[i]指向的子树，均小于关键字K[i+1]的值）

3）非叶子节点仅用来索引，数据都保存在叶子节点中。（B+树所有的检索都是从根部开始，检索到叶子节点结束，非叶子节点仅存储索引不存储数据，能存储更多的数据。B+树相对B树更矮。B树的搜索可能在任何一个非叶子节点就终结掉了。）

4）所有叶子节点均有一个链指针指向下一个叶子节点并按大小顺序链接。（支持范围统计，即定位到某个叶子节点便可以从该叶子节点开始横向跨子树统计。）

B+树示例：

B+树更适合用来做存储索引：

1）B+树的磁盘读取代价更低（B+树内部结构没有指向关键字具体信息的指针，不存放数据，只存放索引信息。内部节点相对B树更小。如果把所有内部节点的关键字存放在同一盘块中，盘块能容纳的关键字数量也越多，一次性读入内存查找的关键字也就越多，相对来说IO读写次数低）。

2）B+树的查询效率更加稳定（内部节点不是指向文件内容的节点，只是叶子节点中关键字的索引，任何节点的查找必须有一条从叶子节点到根节点的路，所有关键字查询的长度相同，每个数据的查询时间相同，O(logn)）。

3）B+树更有利于对数据库的扫描（B+树只需要遍历叶子节点就可以解决对全部数据的扫描）。

• 利用Hash以及BitMap

Hash索引

根据Hash函数的运算只需1次定位便能找到需要查询数据所在的头。Hash索引的查询效率理论上高于B+树索引。

缺点：

1）仅仅能满足“=”，“IN”，不能使用查询范围。（Hash索引比较的是进行Hash运算后的Hash值，只能用于等值的过滤，不能用于基于范围的查询，因为经过相应的Hash算法处理过的Hash值的大小关系不能保证和Hash运算前的完全一样。）

2）无法被用来避免数据的排序操作。

3）不能利用部分索引键查询。（对于组合索引，Hash索引在计算Hash值的时候是组合键，将键组合合并后在一起计算Hash值，而不是单独计算Hash值。通过组合索引的前一个或几个索引键进行查询时Hash索引也无法被利用。B+树支持利用组合索引中的部分索引。）

4）不能避免表扫描。（Hash索引是将索引键通过Hash运算后将运算结果的Hash值和所对应的行指针存放在一个Backet中，不同的索引键具有相同的Hash值，所以取出满足某个Hash键值的数据也无法从Hash索引中直接完成查询，还是需要访问Backet中的数据进行比较。）

5）遇到大量Hash值相等的情况性能并不一定会比B树索引高。

BitMap位图索引

当表中的某个字段只有几种值的时候，在该字段上实现高效统计用位图索引是最佳的选择。目前很少数据库支持位图索引，已知比较主流的是Oracle。位图索引的结构类似B+树。在存储方式上会先按照状态值分开，每种值的空间存放每个实际的数据行是否是这个值。因为只需要存放是与否，所以只需要一个Bit位存放。理论上一个叶子块可以存放非常多的Bit位来表示不同的行。

缺点：

锁的密度非常大，当尝试新增或修改数据时，与它在同一个位图的数据操作都会被锁住。因为某行所在的位置顺序会因为数据的添加或者删除而发生改变。不适合高并发的联机事务处理系统，即常见的OLTP系统。而适合并发较少，统计数据较多的OLAP系统。

 

三、密集索引和稀疏索引的区别

密集索引文件中的每个搜索码值都对应一个索引值。（叶子节点不仅保存键值，还保存了位于同一行记录里的其他列的信息。密集索引决定了表的物理排列顺序，一个表只能有一个物理排列顺序，所以一个表只能创建一个密集索引。）

稀疏索引文件只为索引码的某些值建立索引项。（叶子节点仅保存键位信息和该行数据的地址，有的稀疏索引仅保存键位信息及其主键。定位到叶子节点仍需要地址或主键信息进一步定位到数据。）

MySQL常见的两种的存储引擎：

MyISAM：主键索引、唯一键索引、普通索引其索引均属于稀疏索引

InnoDB：必须有且仅有一个密集索引，密集索引的选取规则如下：

1）若一个主键被定义，则该主键作为密集索引。

2）如果没有主键被定义，该表的第一个唯一非空索引则作为密集索引。

3）若不满足以上条件，innodb内部会生成一个隐藏主键（密集索引）。

4）非主键索引存储相关键位和其对应的主键值，包含两次查找。（非主键索引即稀疏索引的叶子节点不存储行数据的物理地址，而是存储的该行的主键值，所以非主键索引包含两次查找，一次查找次级索引自身，再查找主键。见下图左）

 

InnoDB使用密集索引，将主键组织到一棵B+树中，行数据就存储在叶子节点上。因为InnoDB的主键索引和对应的数据是保存在同一个文件，检索时在加载叶子节点的数据进入内存时，也加载了对应的数据。若对稀疏索引进行条件筛选，首先在稀疏索引的B+树中检索该键，获取到主键信息。然后利用主键在密集索引B+树中再执行一遍检索操作，最终到达叶子节点，获取整行数据。

MyISAM均为稀疏索引，稀疏索引的两棵B+树节点结构完全一致，只是存储的内容不一样。主键索引B+树存储主键，辅助键索引B+树存储辅助键，表数据存储在独立的地方，索引和数据是分开存储的。两棵B+树的叶子节点都使用地址指向真正的表数据。对于表数据来说，两个键没有任何差别。通过辅助键检索无需访问主键的索引树。

参考：聚簇索引和非聚簇索引、如何避免回表查询？什么是覆盖索引？

 

四、索引模块衍生出来的问题（以MySQL为例）

• 如何定位并优化慢查询SQL？

1）根据日志定位慢查询sql

慢日志是记录执行的比较慢的SQL。

执行show variable like '%query%';显示long_query_time为10秒即SQL执行时间超过10秒会被记录在慢日志中，slow_query_log为OFF慢日志为关闭状态，slow_query_log_file慢日志存储地址。通过set global slow_query_log=on;设置打开慢日志。该语句只是暂时保存，重启数据库服务会还原成原来的样子。

show status like '%slow_queries%';显示慢查询的条数。

通过慢日志捕获慢sql，进而分析sql为什么慢，然后对它进行调优。

2）使用explain等工具分析SQL

关键字放在select查询语句的前面，用于描述MySQL如何执行查询操作，以及MySQL成功返回结果集需要执行的行数。explain可以分析select语句，知道查询效率低下的原因，从而改进查询。

explain关键字字段

a）type

MySQL找到需要数据行的方式，性能从最优到最差排序如下：

system，const，eq_ref，ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery，range，index，all

index和all查询是全表扫描。

b）extra

3）修改SQL或尽快让SQL走索引

只有DML数据操纵语言才会进慢查询语句中，DDL数据定义语言不会进入慢SQL 。

（DQL 数据查询语言 select

DML 数据操纵语言 insert、update、delete

DDL 数据定义语言 crete、drop

DCL 数据控制语言 grant、revoke）

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

联合索引：由多列组成的索引

最左匹配原则：假设有两列A、B，对A设置联合索引，即将A和B都设置为索引，顺序是A、B。在where语句中调用where A=? and B=?，会走这个索引； 调用where A=?也会走这个索引；调用where B=?就不走这个索引了。

1、最左匹配非常重要的原则，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的顺序都可以任意调整。

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

MySQL创建联合索引首先会对复合索引最左边即第一个索引字段的数据进行排序，在第一个排序字段的基础上再对后面第二个索引字段进行排序，类似实现了order by 字段1 order by 字段2，第一个字段绝对有序第二个字段无序。因此MySQL用第二个字段进行条件判断是用不到索引的。

• 索引是建立的越多越好吗？

不是。

1）数据量小的表不需要建立索引，建立会增加额外的索引开销。

2）数据变更需要维护索引，因此更多的索引意味着更多的维护成本。

3）更多的索引意味着需要更多的空间。

• 索引失效

参考：索引失效

 

锁模块

一、MyISAM和InnoDB关于锁方面的区别？

MyISAM默认使用表级锁，不支持行级锁；InnoDB默认用的行级锁，也支持表级锁。无论是表锁还是行锁，均分为共享锁share lock（S）和排它锁exclusive lock（X）。

• MyISAM

MyISAM先上读锁后上写锁（被Block）、读锁（不被Block）

MyISAM对数据进行select时，自动加上一个表级读锁，表级锁自动锁住整张表；对数据进行增删改时，操作表加上一个表级别的写锁。读锁未被释放时，另外一个Session（数据库客户端一个窗口tab就是一个Session）想要对该表加上一个写锁就会被阻塞（Block），直到所有的读锁都被释放为止。

显示给表加上读锁：lock table 表名 read;

释放锁：unlock tables;

读锁也叫共享锁（S锁），因为在进行范围查询时依然能对表里的数据进行读操作。

MyISAM先上写锁后上读锁（被Block）、写锁（被Block）

当上了写锁在上读锁时，需要等待写锁的释放。上写锁的同时再上写锁，也被阻塞。所以写锁也叫排它锁（X锁）。

上共享锁后依然支持上共享锁，上排它锁后共享锁和排它锁都不支持。

除了可以对insert、update、delete语句上排它锁，也可以对select语句上排它锁。在语句后面加上for update。

• InnoDB

InnoDB用的二段锁，即加锁和解锁是分成两个步骤。先对同一个事务里的一批操作进行加锁，commit后再对事务加上的锁进行统一的解锁。MySQL自动提交事务，即commit是自动提交的。

InnoDB在SQL没用到索引时走的是表级锁，用到索引时走的时行级锁和gap锁。

InnoDB的锁默认支持行级锁。InnoDB对select进行了改进，在select语句后面加lock in share mode显示上读锁，才不可以上写锁。可以上共享锁。

InnoDB除了支持行级锁外，还支持表级意向锁，意向锁分为意向共享锁IS、意向排它锁IX，作用是在进行表级别操作时不用轮询每一行看有没有上行锁。

• 数据库锁的分类

按锁的粒度划分，可以分为表级锁、行级锁、页级锁。（BDB引擎使用页级锁，介于表级锁和行级锁，锁定位于同一个存储页的相邻几行数据）

按锁级别划分，可分为共享锁和排它锁。

按加锁方式划分，可分为自动锁、显式锁。

按操作划分，可分为DML锁、DDL锁。

按使用方式划分，可分为乐观锁和悲观锁。（悲观锁对外界的修改持保守态度，外界指即本系统当前的其他事务和外部系统的事务处理。全程用排它锁锁定是悲观锁的一种实现。悲观并发控制是先取锁再访问的保守策略，对数据处理的安全提供了保证。在效率方面处理加锁的机制会产生额外的开销，增加产生死锁的机会；乐观锁认为数据一般情况不会造成冲突，数据提交更新时才会对数据的冲突与否进行检测，发现冲突返回用户错误的信息，让用户决定如何去做。相对悲观锁对数据进行处理时，乐观锁不会使用事务的锁机制，一般实现乐观锁的方式是记录数据版本。实现数据版本有两种方式：第一种是使用版本号；第二种是使用时间戳）

 

二、数据库事务的四大特性

事务是访问并可能更新数据库中各种数据项的一个程序执行单元。

ACID

A原子性：事务包含的所有操作要么全部执行，要么全部失败回滚。要么全做，要么全不做。

C一致性：事务应确保数据库的状态从一个一致状态转变为另外一个一致的状态。以转账为例，A账户+B账户=2000，无论A和B如何转账，转几次账，A和B的钱加起来还是2000。

I隔离性：多个事务并发执行时一个事务的执行不影响其他事务的执行。下面的知识点是对隔离性的深入研究。

D持久性：一个事务一旦提交，对数据库的修改永久保存在数据库中。当系统或者介质发生故障时确保已提交事务的更新不能丢失，即对已提交事务的更新能恢复。一旦一个事务被提交，DBMS保证提供适当冗余，使其耐得住系统的故障。

 

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

事务并发访问引起的问题以及如何避免？

1）更新丢失--MySQL所有事务隔离级别在数据库层面上均可避免

2）脏读（一个事务读到另一个事务未提交的数据）--Read-Committed即RC事务隔离级别以上可以避免（Read-Committed规定事务只能读取其他事务已经提交的数据，不允许读未提交的数据）

查询当前Session的事务隔离级别：select @@tx_isolation;

设置当前Session的事务隔离级别为read uncommitted：set session transaction isolation level read uncommitted;

3）不可重复读（事务A多次读取同一数据，事务B在事务A读取数据时对数据更新并提交，导致事务A多次读取数据时数据不一致）--Repeatable-Read即RR事务隔离级别以上可以避免

4）幻读（事务A读取与搜索条件相匹配的若干行，事务B以插入或删除行的方式来修改事务A的结果集，导致事务A看起来像出现幻觉一样）--Serializable事务隔离级别可避免

不可重复读侧重于对同一数据的修改，幻读侧重于新增或删除。

事务隔离级别越高，安全性越高，串行化执行越严重，降低数据的并发度。根据业务的需要设置事务的隔离级别。Oracle默认为Read-Committed，MySQL默认为Repeatable-Read。

 

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

表象：在RR级别下，基于伪MVCC（多版本并发控制，读不加锁，读写不冲突）实现的快照读（非阻塞读）来避免使我们看到幻行

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

当前读：select...lock in share mode、select...for update、update、delete、insert。加了锁的增删改查语句，不管是共享锁还是排它锁。读取的是记录的最新版本，读取之后还需要保证其他并发事务不能修改当前记录，对读取的记录加锁，所以叫当前读。除了select...lock in share mode对记录加共享锁，其他都加排它锁。

为什么update、delete、insert也是当前读? RDMS关系型数据库管理系统由两部分组成，程序实例和存储InnoDB，如图。update操作内部包含一个当前读来获取数据的最新版本。

快照读：不加锁的非阻塞读，select。不加锁是在事务隔离级别不为Serializable的前提下。在Serializable下，由于是串行读，快照读退化成当前读，即select...lock in share mode。快照读是为了提升并发性能，快照读的实现是基于多版本并发控制即MVCC，MVCC是行级锁的一个变种，在很多情况下避免了加锁操作，因此开销更低。基于多版本意味着快照读读到的不一定是数据的最新版本，可能是历史版本。

 

五、RC、RR级别下的InnoDB的非阻塞读如何实现？（MVCC）

1）数据行里DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID字段

每行数据的记录除了存储数据外，还有额外的字段，其中最重要的就是DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID。DB_TRX_ID字段标识最近一次对本行记录做修改，不管是insert或update，事务的标识符，即最后一次修改本行记录的事务的ID。

DB_ROLL_PTR回滚指针，写入回滚段Rollback segment的undo日志记录，如果一行记录被更新，undo log report包含重建该行记录被更新之前内容所必须的信息。

DB_ROW_ID行号，包含一个随着新行插入而单调递增的行id，由innoDB自动产生聚集索引时，聚集索引会包含行id的值，否则行id不会出现在任何索引中。InnoDB的表即没有主键也没有唯一键时，InnoDB会自动隐式创建一个的自动递增隐藏主键字段，即DB_ROW_ID。

2）光有这三个字段不足以实现快照读，还需要undo日志。当对记录做了变更操作时，就会成undo记录。undo记录存储的是老版数据，当一个旧事务需要读取数据时，为了读取老版数据，需要顺着undo链找到满足其可见性的记录。undo log主要分为两种，insert undo log和update undo log，insert undo log表示事务对insert新纪录产生的undo log，只在事务回滚时需要，事务提交后就可以立即丢弃。update undo log是事务在对数据delete、update时产生的undo log，不仅在事务回滚时需要，快照读也需要，所以不能随便删除。只有当数据库使用的快照不涉及该日志记录，对应的回滚日志才会被线程删除。

日志的工作方式：

事务对行记录的更新过程。InnoDB在内部做了非常多的工作。假设对DB_ROLE_ID为1的行做变动，被事务A做修改，将Field2的值由12改为32，修改流程如下：首先用排他锁锁定该行，将该行修改前的值拷贝一份到undo log里面，修改当前行的值，填写事务ID即DB_TRX_ID，使用回滚指针指向undo log中修改前的行。之后假设数据库还有别的事务使用快照读读取该日志记录，此时某个事务又对同一行做了修改，Field3由13改为45，效果和刚刚一样，又多了一条undo记录。按照修改的时间顺序由近到远，通过DB_ROLL_PTR连接起来。

3）read view

read view做可见性判断，当进行快照读Select时对针对查询出的数据做read review来决定当前事务能看到的是哪个版本的数据，有可能是最新版本的数据，也有可能是undo log某个版的数据。read review遵循一个可见性算法，将要修改的数据的DB_TRX_ID与系统其他活跃事务ID做对比，大于等于这些ID，就通过DB_ROLL_PTR取出undo log上一层的DB_TRX_ID，直到小于这些活跃事务ID为止，这样保证当前数据版本是当前可见的最稳定版本。

RR级别下，Session在start transaction后，第一条快照读会创建一个快照read view，将当前系统中活跃的其他事务记录，此后在调用快照读还是用同一个read；RC级别下，事务中每条Select语句即每次调用快照读时都会创建一个新的快照。所以在RC下能用快照读看到别的事务提交的对表事务的增删，在RR下首次使用快照读是在别的事务对数据做出增删改并提交之前的，此后即使别的事务对数据做了增删改并提交还是都不到数据变更。对RR首次事务提交的时机是重要的。

gap锁：gap是指索引数中插入新记录的空隙，gap锁即间隙锁锁定一个范围但不包括记录本身，gap锁的目的是为了防止同一事务的两次当前读出现幻读的情况，gap锁在read commit和更低级别的事务隔离级别下是没有的，RR和Serializable默认支持gap锁。

RR下gap锁出现的场景：对主键索引或者唯一键索引，如果where条件全部命中，则不会用gap锁，只会加记录锁；如果where条件部分命中或者全不命中，则会加gap锁。

gap锁会用在非唯一索引或者不走索引的当前读中。

参考：谈谈你对Mysql的MVCC的理解、MVCC解决了什么问题

 

MyISAM和InnoDB引擎区别

1、存储结构（主索引／辅助索引）

InnoDB的数据文件本身就是主索引文件。而MyISAM的主索引和数据是分开的。

InnoDB的辅助索引data域存储相应记录主键的值而不是地址。而MyISAM的辅助索引和主索引没有多大区别。

InnoDB是聚簇索引，数据挂在主键索引之下。

2、锁

MyISAM使用的是表锁；InnoDB使用行锁

3、事务

MyISAM没有事务支持和MVCC；InnoDB支持事务和MVCC

4、全文索引

MyISAM支持FULLTEXT类型的全文索引；InnoDB不支持FULLTEXT类型的全文索引，但是InnoDB可以使用sphinx插件支持全文索引，并且效果更好。

5、主键

MyISAM允许没有任何索引和主键的表存在，索引都是保存行的地址；InnoDB如果没有设定主键或非空唯一索引，就会自动生成一个6字节的主键，数据是主索引的一部分，附加索引保存的是主索引的值。

6、外键

 MyISAM不支持；InnoDB支持

 

关键语法

group by：给定数据列的每个成员，对查询结果进行分组统计，最终得到一个分组汇总表。

对同一张表

1）select子句的列名必须为分组列（group by用到的列）或列函数（count、sum、max、min、avg）

2）列函数对于group by子句定义的每个组各返回一个结果

order by ：根据指定的列对结果集进行排序，默认按照升序（ASC）对记录进行排序，降序使用 DESC 关键字。

having：1）通常与group by子句一起使用（在group by后指定过滤的条件，省略group by，having就和where一样）

2）where过滤行，having过滤组

3）出现在同一sql的顺序：where>group by>having

统计相关（聚合函数）：count求总数、sum求和、max求最大值、min求最小值、avg求平均。

在MySQL数据库中，聚合函数不能出现在where语句中，聚合函数的实现是基于所有数据的基础上，where语句是对数据进行筛选的。

查询前N条记录：limit

 

数据库范式

第一范式：属性不可分割

第二范式：要求表中要有主键，表中其他其他字段都依赖于主键（主键约束）

第三范式：要求表中不能有其他表中存在的、存储相同信息的字段，不得存在传递依赖（外键约束）

 

主从复制、读写分离、分库分表

binlog和redolog的区别

1. redolog是在InnoDB存储引擎层产生，而binlog是MySQL数据库的上层服务层产生的。
2. 两种日志记录的内容形式不同。MySQL的binlog是逻辑日志，其记录是对应的SQL语句，对应的事务。而innodb存储引擎层面的重做日志是物理日志，是关于每个页（Page）的更改的物理情况。
3. 两种日志与记录写入磁盘的时间点不同，binlog日志只在事务提交完成后进行一次写入。而innodb存储引擎的重做日志在事务进行中不断地被写入，并日志不是随事务提交的顺序进行写入的。
4. binlog不是循环使用，在写满或者重启之后，会生成新的binlog文件，redolog是循环使用。
5. binlog可以作为恢复数据使用，主从复制搭建，redolog作为异常宕机或者介质故障后的数据恢复使用。

参考：Mysql的Binlog原理

Mysql读写分离以及主从同步

1. 原理：主库将变更写binlog日志，然后从库连接到主库后，从库有一个IO线程，将主库的binlog日志拷贝到自己本地，写入一个中继日志中，接着从库中有一个sql线程会从中继日志读取binlog，然后执行binlog日志中的内容，也就是在自己本地再执行一遍sql，这样就可以保证自己跟主库的数据一致。
2. 问题：这里有很重要一点，就是从库同步主库数据的过程是串行化的，也就是说主库上并行操作，在从库上会串行化执行，由于从库从主库拷贝日志以及串行化执行sql特点，在高并发情况下，从库数据一定比主库慢一点，是有延时的，所以经常出现，刚写入主库的数据可能读不到了，要过几十毫秒，甚至几百毫秒才能读取到。还有一个问题，如果突然主库宕机了，然后恰巧数据还没有同步到从库，那么有些数据可能在从库上是没有的，有些数据可能就丢失了。所以mysql实际上有两个机制，一个是半同步复制，用来解决主库数据丢失问题，一个是并行复制，用来解决主从同步延时问题。
3. 半同步复制：semi-sync复制，指的就是主库写入binlog日志后，就会将强制此时立即将数据同步到从库，从库将日志写入自己本地的relay log之后，接着会返回一个ack给主库，主库接收到至少一个从库ack之后才会认为写完成。
4. 并发复制：指的是从库开启多个线程，并行读取relay log中不同库的日志，然后并行重放不同库的日志，这样库级别的并行。（将主库分库也可缓解延迟问题）

 

推荐资料

何登成的blog、《MySQL技术内幕InnoDB存储引擎》姜承尧老师的书、叶金荣老师的书