1.2　数据库

本节介绍关于数据库的高频面试题目，主要包含以下内容：一次SQL查询的执行过程，binlog的类型、作用与使用场景，事务两阶段提交，B+树索引，存储引擎、事务ACID的特性及实现原理，事务的隔离级别及如何解决脏读、不可重复读、幻读问题，多版本并发控制MVCC，数据库锁（行锁、表锁、乐观／悲观锁），索引优化，MySQL高可用架构，数据库缓存，索引下推，索引覆盖，数据库热点更新等。

1.2.1　一次SQL查询的执行过程

SQL查询操作的一般流程如图1-75所示。

具体步骤如下：

步骤01　连接器负责与客户端建立并管理连接、获取权限等。

步骤02　查询缓存，执行过的语句及其结果可能会以键值对的形式缓存在内存中，key是查询语句，value是查询结果；若查询命中缓存则直接返回。如果表发生变化，那么和该表相关的所有缓存数据都将失效，因此只有数据更新不频繁的静态配置表才开启查询缓存。

步骤03　分析器进行语法分析，判断SQL是否满足MySQL语法。

步骤04　优化器选择索引，或者在多表关联时决定各个表的连接顺序，以此获取较好的查询性能。

步骤05　执行器通过存储引擎的接口获取数据。

1.2.2　binlog日志备份的3种模式

MySQL的binlog日志备份有3种模式：基于行的复制（Row-Based Replication，RBR）、基于SQL语句的复制（Statement-Based Replication，SBR）和混合模式复制（Mixed-Based Replication，MBR)，分别说明如下：

1．RBR

日志记录了每一行数据被修改的细节，然后在Slave端对数据进行同样的修改，RBR模式可能会产生大量的日志。

2．SBR

记录每一条修改数据的SQL，Slave端解析出和Master端相同的SQL后执行。由于不记录每一行数据的变化，因此产生的binlog日志量较少。但为了让SQL在Slave端执行的效果和Master相同，因此必须记录每条语句在Master端执行时的上下文信息。

3．MBR

MBR模式是RBR和SBR两种模式的结合，根据执行的具体SQL来区分对待记录日志的形式，比如表结构变更以SBR模式来记录，update、delete等SQL语句一般以RBR模式记录。

1.2.3　事务的两阶段提交

事务就是定义的一系列数据库操作，这些操作要么全部执行，要么全部不执行，是不可分割的。两阶段提交协议（Two-Phase Commit，2PC）用于分布式系统保证数据的一致性，该协议将一个分布式的事务分成两个阶段：投票阶段（Voting phase）和提交阶段（Commit Phase）。

1．投票阶段

协调者发送事务执行请求给所有参与者，参与者根据事务在本地执行成功或者失败的结果回复同意或取消。

2．提交阶段

协调者可能收到3种事务执行结果：

●　所有参与者成功执行事务。

●　部分参与者执行事务失败。

●　协调者等待超时。

如果所有参与者成功执行事务，那么协调者将向所有参与者发送事务提交的通知，参与者提交事务并返回执行结果，整体执行流程如图1-76所示。

如果部分参与者执行事务失败或者协调者等待超时，那么协调者向所有参与者发送事务回滚通知，参与者执行回滚操作、释放资源，并将执行结果返回给协调者，整体执行流程如图1-77所示。

1.2.4　二叉搜索树、平衡树、红黑树、B树、B+树的区别

1．二叉搜索树

二叉搜索树（见图1-78）的定义需满足以下3点：

●　对于任意节点，若左子树不为空，则左子树的值均小于根节点的值。

●　对于任意节点，若右子树不为空，则右子树的值均大于根节点的值。

●　任意节点的左右子树也分别是二叉搜索树。

查询二叉搜索树的平均时间复杂度是O(logn)；如图1-78所示，一些场景下二叉搜索树会退化为线性链表结构，查询时间复杂度为O(n)，因此，在二叉搜索树的基础上出现了AVL树（平衡二叉树）。

2．AVL树

AVL是平衡二叉树，它的所有节点的左、右子树高度差不超过1，如图1-79所示。由于插入和删除元素都需要通过旋转来维护树的平衡性，因此旋转操作付出的代价甚至比查询获得的收益还大，基于这个问题又出现了红黑树。

