查询耗时太长

Question

我有以下查询：

SELECT *
FROM document d
INNER JOIN users_documents ud ON d.id = ud.document_id 
WHERE (d.document_state_id=2
       AND ud.sharing_type_id=1
       AND ud.user_id=:id
       AND CURRENT_TIMESTAMP() BETWEEN d.upload_datetime AND d.approval_end_time
       AND ud.approval=2)
OR(d.document_state_id=1
   AND ud.sharing_type_id=2
   AND ud.user_id=:id
   AND CURRENT_TIMESTAMP() BETWEEN d.active_start_time AND d.active_end_time 
   AND ud.approval=2)
ORDER BY ud.application_date;

问题是，它需要大约800毫秒。当我将查询的结尾更改为：

ORDER BY ud.uploadDatetime;

我添加了索引: upload_datetime (表文档)，现在查询大约需要70ms。问题是我真的需要根据ud.application_date对数据进行排序。我尝试添加一个索引: application_date (在表users_documents上)，但是没有帮助(仍然需要800ms)。这次我应该用哪个指数来降低呢？或者我该怎么做？我正在使用MySQL。

非常感谢你的帮助！

Edit: Query解释: Query返回具有某个id的给定用户的新文档。它返回文档供“审批”(sharing_type=1)和供阅读(sharing_type=2)，approval=2 (用户尚未看到文档)，approval=1 (用户已审批文档)，0=disapproved；upload_datetime=the上传文档到系统的时间戳；approver_end_time=document可以由用户审批到此日期；activeStartTime=after审批，文档将从该日期起向其他用户显示(=活动结束时间)。结构

-- -----------------------------------------------------
-- Table `po_db`.`document_state`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `po_db`.`document_state` (
  `id` BIGINT(20) NOT NULL,
  `state` VARCHAR(255) NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

-- -----------------------------------------------------
-- Table `po_db`.`document`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `po_db`.`document` (
  `id` BIGINT(20) NOT NULL,
  `active_end_time` DATETIME NOT NULL,
  `active_start_time` DATETIME NOT NULL,
  `approval_end_time` DATETIME NOT NULL,
  `description` VARCHAR(255) NOT NULL,
  `name` VARCHAR(60) NOT NULL,
  `resource_path` VARCHAR(255) NOT NULL,
  `title` VARCHAR(15) NOT NULL,
  `upload_datetime` DATETIME NOT NULL,
  `document_state_id` BIGINT(20) NOT NULL,
  `user_id` BIGINT(20) NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `UK_36vs45u76s1n950kwxfa5lyhc` (`name` ASC),
  INDEX `FK1rwpvwxw2bvldt30kwfcbf57l` (`document_state_id` ASC),
  INDEX `FKjhdxdv9sijhujiynqbb5jc010` (`user_id` ASC),
  INDEX `title` (`title` ASC),
  INDEX `upload` USING BTREE (`upload_datetime`),
  CONSTRAINT `FK1rwpvwxw2bvldt30kwfcbf57l`
    FOREIGN KEY (`document_state_id`)
    REFERENCES `po_db`.`document_state` (`id`),
  CONSTRAINT `FKjhdxdv9sijhujiynqbb5jc010`
    FOREIGN KEY (`user_id`)
    REFERENCES `po_db`.`user` (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `po_db`.`sharing_type`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `po_db`.`sharing_type` (
  `id` BIGINT(20) NOT NULL,
  `name` VARCHAR(255) NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

和

-- -----------------------------------------------------
-- Table `po_db`.`users_documents`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `po_db`.`users_documents` (
  `document_id` BIGINT(20) NOT NULL,
  `sharing_type_id` BIGINT(20) NOT NULL,
  `user_id` BIGINT(20) NOT NULL,
  `application_date` DATETIME NOT NULL,
  `approval` INT(11) NOT NULL,
  `email_sent` BIT(1) NOT NULL,
  PRIMARY KEY (`document_id`, `sharing_type_id`, `user_id`),
  INDEX `FK4mt7odsubst5269c4djnj4jip` (`sharing_type_id` ASC),
  INDEX `FKtn1i27tltfpy3n4pe306vtwu9` (`user_id` ASC),
  INDEX `historie_dok` (`approval` ASC, `sharing_type_id` ASC, `user_id` ASC, `application_date` ASC),
  CONSTRAINT `FK4mt7odsubst5269c4djnj4jip`
    FOREIGN KEY (`sharing_type_id`)
    REFERENCES `po_db`.`sharing_type` (`id`),
  CONSTRAINT `FK6abpoybb0f2ydy6ufla1wo80x`
    FOREIGN KEY (`document_id`)
    REFERENCES `po_db`.`document` (`id`),
  CONSTRAINT `FKtn1i27tltfpy3n4pe306vtwu9`
    FOREIGN KEY (`user_id`)
    REFERENCES `po_db`.`user` (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

编辑2:我将查询的结尾更改为：

ORDER BY CASE WHEN d.document_state_id=2 THEN d.upload_datetime ELSE d.active_start_time END;

它与application_date基本相同，但速度仍然很慢(300ms，但比800ms要好)。

我尝试添加索引(表document)：document_state_id, upload_datetime, active_start_time

但它再一次也帮不上忙。:/

Answer 1

计划A:将OR变成UNION

    ( SELECT  *
            FROM  document d
            INNER JOIN  users_documents ud  ON d.id = ud.document_id
            WHERE  d.document_state_id=2
              AND  ud.sharing_type_id=1
              AND  ud.user_id=:id
              AND  CURRENT_TIMESTAMP() BETWEEN d.upload_datetime
                                           AND d.approval_end_time
              AND  ud.approval=2
    )
    UNION ALL
    ( SELECT  *
            FROM  document d
            INNER JOIN  users_documents ud  ON d.id = ud.document_id
            WHERE  d.document_state_id=1
              AND  ud.sharing_type_id=2
              AND  ud.user_id=:id
              AND  CURRENT_TIMESTAMP() BETWEEN d.active_start_time
                                           AND d.active_end_time
              AND  ud.approval=2 
    )
    ORDER BY  application_date;

这样，每个SELECT都可以很好地利用以approval ASC、sharing_type_id ASC、user_id ASC开头的索引。(第4列没有帮助。)

B计划：使用“覆盖索引”：

SELECT d2.*, ud2.*
  FROM (
    ( SELECT  id, document_id, sharing_type_id, user_id
            FROM  document d
            INNER JOIN  users_documents ud  ON d.id = ud.document_id
            WHERE  d.document_state_id=2
              AND  ud.sharing_type_id=1
              AND  ud.user_id=:id
              AND  CURRENT_TIMESTAMP() BETWEEN d.upload_datetime 
                                           AND d.approval_end_time
              AND  ud.approval=2
    )
    UNION ALL
    ( SELECT  id, document_id, sharing_type_id, user_id
            FROM  document d
            INNER JOIN  users_documents ud  ON d.id = ud.document_id
            WHERE  d.document_state_id=1
              AND  ud.sharing_type_id=2
              AND  ud.user_id=:id
              AND  CURRENT_TIMESTAMP() BETWEEN d.active_start_time
                                           AND d.active_end_time
              AND  ud.approval=2 
    )
       )
  JOIN document d2  USING(id)
  JOIN users_documents ud2  USING(document_id, sharing_type_id, user_id)
  ORDER BY  ud2.application_date;

加上这些新的索引；顺序很重要：

UD: INDEX(approval, sharing_type_id, user_id, document_id)  -- document_id last
D: INDEX(document_state_id, approval_end_time, approval_start_time, document_id)
D: INDEX(document_state_id, active_end_time, active_start_time, document_id)