《高性能 MySQL》笔记

部分章节内容更偏向于 DBA 的工作，在实际的开发工作中相关性较少，直接略过。

第一章 MySQL 架构与历史

MySQL 逻辑架构

MySQL 逻辑架构分为三层：

• 连接层 - 连接管理、认证管理
• 核心服务层 - 缓存、解析、优化、执行
• 存储引擎层 - 数据实际读写

并发控制

解决并发问题的最常见方式是加锁。

• 排它锁（exclusive lock） - 也叫写锁（write lock）。锁一次只能被一个线程所持有。

• 共享锁（shared lock） - 也叫读锁（read lock）。锁可被多个线程所持有。

加锁、解锁，检查锁是否已释放，都需要消耗资源，因此锁定的粒度越小，并发度越高。

MySQL 中支持多种锁粒度：

• 表级锁（table lock） - 锁定整张表，会阻塞其他用户对该表的读写操作。
• 行级锁（row lock） - 可以最大程度的支持并发处理。

事务

事务就是一组原子性的 SQL 查询。事务内的语句，要么全部执行成功，要么全部执行失败。

ACID

ACID 是数据库事务正确执行的四个基本要素。

• **原子性 (Atomicity)**：一个事务被视为不可分割的最小工作单元，一个事务的所有操作要么全部提交成功，要么全部失败回滚。
• **一致性 (Consistency)**：数据库总是从一个一致的状态到另一个一致的状态。事务没有提交，事务的修改就不会保存到数据库中。
• **隔离性 (isolation)**：通常来说，一个事务所作的操作在最终提交之前，对其他事务来说是不可见的。
• **持久性 (durability)**：一旦事务提交，则其所作的修改就会永久的保存到数据库中。

事务隔离级别

SQL 标准提出了四种“事务隔离级别”。事务隔离级别等级越高，越能保证数据的一致性和完整性，但是执行效率也越低。因此，设置数据库的事务隔离级别时需要做一下权衡。

事务隔离级别从低到高分别是：

• “读未提交（read uncommitted）” - 是指，事务中的修改，即使没有提交，对其它事务也是可见的。
  • 读未提交存在脏读问题。“脏读（dirty read）”是指当前事务可以读取其他事务未提交的数据。
• “读已提交（read committed）” ** - 是指，事务提交后，其他事务才能看到它的修改**。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。
  • 读已提交解决了脏读的问题。
  • 读已提交存在不可重复读问题。“不可重复读（non-repeatable read）”是指一个事务内多次读取同一数据，过程中，该数据被其他事务所修改，导致当前事务多次读取的数据可能不一致。
  • 读已提交是大多数数据库的默认事务隔离级别，如 Oracle。
• “可重复读（repeatable read）” - 是指：保证在同一个事务中多次读取同样数据的结果是一样的。
  • 可重复读解决了不可重复读问题。
  • 可重复读存在幻读问题。“幻读（phantom read）”是指一个事务内多次读取同一范围的数据过程中，其他事务在该数据范围新增了数据，导致当前事务未发现新增数据。
  • 可重复读是 InnoDB 存储引擎的默认事务隔离级别。
• 串行化（serializable ） - 是指，强制事务串行执行，对读取的每一行数据都加锁，一旦出现锁冲突，必须等前面的事务释放锁。
  • 串行化解决了幻读问题。由于强制事务串行执行，自然避免了所有的并发问题。
  • 串行化策略会在读取的每一行数据上都加锁，这可能导致大量的超时和锁竞争。这对于高并发应用基本上是不可接受的，所以一般不会采用这个级别。

事务隔离级别对并发一致性问题的解决情况：

死锁

死锁是指两个或多个事务竞争同一资源，从而导致恶性循环的现象。多个事务视图以不同顺序锁定资源时，就可能会产生死锁；多个事务同时锁定同一资源时，也会产生死锁。

InnoDB 目前处理死锁的方法是将持有最少行级锁的事务进行回滚。

事务日志

InnoDB 通过事务日志记录修改操作。事务日志的写入采用追加方式，因此是顺序 I/O，比随机 I/O 快很多。

事务日志持久化后，内存中被修改的数据由后台程序慢慢刷回磁盘，这称为预写日志（Write Ahead Logging，WAL）

如果数据修改以及记录到事务日志并持久化，此时系统崩溃，存储引擎可以在系统重启之后自动恢复数据。

MySQL 中的事务

MySQL 提供了两种事务存储引擎：InnoDB 和 NDB CLuster。

MySQL 默认采用自动提交模式（AUTOCOMMIT）。即如果不显式的声明一个事务，MySQL 会把每一个查询都当作一个事务来操作。

可以通过设置 AUTOCOMMIT 来启用或禁用自动提交模式。

可以通过执行 SET TRANSACTION ISOLATION LEVEL 来设置事务隔离级别。

InnoDB 采用两阶段锁定协议，在事务执行过程中，随时都可以执行锁定，锁只有在执行 COMMIT 或者 ROLLBACK 时才会释放，并且所有的锁都在一瞬间释放。

InnoDB 也支持通过特定语句显示加锁：

1
2
3
4
5

// 先在表上加上 IS 锁，然后对读取的记录加 S 锁
select ... lock in share mode;

// 当前读：先在表上加上 IX 锁，然后对读取的记录加 X 锁
select ... for update;

多版本并发控制

可以将 MVCC 视为行级锁的一个变种，它在很多情况下避免了加锁，因此开销更低。

MVCC 是通过保存数据在某个时刻的快照来实现的。也就是说，不管执行多久，每个事务看到的数据是一致的。根据事务开始时间不同， 每个事务对同一张表，同一时刻看到的数据可能是不一样的。

不同存储引擎实现 MVCC 的方式有所不同，典型的有乐观并发控制和悲观并发控制。

InnoDB 的 MVCC 是通过在每行记录后面保存两个隐藏列来实现。一个列保存了行的创建时间，一个是保存了过期时间。当然存储的不是实际的时间，而是系统版本号（system version number），每开始一个新事务，系统版本号都会自动递增。事务开始时刻的系统版本号作为事务的版本号，用来和查询到的每行记录的版本号作比较。

• Select - InnoDB 会根据这两个条件来查询：
  • 只查找版本号小于或者等于当前事务的数据行，这样可以保证事务读取到的数据要么是在事务开始前就存在的，要么是自己插入或者修改的。
  • 行的删除版本要么未定义，要么大于当前事务的版本号，这样可以保证读取到的数据在事务开始之前没有被删除。
• Insert - InnoDB 为新插入的每一行数据保存当前的系统版本号为行版本号。
• Delete - InnoDB 为删除的每一行保存当前的版本号为行删除标识。
• Update - InnoDB 为插入一条新纪录，保存当前系统版本号为行版本号，同时保存当前系统的版本号到原来的行为行删除标识。

MVCC 只在可重复读和读已提交两个隔离级别下工作。

MySQL 的存储引擎

Mysql 将每个数据库保存为数据目录下的一个子目录。建表时，MySQL 会在数据库子目录下创建一个和表同名的 .frm 文件保存表的定义。因为 MySQL 使用文件系统的目录和文件来保存数据库和表的定义，大小写敏感性和具体的平台密切相关：在 Windows 中，大小写不敏感；在 Linux 中，大小写敏感。

Mysql 常见存储引擎

• InnoDB - 默认事务引擎。
• MyISAM - Mysql 5.1 及之前的默认引擎。
• Archive
• Memory
• NDB

第二章 MySQL 基准测试（略）

第三章 服务器性能剖析（略）

第四章 Schema 与数据类型优化

数据类型

整数类型

整数类型有可选的 UNSIGNED 属性，标识不允许负值，大致可以使正数的上限提高一倍。

浮点数类型

FLOAT 和 DOUBLE 分别使用 4 个字节、8 个字节存储空间，它们支持使用标准的浮点运算进行近似计算，存在丢失精度的可能。

DECIMAL 类型用于存储精确的小数，支持精确计算，但是计算代价高。只有在需要对小数进行精确计算时，才应该使用 DECIMAL，例如财务数据。此外，当数据量较大时，可以考虑使用 BIGINT 代替 DECIMAL，将需要存储的货币单位乘以需要精确的倍数即可。

字符串类型

VARCHAR 类型用于存储可变长字符串。

CHAR 类型是定长字符串。

与 CHAR 和 VARCHAR 类似的类型还有 BINARY 和 VARBINARY，它们存储的是二进制字符串。

BLOB 和 TEXT

BLOB 和 TEXT 都用于存储很大的数据，分别采用二进制和字符串方式存储。

日期和时间类型

特殊类型

• ENUM - 枚举类型，用于存储单一值，可以选择一个预定义的集合。
• SET - 集合类型，用于存储多个值，可以选择多个预定义的集合。

Schema 设计简单规则

• 尽量避免过度设计，例如会导致极其复杂查询的 schema 设计，或者有很多列的表设计。
• 使用小而简单的合适数据类型，除非真实数据模型中有确切的需要，否则应该尽可能地避免使用 NULL 值。
• 尽量使用相同的数据类型存储相似或相关的值，尤其是要在关联条件中使用的列。
• 注意可变长字符串，其在临时表和排序时可能导致悲观的按最大长度分配内存。
• 尽量使用整型定义标识列。
• 避免使用 MySQL 已经遗弃的特性，例如制定浮点数的精度，或者整数的显示宽度。
• 小心使用 ENUM 和 SET，虽然它们用起来很方便，但是不要滥用，否则有时候会变成陷阱，最好避免使用 BIT。

范式意味着不存储冗余数据，但往往需要多关联查询，增加了查询的复杂度；反范式意味着存储冗余数据，但是减少了关联查询。在实际应用中，范式和反范式应当混合使用。

ALTER TABLE 如果操作的是大表，需要耗费大量时间。一般的操作是：用新结构创建一张空表，从旧表查出所有数据插入新表，然后删除旧表。

有两种替代方案：

• 在一台不提供服务的机器上执行 ALTER TABLE 操作，然后和提供服务的主库进行切换。
• 影子拷贝：创建一张新表，然后通过重命名和删表操作交换两张表。

第五章 创建高性能的索引

索引是存储引擎用于快速找到记录的一种数据结构。

索引优化应该是对查询性能优化最有效的手段了。

索引基础

索引可以包含一个或多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为 MySQL 只能高效地使用索引的最左前缀列。

B-Tree 索引

大多数 MySQL 引擎都支持 B-Tree 索引。存储引擎以不同的方式使用 B-Tree 索引，性能也各有不同，各有优劣。例如，MyISAM 使用前缀压缩技术使得索引更小，但 InnoDB 则按照原数据格式进行存储。再如 MyISAM 索引通过数据的物理位置引用被索引的行，而 InnoDB 则根据主键引用被索引的行。

B-Tree 通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。

B-Tree 索引从索引的根节点开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么是找到对应的值，要么该记录不存在。

叶子节点比较特别，它们的指针指向的是被索引的数据，而不是其他的节点页。在根节点和叶子节点之间可能有很多层节点页。树的深度和表的大小直接相关。

B-Tree 对索引列是顺序组织存储的，所以很适合查找范围数据。

假设有如下数据表：

1
2
3
4
5
6
7

CREATE TABLE People(
	last_name varchar(50) not null,
	first_name varchar(50) not null,
	dob date not null,
	gender enum('m','f')not null,
	key(last_name, first_name, dob)
);

对于表中的每一行数据，索引中包含了 last_name、 first_name 和 dob 列的值。

请注意，索引对多个值进行排序的依据是 CREATE TABLE 语句中定义索引时列的顺序。看一下最后两个条目，两个人的姓和名都-样，则根据他们的出生日期来排列顺序。

可以使用 B-Tree 索引的查询类型。B-Tree 索引适用于全键值、键值范围或键前缀查找。

其中键前缀查找只适用于根据最左前缀的查找生。前面所述的索引对如下类型的查询有效。

• 全值匹配 - 全值匹配指的是和索引中的所有列进行匹配，例如前面提到的索引可用于查找姓名为 Cuba Allen、出生于 1960-01-01 的人。
• 匹配最左前缀 - 前面提到的索引可用于查找所有姓为 Allen 的人，即只使用索引的第一列。
• 匹配列前缀 - 也可以只匹配某–列的值的开头部分。例如前面提到的索引可用于查找所有以 J 开头的姓的人。这里也只使用了索引的第一列。
• 匹配范围值 - 例如前面提到的索引可用于查找姓在 Allen 和 Barrymore 之间的人。这里也只使用了索引的第一列。
• 精确匹配某一列并范围匹配另外一列 - 前面提到的索引也可用于查找所有姓为 Allen, 并且名字是字母 K 开头的人。即第一列 last_ name 全匹配，第二列 first_name 范围匹配。
• 只访问索引的查询 - B-Tree 通常可以支持“只访问索引的查询”，即查询只需要访问索引，而无须访问数据行。也叫做覆盖索引。

