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

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

隔离级别	丢失修改	脏读	不可重复读	幻读
读未提交	✔️️️	❌	❌	❌
读已提交	✔️️️	✔️️️	❌	❌
可重复读	✔️️️	✔️️️	✔️️️	❌
可串行化	✔️️️	✔️️️	✔️️️	✔️️️

死锁

死锁是指两个或多个事务竞争同一资源，形成恶性循环。InnoDB 处理死锁的方式：回滚持有最少行级锁的事务。

事务日志

InnoDB 通过事务日志记录修改操作，采用追加方式（顺序 I/O，远快于随机 I/O）。

WAL（Write Ahead Logging）：先写日志，再由后台程序将内存中的脏数据刷回磁盘。日志持久化后，系统崩溃可自动恢复数据。

MySQL 中的事务

支持事务的引擎：InnoDB、NDB Cluster
自动提交：MySQL 默认 AUTOCOMMIT 模式，每条查询当作独立事务
两阶段锁定：InnoDB 随时可加锁，COMMIT/ROLLBACK 时统一释放

SELECT ... LOCK IN SHARE MODE; -- IS 锁 + S 锁
SELECT ... FOR UPDATE;         -- IX 锁 + X 锁（当前读）

多版本并发控制

MVCC 是行级锁的变种，很多场景避免了加锁，开销更低。

核心思想：通过保存数据在某个时刻的快照，使每个事务看到的数据保持一致（同一张表，不同事务可能看到不同版本）。

InnoDB 实现：每行记录保存两个隐藏列——创建版本号和删除版本号（基于系统版本号比较）。

Select：读取版本号 ≤ 当前事务版本号，且删除版本号未定义或 > 当前版本号的行
Insert：新行的创建版本号 = 当前系统版本号
Delete：被删行的删除版本号 = 当前系统版本号
Update：插入新行（创建版本号 = 当前版本号），旧行标记删除（删除版本号 = 当前版本号）

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

MySQL 的存储引擎

MySQL 将每个数据库保存为数据目录下的子目录，建表时创建 .frm 文件保存表定义。大小写敏感性与平台相关（Windows 不敏感，Linux 敏感）。

常见存储引擎：

引擎	说明
InnoDB	默认事务引擎，支持行锁、MVCC
MyISAM	MySQL 5.1 及之前的默认引擎
Archive	压缩存储，适合归档
Memory	内存表，速度快但不持久
NDB	分布式存储引擎

第二章 MySQL 基准测试（略）

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

第四章 Schema 与数据类型优化

数据类型

整数类型

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

类型	大小	作用
`TINYINT`	1 字节	小整数值
`SMALLINT`	2 字节	大整数值
`MEDIUMINT`	3 字节	大整数值
`INT`	4 字节	大整数值
`BIGINT`	8 字节	极大整数值

浮点数类型

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

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

类型	大小	用途
`FLOAT`	4 字节	单精度浮点数值
`DOUBLE`	8 字节	双精度浮点数值
`DECIMAL`		精确的小数值

字符串类型

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

CHAR 类型是定长字符串。

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

类型	大小	用途
`CHAR`	0-255 字节	定长字符串
`VARCHAR`	0-65535 字节	变长字符串

BLOB 和 TEXT

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

类型	大小	用途
`TINYBLOB`	0-255 字节	不超过 255 个字符的二进制字符串
`TINYTEXT`	0-255 字节	短文本字符串
`BLOB`	0-65 535 字节	二进制形式的长文本数据
`TEXT`	0-65 535 字节	长文本数据
`MEDIUMBLOB`	0-16 777 215 字节	二进制形式的中等长度文本数据
`MEDIUMTEXT`	0-16 777 215 字节	中等长度文本数据
`LONGBLOB`	0-4 294 967 295 字节	二进制形式的极大文本数据
`LONGTEXT`	0-4 294 967 295 字节	极大文本数据

日期和时间类型

类型	大小	格式	作用	备注
DATE	3 字节	YYYY-MM-DD	日期值
TIME	3 字节	HH:MM:SS	时间值或持续时间
YEAR	1 字节	YYYY	年份值
DATETIME	8 字节	YYYY-MM-DD hh:mm:ss	混合日期和时间值	有效时间范围为 1000-01-01 00:00:00 到 9999-12-31 23:59:59
TIMESTAMP	4 字节	YYYY-MM-DD hh:mm:ss	混合日期和时间值，时间戳	有效时间范围为 1970-01-01 00:00:01 到 2038-01-19 03:14:07

特殊类型

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

Schema 设计简单规则

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

范式 vs 反范式：范式减少冗余但增加关联查询复杂度，反范式减少关联但增加冗余。实际应混合使用。

ALTER TABLE 大表操作：

方案一：在备用机器上执行，然后切换主库
方案二：影子拷贝（创建新表 → 重命名交换）

第五章创建高性能的索引

索引是存储引擎用于快速查找记录的数据结构，是查询性能优化最有效的手段。

索引基础

索引可包含一个或多个列，多列索引中列的顺序很重要，MySQL 只能高效使用最左前缀列。

