mysql - 如何在mysql 5.7中使用 "OR"条件访问5000万条记录

Question

我在单个分区中有 3 亿条数据，现在我将使用 2 列(索引)或条件来过滤该记录。这可能吗？

CREATE TABLE `temp_bulk_tesing` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `request_account` INT(11) NOT NULL DEFAULT '0',
    `responce_account` INT(11) NOT NULL DEFAULT '0',
    `creatition_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `request_account` (`request_account`),
    INDEX `responce_account` (`responce_account`),
    INDEX `creatition_date` (`creatition_date`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=16371
; 

CREATE TABLE `org_account` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

select count(temp_bulk_tesing.id) from temp_bulk_tesing
inner join org_account
on (temp_bulk_tesing.request_account=org_account.id
or temp_bulk_tesing.responce_account=org_account.id)
and temp_bulk_tesing.creatition_date >='2019-10-10 22:22:18'
and temp_bulk_tesing.creatition_date <= '2019-10-13 22:22:18'

这花费了太多时间。我们如何优化它？

原始表中也存在分区，但挑战是我们在 1 天内获得 3 亿条记录。

Answer 1

您可以尝试UNION ALL:

SELECT COUNT(*)
FROM (
  select temp_bulk_tesing.id
  from temp_bulk_tesing
  join org_account
    on temp_bulk_tesing.request_account=org_account.id
   and temp_bulk_tesing.creatition_date BETWEEN '2019-10-10 22:22:18'AND'2019-10-13 22:22:18'
  union all
  select temp_bulk_tesing.id
  from temp_bulk_tesing
  join org_account
    on temp_bulk_tesing.responce_account=org_account.id
   and temp_bulk_tesing.creatition_date BETWEEN '2019-10-10 22:22:18'AND'2019-10-13 22:22:18'
) sub