Zusammenfassung und Austausch von Ideen zur Optimierung langsamer MySQL-Abfragen

Dieser Artikel vermittelt Ihnen relevantes Wissen über MySQL, in dem hauptsächlich Probleme im Zusammenhang mit der Optimierung langsamer Abfragen vorgestellt werden, einschließlich der Verwendung langsamer Abfrageprotokolle zum Auffinden langsamer Abfrage-SQL, der Analyse langsamer Abfrage-SQL durch Erklären und der größtmöglichen Änderung von SQL Lassen Sie SQL verwenden Schauen wir uns den Index an. Ich hoffe, er wird für alle hilfreich sein.

Empfohlenes Lernen: MySQL-Video-Tutorial

1 Ideen zur Optimierung langsamer Abfragen

Wenn eine langsame Abfrage auftritt, lautet die Optimierungsidee:

• Verwenden Sie langsame Abfrageprotokolle, um langsame Abfrage-SQL zu finden

• Analysieren Sie das langsame Abfrage-SQL durch EXPLAIN.

• Ändern Sie das SQL und versuchen Sie, den SQL-Index zu erstellen Der angegebene Zeitschwellenwert ist für uns praktisch, um langsame Abfragen zu finden und die entsprechenden SQL-Anweisungen zu optimieren.

Überprüfen Sie zunächst die globalen Variablen im Zusammenhang mit langsamen Abfragen in MySQL:

mysql> show global variables like '%quer%';
+----------------------------------------+-------------------------------+
| Variable_name                          | Value                         |
+----------------------------------------+-------------------------------+
| binlog_rows_query_log_events           | OFF                           |
| ft_query_expansion_limit               | 20                            |
| have_query_cache                       | YES                           |
| log_queries_not_using_indexes          | OFF                           |
| log_throttle_queries_not_using_indexes | 0                             |
==========================================================================
| long_query_time                        | 10.000000                     |【1】慢查询的时间阈值
==========================================================================
| query_alloc_block_size                 | 8192                          |
| query_cache_limit                      | 1048576                       |
| query_cache_min_res_unit               | 4096                          |
| query_cache_size                       | 16777216                      |
| query_cache_type                       | OFF                           |
| query_cache_wlock_invalidate           | OFF                           |
| query_prealloc_size                    | 8192                          |
==========================================================================
| slow_query_log                         | OFF                           |【2】慢查询日志是否开启
| slow_query_log_file                    | /var/lib/mysql/Linux-slow.log |【3】慢查询日志文件存储位置
==========================================================================
+----------------------------------------+-------------------------------+
15 rows in set (0.00 sec)

Hier konzentrieren wir uns hauptsächlich auf drei Variablen:

long_query_time, der Zeitschwellenwert für langsame Abfragen in Sekunden, wenn die Ausführungszeit einer SQL-Anweisung überschritten wird Dieser Wert wird von MySQL als langsame Abfrage identifiziert. Slow_query_log ist standardmäßig deaktiviert . Der Speicherort der Protokolldatei für langsame Abfragen

Die Funktion für das Standardprotokoll für langsame Abfragen ist deaktiviert, daher müssen wir sie aktivieren Sie müssen in der Konfigurationsdatei geändert werden, um dauerhaft wirksam zu sein.

• 3 EXPLAIN

Wir können EXPLAIN verwenden, um die Ausführung von SQL-Anweisungen zu analysieren, zum Beispiel:

• # 开启慢查询日志
  mysql> set global slow_query_log=ON;
  Query OK, 0 rows affected (0.00 sec)
  # 设置慢查询时间阈值
  mysql> set long_query_time=1;
  Query OK, 0 rows affected (0.00 sec)

  Die Ausführungsergebnisse sind wie folgt. Sie können sehen, dass es viele Felder gibt

• Wir betrachten hauptsächlich einige wichtige Felder:

select_type stellt den Abfragetyp der Abfrageanweisung dar, einschließlich einfacher Abfrage, Unterabfrage usw.

table stellt die Abfragetabelle dar, die nicht unbedingt vorhanden ist. Möglicherweise handelt es sich um eine temporäre Tabelle, die in dieser Abfrage erhalten wird

type stellt den Abruftyp dar, verwendet vollständigen Tabellenscan, Indexscan usw.

possible_keys stellt die Indexspalten dar, die verwendet werden können.

• keys stellt die Indexspalten dar, die tatsächlich in der Abfrage verwendet werden bestimmt durch den Abfrageoptimierer

3.1 Select_Type-Feld
• 3.2 Typfeld

Für die InnoDB-Speicher-Engine ist die Typspalte normalerweise „all“ oder „index“.

