MySQL 查询性能 - 时间上的巨大差异-6ren

MySQL 查询性能 - 时间上的巨大差异

转载作者：可可西里更新时间：2023-11-01 06:32:10

我有一个查询，它在 2 个数据集之间以截然不同的时间返回。对于一组(数据库 A)，它会在几秒钟内返回，对于另一组(数据库 B)......好吧，我还没有等足够长的时间，但超过了 10 分钟。我已将这两个数据库转储到我的本地机器上，在那里我可以重现运行 MySQL 5.1.37 的问题。

奇怪的是，数据库 B 比数据库 A 小。

重现该问题的精简版查询是:

SELECT * FROM po_shipment ps 
JOIN po_shipment_item psi USING (ship_id) 
JOIN po_alloc pa ON ps.ship_id = pa.ship_id AND pa.UID_items = psi.UID_items 
JOIN po_header ph ON pa.hdr_id = ph.hdr_id 
LEFT JOIN EVENT_TABLE ev0 ON ev0.TABLE_ID1 = ps.ship_id AND ev0.EVENT_TYPE = 'MAS0' 
LEFT JOIN EVENT_TABLE ev1 ON ev1.TABLE_ID1 = ps.ship_id AND ev1.EVENT_TYPE = 'MAS1' 
LEFT JOIN EVENT_TABLE ev2 ON ev2.TABLE_ID1 = ps.ship_id AND ev2.EVENT_TYPE = 'MAS2' 
LEFT JOIN EVENT_TABLE ev3 ON ev3.TABLE_ID1 = ps.ship_id AND ev3.EVENT_TYPE = 'MAS3' 
LEFT JOIN EVENT_TABLE ev4 ON ev4.TABLE_ID1 = ps.ship_id AND ev4.EVENT_TYPE = 'MAS4' 
LEFT JOIN EVENT_TABLE ev5 ON ev5.TABLE_ID1 = ps.ship_id AND ev5.EVENT_TYPE = 'MAS5' 
WHERE ps.eta >= '2010-03-22'
GROUP BY ps.ship_id
LIMIT 100;

在大约 2 秒内返回的第一个数据库 (A) 的 EXPLAIN 查询计划是:

+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                                                                                                                          | key                              | key_len | ref                          | rows | Extra                                        |
+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | ps    | range  | PRIMARY,IX_ETA_DATE                                                                                                                    | IX_ETA_DATE                      | 4       | NULL                         |  174 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | ev0   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_PROD.ps.ship_id,const |    1 |                                              | 
|  1 | SIMPLE      | ev1   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_PROD.ps.ship_id,const |    1 |                                              | 
|  1 | SIMPLE      | ev2   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_PROD.ps.ship_id,const |    1 |                                              | 
|  1 | SIMPLE      | ev3   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_PROD.ps.ship_id,const |    1 |                                              | 
|  1 | SIMPLE      | ev4   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_PROD.ps.ship_id,const |    1 |                                              | 
|  1 | SIMPLE      | ev5   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_PROD.ps.ship_id,const |    1 |                                              | 
|  1 | SIMPLE      | psi   | ref    | PRIMARY,IX_po_shipment_item_po_shipment1,FK_po_shipment_item_po_shipment1                                                              | IX_po_shipment_item_po_shipment1 | 4       | UNIVIS_PROD.ps.ship_id       |    1 |                                              | 
|  1 | SIMPLE      | pa    | ref    | IX_po_alloc_po_shipment_item2,IX_po_alloc_po_details_old,FK_po_alloc_po_shipment1,FK_po_alloc_po_shipment_item1,FK_po_alloc_po_header1 | FK_po_alloc_po_shipment1         | 4       | UNIVIS_PROD.psi.ship_id      |    5 | Using where                                  | 
|  1 | SIMPLE      | ph    | eq_ref | PRIMARY,IX_HDR_ID                                                                                                                      | PRIMARY                          | 4       | UNIVIS_PROD.pa.hdr_id        |    1 |                                              | 
+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------+------+----------------------------------------------+

