Əsas səhifə > MySQL administration, Performance tests and tips > Union optimization (perfomance test)

Union optimization (perfomance test)

*MySQL version: 5.6.12
Linux version: Ubuntu 12.04
Engine: InnoDB*

MySQL Performance test seriyalı yazılarımızdan növbətisi UNİON və UNİON ALL-dur.
İlk öncə biraz union-nun özündən bəhs edək.
sadə olaraq Union birləşdirmə aparır və result set olaraq 2 və yaxud daha artıq sorğunun birləşmiş halını qaytarır. Deyək ki, bizim cədvəl1-də aşağıdakı məlumatlar var:

blue
green
gray
black

və cədvəl2-də isə:

red
green
yellow
blue

UNION etsək MySQL internal olaraq bir temporary cədvələ hər 2 cədvəldən gələn result-ları sort edib yığacaq. Yəni bizim temprorary cədvəldə aşağıdakı məlumatlar toplanacaq:

black
blue
blue
gray
green
green
red
yellow

Daha sonra da məhz bu temporary cədvəldə olan təkrar məlumatlar silinəcək və son nəticədə user-ə aşağıdakı məlumatlar qaytarılacaq:

black
blue
gray
green
red
yellow

Dolayısı ilə yadda saxlamaq lazımdır ki, UNİON və UNİON ALL hər 2si temporary table-dan istifadə edir.

Test-lərimiz üçün biz sales adlı cədvəldən istifadə edəcik. 2.5 mln row-luq bu cədvəlin oxşarını yaradaq:

CREATE TABLE `sales` (
  `SALES_ID` int(8) NOT NULL AUTO_INCREMENT,
  `CUSTOMER_ID` decimal(8,0) NOT NULL,
  `PRODUCT_ID` decimal(8,0) NOT NULL,
  `SALE_DATE` datetime NOT NULL,
  `QUANTITY` decimal(8,0) NOT NULL,
  `SALE_VALUE` decimal(8,0) NOT NULL,
  `DEPARTMENT_ID` decimal(8,0) DEFAULT '0',
  `SALES_REP_ID` decimal(8,0) DEFAULT '0',
  `GST_FLAG` decimal(8,0) DEFAULT NULL,
  `sale_status` char(1) DEFAULT NULL,
  `FREE_SHIPPING` char(1) DEFAULT '',
  `DISCOUNT` decimal(8,0) unsigned DEFAULT '0',
  PRIMARY KEY (`SALES_ID`),
  KEY `sales_cust_idx` (`CUSTOMER_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2500004 DEFAULT CHARSET=latin1


create table sales2 like sales;

Daha sonra da sales_id-lərin hər 2 cədvəldə eyni olmaması(fərqli yerlərdə təkrarlanması üçün) məqsədilə onları procedure-la update edirik:

delimiter $$
 
CREATE PROCEDURE `insert_table`()
begin
	-- column deyisenleri
    declare v_SALES_ID int(8);
	declare v_CUSTOMER_ID decimal(8,0);
	declare v_PRODUCT_ID decimal(8,0);
	declare v_SALE_DATE datetime default NOT NULL;
	declare v_QUANTITY decimal(8,0) default NOT NULL;
    declare v_SALE_VALUE decimal(8,0) default NOT NULL;
    declare v_DEPARTMENT_ID decimal(8,0) DEFAULT '0';
    declare v_SALES_REP_ID decimal(8,0) DEFAULT '0';
    declare v_GST_FLAG decimal(8,0) DEFAULT NULL;
    declare v_sale_status char(1) DEFAULT NULL;
    declare v_FREE_SHIPPING char(1) DEFAULT '';
    declare v_DISCOUNT decimal(8,0) unsigned DEFAULT '0';
    
	-- sonuncu fetch olunmus row-nu tapan deyisen
    declare v_last_row_fetched int default 0;
     
    declare cursor1 cursor for select * from sales;
        
    declare continue handler for not found set v_last_row_fetched=1;
 
    set v_last_row_fetched=0;
    open cursor1;
    cursor_loop: loop
    fetch cursor1 
			into v_SALES_ID,v_CUSTOMER_ID,v_PRODUCT_ID,
					v_SALE_DATE,v_QUANTITY,v_SALE_VALUE,v_DEPARTMENT_ID,v_SALES_REP_ID,
					v_GST_FLAG,v_sale_status,v_FREE_SHIPPING,v_DISCOUNT;
            if v_last_row_fetched=1 then
                    leave cursor_loop;
            end if;
			set v_SALES_ID=v_SALES_ID+1;

            insert into sales2()
			values(v_SALES_ID,v_CUSTOMER_ID,v_PRODUCT_ID,v_SALE_DATE,
						v_QUANTITY,v_SALE_VALUE,v_DEPARTMENT_ID,v_SALES_REP_ID,
						v_GST_FLAG,v_sale_status,v_FREE_SHIPPING,v_DISCOUNT);
            
            
            
    end loop cursor_loop;
    close cursor1;
    set v_last_row_fetched=0;
     
end$$

Və dərhal ilk query-mizi işə salaq yoxlayaq:

mysql> (select sales_id from sales)
    -> union
    -> (select sales_id from sales2);
.
.
.

2500004 rows in set (14.44 sec)

Nəyə görə 2500004 rows?
Axı bizdə:

mysql> select count(sales_id) from sales;
+-----------------+
| count(sales_id) |
+-----------------+
|         2500003 |
+-----------------+
1 row in set (0.96 sec)

mysql> select count(sales_id) from sales2;
+-----------------+
| count(sales_id) |
+-----------------+
|         2500003 |
+-----------------+
1 row in set (0.84 sec)

Lakin diqqətlə baxdıqda:

mysql> select max(sales_id) from sales2;
+---------------+
| max(sales_id) |
+---------------+
|       2500004 |
+---------------+
1 row in set (0.02 sec)

mysql> select max(sales_id) from sales;
+---------------+
| max(sales_id) |
+---------------+
|       2500003 |
+---------------+
1 row in set (0.00 sec)

dolayısı ilə bizdə 2500003 təkrar olunur lakin sonuncu id 2500004(1) distinct olduğu üçün,
2500003+1 olaraq toplam 2500004 row qaytarır. Bu da ona sübütdur ki, UNİON duplicate-ləri silir və yalnız DİSTİNCT məlumatları qaytarır. Temporary cədvəl-in yaranıb yaranmadığına baxmaq üçen isə status dəyişənlərinə diqqət yetirmək lazımdır:

1. mysql> flush status;

2. mysql> (select sales_id from sales) union (select sales_id from sales2);

3. mysql> show status Where variable_name like 'Created%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 1     |
+-------------------------+-------+
3 rows in set (0.00 sec)

Gördüyümüz kimi, Created_tmp_tables=1 temporary cədvəlimiz yaradıldı. Lakin burda daha təhlükəli hadisənin şahidi oluruq Created_tmp_disk_tables=1. Bu o deməkdir ki, query-nin işlədilməsi zamanı RAM yetərsizliyindən temporary table in-memory şəklindən disk şəklinə convert olunmuşdur. Yəni artıq temp table-ımız disk table olmuşdur ki, bu həmçinin əlavə vaxt itkisi deməkdir. Müvəqqəti olaraq yaradılan bu disk table həmçinin query-nin bitimindən sonra drop olunur ki, bu da əlavə vaxt tələb edir. Daha da incələməyə çalışsaq və profile etsək yuxarıda dediyimizin doğru olduğunu sübut edərik.

mysql> show profile for query 1;
+---------------------------+----------+
| Status                    | Duration |
+---------------------------+----------+
| starting                  | 0.000125 |
| checking permissions      | 0.000010 |
| checking permissions      | 0.000008 |
| Opening tables            | 0.000110 |
| System lock               | 0.058612 |
| optimizing                | 0.000037 |
| statistics                | 0.000027 |
| preparing                 | 0.000019 |
| optimizing                | 0.000005 |
| statistics                | 0.000006 |
| preparing                 | 0.000007 |
| executing                 | 0.000005 |
| Sending data              | 1.568980 |
| converting HEAP to MyISAM | 2.008669 |
| Sending data              | 3.861828 |
| executing                 | 0.000027 |
| Sending data              | 6.042862 |
| optimizing                | 0.000029 |
| statistics                | 0.000020 |
| preparing                 | 0.000016 |
| executing                 | 0.000005 |
| Sending data              | 1.377518 |
| removing tmp table        | 0.555878 |
| Sending data              | 0.000028 |
| query end                 | 0.000016 |
| closing tables            | 0.000031 |
| freeing items             | 0.054344 |
| logging slow query        | 0.146203 |
| cleaning up               | 0.000048 |
+---------------------------+----------+
29 rows in set, 1 warning (0.09 sec)

Diqqətlə baxdıqda:
converting HEAP to MyISAM | 2.008669 – – – Yəni in-memory temp cədvəlin disk MyiSAM cədvələ çevrilməsi.
removing tmp table | 0.555878 – – – tmp cədvəlin silinməsi.

Təqribən 2.5-3 saniyə burada vaxt gedib.

Testlərimizə davam edək. Yuxarıda qeyd etdiyimiz kimi UNİON sort edib tmp table-a yığır nəticəni. Məntiqlə düşündükdə yaxşı olmaz mi ki, özümüz query-də sort edək? Bir növü UNİON-a kömək edək:

(select sales_id from sales order by sales_id)
union
(select sales_id from sales2 order by sales_id);
.
.
2500004 rows in set (13.99 sec)

Başqa şəkildə yazıb test etdikdə:

(select sales_id from sales)
union
(select sales_id from sales2)
order by sales_id;
.
.
2500004 rows in set (14.62 sec)

14.44, 13.99 və 14.62 sonuncu order by-lı query-nin ümumiyyətlə order by-sıza yaxın sürətdə işləməsi hətta bəzən geri qalması çox maraqlıdır. Yəqin ki bu ona görədir ki 1 dəfə əlavə olaraq order edir. Bunun sübutunu Explain plana baxdıqda görmüş oluruq:

(select sales_id from sales)
union
(select sales_id from sales2)
order by sales_id;

*************************** 3. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union1,2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Using temporary; Using filesort


(select sales_id from sales)
union
(select sales_id from sales2);

*************************** 3. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union1,2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Using temporary
3 rows in set (0.00 sec)

Extra hissəsində olan Using filesort görülmüş əlavə sort-dan(order) xəbər verir.

Davam edək. Deyək ki bizə yalnız ilk 100 məlumat lazımdır. Biz bunu müxtəlif cür yaza bilərik.

(select sales_id from sales)
union
(select sales_id from sales2)
limit 100;
.
.
100 rows in set (13.61 sec)


(select sales_id from sales)
union
(select sales_id from sales2)
order by sales_id limit 100;
.
.
100 rows in set (13.73 sec)

select * from(
(select sales_id from sales order by sales_id asc)
union
(select sales_id from sales2 order by sales_id asc)
) as s where sales_id between 1 and 100;
.
.
100 rows in set (14.29 sec)


select sales_id from (select sales_id from sales order by sales_id asc limit 100) as s
union
select sales_id from (select sales_id from sales2 order by sales_id asc limit 100) as s2
limit 100;
.
.
100 rows in set (0.00 sec)


(select sales_id from sales order by sales_id limit 100)
union
(select sales_id from sales2 order by sales_id limit 100)
limit 100;
.
.
100 rows in set (0.00 sec)

13.61, 13.73 , 14.29 və 0.00,0.00 sec. Sözsüz ki, fərq göz önündədir. Son 2 sorğu demək olar ki eynidirlər.
Davam edək. Desc olaraq order etmək istədikdə:

(select sales_id from sales order by sales_id desc)
union
(select sales_id from sales2 order by sales_id desc);

1
2
3
.
.
2500003
2500004

2,500,004 rows found. Duration for 1 query: 13.272 sec

Gördüyümüz kimi bu işləmədi. Subquery-lərin özündə desc olmasına baxmayaraq UNİON yenə də həmişəki kimi bizə asc qaytarır. Lakin aşağıdakı kimi yazdıqda:

(select sales_id from sales)
union
(select sales_id from sales2) order by sales_id desc;

2,500,004 rows found. Duration for 1 query: 14.892 sec

(select sales_id from sales order by sales_id desc)
union
(select sales_id from sales2 order by sales_id desc)
order by sales_id desc;

2,500,004 rows found. Duration for 1 query: 14.471 sec

Düzgün nəticə qaytarır.
İndi isə istəyirik ki, ilk sales-dən ilk 100-ü, sales2-dən isə son 100-ü union edək.

select sales_id from (select sales_id from sales order by sales_id asc limit 100) as s
union
select sales_id from (select sales_id from sales2 order by sales_id desc limit 100) as s2;

(select sales_id from sales order by sales_id asc limit 100)
union
(select sales_id from sales2 order by sales_id desc limit 100);

200 rows found. Duration for 1 query: 0.001 sec.

Mövzunu genişləndirə bilərik amma mən ən son UNİON-nun tmp table problemi üçün bəzi həll yollarını göstərmək istərdim. Xüsusən də converting HEAP to MyISAM-ın qarşısını almaq üçün görülə biləcək tədbirlərə baxacıq.
Tmp table size-ı MySQL-də 2 system dəyişəni ilə idarə edə bilərsiniz:
tmp_table_sizemax_heap_table_size

İndiki halda bizim sorğumuz üçün my.cnf-də
tmp_table_size=128M
max_heap_table_size=128M

qeyd edirik və MySQL-ə restart veririk.

Daha sonra da sorğumuzu bir daha çalışdırırırq:

mysql> (select sales_id from sales order by sales_id) union (select sales_id from sales2 order by sales_id);
.
.
2500004 rows in set (9.53 sec)

Və dərhal status counter-lərər baxırıq ki, bəli bizim istədiyimiz kimi disk table yaradılmadı:
Created_tmp_disk_tables=0

mysql> show status Where variable_name like 'Created%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 1     |
+-------------------------+-------+
3 rows in set (0.32 sec)

Profiling-də baxdıqda da bunun şahidi oluruq:

mysql> show profile for query 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000105 |
| checking permissions | 0.000011 |
| checking permissions | 0.000008 |
| Opening tables       | 0.000122 |
| System lock          | 0.000022 |
| optimizing           | 0.000011 |
| statistics           | 0.000037 |
| preparing            | 0.000020 |
| optimizing           | 0.000006 |
| statistics           | 0.000009 |
| preparing            | 0.000009 |
| executing            | 0.000007 |
| Sending data         | 4.705173 |
| executing            | 0.000030 |
| Sending data         | 3.784227 |
| optimizing           | 0.000036 |
| statistics           | 0.000020 |
| preparing            | 0.000017 |
| executing            | 0.000004 |
| Sending data         | 1.043749 |
| removing tmp table   | 0.003071 |
| Sending data         | 0.000051 |
| query end            | 0.000019 |
| closing tables       | 0.000035 |
| freeing items        | 0.000040 |
| cleaning up          | 0.000043 |
+----------------------+----------+
26 rows in set, 1 warning (0.00 sec)

Gördüyümüz kimi əvvəlki kimi converting HEAP to MyISAM yoxdur.
Həmçinin query-nin çalışma zamanında da çox azalma baş verdi:
13.99 – 9.53 = 4.46 sec

Əgər sizin də UNİON-la başınız ağrayırsa yuxarıda göstərdiyim üsullardan birindən istifadə edə bilərsiniz. Lakin tmp_table_size və max_heap_table_size-a toxunmazdan əvvəl ciddi düşünməniz lazımdır. Dokumentasiyada da deyildiyi kimi:

Increase the value of tmp_table_size (and max_heap_table_size if necessary) if you do many advanced GROUP BY queries and you have lots of memory.

Dolayısı ilə ~çox həmişə yaxşıdır~ anlayışını bu dəyişənlər üçün işlətmək düzgün deyildir. Kifayət qədər RAM varsa və həqiqətən də inanirsınızsa ki, bu dəyişənlərin qiymətlərini dəyişmək sizə fayda verəcək o zaman sınaya bilərsiniz.

Ümumiyyətlə isə beynəlxalq məsləhət bu yöndədir ki, UNİON və UNİON ALL-dan mümkün mərtəbə qaçılsın. Çünki nə qədər sürətli çalışır çalışsın temp table-sız çalışmır.

Təşəkkürlər😉

  1. Hələlik heç bir şərh yoxdur
  1. No trackbacks yet.

Bir cavab yazın

Sistemə daxil olmaq üçün məlumatlarınızı daxil edin və ya ikonlardan birinə tıklayın:

WordPress.com Loqosu

WordPress.com hesabınızdan istifadə edərək şərh edirsinz. Çıxış / Dəyişdir )

Twitter rəsmi

Twitter hesabınızdan istifadə edərək şərh edirsinz. Çıxış / Dəyişdir )

Facebook fotosu

Facebook hesabınızdan istifadə edərək şərh edirsinz. Çıxış / Dəyişdir )

Google+ foto

Google+ hesabınızdan istifadə edərək şərh edirsinz. Çıxış / Dəyişdir )

%s qoşulma

%d bloqqer bunu bəyənir: