Meituan 인터뷰 질문: 느린 SQL을 본 적이 있습니까? 어떻게 해결되었나요?

느린 SQL에 대해서 면접관님과 저는 오랫동안 이야기를 나눴고, 면접관님도 굉장히 겸손하시고 항상 고개를 끄덕이시더라구요. 마지막에도 "你先回去等通知吧！"이라고 했어요.

그래서 저는 이 느린 SQL 기술 포인트를 여러분과 공유하기로 결정했습니다. 다음에 비슷한 면접을 보게 된다면, 원활하고 쉽게 원하는 제안을 받을 수 있기를 바라겠습니다.

세상에서 가장 큰 기쁨은 모두가 할 수 없다고 하는데 해낸다는 것입니다!

느린 SQL이란?

MySQL의 느린 쿼리 로그는 MySQL에서 제공하는 로그 기록입니다. MySQL에서 쿼리 시간이 설정된 임계값(long_query_time)을 초과(이상)한 명령문을 기록하여 느린 쿼리 로그에 기록하는 데 사용됩니다.

그 중 long_query_time의 기본값은 10이고, 단위는 초입니다. 즉, 기본적으로 SQL 쿼리 시간이 10초를 초과하면 느린 SQL로 간주됩니다.

느린 SQL 로그를 활성화하는 방법은 무엇입니까?

MySQL에서는 느린 SQL 로그가 기본적으로 켜지지 않습니다. 즉, 느린 SQL이 발생하더라도 어떤 SQL이 느린 SQL인지 알아야 할 경우 수동으로 켜야 합니다. 느린 SQL 로그에.

느린 SQL 활성화 여부는 다음 명령어를 통해 확인할 수 있습니다.

-- 查看慢查询日志是否开启
show variables like '%slow_query_log%';

여기에 그림 설명 삽입

명령어를 통해 Slow_query_log 항목이 OFF로 되어 있는 것을 볼 수 있는데, 이는 느린 SQL 로그가 켜져 있지 않음을 나타냅니다. 또한 느린 SQL 로그가 저장된 디렉터리와 로그 파일 이름도 볼 수 있습니다.

느린 SQL 로그를 활성화하고 다음 명령을 실행해 보겠습니다.

set global slow_query_log = 1;

여기에서 활성화된 것은 현재 데이터베이스이며 데이터베이스를 다시 시작한 후에는 유효하지 않게 됩니다.

느린 SQL 로그를 켠 후 다시 확인하세요.

slow_query_log 항목이 ON으로 설정되어 성공적으로 활성화되었음을 나타냅니다.

위에서 언급했듯이 느린 SQL의 기본 시간은 10초입니다. 다음 명령을 통해 느린 SQL의 기본 시간을 확인할 수 있습니다.

show variables like '%long_query_time%';

여기에 그림 설명 삽입

항상 사용할 수는 없습니다. 기본값은 많은 기업에서 더 짧거나 긴 시간을 요구할 수 있으므로 이때 기본 시간을 수정해야 합니다. 수정 명령은 다음과 같습니다.

set long_query_time = 3;

수정 후 3초로 변경되었는지 확인해 보겠습니다.

여기서 참고할 사항: 영구적으로 적용하려면 MySQL에서 my.cnf 구성 파일도 수정해야 합니다.

[mysqld]
slow_query_log=1
slow_query_log_file=/var/lib/mysql/atguigu-slow.log
long_query_time=3
log_output=FILE

참고: 운영 체제마다 구성이 약간 다릅니다.

Linux 운영 체제

mysql 구성 파일 my.cnf에

