MySQL查询优化终极版(强烈建议收藏)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介: select查询优化一直是日常开发和数据库运维绕不开的一道坎,SQL的查询速度决定了页面的加载速度,进一步决定了客户浏览体验。

select查询优化一直是日常开发和数据库运维绕不开的一道坎,SQL的查询速度决定了页面的加载速度,进一步决定了客户浏览体验。
为了找到MySQL查询优化解决方案,百度了大量技术博客和资料,发现一个很好的技术资源平台-昂焱数据(www.ayshuju.com),该平台包括了接口API、代码工具、技术文档、标准码表、统计数据、网站素材等丰富的技术资源。
在技术文档模块找到了一篇“MySQL查询优化绝世宝典”技术文档,该文档详细介绍了MySQL查询优化的所有场景与方法,并有示例说明。具体目录如下:
1 MySQL查询优化基础
1.1 MySQL索引说明及其选择
1.2 Explain工具使用详解
2 MySQL简单查询优化方法
2.1 覆盖索引
2.2 最左前缀
2.3 索引下推
2.4 避免回表
2.5 强制索引
3 MySQL长字符串索引优化方法
3.1 前缀索引
3.2 倒序存储
3.3 Hash字段
4 MySQL连接查询优化方法
4.1 小表驱动大表
4.2 算法 NLJ与MRR/BKA
5 MySQL数据库行业规范
5.1 MySQL数据库设计规范
5.2 MySQL数据库应用规范
下面根据这篇“MySQL查询优化绝世宝典”技术文档,引用几个比较常见的知识点和大家技术分享一下,共同学习,共同进步。
1.2 Explain工具使用详解
explain是优化SQL语句的利器,explain关键字可以模拟优化器执行SQL语句,分析查询语句或是结构的性能瓶颈。该工具能够解释SQL语句处理情况、表的加载顺序、表是如何连接、以及索引的使用情况。explain参数示例概览如下:
(1) id:一个简单的SQL语句表示
select查询语句序列号,有几个select语句就有几个id序列号,id序列号从1开始顺序递增。
(2)select_type:描述select语句的类型
▲simple:简单查询。查询不包含子查询和union等复合查询。
▲primary:复杂查询中最外层的select查询。
▲subquery:包含在select中的子查询(不在from子句中)
▲derived:包含在from子句中的子查询。mysql会将结果存放在一个临时表中,又称派生表。
▲union:在union中的第二个和随后的select语句。
▲union result:从union临时表中查询结果的select语句。
(3) table:查询的表对象-表名
显示数据库中的表名,但有时也可能是某些阶段执行结果的简称。
(4) partitions:匹配的表的分区
如果查询语句基于表分区,这里会显示查询要访问的分区。
(5) type:表示优化器将要使用的存取方式(性能好➞差:system>const>eq_ref>ref>range>index>all)
▲system:表中只有一行记录,属于特殊的const类型。
▲const:对于主键或唯一索引的where等值查询,索引检索一次就找到结果且最多返回一行数据。因只读取一次,所以速度非常快。
▲eq_ref:主要对于唯一索引检索,一般是两表关联,关联字段为主键或唯一索引,表中只有一条记录相匹配。
▲ref:常用于多表关联,针对非唯一索引或非主键索引,返回匹配某个值的所有行。
▲range:表示检索使用了索引范围查询,一般where条件中会出现<>、>、>=、<、<=、is null、between、in等范围查询。
▲index:表示全索引扫描,会遍历索引树B+Tree,只扫描索引不扫描数据。
▲all:表示全表扫描,该类型查询性能最差,all是从硬盘中检索,遍历全表查找匹配的行。
(6) possible_keys:可以使用的索引,如果没有索引可用返回Null;也可能显示多个索引可以使用的索引,如果没有索引可用返回Null;也可能显示多个索引。
(7) key:优化器最终选择的索引。如果没有索引可用,返回null
优化器在possible_keys范围内最终选择的索引。如果没有索引可用,返回null。
如果对优化器选择的索引不满意,可以用use index、ignore index、force index等指定索引。
(8) key_len:被选择的key索引的长度,如果没有使用索引,返回null
表示索引使用的字节数,一般情况下,索引长度越长精度越高,但效率偏低;长度越短,精度越低,但效率高。如果键为null,则长度为null。在不损失精确性的情况下,长度越短越好。key_len长度计算公式如下:
▲int not null(key_len):4(字节)
▲int null(key_len):4(字节)+1(null标记位)=5
▲varchar(n) null(key_len):n*(utf8=3,gbk=2,latin1=1)+1(NULL标记位)+2(变长字段)
▲varchar(n) not null(key_len):n*(utf8=3,gbk=2,latin1=1)+2(变长字段)
▲char(n) null(key_len):n*(utf8=3,gbk=2,latin1=1)+1(NULL标记位)
▲char(n) not null(key_len):n*(utf8=3,gbk=2,latin1=1)
▲datetime(key_len):8(字节)
(9) ref:索引中被使用的列
显示where后索引查找值所用到的列或常量,常见有const常量、func函数、null、字段名等。例如const表示检索来自常数值,如name='小名';func表示检查来自函数表达式。
注意:如果where后条件是组合索引,但没有按照组合索引顺序使用,则ref为null。
(10) rows:要得到最终记录,而要扫描经过的记录数,该值是预估值
例如要查询10条记录,结果扫描了100行才把这10条记录查找出来,那么rows=100。rows数值越大,说明查询效率越低。
(11) filtered:该值只有where后条件字段建立索引,才准确;公式=最终记录/扫描记录*100%表示存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数据的百分比。
(12) extra:获取数据过程中的额外信息,有利于SQL优化
▲Using where
表示此select查询语句where后过滤条件字段无索引或者索引失效。
常见的优化方法是在where后字段上加上索引。
▲Using filesort
MySQL需要额外的一次传递,以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序,并按排序顺序检索行。
最常见的是order by后面的字段没有建立索引,就会触发filesort。优化方法是在order by后字段建立索引,避免每次查询都全量排序。
▲Using temporary
表示由于排序没有走索引、使用union、子查询连接查询、使用某些视图等,一般出现在多张表的数据需要排序的情况下。例如有ORDER BY子句和一个不同的GROUP BY子句, 或者如果ORDER BY或GROUP BY中的字段都来自其他的表而非连接顺序中的第一个表的话, 就会创建一个临时表了。
最常见的是left join多表关联,order by没有使用主表字段排序,而使用了关联表字段排序,所以当多表关联时,尽量使用主表字段进行order by。
▲Using index
表示直接访问索引就足够获取到所需要的数据,不需要通过索引回表;是查询性能高的一个表现。
▲Using join buffer (Block Nested Loop)
此类select查询语句需要进一步优化。常见的是两个表关联,关联字段都没有建立索引。常见的优化方案是在被驱动表的关联字段上建立索引。
▲Impossible where
表示where后条件是永假条件,导致select语句无法选择任何一行数据。常见的例子如select * from tbl_student where 1<0;
2.1 覆盖索引
覆盖索引满足的条件是select后所有字段和where后所有字段都是索引字段。若是组合索引,必须是select后字段+where后字段=组合索引字段,组合索引字段顺序不受限制。覆盖索引还有一点要注意,就是不能使用select *来查询,这样就无法形成覆盖索引。
这样做的目的是避免回表查询,因为查询的数据可直接在索引中找到,提高了查询效率。一般覆盖索引的标志是explain的Extra属性为Using index。下面是示例试验表结构及数据:
▲(username,age,score)没有建立索引
explain select score,age from tbl_stu where username='cc';
image.png

