mysql 5.7速度慢_mysql – Percona 5.7很多联接速度很慢

我们最近将percona 5.5 sql服务器升级到percona 5.7.到目前为止工作得很好.不幸的是,我们有一个巨大的查询,在5.7下极慢.低于5.5.即使使用sql_no_cache,它也只需不到一秒钟.使用Percona 5.7.执行此查询最多需要1分钟.奇怪的是,随着我们使用的组合指标越多,它变得越慢.删除所有组合指标导致执行时间为30秒.强制sql_straight_join使查询在不到一秒的时间内运行.

所以这是查询：

SELECT t0_.tree_id AS tree_id0, t1_.treetype_name AS treetype_name1, c2_.contentelement_id AS contentelement_id2, t0_.tree_name AS tree_name3, (CASE WHEN t3_.treetype_name <> 'global' THEN t4_.tree_name ELSE t0_.tree_name END) AS sclr4, p5_.picture_id AS picture_id5, t6_.tree_misc_value_text AS tree_misc_value_text6, (CASE WHEN t3_.treetype_name <> 'global' THEN t7_.tree_misc_value_text ELSE t6_.tree_misc_value_text END) AS sclr7, w8_.widgetgeneral_slug AS widgetgeneral_slug8, (CASE WHEN t3_.treetype_name <> 'global' THEN w9_.widgetgeneral_slug ELSE w8_.widgetgeneral_slug END) AS sclr9, t10_.tree_misc_value_text AS tree_misc_value_text10, t11_.tree_misc_value_text AS tree_misc_value_text11

FROM tree_relation t12_

INNER JOIN tree t4_ ON t12_.tree_relation_parent = t4_.tree_id

INNER JOIN treetype t3_ ON t4_.tree_type_id = t3_.treetype_id AND (t3_.treetype_name IN ('global', 'country'))

INNER JOIN contentelement c13_ ON t4_.tree_id = c13_.contentelement_tree_id

INNER JOIN contentleaf c14_ ON c13_.contentelement_contentleaf_id = c14_.contentleaf_id AND (c14_.contentleaf_contentbranch_id = 1)

INNER JOIN widgetgeneral w9_

INNER JOIN widgetabstract w15_ ON w9_.widgetabstract_id = w15_.widgetabstract_id AND (w15_.widgetabstract_contentelement_id = c13_.contentelement_id AND w15_.widgetabstract_discriminator IN ('general') AND w15_.widgetabstract_state = 'preview')

INNER JOIN tree t0_ ON t12_.tree_relation_child = t0_.tree_id

INNER JOIN treetype t1_ ON t0_.tree_type_id = t1_.treetype_id AND (t1_.treetype_name IN ('city','region'))

INNER JOIN contentelement c2_ ON t0_.tree_id = c2_.contentelement_tree_id

INNER JOIN contentleaf c16_ ON c2_.contentelement_contentleaf_id = c16_.contentleaf_id AND (c16_.contentleaf_contentbranch_id = 1)

INNER JOIN widgetgeneral w8_

INNER JOIN widgetabstract w17_ ON w8_.widgetabstract_id = w17_.widgetabstract_id AND (w17_.widgetabstract_contentelement_id = c2_.contentelement_id AND w17_.widgetabstract_discriminator IN ('general') AND w17_.widgetabstract_state = 'preview')

INNER JOIN widgetgeneral w18_

INNER JOIN widgetabstract w19_ ON w18_.widgetabstract_id = w19_.widgetabstract_id AND (w19_.widgetabstract_contentleaf_id = c16_.contentleaf_id AND w19_.widgetabstract_discriminator IN ('general') AND w19_.widgetabstract_state = 'preview')

LEFT JOIN picture p5_ ON t0_.tree_picture_id = p5_.picture_id

LEFT JOIN tree_misc t6_ ON t0_.tree_id = t6_.tree_misc_tree_id AND (t6_.tree_misc_attributetype_key = 'flagId')

LEFT JOIN tree_misc t7_ ON t4_.tree_id = t7_.tree_misc_tree_id AND (t7_.tree_misc_attributetype_key = 'flagId')

LEFT JOIN tree_misc t10_ ON t0_.tree_id = t10_.tree_misc_tree_id AND (t10_.tree_misc_attributetype_key = 'latitude')

LEFT JOIN tree_misc t11_ ON t0_.tree_id = t11_.tree_misc_tree_id AND (t11_.tree_misc_attributetype_key = 'longitude')

WHERE w17_.widgetabstract_visibility = 'active' OR (w17_.widgetabstract_visibility = 'parent' AND w19_.widgetabstract_visibility = 'active')

以及5.7的解释：

我们尝试升级以及完成空白安装.打开和关闭所有sql模式和查询优化器选项.如果您需要更多信息或服务器变量,请告诉我.

os：Debian GNU / Linux 8(jessie)

服务器版本是：5.7.14-7-log Percona Server(GPL),Release’7′,Revision’083e298′

也许你有一个暗示我们缺少的东西.

编辑：

添加配置

[mysqld]

port = 3306

user = mysql

socket = /var/run/mysqld/mysqld.sock

pid-file = /var/run/mysqld/mysqld.pid

basedir = /usr

datadir = /var/lib/mysql

tmpdir = /tmp

lc-messages-dir = /usr/share/mysql

max_connect_errors = 1000000

log-error = /var/log/mysql/error.log

skip-external-locking

myisam-recover-options = BACKUP

character-set-server = utf8

collation-server = utf8_general_ci

interactive_timeout = 28800

wait_timeout = 28800

skip-name-resolve

group_concat_max_len = 268435456

innodb_file_per_table

innodb_buffer_pool_size = 48G

innodb_buffer_pool_instances = 1

innodb_flush_log_at_trx_commit = 1

innodb_data_file_path = ibdata1:2G:autoextend

innodb_log_file_size = 256M

innodb_log_buffer_size = 64M

innodb_file_format = barracuda

innodb_flush_method = O_DIRECT[mysqld_safe]

syslog

numa_interleave

# Per Thread

sort_buffer_size = 4M

read_buffer_size = 2M

# Cache/connection relevant

thread_cache_size = 850

table_open_cache = 4048

max_connections = 1300

# MyISAM settings (also valid for queries with temporary tables)

key_buffer_size = 128M

myisam_sort_buffer_size = 16M

# Misc

max_allowed_packet = 256M

max_heap_table_size = 16M

thread_stack = 192K

tmp_table_size = 16M

# Query cache

query_cache_limit = 5M

query_cache_size = 4024M

server-id = 102

log_bin = /var/log/mysql/mysql-bin.log

binlog_format = mixed

expire_logs_days = 10

max_binlog_size = 100M

# enforce syncing of every transation to binlog (crash safe, with bbu this should be fast)

sync_binlog = 1

sync_relay_log = 1

sync_master_info = 1

sync_relay_log_info = 1

relay-log = mysqld-relay-bin

skip-slave-start

log-slave-updates

slow_query_log = 1

slow_query_log_file = /var/log/mysql/mysql-slow.log

long_query_time = 1

log-queries-not-using-indexes

编辑2：

添加5.5的解释