php - 为什么这个 MySQL 查询在没有索引的情况下更快？

Question

我无法理解为什么当我将 MySQL 查询更改为不使用索引时它运行得更快。

我的第一个查询需要 0.236 秒才能运行:

SELECT 
    u.id, 
    u.email, 
    CONCAT(u.first_name, ' ', u.last_name) AS u_name
FROM
    tbl_user AS u
WHERE
    u.site_id=1
    AND u.role_id=5
    AND u.removed_date IS NULL
ORDER BY 
    u_name ASC 
LIMIT 0, 20

我的第二个查询需要 0.147 秒才能运行:

SELECT 
    u.id, 
    u.email, 
    CONCAT(u.first_name, ' ', u.last_name) AS u_name
FROM
    tbl_user AS u USE INDEX () 
WHERE
    u.site_id=1
    AND u.role_id=5
    AND u.removed_date IS NULL
ORDER BY 
    u_name ASC 
LIMIT 0, 20

我在 site_id、role_id 和 email 列上有一个名为 idx_1 的唯一索引。

EXPLAIN 语句表明它将使用 idx_1。

+----+-------------+-------+------+-------------------------------------+-------+---------+-------------+-------+----------------------------------------------------+
| id | select_type | table | type | possible_keys                       | key   | key_len | ref         | rows  | Extra                                              |
+----+-------------+-------+------+-------------------------------------+-------+---------+-------------+-------+----------------------------------------------------+
|  1 | SIMPLE      | u     | ref  | idx_1,idx_import,tbl_user_ibfk_2    | idx_1 | 8       | const,const | 55006 | Using index condition; Using where; Using filesort |
+----+-------------+-------+------+-------------------------------------+-------+---------+-------------+-------+----------------------------------------------------+

该表大约有110000条记录。

谢谢

更新 1:

下面是我的表索引列表:

Name                Fields                      Type    Method
---------------------------------------------------------------
idx_1               site_id, role_id, email     Unique  BTREE
idx_import          site_id, external_id        Unique  BTREE
tbl_user_ibfk_2     role_id                     Normal  BTREE
tbl_user_ibfk_3     country_id                  Normal  BTREE
tbl_user_ibfk_4     preferred_country_id        Normal  BTREE
---------------------------------------------------------------

Answer 1

您还没有指定您使用的是哪个 mysql。这个能解释吗

Prior to MySQL 5.1.17, USE INDEX, IGNORE INDEX, and FORCE INDEX affect only which indexes are used when MySQL decides how to find rows in the table and how to process joins. They do not affect whether an index is used when resolving an ORDER BY or GROUP BY clause.

来自 https://dev.mysql.com/doc/refman/5.1/en/index-hints.html