MYSQL数据库查询优化

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介: MySQL 中的 `EXPLAIN` 语句用于查看 SQL 查询的执行计划,帮助分析查询如何使用索引以及优化查询性能。`EXPLAIN` 输出包括多个列,例如 `id`、`select_type`、`table`、`type`、`possible_keys`、`key`、`key_len`、`ref`、`rows` 和 `Extra`,这些列提供了关于如何处理查询的详细信息。

explain

查看一个这些SQL语句的执行计划

Json 名称

意义

id

select_id

标识符SELECT

select_type

没有

类型SELECT

table

table_name

输出行的表

partitions

partitions

匹配的分区

type

access_type

联接类型

possible_keys

possible_keys

可以选择的索引

key

key

实际选择的索引

key_len

key_length

所选键的长度

ref

ref

与索引比较的列

rows

rows

要检查的行的估计值

filtered

filtered

按表条件筛选的行的百分比

Extra

没有

其他信息

一、 id

我的理解是SQL执行的顺序的标识,SQL从大到小的执行

1. id相同时,执行顺序由上至下

2. 如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行

3.id如果相同,可以认为是一组,从上往下顺序执行;在所有组中,id值越大,优先级越高,越先执行

二、select_type

     表示查询中每个select子句的类型

(1) SIMPLE(简单SELECT,不使用UNION或子查询等)

(2) PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)

(3) UNION(UNION中的第二个或后面的SELECT语句)

(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句,取决于外面的查询)

(5) UNION RESULT(UNION的结果)

(6) SUBQUERY(子查询中的第一个SELECT)

(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT,取决于外面的查询)

(8) DERIVED(派生表的SELECT, FROM子句的子查询)

(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存,必须重新评估外链接的第一行)

三、table

显示这一行的数据是关于哪张表的

四、type

表示MySQL在表中找到所需行的方式,又称“访问类型”。

常用的类型有: ALL, index,  range, ref, eq_ref, const, system, NULL(从左到右,性能从差到好)

ALL:Full Table Scan, MySQL将遍历全表以找到匹配的行

index: Full Index Scan,index与ALL区别为index类型只遍历索引树

range:只检索给定范围的行,使用一个索引来选择行

ref: 表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值

eq_ref: 类似ref,区别就在使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配,简单来说,就是多表连接中使用primary key或者 unique key作为关联条件

const、system: 当MySQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。如将主键置于where列表中,MySQL就能将该查询转换为一个常量,system是const类型的特例,当查询的表只有一行的情况下,使用system

NULL: MySQL在优化过程中分解语句,执行时甚至不用访问表或索引,例如从一个索引列里选取最小值可以通过单独索引查找完成。

五、possible_keys

指出MySQL能使用哪个索引在表中找到记录,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用

该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。

如果该列是NULL,则没有相关的索引。在这种情况下,可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样,创造一个适当的索引并且再次用EXPLAIN检查查询

六、Key

key列显示MySQL实际决定使用的键(索引)

如果没有选择索引,键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

七、key_len

表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度(key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的)

不损失精确性的情况下,长度越短越好

八、ref

表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值

九、rows

表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数

十、Extra

该列包含MySQL解决查询的详细信息,有以下几种情况:

Using where:列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,这发生在对表的全部的请求列都是同一个索引的部分的时候,表示mysql服务器将在存储引擎检索行后再进行过滤

Using temporary:表示MySQL需要使用临时表来存储结果集,常见于排序和分组查询

Using filesort:MySQL中无法利用索引完成的排序操作称为“文件排序”

Using join buffer:改值强调了在获取连接条件时没有使用索引,并且需要连接缓冲区来存储中间结果。如果出现了这个值,那应该注意,根据查询的具体情况可能需要添加索引来改进能。

Impossible where:这个值强调了where语句会导致没有符合条件的行。

Select tables optimized away:这个值意味着仅通过使用索引,优化器可能仅从聚合函数结果中返回一行

优化方式

1、索引

为搜索字段创建索引(考虑:查询多还是增删多?)

尽量建立组合索引并注意组合索引的创建顺序,按照顺序组织查询条件,尽量将筛选粒度大的查询条件放到最左边

SELECT 语句尽量不要使用*

order by、group by语句尽量使用索引

2、分库分表分区

分库,可以按照业务分库,分流数据库并发压力,使数据库表更加有条理性,查询库和系统库(增删改比较频繁的表)分开

