时间戳

首页 > 解决方案 > 用于连接具有最新更改的表的 Mysql 优化

mysql - 用于连接具有最新更改的表的 Mysql 优化

问题描述

我是 MYSQL 的初学者，我有这个查询大约需要 4-7 秒，我需要在你的帮助下对其进行优化！

我的桌子是：

cheques_movimientos（支票的“状态变化或变动”）
cheques_movimientos_rel（支票和“状态变化或变动”的关系表）
payment_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，这是一个示例数据库，真实的大约 500 个 ID。

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

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

在我存入支票并记录移动并将其分配给支票后，我没有保留支票的最新状态

标签： mysqlsqlquery-optimization

解决方案

还没有答案，但不仅仅是评论允许的。

你提供了一堆可以使用的......表结构，查询（即使它很糟糕）甚至是示例数据。

您提供的示例数据有点好，查看它已经确定它们之间的关系分别是

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

也就是说，查看数据，有一些条目在 payment_cheques 中有不在 cheques_movimentos_rel 表中的条目，例如 ID 671、682、685。

反之亦然... cheques_movimentos_rel 中的条目在 payment_cheques 中不存在，例如 671、681

同样，在 cheques_movimientos 中有不在 cheques_movimentos_rel 表中的条目，包括 192、193 和 195。

您能否编辑您的帖子，以阐明为什么在任一表格中可能缺少此类 ID。此外，用简单的句子描述你正在尝试做的事情，不管查询是什么。例如：我正在寻找所有 xxx，其中 blah、blah 等。如果没有记录匹配 blah、blah，我需要做 zzz。

您还在比较 TIPO != '1' 并且没有此类条目甚至存在类型为 '1' ，除非这是您的左连接的基础。提供更多信息，您可能会获得解决方案。

推荐阅读