执行时长超3秒的MariaDB慢查询排查优化求助

• 操作系统：FreeBSD freebsd 13.2-RELEASE-p2 FreeBSD 13.2-RELEASE-p2 GENERIC amd64（EC2实例类型t2.xlarge）
• 数据库：mariadb104-server-10.4.28（客户端版本：mysql Ver 15.1 Distrib 10.5.20-MariaDB, for FreeBSD13.2 (amd64) using EditLine wrapper）

执行以下查询耗时超3秒：

select v.id, b.book_number, b.title, v.question_ocr 
from version v             
join solution s on v.solution_id = s.id             
join assignment a on s.assignment_id = a.id   
join book b on a.book_id = b.id           
order by v.created_at desc limit 10;

移除order by子句后，查询仅需0.0001秒即可完成。已通过vmstat、iostat、top、netstat等工具排查系统层面性能问题，未发现异常。

+------+-------------+-------+--------+------------------------------+----------------------+---------+-----------------------+------+-----------------------------------------------------------+
| id   | select_type | table | type   | possible_keys                | key                  | key_len | ref                   | rows | Extra                                                     |
+------+-------------+-------+--------+------------------------------+----------------------+---------+-----------------------+------+-----------------------------------------------------------+
|    1 | SIMPLE      | a     | index  | PRIMARY,IDX_30C544BA16A2B381 | IDX_30C544BA16A2B381 | 145     | NULL                  | 1344 | Using where; Using index; Using temporary; Using filesort |
|    1 | SIMPLE      | b     | eq_ref | PRIMARY                      | PRIMARY              | 144     | db_name____.a.book_id | 1    |                                                           |
|    1 | SIMPLE      | s     | ref    | PRIMARY,IDX_9F3329DBD19302F8 | IDX_9F3329DBD19302F8 | 145     | db_name____.a.id      | 35   | Using index                                               |
|    1 | SIMPLE      | v     | ref    | IDX_BF1CD3C31C0BE183         | IDX_BF1CD3C31C0BE183 | 145     | db_name____.s.id      | 1    |                                                           |
+------+-------------+-------+--------+------------------------------+----------------------+---------+-----------------------+------+-----------------------------------------------------------+

version表结构 #

+---------------------+--------------+------+-----+---------+-------+
| Field               | Type         | Null | Key | Default | Extra |
+---------------------+--------------+------+-----+---------+-------+
| id                  | char(36)     | NO   | PRI | NULL    |       |
| solution_id         | char(36)     | YES  | MUL | NULL    |       |
| status              | varchar(255) | NO   |     | NULL    |       |
| created_at          | datetime     | NO   | MUL | NULL    |       |
+---------------------+--------------+------+-----+---------+-------+

version表索引 #

+---------+------------+----------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name                   | Seq_in_index | Column_name         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| version |          0 | PRIMARY                    |            1 | id                  | A         |      223710 |     NULL | NULL   |      | BTREE      |         |               |
| version |          1 | IDX_BF1CD3C31C0BE183       |            1 | solution_id         | A         |      223710 |     NULL | NULL   | YES  | BTREE      |         |               |
| version |          1 | idx_solution_id_created_at |            1 | solution_id         | A         |      223710 |     NULL | NULL   | YES  | BTREE      |         |               |
| version |          1 | idx_solution_id_created_at |            2 | created_at          | A         |      223710 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Solution表结构 #

+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| id               | char(36)     | NO   | PRI | NULL    |       |
| book_id          | char(36)     | YES  | MUL | NULL    |       |
| exercise_number  | varchar(255) | NO   |     | NULL    |       |
| created_at       | datetime     | NO   |     | NULL    |       |
| assignment_id    | char(36)     | YES  | MUL | NULL    |       |
+------------------+--------------+------+-----+---------+-------+

Solution表索引 #

+----------+------------+----------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name             | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| solution |          0 | PRIMARY              |            1 | id               | A         |       91914 |     NULL | NULL   |      | BTREE      |         |               |
| solution |          1 | IDX_9F3329DB16A2B381 |            1 | book_id          | A         |         666 |     NULL | NULL   | YES  | BTREE      |         |               |
| solution |          1 | IDX_9F3329DBD19302F8 |            1 | assignment_id    | A         |        2872 |     NULL | NULL   | YES  | BTREE      |         |               |
| solution |          1 | IDX_9F3329DB4B09E92C |            1 | administrator_id | A         |         154 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+----------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Assignment表结构 #

