MySQL优化器参数prefer_ordering_index优化理念_mysql order by perferindex-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_41706908/article/details/143629591

一、关于order by limit n的优化

示例表结构如下：
root@localhost : monitor_connect 22:26:45>show create table monitor_oracle_info\G
*************************** 1. row ***************************
Table: monitor_oracle_info
Create Table: CREATE TABLE monitor_oracle_info (
id bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘自增主键’,
login_time datetime DEFAULT NULL COMMENT ‘登录时间’,
db_ip varchar(20) DEFAULT NULL COMMENT ‘数据库ip’,
username varchar(200) DEFAULT NULL COMMENT ‘数据库账号’,
login_type varchar(200) DEFAULT NULL COMMENT ‘登录数据库的方式，例如：plsqldeveloper客户端登录就显示PL/SQL Developer’,
client_hostname varchar(200) DEFAULT NULL COMMENT ‘连接到数据库会话的客户端机器的名称’,
TERMINAL varchar(20) DEFAULT NULL COMMENT ‘客户端会话所在的终端名称’,
PRIMARY KEY (id),
KEY idx_login_time (login_time),
KEY idx_login_type (login_type),
KEY idx_db_ip (db_ip)
) ENGINE=InnoDB AUTO_INCREMENT=8131432 DEFAULT CHARSET=utf8mb4 COMMENT=‘oracle数据库登录连接信息’

分析：
该查询走的全表扫描，明明login_time有索引，为啥不直接借助索引的有序性来减少排序呢？
我们假设走索引login_time，因为select *，没办法直接从索引login_time的叶子节点取到其他字段的值，所以需要回表，

具体检索流程应该为：
1、遍历login_time索引的叶子节点，由于索引的有序性，直接拿出已经排好序的login_time值对应的主键id,；
2、因为开启了MRR特性，借助mrr特性，会把id放到read_rnd_buffer中，由于数据量大read_rnd_buffer_size可能不足，所以只能分批把主键id放到read_rnd_buffer中，然后id按递增排序，这样会把回表的随机 IO 转化为顺序 IO 以降低查询过程中 IO 开销；
3、排序后的 id 数组，依次到主键 id 索引中查记录，并作为结果返回；
MySQL优化器会认为查询数据量大，回表数据量也大，因此还不如直接全表记录扫描呢！于是便走的全表扫描，但是由于数据量大，sort buffer不足，需要借助临时文件来排序(Extra 字段中的Using filesort关键字表示使用了临时文件)

此时的真实检索流程：
1、初始化 sort_buffer，用来存放表的所有字段(假设max_length_for_sort_data设置的足够大，否则可能会rowid排序算法）；
2、依次在主键索引中找到整行数据，存入 sort_buffer 中，直到sort buffer耗尽；
3、对 sort_buffer 中的数据按照字段 login_time 做排序；
4、然后把sort buffer按着login_time排序后的数据转存到磁盘上的临时文件file_1中；
5、依次重复1-4步骤，最终根据数据量会生成N个排序后临时文件，
6、最后把这 N 个有序文件再合并成一个有序的大文件；
7、按照大文件把结果分批返回给客户端(这里分批由MySQL参数net_buffer_length和操作系统内核参数/proc/sys/net/core/wmem_default决定)；

分析：

1、我们先介绍下MySQL优化器参数prefer_ordering_index：

1）mysql 5.7中：
prefer_ordering_index (default on)
Controls whether, in the case of a query having an ORDER BY or GROUP BY with a LIMIT clause, the optimizer tries to use an ordered index instead of an unordered index, a filesort, or some other optimization. This optimzation is performed by default whenever the optimizer determines that using it would allow for faster execution of the query.
Because the algorithm that makes this determination cannot handle every conceivable case (due in part to the assumption that the distribution of data is always more or less uniform), there are cases in which this optimization may not be desirable. Prior to MySQL 5.7.33, it ws not possible to disable this optimization, but in MySQL 5.7.33 and later, while it remains the default behavior, it can be disabled by setting the prefer_ordering_index flag to off.
prefer_ordering_index（默认打开）
控制在查询具有ORDER BY或GROUP BY并带有LIMIT子句的情况下，优化器是否尝试使用有序索引而不是无序索引、文件排序或其他优化。默认情况下，每当优化器确定使用它可以更快地执行查询时，就会执行此优化。
因为做出这一决定的算法无法处理所有可能的情况（部分原因是假设数据的分布总是或多或少均匀的），所以在某些情况下，这种优化可能是不可取的。在MySQL 5.7.33之前，无法禁用此优化，但在MySQL 5.7.33及更高版本中，虽然它仍然是默认行为，但可以通过将prefer_ordering_index标志设置为关闭来禁用它

2）mysql 8中：
prefer_ordering_index (default on)
Controls whether, in the case of a query having an ORDER BY or GROUP BY with a LIMIT clause, the optimizer tries to use an ordered index instead of an unordered index, a filesort, or some other optimization. This optimization is performed by default whenever the optimizer determines that using it would allow for faster execution of the query.
Because the algorithm that makes this determination cannot handle every conceivable case (due in part to the assumption that the distribution of data is always more or less uniform), there are cases in which this optimization may not be desirable. Prior to MySQL 8.0.21, it was not possible to disable this optimization, but in MySQL 8.0.21 and later, while it remains the default behavior, it can be disabled by setting the prefer_ordering_index flag to off.
prefer_ordering_index（默认打开）
控制在查询具有ORDER BY或GROUP BY并带有LIMIT子句的情况下，优化器是否尝试使用有序索引而不是无序索引、文件排序或其他优化。默认情况下，每当优化器确定使用它可以更快地执行查询时，就会执行此优化。
因为做出这一决定的算法无法处理所有可能的情况（部分原因是假设数据的分布总是或多或少均匀的），所以在某些情况下，这种优化可能是不可取的。在MySQL 8.0.21之前，无法禁用此优化，但在MySQL 8.0.21及更高版本中，虽然它仍然是默认行为，但可以通过将prefer_ordering_index标志设置为关闭来禁用它。

综上：
1）prefer_ordering_index参数在MySQL5.7和8.0中默认都是开启的，并且在MySQL5.7版本中MySQL5.7.33之前没有该参数，数据库依旧会进行limit优化，5.7.33以及之后可以通过该参数关闭这个优化策略，同样在MySQL8.0版本中MySQL 8.0.21之前没有该参数，数据库依旧会进行limit优化，8.0.21以及之后可以通过该参数关闭这个优化策略；
2）该参数开启的意义是，当查询具有ORDER BY或GROUP BY并带有LIMIT子句的情况下，优化器会做出判断，不会因为数据量大回表代价高而直接选择全表扫描，而是会结合limit n中的n的大小来做出判断，如果n比较小，会选择使用索引+回表的方式检索数据，如果n比较大，依旧会选择使用全表扫描的方式检索；
因为order by执行循序优先于limit，因此优化器如果没有对order by和limit同时使用的场景的特殊优化，那么就会和无limit的情况一样去考虑，大多数情况下，会全表扫描。正是因为MySQL优化器中limit对于order by语句的优化策略：
我们上面的例子是limit 10, 如果read_rnd_buffer足够的话，只需要依次顺序id回表即可查询需要数据，这个开销可能远小于全表扫描，最终优化器会选择索引检索的方式，这得益于prefer_ordering_index=on；

分析：
我们发现确实如前面分析，limit n比较大的时候会走全表扫描的方式；

分析：
我们发现当查询语句可以借助覆盖索引的来得到结果集时，他由于不需要回表，也就没有前面分析的由于回表代价高，优化器放弃选择索引检索，进而选择全表扫描的方式，可以看到使用覆盖索引的时候(Extra中关键字是Using index)，依旧会使用索引检索的方式；

二、如何合理设置该参数

1）查看参数设置
root@localhost : monitor_connect 00:13:47>show variables like ‘optimizer_switch’\G
*************************** 1. row ***************************
Variable_name: optimizer_switch
Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=off,block_nested_loop=on,batched_key_access=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,prefer_ordering_index=on

2）设置方式：
root@localhost : monitor_connect 00:13:51>set global optimizer_switch=‘prefer_ordering_index=on’;
Query OK, 0 rows affected (0.00 sec)

设置建议：：

1、我们知道MySQL 5.7已经由了MRR的特性，通过把主键放到read_rnd_buffer中进行递增排序，然后把排序后的id数据整体去主键索引中检索数据，这样已经把原本回表的离散io转换成了顺序io，极大的降低了回表的代价，提高了回表的效率，尤其是limit n当n比较小的时候，这个时候开启这个参数，会减少由于优化器认为回表代价高导致不走索引而选择全表扫描；所以此时我建议你开启这个参数；
2、但是该参数存在bug，当limit n中的n比较小的时候，导致优化器直接选择走order by上的索引，忽略了选择性更高的其他索引；例如如下sql：
select login_time from monitor_oracle_info where db_ip=‘10.2.1.1’ order by login_time limit 10,
执行计划：
root@localhost : monitor_connect 00:41:38>desc select login_time from monitor_oracle_info where db_ip=‘10.2.1.1’ order by login_time limit 10;
±—±------------±--------------------±-----------±------±--------------±---------------±--------±-----±-----±---------±------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±--------------------±-----------±------±--------------±---------------±--------±-----±-----±---------±------------+
| 1 | SIMPLE | monitor_oracle_info | NULL | index | NULL | idx_login_time | 6 | NULL | 10 | 10.00 | Using where |
±—±------------±--------------------±-----------±------±--------------±---------------±--------±-----±-----±---------±------------+
1 row in set, 1 warning (0.00 sec)
虽然走login_time索引可以减少避免排序，但是由于条件中还有db_ip, 所以无法直接从login_time索引中确定db_ip的值(无法使用覆盖索引)，需要回表很多次，去主键索引中确认db_ip的值。导致sql性能低下，需要查询很久（13.43 秒）。
很明显该该sql选择走db_ip索引效果会更好，因为db_ip的选择性更好，直接过滤出来几条数据，然后再排序，性能也很好，但是他选择是idx_login_time，此时我建议你关闭这个参数；

综上所述：
该参数开启和关闭各有利弊，建议保持默认即可，当出现问题的时候你需要了解其中的奥秘，然后做出针对性的优化，例如真触发了bug导致没有选择合理的索引，
你可以选择创建一个组合索引来解决这个问题：
alter table monitor_oracle_info add idx_db_ip_login_time(db_ip,login_time)；

为什么加完组合索引后会导致优化器选择正确的索引呢？
组合索引中db_ip确定值后，login_time的值也是有序的，直接覆盖索引即可查询到结果；加完索引后语句0.01不到就出结果了！