検索
ホームページデータベースmysql チュートリアルmysql调优三步曲(慢查询、explain profile)_MySQL

MySQLexplain

bitsCN.com

mysql调优三步曲(慢查询、explain profile)

 

在做性能测试中经常会遇到一些sql的问题,其实做性能测试这几年遇到问题最多还是数据库这块,要么就是IO高要么就是cpu高,所以对数据的优化在性能测试过程中占据着很重要的地方,下面我就介绍一些msyql性能调优过程中经常用到的三件利器:

 

1、慢查询 (分析出现出问题的sql)

 

2、Explain (显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句)

 

3、Profile(查询到 SQL 会执行多少时间, 并看出 CPU/Memory 使用量, 执行过程中 Systemlock, Table lock 花多少时间等等.)

 

首先我们先讲一讲mysql的慢查询

1,配置开启

 

Linux:

 

在mysql配置文件my.cnf中增加

 

log-slow-queries=/var/lib/mysql/slowquery.log (指定日志文件存放位置,可以为空,系统会给一个缺省的文件host_name-slow.log)

 

long_query_time=2 (记录超过的时间,默认为10s)

 

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 = 2(其他参数如上)

 

 

 

2,查看方式

 

Linux:

 

使用mysql自带命令mysqldumpslow查看

 

常用命令

 

-s ORDER what to sort by (t, at, l, al, r, aretc), 'at’ is default-t NUM just show the top n queries-g PATTERN grep: only consider stmts that includethis string

 

 

eg:

 

s,是order的顺序,说明写的不够详细,俺用下来,包括看了代码,主要有 c,t,l,r和ac,at,al,ar,分别是按照query次数,时间,lock的时间和返回的记录数来排序,前面加了a的时倒序 -t,是top n的意思,即为返回前面多少条的数据 -g,后边可以写一个正则匹配模式,大小写不敏感的

 

mysqldumpslow -s c -t 20 host-slow.logmysqldumpslow -s r -t 20 host-slow.log

 

 

上述命令可以看出访问次数最多的20个sql语句和返回记录集最多的20个sql。

 

mysqldumpslow -t 10 -s t -g “left join” host-slow.log这个是按照时间返回前10条里面含有左连接的sql语句。

 

接下来就是explain

使用方法:

 

  

 

执行EXPLAIN SELECT * FROM res_user ORDER BYmodifiedtime LIMIT 0,1000 得到如下结果:

 

 

 

显示结果分析:

 

table |  type | possible_keys | key |key_len  | ref | rows | Extra 

EXPLAIN列的解释: 

table 

显示这一行的数据是关于哪张表的 

type 

这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL 

possible_keys 

显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句 

key 

