mysql の遅いクエリの最適化に関するアイデアの概要と共有

この記事では、mysql に関する関連知識を提供します。主に、スロー クエリ ログを使用してスロー クエリ SQL を特定することや、Explain によるスロー クエリの分析など、スロー クエリの最適化に関連する問題を紹介します。SQL のクエリと SQL の変更SQL に可能な限りインデックスを作成できるようにするために、一緒に見てみましょう。

推奨学習: mysql ビデオ チュートリアル

1 遅いクエリの最適化に関するアイデア

遅いクエリが発生した場合、最適化のアイデアは次のとおりです。

• #スロー クエリ ログを使用してスロー クエリ SQL を特定する

• Explain を使用してスロー クエリ SQL

  ## を分析する

#SQL を変更し、SQL にインデックスを付けてみる
2 スロー クエリ ログ

MySQL には、クエリを記録する関数スロー クエリ ログが用意されています。時間が指定された時間しきい値を超えた SQL はログに記録されるため、遅いクエリを特定し、対応する SQL ステートメントを最適化できます。

まず、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)

ここでは主に 3 つの変数に焦点を当てます:

long_query_time、時間スロー クエリのしきい値 (秒単位)。SQL ステートメントの実行時間がこの値を超えると、MySQL はそれをスロー クエリと判断します。
slow_query_log、スロー クエリ ログ機能が有効かどうかオンにすると、スロー クエリの記録
slow_query_log_file、スロー クエリ ログ ファイルの保存場所

• #スロークエリログ機能はデフォルトでオフになっているため、有効にする必要があります機能

# 开启慢查询日志
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)

このように設定した後、MySQL は再起動時にこれらの設定を失うため、設定を変更する必要がありますファイルを永続的に有効にします。

3 Explain

Explain を使用して SQL ステートメントの実行を分析できます。たとえば:

mysql> explain select sum(1+2);

実行結果は次のとおりです。多くのステートメントがあることがわかります。フィールド

主にいくつかの重要なフィールドについて説明します。

select_type は、単純なクエリを含むクエリ ステートメントのクエリ タイプを表します。サブクエリなど

• table はクエリ テーブルを表しますが、必ずしも存在するとは限りません。このクエリで取得された一時テーブルである可能性があります。

• type は取得タイプを表します。テーブル全体のスキャン、インデックス スキャンなどを使用します。

• #possible_keys は、使用できるインデックス列を示します

• keys は、クエリで実際に使用されるインデックス列を示します。これらはクエリによって最適化されます。プロセッサが決定します。
#3.1 select_type フィールド

3.2 タイプ フィールド

InnoDB ストレージ エンジンの場合、タイプ カラムは通常、all またはインデックスです。 type フィールドの値は、上から下に向かって、対応する SQL の実行パフォーマンスが徐々に悪くなります。

#3.3 追加フィールド

4 遅いクエリ例

データ、データ テーブル構造の準備:

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 '组合索引'
);

200 万個のデータをランダムに生成

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

データの一部をインターセプト:

インデックス フィールドを使用せずに次の SQL ステートメントを実行します:

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

Navicat ツールによって表示されるクエリ時間は次のとおりです。これは MySQL が実際に SQL を実行する時間ではありません。ネットワーク送信やその他の時間が含まれます。 ：

SQL 固有のクエリ時間は、スロー クエリ ログで確認できます:

# Time: 2022-09-26T13:44:18.405459Z
# User@Host: root[root] @  [ip]  Id:  1893
# Query_time: 10.162999  Lock_time: 0.000113 Rows_sent: 100000  Rows_examined: 2100000
SET timestamp=1664199858;
SELECT name from user_info_large ORDER BY name desc limit 0,100000;

説明の一部情報:

Time: SQL 実行の開始時刻

• Query_time: SQL ステートメントのクエリにかかった時間。 10 秒

• Lock_time: ロック テーブルを待機する時間

• #Rows_sent: ステートメントによって返されるレコードの数

• Rows_examined: ストレージ エンジンから返されたレコードの数

• 実行中のスロー クエリはスロー クエリ ログに記録されません。完了後にログに記録されます。
  show processlist を使用すると、SQL を実行しているスレッドを表示できます。

次のステートメントを再度実行し、インデックス アカウント フィールドを使用します:

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

スロー クエリ ログを表示しますが、記録されていません。

ここで Explain を使用して SQL ステートメントの実行を表示します:

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

分析は次のとおりです:

可以看到没有使用到索引，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视频教程

以上がmysql の遅いクエリの最適化に関するアイデアの概要と共有の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。