新MariaDB 10.8.4服务器上MySQL查询运行异常缓慢求助

环境信息 #

• 新服务器：CentOS7 + MariaDB 10.8.4，32GB内存，AMD Ryzen 5 PRO 3600（6核12线程）
• 旧服务器：CentOS7 + MariaDB 5.5.68
• 开发机：2017款MacBook Pro

问题现象 #

同一条查询在开发机耗时0.17秒，旧服务器耗时0.56秒，但新服务器耗时29.8秒。已尝试各类配置调优，甚至直接套用旧服务器配置文件，问题依旧。

服务器配置文件 #

注释行是新服务器默认配置，未注释为旧服务器配置：

[mysqld]
#max_allowed_packet = 512M
#query_cache_size = 32M
#thread_cache_size = 8
#myisam_sort_buffer_size = 64M
#read_rnd_buffer_size = 8M
#read_buffer_size = 2M
#sort_buffer_size = 8M
#table_open_cache = 1512
#key_buffer_size = 384M
#sql_mode=NO_ENGINE_SUBSTITUTION
query_cache_size = 32M
query_cache_type=1
query_cache_limit=1048576
sort_buffer_size = 8M
table_open_cache = 500
key_buffer_size = 32M
max_connections = 500
big-tables
log_error=/var/log/mariadb/mariadb.err

慢查询语句 #

SELECT cornishestabs.id, cornishestabs.name, townid, cornishestabs.postcode, 
        cornishestabs.latitude, cornishestabs.longitude, cornishestabs.image, 
        cornishestabs.web, cornishtowns.Name AS Town, cornishtowns.bloglink, 
        cornishtowns.blogtitle, types.title, 
        SQRT( POW( 69.1 * ( cornishestabs.latitude - '50.16304' ) , 2 ) + POW( 69.1 * ( '-5.3761' - cornishestabs.longitude ) * COS( cornishestabs.latitude / 57.3 ) , 2 ) ) AS distance
FROM cornishestabs
    INNER JOIN cornishtowns ON cornishtowns.ID = cornishestabs.townid
    INNER JOIN types ON types.id = cornishestabs.etype
WHERE cornishestabs.display = 1
AND cornishestabs.latitude > 0
AND cornishestabs.id != '60959'
GROUP BY name
ORDER BY distance LIMIT 10;

EXPLAIN结果对比 #

新服务器 #

+------+-------------+---------------+------+--------------------------------------+--------+---------+--------------------------------+------+------------------------------------+
| id   | select_type | table         | type | possible_keys                        | key    | key_len | ref                            | rows | Extra                              |
+------+-------------+---------------+------+--------------------------------------+--------+---------+--------------------------------+------+------------------------------------+
|    1 | SIMPLE      | cornishtowns  | ALL  | PRIMARY                              | NULL   | NULL    | NULL                           | 1    | Using temporary; Using filesort    |
|    1 | SIMPLE      | types         | ALL  | PRIMARY                              | NULL   | NULL    | NULL                           | 1    | Using join buffer (flat, BNL join) |
|    1 | SIMPLE      | cornishestabs | ref  | PRIMARY,display,type,townid,latitude | townid | 2       | cornish_breaks.cornishtowns.ID | 88   | Using index condition; Using where |
+------+-------------+---------------+------+--------------------------------------+--------+---------+--------------------------------+------+------------------------------------+

旧服务器 #

+------+-------------+---------------+--------+--------------------------------------+---------+---------+--------------------------------------+------+---------------------------------------------------------------------+
| id   | select_type | table         | type   | possible_keys                        | key     | key_len | ref                                  | rows | Extra                                                               |
+------+-------------+---------------+--------+--------------------------------------+---------+---------+--------------------------------------+------+---------------------------------------------------------------------+
|    1 | SIMPLE      | cornishestabs | ref    | PRIMARY,display,type,townid,latitude | display | 1       | const                                | 5083 | Using index condition; Using where; Using temporary; Using filesort |
|    1 | SIMPLE      | cornishtowns  | eq_ref | PRIMARY                              | PRIMARY | 2       | stayin_cornwall.cornishestabs.townid |    1 |                                                                     |
|    1 | SIMPLE      | types         | eq_ref | PRIMARY                              | PRIMARY | 4       | stayin_cornwall.cornishestabs.etype  |    1 | Using where                                                         |
+------+-------------+---------------+--------+--------------------------------------+---------+---------+--------------------------------------+------+---------------------------------------------------------------------+