+--------------------------------+--------------+------+-----+---------+-------+
| Field                          | Type         | Null | Key | Default | Extra |
+--------------------------------+--------------+------+-----+---------+-------+
| id                             | char(36)     | NO   | PRI | NULL    |       |
| book_id                        | char(36)     | YES  | MUL | NULL    |       |
| price                          | double       | YES  |     | NULL    |       |
| created_at                     | datetime     | YES  |     | NULL    |       |
+--------------------------------+--------------+------+-----+---------+-------+

Assignment表索引 #

+------------+------------+-----------------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name              | Seq_in_index | Column_name                | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+-----------------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| assignment |          0 | PRIMARY               |            1 | id                         | A         |        1431 |     NULL | NULL   |      | BTREE      |         |               |
| assignment |          1 | IDX_30C544BA16A2B381  |            1 | book_id                    | A         |         715 |     NULL | NULL   | YES  | BTREE      |         |               |
| assignment |          1 | IDX_30C544BA4B09E92C  |            1 | administrator_id           | A         |         130 |     NULL | NULL   | YES  | BTREE      |         |               |
+------------+------------+-----------------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Book表结构 #

+--------------------------+--------------+------+-----+---------+-------+
| Field                    | Type         | Null | Key | Default | Extra |
+--------------------------+--------------+------+-----+---------+-------+
| id                       | char(36)     | NO   | PRI | NULL    |       |
| title                    | varchar(255) | NO   |     | NULL    |       |
| book_number              | varchar(255) | NO   | UNI | NULL    |       |
+--------------------------+--------------+------+-----+---------+-------+

Book表索引 #

+-------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| book  |          0 | PRIMARY               |            1 | id          | A         |        2698 |     NULL | NULL   |      | BTREE      |         |               |
| book  |          1 | IDX_CBE5A33123EDC87   |            1 | subject_id  | A         |          42 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

sql_mode = STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO
bind-address = 127.0.0.1
socket = /var/run/mysql/mysql.sock
innodb_print_all_deadlocks = ON
collation_server = utf8_unicode_ci
character_set_server = utf8


innodb_buffer_pool_size = 8G  
performance_schema = ON       
innodb_thread_concurrency = 10 
max_connections = 150         
innodb_flush_log_at_trx_commit = 2  
join_buffer_size = 512K       
key_buffer_size = 128M         
sort_buffer_size = 1M

1. 优先从version表取最新数据再关联
   当前执行计划从assignment表开始遍历，导致需对大量数据排序。改为先获取version表最新10条记录，再关联其他表，避免全局排序：

   select v.id, b.book_number, b.title, v.question_ocr 
   from (select id, solution_id, question_ocr from version order by created_at desc limit 10) v
   join solution s on v.solution_id = s.id             
   join assignment a on s.assignment_id = a.id   
   join book b on a.book_id = b.id;
   

2. 创建覆盖索引减少回表与排序
   当前version表的索引未覆盖查询所需全部字段，创建包含排序字段与查询字段的覆盖索引：

   -- 支持INCLUDE的版本使用此语句
   create index idx_created_at_include on version(created_at desc) include(id, solution_id, question_ocr);
   -- 不支持INCLUDE的版本使用联合索引
   create index idx_created_at_solution_id on version(created_at desc, solution_id, id, question_ocr);
   

   该索引可直接提供排序与查询所需数据，无需回表和额外排序。

3. 强制优化器选择version表作为驱动表
   使用STRAIGHT_JOIN强制查询从version表开始执行，利用created_at索引排序：

   select v.id, b.book_number, b.title, v.question_ocr 
   from version v             
   straight_join solution s on v.solution_id = s.id             
   straight_join assignment a on s.assignment_id = a.id   
   straight_join book b on a.book_id = b.id           
   order by v.created_at desc limit 10;
   

4. 优化主键类型减少索引碎片化
   所有表使用char(36) UUID作为主键，会导致索引碎片化影响性能。若业务允许，建议逐步替换为自增整数主键，同时确保关联字段字符集一致，避免隐式转换导致索引失效。

内容的提问来源于stack exchange，提问作者proggaf