MySQL性能分析工具profile使用教程-tutorial mysql-php.cn

Rumah

pangkalan data

tutorial mysql

MySQL性能分析工具profile使用教程

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 04:27 PM

mysqlprofilegunatitikmenganalisisalatprestasiTutorial

分析SQL执行带来的开销是优化SQL的重要手段。在MySQL数据库中，可以通过配置profiling参数来启用SQL剖析。该参数可以在全局和session级别来设置。对于全局级别则作用于整个MySQL实例，而session级别紧影响当前session。该参数开启后，后续执行的SQL语句都将

分析SQL执行带来的开销是优化SQL的重要手段。在MySQL数据库中，可以通过配置profiling参数来启用SQL剖析。该参数可以在全局和session级别来设置。对于全局级别则作用于整个MySQL实例，而session级别紧影响当前session。该参数开启后，后续执行的SQL语句都将记录其资源开销，诸如IO，上下文切换，CPU，Memory等等。根据这些开销进一步分析当前SQL瓶颈从而进行优化与调整。本文描述了如何使用MySQL profile，不涉及具体的样例分析。

1、有关profile的描述

--当前版本
root@localhost[sakila]> show variables like 'version';
+---------------+---------------------------------------+
| Variable_name | Value                                 |
+---------------+---------------------------------------+
| version       | 5.6.17-enterprise-commercial-advanced |
+---------------+---------------------------------------+

--查看profiling系统变量
root@localhost[sakila]> show variables like '%profil%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |   --只读变量，用于控制是否由系统变量开启或禁用profiling
| profiling              | OFF   |   --开启SQL语句剖析功能
| profiling_history_size | 15    |   --设置保留profiling的数目，缺省为15，范围为0至100，为0时将禁用profiling
+------------------------+-------+

profiling [539]
If set to 0 or OFF (the default), statement profiling is disabled. If set to 1 or ON, statement prof
is enabled and the SHOW PROFILE and SHOW PROFILES statements provide access to prof
information. See Section 13.7.5.32, “SHOW PROFILES Syntax”.

This variable is deprecated in MySQL 5.6.8 and will be removed in a future MySQL release.
profiling_history_size [539]
The number of statements for which to maintain profiling information if profiling [539] is
enabled. The default value is 15. The maximum value is 100. Setting the value to 0 effectively
disables profiling. See Section 13.7.5.32, “SHOW PROFILES Syntax”.
This variable is deprecated in MySQL 5.6.8 and will be removed in a future MySQL release.

--获取profile的帮助
root@localhost[sakila]> help profile;
Name: 'SHOW PROFILE'
Description:
Syntax:
SHOW PROFILE [type [, type] ... ]
    [FOR QUERY n]
    [LIMIT row_count [OFFSET offset]]

type:
    ALL                --显示所有的开销信息
| BLOCK IO           --显示块IO相关开销
| CONTEXT SWITCHES   --上下文切换相关开销
| CPU                --显示CPU相关开销信息
| IPC                --显示发送和接收相关开销信息
| MEMORY             --显示内存相关开销信息
| PAGE FAULTS        --显示页面错误相关开销信息
| SOURCE             --显示和Source_function，Source_file，Source_line相关的开销信息
| SWAPS              --显示交换次数相关开销的信息

The SHOW PROFILE and SHOW PROFILES statements display profiling
information that indicates resource usage for statements executed
during the course of the current session.

*Note*: These statements are deprecated as of MySQL 5.6.7 and will be
removed in a future MySQL release. Use the Performance Schema instead;
see http://dev.mysql.com/doc/refman/5.6/en/performance-schema.html.
--上面描述从5.6.7开始该命令将会被移除，用Performance Schema instead代替
--在Oracle数据库中，是通过autotrace来剖析单条SQL并获取真实的执行计划以及其开销信息

2、开启porfiling

--启用session级别的profiling
root@localhost[sakila]> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

--验证修改后的结果
root@localhost[sakila]> show variables like '%profil%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | ON    |
| profiling_history_size | 15    |
+------------------------+-------+

--发布SQL查询
root@localhost[sakila]> select count(*) from customer;
+----------+
| count(*) |
+----------+
|      599 |
+----------+

--查看当前session所有已产生的profile
root@localhost[sakila]> show profiles;
+----------+------------+--------------------------------+
| Query_ID | Duration   | Query                          |
+----------+------------+--------------------------------+
|        1 | 0.00253600 | show variables like '%profil%' |
|        2 | 0.00138150 | select count(*) from customer |
+----------+------------+--------------------------------+
2 rows in set, 1 warning (0.01 sec)

--我们看到有2个warning，之前一个，现在一个
root@localhost[sakila]> show warnings;    --下面的结果表明SHOW PROFILES将来会被Performance Schema替换掉
+---------+------+--------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                      |
+---------+------+--------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | 'SHOW PROFILES' is deprecated and will be removed in a future release. Please use Performance Schema instead |
+---------+------+--------------------------------------------------------------------------------------------------------------+

3、获取SQL语句的开销信息

--可以直接使用show profile来查看上一条SQL语句的开销信息
--注，show profile之类的语句不会被profiling，即自身不会产生Profiling
--我们下面的这个show profile查看的是show warnings产生的相应开销
root@localhost[sakila]> show profile;
+----------------+----------+
| Status         | Duration |
+----------------+----------+
| starting       | 0.000141 |
| query end      | 0.000058 |
| closing tables | 0.000014 |
| freeing items | 0.001802 |
| cleaning up    | 0.000272 |
+----------------+----------+

--如下面的查询show warnings被添加到profiles
root@localhost[sakila]> show profiles;
+----------+------------+--------------------------------+
| Query_ID | Duration   | Query                          |
+----------+------------+--------------------------------+
|        1 | 0.00253600 | show variables like '%profil%' |
|        2 | 0.00138150 | select count(*) from customer |
|        3 | 0.00228600 | show warnings                  |
+----------+------------+--------------------------------+

--获取指定查询的开销
root@localhost[sakila]> show profile for query 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000148 |
| checking permissions | 0.000014 |
| Opening tables       | 0.000047 |
| init                 | 0.000023 |
| System lock          | 0.000035 |
| optimizing           | 0.000012 |
| statistics           | 0.000019 |
| preparing            | 0.000014 |
| executing            | 0.000006 |
| Sending data         | 0.000990 |
| end                  | 0.000010 |
| query end            | 0.000011 |
| closing tables       | 0.000010 |
| freeing items        | 0.000016 |
| cleaning up          | 0.000029 |
+----------------------+----------+

--查看特定部分的开销，如下为CPU部分的开销
root@localhost[sakila]> show profile cpu for query 2 ;
+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting             | 0.000148 | 0.000000 |   0.000000 |
| checking permissions | 0.000014 | 0.000000 |   0.000000 |
| Opening tables       | 0.000047 | 0.000000 |   0.000000 |
| init                 | 0.000023 | 0.000000 |   0.000000 |
| System lock          | 0.000035 | 0.000000 |   0.000000 |
| optimizing           | 0.000012 | 0.000000 |   0.000000 |
| statistics           | 0.000019 | 0.000000 |   0.000000 |
| preparing            | 0.000014 | 0.000000 |   0.000000 |
| executing            | 0.000006 | 0.000000 |   0.000000 |
| Sending data         | 0.000990 | 0.001000 |   0.000000 |
| end                  | 0.000010 | 0.000000 |   0.000000 |
| query end            | 0.000011 | 0.000000 |   0.000000 |
| closing tables       | 0.000010 | 0.000000 |   0.000000 |
| freeing items        | 0.000016 | 0.000000 |   0.000000 |
| cleaning up          | 0.000029 | 0.000000 |   0.000000 |
+----------------------+----------+----------+------------+

--如下为MEMORY部分的开销
root@localhost[sakila]> show profile memory for query 2 ;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000148 |
| checking permissions | 0.000014 |
| Opening tables       | 0.000047 |
| init                 | 0.000023 |
| System lock          | 0.000035 |
| optimizing           | 0.000012 |
| statistics           | 0.000019 |
| preparing            | 0.000014 |
| executing            | 0.000006 |
| Sending data         | 0.000990 |
| end                  | 0.000010 |
| query end            | 0.000011 |
| closing tables       | 0.000010 |
| freeing items        | 0.000016 |
| cleaning up          | 0.000029 |
+----------------------+----------+

--同时查看不同资源开销
root@localhost[sakila]> show profile block io,cpu for query 2;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000148 | 0.000000 |   0.000000 |            0 |             0 |
| checking permissions | 0.000014 | 0.000000 |   0.000000 |            0 |             0 |
| Opening tables       | 0.000047 | 0.000000 |   0.000000 |            0 |             0 |
| init                 | 0.000023 | 0.000000 |   0.000000 |            0 |             0 |
| System lock          | 0.000035 | 0.000000 |   0.000000 |            0 |             0 |
| optimizing           | 0.000012 | 0.000000 |   0.000000 |            0 |             0 |
| statistics           | 0.000019 | 0.000000 |   0.000000 |            0 |             0 |
| preparing            | 0.000014 | 0.000000 |   0.000000 |            0 |             0 |
| executing            | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |
| Sending data         | 0.000990 | 0.001000 |   0.000000 |            0 |             0 |
| end                  | 0.000010 | 0.000000 |   0.000000 |            0 |             0 |
| query end            | 0.000011 | 0.000000 |   0.000000 |            0 |             0 |
| closing tables       | 0.000010 | 0.000000 |   0.000000 |            0 |             0 |
| freeing items        | 0.000016 | 0.000000 |   0.000000 |            0 |             0 |
| cleaning up          | 0.000029 | 0.000000 |   0.000000 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+

--下面的SQL语句用于查询query_id为2的SQL开销，且按最大耗用时间倒序排列
root@localhost[sakila]> set @query_id=2;

root@localhost[sakila]> SELECT STATE, SUM(DURATION) AS Total_R,
    ->   ROUND(
    ->        100 * SUM(DURATION) /
    ->           (SELECT SUM(DURATION)
    ->            FROM INFORMATION_SCHEMA.PROFILING
    ->            WHERE QUERY_ID = @query_id
    ->        ), 2) AS Pct_R,
    ->     COUNT(*) AS Calls,
    ->     SUM(DURATION) / COUNT(*) AS "R/Call"
    -> FROM INFORMATION_SCHEMA.PROFILING
    -> WHERE QUERY_ID = @query_id
    -> GROUP BY STATE
    -> ORDER BY Total_R DESC;
+----------------------+----------+-------+-------+--------------+
| STATE                | Total_R | Pct_R | Calls | R/Call       |
+----------------------+----------+-------+-------+--------------+
| Sending data         | 0.000990 | 71.53 |     1 | 0.0009900000 |--最大耗用时间部分为发送数据
| starting             | 0.000148 | 10.69 |     1 | 0.0001480000 |
| Opening tables       | 0.000047 | 3.40 |     1 | 0.0000470000 |
| System lock          | 0.000035 | 2.53 |     1 | 0.0000350000 |
| cleaning up          | 0.000029 | 2.10 |     1 | 0.0000290000 |
| init                 | 0.000023 | 1.66 |     1 | 0.0000230000 |
| statistics           | 0.000019 | 1.37 |     1 | 0.0000190000 |
| freeing items        | 0.000016 | 1.16 |     1 | 0.0000160000 |
| preparing            | 0.000014 | 1.01 |     1 | 0.0000140000 |
| checking permissions | 0.000014 | 1.01 |     1 | 0.0000140000 |
| optimizing           | 0.000012 | 0.87 |     1 | 0.0000120000 |
| query end            | 0.000011 | 0.79 |     1 | 0.0000110000 |
| end                  | 0.000010 | 0.72 |     1 | 0.0000100000 |
| closing tables       | 0.000010 | 0.72 |     1 | 0.0000100000 |
| executing            | 0.000006 | 0.43 |     1 | 0.0000060000 |
+----------------------+----------+-------+-------+--------------+

--开启profiling后，我们可以通过show profile等方式查看，其实质是这些开销信息被记录到information_schema.profiling表
--如下面的查询，部分信息省略
profiling
root@localhost[information_schema]> select * from profiling limit 3,3\G;
*************************** 1. row ***************************
           QUERY_ID: 1
                SEQ: 5
              STATE: init
           DURATION: 0.000020
           CPU_USER: 0.000000
         CPU_SYSTEM: 0.000000
CONTEXT_VOLUNTARY: 0
CONTEXT_INVOLUNTARY: 0
       BLOCK_OPS_IN: 0
      BLOCK_OPS_OUT: 0
      MESSAGES_SENT: 0
MESSAGES_RECEIVED: 0
PAGE_FAULTS_MAJOR: 0
PAGE_FAULTS_MINOR: 0
              SWAPS: 0
    SOURCE_FUNCTION: mysql_prepare_select
        SOURCE_FILE: sql_select.cc
        SOURCE_LINE: 1050

--停止profile,可以设置profiling参数，或者在session退出之后,profiling会被自动关闭
root@localhost[sakila]> set profiling=off;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Kenyataan

Kandungan artikel ini disumbangkan secara sukarela oleh netizen, dan hak cipta adalah milik pengarang asal. Laman web ini tidak memikul tanggungjawab undang-undang yang sepadan. Jika anda menemui sebarang kandungan yang disyaki plagiarisme atau pelanggaran, sila hubungi admin@php.cn

Artikel Berkaitan

Terangkan sifat asid (atom, konsistensi, pengasingan, ketahanan).Apr 16, 2025 am 12:20 AM

Atribut asid termasuk atom, konsistensi, pengasingan dan ketahanan, dan merupakan asas reka bentuk pangkalan data. 1. Atomicity memastikan bahawa urus niaga sama ada berjaya atau gagal sepenuhnya. 2. Konsistensi memastikan pangkalan data tetap konsisten sebelum dan selepas transaksi. 3. Pengasingan memastikan bahawa urus niaga tidak mengganggu satu sama lain. 4. Kegigihan memastikan data disimpan secara kekal selepas penyerahan transaksi.

