MySQL 8查询性能较MySQL 5.7慢10倍，求助排查原因

在Windows本地和Linux生产服务器上，相同应用实例分别连接MySQL 5.7.28和8.0.16，发现所有表的SELECT查询在MySQL 8中耗时约为5.7的10倍，但两者执行计划完全一致。

测试示例 #

简单全表查询对比 #

查询语句：select * from available_times

• MySQL 5.7耗时：0.007s
• MySQL 8耗时：0.065s

表结构与数据 #

表available_times为MyISAM引擎，共6927条数据，创建语句：

SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

CREATE TABLE `available_times` (
  `id` int(11) NOT NULL,
  `week_no` int(11) NOT NULL,
  `year` int(11) NOT NULL,
  `monday_start` varchar(5) NOT NULL,
  `monday_end` varchar(5) NOT NULL,
  `tuesday_start` varchar(5) NOT NULL,
  `tuesday_end` varchar(5) NOT NULL,
  `wednesday_start` varchar(5) NOT NULL,
  `wednesday_end` varchar(5) NOT NULL,
  `thursday_start` varchar(5) NOT NULL,
  `thursday_end` varchar(5) NOT NULL,
  `friday_start` varchar(5) NOT NULL,
  `friday_end` varchar(5) NOT NULL,
  `saturday_start` varchar(5) NOT NULL,
  `saturday_end` varchar(5) NOT NULL,
  `sunday_start` varchar(5) NOT NULL,
  `sunday_end` varchar(5) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

示例数据：

INSERT INTO `available_times` (`id`, `week_no`, `year`, `monday_start`, `monday_end`, `tuesday_start`, `tuesday_end`, `wednesday_start`, `wednesday_end`, `thursday_start`, `thursday_end`, `friday_start`, `friday_end`, `saturday_start`, `saturday_end`, `sunday_start`, `sunday_end`) VALUES
(1, 44, 2011,   '9:00', '17:30',    '9:00', '17:30',    '9:00', '17:30',    '9:00', '17:30',    '9:00', '17:30',    '9:00', '18:00',    'None', 'None'),
(1, 45, 2011,   '9:00', '19:00',    '9:00', '19:00',    '9:00', '19:00',    '9:00', '19:00',    '9:00', '19:00',    '9:00', '19:00',    'None', 'None'),
(60,    44, 2011,   'None', 'None', 'None', 'None', '8:30', '18:30',    '10:15',    '20:00',    '8:30', '20:30',    '8:30', '13:00',    'None', 'None'),
(60,    45, 2011,   '10:15',    '20:00',    '8:30', '18:15',    '10:15',    '20:30',    '8:30', '20:30',    '10:00',    '17:00',    '7:15', '14:30',    '10:00',    '12:00'),
(60,    46, 2023,   '11:00',    '17:30',    '11:00',    '17:30',    '11:00',    '17:30',    '11:00',    '17:30',    '11:00',    '17:30',    'None', 'None', 'None', 'None');

配置文件对比 #

MySQL 5.7配置 #

[mysqld]
{{#unless os.windows}}
skip-name-resolve
{{/unless}}

datadir = {{datadir}}
port = {{port}}
bind-address = {{bindAddress}}
socket = {{socket}}

{{#if os.windows}}
console
{{/if}}

# Fine Tuning
performance_schema = off
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8

# InnoDB
innodb_buffer_pool_size = 32M
innodb_log_file_size = 96M

[client]
{{#unless os.windows}}
socket = {{socket}}
{{else}}
host = {{clientAddress}}
port = {{port}}
{{/unless}}
user = root
password = root

MySQL 8配置 #

[mysqld]
{{#unless os.windows}}
skip-name-resolve
{{/unless}}

{{#if disableMysqlx}}
mysqlx = 0
{{/if}}

datadir = {{datadir}}
port = {{port}}
bind-address = {{bindAddress}}
socket = {{socket}}

{{#if os.windows}}
console
{{/if}}

# Older PHP/client compatibility
character-set-server = utf8mb3
default_authentication_plugin = mysql_native_password

# Fine Tuning
performance_schema = off
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8

# InnoDB
innodb_buffer_pool_size = 32M
innodb_log_file_size = 96M

[client]
{{#unless os.windows}}
socket = {{socket}}
{{else}}
host = {{clientAddress}}
port = {{port}}
{{/unless}}
user = root
password = root

执行计划对比 #

全表查询执行计划（两个版本一致） #

id,select_type,table,partitions,type,possible_keys,key,key_len,ref,rows,filtered,Extra
1,SIMPLE,available_times,,ALL,,,,,6927,"100.00",

带条件排序查询执行计划 #

查询语句：SELECT * FROM lessons WHERE student_id = 6001 AND ((status = 1 AND date_stamp < 1698233040) OR status = 2) ORDER BY date_stamp

MySQL 8执行计划

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "10807.28"
    },
    "ordering_operation": {
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "1188.85"
      },
      "table": {
        "table_name": "lessons",
        "access_type": "ALL",
        "rows_examined_per_scan": 91452,
        "rows_produced_per_join": 1188,
        "filtered": "1.30",
        "cost_info": {
          "read_cost": "9499.54",
          "eval_cost": "118.88",
          "prefix_cost": "9618.43",
          "data_read_per_join": "222K"
        },
        "used_columns": [
          "id",
          "student_id",
          "instructor_id",
          "start_time",
          "duration",
          "type",
          "day",
          "month",
          "year",
          "notes",
          "date_stamp",
          "approved",
          "status",
          "payment_status",
          "payment_type",
          "last_minute",
          "price",
          "created_date"
        ],
        "attached_condition": "((`local`.`lessons`.`student_id` = 6001) and (((`local`.`lessons`.`status` = 1) and (`local`.`lessons`.`date_stamp` < 1698233040)) or (`local`.`lessons`.`status` = 2)))"
      }
    }
  }
}

MySQL 5.7执行计划

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "21372.16"
    },
    "ordering_operation": {
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "1188.85"
      },
      "table": {
        "table_name": "lessons",
        "access_type": "ALL",
        "rows_examined_per_scan": 91452,
        "rows_produced_per_join": 1188,
        "filtered": "1.30",
        "cost_info": {
          "read_cost": "19945.54",
          "eval_cost": "237.77",
          "prefix_cost": "20183.31",
          "data_read_per_join": "222K"
        },
        "used_columns": [
          "id",
          "student_id",
          "instructor_id",
          "start_time",
          "duration",
          "type",
          "day",
          "month",
          "year",
          "notes",
          "date_stamp",
          "approved",
          "status",
          "payment_status",
          "payment_type",
          "last_minute",
          "price",
          "created_date"
        ],
        "attached_condition": "((`local`.`lessons`.`student_id` = 6001) and (((`local`.`lessons`.`status` = 1) and (`local`.`lessons`.`date_stamp` < 1698233040)) or (`local`.`lessons`.`status` = 2)))"
      }
    }
  }
}

排查方向建议 #

• MyISAM引擎优先级：MySQL 8对MyISAM的优化远低于InnoDB，建议将表转换为InnoDB测试性能变化。
• 字符集处理差异：检查连接字符集是否完全一致，MySQL 8默认字符集为utf8mb4，即便配置了utf8mb3，仍可能存在隐式转换或额外校验开销。
• 查询缓存影响：MySQL 8已移除查询缓存，若5.7未禁用该功能，重复查询会因缓存导致耗时差异，可在5.7中执行SET GLOBAL query_cache_type=0;后重新测试。
• IO配置差异：对比read_buffer_size、read_rnd_buffer_size等IO相关参数，MySQL 8可能调整了默认值，降低了全表扫描的缓存效率。
• 版本bug修复：8.0.16属于早期版本，存在已知性能bug，建议升级到8.0最新稳定版本（如8.0.36）后验证问题是否解决。

内容的提问来源于stack exchange，提问作者alex rees