log-slow-queries=/var/lib/mysql/slowquery.log를 추가합니다(로그 파일 저장 위치를 ​​지정합니다. 비어 있을 수 있습니다. 시스템은 기본 파일(host_name-slow.log)

long_query_time=2(초과된 시간을 기록합니다. 기본값은 10초입니다)

log-queries-not-using-indexes (log下来没有使用索引的query,可以根据情况决定是否开启)

log-long-format (如果设置了，所有没有使用索引的查询也将被记录)

Windows操作系统中

在my.ini的[mysqld]添加如下语句：

log-slow-queries = E:\web\mysql\log\mysqlslowquery.log

long_query_time = 3(其他参数如上)

执行一条慢SQL，因为我们前面已经设置好了慢SQL时间为3秒，所以，我们只要执行一条SQL时间超过3秒即可。

SELECT SLEEP(4);

该SQL耗时4.024秒，下面我们就来查看慢SQL出现了多少条。

使用命令：

show global status like '%Slow_queries%';

查询SQL历程

找到慢SQL日志文件，打开后就会出现类似下面这样的语句；

# Time: 2021-07-20T09:17:49.791767Z
# User@Host: root[root] @ localhost []  Id:   150
# Query_time: 0.002549  Lock_time: 0.000144 Rows_sent: 1  Rows_examined: 4079
SET timestamp=1566292669;
select * from city where Name = 'Salala';

简单说明：

1.Time 该日志记录的时间

2.User @Host MySQL登录的用户和登录的主机地址

3.Query_time一行 第一个时间是查询的时间、第二个是锁表的时间、第三个是返回的行数、第四个是扫描的行数

4.SET timestamp 这一个是MySQL查询的时间

5.sql语句 这一行就很明显了，表示的是我们执行的sql语句

切记

long_query_time=0으로 설정하면 모든 쿼리 SQL 문이 느린 SQL 로그 파일에 출력된다는 의미입니다.

느린 SQL을 찾는 방법은 무엇입니까?

일반적으로 느린 SQL을 찾는 방법에는 두 가지가 있습니다.

첫 번째 방법: 느린 쿼리 찾기 SQL은 두 가지 표현으로 판단할 수 있습니다

• 시스템 수준 표현:
• sar 명령과 top 명령을 사용하여 현재 시스템 상태를 확인하세요.
• Prometheus 및 Grafana모니터링 도구를 사용하여 현재 시스템 상태를 확인할 수도 있습니다.
• CPU가 심각하게 소모됩니다.
• IO 심각하게 기다리고 있습니다
• 페이지 응답 시간이 너무 깁니다
• 프로젝트 로그에 시간 초과 및 기타 오류가 있습니다
• SQL 문 표현:
• sqllong statement
• sql 명세서 실행 시간은 너무 길다. 전체 테이블 스캔에서 데이터를 얻는다
• rows 및 cost
  는 매우 큽니다.
• 두 번째: 다양한 데이터베이스에 따라 다양한 방법을 사용합니다. 질문을 받는 방법 SQL

  • MySQL:
  • 慢查询日志
  • 测试工具loadrunner
  • ptquery工具
  • Oracle:
  • AWR报告
  • 测试工具loadrunner
  • 相关内部视图vsession_wait
  • GRID CONTROL监控工具

  熟悉慢SQL日志分析工具吗？

  如果开启了慢SQL日志后，可能会有大量的慢SQL日志产生，此时再用肉眼看，那是不太现实的，所以大佬们就给我搞了个工具：mysqldumpslow。

  mysqldumpslow能将相同的慢SQL归类，并统计出相同的SQL执行的次数，每次执行耗时多久、总耗时，每次返回的行数、总行数，以及客户端连接信息等。

  通过命令

  mysqldumpslow --help

  可以看到相关参数的说明：

  ~# mysqldumpslow --help
  Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
  
  Parse and summarize the MySQL slow query log. Options are
  
    --verbose    verbose
    --debug      debug
    --help       write this text to standard output
  
    -v           verbose
    -d           debug
    -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                  al: average lock time
                  ar: average rows sent
                  at: average query time
                   c: count
                   l: lock time
                   r: rows sent
                   t: query time  
    -r           reverse the sort order (largest last instead of first)
    -t NUM       just show the top n queries
    -a           don't abstract all numbers to N and strings to 'S'
    -n NUM       abstract numbers with at least n digits within names
    -g PATTERN   grep: only consider stmts that include this string
    -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
                 default is '*', i.e. match all
    -i NAME      name of server instance (if using mysql.server startup script)
    -l           don't subtract lock time from total time

  比较常用的参数有这么几个：

  -s 指定输出的排序方式
     t  : 根据query time(执行时间)进行排序；
     at : 根据average query time(平均执行时间)进行排序；（默认使用的方式）
     l  : 根据lock time(锁定时间)进行排序；
     al : 根据average lock time(平均锁定时间)进行排序；
     r  : 根据rows(扫描的行数)进行排序；
     ar : 根据average rows(扫描的平均行数)进行排序；
     c  : 根据日志中出现的总次数进行排序；
  -t 指定输出的sql语句条数；
  -a 不进行抽象显示(默认会将数字抽象为N，字符串抽象为S)；
  -g 满足指定条件，与grep相似；
  -h 用来指定主机名(指定打开文件，通常慢查询日志名称为“主机名-slow.log”，用-h exp则表示打开exp-slow.log文件)；

  使用方式

  mysqldumpslow常用的使用方式如下：

  # mysqldumpslow -s c slow.log

  如上一条命令，应该是mysqldumpslow最简单的一种形式，其中-s参数是以什么方式排序的意思，c指代的是以总数从大到小的方式排序。-s的常用子参数有：c: 相同查询以查询条数和从大到小排序。t: 以查询总时间的方式从大到小排序。l: 以查询锁的总时间的方式从大到小排序。at: 以查询平均时间的方式从大到小排序。al: 以查询锁平均时间的方式从大到小排序。

  同样的，还可以增加其他参数，实际使用的时候，按照自己的情况来。

  其他常用方式：

  # 得到返回记录集最多的10 个SQL
  mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log
  
  # 得到访问次数最多的10 个SQL
  mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log
  
  # 得到按照时间排序的前10 条里面含有左连接的查询语句
  mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log
  
  # 另外建议在使用这些命令时结合| 和more 使用，否则有可能出现爆屏情况
  mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

  接下，我们来个实际操作。

  实操

  root@yunzongjitest1:~# mysqldumpslow -s t -t 3
  
  Reading mysql slow query log from /var/lib/mysql/exp-slow.log /var/lib/mysql/yunzongjitest1-slow.log
  Count: 464  Time=18.35s (8515s)  Lock=0.01s (3s)  Rows=90884.0 (42170176), root[root]@localhost
    select ************
  
  Count: 38  Time=11.22s (426s)  Lock=0.00s (0s)  Rows=1.0 (38), root[root]@localhost
    select *********** not like 'S'
  
  Count: 48  Time=5.07s (243s)  Lock=0.02s (1s)  Rows=1.0 (48), root[root]@localhost
    select ********='S'

  这其中的SQL语句因为涉及某些信息，所以我都用*号将主体替换了，如果希望得到具体的值，使用-a参数。

  使用mysqldumpslow查询出来的摘要信息，包含了这些内容：

  Count: 464 ：表示慢查询日志总共记录到这条sql语句执行的次数；

  Time=18.35s (8515s)：18.35s表示平均执行时间（-s at)，8515s表示总的执行时间(-s t)；

  Lock=0.01s (3s)：与上面的Time相同，第一个表示平均锁定时间(-s al)，括号内的表示总的锁定时间(-s l)（也有另一种说法，说是表示的等待锁释放的时间）；

  Rows=90884.0 (42170176): 첫 번째 값은 스캔된 평균 행 수(-s ar)를 나타내고, 괄호 안의 값은 스캔된 총 행 수(-s r)를 나타냅니다.

위 내용은 Meituan 인터뷰 질문: 느린 SQL을 본 적이 있습니까? 어떻게 해결되었나요?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!