新数据库速度慢了很多

Question

我的工作切换到了一个新的数据库。从MySQL 5.5.31到MariaDB 10.5.4。现在大多数查询速度更快，但有一个查询从0.3秒变为1.6秒。当我尝试解释这两个查询时，我得到了不同的结果。

旧数据库解释

新数据库解释

有什么设置我需要更改吗？

查询：

SELECT
aanbieding.id,
aanbieding.uid,
aanbieding.vanprijs,
aanbieding.voorprijs,
aanbieding.opmerking,
aanbieding.aantal,
aanbieding.begindatum,
aanbieding.einddatum,
aanbieding.link,
aanbieding.alleen_online,
aanbieding.alleen_winkel,
aanbieding.ppc_feed_uid,
`aanbieding`.`einddatum` AS `einddatumFormat`,
DATE_FORMAT(
(`aanbieding`.`einddatum`),
'%W %e %M'
) AS `einddatumFormat2`,
DATE_FORMAT(
(`aanbieding`.`begindatum`),
'%W %e %M'
) AS `begindatumFormat2`,
ROUND(
(aanbieding.vanprijs) -(aanbieding.voorprijs),
2
) AS `totalekorting`,
ROUND(
100 *(
(aanbieding.vanprijs) -(aanbieding.voorprijs)
) / aanbieding.vanprijs
) AS `kortingspercentage`,
ROUND(
(aanbieding.voorprijs) /(
inhoud.aantal_liter * aanbieding.aantal
),
2
) AS `prijsperliter`,
merken.name AS merken_name,
merken.name_streep AS merken_name_streep,
inhoud.korte_name AS inhoud_name,
inhoud.name_streep AS inhoud_name_streep,
inhoud.korte_name_meervoud AS inhoud_name_meervoud,
inhoud.aantal_liter,
soort.uid AS soort_uid,
soort.name_streep AS soort_name_streep,
winkel.uid AS winkel_uid,
winkel.name AS winkel_name,
winkel.name_streep AS winkel_name_streep,
winkel.weergeven_aanbieding,
img_verpak.id AS img_verpak_id,
img_verpak.name AS img_verpak_name,
img_verpak.title AS img_verpak_title,
img_verpak.alt AS img_verpak_alt,
img_winkel.id AS img_winkel_id,
img_winkel.name AS img_winkel_name,
img_winkel.title AS img_winkel_title,
img_winkel.alt AS img_winkel_alt,
inhoud_soort.name AS inhoud_soort_name,
inhoud_soort.name_streep AS inhoud_soort_name_streep,
inhoud_soort.uid AS inhoud_soort_uid
FROM
aanbieding
JOIN soort ON aanbieding.`soort__soort_id` = soort.uid AND soort.weergeven = 1 AND soort.deleted = 0
JOIN winkel ON winkel.uid = aanbieding.`winkel__winkel_id` AND winkel.deleted = '0' AND winkel.weergeven = '1'
JOIN merken ON merken.uid = soort.`merken__merken_id` AND merken.deleted = '0' AND merken.weergeven = '1'
JOIN inhoud ON inhoud.uid = soort.`inhoud__inhoud_id` AND inhoud.deleted = '0' AND inhoud.weergeven = '1'
JOIN `fr_images` AS `img_verpak`
ON
`img_verpak`.`table_id_id` = `soort`.`uid` AND img_verpak.image_settings_id = 53 AND img_verpak.deleted = 0 AND img_verpak.position = 1
JOIN `fr_images` AS `img_winkel`
ON
`img_winkel`.`table_id_id` = `winkel`.`uid` AND img_winkel.image_settings_id = 108 AND img_winkel.deleted = 0 AND img_winkel.position = 1
JOIN inhoud_soort ON inhoud_soort.uid = inhoud.`soort__inhoud_soort_id` AND inhoud_soort.deleted = 0 AND inhoud_soort.weergeven = 1
WHERE
aanbieding.deleted = '0' AND aanbieding.weergeven = '1' AND aanbieding.begindatum <= CURDATE() AND(
aanbieding.einddatum >= CURDATE() OR aanbieding.einddatum IS NULL)

Answer 1

新设置的解释屏幕截图看起来被截断了，所以不是它的尾部所说的100%，而是一个通用的解释：

1. 查看新旧服务器上optimizer_switch变量的值。确定不同之处。将新服务器设置为与旧服务器相同的优化器开关设置。如果这导致了相同的执行计划，那么将您的方法一分为二，以使与产生旧执行计划的默认值的偏差最小。

2. 如果1(被证明是不够的，那么为每个表添加索引提示(USE index(，直到您让它使用旧的执行计划。然后将你的方式一分为二，得到生成旧计划所需的最小索引提示集。

3. 如果2(仍然不够，请使用SELECT STRAIGHT_JOIN而不仅仅是SELECT，并更改查询，使表按与原始快速EXPLAIN完全相同的顺序列出。

如果在所有3个之后，它仍然拒绝执行原始计划，那么你就运气不好了。

请注意，10.5.x还没有准备好生产，它仍然有一些令人讨厌的性能倒退。参见：

https://www.percona.com/blog/2020/07/31/how-mysql-8-0-21-and-mariadb-10-5-4-perform-in-io-bound-scenarios-on-sata-ssd-and-nvme-storage/

和

https://www.percona.com/blog/2020/08/14/evaluating-performance-improvements-in-mariadb-10-5-5/