在 >600 秒内返回的第二个数据库 (B) 的 EXPLAIN 查询计划是:

+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+--------------------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                                                                                                                          | key                              | key_len | ref                            | rows | Extra                                        |
+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+--------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | ps    | range  | PRIMARY,IX_ETA_DATE                                                                                                                    | IX_ETA_DATE                      | 4       | NULL                           |   38 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | psi   | ref    | PRIMARY,IX_po_shipment_item_po_shipment1,FK_po_shipment_item_po_shipment1                                                              | IX_po_shipment_item_po_shipment1 | 4       | UNIVIS_DEV01.ps.ship_id        |    1 |                                              | 
|  1 | SIMPLE      | ev0   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_DEV01.psi.ship_id,const |    1 |                                              | 
|  1 | SIMPLE      | ev1   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_DEV01.psi.ship_id,const |    1 |                                              | 
|  1 | SIMPLE      | ev2   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_DEV01.ps.ship_id,const  |    1 |                                              | 
|  1 | SIMPLE      | ev3   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_DEV01.psi.ship_id,const |    1 |                                              | 
|  1 | SIMPLE      | ev4   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_DEV01.psi.ship_id,const |    1 |                                              | 
|  1 | SIMPLE      | ev5   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_DEV01.ps.ship_id,const  |    1 |                                              | 
|  1 | SIMPLE      | pa    | ref    | IX_po_alloc_po_shipment_item2,IX_po_alloc_po_details_old,FK_po_alloc_po_shipment1,FK_po_alloc_po_shipment_item1,FK_po_alloc_po_header1 | IX_po_alloc_po_shipment_item2    | 4       | UNIVIS_DEV01.ps.ship_id        |    4 | Using where                                  | 
|  1 | SIMPLE      | ph    | eq_ref | PRIMARY,IX_HDR_ID                                                                                                                      | PRIMARY                          | 4       | UNIVIS_DEV01.pa.hdr_id         |    1 |                                              | 
+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+--------------------------------+------+----------------------------------------------+

当数据库 B 正在运行时，我可以查看 MySQL 管理员，并且状态无限期地保持在“正在复制到 tmp 表”。数据库 A 也有这种状态，但只有一秒钟左右。

