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

最新推荐文章于 2024-06-16 04:56:11 发布

RenX000

最新推荐文章于 2024-06-16 04:56:11 发布

阅读量840

点赞数 27

分类专栏：自我总结文章标签： mysql 数据库后端笔记

本文链接：https://blog.csdn.net/m0_73976305/article/details/139103265

版权

自我总结专栏收录该内容

8 篇文章 0 订阅

订阅专栏

文章目录

MySQL的 ORDER BY 执行流程
使用`ORDER BY RAND()`
误区
为什么单行查询在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会使用全字段排序：

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

Rowid 排序

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

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

全字段排序 VS rowid排序

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

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

联合索引优化

创建联合索引：

ALTER TABLE t ADD INDEX city_user(city, name);

查询流程简化：利用联合索引 city_user(city, name)，天然保证按 name 递增排序，避免排序操作，流程如下：
- 从索引(city, name)中找到第一个满足 city=‘杭州’ 条件的记录。
- 取出该记录中的 city、name、age 直接返回。
- 重复上述步骤，直到取到1000条记录或不满足条件时停止。

覆盖索引优化

创建覆盖索引：

ALTER TABLE t ADD INDEX city_user_age(city, name, age);

查询流程进一步简化：利用覆盖索引避免回表操作，流程如下：
- 从索引(city, name, age)中找到第一个满足 city=‘杭州’ 条件的记录，直接取出并返回。
- 重复上述步骤，直到取到1000条记录或不满足条件时停止。

小结

MySQL 的排序操作有多种实现方式，具体选择取决于查询条件和索引情况。为了提高性能，建议合理利用联合索引和覆盖索引，减少排序操作和磁盘访问。

思考题

问题

假设表中已有联合索引 city_name(city, name)，查询杭州和苏州两个城市的市民名字，并按名字排序，显示前100条记录：

SELECT * FROM t WHERE city IN ('杭州', '苏州') ORDER BY name LIMIT 100;

执行过程中是否需要排序？

由于查询条件包含多个城市，合并后可能无序，无法利用单一的联合索引顺序，MySQL 需要对结果集进行排序。

如何在数据库端实现不排序？

每个城市单独查询

可以创建联合索引 city_name(city, name) 并结合分区查询：

(SELECT * FROM t WHERE city = '杭州' ORDER BY name LIMIT 100)
UNION ALL
(SELECT * FROM t WHERE city = '苏州' ORDER BY name LIMIT 100)
ORDER BY name LIMIT 100;

实现分页需求

对于第101页，即查询第10000至10099条记录：

(SELECT * FROM t WHERE city = '杭州' ORDER BY name LIMIT 10000, 100)
UNION ALL
(SELECT * FROM t WHERE city = '苏州' ORDER BY name LIMIT 10000, 100)
ORDER BY name LIMIT 10000, 100;

这种方式避免了单次大数据量排序，提高查询效率。

本文主要讲述了如何在MySQL中实现随机选择单词的功能，并分析了使用ORDER BY RAND()导致的性能问题，提出了几种优化方法。

使用`ORDER BY RAND()`

在初步尝试中，可以使用SELECT word FROM words ORDER BY RAND() LIMIT 3;来随机选择三个单词。这种方法虽简单，但随着数据量的增大，性能问题逐渐显现：

MySQL需要在内存中创建临时表并对其进行随机排序，这一过程涉及大量的数据读取和排序操作。
根据EXPLAIN命令的输出，此查询需要使用临时表和文件排序，显著增加了查询的资源消耗。

内存临时表与磁盘临时表

文章进一步解释了MySQL在处理ORDER BY RAND()时可能使用的两种临时表：

内存临时表：如果临时表的大小未超过tmp_table_size配置的限制，则使用内存中的临时表。
磁盘临时表：如果临时表大小超过限制，则转为在磁盘上创建临时表，这会进一步降低性能，由internal\_tmp\_disk\_storage\_engine控制。

随机选择算法的优化

文章提出了两种优化随机选择单词的方法：

随机算法1：
- 查询表的主键ID的最大和最小值。
- 生成一个介于最大和最小ID之间的随机数。
- 查询ID大于或等于该随机数的第一条记录。
- 这种方法虽然快速，但不够均匀，特别是当ID存在空洞时。
随机算法2：
- 计算表中的总行数。
- 生成一个随机数来选择行号。
- 使用LIMIT语句直接跳过前N行，返回第N+1行。
- 这种方法提供了更均匀的随机性，但性能开销大于算法1，尽管依然优于ORDER BY RAND()。

实际应用和进一步优化

对于实际应用，尤其是行数较多的表，推荐使用随机算法2，并在应用层进行SQL语句的构建和执行。此外，对于随机选择多个单词的需求，可以通过生成多个随机行号并分别查询来实现，尽管这会增加数据库的访问次数。

小结

在设计数据库交互时，考虑查询的性能影响是非常关键的。直接使用ORDER BY RAND()可能引起严重的性能问题，尤其是在数据量较大的情况下。通过理解MySQL的内部工作原理和利用优化的查询方法，可以显著提高应用的响应速度和整体性能。

思考题

如何进一步减少随机算法3的扫描行数？
是否有可能通过改进SQL结构或使用特定的数据库功能（如更复杂的索引策略）来优化随机选择的效率？

回答

要减少随机算法3的扫描行数，并优化随机选择的效率，可以采取以下措施：

使用单查询优化：
- 在一次查询中获取多个随机行。例如：
```
SELECT * FROM words WHERE id >= @X ORDER BY id LIMIT 3;
```
- 这样可以减少多次扫描。

利用表统计信息：

获取表的总行数并计算随机偏移量。

SELECT COUNT(*) INTO @C FROM words;
SET @Y = FLOOR(@C * RAND());
SELECT * FROM words LIMIT @Y, 1;

更准确地生成随机数以减少空洞。

索引优化：
- 创建复合索引，提高查询速度。
应用层优化：
- 缓存部分单词表，应用层随机选择，减少数据库访问。
- 预加载单词列表到应用层进行随机选择。
利用数据库特性：
- 使用数据库特定的优化功能，如随机选择函数。

示例优化：

使用单查询获取多个随机行，并利用表统计信息：

SELECT COUNT(*) INTO @C FROM words;
SET @Y1 = FLOOR(@C * RAND());
SET @Y2 = FLOOR(@C * RAND());
SET @Y3 = FLOOR(@C * RAND());
SELECT * FROM words WHERE id >= @Y1 LIMIT 1;
SELECT * FROM words WHERE id >= @Y2 LIMIT 1;
SELECT * FROM words WHERE id >= @Y3 LIMIT 1;

这样可以减少扫描行数，提高效率。

误区

案例一：条件字段函数操作

问题描述：在使用函数处理索引字段（如month(t_modified)）时，可能会破坏值的有序性，优化器会放弃走树搜索。MySQL无法利用索引进行快速查找，导致性能下降。
解决方案：避免在查询条件中对索引字段使用函数。改用基于字段本身的范围查询，例如使用t_modified BETWEEN '2016-7-1' AND '2016-7-31'。

案例二：隐式类型转换

问题描述：当查询条件中的数据类型不匹配字段类型时（如整数与字符串比较tradeid = 110717），会触发类型转换，从而导致索引失效。
解决方案：确保查询条件中的数据类型与数据库字段的类型一致，避免隐式类型转换。

案例三：隐式字符编码转换

问题描述：不同表之间的字段字符编码不一致（如utf8与utf8mb4），在进行连接查询时无法使用索引。

这个设定很好理解，utf8mb4是utf8的超集。类似地，在程序设计语言里面，做自动类型转换的时候，为了避免数据在转换过程中由于截断导致数据错误，也都是“按数据长度增加的方向”进行转换的。

解决方案：
- 调整编码：调整相关字段的字符编码，使其一致。
- 显式转换：在查询时使用CONVERT函数显式进行字符编码转换。

总结优化策略

避免在索引字段上使用函数：使用索引字段本身而非经过函数处理的结果作为查询条件。
保持数据类型一致：在查询中使用与数据库字段相同的数据类型。
统一字符编码：确保关联查询中涉及的字段具有相同的字符编码，或在查询中指定正确的字符编码转换。

行动建议

在业务代码升级时，对可能出现的新SQL语句进行EXPLAIN分析，是一个避免性能问题的好习惯。
共享实际遇到的性能问题及其解决方案，可以帮助团队成员学习并提高处理类似问题的能力。

为什么单行查询在MySQL中执行缓慢

背景

常见的理解是，复杂查询或大数据量返回会导致性能问题，但某些单行查询同样会非常缓慢。
排除数据库整体负载高的情况，本文专注于单行查询的特定性能问题。

第一类：查询长时间不返回

等待MDL锁