In Bezug auf den Wert des Typfelds wird die Ausführungsleistung des entsprechenden SQL von oben nach unten allmählich schlechter. 3.3 zusätzliches Feld rreeeInterception Teildaten:

Führen Sie die folgende SQL-Anweisung aus, ohne Indexfelder zu verwenden:

mysql> explain select sum(1+2);

Die vom Navicat-Tool angezeigte Abfragezeit ist nicht die Zeit, zu der MySQL tatsächlich SQL ausführt, sondern andere Zeiten:

SQL-spezifisch Für die Abfragezeit können Sie das langsame Abfrageprotokoll anzeigen:

create table user_info_large (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
`account` VARCHAR(20) NOT NULL COMMENT '用户账号',
`name` VARCHAR(20) NOT NULL COMMENT '用户名',
`password` VARCHAR(20) not null COMMENT '用户密码',
`area` VARCHAR(20) NOT NULL COMMENT '用户地址',
`signature` VARCHAR(50) not null COMMENT '个性签名',
PRIMARY KEY (`id`) COMMENT '主键',
UNIQUE (`account`) COMMENT '唯一索引',
KEY `index_area_signture` (`area`,  `signature`)  COMMENT '组合索引'
);

Erläuterung einiger Informationen:

Zeit: Die Startzeit der SQL-Ausführung

Abfragezeit: Die aufgewendete Zeit SQL-Anweisungsabfrage, Sie können sehen, dass es 10 Sekunden gedauert hat Uhr

Lock_time: Die Zeit, die auf die Sperrtabelle gewartet wird

Rows_sent: Die Anzahl der von der Anweisung zurückgegebenen Datensätze

Rows_examined: Die Anzahl der Von der Speicher-Engine zurückgegebene Datensätze

Die langsame Abfrage wird nicht ausgeführt. Wenn das langsame Abfrageprotokoll aufgezeichnet wird, wird es erst im Protokoll aufgezeichnet, nachdem auf den Abschluss der Ausführung gewartet wurde.

Wir können show Processlist verwenden, um den Thread anzuzeigen, der SQL ausführt.

Führen Sie die folgende Anweisung erneut aus und verwenden Sie das Indexkontofeld:

mysql> select count(id) from user_info_large;
+-----------+
| count(id) |
+-----------+
|   2000000 |
+-----------+
1 row in set (0.38 sec)

Sehen Sie sich das langsame Abfrageprotokoll an und es wird nicht aufgezeichnet.

Jetzt verwenden Sie EXPLAIN, um die Ausführung von SQL-Anweisungen anzuzeigen:

SELECT name from user_info_large ORDER BY name desc limit 0,100000;

Die Analyse lautet wie folgt:

可以看到没有使用到索引，type 为 ALL 表示全表扫描，效率最差，并且 Extra  也是外部排序。

再看看这条 SQL 语句：

explain SELECT account from user_info_large ORDER BY account desc limit 0,100000;

分析情况如下：

type 为 index，使用了索引，使用的索引字段为 account，Extra 显示为使用索引排序。

因此，在实际开发中，我们可以针对慢查询的 SQL，使用 explain 分析语句，根据分析情况以及索引的设计，重新设计 SQL 语句，让 SQL 语句尽量走索引，走合适的索引。

5 优化器与索引

在执行 SQL 时，MySQL 的优化器会根据情况选择索引，但并不能保证其执行时间一定最短，我们可以根据实际情况使用 force key (index) 让 SQL 语句强制走某个索引。

例如，以下语句执行后，key 字段为 account，并没有走主键索引。

explain SELECT count(id) from user_info_large;

如果使用 force key，就可以强制令语句走主键索引。

explain SELECT count(id) from user_info_large force key (PRIMARY);

6 总结

在项目中如果发现部分 SQL 语句执行缓慢，等待查询时间长，可以考虑优化慢查询，具体思路为：

• 通过慢查询日志定位 SQL

• 使用 explain 分析 SQL

• 修改 SQL，令其走合适的索引

 在使用 explain 时，我们主要关注这些字段：

• type

• key

• Extra

在编写 SQL 使用索引的时候，我们尽量注意一下规则：

• 模糊查询不要使用通配符 % 开头，例如 like '%abc'

• 使用 or 关键字时，两边的字段都要有索引。或者使用 union 替代 or

• 使用复合索引遵循最左原则

• 索引字段不要参加表达式运算、函数运算

推荐学习：mysql视频教程

Das obige ist der detaillierte Inhalt vonZusammenfassung und Austausch von Ideen zur Optimierung langsamer MySQL-Abfragen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!