索引是否用于包含where子句中只有第一列索引的两列的查询？

Question

我读到了

如果在(col1、col2、col3)上有三列索引，则在(col1)、(col1、col2)和(col1、col2、col3)上有索引搜索功能。

我目前正在索引我的数据库表，但是我想知道如果我的查询(例如，在where子句中包含col1、col2和col5 )，数据库是否仍然使用创建的索引(col1、col2、col3)，即使col5不是创建的索引的一部分？

E.g

SELECT * FROM my_table WHERE col1='male' AND col2='24' AND col5='teacher'

Answer 1

是的，只要列是最左边的子集，MySQL就可以使用索引的子集。

演示：

mysql> create table my_table (col1 varchar(10), col2 varchar(10), col3 varchar(10), col4 varchar(10), col5 varchar(10), key(col1, col2, col3));

mysql> explain SELECT * FROM my_table WHERE col1='male' AND col2='24' AND col5='teacher'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: my_table
   partitions: NULL
         type: ref
possible_keys: col1
          key: col1
      key_len: 66
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: Using where

“解释”向您展示了优化器计划如何使用索引的报告。在这种情况下，请注意key: col1，它显示选择了哪个索引，ref: const,const表示它将比较查询中的两个常量值和索引中的列。

如果我搜索的列子集不是索引的左端连续列，则它不能使用索引的多个列。请参阅下面的ref: const，指示只使用一个值进行查找。

mysql> explain SELECT * FROM my_table WHERE col1='male' AND col3='24' AND col5='teacher'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: my_table
   partitions: NULL
         type: ref
possible_keys: col1
          key: col1
      key_len: 33
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where

如果我搜索一个连续的列子集，但它们不是索引的最左边的列，那么它根本不能使用索引。下面的示例显示，它必须使用由type: ALL表示的表扫描。

mysql> explain SELECT * FROM my_table WHERE col2='male' AND col3='24' AND col5='teacher'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: my_table
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where

Answer 2

是。任何以(col1, col2)开头的索引都将完全用于该查询。但是，如果您将其更改为，比如WHERE col1 > 5，它将只使用索引的col1。还有很多其他的例外。因此，请提供“真实”的查询进行分析。

“使用索引”意味着SELECT中任何地方所需的所有列都是在所选的INDEX中找到的(不一定是以最优的顺序)。它被称为“覆盖”指数。“使用where”并不提供太多的信息。

WHERE中项的顺序和d项并不重要。INDEX中的顺序确实很重要。

更多讨论：