So finden Sie langsame SQL-Anweisungen in MySQL

In diesem Artikel wird hauptsächlich die Methode zum Finden langsamer SQL-Anweisungen in Abfrage in MySQL vorgestellt. Freunde in Not können sich auf

So finden Sie langsame SQL-Anweisungen in beziehen MySQL Was ist mit SQL-Anweisungen? Dies kann für viele Benutzer ein Problem sein. MySQL verwendet langsame Abfrageprotokolle, um SQL-Anweisungen mit geringer Ausführungseffizienz zu finden Es wird eine Datei geschrieben, die alle SQL-Anweisungen enthält, deren Ausführungszeit long_query_time Sekunden überschreitet, und das weniger effiziente SQL kann durch Anzeigen dieser Protokolldatei lokalisiert werden. Im Folgenden wird beschrieben, wie langsame SQL-Anweisungen in MySQL abgefragt werden

1.

MySQL-Datenbank verfügt über mehrere Konfigurationsoptionen, die uns helfen können, ineffiziente SQL-Anweisungen rechtzeitig zu erfassen 1, slow_query_log

Dieser Parameter ist auf ON gesetzt, wodurch SQL-Anweisungen erfasst werden können, deren Ausführungszeit einen bestimmten Wert überschreitet.

2, long_query_time

Wenn die Ausführungszeit der SQL-Anweisung diesen Wert überschreitet, wird sie im Protokoll aufgezeichnet. Es wird empfohlen, sie auf 1 oder kürzer festzulegen.

3, slow_query_log_file

Der Dateiname des Protokolls.

4, log_queries_not_using_indexes

Dieser Parameter ist auf ON gesetzt, wodurch alle SQL-Anweisungen erfasst werden können, die keine

Indizes

verwenden, obwohl diese SQL-Anweisung möglicherweise sehr schnell ausgeführt wird.

2. Methoden zur Erkennung der Effizienz von SQL-Anweisungen in MySQL

1. Aktivieren Sie die langsame MySQL-Abfrage unter Windows

Die

Konfigurationsdatei

von MySQL im Windows-System befindet sich normalerweise in my.ini [mysqld] und der

-Code lautet wie folgt log-slow -queries=F:/MySQL/log/mysqlslowquery. log

long_query_time = 2

(2),

Langsame MySQL-Abfrage unter Linux aktivieren

Die Konfigurationsdatei von MySQL im Windows-System befindet sich im Allgemeinen in my.cnf [mysqld] Fügen Sie unten hinzu und der Code lautet wie folgt:

log-slow-queries=/data/mysqldata/slowquery. log

long_query_time=2

Erklärung

log-slow-queries = F:/MySQL/log/mysqlslowquery.

ist der Speicherort des langsamen Abfrageprotokolls. Im Allgemeinen muss dieses Verzeichnis über Schreibberechtigungen für das MySQL-Laufkonto verfügen. Dieses Verzeichnis ist im Allgemeinen als MySQL-Datenspeicherverzeichnis festgelegt 2 Gibt an, dass die Abfrage erst aufgezeichnet wird, wenn sie länger als zwei Sekunden dauert;

2.show Process

list

Befehl

WSHOW PROCESSLIST zeigt an, welche Threads ausgeführt werden. Sie können diese Informationen auch mit der mysql-Anweisung

admin processlist abrufen.

Die Bedeutung und der Zweck jeder Spalte:

ID-Spalte

