代码之家 › 专栏 › 技术社区 › drenda

使用MySQL5.7的NULL为NULL或条件语法的慢查询

jpa hibernate mysql

drenda · 技术社区 · 6 年前

我从运行“简单”查询的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;

对于了解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)

这个查询涉及 0.105秒 上一个查询的解释返回:

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为NULL或条件”

1 回复 | 直到 6 年前

László Tóth 6 年前

我认为Ryan是对的,所以许多或语句使查询变得如此糟糕。

您应该以编程方式构建查询以获得更好的性能。因此,如果用户没有通过一个可能的过滤器进行选择,那么您不应该包括在查询中!

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