问题：查询被表的元数据锁（MDL）阻塞。
诊断：使用 SHOW PROCESSLIST 查看状态为 Waiting for table metadata lock。
解决：通过 sys.schema_table_lock_waits 找到阻塞的线程ID并 KILL 掉。

等待表Flush

问题：查询被 FLUSH TABLES 操作阻塞。
诊断：SHOW PROCESSLIST 显示状态为 Waiting for table flush。
解决：找到并终止执行 FLUSH TABLES 的会话。

等待行锁

问题：查询被另一个事务持有的行锁阻塞。
诊断：SHOW PROCESSLIST 显示状态为 Waiting for lock.
解决：使用 sys.innodb_lock_waits 查找持有锁的事务，并 KILL 掉。

第二类：查询执行慢

全表扫描

问题：查询没有使用索引，导致全表扫描。
示例：SELECT * FROM t WHERE c=50000 LIMIT 1;
解决：添加适当的索引。

一致性读导致慢查询

问题：一致性读导致扫描大量回滚日志（undo log）。
示例：SELECT * FROM t WHERE id=1;
复现：一个事务进行大量更新，导致查询需要回滚大量操作。
解决：优化事务设计，避免生成大量的undo log。

解决查询慢的策略

使用索引：确保查询条件使用适当的索引。
优化事务设计：避免长事务和大事务，减少锁争用。
监控和诊断工具：使用 SHOW PROCESSLIST、sys.innodb_lock_waits 等工具诊断问题。

小结

即使是简单的单行查询也可能因多种原因导致性能问题。了解和识别这些问题的原因是关键，合理使用工具和策略可以显著提升查询效率。

思考题

考虑查询SELECT * FROM t WHERE c = 5 FOR UPDATE;，分析它如何加锁以及锁释放的时机，讨论可能的性能影响。

幻读

幻读的定义

幻读是在数据库事务处理中遇到的一种现象，它发生在一个事务（Transaction）在执行过程中进行两次相同的查询，却得到了不同的结果。这种情况通常是由于在这两次查询间有其他事务插入或修改了数据所致。

幻读在“当前读”下才会出现

幻读的问题

幻读主要问题在于它违反了事务的隔离性，特别是在可重复读（Repeatable Read）隔离级别下。在这个级别，预期事务能够多次读取同样的数据行并得到相同的结果，但幻读破坏了这一期望。

在上文中提到的例子中，尽管在事务开始时锁定了d=5的行（id=5），事务中途对其他行（如id=0或id=1）的修改和新增行（id=1新增），导致了幻读，因为这些修改和添加的数据行在后续的查询中被看到了，违反了预期的事务隔离性。

解决幻读的策略

为了解决幻读问题，MySQL的InnoDB存储引擎引入了间隙锁（Gap Locks）的概念，除了对查询到的数据行加锁外，还对数据行之间的间隙加锁。这种锁策略不仅锁定数据行，也锁定行之间的间隙，防止其他事务在这些间隙中插入新的行。间隙锁和行锁合称next-key lock，每个next-key lock是前开后闭区间。

例如，如果事务A查询了所有d=5的数据行并对其加锁，通过间隙锁机制，事务B将无法插入一个新的d=5的行，因为这将需要在已锁定的间隙中插入数据，而这是被禁止的。

幻读的业务影响

幻读可能会导致数据不一致性，影响业务逻辑的正确执行。例如，如果一个事务基于查询结果执行操作（如计算总和或更新数据），由于幻读导致的数据变化可能会使得最终结果不正确或事务失败。

总结

在处理数据库事务时，了解和应对幻读是确保数据一致性和事务隔离性的关键。通过使用适当的隔离级别和理解数据库的锁机制，可以有效地管理和减少幻读带来的问题。在设计数据库操作和事务逻辑时，合理选择隔离级别和理解其对应的锁策略对于开发稳定可靠的应用程序至关重要。

RenX000

关注

27
点赞
踩
13

收藏

觉得还不错? 一键收藏
0
评论
mysql 排序、查询执行流程、幻读

MySQL的 ORDER BY 执行流程示例表和查询语句执行流程全字段排序Rowid 排序全字段排序 VS rowid排序联合索引优化覆盖索引优化小结思考题问题执行过程中是否需要排序？如何在数据库端实现不排序？实现分页需求使用`ORDER BY RAND()`内存临时表与磁盘临时表随机选择算法的优化实际应用和进一步优化小结思考题回答示例优化：误区案例一：条件字段函数操作案例二：隐式类型转换案例三：隐式字符编码转换总结优化策略行动建议为什么单行查询在My
复制链接

扫一扫