Meituan のインタビューでの質問: SQL が遅いと感じたことはありますか?どのように解決されましたか?

# SQL が遅いということで、面接官と長時間雑談しましたが、面接官もとても謙虚で、いつもうなずいてくれたので、大丈夫だと思いました。 最後に、「戻って通知を待ちましょう!」と言いました。

#そこで、この遅い SQL テクノロジを皆さんと共有することにしました。次回同様の面接に遭遇した際には、スムーズかつ簡単に希望する内定を獲得できることを願っています。

人生の最大の喜びは、誰もがそれをできないと言っているにもかかわらず、あなたがそれをやり遂げることです!

Slow SQL とは何ですか?

MySQL のスロー クエリ ログは、MySQL が提供するログ レコードで、クエリ時間が設定されたしきい値 (long_query_time) を超えた (超過した) MySQL ステートメントを記録するために使用されます。スロークエリログ。中央。

このうち、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.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 を見つけるには 2 つの方法があります。

最初の方法: 遅いクエリを見つける SQL は 2 つの表現を使用できます。

• システム レベルの症状を特定します。
• sar コマンドを使用し、 top現在のシステム ステータスを表示するコマンド
• Prometheus および Grafana 監視ツールを使用して、システム ステータスを表示することもできます。現在のシステム ステータス
• CPU消費が深刻です
• IO待機が深刻です
• ページの応答時間が長すぎます
• プロジェクト ログにタイムアウトやその他のエラーがあります
• SQL ステートメントの表現:
• SQL長いステートメント
• SQLステートメントの実行時間が長すぎます
• SQLフル テーブル スキャンからデータを取得
• 実行計画の rows と costVery big

2 番目: 異なるデータベースに応じて異なる方法を使用して問題を取得します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 中国語 Web サイトの他の関連記事を参照してください。