MySQL에서 느린 SQL 문을 찾는 방법

본 글에서는 MySQL의 query에서 느린 SQL 문을 찾는 방법을 주로 소개하고 있습니다. 필요한 친구는

에서 느린 SQL 문 찾는 방법을 참고하세요. mysql SQL 문은 어떻습니까? 이는 많은 사람들에게 문제가 될 수 있습니다. MySQL은 실행 효율성이 낮은 SQL 문을 찾기 위해 느린 쿼리 로그를 사용합니다. --log-slow-queries[=file_name] 옵션으로 시작할 때, mysqld A 로그 실행 시간이 long_query_time초를 초과하는 모든 SQL 문을 포함하는 파일이 기록되며, 이 로그 파일을 보면 효율성이 떨어지는 SQL을 찾을 수 있습니다. 다음은 MySQL에서 느린 SQL 문을 쿼리하는 방법을 설명합니다

1. MySQL 데이터베이스에는 비효율적인 SQL 문을 적시에 캡처하는 데 도움이 되는 여러 구성 옵션이 있습니다

1, Slow_query_log

이 파라미터를 ON으로 설정하면 실행 시간이 특정 값을 초과하는 SQL 문을 캡처할 수 있습니다.

2, long_query_time

SQL 문 실행 시간이 이 값을 초과하면 로그에 기록되므로 1 이하로 설정하는 것이 좋습니다.

3, Slow_query_log_file

로그 파일명입니다.

4, log_queries_not_using_indexes

이 매개변수는 ON으로 설정되어 인덱스를 사용하지 않는 모든 SQL 문을 캡처할 수 있지만, 이 SQL 문은 매우 빠르게 실행될 수 있습니다.

2. mysql에서 sql 문의 효율성을 확인하는 방법

1. 쿼리 로그를 통해

(1)에서 MySQL 느린 쿼리를 활성화합니다. Windows

Windows 시스템의 MySQL의 구성 파일은 일반적으로 my.ini [mysqld]에 있으며

코드는 다음과 같습니다

로그-느린 -queries=F:/MySQL/log/mysqlslowquery. log
long_query_time = 2

(2), Linux에서 MySQL 느린 쿼리 활성화

Windows 시스템의 MySQL 구성 파일은 일반적으로 my.cnf에 있습니다. [mysqld] 아래에

을 추가하면 코드는

log-slow-queries=/data/mysqldata/slowquery와 같습니다. log
long_query_time=2

설명

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

은 느린 쿼리 로그가 저장되는 위치입니다. 일반적으로 이 디렉터리에는 MySQL 실행 계정에 대한 쓰기 권한이 있어야 합니다. 이 디렉터리는 일반적으로
long_query_time=2의 MySQL 데이터 저장 디렉터리로 설정됩니다. 2 2초 이상 걸릴 때까지 쿼리가 기록되지 않음을 나타냅니다.

2.show processlist 명령

WSHOW PROCESSLIST는 실행 중인 스레드를 표시합니다. mysql admin processlist 문을 사용하여 이 정보를 얻을 수도 있습니다.

각 열의 의미와 목적:

ID 열

