Mengapa pertanyaan arahan DESC lambat pada MariaDB

Question

Saya mempunyai pertanyaan yang dijalankan terhadap Maridb, apabila kami membuat pertanyaan dalam susunan ASC, pengoptimum menyemak lebih sedikit bilangan rekod (r_rows) dan menyelesaikan pertanyaan dalam kira-kira 500 ms, tetapi apabila menukar pesanan kepada DESC, pertanyaan yang sama memerlukan lebih banyak masa untuk disiapkan dan r_rows adalah kira-kira 2.27 juta.

Kenapa ni? Mengapakah susunan ASC/DESC mempengaruhi prestasi pertanyaan?

Ini adalah pertanyaan SQL

SELECT x_nuvo_eam_scheduled_m9e_e8s0.`sys_id`
FROM (
        x_nuvo_eam_scheduled_m9e_e8s x_nuvo_eam_scheduled_m9e_e8s0
        LEFT JOIN x_nuvo_eam_scheduled_m10s x_nuvo_eam_scheduled_maintena1 ON x_nuvo_eam_scheduled_m9e_e8s0.`scheduled_maintenance` = x_nuvo_eam_scheduled_maintena1.`sys_id`
    )
WHERE x_nuvo_eam_scheduled_m9e_e8s0.`status` = 'Pending'
    AND x_nuvo_eam_scheduled_m9e_e8s0.`scheduled_date` >= '2022-02-15 06:00:00'
    AND x_nuvo_eam_scheduled_maintena1.`asset` IS NULL
ORDER BY x_nuvo_eam_scheduled_m9e_e8s0.`sys_created_on` ASC
limit 0, 100

2 output analisis MariaDB berikut menunjukkan rancangan pelaksanaan

Pertanyaan pesanan ASC selesai dalam ~503ms

+---------+------------------------------------------------------------------------------------------------------------------------
|                                                                             1 result(s):                                         
+---------+------------------------------------------------------------------------------------------------------------------------
| ANALYZE | {                                                                                                                      
|         |   "query_block": {                                                                                                     
|         |     "select_id": 1,                                                                                                    
|         |     "r_loops": 1,                                                                                                      
|         |     "r_total_time_ms": 503.93,                                                                                         
|         |     "table": {                                                                                                         
|         |       "table_name": "Table_A",                                                                                         
|         |       "access_type": "index",                                                                                          
|         |       "possible_keys": ["idx1"],                                                                                       
|         |       "key": "sys_created_on",                                                                                         
|         |       "key_length": "6",                                                                                               
|         |       "used_key_parts": ["sys_created_on"],                                                                            
|         |       "r_loops": 1,                                                                                                    
|         |       "rows": 2695302,                                                                                                 
|         |       "r_rows": 234328,                                                                                                
|         |       "r_total_time_ms": 476.64,                                                                                       
|         |       "filtered": 50,                                                                                                  
|         |       "r_filtered": 0.1903,                                                                                            
|         |       "attached_condition": "Table_A.`status` = 'Pending' and Table_A.scheduled_date >= '2022-02-15 06:00:00'"         
|         |     },    
+---------+------------------------------------------------------------------------------------------------------------------------

Pertanyaan pesanan DESC ASC selesai ~9118 ms

r_rows significantly Larger as comparing to ASC. 

+---------+-----------------------------------------------------------------------------------------------------------------------
|                                                                             1 result(s):                                        
+---------+-----------------------------------------------------------------------------------------------------------------------
| ANALYZE | {                                                                                                                     
|         |   "query_block": {                                                                                                    
|         |     "select_id": 1,                                                                                                   
|         |     "r_loops": 1,                                                                                                     
|         |     "r_total_time_ms":9118.4,                                                                                              
|         |     "table": {                                                                                                        
|         |       "table_name": "Table_A",                                                                                        
|         |       "access_type": "index",                                                                                         
|         |       "possible_keys": ["idx1"],                                                                                      
|         |       "key": "sys_created_on",                                                                                        
|         |       "key_length": "6",                                                                                              
|         |       "used_key_parts": ["sys_created_on"],                                                                           
|         |       "r_loops": 1,                                                                                                   
|         |       "rows": 2695302,                                                                                                
|         |       "r_rows": 2.27e6,                                                                                               
|         |       "r_total_time_ms": 4380.1,                                                                                      
|         |       "filtered": 50,                                                                                                 
|         |       "r_filtered": 70.102,                                                                                           
|         |       "attached_condition": "Table_A.`status` = 'Pending' and Table_A.scheduled_date >= '2022-02-15 06:00:00'" |
|         |     },  
+---------+-----------------------------------------------------------------------------------------------------------------------

Answer 1

Cadangan pengoptimuman indeks

Indeks jadual x_nuvo_eam_scheduled_m9e_e8s (status, scheduled_date, scheduled_maintenance, sys_created_on) x_nuvo_eam_scheduled_m10s (sys_id)

Kemudian, diubah suai kepada tidak mempunyai (paren) dan ticks, tetapi juga menggunakan alias lebih bersih yang diselenggara oleh berjadual vs. Ia akan membantu untuk mempunyai jadual pertama dengan indeks yang sesuai untuk mengoptimumkan kriteria WHERE dan JOIN. Tetapi mencipta indeks penutup yang lengkap juga akan membantu pertanyaan kerana semua elemen boleh datang daripada indeks dan bukannya kembali ke halaman data asal untuk setiap jadual.

SELECT 
        sched.sys_id
    FROM 
        x_nuvo_eam_scheduled_m9e_e8s sched
            LEFT JOIN x_nuvo_eam_scheduled_m10s maint
                ON sched.scheduled_maintenance = maint.sys_id
    WHERE 
            sched.status = 'Pending'
        AND sched.scheduled_date >= '2022-02-15 06:00:00'
        AND maint.asset IS NULL
    ORDER BY 
        sched.sys_created_on ASC
    limit 
        0, 100