▲(username,age,score)建立了索引
alter table tbl_stu add index ind_ua(username,age,score);
explain select score,age from tbl_stu where username='cc';
image.png

2.2 最左前缀
最左前缀原则顾名思义,强调select查询语句的where后面各个条件字段要按照最左组合索引字段(即组合索引第一个字段)必使用原则。也就是说where条件字段,只有使用了组合索引的第一个字段,整个组合索引才会生效。组合索引字段使用的先后顺序不受限制。
例如tbl_demo表的组合索引(a,b,c),只有在where后使用了a字段,才可以使用b字段或c字段。使用的先后顺序不影响组合索引的有效性。组合索引(a,b,c)生效的组合有以下四个:
① select * from tbl_demo where a=xx;
② select * from tbl_demo where a=xx and b=xx;
③ select * from tbl_demo where a=xx and c=xx;
④ select * from tbl_demo where a=xx and b=xx and c=xx;
使用最左前缀原则要注意如后几个方面会导致组合索引失效或影响性能。一是组合索引字段遇到范围查询(>、<、between、like)就停止匹配,影响性能;二是使用组合索引第一个字段like时%在最前面会导致组合索引失效;三是组合索引第一个字段参与表达式计算会导致失效;四是where后组合索引第一个字段与条件值存在“隐式转换”,该字段类型与条件值类型不一致导致失效。
时间有限,如果需要可以自行到昂焱数据官网(www.ayshuju.com)上下载。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
2月前
|
缓存 关系型数据库 MySQL
MySQL慢查询优化策略
MySQL慢查询优化是一个复杂的过程,需要根据具体的应用场景和数据特点进行。以上策略是提升数据库查询性能的有效途径,但最关键的是对系统进行持续的监控和分析,及时发现并解决性能瓶颈。通过实践这些策略,你可以显著提高MySQL数据库的性能,为用户提供更快的响应时间和更好的体验。
78 10
|
6月前
|
关系型数据库 MySQL 数据库
MySQL索引和查询优化
MySQL索引和查询优化
62 1
|
6月前
|
SQL 缓存 关系型数据库
MySQL技能完整学习列表6、查询优化——3、查询缓存——4、SQL优化技巧
MySQL技能完整学习列表6、查询优化——3、查询缓存——4、SQL优化技巧
113 0
|
7天前
|
缓存 关系型数据库 MySQL
MySQL执行计划选择策略:揭秘查询优化的艺术
【10月更文挑战第15天】 在数据库性能优化中,选择最优的执行计划是提升查询效率的关键。MySQL作为一个强大的关系型数据库管理系统,提供了复杂的查询优化器来生成执行计划。本文将深入探讨如何选择合适的执行计划,以及为什么某些计划更优。
21 2
|
27天前
|
缓存 关系型数据库 MySQL
MySQL慢查询优化
通过上述方法综合施策,可以显著提升MySQL数据库的查询性能,降低延迟,增强应用系统的整体响应能力。实践中,优化工作是一个持续迭代的过程,需要结合具体应用场景不断调整策略。
104 1
|
5月前
|
SQL 关系型数据库 MySQL
从理论到实践,Mysql查询优化剖析(联表查询)
从理论到实践,Mysql查询优化剖析(联表查询)
182 0
|
2月前
|
存储 缓存 关系型数据库
MySQL 查询优化方法
在数据库应用中,高效的查询性能至关重要。本文探讨了常用的 MySQL 查询优化方法,包括索引优化(选择合适的索引字段、复合索引、定期维护索引)、查询语句优化(避免全表扫描、限制返回行数、避免使用不必要的函数)、表结构优化(选择合适的数据类型、分区表、定期清理无用数据)及数据库配置优化(调整缓存大小、优化存储引擎参数)。通过这些方法,可以显著提高 MySQL 的查询性能,为应用程序提供更好的用户体验。
155 4
|
6月前
|
缓存 关系型数据库 MySQL
MySQL查询优化:提速查询效率的13大秘籍(合理使用索引合并、优化配置参数、使用分区优化性能、避免不必要的排序和group by操作)(下)
MySQL查询优化:提速查询效率的13大秘籍(合理使用索引合并、优化配置参数、使用分区优化性能、避免不必要的排序和group by操作)(下)
265 0
|
6月前
|
缓存 关系型数据库 MySQL
MySQL 查询优化:提速查询效率的13大秘籍(索引设计、查询优化、缓存策略、子查询优化以及定期表分析和优化)(中)
MySQL 查询优化:提速查询效率的13大秘籍(索引设计、查询优化、缓存策略、子查询优化以及定期表分析和优化)(中)
926 0
|
4月前
|
SQL 关系型数据库 MySQL
Mysql 中 not in 的查询优化
Mysql 中 not in 的查询优化
121 4