logo标签列表

MySql 8 中的 SQL 查询速度比 MySql 5.6 慢得多。

mysqlquery-optimizationmysql-8.0mysql-5.6
4

我有一个简单的查询：

select  count(*)
    from  ror
    where  rorsts not in ( 'RECD', 'CANC', 'CCNS', 'SDNY' )
      and  ( ( rorbcd = '00009310022487'
                      and  rorfid = 'VDR' )
              or  ( rorfid = 'VDR'
                      and  rorbcd in (
                        SELECT  pplbcd
                            from  ppl
                            where  pplfid = 'VDR'
                              and  pplscb6 = '00009310022487'
                              and  pplsflg = 'Y'
                              and  pplsku = '0332690-008'
                              and  ppldoc = '73'
                              and  pplsca9 = ''
                              and  pplven = '10112' ) ) 
           )

在一台 MySql 5.6 的机器上，它运行得非常快：
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.01 sec)

但在我的 MySql 8 服务器上，它运行得慢得多：

+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.90 sec)

我主要使用默认参数来配置两个服务器（亚马逊RDS默认设置）。是哪个参数导致了这种减速？有什么配置可以帮助在MySql 8中以大致相同的速度运行，就像在MySql 5.6中一样。
根据许多其他在线资源，这是一个糟糕的SQL语句。我相信我可以轻松地将其重写为一个"exists"语句，这应该更加高效。我的问题是，我们正在从5.6迁移到8.0，很有可能我会错过一个查询，或者由于某些奇怪的要求而无法修复其中的一个查询。我敢打赌，在我的MySql 8配置中，有一些参数可以调整，使其以大致与5.6相当的方式运行。这就是我所寻找的。
更多信息：
  1. 硬件方面，我们使用了同样的Amazon RDS服务器设置。
  2. 我们刚将版本从5.6升级到了5.7，然后又升级到了8.0。尽可能地，我保持了默认的MySql参数设置。我只有故意更改了lower_case_table_names = 1和max_connections=300以匹配5.6和我们当前的需求。
  3. PPL和ROR表非常复杂。PPL表有137列，ROR表有144列。

对于MySql 5.6的解释，这里提供一个更简单（但仍然更快）的查询，并附带一些索引信息：

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.51-log |
+------------+
1 row in set (0.01 sec)

mysql> explain extended select count(*) from ror where rorsts not in ( 'RECD', 'CANC', 'CCNS', 'SDNY' )  and  ( ( rorbcd = '00009310022487' and rorfid = 'VDR'  ) or ( rorfid = 'VDR' and rorbcd in ( select pplbcd from ppl where pplfid = 'VDR' and pplscb6 = '00009310022487' and pplsflg = 'Y' and pplsku = '0332690-008' and ppldoc = '73' and pplsca9 = '' and pplven = '10112'  ) ) );
+----+--------------------+-------+-----------------+----------------------------------------------------------------+--------------+---------+------------+------+----------+------------------------------------+
| id | select_type        | table | type            | possible_keys                                                  | key          | key_len | ref        | rows | filtered | Extra                              |
+----+--------------------+-------+-----------------+----------------------------------------------------------------+--------------+---------+------------+------+----------+------------------------------------+
|  1 | PRIMARY            | ror   | ref             | ROR_RORBCDK,ROR_RORSTSK,ROR_RORBSTSK,ROR_RORSDT3K,ROR_RORGMOQK | ROR_RORGMOQK | 5       | const      |    1 |   100.00 | Using index condition; Using where |
|  2 | DEPENDENT SUBQUERY | ppl   | unique_subquery | PPL_PPLBCDC,PPL_PPLDOCK,PPL_PPLVENC,PPL_PPLSKUC,PPL_PPLFID     | PPL_PPLBCDC  | 29      | func,const |    1 |   100.00 | Using where                        |
+----+--------------------+-------+-----------------+----------------------------------------------------------------+--------------+---------+------------+------+----------+------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings;                                                                                                                                                                                                                         +-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select count(0) AS `count(*)` from `celltreat`.`ror` where ((`celltreat`.`ror`.`RORSTS` not in ('RECD','CANC','CCNS','SDNY')) and (((`celltreat`.`ror`.`RORFID` = 'VDR') and (`celltreat`.`ror`.`RORBCD` = '00009310022487')) or ((`celltreat`.`ror`.`RORFID` = 'VDR') and <in_optimizer>(`celltreat`.`ror`.`RORBCD`,<exists>(<primary_index_lookup>(<cache>(`celltreat`.`ror`.`RORBCD`) in ppl on PPL_PPLBCDC where ((`celltreat`.`ppl`.`PPLVEN` = '10112') and (`celltreat`.`ppl`.`PPLSCA9` = '') and (`celltreat`.`ppl`.`PPLDOC` = '73') and (`celltreat`.`ppl`.`PPLSKU` = '0332690-008') and (`celltreat`.`ppl`.`PPLSFLG` = 'Y') and (`celltreat`.`ppl`.`PPLSCB6` = '00009310022487') and (`celltreat`.`ppl`.`PPLFID` = 'VDR') and (<cache>(`celltreat`.`ror`.`RORBCD`) = `celltreat`.`ppl`.`PPLBCD`)))))))) |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> select count(*) from ppl;
+----------+
| count(*) |
+----------+
|    95108 |
+----------+
1 row in set (0.01 sec)