分表,索引适合应对百万级别的数据量,千万级别数据量使用的好,勉强也能凑合,如果是上亿级别的数据量,索引就无能为力了,因为单索引文件可能就已经上百兆或者更多了,那么,轮到我们的分表分区登场了

分表的方法有很多种

a、如果这个业务是有流程的,那么我们通常会设计一个历史表或者归档表,用来存放历史数据,这样能保证实时数据效率比较高

b、针对某一张大表,可以根据查询条件分成多张表,比如时间,我们可以将半个月或者10天的数据放到一张表里(看具体数据量,个人认为3000W是个上限,最好控制到百万级别),每过10天,我们就自动创建一张数据库表,然后将数据插入,如此,按照时间查询,就要先定位去那种表中去取数,这样,效率能够得到大幅度提升,当然,这么解决也有问题,比如跨表,需要union多张表,而且跨表没法支持索引

分区,分区的实现道理和分表一样,也是将相应规则的数据放在一起,唯一不同的是分区你只需要设定好分区规则,插入的数据会被自动插入到指定的区里,当然查询的时候也能很快查询到需要区,相当于是分表对外透明了,出现跨表数据库自动帮我们合并做了处理,使用起来比分表更加方便,但是分区也有自己的问题,每一个数据库表的并发访问是有上限的,也就是说,分表能够抗高并发,而分区不能,如何选择,要考虑实际情况

3、数据库引擎

mysql比较常用的数据库引擎有两种,一种是innodb、一种是myisam

mysiam查询速度快,是因为他的数据存储结构、索引存储结构和innodb不一样的,mysiam的索引结构是在内存中存的

当然,mysiam也有弱点,那就是他是表级锁,而innodb是行级锁,所以,mysiam适用于一次插入,多次查询的表,或者是读写分离中的读库中的表,而对于修改插入删除操作比较频繁的表,就很不合适了

4、预处理

a、实时数据(当天数据),通过对对业务的抽象,可以放在缓存里面,提升系统运行效率

b、历史数据,大数据表历史数据且有表关联,通过常规sql难以优化,但是该数据通常有个共性,就是第二天去查询前一天的数据做分析报表,也就是说对时效性要求不高,这种情况的解决方案是预处理,做法是将这些复杂表关联sql写成个定时任务在半夜执行,将执行的结果存入到一张结果表中,第二天直接查询结果表,如此,效率能得到十分明显提升

c、和b类似,可以将表关联结果存入solr或者elastisearch中,以此提升效率,目前我们的项目就是如此处理

5、mysql  like查询

like  “%str%” 不支持索引, "str%"号是支持索引的

可以在like字段加前缀 like 'prefix%?%'

6、读写分离

在数据库并发大的情况下,最好的做法就是进行横向扩展,增加机器,以提升抗并发能力,而且还兼有数据备份功能

7、模糊查询时总数据量查询较慢

如果查询字段属于外键,用小表筛选掉不需要的数据

设计优化

表参数row_format设计:

row_format=fixed,每条记录所占的字节一样,优点是读取快,缺点是浪费额外一部分空间

row_dynamic=dynamic,每条记录所占用的字节是动态的,优点是节省空间,缺点是增加读取的开销

设计中间表,一般针对统计分析功能,或者实时性不高的需求

为减少关联查询,创建合理的冗余字段

对于字段太多的大表,考虑拆表

对于表中经常不被使用的字段,考虑拆表

每张表都有一个主键,而且主键类型最好是int类型,建议自增主键

LIMIT优化

如果预计SELECT 语句的查询结果是一条,最好使用LIMIT 1,可以停止全表扫描

SELECT * FROM S_USER WHERE USERNAME='chenyanbin' LIMIT 1;

处理分页会使用到LIMIT,当翻页到非常靠后的页面的时候,偏移量会非常大,这时LIMIT的效率会非常差

1.可以使用between优化分页,需要注意的是between要在唯一索引上分页

2.可以使用做子查询先把数据筛选出来

SELECT t1.* FROM 表 1 as t1, (select id from 表 1 where 条件 LIMIT 100000,20 ) as t2 where t1.id=t2.id

其他优化

JOIN两张表的关联字段最好都建立索引,而且最好字段类型是一样的

WHERE条件中尽量不要使用1=1、not in 语句(建议使用not exists)

不用MySQL内置的函数,因为内置函数不会建立查询缓存

合理利用慢查询日志、explain执行计划查询、show profile查询SQL执行的资源使用情况

则子查询表大的用exists,子查询表小的用in

小表为主表连接大表

百万级数据组合索引比单个索引性能高

1.对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描

