mysql 排序、查询执行流程、幻读

MySQL的 ORDER BY 执行流程

在开发应用时，经常需要根据指定字段排序来显示结果。以下是关于 MySQL 中 ORDER BY 的执行流程及影响因素的总结。

示例表和查询语句

假设有一个市民表，定义如下：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

查询城市为“杭州”的市民名字，并按姓名排序返回前1000个结果的SQL语句：

SELECT city, name, age FROM t WHERE city='杭州' ORDER BY name LIMIT 1000;

执行流程

全字段排序

当执行一个包含ORDER BY的查询时，如果不使用适当的索引，MySQL会使用全字段排序：

1. 初始化排序缓冲区：sort_buffer被用来存储需要排序的列。
2. 从索引中检索数据：基于city索引检索满足条件的记录，然后加载相应的字段到sort_buffer。
3. 执行排序：在sort_buffer中对数据进行排序。
4. 输出结果：根据排序结果输出前1000条数据。

Rowid 排序

当单行数据量过大时，MySQL可能采用rowid排序以节约内存：

1. 排序缓冲区较小：只存储关键的排序字段和主键id。
2. 数据检索和排序：检索并排序关键字段，使用主键再次查询以获取完整数据。
3. 结果输出：根据排序后的id顺序输出结果。

全字段排序 VS rowid排序

在内存足够的情况下优先选择全字段排序，否则rowid排序

体现了MySQL的一个设计思想： 如果内存够，就要多利用内存，尽量减少磁盘访问。

联合索引优化

1. 创建联合索引：

<