MySQL lietotāja definētie mainīgie un paplašinātais izpildes plāns

Raksts būs par divām lietām, kuras vieno faktiski tikai tas, ka tās ir saistītas ar MySQL. Uzdūros tām atbildot uz jautājumiem Latvijas programmētāju forumos, cik nu mums vispār te tādu ir.

Un tātad pirmais stāsts ir par MySQL lietotāja definētajiem mainīgajiem SQLā. Daudzām DBVS ir savi procedurālie paplašinājumi SQL, piemēram, Oracle tas ir PL/SQL, Microsoft SQL Server tas ir Transact-SQL jeb saīsinot T-SQL, MySQL arī 5.0 versijā pirmo reizi parādījās saglabātās procedūras un funkcijas. Tomēr jau no 3.23.6 versijas ir kaut kādi iedīgļi – lietotāja definētie mainīgie. Viens no populāriem to pielietojumiem, piemēram, ir ģenerēt numurus pēc kārtas, kas itin bieži ir nepieciešams, bet citā veidā MySQL nav iespējams. Piemērs, kurā ir tabula ar primārās atslēgas lauku, kurš ir unikāls, bet nenodrošina numurus pēc kārtas (kas tam, protams, NAV ARĪ JĀDARA!):

mysql> CREATE TABLE t (id int PRIMARY KEY);
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO t VALUES (1), (3), (17), (21);
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SET @rownum:=0;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT @rownum:=@rownum+1 rownum, t.*
    -> FROM t
    -> ORDER BY id;
+--------+----+
| rownum | id |
+--------+----+
|      1 |  1 |
|      2 |  3 |
|      3 | 17 |
|      4 | 21 |
+--------+----+
4 rows in set (0.00 sec)

Atgādinu, ka Oracle to var panākt ar pseidofunkciju Rownum un gan Oracle, gan SQL Serverī izmantojot analītisko funkciju row_number.

Bet kā izrādās un ko droši vien vairums ļaužu nav pamanījuši – šī iespēja ir domāta, lai mainīgos nodotu no viena SQL teikuma citam. Pie tam vēl vairāk – dokumentācijā ir šāds teksts:

As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed. The order of evaluation for expressions involving user variables is undefined and may change based on the elements contained within a given statement.

Tātad patiesībā jau augšminētais piemērs arī tā īsti neatbilst šim nosacījumam, jo, lai palielinātu skaitītāju par 1, nākas jau to tā kā arī nolasīt. To, ka šī nav tikai teorētiska iespējamība, parādīja jautājums php.lv forumā. Šeit manā atbildē ir demonstrēts kā iegūtais rezultāts nav īsti tas, ko mēs nezinot augšminēto citātu sagaidītu. Pie tam neinicializējot mainīgos no jauna (kas būtu jāatceras darīt, jo mainīgie dzīvo visu konekcijas laiku) identisku vaicājumu rezultāts atšķiras pirmajā un atkārtotajās reizēs. Un tās nu lūk ir tādas kļūdas, ko ir bezmaz visgrūtāk atklāt – vienu reizi nav ok, bet pēc tam jau ir.

Morāle – jālasa dokumentācija. Un nevis jāiziet no apšaubāmā pieņēmuma, ka īsti programmētāji dokumentāciju nelasa. Izlasīt dokumentāciju parasti ir ātrāk un rada krietni mazāk kļūdu nekā pašam atklāt visas smalkās lietas, ka nestrādā tieši tā kā esmu iedomājies.

Otra lieta, ko vēlējos parādīt, ir EXPLAIN EXTENDED klauza. Es jau rakstīju par to, kā MySQL var iegūt SQL teikuma izpildes plānu, bet šis dod šādu tādu papildus info. Piemēram atklāti parāda to, ko MySQL dara, kad ir pārliecināts, ka rezultātā dabūs ne vairāk kā 1 ierakstu. Atceramies papildus info var iegūt izmantojot show warnings pēc explain extended.

mysql> CREATE TABLE t (id int PRIMARY KEY, data varchar(10));
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO t VALUES (1, 'aaa');
Query OK, 1 row affected (0.05 sec)

mysql> EXPLAIN EXTENDED
    -> SELECT * FROM t
    -> WHERE id = 1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t     | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 |       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+------------------------------------------------------------+
| Level | Code | Message                                                    |
+-------+------+------------------------------------------------------------+
| Note  | 1003 | select '1' AS `id`,'aaa' AS `data` from `test`.`t` where 1 |
+-------+------+------------------------------------------------------------+
1 row in set (0.00 sec)

Pievēršam uzmanību, ka vaicājums patiesībā ir transformēts. Savukārt nākošajā piemērā, kad atbilstošs ieraksts nav atrodams, vaicājums atkal tiek transformēts jau uz citu:

mysql> EXPLAIN EXTENDED
    -> SELECT * FROM t
    -> WHERE id = 2;
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+---------------------------------------------------------+
| Level | Code | Message                                                 |
+-------+------+---------------------------------------------------------+
| Note  | 1003 | select '0' AS `id`,'' AS `data` from `test`.`t` where 0 |
+-------+------+---------------------------------------------------------+
1 row in set (0.00 sec)

Par explain extended dokumentācija ir visai nabadzīga, piemin tikai to, ka tāds ir, bet ir pāris mysqlperformanceblog rakstu par šo:

Komentēt

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Mainīt )

Twitter picture

You are commenting using your Twitter account. Log Out / Mainīt )

Facebook photo

You are commenting using your Facebook account. Log Out / Mainīt )

Google+ photo

You are commenting using your Google+ account. Log Out / Mainīt )

Connecting to %s

%d bloggers like this: