- Java 双重比较
- java - 比较器与 Apache BeanComparator
- Objective-C 完成 block 导致额外的方法调用?
- database - RESTful URI 是否应该公开数据库主键?
我在查询优化方面遇到了奇怪的问题。 SQL 由类似 ORM 的库生成,只有在读取数兆字节的 SQL 日志后才会检测到错误。
SELECT
`ct_pricelistentry`.`uid` as `uid`, `ct_pricelistentry`.`skuGroup` as `skuGroup`
FROM
`ct_pricelistentry` INNER JOIN `lct_set`
ON `lct_set`.`parent_uid`='SET:ALLPRICELISTENTRIES' AND
`lct_set`.`ref_uid`=`ct_pricelistentry`.`uid`
WHERE
(`isGroup` IS FALSE) AND
(`isService` IS FALSE) AND
(`brand` = 'BRAND:5513f43697d637.00632331' OR `brand` IS NULL)
ORDER BY `skuGroup` ASC
EXPLAIN 说:
'1', 'SIMPLE', 'ct_pricelistentry', 'ALL', 'PRIMARY', NULL, NULL, NULL, '34591', 'Using where; Using filesort'
'1', 'SIMPLE', 'lct_set', 'eq_ref', 'PRIMARY', 'PRIMARY', '292', 'const,dealers_v2.ct_pricelistentry.uid', '1', 'Using where; Using index'
注意:提供了所有需要的索引,包括 skuGroup
。但是索引 skuGroup
仍然没有在 EXPLAIN possible_keys
中列出。它也不能被 FORCE INDEX
强制执行(它只是禁用所有索引)。
经过一些研究,我发现了 hacky 解决方案,但不确定它是否按预期工作:
FORCE INDEX (skuGroup)
,WHERE
子句虚拟 AND (skuGroup IS NULL OR skuGroup IS NOT NULL)
部分。后续查询
SELECT
`ct_pricelistentry`.`uid` as `uid`, `ct_pricelistentry`.`skuGroup` as `skuGroup`
FROM
`ct_pricelistentry` FORCE INDEX (`skuGroup`) INNER JOIN `lct_set`
ON `lct_set`.`parent_uid`='SET:ALLPRICELISTENTRIES' AND
`lct_set`.`ref_uid`=`ct_pricelistentry`.`uid`
WHERE
(`isGroup` IS FALSE) AND
(`isService` IS FALSE) AND
(`brand` = 'BRAND:5513f43697d637.00632331' OR `brand` IS NULL) AND
(`skuGroup` IS NULL OR `skuGroup` IS NOT NULL)
ORDER BY `skuGroup` ASC
给出没有文件排序的 EXPLAIN,所以它似乎使用索引来获取有序行:
'1', 'SIMPLE', 'ct_pricelistentry', 'range', 'skuGroup', 'skuGroup', '768', NULL, '16911', 'Using where'
'1', 'SIMPLE', 'lct_set', 'eq_ref', 'PRIMARY', 'PRIMARY', '292', 'const,dealers_v2.ct_pricelistentry.uid', '1', 'Using where; Using index'
到底发生了什么?这是一个MySQL错误吗?我在 MySQL 5.1 - 5.5 上测试过 - 结果相同。您有更可预测/稳定的解决方案吗?
---- CREATE TABLE ----
CREATE TABLE IF NOT EXISTS `lct_set` (
`parent_uid` varchar(48) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`ref_uid` varchar(48) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`parent_uid`,`ref_uid`),
UNIQUE KEY `BACK_PRIMARY` (`ref_uid`,`parent_uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `ct_pricelistentry` (
`uid` varchar(48) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`refcount` int(11) NOT NULL,
`isDisposed` tinyint(1) DEFAULT NULL,
`tag` text,
`isGroup` tinyint(1) DEFAULT NULL,
`parentEntry` varchar(48) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`externalUID` varchar(255) DEFAULT NULL,
`productCode` varchar(16) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`sku` varchar(255) DEFAULT NULL,
`skuGroup` varchar(255) DEFAULT NULL,
`measureUnit` varchar(16) DEFAULT NULL,
`image` varchar(48) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`itemClassExternalUID` varchar(255) DEFAULT NULL,
`itemClassName` varchar(255) DEFAULT NULL,
`itemClassDescription` text,
`itemClassComments` text,
`itemClassAttachments` varchar(48) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`brand` varchar(48) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`priceGroups` text,
`productAttributes` text,
`constituents` text,
`position` int(11) DEFAULT NULL,
`isService` tinyint(1) DEFAULT NULL,
`stackability` varchar(255) DEFAULT NULL,
PRIMARY KEY (`uid`),
UNIQUE KEY `test1` (`uid`,`skuGroup`),
KEY `name` (`name`),
KEY `sku` (`sku`),
KEY `itemClassExternalUID` (`itemClassExternalUID`),
KEY `parentEntry` (`parentEntry`),
KEY `position` (`position`),
KEY `externalUID` (`externalUID`),
KEY `productCode` (`productCode`),
KEY `skuGroup` (`skuGroup`),
KEY `brand` (`brand`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
最佳答案
修复 使用 INDEX(skuGroup)
避免了文件排序,但阻止了任何有用的过滤。优化过滤比避免文件排序更重要。
删除 FORCE
并添加此“复合”索引
INDEX(isGroup, isService, brand) -- (in any order)
它应该有所帮助,但可能不会阻止“使用文件排序”。 OR
是 killer 。
要防止对 ORDER BY
使用文件排序,您需要一个包含 所有 WHERE
子句的索引(通常是复合索引) , 加上 ORDER BY
列。在构建这样的索引时,唯一可以在WHERE
中处理的事情是and'd together '=' 子句。任何其他内容(例如您的 OR
)都会阻止优化。
为什么 OR 会造成伤害 这样想...假设有一长串按姓氏 + 名字排序的姓名打印列表。查询要求 WHERE last = 'Karakulov' ORDER BY first
。你会跳到第一个 Karakulov,所有的名字都会按顺序排列。现在假设您想要 WHERE (last = 'Karakulov' OR last = 'James') ORDER BY first
。你可以得到你所有的亲戚和我所有的亲戚,但你仍然需要将他们洗牌在一起以执行 ORDER BY first
。 MySQL 有一种技术:filesort(以及指向它的 tmp 表。)
作为安慰,filesort 的临时表通常是内存中的 MEMORY 表,因此速度相当快。
解决方法是有时将OR
转换为UNION
。 (这可能对您的查询没有帮助。)
一些架构评论和其他注释......
UNIQUE
键没有用,因为 PRIMARY KEY
已经将 uid
声明为“唯一”。
VARCHAR(48) utf8
是一个相当笨拙的大键。它是某种形式的 UUID 吗?如果是这样,我有关于随机性、字符集和大小的讨厌的话要说。
有的uid是(48),有的是(255);这是故意的吗?
去掉 (
skuGroupIS NULL OR
skuGroupIS NOT NULL)
-- 优化器可能不够聪明,无法意识到这一点永远是“真”!
FORCE INDEX
今天可能有效,但明天可能适得其反。摆脱它。
innodb_buffer_pool_size
的值是多少?如果您至少有 4GB 内存,它应该是 可用 RAM 的大约 70%。如果您将它保留在某个较低的默认值,那么您可能受 I/O 限制,因此速度很慢。
请提供 SHOW CREATE TABLE lct_set
-- JOIN
中发生了一些奇怪的事情。
关于MySQL ORDER BY 使用文件排序(2 个连接表),我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/38585070/
我正在寻找通过 sql 查询对我的 sql 结果进行排序,大概在 order by 子句中使用某种嵌套的 order by/order by 我有以下数据: TERM USER I
我有一个表格,其中包含如下所示的部分数据。我已经在 edition_id 上完成了订购。现在还需要订购 laungauge_id,这取决于 edition_id 的值。 Edition_id 是指报纸
所以我有两个表,Questions 和 Answers,由多对多关系表 QuestionsAnswers 连接。 Questions 有一个排序列,允许我控制它们如何显示给用户,而 Questions
当我们说“高阶”函数时,我怀疑“阶”的真正含义是什么?例如,我有一个嵌入式函数调用: f.g.h 那么它叫“三阶”函数吗? “高阶”函数是静态函数累加的概念吗?然后当我有一个递归函数 f 时,在运行时
在具有多个 order by 子句的 SQL 查询中,它们是否真的在执行期间全部运行? 例子: select * from my_table order by field5, field3, fiel
我跟进 query其中 schema.org 数据库用于查找类的子级数量 - 作为比我的应用程序更简单的数据库。我想按字母顺序连接 child 的名字。查询: prefix schema: pre
正如 nazdrovje 所指出的(参见 here ) Ordering@Ordering 可用于获取列表中每个元素的排名。即使列表包含重复元素,结果也是 n 排列(作为整数 1 到 n 的有序列表,
我有两张 table 。 它们都有日期和 item_id 列。 我正在通过 item_id 加入他们。 结果应按两个日期列一起排序 下面的代码有效,生成正确的结果集... 但是它们仅按第一个表的日期排
尝试掌握 SQL 我想按日期订购,然后在其中按标题订购。 示例: SELECT * FROM tblboek ORDER BY jr_van_uitgave DESC 如何在按年龄的订单中按头衔排序?
我想使用 FIELD 参数对我的 SQL 输出进行排序,但是当我这样做时,它首先吐出我不想要的结果,然后它首先吐出我想要的结果。在结果之上,它首先吐出。如果这有意义的话 ;) 如何先吐出已定义的值,然
我有一个无法破解的排序问题。我这样从我的表中选择: SELECT * FROM 'sidemodules' WHERE name = 'module1' OR name = 'module2' OR
我对 Django oscar 的覆盖模型有疑问。我想为模型添加一个新字段,但是当我这样做时,我遇到了 RuntimeError: Conflicting 'order' models in appl
我有两个表,电影和类别,我想先按CategoryID获得一个排序列表,然后按名称排序。。电影表格有三个列ID、NAME和CategoryID。CATEGORY表有两列ID和NAME。。我尝试了下面这样
In a MySQL query, when using the DISTINCT option, does ORDER BY apply after the duplicates are re
我想创建一个 sql 查询,为 2 个不同的查询一起返回结果。例如,我想要以下形式的结果:产品名称, avg(price), min(price), max(price), avg(order), m
我正在使用行号从存储过程中获取分页结果。 我发现使用动态 case 语句列名称进行排序会减慢速度 - 但如果我对所有内容进行硬编码就可以了。 有没有办法通过不使整个 sql 查询一个字符串并使用 SP
如何在范围搜索中使用Morton Order? 在wiki中,在“使用一维数据结构进行范围搜索”段落中, 它说 "the range being queried (x = 2, ..., 3, y =
我正在使用 sequelize.js,我在使用 order 语句时遇到问题,我想先通过 if id 排序(如果我的 id 在该别名表中),然后再排序.... order = [['alias', 'i
我有一个 php 脚本,它从数据库中提取内容并以某种方式打印它们。数据库有一个名为“order”的列标题,它的 INT 大小为 11。当我从数据库中获取数据时,我试图按数据库中的值“order”对内容
我有一个带有 ORDER BY 子句的 UPDATE 查询。我已将相同的查询复制到具有相同 ORDER BY 子句的 SELECT 中,但得到了不同的结果。 更新查询: UPDATE t_locks
我是一名优秀的程序员,十分优秀!