mysql - MySQL：排序内存不足，考虑增加服务器排序缓冲区大小
问题描述
我找不到 MySQL 行为的解释。示例如下。
字段name和name_full是text类型，字段price_steps是json类型。他们都没有索引。
SELECT
name,
name_full,
price_steps
FROM
`lots`
WHERE
EXISTS (
SELECT
*
FROM
`categories`
INNER JOIN `category_lot` ON `categories`.`id` = `category_lot`.`category_id`
WHERE
`lots`.`id` = `category_lot`.`lot_id`
AND `category_id` IN (25)
)
ORDER BY
`created_at` DESC
LIMIT 31 OFFSET 0
MySQL 抛出错误：[Err] 1038 - Out of sort memory，考虑增加服务器排序缓冲区大小。
好吧，就这样吧。
然后我在选择部分添加了额外的字段。
SUBSTRING(`name_full`, 1, 200000000000) as name_full2
并且查询成功运行（为什么？额外的字段应该导致额外的内存分配，不是吗？）。
然后我决定让查询更重并替换字符串
AND `category_id` IN (25)
和
AND `category_id` IN (1,2,3,4,5,6,7,8,9,10, 25)
并且查询也成功完成。
类别 = 25 的行数仅为 250 左右，但 (1,2,3,4,5,6,7,8,9,10,25) 中的类别约为 40000 行。这一定会导致额外的内存需求，但 mysql 不会抛出错误。为什么？
对这个悖论有什么解释吗？提前致谢！
更新1
EXPLAIN查询失败
mysql> EXPLAIN SELECT name, name_full, price_steps FROM `lots` WHERE EXISTS ( SELECT * FROM `categories` INNER JOIN `category_lot` ON `categories`.`id` = `category_lot`.`category_id` WHERE `lots`.`id` = `category_lot`.`lot_id` AND `category_id` IN(25) ) ORDER BY `created_at` DESC;
+----+-------------+--------------+------------+--------+--------------------------------------------------------------+----------------------------------+---------+---------------------------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+--------+--------------------------------------------------------------+----------------------------------+---------+---------------------------+------+----------+----------------------------------------------+
| 1 | SIMPLE | categories | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | category_lot | NULL | ref | category_lot_lot_id_foreign,category_lot_category_id_foreign | category_lot_category_id_foreign | 8 | const | 1099 | 100.00 | Start temporary |
| 1 | SIMPLE | lots | NULL | eq_ref | PRIMARY | PRIMARY | 8 | torgs.category_lot.lot_id | 1 | 100.00 | End temporary |
+----+-------------+--------------+------------+--------+--------------------------------------------------------------+----------------------------------+---------+---------------------------+------+----------+----------------------------------------------+
3 rows in set, 2 warnings (0.00 sec)
EXPLAIN成功查询（添加第 4 个字段）
mysql> EXPLAIN SELECT name, name_full, price_steps,SUBSTRING(`name_full`, 1, 200000000000) as name_full2 FROM `lots` WHERE EXISTS ( SELECT * FROM `categories` INNER JOIN `category_lot` ON `categories`.`id` = `category_lot`.`category_id` WHERE `lots`.`id` = `category_lot`.`lot_id` AND `category_id` IN(25) ) ORDER BY `created_at` DESC;
+----+-------------+--------------+------------+--------+--------------------------------------------------------------+----------------------------------+---------+---------------------------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+--------+--------------------------------------------------------------+----------------------------------+---------+---------------------------+------+----------+----------------------------------------------+
| 1 | SIMPLE | categories | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | category_lot | NULL | ref | category_lot_lot_id_foreign,category_lot_category_id_foreign | category_lot_category_id_foreign | 8 | const | 1099 | 100.00 | Start temporary |
| 1 | SIMPLE | lots | NULL | eq_ref | PRIMARY | PRIMARY | 8 | torgs.category_lot.lot_id | 1 | 100.00 | End temporary |
+----+-------------+--------------+------------+--------+--------------------------------------------------------------+----------------------------------+---------+---------------------------+------+----------+----------------------------------------------+
3 rows in set, 2 warnings (0.00 sec)
EXPLAIN成功查询（添加第 4 个字段和category_id in (1,2,3,4,5,6,7,8,9,10,25)）
mysql> EXPLAIN SELECT name, name_full, price_steps, SUBSTRING(`name_full`, 1, 200000000000) as name_full2 FROM `lots` WHERE EXISTS ( SELECT * FROM `categories` INNER JOIN `category_lot` ON `categories`.`id` = `category_lot`.`category_id` WHERE `lots`.`id` = `category_lot`.`lot_id` AND `category_id` IN(1,2,3,4,5,6,7,8,9,10,25) ) ORDER BY `created_at` DESC;
+----+--------------+--------------+------------+--------+--------------------------------------------------------------+----------------------------------+---------+---------------------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+--------------+------------+--------+--------------------------------------------------------------+----------------------------------+---------+---------------------+------+----------+---------------------------------+
| 1 | SIMPLE | <subquery2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | Using temporary; Using filesort |
| 1 | SIMPLE | lots | NULL | eq_ref | PRIMARY | PRIMARY | 8 | <subquery2>.lot_id | 1 | 100.00 | NULL |
| 2 | MATERIALIZED | categories | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 11 | 100.00 | Using where; Using index |
| 2 | MATERIALIZED | category_lot | NULL | ref | category_lot_lot_id_foreign,category_lot_category_id_foreign | category_lot_category_id_foreign | 8 | torgs.categories.id | 1883 | 100.00 | NULL |
+----+--------------+--------------+------------+--------+--------------------------------------------------------------+----------------------------------+---------+---------------------+------+----------+---------------------------------+
4 rows in set, 2 warnings (0.00 sec)
更新2
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1276 | Field or reference 'torgs.lots.id' of SELECT #2 was resolved in SELECT #1 |
| Note | 1003 | /* select#1 */ select `torgs`.`lots`.`name` AS `name`,`torgs`.`lots`.`name_full` AS `name_full`,`torgs`.`lots`.`price_steps` AS `price_steps`,substr(`torgs`.`lots`.`name_full`,1,200000000000) AS `name_full2` from `torgs`.`lots` semi join (`torgs`.`categories` join `torgs`.`category_lot`) where ((`torgs`.`category_lot`.`category_id` = `torgs`.`categories`.`id`) and (`torgs`.`lots`.`id` = `<subquery2>`.`lot_id`) and (`torgs`.`categories`.`id` in (1,2,3,4,5,6,7,8,9,10,25))) order by `torgs`.`lots`.`created_at` desc |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
解决方案
推荐阅读
- highcharts - 基于数据分组的 Highstock 导出 csv（export-data 模块）
- javascript - 为什么 x *和* z = 3？
- python - Requests-HTML (Python) 的协程问题
- kotlin - For 循环必须有一个 iterator()
- algorithm - 计算数组元素对，其中一个是另一个的倍数
- javascript - 无法将新文档设置为 Firestore 中的集合
- jquery - jQuery长度属性根本不起作用
- html - 如何在没有 id/name 的情况下生成指向页面上特定点的超链接？
- mixed-models - 考虑随机效应不确定性的边际均值
- react-native - react-native 为什么如果我使用这个 useEffect 方法会出现内存泄漏