Die Abfrage verwendet keinen Covering-Index, falls zutreffend

Question

Ich habe die Mitarbeiterdatenbank und führte einige Abfragen zu Benchmarking-Zwecken aus.
Dann fiel mir auf, dass eine Abfrage keinen abdeckenden Index verwendete, obwohl ein entsprechender Index vorhanden war, den ich zuvor erstellt hatte. Erst als ich einen FORCE INDEX Klausel in die Abfrage einfügen, er verwendete einen Deckungsindex .
Ich habe zwei Dateien hochgeladen, eine ist die ausgeführten SQL-Abfragen und die andere ist die Ergebnisse .
Können Sie sagen, warum die Abfrage nur dann einen Covering-Index verwendet, wenn ein FORCE INDEX Klausel hinzugefügt wird? Das EXPLAIN zeigt, dass in beiden Fällen der Index dept_no_from_date_idx ohnehin verwendet wird.

Um mich an die Standards von SO anzupassen, schreibe ich auch den Inhalt der beiden Dateien hier:

Die SQL-Abfragen:

USE employees;

/* Creating an index for an index-covered query */
    CREATE INDEX dept_no_from_date_idx ON dept_emp (dept_no, from_date);

/* Show `dept_emp` table structure, indexes and generic data */
    SHOW TABLE STATUS LIKE "dept_emp";
    DESCRIBE dept_emp;
    SHOW KEYS IN dept_emp;

/* The EXPLAIN shows that the subquery doesn't use a covering-index */
EXPLAIN SELECT SQL_NO_CACHE * FROM dept_emp INNER JOIN (
        /* The subquery should use a covering index, but isn't */
        SELECT SQL_NO_CACHE emp_no, dept_no FROM dept_emp WHERE dept_no="d001" ORDER BY from_date DESC LIMIT 20000,50
    ) AS `der` USING (`emp_no`, `dept_no`);

/* The EXPLAIN shows that the subquery DOES use a covering-index,
        thanks to the FORCE INDEX clause */
EXPLAIN SELECT SQL_NO_CACHE * FROM dept_emp INNER JOIN (
        /* The subquery use a covering index */
        SELECT SQL_NO_CACHE emp_no, dept_no FROM dept_emp FORCE INDEX(dept_no_from_date_idx) WHERE dept_no="d001" ORDER BY from_date DESC LIMIT 20000,50
    ) AS `der` USING (`emp_no`, `dept_no`);

Die Ergebnisse:

--------------
/* Creating an index for an index-covered query */
    CREATE INDEX dept_no_from_date_idx ON dept_emp (dept_no, from_date)
--------------

Query OK, 331603 rows affected (33.95 sec)
Records: 331603  Duplicates: 0  Warnings: 0

--------------
/* Show `dept_emp` table structure, indexes and generic data */
    SHOW TABLE STATUS LIKE "dept_emp"
--------------

+----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name     | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| dept_emp | InnoDB |      10 | Compact    | 331883 |             36 |    12075008 |               0 |     21544960 |  29360128 |           NULL | 2010-05-04 13:07:49 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.47 sec)

--------------
    DESCRIBE dept_emp
--------------

+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| emp_no    | int(11) | NO   | PRI | NULL    |       |
| dept_no   | char(4) | NO   | PRI | NULL    |       |
| from_date | date    | NO   |     | NULL    |       |
| to_date   | date    | NO   |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+
4 rows in set (0.05 sec)

--------------
    SHOW KEYS IN dept_emp
--------------

+----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| dept_emp |          0 | PRIMARY               |            1 | emp_no      | A         |      331883 |     NULL | NULL   |      | BTREE      |         |
| dept_emp |          0 | PRIMARY               |            2 | dept_no     | A         |      331883 |     NULL | NULL   |      | BTREE      |         |
| dept_emp |          1 | emp_no                |            1 | emp_no      | A         |      331883 |     NULL | NULL   |      | BTREE      |         |
| dept_emp |          1 | dept_no               |            1 | dept_no     | A         |           7 |     NULL | NULL   |      | BTREE      |         |
| dept_emp |          1 | dept_no_from_date_idx |            1 | dept_no     | A         |          13 |     NULL | NULL   |      | BTREE      |         |
| dept_emp |          1 | dept_no_from_date_idx |            2 | from_date   | A         |      165941 |     NULL | NULL   |      | BTREE      |         |
+----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
6 rows in set (0.23 sec)