B-Tree 索引

大多数 MySQL 引擎支持 B-Tree 索引。索引值按顺序存储，叶子页到根距离相同。

检索过程：从根节点开始，逐层向下查找，叶子节点指向被索引的数据。

B-Tree 索引适用于全键值、键值范围、键前缀查找。

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)
);

B-Tree 索引支持的查询类型：

类型	说明	示例
全值匹配	匹配所有索引列	`last_name='Allen' AND first_name='Cuba' AND dob='1960-01-01'`
最左前缀匹配	只使用索引第一列	`last_name='Allen'`
列前缀匹配	匹配索引列的开头	`last_name LIKE 'J%'`
范围值匹配	匹配索引列的范围	`last_name BETWEEN 'Allen' AND 'Barrymore'`
精确+范围	精确匹配一列+范围匹配另一列	`last_name='Allen' AND first_name LIKE 'K%'`
覆盖索引	查询只访问索引	`SELECT last_name FROM people`

索引还可用于排序操作（索引树节点有序）。

B-Tree 索引的限制：

必须从最左列开始查找，否则无法使用索引
不能跳过索引中的列，只能用到跳过列之前的索引列
范围查询右边的列无法用索引，可用多个等值条件替代范围条件

哈希索引

基于哈希表实现，只有精确匹配索引所有列的查询才有效。不同键值的行计算不同哈希码，冲突时以链表方式存放指针。

限制：

不能避免读取行（不存储字段值，只存哈希值+行指针）
无法用于排序（数据不按索引值顺序存储）
不支持部分列匹配（始终使用全部索引列计算哈希）
只支持等值查询（=、IN()、<=>），不支持范围查询
访问速度快，但哈希冲突多时维护代价高

空间数据索引 (R-Tree)

MyISAM 支持空间索引，可用于地理数据存储，无须前缀查询，可从任意维度组合查询。MySQL 的 GIS 支持不完善，推荐使用 PostgreSQL 的 PostGIS。

全文索引

查找文本中的关键词，类似搜索引擎。支持停用词、词干、复数、布尔搜索等。适用于 MATCH AGAINST 操作，可与 B-Tree 索引共存。

索引的优点

大大减少服务器扫描的数据量
避免排序和临时表
将随机 I/O 变为顺序 I/O

不同规模表的策略：

小表：全表扫描更高效
中大型表：索引非常有效
特大型表：考虑分区技术

高性能的索引策略

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

独立的列

索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引。

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 必须使用前缀索引
前缀应足够长，使选择性接近整列（通常接近 0.03 即可）
索引选择性 = 不重复索引值 / 总记录数，越高越好

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

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;

多列索引

多个单列索引大部分情况下不能提高查询性能，应使用多列联合索引。

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

选择合适的索引列顺序

将选择性最高的列放到索引最前列
根据运行频率最高的查询调整顺序

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

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

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

聚簇索引

聚簇索引是一种数据存储方式：数据行和相邻键值紧凑存储。一个表只能有一个聚簇索引。

InnoDB 中数据行存放在索引的叶子页中。

优点：

相关数据保存在一起，减少磁盘 I/O
数据访问更快，索引和数据在同一个 B-Tree 中
覆盖索引扫描可直接使用页节点中的主键值

缺点：

数据全在内存时优势不明显
插入速度严重依赖插入顺序（按主键顺序插入最快）
更新聚簇索引列代价高（需移动行）
页分裂问题：插入时页满会分裂，占用更多磁盘空间
可能导致全表扫描变慢（行稀疏或页分裂）
二级索引更大：叶子节点包含主键值
二级索引需要两次查找（回表）

InnoDB 和 MyISAM 的数据分布对比

MyISAM 采用非聚簇索引，InnoDB 采用聚簇索引。

MyISAM 存储特点：

数据按插入顺序存储（定长行，类似数组）
主键索引叶子节点存储行指针（行号）
主键索引与二级索引结构无区别

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

InnoDB 存储特点：

主键索引是聚簇的（主键索引就是表）
叶子节点包含主键值、事务 ID、回滚指针及剩余列
二级索引叶子节点存储主键值（非行指针），移动行时无须更新
必须指定主键，未定义时 InnoDB 会隐式创建
使用递增 ID 作为主键可获得最佳插入性能

覆盖索引

索引包含所有需要查询的字段值，无须回表。

索引条目远小于数据行大小，减少数据访问
索引按列值顺序存储，范围查询 I/O 优于随机读取
InnoDB 二级索引叶子节点保存主键值，覆盖查询可避免对主键索引的二次查询

覆盖索引只能使用 B-Tree 索引（哈希/空间/全文索引不存储列值）。

使用索引扫描来做排序

EXPLAIN 中 type 列为 index 表示使用了索引扫描排序（与 Extra 列的 Using index 不同）。

只有当索引列顺序和 ORDER BY 子句顺序完全一致且方向相同时，才能用索引排序。

索引和锁

InnoDB 只在访问行时加锁，索引能减少访问的行数，从而减少锁的数量。