我在mysql 5.7.19中有一个innodb表,其中包含约170万行。我要优化以下查询:
select * from `table` where `col1` = 'x' and `col2` = 123 and `col3` = 'z'
其中列定义为(全部使用utf8mb4编码):
col1 varchar(255) null
col2 varchar(255) not null
col3 varchar(255) not null
以及所有列的索引:
key (
col1, -- Cardinality: 40
col2, -- Cardinality: 472810
col3 -- Cardinality: 403767
)
我希望查询运行得很快,因为mysql应该能够充分利用索引。现在,性能不是很好,当我用 explain format=json
:
"used_key_parts": [
"col1"
],
"key_length": "1022"
只使用综合指数的第一列。限制 col2
以及 col3
使用表格扫描进行评估。
有谁能给我解释一下这是怎么回事,并就如何改进这一点提出建议吗?
目前,我通过将列合并到一个列中,引入并索引一个存储的生成列来解决这个问题 col1
以及 col2
. 但是,我不能将此用于希望使用 IN()
这些列上的运算符。
提前谢谢!
雅诺
3条答案
按热度按时间q9rjltbz1#
谢谢大家的回复。在处理了索引中的列顺序(与基数相关)之后,我注意到应用程序正在与上的整数值进行比较
col2
(这是一个varchar列)。将值强制转换为字符串解决了性能问题。zengzsys2#
是你的毁灭。在比较
VARCHAR
将varchar转换为整数常量。这需要动态转换所有相关行。毕竟,
col2
可能包含"0123"
或者"123.0"
或者"1.23e2"
. 因为弦是完全不同的;varchar上的索引是根据字符串属性排序的(COLLATION
).可能的解决方案是更改为添加引号:
世界的秩序
ANDs
在WHERE
没关系。中列的顺序
INDEX
这很重要。有一个INDEX
与col1
以及col3
首先,无论是哪种顺序,都会更好。比较时基数很重要
INDEX(col1)
与INDEX(col3)
.基数对于实际使用的索引部分并不重要,例如在比较时
INDEX(col1, col3)
与INDEX(col3, col1)
.相反,
WHERE int_col = "123"
将转换"123"
至123
能够使用索引。rks48beu3#
尝试使用高基数列构建复合
如:
在不需要()的条件下使用and运算符
最后,你可以强行施加指数