search
HomeDatabaseMysql Tutorialmysql性能问题定位_MySQL

bitsCN.com

mysql性能问题定位
 

 使用mysql作为基础数据库的应用,可能会遇到一些数据库方面的性能问题,我们可以通过一些方法进行问题定位。以下介绍可以定位性能问题的四种方法,欢迎拍砖。

一、开启慢查询日志:

记录执行查询时间大于long_query_time的sql,long_query_time默认为2s;

show variables like ‘%slow%’

mysql性能问题定位_MySQL

得到图中所示信息,这里可以查看到慢查询日志是否开启,慢查询日志文件的存放目录。

开启慢查询日志的方法:

1、vi  /etc/my.cnf(这个是mysql的默认读取配置文件目录,一般会将my.cnf文件放在这下面)

[mysqld]下添加

slow_query_log=ON

long_query_time=1(sql语句执行时间超过该参数值,则会打印在慢查询日志中),默认执行时间超过2s的sql会打印在慢查询日志中。

修改了my.cnf中的配置项,需要重启数据库。

2、不重启数据库的情况下,执行 set global slow_query_log=ON,可以开启慢查询日志。

开启慢查询日志后,跟踪慢查询日志文件中的慢查询sql,再具体分析,通过调整sql写法,或者添加正确的索引,可以看到意想不到的性能效果。

二、分析慢查询sql:

1、Explain 打印执行计划

执行的selcet语句前面加上explain,可以告诉你mysql如何执行该条语句。

mysql性能问题定位_MySQL

这里需要额外注意type、key、rows 、extra列展示的内容。

其中,

Type=all,表示使用的是全表扫描,在数据量大的情况下,全表扫描是非常耗性能的,这个需要特别注意;

Type=index,表示使用索引扫描,只会遍历索引树;

Type=range,表示使用索引范围扫描,常见于between 、>、

Type=ref,非唯一性索引扫,返回匹配某个单独值得所有行

Type=eq_ref 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配

Type=const/system   读常量,最多只会有一条记录匹配,由于是常量,实际上只须要读一次

Type=null 不需要扫描表

访问类型从上到下由差变为最好。

key表示select中使用到的索引,如果为null,表示没有使用索引,从查询效率上讲,使用索引比不使用索引快。但并不是所有的都要加索引,索引也存在不足,这里就不详解。

rows表示执行该条sql所需要扫描的行数,这个没有绝对值可参考,一般来说越小越好,如果100万数据量的数据库,rows是70万,通过这个可以判断sql的查询性能很差,如果100万条数据量的数据库,rows是1万,从我个人的角度,还是能接受的。

extra

一些十分重要的额外信息,重点关注出现关键字:

Using filesort:当Query 中包含order by 操作,而且无法利用索引完成排序操作的时候,MySQL Query Optimizer 不得不选择相应的排序算法来实现。

Using temporary:在某些操作中必须使用临时表时,在 Extra 信息中就会出现Using temporary ,主要常见于 GROUP BY 和 ORDER BY 等操作中

当执行计划Extra 出现Using filesort 、Using temporary 时,可以考虑是否需要进行sql优化和调整索引,最后再调整my.cnf 中与排序或者临时表相关的参数,如sort_buffer_size或者tmp_table_size.

2、show full processlist 查看哪条sql一直占用进程

mysql性能问题定位_MySQL

Time表示执行当前操作所耗费的时间,单位为秒(s);

State表面当前线程的状态,Info表示正在执行的操作;

这里如果发现time值比较大,state一直处于一个状态,那么从Info中我们可以获得耗时长的操作,再具体分析;

注意观察State中出现关于lock关键字的状态。

这个命令可以很直观地看到正在执行的sql,及其当前状态,操作比较方便。

3、show profile 定位sql在数据库中资源占用情况(注意,一个是show profiles,一个是show profile)

Show profiles主要展示在当前会话中,profiling_history_size条sql执行的时间、query_id,默认为15条,最大为100条,不能设为0。

SHOW VARIABLES LIKE ‘%profiling_history_size%’

 

mysql性能问题定位_MySQL

 

SHOW VARIABLES LIKE ‘%profiling’或者select @@profiling 查看profiling是否开启

mysql性能问题定位_MySQL

set profiling=1 开启profiling

执行一条sql

mysql性能问题定位_MySQL

再执行show profiles,会把最近执行的sql给展示出来,

mysql性能问题定位_MySQL

从图中找到刚刚执行的sql,query_id是120,执行时间是0.00079725s

如果我们想看sql在各个阶段所消耗时间,则使用如下

SHOW PROFILE FOR QUERY 120

mysql性能问题定位_MySQL

各个阶段所消耗时间一目了然。

