mysql - mysql 选择了错误的索引

Question

1. 为什么我得到 Using where;使用索引；使用临时的；在我的中使用 filesort解释 cmd 因为它减慢了我的查询 1.3s 。

解释:

+----+-------------+--------------+--------+---------------------------------------------------+---------------------+---------+-----------------------------+-------+-----------------------------------------------------------+
| id | select_type | table        | type   | possible_keys                                     | key                 | key_len | ref                         | rows  | Extra                                                     |
+----+-------------+--------------+--------+---------------------------------------------------+---------------------+---------+-----------------------------+-------+-----------------------------------------------------------+
|  1 | SIMPLE      | lieuexecut1_ | ref    | fk_ao_lieuex,fk_region_lieuex,idao_idregion_index | idao_idregion_index | 5       | const                       | 27343 | Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | appeloffre0_ | eq_ref | PRIMARY                                           | PRIMARY             | 4       | ao.lieuexecut1_.appel_offre |     1 |                                                           |
+----+-------------+--------------+--------+---------------------------------------------------+---------------------+---------+-----------------------------+-------+-----------------------------------------------------------+

1. 为什么当我强制使用索引 fk_ao_lieuex 我的外键 use index (fk_ao_lieuex) 查询运行速度快 0.0x s 。

解释

+----+-------------+--------------+-------+---------------+--------------+---------+--------------------------------+------+-------------+
| id | select_type | table        | type  | possible_keys | key          | key_len | ref                            | rows | Extra       |
+----+-------------+--------------+-------+---------------+--------------+---------+--------------------------------+------+-------------+
|  1 | SIMPLE      | appeloffre0_ | index | PRIMARY       | PRIMARY      | 4       | NULL                           |   10 |             |
|  1 | SIMPLE      | lieuexecut1_ | ref   | fk_ao_lieuex  | fk_ao_lieuex | 4       | ao.appeloffre0_.ID_APPEL_OFFRE |    1 | Using where |
+----+-------------+--------------+-------+---------------+--------------+---------+--------------------------------+------+-------------+

这是我的查询:

select  .... from ao.appel_offre appeloffre0_ 
inner join ao.lieu_execution lieuexecut1_ 
on appeloffre0_.ID_APPEL_OFFRE=lieuexecut1_.appel_offre 
where lieuexecut1_.region=1
group by appeloffre0_.ID_APPEL_OFFRE 
order by appeloffre0_.ID_APPEL_OFFRE desc 
limit 10

我的表索引 lieu_execution

+----------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-
| Table          | Non_unique | Key_name            | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type |
+----------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-
| lieu_execution |          0 | PRIMARY             |            1 | id          | A         |       62127 |     NULL | NULL   |      | BTREE      |
| lieu_execution |          1 | fk_ao_lieuex        |            1 | appel_offre | A         |       62127 |     NULL | NULL   |      | BTREE      |
| lieu_execution |          1 | fk_province_lieuex  |            1 | province    | A         |           2 |     NULL | NULL   | YES  | BTREE      |
| lieu_execution |          1 | fk_region_lieuex    |            1 | region      | A         |           2 |     NULL | NULL   | YES  | BTREE      |
| lieu_execution |          1 | fk_ville_lieuex     |            1 | ville       | A         |          13 |     NULL | NULL   | YES  | BTREE      |
| lieu_execution |          1 | idao_idregion_index |            1 | region      | A         |         227 |     NULL | NULL   | YES  | BTREE      |
| lieu_execution |          1 | idao_idregion_index |            2 | appel_offre | A         |         227 |     NULL | NULL   |      | BTREE      |
+----------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-

Answer 1

你需要做两件事:

1. 创建复合(region, appel_offre)索引
2. 您将 GROUP BY 和 ORDER BY 子句改为使用 lieuexecut1_.appel_offre 列。

它提高了性能，因为现在 mysql 优化器有机会在 lieuexecut1_.region=1 谓词和进一步 中使用刚刚创建的索引GROUP BY 和排序。否则，它必须在应用 LIMIT 之前完成所有操作，因此速度很慢。