MySQL分析百万深度数据分页优化思路

业务场景

一般情况下，项目开发过程中会需要上报和分析大量的统计数据。一般情况下，分析完后，会在后台显示要执行的操作和产品。这类统计数据量会随着时间的推移慢慢增加，达到数百万、数千万的数据只是时间问题。

瓶颈再次出现

我们创建了users表，并将索引添加到create_time字段。并在表中添加了100万条数据。

我们使用有限分页来查询前5条

查询前10个基本上不需要时间

当我们开始从获取数据时，1秒内，❝❙❙

SQL_NO_CACHE
该关键字的目的是防止 SQL 查询被定向到缓存。

同样的SQL语句，不同的分页条件，性能差异这么大。随着数据量的增加，查询后面的页面的时间会越来越长。

问题分析

返回表格

一般情况下，我们都会为查询频率较高的字段创建索引。索引会提高我们查询的效率。我们上面的语句使用 SELECT * FROM user，但并非所有字段都被索引。从索引文件中查询到符合条件的数据后，还需要从数据文件中查询非索引字段。那么这个过程就称为回到表。

覆盖索引

如果查询的字段恰好已经创建了索引，比如SELECT create_time FROM user，我们查询的字段就是我们创建的索引，那么就不用去此时向数据文件中查询内部时可能不会返回表。这种情况称为覆盖指数。

IO

表返回操作通常是IO操作因为通过索引找到一行数据后，在聚集索引中通过主键或唯一索引找到一行数据。聚集索引一般是数据文件存储在磁盘上，因此在进行表返回操作时需要从磁盘读取数据，而磁盘IO是一个相对慢的操作。

LIMTI 2000.10？

您是否想过LIMIT 2000,10 是否扫描第 1-2000 行？您是否曾经像我一样认为数据是直接从第 2000 行获取的？该表格未扫描或未退回。其实用这种写法，整个过程就是在查询数据。如果无法覆盖索引，则必须将数据返回到表中。

现在您知道为什么当您倒退时需求会放缓！

问题总结

我们现在知道LIMIT在遇到后续查询时性能会更差。 性能不佳的原因是我们需要归还表。既然问题找到了，我们只需要减少返回的表数量就可以提高查询性能。

解决方案

由于索引隐藏会导致数据无法返回到表中，所以我们可以先找到主键ID（主键索引），然后将找到的数据作为临时表然后 JOIN 一张原始表就足够了。这样只需要返回5条查询结果到表中，大大减少了IO操作。

优化前后性能对比

看执行效果：

• 优化前：1.4秒
  
  • 
  • • 
    • 
      

    耗时查询 性能显着提升。这样，如果分页数据很大，就不会像常规限制查询那么慢。