我在我的两台服务器中的一台上运行着 moodle，他的 moodle 数据库安装在 mariadb 10.4.22 上并且正在维护中，因此我将 moodle 转移到了另一台安装有 mariadb 11.2.4 的服务器上，但是第二台服务器的 moodle 数据库性能存在问题，我在 moodle 论坛上找到了一个帖子，讨论了将 10.4.2 mariadb 的数据库转储恢复到 11 之后的版本时出现的问题，那里说我需要在数据库上运行 ANALYZE TABLE 才能使其更好地工作：
https://moodle.org/mod/forum/discuss.php?d=446712
我尝试在数据库的每个表上执行此命令，但是它不起作用，数据库仍然非常慢（主要是消息），我尝试将转储恢复到旧服务器并且它正常工作，但是 moodle 不再支持 mariadb 10.4.2，所以我必须更新，我该怎么办？我可以运行其他命令吗？我该如何解决问题？（我对故障排除不是很有经验，所以如果你们想要一些信息，就说我会提供，因为我不知道解决这个问题需要什么信息）
当我在新服务器（装有 mariadb 11.2.4 的服务器）上运行 mysqltuner 时有结果，但似乎没有太大帮助。
mysqltuner1 mysqltuner2 mysqltuner3 mysqltuner4
编辑：我收到了来自 slowlog 的回复
# Time: 240828 12:55:04
# User@Host: user[user] @ localhost []
# Thread_id: 318 Schema: my_db QC_hit: No
# Query_time: 332.625304 Lock_time: 0.000335 Rows_sent: 32 Rows_examined: 111342578
# Rows_affected: 0 Bytes_sent: 0
use my_db;
SET timestamp=1724849704;
SELECT m.id as messageid, mc.id as id, mc.name as conversationname, mc.type as conversationtype, m.useridfrom,
m.smallmessage, m.fullmessage, m.fullmessageformat, m.fullmessagetrust, m.fullmessagehtml, m.timecreated,
mc.component, mc.itemtype, mc.itemid, mc.contextid, mca.action as ismuted
FROM ehvp_message_conversations mc
INNER JOIN ehvp_message_conversation_members mcm
ON (mcm.conversationid = mc.id AND mcm.userid = '21')
LEFT JOIN (
SELECT m.conversationid, MAX(m.id) AS messageid
FROM ehvp_messages m
INNER JOIN (
SELECT m.conversationid, MAX(m.timecreated) as maxtime
FROM ehvp_messages m
INNER JOIN ehvp_message_conversation_members mcm
ON mcm.conversationid = m.conversationid
LEFT JOIN ehvp_message_user_actions mua
ON (mua.messageid = m.id AND mua.userid = '21' AND mua.action = '2')
WHERE mua.id is NULL
AND mcm.userid = '21'
GROUP BY m.conversationid
) maxmessage
ON maxmessage.maxtime = m.timecreated AND maxmessage.conversationid = m.conversationid
GROUP BY m.conversationid
) lastmessage
ON lastmessage.conversationid = mc.id
LEFT JOIN ehvp_messages m
ON m.id = lastmessage.messageid
LEFT JOIN ehvp_message_conversation_actions mca
ON (mca.conversationid = mc.id AND mca.userid = '21' AND mca.action = '1')
WHERE mc.id IS NOT NULL
AND mc.enabled = 1 AND (mc.type = '1' OR mc.type = '3') AND mc.id <> '13'
ORDER BY (CASE WHEN m.timecreated IS NULL THEN 0 ELSE 1 END) DESC, m.timecreated DESC, id DESC LIMIT 0, 51;
编辑2：我在两台服务器上的查询中使用命令 EXPLAIN：
11.2.4 mariadb服务器（存在性能问题的服务器，这里的数据库称为my_db）：
+------+-----------------+------------+------------+----------------------------------------------------------------------------------+---------------------------------------------------+---------+--------------------------------------------+---------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-----------------+------------+------------+----------------------------------------------------------------------------------+---------------------------------------------------+---------+--------------------------------------------+---------+----------------------------------------------+
| 1 | PRIMARY | mcm | ref | ehvp_messconvmemb_con_ix,ehvp_messconvmemb_use_ix | ehvp_messconvmemb_use_ix | 8 | const | 4092 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | mc | eq_ref | PRIMARY,ehvp_messconv_typ_ix | PRIMARY | 8 | my_db.mcm.conversationid | 1 | Using where |
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 9 | my_db.mcm.conversationid | 1 | |
| 1 | PRIMARY | m | eq_ref | PRIMARY | PRIMARY | 8 | lastmessage.messageid | 1 | Using where |
| 1 | PRIMARY | mca | ref|filter | ehvp_messconvacti_use_ix,ehvp_messconvacti_con_ix | ehvp_messconvacti_con_ix|ehvp_messconvacti_use_ix | 8|8 | my_db.mcm.conversationid | 1 (11%) | Using where; Using rowid filter |
| 2 | LATERAL DERIVED | m | ref | ehvp_mess_contim_ix,ehvp_mess_con_ix | ehvp_mess_contim_ix | 8 | my_db.mcm.conversationid | 2 | Using where; Using index |
| 2 | LATERAL DERIVED | <derived3> | ref | key0 | key0 | 17 | my_db.m.conversationid,my_db.m.timecreated | 1 | |
| 3 | DERIVED | m | index | ehvp_mess_contim_ix,ehvp_mess_con_ix | ehvp_mess_contim_ix | 16 | NULL | 9664 | Using index; Using temporary; Using filesort |
| 3 | DERIVED | mua | eq_ref | ehvp_messuseracti_usemesac_uix,ehvp_messuseracti_use_ix,ehvp_messuseracti_mes_ix | ehvp_messuseracti_usemesac_uix | 24 | const,my_db.m.id,const | 1 | Using where; Using index; Not exists |
| 3 | DERIVED | mcm | ref | ehvp_messconvmemb_con_ix,ehvp_messconvmemb_use_ix | ehvp_messconvmemb_con_ix | 8 | my_db.m.conversationid | 1 | Using where |
+------+-----------------+------------+------------+----------------------------------------------------------------------------------+---------------------------------------------------+---------+--------------------------------------------+---------+----------------------------------------------+
10.4.2 mariadb服务器（没有问题的服务器，这里的数据库称为moodle）：
+------+-----------------+------------+------------+----------------------------------------------------------------------------------+---------------------------------------------------+---------+----------------------------------------------+---------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-----------------+------------+------------+----------------------------------------------------------------------------------+---------------------------------------------------+---------+----------------------------------------------+---------+----------------------------------------------+
| 1 | PRIMARY | mcm | ref | ehvp_messconvmemb_con_ix,ehvp_messconvmemb_use_ix | ehvp_messconvmemb_use_ix | 8 | const | 2988 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | mc | eq_ref | PRIMARY,ehvp_messconv_typ_ix | PRIMARY | 8 | moodle.mcm.conversationid | 1 | Using where |
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 9 | moodle.mcm.conversationid | 2 | |
| 1 | PRIMARY | m | eq_ref | PRIMARY | PRIMARY | 8 | lastmessage.messageid | 1 | Using where |
| 1 | PRIMARY | mca | ref|filter | ehvp_messconvacti_use_ix,ehvp_messconvacti_con_ix | ehvp_messconvacti_con_ix|ehvp_messconvacti_use_ix | 8|8 | moodle.mcm.conversationid | 1 (11%) | Using where; Using rowid filter |
| 2 | LATERAL DERIVED | m | ref | ehvp_mess_contim_ix,ehvp_mess_con_ix | ehvp_mess_contim_ix | 8 | moodle.mcm.conversationid | 1 | Using index |
| 2 | LATERAL DERIVED | <derived3> | ref | key0 | key0 | 17 | moodle.m.conversationid,moodle.m.timecreated | 2 | |
| 3 | DERIVED | m | index | ehvp_mess_contim_ix,ehvp_mess_con_ix | ehvp_mess_contim_ix | 16 | NULL | 5005 | Using index; Using temporary; Using filesort |
| 3 | DERIVED | mua | ref | ehvp_messuseracti_usemesac_uix,ehvp_messuseracti_use_ix,ehvp_messuseracti_mes_ix | ehvp_messuseracti_mes_ix | 8 | moodle.m.id | 1 | Using where; Not exists |
| 3 | DERIVED | mcm | ref|filter | ehvp_messconvmemb_con_ix,ehvp_messconvmemb_use_ix | ehvp_messconvmemb_con_ix|ehvp_messconvmemb_use_ix | 8|8 | moodle.m.conversationid | 1 (3%) | Using where; Using rowid filter |
+------+-----------------+------------+------------+----------------------------------------------------------------------------------+---------------------------------------------------+---------+----------------------------------------------+---------+----------------------------------------------+
看起来两个服务器的索引之间没有太大差别。
编辑 3：这些是针对来自 slowlog 的查询所涉及的表的命令 SHOW CREATE TABLE 的返回值：
为服务器 mariadb 11.2.4（存在性能问题的服务器）创建表
为服务器 mariadb 10.4.2（没有问题的服务器）创建表