本案例中的问题现象发生于mysql环境。涵盖的版本经测试在主流的5.7-8.0均有涉及。
不排除随着版本的变化可能会有细微的差异表现。但问题的原因及m6米乐安卓版下载的解决方案基本相同。
数据库版本: mysql5.7-8.0
编写：张程
技术指导：王运亮
针对目标应用系统分析，当前库中存在一类包含limit 的低效查询语句。且都具有共同的查询表现。为此单独排查了这一类性能问题以确定根本原因。该问题发生于mysql5.7版本。
问题表象：
- 同时使用order by和limit。
- order by的字段有现成索引可以用，且该索引与where期望使用的索引不一致。
- limit的数量较少。
达成上述条件时，需要注意相应sql是否出现性能问题。
问题影响：
- 执行时间普遍比较长。
- 返回很少的数据量，低效查询有较大优化空间。
历史执行情况如下：
该查询执行一直很慢，达到数百秒且较为平均，代表存在性能问题。另外通过扫描行数分析，需要扫描大量的数据，但最终却返回很少的数据（这里几乎为0）。说明访问过程中做了很多无用功，造成了执行效率问题。
以上分析，排除了环境或主机层面的影响，基本可以确定，需要优化sql解决。
3.1 执行效率分析：
sql文本如下：
select ticket . id,
ticket . creator,
ticket . create_at,
ticket . update_at,
ticket . end_at,
ticket . is_deleted,
ticket . sn,
ticket . title
from ticket
where (ticket . is_deleted = 0 and ticket .
service_type = 'xxxxxxx' and ticket .
create_at between '2022-01-01 00:00:00' and '2022-09-30 23:59:59')
order by ticket . id desc limit 2;
执行计划如下：
实测执行时间较长。
语句通过primary主键索引访问。查看表上相应索引信息：
表上是有满足相应过滤条件的复合索引的。
相应条件列的过滤性很好，测试使用该索引访问的查询效率也比较好。因此查询使用该复合索引才对，但问题sql却采用了主键索引的顺序扫描。好处是可以避免排序动作，但如果顺序扫描时前面大多数记录都不满足where过滤条件，则会导致需要扫描大量的数据才找到满足条件的结果，造成查询低效。
3.2 去掉limit语句测试：
这里看到相应查询又都包含limit n的语句，分析是否与该语句有关。去掉进行测试：
select ticket . id,
ticket . creator,
ticket . create_at,
ticket . update_at,
ticket . end_at,
ticket . is_deleted,
ticket . sn,
ticket . title
from ticket
where (ticket . is_deleted = 0 and ticket .
service_type = 'xxxxxxx' and ticket .
create_at between '2022-01-01 00:00:00' and '2022-09-30 23:59:59')
order by ticket . id desc;
调整后，查询直接选择了新的索引。
实测执行效率很好。这里基本可以确定与limit 语句有关。
3.3 问题根源排查：
1.利用hint提示，对比相同sql的不同执行计划成本：
正确索引的执行计划成本：
原始错误索引的执行计划成本：
这里看到查询成本相同。优化器选择了走主键索引避免排序。但仍然没有找到走错索引的根本原因。
2.分析问题执行计划的trace文件：
这里看到，首先优化器在分析到key_delete_service_create索引的时候，根据查询成本是选择了该索引的。
接下来在重新考虑排序及limit部分时，优化器又重新选择了主键primary索引。
正是这次重新调整，让最终查询使用了上述错误的主键索引查询并造成执行效率问题。这里只能怀疑优化器在处理这类情况时的评估不够准确，造成选择索引的问题。
更进一步的论证可以参考相应文章：
怀疑为mysql的bug，是因为mysql源码中，对order by … limit …评估不准确。
##首先假设被检查的索引同ref_key没有关系。其次假设数据是均匀分布的。
如果数据是均匀分布的,那么通过排序索引访问前n行数据(order by limit n)
需要扫描的行数为 n*(table_records/distinct(ref_key))
这两个假设是估算通过排序索引来访问cost 的前提(但是现实生产中这两个假设在绝大多数场景中都是不成立的，所以就造成多数场景下索引选择错误)
因此会有如下判断：
（1） 当limit n过小时，会重新计算order by的成本。
（2） 估算成本时，会按选择性和limit n，来估算要扫描的行数。如果行数大于表中记录数，则不会使用limit n优化。否则，会使用limit n优化（导致使用排序列索引）
这里更进一步的原因不再深入分析。相应的问题表现与上述文章现象基本一致。
参考文章中的处理方案，可以总结出对应的m6米乐安卓版下载的解决方案。为了让案例更具有说服力，也为了测试具体优化手段的提升表现。设计了后面的模拟案例。
3.4 案例模拟演示：
问题sql是发生在5.7版本中的，本次案例通过版本较新的8.0环境测试。可以验证不同环境是否表现一致，也可以更为准确的跟踪到具体的执行步骤耗时等。
1.问题重现：
创建测试表：
create table test_t2 as select * from information_schema.columns;
create index key_table_schema on test_t2(table_schema);
alter table test_t2 add column id int auto_increment primary key
问题模拟查询语句：
select *
from test_t2
where table_schema not in
('performance_schema', 'sys', 'information_schema', 'mysql')
order by id limit 2;
主要过滤条件为table_schema列。
条件过滤后的数据量很少，查询条件具有很好的过滤作用。
过滤列上存在索引。这里分析是可以通过table_schema索引完成查询的。
查看相应执行计划：
与问题sql表现一致，使用了主键索引去访问，且在主键索引扫描过程中消耗了大量的执行时间（169ms）。
预估：(cost=0.72 rows=74)。而实际扫描了3443行。这也是执行效率偏低的原因。
2.问题分析：
同样去掉limit测试：
select *
from test_t2
where table_schema not in
('performance_schema', 'sys', 'information_schema', 'mysql')
order by id;
这里选择了目标条件索引，查询效率也是比较高的。查询成本：44
对原始limit测试走目标索引：
同样的执行效率和成本。而对比原始默认的执行计划中，cost只有0.72。预估行数只有74。也体现了原始查询选择主键索引的原因。优化器评估order by limit时的返回行数及预估成本过小。
本部分m6米乐安卓版下载的解决方案，通过上一章节的模拟案例来演示。
4.1 改写查询：去掉limit语句
版本< mysql 5.7.33只能使用此方式。
如果查询返回的记录不多时，可以把order by limit放在业务层去做，数据库只负责查询记录（即去掉order by limit语句）。根据上面的测试，则不会出现走错索引问题。
select *
from test_t2
where table_schema not in
('performance_schema', 'sys', 'information_schema', 'mysql')
order by id;
调整后可以顺利使用正确的索引过滤（本次耗时18ms）。
4.2 改写查询：外面单独套一层
版本< mysql 5.7.33只能使用此方式。
也可以利用sql改写，在外面单独套一层，物化内层查询用以消除order by limit 的影响。经过总结有如下改写方式：
1.改写查询，单独执行内部过滤条件。外面增加一层关联并排序取limit。
select *
from test_t2
where id in
(select id
from test_t2
where table_schema not in
('performance_schema', 'sys', 'information_schema', 'mysql'))
order by id limit 2;
执行计划如下：
内层可以使用正确索引完成过滤。外层通过主键索引再做一次关联。
该种改写方法适用于内层返回行数不是太多的场景，如果内层过滤后数据量仍然很多，增加的一次主键关联也会产生不小的性能开销。
对比analyze执行计划可以看到主要开销发生在表关联的部分。返回行数不多的场景执行效率可以保证（本次耗时50ms）。
2.改写查询，增加虚拟行union物化内部查询。外面再套一层。
select *
from (
select id, table_catalog,table_schema,table_name,column_name,ordinal_position,column_default,is_nullable,data_type,character_maximum_length,character_octet_length,
numeric_precision,numeric_scale,datetime_precision,character_set_name,collation_name,column_type,column_key,extra,privileges,column_comment,generation_expression,srs_id
from test_t2
where table_schema not in
('performance_schema', 'sys', 'information_schema', 'mysql')
union all
select 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0) t
where id!=0
order by id limit 2;
增加union的目的是让内层查询物化，不受外层limit的影响。
改写后保证了内部查询单独执行，通过正确的索引去过滤。
分析真实执行计划看
执行效率比第一种增加一次关联的效率更好（本次耗时18ms）。
3.改写查询，增加变量赋值物化内部查询。
还有一种办法可以增加变量赋值的方式物化内部查询。
select *
from (select @id:=0,
id,table_catalog,table_schema,table_name,column_name,ordinal_position,column_default,is_nullable,data_type,character_maximum_length,character_octet_length,
numeric_precision,numeric_scale,datetime_precision,character_set_name,collation_name,column_type,column_key,extra,privileges,column_comment,generation_expression,srs_id
from test_t2
where table_schema not in
('performance_schema', 'sys', 'information_schema', 'mysql')
) t
order by id limit 2;
执行计划如下：
对比真实执行计划看，这部分查询的效率也是相对更高的（本次耗时13ms）。
除了方法1，方法2-3均不会受到内部查询行数的影响。
4.3 关闭优化器参数：
适用于>=5.7.33的环境。
mysql 5.7.33及之后版本提供了一个优化器开关，prefer_ordering_index。主动关闭该优化器开关可以避免走错执行计划的问题。优化器参数 (默认打开，可以根据需要进行关闭)
select @@optimizer_switch\g
*************************** 1. row ***************************
@@optimizer_switch:prefer_ordering_index=on
session级别关闭：
set session optimizer_switch='prefer_ordering_index=off';
测试查询：
select *
from test_t2
where table_schema not in
('performance_schema', 'sys', 'information_schema', 'mysql')
order by id limit 2;
直接使用了正确的索引去查询，避免了性能问题。查询效率也是最高的（本次耗时8ms）。
4.4 利用hint固定索引：
不限版本。
如果查询代码相对固定，也可以利用force index提示，固定查询采用目标的过滤条件索引，避免走错索引问题。
select *
from test_t2 force index(key_table_schema)
where table_schema not in
('performance_schema', 'sys', 'information_schema', 'mysql')
order by id limit 2;
可以固定查询使用正确的索引（本次耗时14ms）。
本案例是一类典型的包含limit的性能问题，通过分析可以利用文中的相应的优化手段，来解决这类性能问题。
优化器在很多情况下都会判断出错选择错误的执行计划，就需要我们总结问题，分析原因并找到m6米乐安卓版下载的解决方案来优化低效查询。
参考文章：