So finden Sie langsames SQL in MySQL schnell

Langsames Abfrageprotokoll aktivieren

Wir stoßen im Projekt häufig auf langsame Abfragen. Wenn wir auf langsame Abfragen stoßen, müssen wir normalerweise das langsame Abfrageprotokoll aktivieren, das langsame Abfrageprotokoll analysieren, das langsame SQL finden und es dann mit „explain“ analysieren

Systemvariablen

Die Systemvariablen im Zusammenhang mit MySQL und langsamer Abfrage lauten wie folgt:

Parameter	Bedeutung
slow_query_log	Ob das langsame Abfrageprotokoll aktiviert werden soll, EIN bedeutet aktiviert, AUS bedeutet nicht aktiviert, die Standardeinstellung ist AUS
log_output	Der Speicherort für die Protokollausgabe ist standardmäßig DATEI, was bedeutet, dass das Protokoll als Datei gespeichert wird. Es unterstützt die Einstellung mehrerer formats
slow_query_log_file	Slow angeben Der Pfad und Name der Abfrageprotokolldatei
long_query_time	Das langsame Abfrageprotokoll wird nur aufgezeichnet, wenn die Ausführungszeit diesen Wert überschreitet 10

Führen Sie die folgende Anweisung aus, um zu sehen, ob das Protokoll für langsame Abfragen aktiviert ist. EIN bedeutet aktiviert, AUS bedeutet nicht aktiviert Ändern Sie die Konfigurationsdatei: Ändern Sie die Konfigurationsdatei my.ini und fügen Sie die folgenden Parameter im Abschnitt [mysqld] hinzu: Ich führe die folgenden 2 Sätze in der Befehlszeile aus, um das langsame Protokoll zu öffnen, das Zeitlimit auf 0,001 s festzulegen und das Protokoll in der Datei und in der Tabelle mysql.slow_log aufzuzeichnen.

show variables like "%slow_query_log%"

Wenn Sie es dauerhaft machen möchten, Rufen Sie die Konfiguration in der Konfigurationsdatei ab, andernfalls werden diese Konfigurationen nach dem Neustart der Datenbank ungültig. Daher müssen wir einige Tools verwenden, um es zu analysieren.

Allgemeine Verwendung

[mysqld]
log_output='FILE,TABLE'
slow_query_log='ON'
long_query_time=0.001

pt-query-digest

pt-query-digest ist das Tool, das ich am häufigsten verwende. Es ist sehr leistungsfähig und kann Binlog, allgemeines Protokoll und Slowlog analysieren und kann auch über verwendet werden Prozessliste anzeigen oder die von tcpdump erfassten MySQL-Protokolldaten analysieren. Laden Sie es einfach herunter und autorisieren Sie es, um das Perl-Skript „pt-query-digest“ auszuführen Der Überprüfungsparameter gibt die Analyseergebnisse in die Tabelle aus. Wenn keine Tabelle vorhanden ist, wird diese automatisch erstellt.

--create-history-table Wenn Sie den Parameter --history verwenden, um die Analyseergebnisse in einer Tabelle auszugeben, wird diese automatisch erstellt, wenn keine Tabelle vorhanden ist.

--filter Gleicht die langsame Eingabeabfrage entsprechend der angegebenen Zeichenfolge ab und analysiert sie dann.

--limit begrenzt den Prozentsatz oder die Anzahl der Ausgabeergebnisse. Der Standardwert ist 20, was den langsamsten 20 entspricht Wenn die Ausgabe 50 % beträgt, wird sie entsprechend der Gesamtantwortzeit von groß nach klein sortiert und die Ausgabe wird abgeschnitten, wenn die Gesamtantwortzeit 50 % erreicht.

– Host-MySQL-Serveradresse

– Benutzer-MySQL-Benutzername

– Passwort MySQL-Benutzerpasswort

– Verlauf Speichern Sie die Analyseergebnisse in der Tabelle, die Analyseergebnisse sind detaillierter Wenn Sie das nächste Mal --history verwenden und die gleiche Anweisung vorhanden ist und sich das Zeitintervall der Abfrage von dem in der Verlaufstabelle unterscheidet, werden die historischen Änderungen eines bestimmten Typs aufgezeichnet der Abfrage durch Abfrage derselben CHECKSUM.

--Überprüfung Speichern Sie die Analyseergebnisse in der Tabelle. Bei dieser Analyse werden nur die Abfragebedingungen parametrisiert, was relativ einfach ist. Wenn dieselbe Anweisungsanalyse auftritt, wird sie bei der nächsten Verwendung von --review nicht in der Datentabelle aufgezeichnet.

- Ausgabetyp der Analyseergebnisse. Der Wert kann Report (Standardanalysebericht), Slowlog (MySQL-Slow-Log), JSON, JSON-Anon sein. Verwenden Sie im Allgemeinen Report, um das Lesen zu erleichtern.
– seit ist der Zeitpunkt, seit dem die Analyse begonnen hat. Der Wert kann ein angegebener Zeitpunkt im Format „JJJJ-MM-TT [hh:mm:ss]“ sein kann eine einfache Zeit sein: s (Sekunden), h (Stunden), m (Minuten), d (Tage), zum Beispiel bedeutet 12h, dass die Statistik vor 12 Stunden begonnen hat.
--bis zum Stichtag, kombiniert mit -da langsame Abfragen innerhalb eines bestimmten Zeitraums analysiert werden können.
• Common DSN

