侧边栏壁纸
  • 累计撰写 244 篇文章
  • 累计创建 16 个标签
  • 累计收到 0 条评论
隐藏侧边栏

带分页、排序和分组统计的查询如何使用索引进行优化

kaixindeken
2021-05-03 / 0 评论 / 0 点赞 / 115 阅读 / 6,965 字

我们在进行日常 SQL 查询时,经常会使用分页(LIMIT)、排序(ORDER BY)等额外约束对结果集进行过滤,以便返回符合我们预期的结果:

// 限定:
SELECT * FROM demo WHERE a > 100 LIMIT 10;
// 排序:
SELECT * FROM demo WHERE a IN (100, 1, 1000) ORDER BY a ASC;

分页查询

LIMIT 查询用于限定 SQL 查询返回的记录数:

SELECT * FROM demo WHERE a > 1000 LIMIT 10;
SELECT * FROM demo WHERE a > 1000 LIMIT 0, 10;
SELECT * FROM demo WHERE a > 1000 LIMIT 10 OFFSET 0;

以上 SQL 语句是等价的,都是从 1001 条记录开始,返回 10 条符合 a > 1000 条件的记录:

1.jpeg

通过 LIMIT 优化未命中索引的查询

如果 where 条件中的字段可以命中索引,使用 LIMIT 限定可以非常高效地从已经排好序的 B+ 树索引数据返回满足 where 条件之后的限定条数数据,即便是 where 条件中的字段没有命中索引,通过 LIMIT 也可以提升性能,比如下面这条 SQL 查询:

1.jpeg

由于 c 对应数据列未设置索引,直接这么查询会进行全表扫描,但是由于我们知道 c 字段值在全表是唯一的,因此可以通过 LIMIT 限定匹配到第一条记录后就立即返回:

1.jpeg

同样是全表扫描,第一种情况需要扫描全表 100 万行记录,第二种只需要扫描 10 万行,显然性能更优。

关于 LIMIT 分页时偏移量过大引起的性能问题优化

不过,即便是 where 语句命中了索引,如果数据量很大的话,LIMIT 在分页查询中有一个非常常见的性能问题,就是随着分页偏移量越来越大,分页查询的性能也越来越低:

1.jpeg

因为偏移量是相对于 a = 1001 这个匹配到的第一个索引值来的,如果偏移量很大,就又相当于进行全表扫描了,所以这也是为什么你会看到很多大型网站只会提供前面几十页的真实分页,后面的分页查询都是不可用的。

对于这种分页查询,一个优化方式是改变 where 语句中的偏移量起始值,比如对于上面这个 查询,可以这样优化:

1.jpeg

这样一来,我们就又可以通过 B+ 索引树快速定位到 501001 这个索引值位置,然后在其基础上往后偏移 10 条记录,进行高效的查询了!

排序查询

然后我们来看看排序,排序操作通常是在数据库查询返回的结果集上进行的,如果结果集不大,可以直接在内存中通过快排、归并等排序算法完成,如果数据集很大,内存不能完全加载,还需要借助磁盘空间完成排序操作(这种排序被称作文件排序,显然,涉及到磁盘 IO 的操作性能肯定是不行的),最后把排序结果返回给调用的客户端。

当然,如果 where 条件语句命中了索引,由于 B+ 树索引数据本身已经做好了排序,则可以高效返回排序结果,这里的排序比较规则和索引排序一样,依然遵循设置的字符集和字符集比较规则:

SELECT * FROM demo WHERE a BETWEEN 100000 AND 100010 ORDER BY a ASC;

注:对于升序排序,直接在 B+ 树索引数据中从左往右读取数据返回即可,无需再在内存中对结果集排序。

对于使用联合索引的字段而言,如果要设定多个排序字段,需要遵循索引列的顺序设置排序字段顺序,这样,才可以使用索引的排序:

SELECT * FROM demo WHERE b BETWEEN 100000 AND 100010 ORDER b, c DESC;

注:对于降序排序,直接在 B+ 树索引数据中从右往左读取数据返回即可,无需再在内存中对结果集排序。

不过,对于结果集很小的查询,排序本身的开销可以忽略。

不能使用索引排序的几种排序查询

以上是可以直接使用索引排序的几种排序查询,下面我们来看一下不能使用索引排序的场景。其实都很好理解。

排序字段未出现在 where 语句中

当然如果排序字段没有出现在 where 条件语句中,或者压根没有 where 条件,是无法使用索引已有的排序的:

SELECT * FROM demo ORDER BY a DESC;
联合索引字段混合使用升序和降序

如果联合索引混合使用了升序和降序,则不能直接使用索引排序:

SELECT * FROM demo WHERE b BETWEEN 100000 AND 100010 ORDER BY b DESC, c ASC;
包含非同一个索引的列排序

对于排序字段中混合包含不同索引列的查询,也能使用索引排序:

SELECT * FROM demo WHERE a BETWEEN 100000 AND 100010 ORDER BY a, b ASC;
排序包含函数调用

最后,对于使用函数的表达式,也通通不能使用索引排序:

SELECT * FROM demo WHERE d LIKE 'xxx%' ORDER BY upper(`d`) DESC;

这一点,不仅限于排序,如果是 where 语句中调用了函数,则也不能使用索引:

SELECT * FROM demo WHERE lower(`d`) = 'c51ce410c124a10e0db5e4b97fc2af39';

你可以通过查询对比或者通过 explain 语句予以验证:

1.jpeg

与 LIMIT 结合使用优化实例

如果返回的排序结果集数量很大,往往会结合 LIMIT 语句进行分页展示,这个场景想必大家都很熟悉,我们在开发 Web 应用的时候分页功能通常都是按照指定条件升/降序后分页展示的,不管是搜索引擎、电商、社交媒体、新闻资讯、还是音视频网站,所有的分页功能概莫能外。

