我们将一个完整的数据库从MySQL 5.1.63迁移到另一个(更好一点)的服务器到MySQL 5.7.22 . 现在大多数查询平均慢了2倍 . 我们没有对MySQL 5.1做太多优化 .
以下是我们更改的配置:
table_open_cache = 4096
tmp_table_size=256M
max_heap_table_size=256M
query_cache_limit = 1000000
query_cache_size = 32000000
innodb_buffer_pool_size = 3200M
innodb_log_buffer_size = 1024M
这是一个具体的例子:
我希望得到所有在课程中被标记为教师的人:
SELECT id, TRIM(CONCAT_WS(" ", name, lastname)) AS name
FROM person
WHERE id IN (SELECT DISTINCT teacher_id FROM course)
ORDER BY name;
运行时间:
-
mysql 5.1:0.03s . 后续查询:0.00s
-
mysql 5.7:1.27s . 后续查询:0.80秒
差异是40倍 .
5.1中的说明:
+----+--------------------+----------+----------------+--------------------+--------------------+---------+------+-------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+----------+----------------+--------------------+--------------------+---------+------+-------+-----------------------------+
| 1 | PRIMARY | person | ALL | NULL | NULL | NULL | NULL | 16293 | Using where; Using filesort |
| 2 | DEPENDENT SUBQUERY | course | index_subquery | teacher_id | teacher_id | 5 | func | 2677 | Using index; Using where |
+----+--------------------+----------+----------------+--------------------+--------------------+---------+------+-------+-----------------------------+
5.7中的说明:
+----+--------------+-------------+------------+--------+--------------------+--------------------+---------+----------------------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+-------------+------------+--------+--------------------+--------------------+---------+----------------------+--------+----------+-------------+
| 1 | SIMPLE | person | NULL | ALL | PRIMARY | NULL | NULL | NULL | 16491 | 100.00 | Using where |
| 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_key> | <auto_key> | 5 | db.person.id | 1 | 100.00 | Using where |
| 2 | MATERIALIZED | course | NULL | index | teacher_id | teacher_id | 5 | NULL | 109741 | 100.00 | Using index |
+----+--------------+-------------+------------+--------+--------------------+--------------------+---------+----------------------+--------+----------+-------------+
现在,如果我写这样的查询:
select distinct person.id, trim(concat_ws(" ", name, lastname)) as name
from person, course
where person.id = course.teacher_id
order by name;
时代将成为:
-
mysql 5.1:0.01s
-
mysql 5.7:0.03s
所以,它有点好,但仍然较慢 .
course.teacher_id
有一个索引 .
安装之间的一个区别是,在5.7的服务器中,数据文件夹位于另一个驱动器上(SSD,性能比5.1服务器中的驱动器好一点) .
有什么建议我应该配置什么来获得5.7到5.1的相同速度?可能还有一些查询需要重写,但我认为配置似乎是必要的 .
1 回答
您可以使用内部联接使用更好的查询otimization
并确保您在相同的条件下进行测试..相同的数据..并且首次执行两个查询