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'" |
| | },
+---------+----------------------------------------------------------------------------------------------------------------------- 
![[Web front-end] Permulaan pantas Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
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