3．红黑树的特点

红黑树需要满足以下条件：

●　根节点是黑的。

●　每个节点非红即黑。

●　叶子节点是黑的。

●　如果一个节点是红的，那么它的子节点一定是黑的。

●　对于任意节点，它到叶子节点的所有路径上都包含相同数目的黑节点。

红黑树的示例如图1-80所示。

红黑树是一种弱平衡二叉树，相对于AVL树来说，维护红黑树的上述特性旋转的次数较少，但查询时间复杂度依然为O(logn)，因此红黑树应用广泛，比如HashMap底层数据结构就引入红黑树来加快查询速度，IO多路复用的epoll实现也采用红黑树来管理文件描述符。但红黑树相对于多路查找树而言树高较高，不适合用作数据库的索引。

4．B树

B树是一种平衡多路查找树，与二叉搜索树相比，B树每个节点有多个分支；与红黑树相比，相同数量的节点B树的高度远小于红黑树的高度。B树示意图如图1-81所示。一棵M阶B树的性质如下：

●　每个节点至多有M棵子树。

●　若根节点不是叶子节点，那么根节点的儿子数为[2,M]。

●　除根节点和叶子节点外，其他每个节点的儿子数为[M/2,M]。

●　非叶子节点的关键字：K[1],K[2],…,K[M-1]；且K[i]<K[i+1]。

●　非叶子节点的指针：P[1],P[2],…,P[M]。其中P[1]指向的关键字小于K[1]，P[M]指向的关键字大于K[M-1]，其他P[i]指向的关键字属于(K[i-1],K[i])。

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

5．B+树

B+树是B树的演进，示意图如图1-82所示。

与B树相比，B+树有以下两个核心不同点：

●　叶子节点间存在一个链表指针。

●　所有关键字都在叶子节点出现，非叶子节点是叶子节点的索引，叶子节点相当于是存储数据的数据层。

因此，B+树相对于B树来说，更适合用作数据库索引：

●　B+树内部节点是叶子节点的索引，不存放真正的数据，树高更低，IO次数更少。

●　B+树对关键字的查询都会走一条从根到叶子节点的路径，路径长度相同，查询性能稳定。

●　B+树所有叶子节点通过横向指针形成了一个有序链表，便于基于范围的查询。

1.2.5　MySQL存储引擎InnoDB与MyISAM的区别

MySQL有多种存储引擎，包括：MyISAM和InnoDB等。两者的主要区别如下：● InnoDB支持事务，MyISAM不支持事务。

●　InnoDB支持外键，MyISAM不支持。

●　InnoDB是聚集索引，MyISAM是非聚集索引。

●　InnoDB锁最小粒度是行锁，MyISAM锁最小粒度是表锁。

1.2.6　聚集索引与非聚集索引的区别

聚集索引与非聚集索引的区别如下：

（1）每张表只能有一个聚集索引，可以有多个非聚集索引。

（2）聚集索引的叶节点就是数据节点，非聚集索引的叶节点仍然是索引，存在一个指针指向对应的数据块。

（3）聚集索引键值的逻辑顺序决定了相应行的物理存储顺序，非聚集索引的逻辑顺序与磁盘上行的物理存储顺序不同。

1.2.7　数据库事务的4大特性

ACID是事务的4大特性，详细说明如下：

1．原子性（Atomicity）

原子性是指一个事务不可分割，事务中的操作要么都做，要么都不做。其实现原理是：事务执行前，MySQL会将更新前的数据保存到undo日志中，如果事务执行失败，就可以利用undo日志进行回退。

2．一致性（Consistency）

一致性是指在保证原子性、持久性和隔离性的基础上，数据库的完整性约束没有被破坏，事务执行前后都是合法的数据状态。

3．隔离性（Isolation）

隔离性是指事务的内部操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。当数据库有多个事务同时执行时，可能出现脏读、不可重复读、幻读的问题，不同的事务隔离级别可以解决对应的问题。

●　一个事务写操作对另一个事务写操作的影响：锁机制保证隔离性。

●　一个事务写操作对另一个事务读操作的影响：MVCC保证隔离性。

事务隔离级别与MVCC在后面会展开解释。

4．持久性（Durability）

事务一旦提交，事务的状态就会被持久化在数据库中，理论上任何故障都不应该对本次事务的修改有任何影响。

为了避免每次读写数据库都产生随机IO，InnoDB为MySQL提供了缓冲池（Buffer Pool），读取数据时先从Buffer Pool读取，数据不存在则从磁盘读取后再写入Buffer Pool；数据写入时先写入Buffer Pool，Buffer Pool中的数据定期刷盘。Buffer Pool提升了数据库的读写性能，但数据库一旦宕机，Buffer Pool中缓存的数据若未刷盘就会丢失。为了保证事务的持久性，所有对数据的修改先写入redo log，再更新到Buffer Pool。如果MySQL宕机，重启时可以读取redo log对数据进行恢复。

1.2.8　事务隔离级别与脏读、不可重复读、幻读

数据库事务有4种隔离级别：读未提交（Read Uncommitted，RU）、读已提交（Read Committed，RC）、可重复读（Repeatable Read，RR）、串行化（Serializable）。低事务隔离级别通常可以执行更高的并发，但容易产生不符合预期的脏数据。高事务隔离级别可以保证数据的完整性和一致性，但对并发性能有较大影响。下面给出4种隔离级别的定义：

●　读未提交：该隔离级别下，一个事务在执行过程中可以看到其他未提交的事务操作。

●　读已提交：该隔离级别下，一个事务只能看见已提交事务所做的改变。

●　可重复读：MySQL默认的隔离级别，该隔离级别下，保证在同一个事务中多次读取同样记录的结果是一致的。

●　串行化：最高的隔离级别，强制事务串行执行。

4种隔离级别及其存在的问题如表1-5所示。

对脏读、不可重复读和幻读的解释如下：

1．脏读

在当前事务（A）中可以读到其他事务（B）未提交的数据，这种现象是脏读，如表1-6所示。

2．不可重复读

在事务A中先后两次读取同一条记录但结果不一样，这种现象称为不可重复读，如表1-7所示。

脏读与不可重复读的区别在于前者读到的是其他事务未提交的数据，后者读到的是其他事务已提交的数据。

3．幻读

在事务A中按照某个条件前后两次查询，后一次查询看到了前一次查询没有看到的行，这种现象称为幻读，如表1-8所示。

不可重复读与幻读的区别在于前者是数据变了，后者是数据的行数变了。

1.2.9　redo、undo、binlog数据库日志的作用

1．redo log

为了避免每次读写数据库都会产生随机IO，InnoDB为MySQL提供了Buffer Pool，读取数据时先从Buffer Pool读取，数据不存在则从磁盘读取后再写入Buffer Pool；数据写入时先写入Buffer Pool，Buffer Pool中的数据定期刷盘。Buffer Pool提升了数据库的读写性能，但数据库一旦宕机，Buffer Pool中缓存的数据若未刷盘就会丢失，因此，当数据变更时，还需要记录redo log，redo log是预写日志（Write-ahead logging，WAL），所有对数据的修改先写入redo log，再更新到Buffer Pool，如果MySQL宕机，重启时可以读取redo log对数据进行恢复。这里需要说明的是，不同于数据库操作产生的随机读写，写redo日志是顺序写，效率极高。因此，记录redo日志虽然是磁盘操作但对数据库整体的读写性能几乎没有影响。

2．undo log

事务执行前，MySQL会将更新前的数据保存到undo日志中，如果事务回滚或者数据库崩溃，可以利用undo日志进行回退。undo log也可以用于实现多版本并发控制（MVCC）。

3．binlog

主从复制，从库利用主库上的binlog进行数据回放，实现主从同步。

1.2.10　多版本并发控制

MVCC（Multi-Version Concurrency Control，多版本并发控制）解决了读写不冲突的问题，实现上依赖了Readview和undo版本链。ReadView存储当前活跃的读写事务列表，也就是开启但还未提交的事务。通过这个读写事务列表来判断记录的某个版本是否对当前事务可见。在读已提交隔离级别下，每次SELECT都会生成最新的Readview；在可重复读隔离级别下，只在事务第一次SELECT时创建一次Readview。undo日志记录了数据被修改的历史版本。下面举例解释MVCC的工作原理。

图1-83展示了读写事务列表的可见区间、临界点。

●　trx_ids：ReadView初始化时当前未提交的事务列表。

●　trx_up_limit_id：当前已提交的事务id加1，若事务id < trx_ up_limit_id，则对于当前ReadView可见。

●　trx_low_limit_id：当前最大的事务id加1，若事务id≥low_limit_id，则对于当前ReadView不可见。

假设数据库当前存在一条记录，如表1-9所示。

有一个事务（trxId = 30）更新id=1的记录name=李四-V2，此时undo log版本链如表1-10所示。

接着有一个事务（trxId = 100）修改name =李四-V3，但事务未提交，此时的版本链如表1-11所示。

现在一个事务A查询id=1的记录，因为当前活跃的事务id列表是trx_ids = [100]，所以id = 1的V3版本数据不可见，那么根据版本链往下查找，发现trx_id = 30的事务id小于trx_up_limit_id，因此当前查询返回李四-V2版本。

将事务（trxId = 100）提交，接着一个新的事务（trxId = 110）修改id=1的记录name=李四-V4，但不提交事务，此时版本链如表1-12所示。

此时事务A又执行了一次查询id = 1的记录。

若隔离级别为读已提交，则每次查询都会重新生成新的ReadView，活跃事务列表trx_ids更新为[110]，因此查询结果为李四-V3版本。

若隔离级别为可重复读，则ReadView只在事务开启的第一次查询时生成，之后的查询都复用之前的ReadView，那么活跃事务列表trx_ids依然为[100]，因此事务A查询的结果是李四-V2版本。

1.2.11　数据库行锁、表锁、乐观锁与悲观锁

1．行锁

行锁锁住一行或者多行记录。行锁开销大，加锁慢，可能会出现死锁，但锁定粒度小、并发度高。InnoDB存储引擎默认的事务隔离级别为可重复读，并且在参数innodb_locks_unsafe_for_binlog = 0的模式下，行锁有3种类型：记录锁（record lock）、间隙锁（GAP lock）、临键锁（next-key lock）。

1）记录锁

记录锁锁住的是某一条记录，记录锁的触发条件是等值查询、命中唯一索引。在读已提交隔离级别下，只有记录锁模式。

2）间隙锁

间隙锁锁住的是一个区间，并且左右都是开区间。在读已提交隔离级别下，不会使用间隙锁，只有当隔离级别是可重复读和串行化时才会存在间隙锁。例如以下SQL语句：

     SELECT * FROM tName WHERE id BETWEEN 2 and 6 FOR UPDATE;

执行该SQL，（2，6）整个区间的记录都会被锁住，即id为3，4，5的数据行插入时会被阻塞。

3）临键锁

临键锁是记录锁和间隙锁的组合，锁住的是索引本身以及索引之前的间隙，是一个左开右闭的区间。在可重复读事务隔离级别下查询非唯一索引时，InnoDB使用临键锁防止幻读。举个例子，一个索引列col包含值12，13，15和22，临键锁的范围如下：

     (negative infinity, 12]
     (12, 13]
     (13, 15]
     (15, 22]
     (22, positive infinity)
     事务1 select...where col =12 for update;
     事务2 insert into … ( … ,col, …)values (… ,11, …);
     事务3 insert into … ( … ,col, …)values (… ,13, …);

事务2被阻塞，无法插入数据，事务3正常执行。

2．表锁

表锁锁定整张表，锁定期间其他事务不能对该表进行操作。查询非索引字段时一般触发表锁。表锁开销小，加锁快，不会出现死锁，但锁粒度大，并发度低。

3．数据库的乐观锁与悲观锁

●　悲观锁：数据修改前，假设一定会发生冲突，先尝试为它加锁。

●　乐观锁：假设数据修改不会造成冲突，在数据提交更新时才进行冲突检测。

在低并发场景下，使用乐观锁可以避免加锁开销；在高并发场景下，使用乐观锁会因为数据冲突频繁而导致执行效率变低，该场景下应使用悲观锁。

1.2.12　当前读与快照读

1．当前读

当前读读取的是最新版本数据，并且对读取的记录加锁，阻塞其他事务同时改动相同记录。比如如下形式的SQL属于当前读：

     select...lock in share mode (共享读锁)
     select...for update

2．快照读

快照读读取快照中的数据，不需要进行加锁。快照读通过MVCC机制实现，MVCC作用于读已提交和可重复读这两个隔离级别，这两个隔离级别下的普通select操作就是快照读。

●　读已提交隔离级别：事务每次select都生成一个最新的ReadView。

●　可重复读隔离级别：只在开启事务后第一次select时生成一次ReadView。

ReadView中存储当前活跃着的读写事务列表，也就是开启了但还未提交的事务。通过这个读写事务列表来判断记录的某个版本是否对当前事务可见。

1.2.13　幻读是怎么解决的

幻读是指一个事务在前后两次查询同一个范围时，后一次查询观察到了前一次查询结果集中没有的记录。假设用户表存在如表1-13所示的4条记录。

可重复读隔离级别下，事务A和事务B在不同时刻交替执行，在事务A的两次查询之间，事务B插入新的记录（id=5），观察事务A的两次执行结果，如表1-14所示。

可以看出，即使事务B中途插入一条记录，事务A前后两次查询结果依然相同，没有出现幻读现象。这是因为在可重复读隔离级别下，普通的查询是快照读，ReadView只在事务开启的第一次查询时生成，之后的查询都复用之前的ReadView，这就使得前后两次查询结果相同。因此，快照读不会出现幻读问题。快照读依赖MVCC机制，MVCC的原理在1.2.10节有详细说明。

当前读读取的是最新版本的数据，如果无法阻止其他事务在查询区间范围内插入新的数据，就会出现幻读现象。示例如表1-15所示。

为了解决幻读问题，引入间隙锁。间隙锁给某一个范围区间加锁，阻止其他事务将新的记录插入该范围区间内，因此也就不会出现幻读了。关于间隙锁的详细说明见1.2.11节。

总结：可重复读隔离级别下，快照读依靠MVCC机制解决幻读问题，当前读通过间隙锁解决幻读问题。

1.2.14　SQL索引优化

（1）最左前缀匹配原则：索引查询会一直向右匹配，直至遇到范围查询（>、<、between、like）才停止匹配，这一条的详细解释见1.2.17节。

（2）选择区分度高的列作为索引，区分度计算如下：

     count(distinct col)/count(*)

字段属性值不重复的比例越大，SQL查询需要扫描的记录数越少。比如，性别与姓名这两个索引字段，姓名的区分度远远高于性别，因此下面SQL1的查询性能远好于SQL2。

     SQL1: Select * from tableName where name = ‘张三’
     SQL2: Select * from tableName where sex = ‘M’

（3）索引列不要参与计算，保持列“干净”。

    SQL1  ： select * from   tableName    where    from_unixtime(createTime)  =
‘2023-01-01’
    SQL2：select * from tableName where createTime = unix_timestamp(’
2023-01-01’)

B+树中存储的是数据表中的字段值，SQL1中的所有元素值经过时间函数处理后才能进行比较，因此SQL1无法使用索引，改写成SQL2即可。

（4）利用索引覆盖进行查询，避免二次回表，减少select *的使用。索引覆盖的详细讲解见1.2.19节。

（5）建议使用自增主键（不推荐使用UUID作为主键），数据的新增会更新B+树索引，非自增主键在数据插入时可能使节点分裂更频繁。

（6）分库分表在创建唯一索引时（或者查询时）必须带上均衡字段。

分布式数据库数据分区一般采用Hash函数+Map映射的方式实现，如图1-84所示。首先根据数据的均衡字段计算出该数据的Hash桶位置，然后使用事先定义好的映射表将这个Hash桶中的数据映射到数据库节点中。

因此，构建唯一索引必须带上均衡字段才能保证全局唯一，同时SQL查询时带上均衡字段才能路由到具体节点进行数据查询，避免读放大。

思考问题：为什么不直接哈希到具体的数据库节点，而是在中间多了一层映射关系呢？

1.2.15　Dao层缓存先更新缓存还是先更新数据库

