Əsas səhifə > MySQL administration > LIMIT və OFFSET optimization

LIMIT və OFFSET optimization

LIMIT və OFFSET bütün pagination-larda bolca istifadə olunan bir şeydir. Mənə elə gəlir ki hamı tanıyır bu nədir…
Biz yenə əlimizdə olan 2.5 mln row-luq Sales cədvəli ilə işləyəcəyik…Sales table download
Sales cədvəlimizdə 2500004 row var…Bu cədvəlin strukturu:

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=2500005 DEFAULT CHARSET=latin1

2 index-li sütunumuz var sales_id və customer_id…
Bu mövzu limit və offset-in optimization üsullarından bəhs edir. Siz özünüzə uyğununu seçib istifadə edin…

1-ci üsul bildiyimiz gündəlik istifadə olunandır:

mysql> select sales_id,sale_date from sales order by sales_id limit 2500000,4;
+----------+---------------------+
| sales_id | sale_date           |
+----------+---------------------+
|  2500001 | 2005-11-25 22:56:24 |
|  2500002 | 2005-11-25 22:56:58 |
|  2500003 | 2005-11-25 23:12:30 |
|  2500004 | 2012-11-22 18:09:00 |
+----------+---------------------+
4 rows in set (0.85 sec)

vaxt : 0.85 saniyə

show status-a baxaq:

mysql> show status Where variable_name like 'Handler%' or Variable_name like 'Created%';
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| Created_tmp_disk_tables    | 0       |
| Created_tmp_files          | 0       |
| Created_tmp_tables         | 0       |
| Handler_commit             | 1       |
| Handler_delete             | 0       |
| Handler_discover           | 0       |
| Handler_prepare            | 0       |
| Handler_read_first         | 1       |
| Handler_read_key           | 1       |
| Handler_read_last          | 0       |
| Handler_read_next          | 2500003 |
| Handler_read_prev          | 0       |
| Handler_read_rnd           | 0       |
| Handler_read_rnd_next      | 0       |
| Handler_rollback           | 0       |
| Handler_savepoint          | 0       |
| Handler_savepoint_rollback | 0       |
| Handler_update             | 0       |
| Handler_write              | 0       |
+----------------------------+---------+
19 rows in set (0.00 sec)

Handler_read_next = 2500003
From Documentation:
Handler_read_next
The number of requests to read the next row in the data file. This value is high if you are doing a lot of table scans.

Explain-e baxaq:

mysql> explain select sales_id,sale_date from sales order by sales_id limit 2500000,4\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 2500004
        Extra: 
1 row in set (0.00 sec)

rows: 2500004
From Documentation
rows: Approximation of # of rows returned

İndi isə limit-in işləmə prinsipini izah edək: limit və offset bütün cədvəli oxuyur…və yalnız bu oxunma bitdikdən sonra öz işlərini görürlər. daha dəqiq bizim misal üçün 2500003 row generate olunur və yalnız bundan sonra 2500000 atılır və son 4 saxlanılır.
Məhz buna görədir ki, bu qədər vaxt gedir. əgər sales_id index-lənməmiş olsaydı onda daha çox vaxt gedərdi:

mysql> select sales_id,sale_date from sales_test order by sales_id limit 2500000,4;
+----------+---------------------+
| sales_id | sale_date           |
+----------+---------------------+
|  2500001 | 2005-11-25 22:56:24 |
|  2500002 | 2005-11-25 22:56:58 |
|  2500003 | 2005-11-25 23:12:30 |
|  2500004 | 2012-11-22 18:09:00 |
+----------+---------------------+
4 rows in set (4.35 sec)

vaxt : 4.35 saniyə

2-ci üsul eyni nəticəni daha tez alırıq:

select sales_id,sale_date from sales
inner join
(select sales_id from sales order by sales_id limit 2500000,4
) as lim using(sales_id);

+----------+---------------------+
| sales_id | sale_date           |
+----------+---------------------+
|  2500001 | 2005-11-25 22:56:24 |
|  2500002 | 2005-11-25 22:56:58 |
|  2500003 | 2005-11-25 23:12:30 |
|  2500004 | 2012-11-22 18:09:00 |
+----------+---------------------+
4 rows in set (0.77 sec)

vaxt : 0.77 saniyə

Ümumiyyətlə deyə bilərik ki, limit və offset-lə işləyəndə problem yaradan yalnız və yalnız offset-dir. Aşağıdakı 2 üsul offset-sizdir.

3-cü üsul:

select sales_id,sale_date from sales
where sales_id>2500000
order by sales_id limit 4;

+----------+---------------------+
| sales_id | sale_date           |
+----------+---------------------+
|  2500001 | 2005-11-25 22:56:24 |
|  2500002 | 2005-11-25 22:56:58 |
|  2500003 | 2005-11-25 23:12:30 |
|  2500004 | 2012-11-22 18:09:00 |
+----------+---------------------+
4 rows in set (0.00 sec)

vaxt : 0.0 saniyə

4-cü üsul:

select sales_id, sale_date from sales
where sales_id between 2500001 and 2500004
order by sales_id;

+----------+---------------------+
| sales_id | sale_date           |
+----------+---------------------+
|  2500001 | 2005-11-25 22:56:24 |
|  2500002 | 2005-11-25 22:56:58 |
|  2500003 | 2005-11-25 23:12:30 |
|  2500004 | 2012-11-22 18:09:00 |
+----------+---------------------+
4 rows in set (0.00 sec)

vaxt : 0.0 saniyə

Nətər söhbətdi?🙂 Bəli həqiqətən də dəhşətli imiş…0.85 sec, 0.77 sec və 0.0 sec…
Gəlin status-a da baxaq:

mysql> show status Where variable_name like 'Handler%' or Variable_name like 'Created%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Created_tmp_disk_tables    | 0     |
| Created_tmp_files          | 0     |
| Created_tmp_tables         | 0     |
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     |
| Handler_read_last          | 0     |
| Handler_read_next          | 3     |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
19 rows in set (0.00 sec)

Handler_read_next : 3. Bəli bizim yazdığımız son 2 üsul zamanı (3 və 4) query cəmi 3 row oxuyur…
Səbəbini araşdırarkən maraqlı bir fakta rast gəldim və google-lladım…Oracle-da “Index Range Scan” deyilən bir şey var imiş…Axtardığım bütün məlumatlar yalnız Oracle üçün çıxdı. MySQL-ə aid 1 dənə düz əməlli şey yenə documentation-da:
The Range Access Method for Single-Part Indexes

Explain-e baxsaq “Index Range Scan”-ı görmüş olarıq:

mysql> explain select sales_id, sale_date from sales where sales_id between 2500001 and 2500004 order by sales_id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 4
        Extra: Using where
1 row in set (0.00 sec)

type: range
range: KEY compared with =, <, , >=, IS NULL, ,BETWEEN, or IN([list]), the index is used to select rows that fall within a given range of index values

Bütün bunlardan belə nəticə çıxarmaq olar ki, böyük cədvəllər üçün offset-dən uzaq durun…Onun əvəzinə yuxarıdakı 2 üsuldan(3 və 4) birini istifadə edin və yaxud özünüz daha fərqlı bir üsul tapın

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: