时间戳

首页 > 解决方案 > 从 5.6 升级到 5.7 后查询极慢

php - 从 5.6 升级到 5.7 后查询极慢

问题描述

当将 mySQL 服务器从 5.6 升级到 5.7 时，在 vbb5 上运行的论坛似乎变得非常缓慢。查看缓慢的查询文件，似乎行扫描变得比以前慢得多。

下面是一个典型的论坛查询，它在 1 到 3 秒之前运行，现在在 30 到 75 秒之间运行。节点表和闭包表各有 11 到 1300 万条记录。我对两个表都进行了表优化。

# Query_time: 56.239686  Lock_time: 0.000452 Rows_sent: 500  Rows_examined: 672441
SELECT DISTINCT node.starter AS nodeid
                        FROM node as node
                        LEFT JOIN node AS starter ON starter.nodeid = IF(node.starter = 0, node.nodeid, node.starter)
                                JOIN closure AS closure  ON node.nodeid = closure.child
                        WHERE node.contenttypeid <> 30 AND
                                closure.parent = '47' AND
                                node.nodeid <> closure.parent AND
                                node.inlist = 1 AND
                                node.contenttypeid <> '15' AND
                                node.protected <> '1'
AND (node.public_preview = 1 OR  ( ( (  (starter.parentid IN (13,22,1,2,24,27,33,47,49,62,14,25,28,32,50,52,70,15,29,31,40,44,63,69,6,16,37,38,43,46,51,53,17,30,39,54,55,67,12,18,23,26,34,42,45,20,21,41,48,56,61,82,80,72,79,77,78,76,75,74,68,73,3629857,3654649) ) AND node.showapproved > 0 AND node.viewperms > 1 AND node.showpublished > 0 ))
 OR starter.parentid = 9 AND node.userid IN (0)))


                        ORDER BY starter.created DESC,node.nodeid ASC
                        LIMIT 500

我已经使用 my.cnf 设置进行了一些操作，但仍然对性能提升持负面态度：

performance-schema=0
symbolic-links=0
performance-schema=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
default-storage-engine=MyISAM
innodb_file_per_table=1
max_allowed_packet=268435456
open_files_limit=10000
innodb_buffer_pool_size = 256M
#tmp_table_size = 50M
#max_heap_table_size = 40M
join_buffer_size= 256K
query_cache_size = 128M
query_cache_type = 1

任何提示我应该看什么？

标签： phpmysql

解决方案

推荐阅读