mysql> explain extended select count(*) from ppl;
+----+-------------+-------+-------+---------------+------------+---------+------+-------+----------+-------------+
| id | select_type | table | type  | possible_keys | key        | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+-------+---------------+------------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | ppl   | index | NULL          | PPL_PPLFID | 5       | NULL | 11194 |   100.00 | Using index |
+----+-------------+-------+-------+---------------+------------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+---------------------------------------------------------------------+
| Level | Code | Message                                                             |
+-------+------+---------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select count(0) AS `count(*)` from `celltreat`.`ppl` |
+-------+------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT TABLE_NAME,  index_name, COUNT(1) column_count FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'veeder' and table_name in ( 'ppl', 'ror' ) group by table_name, index_name order by table_name collate utf8_general_ci, index_name collate utf8_general_ci;
+------------+--------------+--------------+
| TABLE_NAME | index_name   | column_count |
+------------+--------------+--------------+
| ppl        | PPLKEY       |            1 |
| ppl        | PPL_PPLBCDC  |            2 |
| ppl        | PPL_PPLDOCK  |            3 |
| ppl        | PPL_PPLFID   |            1 |
| ppl        | PPL_PPLPONOC |            4 |
| ppl        | PPL_PPLSKUC  |            4 |
| ppl        | PPL_PPLVENC  |            5 |
| ror        | RORKEY       |            1 |
| ror        | ROR_RORBCDK  |            5 |
| ror        | ROR_RORBSTSK |            4 |
| ror        | ROR_RORCNSK  |            3 |
| ror        | ROR_RORCRDTK |            4 |
| ror        | ROR_RORCUIDK |            5 |
| ror        | ROR_RORDOCK  |            5 |
| ror        | ROR_RORGMOQK |            4 |
| ror        | ROR_RORPONOK |            6 |
| ror        | ROR_RORRORK  |            2 |
| ror        | ROR_RORSDT3K |            4 |
| ror        | ROR_RORSKUK  |            5 |
| ror        | ROR_RORSTSK  |            5 |
| ror        | ROR_RORUSTSK |            4 |
| ror        | ROR_RORVENK  |            7 |
| ror        | ROR_RORVSTSK |            4 |
+------------+--------------+--------------+
23 rows in set (0.00 sec)

现在，MySQL 8 的解释，使用一个更简单（但仍然较慢）的查询和一些索引信息：

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.23    |
+-----------+
1 row in set (0.00 sec)

mysql> explain select count(*) from ror where rorsts not in ( 'RECD', 'CANC', 'CCNS', 'SDNY' )  and  ( ( rorbcd = '00009310022487' and rorfid = 'VDR'  ) or ( rorfid = 'VDR' and rorbcd in ( select pplbcd from ppl where pplfid = 'VDR' and pplscb6 = '00009310022487' and pplsflg = 'Y' and pplsku = '0332690-008' and ppldoc = '73' and pplsca9 = '' and pplven = '10112'  ) ) );
+----+-------------+-------+------------+------+----------------------------------------------------------------+--------------+---------+-------------------------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys                                                  | key          | key_len | ref                     | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+----------------------------------------------------------------+--------------+---------+-------------------------+--------+----------+-------------+
|  1 | PRIMARY     | ror   | NULL       | ref  | ROR_RORBCDK,ROR_RORSTSK,ROR_RORBSTSK,ROR_RORSDT3K,ROR_RORGMOQK | ROR_RORGMOQK | 5       | const                   | 185179 |    50.03 | Using where |
|  2 | SUBQUERY    | ppl   | NULL       | ref  | PPL_PPLBCDC,PPL_PPLDOCK,PPL_PPLVENC,PPL_PPLSKUC,PPL_PPLFID     | PPL_PPLVENC  | 53      | const,const,const,const |      2 |     2.50 | Using where |
+----+-------------+-------+------------+------+----------------------------------------------------------------+--------------+---------+-------------------------+--------+----------+-------------+
2 rows in set, 1 warning (0.02 sec)

mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select count(0) AS `count(*)` from `veeder`.`ror` where ((`veeder`.`ror`.`RORSTS` not in ('RECD','CANC','CCNS','SDNY')) and (((`veeder`.`ror`.`RORFID` = 'VDR') and (`veeder`.`ror`.`RORBCD` = '00009310022487')) or ((`veeder`.`ror`.`RORFID` = 'VDR') and <in_optimizer>(`veeder`.`ror`.`RORBCD`,`veeder`.`ror`.`RORBCD` in ( <materialize> (/* select#2 */ select `veeder`.`ppl`.`PPLBCD` from `veeder`.`ppl` where ((`veeder`.`ppl`.`PPLVEN` = '10112') and (`veeder`.`ppl`.`PPLSCA9` = '') and (`veeder`.`ppl`.`PPLDOC` = '73') and (`veeder`.`ppl`.`PPLSKU` = '0332690-008') and (`veeder`.`ppl`.`PPLSFLG` = 'Y') and (`veeder`.`ppl`.`PPLSCB6` = '00009310022487') and (`veeder`.`ppl`.`PPLFID` = 'VDR')) ), <primary_index_lookup>(`veeder`.`ror`.`RORBCD` in <temporary table> on <auto_distinct_key> where ((`veeder`.`ror`.`RORBCD` = `<materialized_subquery>`.`pplbcd`)))))))) |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> select count(*) from ppl;
+----------+
| count(*) |
+----------+
|    94657 |
+----------+
1 row in set (0.49 sec)

mysql> explain select count(*) from ppl;
+----+-------------+-------+------------+-------+---------------+------------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | ppl   | NULL       | index | NULL          | PPL_PPLFID | 5       | NULL | 89284 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+------------------------------------------------------------------+
| Level | Code | Message                                                          |
+-------+------+------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select count(0) AS `count(*)` from `veeder`.`ppl` |
+-------+------+------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT TABLE_NAME,  index_name, COUNT(1) column_count FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'veeder' and table_name in ( 'ppl', 'ror' ) group by table_name, index_name order by table_name collate utf8_general_ci, index_name collate utf8_general_ci;
+------------+--------------+--------------+
| TABLE_NAME | INDEX_NAME   | column_count |
+------------+--------------+--------------+
| ppl        | PPLKEY       |            1 |
| ppl        | PPL_PPLBCDC  |            2 |
| ppl        | PPL_PPLDOCK  |            3 |
| ppl        | PPL_PPLFID   |            1 |
| ppl        | PPL_PPLPONOC |            4 |
| ppl        | PPL_PPLSKUC  |            4 |
| ppl        | PPL_PPLVENC  |            5 |
| ror        | RORKEY       |            1 |
| ror        | ROR_RORBCDK  |            5 |
| ror        | ROR_RORBSTSK |            4 |
| ror        | ROR_RORCNSK  |            3 |
| ror        | ROR_RORCRDTK |            4 |
| ror        | ROR_RORCUIDK |            5 |
| ror        | ROR_RORDOCK  |            5 |
| ror        | ROR_RORGMOQK |            4 |
| ror        | ROR_RORPONOK |            6 |
| ror        | ROR_RORRORK  |            2 |
| ror        | ROR_RORSDT3K |            4 |
| ror        | ROR_RORSKUK  |            5 |
| ror        | ROR_RORSTSK  |            5 |
| ror        | ROR_RORUSTSK |            4 |
| ror        | ROR_RORVENK  |            7 |
| ror        | ROR_RORVSTSK |            4 |
+------------+--------------+--------------+
23 rows in set (0.01 sec)

我不理解MySql 8给出的警告，但也许这是找到正确服务器设置的关键。
表定义和索引有点长，特别是对于两个数据库，但我有它们的副本，它们看起来几乎相同。最大的区别似乎是在5.6中，我将一些整数列定义为"int(11)"，而在8.0中，它们被定义为"int"。在"show create table"输出中，索引是相同的。显然，"AUTO_INCREMENT"值是不同的，但这很有道理，因为MySql 8版本已经过时了一周。希望这提供了足够的信息。当我在两个数据库服务器上执行"describe ppl"、"show indexes from ppl"、"describe ror"和"show indexes from ror"时，它们几乎相同，除了以下几点：
  1. 如上所述，int(11) 更改为 int。
  2. "Cardinality" 列发生变化。MySQL 8 版本的值较小，可能是因为它已经过时了。
  3. 显然，8.0 版本的 "show indexes" 命令显示更多列。"Visible" 列始终为 "YES"，"Expression" 列始终为 "NULL"。顺便说一下，所有索引的 INDEX_TYPE 都是 "BTREE"。

