Профил е един вид възможност да записвате вашите заявки от конкретен момент и след това да ги изследвате по- подробно. Благодарение на този метод може да видите резултата от оптимизацията(+ explain). С профил може да проследите по- подробно една заявка какво процесорно време ползва, памет и дооооста други неща 🙂

Ето един пример как работи. Изполваме базата „test“ където създаваме таблица „t1“ и процедура „dh_profile“ с която да я напълним.

use test;

drop table t1;
CREATE TABLE `t1` (
`id` int(11) NOT NULL auto_increment,
`weight` int(11) NOT NULL,
`query` varchar(3072) NOT NULL,
`ddate` date NOT NULL,
PRIMARY KEY  (`id`),
KEY `query` (`query`)
) ENGINE=MyISAM;

delimiter $$
CREATE PROCEDURE dh_profile()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE (i<=100000) DO
insert into t1 (weight,query,ddate) VALUES („1″,“Mysql rulz +{:>>“,CURDATE());
SET i=i+1;
END WHILE;
END$$
delimiter ;

Пълним таблицата t1

mysql> call dh_profile();
Query OK, 1 row affected (37.06 sec)

Проверяваме дали профилирането е пуснато

mysql> SELECT @@profiling;
+––––-+
| @@profiling |
+––––-+
|           0 |
+––––-+
1 row in set (0.00 sec)

Пускаме профилиране

mysql> SET profiling = 1;
Query OK, 0 rows affected (0.00 sec)

Изпълняваме малка натоварваща заявка която по – долу ще разгледам по- обстойно

mysql> select query from t1 order by rand() limit 10;
+––––––+
| query            |
+––––––+
| Mysql rulz +{:>> |
| Mysql rulz +{:>> |
| Mysql rulz +{:>> |
| Mysql rulz +{:>> |
| Mysql rulz +{:>> |
| Mysql rulz +{:>> |
| Mysql rulz +{:>> |
| Mysql rulz +{:>> |
| Mysql rulz +{:>> |
| Mysql rulz +{:>> |
+––––––+
10 rows in set (5.10 sec)

Извеждаме какво е записано в профила ни

mysql> SHOW PROFILES;
+–––-+––––+––––––––––––––––+
| Query_ID | Duration   | Query                                         |
+–––-+––––+––––––––––––––––+
|        1 | 6.50790900 | select query from t1 order by rand() limit 10 |
+–––-+––––+––––––––––––––––+
1 row in set (0.00 sec)

Информация по стъпки за заявка 1

mysql> SHOW PROFILE FOR QUERY 1;
+–––––––––––+–––-+
| Status                         | Duration |
+–––––––––––+–––-+
| (initialization)               | 0.000003 |
| checking query cache for query | 0.000021 |
| Opening tables                 | 0.000005 |
| System lock                    | 0.000003 |
| Table lock                     | 0.000004 |
| init                           | 0.000008 |
| optimizing                     | 0.000002 |
| statistics                     | 0.000007 |
| preparing                      | 0.000007 |
| Creating tmp table             | 0.000027 |
| executing                      | 0.000003 |
| Copying to tmp table           | 0.017907 |
| converting HEAP to MyISAM      | 0.049151 |
| Copying to tmp table on disk   | 4.75879  |
| Sorting result                 | 0.268406 |
| Sending data                   | 0.000364 |
| end                            | 0.000004 |
| removing tmp table             | 1.413161 |
| end                            | 0.000014 |
| query end                      | 0.000005 |
| freeing items                  | 0.000009 |
| closing tables                 | 0.000006 |
| logging slow query             | 0.000002 |
+–––––––––––+–––-+
23 rows in set (0.01 sec)

Извеждане на всички стъпки при заявка 1 + тяхното CPU натоварване

mysql> SHOW PROFILE CPU FOR QUERY 1;
+–––––––––––+–––-+–––-+––––+
| Status                         | Duration | CPU_user | CPU_system |
+–––––––––––+–––-+–––-+––––+
| (initialization)               | 0.000003 | 0        | 0          |
| checking query cache for query | 0.000021 | 0        | 0          |
| Opening tables                 | 0.000005 | 0        | 0          |
| System lock                    | 0.000003 | 0        | 0          |
| Table lock                     | 0.000004 | 0        | 0          |
| init                           | 0.000008 | 0        | 0          |
| optimizing                     | 0.000002 | 0        | 0          |
| statistics                     | 0.000007 | 0        | 0          |
| preparing                      | 0.000007 | 0        | 0          |
| Creating tmp table             | 0.000027 | 0        | 0          |
| executing                      | 0.000003 | 0        | 0          |
| Copying to tmp table           | 0.017907 | 0.016001 | 0          |
| converting HEAP to MyISAM      | 0.049151 | 0.024001 | 0.028001   |
| Copying to tmp table on disk   | 4.75879  | 0.260017 | 0.644041   |
| Sorting result                 | 0.268406 | 0.17201  | 0.0960059  |
| Sending data                   | 0.000364 | 0        | 0.004      |
| end                            | 0.000004 | 0        | 0          |
| removing tmp table             | 1.413161 | 0        | 0.088005   |
| end                            | 0.000014 | 0        | 0          |
| query end                      | 0.000005 | 0        | 0          |
| freeing items                  | 0.000009 | 0        | 0          |
| closing tables                 | 0.000006 | 0        | 0          |
| logging slow query             | 0.000002 | 0        | 0          |
+–––––––––––+–––-+–––-+––––+
23 rows in set (0.00 sec)

Извеждане на всички стъпки при заявка 1 + натоварването в паметта

mysql> SHOW PROFILE MEMORY FOR QUERY 1;
+–––––––––––+–––-+
| Status                         | Duration |
+–––––––––––+–––-+
| (initialization)               | 0.000003 |
| checking query cache for query | 0.000021 |
| Opening tables                 | 0.000005 |
| System lock                    | 0.000003 |
| Table lock                     | 0.000004 |
| init                           | 0.000008 |
| optimizing                     | 0.000002 |
| statistics                     | 0.000007 |
| preparing                      | 0.000007 |
| Creating tmp table             | 0.000027 |
| executing                      | 0.000003 |
| Copying to tmp table           | 0.017907 |
| converting HEAP to MyISAM      | 0.049151 |
| Copying to tmp table on disk   | 4.75879  |
| Sorting result                 | 0.268406 |
| Sending data                   | 0.000364 |
| end                            | 0.000004 |
| removing tmp table             | 1.413161 |
| end                            | 0.000014 |
| query end                      | 0.000005 |
| freeing items                  | 0.000009 |
| closing tables                 | 0.000006 |
| logging slow query             | 0.000002 |
+–––––––––––+–––-+
23 rows in set (0.00 sec)

PS: Надявам се публикацията бъде полезни на всеки MySQL оптимизатор. Успех!