mysql 和多于一列的索引 [英] mysql and indexes with more than one column

问题描述

如何使用多列索引

原始索引在block_id上有一个索引，但是当它已经在有两列的唯一索引中时是否有必要?

The original index has an index on block_id, but is it necesarry when it's already in the unique index with two column?

多于一列的索引

(a,b,c)

• 您可以搜索 a、b 和 c
• 你可以搜索a和b
• 你可以搜索一个
• 您可以不搜索 a 和 c

• you can search for a, b and c
• you can search for a and b
• you can search for a
• you can not search for a and c

这也适用于唯一索引吗?

Does this apply to unique indexes too?

id
block_id
account_id
name

索引来源

PRIMARY KEY (`id`)
UNIQUE KEY `block_id` (`block_id`,`account_id`)
KEY `block_id` (`block_id`),
KEY `account_id` (`account_id`),

索引替代

PRIMARY KEY (`id`)
UNIQUE KEY `block_id` (`block_id`,`account_id`)
KEY `account_id` (`account_id`),

推荐答案

据我所知，您在上面描述的规则始终适用于索引是否唯一.您可以对您想到的查询运行解释，并观察在各种情况下何时使用索引以及何时不使用索引.

The rules you describe above have to my knowledge always held whether an index is unique or not. You might run explain on the query you have in mind and observe when the index is used and when it is not used under various circumstances.

这篇关于mysql 和多于一列的索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！