mariadb 执行3秒的慢速查询

jrcvhitl 于 2023-10-20 发布在其他

关注(0)|答案(2)|浏览(104)

使用FreeBSD freebsd 13.2-RELEASE-p2 FreeBSD 13.2-RELEASE-p2 GENERIC amd64作为ec2 instace（t2.xlarge）和mariadb104-server-10.4.28：mysql Ver 15.1 Distrib 10.5.20-MariaDB, for FreeBSD13.2 (amd64) using EditLine wrapper
试着找出为什么查询：

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;

它需要3秒才能执行。
解释显示：

+------+-------------+-------+--------+------------------------------+----------------------+---------+-----------------------+------+-----------------------------------------------------------+
| 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    |                                                           |
+------+-------------+-------+--------+------------------------------+----------------------+---------+-----------------------+------+-----------------------------------------------------------+

有趣的是，当我删除order by子句时，执行需要0.0001秒。
我尝试了vmstat、iostat、top、netstat等分析工具。
有没有人有任何解决方案，我如何解决查询速度慢的问题？
在created_at列上有一个索引。
关于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    |       |
+---------------------+--------------+------+-----+---------+-------+

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    |       |
+------------------+--------------+------+-----+---------+-------+

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    |       |
+--------------------------------+--------------+------+-----+---------+-------+

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    |       |
+--------------------------+--------------+------+-----+---------+-------+

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：

+----------+------------+----------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 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：

+------------+------------+-----------------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 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：

+-------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 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      |         |               |
+-------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

my.cnf对于mariadb：

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

mariadb

来源：https://stackoverflow.com/questions/77105767/slow-query-with-3-seconds-of-execution

2条答案

按热度按时间

0mkxixxg1#

你有没有试着把created_at中的索引改为DESC而不是ASC？你可以在这里阅读更多关于这一点：https://dev.mysql.com/doc/refman/8.0/en/descending-indexes.html

赞(0）回复(0）举报 2023-10-20

xt0899hw2#

select  v2.id, b.book_number, b.title, v3.question_ocr
    from  ( SELECT id, solution_id 
              FROM version v1 ORDER BY created_at DESC LIMIT 10
          ) v2
    join  solution s  ON v2.solution_id = s.id
    join  assignment a  ON s.assignment_id = a.id
    join  book b  ON a.book_id = b.id
    JOIN  version v3 ON v3.id = v2.id
    order by  v3.created_at desc   -- yes, repeated (if needed)
    ;

并且具有

INDEX(created_at, solution_id, id)

char(36)--另外，考虑避免UUID;参见UUIDs
innodb_buffer_pool_size的值是多少？它应该是 * 可用 * RAM的70%左右。

赞(0）回复(0）举报 2023-10-20

我来回答

mariadb 执行3秒的慢速查询

2条答案

相关问题

热门标签

最新问答