实际使用的索引。如果为NULL,则没有使用索引。很少的情况下,MYSQL会选择优化不足的索引。这种情况下,可以在SELECT语句中使用USE INDEX(indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MYSQL忽略索引 

key_len 

使用的索引的长度。在不损失精确性的情况下,长度越短越好 

ref 

显示索引的哪一列被使用了,如果可能的话,是一个常数 

rows 

MYSQL认为必须检查的用来返回请求数据的行数 

Extra 

关于MYSQL如何解析查询的额外信息。将在表4.3中讨论,但这里可以看到的坏的例子是Using temporary和Using filesort,意思MYSQL根本不能使用索引,结果是检索会很慢 

 

extra列返回的描述的意义 

Distinct 

一旦MYSQL找到了与行相联合匹配的行,就不再搜索了 

Not exists 

MYSQL优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行, 

就不再搜索了 

Range checked for each 

Record(index map:#) 

没有找到理想的索引,因此对于从前面表中来的每一个行组合,MYSQL检查使用哪个索引,并用它来从表中返回行。这是使用索引的最慢的连接之一 

Using filesort 

看到这个的时候,查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行 

Using index 

列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,这发生在对表的全部的请求列都是同一个索引的部分的时候 

Using temporary 

看到这个的时候,查询需要优化了。这里,MYSQL需要创建一个临时表来存储结果,这通常发生在对不同的列集进行ORDER BY上,而不是GROUP BY上 

Where used 

使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行,并且连接类型ALL或index,这就会发生,或者是查询有问题

 

 

不同连接类型的解释(按照效率高低的顺序排序) 

system 

表只有一行:system表。这是const连接类型的特殊情况 

const 

表中的一个记录的最大值能够匹配这个查询(索引可以是主键或惟一索引)。因为只有一行,这个值实际就是常数,因为MYSQL先读这个值然后把它当做常数来对待 

eq_ref 

在连接中,MYSQL在查询时,从前面的表中,对每一个记录的联合都从表中读取一个记录,它在查询使用了索引为主键或惟一键的全部时使用 

ref 

这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分(比如,利用最左边前缀)时发生。对于之前的表的每一个行联合,全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好 

range 

这个连接类型使用索引返回一个范围中的行,比如使用>或

index 

这个连接类型对前面的表中的每一个记录联合进行完全扫描(比ALL更好,因为索引一般小于表数据) 

ALL 

这个连接类型对于前面的每一个记录联合进行完全扫描,这一般比较糟糕,应该尽量避免

 

再者就是profile

我们可以先使用

 

mysql> SELECT @@profiling;+-------------+| @@profiling |+-------------+|          0 |+-------------+1 row in set (0.00 sec)来查看是否已经启用profile,如果profilng值为0,可以通过mysql> SET profiling = 1;Query OK, 0 rows affected (0.00 sec)mysql> SELECT @@profiling;+-------------+| @@profiling |+-------------+|          1 |+-------------+1 row in set (0.00 sec)

 

 

来启用。启用profiling之后,我们执行一条查询语句,比如:

 

SELECT * FROM res_user ORDER BY modifiedtimeLIMIT 0,1000mysql> show profiles;+----------+------------+-------------------------------------------------------------+| Query_ID | Duration   | Query                                                      |+----------+------------+-------------------------------------------------------------+|        1| 0.00012200 | SELECT @@profiling                                         ||        2| 1.54582000 | SELECT res_id FROM res_user ORDER BY modifiedtime LIMIT 0,3 |+----------+------------+-------------------------------------------------------------+2 rows in set (0.00 sec)  注意:Query_ID表示刚执行的查询语句mysql> show profile for query 2;+--------------------------------+----------+| Status                         | Duration |+--------------------------------+----------+| starting                       | 0.000013 || checking query cache for query | 0.000035 || Opening tables                 | 0.000009 || System lock                    | 0.000002 || Table lock                     | 0.000015 || init                           | 0.000011 || optimizing                     | 0.000003 || statistics                     | 0.000006 || preparing                      | 0.000006 || executing                      | 0.000001 || Sorting result                 | 1.545565 || Sending data                   | 0.000038 || end                            | 0.000003 || query end                      | 0.000003 || freeing items                  | 0.000069 || storing result in query cache  | 0.000004 || logging slow query             | 0.000001 || logging slow query             | 0.000033 || cleaning up                    | 0.000003 |+--------------------------------+----------+19 rows in set (0.00 sec)

 

 

结论:可以看出此条查询语句的执行过程及执行时间,总的时间约为1.545s。这时候我们再执行一次。

 

 

mysql> SELECT res_id FROM res_user ORDERBY modifiedtime LIMIT 0,3;+---------+| res_id |+---------+| 1000305 || 1000322 || 1000323 |+---------+3 rows in set (0.00 sec)mysql> show profiles;+----------+------------+-------------------------------------------------------------+| Query_ID | Duration   | Query                                                      |+----------+------------+-------------------------------------------------------------+|       1 | 0.00012200 | SELECT @@profiling                                          ||       2 | 1.54582000 | SELECT res_id FROM res_userORDER BY modifiedtime LIMIT 0,3 ||        3 | 0.00006500 | SELECT res_id FROMres_user ORDER BY modifiedtime LIMIT 0,3 |+----------+------------+-------------------------------------------------------------+3 rows in set (0.00 sec)mysql> show profile for query 3;+--------------------------------+----------+| Status                         | Duration |+--------------------------------+----------+| starting                       | 0.000013 || checking query cache for query | 0.000005|| checking privileges on cached  | 0.000003 || sending cached result to clien | 0.000040|| logging slow query             | 0.000002 || cleaning up                    | 0.000002 |+--------------------------------+----------+6 rows in set (0.00 sec) (注意红色标记的地方)

 

 

 

结论:可以看出此次第二次查询因为前一次的查询生成了cache,所以这次无需从数据库文件中再次读取数据而是直接从缓存中读取,结果查询时间比第一次快多了(第一次查询用了1.5秒而本次用了不到5毫秒)。
 

bitsCN.com
声明
この記事の内容はネチズンが自主的に寄稿したものであり、著作権は原著者に帰属します。このサイトは、それに相当する法的責任を負いません。盗作または侵害の疑いのあるコンテンツを見つけた場合は、admin@php.cn までご連絡ください。
InnoDBバッファープールとそのパフォーマンスの重要性を説明してください。InnoDBバッファープールとそのパフォーマンスの重要性を説明してください。Apr 19, 2025 am 12:24 AM

Innodbbufferpoolは、データをキャッシュしてページをインデックス作成することにより、ディスクI/Oを削減し、データベースのパフォーマンスを改善します。その作業原則には次のものが含まれます。1。データ読み取り:Bufferpoolのデータを読む。 2。データの書き込み:データを変更した後、bufferpoolに書き込み、定期的にディスクに更新します。 3.キャッシュ管理:LRUアルゴリズムを使用して、キャッシュページを管理します。 4.読みメカニズム:隣接するデータページを事前にロードします。 BufferPoolのサイジングと複数のインスタンスを使用することにより、データベースのパフォーマンスを最適化できます。

MySQL対その他のプログラミング言語:比較MySQL対その他のプログラミング言語:比較Apr 19, 2025 am 12:22 AM

他のプログラミング言語と比較して、MySQLは主にデータの保存と管理に使用されますが、Python、Java、Cなどの他の言語は論理処理とアプリケーション開発に使用されます。 MySQLは、データ管理のニーズに適した高性能、スケーラビリティ、およびクロスプラットフォームサポートで知られていますが、他の言語は、データ分析、エンタープライズアプリケーション、システムプログラミングなどのそれぞれの分野で利点があります。

MySQLの学習:新しいユーザー向けの段階的なガイドMySQLの学習:新しいユーザー向けの段階的なガイドApr 19, 2025 am 12:19 AM

MySQLは、データストレージ、管理、分析に適した強力なオープンソースデータベース管理システムであるため、学習する価値があります。 1)MySQLは、SQLを使用してデータを操作するリレーショナルデータベースであり、構造化されたデータ管理に適しています。 2)SQL言語はMySQLと対話するための鍵であり、CRUD操作をサポートします。 3)MySQLの作業原則には、クライアント/サーバーアーキテクチャ、ストレージエンジン、クエリオプティマイザーが含まれます。 4)基本的な使用には、データベースとテーブルの作成が含まれ、高度な使用にはJoinを使用してテーブルの参加が含まれます。 5)一般的なエラーには、構文エラーと許可の問題が含まれ、デバッグスキルには、構文のチェックと説明コマンドの使用が含まれます。 6)パフォーマンスの最適化には、インデックスの使用、SQLステートメントの最適化、およびデータベースの定期的なメンテナンスが含まれます。