因为索引树中的节点是有序的，所以除了按值查找外，索引还可以用于查询中的排序操作。

B-Tree 索引的限制：

• 如果不是按照索引的最左列开始查找，则无法使用索引。例如上面例子中的索引无法用于查找名字为 Bill 的人，也无法查找某个特定生日的人，因为这两列都不是最左数据列。类似地，也无法查找姓氏以某个字母结尾的人。
• 不能跳过索引中的列。也就是说，前面所述的索引无法用于查找姓为 Smith 并且在某个特定日期出生的人。如果不指定名 (first_name)，则 MySQL 只能使用索引的第一列。
• 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。例如有查询 WHERE last_name=' Smith' AND first_name LIKE 'J%' AND dob = '1976-12-23' ，这个查询只能使用索引的前两列，因为这里 LIKE 是一个范围条件（但是服务器可以把其余列用于其他目的）。如果范围查询列值的数量有限，那么可以通过使用多个等于条件来代替范围条件。

哈希索引

哈希索引 (hashindex) 基于哈希表实现，只有精确匹配索引所有列的查询才有效。

对于每一行数据，存储引擎都会对所有的索引列计算-一个哈希码 (hash code), 哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。

哈希索引的限制：

• 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过，访问内存中的行的速度很快，所以大部分情况下这一点对性能的影响并不明显。

• 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。

• 哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。例如，在数据列 (A,B) 上建立哈希索引，如果查询只有数据列 A, 则无法使用该索引。

• 哈希索引只支持等值比较查询，包括 =、IN()、 <=> （注意 <> 和 <=> 是不同的操作）。也不支持任何范围查询，例如 WHERE price > 100。

• 访问哈希索引的数据非常快，除非有很多哈希冲突（不同的索引列值却有相同的哈希值）。当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行。

• 如果哈希冲突很多的话，一些索引维护操作的代价也会很高。例如，如果在某个选择性很低（哈希冲突很多）的列上建立哈希索引，那么当从表中删除一行时，存储引擎需要遍历对应哈希值的链表中的每一行，找到并删除对应行的引用，冲突越多，代价越大。

空间数据索引 (R-Tree)

MyISAM 表支持空间索引，可以用作地理数据存储。和 B-Tree 索引不同，这类索引无须前缀查询。空间索引会从所有维度来索引数据。

查询时，可以有效地使用任意维度来组合查询。必须使用 MySQL 的 GIS 相关函数如 MBRCONTAINS() 等来维护数据。MySQL 的 GIS 支持并不完善，所以大部分人都不会使用这个特性。开源关系数据库系统中对 GIS 的解决方案做得比较好的是 PostgreSQL 的 PostGIS

全文索引

全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。

全文搜索和其他几类索引的匹配方式完全不一样。它有许多需要注意的细节，如停用词、词干和复数、布尔搜索等。

全文索引更类似于搜索引擎做的事情，而不是简单的 WHERE 条件匹配。

在相同的列上同时创建全文索引和基于值的 B-Tree 索引不会有冲突，全文索引适用于 MATCH AGAINST 操作，而不是普通的 WHERE 条件操作。

索引的优点

索引有以下优点：

1. 索引大大减少了服务器需要扫描的数据量。
2. 索引可以帮助服务器避免排序和临时表。
3. 索引可以将随机 I/O 变为顺序 I/O。

索引是最好的解决方案吗？

• 对于非常小的表，大部分情况下简单的全表扫描更高效。

• 对于中到大型的表，索引就非常有效。

• 但对于特大型的表，建立和使用索引的代价将随之增长。这种情况下，则需要一种技术可以直接区分出查询需要的一组数据，而不是一条记录一条记录地匹配。例如可以使用分区技术。

• 如果表的数量特别多，可以建立一个元数据信息表，用来查询需要用到的某些特性。例如执行那些需要聚合多个应用分布在多个表的数据的查询，则需要记录。哪个用户的信息存储在哪个表中”的元数据，这样在查询时就可以直接忽略那些不包含指定用户信息的表。对于大型系统，这是一个常用的技巧。

高性能的索引策略

正确地创建和使用索引是实现高性能查询的基础。

独立的列

独立的列是指索引列不能是表达式的一部分，也不能是函数的参数。

下面两个例子都无法使用索引：

1
2

SELECT actor_ id FROM sakila.actor WHERE actor_id + 1 = 5;
SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_ DAYS(date_col) <= 10;

前缀索引和索引选择性

有时候需要索引很长的字符列，这会让索引变得大且慢。一种策略是，可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。索引的选择性是指，不重复的索引值和总记录数的比值。索引的选择性越高则查询效率越高。

对于 BLOB、TEXT 或者很长的 VARCHAR 类型的列，必须使用前缀索引，因为 MySQL 不允许索引这些列的完整长度。

前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。通常来说，选择性能够接近 0.03，基本上就可用了。

计算前缀索引选择性的示例

1
2
3
4
5
6

SELECT COUNT(DISTINCT LEFT (city, 3)) / COUNT(*) AS sel3,
  COUNT(DISTINCT LEFT (city, 4)) / COUNT(*) AS sel4,
  COUNT(DISTINCT LEFT (city, 5)) / COUNT(*) AS sel5,
  COUNT(DISTINCT LEFT (city, 6)) / COUNT(*) AS se16,
  COUNT(DISTINCT LEFT (city, 7)) / COUNT(*) AS sel7,
FROM sakila.city demo;

多列索引

在多个列上建立独立的单列索引大部分情况下并不能提高 MySQL 的查询性能。

例如，表 film_actor 在字段 film_id 和 actor_id 上各有一个单列索引。但对于下面这个查询 WHERE 条件，这两个单列索引都不是好的选择：

1
2

SELECT film_id, actor_id FROM sakila.film_actor
WHERE actor_id = 10 or film_id = 1;

选择合适的索引列顺序

正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要。

如何选择索引的列顺序：

• 将选择性最高的列放到索引最前列。
• 可能需要根据那些运行频率最高的查询来调整索引列的顺序，让这种情况下索引的选择性最高。

1

SELECT * FROM payment WHERE staff.id = 2 AND customer._id = 584;

是应该创建一个 (staffid, customer id) 索引还是应该颠倒一下顺序？

可以跑一些查询来确定在这个表中值的分布情况，并确定哪个列的选择性更高。

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。聚簇表示数据行和相邻的键值紧凑地存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

具体的细节依赖于其实现方式，在 InnoDB 中，数据行实际上存放在索引的叶子页 (leaf page) 中。

聚簇索引的优点：

• 可以把相关数据保存在一起，访问数据时，可以减少磁盘 I/O。
• 数据访问更快。聚簇索引将索引和数据保存在同一个 B-Tree 中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

聚簇索引的缺点：

• 聚簇数据最大限度地提高了 I/O 密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没什么优势了。
• 插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到 InnoDB 表中速度最快的方式。但如果不是按照主键顺序加载数据，那么在加载完成后最好使用 OPTIMIZE TABLE 命令重新组织一下表。
• 更新聚簇索引列的代价很高，因为会强制 InnoDB 将每个被更新的行移动到新的位置。
• 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂 (page split) 的问题。当行的主键值要求必须将这一行插人到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间。
• 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
• 二级索引 （非聚簇索引）可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列。
• 二级索引访问需要两次索引查找，而不是一次。（回表）

InnoDB 和 MyISAM 的数据分布对比

MyISAM 存储引擎采用非聚簇索引存储数据，而 InnoDB 存储引擎采用聚簇索引存储数据。

来看下 MyISAM 和 InnoDB 是如何存储下面的表：

1
2
3
4
5
6

CREATE TABLE layout_test (
    col1 int NOT NULL,
    col2 int NOT NULL,
    PRIMARY KEY(col1),
    KEY(col2),
);

对于 MyISAM，其数据分布比较简单，按照数据插入的顺序存储在磁盘上。对于每一行数据，都是一个行号，从 0 开始递增。由于行是定长的，所以 MyISAM 可以从表的开头跳过所需的字节找到需要的行（有点类似于数组）。如下图：

MyISAM 使用主键索引查找数据时，在 B+Tree 的叶子节点除了存储索引键之外，还保存了每个键所处的行指针（可以理解为行号）。当找到某个索引键对应的行指针后，就能定位到它对应的数据。如下图：

对于 MyISAM 的二级索引，它的存储方式跟主键索引没有什么区别，如下图：

所以对于 MyISAM 来讲，主键索引和其它索引在存储结构上并没有什么区别。主键索引就是一个名为 PRIMARY 的惟一非空索引。

对于 InnoDB 来讲，主键索引是聚簇的，也就是主键索引就是表，所以不像 MyISAM 那样需要独立的行存储。 聚簇索引的每个叶子节点都包含了主键值、事务 ID、用于事务和 MVCC 的回滚指针以及所有剩余列（这个例子中是 col2）。对于 InnoDB 的主键索引，数据分布如下图：

InnoDB 的二级索引和聚簇索引区别比较大，它的二级索引的叶子节点存储的不是”行指针”，而是主键值。存储主键值带来的好处是，InnoDB 在移动行时无须更新二级索引的这个指针。如下图：

由于 InnoDB 是通过主键聚集数据，所以使用 InnoDB 时，一定要指定主键，如果没有定义主键，InnoDB 会选择一个惟一的非空索引代替，如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。

由于聚簇索引插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到 InnoDB 表中速度最快的方式，所以通常我们都使用一个递增 ID 作为主键。

最后，我们使用一个比较抽象的图，对比一下聚簇和非聚簇的数据分布：

覆盖索引

如果一个索引包含所有需要查询的字段的值，我们就称之为“ 覆盖索引”。覆盖索引能极大地提高性能。

• 索引条目通常远小于数据行大小，所以如果只需要读取索引，那 MySQL 就会极大地减少数据访问量。
• 因为索引是按照列值顺序存储的（至少在单个页内是如此），所以对于 I/O 密集型的范围查询会比随机从磁盘读取每一行数据的 I/O 要少得多。
• 一些存储引擎如 MyISAM 在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。
• InnoDB 的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。

覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以 MySQL 只能使用 B-Tree 索引做覆盖索引。

使用索引扫描来做排序

如果 EXPLAIN 出来的 type 列的值为 index, 则说明 MySQL 使用了索引扫描来做排序（不要和 Extra 列的 Using index 搞混淆了）。

MySQL 可以使用同一个索引既满足排序，又用于查找行。只有当索引的列顺序和 ORDER BY 子句的顺序完全一致，并且所有列的排序方向都一样时，MySQL 才能够使用索引来对结果做排序。

索引和锁

InnoDB 只有在访问行的时候才会对其加锁，而索引能够减少 InnoDB 访问的行数，从而减少锁的数量。

第六章 查询性能优化

为什么查询速度会慢

慢查询基础：优化数据访问

重构查询的方式

查询执行的基础

MySQL 查询优化器的局限性

查询优化器的提示（hint)

优化特定类型的查询

案例学习

第七章 MySQL 高级特性（略）

第八章 优化服务器设置（略）

第九章 操作系统和硬件优化（略）

第十章 复制

复杂概述

配置复制

复制的原理

复制拓扑

复制和容量规划

复制管理和维护

复制的问题和解决方案

复制有多快

MySQL 复制的高级特性

其他复制技术

第十一章 可扩展的 MySQL（略）

第十二章 高可用性（略）

第十三章 云端的 MySQL（略）

第十四章 应用层优化（略）

第十五章 备份与恢复（略）

第十六章 MySQL 用户工具（略）

参考资料

• 《高性能 MySQL》

MySQL 面试之事务和锁篇

MySQL 事务

扩展阅读：

• MySQL 官方文档之 InnoDB 锁和事务模型

【基础】什么是事务，什么是 ACID？

:::details 要点

事务指的是满足 ACID 特性的一组操作。事务内的 SQL 语句，要么全执行成功，要么全执行失败。可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚。通俗来说，事务就是要保证一组数据库操作，要么全部成功，要么全部失败。

ACID 是数据库事务正确执行的四个基本要素。

