Mysql进阶优化篇06——分组查询优化、分页查询优化、覆盖索引

前 言
🍉 作者简介：半旧518，长跑型选手，立志坚持写10年博客，专注于java后端
🍌 专栏简介：mysql基础、进阶，主要讲解mysql数据库sql刷题、进阶知识，包括索引、数据库调优、分库分表等
🌰 文章简介：本文将介绍JOIN语句的底层原理，建议收藏备用。
🍓 相关推荐：

• MySql进阶索引篇01——深度讲解索引的数据结构：B+树
• Mysql进阶索引篇02——InnoDB存储引擎的数据存储结构
• Mysql进阶索引篇03——2个新特性，11+7条设计原则教你创建索引
• Mysql进阶优化篇01——四万字详解数据库性能分析工具(深入、全面、详细，收藏备用)
• Mysql进阶优化篇02——索引失效的10种情况及原理
• Mysql进阶优化篇03——多表查询的优化
• mysql进阶优化篇04——深入JOIN语句的底层原理
• Mysql进阶优化篇05——子查询的优化和排序优化
• 大厂SQL面试真题大全

文章目录

1. GROUP BY优化

group by 使用索引的原则几乎跟 order by 一致 ，group by 即使没有过滤条件用到索引，也可以直接使用索引。

group by 先排序再分组，遵照索引建的最佳左前缀法则

当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置

where 效率高于 having，能写在 where 限定的条件就不要写在 having 中了

减少使用 order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct 这些语句较为耗费 CPU，数据库的 CPU 资源是极其宝贵的。

包含了 order by、group by、distinct 这些查询的语句，where 条件过滤出来的结果集请保持在 1000 行以内，否则 SQL 会很慢。

2.优化分页查询

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见有非常头疼的问题就是 limit 2000000,10，此时需要 MySQL 排序前 2000010 记录，仅仅返回 2000000-2000010 的记录，其他记录丢弃，查询排序的代价非常大。

EXPLAINSELECT*FROM student LIMIT2000000,10;

优化思路：
该方案适用于主键自增的表，可以把 Limit 查询转换成某个位置的查询 。

EXPLAINSELECT*FROM student WHERE id >2000000LIMIT10;

3.覆盖索引的使用

3.1 什么是覆盖索引？

理解方式一：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。 一个索引包含了满足查询结果的数据就叫做覆盖索引。

理解方式二：非聚簇复合索引的一种形式，它包括在查询里的 SELECT、JOIN 和 WHERE 子句用到的所有列（即建索引的字段正好是覆盖查询条件中所涉及的字段）。

简单说就是， 索引列+主键 包含 SELECT 到 FROM 之间查询的列。

举例一：

#删除之前的索引DROPINDEX idx_age_stuno ON student;CREATEINDEX idx_age_name ON student (age,NAME);EXPLAINSELECT*FROM student WHERE age <>20;

EXPLAINSELECT age,NAME FROM student WHERE age <>20;

举例二：

EXPLAINSELECT*FROM student WHERE NAME LIKE'%abc';

EXPLAINSELECT id,age FROM student WHERE NAME LIKE'%abc';

3.2 覆盖索引的利弊

好处:

避免Innodb表进行索引的二次查询（回表）

Innodb 是以聚集索引的顺序来存储的，对于 Innodb 来说，二级索引在叶子节点中所保存的是行的主键信息，如果是用二级索引查询数据，在查找到相应的键值后，还需通过主键进行二次查询才能获取我们真实所需要的数据。

在覆盖索引中，二级索引的键值中可以获取所要的数据，避免了对主键的二次查询，减少了 IO 操作，提升了查询效率。

可以把随机 IO 变成顺序 IO 加快查询效率

由于覆盖索引是按键值的顺序存储的，对于 I/O 密集型的范围查找来说，对比随机从磁盘读取每一行的数据 I/O 要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的 I/O 转变成索引查找的顺序 I/O。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

弊端:

索引字段的维护 总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这是业务 DBA，或者称为业务数据架构师的工作。

工欲善其事，必先利其器”。要想成为工作上的数据库高手，面试时的题霸，独步江湖，就必须拿到一份"武林秘籍"。

我个人强推牛客网：找工作神器|大厂java面经汇总|超全笔试题库

推荐理由：
1.刷题题库，题目特别全面，刷爆笔试再也不担心

链接： 找工作神器|大厂java面经汇总|超全笔试题库
2.超全面试题、成体系、高质量，还有AI模拟面试黑科技

链接： 工作神器|大厂java面经汇总|超全笔试题库
3.超多面经，大厂面经很多

4.内推机会，大厂招聘特别多

链接： 找工作神器|大厂java面经汇总|超全笔试题库
5.大厂真题，直接拿到大厂真实题库，而且和许多大厂都有直接合作，题目通过率高有机会获得大厂内推资格。

链接： 找工作神器|大厂java面经汇总|超全笔试题库