Use mysqlshowprofiles to analyze SQL performance _ MySQL

Use mysqlshowprofiles to analyze SQL performance bitsCN.com
Mysql show profiles is added after 5.0.37 to analyze SQL performance. to use this function, make sure that the version is later than 5.0.37. Check my database version mysql> Select version (); + --------------------- + | version () | + --------------------- + | 5.0.82-community-nt | + --------------------- + 1 row in set (0.00 sec) version supports the show profiles function. Next, go to the mysql performance tracking and diagnosis world to check whether the profiles function is enabled. the default is to disable mysql> use test; Database changed mysql> show profiles; Empty set (0.00 sec) if it is blank, the profiles function is disabled. Start mysql> set profiling = 1; Query OK, 0 rows affected (0.00 sec) and execute the following Query mysql> explain select distinct player_idfrom task limit 20; mysql> select distinct player_id from task; then execute show profiles mysql> show profiles; + ---------- + ------------ + response + | Query_ID | Duration | Query | + ---------- + ------------ + Duration + | 1 | 0.00035225 | explain select distinct player_id from task limit 20 | 2 | 1.91772775 | select distinct player_id from task | + ---------- + ------------ + progress + now we can see that it takes 1.91772775 seconds to execute select distinct player_id from task to view the detailed time of a query according to query_id. mysql> show profile for query 2; + ---------------------- + ---------- + | Status | Duration | + -------------------- + ---------- + | starting | 0.000052 | Opening tables | 0.000009 | System lock | 0.000003 | Table lock | 0.000007 | init | 0.000013 | optimizing | 0.000003 | statistics | 0.000009 | preparing | 0.000008 | Creating tmp table | 0.000074 | executing | 0.000002 | Copying to tmp table | 1.916551 | Sending data | 0.000667 | end | 0.000004 | removing tmp table | 0.000065 | end | 0.000002 | end | 0.000002 | query end | 0.000003 | freeing items | 0.000245 | closing tables | 0.000006 | logging slow query | 0.000002 | cleaning up | 0.000003 | + -------------------- + ---------- + it takes a lot of time to see the red font, this is because distinct will use temporary tables to view cpu usage, io, and other information. mysql> show profile block io, cpu for query2; + Duration + ---------- + ------------ + ------ --------- + | Status | Duration | CPU_user | CPU_system | usage | Block _ ops_out | + Duration + ---------- + ------------ + Duration + ------ --------- + | starting | 0.000052 | NULL | Opening tables | 0.000009 | NULL | System lock | 0.000003 | NULL | NULL | Table lock | 0.000007 | NULL | init | 0.000013 | NULL | optimizing | 0.000003 | NULL | NULL | statistics | 0.000009 | NULL | preparing | 0.000008 | NULL | Creating tmp table | 0.000074 | NULL | NULL | executing | 0.000002 | NULL | Copying to tmp table | 1.916551 | NULL | Sending data | 0.000667 | NULL | end | 0.000004 | NULL | removing tmp table | 0.000065 | NULL | end | 0.000002 | NULL | end | 0.000002 | NULL | query end | 0.000003 | NULL | freeing items | 0.000245 | NULL | closing tables | 0.000006 | NULL | logging slow query | 0.000002 | NULL | cleaning up | 0.000003 | NULL | + ---------------------- + ---------- + ------------ + -------------- + ------ you can also see memory, for details about swaps, context switches, and source, refer http://dev.mysql.com/doc/refman/5.0/en/show-profiles.html Author: aeolus_pubitsCN.com