MySQL-多列索引的序列 [英] Mysql - sequence of multiple column indexes

问题描述

如果我有一个如下查询:

Say if I have a query that look like this:

SELECT * FROM table WHERE category='5' and status='1' LIMIT 5

该表有100万行.

为了加快速度，我创建了索引(状态，类别)，即多列索引.

To speed things up, I create index (status, category), i.e. multiple column index.

有600个类别，但只有2个状态(1或0).我想知道如果创建索引(类别，状态)而不是索引(状态，类别)的性能是否存在差异.

There are 600 categories but only 2 status (1 or 0). I'm wondering if there is any difference in performance if I create index (category, status) instead of index (status, category).

推荐答案

状态优先. 诀窍在于，如果您只需要按类别查询，就可以.

Status first. The trick is then if you only need to query by category you can.

SELECT * from table where status in (1,0) and category = 'whatever'

并仍然获得索引支持. 当然，如果您的查询全部都使用这两个列，则两者都是相同的，但是在这种情况下，如果仅使用状态，那就好多了，并且仅对类别稍差一点.

and still get index support. Of course if your queries all use both columns it's the same either way, but in this case if you use only status it's much better, and only category only slightly worse if at all.

如果您还要查看大量插入内容，则希望最大程度地减少索引的数量，因此这是最好的选择，而不是拥有多个索引.

If you are looking at a lot of inserts as well, you want to minimize the number of indices, so this is your best bet rather than having multiple indices.

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