SELECT `id`, `title`, `summary`, `image`, `chapter_id`, `book_id`, `created_by`, `views`, `votes`, `created_at` 
FROM `posts` 
WHERE `draft` <> 0
ORDER BY `created_at` DESC 
LIMIT 15 OFFSET 0;

虽然 draft 列上设置了索引,而且加上 LIMIT 限定,但是由于排序字段 created_at 不在 draft 索引数据中,所以其实还是需要针对 draft 索引进行近似索引树扫描,将非草稿状态文章主键 ID 返回去主键索引进行回表(在回表的时候,我们指定了要查询的字段,尤其要屏蔽文章内容这种长度很长的文本数据,以便降低回表的开销),再将返回的结果集放到内存按照 created_at 作降序排序,最后提取排序后结果集前 15 条记录返回(不是第一页的话从 OFFSET 指定的位置开始提取 15 条记录返回),如果结果集很大,可能还需要借助磁盘文件完成排序。

所以说涉及到复杂条件和排序的分页查询底层操作还是很麻烦的,有很大几率会触发全表扫描或者全索引树扫描,我们还可以在 explain 语句返回结果的 Extra 字段中查看排序是否会用到文件排序(filesort):

1.jpeg

如果想要优化上述排序查询不使用文件排序,而是基于已有的数据库字段索引,可以删除 draft 索引并设置一个包含 created_at 和 draft 的联合索引,并且 created_at 放到 draft 前面:

ALTER TABLE `posts` DROP INDEX `draft_index`,
ADD INDEX `created_at_draft_index` (`created_at`, `draft`) USING BTREE;

然后修改上述 SQL 查询语句如下:

SELECT `id`, `title`, `summary`, `image`, `chapter_id`, `book_id`, 
`created_by`, `views`, `votes`, `created_at` 
FROM `posts` 
WHERE `created_at` > '2015-08-08 00:00:00' AND `draft` <> 0 
ORDER BY `created_at` DESC 
LIMIT 15 OFFSET 0;

再次对上述查询运行 explain 语句,返回结果如下:

1.jpeg

在 Extra 字段中就不再包含 filesort 了,这一次,由于 created_at 和 draft 字段位于同一个联合索引,所以查询的时候,只需要在相应索引数据中找到满足这两个条件的结果集,然后从根据 created_at 从后往前读取 15 条记录即可,再根据返回的主键 ID 去簇拥索引回表拿到结果集直接返回,不再需要额外的排序操作。

分组统计

基本使用

除了分页和排序之外,有时候,我们还需要对特定的数据库查询结果进行分组,以便统计特定分组的数据信息,比如我们的应用有个站点访问记录表 site_visits:

CREATE TABLE `site_visits` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL DEFAULT '0',
  `path` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `method` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ip` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_agent` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `referer` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL,
  PRIMARY KEY (`id`),
  KEY `site_visits_user_id_index` (`user_id`),
  KEY `site_visits_created_at_index` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

如果我们想要分组统计每个用户的访问量,可以使用如下分组查询实现:

SELECT user_id, count(*) as visits FROM `site_visits` GROUP BY user_id;

你还可以进入筛选访问量大于等于 100 的分组统计结果:

SELECT user_id, count(*) as visits FROM `site_visits` GROUP BY user_id HAVING visits >= 100;

当然,如果需要的话,还可以在分组查询中应用限定(LIMIT)和排序(ORDER BY)。

底层原理

我们来看看分组查询的底层原理。

对于上面这个查询,由于 user_id 列上设置了索引,对于 B+ 树而言,叶子节点中的索引数据都是经过升序排序的(这一点已经反复强调了),而分组就是把字段值相同的记录归置到一起进行统计,所以当我们通过 user_id 字段进行分组时,只需要去 user_id 对应的索引树拿到结果返回就可以了,不需要再进行额外的分组操作,相同 user_id 值对应的记录已经连在一起了。

而对于分组字段没有设置索引的场景,比如下面这个根据 ip 地址分组统计的查询:

SELECT ip, count(*) as visits FROM `site_visits` GROUP BY ip;

则需要进行全表扫描然后在内存中对结果集进行分组再返回给客户端,如果数据量很大还需要借助磁盘完成分组统计,这个时候又要涉及到文件排序(filesort)了:

1.jpeg

你可能注意到 explain 语句分析结果 Extra 字段里面还有一个 Using temporary,其含义是 MySQL 会创建临时表(使用的存储引擎是 memory)保存数据进行后续分组统计处理。不管是临时表还是文件排序,都涉及到磁盘 IO,所以性能自然可想而知了。

注意事项

另外,使用联合索引字段进行分组统计的时候,索引是否命中依然遵循最左前缀原则,并且在设置多个字段的分组顺序时,需要与联合索引的字段顺序一一对应才能最大化利用索引对查询性能的提升。

我们也可以在分组查询时指定 where 子句和排序、限定条件,在字段命中索引的情况下,MySQL 优化器会根据预测的扫描记录行数来决定使用哪个索引(开销最小的那个执行计划),具体情况可以通过 explain 语句结合前面全值匹配、范围匹配和分页、排序查询索引使用进行分析,这里就不一一展开介绍了,有了前面的基础,只需要做排列组合进行推演即可。

需要注意的是分组统计会在 where 条件返回结果集之上进行,如果分组字段包含在 where 子句中,并且 where 条件命中的索引也是这个字段,则不需要额外分组,否则需要在查询结果集上进行分组;而排序和限定会在分组统计结果集之上进行,并且 ORDER BY 字段必须出现在 GROUP BY 字段中,否则会报错。

0

评论区