我们正在迁移到新服务器，并注意到一些奇怪的性能下降。旧服务器是运行 MariaDB 10.3 (10.3.27-MariaDB-1:10.3.27+maria~bionic-log) 的 Ubuntu 18.04；新服务器是运行 Mariadb 10.4 (10.4.33-MariaDB-1:10.4.33+maria~ubu2004-log) 的 Ubuntu 20.04。硬件也较新。我们构建了一个示例查询：
SELECT * FROM
( SELECT product_id, purchase_id, SUM(qty) AS qty
FROM bq_purchaseitems GROUP BY purchase_id ) pi
LEFT JOIN bq_purchases pu ON pi.`purchase_id` = pu.id
LEFT JOIN ( SELECT * FROM bq_products ) p ON p.id = pi.`product_id`
WHERE p.id = 30242;
在 MariaDB 10.3 上我们得到以下信息：
Query_time: 2.992407 Lock_time: 0.000101 Rows_sent: 105 Rows_examined: 3625797
# Rows_affected: 0 Bytes_sent: 10461
# Tmp_tables: 1 Tmp_disk_tables: 0 Tmp_table_sizes: 4190240
# Full_scan: Yes Full_join: No Tmp_table: Yes Tmp_table_on_disk: No
# Filesort: No Filesort_on_disk: No Merge_passes: 0 Priority_queue: No
然而，在 10.4 版本中我们看到了这样的情况：
# Query_time: 8.631189 Lock_time: 0.000274 Rows_sent: 105 Rows_examined: 3625797
# Rows_affected: 0 Bytes_sent: 10461
# Tmp_tables: 1 Tmp_disk_tables: 0 Tmp_table_sizes: 4190240
# Full_scan: Yes Full_join: No Tmp_table: Yes Tmp_table_on_disk: No
# Filesort: No Filesort_on_disk: No Merge_passes: 0 Priority_queue: No
10.4 版扩展解释
>explain extended SELECT * FROM ( SELECT product_id, purchase_id, SUM(qty) AS qty FROM bq_purchaseitems GROUP BY purchase_id ) pi LEFT JOIN bq_purchases pu ON pi.`purchase_id` = pu.id LEFT JOIN ( SELECT * FROM bq_products ) p ON p.id = pi.`product_id` WHERE p.id = 30242;
+------+-------------+------------------+--------+---------------+-----------+---------+----------------+---------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+------------------+--------+---------------+-----------+---------+----------------+---------+----------+-------------+
| 1 | PRIMARY | bq_products | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2933042 | 100.00 | Using where |
| 1 | PRIMARY | pu | eq_ref | PRIMARY | PRIMARY | 4 | pi.purchase_id | 1 | 100.00 | |
| 2 | DERIVED | bq_purchaseitems | index | NULL | accountid | 4 | NULL | 2933042 | 100.00 | |
+------+-------------+------------------+--------+---------------+-----------+---------+----------------+---------+----------+-------------+
显示解释的警告：
| Note | 1003 | /* select#1 */ select `pi`.`product_id` AS `product_id`,`pi`.`purchase_id` AS `purchase_id`,`pi`.`qty` AS `qty`,`pagony`.`pu`.`id` AS `id`,`pagony`.`pu`.`pid` AS `pid`,`pagony`.`pu`.`seller` AS `seller`,`pagony`.`pu`.`origin` AS `origin`,`pagony`.`pu`.`pos` AS `pos`,`pagony`.`pu`.`customer` AS `customer`,`pagony`.`pu`.`agent` AS `agent`,`pagony`.`pu`.`destination` AS `destination`,`pagony`.`pu`.`transaction_date` AS `transaction_date`,`pagony`.`pu`.`close_date` AS `close_date`,`pagony`.`pu`.`state` AS `state`,`pagony`.`pu`.`modifying` AS `modifying`,`pagony`.`pu`.`stock_reserved` AS `stock_reserved`,`pagony`.`pu`.`processable` AS `processable`,`pagony`.`pu`.`strict` AS `strict`,`pagony`.`pu`.`is_return` AS `is_return`,`pagony`.`pu`.`autocheckup` AS `autocheckup`,`pagony`.`pu`.`paid` AS `paid`,`pagony`.`pu`.`original_purchase_id` AS `original_purchase_id`,`pagony`.`pu`.`comment` AS `comment`,`pagony`.`pu`.`deleted` AS `deleted`,`pagony`.`pu`.`created_at` AS `created_at`,`pagony`.`pu`.`updated_at` AS `updated_at`,`pagony`.`pu`.`created_by` AS `created_by`,`pagony`.`pu`.`updated_by` AS `updated_by`,30242 AS `id`,30242 AS `base_product_id`,NULL AS `label_id`,'Matricás foglalkoztató - Állatokkal' AS `name`,NULL AS `display_name`,NULL AS `original_title`,7 AS `category_id`,327 AS `tradename_id`,'9071' AS `sku`,3070900090712 AS `barcode`,3490 AS `listprice`,NULL AS `b2b_price`,1 AS `currency`,215 AS `width`,230 AS `length`,NULL AS `height`,NULL AS `weight`,'My long description text cutted out' AS `description`,NULL AS `list_description`,NULL AS `pagenum`,NULL AS `cover`,NULL AS `pub_year`,900 AS `state`,0 AS `dividable`,NULL AS `voucher_type_id`,1 AS `active`,NULL AS `liquid_measure`,NULL AS `ntak_alkategoria_id`,'2021-04-23 10:08:43' AS `created_at`,'2024-02-08 15:15:50' AS `updated_at`,41 AS `created_by`,44 AS `updated_by`,1 AS `from_age`,3 AS `to_age`,NULL AS `gender_preference`,NULL AS `disc_number`,NULL AS `playtime`,NULL AS `player_number`,NULL AS `material`,NULL AS `size`,NULL AS `color`,NULL AS `series_id`,NULL AS `volume`,NULL AS `edition_number`,NULL AS `epub`,NULL AS `pdf`,NULL AS `audiobook` from (/* select#2 */ select `pagony`.`bq_purchaseitems`.`product_id` AS `product_id`,`pagony`.`bq_purchaseitems`.`purchase_id` AS `purchase_id`,sum(`pagony`.`bq_purchaseitems`.`qty`) AS `qty` from `pagony`.`bq_purchaseitems` group by `pagony`.`bq_purchaseitems`.`purchase_id` having `pagony`.`bq_purchaseitems`.`product_id` = 30242) `pi` left join `pagony`.`bq_purchases` `pu` on(`pagony`.`pu`.`id` = `pi`.`purchase_id`) join `pagony`.`bq_products` where `pi`.`product_id` = 30242 |
SQL 配置几乎相同，10.4 具有更多 RAM，tmp 位于 ramdisk/tmpfs 上。可能存在什么问题？