MySQL通过show status查看、explain分析优化数据库性能

MySQL通过show status查看、explain分析优化数据库性能,第1张

MySQL通过show status查看、explain分析优化数据库性能 1.概述

在应用系统开发过程中,由于初期数据量小,开发人员写SQL语句时更重视功能上的实现,但是当应用系统正式上线后,随着生产数据量的急剧增长,很多SQL语句开始逐渐显露出性能问题,对生产环境的影响也越来越大,此时这些有问题的SQL语句就成为整个系统性能的瓶颈,因此我们必须要对它们进行优化,该章节将详细介绍在MySQL中优化SQL语句的方法。

2.通过show status命令了解各种SQL的执行频率

MySQL客户端连接成功后,通过show [session|global]status命令可以提供服务器状态信息,也可以在操作系统上使用mysqladmin extended-status命令获得这些消息。show [session|global] status可以根据需要加上参数“session”或者“global”来显示session级(当前连接)的统计结果和global级(自数据库上次启动至今)的统计结果。如果不写,默认使用参数是“session”。

下面的命令显示了当前session中所有统计参数的值:

-- 查看会话所有统计的值
SHOW STATUS LIKE 'Com_%';
Or
SHOW SESSION STATUS LIKE 'Com_%';

下面的命令显示了当前global中所有统计参数的值:

-- 查看全局所有统计的值

SHOW GLOBAL STATUS LIKE 'Com_%';

Com_xxx表示每个xxx语句执行的次数,我们通常比较关心的是以下几个统计参数:

  • 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操作删除的行数。

通过以上几个参数,可以很容易地了解当前数据库的应用系统是以插入更新为主还是以查询操作为主,以及各种类型的SQL大致的执行比例是多少。对于更新操作的计数,是对执行次数的计数,不论提交还是回滚都会进行累加。

对于事务型的应用,通过Com_commit和Com_rollback可以了解事务提交和回滚的情况,对于回滚操作非常频繁的数据库,可能意味着应用编写存在问题。此外,以下几个参数便于用户了解数据库的基本情况。

  • Connections:试图连接MySQL服务器的次数。
  • Uptime:服务器工作时间。
  • Slow_queries:慢查询的次数。
3.定位执行效率较低的SQL语句

可以通过以下两种方式定位执行效率较低的SQL语句。

  • 通过慢查询日志定位那些执行效率较低的SQL语句,用--log-slow-queries[=file_name]选项启动时,mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。
  • 慢查询日志在查询结束以后才纪录,所以在应用系统反映执行效率出现问题的时候查询慢查询日志并不能定位问题,可以使用show processlist命令查看当前MySQL在进行的线程,包括线程的状态、是否锁表等,可以实时地查看SQL的执行情况,同时对一些锁表操作进行优化。
4.通过EXPLAIN分析低效SQL的执行计划

通过定位执行效率较低的SQL语句后,可以通过EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息,包括在SELECT语句执行过程中表如何连接和连接的顺序,比如想统计所有库存阶梯数量,需要关联goods_stock表和goods_stock_price表,并且对goods_stock_price.Qty字段做求和(sum)操作,相应 SQL 的执行计划如下:

EXPLAIN SELECT SUM(sp.Qty)
FROM goods_stock AS s LEFT JOIN goods_stock_price AS sp
ON s.ID=sp.GoodsStockID;

如上图所示每个列的简单解释如下:

  • select_type:表示 SELECT 的类型,常见的取值有:
    • SIMPLE(简单表,即不使用表连接 或者子查询)。
    • PRIMARY(主查询,即外层的查询)、UNION(UNION 中的第二个或 者后面的查询语句)、◎SUBQUERY(子查询中的第一个SELECT)等。
  • table:输出结果集的表。
  • type:表示表的连接类型,性能由好到差的连接类型为:
    • system(表中仅有一行,即常量表)。
    • const(单表中最多有一个匹配行,例如primary key或者unique index)。
    • eq_ref(对于前面的每一行,在此表中只查询一条记录,简单来说,就是多表连接中使用primary key或者unique index)。
    • ref(与eq_ref类似,区别在于不是使用primary key或者unique index,而是使用普通的索引)。
    • ref_or_null(与ref类似,区别在于条件中包含对NULL的查询)。
    • index_merge(索引合并优化)。
    • unique_subquery(in的后面是一个查询主键字段的子查询)。
    • index_subquery(与unique_subquery类似,区别在于in的后面是查询非唯一索引字段的子查询)。
    • range(单表中的范围查询)。
    • index(对于前面的每一行,都通过查询索引来得到数据)。
    • all(对于前面的每一行,都通过全表扫描来得到数据)。
  • possible_keys:表示查询时,可能使用的索引。
  • key:表示实际使用的索引。
  • key_len:索引字段的长度。
  • rows:扫描行的数量。
  • filtered:返回结果的行占需要读到的行(rows列的值)的百分比。
  • Extra:执行情况的说明和描述。
    • Using index(此值表示mysql将使用覆盖索引,以避免访问表)。
    • Using where(mysql 将在存储引擎检索行后再进行过滤,许多where条件里涉及索引中的列,当(并且如果)它读取索引时,就能被存储引擎检验,因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示:查询可受益于不同的索引)。
    • Using temporary(mysql 对查询结果排序时会使用临时表)。
    • Using filesort(mysql会对结果使用一个外部索引排序,而不是按索引次序从表里读取行。mysql有两种文件排序算法,这两种排序方式都可以在内存或者磁盘上完成,explain不会告诉你mysql将使用哪一种文件排序,也不会告诉你排序会在内存里还是磁盘上完成)。
    • Range checked for each record(index map: N) (没有好用的索引,新的索引将在联接的每一行上重新估算,N是显示在possible_keys列中索引的位图,并且是冗余的)。
