mysql - 在 mysql 中使用索引不会缩短查询时间

Question

我有这个 sql 查询:

SELECT Frage_ID 
FROM Session_Fragen WHERE 
(
    Userantwort1 = 0 
    AND Userantwort2 = 0 
    AND Userantwort3 = 0 
    AND Userantwort4 = 0 
    AND Userantwort5 = 0
) AND Session_ID = 17898 
ORDER BY Sessionfrage_ID ASC LIMIT 1;

一开始这个查询运行的很慢。我添加了索引以提高速度。最好使用我扫描过的索引。 500.000 行...使用索引后，我将行减少到大约。 550(取决于结果)但查询仍然需要超过 2 秒。运行。

我希望有人对我有任何改进的建议...

坦克很多!

---

SQL 解释:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  Session_Fragen  index   Session_Fragen_big_index_2,Session_Fragen_big_inde...   PRIMARY     8   NULL    535     Using where

---

显示索引:

Session_Fragen  0   PRIMARY     1   Sessionfrage_ID     A   626229  NULL    NULL        BTREE   
Session_Fragen  1   Frage_ID    1   Frage_ID    A   3479    NULL    NULL        BTREE   
Session_Fragen  1   Session_Fragen_big_index_2  1   Userantwort1    A   2   NULL    NULL        BTREE   
Session_Fragen  1   Session_Fragen_big_index_2  2   Userantwort2    A   3   NULL    NULL        BTREE   
Session_Fragen  1   Session_Fragen_big_index_2  3   Userantwort3    A   5   NULL    NULL        BTREE   
Session_Fragen  1   Session_Fragen_big_index_2  4   Userantwort4    A   7   NULL    NULL        BTREE   
Session_Fragen  1   Session_Fragen_big_index_2  5   Userantwort5    A   9   NULL    NULL        BTREE   
Session_Fragen  1   Session_Fragen_big_index    1   Session_ID  A   16057   NULL    NULL        BTREE   
Session_Fragen  1   Session_Fragen_big_index    2   Frage_ID    A   626229  NULL    NULL        BTREE   
Session_Fragen  1   Session_Fragen_big_index    3          
Sessionfrage_ID     A   626229  NULL    NULL        BTREE   
Session_Fragen  1   Userantwort1    1   Userantwort1    A   2   NULL    NULL        BTREE   
Session_Fragen  1   Userantwort2    1   Userantwort2    A   2   NULL    NULL        BTREE   
Session_Fragen  1   Userantwort3    1   Userantwort3    A   2   NULL    NULL        BTREE   
Session_Fragen  1   Userantwort4    1   Userantwort4    A   2   NULL    NULL        BTREE   
Session_Fragen  1   Userantwort5    1   Userantwort5    A   2   NULL    NULL        BTREE   
Session_Fragen  1   Session_ID  1   Session_ID  A   16057   NULL    NULL        BTREE

---

表结构:

Session_Fragen  CREATE TABLE `Session_Fragen` (
 `Sessionfrage_ID` bigint(20) NOT NULL AUTO_INCREMENT,
 `User_ID` int(7) NOT NULL,
 `SF_Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `Session_ID` int(11) NOT NULL,
 `Frage_ID` int(11) NOT NULL,
 `Userantwort1` tinyint(1) NOT NULL DEFAULT '0',
 `Userantwort2` tinyint(1) NOT NULL DEFAULT '0',
 `Userantwort3` tinyint(1) NOT NULL DEFAULT '0',
 `Userantwort4` tinyint(1) NOT NULL DEFAULT '0',
 `Userantwort5` tinyint(1) NOT NULL DEFAULT '0',
 PRIMARY KEY (`Sessionfrage_ID`),
 KEY `Frage_ID` (`Frage_ID`),
 KEY `Session_Fragen_big_index_2`         (`Userantwort1`,`Userantwort2`,`Userantwort3`,`Userantwort4`,`Userantwort5`),
 KEY `Session_Fragen_big_index` (`Session_ID`,`Frage_ID`,`Sessionfrage_ID`),
 KEY `Userantwort1` (`Userantwort1`),
 KEY `Userantwort2` (`Userantwort2`),
 KEY `Userantwort3` (`Userantwort3`),
 KEY `Userantwort4` (`Userantwort4`),
 KEY `Userantwort5` (`Userantwort5`),
 KEY `Session_ID` (`Session_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=862095 DEFAULT CHARSET=utf8

Answer 1

我为这个查询推荐以下索引:

ALTER TABLE Session_Fragen ADD INDEX sf_sess_fragen_id (
  Session_ID, 
  Userantwort1, 
  Userantwort2,
  Userantwort3,
  Userantwort4,
  Userantwort5,
  Sessionfrage_ID,
  Frage_ID
);

我的理由是:

• 首先添加等式谓词中引用的所有列。将 Session_ID 列放在第一位，因为它最有可能快速缩小搜索范围。

• 在这些列之后，添加 Sessionfrage_ID 列进行排序。一旦搜索找到符合 WHERE 子句中条件的行，这些行就会有效地按绑定(bind)顺序排列。顺序由索引的下一列决定，MySQL 的优化器将使用此顺序，而不必对匹配的行进行排序。

• 最后，从选择列表中添加 Frage_ID 列。这既不用于搜索也不用于排序，但将其包含在索引中会创建一个覆盖索引。

我测试了这个但是没有合理的测试数据，优化器没有自动使用这个新索引。如果我使用 index hint为了哄它使用新索引，我得到了一个更好的 EXPLAIN 报告(如果你在你的真实表上运行它，索引提示可能是不必要的)。

mysql> explain SELECT Frage_ID FROM Session_Fragen use index (sf_sess_fragen_id) 
WHERE  (Userantwort1 = 0 AND Userantwort2 = 0 AND Userantwort3 = 0 
    AND Userantwort4 = 0 AND Userantwort5 = 0 ) 
  AND Session_ID = 17898
ORDER BY Sessionfrage_ID ASC LIMIT 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Session_Fragen
         type: ref
possible_keys: sf_sess_fragen_id
          key: sf_sess_fragen_id
      key_len: 9
          ref: const,const,const,const,const,const
         rows: 1
        Extra: Using where; Using index

关于这个 EXPLAIN 的好处:

• rows 很好而且很低，因为 Session_ID 缩小了搜索范围。
• Using index 表示仅索引查询(即覆盖索引)，这意味着查询根本不需要读取表行。
• 我们没有看到type: index(索引扫描)，也没有看到Using filesort。

查看我的演示 How to Design Indexes, Really有关设计索引的更多指南。