表结构对比 #

新服务器表结构 #

SHOW CREATE TABLE cornishestabs
| cornishestabs | CREATE TABLE `cornishestabs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `associd` varchar(16) DEFAULT NULL,
  `name` varchar(96) NOT NULL DEFAULT '',
  `address` text NOT NULL,
  `townid` smallint(4) unsigned NOT NULL,
  `postcode` varchar(34) NOT NULL DEFAULT '',
  `descr` text NOT NULL,
  `ourdescr` text DEFAULT NULL,
  `rating` varchar(96) NOT NULL DEFAULT '',
  `etype` smallint(5) unsigned NOT NULL DEFAULT 0,
  `etype2` smallint(5) unsigned NOT NULL DEFAULT 0,
  `etype3` smallint(5) unsigned NOT NULL DEFAULT 0,
  `etype4` smallint(5) unsigned NOT NULL,
  `tel` varchar(64) NOT NULL DEFAULT '',
  `email` varchar(76) NOT NULL DEFAULT '',
  `web` varchar(255) NOT NULL,
  `online` varchar(255) NOT NULL DEFAULT '',
  `assoc` smallint(5) unsigned NOT NULL DEFAULT 0,
  `date` date DEFAULT NULL,
  `image` varchar(255) NOT NULL,
  `moreimgs` text NOT NULL,
  `latitude` decimal(18,15) DEFAULT NULL,
  `longitude` decimal(18,15) DEFAULT NULL,
  `northing` int(11) unsigned DEFAULT NULL,
  `easting` int(11) unsigned DEFAULT NULL,
  `sleeps` tinyint(2) unsigned NOT NULL DEFAULT 0,
  `pets` tinyint(1) unsigned NOT NULL DEFAULT 0,
  `price` varchar(16) NOT NULL DEFAULT '0',
  `facilities` text NOT NULL,
  `brochure` tinyint(1) unsigned NOT NULL DEFAULT 0,
  `display` tinyint(1) unsigned NOT NULL DEFAULT 0,
  `reviewnumber` tinyint(3) unsigned NOT NULL,
  `reviews` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `display` (`display`),
  KEY `type` (`etype`),
  KEY `etype2` (`etype2`),
  KEY `etype3` (`etype3`),
  KEY `etype4` (`etype4`),
  KEY `associd` (`associd`),
  KEY `postcode` (`postcode`),
  KEY `assoc` (`assoc`),
  KEY `townid` (`townid`),
  KEY `latitude` (`latitude`),
  KEY `longitude` (`longitude`)
) ENGINE=InnoDB AUTO_INCREMENT=73642 DEFAULT CHARSET=latin1 

 cornishtowns | CREATE TABLE `cornishtowns` (
  `ID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) DEFAULT NULL,
  `Description` text DEFAULT NULL,
  `telcode` varchar(64) NOT NULL,
  `postcode` varchar(10) NOT NULL,
  `population` varchar(8) DEFAULT NULL,
  `latitude` decimal(18,15) DEFAULT NULL,
  `longitude` decimal(18,15) DEFAULT NULL,
  `northing` int(11) unsigned DEFAULT NULL,
  `easting` int(11) unsigned DEFAULT NULL,
  `NationalPark` tinyint(2) unsigned NOT NULL,
  `village` tinyint(1) unsigned NOT NULL DEFAULT 0,
  `area` tinyint(1) unsigned NOT NULL,
  `bloglink` varchar(96) NOT NULL,
  `blogtitle` varchar(48) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `Name` (`Name`),
  KEY `NationalPark` (`NationalPark`),
  KEY `northing` (`northing`),
  KEY `easting` (`easting`),
  KEY `area` (`area`)
) ENGINE=InnoDB AUTO_INCREMENT=4675 DEFAULT CHARSET=latin1 

