索引 IN 和范围

Question

我需要找到此查询的最佳索引：

SELECT c.id id, type
FROM Content c USE INDEX (type_proc_last_cat)
LEFT JOIN Battles b ON c.id = b.id
WHERE type = 1
    AND processing_status = 1
    AND category IN (13, 19)
    AND status = 4
ORDER BY last_change DESC
LIMIT 100";

表如下所示：

mysql> describe Content;
+-------------------+---------------------+------+-----+---------+-------+
| Field             | Type                | Null | Key | Default | Extra |
+-------------------+---------------------+------+-----+---------+-------+
| id                | bigint(20) unsigned | NO   | PRI | NULL    |       |
| type              | tinyint(3) unsigned | NO   | MUL | NULL    |       |
| category          | bigint(20) unsigned | NO   |     | NULL    |       |
| processing_status | tinyint(3) unsigned | NO   |     | NULL    |       |
| last_change       | int(10) unsigned    | NO   |     | NULL    |       |
+-------------------+---------------------+------+-----+---------+-------+

mysql> show indexes from Content;
+---------+------------+---------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name            | Seq_in_index | Column_name       | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+---------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
| Content |          0 | PRIMARY             |            1 | id                | A         |        4115 |     NULL | NULL   |      | BTREE      |         |
| Content |          1 | type_proc_last_cat  |            1 | type              | A         |           4 |     NULL | NULL   |      | BTREE      |         |
| Content |          1 | type_proc_last_cat  |            2 | processing_status | A         |          20 |     NULL | NULL   |      | BTREE      |         |
| Content |          1 | type_proc_last_cat  |            3 | last_change       | A         |        4115 |     NULL | NULL   |      | BTREE      |         |
| Content |          1 | type_proc_last_cat  |            4 | category          | A         |        4115 |     NULL | NULL   |      | BTREE      |         |
+---------+------------+---------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+


mysql> describe Battles;
+---------------------+---------------------+------+-----+---------+-------+
| Field               | Type                | Null | Key | Default | Extra |
+---------------------+---------------------+------+-----+---------+-------+
| id                  | bigint(20) unsigned | NO   | PRI | NULL    |       |
| status              | tinyint(4) unsigned | NO   |     | NULL    |       |
| status_last_changed | int(11) unsigned    | NO   |     | NULL    |       |
+---------------------+---------------------+------+-----+---------+-------+

mysql> show indexes from Battles;
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Battles |          0 | PRIMARY   |            1 | id          | A         |        1215 |     NULL | NULL   |      | BTREE      |         |
| Battles |          0 | id_status |            1 | id          | A         |        1215 |     NULL | NULL   |      | BTREE      |         |
| Battles |          0 | id_status |            2 | status      | A         |        1215 |     NULL | NULL   |      | BTREE      |         |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

我得到的输出如下：

mysql> explain
    -> SELECT c.id id, type
    -> FROM Content c USE INDEX (type_proc_last_cat)
    -> LEFT JOIN Battles b USE INDEX (id_status) ON c.id = b.id
    -> WHERE type = 1
    ->     AND processing_status = 1
    ->     AND category IN (13, 19)
    ->     AND status = 4
    -> ORDER BY last_change DESC
    -> LIMIT 100;
+----+-------------+-------+--------+--------------------+--------------------+---------+-----------------------+------+--------------------------+
| id | select_type | table | type   | possible_keys      | key                | key_len | ref                   | rows | Extra                    |
+----+-------------+-------+--------+--------------------+--------------------+---------+-----------------------+------+--------------------------+
|  1 | SIMPLE      | c     | ref    | type_proc_last_cat | type_proc_last_cat | 2       | const,const           | 1352 | Using where; Using index |
|  1 | SIMPLE      | b     | eq_ref | id_status          | id_status          | 9       | wtm_master.c.id,const |    1 | Using where; Using index |
+----+-------------+-------+--------+--------------------+--------------------+---------+-----------------------+------+--------------------------+

问题是内容表的行数。看来MySQL无法有效地使用type_proc_last_cat索引中的last_change和category。如果我切换 last_change 和类别的顺序，则会选择更少的行，但会导致 ORDER BY 的文件排序，这意味着它会从数据库中提取所有匹配的行。情况更糟，因为两个表中都有 100,000 多行。

表都是 InnoDB，因此请记住主键附加到每个其他索引。因此，上面的索引 type_proc_last_cat 的行为就像它在 (type,processing_status,last_change,category,id) 上一样。我知道我可以将战斗的主键更改为 (id, status) 并删除 id_status 索引（我可能会这样做）。

编辑：type、category、processing_status 和 status 的任何值都小于总值的 20%。 last_change 和 status_last_change 是 unix 时间戳。

编辑：如果我以相反的顺序使用 category 和 last_change 的不同索引，我会得到以下信息：

mysql> show indexes from Content;
+---------+------------+---------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name            | Seq_in_index | Column_name       | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+---------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
| Content |          0 | PRIMARY             |            1 | id                | A         |        4115 |     NULL | NULL   |      | BTREE      |         |
| Content |          1 | type_proc_cat_last  |            1 | type              | A         |           6 |     NULL | NULL   |      | BTREE      |         |
| Content |          1 | type_proc_cat_last  |            2 | processing_status | A         |          26 |     NULL | NULL   |      | BTREE      |         |
| Content |          1 | type_proc_cat_last  |            3 | category          | A         |         228 |     NULL | NULL   |      | BTREE      |         |
| Content |          1 | type_proc_cat_last  |            4 | last_change       | A         |        4115 |     NULL | NULL   |      | BTREE      |         |
+---------+------------+---------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+


mysql> explain SELECT c.id id, type FROM Content c USE INDEX (type_proc_cat_last) LEFT JOIN Battles b 
USE INDEX (id_status) ON c.id = b.id WHERE type = 1     AND processing_status = 1     AND category IN (13, 19)     AND status = 4 ORDER BY last_change DESC LIMIT 100;
+----+-------------+-------+-------+--------------------+--------------------+---------+-----------------------+------+------------------------------------------+
| id | select_type | table | type  | possible_keys      | key                | key_len | ref                   | rows | Extra                                    |
+----+-------------+-------+-------+--------------------+--------------------+---------+-----------------------+------+------------------------------------------+
|  1 | SIMPLE      | c     | range | type_proc_cat_last | type_proc_cat_last | 10      | NULL                  |  165 | Using where; Using index; Using filesort |
|  1 | SIMPLE      | b     | ref   | id_status          | id_status          | 9       | wtm_master.c.id,const |    1 | Using where; Using index                 |
+----+-------------+-------+-------+--------------------+--------------------+---------+-----------------------+------+------------------------------------------+

文件排序让我担心，因为它告诉我 MySQL 首先提取所有匹配的行，排序之前。当有100,000+时这将是一个大问题。

Answer 1

EXPLAIN 中的 rows 字段并不反映实际读取的行数。它反映了可能受到影响的行数。此外，它不依赖于LIMIT，因为LIMIT是在计算计划之后应用的。

所以你不需要担心它。

另外我建议你在type_proc_last_cat中交换last_change和category，这样mysql就可以尝试使用最后一个索引部分(last_change)代码>) 用于排序目的。