Əsas səhifə > MySQL, MySQL 5.6, MySQL administration, Performance tests and tips > Equality Range Optimization(eq_range_index_dive_limit system variable)

Equality Range Optimization(eq_range_index_dive_limit system variable)

Bu mövzu şəxsi araşdırmalarımdan biridir ki, `Equality RANGE` scan zamanı 5.6 və 5.5 versiyaları arasında performance fərqinə baxacıq.
İlk öncə Equality RANGE nədir? 2 növ bizə tanış olan query təsəvvür edək:

col_name IN(val1, …, valN)
col_name = val1 OR … OR col_name = valN

Yuxarıdakı sorğular o zaman TRUE olur ki, col_name val-ların hər hansı birinə bərabər olsun. Dolayısı ilə bu müqayisə, bərabərliyi yoxlayır və məhz buna görə `Bərabərlik Aralığı`=`Equality RANGE` adlanır.
Teoriyası biraz aydın olmasa belə praktik olaraq izah edəcik bunu.
İlk öncə MySQL-imizə baxaq. MySQL 5.6.12 və MySQL 5.5.33-dir Qısa olaraq 5.6 və 5.5 olaraq qeyd edəcik.
Birinci olaraq 5.5 versiyada bu tip sorğuların necə çalışdığına baxacıq:

mysql [localhost] {msandbox} (mysqlspp) > select @@version;
+-----------+
| @@version |
+-----------+
| 5.5.33    |
+-----------+
1 row in set (0.00 sec)

Cədvəlmiz isə hər 2 tərəfdə eynidir və aşağıdakı strukturdadır:

mysql [localhost] {msandbox} (mysqlspp) > show create table sales;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------+
| sales | 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`),
  KEY `PRODUCT_ID` (`PRODUCT_ID`,`CUSTOMER_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2500004 DEFAULT CHARSET=latin1 |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Toplam row sayı 2500003-dir:

mysql [localhost] {msandbox} (mysqlspp) > select count(*) from sales;
+----------+
| count(*) |
+----------+
|  2500003 |
+----------+
1 row in set (14.88 sec)

Gördüyümüz kimi, cədvəldə 2500003 row var. Biraz da cədvəlimizi araşdıraq. Cədvəl statusumuza baxdıqda görürük ki, MySQL 5.5 cədvəli full scan etdikdə təqribi row sayını 2500273 hesablayır:

Bundan əlavə cədvəl index-lərinin cardinality-sini öyrənək. Bizim üçün əsas PRODUCT_ID index-idir( KEY `PRODUCT_ID`):

cardinality=200, burdan belə nəticə çıxır ki product_id təqribən 200 unique scan-la tapıla bilər.
Bu rəqəm təqribi rəqəmdir və bunu başqa yolla da tapa bilərik:

mysql [localhost] {msandbox} (mysqlspp) > select count(product_id) from sales;
+-------------------+
| count(product_id) |
+-------------------+
|           2500003 |
+-------------------+
1 row in set (0.32 sec)

mysql [localhost] {msandbox} (mysqlspp) > select count(distinct product_id) from sales;
+----------------------------+
| count(distinct product_id) |
+----------------------------+
|                        150 |
+----------------------------+
1 row in set (0.00 sec)

Burdan belə nəticə çıxardırıq ki, MySQL 5.5-də full table scan zamanı 2500273 row examine olunacaq! bundan əlavə product_id-nin seçkinliyi(cardinality) 200-dür

MySQL 5.6-da isə bu qiymətlər aşağıdakı kimidir:
Table status:

Index cardinality:

Burdan belə nəticə çıxardırıq ki, MySQL 5.6-də full table scan zamanı 2351755 row examine olunacaq!(optimizer belə qərara gəlib təbii ki bu nisbi rəqəmdir) bundan əlavə product_id-nin seçkinliyi(cardinality) 286-dır

Hər 2 versiya MySQL-də cədvəl və index-lər haqqında məlumatı əldə etdikdən sonra mövzumuzu izah etməyə başlaya bilərik. sınaqdan keçirəcəyimiz sorğu:
select count(*) from sales force index(product_id) where product_id in (1,2,3,4,5,6,7,8,9,10)

MySQL 5.5 üçün:

mysql [localhost] {msandbox} (mysqlspp) > select count(*) from sales force index(product_id) where product_id in (1,2,3,4,5,6,7,8,9,10);
+----------+
| count(*) |
+----------+
|   166659 |
+----------+
1 row in set (0.06 sec)

MySQL 5.6 üçün:

mysql> select count(*) from sales force index(product_id) where product_id in (1,2,3,4,5,6,7,8,9,10);
+----------+
| count(*) |
+----------+
|   166659 |
+----------+
1 row in set (0.05 sec)

1-dən 10-a qədər range üçün QEP MySQL 5.5-də:

mysql [localhost] {msandbox} (mysqlspp) > explain select count(*) from sales force index(product_id) where product_id in (1,2,3,4,5,6,7,8,9,10)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales
         type: range
possible_keys: PRODUCT_ID
          key: PRODUCT_ID
      key_len: 4
          ref: NULL
         rows: 332662
        Extra: Using where; Using index
1 row in set (0.00 sec)

MySQL 5.6-da:

mysql> explain select count(*) from sales force index(product_id) where product_id in (1,2,3,4,5,6,7,8,9,10)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales
         type: range