由CAP原则可知，使用缓存在提高可用性的同时会带来数据不一致的问题，无论先更新缓存还是先更新数据库，都无法保证数据库与缓存实时一致，都有可能产生脏数据。我们往往通过设置缓存过期时间来保证数据最终的一致性，但是先更新缓存再更新数据库更容易产生脏数据。

先更新数据库再更新缓存产生脏数据的场景如图1-85所示。

图1-85中涉及两个并发操作，一个是更新操作，另一个是查询操作。查询操作没有命中缓存，于是尝试读取数据库中的数据然后放入缓存；并发写请求更新数据库然后更新缓存，但在写请求完成缓存更新后，读请求才完成缓存回写，于是，缓存中的数据是旧的脏数据。出现这个问题的原因是读操作时间跨度大于写操作时间跨度，一般而言写比读耗时，所以出现该场景的概率较低。

先更新缓存再更新数据库产生脏数据的场景如图1-86所示。

图中涉及两个并发操作，读请求发现缓存数据不存在，然后查询数据库并回写到缓存中；在读请求回写缓存前来了一个写操作—更新缓存然后更新数据库，写请求更新缓存成功后，之前的那个读操作把旧的数据回写到缓存，覆盖了写请求的缓存，产生了脏数据。

1.2.16　MySQL高可用架构

MySQL通过主从复制实现高可用，复制过程如图1-87所示。

●　主库向从库同步自己的binlog日志。

●　从库的IO线程将binlog日志写入relay log。

●　从库的SQL线程读取relay log并进行数据回放。

MySQL数据复制方案演进路线：异步复制（Asynchronous Replication）到半同步复制（Semisynchronous Replication）和组复制（Group Replication）。

1．异步复制

主库执行完用户提交的事务后，写入binlog日志即可成功返回客户端，如图1-88所示。主库只通知Dump线程发送binlog，然后继续处理客户端的请求，它不会保证binlog一定传送到任何一个从库上。若主库宕机，则主从切换可能导致新主库上的数据不完整、不一致。

2．半同步复制

主库执行完用户提交的事务后，至少等待一个从库将接收到的binlog写入relay log，并返回给主库ACK后，主库才继续处理用户的提交，如图1-89所示。半同步复制增强了数据一致性，但也存在一定程度的数据延迟，同时也会影响主库写入性能。

3．组复制

复制组由多个server组成，提交事务在写binlog之前会被MySQL预设的钩子拦截，进入到MGR层，它将事务执行的相关信息打包，通过Paxos一致性协议进行全局排序后发送给各server节点，超过半数节点响应后，通知所有节点数据包同步成功。各节点独自进行认证（Certify）。若认证通过，则本地节点将数据变更写入自身的binlog中，然后在存储引擎层进行提交。异地节点把主库发送过来的binlog写入自身的relay log中，然后SQL线程读取relay log进行重放，并把重放的binlog写入自身的binlog，接下来在存储引擎内部进行提交。若认证不通过，则进行回滚。事务整体执行流程如图1-90所示。

1.2.17　为什么遇到范围查询会停止索引的最左匹配

索引底层数据结构是一棵B+树，在构建B+树时只能根据一个值来构建，而联合索引会依据最左字段来构建B+树，创建一个（a,b）的联合索引，它的索引树如图1-91所示。

在图1-91中，a值是有序的，即1，1，2，2，3，3；b的值是无序的，即1，2，1，4，1，2；在a值确定的情况下，b又是有序的，比如a=3时b等于1，2，是有序的。因此，可以得出如下结论：

●　select … where a=? and b = ?，查询时a,b字段都可以使用索引。

●　select … where b = ?，因为联合索引首先是按a排序的，只看b是无序的，所以这种查询无法使用索引。

●　select … where a>？and b =？，a字段可以使用索引，但b值不可以，因为a的值是一个范围，在这个范围内b是无序的。

1.2.18　索引下推

下面通过一个例子来说明什么是索引下推：用户表字段有id、name、age，联合索引为（name，age）。

     SELECT * from user where  name like '袁%' and age = 20

上述语句的执行过程如图1-92所示。

在(name,age)这棵索引树上忽略age字段，通过name进行查询得到两个结果，id分别为2,1，然后根据id值回表查询两次获得结果。

MySQL 5.6版本引入了索引下推，执行的过程如图1-93所示。

