如果 WHERE 子句中没有值，列的顺序在多列索引中是否重要 [英] Does the order of columns matter in a multi-column index if there is no value in the WHERE clause

问题描述

我了解需要多列索引，并且索引顺序在诸如

I understand that a multi-column index is required and that index order matters in requests such as

    SELECT product_id 
      FROM order_detail 
     WHERE order_id in (1, 2, 3)

但是，当WHERE子句中没有值时，我无法弄清楚多列索引和索引顺序的必要性.

However, I can't figure out the necessity of multi-column indexes and index order when there is no value in the WHERE clause.

1. 我相信只有在 WHERE 子句中指定了值时才会使用多列索引(我可能错了).因此，为了优化"表 order_detail 和下面的请求，我应该在 product_id 和 order_id 上创建多列索引 还是我应该创建两个独立的索引，一个在 order_id 上，一个在 product_id 上:

1. I believe multi-column index is only used when a value is specified in the WHERE clause (I may be wrong). Therefore, in order to "optimize" the table order_detail and for the request below, should I create a multi-column index on product_id and order_id or should I create two separate indexes, one on order_id and one on product_id:

SELECT O.order_id, P.product_name 
  FROM order_detail OD, order O, product P 
 WHERE OD.order_id = O.order_id 
   AND P.product_id = OD.product_id    

如果在 WHERE 子句中的两列上进行搜索时仍然需要多列索引，即使其中没​​有值，正确的顺序是什么?多列索引呢?关于上面表 order_detail 的示例，我应该使用 (order_id, product_id) 还是应该使用 (product_id, order_id)?当 WHERE 子句中没有值时，顺序是否重要?

If a multi-column index is still required when search is proceeded on two columns in the WHERE clause even if there is no value in it, what is the proper order to give to the multi column index then? Regarding the example above for the table order_detail, should I use (order_id, product_id) or should I use (product_id, order_id)? Does the order matter when there is no value in the WHERE clause?

非常感谢您的帮助.

推荐答案

1. 优化WHERE 子句时只能使用一个索引.因此，如果您要测试多个列，并且想要获得最佳性能，则应该使用包含所有(或大部分)列的多列索引.如果您创建两个单独的索引，它只会使用其中一个来优化 WHERE 条件的那部分，然后它必须在这些行中进行扫描以匹配另一个条件.

1. Only one index can be used when optimizing the WHERE clause. So if you're testing multiple columns, and you want the best performance, you should use a multi-column index that includes all (or most) of them. If you create two separate indexes, it will just use one of them to optimize that part of the WHERE condition, and then it will have to do scans within those rows to match the other condition.

多列索引可用作前缀中任何一组列的索引.因此，如果您在 (col1, col2, col3) 上有一个索引，它将充当 col1 和 (col1, col2) 上的索引出色地.如果您在 (order_id, product_id) 上有索引，它也可用于优化第一个查询.如果您还希望能够优化仅使用 product_id 的查询，请在该列上创建一个额外的索引.相反，如果您在 (product_id, order_id) 上有索引，则需要在 order_id 上有一个单独的索引来优化第一个查询.

A multi-column index can be used as an index for any set of columns in a prefix. So if you have an index on (col1, col2, col3), it acts as an index on col1 and (col1, col2) as well. If you have an index on (order_id, product_id), it can be used to optimize the first query as well. If you also want to be able to optimize queries that just use product_id, create an additional index on that column. Conversely, if you have an index on (product_id, order_id), you'll need a separate index on order_id to optimize the first query.

这篇关于如果 WHERE 子句中没有值，列的顺序在多列索引中是否重要的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！