42 张图带你撸完 MySQL 优化 上

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 42 张图带你撸完 MySQL 优化 上

欢迎你阅读最新一期的文章,这篇文章是 MySQL 调优的汇总版,我加了一下日常开发过程中的调优经验,希望对各位小伙伴们有所帮助。下面开始正文。

一般传统互联网公司很少接触到 SQL 优化问题,其原因是数据量小,大部分厂商的数据库性能能够满足日常的业务需求,所以不需要进行 SQL 优化,但是随着应用程序的不断变大,数据量的激增,数据库自身的性能跟不上了,此时就需要从 SQL 自身角度来进行优化,这也是我们这篇文章所讨论的。

SQL 优化步骤

当面对一个需要优化的 SQL 时,我们有哪几种排查思路呢?

通过 show status 命令了解 SQL 执行次数

首先,我们可以使用 show status 命令查看服务器状态信息。show status 命令会显示每个服务器变量 variable_name 和 value,状态变量是只读的。如果使用 SQL 命令,可以使用 like 或者 where 条件来限制结果。like 可以对变量名做标准模式匹配。

0d6549df5ce0e3d71d0abe57966f51e.jpg

图我没有截全,下面还有很多变量,读者可以自己尝试一下。也可以在操作系统上使用 mysqladmin extended-status 命令来获取这些消息。

但是我执行 mysqladmin extended-status 后,出现这个错误。

b9345a7e3fcf27294e492a366086f80.jpg

应该是我没有输入密码的原因,使用 mysqladmin -P3306 -uroot -p -h127.0.0.1 -r -i 1 extended-status 后,问题解决。

这里需要注意一下 show status 命令中可以添加统计结果的级别,这个级别有两个

session 级:默认当前链接的统计结果

global 级:自数据库上次启动到现在的统计结果

如果不指定统计结果级别的话,默认使用 session 级别。

对于 show status 查询出来的统计结果,有两类参数需要注意下,一类是以 Com_ 为开头的参数,一类是以 Innodb_ 为开头的参数。

下面是 Com_ 为开头的参数,参数很多,我同样没有截全。

711f0c563dc089c6c992c1eda0eb85a.jpg

Com_xxx 表示的是每个 xxx 语句执行的次数,我们通常关心的是 select 、insert 、update、delete 语句的执行次数,即

Com_select:执行 select 操作的次数,一次查询会使结果 + 1。

Com_insert:执行 INSERT 操作的次数,对于批量插入的 INSERT 操作,只累加一次。

Com_update:执行 UPDATE 操作的次数。

Com_delete:执行 DELETE 操作的次数。

以 Innodb_ 为开头的参数主要有

Innodb_rows_read:执行 select 查询返回的行数。

Innodb_rows_inserted:执行 INSERT 操作插入的行数。

Innodb_rows_updated:执行 UPDATE 操作更新的行数。

Innodb_rows_deleted:执行 DELETE 操作删除的行数。

通过上面这些参数执行结果的统计,我们能够大致了解到当前数据库是以更新(包括插入、删除)为主还是查询为主。

除此之外,还有一些其他参数用于了解数据库的基本情况。

Connections:查询 MySQL 数据库的连接次数,这个次数是不管连接是否成功都算上。

Uptime:服务器的工作时间。

Slow_queries:满查询次数。

Threads_connected:查看当前打开的连接的数量。

下面这个博客汇总了几乎所有 show status 的参数,可以当作参考手册。

https://blog.csdn.net/ayay_870621/article/details/88633092

定位执行效率较低的 SQL

定位执行效率比较慢的 SQL 语句,一般有两种方式

可以通过慢查询日志来定位哪些执行效率较低的 SQL 语句。

MySQL 中提供了一个慢查询的日志记录功能,可以把查询 SQL 语句时间大于多少秒的语句写入慢查询日志,日常维护中可以通过慢查询日志的记录信息快速准确地判断问题所在。用 --log-slow-queries 选项启动时,mysqld 会写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件,通过查看这个日志文件定位效率较低的 SQL 。

比如我们可以在 my.cnf 中添加如下代码,然后退出重启 MySQL。

log-slow-queries = /tmp/mysql-slow.log
long_query_time = 2

通常我们设置最长的查询时间是 2 秒,表示查询时间超过 2 秒就记录了,通常情况下 2 秒就够了,然而对于很多 WEB 应用来说,2 秒时间还是比较长的。

也可以通过命令来开启:

我们先查询 MySQL 慢查询日志是否开启

show variables like "%slow%";

3ed20333f0b80989477917d564ecfab.jpg

启用慢查询日志

set global slow_query_log='ON';

1cd80eae8f571f4103f5f03d22aa27e.jpg

然后再次查询慢查询是否开启

0f449b4b0141f625b3da91d33dbc316.jpg

如图所示,我们已经开启了慢查询日志。

慢查询日志会在查询结束以后才记录,所以在应用反应执行效率出现问题的时候慢查询日志并不能定位问题,此时应该使用 show processlist 命令查看当前 MySQL 正在进行的线程。包括线程的状态、是否锁表等,可以实时的查看 SQL 执行情况。同样,使用mysqladmin processlist语句也能得到此信息。

9b909b73b3f7fd163b3a13455f11412.jpg

下面就来解释一下各个字段对应的概念

Id :Id 就是一个标示,在我们使用 kill 命令杀死进程的时候很有用,比如 kill 进程号。

User:显示当前的用户,如果不是 root,这个命令就只显示你权限范围内的 SQL 语句。

Host:显示 IP ,用于追踪问题

Db:显示这个进程目前连接的是哪个数据库,为 null 是还没有 select 数据库。

Command:显示当前连接锁执行的命令,一般有三种:查询 query,休眠 sleep,连接 connect。

Time:这个状态持续的时间,单位是秒

State:显示当前 SQL 语句的状态,非常重要,下面会具体解释。

Info:显示这个 SQL 语句。

State 列非常重要,关于这个列的内容比较多,读者可以参考一下这篇文章

https://blog.csdn.net/weixin_34357436/article/details/91768402

这里面涉及线程的状态、是否锁表等选项,可以实时的查看 SQL 的执行情况,同时对一些锁表进行优化。

通过 EXPLAIN 命令分析 SQL 的执行计划

通过以上步骤查询到效率低的 SQL 语句后,可以通过 EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息,包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

比如我们使用下面这条 SQL 语句来分析一下执行计划

explain select * from test1;

4b154d51b741231207f92976d218bda.jpg

上表中涉及内容如下

select_type:表示常见的 SELECT 类型,常见的有 SIMPLE,SIMPLE 表示的是简单的 SQL 语句,不包括 UNION 或者子查询操作,比如下面这段就是 SIMPLE 类型。

384349f06f4c84307340ecf419d9620.jpg

PRIMARY ,查询中最外层的 SELECT(如两表做 UNION 或者存在子查询的外层的表操作为 PRIMARY,内层的操作为 UNION),比如下面这段子查询。

79848c983e3d54a744fb5f7c101aafb.jpg

UNION,在 UNION 操作中,查询中处于内层的 SELECT(内层的 SELECT 语句与外层的 SELECT 语句没有依赖关系时)。

SUBQUERY:子查询中首个SELECT(如果有多个子查询存在),如我们上面的查询语句,子查询第一个是 sr(sys_role)表,所以它的 select_type 是 SUBQUERY。

table ,这个选项表示输出结果集的表。

type,这个选项表示表的连接类型,这个选项很有深入研究的价值,因为很多 SQL 的调优都是围绕 type 来讲的,但是这篇文章我们主要围绕优化方式来展开的,type 这个字段我们暂时作为了解,这篇文章不过多深入。

type 这个字段会牵扯到连接的性能,它的不同类型的性能由好到差分别是

system :表中仅有一条数据时,该表的查询就像查询常量表一样。

const :当表中只有一条记录匹配时,比如使用了表主键(primary key)或者表唯一索引(unique index)进行查询。

eq-ref :表示多表连接时使用表主键或者表唯一索引,比如

select A.text, B.text where A.ID = B.ID

这个查询语句,对于 A 表中的每一个 ID 行,B 表中都只能有唯一的 B.Id 来进行匹配时。