此时，不会忽略age字段，在索引内部会进一步判断age是否等于20，因此在(name,age)这棵索引树中只匹配到了一个记录（id = 1），此时只需回表查询一次。

1.2.19　索引覆盖

所谓索引覆盖是指SQL查询时只需在一棵索引树上就能获取所需的列数据，无须二次回表查询。

假设表有四个字段`id`、`col1`、`col2`、`col3`。

建立的联合索引如下：

     ALTER TABLE `tableNmae` ADD INDEX `idx_col1_col2`(`col1`，`col2`);
     SQL1:SELECT * FROM tableNmae WHERE col1 = xxx ORDER BY col2;
     SQL2:SELECT col1, col2 FROM tableNmae WHERE col1 = xxx ORDER BY col2;

SQL1由于select *需要查询所有的列，col3在联合索引上是缺失的，因此只能在联合索引中检索出符合条件的主键id，然后二次回表查询出所有数据列。

SQL2在联合索引上即可检索出匹配的数据并返回，无须二次回表查询，这种查询就是索引覆盖。

1.2.20　经典SQL语句考察：group by、join、having

面试官也会考察求职者的SQL功底，主要是针对group by、having、join等关键字的使用。下面给出一个经典题目：

有一张学生表Student(SId,Sname,Sage,Ssex)和一张成绩表SC(SId,CId,score)，查询平均成绩大于或等于60分的同学的学生编号、学生姓名和平均成绩。SQL语句如下：

     SELECT stu.`SId` 学生编号, stu.`Sname` 学生姓名, AVG(sc.`score`) 平均成绩
     FROM Student stu
     LEFT JOIN SC sc ON stu.`SID` = sc.`SID`
     GROUP BY stu.`SID`, stu.`Sname`
     HAVING AVG(sc.`score`) >= 60;

此外，面试官有时也会手写一些SQL，让求职者判断SQL使用了什么索引、加了什么锁。

1.2.21　避免数据库热点更新

用户在购买商品时一般需要事务完成相关操作，例如扣减商品库存（update）、扣减用户虚拟货币或现金（update）、发放商品（insert）等。上述操作对应的SQL语句如下：

     SQL1:update tableName set inventory = inventory -1 where id = ？
     SQL2:update tableName set currency = currency - ？ where userId = ?
     SQL3:insert into tableName (userId,awardId,..) values （?,?）

表1-16展示了三个数据库事务操作随着并发数的升高对应的执行时间。其中，扣减库存是所用用户同时更改该记录的字段，存在热点更新，SQL执行时间随着并发数的增加而急剧增加；扣减用户金币或者给用户发放商品是用户维度的操作，不存在热点更新问题，SQL执行时间稳定在3～4ms，与用户并发数无关。

测试结果在不同服务器上会有差异，但趋势是一致的，存在热点竞争的SQL执行时间与并发数强相关（落在同一个主键id上的并发更新数）。热点更新问题可以通过库存拆分与资源隔离的方式解决，3.1节会详细介绍。

1.2.22　线上环境如何修改表结构

MySQL在进行alter table等DDL操作时，有时会出现Waiting for table metadata lock的情况，后续对表的任何操作都无法进行。对千万数量级的表新增字段或者其他DDL操作，如何保证在不影响在线读写的同时完成表结构的修改？

方案一：创建临时表，数据完全同步后重命名表，如图1-94所示。

步骤01　创建新的临时表结构Table A_New。

步骤02　数据同步A->A_New。

步骤03　待A与A_New表完全实时同步后重命名表。

     Table A     ->  Table A_Old
     Table A_New ->  Table A

该操作会出现短暂的Table not found（查找不到表）。

该方案存在的问题是在重命名表的瞬间可能存在数据不一致的情况，具体如图1-95所示。

步骤01　数据写入Table A，更新id=1的记录字段c为1。

步骤02　binlog增量同步至Table A_New，由于延迟导致SQL未回放执行。

步骤03　重命名，线上写操作切换至Table A_New，更新id=1的字段c的值为2。

步骤04　执行步骤02的SQL，覆盖c=1，此时出现数据错误。

方案二：通过触发器完成两个表的同步。该方案对写入性能影响很大，仅在强一致性要求高的场景使用。