show profile具体写法为 Show profile TYPE for query n,n为query_id,TYPE可写可不写

TYPE的取值有为ALL、BLOCK IO、CONTEXT SWITCHES、CPU、IPC、MEMORY、PAGE FAULTS、SOURCE、SWAPS

如上述例子中

SHOW PROFILE CPU,MEMORY FOR QUERY 120

mysql性能问题定位_MySQL

不加for query n这句,则展示执行show profile之前执行过一条语句。

4、Mysqladmin 查询整个数据库的状态

在mysql的bin目录下,执行

./mysqladmin -u用户名 -p密码 proc stat

mysql性能问题定位_MySQL

这里添加proc就如同show full processlist功效

stat展示当前数据库的状态

threads 表示当前线程数,Opens 当前打开的表数目,Queries per second 每秒执行的查询数,数据库性能越好,这个值就越高 


bitsCN.com
Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
深入理解MySQL索引优化器工作原理深入理解MySQL索引优化器工作原理Nov 09, 2022 pm 02:05 PM

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了关于索引优化器工作原理的相关内容,其中包括了MySQL Server的组成,MySQL优化器选择索引额原理以及SQL成本分析,最后通过 select 查询总结整个查询过程,下面一起来看一下,希望对大家有帮助。

sybase是什么数据库sybase是什么数据库Sep 22, 2021 am 11:39 AM

sybase是基于客户/服务器体系结构的数据库,是一个开放的、高性能的、可编程的数据库,可使用事件驱动的触发器、多线索化等来提高性能。

visual foxpro数据库文件是什么visual foxpro数据库文件是什么Jul 23, 2021 pm 04:53 PM

visual foxpro数据库文件是管理数据库对象的系统文件。在VFP中,用户数据是存放在“.DBF”表文件中;VFP的数据库文件(“.DBC”)中不存放用户数据,它只起将属于某一数据库的 数据库表与视图、连接、存储过程等关联起来的作用。

数据库系统的构成包括哪些数据库系统的构成包括哪些Jul 15, 2022 am 11:58 AM

数据库系统由4个部分构成:1、数据库,是指长期存储在计算机内的,有组织,可共享的数据的集合;2、硬件,是指构成计算机系统的各种物理设备,包括存储所需的外部设备;3、软件,包括操作系统、数据库管理系统及应用程序;4、人员,包括系统分析员和数据库设计人员、应用程序员(负责编写使用数据库的应用程序)、最终用户(利用接口或查询语言访问数据库)、数据库管理员(负责数据库的总体信息控制)。

microsoft sql server是什么软件microsoft sql server是什么软件Feb 28, 2023 pm 03:00 PM

microsoft sql server是Microsoft公司推出的关系型数据库管理系统,是一个全面的数据库平台,使用集成的商业智能(BI)工具提供了企业级的数据管理,具有使用方便可伸缩性好与相关软件集成程度高等优点。SQL Server数据库引擎为关系型数据和结构化数据提供了更安全可靠的存储功能,使用户可以构建和管理用于业务的高可用和高性能的数据应用程序。

go语言可以写数据库么go语言可以写数据库么Jan 06, 2023 am 10:35 AM

go语言可以写数据库。Go语言和其他语言不同的地方是,Go官方没有提供数据库驱动,而是编写了开发数据库驱动的标准接口,开发者可以根据定义的接口来开发相应的数据库驱动;这样做的好处在于,只要是按照标准接口开发的代码,以后迁移数据库时,不需要做任何修改,极大方便了后期的架构调整。

数据库的什么是指数据的正确性和相容性数据库的什么是指数据的正确性和相容性Jul 04, 2022 pm 04:59 PM

数据库的“完整性”是指数据的正确性和相容性。完整性是指数据库中数据在逻辑上的一致性、正确性、有效性和相容性。完整性对于数据库系统的重要性:1、数据库完整性约束能够防止合法用户使用数据库时向数据库中添加不合语义的数据;2、合理的数据库完整性设计,能够同时兼顾数据库的完整性和系统的效能;3、完善的数据库完整性有助于尽早发现应用软件的错误。

mysql查询慢的因素除了索引,还有什么?mysql查询慢的因素除了索引,还有什么?Jul 19, 2022 pm 08:22 PM

mysql查询为什么会慢,关于这个问题,在实际开发经常会遇到,而面试中,也是个高频题。遇到这种问题,我们一般也会想到是因为索引。那除开索引之外,还有哪些因素会导致数据库查询变慢呢?

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Tools

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

SublimeText3 Linux new version

SublimeText3 Linux new version

SublimeText3 Linux latest version

Safe Exam Browser

Safe Exam Browser

Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.