MYSQL 8 和 POLARDB 在处理order by 时的缺陷问题

最近问问题的同学挺多的，也有问有没有群的，实在是忙没有建群，所以问的人多了，想想还是建一个群，但本人写文章不懒，其他的比较懒，因为问POLARDB 的问题的多，所以建立了一个 POLARDB 和 PG，MYSQL 8 以及文章问题的讨论群。希望能帮助自己也帮助大家共同提高，要进群的，可以添加微信 liuaustin3 ,来申请加群

先说说这个问题，这个问题在POLARDB 和 MYSQL 都存在，所以这不是POLARDB 代码的问题，这是存在于 MYSQL 8 的问题， 而由于POLARDB 使用了 MYSQL 的语句处理和解析等部分，导致的跟随性问题。

这个功能是体现在查询中如果有ORDER BY 的语句，并且ORDER BY  后面的谓词是索引或索引的部分的情况下，同时如果where 条件的键值也包含在索引中此时，就可以使用这个索引来避免 file sort 的排序方式，而是否使用这个索引来进行工作，则与优化器判断是否需要回表，回表后的成本问题，来判断是否使用索引。但问题是，在使用这个功能的时候，由于成本判断的问题，导致使用了错误的方式处理了语句导致语句执行的效能问题。

SELECT @@optimizer_switch;

SELECT @@optimizer_switch LIKE '%prefer_ordering_index=on%';

https://dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html

https://dev.mysql.com/doc/refman/8.0/en/limit-optimization.html

在MYSQL 中处理ORDER BY 中条件带有索引的问题时并不能有效利用索引，而使用file sort 的方式来处理ORDER BY 的查询。同时这里还带有两个问题

1  ORDER BY 后带有 LIMIT 

2  ORDER BY 后不带有LIMIT 

在某些例子中MYSQL 可以使用索引的方式来满足ORDER BY 的查询，而不在使用FILE SORT 的方式处理查询，这里索引起到了加速索引结果给出的结果，但实际上如果查询是

下面我们来用事例来说明MYSQL 8 中的功能,我们创建一张表，并灌入数据

 CREATE TABLE `t_user` (

  `id` bigint NOT NULL AUTO_INCREMENT,

  `name` varchar(255) DEFAULT NULL,

  `age` tinyint DEFAULT NULL,

  `create_time` datetime DEFAULT NULL,

  `update_time` datetime DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `idx_create_time` (`create_time`),

  KEY `idx_create_time_update_time` (`create_time`,`update_time`),

  KEY `idx_name` (`name`)

) ENGINE=InnoDB AUTO_INCREMENT=1000000 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |；

delimiter $$

DROP PROCEDURE IF EXISTS proc_batch_insert;

CREATE PROCEDURE proc_batch_insert()

BEGIN

DECLARE pre_name BIGINT;

DECLARE ageVal INT;

DECLARE i INT;

SET pre_name=187635267;

SET ageVal=100;

SET i=1;

WHILE i < 1000000 DO

INSERT INTO t_user(`name`,age,create_time,update_time) VALUES(CONCAT(pre_name,'@qq.com'),(ageVal+i)%30,NOW(),NOW());

SET pre_name=pre_name+100;

SET i=i+1;

END WHILE;

END $$

delimiter ;

call proc_batch_insert();

事例：首先下面是一条查询语句，虽然有排序索引 和 命中

explain select * from t_user where age = 11 order by create_time,update_time;

从这个语句可以明确的看到排序走了filesort ，虽然我们建立了 create_time 和 update 的索引，但是因为我们的条件中并未含有 create_time或者update_time 的字段条件，所以最终MYSQL 8.030并未使用order by 排序相关的索引。

1  查询条件没有索引，同时排序有索引，走了排序索引

explain select * from t_user where age = 11 order by create_time,update_time limit 1;   

实际上我们可以比对，在我们默认开启prefer_ordering_index=on 的情况下，我们的下面的查询都在使用 order by 后的索引，但是如果我们将这个mysql 8.025 后的功能 prefer_order_index=off 关闭后，我们的查询速度直线上升。

可以参见下图。

那么到底这里执行计划在哪里有变化了，我们通过optimizer_trace 来查看其中的不同。其中问题在下图中，使用了 index_order

而不使用prefer_ordering_index=off 的语句的执行计划参见下图

这里最主要的问题在于一般，通过条件查询后，获得数据的结果集并不大，通过filesort 的方式也未必会太慢，但如果打开了order by 索引优化，会导致查询走order by 后的索引，导致表扫描的问题加重，次数增加。

所以 prefer_ordering_index=on 

explain select * from t_user where create_time = '2022-10-10' order by create_time,update_time;

而我们变化条件后，可以看到相关的查询就走了 create_time 和  update_time的索引。

当然这不是我们问题要提到的BUG 的问题，问题的产生是基于order by 后加limit 的问题, limit 的限制数据量越大，出现问题的可能性越小。

下面我们根据这个表，并且建立多种索引，看看在打开 prefer_ordering_index=on  和不打开的情况下，的语句执行的情况。

查询语句1 

set optimizer_switch = "prefer_ordering_index=on"; 

select * from t_user where name = '138482267@qq.com' order by create_time,update_time ;

 set optimizer_switch = "prefer_ordering_index=off";

explain select * from t_user where name = '138482267@qq.com' order by create_time,update_time ;

在有合适的索引的情况下，打开perfer_order_index 在查询最终的执行计划没有区别。

下面我们删除这个索引，在此查询，发现MYSQL 8在打开 perfer_order_index 后的在没有合适的索引的情况下，还是走了同一种索引，以WHERE 条件为准

我们更改查询条件，并建立 age 的索引，此时执行计划变化了，在打开order_index perfer 后，我们的第二个语句的索引变成了排序使用的索引。

 set optimizer_switch = "prefer_ordering_index=off";

explain select * from t_user where age = 11 order by create_time,update_time limit 10;

 set optimizer_switch = "prefer_ordering_index=on";

explain select * from t_user where age = 11 order by create_time,update_time limit 10;

并且实际比对，第二个走了排序的索引的方式的查询的速度，比没有使用的速度还要快。

但如果我们在变化条件将条件转换为主键，并且还用类似范围查询的方式对比，则不打开perfer_order_index 的方式更好。

OFF

ON

总结：

1 不建议在不熟悉这个功能的情况下，使用 perfer_order_index , 在8.025 的后的MYSQL 的版本，建议在my.cnf  设置为关闭这个功能

2  打开这个功能的情况下，注意以下查询预计

   1  where 条件使用主键的方式时，可能会触发BUG 导致查询效率降低，此时语句中必然的LIMIT 否则触发的概率不大。

   2  在某些情况下，非主键的 where 条件，在打开 perfer_order_index 后，可能查询比不打开功能要快，但有些时候要慢，这取决于使用 order by 后的条件索引扫描时，相关where 条件的值在索引中遍历到的位置，位置靠前，速度快，位置靠后，查询速度慢。

同时此问题在POLARDB 8.01 版本 for mysql （等同于MYSQL 8.013 也存在此问题，阿里云正在修改并提供更好支持此功能的算子方式）