MySQL: Sistem Pengurusan Pangkalan Data vs Bahasa PengaturcaraanApr 16, 2025 am 12:19 AM

MySQL bukan sahaja sistem pengurusan pangkalan data (DBMS) tetapi juga berkait rapat dengan bahasa pengaturcaraan. 1) Sebagai DBMS, MySQL digunakan untuk menyimpan, menyusun dan mengambil data, dan mengoptimumkan indeks dapat meningkatkan prestasi pertanyaan. 2) Menggabungkan SQL dengan bahasa pengaturcaraan, tertanam dalam Python, menggunakan alat ORM seperti SQLalChemy dapat memudahkan operasi. 3) Pengoptimuman prestasi termasuk pengindeksan, pertanyaan, caching, perpustakaan dan bahagian meja dan pengurusan transaksi.

MySQL: Menguruskan data dengan arahan SQLApr 16, 2025 am 12:19 AM

MySQL menggunakan arahan SQL untuk menguruskan data. 1. Perintah asas termasuk pilih, masukkan, kemas kini dan padam. 2. Penggunaan lanjutan melibatkan fungsi gabungan, subquery dan agregat. 3. Kesilapan umum termasuk isu sintaks, logik dan prestasi. 4. Petua Pengoptimuman termasuk menggunakan indeks, mengelakkan Pilih* dan menggunakan had.

Tujuan Mysql: Menyimpan dan Menguruskan Data dengan berkesanApr 16, 2025 am 12:16 AM

MySQL adalah sistem pengurusan pangkalan data relasi yang sesuai untuk menyimpan dan menguruskan data. Kelebihannya termasuk pertanyaan berprestasi tinggi, pemprosesan transaksi fleksibel dan jenis data yang kaya. Dalam aplikasi praktikal, MySQL sering digunakan dalam platform e-dagang, rangkaian sosial dan sistem pengurusan kandungan, tetapi perhatian harus dibayar kepada pengoptimuman prestasi, keselamatan data dan skalabilitas.

SQL dan MySQL: Memahami hubunganApr 16, 2025 am 12:14 AM

Hubungan antara SQL dan MySQL adalah hubungan antara bahasa standard dan pelaksanaan khusus. 1. SQL adalah bahasa standard yang digunakan untuk mengurus dan mengendalikan pangkalan data relasi, membolehkan penambahan data, penghapusan, pengubahsuaian dan pertanyaan. 2.MYSQL adalah sistem pengurusan pangkalan data tertentu yang menggunakan SQL sebagai bahasa pengendaliannya dan menyediakan penyimpanan dan pengurusan data yang cekap.

Terangkan peranan log redo innoDB dan membatalkan log.Apr 15, 2025 am 12:16 AM

InnoDB menggunakan redolog dan undologs untuk memastikan konsistensi dan kebolehpercayaan data. 1. Pengubahsuaian halaman data rekod untuk memastikan pemulihan kemalangan dan kegigihan transaksi. 2.UNDOLOGS merekodkan nilai data asal dan menyokong penggantian transaksi dan MVCC.

Apakah metrik utama untuk dicari dalam output yang dijelaskan (jenis, kunci, baris, tambahan)?Apr 15, 2025 am 12:15 AM

Metrik utama untuk menjelaskan arahan termasuk jenis, kunci, baris, dan tambahan. 1) Jenis mencerminkan jenis akses pertanyaan. Semakin tinggi nilai, semakin tinggi kecekapan, seperti const adalah lebih baik daripada semua. 2) Kunci memaparkan indeks yang digunakan, dan null menunjukkan tiada indeks. 3) Baris menganggarkan bilangan baris yang diimbas, yang mempengaruhi prestasi pertanyaan. 4) Tambahan memberikan maklumat tambahan, seperti menggunakanFilesort meminta bahawa ia perlu dioptimumkan.

Apakah status sementara dalam menjelaskan dan bagaimana untuk mengelakkannya?Apr 15, 2025 am 12:14 AM

MenggunakanTemary menunjukkan bahawa keperluan untuk membuat jadual sementara dalam pertanyaan MySQL, yang biasanya dijumpai di Orderby menggunakan lajur yang berbeza, GroupBy, atau tidak diindeks. Anda boleh mengelakkan berlakunya indeks dan menulis semula pertanyaan dan meningkatkan prestasi pertanyaan. Khususnya, apabila menggunakan pembelian muncul dalam menjelaskan output, ini bermakna MySQL perlu membuat jadual sementara untuk mengendalikan pertanyaan. Ini biasanya berlaku apabila: 1) deduplikasi atau pengelompokan apabila menggunakan yang berbeza atau kumpulan; 2) Susun apabila Orderby mengandungi lajur bukan indeks; 3) Gunakan subquery kompleks atau menyertai operasi. Kaedah Pengoptimuman termasuk: 1) Orderby dan GroupB

See all articles