mysql5.5比5.1快吗_Mariadb 5.5比MySQL 5.1慢

我有一个查询在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;

唯一的区别在于解释fase：

旧服务器(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 | | 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秒在这个fase中 .

新服务器都有用于存储的SSD磁盘，旧服务器具有普通磁盘 .

Edit ：我也有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中不理想的设置，但我找不到它 .