• 原子性（Atomicity）
  • 事务被视为不可分割的最小单元，事务中的所有操作要么全部提交成功，要么全部失败回滚。
  • 回滚可以用日志来实现，日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可。
• 一致性（Consistency）
  • 数据库在事务执行前后都保持一致性状态。
  • 在一致性状态下，所有事务对一个数据的读取结果都是相同的。
• 隔离性（Isolation）
  • 一个事务所做的修改在最终提交以前，对其它事务是不可见的。
• 持久性（Durability）
  • 一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。
  • 可以通过数据库备份和恢复来实现，在系统发生奔溃时，使用备份的数据库进行数据恢复。

一个支持事务（Transaction）中的数据库系统，必需要具有这四种特性，否则在事务过程（Transaction processing）当中无法保证数据的正确性。

:::

【中级】事务存在哪些并发一致性问题？

:::details 要点

事务中存在的并发一致性问题有：

• 丢失修改
• 脏读
• 不可重复读
• 幻读

“丢失修改”是指一个事务的更新操作被另外一个事务的更新操作替换。

如下图所示，T₁ 和 T₂ 两个事务对同一个数据进行修改，T₁ 先修改，T₂ 随后修改，T₂ 的修改覆盖了 T₁ 的修改。

“脏读（dirty read）”是指当前事务可以读取其他事务未提交的数据。

如下图所示，T₁ 修改一个数据，T₂ 随后读取这个数据。如果 T₁ 撤销了这次修改，那么 T₂ 读取的数据是脏数据。

“不可重复读（non-repeatable read）”是指一个事务内多次读取同一数据，过程中，该数据被其他事务所修改，导致当前事务多次读取的数据可能不一致。

如下图所示，T₂ 读取一个数据，T₁ 对该数据做了修改。如果 T₂ 再次读取这个数据，此时读取的结果和第一次读取的结果不同。

“幻读（phantom read）”是指一个事务内多次读取同一范围的数据，过程中，其他事务在该数据范围新增了数据，导致当前事务未发现新增数据。

事务 T₁ 读取某个范围内的记录时，事务 T₂ 在该范围内插入了新的记录，T₁ 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。

:::

【中级】长事务可能会导致哪些问题？

:::details 要点

长事务可能会导致以下问题：

• 锁竞争与资源阻塞
  • 长事务长时间持有锁，导致其他事务阻塞，增加系统等待时间，降低并发性能。
  • 业务线程因数据库请求等待而阻塞，可能引发连锁反应（如服务雪崩），造成严重线上事故。
• 死锁风险增加 - 多个长事务互相等待对方释放锁，容易形成死锁，导致系统无法正常执行。
• 主从延迟问题 - 主库执行时间长，从库同步及重放耗时增加，导致主从数据长时间不一致。
• 回滚效率低下 - 长事务执行中途失败时，回滚操作会浪费已执行的资源与时间，影响系统效率。

:::

【高级】事务的二阶段提交是什么？

:::details 要点

事务的二阶段提交确保 redo log（重做日志） 和 binlog（二进制日志） 的一致性，防止崩溃恢复时出现数据丢失或不一致。

两阶段流程

• Prepare 阶段（准备阶段） - InnoDB 写入 redo log，并标记为 prepare 状态（事务预提交，但未最终提交）。
• Commit 阶段（提交阶段） - MySQL Server 写入 binlog（记录 DML 操作）。binlog 写入成功后，InnoDB 将 redo log 状态改为 commit，完成事务提交。

:::

:::details 细节

binlog 和 redo log 的区别

为什么需要二阶段提交？

无论是单独先写 redo log 或先写 binlog，都可能导致数据不一致：

1. 先写 redo log，后写 binlog（宕机时 binlog 未写入） - redo log 恢复数据，但 binlog 缺失该事务 → 主从数据不一致。
2. 先写 binlog，后写 redo log（宕机时 redo log 未写入） - binlog 有记录，但 redo log 未提交 → 数据库实际数据丢失，与 binlog 不一致。

为了解决以上问题，所以需要事务二阶段提交（repare → commit），以确保写入两日志的原子性。

二阶段提交如何保证一致性？

MySQL 崩溃恢复时，检查两日志状态：

• redo log prepare，binlog 未写入 - 直接回滚（两日志均无有效记录）。
• redo log prepare，binlog 已写入 - 对比两日志数据：
  • 一致：提交事务（redo log commit）。
  • 不一致：回滚事务（保证数据一致）。

:::

【中级】有哪些事务隔离级别，分别解决了什么问题？

:::details 要点

为了解决以上提到的并发一致性问题，SQL 标准提出了四种“事务隔离级别”来应对这些问题。事务隔离级别等级越高，越能保证数据的一致性和完整性，但是执行效率也越低。因此，设置数据库的事务隔离级别时需要做一下权衡。

事务隔离级别从低到高分别是：

• “读未提交（read uncommitted）” - 是指，事务中的修改，即使没有提交，对其它事务也是可见的。
• “读已提交（read committed）” ** - 是指，事务提交后，其他事务才能看到它的修改**。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。
  • 读已提交解决了脏读的问题。
  • 读已提交是大多数数据库的默认事务隔离级别，如 Oracle。
• “可重复读（repeatable read）” - 是指：保证在同一个事务中多次读取同样数据的结果是一样的。
  • 可重复读解决了不可重复读问题。
  • 可重复读是 InnoDB 存储引擎的默认事务隔离级别。
• “串行化（serializable ）” - 是指，强制事务串行执行，对于同一行记录，加读写锁，一旦出现锁冲突，必须等前面的事务释放锁。
  • 串行化解决了幻读问题。由于强制事务串行执行，自然避免了所有的并发问题。
  • 串行化策略会在读取的每一行数据上都加锁，这可能导致大量的超时和锁竞争。这对于高并发应用基本上是不可接受的，所以一般不会采用这个级别。

事务隔离级别对并发一致性问题的解决情况：

:::

【中级】MySQL 的默认事务隔离级别是什么？为什么？

:::details 要点

事务隔离级别等级越高，越能保证数据的一致性和完整性，但是执行效率也越低。因此，设置数据库的事务隔离级别时需要做一下权衡。

MySQL 中的事务功能是在存储引擎层实现的，并非所有存储引擎都支持事务功能。比如 MyISAM 引擎就不支持事务，这也是 MyISAM 被 InnoDB 取代的重要原因之一。

大部分数据库的默认隔离级别是“读已提交”。然而，InnoDB 的默认隔离级别是“可重复读”。这是为了兼容早期 binlog 的 statement 格式问题。如果使用可重复读以下的隔离级别，使用了 statement 格式的 binlog 会产生主从数据不一致的问题。

此外，在 InnoDB 中，可重复读隔离级别虽然不能解决幻读，但是可以很大程度的避免幻读的发生。根据不同的查询方式，分别提出了避免幻读的方案：

• 针对快照读（普通 select 语句），通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
• 针对当前读（select ... for update 等语句），通过 Next-Key Lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select … for update 语句的时候，会加上 Next-Key Lock，如果有其他事务在 Next-Key Lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。

很大程度的避免幻读，不代表完全解决幻读问题，下面是两个示例：

• 对于快照读，MVCC 并不能完全避免幻读现象。因为当事务 A 更新了一条事务 B 插入的记录，那么事务 A 前后两次查询的记录条目就不一样了，所以就发生幻读。
• 对于当前读，如果事务开启后，并没有执行当前读，而是先快照读，然后这期间如果其他事务插入了一条记录，那么事务后续使用当前读进行查询的时候，就会发现两次查询的记录条目就不一样了，所以就发生幻读。

:::

【高级】MySQL 是如何实现事务的？

:::details 要点

MySQL 主要是通过 锁、Redo Log 、Undo Log、MVCC 来实现事务。

• MySQL 利用锁（行锁、间隙锁等等）机制，控制数据的并发修改，满足事务的隔离性。
• Redo Log（重做日志），它会记录事务对数据库的所有修改，当 MySQL 发生宕机或崩溃时，通过重放 redo log 就可以恢复数据，用来满足事务的持久性。
• Undo Log（回滚日志），它会记录事务的反向操作，简单地说就是保存数据的历史版本，用于事务的回滚，使得事务执行失败之后可以恢复之前的样子。实现原子性和隔离性
• MVCC（多版本并发控制），满足了非锁定读的需求，提高了并发度，实现了读已提交和可重复读两种隔离级别，实现了事务的隔离性。

事务实现了原子性、隔离性和持久性特性后，本身就达到了一致性的目的。

:::

【高级】各事务隔离级别是如何实现的？

:::details 要点

四种隔离级别具体是如何实现的呢？

以 InnoDB 的事务实现来说明：

• 对于“读未提交”隔离级别的事务来说，因为可以读到未提交事务修改的数据，所以直接读取最新的数据就好了；
• 对于“串行化”隔离级别的事务来说，通过加读写锁的方式来避免并行访问；
• 对于“读提交”和“可重复读”隔离级别的事务来说，它们都是通过 ReadView 来实现的，区别仅在于创建 ReadView 的时机不同。ReadView 可以理解为一个数据快照。
  • “读提交”隔离级别是在“每个语句执行前”都会重新生成一个 ReadView
  • “可重复读”隔离级别是在“启动事务时”生成一个 ReadView，然后整个事务期间都在用这个 ReadView。

:::

【中级】什么是 MVCC？

:::details 要点

MVCC 是 Multi Version Concurrency Control 的缩写，即“多版本并发控制”。MVCC 的设计目标是提高数据库的并发性，采用非阻塞的方式去处理读/写并发冲突，可以将其看成一种乐观锁。

不仅是 MySQL，包括 Oracle、PostgreSQL 等其他关系型数据库都实现了各自的 MVCC，实现机制没有统一标准。MVCC 是 InnoDB 存储引擎实现事务隔离级别的一种具体方式。其主要用于实现读已提交和可重复读这两种隔离级别。而未提交读隔离级别总是读取最新的数据行，要求很低，无需使用 MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

:::

【高级】MVCC 的实现原理是什么？

:::details 要点

MVCC 的实现原理，主要基于隐式字段、UndoLog、ReadView 来实现。

隐式字段

InnoDB 存储引擎中，数据表的每行记录，除了用户显示定义的字段以外，还有几个数据库隐式定义的字段：

• row_id - 隐藏的自增 ID。如果数据表没有指定主键，InnoDB 会自动基于 row_id 产生一个聚簇索引。
• trx_id - 最近修改的事务 ID。事务对某条聚簇索引记录进行改动时，就会把该事务的事务 id 记录在 trx_id 隐藏列里；
• roll_pointer - 回滚指针，指向这条记录的上一个版本。

UndoLog

MVCC 的多版本指的是多个版本的快照，快照存储在 UndoLog 中。该日志通过回滚指针 roll_pointer 把一个数据行的所有快照链接起来，构成一个版本链。

ReadView

ReadView 就是事务进行快照读时产生的读视图（快照）。

ReadView 有四个重要的字段：

• m_ids - 指的是在创建 ReadView 时，当前数据库中“活跃事务”的事务 ID 列表。注意：这是一个列表，“活跃事务”指的就是，启动了但还没提交的事务。
• min_trx_id - 指的是在创建 ReadView 时，当前数据库中“活跃事务”中事务 id 最小的事务，也就是 m_ids 的最小值。
• max_trx_id - 这个并不是 m_ids 的最大值，而是指创建 ReadView 时当前数据库中应该给下一个事务分配的 ID 值，也就是全局事务中最大的事务 ID 值 + 1；
• creator_trx_id - 指的是创建该 ReadView 的事务的事务 ID。

在创建 ReadView 后，我们可以将记录中的 trx_id 划分为三种情况：

• 已提交事务
• 已启动但未提交的事务
• 未启动的事务

ReadView 如何判断版本链中哪个版本可见？

一个事务去访问记录的时候，除了自己的更新记录总是可见之外，还有这几种情况：

• trx_id == creator_trx_id - 表示 trx_id 版本记录由 ReadView 所代表的当前事务产生，当然可以访问。
• trx_id < min_trx_id - 表示 trx_id 版本记录是在创建 ReadView 之前已提交的事务生成的，当前事务可以访问。
• trx_id >= max_trx_id - 表示 trx_id 版本记录是在创建 ReadView 之后才启动的事务生成的，当前事务不可以访问。
• min_trx_id <= trx_id < max_trx_id - 需要判断 trx_id 是否在 m_ids 列表中
  • 如果 trx_id 在 m_ids 列表中，表示生成 trx_id 版本记录的事务依然活跃（未提交事务），当前事务不可以访问。
  • 如果 trx_id 不在 m_ids 列表中，表示生成 trx_id 版本记录的事务已提交，当前事务可以访问。

