mysql - EXPLAIN 显示 "DEPENDENT SUBQUERY"并且从 MariaDB 迁移到 MySQL 后非常慢

Question

我正在使用 mysqldump 方法从旧的 MariaDB 10.0 数据库迁移到新的 Google Cloud Sql with Mysql 5.7。迁移后，我收到一些关于 WHERE EXISTS 语句的非常慢的查询。

我试图在我的旧数据库和新数据库上解释我的查询，它解释了不同的结果。由于它使用转储，我假设表索引没有变化。这是我想要运行的查询

SELECT * FROM detitem
    where exists (select 1 from detlayanan
                    where detitem.iddetlayanan = detlayanan.id
                      and detlayanan.layanan_idlayanan='LYN15176176101503')

来自旧数据库的 EXPLAIN

+------+-------------+------------+------+------------------------------------+----------------------------+---------+--------------------------------+-------+--------------------------+
| id   | select_type | table      | type | possible_keys                      | key                        | key_len | ref                            | rows  | Extra                    |
+------+-------------+------------+------+------------------------------------+----------------------------+---------+--------------------------------+-------+--------------------------+
|    1 | PRIMARY     | detlayanan | ref  | PRIMARY,fk_detlayanan_layanan1_idx | fk_detlayanan_layanan1_idx | 22      | const                          | 11030 | Using where; Using index |
|    1 | PRIMARY     | detitem    | ref  | FK_detitem_detlayanan              | FK_detitem_detlayanan      | 52      | citridia_sinadme.detlayanan.id |     1 |                          |
+------+-------------+------------+------+------------------------------------+----------------------------+---------+--------------------------------+-------+--------------------------+

和来自新数据库的 EXPLAIN

+----+--------------------+------------+------------+--------+------------------------------------+---------+---------+---------------------------------------+---------+----------+-------------+
| id | select_type        | table      | partitions | type   | possible_keys                      | key     | key_len | ref                                   | rows    | filtered | Extra       |
+----+--------------------+------------+------------+--------+------------------------------------+---------+---------+---------------------------------------+---------+----------+-------------+
|  1 | PRIMARY            | detitem    | NULL       | ALL    | NULL                               | NULL    | NULL    | NULL                                  | 2079094 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | detlayanan | NULL       | eq_ref | PRIMARY,fk_detlayanan_layanan1_idx | PRIMARY | 52      | citridia_sinadme.detitem.iddetlayanan |       1 |     5.00 | Using where |
+----+--------------------+------------+------------+--------+------------------------------------+---------+---------+---------------------------------------+---------+----------+-------------+

即使有索引，新的也是全表扫描。我在这里遗漏了什么吗？

这是“detlayanan”表

CREATE TABLE `detlayanan` (
  `transaksi_idtransaksi` varchar(40) NOT NULL,
  `layanan_idlayanan` varchar(20) NOT NULL,
  `nama_layanan` varchar(255) DEFAULT NULL,
  `jumlah_beli` float DEFAULT NULL,
  `id` varchar(50) NOT NULL,
  `harga` decimal(20,2) DEFAULT '0.00',
  `hargatotal` decimal(20,2) DEFAULT '0.00',
  `luas_p` double(255,2) DEFAULT '0.00',
  `luas_l` double(255,2) DEFAULT '0.00',
  `luas_q` double(255,2) DEFAULT '0.00',
  `keterangan` varchar(255) DEFAULT '',
  `iddeposit` varchar(255) DEFAULT NULL,
  `posisi` tinyint(4) DEFAULT '1',
  `idworkshop` varchar(60) DEFAULT NULL,
  `is_wsot` tinyint(4) DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `total_bersih` varchar(20) DEFAULT '0',
  `total_min_order` decimal(20,2) DEFAULT '0.00',
  `kondisi_barang` text,
  PRIMARY KEY (`id`),
  KEY `fk_detlayanan_layanan1_idx` (`layanan_idlayanan`),
  KEY `fk_detlayanan_deposit` (`iddeposit`),
  KEY `transaksi_idtransaksi` (`transaksi_idtransaksi`),
  CONSTRAINT `detlayanan_ibfk_1` FOREIGN KEY (`transaksi_idtransaksi`) REFERENCES `transaksi` (`idtransaksi`),
  CONSTRAINT `fk_detlayanan_layanan1` FOREIGN KEY (`layanan_idlayanan`) REFERENCES `layanan` (`idlayanan`) ON DELETE NO ACTION ON UPDATE NO ACTION
)

这是“detitem”表

CREATE TABLE `detitem` (
  `item_iditem` varchar(20) NOT NULL,
  `layanan_idlayanan` varchar(255) NOT NULL,
  `jumlah_item` int(255) DEFAULT NULL,
  `transaksi_idtransaksi` varchar(255) NOT NULL,
  `id` varchar(50) NOT NULL,
  `iddetlayanan` varchar(50) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `hapus` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `FK_detitem_item` (`item_iditem`),
  KEY `FK_detitem_layanan` (`layanan_idlayanan`),
  KEY `FK_detitem_transaksi` (`transaksi_idtransaksi`),
  KEY `FK_detitem_detlayanan` (`iddetlayanan`),
  CONSTRAINT `FK_detitem_detlayanan` FOREIGN KEY (`iddetlayanan`) REFERENCES `detlayanan` (`id`),
  CONSTRAINT `FK_detitem_item` FOREIGN KEY (`item_iditem`) REFERENCES `item` (`iditem`),
  CONSTRAINT `FK_detitem_layanan` FOREIGN KEY (`layanan_idlayanan`) REFERENCES `layanan` (`idlayanan`),
  CONSTRAINT `FK_detitem_transaksi` FOREIGN KEY (`transaksi_idtransaksi`) REFERENCES `transaksi` (`idtransaksi`)
)

我希望解释中的“行”保持较小，在旧数据库中，即使在另一个表中，“行”也保持在非常低的水平。但在新数据库中它最多可以显示百万。

更新

经过一些研究，实际上我必须在 where 语句中显式添加一些索引列以伴随 EXISTS 语句。所以查询会像这样

SELECT  * FROM  detitem WHERE
    <indexed column> in (<some id's>)
    AND EXISTS ( SELECT 1 FROM detlayanan WHERE detitem.iddetlayanan = 
    detlayanan.id AND detlayanan.layanan_idlayanan = 'LYN15176176101503' )

显然 MySQL 对 detitem 表进行全扫描以检查子查询值是否存在，当某些索引列被声明时，sql 不需要这样做。在MySQL中也发现了这种情况。

一些引用资料: https://mariadb.com/kb/en/library/exists-to-in-optimization/

Answer 1

这看起来像是 MariaDB 的优化器比 MySQL 领先一两步的情况。

看看这是否有效:

SELECT  i.*
    FROM ( SELECT  id
            FROM  detlayanan
            WHERE  layanan_idlayanan = 'LYN15176176101503' 
         ) AS x
    JOIN  detitem AS i  ON x.id = i.iddetlayanan 

我认为它在两个服务器上都能很好地工作。

更简单:

SELECT  i.*
    FROM  detlayanan AS lay
    JOIN  detitem AS i  ON lay.id = i.iddetlayanan 
    WHERE  lay.layanan_idlayanan = 'LYN15176176101503'