mysql - Mariadb 5.5 比 MySQL 5.1 慢

Question

我有一个查询在 MySQL 5.1 服务器上运行大约 20 秒，但在 MariaDB 5.5 服务器上需要将近 15 分钟。像 key_buffer_size 和 tmp_table_size 和 max_heap_table_size 这样的常见嫌疑人都是相等的(128M)。据我所知，大多数设置都是相等的(query_cache 等)

查询:

SELECT  products.id, 
concat(publications.company_name,' [',publications.quote,'] ', products.name) as n, 
products.impressions, 
products.contacts, 
is_channel, 
sl.i, 
count(*) 
FROM products 
LEFT JOIN publications ON products.publications_id = publications.id 
LEFT OUTER JOIN (  
    SELECT adspace.id AS i, 
    slots.products_id FROM adspace 
    LEFT JOIN  slots ON adspace.slots_id = slots.id 
        AND adspace.end > '2016-01-25 10:28:49' 
        WHERE adspace.active = 1) AS sl 
    ON sl.products_id = products.id  
WHERE 1 = 1 
AND publications.active=1 
GROUP BY products.id 
ORDER BY n ASC;

唯一的区别在于解释:

旧服务器(MySQL 5.1)

+----+-------------+--------------+--------+---------------+---------+---------+-----------------------------------------+--------+---------------------------------+
| id | select_type | table        | type   | possible_keys | key     | key_len | ref                                     | rows   | Extra                           |
+----+-------------+--------------+--------+---------------+---------+---------+-----------------------------------------+--------+---------------------------------+
|  1 | PRIMARY     | products     | ALL    | NULL          | NULL    | NULL    | NULL                                    |   6568 | Using temporary; Using filesort |
|  1 | PRIMARY     | publications | eq_ref | PRIMARY       | PRIMARY | 4       | db.products.publications_id |      1 | Using where                                 |
|  1 | PRIMARY     | <derived2>   | ALL    | NULL          | NULL    | NULL    | NULL                                    |  94478 |                                 |
|  2 | DERIVED     | adspace      | ALL    | NULL          | NULL    | NULL    | NULL                                    | 101454 | Using where                     |
|  2 | DERIVED     | slots        | eq_ref | PRIMARY       | PRIMARY | 4       | db.adspace.slots_id         |      1 |                                             |
+----+-------------+--------------+--------+---------------+---------+---------+-----------------------------------------+--------+---------------------------------+

新服务器 (MariaDB 5.5)

+------+-------------+--------------+--------+---------------+---------+---------+-----------------------------------------+--------+---------------------------------+
| id   | select_type | table        | type   | possible_keys | key     | key_len | ref                                     | rows   | Extra                           |
+------+-------------+--------------+--------+---------------+---------+---------+-----------------------------------------+--------+---------------------------------+
|    1 | SIMPLE      | products     | ALL    | test_idx      | NULL    | NULL    | NULL                                    |   6557 | Using temporary; Using filesort |
|    1 | SIMPLE      | publications | eq_ref | PRIMARY       | PRIMARY | 4       | db.products.publications_id |      1 | Using where                                 |
|    1 | SIMPLE      | adspace      | ALL    | NULL          | NULL    | NULL    | NULL                                    | 100938 | Using where                     |
|    1 | SIMPLE      | slots        | eq_ref | PRIMARY       | PRIMARY | 4       | db.adspace.slots_id         |      1 | Using where                                 |
+------+-------------+--------------+--------+---------------+---------+---------+-----------------------------------------+--------+---------------------------------+

在新服务器上的产品表中添加了索引以加快速度，但无济于事。

引擎变量:

旧服务器:

mysql> show variables like '%engine%';
+---------------------------+--------+
| Variable_name             | Value  |
+---------------------------+--------+
| engine_condition_pushdown | ON     |
| storage_engine            | MyISAM |
+---------------------------+--------+

mysql> show variables like '%buffer_pool%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| innodb_buffer_pool_size | 8388608 |
+-------------------------+---------+

新服务器:

MariaDB [db]> show variables like '%engine%';
+---------------------------+--------+
| Variable_name             | Value  |
+---------------------------+--------+
| default_storage_engine    | InnoDB |
| engine_condition_pushdown | OFF    |
| storage_engine            | InnoDB |
+---------------------------+--------+


MariaDB [db]> show variables like '%buffer_pool%';
+---------------------------------------+-----------+
| Variable_name                         | Value     |
+---------------------------------------+-----------+
| innodb_blocking_buffer_pool_restore   | OFF       |
| innodb_buffer_pool_instances          | 1         |
| innodb_buffer_pool_populate           | OFF       |
| innodb_buffer_pool_restore_at_startup | 0         |
| innodb_buffer_pool_shm_checksum       | ON        |
| innodb_buffer_pool_shm_key            | 0         |
| innodb_buffer_pool_size               | 134217728 |
+---------------------------------------+-----------+

查询中使用的所有表都是 MyISAM(旧服务器和新服务器)

分析显示，旧查询在“复制到 tmp 表”中花费了大约 16 秒，而新服务器在此情况下花费了大约 800 秒。

新服务器都有SSD盘存储，旧服务器有普通盘。

编辑:我还有一个 MySQL 5.5 服务器，查询只需要大约 10 秒。就我所见，也具有所有相同的设置。

我试着用表格总结一下:

Location:       Customer                    Own                     Customer
MySQL Type:     MySQL                       MySQL                   MariaDB
Mysql Version:  5.1.56-community-log        5.5.39-1-log (Debian)   5.5.44-MariaDB-log
HDD:            Normal                      Normal                  SSD
Type:           Virtual                     Real                    Virtual
Query time:     ~15s                        ~10s                    ~15min
DB engine:      MyISAM                      InnoDB                  InnoDB
Table Engine:   MyISAM                      MyISAM                  MyISAM

我不想重写查询(虽然它可能需要一些工作)但我想找出两台机器之间的区别，我猜是 MariaDB 中不理想的设置但我找不到它。

Answer 1

从上面的解释可以看出Derived Table Merge Optimization用来。不幸的是，在您的情况下，这意味着不是只对 adspace 进行一次全表扫描，而是完成了一些 ~6k。

一个可能的解决方案是通过发出 set optimizer_switch='derived_merge=off'; 在查询之前禁用优化。向后兼容的替代方法是将 GROUP BY adspace.id, slots.products_id 添加到子查询(如果它不改变结果 - 最安全的是对所有连接表的 PK 进行分组)，禁止合并具有不同的语义。

有一个报告optimizer bug关于这一点 - 您的情况可能会有所帮助。