这种通过“版本链”来控制并发事务访问同一个记录时的行为就叫 MVCC（多版本并发控制）。

:::

【高级】MVCC 实现了哪些隔离级别，如何实现的？

:::details 要点

对于“读已提交”和“可重复读”隔离级别的事务来说，它们都是通过 MVCC 的 ReadView 机制来实现的，区别仅在于创建 ReadView 的时机不同。ReadView 可以理解为一个数据快照。

MVCC 如何实现可重复读隔离级别

可重复读隔离级别只有在启动事务时才会创建 ReadView，然后整个事务期间都使用这个 ReadView。这样就保证了在事务期间读到的数据都是事务启动前的记录。

举个例子，假设有两个事务依次执行以下操作：

• 初始，表中 id = 1 的 value 列值为 100。
• 事务 2 读取数据，value 为 100；
• 事务 1 将 value 设为 200；
• 事务 2 读取数据，value 为 100；
• 事务 1 提交事务；
• 事务 2 读取数据，value 依旧为 100；

以上操作，如下图所示。T2 事务在事务过程中，是否可以看到 T1 事务的修改，可以根据 ReadView 中描述的规则去判断。

从图中不难看出：

• 对于 trx_id = 100 的版本记录，比对 T2 事务 ReadView ，trx_id < min_trx_id，因此在 T2 事务中的任意时刻都可见；
• 对于 trx_id = 101 的版本记录，比对 T2 事务 ReadView ，可以看出 min_trx_id <= trx_id < max_trx_id ，且 trx_id 在 m_ids 中，因此 T2 事务中不可见。

综上所述，在 T2 事务中，自始至终只能看到 trx_id = 100 的版本记录。

MVCC 如何实现读已提交隔离级别

读已提交隔离级别每次读取数据时都会创建一个 ReadView。这意味着，事务期间的多次读取同一条数据，前后读取的数据可能会出现不一致——因为，这期间可能有另外一个事务修改了该记录，并提交了事务。

举个例子，假设有两个事务依次执行以下操作：

• 初始，表中 id = 1 的 value 列值为 100。
• 事务 2 读取数据（创建 ReadView），value 为 0；
• 事务 1 将 value 设为 100；
• 事务 2 读取数据（创建 ReadView），value 为 0；
• 事务 1 提交事务；
• 事务 2 读取数据（创建 ReadView），value 为 100；

以上操作，如下图所示，T2 事务在事务过程中，是否可以看到其他事务的修改，可以根据 ReadView 中描述的规则去判断。

从图中不难看出：

• 对于 trx_id = 100 的版本记录，比对 T2 事务 ReadView ，trx_id < min_trx_id，因此在 T2 事务中的任意时刻都可见；
• 对于 trx_id = 101 的版本记录，比对 T2 事务 ReadView ，可以看出第二次查询时（T1 更新未提交），min_trx_id <= trx_id < max_trx_id ，且 trx_id 在 m_ids 中，因此 T2 事务中不可见；而第三次查询时（T1 更新已提交），trx_id < min_trx_id，因此在 T2 事务中可见；

综上所述，在 T2 事务中，当 T1 事务提交前，可读取到的是 trx_id = 100 的版本记录；当 T1 事务提交后，可读取到的是 trx_id = 101 的版本记录。

MVCC + Next-Key Lock 如何解决幻读

MySQL InnoDB 引擎的默认隔离级别虽然是“可重复读”，但是它很大程度上避免幻读现象（并不是完全解决了），解决的方案有两种：

• 针对快照读（普通 SELECT 语句），通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
• 针对当前读（SELECT ... FOR UPDATE 等语句），通过 Next-Key Lock（记录锁+间隙锁）方式解决了幻读，因为当执行 SELECT ... FOR UPDATE 语句的时候，会加上 Next-Key Lock，如果有其他事务在 Next-Key Lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好的避免了幻读问题。

:::

MySQL 锁

【中级】MySQL 中有哪些锁？

:::details 要点

为了解决并发一致性问题，MySQL 支持了很多种锁来实现不同程度的隔离性，以保证数据的安全性。

• 独享锁和共享锁
• 悲观锁和乐观锁
• 全局锁 - 锁定整个数据库。典型应用是全库逻辑备份。
• 表级锁
  • 表锁 - 表锁就是对数据表进行锁定，锁定粒度很大，同时发生锁冲突的概率也会较高，数据访问的并发度低。
  • 元数据锁（Metadata Lock, MDL） - 用于保护表结构变更，MDL 不需要显式使用，在访问一个表的时候会被自动加上。
    • 增删改查，加读锁
    • 结构变更，加写锁
  • 意向锁（Intention Lock） - 表明事务打算在表中的行上获取什么类型的锁。
    • 意向共享锁 (IS)
    • 意向排他锁 (IX)
  • 自增锁（Auto Increment Lock）
• 行级锁
  • 记录锁（Record Lock） - 锁定索引中的单条记录。
  • 间隙锁（Gap Lock） - 锁定索引记录之间的间隙。
  • 临键锁（Next-Key Lock） - 记录锁+间隙锁的组合。
  • 插入意向锁（Insert Intention Lock） - INSERT 操作设置的间隙锁。

:::

【中级】独享锁 vs. 共享锁？

:::details 要点

InnoDB 实现标准行级锁定，根据是否独享资源，可以把锁分为两类：

• 独享锁（Exclusive），简写为 X 锁，又称为“写锁”、“排它锁”。
  • 独享锁锁定的数据只允许进行锁定操作的事务使用，其他事务无法对已锁定的数据进行查询或修改。
  • 使用方式：SELECT ... FOR UPDATE;
• 共享锁（Shared），简写为 S 锁，又称为“读锁”。
  • 共享锁锁定的资源可以被其他用户读取，但不能修改。在进行 SELECT 的时候，会将对象进行共享锁锁定，当数据读取完毕之后，就会释放共享锁，这样就可以保证数据在读取时不被修改。
  • 使用方式：SELECT ... LOCK IN SHARE MODE;

为什么要引入读写锁机制？

实际上，读写锁是一种通用的锁机制，并非 MySQL 的专利。在很多软件领域，都存在读写锁机制。

因为读操作本身是线程安全的，而一般业务往往又是读多写少的情况。因此，如果对读操作进行互斥，是不必要的，并且会大大降低并发访问效率。正式为了应对这种问题，产生了读写锁机制。

读写锁的特点是：读读不互斥、读写互斥、写写互斥。简言之：只要存在写锁，其他事务就不能做任何操作。

注：InnoDB 下的行锁、间隙锁、next-key 锁统统属于独享锁。

:::

【中级】悲观锁 vs. 乐观锁？

:::details 要点

基于加锁方式分类，MySQL 可以分为悲观锁和乐观锁。

• 悲观锁 - 假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作
  • 在查询完数据的时候就把事务锁起来，直到提交事务（COMMIT）
  • 实现方式：使用数据库中的锁机制。
• 乐观锁 - 假设最好的情况——每次访问数据时，都假设数据不会被其他线程修改，不必加锁。只在更新的时候，判断一下在此期间是否有其他线程更新该数据。
  • 实现方式：更新数据时，先使用版本号机制或 CAS 算法检查数据是否被修改。

为什么要引入乐观锁？

乐观锁也是一种通用的锁机制，在很多软件领域，都存在乐观锁机制。

锁，意味着互斥，意味着阻塞。在高并发场景下，锁越多，阻塞越多，势必会拉低并发性能。那么，为了提高并发度，能不能尽量不加锁呢？

乐观锁，顾名思义，就是假设最好的情况——每次访问数据时，都假设数据不会被其他线程修改，不必加锁。虽然不加锁，但不意味着什么都不做，而是在更新的时候，判断一下在此期间是否有其他线程更新该数据。乐观锁最常见的实现方式，是使用版本号机制或 CAS 算法（Compare And Swap）去实现。

【示例】MySQL 乐观锁示例

假设，order 表中有一个字段 status，表示订单状态：status 为 1 代表订单未支付；status 为 2 代表订单已支付。现在，要将 id 为 1 的订单状态置为已支付，则操作如下：

1
2
3
4
5

select status, version from order where id=#{id}

update order
set status=2, version=version+1
where id=#{id} and version=#{version};

乐观锁的优点是：减少锁竞争，提高并发度。

乐观锁的缺点是：

• 存在 ABA 问题。所谓的 ABA 问题是指在并发编程中，如果一个变量初次读取的时候是 A 值，它的值被改成了 B，然后又其他线程把 B 值改成了 A，而另一个早期线程在对比值时会误以为此值没有发生改变，但其实已经发生变化了
• 如果乐观锁所检查的数据存在大量锁竞争，会由于不断循环重试，产生大量的 CPU 开销。

:::

【中级】全局锁 vs. 表级锁 vs. 行级锁？

:::details 要点

前文提到了，锁，意味着互斥，意味着阻塞。在高并发场景下，锁越多，阻塞越多，势必会拉低并发性能。在不得不加锁的情况下，显然，加锁的范围越小，锁竞争的发生频率就越小，系统的并发程度就越高。但是，加锁也需要消耗资源，锁的各种操作（包括获取锁、释放锁、以及检查锁状态）都会增加系统开销，锁粒度越小，系统的锁操作开销就越大。因此，在选择锁粒度时，也需要在锁开销和并发程度之间做一个权衡。

根据加锁的范围，MySQL 的锁大致可以分为：

• 全局锁 - “全局锁”会锁定整个数据库。
• 表级锁（table lock） - “表级锁”锁定整张表。用户对表进行写操作前，需要先获得写锁，这会阻塞其他用户对该表的所有读写操作。只有没有写锁时，其他用户才能获得读锁，读锁之间不会相互阻塞。表级锁有：
  • 表锁 - 表锁就是对数据表进行锁定，锁定粒度很大，同时发生锁冲突的概率也会较高，数据访问的并发度低。
  • 元数据锁（MDL） - MDL 不需要显式使用，在访问一个表的时候会被自动加上。
    • 增删改查，加读锁
    • 结构变更，加写锁
  • 意向锁（Intention Lock）
  • 自增锁（AUTO-INC）
• 行级锁（row lock） - “行级锁”锁定指定的行记录。这样其它线程还是可以对同一个表中的其它行记录进行操作。行级锁有：
  • 记录锁（Record Lock）
  • 间隙锁（Gap Lock）
  • 临键锁（Next-Key Lock）
  • 插入意向锁

以上各种加锁粒度，在不同存储引擎中的支持情况并不相同。如：InnoDB 支持全局锁、表级锁、行级锁；而 MyISAM 只支持全局锁、表级锁。

每个层级的锁数量是有限制的，因为锁会占用内存空间，锁空间的大小是有限的。当某个层级的锁数量超过了这个层级的阈值时，就会进行锁升级。锁升级就是用更大粒度的锁替代多个更小粒度的锁，比如 InnoDB 中行锁升级为表锁，这样做的好处是占用的锁空间降低了，但同时数据的并发度也下降了。

:::

【中级】死锁是如何产生的？

:::details 要点

“死锁”是指两个或多个事务竞争同一资源，并请求锁定对方占用的资源，从而导致恶性循环的现象。

产生死锁的场景：

• 多个事务以不同的顺序锁定资源，可能会产生死锁。
• 多个事务同时锁定同一个资源时，也会产生死锁。

:::

【高级】如何避免死锁？

:::details 要点

死锁的四个必要条件：互斥、占有且等待、不可强占用、循环等待。只要系统发生死锁，这些条件必然成立，但是只要破坏任意一个条件就死锁就不会成立。由此可知，要想避免死锁，就要从这几个必要条件上去着手：

• 更新表时，尽量使用主键更新，减少冲突；
• 避免长事务，尽量将长事务拆解，可以降低与其它事务发生冲突的概率；
• 设置合理的锁等待超时参数，我们可以通过 innodb_lock_wait_timeout 设置合理的等待超时阈值，特别是在一些高并发的业务中，我们可以尽量将该值设置得小一些，避免大量事务等待，占用系统资源，造成严重的性能开销。
• 在编程中尽量按照固定的顺序来处理数据库记录，假设有两个更新操作，分别更新两条相同的记录，但更新顺序不一样，有可能导致死锁；
• 在允许幻读和不可重复读的情况下，尽量使用读已提交事务隔离级别，可以避免 Gap Lock 导致的死锁问题；
• 还可以使用其它的方式来代替数据库实现幂等性校验。例如，使用 Redis 以及 ZooKeeper 来实现，运行效率比数据库更佳。

