설명: MariaDB에서 MySQL로 이행한 후 "DEPEND SUBQUERY"가 표시되며 매우 느림

설명: MariaDB에서 MySQL로 이행한 후 "DEPEND SUBQUERY"가 표시되며 매우 느림

이전 MariaDB 10.0 데이터베이스에서 mysqldump 방식으로 Mysql 5.7을 사용하는 새로운 Google Cloud SQL로 마이그레이션하고 있었습니다.이행 후 WHERE EXISTS 스테이트먼트에 관한 매우 느린 쿼리를 받았습니다.

이전 DB와 새 DB에 대한 내 쿼리를 설명하려고 했더니 다른 결과가 나왔습니다.덤프를 사용하고 있기 때문에 테이블 인덱스에 변경은 없을 것으로 생각합니다.이것이 제가 실행하고자 했던 쿼리입니다.

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

이전 DB의 설명

+------+-------------+------------+------+------------------------------------+----------------------------+---------+--------------------------------+-------+--------------------------+
| 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 |                          |
+------+-------------+------------+------+------------------------------------+----------------------------+---------+--------------------------------+-------+--------------------------+

새로운 DB의 설명

+----+--------------------+------------+------------+--------+------------------------------------+---------+---------+---------------------------------------+---------+----------+-------------+
| 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 |
+----+--------------------+------------+------------+--------+------------------------------------+---------+---------+---------------------------------------+---------+----------+-------------+

새로운 것은 인덱스가 존재해도 Full-table 스캔을 하고 있습니다.내가 뭘 빠트렸나요?

여기 "데틀라얀" 테이블이 있습니다.

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
)

그리고 여기 "상세" 표가 있습니다.

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`)
)

설명의 "행"은 작게 유지되고, 이전 DB에서는 "행"이 다른 테이블에서도 매우 낮게 유지될 것으로 예상됩니다.그러나 새로운 DB에서는 최대 백만 개의 DB를 표시할 수 있습니다.

갱신하다

어떤 조사 후에 실제로 나는 EXISTS 문에 첨부하기 위해 where 문에 인덱스된 컬럼을 명시적으로 추가해야 합니다.그러면 쿼리는 다음과 같습니다.

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' )

서브쿼리 값의 유무를 확인하기 위해 detitem 테이블 상에서 풀스캔을 하고 있는 MySQL은 인덱스된 컬럼이 선언되어 있을 때 sql은 그것을 할 필요가 없다고 합니다.이 케이스는 MySQL에서도 발견되었습니다.

참고 자료: https://mariadb.com/kb/en/library/exists-to-in-optimization/

이는 MariaDB의 Optimizer가 MySQL의 Optimizer보다 한두 단계 앞서 있는 경우처럼 보입니다.

이것이 올바르게 동작하는지 확인합니다.

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' 

언급URL : https://stackoverflow.com/questions/57352410/explain-shows-dependent-subquery-and-very-slow-after-migrating-from-mariadb-to