A Geben Sie den Zeichensatz an
• D Geben Sie die Datenbank an, mit der eine Verbindung hergestellt werden soll

  P Stellen Sie eine Verbindung zum Datenbankport her zur Datenbank

  t Verwenden Sie --review oder -In welcher Tabelle sollen die Daten während des Verlaufs gespeichert werden
u Der Benutzername für die Verbindung zur Datenbank

• DSN wird in der Form „key=value“ konfiguriert und getrennte

• Verwendungsbeispiele
  rrree
tatsächliche Verwendung

• Schreibspeicherung Prozess-Batch-Datenerstellung

• Es gibt keine Testleistung in der tatsächlichen Arbeit. Das manuelle Einfügen ist oft nicht möglich Dieses Mal müssen wir gespeicherte Prozeduren verwenden

  set global slow_query_log = on;
  set global log_output = 'FILE,TABLE';
  set global long_query_time = 0.001;

Wie definiere ich eine gespeicherte Prozedur?

• # 取出使用最多的10条慢查询
  mysqldumpslow -s c -t 10 /var/run/mysqld/mysqld-slow.log
  
  # 取出查询时间最慢的3条慢查询
  mysqldumpslow -s t -t 3 /var/run/mysqld/mysqld-slow.log 
  
  # 得到按照时间排序的前10条里面含有左连接的查询语句
  mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow-log

  Fügen Sie beispielsweise 100.000 Daten mit den IDs 1-100000 ein.

Verwenden Sie Navicat, um

• wget www.percona.com/get/pt-query-digest
  chmod u+x pt-query-digest
  ln -s /opt/soft/pt-query-digest /usr/bin/pt-query-digest

  auszuführen. Drei Parametertypen gespeicherter Prozeduren. Parametertyp. Gibt an, ob zurückgegeben werden soll. Funktion

• IN
  Nein

Eingabeparameter für die gespeicherte Prozedur. Der Wert des Parameters wird während der gespeicherten Prozedur geändert und kann nicht zurückgegeben werden. Der Aufrufer kann den Rückgabewert erhalten.

INOUT

ist die Kombination aus

IN und OUT, die zum Speichern der eingehenden Parameter der Prozedur verwendet wird. Gleichzeitig kann die Berechnungsstruktur in die Parameter eingefügt werden , und der Anrufer kann den Rückgabewert erhalten

用MySQL执行

得用DELIMITER 定义新的结束符，因为默认情况下SQL采用（;）作为结束符，这样当存储过程中的每一句SQL结束之后，采用（；）作为结束符，就相当于告诉MySQL可以执行这一句了。但是存储过程是一个整体，我们不希望SQL逐条执行，而是采用存储过程整段执行的方式，因此我们就需要定义新的DELIMITER ，新的结束符可以用（//）或者（$$）

因为上面的代码应该就改为如下这种方式

DELIMITER //
CREATE PROCEDURE create_kf_kfGroup(IN loop_times INT)  
BEGIN  
	DECLARE var INT;
	SET var = 1;
	WHILE var <= loop_times DO    
		INSERT INTO kf_user_info (`id`,`gid`,`name`) 
		VALUES (var, 1000, var);
		SET var = var + 1;
	END WHILE;  
END //
DELIMITER ;

查询已经定义的存储过程

show procedure status;

开始执行慢sql

select * from kf_user_info where id = 9999;
select * from kf_user_info where id = 99999;
update kf_user_info set gid = 2000 where id = 8888;
update kf_user_info set gid = 2000 where id = 88888;

可以执行如下sql查看慢sql的相关信息。

SELECT * FROM mysql.slow_log order by start_time desc;

查看一下慢日志存储位置

show variables like "slow_query_log_file"

pt-query-digest /var/lib/mysql/VM-0-14-centos-slow.log

执行后的文件如下

# Profile
# Rank Query ID                            Response time Calls R/Call V/M 
# ==== =================================== ============= ===== ====== ====
#    1 0xE2566F6154AFF41948FE497E53631B43   0.1480 56.1%     4 0.0370  0.00 UPDATE kf_user_info
#    2 0x2DFBC6DBF0D68EF2EC2AE954DC37A1A4   0.1109 42.1%     4 0.0277  0.00 SELECT kf_user_info
# MISC 0xMISC                               0.0047  1.8%     2 0.0024   0.0 <2 ITEMS>

从最上面的统计sql中就可以看到执行慢的sql

可以看到响应时间，执行次数，每次执行耗时（单位秒），执行的sql

下面就是各个慢sql的详细分析，比如，执行时间，获取锁的时间，执行时间分布，所在的表等信息

不由得感叹一声，真是神器，查看慢sql超级方便

最后说一个我遇到的一个有意思的问题，有一段时间线上的接口特别慢，但是我查日志发现sql执行的很快，难道是网络的问题？

为了确定是否是网络的问题，我就用拦截器看了一下接口的执行时间，发现耗时很长，考虑到方法加了事务，难道是事务提交很慢？

于是我用pt-query-digest统计了一下1分钟左右的慢日志，发现事务提交的次很多，但是每次提交事务的平均时长是1.4s左右，果然是事务提交很慢。

Das obige ist der detaillierte Inhalt vonSo finden Sie langsames SQL in MySQL schnell. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!