时间戳

首页 > 解决方案 > 我目前在 mySQL 的“视图”中收到附加警告，知道这意味着什么吗？

mysql - 我目前在 mySQL 的“视图”中收到附加警告，知道这意味着什么吗？

问题描述

警告：#1292 截断不正确的 DOUBLE 值：“

错误：警告：#1292 截断不正确的 DOUBLE 值：“

我的“视图”的结构如下：

# | Name    | Type
1  id1       varchar(255)
2  id2       varchar(255)
3  category1 varchar(255)
4  category2 varchar(255)
5  category3 varchar(255)
6  id2name   varchar(255)
7  date1     date
8  date2     date
9  name1     varchar(255)
10 name2     varchar(255)
11 name3     varchar(255)
12 category4 varchar(255)
13 level1     int(11)
14 level2     int(11)
15 levelflex  varchar(255)
16 category5  varchar(255)
17 category6  varchar(255)
18 category7  varchar(21)

SQL 代码：

SELECT
`x`.`id1` AS `id1`,
`x`.`id2` AS `id2`,
`x`.`category1` AS `category1`,
`x`.`category2` AS `category2`,
`x`.`category3` AS `category3`,
`x`.`id2name` AS `id2name`,
`x`.`date1` AS `date1`,
`x`.`date2` AS `date2`,
`x`.`name1` AS `name1`,
`x`.`name2` AS `name2`,
`x`.`name3` AS `name3`,
`x`.`category4` AS `category4`,
`x`.`level1` AS `level1`,
`x`.`level2` AS `level2`,
`x`.`levelflex` AS `levelflex`,
`x`.`category5` AS `category5`,
`x`.`category6` AS `category6`,
`x`.`category7` AS `category7`FROM
(
    (
    SELECT
        `r`.`id1` AS `id1`,
        `d`.`id2` AS `id2`,
        `m`.`subcategory` AS `category1`,
        `n`.`subcategory` AS `category2`,
        `r`.`category3` AS `category3`,
        `d`.`id2name` AS `id2name`,
        `r`.`date1` AS `date1`,
        `d`.`date2` AS `date2`,
        `r`.`name1` AS `name1`,
        `r`.`name2` AS `name2`,
        `r`.`name3` AS `name3`,
        `r`.`category4` AS `category4`,
        `r`.`level1` AS `level1`,
        `d`.`level1` AS `level2`,
        `d`.`level1flex` AS `levelflex`,
        `r`.`category5` AS `category5`,
        `r`.`category6` AS `category6`,
        CAST(
            rank() over(
            PARTITION BY `r`.`id1`
        ORDER BY CASE WHEN
            `m`.`subcategory` = `n`.`subcategory` AND `r`.`level1` = `d`.`level1` THEN 1 WHEN `m`.`subcategory` = `n`.`subcategory` AND `r`.`level1` = `d`.`levelflex` THEN 2 WHEN `m`.`subcategory` <> `n`.`subcategory` AND `r`.`level1` = `d`.`level1` THEN 3 WHEN `m`.`subcategory` <> `n`.`subcategory` AND `r`.`level1` = `d`.`levelflex` THEN 4
        END,
        `d`.`date2`,
        `r`.`id1`
            ) AS CHAR CHARSET utf8mb4) AS `category7`FROM
        (
            (
                (
                    (
                        (
                            (
                                (
                                    (
                                        `Server`.`table1` `d`
                                    JOIN `Server`.`table2` `r`
                                    ON
                                        (
                                            `d`.`category4` = `r`.`category4` AND(
                                                `d`.`level1` = `r`.`level1` AND `r`.`level1` <> 12 AND `r`.`level1` <> 13 OR SUBSTRING_INDEX(`d`.`levelflex`, ',', 1) = `r`.`level1` AND `r`.`level1` <> 12 AND `r`.`level1` <> 13 OR SUBSTRING_INDEX(`d`.`levelflex`, ',', -1) = `r`.`level1` AND `r`.`level1` <> 12 AND `r`.`level1` <> 13
                                            )
                                        )
                                    )
                                JOIN `Server`.`table3` `p`
                                ON
                                    (
                                        `d`.`id2name` = `p`.`newid2name` OR `d`.`id2name` = `p`.`oldid2name`
                                    )
                                )
                            JOIN `Server`.`table4` `m`
                            ON
                                (
                                    `p`.`newid2name` = `m`.`id2name` OR `p`.`oldid2name` = `m`.`id2name`
                                )
                            )
                        JOIN `Server`.`table5` `h`
                        ON
                            (`r`.`category3` = `h`.`category3`)
                        )
                    JOIN `Server`.`table6` `f`
                    ON
                        (`r`.`id1` = `f`.`id1`)
                    )
                LEFT JOIN `Server`.`table7` `e`
                ON
                    (`f`.`id1` = `e`.`id1`)
                )
            JOIN `Server`.`table8` `n`
            ON
                (
                    (
                        `r`.`date1` >= `h`.`subdate1` AND `e`.`subdate1` = `h`.`subdate1` OR `f`.`subdate2` = `h`.`subdate1` AND `r`.`date1` < `h`.`subdate1`
                    ) AND `h`.`id2name` = `n`.`id2name`
                )
            )
        LEFT JOIN `Server`.`table9` `z`
        ON
            (
                `d`.`id2` = `z`.`id2` AND `r`.`id1` = `z`.`id1`
            )
        )
    WHERE
        `z`.`id2` IS NULL
) `x`LEFT JOIN `Server`.`table10` `y`ON
(
    `x`.`id2` = `y`.`id2` AND `x`.`id1` = `y`.`id1`
)
)WHERE
`y`.`id2` IS NULL