--------------
/* The EXPLAIN shows that the subquery doesn't use a covering-index */
EXPLAIN SELECT SQL_NO_CACHE * FROM dept_emp INNER JOIN (
        /* The subquery should use a covering index, but isn't */
        SELECT SQL_NO_CACHE emp_no, dept_no FROM dept_emp WHERE dept_no="d001" ORDER BY from_date DESC LIMIT 20000,50
    ) AS `der` USING (`emp_no`, `dept_no`)
--------------

+----+-------------+------------+--------+----------------------------------------------+-----------------------+---------+------------------------+-------+-------------+
| id | select_type | table      | type   | possible_keys                                | key                   | key_len | ref                    | rows  | Extra       |
+----+-------------+------------+--------+----------------------------------------------+-----------------------+---------+------------------------+-------+-------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL                                         | NULL                  | NULL    | NULL                   |    50 |             |
|  1 | PRIMARY     | dept_emp   | eq_ref | PRIMARY,emp_no,dept_no,dept_no_from_date_idx | PRIMARY               | 16      | der.emp_no,der.dept_no |     1 |             |
|  2 | DERIVED     | dept_emp   | ref    | dept_no,dept_no_from_date_idx                | dept_no_from_date_idx | 12      |                        | 21402 | Using where |
+----+-------------+------------+--------+----------------------------------------------+-----------------------+---------+------------------------+-------+-------------+
3 rows in set (0.09 sec)

--------------
/* The EXPLAIN shows that the subquery DOES use a covering-index,
        thanks to the FORCE INDEX clause */
EXPLAIN SELECT SQL_NO_CACHE * FROM dept_emp INNER JOIN (
        /* The subquery use a covering index */
        SELECT SQL_NO_CACHE emp_no, dept_no FROM dept_emp FORCE INDEX(dept_no_from_date_idx) WHERE dept_no="d001" ORDER BY from_date DESC LIMIT 20000,50
    ) AS `der` USING (`emp_no`, `dept_no`)
--------------

+----+-------------+------------+--------+----------------------------------------------+-----------------------+---------+------------------------+-------+--------------------------+
| id | select_type | table      | type   | possible_keys                                | key                   | key_len | ref                    | rows  | Extra                    |
+----+-------------+------------+--------+----------------------------------------------+-----------------------+---------+------------------------+-------+--------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL                                         | NULL                  | NULL    | NULL                   |    50 |                          |
|  1 | PRIMARY     | dept_emp   | eq_ref | PRIMARY,emp_no,dept_no,dept_no_from_date_idx | PRIMARY               | 16      | der.emp_no,der.dept_no |     1 |                          |
|  2 | DERIVED     | dept_emp   | ref    | dept_no_from_date_idx                        | dept_no_from_date_idx | 12      |                        | 37468 | Using where; Using index |
+----+-------------+------------+--------+----------------------------------------------+-----------------------+---------+------------------------+-------+--------------------------+
3 rows in set (0.05 sec)

Bye

---

Bearbeiten:
Mir ist aufgefallen, dass die Ausführungsgeschwindigkeit zwischen den beiden letzten Abfragen erheblich abweicht, und die Ergebnisse liegen vor Ihnen:

SELECT SQL_NO_CACHE * FROM dept_emp INNER JOIN (
    SELECT SQL_NO_CACHE emp_no, dept_no FROM dept_emp WHERE dept_no="d001" ORDER BY from_date DESC LIMIT 20000,50
) AS `der` USING (`emp_no`, `dept_no`)
--------------

+--------+---------+------------+------------+
| emp_no | dept_no | from_date  | to_date    |
+--------+---------+------------+------------+
|  38552 | d001    | 1985-04-16 | 2000-10-20 |
            ... omitted ...
|  98045 | d001    | 1985-03-28 | 9999-01-01 |
+--------+---------+------------+------------+
50 rows in set (0.31 sec)

--------------
SELECT SQL_NO_CACHE * FROM dept_emp INNER JOIN (
    SELECT SQL_NO_CACHE emp_no, dept_no FROM dept_emp FORCE INDEX(dept_no_from_date_idx) WHERE dept_no="d001" ORDER BY from_date DESC LIMIT 20000,50
) AS `der` USING (`emp_no`, `dept_no`)
--------------

+--------+---------+------------+------------+
| emp_no | dept_no | from_date  | to_date    |
+--------+---------+------------+------------+
|  38552 | d001    | 1985-04-16 | 2000-10-20 |
            ... omitted ...
|  98045 | d001    | 1985-03-28 | 9999-01-01 |
+--------+---------+------------+------------+
50 rows in set (0.06 sec)