这些数据库之间的表结构、索引、键等没有差异(我已经完成了 show create tables 和 diff'd)。

表格的大小是:

database A:
po_shipment 1776
po_shipment_item 1945
po_alloc 36298
po_header 71642
EVENT_TABLE 1608

database B:
po_shipment 463
po_shipment_item 470
po_alloc 3291
po_header 56149
EVENT_TABLE 1089

一些注意事项:

删除 WHERE 子句使查询返回 < 1 秒。
删除 GROUP BY 使查询返回 < 1 秒。
删除 ev5、ev4、ev3 等使得每个查询都变得更快删除。

AJ 回答后

更新: - 数据库 B 上 ship_id 的大小(最大值 = 800002752)明显大于数据库 A(最大值 = 3489)。鉴于这些是 InnoDB 表，更改任何缓冲区有助于处理这种大小的键吗？ 进一步更新:我减小了键的大小并重新分析，但性能仍然没有变化。

更新 EVENT_TABLE 的描述:

请注意，它在两个数据库中相同

+--------------------+--------------+------+-----+---------+----------------+
| Field              | Type         | Null | Key | Default | Extra          |
+--------------------+--------------+------+-----+---------+----------------+
| EVENT_TABLE_ID     | bigint(20)   | NO   | PRI | NULL    | auto_increment | 
| EVENT_TYPE         | varchar(10)  | NO   |     | NULL    |                | 
| TABLE_ID1          | int(11)      | NO   | MUL | NULL    |                | 
| TABLE_ID2          | int(11)      | YES  |     | NULL    |                | 
| TABLE_ID3          | int(11)      | YES  |     | NULL    |                | 
| TABLE_ID4          | int(11)      | YES  |     | NULL    |                | 
| EVENT_CREATED_DATE | datetime     | NO   |     | NULL    |                | 
| MESSAGE_REF        | varchar(100) | YES  |     | NULL    |                | 
+--------------------+--------------+------+-----+---------+----------------+

为了更好地衡量 SHOW CREATE TABLE EVENT_TABLE:

数据库之间唯一不同的是自动增量值

| EVENT_TABLE | CREATE TABLE `EVENT_TABLE` (
  `EVENT_TABLE_ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `EVENT_TYPE` varchar(10) NOT NULL,
  `TABLE_ID1` int(11) NOT NULL,
  `TABLE_ID2` int(11) DEFAULT NULL,
  `TABLE_ID3` int(11) DEFAULT NULL,
  `TABLE_ID4` int(11) DEFAULT NULL,
  `EVENT_CREATED_DATE` datetime NOT NULL,
  `MESSAGE_REF` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`EVENT_TABLE_ID`),
  KEY `IX_EVENT_ID_EVENT_TYPE` (`TABLE_ID1`,`EVENT_TYPE`)
) ENGINE=InnoDB AUTO_INCREMENT=1925 DEFAULT CHARSET=utf8 |

谁能建议如何解决这个问题？我错过了什么？

Michael Holzmann 提问后更新这是基于他更新的 STRAIGHT_JOIN 查询的新查询计划。请注意，数据库 B 具有“使用临时；使用文件排序”，而现在数据库 A 没有。这可能是由于长键或类似原因造成的吗？

数据库A

+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------+------+-------------+
| id | select_type | table | type   | possible_keys                                                                                                                          | key                              | key_len | ref                          | rows | Extra       |
+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------+------+-------------+
|  1 | SIMPLE      | ps    | index  | PRIMARY,IX_ETA_DATE                                                                                                                    | PRIMARY                          | 4       | NULL                         |  168 | Using where | 
|  1 | SIMPLE      | ev0   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_PROD.ps.ship_id,const |    1 |             | 
|  1 | SIMPLE      | ev1   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_PROD.ps.ship_id,const |    1 |             | 
|  1 | SIMPLE      | ev2   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_PROD.ps.ship_id,const |    1 |             | 
|  1 | SIMPLE      | ev3   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_PROD.ps.ship_id,const |    1 |             | 
|  1 | SIMPLE      | ev4   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_PROD.ps.ship_id,const |    1 |             | 
|  1 | SIMPLE      | ev5   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_PROD.ps.ship_id,const |    1 |             | 
|  1 | SIMPLE      | psi   | ref    | PRIMARY,IX_po_shipment_item_po_shipment1,FK_po_shipment_item_po_shipment1                                                              | IX_po_shipment_item_po_shipment1 | 4       | UNIVIS_PROD.ps.ship_id       |    1 |             | 
|  1 | SIMPLE      | pa    | ref    | IX_po_alloc_po_shipment_item2,IX_po_alloc_po_details_old,FK_po_alloc_po_shipment1,FK_po_alloc_po_shipment_item1,FK_po_alloc_po_header1 | FK_po_alloc_po_shipment_item1    | 8       | UNIVIS_PROD.psi.UID_items    |    6 | Using where | 
|  1 | SIMPLE      | ph    | eq_ref | PRIMARY,IX_HDR_ID                                                                                                                      | PRIMARY                          | 4       | UNIVIS_PROD.pa.hdr_id        |    1 |             | 
+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------+------+-------------+

数据库B

+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+-------------------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                                                                                                                          | key                              | key_len | ref                           | rows | Extra                                        |
+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+-------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | ps    | range  | PRIMARY,IX_ETA_DATE                                                                                                                    | IX_ETA_DATE                      | 4       | NULL                          |   38 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | ev0   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_DEV01.ps.ship_id,const |    1 |                                              | 
|  1 | SIMPLE      | ev1   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_DEV01.ps.ship_id,const |    1 |                                              | 
|  1 | SIMPLE      | ev2   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_DEV01.ps.ship_id,const |    1 |                                              | 
|  1 | SIMPLE      | ev3   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_DEV01.ps.ship_id,const |    1 |                                              | 
|  1 | SIMPLE      | ev4   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_DEV01.ps.ship_id,const |    1 |                                              | 
|  1 | SIMPLE      | ev5   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_DEV01.ps.ship_id,const |    1 |                                              | 
|  1 | SIMPLE      | psi   | ref    | PRIMARY,IX_po_shipment_item_po_shipment1,FK_po_shipment_item_po_shipment1                                                              | IX_po_shipment_item_po_shipment1 | 4       | UNIVIS_DEV01.ps.ship_id       |    1 |                                              | 
|  1 | SIMPLE      | pa    | ref    | IX_po_alloc_po_shipment_item2,IX_po_alloc_po_details_old,FK_po_alloc_po_shipment1,FK_po_alloc_po_shipment_item1,FK_po_alloc_po_header1 | IX_po_alloc_po_shipment_item2    | 4       | UNIVIS_DEV01.ps.ship_id       |    3 | Using where                                  | 
|  1 | SIMPLE      | ph    | eq_ref | PRIMARY,IX_HDR_ID                                                                                                                      | PRIMARY                          | 4       | UNIVIS_DEV01.pa.hdr_id        |    1 |                                              | 
+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+-------------------------------+------+----------------------------------------------+

UPDATE 这绝对是数据相关的。我从数据库 A 中转储数据并使用以下方法将其加载到数据库 B:

SELECT * from <table> into outfile <file>

和

LOAD DATA INFILE <file> into table <table>

然后数据库 B 查询快速运行 - 即。和数据库 A 一样快。关于如何诊断数据可能有什么问题有什么想法吗？？

更新 @newtover:来自数据库 A:

+-----------------+---------------------+
| eta_selectivity | ship_id_selectivity |
+-----------------+---------------------+
|          0.0693 |              1.0000 | 
+-----------------+---------------------+
1 row in set (0.02 sec)

来自数据库 B(坏的)

+-----------------+---------------------+
| eta_selectivity | ship_id_selectivity |
+-----------------+---------------------+
|          0.1814 |              1.0000 | 
+-----------------+---------------------+
1 row in set (0.02 sec)

为 po_shipment 创建的节目:

| po_shipment | CREATE TABLE `po_shipment` (
  `ship_id` int(11) NOT NULL DEFAULT '0',
  `ship_type` varchar(16) DEFAULT NULL,
  `foreign_agent` varchar(16) DEFAULT NULL,
  `agent_ref` varchar(16) DEFAULT NULL,
  `exporter_code` varchar(30) DEFAULT NULL,
  `importer_code` varchar(30) DEFAULT NULL,
  `carrier_code` varchar(30) DEFAULT NULL,
  `exporter_name` varchar(50) DEFAULT NULL,
  `importer_name` varchar(50) DEFAULT NULL,
  `carrier_name` varchar(50) DEFAULT NULL,
  `receipt` varchar(30) DEFAULT NULL,
  `pol_aol` varchar(50) DEFAULT NULL,
  `pod_aod` varchar(30) DEFAULT NULL,
  `final_dest` varchar(50) DEFAULT NULL,
  `vessel_flno` varchar(30) DEFAULT NULL,
  `ets` date DEFAULT NULL,
  `eta` date DEFAULT NULL,
  `pieces` int(11) DEFAULT '0',
  `weight` decimal(17,2) DEFAULT '0.00',
  `volume` decimal(17,2) DEFAULT '0.00',
  `marks` varchar(500) DEFAULT NULL,
  `goods_desc` varchar(500) DEFAULT NULL,
  `ship_terms` varchar(16) DEFAULT NULL,
  `ship_terms_desc` varchar(50) DEFAULT NULL,
  `house_hawb` varchar(30) DEFAULT NULL,
  `ocean_mawb` varchar(30) DEFAULT NULL,
  `booking_date` date DEFAULT NULL,
  `expected_cargo` date DEFAULT NULL,
  `mfrt_jobdisp` varchar(30) DEFAULT NULL,
  `ship_complete` date DEFAULT NULL,
  `user_id` varchar(30) DEFAULT NULL,
  `receipt_desc` varchar(60) DEFAULT NULL,
  `fin_dest_desc` varchar(60) DEFAULT NULL,
  `pol_aol_desc` varchar(60) DEFAULT NULL,
  `pod_aod_desc` varchar(60) DEFAULT NULL,
  `exporter_ref` varchar(26) DEFAULT NULL,
  `carrier_ref` varchar(26) DEFAULT NULL,
  `terms_conds` date DEFAULT NULL,
  `last_amended` date DEFAULT NULL,
  `user_amended` varchar(30) DEFAULT NULL,
  `package_type` varchar(24) DEFAULT NULL,
  `ext_cancelled` tinyint(1) NOT NULL DEFAULT '0',
  `ext_goh` tinyint(1) NOT NULL DEFAULT '0',
  `ext_arrival_date` date DEFAULT NULL,
  `ext_booking_ref` varchar(255) DEFAULT NULL,
  `ext_dc_booked_delivery_date` date DEFAULT NULL,
  `ext_dc_booked_delivery_time` varchar(10) DEFAULT NULL,
  `ext_comments` text,
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  `last_amended_time` int(10) DEFAULT NULL,
  `last_amended_uni` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`ship_id`),
  KEY `IX_ETA_DATE` (`eta`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

更新 @chris_I如果我通过删除除 EVENT_TABLE 之外的所有其他连接来简化查询，我将获得相同的性能(即糟糕)

SELECT * FROM po_shipment ps 
LEFT JOIN EVENT_TABLE ev0 ON ev0.TABLE_ID1 = ps.ship_id AND ev0.EVENT_TYPE = 'MAS0' 
LEFT JOIN EVENT_TABLE ev1 ON ev1.TABLE_ID1 = ps.ship_id AND ev1.EVENT_TYPE = 'MAS1' 
LEFT JOIN EVENT_TABLE ev2 ON ev2.TABLE_ID1 = ps.ship_id AND ev2.EVENT_TYPE = 'MAS2' 
LEFT JOIN EVENT_TABLE ev3 ON ev3.TABLE_ID1 = ps.ship_id AND ev3.EVENT_TYPE = 'MAS3' 
LEFT JOIN EVENT_TABLE ev4 ON ev4.TABLE_ID1 = ps.ship_id AND ev4.EVENT_TYPE = 'MAS4' 
LEFT JOIN EVENT_TABLE ev5 ON ev5.TABLE_ID1 = ps.ship_id AND ev5.EVENT_TYPE = 'MAS5' 
WHERE ps.eta >= '2010-03-22'
GROUP BY ps.ship_id
LIMIT 100;

更新 @Marcus Adams:查询已删除内部联接的计划:

SELECT * FROM po_shipment ps 
LEFT JOIN EVENT_TABLE ev0 ON ev0.TABLE_ID1 = ps.ship_id AND ev0.EVENT_TYPE = 'MAS0' 
LEFT JOIN EVENT_TABLE ev1 ON ev1.TABLE_ID1 = ps.ship_id AND ev1.EVENT_TYPE = 'MAS1' 
LEFT JOIN EVENT_TABLE ev2 ON ev2.TABLE_ID1 = ps.ship_id AND ev2.EVENT_TYPE = 'MAS2' 
LEFT JOIN EVENT_TABLE ev3 ON ev3.TABLE_ID1 = ps.ship_id AND ev3.EVENT_TYPE = 'MAS3' 
LEFT JOIN EVENT_TABLE ev4 ON ev4.TABLE_ID1 = ps.ship_id AND ev4.EVENT_TYPE = 'MAS4' 
LEFT JOIN EVENT_TABLE ev5 ON ev5.TABLE_ID1 = ps.ship_id AND ev5.EVENT_TYPE = 'MAS5' 
WHERE ps.eta >= '2010-03-22'
GROUP BY ps.ship_id
LIMIT 100;

来自数据库 A 的查询计划(在 0.35 秒内响应)

+----+-------------+-------+-------+------------------------+------------------------+---------+------------------------------+------+----------------------------------------------+
| id | select_type | table | type  | possible_keys          | key                    | key_len | ref                          | rows | Extra                                        |
+----+-------------+-------+-------+------------------------+------------------------+---------+------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | ps    | range | IX_ETA_DATE            | IX_ETA_DATE            | 4       | NULL                         |  174 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | ev0   | ref   | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36      | UNIVIS_PROD.ps.ship_id,const |    1 |                                              | 
|  1 | SIMPLE      | ev1   | ref   | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36      | UNIVIS_PROD.ps.ship_id,const |    1 |                                              | 
|  1 | SIMPLE      | ev2   | ref   | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36      | UNIVIS_PROD.ps.ship_id,const |    1 |                                              | 
|  1 | SIMPLE      | ev3   | ref   | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36      | UNIVIS_PROD.ps.ship_id,const |    1 |                                              | 
|  1 | SIMPLE      | ev4   | ref   | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36      | UNIVIS_PROD.ps.ship_id,const |    1 |                                              | 
|  1 | SIMPLE      | ev5   | ref   | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36      | UNIVIS_PROD.ps.ship_id,const |    1 |                                              | 
+----+-------------+-------+-------+------------------------+------------------------+---------+------------------------------+------+----------------------------------------------

数据库 B 的查询计划(没有及时响应泡茶)

    +----+-------------+-------+-------+------------------------+------------------------+---------+-------------------------------+------+----------------------------------------------+
| id | select_type | table | type  | possible_keys          | key                    | key_len | ref                           | rows | Extra                                        |
+----+-------------+-------+-------+------------------------+------------------------+---------+-------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | ps    | range | IX_ETA_DATE            | IX_ETA_DATE            | 4       | NULL                          |   38 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | ev0   | ref   | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36

最佳答案

尝试将 STRAIGHT_JOIN 添加到查询中以查看执行计划是否是问题所在。优化器正在为每个数据库选择不同的执行计划，这可能会导致问题。

SELECT STRAIGHT_JOIN * FROM po_shipment ps 
LEFT JOIN EVENT_TABLE ev0 ON ev0.TABLE_ID1 = ps.ship_id AND ev0.EVENT_TYPE = 'MAS0' 
LEFT JOIN EVENT_TABLE ev1 ON ev1.TABLE_ID1 = ps.ship_id AND ev1.EVENT_TYPE = 'MAS1' 
LEFT JOIN EVENT_TABLE ev2 ON ev2.TABLE_ID1 = ps.ship_id AND ev2.EVENT_TYPE = 'MAS2' 
LEFT JOIN EVENT_TABLE ev3 ON ev3.TABLE_ID1 = ps.ship_id AND ev3.EVENT_TYPE = 'MAS3' 
LEFT JOIN EVENT_TABLE ev4 ON ev4.TABLE_ID1 = ps.ship_id AND ev4.EVENT_TYPE = 'MAS4' 
LEFT JOIN EVENT_TABLE ev5 ON ev5.TABLE_ID1 = ps.ship_id AND ev5.EVENT_TYPE = 'MAS5' 
JOIN po_shipment_item psi USING (ship_id) 
JOIN po_alloc pa ON ps.ship_id = pa.ship_id AND pa.UID_items = psi.UID_items 
JOIN po_header ph ON pa.hdr_id = ph.hdr_id
WHERE ps.eta >= '2010-03-22'
GROUP BY ps.ship_id
LIMIT 100;

更新
1. 数据库 A 的新执行计划不需要文件排序或临时表，因为它使用主键。我将开始在查询中添加 USE INDEX 以查看是否可以加快对数据库 B 的查询。ship_id 是 po_shipment 的主键吗？如果是这样，您需要弄清楚分组依据或过滤日期的成本更高。

SELECT STRAIGHT_JOIN * FROM po_shipment ps USE INDEX( PRIMARY )
LEFT JOIN EVENT_TABLE ev0 ON ev0.TABLE_ID1 = ps.ship_id AND ev0.EVENT_TYPE = 'MAS0' 
LEFT JOIN EVENT_TABLE ev1 ON ev1.TABLE_ID1 = ps.ship_id AND ev1.EVENT_TYPE = 'MAS1' 
LEFT JOIN EVENT_TABLE ev2 ON ev2.TABLE_ID1 = ps.ship_id AND ev2.EVENT_TYPE = 'MAS2' 
LEFT JOIN EVENT_TABLE ev3 ON ev3.TABLE_ID1 = ps.ship_id AND ev3.EVENT_TYPE = 'MAS3' 
LEFT JOIN EVENT_TABLE ev4 ON ev4.TABLE_ID1 = ps.ship_id AND ev4.EVENT_TYPE = 'MAS4' 
LEFT JOIN EVENT_TABLE ev5 ON ev5.TABLE_ID1 = ps.ship_id AND ev5.EVENT_TYPE = 'MAS5' 
JOIN po_shipment_item psi USING (ship_id) 
JOIN po_alloc pa ON ps.ship_id = pa.ship_id AND pa.UID_items = psi.UID_items 
JOIN po_header ph ON pa.hdr_id = ph.hdr_id
WHERE ps.eta >= '2010-03-22'
GROUP BY ps.ship_id
LIMIT 100;

如果这没有帮助，请尝试为数据库 B 建议更多在数据库 A 的执行计划中使用的索引。

关于MySQL 查询性能 - 时间上的巨大差异，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/2549165/

文章推荐： ios - 数组中的值不更新

文章推荐： mysql - 无法通过套接字连接到本地MySQL服务器

文章推荐： swift - 修改作为参数传递给 Swift 中的函数的数组

文章推荐： java - sql jdbc getgeneratedkeys 返回列 "id"未找到，列类型未知

Mysql 查询 JOIN 查询
我有三张 table 。表 A 有选项名称(即颜色、尺寸)。表 B 有选项值名称(即蓝色、红色、黑色等)。表C通过将选项名称id和选项名称值id放在一起来建立关系。我的查询需要显示值和选项的名称，而
查询
在mysql中，如何计算一行中的非空单元格？我只想计算某些列之间的单元格，比如第 3-10 列之间的单元格。不是所有的列...同样，仅在该行中。最佳答案如果你想这样做，只能在 sql 中使用名称而
sql - 查询、 native 查询、命名查询和类型化查询之间的区别
关闭。这个问题需要多问focused 。目前不接受答案。想要改进此问题吗？更新问题，使其仅关注一个问题 editing this post . 已关闭 7 年前。 Improve this ques
elasticsearch - 在Elasticsearch查询中没有为[查询]注册的[查询]
我正在为版本7.6进行Elasticsearch查询我的查询是这样的: { "query": { "bool": { "should": [ {
sql - 查询、 native 查询、命名查询和类型化查询之间的区别
关闭。这个问题需要多问focused 。目前不接受答案。想要改进此问题吗？更新问题，使其仅关注一个问题 editing this post . 已关闭 7 年前。 Improve this ques
php - Mysql WHERE NOT EXISTS(查询)OR(查询)
是否可以编写一个查询来检查任一子查询(而不是一个子查询)是否正确？ SELECT * FROM employees e WHERE NOT EXISTS (
javascript - 查询。为表中的每一行发送 ajax 查询
我找到了很多关于我的问题的答案，但问题没有解决我有表格，有数据，例如: Data 1 Data 2 Data 3
salesforce - SOQL 查询 - 如何通过将字段设为小写并进行比较来编写 SOQL 查询？
以下查询返回错误: 查询: SELECT Id, FirstName, LastName, OwnerId, PersonEmail FROM Account WHERE lower(PersonEm
salesforce - SOQL 查询 - 如何通过将字段设为小写并进行比较来编写 SOQL 查询？
以下查询返回错误: 查询: SELECT Id, FirstName, LastName, OwnerId, PersonEmail FROM Account WHERE lower(PersonEm
Android SQLite 查询(我想解析一般的 SQL 查询)
我从 EditText 中获取了 String 值。以及提交查询的按钮。 String sql=editQuery.getText().toString();// SELECT * FROM empl
mysql 查询 - 为一个巨大的表优化现有的 MAX-MIN 查询
我有一个或多或少有效的查询(关于结果)，但处理大约需要 45 秒。这对于在 GUI 中呈现数据来说肯定太长了。所以我的需求是找到一个更快/更高效的查询(几毫秒左右会很好)我的数据表大约有 3000
SQL 查询 - 将 NULL 结果添加到 SELECT 查询
这是我第一次使用 Stack Overflow，所以我希望我以正确的方式提出这个问题。我有 2 个 SQL 查询，我正在尝试比较和识别缺失值，尽管我无法将 NULL 字段添加到第二个查询中以识别缺失
sql - 什么是动态 SQL 查询？何时需要使用动态 SQL 查询？
什么是动态 SQL 查询？何时需要使用动态 SQL 查询？我使用的是 SQL Server 2005。最佳答案这里有几篇文章: Introduction to Dynamic SQL Dynami
php - 在另一个 mysql 查询 while 循环中调用 mysql 查询
include "mysql.php"; $query= "SELECT ID,name,displayname,established,summary,searchlink,im
java - MySQL 查询 "select top 5"查询
我有一个查询要“转换”为 mysql。这是查询: select top 5 * from (select id, firstName, lastName, sum(fileSize) as To
c# - Entity Framework 查询 ToString 不会产生 SQL 查询
通过我的研究，我发现至少从 EF 4.1 开始，EF 查询上的 .ToString() 方法将返回要运行的 SQL。事实上，这对我来说非常有用，使用 Entity Framework 5 和 6。但
MySQL 查询(或 Doctrine 1.2 查询)- 从连接表和过滤器中获取最新项目
我在构造查询来执行以下操作时遇到问题: 按activity_type_id过滤联系人，仅显示最近事件具有所需activity_type_id或为NULL(无事件)的联系人表格结构如下: 一个联系人可
php - 如何在执行另一个 SQL 查询 x 分钟后执行一个 SQL 查询？
如何让我输入数据库的信息在输入数据 5 分钟后自行更新？假设我有一张 table : +--+--+-----+ |id|ip|count| +--+--+-----+ |
database - 如何在 N1QL 查询(Couchbase 查询)中使用 LENGTH() 字符串函数
我正在尝试搜索正好是 4 位数字的 ID，我知道我需要使用 LENGTH() 字符串函数，但找不到如何使用它的示例。我正在尝试以下(和其他变体)但它们不起作用。 SELECT max(car_id)
php - 将 SQL 查询 (+JOIN) 转换为 Symfony Propel 查询
我有一个在 mysql 上运行良好的 sql 查询(查询 + 连接): select sum(pa.price) from user u , purchase pu , pack pa where (

可可西里

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

MySQL 查询性能 - 时间上的巨大差异