5.确定问题并采取相应的优化措施

经过以上定位步骤,我们基本就可以分析到问题出现的原因。此时我们可以根据情况采取相应的改进措施,进行优化提高语句执行效率。
在上面的例子中,已经可以确认是goods_stock是走主键索引的,但是对goods_stock_price子表的进行了全表扫描导致效率的不理想,那么应该对goods_stock_price表的GoodsStockID字段创建索引,具体命令如下:

-- 创建索引
CREATE INDEX idx_stock_price_1 ON goods_stock_price (GoodsStockID);
-- 附加删除跟查询索引语句
ALTER TABLE goods_stock_price DROP INDEX idx_stock_price_1;
SHOW INDEX FROM goods_stock_price;

创建索引后,我们再看一下这条语句的执行计划,具体如下:

EXPLAIN SELECT SUM(sp.Qty)
FROM goods_stock AS s LEFT JOIN goods_stock_price AS sp
ON s.ID=sp.GoodsStockID;

可以发现建立索引后对goods_stock_price子表需要扫描的行数明显减少(从 3 行减少到1行),可见索引的使用可以大大提高数据库的访问速度,尤其在表很庞大的时候这种优势更为明显。

到此这篇关于MySQL通过show status查看、explain分析优化数据库性能的文章就介绍到这了。希望对大家的学习有所帮助,也希望大家多多支持脚本之家。

欢迎分享,转载请注明来源:内存溢出

原文地址:https://outofmemory.cn/sjk/2997102.html

(0)
打赏 微信扫一扫微信扫一扫 支付宝扫一扫支付宝扫一扫
上一篇 2022-09-26
下一篇2022-09-26

随机推荐

  • 君子敏于行而讷于言是什么意思

    君子敏于行而讷于言的意思是:孔子说君子说话要谨慎,而行动要敏捷。子曰君子欲讷于言而敏于行。出自《论语里仁》这里的讷是忍而少言,敏是机敏、积极,意思是君子说话要谨慎而行动要敏捷。人们应该说话谨慎,因为祸

    2022-12-08
    000
  • 上海市残联地址邮编

    上海市残联地址邮编为200082。上海市残疾人联合会的前身是上海市盲人聋哑人协会。在上海市委、市政府的领导下,在中国残联的指导下,在社会各界的支持下,上海市残疾人事业紧紧依托中国残疾人事业和上海的发展

    2022-12-08
    000
  • 析珪判野像什么动物

    析珪判野像动物鼠,属鼠的人容易相处,工作努力,生活非常节省,除非他十分喜欢的人,他是不会大方解囊的。所以,假设你从他那里得到一件贵重礼物,那么,他对你的评价一定相当高。但是虽然他会一丝不苟,并以此来夸

    2022-12-08
    000
  • 扫黑风暴项总为什么怂了

    李成阳逼迫项天同意和解的。李成阳带着一大袋的钱来夜总会找项天,一开始低三下四,满脸堆笑,恳请项天高抬贵手不要追究马帅的刑事责任,这样过两天马帅就能放出来。项天不同意,趾高气扬,30万元钱看不到眼里,事

    2022-12-08
    000
  • 吞海淮上主角叫什么

    《吞海》主的角叫步重华和吴雩。《破云2吞海》是完结于晋江中文网的一本近代现代类网络小说,作者是淮上。吞海淮上主角叫什么《吞海》主的角叫步重华和吴雩。《破云2吞海》是完结于晋江中文网的一本近代现代类网

    2022-12-08
    000
  • 扫黑风暴死的6个人

    《扫黑风暴》中高明远一共害死了六条人命,分别是麦自立、诬陷林汉、为自己的丈夫伸冤薛梅、徐小山、马帅、大江。高明远杀这些人都是因为保护孙兴,整部剧里面孙兴一直想要博取高明远的重视,所以到处闯祸,却不知道

  • 潍坊智谷学校怎么样

    智谷学校是高新区为市民配备的一所高质量九年一贯制学校,2021年该校招生只招收小学一年级和初中一年级的学生,主要基于以下两个方面的考虑:一是智谷学校周边小区实际入住率较低,大多数小区尚处于在建状态,适

    2022-12-08
    000
  • 糜子是小米吗

    糜子不是小米。区别:1、糜子秆粗壮,直立,叶鞘松弛,被疣基毛,叶舌膜质,长约1毫米,顶端具长约2毫米的睫毛;叶片线形或线状披针形,两面具疣基的长柔毛或无毛,顶端渐尖,基部近圆形,边缘常粗糙。小米秆粗壮

    2022-12-08
    000
  • 省考照片尺寸要求

    公务员考试照片尺寸是2寸电子照片:宽为3.5厘米,高为5.3厘米,像素值是626×413。电子照片是以电子文件的形式存储的照片,没有以往的照相底片。用数码相机拍的照片或扫描仪扫描存储的照片,常见的格式

    2022-12-08
    000

发表评论

登录后才能评论

评论列表(0条)

    保存