ABER, wenn ich die Reihenfolge der Ausführung ändere (so dass die letzte Abfrage zuerst und die erste Abfrage zuletzt ausgeführt wird), dann ist die Ausführungsgeschwindigkeit dieselbe:

--------------
SELECT SQL_NO_CACHE * FROM dept_emp INNER JOIN (
    SELECT SQL_NO_CACHE emp_no, dept_no FROM dept_emp FORCE INDEX(dept_no_from_date_idx) WHERE dept_no="d001" ORDER BY from_date DESC LIMIT 20000,50
) AS `der` USING (`emp_no`, `dept_no`)
--------------

+--------+---------+------------+------------+
| emp_no | dept_no | from_date  | to_date    |
+--------+---------+------------+------------+
|  38552 | d001    | 1985-04-16 | 2000-10-20 |
            ... omitted ...
|  98045 | d001    | 1985-03-28 | 9999-01-01 |
+--------+---------+------------+------------+
50 rows in set (0.08 sec)

--------------
SELECT SQL_NO_CACHE * FROM dept_emp INNER JOIN (
    SELECT SQL_NO_CACHE emp_no, dept_no FROM dept_emp WHERE dept_no="d001" ORDER BY from_date DESC LIMIT 20000,50
) AS `der` USING (`emp_no`, `dept_no`)
--------------

+--------+---------+------------+------------+
| emp_no | dept_no | from_date  | to_date    |
+--------+---------+------------+------------+
|  38552 | d001    | 1985-04-16 | 2000-10-20 |
            ... omitted ...
|  98045 | d001    | 1985-03-28 | 9999-01-01 |
+--------+---------+------------+------------+
50 rows in set (0.08 sec)

Es kann nicht sein, dass die zweite Abfrage aus dem Cache genommen wird, weil SQL_NO_CACHE in beiden Abfragen geschrieben wird. Warum dauerte also im ersten Beispiel die erste Abfrage 0,31 Sekunden und die zweite 0,06 Sekunden, aber im zweiten Beispiel benötigen beide Abfragen 0,08 Sekunden?

Bearbeiten2:
Ich denke, dass die Unterschiede in der Ausführungsgeschwindigkeit auf den Cache des Betriebssystems und vielleicht auf andere Faktoren zurückzuführen sind. Bei wiederholter Ausführung der oben genannten 2 Abfragen werden die Unterschiede in der Ausführungszeit vernachlässigbar. Ich habe die oben genannten 2 Abfragen 3 Mal wiederholt ausgeführt und folgende Ergebnisse erhalten:

#1: 0.08 sec
#2: 0.03 sec
#1: 0.05 sec
#2: 0.05 sec
#1: 0.03 sec
#2: 0.05 sec

Answer 1

In der Tat verwenden beide Ihre Abfragen den abdeckenden Index.

Ihre Indexdefinition umfasst nicht emp_no , also in MyISAM , Using index wäre selbst mit dem FORCE INDEX Klausel.

Allerdings, InnoDB Tabellen sind geclustert und jeder Index enthält implizit die PRIMARY KEY als Datensatzzeiger.

Dies bedeutet, dass Ihr Index in Wirklichkeit ein Index auf (dept_no, from_date, emp_no, dept_no) und enthält daher alle erforderlichen Felder.

EXPLAIN PLAN spiegelt dies nicht immer korrekt wider, aber InnoDB Motor kommt damit zurecht.

Sie können dies überprüfen, indem Sie die Leistung dieser beiden Abfragen vergleichen:

SELECT SQL_NO_CACHE * FROM dept_emp INNER JOIN (
        /* The subquery use a covering index */
        SELECT SQL_NO_CACHE from_date, emp_no, dept_no FROM dept_emp WHERE dept_no="d001" ORDER BY from_date DESC LIMIT 20000,50
    ) AS `der` USING (`emp_no`, `dept_no`);

y

SELECT SQL_NO_CACHE * FROM dept_emp INNER JOIN (
        /* The subquery use a covering index */
        SELECT SQL_NO_CACHE to_date, emp_no, dept_no FROM dept_emp WHERE dept_no="d001" ORDER BY from_date DESC LIMIT 20000,50
    ) AS `der` USING (`emp_no`, `dept_no`);

Sie werden sehen, dass trotz der Tatsache, dass die Pläne als identisch angezeigt werden, die zweite Abfrage viel mehr Zeit in Anspruch nehmen wird (eben weil to_date ist nicht abgedeckt).

Es ist ein Fehler in EXPLAIN PLAN , nicht in InnoDB Motor.