mysql 느린 쿼리 최적화 아이디어 요약 및 공유

이 기사에서는 느린 쿼리 로그를 사용하여 느린 쿼리 SQL 찾기, explain을 통해 느린 쿼리 SQL 분석, SQL을 최대한 수정하는 등 느린 쿼리 최적화와 관련된 문제를 주로 소개하는 mysql에 대한 관련 지식을 제공합니다. 색인을 살펴보겠습니다. 모든 사람에게 도움이 되기를 바랍니다.

추천 학습: mysql 동영상 튜토리얼

1 느린 쿼리 최적화 아이디어

느린 쿼리가 발생할 때 최적화 아이디어는 다음과 같습니다.

• 느린 쿼리 로그를 사용하여 느린 쿼리 SQL을 찾습니다

• explain

• 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)

여기에서는 주로 세 가지 변수에 중점을 둡니다.

• long_query_time, SQL 문의 실행 시간이 초과되는 경우 느린 쿼리의 시간 임계값(초) 이 값을 사용하면 MySQL은 이를 느린 쿼리로 식별합니다

• slow_query_log.느린 쿼리 로그 기능이 켜져 있는지 여부는 기본적으로 꺼져 있습니다. . 느린 쿼리 로그 파일 저장 위치

• 기본 느린 쿼리 로그 기능이 꺼져 있으므로 활성화해야 합니다

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

실행 결과는 다음과 같습니다. 많은 필드가 있음을 알 수 있습니다.

우리는 주로 몇 가지 중요한 사항을 살펴봅니다. fields:

select_type은 단순 쿼리, 하위 쿼리 등을 포함하는 쿼리 문의 쿼리 유형을 나타냅니다.

• table은 쿼리 테이블을 나타내며 반드시 존재하지 않는 쿼리에서 얻은 임시 테이블일 수 있습니다.

• type은 검색 유형을 나타내며 전체 테이블 스캔, 인덱스 스캔 등을 사용합니다.

• possible_keys는 사용할 수 있는 인덱스 열을 나타냅니다.

• keys는 쿼리에 실제로 사용되는 인덱스 열을 나타냅니다. 쿼리 최적화 프로그램에 의해 결정됨

3.1 select_type 필드

3.2 유형 필드

InnoDB 스토리지 엔진의 경우 유형 열은 일반적으로 all 또는 index입니다. 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 '组合索引'
);

200w 데이터를 무작위로 생성

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 중국어 웹사이트의 기타 관련 기사를 참조하세요!