:::

【高级】如何解决死锁？

:::details 要点

当出现死锁以后，有两种策略：

• 设置事务等待锁的超时时间。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
• 开启死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

在 InnoDB 中，innodb_lock_wait_timeout 的默认值是 50s，意味着如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过 50s 才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。但是，若直接把这个时间设置成一个很小的值，比如 1s，也是不可取的。当出现死锁的时候，确实很快就可以解开，但如果不是死锁，而是简单的锁等待呢？所以，超时时间设置太短的话，会出现很多误伤。

所以，正常情况下我们还是要采用第二种策略，即：主动死锁检测，而且 innodb_deadlock_detect 的默认值本身就是 on。为了解决死锁问题，不同数据库实现了各自的死锁检测和超时机制。InnoDB 的处理策略是：将持有最少行级排它锁的事务进行回滚。主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的：每当事务被锁时，就要查看它所依赖的线程是否被其他事务锁住，如此循环，来判断是否出现了循环等待，也就是死锁。因此，死锁检测可能会耗费大量的 CPU。

:::

参考资料

• 《高性能 MySQL》
• 极客时间教程 - MySQL 实战 45 讲
• 图解 MySQL 介绍

MySQL 面试之索引篇

综合

【基础】什么是索引？为什么要使用索引？

:::details 要点

“索引”是数据库为了提高查找效率的一种数据结构。

日常生活中，我们可以通过检索目录，来快速定位书本中的内容。索引和数据表，就好比目录和书，想要高效查询数据表，索引至关重要。在数据量小且负载较低时，不恰当的索引对于性能的影响可能还不明显；但随着数据量逐渐增大，性能则会急剧下降。因此，设置合理的索引是数据库查询性能优化的最有效手段。

:::

【基础】索引的优点和缺点是什么？

:::details 要点

✔️️️️️️️ 索引的优点：

• 索引大大减少了服务器需要扫描的数据量，从而加快检索速度。
• 索引可以帮助服务器避免排序和临时表。
• 索引可以将随机 I/O 变为顺序 I/O。
• 支持行级锁的数据库，如 InnoDB 会在访问行的时候加锁。使用索引可以减少访问的行数，从而减少锁的竞争，提高并发。
• 唯一索引可以确保每一行数据的唯一性，通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能。

❌ 索引的缺点：

• 创建和维护索引要耗费时间，这会随着数据量的增加而增加。
• 索引需要占用额外的物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立组合索引那么需要的空间就会更大。
• 写操作（INSERT/UPDATE/DELETE）时很可能需要更新索引，导致数据库的写操作性能降低。

基于以上，可以归纳出索引的基本使用规则：

• 索引不是越多越好，不要为所有列都创建索引
• 要尽量避免冗余和重复索引
• 要考虑删除未使用的索引
• 尽量的扩展索引，不要新建索引
• 频繁作为 WHERE 过滤条件的列应该考虑添加索引

:::

【基础】何时适用索引？何时不适用索引？

:::details 要点

✔️️️️ 什么情况适用索引？

• 字段的数值有唯一性的限制，如用户名。
• 频繁作为 WHERE 条件或 JOIN 条件的字段，尤其在数据表大的情况下。
• 频繁用于 GROUP BY 或 ORDER BY 的字段。将该字段作为索引，查询时就无需再排序了，因为 B+ 树本身就是按序存储的。
• DISTINCT 字段需要创建索引。

❌ 什么情况不适用索引？

• 频繁写操作（ INSERT/UPDATE/DELETE ），也就意味着需要更新索引。
• 很少作为 WHERE 条件或 JOIN 条件的字段，也就意味着索引会经常无法命中，没有意义，还增加空间开销。
• 非常小的表，对于非常小的表，大部分情况下简单的全表扫描更高效。
• 特大型的表，建立和使用索引的代价将随之增长。可以考虑使用分区技术或 Nosql。

:::

【基础】索引有哪些分类？

:::details 要点

MySQL 索引可以从以下四个维度来分类：

• 按【数据结构】分类：B+tree 索引、Hash 索引、Full-text 索引
• 按【物理存储】分类：聚簇索引、二级索引（辅助索引）
• 按【字段特性】分类：主键索引、普通索引、前缀索引
• 按【字段个数】分类：单列索引、联合索引（复合索引、组合索引）

:::

【中级】创建索引有哪些基本原则？

:::details 要点

• 索引不是越多越好，不要为所有列都创建索引。要考虑到索引的维护代价、空间占用和查询时回表的代价。索引一定是按需创建的，并且要尽可能确保足够轻量。一旦创建了多字段的联合索引，我们要考虑尽可能利用索引本身完成数据查询，减少回表的成本。
• 要尽量避免冗余和重复索引。
• 要考虑删除未使用的索引。
• 尽量的扩展索引，不要新建索引。
• 频繁作为 WHERE 过滤条件的列应该考虑添加索引。

:::

【中级】哪些情况下，索引会失效？

:::details 要点

导致索引失效的情况有：

• 对索引使用左模糊匹配
• 对索引使用函数或表达式
• 对索引隐式类型转换
• 联合索引不遵循最左匹配原则
• 索引列判空 - 索引列与 NULL 或者 NOT NULL 进行判断的时候也会失效
• WHERE 子句中的 OR

:::

【基础】= 和 in 的顺序对于命中索引是否有影响？

:::details 要点

不需要考虑 =、IN 等的顺序，MySQL 会自动优化这些条件的顺序，以匹配尽可能多的索引列。

【示例】如有索引 (a, b, c, d)，查询条件 c > 3 and b = 2 and a = 1 and d < 4 与 a = 1 and c > 3 and b = 2 and d < 4 等顺序都是可以的，MySQL 会自动优化为 a = 1 and b = 2 and c > 3 and d < 4，依次命中 a、b、c、d。

:::

数据结构

【基础】索引有哪些常见数据结构？

:::details 要点

在 MySQL 中，索引是在存储引擎层而不是服务器层实现的，所以，并没有统一的索引标准。不同存储引擎的索引的数据结构也不相同。下面是 MySQL 常用存储引擎对一些主要索引数据结构的支持：

MySQL 索引的常见数据结构：

• 哈希索引
  • 因为索引数据结构紧凑，所以查询速度非常快。
  • 只支持等值比较查询 - 包括 =、IN()、<=>；不支持任何范围查询，如 WHERE price > 100。
  • 无法用于排序 - 因为哈希索引数据不是按照索引值顺序存储的。
  • 不支持部分索引匹配查找 - 因为哈希索引时使用索引列的全部内容来进行哈希计算的。如，在数据列 (A,B) 上建立哈希索引，如果查询只有数据列 A，无法使用该索引。
  • 不能用索引中的值来避免读取行 - 因为哈希索引只包含哈希值和行指针，不存储字段，所以不能使用索引中的值来避免读取行。不过，访问内存中的行的速度很快，所以大部分情况下这一点对性能影响不大。
  • 哈希索引有可能出现哈希冲突
    • 出现哈希冲突时，必须遍历链表中所有的行指针，逐行比较，直到找到符合条件的行。
    • 如果哈希冲突多的话，维护索引的代价会很高。
• B 树索引
  • 适用于全键值查找、键值范围查找和键前缀查找，其中键前缀查找只适用于最左前缀查找。
  • 所有的关键字（可以理解为数据）都存储在叶子节点，非叶子节点并不存储真正的数据，所有记录节点都是按键值大小顺序存放在同一层叶子节点上。
  • 所有的叶子节点由指针连接。

:::

【中级】为什么 InnoDB 采用 B+ 树索引？

:::details 要点

二叉搜索树的特点是：每个节点的左儿子小于父节点，父节点又小于右儿子。其查询时间复杂度是 $$O(log(N))$$。

当然为了维持 $$O(log(N))$$ 的查询复杂度，你就需要保持这棵树是平衡二叉树。为了做这个保证，更新的时间复杂度也是 $$O(log(N))$$。

随着数据库中数据的增加，索引本身大小随之增加，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘 I/O 消耗，相对于内存存取，I/O 存取的消耗要高几个数量级。可以想象一下一棵几百万节点的二叉树的深度是多少？如果将这么大深度的一颗二叉树放磁盘上，每读取一个节点，需要一次磁盘的 I/O 读取，整个查找的耗时显然是不能够接受的。那么如何减少查找过程中的 I/O 存取次数？

一种行之有效的解决方法是减少树的深度，将二叉树变为 N 叉树（多路搜索树），而 B+ 树就是一种多路搜索树。

B+ 树索引适用于全键值查找、键值范围查找和键前缀查找，其中键前缀查找只适用于最左前缀查找。

理解 B+Tree 时，只需要理解其最重要的两个特征即可：

• 首先，所有的关键字（可以理解为数据）都存储在叶子节点，非叶子节点并不存储真正的数据，所有记录节点都是按键值大小顺序存放在同一层叶子节点上。
• 其次，所有的叶子节点由指针连接。如下图为简化了的 B+Tree。

:::

:::details 细节

B+ 树 vs B 树

• B+ 树只在叶子节点存储数据，而 B 树的非叶子节点也要存储数据，所以 B+ 树的单个节点的数据量更小，在相同的磁盘 I/O 次数下，就能查询更多的节点。
• 另外，B+ 树叶子节点采用的是双链表连接，适合 MySQL 中常见的基于范围的顺序查找，而 B 树无法做到这一点。

B+ 树 vs 二叉树

• 对于有 N 个叶子节点的 B+ 树，其搜索复杂度为 O(logdN)，其中 d 表示节点允许的最大子节点个数为 d 个。
• 在实际的应用当中， d 值是大于 100 的，这样就保证了，即使数据达到千万级别时，B+ 树的高度依然维持在 13 层左右，也就是说一次数据查询操作只需要做 13 次的磁盘 I/O 操作就能查询到目标数据。
• 而二叉树的每个父节点的儿子节点个数只能是 2 个，意味着其搜索复杂度为 O(logN)，这已经比 B+ 树高出不少，因此二叉树检索到目标数据所经历的磁盘 I/O 次数要更多。

一言以蔽之，使用 B+ 树，而不是二叉树，是为了减少树的高度，也就是为了减少磁盘 I/O 次数。

B+ 树索引和 Hash 索引的差异

• B+ 树索引支持范围查询；Hash 索引不支持。
• B+ 树索引支持联合索引的最左匹配原则；Hash 索引不支持。
• B+ 树索引支持排序；Hash 索引不支持。
• B+ 树索引支持模糊查询；Hash 索引不支持。
• Hash 索引的等值查询比 B+ 树索引效率高。

综上，Hash 索引的应用场景很苛刻，不适用于绝大多数场景。

:::

【中级】B+ 树索引能存多少数据？

:::details 要点

在 InnoDB 存储引擎中，B+ 树默认数据页大小为 16KB。

所有 B+ 树都是从高度为 1 的树开始，然后根据数据的插入，慢慢增加树的高度。随着插入 B+ 树索引的记录变多，1 个页（16K）无法存放这么多数据，所以会发生 B+ 树的分裂，B+ 树的高度变为 2。

非叶子节点可存储的记录数

根节点和中间节点存放的是索引键对，由（索引键、指针）组成。

索引键就是排序的列，而指针是指向下一层的地址，在 MySQL 的 InnoDB 存储引擎中占用 6 个字节。若主键是 BIGINT 类型，占 8 个字节。也即是说，这样的一个键值对需要 8+6 = 14 字节。

1

非叶子节点可存储的记录数 = 页大小（16K） / 键值对大小（14） ≈ 1100

叶子节点可存储的记录数

为了方便计算，假设数据记录的平均大小为 1000 字节（实际一般小于这个值），则

1

叶子节点可存储的记录数 = 页大小（16K） / 记录平均大小（1000） ≈ 16

由此可知，树高度为 2 的 B+ 树索引，有一个根节点，约 1100 个叶子节点。因此，最多能存放的记录数为：

1

二层 B+ 树记录数 ≈ 1100 * 16 =  16,100

如何推算不同高度的 B+ 树可存储的记录数

综上所述，数据记录的平均大小为 1000 字节，主键为 BIGINT 的表，可以按如下推算其可存储的记录数：

• 高度为 2 的 B+树索引最多能存放约 1100 * 16 = 16,100 条记录（约 1.6 万），查询时只需 2 次 I/O。
• 高度为 3 的 B+树索引最多能存放约 1100 * 1100 * 16 = 19,360,000 条记录（约 2 千万），查询时只需 3 次 I/O。
• 高度为 4 的 B+树索引最多能存放约 1100 * 1100 * 1100 * 16 = 21,296,000,000 条记录（约 200 多亿），查询时只需 4 次 I/O。

优化 B+ 树索引的插入性能：

• 顺序插入（如自增 ID 或时间列）的维护代价小，性能较好。
• 无序插入（如用户昵称）会导致页分裂、旋转等开销较大的操作，影响性能。
• 主键设计应尽量使用顺序值（如自增 ID），以保证高并发场景下的性能。

:::

聚簇索引和非聚簇索引

【中级】聚簇索引和非聚簇索引有什么区别？

:::details 要点

根据叶子节点的内容，索引类型分为主键索引和非主键索引。

主键索引又被称为聚簇索引（clustered index），其叶子节点存的是整行数据。

• 聚簇表示数据行和相邻的键值紧凑地存储在一起，因为数据紧凑，所以访问快。
• 因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。
• InnoDB 的聚簇索引实际是在同一个结构中保存了 B 树的索引和数据行。

非主键索引又被称为二级索引（secondary index），其叶子节点存的是主键的值。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。

• 如果语句是 select * from T where ID=500，即聚簇索引查询方式，则只需要搜索主键索引树；
• 如果语句是 select * from T where k=5，即非聚簇索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。

也就是说，基于非聚簇索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

显然，主键长度越小，非聚簇索引的叶子节点就越小，非聚簇索引占用的空间也就越小。

:::

【基础】什么是覆盖索引？

:::details 要点

覆盖索引是指：二级索引上的信息满足查询所需的所有字段，不需要回表查询聚簇索引上的数据。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

:::

字段特性索引

【基础】AUTO_INCREMENT 列达到最大值时会发生什么？

:::details 要点

配置主键

在 MySQL 中，如果表定义的自增 ID 到达上限后，再申请下一个 ID，得到的值不变！因此会导致重复值的错误。

没有配置主键

如果 InnoDB 表中没有配置主键，InnoDB 会自动创建一个不可见的、长度为 6 个字节的 row_id 作为默认主键。

InnoDB 在全局维护一个 dict_sys.row_id 值。每次插入一行数据时，都会获取当前的 dict_sys.row_id 值，并将其加 1。row_id 的范围是 0 到 2^48 - 1。当 row_id 达到上限后，会从 0 开始重新循环。

如果插入的新数据的 row_id 在表中已存在，老数据会被新数据覆盖，且不会产生任何报错。可以通过 gdb 动态修改 dict_sys.row_id 来验证这一行为。

:::

【基础】普通键和唯一键，应该怎么选择？

:::details 要点

唯一索引的主要作用是保证数据的唯一性，而普通索引则更灵活。在业务代码保证不会写入重复数据的情况下，普通索引和唯一索引在查询性能上几乎没有差别。

普通索引 在更新操作中性能更优，尤其是在写多读少的场景下，能够利用 change buffer 减少磁盘 I/O。

唯一索引 适用于需要保证数据唯一性的场景，但在更新操作中性能较差，因为它无法使用 change buffer。

在业务允许的情况下，优先选择普通索引，因为它可以利用 change buffer 来提升更新性能。如果业务要求必须保证数据的唯一性，则必须使用唯一索引。

:::

:::details 细节

查询过程的性能差异：对于查询操作，普通索引和唯一索引的性能差异微乎其微。唯一索引在找到第一个满足条件的记录后会停止检索，而普通索引需要继续查找下一个记录，但由于数据页的读取方式，这种差异可以忽略不计。

更新过程的性能差异：更新操作中，普通索引可以利用 change buffer 来优化性能，而唯一索引则不能使用 change buffer。

• change buffer 是一种将更新操作缓存在内存中的机制，减少了对磁盘的随机读取，从而提升了更新操作的性能。
• 唯一索引在更新时需要检查唯一性约束，必须将数据页读入内存，增加了磁盘 I/O 的开销。

change buffer 的应用

• change buffer 的数据是持久化的，即使机器掉电重启，change buffer 中的数据也不会丢失，因为它会被写入磁盘。
• change buffer 适用于写多读少的场景，如账单类、日志类系统，因为这些场景下数据页在写入后不会立即被访问，change buffer 可以显著减少磁盘 I/O。
• 对于写后立即查询的场景，change buffer 的效果不明显，甚至可能增加维护成本。

change buffer vs. redo log

• redo log 主要减少随机写磁盘的 I/O 消耗，将随机写转换为顺序写。
• change buffer 主要减少随机读磁盘的 I/O 消耗，通过缓存更新操作来减少磁盘读取。

:::

【中级】为什么不推荐使用外键？

:::details 要点

逻辑外键是一种在应用程序层面上管理和维护数据完整性的方法，而不是通过数据库本身的外键约束。主要是利用应用程序代码来保证引用的完整性。

逻辑外键的优缺点：

优点：

• 灵活性高：应用程序层面控制，可以更灵活地实现复杂的业务逻辑。
• 性能优化：避免了数据库层面的约束检查，可以在某些情况下提高性能（详细看扩展知识）。
• 跨数据库兼容性：逻辑外键在不同类型的数据之间更容易迁移。

缺点：

• 代码复杂性增加：需要在应用程序代码中手动实现和维护引用完整性，增加了代码的复杂性和错误的可能性。
• 一致性风险：如果应用程序代码未正确实现引用完整性检查，可能导致数据不一致。
• 维护成本高：逻辑外键需要开发人员持续关注和维护，增加了维护成本。

物理外键的优缺点：

优点：

• 自动维护引用完整性：数据库会自动检查和维护外键约束，确保数据的一致性。
• 减少应用层复杂性：开发人员不需要手动管理引用完整性，减少了代码的复杂性和错误的可能性。
• 数据完整性保障：数据库层面的约束能够更有效地防止非法数据的插入或更新。

缺点：

• 性能开销：外键约束会增加插入、更新和删除操作的开销，特别是在处理大量数据时。
• 迁移和复制的复杂性：在进行数据库迁移或复制时，外键约束可能会增加复杂性，需要小心处理。
• 灵活性较低：物理外键在某些复杂业务逻辑下可能不够灵活，需要更多的手动控制。

:::

【中级】什么是前缀索引？

:::details 要点

“前缀索引”是指索引开始的部分字符。对于 BLOB/TEXT/VARCHAR 这种文本类型的列，必须使用前缀索引，因为数据库往往不允许索引这些列的完整长度。

前缀索引的优点是可以大大节约索引空间，从而提高索引效率。

前缀索引的缺点是会降低索引的区分度。此外，**order by 无法使用前缀索引，无法把前缀索引用作覆盖索引**。

:::

字段个数索引

【中级】什么是索引最左匹配原则？

:::details 要点

不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这里的最左，可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

如果是联合索引，那么 key 也由多个列组成，同时，索引只能用于查找 key 是否存在（相等），遇到范围查询 (>、<、BETWEEN、LIKE) 就不能进一步匹配了，后续退化为线性查找。因此，列的排列顺序决定了可命中索引的列数。

应该将选择性高的列或基数大的列优先排在多列索引最前列。但有时，也需要考虑 WHERE 子句中的排序、分组和范围条件等因素，这些因素也会对查询性能造成较大影响。“索引的选择性”是指不重复的索引值和记录总数的比值，最大值为 1，此时每个记录都有唯一的索引与其对应。索引的选择性越高，查询效率越高。如果存在多条命中前缀索引的情况，就需要依次扫描，直到最终找到正确记录。

:::

【中级】什么是索引下推？

:::details 要点

索引下推是一种减少回表查询、提高查询效率的技术。索引下推主要应用于联合索引。

它允许 MySQL 在使用索引查找数据时，将部分查询条件下推到存储引擎层进行过滤，从而减少需要从表中读取的数据行，减少 IO 操作。

索引下推注意点：

• MySQL 5.6 及以后版本支持索引下推，适用于 InnoDB 和 MyISAM 存储引擎。
• 如果查询中包含子查询，索引下推可能不会生效。
• 使用函数或表达式时，索引下推不会生效。
• 使用聚簇索引（主键）查询时，索引下推不会生效，因为它主要针对非聚簇索引。

扩展阅读：https://zhuanlan.zhihu.com/p/121084592

:::

参考资料

• 《高性能 MySQL》
• 极客时间教程 - MySQL 实战 45 讲
• 图解 MySQL 介绍

MySQL CRUD

::: info 概述

CRUD 由英文单词 Create, Read, Update, Delete 的首字母组成，即增删改查。

本文通过介绍基本的 MySQL CRUD 方法，向读者呈现如何访问 MySQL 数据。

扩展阅读：SQL 语法必知必会

:::

MySQL 数据类型

::: info 概述

数据类型在 MySQL 中扮演着至关重要的角色，它定义了表中每个字段可以存储的数据种类和格式。

MySQL 支持多种类型，大致可以分为三类：数值、时间和字符串类型。

:::

MySQL 简介

::: info 概述

MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL 是最好的 RDBMS 应用软件之一。

本文简单介绍了 MySQL 的功能、特性、发行版本、简史、概念，可以让读者在短时间内对于 MySQL 有一个初步的认识。

:::

MongoDB 面试

HBase 面试

HBase 简介

【基础】什么是 HBase？

:::details 要点

HBase 是一个构建在 HDFS（Hadoop 文件系统）之上的列式数据库。

HBase 是一种类似于 Google’s Big Table 的数据模型，它是 Hadoop 生态系统的一部分，它将数据存储在 HDFS 上，客户端可以通过 HBase 实现对 HDFS 上数据的随机访问。

HBase 的核心特性如下：

• 分布式
  • 伸缩性：支持通过增减机器进行水平扩展，以提升整体容量和性能
  • 高可用：支持 RegionServers 之间的自动故障转移
  • 自动分区：Region 分散在集群中，当行数增长的时候，Region 也会自动的分区再均衡
• 超大数据集：HBase 被设计用来读写超大规模的数据集（数十亿行至数百亿行的表）
• 支持结构化、半结构化和非结构化的数据：由于 HBase 基于 HDFS 构建，所以和 HDFS 一样，支持结构化、半结构化和非结构化的数据
• 非关系型数据库
  • 不支持标准 SQL 语法
  • 没有真正的索引
  • 不支持复杂的事务：只支持行级事务，即单行数据的读写都是原子性的

HBase 的其他特性

• 读写操作遵循强一致性
• 过滤器支持谓词下推
• 易于使用的 Java 客户端 API
• 它支持线性和模块化可扩展性。
• HBase 表支持 Hadoop MapReduce 作业的便捷基类
• 很容易使用 Java API 进行客户端访问
• 为实时查询提供块缓存 BlockCache 和布隆过滤器
• 它通过服务器端过滤器提供查询谓词下推

:::

【基础】为什么需要 HBase？

:::details 要点

在 HBase 诞生之前，Hadoop 可以通过 HDFS 来存储结构化、半结构甚至非结构化的数据，它是传统数据库的补充，是海量数据存储的最佳方法，它针对大文件的存储，批量访问和流式访问都做了优化，同时也通过多副本解决了容灾问题。

