时间戳

首页 > 解决方案 > MySql 自连接优化查询

mysql - MySql 自连接优化查询

问题描述

我试图从我的数据库中找出一些垃圾数据，其中删除了父项但未删除子项（仅一级）。为了解释这种情况，我提供了一些示例数据。

查询创建样本表

CREATE TABLE  `parentchild` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `parentid` int(10) unsigned DEFAULT NULL,
  `Name` varchar(45) NOT NULL,
  `IsDeleted` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `FK_parentchild_Self` (`parentid`) USING BTREE,
  CONSTRAINT `FK_parentchild_Self` FOREIGN KEY (`parentid`) REFERENCES `parentchild` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

插入一些虚拟数据后，表格看起来像

id  parentid    Name     IsDeleted
1               Fruits       1
2     1         Mango        0
3     1         Apple        0
4               Car          0
5     4         Baleno       0
6     4         Santro       0
7               Animals      0
8     7         Dog          0
9     7         Cat          0

现在我为找出第一级儿童而形成的查询是

SELECT t2.Name AS Name, t1.Name AS ParentName FROM parentchild t1
INNER JOIN parentchild t2 ON t1.ID=t2.ParentID
WHERE t1.IsDeleted=1 AND t2.IsDeleted=0;

我从这个查询中得到的输出似乎很好

Name    ParentName
Mango   Fruits
Apple   Fruits

但我担心的是性能，因为它的解释输出并不令人满意。

id  select_type table   partitions  type    possible_keys           key                 key_len     ref                 rows    filtered    Extra
1   SIMPLE        t1                ALL     PRIMARY                                                                      8       12.50      Using where
1   SIMPLE        t2                ref     FK_parentchild_Self     FK_parentchild_Self     5       test1.t1.id 2        2       12.50      Using where

谁能告诉我正确的索引或更好的查询来优化它吗？

我尝试在Index(id,IsDeleted)、Index(parentid,IsDeleted)和Index(id, parentid, IsDeleted)上添加索引，但没有一个可以优化扫描。

在我的暂存环境中，这些数据大约是 1 lac 行，它正在扫描所有行以找出 18 条父项已删除但未删除的记录。这是我的暂存环境的说明。在我的生产环境中有数百万行，因此我无法对大量数据运行此查询。

标签： mysqljoin

解决方案

一种索引策略将使用此索引：

CREATE INDEX idx2 ON parentchild (ParentID, IsDeleted, Name);

这将加快对 的查找parentchild，在连接过程中从左到右进行。另一种可能的策略是：

CREATE INDEX idx1 ON parentchild (ID, IsDeleted, Name);

如果 MySQL 决定这样做，这可能会加速另一个方向的连接。

推荐阅读