用最新更改连接表的Mysql优化

Question

我是一个MYSQL的初学者，我有一个查询，大约需要4-7秒，我需要优化它，在您的帮助下！

我的桌子是：

cheques_movimientos (检查的“状态更改或移动”)

cheques_movimientos_rel (检查和“状态变化或移动”的关系表)

payments_cheques (检查列表，没有状态)

查询是选择最新的状态并与检查表连接，如果没有任何状态更改，则意味着它应该是状态10。

我需要一张检查清单，上面有任何最新的状态变化( 10,15,40,70,80 )。

这是SqlFiddle

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


CREATE TABLE `payments_cheques` (
  `id` int(9) NOT NULL,
  `venta_id` bigint(20) NOT NULL,
  `fecha` date NOT NULL DEFAULT '0000-00-00',
  `moneda` varchar(3) COLLATE utf8_spanish_ci NOT NULL,
  `valor` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `banco` varchar(30) COLLATE utf8_spanish_ci DEFAULT NULL,
  `numero` varchar(20) COLLATE utf8_spanish_ci NOT NULL DEFAULT '0',
  `cruzado` tinyint(1) NOT NULL DEFAULT 0,
  `primer_beneficiario` tinyint(1) NOT NULL DEFAULT 0,
  `almacen_id` int(6) DEFAULT NULL,
  `llamar_antes` varchar(40) COLLATE utf8_spanish_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;


INSERT INTO `payments_cheques` (`id`, `venta_id`, `fecha`, `moneda`, `valor`, `banco`, `numero`, `cruzado`, `primer_beneficiario`, `almacen_id`, `llamar_antes`) VALUES
(685, 1150, '2021-03-16', 'COP', 2500000, '1', '17002', 0, 0, 1, NULL),
(682, 1167, '2021-03-05', 'COP', 3300000, '51', '0387-1', 0, 0, 1, NULL),
(680, 1193, '2021-02-04', 'COP', 1050000, '7', 'MP8138', 0, 0, 1, NULL),
(678, 1143, '2021-02-02', 'COP', 1500000, '51', '5/58005', 0, 0, 1, NULL),
(677, 1120, '2021-01-25', 'COP', 3300000, '7', '07/00006', 0, 0, 1, NULL),
(676, 1199, '2020-12-30', 'COP', 3000000, '1', '89123', 0, 0, 1, NULL),
(675, 1297, '2020-12-29', 'COP', 3500000, '1', '39302', 0, 0, 1, NULL),
(673, 1124, '2020-12-23', 'COP', 1400000, '13', '418626', 0, 0, 1, NULL),
(671, 1219, '2021-03-01', 'COP', 1000000, '7', 'AF8398', 0, 0, 1,  NULL),
(670, 1129, '2020-12-23', 'COP', 900000, '7', 'AF7396', 0, 0, 1,  NULL);



ALTER TABLE `payments_cheques`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `payments_cheques`
  MODIFY `id` int(9) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=686;
  

CREATE TABLE `cheques_movimientos_rel` (
  `cheque_id` int(10) UNSIGNED NOT NULL,
  `movimiento_id` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `cheques_movimientos_rel` (`cheque_id`, `movimiento_id`) VALUES
(680, 198),
(678, 197),
(677, 196),
(676, 194),
(675, 194),
(673, 189),
(671, 200),
(670, 188);


ALTER TABLE `cheques_movimientos_rel`
  ADD UNIQUE KEY `Movimiento_Cheque` (`movimiento_id`,`cheque_id`);



CREATE TABLE `cheques_movimientos` (
  `id` int(10) UNSIGNED NOT NULL,
  `userid` int(10) UNSIGNED DEFAULT NULL,
  `timestamp` datetime NOT NULL,
  `fecha` date NOT NULL,
  `tipo` varchar(40) NOT NULL,
  `caja_movimiento_id` int(10) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `cheques_movimientos` (`id`, `userid`, `timestamp`, `fecha`, `tipo`, `caja_movimiento_id`) VALUES
(200, 1, '2021-03-04 17:24:28', '2021-03-04', '1', NULL),
(198, 1, '2021-02-12 15:15:40', '2021-02-12', '30', 14783),
(197, 1, '2021-02-08 15:17:13', '2021-02-08', '60', 14692),
(196, 1, '2021-01-27 17:27:49', '2021-01-27', '30', 14543),
(194, 1, '2020-12-30 16:08:05', '2020-12-30', '30', 14339),
(189, 1, '2020-12-29 11:19:20', '2020-12-29', '70', NULL),
(188, 1, '2020-12-29 11:18:47', '2020-12-29', '30', 14301);



ALTER TABLE `cheques_movimientos`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `grupo` (`id`);

ALTER TABLE `cheques_movimientos`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=203;

COMMIT;

这是我使用的查询：

SELECT * FROM (SELECT payments_cheques.*, IFNULL(cheques.tipo,'10') as status from `payments_cheques` LEFT JOIN 
    (SELECT a.*
    FROM (SELECT * FROM (SELECT 
                `payments_cheques`.`id`,
                `cheques_movimientos`.`id` as `mov_id`,
                `cheques_movimientos`.`tipo`
            FROM `payments_cheques`
            JOIN `cheques_movimientos_rel` ON `payments_cheques`.`id` = `cheques_movimientos_rel`.`cheque_id`
            JOIN `cheques_movimientos` ON `cheques_movimientos_rel`.`movimiento_id` = `cheques_movimientos`.`id`) as aa WHERE tipo != '1') as a
    LEFT OUTER JOIN (SELECT * FROM (SELECT 
                `payments_cheques`.`id`,
                `cheques_movimientos`.`id` as `mov_id`,
                `cheques_movimientos`.`tipo`
            FROM `payments_cheques`
            JOIN `cheques_movimientos_rel` ON `payments_cheques`.`id` = `cheques_movimientos_rel`.`cheque_id`
            JOIN `cheques_movimientos` ON `cheques_movimientos_rel`.`movimiento_id` = `cheques_movimientos`.`id`) as bb WHERE tipo != '1') as b
        ON a.id = b.id AND a.mov_id < b.mov_id
    WHERE b.id IS NULL and a.`tipo` != '1') as cheques
ON payments_cheques.id = cheques.id) as t1
WHERE status IN (10,15,40,70,80)

编辑：

我修正了缺少的ID，这是一个示例数据库，真正的ID是大约500个ID。

我需要的是一个检查列表，它的状态为10,15,40,70,80，10是状态，没有任何移动或状态变化。

这就是背后的故事:我收到客户的支票，然后存款或兑现支票，但首先。我需要可以使用的可用支票列表。

在我交存支票并记录移动并将其分配给支票之后，但我不保留

Answer 1

这个问题真的很糟糕。

我需要一张检查清单，上面有任何最新的状态变化( 10,15,40,70,80 )。

那就是你的出发点。是个常见问题。它甚至还有MySQL手册中自己的页面描述了两种获取结果的方法。还有另一种没有描述的方法，因为它的依赖于大量填充和类型转换有点混乱。

查看您的模式，cheques_movimientos_rel似乎没有增加任何价值，但是会给您的查询增加大量成本。

Answer 2

目前还没有答案，但不止是一句评论就允许了。

你提供了一群人和你一起工作..。表结构，查询(即使是坏的)，甚至是样本数据。

您提供的示例数据比较好，通过查看它们分别确定了它们之间的关系

payments_cheques pc
    JOIN cheques_movimientos_rel cmr
            ON pc.id = cmr.cheque_id
            JOIN cheques_movimientos cm
                ON cmr.movimiento_id = cm.id

尽管如此，从数据来看，在payments_cheques中有一些条目不在cheques_movimentos_rel表中，比如ID 671、682、685。

相反也是正确的..。cheques_movimentos_rel中不存在于payments_cheques中的条目，如671,681

类似地，在cheques_movimientos中有不存在于cheques_movimentos_rel表中的条目，包括192、193和195。

您能否请编辑您的帖子，以澄清为什么这些Ids可能丢失在各自的表中。此外，在简单的句子中，描述您想要做的事情，而不管查询是什么。例句:我在找所有的xxx，如果没有匹配的记录，我需要做zzz。

您也在比较TIPO != '1‘，并且没有这样的条目，甚至没有一个类型的'1’，除非这是您的左联接的基础。如果提供更多的信息，您可能会得到一个解决方案。