简单sql查询永存

bgibtngc 于 2021-06-19 发布在 Mysql

关注(0)|答案(2)|浏览(316)

我正在使用 mysql-workbench 以及 mysql 中的服务器 ubunt 18 具有16 gb ram的计算机。
我有一个名为 ips ，还有两张table，上面写着： table1 以及 table2 . 在 table1 以及 table2 有两个字段： ip 以及 description 位的类型为字符串。我有很多记录。 table1 有779938条记录 table2 有136657条记录。
我需要联合查询一下 ip s在 table2 有一个 description 开始于 str1% 并且不包含 str2 并且不包含 str3 . 同时，那些 ip s在中有描述 table1 这不是从 str1% ，并包含 str2 或者 str3 .
我的问题是：

SELECT COUNT(`table2`.`ip`)
FROM `ips`.`table2`, `ips`.`table1`
WHERE `table2`.`ip` = `table1`.`ip`
       AND (LOWER(`table1`.`description`) NOT LIKE 'str1%' 
             AND (LOWER(`tabl1`.`description`) LIKE '%-str2-%' 
                   OR LOWER(`table1`.`description`) LIKE '%-str3-%'
                 )
            )
       AND (LOWER(`table2`.`description`) LIKE 'str1%'
            AND LOWER(`table2`.`description`) NOT LIKE '%-str2-%' 
            AND LOWER(`table2`.`description`) NOT LIKE '%-str3-%'
           );

但是，查询永远不会结束。持续时间已结束 ? 我从来没有得到结果。你能帮忙吗？
编辑：
下面是创建表和

SHOW CREATE TABLE IP地址 . 表2 ; ```
CREATE TABLE table2 (
ip varchar(500) DEFAULT NULL,
description varchar(500) DEFAULT NULL,
type varchar(500) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

2)  `SHOW CREATE TABLE` IP地址 `.` 表1 `;` ```
CREATE TABLE `table1` (
  `ip` varchar(500) DEFAULT NULL,
  `description` varchar(500) DEFAULT NULL,
  `type` varchar(500) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

EXPLAIN <query> ```

id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra

1, SIMPLE, table2, , ALL, , , , , 136109, 100.00, Using where
1, SIMPLE, table1, , ALL, , , , , 786072, 10.00, Using where; Using join buffer (Block Nested Loop)

编辑2：
的数据 `ip` 字段是以下格式的字符串： `str.str.str.str` 这个 `decription` 字段的格式如下： `str1-str2-str3-str4`

sql mysql Database mysql-workbench

来源：https://stackoverflow.com/questions/52426665/simple-sql-query-lasts-forever

2条答案

按热度按时间

moiiocjp1#

前面关于索引的回答可能会优化查询。可能是对的。但我很抱歉，我必须检查我用来解决问题的答案。感谢@raymond nijland第一个指出索引问题，这让我想起了主键。
问题的根源是查询中的两个表都没有主键。主键必须是唯一且不为null的键。就我而言，我已经有了 ip 字段准备作为主键发送到服务器。因为我用了 mysql- workbench 我右击表格，点击 Alter Table 然后检查AppPeriate字段的主键，如下所示：

解决了我的问题。

赞(0）回复(0）举报 2021-06-20

dxpyg8gm2#

你得到了 ALL 运算符，因为sql规划器未使用任何索引。它正在两个表上执行全表扫描。
当您选择超过5%的行时，全表扫描是最佳的。在您的例子中，如果您的字符串前缀“str1”只有一个字母，这可能会很好。如果它有多个字符，那么对索引的使用可以极大地提高性能。
现在，你正在进行的比较不是一个简单的比较。比较的不是列的值，而是表达式的结果： LOWER(table1.description) . 因此，如果希望此查询速度更快，则需要创建虚拟列并对它们进行索引。这在MySQL5.7及更新版本上可用：

alter table table1 add lower_desc varchar(50) 
  generated always as (LOWER(description)) virtual;
create index ix1 on table1 (lower_desc);

alter table table2 add lower_desc varchar(50) 
  generated always as (LOWER(description)) virtual;
create index ix2 on table2 (lower_desc);

当前缀有两个或更多字符时，这些索引将使查询更快。再拿一次执行计划。现在，接线员 ALL 不应该再出现了( INDEX 操作员现在应该出现在他们的位置上）。
顺便说一句，我想你在查询中错过了一个连接。我想应该是这样的（我加了第三行）：

SELECT COUNT(`table2`.`ip`)
FROM `ips`.`table2`
JOIN `ips`.`table1` on `ips`.`table1`.ip = `ips`.`table2`.ip
WHERE `table2`.`ip` = `table1`.`ip`
       AND (LOWER(`table1`.`description`) NOT LIKE 'str1%' 
             AND (LOWER(`tabl1`.`description`) LIKE '%-str2-%' 
                   OR LOWER(`table1`.`description`) LIKE '%-str3-%'
                 )
            )
       AND (LOWER(`table2`.`description`) LIKE 'str1%'
            AND LOWER(`table2`.`description`) NOT LIKE '%-str2-%' 
            AND LOWER(`table2`.`description`) NOT LIKE '%-str3-%'
           );

此外，要优化联接性能，您需要以下所示的一个（或两个）索引：

create index ix3 on table1 (ip);
create index ix4 on table2 (ip);

赞(0）回复(0）举报 2021-06-20

我来回答

简单sql查询永存

id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra

2条答案

相关问题

热门标签

最新问答