식별자를 사용하면 매우 유용합니다. 이를 종료하는 명령입니다. 이 쿼리는 /*/mysqladmin 프로세스 번호를 종료합니다.

사용자 열

에는 이전 사용자가 표시됩니다. 루트가 아닌 경우 이 명령은 권한 내의 SQL 문만 표시합니다.

호스트 열

에는 이 명령문이 전송된 IP와 포트가 표시됩니다. 문제가 있는 진술을 발행한 사용자를 추적하는 데 사용됩니다.

db 열

은 이 프로세스가 현재 어떤 데이터베이스에 연결되어 있는지 보여줍니다.

명령 열

에는 현재 연결의 실행된 명령이 표시됩니다. 일반적으로 절전 모드, 쿼리 및 연결입니다.

시간 열

이 상태의 지속 시간(초)입니다.

상태 열

은 현재 연결을 사용하는 SQL 문의 상태를 표시하는 매우 중요한 열입니다. 모든 상태 설명은 나중에 설명됩니다. 쿼리를 예로 들어 SQL 문은 tmp 테이블에 복사하고, 정렬하고, S끝을 거쳐야 할 수도 있습니다. 데이터 및 기타 상태를 완료하기 전에

정보 열

에 표시되는 SQL 문은 길이가 제한되어 있으므로 긴 SQL 문은 완전히 표시되지 않습니다. 문제 진술을 판단하는 중요한 기초.

이 명령에서 가장 중요한 것은 상태 열입니다. mysql에 나열되는 상태는 주로 다음과 같습니다.

Checking table
은 데이터 테이블을 확인하는 것입니다(자동입니다).
테이블 닫기
테이블의 수정된 데이터가 디스크로 플러시되고, 소진된 테이블이 닫힙니다. 이는 빠른 작업이지만 그렇지 않은 경우에는 디스크 공간이 꽉 찼는지 또는 디스크 부하가 심한지 확인해야 합니다.
Connect Out
복제 슬레이브 서버가 마스터 서버에 연결 중입니다.
디스크의 tmp 테이블에 복사
임시 결과 세트가 tmp_table_size보다 크기 때문에 메모리 절약을 위해 임시 테이블을 메모리 저장소에서 디스크 저장소로 변환하는 중입니다.
tmp 테이블 생성
　일부 쿼리 결과를 저장하기 위해 임시 테이블을 생성하는 중입니다.
deleting from main 테이블
서버에서 다중 테이블 삭제의 첫 번째 부분을 실행 중이며 첫 번째 테이블이 방금 삭제되었습니다.
참조 테이블에서 삭제
서버가 다중 테이블 삭제의 두 번째 부분을 수행하고 있으며 다른 테이블의 레코드를 삭제하고 있습니다.
테이블 플러시ing
FLUSH TABLES를 실행하고 다른 스레드가 데이터 테이블을 닫을 때까지 기다립니다.
Killed
스레드에 종료 요청이 전송되면 스레드는 종료 플래그를 확인하고 다음 종료 요청을 포기합니다. MySQL은 각 메인 루프 에서 종료 플래그를 확인하지만 어떤 경우에는 스레드가 짧은 시간 후에 종료될 수 있습니다. 스레드가 다른 스레드에 의해 잠긴 경우 잠금이 해제되는 즉시 종료 요청이 적용됩니다.
잠김
은 다른 쿼리에 의해 잠겨 있습니다.
데이터 전송
SELECT 쿼리의 레코드를 처리하고 결과를 클라이언트로 전송하는 중입니다.
그룹
에 대해 정렬하는 것은 GROUP BY를 정렬하는 것입니다.
order
정렬은 ORDER BY 정렬입니다.
테이블 열기
이 프로세스는 다른 요인의 방해를 받지 않는 한 빠르게 진행되어야 합니다. 예를 들어, ALTER TABLE 또는 LOCK TABLE 문이 실행되기 전에는 다른 스레드에서 데이터 테이블을 열 수 없습니다. 테이블을 열려고 합니다.
중복 제거
SELECT DISTINCT 쿼리가 실행 중이지만 MySQL은 이전 단계에서 이러한 중복 레코드를 최적화할 수 없습니다. 따라서 MySQL은 결과를 클라이언트에 보내기 전에 중복 레코드를 다시 제거해야 합니다.
테이블 다시 열기
테이블에 대한 잠금을 획득했지만 테이블 구조를 수정한 후에 잠금을 획득해야 합니다. 잠금이 해제되고, 데이터 테이블이 닫히고, 데이터 테이블을 다시 열려고 시도됩니다.
정렬을 통한 복구
복구 지시어는 정렬을 통해 인덱스를 생성하는 것입니다.
키캐시
로 복구 복구 지시어는 캐시 인덱스를 활용하여 새 인덱스를 하나씩 생성합니다. 정렬하여 복구하는 것보다 속도가 느립니다.
update
　update 조건에 맞는 레코드를 찾는 중입니다. UPDATE가 관련 레코드를 수정하기 전에 완료되어야 합니다.
잠자기
는 클라이언트가 새 요청을 보내기를 기다리고 있습니다.
시스템 잠금
은 외부 시스템 잠금을 얻기 위해 기다리고 있습니다. 현재 동시에 동일한 테이블을 요청하는 여러 mysqld 서버를 실행하고 있지 않은 경우 --skip-external-locking 매개변수를 추가하여 외부 시스템 잠금을 비활성화할 수 있습니다.
잠금 업그레이드
　INSERT DELAYED가 새 레코드를 삽입하기 위해 잠금 테이블을 얻으려고 합니다.
업데이트
는 일치하는 레코드를 검색하여 수정하는 것입니다.

사용자 잠금
　GET_LOCK()을 기다리는 중입니다.
테이블을 기다리는 중
데이터 테이블 구조가 수정되었으며 새 구조를 얻으려면 데이터 테이블을 다시 열어야 한다는 알림이 스레드에 전달됩니다. 그런 다음 데이터 테이블을 다시 열려면 다른 모든 스레드가 테이블을 닫을 때까지 기다려야 합니다. 이 알림은 FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE 또는 OPTIMIZE TABLE 상황에서 생성됩니다.
한을 기다리는 중dl삽입
　INSERT DELAYED가 보류 중인 모든 삽입 작업을 처리하고 새 요청을 기다리고 있습니다.
대부분의 상태는 매우 빠른 작업에 해당합니다. 하나의 스레드가 몇 초 동안 동일한 상태로 유지되는 한 문제가 있을 수 있으므로 확인이 필요합니다.
위에 나열되지 않은 다른 상태도 있지만 대부분은 서버에 오류가 있는지 확인하는 데만 유용합니다.

예:

3. SQL 실행 상태 이해

설명은 mysql이 인덱스를 사용하여 선택 문을 처리하고 테이블을 조인하는 방법을 보여줍니다. 더 나은 인덱스를 선택하고 더 최적화된 쿼리 문을 작성하는 데 도움이 될 수 있습니다.

사용하려면 select 문 앞에 explain을 추가하면 됩니다.

예:

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

결과는 그림과 같습니다.

EXPLAIN 열 설명

table

이 데이터 행이 어느 테이블에 관한 것인지 표시

type

연결에 어떤 유형이 사용되는지 보여주는 중요한 열입니다. 최고부터 최악까지의 조인 유형은 const, eq_reg, ref, range, indexhe 및 ALL

possible_keys

로, Index에서 가능한 애플리케이션을 보여줍니다. 이 테이블. 비어 있으면 인덱스를 사용할 수 없습니다. 해당 도메인

key

실제 인덱스에 대한 WHERE 문에서 적절한 문을 선택할 수 있습니다. NULL인 경우 인덱스가 사용되지 않습니다. MYSQL이 최적화되지 않은 인덱스를 선택하는 경우는 거의 없습니다. 이 경우 SELECT 문에서 USE INDEX(indexname)를 사용하여 인덱스를 강제로 사용하거나 IGNORE INDEX(indexname)를 사용하여 MYSQL이 인덱스

key_len<🎜에서 사용하는 인덱스를 무시하도록 할 수 있습니다. >

길이. 길이가 짧을수록 정확도를 잃지 않는 것이 좋습니다.

ref

인덱스의 어떤 열이 사용되는지 표시, 가능하면 상수

행

숫자 MYSQL이 요청한 데이터를 반환하기 위해 확인해야 한다고 생각하는 행

추가

MYSQL이 쿼리를 구문 분석하는 방법에 대한 추가 정보입니다. 표 4.3에서 논의하겠지만 여기서 볼 수 있는 나쁜 예는 임시 사용 및 파일 정렬 사용입니다. 즉, MYSQL은 인덱스를 전혀 사용할 수 없으며 결과적으로 검색 속도가 매우 느려집니다

추가 열에서 반환된 설명

Distinct

의 의미 MYSQL이 행의 합집합과 일치하는 행을 찾으면 더 이상 검색하지 않습니다.

아님 존재합니다

MYSQL 최적화

LEFT JOIN, LEFT JOIN 기준과 일치하는 행을 찾으면 더 이상

각각에 대해 확인된 범위를 검색하지 않습니다. Record(index map :#)

이상적인 인덱스를 찾을 수 없으므로 이전 테이블의 각 행 조합에 대해 MYSQL은 어떤 인덱스가 사용되었는지 확인하고 이를 사용하여 이전 테이블의 행을 반환합니다. 테이블. 이는 인덱스

파일 정렬 사용

을 사용하는 가장 느린 연결 중 하나입니다. 이 내용을 보면 쿼리를 최적화해야 합니다. MYSQL에서는 반환된 행을 정렬하는 방법을 알아내기 위해 추가 단계가 필요합니다. 정렬 키 값과 일치 조건을 저장하는 모든 행의 행 포인터와 연결 유형에 따라 모든 행을 정렬합니다.

실제 작업 테이블에서 반환된 인덱스를 사용하면 요청된 모든 열이 테이블은 동일한 인덱스의 일부입니다

임시 사용

이 내용을 보면 쿼리에 최적화가 필요합니다. 여기서 MYSQL은 결과를 저장하기 위해 임시 테이블을 생성해야 합니다. 이는 일반적으로

Where Used

가 WHERE 절을 사용하는 대신 다른 열 집합에서 ORDER BY를 수행할 때 발생합니다. 다음 테이블과 일치하거나 사용자에게 반환될 행을 제한합니다. 테이블의 모든 행을 반환하고 싶지 않고 조인 유형이 ALL 또는 인덱스이거나 쿼리에 문제가 있는 경우에 발생합니다. 다양한 조인 유형에 대한 설명(효율성 순으로 정렬)

const

이 쿼리와 일치하는 테이블 레코드의 최대 값입니다(인덱스는 기본 키 또는 고유 인덱스일 수 있음). 행이 하나만 있기 때문에 이 값은 실제로 상수입니다. MYSQL은 이 값을 먼저 읽은 다음 이를 상수로 취급하기 때문입니다.

eq_ref

연결에서 MYSQL은 이전 테이블을 쿼리할 때 각 레코드 유니온에 대해 하나의 레코드를 읽습니다.

ref

이 연결 유형은 다음과 같습니다. 쿼리할 때만 사용됩니다. 고유 키나 기본 키가 아닌 키가 사용되거나 이러한 유형의 일부인 경우(예: 가장 왼쪽 접두사 사용) 발생합니다. 이전 테이블의 각 행 조인에 대해 모든 레코드는 테이블에서 읽혀집니다. 이 유형은 인덱스를 기준으로 일치하는 레코드 수에 크게 의존합니다.

범위

이 조인 유형은 인덱스를 사용하여 > 또는 < 무언가를 찾으려면

index

이 조인 유형은 이전 테이블의 모든 레코드 통합을 전체 검색합니다(인덱스가 일반적으로 인덱스보다 작기 때문에 ALL보다 낫습니다). 테이블 데이터)

ALL

이 연결 유형은 이전의 각 레코드를 공동으로 전체 검색을 수행합니다. 이는 일반적으로 좋지 않으므로 피해야 합니다

MySQL - 보기 느림 SQL

느린 SQL 로그 파일을 보려면 MySQL이 활성화되어 있는지 확인(1) 느린 SQL 로그가 활성화되어 있는지 확인

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会被装载到内存。那样会增加数据库访问速度

위 내용은 MySQL에서 느린 SQL 문을 찾는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!