mysql 5.7 中的查询比 5.1 慢 2 倍或更多 [英] Queries in mysql 5.7 2 or more times slower than in 5.1
问题描述
我们将一个完整的数据库从 MySQL 5.1.63 迁移到另一个(更好一点)服务器到 MySQL 5.7.22.现在大多数查询平均慢了 2 倍.我们还没有对 MySQL 5.1 做太多优化.
We migrated a full database from MySQL 5.1.63 to a different (a bit better) server into MySQL 5.7.22. Now most of the queries are 2 times slower on average. We haven't done too much optimization for 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
这是一个具体的例子:
我想得到所有在课程中被标记为教师的人:
I want to get all the persons who are marked as a teacher in a course:
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.03 秒.后续查询:0.00s
- mysql 5.7:1.27 秒.后续查询:0.80s
相差 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 |
+----+--------------+-------------+------------+--------+--------------------+--------------------+---------+----------------------+--------+----------+-------------+
现在如果我这样写查询:
Now if I write the query like that:
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.03 秒
所以，它好一点，但仍然更慢.
So, it's a bit better, but still slower.
course.teacher_id 有一个索引.
安装之间的区别之一是在 5.7 的服务器中，数据文件夹在另一个驱动器上(SSD，性能比 5.1 服务器中的驱动器好一点).
One of the differences between the installations is that in the server with 5.7, data folder is on another drive (SSD, the performance is a bit better than for the drive in 5.1 server).
任何建议我应该配置什么才能使 5.7 达到与 5.1 相同的速度?可能有些查询也需要重写，但我认为配置似乎是必要的.
Any suggestions what should I configure to get 5.7 to the same speed as 5.1? Probably some of the queries have to be rewritten too, but I think configuration seems necessary.
推荐答案
您可以使用内连接来使用更好的查询优化
You could use the better query otimization using an inner join
SELECT id, TRIM(CONCAT_WS(" ", name, lastname)) AS name
FROM person
INNER JOIN (
SELECT DISTINCT teacher_id FROM course
) t on t.teacher_id = person.id
ORDER BY name;
并确保您在相同的条件下进行测试..相同的数据..并首次执行两个查询
and be sure that you test are do in the same condition .. same data .. and first execution for both query
这篇关于mysql 5.7 中的查询比 5.1 慢 2 倍或更多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