types | CREATE TABLE `types` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `link` varchar(32) NOT NULL DEFAULT '',
  `title` varchar(96) NOT NULL DEFAULT '',
  `typedescription` text NOT NULL,
  `shortname` varchar(24) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1 

旧服务器表结构 #

cornishestabs | CREATE TABLE `cornishestabs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `associd` varchar(16) DEFAULT NULL,
  `name` varchar(96) NOT NULL DEFAULT '',
  `address` text NOT NULL,
  `townid` smallint(4) unsigned NOT NULL,
  `postcode` varchar(34) NOT NULL DEFAULT '',
  `descr` text NOT NULL,
  `ourdescr` text,
  `rating` varchar(96) NOT NULL DEFAULT '',
  `etype` smallint(5) unsigned NOT NULL DEFAULT '0',
  `etype2` smallint(5) unsigned NOT NULL DEFAULT '0',
  `etype3` smallint(5) unsigned NOT NULL DEFAULT '0',
  `etype4` smallint(5) unsigned NOT NULL,
  `tel` varchar(64) NOT NULL DEFAULT '',
  `email` varchar(76) NOT NULL DEFAULT '',
  `web` varchar(255) NOT NULL,
  `online` varchar(255) NOT NULL DEFAULT '',
  `assoc` smallint(5) unsigned NOT NULL DEFAULT '0',
  `date` date DEFAULT NULL,
  `image` varchar(255) NOT NULL,
  `moreimgs` text NOT NULL,
  `latitude` decimal(18,15) DEFAULT NULL,
  `longitude` decimal(18,15) DEFAULT NULL,
  `northing` int(11) unsigned DEFAULT NULL,
  `easting` int(11) unsigned DEFAULT NULL,
  `sleeps` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `pets` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `price` varchar(16) NOT NULL DEFAULT '0',
  `facilities` text NOT NULL,
  `brochure` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `display` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `reviewnumber` tinyint(3) unsigned NOT NULL,
  `reviews` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `display` (`display`),
  KEY `type` (`etype`),
  KEY `etype2` (`etype2`),
  KEY `etype3` (`etype3`),
  KEY `etype4` (`etype4`),
  KEY `associd` (`associd`),
  KEY `postcode` (`postcode`),
  KEY `assoc` (`assoc`),
  KEY `townid` (`townid`),
  KEY `latitude` (`latitude`),
  KEY `longitude` (`longitude`)
) ENGINE=InnoDB AUTO_INCREMENT=73642 DEFAULT CHARSET=latin1

cornishtowns | CREATE TABLE `cornishtowns` (
  `ID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) DEFAULT NULL,
  `Description` text,
  `telcode` varchar(64) NOT NULL,
  `postcode` varchar(10) NOT NULL,
  `population` varchar(8) DEFAULT NULL,
  `latitude` decimal(18,15) DEFAULT NULL,
  `longitude` decimal(18,15) DEFAULT NULL,
  `northing` int(11) unsigned DEFAULT NULL,
  `easting` int(11) unsigned DEFAULT NULL,
  `NationalPark` tinyint(2) unsigned NOT NULL,
  `village` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `area` tinyint(1) unsigned NOT NULL,
  `bloglink` varchar(96) NOT NULL,
  `blogtitle` varchar(48) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `Name` (`Name`),
  KEY `NationalPark` (`NationalPark`),
  KEY `northing` (`northing`),
  KEY `easting` (`easting`),
  KEY `area` (`area`)
) ENGINE=InnoDB AUTO_INCREMENT=4675 DEFAULT CHARSET=latin1 

types | CREATE TABLE `types` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `link` varchar(32) NOT NULL DEFAULT '',
  `title` varchar(96) NOT NULL DEFAULT '',
  `typedescription` text NOT NULL,
  `shortname` varchar(24) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1

内容的提问来源于stack exchange，提问作者JIm Rolt