MySQL:初心者が習得するための必須スキルMySQL:初心者が習得するための必須スキルApr 18, 2025 am 12:24 AM

MySQLは、初心者がデータベーススキルを学ぶのに適しています。 1.MySQLサーバーとクライアントツールをインストールします。 2。selectなどの基本的なSQLクエリを理解します。 3。マスターデータ操作:テーブルを作成し、データを挿入、更新、削除します。 4.高度なスキルを学ぶ:サブクエリとウィンドウの関数。 5。デバッグと最適化:構文を確認し、インデックスを使用し、選択*を避け、制限を使用します。

MySQL:構造化データとリレーショナルデータベースMySQL:構造化データとリレーショナルデータベースApr 18, 2025 am 12:22 AM

MySQLは、テーブル構造とSQLクエリを介して構造化されたデータを効率的に管理し、外部キーを介してテーブル間関係を実装します。 1.テーブルを作成するときにデータ形式と入力を定義します。 2。外部キーを使用して、テーブル間の関係を確立します。 3。インデックス作成とクエリの最適化により、パフォーマンスを改善します。 4.データベースを定期的にバックアップおよび監視して、データのセキュリティとパフォーマンスの最適化を確保します。

MySQL:説明されている主要な機能と機能MySQL:説明されている主要な機能と機能Apr 18, 2025 am 12:17 AM