Eine Kennung, die sehr nützlich ist, wenn Sie eine Anweisung beenden möchten Der Befehl zum Beenden. Diese Abfrage /*/mysqladmin kill Prozessnummer. Benutzerspalte

zeigt den vorherigen Benutzer an. Wenn Sie kein Root sind, zeigt dieser Befehl nur die SQL-Anweisungen innerhalb Ihrer Berechtigung an.

Host-Spalte

zeigt an, von welcher IP und welchem ​​Port diese Anweisung gesendet wird. Wird verwendet, um den Benutzer zu verfolgen, der die problematische Aussage abgegeben hat.

db-Spalte

zeigt an, mit welcher Datenbank dieser Prozess derzeit verbunden ist.

Befehlsspalte

zeigt den ausgeführten Befehl der aktuellen Verbindung an, normalerweise Ruhezustand,

Abfrage

und Verbindung.

Zeitspalte

Die Dauer dieses Zustands in Sekunden.

Statusspalte

zeigt den Status der SQL-Anweisung unter Verwendung der aktuellen Verbindung an. Alle Statusbeschreibungen werden später beschrieben Bei der Ausführung der Anweisung muss eine SQL-Anweisung, die eine Abfrage als Beispiel nimmt, möglicherweise das Kopierenin die tmp-Tabelle, das

Sortieren

Ergebnis und das S

Ende durchlaufen

In der Infospalte wird diese SQL-Anweisung angezeigt. Da die Länge begrenzt ist, werden lange SQL-Anweisungen nicht vollständig angezeigt eine wichtige Grundlage zur Beurteilung von Problemstellungen.

Das Wichtigste in diesem Befehl ist die Statusspalte. Die von MySQL aufgelisteten Status umfassen hauptsächlich Folgendes:

Tabelle prüfen
überprüft die Datentabelle (dies erfolgt automatisch).
Tabellen schließen
Die geänderten Daten in der Tabelle werden auf die Festplatte geschrieben und die aufgebrauchten Tabellen werden geschlossen. Dies ist ein schneller Vorgang. Sollte dies jedoch nicht der Fall sein, sollten Sie überprüfen, ob der Speicherplatz voll ist oder die Festplatte stark ausgelastet ist.
Ausgehend verbinden
Der Replikations-Slave-Server stellt eine Verbindung zum Master-Server her.
Kopieren in die tmp-Tabelle auf der Festplatte
Da der temporäre Ergebnissatz größer als tmp_table_size ist, wird die temporäre Tabelle vom Speicher in den Festplattenspeicher konvertiert, um Speicherplatz zu sparen.
Tmp-Tabelle erstellen
　Eine temporäre Tabelle wird erstellt, um einige Abfrageergebnisse zu speichern.
Löschen aus der Haupttabelle
Der Server führt den ersten Teil des Mehrtabellen-Löschvorgangs aus und die erste Tabelle wurde gerade gelöscht.
Löschen aus Referenztabellen
Der Server führt den zweiten Teil des Löschens mehrerer Tabellen durch und löscht Datensätze aus anderen Tabellen.
FlushTabellen löschen
FLUSH TABLES ausführen und darauf warten, dass andere Threads die Datentabelle schließen.
Getötet
Wenn eine Kill-Anfrage an einen Thread gesendet wird, überprüft der Thread das Kill-Flag und gibt die nächste Kill-Anfrage auf. MySQL überprüft das Kill-Flag in jeder Haupt--Schleife , aber in einigen Fällen kann der Thread nach kurzer Zeit absterben. Wenn der Thread von einem anderen Thread gesperrt wird, wird die Kill-Anforderung sofort wirksam, wenn die Sperre aufgehoben wird.
Gesperrt
ist durch andere Abfragen gesperrt. Beim
Senden von Daten
werden die Datensätze der SELECT-Abfrage verarbeitet und die Ergebnisse an den Client gesendet.
Sortieren nachGruppe
ist Sortieren nach GRUPPE NACH.
Sortieren nach order
ist Sortieren nach ORDER BY.
Tische öffnen
Dieser Vorgang sollte schnell erfolgen, sofern er nicht durch andere Faktoren beeinträchtigt wird. Beispielsweise kann die Datentabelle nicht von anderen Threads geöffnet werden, bevor die Anweisung ALTER TABLE oder LOCK TABLE ausgeführt wird. Ich versuche, einen Tisch zu öffnen.
Duplikate entfernen
Eine SELECT DISTINCT-Abfrage wird ausgeführt, aber MySQL kann diese doppelten Datensätze in der vorherigen Phase nicht optimieren. Daher muss MySQL doppelte Datensätze erneut entfernen, bevor die Ergebnisse an den Client gesendet werden.
Tabelle erneut öffnen
Es wurde eine Sperre für eine Tabelle erhalten, die Sperre muss jedoch erhalten werden, nachdem die Tabellenstruktur geändert wurde. Die Sperre wurde aufgehoben, die Datentabelle wurde geschlossen und es wird versucht, die Datentabelle erneut zu öffnen.
Reparieren durch Sortieren
Die Reparaturanweisung sortiert, um einen Index zu erstellen.
Reparieren mit SchlüsselCache
Die Reparaturanweisung nutzt den Index-Cache, um nacheinander neue Indizes zu erstellen. Dies ist langsamer als „Reparieren durch Sortieren“.
Suche nach Zeilen nach aktuellemDatum
　Wir finden Datensätze, die die Bedingungen für Aktualisierung erfüllen. Es muss abgeschlossen sein, bevor UPDATE die zugehörigen Datensätze ändern soll.
Schlafend
wartet darauf, dass der Client eine neue Anfrage sendet.
Systemsperre
wartet darauf, eine externe Systemsperre zu erhalten. Wenn Sie derzeit nicht mehrere mysqld-Server ausführen, die gleichzeitig dieselbe Tabelle anfordern, können Sie externe Systemsperren deaktivieren, indem Sie den Parameter --skip-external-locking hinzufügen.
Sperre wird aktualisiert
　INSERT DELAYED versucht, eine Sperrtabelle zum Einfügen neuer Datensätze zu erhalten. Beim
Aktualisieren
wird nach passenden Datensätzen gesucht und diese geändert.

Benutzersperre
　Warten auf GET_LOCK().
Warten auf Tabellen
Der Thread wird benachrichtigt, dass die Datentabellenstruktur geändert wurde und die Datentabelle erneut geöffnet werden muss, um die neue Struktur zu erhalten. Um die Datentabelle erneut zu öffnen, müssen Sie warten, bis alle anderen Threads die Tabelle schließen. Diese Benachrichtigung wird in den folgenden Situationen generiert: FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE oder OPTIMIZE TABLE.
Warten auf HanDLer-Einfügung
　INSERT DELAYED hat alle ausstehenden Einfügevorgänge verarbeitet und wartet auf neue Anforderungen.
Die meisten Zustände entsprechen sehr schnellen Vorgängen. Solange ein Thread mehrere Sekunden lang im gleichen Zustand bleibt, liegt möglicherweise ein Problem vor und es muss überprüft werden.
Es gibt weitere Status, die oben nicht aufgeführt sind, aber die meisten davon sind nur nützlich, um zu überprüfen, ob Fehler auf dem Server vorliegen.

Zum Beispiel:

3. Erklären Sie, um den Status der SQL-Ausführung zu verstehen.

Erklären Sie, wie MySQL Indizes verwendet, um Auswahlanweisungen zu verarbeiten und Tabellen zu verbinden. Es kann dabei helfen, bessere Indizes auszuwählen und optimiertere Abfrageanweisungen zu schreiben.

Zur Verwendung fügen Sie einfach „explain“ vor der Select-Anweisung hinzu:

Zum Beispiel:

explain select surname,first_name form a,b where a.id=b.id

Das Ergebnis ist wie unten dargestellt

Erklärung der EXPLAIN-Spalte

Tabelle

Zeigt, um welche Tabelle es in dieser Datenzeile geht

Typ

Dies ist die wichtige Spalte, die zeigt, welcher Typ für die Verbindung verwendet wird. Die Join-Typen vom besten zum schlechtesten sind const, eq_reg, ref, range, indexhe und ALL

possible_keys

und zeigen mögliche Anwendungen im Index in dieser Tisch. Wenn leer, ist kein Index möglich. Eine entsprechende Anweisung kann aus der WHERE-Anweisung für die entsprechende Domäne ausgewählt werden

Schlüssel

Der tatsächlich verwendete Index. Wenn NULL, wird kein Index verwendet. In seltenen Fällen wählt MYSQL einen Index aus, der nicht ausreichend optimiert ist. In diesem Fall können Sie USE INDEX (Indexname) in der SELECT-Anweisung verwenden, um die Verwendung eines Index zu erzwingen, oder IGNORE INDEX (Indexname), um MYSQL zu zwingen, den vom Index

key_len<🎜 verwendeten Index zu ignorieren >

Länge. Je kürzer die Länge, desto besser, ohne an Genauigkeit zu verlieren

ref

Zeigt an, welche Spalte des Index verwendet wird, möglichst eine Konstante

Zeilen

Die Zahl Anzahl der Zeilen, von denen MYSQL glaubt, dass sie überprüft werden müssen, um die angeforderten Daten zurückzugeben

Zusätzliche

Zusätzliche Informationen darüber, wie MYSQL die Abfrage analysiert. Wird in Tabelle 4.3 besprochen, aber schlechte Beispiele, die hier zu sehen sind, sind die Verwendung von temporär und die Verwendung von Dateisortierung, was bedeutet, dass MYSQL den Index überhaupt nicht verwenden kann und das Ergebnis darin besteht, dass der Abruf sehr langsam ist

Von der zusätzlichen Spalte zurückgegebene Beschreibung Die Bedeutung von

Distinct

Sobald MYSQL eine Zeile findet, die mit der Zeilenvereinigung übereinstimmt, sucht es nicht mehr

Nicht existiert

MYSQL Optimized

LEFT JOIN, sobald eine Zeile gefunden wird, die den LEFT JOIN-Kriterien entspricht, wird nicht mehr nach

Bereich geprüft für

each gesucht Record(index map :#)

Es wurde kein idealer Index gefunden, daher prüft MYSQL für jede Kombination von Zeilen aus der vorherigen Tabelle, welcher Index verwendet wurde, und verwendet ihn, um die Zeilen zurückzugeben der Tisch. Dies ist eine der langsamsten Verbindungen, die einen Index verwenden

Filesort verwenden

Wenn Sie dies sehen, muss die Abfrage optimiert werden. MYSQL erfordert einen zusätzlichen Schritt, um herauszufinden, wie die zurückgegebenen Zeilen sortiert werden. Es sortiert alle Zeilen nach dem Verbindungstyp und den Zeilenzeigern aller Zeilen, die den Sortierschlüsselwert und die übereinstimmende Bedingung speichern.

Verwenden Sie den aus der tatsächlichen Aktionstabelle zurückgegebenen Index. Dies geschieht, wenn alle angeforderten Spalten der Tabelle sind Teil desselben Index

Verwendung temporär

Wenn Sie dies sehen, muss die Abfrage optimiert werden. Hier muss MYSQL eine temporäre Tabelle erstellen, um die Ergebnisse zu speichern. Dies geschieht normalerweise, wenn ORDER BY für verschiedene Spaltensätze anstelle von GROUP BY ausgeführt wird Begrenzen Sie, welche Zeilen mit der nächsten Tabelle übereinstimmen oder an den Benutzer zurückgegeben werden. Dies geschieht, wenn Sie nicht alle Zeilen in der Tabelle zurückgeben möchten und der Join-Typ „ALL“ oder „Index“ ist, oder wenn bei der Abfrage ein Problem mit der Erklärung verschiedener Join-Typen vorliegt (in der Reihenfolge ihrer Effizienz sortiert)

const

Der maximale Wert eines Datensatzes in der Tabelle, der dieser Abfrage entspricht (der Index kann ein Primärschlüssel oder ein eindeutiger Index sein). Da es nur eine Zeile gibt, ist dieser Wert tatsächlich eine Konstante, da MYSQL diesen Wert zuerst liest und ihn dann als Konstante behandelt

eq_ref

In der Verbindung liest MYSQL den Wert aus dem Bei der Abfrage wird ein Datensatz aus der Tabelle für jede Datensatzvereinigung gelesen. Er wird verwendet, wenn alle Indizes abgefragt werden, die den Primärschlüssel oder den eindeutigen Schlüssel

verwenden Wird nur bei Abfragen verwendet. Tritt auf, wenn ein Schlüssel verwendet wird, der kein eindeutiger Schlüssel oder Primärschlüssel ist oder Teil dieser Typen ist (z. B. wenn ein Präfix ganz links verwendet wird). Für jeden Zeilen-Join der vorherigen Tabelle werden alle Datensätze aus der Tabelle gelesen. Dieser Typ hängt stark davon ab, wie viele Datensätze basierend auf dem Index übereinstimmen – je weniger, desto besser

Bereich

Dieser Join-Typ verwendet den Index, um Zeilen in einem Bereich zurückzugeben, z. B. mit > oder <, um etwas zu finden. Was passiert, wenn

index

Dieser Join-Typ führt einen vollständigen Scan aller Datensatzvereinigungen in der vorherigen Tabelle durch (besser als ALL, da der Index im Allgemeinen kleiner ist als der Tabellendaten)

ALLE

Dieser Verbindungstyp führt einen vollständigen Scan aller vorherigen Datensätze gemeinsam durch. Dies ist im Allgemeinen schlecht und sollte vermieden werden

MySQL – Ansicht langsam SQL

Überprüfen Sie, ob MySQL zum Anzeigen der langsamen SQL-Protokolldatei aktiviert ist

(1) Überprüfen Sie, ob das langsame SQL-Protokoll aktiviert ist

mysql> show variables like 'log_slow_queries'; 
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| log_slow_queries | ON    |
+------------------+-------+
1 row in 
set
 (0.00 sec)

（2） 查看执行慢于多少秒的SQL会记录到日志文件中

mysql> show variables like 'long_query_time';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| long_query_time | 1     |   
+-----------------+-------+
1 row in set (0.00 sec)

这里value=1， 表示1秒

2. 配置my.ini文件（inux下文件名为my.cnf）， 查找到[mysqld]区段，增加日志的配置，如下示例：
[mysqld]
log="C:/temp/mysql.log"
log_slow_queries="C:/temp/mysql_slow.log"
long_query_time=1
log指示日志文件存放目录；
log_slow_queries指示记录执行时间长的sql日志目录；
long_query_time指示多长时间算是执行时间长，单位s。
Linux下这些配置项应该已经存在，只是被注释掉了，可以去掉注释。但直接添加配置项也OK啦。
查询到效率低的 SQL 语句 后，可以通过 EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序，比如我们想计算 2006 年所有公司的销售额，需要关联 sales 表和 company 表，并且对 profit 字段做求和（ sum ）操作，相应 SQL 的执行计划如下：

mysql> explain select sum(profit) from sales a,company b where a.company_id = b.id and a.year = 2006\G; 
*************************** 1. row *************************** 
id: 1 
select_type: SIMPLE 
table: a 
type: ALL 
possible_keys: NULL 
key: NULL 
key_len: NULL 
ref: NULL 
rows: 12 
Extra: Using where 
*************************** 2. row *************************** 
id: 1 
select_type: SIMPLE 
table: b 
type: ALL 
possible_keys: NULL 
key: NULL 
key_len: NULL 
ref: NULL 
rows: 12 
Extra: Using where 
2 rows in set (0.00 sec)

每个列的解释如下：
•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 ：扫描行的数量。
•Extra ：执行情况的说明和描述。
在上面的例子中，已经可以确认是 对 a 表的全表扫描导致效率的不理想，那么 对 a 表的 year 字段创建索引，具体如下：
mysql> create index idx_sales_year on sales(year);
Query OK, 12 rows affected (0.01 sec)
Records: 12 Duplicates: 0 Warnings: 0
创建索引后，这条语句的执行计划如下：

mysql> explain select sum(profit) from sales a,company b where a.company_id = b.id and a.year = 2006\G; 
*************************** 1. row *************************** 
id: 1 
select_type: SIMPLE 
table: a 
type: ref 
possible_keys: idx_sales_year 
key: idx_sales_year 
key_len: 4 
ref: const 
rows: 3 
Extra: 
*************************** 2. row *************************** 
id: 1 
select_type: SIMPLE 
table: b 
type: ALL 
possible_keys: NULL 
key: NULL 
key_len: NULL 
ref: NULL 
rows: 12 
Extra: Using where 
2 rows in set (0.00 sec)

可以发现建立索引后对 a 表需要扫描的行数明显减少（从全表扫描减少到 3 行），可见索引的使用可以大大提高数据库的访问速度，尤其在表很庞大的时候这种优势更为明显，使用索引优化 sql 是优化问题 sql 的一种常用基本方法，在后面的章节中我们会具体介绍如何使索引来优化 sql 。
本文主要介绍的是MySQL慢查询分析方法，前一段日子，我曾经设置了一次记录在MySQL数据库中对慢于1秒钟的SQL语句进行查询。想起来有几个十分设置的方法，有几个参数的名称死活回忆不起来了，于是重新整理一下，自己做个笔记。
　　对于排查问题找出性能瓶颈来说，最容易发现并解决的问题就是MySQL慢查询以及没有得用索引的查询。

　　OK，开始找出MySQL中执行起来不“爽”的SQL语句吧。
　　MySQL慢查询分析方法一：
　　这个方法我正在用，呵呵，比较喜欢这种即时性的。
　　MySQL5.0以上的版本可以支持将执行比较慢的SQL语句记录下来。
　　MySQL> show variables like 'long%';
　　注：这个long_query_time是用来定义慢于多少秒的才算“慢查询”
　　

+-----------------+-----------+
　　| Variable_name | Value |
　　+-----------------+-----------+
　　| long_query_time | 10.000000 |
　　+-----------------+-----------+
　　1 row in set (0.00 sec)
　　MySQL> set long_query_time=1;
　　注： 我设置了1, 也就是执行时间超过1秒的都算慢查询。
　　Query OK, 0 rows affected (0.00 sec)
　　MySQL> show variables like 'slow%';
　　+---------------------+---------------+
　　| Variable_name | Value |
　　+---------------------+---------------+
　　| slow_launch_time | 2 |
　　| slow_query_log | ON |
　　注：是否打开日志记录
　　| slow_query_log_file | /tmp/slow.log |
　　注： 设置到什么位置
　　+---------------------+---------------+
　　3 rows in set (0.00 sec)
　　MySQL> set global slow_query_log='ON'

　　注：打开日志记录
　　一旦slow_query_log变量被设置为ON，MySQL会立即开始记录。
　　/etc/my.cnf 里面可以设置上面MySQL全局变量的初始值。
　　long_query_time=1 slow_query_log_file=/tmp/slow.log
　　MySQL慢查询分析方法二:
　　MySQLdumpslow命令
　　/path/MySQLdumpslow -s c -t 10 /tmp/slow-log
　　这会输出记录次数最多的10条SQL语句，其中：
　　-s, 是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙;
　　-t, 是top n的意思，即为返回前面多少条的数据;
　　-g, 后边可以写一个正则匹配模式，大小写不敏感的;
　　比如
　　/path/MySQLdumpslow -s r -t 10 /tmp/slow-log
　　得到返回记录集最多的10个查询。
　　/path/MySQLdumpslow -s t -t 10 -g “left join” /tmp/slow-log
　　得到按照时间排序的前10条里面含有左连接的查询语句。

简单点的方法：
打开 my.ini ,找到 [mysqld] 在其下面添加 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秒。
以下是mysqldumpslow常用参数说明，详细的可应用mysqldumpslow -help查询。 -s，是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序（从大到小），ac、at、al、ar表示相应的倒叙。 -t，是top n的意思，即为返回前面多少条数据。 www.jb51.net -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查询语句的监控、分析、优化起到非常大的帮助

在日常开发当中，经常会遇到页面打开速度极慢的情况，通过排除，确定了，是数据库的影响，为了迅速查找具体的SQL，可以通过Mysql的日志记录方法。
-- 打开sql执行记录功能
set global log_output='TABLE'; -- 输出到表
set global log=ON; -- 打开所有命令执行记录功能general_log, 所有语句: 成功和未成功的.
set global log_slow_queries=ON; -- 打开慢查询sql记录slow_log, 执行成功的: 慢查询语句和未使用索引的语句
set global long_query_time=0.1; -- 慢查询时间限制(秒)
set global log_queries_not_using_indexes=ON; -- 记录未使用索引的sql语句
-- 查询sql执行记录
select * from mysql.slow_log order by 1; -- 执行成功的：慢查询语句,和未使用索引的语句
select * from mysql.general_log order by 1; -- 所有语句： 成功和未成功的.
-- 关闭sql执行记录
set global log=OFF;
set global log_slow_queries=OFF;
-- long_query_time参数说明
-- v4.0, 4.1, 5.0, v5.1 到 5.1.20(包括):不支持毫秒级别的慢查询分析(支持精度为1-10秒);
-- 5.1.21及以后版本 :支持毫秒级别的慢查询分析, 如0.1;
-- 6.0 到 6.0.3: 不支持毫秒级别的慢查询分析(支持精度为1-10秒);
-- 6.0.4及以后:支持毫秒级别的慢查询分析;
通过日志中记录的Sql，迅速定位到具体的文件，优化sql看一下，是否速度提升了呢？

本文针对MySQL数据库服务器查询逐渐变慢的问题， 进行分析，并提出相应的解决办法，具体的分析解决办法如下：会经常发现开发人员查一下没用索引的语句或者没有limit n的语句，这些没语句会对数据库造成很大的影...

本文针对MySQL数据库服务器查询逐渐变慢的问题， 进行分析，并提出相应的解决办法，具体的分析解决办法如下：
会经常发现开发人员查一下没用索引的语句或者没有limit n的语句，这些没语句会对数据库造成很大的影响，例如一个几千万条记录的大表要全部扫描，或者是不停的做filesort，对数据库和服务器造成io影响等。这是镜像库上面的情况。
而到了线上库，除了出现没有索引的语句，没有用limit的语句，还多了一个情况,mysql连接数过多的问题。说到这里，先来看看以前我们的监控做法
1. 部署zabbix等开源分布式监控系统，获取每天的数据库的io，cpu，连接数
2. 部署每周性能统计，包含数据增加量，iostat，vmstat，datasize的情况
3. Mysql slowlog收集，列出top 10
以前以为做了这些监控已经是很完美了，现在部署了mysql节点进程监控之后，才发现很多弊端
第一种做法的弊端: zabbix太庞大，而且不是在mysql内部做的监控，很多数据不是非常准备，现在一般都是用来查阅历史的数据情况
第二种做法的弊端：因为是每周只跑一次，很多情况没法发现和报警
第三种做法的弊端: 当节点的slowlog非常多的时候，top10就变得没意义了，而且很多时候会给出那些是一定要跑的定期任务语句给你。。参考的价值不大
那么我们怎么来解决和查询这些问题呢
对于排查问题找出性能瓶颈来说，最容易发现并解决的问题就是MYSQL的慢查询以及没有得用索引的查询。
OK，开始找出mysql中执行起来不“爽”的SQL语句吧。

方法一： 这个方法我正在用，呵呵，比较喜欢这种即时性的。

Mysql5.0以上的版本可以支持将执行比较慢的SQL语句记录下来。

mysql> show variables like 'long%'; 注：这个long_query_time是用来定义慢于多少秒的才算“慢查询”

+-----------------+-----------+
 | Variable_name | Value |
 +-----------------+-----------+
 | long_query_time | 10.000000 |
 +-----------------+-----------+
 1 row in set (0.00 sec)
 mysql> set long_query_time=1; 注： 我设置了1, 也就是执行时间超过1秒的都算慢查询。
 Query OK, 0 rows affected (0.00 sec)
 mysql> show variables like 'slow%';
 +---------------------+---------------+
 | Variable_name | Value |
 +---------------------+---------------+
 | slow_launch_time | 2 |
 | slow_query_log | ON | 注：是否打开日志记录
 | slow_query_log_file | /tmp/slow.log | 注： 设置到什么位置
 +---------------------+---------------+
 3 rows in set (0.00 sec)

 mysql> set global slow_query_log='ON' 注：打开日志记录
 一旦slow_query_log变量被设置为ON，mysql会立即开始记录。
 /etc/my.cnf 里面可以设置上面MYSQL全局变量的初始值。
 long_query_time=1
 slow_query_log_file=/tmp/slow.log

方法二:mysqldumpslow命令

 /path/mysqldumpslow -s c -t 10 /tmp/slow-log
 这会输出记录次数最多的10条SQL语句，其中：
 -s, 是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙；
 -t, 是top n的意思，即为返回前面多少条的数据；
 -g, 后边可以写一个正则匹配模式，大小写不敏感的；
 比如
 /path/mysqldumpslow -s r -t 10 /tmp/slow-log
 得到返回记录集最多的10个查询。
 /path/mysqldumpslow -s t -t 10 -g “left join” /tmp/slow-log
 得到按照时间排序的前10条里面含有左连接的查询语句。
最后总结一下节点监控的好处
1． 轻量级的监控，而且是实时的，还可以根据实际的情况来定制和修改
2． 设置了过滤程序，可以对那些一定要跑的语句进行过滤
3． 及时发现那些没有用索引，或者是不合法的查询，虽然这很耗时去处理那些慢语句，但这样可以避免数据库挂掉，还是值得的
4. 在数据库出现连接数过多的时候，程序会自动保存当前数据库的processlist，DBA进行原因查找的时候这可是利器
5. 使用mysqlbinlog 来分析的时候，可以得到明确的数据库状态异常的时间段
有些人会建义我们来做mysql配置文件设置

调节tmp_table_size 的时候发现另外一些参数
Qcache_queries_in_cache 在缓存中已注册的查询数目
Qcache_inserts 被加入到缓存中的查询数目
Qcache_hits 缓存采样数数目
Qcache_lowmem_prunes 因为缺少内存而被从缓存中删除的查询数目
Qcache_not_cached 没有被缓存的查询数目 (不能被缓存的，或由于 QUERY_CACHE_TYPE)
Qcache_free_memory 查询缓存的空闲内存总数
Qcache_free_blocks 查询缓存中的空闲内存块的数目
Qcache_total_blocks 查询缓存中的块的总数目
Qcache_free_memory 可以缓存一些常用的查询,如果是常用的sql会被装载到内存。那样会增加数据库访问速度

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