ref :这个类型不如上面的 eq-ref 快,因为它表示的是因为对于表 A 中扫描的每一行,表 C 中有几个可能的行,C.ID 不是唯一的。

ref_or_null :与 ref 类似,只不过这个选项包含对 NULL 的查询。

index_merge :查询语句使用了两个以上的索引,比如经常在有 and 和 or 关键字出现的场景,但是在由于读取索引过多导致其性能有可能还不如 range(后面说)。

unique_subquery :这个选项经常用在 in 关键字后面,子查询带有 where 关键字的子查询中,用 sql 来表示就是这样

value IN (SELECT primary_key FROM single_table WHERE some_expr)

range :索引范围查询,常见于使用 =,<>,>,>=,<,<=,IS NULL,<=>,BETWEEN,IN() 或者 like 等运算符的查询中。

index :索引全表扫描,把索引从头到尾扫一遍。

all :这个我们接触的最多了,就是全表查询,select * from xxx ,性能最差。

上面就是 type 内容的大致解释,关于 type 我们经常会在 SQL 调优的环节使用 explain 分析其类型,然后改进查询方式,越靠近 system 其查询效率越高,越靠近 all 其查询效率越低。

3ed8ff94e8e1b38da0943287eeb0275.jpg

possible_keys :表示查询时,可能使用的索引。

key :表示实际使用的索引。

key_len :索引字段的长度。

rows :扫描行的数量。

filtered :通过查询条件查询出来的 SQL 数量占用总行数的比例。

extra :执行情况的描述。

通过上面的分析,我们可以大致确定 SQL 效率低的原因,一种非常有效的提升 SQL 查询效率的方式就是使用索引,接下来我会讲解一下如何使用索引提高查询效率。

索引

索引是数据库优化中最常用也是最重要的手段,通过使用不同的索引可以解决大多数 SQL 性能问题,也是面试经常会问到的优化方式,围绕着索引,面试官能让你造出火箭来,所以总结一点就是索引非常非常重!要!不只是使用,你还要懂其原!理!

索引介绍

索引的目的就是用于快速查找某一列的数据,对相关数据列使用索引能够大大提高查询操作的性能。不使用索引,MySQL 必须从第一条记录开始读完整个表,直到找出相关的行,表越大查询数据所花费的时间就越多。如果表中查询的列有索引,MySQL 能够快速到达一个位置去搜索数据文件,而不必查看所有数据,那么将会节省很大一部分时间。

索引分类

先来了解一下索引都有哪些分类。

全局索引(FULLTEXT):全局索引,目前只有 MyISAM 引擎支持全局索引,它的出现是为了解决针对文本的模糊查询效率较低的问题,并且只限于 CHAR、VARCHAR 和 TEXT 列。

哈希索引(HASH):哈希索引是 MySQL 中用到的唯一 key-value 键值对的数据结构,很适合作为索引。HASH 索引具有一次定位的好处,不需要像树那样逐个节点查找,但是这种查找适合应用于查找单个键的情况,对于范围查找,HASH 索引的性能就会很低。默认情况下,MEMORY 存储引擎使用 HASH 索引,但也支持 BTREE 索引。

B-Tree 索引:B 就是 Balance 的意思,BTree 是一种平衡树,它有很多变种,最常见的就是 B+ Tree,它被 MySQL 广泛使用。

R-Tree 索引:R-Tree 在 MySQL 很少使用,仅支持 geometry 数据类型,支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种,相对于 B-Tree 来说,R-Tree 的优势在于范围查找。

从逻辑上来对 MySQL 进行分类,主要分为下面这几种

普通索引:普通索引是最基础的索引类型,它没有任何限制 。创建方式如下

create index normal_index on cxuan003(id);

b1566e39176bb6040e6950d2fbf273f.jpg

删除方式

drop index normal_index on cxuan003;

95feae3ab37574860a1fe9672460bd3.jpg

唯一索引:唯一索引列的值必须唯一,允许有空值,如果是组合索引,则列值的组合必须唯一,创建方式如下

create unique index normal_index on cxuan003(id);