MySQLは、Web開発で広く使用されているオープンソースリレーショナルデータベース管理システムです。その重要な機能には、次のものが含まれます。1。さまざまなシナリオに適したInnodbやMyisamなどの複数のストレージエンジンをサポートします。 2。ロードバランスとデータバックアップを容易にするために、マスタースレーブレプリケーション機能を提供します。 3.クエリの最適化とインデックスの使用により、クエリ効率を改善します。

SQLの目的:MySQLデータベースとの対話SQLの目的:MySQLデータベースとの対話Apr 18, 2025 am 12:12 AM

SQLは、MySQLデータベースと対話して、データの追加、削除、変更、検査、データベース設計を実現するために使用されます。 1)SQLは、ステートメントの選択、挿入、更新、削除を介してデータ操作を実行します。 2)データベースの設計と管理に作成、変更、ドロップステートメントを使用します。 3)複雑なクエリとデータ分析は、ビジネス上の意思決定効率を改善するためにSQLを通じて実装されます。

初心者向けのMySQL:データベース管理を開始します初心者向けのMySQL:データベース管理を開始しますApr 18, 2025 am 12:10 AM

MySQLの基本操作には、データベース、テーブルの作成、およびSQLを使用してデータのCRUD操作を実行することが含まれます。 1.データベースの作成:createdatabasemy_first_db; 2。テーブルの作成:createTableBooks(idintauto_incrementprimarykey、titlevarchary(100)notnull、authorvarchar(100)notnull、published_yearint); 3.データの挿入:InsertIntoBooks(タイトル、著者、公開_year)VA

See all articles

ホットAIツール

Undresser.AI Undress

Undresser.AI Undress

リアルなヌード写真を作成する AI 搭載アプリ

AI Clothes Remover

AI Clothes Remover

写真から衣服を削除するオンライン AI ツール。

Undress AI Tool

Undress AI Tool

脱衣画像を無料で

Clothoff.io

Clothoff.io

AI衣類リムーバー

AI Hentai Generator

AI Hentai Generator

AIヘンタイを無料で生成します。

ホットツール

mPDF

mPDF

mPDF は、UTF-8 でエンコードされた HTML から PDF ファイルを生成できる PHP ライブラリです。オリジナルの作者である Ian Back は、Web サイトから「オンザフライ」で PDF ファイルを出力し、さまざまな言語を処理するために mPDF を作成しました。 HTML2FPDF などのオリジナルのスクリプトよりも遅く、Unicode フォントを使用すると生成されるファイルが大きくなりますが、CSS スタイルなどをサポートし、多くの機能強化が施されています。 RTL (アラビア語とヘブライ語) や CJK (中国語、日本語、韓国語) を含むほぼすべての言語をサポートします。ネストされたブロックレベル要素 (P、DIV など) をサポートします。

SublimeText3 英語版

SublimeText3 英語版

推奨: Win バージョン、コードプロンプトをサポート!

SublimeText3 中国語版

SublimeText3 中国語版

中国語版、とても使いやすい

Dreamweaver Mac版

Dreamweaver Mac版

ビジュアル Web 開発ツール

VSCode Windows 64 ビットのダウンロード

VSCode Windows 64 ビットのダウンロード

Microsoft によって発売された無料で強力な IDE エディター