Hadoop 的缺陷在于：它只能执行批处理，并且只能以顺序方式访问数据。这意味着即使是最简单的工作，也必须搜索整个数据集，即：Hadoop 无法实现对数据的随机访问。实现数据的随机访问是传统的关系型数据库所擅长的，但它们却不能用于海量数据的存储。在这种情况下，必须有一种新的方案来同时解决海量数据存储和随机访问的问题，HBase 就是其中之一 (HBase，Cassandra，CouchDB，Dynamo 和 MongoDB 都能存储海量数据并支持随机访问）。

注：数据结构分类：

• 结构化数据：即以关系型数据库表形式管理的数据；
• 半结构化数据：非关系模型的，有基本固定结构模式的数据，例如日志文件、XML 文档、JSON 文档、Email 等；
• 非结构化数据：没有固定模式的数据，如 WORD、PDF、PPT、EXL，各种格式的图片、视频等。

:::

【基础】HBase 有哪些应用场景？

:::details 要点

根据上一节对于 HBase 特性的介绍，我们可以梳理出 HBase 适用、不适用的场景：

HBase 不适用场景：

• 需要索引
• 需要复杂的事务
• 数据量较小（比如：数据量不足几百万行）

HBase 适用场景：

• 能存储海量数据并支持随机访问（比如：数据量级达到十亿级至百亿级）
• 存储结构化、半结构化数据
• 硬件资源充足

一言以蔽之——HBase 适用的场景是：实时地随机访问超大数据集。

HBase 的典型应用场景

• 存储监控数据
• 存储用户/车辆 GPS 信息
• 存储用户行为数据
• 存储各种日志数据，如：访问日志、操作日志、推送日志等。
• 存储短信、邮件等消息类数据
• 存储网页数据

:::

【基础】HBase vs. RDBMS？

:::details 要点

HBase 和 RDBMS 的不同之处如下：

:::

【基础】HBase vs. HDFS？

:::details 要点

HBase 和 HDFS 的不同之处如下：

:::

【基础】行式数据库 vs. 列式数据库？

:::details 要点

行式数据库和列式数据库的不同之处如下：

列式数据库的优点：

• 支持数据压缩
• 支持快速数据检索
• 简化了管理和配置
• 有利于聚合查询（例如 COUNT、SUM、AVG、MIN 和 MAX）的高性能
• 分区效率很高，因为它提供了自动分片机制的功能，可以将较大的区域分配给较小的区域

列式数据库的缺点：

• JOIN 查询和来自多个表的数据未优化
• 必须为频繁的删除和更新创建拆分，因此降低了存储效率
• 由于非关系数据库的特性，分区和索引的设计非常困难

:::

HBase 存储

【基础】HBase 表有什么特性？

:::details 要点

Hbase 的表具有以下特点：

• 容量大：一个表可以有数十亿行，上百万列；
• 面向列：数据是按照列存储，每一列都单独存放，数据即索引，在查询时可以只访问指定列的数据，有效地降低了系统的 I/O 负担；
• 稀疏性：空 (null) 列并不占用存储空间，表可以设计的非常稀疏 ；
• 数据多版本：每个单元中的数据可以有多个版本，按照时间戳排序，新的数据在最上面；
• 存储类型：所有数据的底层存储格式都是字节数组 (byte[])。

:::

【基础】HBase 的逻辑存储模型是怎样的？

:::details 要点

HBase 是一个面向 列 的数据库管理系统，这里更为确切的而说，HBase 是一个面向 列族 的数据库管理系统。表 schema 仅定义列族，表具有多个列族，每个列族可以包含任意数量的列，列由多个单元格（cell）组成，单元格可以存储多个版本的数据，多个版本数据以时间戳进行区分。

HBase 数据模型和关系型数据库有所不同。其数据模型的关键术语如下：

• **Table**：Table 由 Row 和 Column 组成。
• **Row**：Row 是列族（Column Family）的集合。
• Row Key：Row Key 是用来检索记录的主键。
  • Row Key 是未解释的字节数组，所以理论上，任何数据都可以通过序列化表示成字符串或二进制，从而存为 HBase 的键值。
  • 表中的行，是按照 Row Key 的字典序进行排序。这里需要注意以下两点：
    • 因为字典序对 Int 排序的结果是 1,10,100,11,12,13,14,15,16,17,18,19,2,20,21,…,9,91,92,93,94,95,96,97,98,99。如果你使用整型的字符串作为行键，那么为了保持整型的自然序，行键必须用 0 作左填充。
    • 行的一次读写操作是原子性的 （不论一次读写多少列）。
  • 所有对表的访问都要通过 Row Key，有以下三种方式：
    • 通过指定的 Row Key 进行访问；
    • 通过 Row Key 的 range 进行访问，即访问指定范围内的行；
    • 进行全表扫描。
• **Column Family**：即列族。HBase 表中的每个列，都归属于某个列族。列族是表的 Schema 的一部分，所以列族需要在创建表时进行定义。
  • 一个表的列族必须作为表模式定义的一部分预先给出，但是新的列族成员可以随后按需加入。
  • 同一个列族的所有成员具有相同的前缀，例如 info:format，info:geo 都属于 info 这个列族。
• **Column Qualifier**：列限定符。可以理解为是具体的列名，例如 info:format，info:geo 都属于 info 这个列族，它们的列限定符分别是 format 和 geo。列族和列限定符之间始终以冒号分隔。需要注意的是列限定符不是表 Schema 的一部分，你可以在插入数据的过程中动态创建列。
• **Column**：HBase 中的列由列族和列限定符组成，由 :（冒号） 进行分隔，即一个完整的列名应该表述为 列族名 ：列限定符。
• **Cell**：Cell 是行，列族和列限定符的组合，并包含值和时间戳。HBase 中通过 row key 和 column 确定的为一个存储单元称为 Cell，你可以等价理解为关系型数据库中由指定行和指定列确定的一个单元格，但不同的是 HBase 中的一个单元格是由多个版本的数据组成的，每个版本的数据用时间戳进行区分。
  • Cell 由行和列的坐标交叉决定，是有版本的。默认情况下，版本号是自动分配的，为 HBase 插入 Cell 时的时间戳。Cell 的内容是未解释的字节数组。
• **Timestamp**：Cell 的版本通过时间戳来索引，时间戳的类型是 64 位整型，时间戳可以由 HBase 在数据写入时自动赋值，也可以由客户显式指定。每个 Cell 中，不同版本的数据按照时间戳倒序排列，即最新的数据排在最前面。

下图为 HBase 中一张表的：

• RowKey 为行的唯一标识，所有行按照 RowKey 的字典序进行排序；
• 该表具有两个列族，分别是 personal 和 office;
• 其中列族 personal 拥有 name、city、phone 三个列，列族 office 拥有 tel、addres 两个列。

图片引用自 : HBase 是列式存储数据库吗 https://www.iteblog.com/archives/2498.html

:::

【基础】HBase 的物理存储模型是怎样的？

:::details 要点

HBase Table 中的所有行按照 Row Key 的字典序排列。HBase Tables 通过行键的范围 (row key range) 被水平切分成多个 Region, 一个 Region 包含了在 start key 和 end key 之间的所有行。

每个表一开始只有一个 Region，随着数据不断增加，Region 会不断增大，当增大到一个阀值的时候，Region 就会等分为两个新的 Region。当 Table 中的行不断增多，就会有越来越多的 Region。

Region 是 HBase 中分布式存储和负载均衡的最小单元。这意味着不同的 Region 可以分布在不同的 Region Server 上。但一个 Region 是不会拆分到多个 Server 上的。

:::

HBase 架构

【中级】HBase 读数据流程是怎样的？

:::details 要点

以下是客户端首次读写 HBase 上数据的流程：

1. 客户端从 Zookeeper 获取 META 表所在的 Region Server；
2. 客户端访问 META 表所在的 Region Server，从 META 表中查询到访问行键所在的 Region Server，之后客户端将缓存这些信息以及 META 表的位置；
3. 客户端从行键所在的 Region Server 上获取数据。

如果再次读取，客户端将从缓存中获取行键所在的 Region Server。这样客户端就不需要再次查询 META 表，除非 Region 移动导致缓存失效，这样的话，则将会重新查询并更新缓存。

注：META 表是 HBase 中一张特殊的表，它保存了所有 Region 的位置信息，META 表自己的位置信息则存储在 ZooKeeper 上。

更为详细读取数据流程参考：

HBase 原理－数据读取流程解析

HBase 原理－迟到的‘数据读取流程部分细节

:::

【中级】HBase 写数据流程是怎样的？

:::details 要点

1. Client 向 Region Server 提交写请求；
2. Region Server 找到目标 Region；
3. Region 检查数据是否与 Schema 一致；
4. 如果客户端没有指定版本，则获取当前系统时间作为数据版本；
5. 将更新写入 WAL Log；
6. 将更新写入 Memstore；
7. 判断 Memstore 存储是否已满，如果存储已满则需要 flush 为 Store Hfile 文件。

更为详细写入流程可以参考：HBase － 数据写入流程解析

:::

【中级】HBase 有哪些核心组件？

:::details 要点

HBase 系统遵循 Master/Salve 架构，由三种不同类型的组件组成：

• Zookeeper
  • 保证任何时候，集群中只有一个 Master；
  • 存储所有 Region 的寻址入口；
  • 实时监控 Region Server 的状态，将 Region Server 的上线和下线信息实时通知给 Master；
  • 存储 HBase 的 Schema，包括有哪些 Table，每个 Table 有哪些 Column Family 等信息。
• Master
  • 为 Region Server 分配 Region ；
  • 负责 Region Server 的负载均衡 ；
  • 发现失效的 Region Server 并重新分配其上的 Region；
  • GFS 上的垃圾文件回收；
  • 处理 Schema 的更新请求。
• Region Server
  • Region Server 负责维护 Master 分配给它的 Region ，并处理发送到 Region 上的 IO 请求；
  • Region Server 负责切分在运行过程中变得过大的 Region。

HBase 使用 ZooKeeper 作为分布式协调服务来维护集群中的服务器状态。 Zookeeper 负责维护可用服务列表，并提供服务故障通知等服务：

• 每个 Region Server 都会在 ZooKeeper 上创建一个临时节点，Master 通过 Zookeeper 的 Watcher 机制对节点进行监控，从而可以发现新加入的 Region Server 或故障退出的 Region Server；
• 所有 Masters 会竞争性地在 Zookeeper 上创建同一个临时节点，由于 Zookeeper 只能有一个同名节点，所以必然只有一个 Master 能够创建成功，此时该 Master 就是主 Master，主 Master 会定期向 Zookeeper 发送心跳。备用 Masters 则通过 Watcher 机制对主 HMaster 所在节点进行监听；
• 如果主 Master 未能定时发送心跳，则其持有的 Zookeeper 会话会过期，相应的临时节点也会被删除，这会触发定义在该节点上的 Watcher 事件，使得备用的 Master Servers 得到通知。所有备用的 Master Servers 在接到通知后，会再次去竞争性地创建临时节点，完成主 Master 的选举。

:::

HBase 高可用

Hive 面试

Hive 简介

【基础】什么是 Hive？

:::details 要点

Apache Hive 是一种分布式、容错数据仓库，支持大规模分析。Hive Metastore （HMS） 提供了一个元数据的中央存储库，可以轻松分析以做出明智的数据驱动决策，因此它是许多数据湖架构的关键组件。Hive 构建在 Apache Hadoop 之上，并通过 hdfs 支持在 S3、adls、gs 等上进行存储。Hive 允许用户使用 SQL 读取、写入和管理 PB 级数据。

Hive 可以将结构化的数据文件映射成表，并提供类 SQL 查询功能。用于查询的 SQL 语句会被转化为 MapReduce 作业，然后提交到 Hadoop 上运行。

特点：

1. 简单、容易上手（提供了类似 sql 的查询语言 hql），使得精通 sql 但是不了解 Java 编程的人也能很好地进行大数据分析；
2. 灵活性高，可以自定义用户函数 (UDF) 和存储格式；
3. 为超大的数据集设计的计算和存储能力，集群扩展容易；
4. 统一的元数据管理，可与 presto／impala／sparksql 等共享数据；
5. 执行延迟高，不适合做数据的实时处理，但适合做海量数据的离线处理。

:::

【基础】什么是 HMS？

:::details 要点

Hive Metastore （HMS） 是关系数据库中 Hive 表和分区元数据的中央存储库，它使用元存储服务 API 为客户端（包括 Hive、Impala 和 Spark）提供对此信息的访问。它已成为利用各种开源软件（如 Apache Spark 和 Presto）的数据湖的构建块。事实上，整个工具生态系统，无论是开源的还是其他的，都是围绕 Hive Metastore 构建的，下图说明了其中一些。

:::

Hive 存储

【基础】Hive 支持哪些数据类型？

:::details 要点

Hive 表中的列支持以下基本数据类型：

TIMESTAMP 和 TIMESTAMP WITH LOCAL TIME ZONE 的区别如下：

• TIMESTAMP WITH LOCAL TIME ZONE：用户提交时间给数据库时，会被转换成数据库所在的时区来保存。查询时则按照查询客户端的不同，转换为查询客户端所在时区的时间。
• TIMESTAMP ：提交什么时间就保存什么时间，查询时也不做任何转换。

此外，Hive 还支持以下复杂类型：

:::

【基础】Hive 支持哪些存储格式？

:::details 要点

Hive 会在 HDFS 为每个数据库上创建一个目录，数据库中的表是该目录的子目录，表中的数据会以文件的形式存储在对应的表目录下。Hive 支持以下几种文件存储格式：

以上压缩格式中 ORC 和 Parquet 的综合性能突出，使用较为广泛，推荐使用这两种格式。

通常在创建表的时候使用 STORED AS 参数指定：

1
2
3
4
5
6

CREATE TABLE page_view(viewTime INT, userid BIGINT)
 ROW FORMAT DELIMITED
   FIELDS TERMINATED BY '\001'
   COLLECTION ITEMS TERMINATED BY '\002'
   MAP KEYS TERMINATED BY '\003'
 STORED AS SEQUENCEFILE;

各个存储文件类型指定方式如下：

• STORED AS TEXTFILE
• STORED AS SEQUENCEFILE
• STORED AS ORC
• STORED AS PARQUET
• STORED AS AVRO
• STORED AS RCFILE

:::

【基础】Hive 中的内部表和外部表有什么区别？

:::details 要点

内部表又叫做管理表 (Managed/Internal Table)，创建表时不做任何指定，默认创建的就是内部表。想要创建外部表 (External Table)，则需要使用 External 进行修饰。 内部表和外部表主要区别如下：

:::

【基础】什么是分区表？

:::details 要点

Hive 中的表对应为 HDFS 上的指定目录，在查询数据时候，默认会对全表进行扫描，这样时间和性能的消耗都非常大。

分区为 HDFS 上表目录的子目录，数据按照分区存储在子目录中。如果查询的 where 子句中包含分区条件，则直接从该分区去查找，而不是扫描整个表目录，合理的分区设计可以极大提高查询速度和性能。

分区表并非 Hive 独有的概念，实际上这个概念非常常见。通常，在管理大规模数据集的时候都需要进行分区，比如将日志文件按天进行分区，从而保证数据细粒度的划分，使得查询性能得到提升。比如，在我们常用的 Oracle 数据库中，当表中的数据量不断增大，查询数据的速度就会下降，这时也可以对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据存放到多个表空间（物理文件上），这样查询数据时，就不必要每次都扫描整张表，从而提升查询性能。

在 Hive 中可以使用 PARTITIONED BY 子句创建分区表。表可以包含一个或多个分区列，程序会为分区列中的每个不同值组合创建单独的数据目录。下面的我们创建一张雇员表作为测试：

1
2
3
4
5
6
7
8
9
10
11
12

 CREATE EXTERNAL TABLE emp_partition(
    empno INT,
    ename STRING,
    job STRING,
    mgr INT,
    hiredate TIMESTAMP,
    sal DECIMAL(7,2),
    comm DECIMAL(7,2)
)
    PARTITIONED BY (deptno INT)   -- 按照部门编号进行分区
    ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
    LOCATION '/hive/emp_partition';

加载数据到分区表时候必须要指定数据所处的分区：

1
2
3
4

# 加载部门编号为 20 的数据到表中
LOAD DATA LOCAL INPATH "/usr/file/emp20.txt" OVERWRITE INTO TABLE emp_partition PARTITION (deptno=20)
# 加载部门编号为 30 的数据到表中
LOAD DATA LOCAL INPATH "/usr/file/emp30.txt" OVERWRITE INTO TABLE emp_partition PARTITION (deptno=30)

这时候我们直接查看表目录，可以看到表目录下存在两个子目录，分别是 deptno=20 和 deptno=30, 这就是分区目录，分区目录下才是我们加载的数据文件。

1

# hadoop fs -ls  hdfs://hadoop001:8020/hive/emp_partition/

这时候当你的查询语句的 where 包含 deptno=20，则就去对应的分区目录下进行查找，而不用扫描全表。

:::

【基础】什么是分桶表？

:::details 要点

分区提供了一个隔离数据和优化查询的可行方案，但是并非所有的数据集都可以形成合理的分区，分区的数量也不是越多越好，过多的分区条件可能会导致很多分区上没有数据。同时 Hive 会限制动态分区可以创建的最大分区数，用来避免过多分区文件对文件系统产生负担。鉴于以上原因，Hive 还提供了一种更加细粒度的数据拆分方案：分桶表 (bucket Table)。

分桶表会将指定列的值进行哈希散列，并对 bucket（桶数量）取余，然后存储到对应的 bucket（桶）中。

单从概念上理解分桶表可能会比较晦涩，其实和分区一样，分桶这个概念同样不是 Hive 独有的，对于 Java 开发人员而言，这可能是一个每天都会用到的概念，因为 Hive 中的分桶概念和 Java 数据结构中的 HashMap 的分桶概念是一致的。

当调用 HashMap 的 put() 方法存储数据时，程序会先对 key 值调用 hashCode() 方法计算出 hashcode，然后对数组长度取模计算出 index，最后将数据存储在数组 index 位置的链表上，链表达到一定阈值后会转换为红黑树 (JDK1.8+)。下图为 HashMap 的数据结构图：

图片引用自：HashMap vs. Hashtable

在 Hive 中，我们可以通过 CLUSTERED BY 指定分桶列，并通过 SORTED BY 指定桶中数据的排序参考列。下面为分桶表建表语句示例：

1
2
3
4
5
6
7
8
9
10
11
12

CREATE EXTERNAL TABLE emp_bucket(
  empno INT,
  ename STRING,
  job STRING,
  mgr INT,
  hiredate TIMESTAMP,
  sal DECIMAL(7,2),
  comm DECIMAL(7,2),
  deptno INT)
  CLUSTERED BY(empno) SORTED BY(empno ASC) INTO 4 BUCKETS  --按照员工编号散列到四个 bucket 中
  ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
  LOCATION '/hive/emp_bucket';

这里直接使用 Load 语句向分桶表加载数据，数据时可以加载成功的，但是数据并不会分桶。

这是由于分桶的实质是对指定字段做了 hash 散列然后存放到对应文件中，这意味着向分桶表中插入数据是必然要通过 MapReduce，且 Reducer 的数量必须等于分桶的数量。由于以上原因，分桶表的数据通常只能使用 CTAS(CREATE TABLE AS SELECT) 方式插入，因为 CTAS 操作会触发 MapReduce。加载数据步骤如下：

（1）设置强制分桶

1

set hive.enforce.bucketing = true; --Hive 2.x 不需要这一步

在 Hive 0.x and 1.x 版本，必须使用设置 hive.enforce.bucketing = true，表示强制分桶，允许程序根据表结构自动选择正确数量的 Reducer 和 cluster by column 来进行分桶。

（2）CTAS 导入数据

1

INSERT INTO TABLE emp_bucket SELECT *  FROM emp;  --这里的 emp 表就是一张普通的雇员表

可以从执行日志看到 CTAS 触发 MapReduce 操作，且 Reducer 数量和建表时候指定 bucket 数量一致：

查看分桶文件

bucket（桶） 本质上就是表目录下的具体文件：

:::

【基础】分区和分桶可以一起使用吗？

:::details 要点

分区表和分桶表的本质都是将数据按照不同粒度进行拆分，从而使得在查询时候不必扫描全表，只需要扫描对应的分区或分桶，从而提升查询效率。两者可以结合起来使用，从而保证表数据在不同粒度上都能得到合理的拆分。下面是 Hive 官方给出的示例：

1
2
3
4
5
6
7
8
9
10
11
12
13

CREATE TABLE page_view_bucketed(
	viewTime INT, 
    userid BIGINT,
    page_url STRING, 
    referrer_url STRING,
    ip STRING )
 PARTITIONED BY(dt STRING)
 CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS
 ROW FORMAT DELIMITED
   FIELDS TERMINATED BY '\001'
   COLLECTION ITEMS TERMINATED BY '\002'
   MAP KEYS TERMINATED BY '\003'
 STORED AS SEQUENCEFILE;

此时导入数据时需要指定分区：

1
2
3

INSERT OVERWRITE page_view_bucketed
PARTITION (dt='2009-02-25')
SELECT * FROM page_view WHERE dt='2009-02-25';

:::

Hive 索引

【中级】Hive 的索引是如何工作的？

:::details 要点

Hive 在 0.7.0 引入了索引的功能，索引的设计目标是提高表某些列的查询速度。如果没有索引，带有谓词的查询（如’WHERE table1.column = 10’）会加载整个表或分区并处理所有行。但是如果 column 存在索引，则只需要加载和处理文件的一部分。

在指定列上建立索引，会产生一张索引表（表结构如下），里面的字段包括：索引列的值、该值对应的 HDFS 文件路径、该值在文件中的偏移量。在查询涉及到索引字段时，首先到索引表查找索引列值对应的 HDFS 文件路径及偏移量，这样就避免了全表扫描。

1
2
3
4
5
6
7

+--------------+----------------+----------+--+
|   col_name   |   data_type    | comment     |
+--------------+----------------+----------+--+
| empno        | int            |  建立索引的列  |   
| _bucketname  | string         |  HDFS 文件路径  |
| _offsets     | array<bigint>  |  偏移量       |
+--------------+----------------+----------+--+

创建索引：

1
2
3
4
5
6
7
8
9
10
11
12
13

CREATE INDEX index_name     --索引名称
  ON TABLE base_table_name (col_name, ...)  --建立索引的列
  AS index_type    --索引类型
  [WITH DEFERRED REBUILD]    --重建索引
  [IDXPROPERTIES (property_name=property_value, ...)]  --索引额外属性
  [IN TABLE index_table_name]    --索引表的名字
  [
     [ ROW FORMAT ...] STORED AS ...  
     | STORED BY ...
  ]   --索引表行分隔符 、 存储格式
  [LOCATION hdfs_path]  --索引表存储位置
  [TBLPROPERTIES (...)]   --索引表表属性
  [COMMENT "index comment"];  --索引注释

查看索引：

1
2

--显示表上所有列的索引
SHOW FORMATTED INDEX ON table_name;

删除索引：

删除索引会删除对应的索引表。

1

DROP INDEX [IF EXISTS] index_name ON table_name;

如果存在索引的表被删除了，其对应的索引和索引表都会被删除。如果被索引表的某个分区被删除了，那么分区对应的分区索引也会被删除。

重建索引：

1

ALTER INDEX index_name ON table_name [PARTITION partition_spec] REBUILD;

重建索引。如果指定了 PARTITION，则仅重建该分区的索引。

:::

【中级】Hive 索引有什么缺陷？

:::details 要点

索引表最主要的一个缺陷在于：索引表无法自动 rebuild，这也就意味着如果表中有数据新增或删除，则必须手动 rebuild，重新执行 MapReduce 作业，生成索引表数据。

同时按照 官方文档 的说明，Hive 会从 3.0 开始移除索引功能，主要基于以下两个原因：

• 具有自动重写的物化视图 (Materialized View) 可以产生与索引相似的效果（Hive 2.3.0 增加了对物化视图的支持，在 3.0 之后正式引入）。
• 使用列式存储文件格式（Parquet，ORC）进行存储时，这些格式支持选择性扫描，可以跳过不需要的文件或块。

ORC 内置的索引功能可以参阅这篇文章：Hive 性能优化之 ORC 索引–Row Group Index vs Bloom Filter Index

:::

Hive 架构

【高级】Hive SQL 如何执行的？

:::details 要点

Hive 在执行一条 HQL 的时候，会经过以下步骤：

1. 语法解析：Antlr 定义 SQL 的语法规则，完成 SQL 词法，语法解析，将 SQL 转化为抽象 语法树 AST Tree；
2. 语义解析：遍历 AST Tree，抽象出查询的基本组成单元 QueryBlock；
3. 生成逻辑执行计划：遍历 QueryBlock，翻译为执行操作树 OperatorTree；
4. 优化逻辑执行计划：逻辑层优化器进行 OperatorTree 变换，合并不必要的 ReduceSinkOperator，减少 shuffle 数据量；
5. 生成物理执行计划：遍历 OperatorTree，翻译为 MapReduce 任务；
6. 优化物理执行计划：物理层优化器进行 MapReduce 任务的变换，生成最终的执行计划。

关于 Hive SQL 的详细执行流程可以参考美团技术团队的文章：Hive SQL 的编译过程

:::

参考资料

• [Hive 简介及核心概念](https://github.com/heibaiying/BigData-Notes/blob/master/notes/Hive 简介及核心概念。md)

刷题

经典数据结构

数组

链表

栈和队列

解题套路

链表双指针技巧

递归操作链表

数组双指针技巧

二维数组操作技巧

滑动窗口算法

二分搜索算法

前缀和/差分技巧

栈

队列

单调栈技巧

单调队列技巧

二叉树

二叉搜索树

数据结构设计

图相关算法

DFS/回溯算法

BFS 算法

动态规划

贪心算法

分治算法

数学算法

其他经典面试题