Warum ist die DESC-Befehlsabfrage in MariaDB langsam?

Question

Ich habe eine Abfrage für Maridb ausgeführt. Wenn wir in ASC-Reihenfolge abfragen, prüft der Optimierer weniger Datensätze (r_rows) und schließt die Abfrage in etwa 500 ms ab, aber wenn die Reihenfolge auf DESC umgestellt wird, dauert die Abfrage länger zu vervollständigen und die Anzahl der r_rows beträgt etwa 2,27 Millionen.

Warum ist das so? Warum wirkt sich die ASC/DESC-Reihenfolge auf die Abfrageleistung aus?

Dies ist eine SQL-Abfrage

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

Die folgenden 2 MariaDB-Analyseausgaben zeigen den Ausführungsplan

ASC-geordnete Abfrage wird in ~503 ms abgeschlossen

+---------+------------------------------------------------------------------------------------------------------------------------
|                                                                             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'"         
|         |     },    
+---------+------------------------------------------------------------------------------------------------------------------------

DESC ASC geordnete Abfrage abgeschlossen ~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

索引优化建议

表索引 x_nuvo_eam_scheduled_m9e_e8s（状态、scheduled_date、scheduled_maintenance、sys_created_on） x_nuvo_eam_scheduled_m10s ( sys_id )

然后，修改为没有 (parens) 和 ticks，但也使用了预定 vs 维护的更干净的别名。第一个表具有适当的索引来优化 WHERE 和 JOIN 标准将会有所帮助。但创建完成覆盖索引也将有助于查询，因为所有元素都可以来自索引，而不是返回到每个表的原始数据页。

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