Mysql SQL优化之 limit offset 很大时性能降低

Mysql SQL优化之 limit offset 很大时性能降低(InnoDB引擎)

在 mysql 中,如果select时 offset 很大, 即使相关索引建的挺好, 也会造成慢查询.

如:

索引只能找到主键,要取其他字段,还要用主键逐个查表,判断条件并排序,然后抛弃 offset 个, 留下 offset+1 到 offset+limit 个. 时间被花在了用主键逐个查表取别的字段那里了.

如果能在找主键的时候就抛弃不需要的行,然后需要其他字段的时候再按主键取,就能避免逐条按主键取其他字段的 IO 时间, 从而提高性能.

https://www.jb51.net/article/141933.htm

MySQL 在崩溃恢复时，会遍历打开所有 ibd 文件的 header page 验证数据字典的准确性，如果 MySQL 中包含了大量表，这个校验过程就会比较耗时。 MySQL 下崩溃恢复确实和表数量有关，表总数越大，崩溃恢复时间越长。另外磁盘 IOPS 也会影响崩溃恢复时间，像这里开发库的 HDD IOPS 较低，因此面对大量的表空间，校验速度就非常缓慢。另外一个发现，MySQL 8 下正常启用时居然也会进行表空间校验，而故障恢复时则会额外再进行一次表空间校验，等于校验了 2 遍。不过 MySQL 8.0 里多了一个特性，即表数量超过 5W 时，会启用多线程扫描，加快表空间校验过程。如何跳过校验MySQL 5.7 下有方法可以跳过崩溃恢复时的表空间校验过程嘛？查阅了资料，方法主要有两种：1. 配置 innodb_force_recovery可以使 srv_force_recovery != 0 ，那么 validate = false，即可以跳过表空间校验。实际测试的时候设置 innodb_force_recovery =1，也就是强制恢复跳过坏页，就可以跳过校验，然后重启就是正常启动了。通过这种临时方式可以避免崩溃恢复后非常耗时的表空间校验过程，快速启动 MySQL，个人目前暂时未发现有什么隐患。2. 使用共享表空间替代独立表空间这样就不需要打开 N 个 ibd 文件了，只需要打开一个 ibdata 文件即可，大大节省了校验时间。自从听了姜老师讲过使用共享表空间替代独立表空间解决 drop 大表时性能抖动的原理后，感觉共享表空间在很多业务环境下，反而更有优势。临时冒出另外一种解决想法，即用 GDB 调试崩溃恢复，通过临时修改 validate 变量值让 MySQL 跳过表空间验证过程，然后让 MySQL 正常关闭，重新启动就可以正常启动了。但是实际测试发现，如果以 debug 模式运行，确实可以临时修改 validate 变量，跳过表空间验证过程，但是 debug 模式下代码运行效率大打折扣，反而耗时更长。而以非 debug 模式运行，则无法修改 validate 变量，想法破灭。

水平有限，如有误导请谅解。

最近遇到一个问题，也就是使用分区表进行数据查询/加载的时候比普通表的性能下降了约50%，主要瓶颈出现在CPU，既然是CPU瓶颈理所当然的我们可以采集perf top -a -g和pstack来寻找性能瓶颈所在，同时和普通表进行对比，发现CPU主要耗在函数build_template_field上如下图：

为了和perf top -g -a进行相互印证，我们同时获取了当时的pstack，由于线程较多为了方便获取有用的信息我们通过pt-pmap进行了格式化如下：

格式化后我们提出掉空闲的等待栈，发现大量的如上，这也和perf top -a -g中的表现进行了相互印证。

我们看到这里大量的cpu耗在

对于template来讲，其几乎是和特定的一次的查询进行绑定的，也就是普通的语句至少需要一个template。其结构为row_prebuilt_t，包含查询元组，查询的表，查询用到的索引，事务相关信息，持久化游标，MySQL层查询行的长度，自增信息，ICP相关信息，mysql_row_templ_t结构等信息。

其中mysql_row_templ_t 这个信息就是每个字段一个，主要作用记录的是MySQL层feild信息和Innodb层columns信息的相关属性，用于快速转换一行记录在MySQL层和Innodb层之间转换。为了初始化mysql_row_templ_t 就出现了上面的逻辑，大概逻辑如下：

这里我们看到这里实际上有2层循环，也就是循环套循环（时间复杂度O(M×N)），而循环影响最大的有2个地方：

这里也就是为什么这里会慢的原因。但是template通常不会一个查询进行多次建立，比如一个普通表的大查询，只有在语句第一次进行数据定位之前会进行建立，这就不得不说这是分区表和普通表的对比中一个特殊的地方了。下面描述一下。

假设我们有如下的分区表：

我们使用语句"select * from t where id2=1"，显然id2是二级索引，由于MySQL全部都是local分区的二级索引，因此这里值分别分布在3个分区中，对于这样一个语句在本该是普通表通过上次定位后的位置继续访问（next_same）的时候，通过封装分区表的方法，将其改为了index read再次定位，而我们可以清楚的看到这里是scan next partition，其part=1这是第二个分区了，也就是我们的p1（第一个为0）

这样template需要每个分区（scan next partition）都进行重建，这样就出现了我们上面的问题。这个其实也可以理解，新的分区是新的innodb文件，这样上次定位的持久化游标实际已经没有什么用了，就相当于一次新的表访问。

这里在是否进行template建立还有一个判断如下：

而m_prebuilt->sql_stat_start除了在语句开始的时候设置为true，每次更换分区依旧会设置为true如下：

在我们的故障pstack中还有一个栈如下：

这个栈实际并不完整，但是其中出现了Partition_helper::handle_ordered_index_scan，这个函数实际上和分区表的排序有关，如果我们考虑这样一种情况，对于二级索引select max(id2) from t，那么需要首先访问每个分区获取其中的最大值然后对比每个分区的最大值，得到最终的结果，而MySQL则采用优先队列进行处理，这应该是就是本函数完成的部分功能（没仔细去看）。

其次我们先出现了QUICK_RANGE_SELECT这是范围查询会用到的，那么我们构造如下：

栈：

这里就是因为id2这个字段只保证在分区内部是按照大小排列的但是在整个表来讲，它是无序的，需要额外的处理。

有了这些准备，我们可以构造一个300个字段和25个分区的分区表。测试版本最新8.0.26

然后perf top 观察如下：

这样问题就得到了确认。

这个问题实际上和二级索引相对于分区键的数据离散度有关，但是我们无法控制二级索引的数据，并且索引也是必须使用的。只能通过一些方面尽量避免，当然我也提交了一个BUG，如下：

https://bugs.mysql.com/bug.php?id=104576

不知道是否有办法修复这个问题，比如对于分区表来讲实际上每个分区的字段都是一样的，是否需要每次都重建mysql_row_templ_t.clust_rec_field_no？如果不需要那么问题自解，但是还需要官方确认和验证，毕竟我对分区表了解有限。如下是一些避免的方式，

---