由于这些是相当大的表，show create table 和 describe <table>;show indexes from <table> 的结果太长了，无法放在帖子正文中。因此，如果您需要更多信息，我需要知道您不需要当前提供的哪些内容。谢谢。

编辑：

应用@o-jones的建议后进行编辑: 以下结果是在我运行ANALYZE TABLE ror, ppl;之后得出的，但它们与我在运行ANALYZE TABLE ror, ppl;之前的情况相同。

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.23    |
+-----------+
1 row in set (0.00 sec)

mysql> explain select count(*) from ror where rorsts not in ( 'RECD', 'CANC', 'CCNS', 'SDNY' )  and  ( ( rorbcd = '00009310022487' and rorfid = 'VDR'  ) or ( rorfid = 'VDR' and rorbcd in ( select pplbcd from ppl where pplfid = 'VDR' and '00009310022487' and pplsflg = 'Y' and pplsku = '0332690-008' and ppldoc = '73' and pplsca9 = '' and pplven = '10112'  ) ) );
+----+-------------+-------+------------+------+----------------------------------------------------------------+--------------+---------+-------------------------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys                                                  | key          | key_len | ref                     | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+----------------------------------------------------------------+--------------+---------+-------------------------+--------+----------+-------------+
|  1 | PRIMARY     | ror   | NULL       | ref  | ROR_RORBCDK,ROR_RORSTSK,ROR_RORBSTSK,ROR_RORSDT3K,ROR_RORGMOQK | ROR_RORGMOQK | 5       | const                   | 185179 |    50.03 | Using where |
|  2 | SUBQUERY    | ppl   | NULL       | ref  | PPL_PPLBCDC,PPL_PPLDOCK,PPL_PPLVENC,PPL_PPLSKUC,PPL_PPLFID     | PPL_PPLVENC  | 53      | const,const,const,const |      2 |     2.50 | Using where |
+----+-------------+-------+------------+------+----------------------------------------------------------------+--------------+---------+-------------------------+--------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)

