MySQL

MySQL 是最常见的关系型数据库，面向 OLTP（联机事务处理）——高并发的增删改查、每次只碰少量数据、要求强一致。这和数仓面向的 OLAP（批量扫描、聚合分析）是两类完全不同的负载。

索引的本质是用额外的存储空间换查询速度，就像书的目录。MySQL（InnoDB）用的是 B+ 树而不是二叉树或哈希，原因在于磁盘 IO：

• B+ 树矮而宽。每个节点存放很多键，三四层就能索引上千万行，一次查询只需三四次磁盘 IO。二叉树同样数据量要几十层，就是几十次 IO。
• 数据全部放在叶子节点，且叶子之间用链表相连。这让范围查询（BETWEEN、ORDER BY）可以顺着链表扫，而不必反复回到树根。哈希索引做不到这点，它只能等值查询。

由此引出聚簇索引与非聚簇索引的区别：InnoDB 的主键索引是聚簇索引，叶子节点直接存整行数据；其他索引是非聚簇的，叶子节点只存主键值，查到后还要拿主键再查一次聚簇索引，这一步叫回表。理解了回表，覆盖索引、索引下推这些优化手段就都顺理成章了。

事务的四个特性（ACID）里，原子性、持久性靠日志实现，一致性是目标，真正复杂且高频被问的是隔离性。

多个事务并发时会出现三类问题：脏读（读到别人未提交的数据）、不可重复读（同一事务内两次读同一行结果不同）、幻读（同一事务内两次范围查询行数不同）。四个隔离级别本质上就是在并发性能和这三类问题之间做取舍——级别越高问题越少，但加的锁越多、并发越差。

MySQL 默认是可重复读，这一点和大多数数据库默认的「读已提交」不同，是个常见考点。

线上遇到慢 SQL，排查顺序基本固定：

• 先用 EXPLAIN 看执行计划，确认有没有走索引、扫描了多少行；
• 没走索引就看是不是索引失效（对索引列做了函数运算、隐式类型转换、LIKE '%xx' 以通配符开头、违反最左前缀）；
• 走了索引仍然慢，就看是不是回表太多、或者需要的数据量本身就大。

这个思路和 Hive 调优在方法论上是一致的：都是先想办法少读数据。区别在于 MySQL 靠索引来少读，Hive 没有索引，靠分区裁剪和列式存储来少读。

MySQL 不同的存储引擎支持不同的锁机制：

• InnoDB 存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。
• MyISAM 和 MEMORY 存储引擎采用的是表级锁（table-level locking）
• BDB 存储引擎采用的是页面锁（page-level locking），但也支持表级锁

MySQL大致可归纳为以下3种锁：

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
• 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

ＭySQL的表锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）：

• 表共享读锁 （Table Read Lock）：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
• 表独占写锁 （Table Write Lock）：会阻塞其他用户对同一表的读和写操作；

InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。行锁定是对索引记录的锁定。 例如，从“SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; ” 防止任何其他事务插入，更新或删除t.c1值为10的行。

事务是由一组SQL语句组成的逻辑处理单元，事务具有4属性，通常称为事务的ACID属性。

• 原性性（Actomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
• 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以操持完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。
• 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
• 持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持可以支持更多的用户。但并发事务处理也会带来一些问题，主要包括以下几种情况：

• 更新丢失（Lost Update）：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题——最后的更新覆盖了其他事务所做的更新。例如，两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改保存其更改副本的编辑人员覆盖另一个编辑人员所做的修改。如果在一个编辑人员完成并提交事务之前，另一个编辑人员不能访问同一文件，则可避免此问题

• 脏读（Dirty Reads）：A事务读取B事务尚未提交的更改数据，并在这个数据的基础上进行操作，这时候如果事务B回滚，那么A事务读到的数据是不被承认的。这种现象被形象地叫做“脏读”。

• 不可重复读（Non-Repeatable Reads）：事务A首先读取了一条数据，然后执行逻辑的时候，事务B将这条数据改变了，然后事务A再次读取的时候，发现数据不匹配了，就是所谓的不可重复读了。也就是说，当前事务先进行了一次数据读取，然后再次读取到的数据是别的事务修改成功的数据，导致两次读取到的数据不匹配，也就照应了不可重复读的语义。

• 幻读（Phantom Reads）：事务A首先根据条件索引得到N条数据，然后事务B改变了这N条数据之外的M条或者增添了M条符合事务A搜索条件的数据，导致事务A再次搜索发现有N+M条数据了，就产生了幻读。\

也就是说，当前事务读第一次取到的数据比后来读取到数据条目少。

不可重复读和幻读比较：两者有些相似，但是前者针对的是update或delete，后者针对的insert。

在并发事务处理带来的问题中，“更新丢失”通常应该是完全避免的。但防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁来解决，因此，防止更新丢失应该是应用的责任。

“脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。数据库实现事务隔离的方式，基本可以分为以下两种：

• 一种是在读取数据前，对其加锁，阻止其他事务对数据进行修改。

• 另一种是不用加任何锁，通过一定机制生成一个数据请求时间点的一致性快照（Snapshot），并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度，好像是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制（ＭultiVersion Concurrency Control，简称MVCC或MCC），也经常称为多版本数据库。