深入MySQL(四):MySQL的SQL查询语句性能优化概述

关于SQL查询语句的优化,有一些一般的优化步骤,本节就介绍一下通用的优化步骤。

一条查询语句是如何执行的

首先,我们如果要明白一条查询语句所运行的过程,这样我们才能针对过程去进行优化。
参考我之前画的一张MySQL基础架构图:

在这里插入图片描述


MySQL中一条查询语句的生命周期大概可以分为

客户端通过连接器连接服务器

在解析器中解析SQL语句

优化器进行优化

执行器执行生成的执行计划

返回结果给客户端

一般而言,执行器执行阶段是最为重要的阶段,也就是说,最耗费性能的阶段。所以我们的优化,其实就是在优化执行器的执行计划。

如何发现与解决慢查询

查询性能低下的原因是因为访问的数据量太大,使得一个查询很慢,那么我们如何发现并且解决慢查询呢。

慢查询日志

MySQL中有一个日志叫做慢查询日志SLOW_QUERY_LOG,它会记录该MySQL中响应时间超过阈值(long_query_time)的语句,慢查询日志默认不开启,并且默认阈值为10,如果是在线上环境的话不建议开启慢查询日志,有调优需要的时候在开启比较合适。

mysql> show variables like \'%slow_query_log%\'; +---------------------+-------------------------------------------------+ | Variable_name | Value | +---------------------+-------------------------------------------------+ | slow_query_log | OFF | | slow_query_log_file | /var/lib/mysql/iZwz9iiwojnmkotgajxjlqZ-slow.log | +---------------------+-------------------------------------------------+ 2 rows in set (0.01 sec) mysql> set global slow_query_log=1; Query OK, 0 rows affected (0.00 sec)

使用set global slow_query_log=1开启慢查询在重启MySQL之后会失效,如果要一直生效的话,那么就需要修改配置文件my.cnf。

slow_query_log_file就可以看到你所存储的慢查询日志的位置,我们首先执行语句:

mysql> select sleep(12); +-----------+ | sleep(12) | +-----------+ | 0 | +-----------+ 1 row in set (12.00 sec)

然后进入/var/lib/mysql/iZwz9iiwojnmkotgajxjlqZ-slow.log

/usr/sbin/mysqld, Version: 8.0.27-0ubuntu0.20.04.1 ((Ubuntu)). started with: Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock Time Id Command Argument # Time: 2022-02-04T08:27:51.234016Z # User@Host: root[root] @ localhost [] Id: 1653 # Query_time: 12.001395 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1 SET timestamp=1643963259; select sleep(12);

我们就可以看到,一条慢查询已经记录到慢查询日志中,然后我们可以根据后面会提到的正常的优化步骤进行逐步优化。

注:因为我本身专注后端,MySQL的慢查询日志这里只是简单使用,其实慢查询日志还有很多其他的参数,同时还有多种MySQL日志分析工具可以协助,等到以后如果有需要的时候,我会专门出一章来讲述MySQL日志分析工具的使用。

通用的优化思路

SQL语句的优化有一些通用的思路,这里一一介绍一下。

尽量不获取不需要的数据

有些时候,查询会请求超过实际需要的数据,然后在应用程序中丢弃多余数据,这样会给MySQL服务器带来额外的负担,并且增加网络开销,损耗应用服务器的CPU和内存资源。
典型的场景:

查询不需要的数据

多表关联时返回全部列

总是取出全部列

重复查询相同的数据

总结而言,我认为其实就是分为两点:

慎用SELECT * FROM

只取出需要的字段

减少MySQL扫描的额外记录行数

对于MySQL的查询语句而言,其返回的数据行和扫描的数据行是不同的,我们可以用一个很简单的例子来说明:
这是一张利用存储过程生成了100w条数据的数据表

在这里插入图片描述


其中索引有这些:

在这里插入图片描述


我们执行一条查询SQL语句:

mysql> EXPLAIN SELECT * from user where account = 12345; +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | 1 | SIMPLE | user | NULL | ref | test | test | 4 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/zzffjd.html