possible_keys: PRODUCT_ID
          key: PRODUCT_ID
      key_len: 4
          ref: NULL
         rows: 82220
        Extra: Using where; Using index
1 row in set (0.00 sec)

Bura diqqətlə nəzər yetirsək görərik ki, 5.5 versiya MySQL-də 332662 row scan olunur, 5.6 versiyada isə row scan sayı 82220-dir. Böyük fərq göz önündədir.

Query-imizə əlavələrlə yenidən çalışdıraq.
5.5 versiyada:

mysql [localhost] {msandbox} (mysqlspp) > explain select count(*) from sales force index(product_id) where product_id in (1,2,3,4,5,6,7,8,9,10,11,12)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales
         type: range
possible_keys: PRODUCT_ID
          key: PRODUCT_ID
      key_len: 4
          ref: NULL
         rows: 398890
        Extra: Using where; Using index
1 row in set (0.01 sec)

5.6 versiyada:

mysql> explain select count(*) from sales force index(product_id) where product_id in (1,2,3,4,5,6,7,8,9,10,11,12)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales
         type: range
possible_keys: PRODUCT_ID
          key: PRODUCT_ID
      key_len: 4
          ref: NULL
         rows: 98664
        Extra: Using where; Using index
1 row in set (0.00 sec)

398890 və 98664 yenidən fərq göz önündədir.
Lakin maraqlısı odur ki, product_id-ni 10-dan aşağı range-də müqayisə etdikdə nəticələr bərabər olur:

5.5 versiyada:

mysql [localhost] {msandbox} (mysqlspp) > explain select count(*) from sales force index(product_id) where product_id in (1,2,3,4,5,6,7,8,9)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales
         type: range
possible_keys: PRODUCT_ID
          key: PRODUCT_ID
      key_len: 4
          ref: NULL
         rows: 300112
        Extra: Using where; Using index
1 row in set (0.00 sec)

5.6 versiyada:

mysql> explain select count(*) from sales force index(product_id) where product_id in (1,2,3,4,5,6,7,8,9)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales
         type: range
possible_keys: PRODUCT_ID
          key: PRODUCT_ID
      key_len: 4
          ref: NULL
         rows: 300112
        Extra: Using where; Using index
1 row in set (0.00 sec)

Gördüyümüz kimi hər 2 halda 300112 rows examine olunacaq!
Ümumiləşdirmə aparaq, 5.6 versiyada 10-dan yuxarı range zamanı performance qazancı yaşanır lakin 10-dan aşağı range zamanı heç bir fərq baş vermir.
Buna səbəb 5.6 versiyada ilk dəfə olaraq təqdim olunmuş eq_range_index_dive_limit sistem dəyişənidir. 5.5-də bu dəyişən yoxdur:

mysql [localhost] {msandbox} (mysqlspp) > select @@eq_range_index_dive_limit;
ERROR 1193 (HY000): Unknown system variable 'eq_range_index_dive_limit'

5.6-da isə bu dəyişənin qiyməti default olaraq 10-dur:

mysql> select @@eq_range_index_dive_limit;
+-----------------------------+
| @@eq_range_index_dive_limit |
+-----------------------------+
|                          10 |
+-----------------------------+
1 row in set (0.00 sec)

Yuxarıdakı ümumiləşdirmədə göstərdiyimiz 10-nun səbəbi məhz bu dəyişənin default qiymətidir. Sınaq üçün bu dəyişənin qiymətini 9 edək və query-mizi 1-dən 9-a qədər range-lə run edək:

mysql> set @@eq_range_index_dive_limit=9;
Query OK, 0 rows affected (0.00 sec)

mysql> explain select count(*) from sales force index(product_id) where product_id in (1,2,3,4,5,6,7,8,9)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales
         type: range
possible_keys: PRODUCT_ID
          key: PRODUCT_ID
      key_len: 4
          ref: NULL
         rows: 73998
        Extra: Using where; Using index
1 row in set (0.00 sec)

Daha əvvəl row sayı 300112 idi, indi isə 73998 oldu. Göründüyü kimi Equality Range Optimization məqsədimizə çatmış olduq. Ən sonda eq_range_index_dive_limit dəyişəninin təsir etdiyi row sayının hesablanması alqoritmini də diqqətinizə çatdırım. Əgər xatırlayırsınızsa table status-dan row sayı bərabər idi 2351755-a. İndex cardinality isə 286-ya.
Hesablanma: 2351755/286*9 (range sayı)
Cavab:

mysql> select 2351755/286*9;
+---------------+
| 2351755/286*9 |
+---------------+
|    74006.2762 |
+---------------+
1 row in set (0.03 sec)

Həqiqətən də 74006.2762 təqribən bərabərdir 73998-a.
Nəticə olaraq onu deyə bilərik ki, MySQL 5.6 özü ilə bərabər bir çox yeniliklər gətirmişdir. bu yeniliklər əsasə etibarı ilə 3 qrupa bölünür:
1. Replication( GTİD based and crash-safe)
2. İnnoDB (performance and safety optimization)
3. MySQL query optimizer improvements

Bu tip yeniliklərdən istifadə edə bilməniz üçün 5.6 versiyaya upgrade etməniz məsləhətdir.

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: