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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 文章目录1. GROUP BY优化2.优化分页查询3.覆盖索引的使用3.1 什么是覆盖索引?3.2 覆盖索引的利弊

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 的记录,其他记录丢弃,查询排序的代价非常大。

EXPLAIN SELECT * FROM student LIMIT 2000000,10;

优化思路:

该方案适用于主键自增的表,可以把 Limit 查询转换成某个位置的查询 。

EXPLAIN SELECT * FROM student WHERE id > 2000000 LIMIT 10;
• 1

3.覆盖索引的使用

3.1 什么是覆盖索引?

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


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


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


举例一:

#删除之前的索引
DROP INDEX idx_age_stuno ON student;
CREATE INDEX idx_age_name ON student (age,NAME);
EXPLAIN SELECT * FROM student WHERE age <> 20;

EXPLAIN SELECT age,NAME FROM student WHERE age <> 20;

举例二:

EXPLAIN SELECT * FROM student WHERE NAME LIKE '%abc';
• 1

EXPLAIN SELECT id,age FROM student WHERE NAME LIKE '%abc';
• 1

3.2 覆盖索引的利弊


好处:


避免Innodb表进行索引的二次查询(回表)


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


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


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


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


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


弊端:


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


相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
相关文章
|
存储 SQL 缓存
mysql覆盖索引详解——like模糊全匹配中使用索引
mysql覆盖索引详解——like模糊全匹配中使用索引
1302 0
mysql覆盖索引详解——like模糊全匹配中使用索引
|
存储 容灾 关系型数据库
Mysql回表与索引覆盖,联合索引问题
Mysql回表与索引覆盖,联合索引问题
191 0
Mysql回表与索引覆盖,联合索引问题
|
存储 关系型数据库 MySQL
MySQL 覆盖索引(Cover Index)
MySQL 覆盖索引(Cover Index)
337 0
MySQL 覆盖索引(Cover Index)
|
SQL 关系型数据库 MySQL
软件测试mysql面试题:什么是覆盖索引和回表?
软件测试mysql面试题:什么是覆盖索引和回表?
205 0
|
关系型数据库 MySQL 索引
MYSQL性能调优05_覆盖索引、索引下推、如何选择合适的索引、Order by与Group by优化、索引设计原则(四)
MYSQL性能调优05_覆盖索引、索引下推、如何选择合适的索引、Order by与Group by优化、索引设计原则(四)
192 0
MYSQL性能调优05_覆盖索引、索引下推、如何选择合适的索引、Order by与Group by优化、索引设计原则(四)
|
关系型数据库 MySQL 索引
MYSQL性能调优05_覆盖索引、索引下推、如何选择合适的索引、Order by与Group by优化、索引设计原则(三)
MYSQL性能调优05_覆盖索引、索引下推、如何选择合适的索引、Order by与Group by优化、索引设计原则(三)
315 0
MYSQL性能调优05_覆盖索引、索引下推、如何选择合适的索引、Order by与Group by优化、索引设计原则(三)
|
SQL 关系型数据库 MySQL
MYSQL性能调优05_覆盖索引、索引下推、如何选择合适的索引、Order by与Group by优化、索引设计原则(二)
MYSQL性能调优05_覆盖索引、索引下推、如何选择合适的索引、Order by与Group by优化、索引设计原则(二)
204 0
MYSQL性能调优05_覆盖索引、索引下推、如何选择合适的索引、Order by与Group by优化、索引设计原则(二)
|
关系型数据库 MySQL 索引
MYSQL性能调优05_覆盖索引、索引下推、如何选择合适的索引、Order by与Group by优化、索引设计原则(一)
MYSQL性能调优05_覆盖索引、索引下推、如何选择合适的索引、Order by与Group by优化、索引设计原则(一)
197 0
MYSQL性能调优05_覆盖索引、索引下推、如何选择合适的索引、Order by与Group by优化、索引设计原则(一)
|
SQL 监控 关系型数据库
MYSQL性能调优05_覆盖索引、索引下推、如何选择合适的索引、Order by与Group by优化、索引设计原则(五)
MYSQL性能调优05_覆盖索引、索引下推、如何选择合适的索引、Order by与Group by优化、索引设计原则(五)
220 0
|
存储 SQL 关系型数据库
MySQL 的覆盖索引与回表
索引覆盖和回表是MySQL的高级知识点,理解掌握它们可以让你写出更高级的SQL。
758 0
MySQL 的覆盖索引与回表

热门文章

最新文章

推荐镜像

更多