对于总共 44 行，该查询也需要大约 15 秒或更长时间。有没有办法优化这个？

我也在尝试将数据从视图传输到表。对此有什么建议吗？

显示创建表：

看法 创建视图 character_set_client collat​​ion_connection
样品视图 CREATE ALGORITHM=UNDEFINED DEFINER= dbadmin@ %SQL SECURITY DEFINER VIEW sampleviewAS select x。作为，id1.`r ...id1x utf8mb4 utfmb4_unicode_ci

解释选择...：

ID 选择类型 桌子 类型 可能的键 钥匙 key_len 参考 行 额外的
1 基本的 衍生2 全部 无效的 无效的 无效的 无效的 251497948917600
1 基本的 是的 全部 无效的 无效的 无效的 无效的 1 使用哪里；使用连接缓冲区（平面，BNL 连接）
2 衍生的 d 全部 无效的 无效的 无效的 无效的 106 使用临时
2 衍生的 F 全部 基本的 无效的 无效的 无效的 115 使用连接缓冲区（平面，BNL 连接）
2 衍生的 e eq_ref 基本的 基本的 767 服务器.f.id1 1
2 衍生的 r eq_ref 基本的 基本的 767 服务器.f.id1 1 使用哪里
2 衍生的 z 全部 无效的 无效的 无效的 无效的 1 使用哪里；使用连接缓冲区（平面，BNL 连接）
2 衍生的 p 全部 无效的 无效的 无效的 无效的 140 使用哪里；使用连接缓冲区（增量，BNL 连接）
2 衍生的 米 全部 无效的 无效的 无效的 无效的 198 使用哪里；使用连接缓冲区（增量，BNL 连接）
2 衍生的 n 全部 无效的 无效的 无效的 无效的 198 使用连接缓冲区（平面，BNL 连接）
2 衍生的 H 全部 无效的 无效的 无效的 无效的 3759 使用哪里；使用连接缓冲区（增量，BNL 连接）

标签： mysqlviewmariadb

解决方案

有 251万亿行，我怀疑该查询包含意外的“交叉连接”。检查所有JOIN两者中的VIEW和使用中的VIEW。

AND另外，请注意和之间的优先规则OR。

OR通常是性能杀手。

（同时，我没有看到关于错误 1292 的任何线索。）

其中一些复合索引可能会有所帮助：

y:  INDEX(id2,  id1)
r:  INDEX(category4)
d:  INDEX(category4)
m:  INDEX(id2name,  subcategory)
n:  INDEX(id2name,  subcategory)
p:  INDEX(newid2name,  oldid2name)
h:  INDEX(category3,  subdate1, id2name)
f:  INDEX(id1,  subdate2)
e:  INDEX(id1,  subdate1)
z:  INDEX(id2,  id1)

部分性能优势在于它们中的大多数都是“覆盖”的。如需进一步讨论，请提供SHOW CREATE TABLE基础表格和EXPLAIN SELECT...

推荐阅读