mysql - 使用Mysql 5.7使用NULL IS NULL OR条件语法进行慢速查询

Question

我从运行“简单”查询的 Mysql 中得到一些奇怪的计时值。

这是表的 DDL:

CREATE TABLE `frame` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `createdBy` varchar(255) DEFAULT NULL,
  `createdDate` datetime(6) NOT NULL,
  `lastModifiedBy` varchar(255) DEFAULT NULL,
  `lastModifiedDate` datetime(6) DEFAULT NULL,
  `sid` varchar(36) NOT NULL,
  `version` bigint(20) NOT NULL,
  `brand` varchar(255) DEFAULT NULL,
  `category` varchar(255) DEFAULT NULL,
  `colorCode` varchar(255) DEFAULT NULL,
  `colorDescription` varchar(255) DEFAULT NULL,
  `description` longtext,
  `imageUrl` varchar(255) DEFAULT NULL,
  `lastPurchase` datetime(6) DEFAULT NULL,
  `lastPurchasePrice` decimal(19,2) DEFAULT NULL,
  `lastSell` datetime(6) DEFAULT NULL,
  `lastSellPrice` decimal(19,2) DEFAULT NULL,
  `line` varchar(255) DEFAULT NULL,
  `manufacturer` varchar(255) DEFAULT NULL,
  `manufacturerCode` varchar(255) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `preset` bit(1) NOT NULL DEFAULT b'0',
  `purchasePrice` decimal(19,2) DEFAULT NULL,
  `salesPrice` decimal(19,2) DEFAULT NULL,
  `sku` varchar(255) NOT NULL,
  `stock` bit(1) NOT NULL DEFAULT b'1',
  `thumbUrl` varchar(255) DEFAULT NULL,
  `upc` varchar(255) DEFAULT NULL,
  `arm` int(11) DEFAULT NULL,
  `bridge` int(11) DEFAULT NULL,
  `caliber` int(11) DEFAULT NULL,
  `gender` varchar(255) DEFAULT NULL,
  `lensColor` varchar(255) DEFAULT NULL,
  `material` varchar(255) DEFAULT NULL,
  `model` varchar(255) NOT NULL,
  `sphere` decimal(10,2) DEFAULT NULL,
  `type` varchar(255) NOT NULL,
  `taxRate_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_k7s4esovkoacsc264bcjrre13` (`sid`),
  UNIQUE KEY `UK_ajh6mr6a6qg6mgy8t9nevdym1` (`sku`),
  UNIQUE KEY `UK_boqikmg9o89j8q0o5ujkj33b3` (`upc`),
  KEY `idx_manufacturer` (`manufacturer`),
  KEY `idx_brand` (`brand`),
  KEY `idx_line` (`line`),
  KEY `idx_colorcode` (`colorCode`),
  KEY `idx_preset` (`preset`),
  KEY `idx_manufacturer_model_color_caliber` (`manufacturer`,`model`,`colorCode`,`caliber`),
  KEY `FK1nau29fd70s1nq905dgs6ft85` (`taxRate_id`),
  CONSTRAINT `FK1nau29fd70s1nq905dgs6ft85` FOREIGN KEY (`taxRate_id`) REFERENCES `taxrate` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=392179 DEFAULT CHARSET=utf8;

查询是从我的应用程序以编程方式创建的。 “奇怪”的语法(NULL IS NULL OR 条件)对我来说非常方便，可以使我的代码更加紧凑，并且无需根据参数数量创建不同的查询。

对于了解 Hibernate HQL 和 JPA 工作原理的人，以下是查询:

此查询是在用户未设置任何过滤器时生成的，因此我的条件中的所有参数均为空，这就是查询的结果。

SELECT SQL_NO_CACHE COUNT(frame0_.`id`) AS col_0_0_ FROM `Frame` frame0_ 
WHERE (NULL IS NULL OR NULL LIKE CONCAT('%', NULL, '%') OR frame0_.`manufacturer` LIKE CONCAT('%', NULL, '%') OR frame0_.`manufacturerCode`=NULL OR frame0_.`sku`=NULL OR frame0_.`upc`=NULL OR frame0_.`line` LIKE CONCAT('%', NULL, '%') OR frame0_.`model` LIKE CONCAT('%', NULL, '%')) AND (NULL IS NULL OR frame0_.`manufacturer`=NULL) AND (NULL IS NULL OR frame0_.`line`=NULL) AND (NULL IS NULL OR frame0_.`caliber`=NULL) AND (NULL IS NULL OR frame0_.`type`=NULL) AND (NULL IS NULL OR frame0_.`material`=NULL) AND (NULL IS NULL OR frame0_.`model`=NULL) AND (NULL IS NULL OR frame0_.`colorCode`=NULL)

对于 137548 行的表，查询大约需要 0.105 秒。上一个查询的 EXPLAIN 返回:

id  select_type table   partitions  type    possible_keys   key key_len ref rows     filtered   Extra
1   SIMPLE       frame0_      \N             ALL    \    N  \N  \N  \N      137548   100.00   \N

上一个查询与此查询相同:

SELECT SQL_NO_CACHE COUNT(frame0_.`id`) AS col_0_0_ FROM `Frame` frame0_

对于同一个表中的相同结果，此查询只需 0.05s。

为什么对于Mysql来说它们是不同的并且第一个花费了这么多时间？有没有办法提高第一个查询的性能，并保持语法“NULL IS NULL 或条件”？

Answer 1

我认为 Ryan 说得对，所以 Many or 语句使得查询变得如此糟糕。

您应该以编程方式构建查询以获得更好的性能。因此，如果用户未通过可能的过滤器进行选择，则不应包含在查询中!

(HQL)

if(!StringUtils.isEmpty(manufacturer)) {
  query.and(m.manufacturer.eq(manufacturer))
}