mysql> select count(*) from ror where rorsts not in ( 'RECD', 'CANC', 'CCNS', 'SDNY' )  and  ( ( rorbcd = '00009310022487' and rorfid = 'VDR'  ) or ( rorfid = 'VDR' and rorbcd in ( select pplbcd from ppl where pplfid = 'VDR' and pplscb610022487' and pplsflg = 'Y' and pplsku = '0332690-008' and ppldoc = '73' and pplsca9 = '' and pplven = '10112'  ) ) );
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.80 sec)

mysql> explain select count(*) from ror USE INDEX(ROR_RORSTSK) where rorsts not in ( 'RECD', 'CANC', 'CCNS', 'SDNY' )  and  ( ( rorbcd = '00009310022487' and rorfid = 'VDR'  ) or ( rorfid = 'VDR' and rorbcd in ( select pplbcd from ppl wd = 'VDR' and pplscb6 = '00009310022487' and pplsflg = 'Y' and pplsku = '0332690-008' and ppldoc = '73' and pplsca9 = '' and pplven = '10112'  ) ) );
+----+-------------+-------+------------+------+------------------------------------------------------------+-------------+---------+-------------------------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys                                              | key         | key_len | ref                     | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+------------------------------------------------------------+-------------+---------+-------------------------+--------+----------+-------------+
|  1 | PRIMARY     | ror   | NULL       | ALL  | ROR_RORSTSK                                                | NULL        | NULL    | NULL                    | 370358 |    50.03 | Using where |
|  2 | SUBQUERY    | ppl   | NULL       | ref  | PPL_PPLBCDC,PPL_PPLDOCK,PPL_PPLVENC,PPL_PPLSKUC,PPL_PPLFID | PPL_PPLVENC | 53      | const,const,const,const |      2 |     2.50 | Using where |
+----+-------------+-------+------------+------+------------------------------------------------------------+-------------+---------+-------------------------+--------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql> select count(*) from ror USE INDEX(ROR_RORSTSK) where rorsts not in ( 'RECD', 'CANC', 'CCNS', 'SDNY' )  and  ( ( rorbcd = '00009310022487' and rorfid = 'VDR'  ) or ( rorfid = 'VDR' and rorbcd in ( select pplbcd from ppl where ppl' and pplscb6 = '00009310022487' and pplsflg = 'Y' and pplsku = '0332690-008' and ppldoc = '73' and pplsca9 = '' and pplven = '10112'  ) ) );
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.25 sec)

mysql> explain select count(*) from ror where rorsts not in ( 'RECD', 'CANC', 'CCNS', 'SDNY' )  and  ( ( rorbcd = '00009310022487' and rorfid = 'VDR'  ) );+----+-------------+-------+------------+-------+----------------------------------------------------------------+--------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys                                                  | key          | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+----------------------------------------------------------------+--------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | ror   | NULL       | range | ROR_RORBCDK,ROR_RORSTSK,ROR_RORBSTSK,ROR_RORSDT3K,ROR_RORGMOQK | ROR_RORBSTSK | 35      | NULL |   37 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+----------------------------------------------------------------+--------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select count(*) from ror where rorsts not in ( 'RECD', 'CANC', 'CCNS', 'SDNY' )  and  ( ( rorbcd = '00009310022487' and rorfid = 'VDR'  ) );
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

顺便说一句，在这种情况下使用索引提示不是可接受的解决方案，因为这只是一个SQL的示例。我一直知道我可以使这个特定的SQL更有效率。我的问题是，MySql 8应该更快，但对于相同的SQL来说却明显较慢，所以我试图找出原因。我怀疑在MySql 5.7中也会发生同样的事情，但我还没有尝试过，因为我听说MySql 8是正确的选择。

2
请阅读此内容，然后[编辑]您的问题以提供更多信息，包括您的表定义和MySql两个版本中EXPLAIN的输出。 - O. Jones
1
我只是想知道为什么完全相同的SQL会有不同的表现。我知道我可以在很多方面改进这个特定的SQL。难道没有影响它的配置吗？ - Tony B
2
我们无法告诉您为什么相同的SQL语句在两个MySQL版本中运行不同，除非看到您的数据结构和两个版本中explain的输出。相信我，在MySQL v8中没有神奇的设置会突然导致查询变慢或变快！ - Shadow
2
v8查询规划器与v5.6中的规划器非常不同。此外，您可能已更改存储引擎，甚至可能更改了索引。如果您的问题的答案是“将framis变量设置为42”，那就太好了，但很遗憾事情并不那么简单。 - O. Jones
当我尝试添加"show create table"或索引列表时，它不允许我这样做，说我超过了最大字符数。如果有人能帮我知道该删除什么，那么我就可以重新组织我的帖子，摆脱我不需要的内容，并添加我需要的内容。 - Tony B
显示剩余11条评论
1个回答
4

针对主查询计划（使用ror表）进行比较。

version  type   key        key_len  ref     rows Filtered Extra
 5.6.61 range  ROR_RORSTSK       6  NULL    3256    ----  Using index condition; Using where 
 8.0.23 ref    ROR_RORGMOQK      5  const 185179    50.03 Using where

由于某些原因，8.0.23查询优化器没有选择ROR_RORSTSK索引来满足您的查询，而是选择了ROR_RORGMOQK。因此，查询无法使用范围扫描，必须过滤（逐个比较）185k行而不是3k行。这就是您两个计划之间的明显区别。
（5.6 EXPLAIN输出不包括Filtered列。您需要使用EXPLAIN EXTENDED。）
您可以尝试更改查询中的此行。
from ror

to

from ror USE INDEX(ROR_RORSTSK)

或者

from ror IGNORE INDEX(ROR_RORGMOQK)

其中之一可能会使v8使用类似于v5.6的查询计划。

通过您对每个索引包含哪些列的了解，您可能可以找出更多信息。

编辑

如果您刚刚批量加载表作为迁移的一部分，请尝试执行ANALYZE TABLE ror, ppl;。如果ANALYZE不必要，它不会造成任何损害，而且它可能有助于查询规划器选择最佳计划。

基本上，由于某种原因，MySql 8 没有选择正确的索引。我甚至没有注意到它找到了完全不同的索引，这很奇怪。我会进行实验。 - Tony B
其实，后来我发现如果将 explain 改为 explain extended，就可以在 MySql 5.6 中获得过滤列。只是我没有更新原帖。 - Tony B
ANALYZE 什么都没做。我想这可能是在升级过程中发生的，但像你说的，尝试一下也没有伤害。根据 explain，它仍然选择了错误的索引。我将使用这些信息更新主贴，并深入研究 ANALYZE，以确保我没有错过特殊选项。 - Tony B
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接