3.应尽量避免在 where 子句中使用 != 或 <> 操作符,否则将引擎放弃使用索引而进行全表扫描

4.应尽量避免在 where 子句中使用 or 来连接条件,如果一个字段有索引,一个字段没有索引,将导致引擎放弃使用索引而进行全表扫描

5.in 和 not in 也要慎用,否则会导致全表扫描

6.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描

在 MySQL 中有两个存储引擎 MyISAM 和 InnoDB,每个引擎都有利有弊。

MyISAM 适合于一些需要大量查询的应用,但其对于有大量写操作并不是很好。甚至你只是需要update一个字段,整个表都会被锁起来,而别的进程,就算是读进程都无法操作直到读操作完成。另外,MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。

image.png

索引建立规范:

表主键、外键一定有索引

数据量大的应该有索引

经常做连接查询的表应该在连接字段建立索引

经常在where约束之后的字段,尤其表的字段非常多的情况下要加索引

索引最好建立在小字段上,大文本或者超长字段均不适合加索引

频繁对数据进行操作的表不适合建立索引

无用的索引应尽早删除

image.png

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
1月前
|
关系型数据库 MySQL 数据库
MySQL索引和查询优化
MySQL索引和查询优化
43 1
|
1月前
|
SQL 缓存 关系型数据库
MySQL技能完整学习列表6、查询优化——3、查询缓存——4、SQL优化技巧
MySQL技能完整学习列表6、查询优化——3、查询缓存——4、SQL优化技巧
73 0
|
1月前
|
存储 SQL 关系型数据库
三高Mysql - Mysql索引和查询优化讲解(偏理论部分)
三高Mysql - Mysql索引和查询优化讲解(偏理论部分)
58 0
|
4天前
|
SQL 关系型数据库 MySQL
从理论到实践,Mysql查询优化剖析(联表查询)
从理论到实践,Mysql查询优化剖析(联表查询)
14 0
|
1月前
|
缓存 关系型数据库 MySQL
MySQL查询优化:提速查询效率的13大秘籍(合理使用索引合并、优化配置参数、使用分区优化性能、避免不必要的排序和group by操作)(下)
MySQL查询优化:提速查询效率的13大秘籍(合理使用索引合并、优化配置参数、使用分区优化性能、避免不必要的排序和group by操作)(下)
|
1月前
|
缓存 关系型数据库 MySQL
MySQL 查询优化:提速查询效率的13大秘籍(索引设计、查询优化、缓存策略、子查询优化以及定期表分析和优化)(中)
MySQL 查询优化:提速查询效率的13大秘籍(索引设计、查询优化、缓存策略、子查询优化以及定期表分析和优化)(中)
|
12天前
|
存储 关系型数据库 MySQL
Mysql查询优化
Mysql查询优化
12 1
|
1月前
|
SQL 存储 数据库
SQL数据库查询优化技巧
【5月更文挑战第6天】本文介绍了7个SQL数据库查询优化技巧,包括选择合适索引、避免`SELECT *`、使用JOIN代替子查询、优化WHERE子句、使用LIMIT、分析查询计划和定期维护数据库。通过这些方法,开发者可以提升查询效率,改善系统性能。
|
1月前
|
SQL 缓存 PHP
【PHP开发专栏】PHP数据库查询优化技巧
【4月更文挑战第29天】本文探讨了PHP数据库查询优化技巧,包括数据库设计的规范化与反规范化,合理使用索引,优化查询逻辑,以及避免SELECT *。在SQL查询优化中,利用EXPLAIN分析查询、优化JOIN操作和子查询,以及改进WHERE条件。PHP层面的优化涉及预处理语句、缓存查询结果、分页查询优化和异步处理。此外,还提到了高级技术如数据库分区、读写分离和分布式数据库。通过这些方法,开发者能提升查询效率,优化应用性能和用户体验。
|
1月前
|
缓存 关系型数据库 MySQL
【专栏】提升MySQL性能和高可用性的策略,包括索引优化、查询优化和事务管理
【4月更文挑战第27天】本文探讨了提升MySQL性能和高可用性的策略,包括索引优化、查询优化和事务管理。通过合理使用B-Tree和哈希索引,避免过度索引,以及优化查询语句和利用查询缓存,可以改善性能。事务管理中,应减小事务大小并及时提交,以保持系统效率。主从或双主复制可增强高可用性。综合运用这些方法,并根据实际需求调整,是优化MySQL的关键。

相关产品

  • 云数据库 RDS MySQL 版
  • 推荐镜像

    更多