mysql(slow-query)快速开启慢日志查询的方法

Home

Database

Mysql Tutorial

mysql(slow-query)快速开启慢日志查询的方法_MySQL

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 01, 2016 pm 01:35 PM

mysqlsql statement

bitsCN.com

mysql(slow-query)快速开启慢日志查询的方法

mysql有一个功能就是可以log下来运行的比较慢的sql语句，默认是没有这个log的

打开 my.ini ,找到 [mysql] 在其下面添加

long_query_time = 2

log-slow-queries = D:/mysql/logs/slow.log #设置把日志写在那里，可以为空，系统会给
一个缺省的文件

#log-slow-queries = /var/youpath/slow.log linux下host_name-slow.log

log-queries-not-using-indexes

long_query_time 是指执行超过多长时间（单位是秒）的sql会被记录下来，这里设置的是2秒。

log-slow-queries 设置日志所在位置，可以为空，系统会给一个缺省的文件
host_name-slow.log，生成的日志就在mysql的data目录下

log-queries-not-using-indexes 就是字面意思，记录下没有使用索引的query。

以下是mysqldumpslow常用参数说明，详细的可应用mysqldumpslow -help查询。

-s，是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的
记录数来排序（从大到小），ac、at、al、ar表示相应的倒叙。

-t，是top n的意思，即为返回前面多少条数据。

-g，后边可以写一个正则匹配模式，大小写不敏感。

接下来就是用mysql自带的慢查询工具mysqldumpslow分析了（mysql的bin目录下

），我这里的日志文件名字是host-slow.log。

列出记录次数最多的10个sql语句

mysqldumpslow -s c -t 10 host-slow.log

列出返回记录集最多的10个sql语句

mysqldumpslow -s r -t 10 host-slow.log

按照时间返回前10条里面含有左连接的sql语句

mysqldumpslow -s t -t 10 -g "left join" host-slow.log

使用mysqldumpslow命令可以非常明确的得到各种我们需要的查询语句，对MySQL查
询语句的监控、分析、优化起到非常大的帮助。

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 String Types: Storage, Performance, and Best PracticesMay 10, 2025 am 12:02 AM

MySQLstringtypesimpactstorageandperformanceasfollows:1)CHARisfixed-length,alwaysusingthesamestoragespace,whichcanbefasterbutlessspace-efficient.2)VARCHARisvariable-length,morespace-efficientbutpotentiallyslower.3)TEXTisforlargetext,storedoutsiderows,

Understanding MySQL String Types: VARCHAR, TEXT, CHAR, and MoreMay 10, 2025 am 12:02 AM

MySQLstringtypesincludeVARCHAR,TEXT,CHAR,ENUM,andSET.1)VARCHARisversatileforvariable-lengthstringsuptoaspecifiedlimit.2)TEXTisidealforlargetextstoragewithoutadefinedlength.3)CHARisfixed-length,suitableforconsistentdatalikecodes.4)ENUMenforcesdatainte

What are the String Data Types in MySQL?May 10, 2025 am 12:01 AM

MySQLoffersvariousstringdatatypes:1)CHARforfixed-lengthstrings,2)VARCHARforvariable-lengthtext,3)BINARYandVARBINARYforbinarydata,4)BLOBandTEXTforlargedata,and5)ENUMandSETforcontrolledinput.Eachtypehasspecificusesandperformancecharacteristics,sochoose

How to Grant Permissions to New MySQL UsersMay 09, 2025 am 12:16 AM

TograntpermissionstonewMySQLusers,followthesesteps:1)AccessMySQLasauserwithsufficientprivileges,2)CreateanewuserwiththeCREATEUSERcommand,3)UsetheGRANTcommandtospecifypermissionslikeSELECT,INSERT,UPDATE,orALLPRIVILEGESonspecificdatabasesortables,and4)

How to Add Users in MySQL: A Step-by-Step GuideMay 09, 2025 am 12:14 AM

ToaddusersinMySQLeffectivelyandsecurely,followthesesteps:1)UsetheCREATEUSERstatementtoaddanewuser,specifyingthehostandastrongpassword.2)GrantnecessaryprivilegesusingtheGRANTstatement,adheringtotheprincipleofleastprivilege.3)Implementsecuritymeasuresl

MySQL: Adding a new user with complex permissionsMay 09, 2025 am 12:09 AM

ToaddanewuserwithcomplexpermissionsinMySQL,followthesesteps:1)CreatetheuserwithCREATEUSER'newuser'@'localhost'IDENTIFIEDBY'password';.2)Grantreadaccesstoalltablesin'mydatabase'withGRANTSELECTONmydatabase.TO'newuser'@'localhost';.3)Grantwriteaccessto'

MySQL: String Data Types and CollationsMay 09, 2025 am 12:08 AM

The string data types in MySQL include CHAR, VARCHAR, BINARY, VARBINARY, BLOB, and TEXT. The collations determine the comparison and sorting of strings. 1.CHAR is suitable for fixed-length strings, VARCHAR is suitable for variable-length strings. 2.BINARY and VARBINARY are used for binary data, and BLOB and TEXT are used for large object data. 3. Sorting rules such as utf8mb4_unicode_ci ignores upper and lower case and is suitable for user names; utf8mb4_bin is case sensitive and is suitable for fields that require precise comparison.

MySQL: What length should I use for VARCHARs?May 09, 2025 am 12:06 AM

The best MySQLVARCHAR column length selection should be based on data analysis, consider future growth, evaluate performance impacts, and character set requirements. 1) Analyze the data to determine typical lengths; 2) Reserve future expansion space; 3) Pay attention to the impact of large lengths on performance; 4) Consider the impact of character sets on storage. Through these steps, the efficiency and scalability of the database can be optimized.

See all articles