这篇文章的示例表来源于阿里云upbeat同学的一篇文章，这个例子用来说明8.0的bug。

本篇我们借用这个示例表来说明排序模式对性能的影响。

初始化表结构和数据：

# 建表
create table t1 (
  id int auto_increment primary key,
  col2 int,
  col3 char(255),
  col4 varchar(8192),
  paytime int unsigned DEFAULT NULL
) ENGINE=INNODB;


# 导入数据
DELIMITER //
CREATE PROCEDURE insert_data()
BEGIN
    DECLARE i INT DEFAULT 0;


    WHILE i < 300000 DO
        INSERT INTO t1 VALUES (null, i, repeat('a', 255), repeat('a', 8192), FLOOR(RAND() * 300000));
        SET i = i + 1;
    END WHILE;
END //


CALL insert_data;

“bug”复现：

# 查询
SELECT * FROM t1 ORDER BY paytime LIMIT 1;
//耗时 0.1s

set max_length_for_sort_data = 10240;
SELECT * FROM t1 ORDER BY paytime LIMIT 1;
//耗时 1.4s

可以看到，把内存改大，反而查询性能变低。

分析

从optimizer_trace 可以看到答案。
由于有col4的存在，单行大小大于8k。

当 max_length_for_sort_data是默认值（1024）的时候，由于内存不够放下整行，只能采用row_id 排序，有些文章叫双路排序，
这个排序过程，每次是只把paytime和id取出来放到优先队列（limit 1 的话，就是一个单行结构）。
遍历完成后拿到最小的id，再回表读整行；
因此只有一行读到了col4字段。

当把max_length_for_sort_data改成10k，现在单行放的下了，就变为全字段排序，对应文章叫单路排序，
这个排序过程，每次是把整行读出来放到优先队列，遍历完成后就得到结果，直接返回；
因此3万行都读了col4字段。
所以查询反而变慢了。

小结

知道了这个原因后，好像也可以称之为feature，显然limit 多一点以后，性能的差距就缩小了。

===
顺手帮达叔推下新专栏，别看目录看上去普普通通，要把这些话题写得深入浅出，作者的经验和技术积累是够够的。一起期待。

ITPub博客

MySQL里那些“一眼bug”的案例们（三）

最新文章