95c293235e9ab9ce3d2302a69a25c8e.jpg

主键索引:是一种特殊的索引,一个表只能有一个主键,不允许有空值。一般是在建表的时候同时创建主键索引。

CREATE TABLE `table` (
         `id` int(11) NOT NULL AUTO_INCREMENT ,
         `title` char(255) NOT NULL ,
         PRIMARY KEY (`id`)
)

9d3c4f7294519356b0c6de424d45774.jpg

组合索引:指多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。使用组合索引时遵循最左前缀原则,下面我们就会创建组合索引。

全文索引:主要用来查找文本中的关键字,而不是直接与索引中的值相比较,目前只有 char、varchar,text 列上可以创建全文索引,创建表的适合添加全文索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    FULLTEXT (content)
);
  • 当然也可以直接创建全局索引
CREATE FULLTEXT INDEX index_content ON article(content)


相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
7天前
|
SQL 关系型数据库 MySQL
MySQL慢查询优化、索引优化、以及表等优化详解
本文详细介绍了MySQL优化方案,包括索引优化、SQL慢查询优化和数据库表优化,帮助提升数据库性能。关注【mikechen的互联网架构】,10年+BAT架构经验倾囊相授。
MySQL慢查询优化、索引优化、以及表等优化详解
|
12天前
|
缓存 监控 关系型数据库
如何优化MySQL查询速度?
如何优化MySQL查询速度?【10月更文挑战第31天】
39 3
|
14天前
|
缓存 关系型数据库 MySQL
如何优化 MySQL 数据库的性能?
【10月更文挑战第28天】
38 1
|
21天前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战:百万级数据统计优化实践
【10月更文挑战第21天】 在处理大规模数据集时,传统的单体数据库解决方案往往力不从心。MySQL和Redis的组合提供了一种高效的解决方案,通过将数据库操作与高速缓存相结合,可以显著提升数据处理的性能。本文将分享一次实际的优化案例,探讨如何利用MySQL和Redis共同实现百万级数据统计的优化。
54 9
|
16天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
82 1
|
21天前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战:优化百万数据查询的实战经验
【10月更文挑战第13天】 在处理大规模数据集时,传统的关系型数据库如MySQL可能会遇到性能瓶颈。为了提升数据处理的效率,我们可以结合使用MySQL和Redis,利用两者的优势来优化数据查询。本文将分享一次实战经验,探讨如何通过MySQL与Redis的协同工作来优化百万级数据统计。
48 5
|
26天前
|
存储 关系型数据库 MySQL
优化 MySQL 的锁机制以提高并发性能
【10月更文挑战第16天】优化 MySQL 锁机制需要综合考虑多个因素,根据具体的应用场景和需求进行针对性的调整。通过不断地优化和改进,可以提高数据库的并发性能,提升系统的整体效率。
48 1
|
26天前
|
缓存 关系型数据库 MySQL
一文彻底弄懂MySQL优化之深度分页
【10月更文挑战第24天】本文深入探讨了 MySQL 深度分页的原理、常见问题及优化策略。首先解释了深度分页的概念及其带来的性能和资源问题。接着介绍了基于偏移量(OFFSET)和限制(LIMIT)以及基于游标的分页方法,并分析了它们的优缺点。最后,提出了多种优化策略,包括合理创建索引、优化查询语句和使用数据缓存,帮助提升分页查询的性能和系统稳定性。
|
29天前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战:百万数据量的优化实录
【10月更文挑战第6天】 在现代互联网应用中,随着用户量的增加和业务逻辑的复杂化,数据量级迅速增长,这对后端数据库系统提出了严峻的挑战。尤其是当数据量达到百万级别时,传统的数据库解决方案往往会遇到性能瓶颈。本文将分享一次使用MySQL与Redis协同优化大规模数据统计的实战经验。
110 3
|
29天前
|
NoSQL 关系型数据库 BI
记录一次MySQL+Redis实现优化百万数据统计的方式
【10月更文挑战第13天】 在处理百万级数据的统计时,传统的单体数据库往往力不从心,这时结合使用MySQL和Redis可以显著提升性能。以下是一次实际优化案例的详细记录。
102 1