mysql没有使用复合索引的所有关键部分

lztngnrs  于 2021-06-21  发布在  Mysql
关注(0)|答案(3)|浏览(723)

我在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() 这些列上的运算符。
提前谢谢!
雅诺

q9rjltbz

q9rjltbz1#

谢谢大家的回复。在处理了索引中的列顺序(与基数相关)之后,我注意到应用程序正在与上的整数值进行比较 col2 (这是一个varchar列)。将值强制转换为字符串解决了性能问题。

zengzsys

zengzsys2#

`col2` = 123

是你的毁灭。在比较 VARCHAR 将varchar转换为整数常量。这需要动态转换所有相关行。
毕竟, col2 可能包含 "0123" 或者 "123.0" 或者 "1.23e2" . 因为弦是完全不同的;varchar上的索引是根据字符串属性排序的( COLLATION ).
可能的解决方案是更改为添加引号:

`col2` = "123"

世界的秩序 ANDsWHERE 没关系。
中列的顺序 INDEX 这很重要。有一个 INDEXcol1 以及 col3 首先,无论是哪种顺序,都会更好。
比较时基数很重要 INDEX(col1)INDEX(col3) .
基数对于实际使用的索引部分并不重要,例如在比较时 INDEX(col1, col3)INDEX(col3, col1) .
相反, WHERE int_col = "123" 将转换 "123"123 能够使用索引。

rks48beu

rks48beu3#

尝试使用高基数列构建复合
如:

col2,col3,col1

在不需要()的条件下使用and运算符

select * from `table` where `col1` = 'x' and `col2` = 'y' and `col3` = 'z'

最后,你可以强行施加指数

相关问题