为什么MySQL不使用索引？

Question

我有两个表格：(省略与这个问题无关的栏)：

CREATE TABLE 'oc_room' (
  'id' int(11) NOT NULL AUTO_INCREMENT,
  'house_id' int(11) NOT NULL,
  'style_id' int(11) DEFAULT NULL,
  'weight' int(11) DEFAULT '0',
  'state' tinyint(4) DEFAULT '0',
  -- (more columns, omitted for clarity)
  PRIMARY KEY ('id'),
  KEY 'house_id' ('house_id'),
  KEY 'style_id' ('style_id'),
  KEY 'butler_id' ('butler_id'),
  KEY 'oc_room_house_state_hidden_ik_1' ('house_id','state','hidden'),
  CONSTRAINT 'oc_room_ibfk_1' FOREIGN KEY ('house_id') REFERENCES 'oc_house' ('id'),
  CONSTRAINT 'oc_room_ibfk_2' FOREIGN KEY ('style_id') REFERENCES 'oc_room_style' ('id'),
  CONSTRAINT 'oc_room_ibfk_3' FOREIGN KEY ('butler_id') REFERENCES 'oc_butler' ('id')
) ENGINE=InnoDB AUTO_INCREMENT=267 DEFAULT CHARSET=utf8;

CREATE TABLE 'oc_circle_of_community' (
  'id' int(11) NOT NULL AUTO_INCREMENT,
  'circle_id' int(11) NOT NULL,
  'community_id' int(11) NOT NULL,
  PRIMARY KEY ('id'),
  KEY 'circle_id' ('circle_id'),
  KEY 'community_id' ('community_id'),
  CONSTRAINT 'oc_circle_of_community_ibfk_1' FOREIGN KEY ('circle_id') REFERENCES 'oc_circle' ('id'),
  CONSTRAINT 'oc_circle_of_community_ibfk_2' FOREIGN KEY ('community_id') REFERENCES 'oc_community' ('id')
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

当我explain一个select语句时，我有两个问题

问题1：

让我们从两张图片开始：

图-1：

​

图2：

请特别注意explain输出的底线。

然后将PIC-1中的表格与PIC-2中的表格进行比较.你会发现：

1. PIC-1中oc_room的选择使用组合键oc_room_house_state_hidden_ik_1.
2. 然后在PIC-2中，密钥不被使用.
3. 这两条语句之间唯一的区别是，oc_room.id in (5,7,9,20,40,60 )替换了

oc_room.id in 
   ( select id 
       from oc_house
       where community_id in 
     ( select community_id
         from oc_circle_of_community
        where circle_id in
      ( select id 
          from oc_circle
         where oc_circle.district_id in
         ( select id
             from oc_district
            where oc_district.id = 3 ))))  

为什么有区别？

表oc_room中总共有大约300行。

问题2：

检查表的第2行、第2行，这说明了表oc_circle_of_community的选择。有两个可能的键：circle_id和community_id。为什么不使用这两把钥匙？

(表oc_circle_of_community__中共有14行。这可能会有帮助。)

Answer 1

来自底部的手册。

对于小表上的查询或报表查询处理大部分或所有行的大表，索引不太重要。当查询需要访问大多数行时，按顺序读取比使用索引更快。顺序读取最小化磁盘查找，即使查询不需要所有行。

图-1

( a) 300行的数目太小，无法在单个索引上进行磨练并随后进行扫描，因此它根本不关心索引，或

( b)您试图使用适当的综合索引，或

( c)你用覆盖指数来获取黄金，并避免读取数据

但是注意到，它将其解析为8行，而不是300行。它与复合(house_id、state、hidden)一起进行，其中最后一个未由您显示。7字节宽总计。

由于您只有300行，所以analyze table应该占用一秒钟时间。它刷新密钥分布的统计信息，使其失效，从而迫使密钥不被使用。密钥可能是要使用的目标，但在执行过程中最终会被放弃。因此，它是关于它的有用性的一般性陈述，例如对于大型表，而不是您的问题。

图-2

带有REF NULL的14行与此答案的开头相关。

Answer 2

第一个查询使用索引，因为它是“覆盖”。也就是说，SELECT中的所有字段都在该索引中：

• 索引键: house_id，状态，隐藏
• 隐式添加的主键: id

我可能错了。它说的是Using index condition，它指的是Index Condition Pushdown，而不是Using index，这意味着“覆盖”。ORDER BY weight...防止“覆盖”。要获得更多的洞察力，请做EXPLAIN FORMAT=JSON SELECT ...。

对于第二个查询，不要使用IN ( SELECT ... )，它的优化效果很差。相反，将其转换为JOIN。一旦你这样做了，我们可以讨论它的性能，如果仍然需要的话。

我们可能会发现，它仍然没有使用指数，但这是运行速度足够快，不必担心。

复合索引应该以"=常数“(在第一个查询中为hidden)的任何列开始。优化器不会同时处理多个“范围”，也可能无法很好地处理IN ( constants )。在我的http://mysql.rjweb.org/doc.php/index_cookbook_mysql中有更多的讨论。

(无关.)

state不应该是空的吗？